18일차 MySQL [ select, where, join, group order 함수 등 ]

DML ( Select, Insert, Update, Delete)
DDL ( Alter, Create, Drop )
DCL ( Grant, Rollback ...)

 

 

[ Create Schemas ]

   Administration과 Schemas중 Schemas 선택 -> Navigator창에 오른쪽 버튼 클릭 -> Create Schemas -> 이름 설정

---

[ create table ] 

     - 기본키, 외래키, 후보키

※ 기본키 : 식별할 수 있는 값 not nul, 중복불가능 !! (학생아이디,과목코드,...)

※ 외래키 : 외래키 = 참조키 즉, 수강테이블에서 ..  할때 필요한 

※ 후보키 : 후보키 = 대체키 즉, 기본키를 대체할만 한 키  (보통 개발자가 인위적으로 증가시킴)

 

왼쪽 위에 Create a new SQL tab for executing queries 클릭 -> SQL File 숫자 클릭 -> 쿼리창에 create table 테이블명

  - ;이 끝나는 지점이 에서 실행하면 한 블록씩 실행됨. 주황색블록 한번 실행되고 핑크색 실행 됨. 

  - 그래서 전체 드래그 Ctrl+A 후 Ctrl + Enter or  번개모양 클릭 하면 전체 실행 됨.

  -  Ctrl+Shift+Enter 드래그 없이 전체 실행

     ( 전체 드래그 아니면 한줄 한줄 해야하기 때문에 10 12 20 22 각 줄을 클릭하고 Ctrl+Enter해야지 실행됨 )

  - stu_no char(9), -> 학번은 최대 (9)개 올수 있다.

  - stu_name varchar(12), -> 가변길이 

  - stu_height decimal(5,2), -> 정수 5개 소수 2 개 

  - constraint 제약조건 primary key(stu_no)가 우리를 구별짓는 식별자

  - desc student; 테이블 구조 확인하고 싶을 때 쓰는 명령어 ( 주황네모 보라네모 잘 만들어졌는지 확인하는 작업 )

  - 이 테이블에서 엔티티는 Student / 속성은 필드 이름 stu_no stu_name stu_dept... 

 

[ create table ]

create table t_tbl

 

-- 부서번호 20인 데이터 뽑아 t_tbl 삽입
insert into t_tbl select * from emp where deptno='20';

-- alter : 테이블 구조 변경 
alter table t_tbl add(t_gender char(1)); -- t_tbl에 성별 열 삽입 - char(1)

alter table product add 비고 varchar(20); --product테이블에 열 이름이 ‘비고’ 라는 열을 varchar2(20)으로 삽입해라.

-- modify : 구조 변경
alter table t_tbl modify t_gender varchar(10); -- 성별 열의 varchar(10)으로 변경 

alter table product modify 비고 char(3); -- product테이블에 ‘비고’ 열의 구조를 char(3)으로 변경해라.

-- drop : 열 삭제
alter table t_tbl drop t_gender;

 

-- 테이블 확인
desc t_tbl;

 

-- truncate : 모든 데이터 삭제해라 (구조는 남아있음 delete랑 같은 의미)
truncate table t_tbl;

-- delete : 모든 데이터 삭제해라 (구조는 남아있음 truncate랑 같은 의미)
delete from t_tbl;

 

---

[ Constraint 제약조건 ]

    constraint pk_tempno primary key(t_empno); 기본키에 대한 제약을 거는 조건을 p_k로 준것

 

데이터베이스가 상태가 항상 만족해야할 기본 규칙

1) 키 제약 조건 : 테이블에서 각 튜플을 유일하게 식별할 수 있는 수단 (기본키)

2) 무결성 제약 조건 : 

3) 기본키에 있는 속성값들은 어떠한 경우에도 null 값이 될 수 없다 !!!

    NOT NULL : 옆에 NULL 값을 허용하지 않음. 

    UNIQUE KEY : 열 또는 열 조합이 유일성을 가져야 함.

    PRIMARY KEY : 열에 NULL 허용 안 되고 유일성을 가져야 함. (NOT NULL 과 UNIQUE KEY 특징 섞인 것)

    FOREIGN KEY : 다른 테이블에 참조하는 튜플에 값이 있어야함.

 

[ add : 제약 조건 추가 ]
alter table student add constraint fk_stu foreign key(major_code) ㅇ major(major_code);
      -- student 테이블에 있는 major 코드가 major 테이블에 있는 major 코드를 참조한다.
alter table prof add constraint fk_prof foreign key(major_code) references major(major_code);

 

 

[ insert into 테이블명 values( ); ]

     - 위에서 만들어논 테이블 ( enrol, student, subject ) 에 값 추가하기

     - insert into student values(20153075,'옥한빛','기계',1,'C','M',177,80);

create table을 통해 만들어진 테이블에 값을 추가하는 insert into values

insert into student values, insert into enrol values, insert into subject values를 통해 만들어진 테이블에 값 추가함 !!

그리고 확인하고 싶은 테이블에 [ 오른쪽버튼 -> Select Rows - Limit 1000 클릭 ] -> 핑크색네모를 보면 테이블에 값 추가 된거 알 수 있음 

 

[ table 테이블 복사 ] -  [ create table 복사테이블명 as select * from 기존테이블명 ]

    create table a_enrol as select * from enrol where stu_no < 20150000; 

         -- enrol 테이블로 부터 15학번 이전학생들만 전체 복사해서
      select * from a_enrol; -- 출력 해보기

 

[ INSERT 데이터 삽입 ] - [ insert into 테이블명 values (값) ]
    insert into a_enrol(sub_no, stu_no, enr_grade) values (108,20151062,92);

        -- 새로운 행 추가 sub_no에 108, stu_no에 20151062 , enr_grade에 92 추가 됨

    insert into a_enrol values (108,20151062,92); 

        -- ()안에 안 적어도 상관없지만 안 적을때는 반드시 순서와 갯수  맞춰서 값 적어줘야함

            즉, 개수와 필드에 맞게 값 넣으면 필드 생략 가능 !!!

    insert into a_enrol(sub_no, stu_no) values (110, 20152088);

        -- 필드 수와 맞지 않게 넣으려면 반드시 () 안에 명시 이경우 enr_grade의 값은 null

    insert into a_enrol select * from enrol where stu_no like '201%';

        -- values 대신에 select ~ from ~ where 문도 가능 !

    insert into subject1 values(112,'자동화시스템','고종민',3,'기계');

        -- 과목번호 112, 과목이름 자동화시스템, 교수명 고종민, 학년 3, 학과 기계가 추가되었다.

     select * from a_enrol; -- 출력 해보기 값 추가 후 

insert into 테이블명 values (값)

 

 

[ UPDATE 데이터 수정 ]

   update a_erol set enr_grade=enr_grade+10 where sub_no  = (select sub_no from subject where sub_name='시스템분석설계');

        -- 과목번호가 104번인 성적을 10씩 더함

    update subject set sub_no='501' where sub_no='110';

        -- 과목번호 110이 501로 변경되었다.

 

[ DELET 데이터 삭제 ] - DELETE FROM 테이블이름 WHERE 조건;

    - 삭제 ( delete from where ) 테이블은 남아 있지만 데이터가 없다 ( 모든 튜플을 삭제했기 때문 )
   delete from a_enrol where stu_no = '20131001';
   delete from a_enrol; -- 모든 데이터 삭제

   delete from student1 where stu_no=20153088;

      -- 20153088학생이 자퇴하였다.
    select * from a_enrol; -- 출력

 

 

 

 

[ use 데이터베이스이름 ]

하기 전에 반드시 use yujung;을 적어야지 아니면 원하지 않는 데이터베이스에 쿼리가 들어갈 수도 있음 !!!

이 데이터베이스에서 쿼리문 짜겠단 의미 !

USE 구문 : SELECT문 학습 위해 사용할 데이터베이스 지정  -> 지정해 놓은 후 특별히 다시 USE문 사용하거나 다른 DB 를 사용하겠다고 명시하지 않는 이상 모든 SQL문은 지정 DB에서 수행됨 

Workbench 에서 직접 선택해서 사용도 가능 : [Navigator]의 [Schemas] 탭 – employees 데이터베이스를 더블 클릭하면 진한 글자 전환 – 왼쪽 아래‘Active schema changed to employees’

 

---

[ SELECT... FROM ]

  - select *from 테이블명; : 모든 데이터 추출 

  - select stu_no, stu_name from 테이블명; : 원하는 데이터만 추출 

빨간네모는 모든 데이터 추출하겠단 의미 / 핑크네모는 내가 원하는 부분만 추출하겠단 의미

 

 

[ enr_grade+10 grade에 점수 +10을 추가하는 연산작업도 가능 !! ]

+10 연산도 가능 !!!

 

[ 두가지 속성을 더해서 추출 하는 contact함수 ]

   - select concat(stu_dept,stu_name) from student; 

   - stu_name as 학생이름을 통해 이름을 지어줄 수도 있음 !!

두개 이상의 열을 합쳐서 검색 concat(stu_dept,stu_name) ' ' 공백 멘트 추가 가능/ as 이름설정

 

 

[ 중복된 데이터를 제거하고 출력하는 distinct 함수 ]

  - select distinct stu_dept from student; 

중복제거(distinct)

 

[ limit 원하는 만큼만 데이터 가져오기 ]

  - select * from student limit 5;     

        -- 5개 절만 가져오기

  - select * from student limit 1,5;  

        -- 인덱스 1부터 5개절 가져오기

 

select * from student limit 5;

 

[ where절 사용 ]

  - 컴퓨터정보 학생들을 검색하기 , and를 사용하여 두가지 조건 모두를 충족시키는 정보를 검색할 수도 있다.

  - select * from student where stu_dept = '컴퓨터정보';   

           -- student로 부터 모든 정보에서 학과명에 컴퓨터정보있는 정보 가져오기

  - select * from student where stu_dept='컴퓨터정보' and stu_grade=2;   

           -- and를 사용해서 컴퓨터정보학과와 2학년 정보 둘다 조건을 충족하는 정보만 가져오기   

select * from student where stu_dept='컴퓨터정보';

 

[ 범위조건 where between ]

   - % : 0개 이상 문자
   -  _ : 1개의  문자

   - between 60 and 70 : 60과 70사이

   - not , not in, <> ('컴퓨터정보', '기계') : 컴퓨터정보나 기계가 있으면 출력 or이랑 같은 뜻 

     where not stu_dept = '컴퓨터정보' : 컴퓨터정보만 빼고 출력 이외라는 뜻 세가지 방법 !

     where stu_dept  not in(컴퓨터정보);

     where stu_dept <>('컴퓨터정보');

 

 

[ null 값 존재 여부 확인 ]

  - select * from student where stu_height is null;

         -- 키값이 비어있는 조건을 찾는 작업
  - select * from student where stu_height is not null; 

        -- 키값이 비어있는 조건을 빼고 찾는 작업

 

[ ifnull / nullif  ]

    - ifnull(수식1, 수식2) : 수식 1이 null이 아니면 수식 1 반환, null이면 수식 2 반환
      select ifnull(stu_height,0) from student; 

          -- null값만 0 출력 나머진 원래 키 출력

    - nullif(인수1, 인수2) : 두개값 비교해서 값 같으면 null반환, 아니면 인수1 반환

       select ifnull(nullif('A','A'),'널값'); 

          -- 널값 출력 ifnull(null,'널값');

ifnull / nullif

 

[ 순서화 정렬작업 ]

  - select stu_no, stu_name from student order by stu_no; 

       -- 학번 이름 순으로 정렬 오름차순(작->큰)

  - select stu_no, stu_name from student order by stu_no desc;

       -- 내림차순 (큰->작)

 

[ order by ]

  - 별칭이 붙어있는 열을 기준으로 정렬, 열의 순서번호 이용하여 정렬 

  - select stu_no, stu_name, stu_dept, stu_weight-5 as target from student order by target;

    -- 별칭이 붙어있는 열을 기준으로 정렬
 - select stu_no, stu_name, stu_dept, stu_weight-5 as target from student order by 3;

    -- 3번째 열을 기준 ( stu_dept기준으로 정렬 됨 )
  - select stu_no, stu_name, stu_dept, stu_weight-5 as target from student order by stu_weight-5;

    -- 몸무게에서 -5한 값을 기준으로 정렬하겠다
  - select stu_no, stu_name, stu_dept, stu_weight-5 as target from student order by stu_dept, target;

    -- 학과가 먼저 정렬되고 그 이후 target 정렬됨
  - select stu_no, stu_name, stu_dept, stu_weight-5 as target from student order by stu_dept desc, target desc;

    -- 둘다내림차순 하려면 둘다 desc 적어줘야함 만약 하나만 적어주면 적어준 곳만 내림차순 안 적은곳은 오름차순

 

---

[ 사칙연산 ]

▷ 사칙연산
     select 1+2;  -- 3출력
     select 3*(2+4)/2, 'hi';
     select 10%3;

▷ 문자열에 사칙연산을 가하면 0으로 인식
     select 'AB' + 3;  -- 3출력
     select 'AB' * 3;  -- 0출력

 

▷ true는 1, false는 0
     select true, false;  

         -- true는 1 출력  false는 0 출력
     select true is true; 

          -- true니깐 1출력 
     select 2+4=6 or 2*4=8; 

          -- or니깐 둘중 하나만 맞아도 참. 즉, true 1 출력
     select 'B'='b';

          -- MySQL의 기본 사칙 연산자는 대소문자 구분하지 않는다. 즉, true 1 출력

     select 10 between 15 and 20; 

          -- flase 0 출력
     select 'apple' not between 'banana' and 'computer'; 

          -- ture 1 출력
     select 1+2 in(2,3,4); 

         -- true 1 출
     select 'hi' in (1,true,'hi');

           -- hi가 in안에 들어있냐 -> true 1 출력

 

▷ round (반올림)
    select round(345.678),round(345.678,0),round(345.678,1),round(345.678,2),
    round(345.678,-1); -- 346, 346, 345.7, 345.68, 350 출력

▷ truncate(버림,소숫점자리)
     select truncate(1234.56789, 1), truncate(1234.56789, 2), truncate(1234.56789, -1); -- 1234.5, 1234.56, 1230 출력
     select truncate(1234.56789, -2), truncate(1234.56789, -3); -- 1200, 1000 출력

▷ upper (대문자), lower (소문자)
    select upper('korea'); 
    select lower('KOREA');

▷ abs(절대값), pow(제곱), power(제곱), sqrt(루트), concat(값 연결)
    select abs(1), abs(-1), abs(3-10);
    select pow(2,3), power(5,2), sqrt(16);
    select concat('hello',' ','2024','03','13');

▷ concat_ws (괄호안에 내용이 첫번째 매개변수로 이어 붙여짐)  -- 자주 사용 
    select concat_ws('-','2024',3,13,'PM'); 

       -- 2024-3-13-PM으로 출력

 

▷ substr ( 원하는 위치 문자열 출력 )

    select substr('ABCDEFGH', 3),substr('ABCDEFGH',3,2),substr('ABCDEFGH', -4),substr('ABCDEFGH', -4,2); 
        -- 3번째부터 끝까지 CDEFGHC, 3번째 C부터 글자 2개 CD, 뒤에서부터 4글자 EFGH, 뒤에서4번째 E부터 2개 EF

▷  length ( 문자열의 바이트 길이), char_length(문자열의 문자길이)

     select length('ABCDEFGH'),

          -- 문자열의 바이트 길이 / 영어바이트는1임. 8출력
     char_length('ABC');

          -- 문자열의 문자길이  3 출력
     select length('안녕하세요'), char_length('안녕하세요'); 

          -- 한글바이트 15 출력, char 문자열의 길이 5출력
     select char_length('안녕하세요'),character_length('안녕하세요'); 

          -- 위랑 똑같은 결과 똑같은 말임

 ▷ trim (양쪽공백 제거), rtrim (오른쪽 공백제거), ltrim(왼쪽 공백제거)

    select concat('|', ltrim(' hello'), '|'), -- left trim 왼쪽공백제거  

         -- |hello| 출력
    concat('|', rtrim(' hello     '), '|'), -- right trim 오른쪽공백제거 

         -- |hello| 출력
    concat('|', trim('   hello     '), '|'); -- trim 양쪽공백제거 

         -- |hello| 출력 / 공백 길이는 상관 없이 전부 제거

 

▷ lpad(S, N, P) S가 N이 될 때 까지 P를 왼쪽에 이어 붙이는 작업 
    select lpad('ABC',5,'#'); -- ##ABC 출력

▷ rpad(S, N, P) S가 N이 될 때 까지 P를 오른쪽에 이어 붙이는 작업 
    select rpad('ABC',5,'@'); -- ABC@@ 출력

▷ replace (내용,old,new) 내용에 old가 new로 바뀜
     select replace ('버거킹에서 버거킹 햄버거 먹었다','버거킹','맘스터치'); 

        -- 맘스터치에서 맘스터치 햄버거 먹었다 출력

 

▷ instr(S,s) S중 s의 첫 위치 반환하는 함수
     select instr('ABCDE','ABC'), instr('ABCDE','BC'), instr('ABCDE','C');

         -- ABCDE중에서 ABC의 첫위치 1 출력 / ABCDE중 BC의 첫위치2 출력 / ABCDE 중 C의 첫위치 3 출력

 

▷ cast(A as T) : A를 T 자료형으로 변환 즉, 형변환 함수
     select '01' = '1', -- false 0 출력
     cast('01' as decimal) = cast('1' as decimal); 

         -- cast 형변환 ! '01' 문자를 as decimal로 true 1 출력

 

▷ date_format 날짜형식 (포팅)

    select empno, ename, cast(hiredate as char), date_format(hiredate, '%Y-%m') as 입사년월 from emp;

 

▷ format(숫자, 소수점 자리수)

    select stu_dept, format(avg(stu_height),0) from student group by stu_dept;  

         -- 몸무게 소숫점 지우기

▷ convert(A,T) : A를 T 자료형으로 변환
     select '01' = '1', -- false 0 출력
     convert('01', decimal) = convert('1', decimal);

         -- cast보다 conver가 더 많이 사용 됨 true 1 출력

 

▷ 날짜함수 
   select date(now());   -- 연월일
   select time(now());   -- 시분초
   select timestamp(now());   -- 현재 연월일 시분초
   select sysdate();     -- 현재 연월일 시분초 
   select curdate();     -- 연월일

 

▷ adddate(date, interval expr)  (adddate와 date_add는 똑같음 그치만 adddate로 주요 사용 date_add보단)

   select adddate(now(), interval 1 day); 

      -- 오늘 기준으로 다음날 출력 (24시간 후) 
   select adddate(now(), interval 1 month); 

      -- 오늘 기준으로 다음달 출력 (한달 후)
   select date_add(now(), interval 1 month);

       -- 오늘 기준으로 다음달 출력 (한달 후) 

   select date_sub(now(), interval 1 week); 

       -- 오늘 기준으로 일주일 전 출력 

   ▷ 월 0, 화 1, 수 2, 목 3, 금 4, 토 5, 일 6 

   select weekday(curdate()); -- 2출력 오늘은 수요일이기 때문

   select adddate(curdate(), weekday(curdate())); 

        -- 오늘날짜 2024-03-13 수요일 기준으로 +2 하면 2024-03-15 출력

   select adddate(curdate(), weekday(curdate())+1); 

        -- 오늘날짜 기준 수요일 2 더하고 +1 하면 2024-03-16 출력
   select adddate(curdate(), -weekday(curdate())); 

        -- 오늘날짜 기준 수요일 2 빼면 2024-03-11 출력

   select adddate(curdate(), -weekday(curdate())) as monday; 

        -- 뒤에 as는 그냥 이름 정한거 monday로 

 

   select curdate(), curtime(), now(); -- (현재 년월일), (현재 시간분초), (현재 년월일시분초)

   select date_add('2010-12-31 23:59:59', INTERVAL 1 DAY);

        -- 주어진 날짜에 (현재날짜 curdate대신 날짜 직접 적음) 하루 더하기 2011년 1월 1일

 

DateAdd 함수 - Microsoft 지원  - [ DateAdd 함수 참고 링크 ]

DateAdd 함수 - Microsoft 지원

 

 

---

[ GROUP BY 및 HAVING 그리고 집계 함수 ]

▷ max 최대값, min 최솟값 

    select max(enr_grade), min(enr_grade) from enrol;

    select max(stu_weight), min(stu_weight) from student where stu_dept = '기계' ;

 

▷ count ( count는 null 값 포함하지 않고 카운트  )

    select count(*), count(stu_height) from student;  

         -- * 전체 카운트 / stu_height만 카운트

    select count(stu_dept), count(distinct stu_dept) from student; -

         -- stu_dept 카운트 / stu_dept 중복제거 후 카운

 

▷ group by ( select ~ from ~ where ~ group by -> group by는 where보다 뒤에 젤 뒤에 위치 ) 

    select stu_dept, count(*) from student group by stu_dept; 

          -- stu_dept기준으로 그룹핑

    select stu_dept, stu_grade, count(*) from student group by stu_dept, stu_grade;

         -- group by 2개 설정

    select stu_dept, count(*) from student where stu_weight >=50 group by stu_dept;

          -- (group by stu_dept)학과를 기준으로 (where stu_weight >=50)몸무게50이상인 (count(*))학생 수 뽑아내는 함수

    select stu_dept, avg(stu_weight) from student group by stu_dept;

          --  (group by stu_dept)학과를 기준으로 avg(stu_weight)을 구하는함수 (만약몸무게에 null 값이있다면 avg부정확)

    select stu_dept, format(avg(stu_height),0) from student group by stu_dept;

          -- avg 소수점 없애고 싶을때 format 함수 응용 

 

▷ having 

     select stu_grade, format(avg(stu_height),1) from student where stu_dept = '기계' 
     group by stu_grade having (avg(stu_height))>=160;

           -- 학과는 기계학과 이면서, 학년별로 평균키가 160이상인 평큔 키 값 출력

     select stu_dept, max(stu_height) from student 
     group by stu_dept having max(stu_height) >= 175;

           -- 학과별로 그룹핑해서 그 중 키가 최대키가 175이상인 학과와 학과에서 가장 키가 큰 수치를 출력

having 절

---

[ 부질의 ( 서브쿼리 ) ]

▷ select문 안에 ( select문 )

    select stu_height from student where stu_name = '옥성우'; 

         -- 옥성우 학생의 키
    select stu_name from student where stu_height > 
    (select stu_height from student where stu_name = '옥성우');

         -- 옥성우 학생보다 키 큰 학생의 이름 출력    

     select stu_name from student where stu_weight = 
    (select stu_weight from student where stu_name = '박희철') and stu_name <> '박희철' ;

          -- 박희철 학생과 몸무게가 같은 학생의 이름 출력

     select * from student where stu_height > (select avg(stu_height) from student);

          -- 평균 키보다 큰 학생의 정보 출력

     select * from student where stu_height > all(select avg(stu_height) from student group by stu_dept);

          -- 학과별 평균 키보다 큰 학생의 정보 출력

     select * from student where stu_class 
     in(select stu_class from student where stu_dept='컴퓨터정보') and stu_dept <>'컴퓨터정보';

---

[ Join 함수 ]

※ join ( 조인) : 상호 관련성을 갖는 두 개 이상의 테이블로부터 새로운 테이블을 생성하는 데 사용된다.

 

[ 내부 조인 ]

        : 테이블의 공통속성을 출력하는 것 

▷ inner join ( equi join 이퀴 조인 ) ☆★ :  where절에서 = 연산자 사용

      select stu.stu_no, stu_name, stu_dept, enr_grade from student stu, enrol en where stu.stu_no = en.stu_no; 

          -- from student stu, enrol en 별명으로 접근 가능 ( 속성과 달리 테이블명은 as를 쓰지 않음)

      select s.stu_no, s.stu_name from student s, enrol e where s.stu_no = e.stu_no and sub_no = 101;

          -- 과목번호 101번을 수강하고 있는 학생의 학번, 이름 출력

      select stu_name, enr_grade from student s, enrol e where s.stu_no = e.stu_no and enr_grade >= 70;

         -- 점수가 70점 이상인 학생 이름 검색

     select stu_name from student s, enrol e, subject su 
     where su.sub_no=e.sub_no and e.stu_no=s.stu_no and sub_prof='강종영';

         -- 강종영 교수가 강의하는 과목을 수강하는 학생의 이름 검색(equi join) => 테이블조인 3개 필요

 

▷ natural join ( 자연 조인 )

      select stu.stu_no, stu_name, stu_dept, enr_grade from student stu natural join enrol en;  

         -- 스튜던드 필드와 인롤 필드 겹치는거 자연적으로 조인 

      select * from student natural join enrol;

         -- 학생테이블과 수강테이블을 natural join해라

      select sub_name, enro.stu_no, enr_grade from subject natural join enrol; 

        -- 과목 이름과, 학번, 점수를 검색해라 (natural join)

      select stu_name, enr_grade from student natural join enrol where enr_grade <=70;

        -- 점수가 70점 이하인 학생 이름 검색 (natural join)

 

▷ using ( 공통속성 위치  using(공통속성) )

      select stu.stu_no, stu_name, stu_dept, enr_grade from student stu join enrol using(stu_no);

      select sub_name, enrol.stu_no, enr_grade from subject join enrol using(sub_no);

         -- 과목이름과 학번, 점수를 검색해라

      select stu_name, enr_grade from student join enrol using(stu_no) where enr_grade >= 60;

        -- 점수가 60점 이상인 학생 이름 검색 (join using)

 

▷ join on

       select stu.stu_no, stu_name, stu_dept, enr_grade from student stu join enrol en on stu.stu_no = en.stu_no; 

 

[ 외부 조인 ] on 

       : 한쪽에는 데이터가 있고 다른 쪽에는 데이터가 없는 경우 데이터가 있는 쪽

         테이블의 내용을 모두 출력한다. ( 내부조인은 공통성이 없으면 출력되지 않음 )

         공통적이지 않은 과목번호는 합쳐지지 않음 이것을 합치게끔 하는게 외부조인

▷ left outer join : 왼쪽에 있는 테이블의 모든 결과를 가져온 후 오른쪽 테이블의 데이터를 매칭

    select a.*, sub_name from subject b left outer join enrol a on a.sub_no = b.sub_no;

       -- 왼쪽 테이블 기준으로 오른쪽 테이블을 합친다.
    select a.*, sub_name from enrol a left outer join subject b on a.sub_no = b.sub_no;

 

▷ right outer join : 오른쪽에 있는 테이블의 모든 결과를 가져온 후 왼쪽 테이블의 데이터를 매칭

     select a.*, sub_name from enrol a right outer join subject b on a.sub_no = b.sub_no;

        -- 오른쪽 테이블 기준으로 왼쪽 테이블을 합친다.

 

외부 조인

 

 

[ 교차조인 ]

▷cross join

         - 테이블의 모든 행이 각각 한번 씩 조인되어 모든 경우의 수 조합 됨

        select student.*, enrol.* from student cross join enrol;

 

[ 셀프조인 ]

▷self join

   select a.empno as 사원번호, a.ename as 사원이름, b.empno as 상급자사원번호, b.ename as 상급자이름 
   from emp a join emp b on a.mgr=b.empno; 
      -- emp가 smith인 사람의 상급자번호 7902와 상급자이름은 ford 알아내는 작업 

 

 

 

---

[ Table 복사, Insert 삽입, Update 수정, Delete 삭제 복습 ]

-- table 복사 
create table a_enrol as select * from enrol where stu_no < 20150000; 

-- insert 데이터 삽입
insert into a_enrol(sub_no, stu_no, enr_grade) values (108,20151062,92); -- 새로운 행 추가 sub_no에 108, stu_no에 20151062 , enr_grade에 92 추가 됨
insert into a_enrol values (108,20151062,92);  -- ()안에 안 적어도 상관없지만 안 적을때는 반드시 순서와 갯수  맞춰서 값 적어줘야함 즉, 개수와 필드에 맞게 값 넣으면 필드 생략 가능 !!!
insert into a_enrol(sub_no, stu_no) values (110, 20152088); -- 필드 수와 맞지 않게 넣으려면 반드시 () 안에 명시 이경우 enr_grade의 값은 null
insert into a_enrol select * from enrol where stu_no like '201%';

-- update 수정 구문 ( 과목번호가 104번인 성적을 10씩 더함 )
update a_erol set enr_grade=enr_grade+10 where sub_no 
= (select sub_no from subject where sub_name='시스템분석설계');

-- 삭제 ( delete from where )
delete from a_enrol where stu_no = '20131001';
delete from a_enrol; -- 모든 데이터 삭제
select * from a_enrol; -- 출력

-- 학생테이블로부터 학년이 1 또는 2학년인 조건들만 복사하여 student1 테이블을 생성한다.
create table student1 as select * from student where stu_grade='1' or stu_grade='2';

-- 과목테이블을 복사하여 subject1 테이블을 생성한다.
create table subject1 as select * from subject;

-- 수강테이블을 복사하여 enrol1 테이블을 생성한다.
create table subject1 as select * from enrol;

-- 다 한 후 복사된 테이블의 내용을 확인한다.
select * from student1; select * from subject1; select * from enrol1;

-- 학번 20101059, 이름 조병준, 학과 컴퓨터정보, 학년 1, 반B, 키 164, 몸무게 70인남학생이 추가되었다.
insert into student1 values('20102059','조병준','컴퓨터정보','1','B','M','164','70');

-- 학번 20102038, 이름 남지선, 학과 전기전자, 학년 1, 반C, 여학생이 추가되었다.
insert into student1(stu_no, stu_name, stu_dept, stu_grade, stu_gender)
values('20102038','남지선','전기전자','1','C','F');

-- Student1 테이블에 학생 테이블의 3학년 학생들 데이터를 입력하라.
insert into student1 select * from student where stu_grade='3';

-- 20153088학생이 자퇴하였다.
delete from student1 where stu_no=20153088;

-- 과목번호 112, 과목이름 자동화시스템, 교수명 고종민, 학년 3, 학과 기계가 추가되었다.
insert into subject1 values(112,'자동화시스템','고종민',3,'기계');

-- 과목번호 110이 501로 변경되었다.
update subject1 set sub_no='501' where sub_no='110';

 

---

[ select ~ from ; 을 이용한 총 복습 ]

use yujung;

select *from student;
select *from subject;
select *from enrol;

select stu_no, stu_name from student;
select stu_dept from student;

-- 중복제거(distinct)
select distinct stu_dept from student;
select distinct stu_grade, stu_class from student;

-- 원하는 별명(alias)으로 데이터 가져오기
-- 학번, 과목번호, 성적 가져오기
select stu_no, sub_no,enr_grade+10 from enrol;
select stu_no as ID, stu_name as name from student;
-- as 뒤에 원하는 필드명 지정 가능 

-- 두개 이상의 열을 합쳐서 검색
-- 학과, 학생이름 추출 -> 학생테이블로부터
select stu_dept, stu_name from student;
select concat(stu_dept,stu_name) as 학과성명 from student;
select concat(stu_dept,' ',stu_name,'입니다') as 학과성명 from student;

-- where절 사용
select stu_name, stu_dept, stu_grade, stu_class from student where stu_dept = '컴퓨터정보';

-- 두가지 조건이 맞아야 출력 되는 and 연산자
select stu_name, stu_dept, stu_grade, stu_class from student 
where stu_dept = '컴퓨터정보' and stu_grade=2; 

-- 원하는 만큼만 데이터 가져오기
select * from student limit 5;
select * from student;
select * from student limit 1,5; -- 인덱스 1부터 5개

-- 범위조건 
select * from student where stu_weight between 60 and 70;
select * from student where stu_no between '20140001' and '20149999';
-- % : 0개 이상 문자
-- _ : 1개 이상 문자
select stu_no, stu_name, stu_dept from student where stu_name like '김%'; -- 성김씨만추출 김%는뒤에뭐가와도상관x 첫글자가김으로만시작하면됨
select stu_no, stu_name, stu_dept from student where stu_name like '_수%'; -- _수_ 도 무관 왜냐면 수 뒤에 한글자니깐 ! 심수정 출력
select * from student where stu_no like '2014%'; -- 학번이 2014로 시작하는 모든 학번 출력 
select stu_no, stu_name, stu_dept from student where stu_dept in('컴퓨터정보', '기계'); -- in 또는 이라는 뜻 = or
select * from student where stu_height >= 170;

-- 이외
select * from student where not stu_dept = '컴퓨터정보'; -- 이외라는 뜻 컴퓨터정보만빼고
SELECT * FROM student WHERE stu_dept not in('컴퓨터정보'); --  not in도 가능 
SELECT * FROM student WHERE stu_dept <>('컴퓨터정보'); -- <>도 가능

-- null 값 존재 여부
select stu_no, stu_name, stu_height from student where stu_height is null; -- 키값이 비어있는 조건을 찾는 작업
select stu_no, stu_name, stu_height from student where stu_height is not null; -- 키값이 비어있는 조건을 빼고 찾는 작업

-- ifnull/nullif
-- ifnull(수식1, 수식2) : 수식 1이 null이 아니면 수식 1 반환, null이면 수식 2 반환
select ifnull(stu_height,0) from student;

-- nullif(인수1, 인수2) : 두개값 비교해서 값 같으면 null반환, 아니면 인수1 반환
select ifnull(nullif('A','A'),'널값'); -- 널값 출력 ifnull(null,'널값');

-- 순서화(정렬)
select stu_no, stu_name from student order by stu_no;
select stu_no, stu_name from student order by stu_no desc;

-- 별칭이 붙어있는 열을 기준으로 정렬
select stu_no, stu_name, stu_dept, stu_weight-5 
as target from student order by target;

-- 열의 순서번호 이용하여 정렬
select stu_no, stu_name, stu_dept, stu_weight-5 
as target from student order by 3; -- stu_dept기준으로 정렬

select stu_no, stu_name, stu_dept, stu_weight-5 
as target from student order by stu_weight-5; -- 몸무게에서 -5한 값을 기준으로 정렬하겠다

select stu_no, stu_name, stu_dept, stu_weight-5 
as target from student order by stu_dept, target; -- 학과가 먼저 정렬되고 그 이후 target 정렬됨

select stu_no, stu_name, stu_dept, stu_weight-5 
as target from student order by stu_dept desc, target desc; -- 둘다내림차순 하려면 둘다 desc 적어줘야함

-- max,min 
select max(enr_grade), min(enr_grade) from enrol;
select max(stu_weight), min(stu_weight) from student where stu_dept = '기계' ;

-- count (null값은 포함하지 않고 카운트)
select count(*), count(stu_height) from student;
select count(stu_dept), count(distinct stu_dept) from student; -- 중복 제거 카운트

-- group by
select stu_dept, count(*) from student group by stu_dept; -- stu_dept기준으로 그룹핑
select stu_dept, stu_grade, count(*) from student group by stu_dept,stu_grade; -- group by 2개 설정
select stu_dept, count(*) from student where stu_weight >=50 group by stu_dept;
select stu_dept, avg(stu_height) from student group by stu_dept;
select stu_dept, format(avg(stu_height),0) from student group by stu_dept; -- format(숫자, 소수점 자리수)

-- having 절
select stu_grade, format(avg(stu_height),1) from student where stu_dept = '기계' 
group by stu_grade having (avg(stu_height))>=160;
select stu_dept, max(stu_height) from student 
group by stu_dept having max(stu_height) >= 175;

-- 부질의 
select stu_height from student where stu_name = '옥성우'; -- 옥성우 학생의 키
select stu_name from student where stu_height > 
(select stu_height from student where stu_name = '옥성우'); -- 옥성우 학생보다 키 큰 학생의 이름 출력 
select stu_name from student where stu_weight = 
(select stu_weight from student where stu_name = '박희철') and stu_name <> '박희철'; -- 박희철 학생과 몸무게가 같은 학생의 이름 출력
select * from student where stu_height > (select avg(stu_height) from student); -- 평균 키보다 큰 학생의 정보 출력
select * from student where stu_height > all(select avg(stu_height) from student group by stu_dept);
select * from student where stu_class 
in(select stu_class from student where stu_dept='컴퓨터정보') and stu_dept <>'컴퓨터정보';

 

[ 사칙연산 복습 ]

-- 사칙연산
select 1+2;  -- 3출력
select 3*(2+4)/2, 'hi';
select 10%3;

-- 문자열에 사칙연산을 가하면 0으로 인식
select 'AB' + 3;  -- 3출력
select 'AB' * 3;  -- 0출력

-- true는 1 false는 0
select true, false;  -- true는 1  false는 0  출력
select true is true; -- true니깐 1출력 
select 2+4=6 or 2*4=8; -- or니깐 둘중 하나만 맞아도 참. 즉, true 1 출력
select 'B'='b';  -- MySQL의 기본 사칙 연산자는 대소문자 구분하지 않는다. 즉, true 1 출력
select 10 between 15 and 20; -- flase 0 출력
select 'apple' not between 'banana' and 'computer'; -- ture 1 출력
select 1+2 in(2,3,4);
select 'hi' in (1,true,'hi'); -- hi가 in안에 들어있냐 -> true 1 출력

-- round (반올림)
select round(345.678),round(345.678,0),round(345.678,1),round(345.678,2),
round(345.678,-1); -- 346, 346, 345.7, 345.68, 350 출력

--  truncate(버림,소숫점자리)
select truncate(1234.56789, 1), truncate(1234.56789, 2), truncate(1234.56789, -1);
-- 1234.5, 1234.56, 1230 출력
select truncate(1234.56789, -2), truncate(1234.56789, -3); -- 1200, 1000 출력

-- upper (대문자), lower (소문자)
select upper('korea'); 
select lower('KOREA');

-- abs(절대값), pow(제곱), power(제곱), sqrt(루트), concat(값 연결)
select abs(1), abs(-1), abs(3-10);
select pow(2,3), power(5,2), sqrt(16);
select concat('hello',' ','2024','03','13');

-- concat_ws (괄호안에 내용이 첫번째 매개변수로 이어 붙여짐) 
select concat_ws('-','2024',3,13,'PM'); -- 2024-3-13-PM으로 출력

select substr('ABCDEFGH', 3),substr('ABCDEFGH',3,2),substr('ABCDEFGH', -4),substr('ABCDEFGH', -4,2); 
-- 3번째부터 끝까지 CDEFGHC, 3번째 C부터 글자 2개 CD, 뒤에서부터 4글자 EFGH, 뒤에서4번째 E부터 2개 EF

select length('ABCDEFGH'), -- 문자열의 바이트 길이 / 영어바이트는1임. 8출력
char_length('ABC');  -- 문자열의 문자길이  3 출력

select length('안녕하세요'), char_length('안녕하세요'); -- 한글바이트 15 출력, char 문자열의 길이 5출력
select char_length('안녕하세요'),character_length('안녕하세요'); -- 똑같은 결과 똑같은 말임

select concat('|', ltrim(' hello'), '|'), -- left trim 왼쪽공백제거  -> |hello| 출력
concat('|', rtrim(' hello     '), '|'), -- right trim 오른쪽공백제거 -> |hello| 출력
concat('|', trim('   hello     '), '|'); -- trim 양쪽공백제거 -> |hello| 출력 / 공백 길이는 상관 없이 전부 제거

-- lpad(S, N, P) S가 N이 될 때 까지 P를 왼쪽에 이어 붙이는 작업 
select lpad('ABC',5,'#'); -- ##ABC 출력

-- rpad(S, N, P) S가 N이 될 때 까지 P를 오른쪽에 이어 붙이는 작업 
select rpad('ABC',5,'@'); -- ABC@@ 출력

-- replace (내용,old,new) 내용에 old가 new로 바뀜
select replace ('버거킹에서 버거킹 햄버거 먹었다','버거킹','맘스터치'); -- 맘스터치에서 맘스터치 햄버거 먹었다 출력

-- instr(S,s) S중 s의 첫 위치 반환하는 함수
select instr('ABCDE','ABC'), instr('ABCDE','BC'); -- ABCDE중에서 ABC의 첫위치 1 출력 / ABCDE중 BC의 첫위치2 출력

-- cast(A as T) : A를 T 자료형으로 변환 즉, 형변환 함수
select '01' = '1', -- false 0 출력
cast('01' as decimal) = cast('1' as decimal); -- cast 형변환 ! '01' 문자를 as decimal로 true 1 출력

-- convert(A,T) : A를 T 자료형으로 변환
select '01' = '1', -- false 0 출력
convert('01', decimal) = convert('1', decimal); -- cast보다 conver가 더 많이 사용 됨 true 1 출력

-- 학생들의 성별을 소문자로 검색
select distinct lower(stu_gender) from student;

-- 학생들의 학과, 이름을 연결하여 검색
select concat(stu_dept,' ',stu_name) from student;

--  substr 학생들의 이름과 이름의 첫 2글자 검색 
select substr(stu_name,1,2) from student; -- 1 첫번째 글짜부터 2 두개글까까지 

-- 학생들의 이름, 학과, 그리고 학과의 두 번째부터 1자리 검색
select stu_name, stu_dept, substr(stu_dept,2,1) from student;

 

[ 날짜 함수 복습  ]

-- 날짜함수
select date(now()); -- 연월일
select time(now()); -- 시분초
select timestamp(now()); -- 현재 연월일 시분초
select sysdate(); -- 현재 연월일 시분초 
select curdate(); -- 연월일

-- adddate(date, interval expr)
select adddate(now(), interval 1 day); -- 오늘 기준으로 다음날 출력 (24시간 후) 
select adddate(now(), interval 1 month); -- 오늘 기준으로 다음달 출력 (한달 후)
select date_add(now(), interval 1 month);-- 오늘 기준으로 다음달 출력 (한달 후) 위에꺼랑 똑같음 뭘 사용하던 상관 x

select date_sub(now(), interval 1 week); -- 오늘 기준으로 일주일 전 출력 
select adddate(curdate(), -weekday(curdate())) as monday; -- 이번주 월요일 (현재날짜를 기준으로 weekday요일을 뽑기 monday)
select adddate(curdate(), weekday(curdate())) as monday; -- 다음주 월요일  현재 날짜(curdate())에서 현재 요일(weekday(curdate()))을 더한 값을 월요일의 날짜로 계산하는 것입니다.

select date_add('2010-12-31 23:59:59', interval 1 day); -- 주어진 날짜에(현재날짜 curdate대신 날짜 직접 적음)하루 더하기 2011년 1월 1일

select curdate(), curtime(), now(); -- (현재 년월일), (현재 시간분초), (현재 년월일시분초)

-- 날짜함수
select date(now()); -- 연월일
select time(now()); -- 시분초
select timestamp(now());-- 현재 연월일 시분초
select sysdate(); -- 현재 연월일 시분초
select curdate(); -- 연월일

select adddate(now(), interval 1 day); -- 오늘기준으로 다음 날 출력
select date_add(now(), interval 1 month);-- 오늘기준으로 다음 달 출력
select date_sub(now(), interval 1 week); -- 오늘 기준으로 일주 전 출력

SELECT DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 DAY); -- 주어진 날짜에 (현재날짜 curdate대신 날짜 직접 적음) 하루 더하기 2011년 1월 1일
select curdate(), curtime(), now(); -- (현재 년월일), (현재 시간분초), (현재 년월일시분초)

-- 월 0, 화 1, 수 2, 목 3, 금 4, 토 5, 일 6
-- adddate (date,interval) :
select weekday(curdate()); -- 2출력 오늘은 수요일이기 때문
select adddate(curdate(), -weekday(curdate())); -- 2024-03-13 수요일 오늘날짜 기준으로 수요일 2 빼면 2024-03-11 출력
select adddate(curdate(), weekday(curdate())+1); -- 2024-03-13 수요일 오늘날짜 기준으로 수요일 2 더하고 +1 하면 2024-03-16 출력
select adddate(curdate(), weekday(curdate())); -- 오늘날짜 수요일 기준으로 +2 2024-03-15 출력
select adddate(curdate(), -weekday(curdate())) as monday;  -as는 그냥 monday이름 정해준것
select adddate(curdate(), weekday(curdate())) as monday;

 

[ Join 복습 ]

-- inner join (equi join)
-- from student stu, enrol en 별명으로 접근 가능
select stu.stu_no, stu_name, stu_dept, enr_grade 
  from student stu, enrol en where stu.stu_no = en.stu_no; 
select s.stu_no, s.stu_name from student s, enrol e 
  where s.stu_no=e.stu_no and sub_no=101; -- 과목번호 101번을 수강하고 있는 학생의 학번, 이름 출력
select stu_name, enr_grade from student s, enrol e 
  where s.stu_no=e.stu_no and enr_grade >= 70; -- 점수가 70점 이상인 학생 이름 검색 (equi join)
select stu_name from student s, enrol e, subject su 
  where su.sub_no=e.sub_no and e.stu_no=s.stu_no and sub_prof='강종영'; -- 강종영 교수가 강의하는 과목을 수강하는 학생의 이름 검색(equi join) 테이블조인 3개 필요

-- natural join (자연 조인)
select stu.stu_no, stu_name, stu_dept, enr_grade 
   from student stu natural join enrol en;  -- 스튜던드 필드와 인롤 필드 겹치는거 자연적으로 조인 
select * from student natural join enrol; -- 학생테이블과 수강테이블을 natural join해라
select sub_name, enro.stu_no, enr_grade 
   from subject natural join enrol; -- 과목 이름과, 학번, 점수를 검색해라 (natural join)
select stu_name, enr_grade from student 
   natural join enrol where enr_grade <=70; -- 점수가 70점 이하인 학생 이름 검색 (natural join)

-- using
select stu.stu_no, stu_name, stu_dept, enr_grade 
  from student stu join enrol using(stu_no);
select sub_name, enrol.stu_no, enr_grade 
  from subject join enrol using(sub_no); -- 과목이름과 학번, 점수를 검색해라
select stu_name, enr_grade 
  from student join enrol using(stu_no) where enr_grade >= 60; -- 점수가 60점 이상인 학생 이름 검색 (join using)

-- join on
select stu.stu_no, stu_name, stu_dept, enr_grade 
from student stu join enrol en on stu.stu_no = en.stu_no;  

-- cross join (교차조인) 
-- 테이블의 모든 행이 각각 한번 씩 조인되어 모든 경우의 수 조합 됨
select student.*, enrol.* from student cross join enrol;

-- 외부조인 (outer join)
select * from student natural join enrol; -- 공통적이지 않은 과목번호는 합쳐지지 않음 이것을 합치게끔 하는게 외부조인
select a.*, sub_name from enrol a right outer join subject b on a.sub_no = b.sub_no;
  -- 오른쪽 테이블 기준으로 왼쪽 테이블을 합친다.
select a.*, sub_name from subject b left outer join enrol a on a.sub_no = b.sub_no;
  -- 왼쪽 테이블 기준으로 오른쪽 테이블을 합친다.
select a.*, sub_name from enrol a left outer join subject b on a.sub_no = b.sub_no;

-- self join (셀프조인)
select a.empno as 사원번호, a.ename as 사원이름, b.empno as 상급자사원번호, b.ename as 상급자이름 
  from emp a join emp b on a.mgr=b.empno; 
-- emp가 smith인 사람의 상급자번호 7902와 상급자이름은 ford 알아내는 작업

 

 

---

[ 문제풀이 1 - student, subject, enrol 테이블 표를 활용한 ]

create table student(
stu_no char(9),
stu_name varchar(12),
stu_dept varchar(20),
stu_grade int(1),
stu_class char(1),
stu_gender char(1),
stu_height decimal(5,2),
stu_weight decimal(5,2),
constraint p_stu_no primary key(stu_no));
desc student;

create table subject(
sub_no char(3),
sub_name varchar(30),
sub_prof varchar(12),
sub_grade int(1),
sub_dept varchar(20),
constraint p_sub_no primary key(sub_no));

desc subject;

create table enrol(
sub_no char(3),
stu_no char(9),
enr_grade int(3),
constraint p_course primary key(sub_no,stu_no));

desc enrol;

insert into student values(20153075,'옥한빛','기계',1,'C','M',177,80);
insert into student values(20153088,'이태연','기계',1,'C','F',162,50);
insert into student values(20143054,'유가인','기계',2,'C','F',154,47);
insert into student values(20152088,'조민우','전기전자',1,'C','M',188,90);
insert into student values(20142021,'심수정','전기전자',2,'A','F',167,45);
insert into student values(20132003,'박희철','전기전자',3,'B','M',null,63);
insert into student values(20151062,'김인중','컴퓨터정보',1,'B','M',166,67);
insert into student values(20141007,'진현무','컴퓨터정보',2,'A','M',174,64);
insert into student values(20131001,'김종헌','컴퓨터정보',3,'C','M',null,72);
insert into student values(20131025,'옥성우','컴퓨터정보',3,'A','F',172,63);

insert into enrol values('101','20131001',80);
insert into enrol values('104','20131001',56);
insert into enrol values('106','20132003',72);
insert into enrol values('103','20152088',45);
insert into enrol values('101','20131025',65);
insert into enrol values('104','20131002',65);
insert into enrol values('108','20151062',81);
insert into enrol values('107','20143054',41);
insert into enrol values('102','20153075',66);
insert into enrol values('105','20153075',56);
insert into enrol values('102','20153088',61);
insert into enrol values('105','20153088',78);

insert into subject values('111','데이터베이스','이재영',2,'컴퓨터정보');
insert into subject values('110','자동제어','정순정',2,'전기전자');
insert into subject values('109','자동화설계','박민영',3,'기계');
insert into subject values('101','컴퓨터개론','강종영',3,'컴퓨터정보');
insert into subject values('102','기계공작법','김태영',1,'기계');
insert into subject values('103','기초전자실험','김유석',1,'전기전자');
insert into subject values('104','시스템분석설계','강석현',3,'컴퓨터정보');
insert into subject values('105','기계요소설계','김명성',1,'기계');
insert into subject values('106','전자회로시험','최영민',3,'전기전자');
insert into subject values('107','CAD응용실습','구봉규',2,'기계');
insert into subject values('108','소프트웨어공학','권민성',1,'컴퓨터정보');

-- 6. 과목들의 과목번호와 과목이름을 검색하라.
select sub_no, sub_name from subject;

-- 7. 학생들의 학번과 이름, 성별을 검색하라.
select stu_no, stu_name, stu_gender from student;

-- 8. 학생들의 모든 정보를 검색하라.
select * from student;

-- 9. 학생들의 학번과 이름, 학년, 반을 검색하라.
select stu_no, stu_name, stu_grade, stu_class from student;

-- 10. 과목들의 과목이름과 교수이름을 검색하라.
select sub_name, sub_prof from subject;

-- 11. 과목번호, 학번, 점수를 검색하라.
select sub_no, stu_no, enr_grade from enrol;

-- 12. 학생들의 체중과 신장을 학번, 이름과 함께 검색하라.
select concat(stu_no, stu_name, stu_height, stu_weight) as 학생정보 from student;

-- 13. 학생들의 학과 중복을 제거하고 검색하라.
select distinct stu_dept from student;

-- 14. 학생들의 성별 중복을 제거하고 검색하라.
select distinct stu_gender from student;

-- 15. 학생들의 학년 중복을 제거하고 검색하라.
select distinct stu_grade from student;

-- 16. 학생들의 학과, 학년 중복을 제거하고 검색하라. 
select distinct stu_dept, stu_grade from student;

-- 17. 학생들의 학과, 반 중복을 제거하고 검색하라.
select distinct stu_dept, stu_class from student;

-- 18. 학생들의 체중을 5만큼 증가시킨 값을 검색하라
select stu_weight+5 from student;

-- 19. 학생들의 체중을 5만큼 감소시킨 값을 검색하라. 
select stu_weight-5 from student;

-- 20. 학생들의 학번과 이름을 별칭 "학번", "이름"으로 부여하여 검색하라.
select stu_no as 학번, stu_name as 이름 from student;

-- 21. 수강(enrol)테이블의 모든 정보를 검색하라. 
select * from enrol;

-- 22. 학생 테이블에서 학과명과 이름을 합쳐서 검색하라.
select concat(stu_dept, stu_name) from student;

-- 23. 학생의 이름과 학과를 '컴퓨터정보과 옥한빛입니다'식으로 검색하라.
select concat(stu_dept, '과 ', stu_name,'입니다') from student;

-- 24. 컴퓨터정보 학생들을 검색하라.
select * from student where stu_dept = '컴퓨터정보';

-- 25. 기계과 학생들을 검색하라. 
select * from student where stu_dept = '기계';

-- 26. 전기전자과 학생들을 검색하라.
select * from student where stu_dept ='전기전자';

-- 27. 신장이 170이상인 학생들을 검색하라.
select * from student where stu_height >= 170;

-- 28. 체중이 65이상인 학생들을 검색하라.
select * from student where stu_weight >= 65;

-- 29. 기계 학생들의 학번, 이름을 검색하라.
select stu_no, stu_name from student where stu_dept = '기계';

-- 30. 컴퓨터정보 학생들의 학번, 이름을 검색하라.
select stu_no, stu_name from student where stu_dept = '컴퓨터정보';

-- 31. 남학생의 이름을 검색하라.
select stu_name from student where stu_gender ='M';

-- 32. 전기전자과 이외 학생들의 모든 정보를 검색하라.
select * from student where not stu_dept = '전기전자';

-- 33. 점수가 80점 이상인 학생들의 학번을 검색하라.
select * from enrol where enr_grade >= 80;

-- 34. '김인중'학생의 모든 정보를 검색하라.
select * from student where stu_name = '김인중';

-- 35. '기계'과 이고 2학년인 학생들의 모든 정보를 검색하라.
select * from student where stu_dept ='기계' and stu_grade = 2;

-- 36. 성별이 여학생이며, 체중이 60이하인 학생을 모두 검색하라.
select *from student where stu_gender = 'f' and stu_weight<=60;

-- 37.'컴퓨터정보'외에 1학년 학생들의 이름을 검색하라.
select stu_name from student where stu_dept<>'컴퓨터정보' and stu_grade=1;

-- 38. 학과 중 '기계'이외의 학과를 검색하라.
select sub_dept from subject where not sub_dept='기계';

-- 39. 컴퓨터정보과 2학년 A반 학생의 이름을 검색하라.
select stu_name from student where stu_dept='컴퓨터정보' and stu_grade=2 and stu_class ='A';

-- 40. 신장이 160이상이며 170이하인 학생의 학번과 이름을 검색하라
select stu_no, stu_name from student where stu_height between 160 and 170;

-- 56번 학생들의 이름과 이름의 길이를 검색하라.
select stu_name , char_length(stu_name) from student;

-- 57번 학생들의 학과와 학과명의 길이를 검색하라.
select stu_dept , char_length(stu_dept) from student;

-- 58번 학생들의 이름에 '김'이 몇번째 있는지 검색하라.
select instr(stu_name,'김') from student;

-- 59번 학생의 이름을 15자리로 하고, 뒤에 '&'를 채워 검색하라.
select rpad(stu_name,15,'&') from student;

-- 60번 학생의 학과를 20자리로 하고, 앞에 '%'를 채워 검색하라.
select lpad(stu_dept,20,'%') from student;

-- 61번 학생의 학번, 이름, 신장을 검색하라. (신장은 첫번째 자리에서 반올림함)
select stu_no, stu_name, round(stu_height,0) from student;

-- 62번 학생의 학번, 이름, 신장을 검색하라. (신장은 두번째 자리에서 절삭함)
select stu_no, stu_name, truncate(stu_height,-2) from student;

-- 63번 체중을 30으로 나눈 나머지를 검색하라.
select (stu_weight%30) from student;

-- 64번 신장열의 값이 널인 학생의 경우 '미기록'으로 검색하라.
select ifnull(stu_height, '미기록') from student;

-- 68번 학생 중 키가 가장 큰 학생의 신장을 검색하라.
select max(stu_height) from student;

-- 69번 학생의 이름 중 MAX와 MIN값을 검색하라.
select max(stu_name), min(stu_name) from student;

-- 70번 학생테이블의 레코드(튜플)수를 검색하라.
select count(*) from student;

-- 71번 학과별 학생들의 인원수를 검색하라.
select stu_dept, count(*) from student group by (stu_dept);

-- 72번 학과별 학생들의 인원수를 인원수가 많은 순으로 검색하라.
SELECT stu_dept, count(*) FROM student GROUP BY stu_dept ORDER BY stu_dept DESC;

-- 73번 학년별 학생들의 인원수를 검색하라.
select stu_grade, count(*) from student group by stu_grade;

-- 74번 학과별 학년별 학생들의 평균 체중을 검색하라.
SELECT stu_dept, stu_grade, format(avg(stu_weight),1) FROM student GROUP BY stu_dept, stu_grade;

-- 75번 학과별 학년별 학생들의 학번의 MAX와 MIN값을 검색하라.
select stu_dept, stu_grade,max(stu_no), min(stu_no) from student group by stu_dept, stu_grade;

 

[ 심화 - 문제풀이 ]

-- 김종헌 학생의 평균 점수보다 높은 점수를 가진 학생의 학번과 이름을 검색해라.
SELECT s.stu_no, stu_name FROM student s, enrol e WHERE s.stu_no = e.stu_no
and enr_grade > ( SELECT avg(enr_grade) FROM enrol e GROUP BY stu_name = '김종헌');

-- 김인중 학생의 평균 점수보다 낮은 점수를 가진 학생의 학번과 이름을 검색해라.
SELECT s.stu_no, stu_name FROM student s, enrol e WHERE s.stu_no = e.stu_no
and enr_grade < ( SELECT avg(enr_grade) FROM enrol e GROUP BY stu_name = '김인중');

-- 전체 학생의 평균 점수보다 높은 점수를 가진 학생의 학번, 이름, 과목이름, 점수를 검색해라.
SELECT s.stu_no, stu_name, sub_name, enr_grade FROM student s, enrol e, subject su
WHERE s.stu_no = e.stu_no and e.sub_no = su.sub_no and enr_grade > 
( SELECT avg(enr_grade) FROM enrol);

-- 점수가 각 학과 학생들의 평균 점수보다 높은 학생의 학번을 검색해라.
SELECT s.stu_no FROM student s, enrol e WHERE s.stu_no = e.stu_no
and enr_grade > (SELECT avg(enr_grade) FROM enrol GROUP BY stu_dept);

-- 기계과의 최고 점수 학생보다 최고 점수가 높은 학과의 학과명과 점수를 검색해라.
SELECT stu_dept, max(enr_grade) FROM student s, enrol e WHERE s.stu_no = e.stu_no
GROUP BY stu_dept HAVING max(enr_grade) > (SELECT max(enr_grade) FROM enrol
GROUP BY stu_dept HAVING stu_dept = '기계');

-- 컴퓨터정보과 학생들의 평균 점수를 구해 학생들의 학번과 이름 평균 점수를 성적 순으로 검색해라.
SELECT s.stu_no, stu_name, avg(enr_grade) FROM student s, enrol e WHERE s.stu_no = e.stu_no
GROUP BY s.stu_no ORDER BY avg(enr_grade) DESC;

-- 시스템분석설계 과목을 수강한 학생들의 학번, 이름, 점수를 성적 순으로 검색해라.
SELECT s.stu_no, stu_name, enr_grade FROM student s, enrol e, subject su
WHERE s.stu_no = e.stu_no and e.sub_no = su.sub_no
and sub_name = '시스템분석설계' ORDER BY enr_grade DESC;

-- 2과목 이상 수강한 학생들의 학번, 이름, 수강과목 수를 수강과목이 많은 순으로 검색해라.
SELECT s.stu_no, stu_name, count(e.stu_no) as 수강과목수 FROM student s, enrol e
WHERE s.stu_no = e.stu_no GROUP BY e.stu_no ORDER BY 수강과목수 DESC;

-- 1과목을 수강한 학생들의 학번, 이름을 학과별 학번 순으로 검색해라.
SELECT e.stu_no, stu_name FROM student s, enrol e WHERE s.stu_no = e.stu_no
GROUP BY e.stu_no HAVING count(e.stu_no) = 1 ORDER BY s.stu_no;

-- 컴퓨터개론과 시스템분석설계 과목을 수강하는 학생의 학번, 이름을 학번순으로 검색해라.
SELECT s.stu_no, stu_name FROM student s, enrol e, subject su
WHERE s.stu_no = e.stu_no and e.sub_no = su.sub_no
and sub_name = '컴퓨터개론' and sub_name = '시스템분석설계' ORDER BY s.stu_no;

 

 

[ 문제 풀이 2 - emp dept salgrade 테이블 표를 활용한 ] 

use yujung;

create table emp(
empno int(4),
ename varchar(10),
job varchar(9),
mgr int(4),
foreign key(mgr) references emp(empno),
hiredate date,
sal decimal(7,2),
comm decimal(7,2),
deptno int(2),
constraint primary key pk_emp (empno));

create table dept(
deptno int(2),
dname varchar(14),
loc varchar(13),
constraint primary key pk_dept(deptno));

create table salgrade(
grade decimal(7,2),
losal decimal(7,2),
hisal decimal(7,2));

insert into emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
insert into emp values(7566,'JONES','MANAGER',7839,'1981-4-2',2975,NULL,20);
insert into emp values(7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,NULL,30);
insert into emp values(7782,'CLARK','MANAGER',7839,'1981-6-9',2450,NULL,10);
insert into emp values(7788,'SCOTT','ANALYST',7566,'1987-7-13',3000,NULL,20);
insert into emp values(7902,'FORD','ANALYST',7566,'1981-12-3',3000,NULL,20);
insert into emp values(7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30);
insert into emp values(7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500,30);
insert into emp values(7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,30);
insert into emp values(7844,'TURNER','SALESMAN',7698,'1981-9-8',1500,0,30);
insert into emp values(7900,'JAMES','CLERK',7698,'1981-12-3',950,NULL,30);
insert into emp values(7934,'MILLER','CLERK',7782,'1982-1-23',1300,NULL,10);
insert into emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
insert into emp values(7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20);


insert into dept values(10,'ACCOUNTING','NEW YORK');
insert into dept values(20,'RESEARCH','DALLAS');
insert into dept values(30,'SALES','CHICAGO');
insert into dept values(40,'OPERATIONS','BOSTON');

insert into salgrade values(1,700,1200);
insert into salgrade values(2,1201,1400);
insert into salgrade values(3,1401,2000);
insert into salgrade values(4,2001,3000);
insert into salgrade values(5,3001,9999);

use yujung;

-- 1번 ⦁ 사원 테이블의 구조를 검색하라.
select * from emp; 

-- 2번 ⦁ 부서 테이블의 구조를 검색하라.
select * from dept; 

-- 3번 ⦁ 급여 테이블의 구조를 검색하라.
select * from salgrade; 

-- 4번 ⦁ 사원들의 사원번호, 사원이름, 사원직무를 검색하라.
select empno, ename, job from emp;

-- 5번 ⦁ 부서의 부서번호와 부서이름을 검색하라.
select deptno, dname from dept;

-- 6번 ⦁ 부서의 부서이름과 지역을 검색하라.
select dname, loc from dept;

-- 7번 ⦁ 사원들의 급여와 커미션을 검색하라.
select sal, comm from emp;

-- 8번 ⦁ 사원들의 입사일 중복을 제거하고 검색하라.
select distinct hiredate from emp;

-- 9번 ⦁ 사원들의 상급자사원번호(mgr)중복을 제거하고 검색하라.
select distinct mgr from emp;

-- 10번 ⦁ 사원들의 부서번호 중복을 제거하고 검색하라.
select distinct deptno from emp;

-- 11번 ⦁ 사원들의 이름과 6개월 급여의 합을 검색하라.

-- 12번 ⦁ 사원들의 6개월 커미션(comm)의 합을 검색하라.

-- 13번 ⦁ 사원이름을 ‘name’으로 사원의 급여를 ‘salary’로 열의 이름을 부여하여 검색하라.
select ename as 사원이름, sal as 사원의급여 from emp;

-- 14번 ⦁ 사원의 사원번호, 사원이름, 입사일, 부서번호를 한글로 바꾸어 검색하라.
select empno as 사원번호, ename as 사원이름, hiredate as 입사일, deptno as 부서번호 from emp;

-- 15번 ⦁ 부서번호, 부서이름, 지역을 한글 제목으로 검색하라.
select deptno as 부서번호, dname as 부서이름, loc as 지역 from dept;

-- 16번 ⦁ 사원의 사원직무와 사원이름을 합쳐서 검색하라.
select concat(job, ' ' ,ename) from emp;

-- 17번 ⦁ 입사일(hiredate) 사원이름을 ’80-12-17에 입사한 SMITH입니다’ 식으로 검색하라.
select concat(hiredate,'에 입사한 ',ename,'입니다') from emp;

-- 18번 ⦁ 10번 부서에 근무하는 사원이름을 검색하라.
select dname from dept where deptno = '10' ;

-- 19번 ⦁ 급여가 2000 이상인 사원들의 사원번호, 사원이름을 검색하라.
select empno,ename from emp where sal >= 2000 ;

-- 20번 ⦁ 사원직무가 “CLERK”인 사원들의 사원번호, 사원이름을 검색하라.
select empno,ename from emp where job='clerk';

-- 21번 ⦁ 1980년 12월 17일에 입사한 사원이름을 검색하라
select ename from emp where hiredate = '1980-12-17';

-- 22번 ⦁ 부서번호 30이외의 부서이름과 지역을 검색하라.
select dname, loc from dept where not deptno = '30';

-- 23번 ⦁ 급여등급이 5인 급여의 상위급여와 하위급여를 검색하라.
select losal, hisal from salgrade where grade ='5'; 

-- 24번 ⦁ ‘WARD’사원의 모든 정보를 검색하라. 
select * from emp where ename = 'ward';

-- 25번 ⦁ 20부터 외에 근무하는 MANAGER의 사원이름을 검색하라.
select ename from emp where not deptno = '20' and job = 'manager'; 

-- 26번 ⦁ BOSTON이외 지역에 있는 부서이름을 검색하라.
select dname from dept where not loc = 'boston';

-- 27번 ⦁ SALEMAN이며 급여가 1500이상인 사원이름을 검색하라.
select ename from emp where job = 'salesman' and sal >= 1500;

-- 28번 ⦁ 급여가 1000이상이며, 2500이하인 사원의 사원번호, 사원이름, 급여를 검색하라.
select empno, ename, sal from emp where sal between 1000 and 2500 ;

-- 29번 ⦁ 사원번호가 75XX인 사원의 사원번호, 사원이름, 부서번호를 검색하라.
select empno, ename, deptno from emp where empno like '75%';

-- 30번 ⦁ 근무하는 사원들의 사부서번호가 10 또는 30에 원이름과 급여를 검색하라.
select ename, sal from emp where deptno ='10' or deptno= '30';

-- 31번 ⦁ 상급자사원번호가 76으로 시작하는 사원들의 사원이름을 검색하라.
select ename from emp where mgr like '76%';

-- 32번 ⦁ 사원번호가 79로 시작하는 사원들의 사원이름, 급여, 커미션을 검색하라.
select ename,sal,comm from emp where empno like '79%';

-- 33번 ⦁ 1981년 2월에 입사한 사원의 사원번호, 사원이름, 부서번호를 검색하라.
select empno, ename, deptno from emp where hiredate like '1981-02%';

-- 34번 ⦁ 사원이름 중간에 ‘A’가 들어있는 사원의 사원번호와 사원이름을 검색하라.
select empno, ename from emp where ename like '_%A%_'; -- 사원이름 중간에 ‘A’가 들어있는 사원의 사원번호와 사원이름을 검색하라.

-- 35번 ⦁ 상급자사원번호가 NULL인 사원의 사원번호와 사원이름을 검색하라.
select empno, ename from emp where mgr is null; 

-- 36번 ⦁ 상급자사원번호가 NULL이 아닌 사원의 사원번호, 이름, 상급자사원번호를 검색하라.
select empno, ename, mgr from emp where mgr is not null;

-- 37번 ⦁ 사원번호가 7902 또는 7781인 사원이름을 검색하라.
select ename from emp where empno = '7902' or empno = '7781';

-- 11번 FORD 사원과 같은 급여를 받는 사원의 사원번호를검색하라. (부질의)
select empno from emp where sal = ( select sal from emp where ename = 'ford' );

-- 13번 SCOTT 사원보다 많은 급여를 받는 사원 정보를검색하라. (부질의)
select * from emp where sal > (select sal from emp where ename = 'scott');

-- 14번 ALLEN 사원보다 적은 급여를 받는 사원 정보를검색하라. (부질의)
select * from emp where sal < (select sal from emp where ename = 'allen');

-- 15번 전체 사원의 평균 급여보다 급여가 많은 사원 정보를 검색하라. (부질의)
select * from emp where sal > (select avg(sal) from emp );

-- 16번 CHICAGO 지역에 위치하는 부서에 근무하는사원 정보를 검색하라. (부질의)
select * from emp where deptno = (select deptno from dept where loc = 'chicago');

-- 1. 상급자사원번호가 7698 또는 7839인 사원의 사원번호와 사원이름을 검색하라. 
select empno, ename from emp where mgr = '7698' or mgr = '7839';

-- 2. 사원직무가 ‘MANAGER’ 또는 ‘SALESMAN’인 사원의 사원번호, 사원이름, 부서번호를 검색하라. 
select empno, ename, deptno from emp where job='manager' or job='saleman';

-- 3. 사원들의 사원번호와 사원이름을 사원번호 순으로 검색하라. 
select empno, ename from emp order by empno;

-- 4. 사원들의 사원번호와 사원이름을 부서번호별 이름순으로 검색하라. 
select empno, ename from emp order by deptno, ename;

-- 5. 사원들의 정보를 부서별 급여가 많은 순으로 검색하라. 
select * from emp order by deptno, sal desc;

-- 6. 사원들의 정보를 사원직무별 급여 순으로 검색하라. 
select * from emp order by job, sal;

-- 7. 사원들의 정보를 부서번호별, 사원직무별, 급여 순으로 검색하라. 
select * from emp order by depno, job, sal;

-- 8. 사원들의 사원이름을 소문자로 검색하라. 
select lower(ename) from emp;

-- 9. 사원들의 사원이름, 사원직무를 소문자로 검색하라. 
select lower(ename), lower(job) from emp;

-- 10. 사원들의 사원이름을 대문자로 검색하라. 
select upper(ename) from emp;

-- 11. 사원들의 사원이름, 사원직무를 대문자로 검색하라. 
select upper(ename), upper(job) from emp;

-- 12. 사원들의 사원이름을 첫자만 대문자로 검색하라. xx
-- 13. 사원들의 사원이름과 사원직무를 첫 자만 대문자로 검색하라. xx

-- 14. 사원들의 사원이름과 사원직무를 연결하여 검색하라.(concat이용) 
select concat(ename,' ',job) from emp;

-- 15. 사원들의 사원이름과 사원이름의 첫 두글자를 검색하라. 
select ename, substr(ename,1,2) from emp;

-- 16. 사원들의 사원이름, 사원직무 그리고 사원직무의 두번째부터 세글자를 검색하라. 
select ename, job, substr(job,2,3) from emp;

-- 17. 사원들의 사원이름과 사원이름의 길이를 검색하라. 
select ename, char_length(ename) from emp;

-- 18. 사원들의 사원이름과 사원직무의 자리수를 검색하라. 
select ename, job, char_length(job) from emp;

-- 19. 사원들의 사원이름에 ‘A’가 몇번 째 위치에 있는지 검색하라. 
select ename ,instr(ename,'A') from emp ;

-- 20. 사원직무에 ‘A’가 몇번 째 위치에 있는지 검색하라. 
select job, instr(job, 'A') from emp;


---------------------JOIN 응용----------------------------

-- ADAMS 사원이 근무중인 부서이름 검색해라 (equi join)
select dname from dept d, emp e where d.deptno=e.deptno and ename='adams';

-- 급여가 2000이상인 사원들의 사원명과 지역을 검색 natural join
select ename, loc from emp natural join dept where sal >= 2000;

-- 위에걸 equi join으로 바꾸기
select ename, loc from emp e, dept d where e.deptno=d.deptno and sal >= 2000;

-- 급여가 1000이상 2000이하인 사원들의 사원번호, 사원이름, 부서이름을 사원번호 순으로 검색(join using)
select empno, ename, dname from emp join dept using(deptno) where sal between 1000 and 2000 order by empno;

-- 사원직무가 salesman이면서 chicago 지역에 근무 중인 사원명을 검색
select ename from emp join dept using(deptno) where job='salesman' and loc='chicago';

-- new york 이나 dallas 지역에 근무하는 사원들의 사원번호, 사원이름을 사원번호 순으로 검색(equi join)이용
select empno, ename from dept d , emp e where d.deptno=e.deptno
and loc = 'newyork' or loc ='dallas' order by empno;

-- new york 이나 dallas 지역에 근무하는 사원들의 사원번호, 사원이름을 사원번호 순으로 검색 (natural join)
select empno, ename from emp natural join dept where loc = 'newyork' or loc ='dallas' order by empno;

-- 부서이름의 accounting이거나 지역이 chicago 인 사원의 사원번호와 사원이름을 검색 (equi join)
select empno, ename from emp e, dept d where e.deptno=d.deptno and ( dname='accounting' or loc='chicago');

------------DML---------------

-- 1. 20 또는 30인 부서번호만 사원(emp)테이블에서 복사하여 emp1테이블을 생성해라.
CREATE TABLE emp1 as SELECT * FROM emp WHERE deptno in (20,30);

-- 2. dept 테이블을 복사하여 dept1테이블을 생성해라.
CREATE TABLE dept1 as SELECT * FROM dept;

-- 3. salgrade테이블을 복사하여 salgrade1테이블을 생성해라.
CREATE TABLE salgrade1 as SELECT * FROM salegrade;

-- 4. 각각의 테이블을 확인한다.
SELECT * FROM emp1;
SELECT * FROM dept1;
SELECT * FROM salgrade1;

-- 5. 사원번호 7401, 사원이름 HOMER, 급여 1300, 부서번호 10인 사원이 오늘 입사하였다.
INSERT INTO emp1 VALUES (7401, 'HOMER', null, null, null, 1300, null, 10);

-- 6. 사원번호 7323, 사원이름 BRANDA, 부서번호 30, 사원번호 7499와 동일한 급여를 받는 사원이 입사하였다. (부질의)
INSERT INTO emp1(empno, ename, deptno,sal) VALUES (7323, 'BRANDA', 30, (
SELECT empno FROM emp WHERE empno = 7499));

-- 7. 사원(emp)테이블에서 부서번호가 10인 데이터를 emp1테이블에 삽입해라.
INSERT INTO emp1 SELECT deptno FROM emp WHERE deptno = 10;

-- 8. 사원번호 7369의 사원직무를 ANALYST로 바꾸어라.
UPDATE emp1 SET job = 'ANALYST' WHERE empno = 7369;

-- 9. 부서번호 20인 직원들의 급여를 10% 감하라.
UPDATE emp1 SET sal = sal*0.9 WHERE deptno = 20;

-- 10. 모든 사원의 급여를 +100 증가시켜라
UPDATE emp1 SET sal = sal+100;

-- 11. 지역이 DALLAS인 사원들의 급여를 10감하라. (부질의)
UPDATE emp1, dept1 SET sal = sal-10 WHERE emp1.deptno = dept1.deptno
and loc = ( SELECT loc FROM dept1 WHERE loc = 'DALLAS');

-- 12. 사원번호 7499가 퇴사하였다.
DELETE FROM emp1 WHERE empno = 7499;

-- 13. 부서번호 50, 부서이름 ‘PLANNING’, 지역 ‘MIAMI’가 추가되었다.
INSERT INTO dept1 VALUES(50, 'PLANNING', 'MIAMI');

-- 14. 부서번호가 40인 부서가 60으로 변경되었다.
UPDATE dept1 set deptno = 60 WHERE deptno = 40;

-- 15. Dept1 테이블에 없는 부서번호들을 갖고 있는 사원들의 부서번호를 99로 변경하라. (부질의)
UPDATE emp1 SET deptno = 99 WHERE deptno NOT IN ( SELECT deptno FROM dept1);

-- 16. JONES, JOSH, CLARK가 30번 부서로 바뀌었다.
UPDATE emp1 SET deptno = 30 WHERE ename in('JONES', 'JOSH', 'CLARK');

-- 17. 커미션이 null인 데이터를 0으로 바꾸어라.
UPDATE emp1 SET comm = 0 WHERE comm is NULL;

-- 18. Emp1 전체 테이블의 데이터를 삭제하라.
DELETE FROM emp1;

 

[ 문제풀이 3 - book ordes custome 테이블 생성 ]

-- 논리적 모델링 테이블 생성으로 후에 물리적 모델링 진행
create table book(
bookid int primary key,
bookname varchar(100),
publisher varchar(100),
price int);

create table orders(
orderid int primary key,
custid int,
bookid int,
saleprice int,
orderdate date);

create table customer(
custid int primary key,
name varchar(10),
address varchar(100),
phone varchar(20));

alter table orders -- book 테이블을 수정하겠다
add 
constraint fk_ord foreign key(bookid) 
references book(bookid);

INSERT INTO BOOK VALUES (1, '컴퓨터의 역사', '아이티', 7000);
INSERT INTO BOOK VALUES (2, '컴퓨터는 무엇일까', '굿북', 13000);
INSERT INTO BOOK VALUES (3, '컴퓨터의 이해', '비트아이티', 22000);
INSERT INTO BOOK VALUES (4, '자바 이론', '비트아이티', 35000);
INSERT INTO BOOK VALUES (5, '데이터베이스 교본', '아이티', 8000);
INSERT INTO BOOK VALUES (6, 'HTML 기술', '아이티', 6000);
INSERT INTO BOOK VALUES (7, '스프링의 개념', '에비씨미디어', 20000);
INSERT INTO BOOK VALUES (8, '스프링을 부탁해', '에비씨빛미디어', 13000);
INSERT INTO BOOK VALUES (9, '파이썬 이야기', '코딩빔', 7500);
INSERT INTO BOOK VALUES (10, 'python story', 'Pearson', 13000);

INSERT INTO CUSTOMER VALUES (1, '박건우', '인천시 송도', '010-1111-2222');
INSERT INTO CUSTOMER VALUES (2, '김선해', '서울시 종로구', '010-2111-2222');   
INSERT INTO CUSTOMER VALUES (3, '장지혜', '서울시 용산구', '010-3111-2222');   
INSERT INTO CUSTOMER VALUES (4, '추오섭', '경기도 의정부', '010-4111-2222');   
INSERT INTO CUSTOMER VALUES (5, '박승철', '서울시 마포구', NULL);

INSERT INTO ORDERS VALUES (1, 1, 1, 6000, '2022-05-01');
INSERT INTO ORDERS VALUES (2, 1, 3, 21000, '2022-05-03');
INSERT INTO ORDERS VALUES (3, 2, 5, 8000, '2022-05-03');
INSERT INTO ORDERS VALUES (4, 3, 6, 6000, '2022-05-04');
INSERT INTO ORDERS VALUES (5, 4, 7, 20000, '2022-05-05');
INSERT INTO ORDERS VALUES (6, 1, 2, 12000, '2022-05-07');
INSERT INTO ORDERS VALUES (7, 4, 8, 13000, '2022-05-07');
INSERT INTO ORDERS VALUES (8, 3, 10, 12000, '2022-05-08');
INSERT INTO ORDERS VALUES (9, 2, 10, 7000, '2022-05-09');
INSERT INTO ORDERS VALUES (10, 3, 8, 13000, '2022-05-10');

/// 테이블 생성후 값 입력 후 잘 들어 갔는지 확인 ///
select * from book;
select * from orders;
select * from customer;

-- 1.   김선해 고객의 전화번호를 찾아라.
select phone from customer where name='김선해';

-- 2.   모든 도서의 이름과 가격을 검색해라.
select bookname, price from book;

-- 3.   모든 도서의 도서번호, 도서이름, 출판사, 가격을 검색해라.
select * from book;

-- 4.   도서 테이블에 있는 모든 출판사를 검색해라. (중복제거해서)
select distinct publisher from book;

-- 5.   가격이 20000원 미만인 도서를 검색해라.
select * from book where price < 20000;

-- 6.   가격이 10000원 이상 20000 이하인 도서를 검색해라.
select * from book where price between 10000 and 20000;

-- 7.   출판사가 ‘아이티’ 혹은 ‘비트아이티’ 인 도서를 검색해라.
select * from book where publisher='아이티' or publisher='비트아이티';
select * from book where publisher in('아이티','비트아이티');


-- 8.   출판사가 ‘아이티’ 혹은 ‘비트아이티’ 아닌 도서를 검색해라.
select * from book where not publisher='아이티' and not publisher='비트아이티';
select * from book where publisher not in('아이티','비트아이티');

-- 9.   ‘컴퓨터의 역사’를 출간한 출판사를 검색해라.
select publisher from book where bookname='컴퓨터의 역사';

-- 10.   도서이름에 ‘컴퓨터’가 포함된 출판사를 검색해라.
select publisher from book where bookname like '%컴퓨터%';

-- 11.   도서이름의 왼쪽 두번 째에 ‘퓨’라는 문자열을 갖는 도서를 검색해라.
select * from book where bookname like '_퓨%';

-- 12.   컴퓨터에 관한 도서 중 가격이 20000원 이상인 도서를 검색해라.
select * from book where price >= 20000;

-- 13.   도서를 이름순으로 검색해라.
select * from book order by bookname;

-- 14. 도서를 가격순으로 검색하고, 가격이 같으면 이름순으로 검색해라.
select * from book ORDER BY price, bookname;

-- 15. 도서를 가격의 내림차순으로 검색해라. 가격이 같다면 출판사의 오름차순으로 검색해라.
select * from book ORDER BY price desc, publisher;

-- 16. 고객이 주문한 도서의 총 판매액을 구해라.
select sum(saleprice) as sum_price from orders;

-- 17. 2번 김선해 고객이 주문한 도서의 총 판매액을 구해라.
select sum(saleprice) as sum_price from orders where custid = 2; -- 만약 김선해로 찾을 시 JOIN 필요

-- 18. 고객이 주문한 도서의 총 판매액, 평균값, 최저가, 최고가를 구해라.
select sum(saleprice) as sum_price, avg(saleprice) as avg_price, 
min(saleprice) as min_price, max(saleprice) as max_price from orders;

-- 19. 23서점의 도서 판매 건수를 구해라. 
select count(*) as all_sale_count from orders;

-- 20. 고객별로 주문한 도서의 총 수량과 총 판매액을 구해라.
select custid, count(*), sum(saleprice) from orders group by custid;

-- 21. 가격이 8000원 이상인 도서를 구매한 고객에 대하여 고객별 주문 도서의 총 수량을 구해라. 단, 두 권 이상 구매한 고객만 구해라.
select custid, count(*) from orders where saleprice >= 8000
group by custid having count(custid)>=2;

select custid, count(*) as BUY 
from (select ord.custid, ord.bookid, ord.saleprice, bk.bookname, bk.price, cust.name
	  from orders ord, book bk, customer cust
      where ord.bookid = bk.bookid 
      and  ord.custid = cust.custid
      and bk.price >= 8000 ) AA -- AA는 별칭 안 주면 Error Code 1248 뜸.
	  group by custid having count(custid)>=2;  -- 테이블 3개를 조인시켜서 다 연결해줌. 위와 달리 custid 2도 출력됨

-- 22. 고객과 고객의 주문에 관한 데이터를 모두 보여라.
select * from customer cust
JOIN orders ord
ON cust.custid=ord.custid;

-- 23. 고객과 고객의 주문에 관한 데이터를 고객번호 순으로 정렬하여 보여라.
select * from customer cust
JOIN orders ord
ON cust.custid=ord.custid
ORDER BY cust.custid;

-- 24. 고객의 이름과 고객이 주문한 도서의 판매가격을 검색해라.
select name, saleprice 
from customer cust
JOIN orders ord ON cust.custid = ord.custid;

-- 25. 고객별로 주문한 모든 도서의 총 판매액을 구하고, 고객별로 정렬해라.
select custid, sum(saleprice) from orders group by custid order by custid;

-- 26. 고객의 이름과 고객이 주문한 도서의 이름을 구해라.
select name, bookname 
from customer cust
JOIN orders ord 
ON cust.custid = ord.custid
JOIN book bk
ON ord.bookid = bk.bookid;

-- 27. 가격이 20000원인 도서를 주문한 고객의 이름과 도서의 이름을 구해라.
select name, bookname
from customer cust
JOIN orders ord 
ON cust.custid = ord.custid
JOIN book bk
ON ord.bookid = bk.bookid
where saleprice = 20000;

-- 28. 도서를 구매하지 않은 고객을 포함하여 고객의 이름과 고객이 주문한 도서의 판매가격을 구해라. outer join 활용
select name, bookname, price 
from customer cust
LEFT JOIN orders ord 
ON cust.custid = ord.custid
LEFT JOIN book bk
ON ord.bookid = bk.bookid;

-- 29. 가장 비싼 도서의 이름을 구해라.
select bookname from book ORDER BY price DESC LIMIT 1;

-- 30. 도서를 구매한 적이 있는 고객의 이름을 검색해라.
SELECT DISTINCT customer.name
FROM orders
JOIN customer ON customer.custid = orders.custid;

-- 31. 비트아이티에서 출판한 도서를 구매한 고객의 이름을 보여라.
select name
from orders join book using(bookid) join customer using(custid)
where publisher='비트아이티';

-- 32. 출판사별로 출판사의 평균 도서 가격보다 비싼 도서를 구해라.
select bk.bookname, bk.price, bk_avg.publisher, bk_avg.avg_price
from book bk,
(select publisher, avg(price) as avg_price
from book
group by publisher) bk_avg
where bk.publisher= bk_avg.publisher
and bk.price > bk_avg.avg_price;

-- 33. Book테이블에 새로운 도서 ‘공학 도서’를 삽입해라.
-- 공학 도서는 더샘에서 출간했으며 가격을 40000원이다.
insert into book values(11,'공학 도서','더샘',40000);

-- 34. Book테이블에 새로운 도서 ‘공학 도서’를 삽입해라.
-- 공학 도서는 더샘에서 출간했으며 가격은 미정이다.
insert into book values(12,'공학 도서','더샘',null);

-- 35. Customer테이블에서 고객번호가 5인 고객의 주소를
-- ‘서울시 서초구’로 변경해라.
update customer
set address='서울시 서초구'
where custid=5;

-- 36. Customer테이블에서 박승철 고객의 주소를 김선해 고객의 주소로 변경해라.
update customer
set address=(select address from (select address customer 
                                 where name='김선해') as A)
                                 where name='박승철';

-- 37. 아이티에서 출판한 도서의 제목과 제목의 글자수를 확인해라.
select bookname, char_length(bookname)
from book
where publisher='아이티';

-- 38. 23서점의 고객 중에서 같은 성(이름 성)을 가진 사람이
-- 몇 명이나 되는지 성별 인원수를 구해라.
select sung,count(*)
from (select substr(name,1,1) as sung
from customer) A
group by sung;

-- 39. 23서점은 주문일로부터 10일 후 매출을 확정한다.
-- 각 주문의 확정일자를 구해라.
select orderdate, (orderdate+10) as confirmdate
from orders;

-- 40. 23서점이 2022년 5월 7일에 주문받은 도서의
-- 주문번호, 주문일, 고객번호, 도서번호를 모두 보여라.
-- 주문일은 ‘yyyy-mm-dd요일’형태로 표시한다.
select orderid, date_format(orderdate,'%y-%m-%d-%w요일'), custid, bookid
from orders
where orderdate='2022-05-07';

-- 41. 이름, 전화번호가 포함된 고객목록을 보여라.
-- 단, 전화번호가 없는 고객은 ‘연락처없음’으로 표시해라.
select name, ifnull(phone,'연락처없음')
from customer;

-- 42. 평균 주문금액 이하의 주문에 대해 주문번호와 금액을 보여라.
select orderid, saleprice
from orders
where saleprice <= (select avg(saleprice) from orders);

-- 43. 각 고객의 평균 주문금액보다 큰 금액의 주문 내역에 대해
-- 주문번호, 고객번호, 금액을 보여라.
select orderid, custid, saleprice
from orders
where saleprice > all(select avg(saleprice) from orders
group by custid);

SELECT o.orderid, o.custid, o.saleprice
FROM orders o
WHERE o.saleprice > (SELECT AVG(saleprice) FROM orders WHERE custid = o.custid);
-- 내가 조회하는 orders테이블의 고객아이디랑 AVG를해서 구해온데이터의 고객아이디랑 같은애들끼리의 평균치를 구하는 것 !!!
-- 각각의 고객의 자기자신의 평균금액보다 큰데이터가 필요해

-- 44. 서울시에 거주하는 고객에게 판매한 도서의 총판매액을 구해라.
select sum(saleprice)
from orders
where custid in (select custid from customer
where address like '서울%');
-----------------------------------------------------
select sum(saleprice)
from orders ord, customer cust
where ord.custid=cust.custid and address like '서울%';

-- 45. Customer테이블에서 고객번호가 5인 고객을 삭제해라.
delete from customer where custid=5;

 

[ 문제풀이 4 - product , trade, customer, stock 테이블 생성 ]

create table product(
p_code char(3) not null,
p_name varchar(30),
p_cost int,
p_group varchar(30),
constraint product_pk primary key(p_code));

insert into product values('101','19인치 모니터',150000,'모니터');
insert into product values('102','22인치 모니터',200000,'모니터');
insert into product values('103','25인치',260000,'모니터');
insert into product values('201','유선마우스',7000,'마우스');
insert into product values('202','무선마우스',18000,'마우스');
insert into product values('301','유선키보드',8000,'키보드');
insert into product values('302','무선키보드',22000,'키보드');
insert into product values('401','2채널 스피커',10000,'스피커');
insert into product values('402','5.1채널 스피커',120000,'스피커');

create table trade(
t_seq int not null,
p_code char(3),
c_code varchar(4),
t_date date,
t_qty int,
t_cost int,
t_tax int,
constraint trade_pk primary key(t_seq));

insert into trade
values (61,'131','101','2016-04-01',10,150000,150000);
insert into trade
values (5,'102','102','2016-04-26',8,200000,160000);
insert into trade
values (8,'103','101','2016-05-20',2,260000,52000);
insert into trade
values (3,'201','103','2016-04-13',7,7000,4900);
insert into trade
values (2,'201','101','2016-04-12',5,7000,3500);
insert into trade
values (9,'202','104','2016-06-02',8,18000,14400);
insert into trade
values (6,'301','103','2016-05-02',12,8000,9600);
insert into trade
values (10,'302','103','2016-06-09',9,22000,19800);
insert into trade
values (4,'401','104','2016-04-20',15,10000,15000);

create table customer(
c_code varchar(4) not null,
c_name varchar(30),
c_ceo varchar(12),
c_addr varchar(100),
c_phone varchar(13),
constraint custromer_pk primary key(c_code));

insert into customer values('101','늘푸른회사','김수종','경기도 안산시','010-1234-5678');
insert into customer values('102','사랑과바다','박나리','경기도 평택시','010-1122-3344');
insert into customer values('103','대한회사','이민수','서울시 구로구','010-3785-8809');
insert into customer values('104','하얀기판','허진수','경상북도 포항시','010-8569-3468');
insert into customer values('105','한마음한뜻','하민우','인천시 남동구','010-9455-6033');

create table stock(
p_code char(3) not null,
s_qty int not null,
s_lastdate date,
constraint stock_pk1 primary key(p_code,s_qty));

insert into stock values('101',50,'2016-04-1');
insert into stock values('102',20,'2016-04-26');
insert into stock values('103',5,'2016-05-20');
insert into stock values('201',2,'2016-04-13');
insert into stock values('202',15,'2016-06-02');
insert into stock values('301',0,'2016-05-02');
insert into stock values('302',20,'2016-06-09');
insert into stock values('401',10,'2016-06-15');
insert into stock values('402',7,'2016-05-08');

-- 1. 상품 정보(product)테이블에 열 이름이 ‘비고’ 라는 열을 varchar2(20)으로 삽입해라.
alter table product add 비고 varchar(20);

-- 2. 1번에서 삽입한 열이 상품 정보(product)테이블에 삽입되었는지 확인해라.
select * from product;

-- 3. 상품 정보(product)테이블에 ‘비고’ 열의 구조를 char(3)으로 변경해라.
alter table product modify 비고 char(3);
desc product;

-- 4. 상품코드 401에 대한 거래내역 뷰(v_trade)를 만들어라.
create view v_trade as select t.* from trade t where t.p_code = '401';

-- 5. 상품 정보(product)테이블에 가장 최근에 들어온 거래처 코드 정보를 검색해라(top-n질의)
select * from product order by p_code desc limit 1;

SELECT p.p_code, p.p_name, t.c_code, MAX(t.t_date)
FROM product p
JOIN trade t ON p.p_code = t.p_code
GROUP BY p.p_code, p.p_name, t.c_code
ORDER BY MAX(t.t_date) DESC
LIMIT 1;

-- 6. 상품을 삽입하는 프로시저를 생성해라.call p_pro(‘403’, ’7.1채널 스피커’, 180000, ‘스피커’); 완료되었다.
DELIMITER //
CREATE PROCEDURE p_pro(in v_p_code int,
in v_p_name varchar(30),
in v_p_cost int,
in v_p_group varchar(30))
BEGIN
INSERT INTO product(p_code, p_name, p_cost, p_group)
VALUES(v_p_code, v_p_name, v_p_cost, v_p_group);
END //
DELIMITER ;

CALL p_pro(403, '7.1채널 스피커', 180000, '스피커');

 

 

---

[ 복습 ]

use mmm;
create table major(
major_code int primary key auto_increment,
major_name varchar(50));

desc major;

create table student(
student_id int primary key auto_increment,
student_name varchar(30),
student_height decimal(5,2),
major_code int);

desc student;

create table enrol(
enrol_id int primary key auto_increment,
enrol_name varchar(50),
prof_code int,
start_date date,
end_date date);

desc enrol;

create table student_enrol (
id int not null unique,
student_id int,
enrol_code int,
primary key(student_id, enrol_code));

desc student_enrol;

create table prof(
prof_code int primary key auto_increment,
prof_name varchar(20),
major_code int);

desc prof;

commit;

 

-- add : 제약 조건 추가 
-- student 테이블에 있는 major 코드가 major 테이블에 있는 major 코드를 참조한다.
alter table student add constraint fk_stu foreign key(major_code)
references major(major_code);

-- prof 테이블에 있는 major 코드가 major 테이블에 있는 major 코드를 참조한다.
alter table prof add constraint fk_prof foreign key(major_code)
references major(major_code);

alter table student add constraint fk_stu foreign key(major_code) references major(major_code); 를 통해 관계가 생김 ( 노랑색은 primary key )

-- student 테이블에 있는 major 코드가 major 테이블에 있는 major 코드를 참조한다.

 

 

 

 

---

[ MySQL api ]

MySQL :: MySQL 8.0 Reference Manual :: 14.7 Date and Time Functions

MySQL :: MySQL 8.0 Reference Manual :: 14.7 Date and Time Functions