12/01 - SQL(3) : 서브쿼리, join(inner, outer), create, alter, insert, update, delete, not null, default

DML

SELECT               // 컬럼절

FROM 테이블명   //

WHERE              // 조건절

GROUP BY        // 그룹화

HAVING            // 그룹조건절

ORDER BY         // 순서

 

* 함수                      - 데이터의 내용 수정해서 볼수 있음( avg, sum)   - 보여주는 형식을 만드는 것

단일행 함수

           1 행 -> 1 결과

복수행 함수(그룹함수)

           여러행 -> 1 결과

           group by          // 집합적 처리 // ~별

 

---

 

 

 

 

 

 

두개의 질의 결합

---

1. 서브쿼리(subquery)

(1)최고 급여를 받는 사원에 대한 사원정보 출력

-   최고 급여

              select max(sal)

              from emp;

-  최고 급여와 같은 사원 정보

              select empno, ename

              from emp

              where sal = (select max(sal) from emp);

(2) scott의 급여보다 큰 사원번호, 사원명, 급여를 출력

(3) 20번 부서의 최고 급여를 받은 사원에 대한 사원 번호, 사원명, 급여

 

1-1 . 서브쿼리 특징

   1) 단일행 서브쿼리(1행, 1열)

   2) 복수행 서브쿼리(여러행, 1열)  - 아래 4개 밖에 못씀

                  < all (서브쿼리) - 서브쿼리에 나온것 중 최솟값 보다 작은

                  > all (서브쿼리) - 서브쿼리에 나온것 중 최댓값 보다 큰

                  < any (서브쿼리) - 서브쿼리에 나온것 중 최댓값 보다 작은

                  > any (서브쿼리) - 서브쿼리에 나온것 중 최솟값 보다 큰

 

   3) 연산자 => 비교연산자

   4) 서브쿼리의 결과 => 반드시 1개

 

 

 

  (1) 직책이 manager 사원의 최소 급여보다 작은 사원에 대한 정보 

  (2) 부서별 최대 급여를 받는 사원들의 사번, 이름 , 부서코드, 급여를 검색하시오. (in 사용)

        select empno, ename, deptno, sal
        from emp   
        where sal in (select max(sal) from emp group by deptno);

 

 

 

 

2. join

       카테시안 프로덕트(곱)

        select * from emp cross join 컬럼명;

        select * from emp inner join 컬럼명;

 

2-1. equil join

두개의 컬럼값의 값이 같은 행만 추출

 

예제) 테이블 emp와 dept이 가지고 있는 deptno 컬럼으로 둘을 합치기

(1)

select *

from emp cross join dept

where(emp.deptno = dept.deptno);

(2)

select *

from emp inner join dept

where(emp.deptno = dept.deptno);

(3)

select *

from emp join dept

where(emp.deptno = dept.deptno);

(4)

select *

from emp inner  join dept

using(deptno);

 

 

예제) 직책이 clerk인 사원의 사원이름, 사원 번호, 부서번호,부서이름, 부서위치

(1)

select ename, empno, deptno, dname, loc

from emp inner join dept

using(deptno)

where (emp.job = 'clerk');

(2) 이 방법의 경우 deptno가 어느 테이블의 deptno인지 헷갈려 하므로 어느테이블의 deptno인지 명시해줘야 함

select empno, ename, e.deptno, dname, loc

from emp e join dept d

where(e.deptno = d.deptno) and job = 'clerk';

 

---- 테이블도 별명을 줄수 있다.

 

 

2-2. non-equil join

테이블 emp와 salgrade 합치는데 겹치는 컬럼이 없다.

따라서 조건 없이 합치면 각각의 레코드가 한번씩 다 매칭 되어야 해서

(emp 레코드 수 * salgrade 레코드 수) 의 많은 양의 레코드가 나온다.

 

(1) 여기선 emp 테이블의 sal이 salgrade 테이블의 losal 초과, hisal 이하인 조건을 걸어서

emp 테이블 레코드 하나당  salgrade 테이블 레코드를 하나씩 매칭 시킨 테이블을 만든다.

 

select empno, ename, sal, grade

from emp e inner join salgrade s

where e.sal > s.losal and e.sal <= s.hisal;

 

(2) 그때의 조건은 where도 되고 on도 된다.

그러나 where 절이 검색 필터로도 쓰일 경우 inner join을 위한 매칭 조건은 on 을 써야한다.

(조건절(where)이랑 합치기 위한 건지 구분하기 위해서)

 

select empno, ename, sal, grade

from emp e inner join salgrade s

on e.sal > s.losal and e.sal <= s.hisal;

 

(3) 부서번호가 20번 이라는 조건을 추가한다.

 

select empno, ename, sal, grade

from emp e inner join salgrade s

on (e.sal > s.losal and e.sal <= s.hisal)

where deptno = 20;

 

 

(4) on에 between 사용한다.

 

select empno, ename, sal, grade

from emp e inner join salgrade s

on (e.sal between  s.losal and s.hisal)

where deptno = 20;

 

 

 

예제) 사원번호, 사원이름, 급여, 호봉, 부서이름, 부서위치

             = 테이블 3

             = 조인 2

 

   select empno, ename, sal, grade, dname, loc

   from emp e inner join dept d

   on e.deptno = d.deptno

   inner join salgrade s 

   on e.sal between s.losal and s.hisal; 

 

 

2-3. outer join

(  inner join 보다 많이 쓰임 )

상품 / 주문

       - 재고

회원 / 주문

       - 구입

left outer join, right outer join

 

1) left outer join

왼쪽 테이블만 다보여줌

 

2) right outer join

 

 

 

 

 

2-4. self join

1) inner join

한 테이블 안에서 컬럼값들에 조건을 걸어 합치기

꼭 테이블 alias를 써줘야한다.

 

사원번호와 담당 매니저의 매칭시키기

 

 

2) outer join

빈곳은  null로 채운다.

 

※ 문제

아래 사진과 같이 출력하기

 

select e.ename 사원, ifnull(m.ename,'관리자 없음') 관리자
from emp e left outer join emp m
on (e.mgr = m.empno);

 

 

 

 

 

 

 

 

DDL  -  create

---

데이터를 담을 상자를 만든다.

1. 데이터베이스

2. 테이블

3. ...

 

* 리눅스 대소문자

* 예약어

 

 

 

1) 데이터베이스 삭제

drop database test1;

 

2) 테이블 생성

creat table 테이블명(

컬럼명 테이터타입(사이즈) 옵션 .. ,

컬럼명 테이터타입(사이즈) 옵션 .. ,

컬럼명 테이터타입(사이즈) 옵션 .. ,

)

 

 

3) 문자열형

        char(크기)            - 고정형            255byte

        varchar(크기)     - 가변형            65535byte

 

       char(10)     - abc      -  10(3)   속도가 빠름

       varchar(10)  - abc  - 3    신축성이 좋음

 

       text ..

  숫자형

       int(integer)

               tiny

               medium

               big.

       number

   날짜형

               date

               time

               datetime(*)

 

 

 

 

4) 예시

 create table emp2(
     empno int(4),
     ename varchar(10),
     job varchar(9),
     mgr int(4),
     hiredate date,
     sal decimal(7,2),
     comm decimal(7,2),
     deptno int(2)
);

 

 

(1) 테이블 참조해서 테이블 만들기

create table dept2 as select * from dept; (1대 1 copy)

create table dept3 as select loc, deptno from dept where deptno = 10;

 

(2) 테이블 emp참조해서 사원번호, 사원이름, 급여, 연봉(null이여도 계산되게), 입사일자가 컬럼인 emp_year 만들기

create table emp_year 

as select empno 사원번호, ename 사원이름, sal 급여, sal*12 +ifnull(comm, 0) 연봉, hiredate 입사일자

from emp;

 

(3) 테이블 emp 참조해서 사원번호, 사원이름, 입사일자, 부서번호, 부서명, 부서위치 컬럼인 all_emp 만들기

create table all_emp as select empno, ename, hiredate, deptno, dname, loc from emp inner join dept using (deptno);

 

(4) 빈테이블로 복사하기  

  *  부정조건  -  ( 데이터 복사가 안되게 부정조건 걸어줌 )

    create table empty_dept1
    as select * from dept where 1!=1;

 

  * like 

    create table empty_dept2 like dept

 

 

 

 

 

 

 

 

 

 

DDL  -  alter

---

테이블 수정   -  alter

         1. 컬럼

                    1-1. 추가

                    1-2. 내용변경

                    1-3. 삭제

         2. 제약 조건  ( 맨 아래)

 

 

1. 컬럼

1-1. 추가

1) add

alter table emp_year
add job varchar(10);

 

1-2. 내용 변경

1) modify

alter table emp_year
modify job varchar(20);

2) rename

alter table emp_year
rename column job to work;

 

 

1-3. 삭제

1) drop

alter table emp_year
drop work;

 

 

 

show create table salgrade;

show columns from dept;

show table status;    -> 관리자들이 주로 함

 

 

 

 

 

 

 

DDL  +   DML

---

    1.  insert

    2.  update

    3.  delete

 

1. insert

 

insert into  테이블명 values (값, 값, 값)

* 값 -  숫자 / '문자열'

insert into 테이블명 (컬럼명, 컬럼명, 컬럼명) values (값, 값, 값);

 

 

2. update

update 테이블명 set 컬럼명=값 values 컬럼명=값;

 

3. delete

delete from 테이블명 where 컬럼명 =  값;

delete from 테이블명;

 

 

 

 

 

 

 

 

 

 

무결성

---

• 제대로 유지되는 / 하는 조건

 

1. null 무결성

''를 insert하면 null이 아닌 공백이 들어간다.

 

not null 로 설정된 컬럼에  null을 insert하면 에러가 발생한다. 

그러나 '' 로 공백을 넣는 것은 가능하다.

update도 마찬가지다.

 

2. 여러개 insert

values (값,값,값), (값, 값,값) , ...

서브쿼리 사용해서 다른 테이블의 레코드 가져와서 넣기

 

3. default

 

 

 

 

 

 

 

 

 

 

 

 

※ 문제

1. 사원 테이블에서 BLAKE 보다 급여가 많은 사원들의 사번, 이름, 급여를 검색하시오.
select empno, ename, sal
from emp
where sal > (select sal from emp where ename = 'blake');

2. 사원 테이블에서 MILLER보다 늦게 입사한 사원의 사번, 이름, 입사일을 검색하시오.
select empno, ename, hiredate
from emp
where hiredate > (select hiredate from emp where ename = 'miller');

3. 사원 테이블에서 사원 전체 평균 급여보다 급여가 많은 사원들의 사번, 이름, 급여를 검색하시오.
select empno, ename, sal
from emp
where sal > (select avg(sal) from emp);

4. 사원 테이블에서 CLARK와 같은 부서이며, 사번이 7698인 직원의 급여보다 많은 급여를 받는 사원들의 사번, 이름, 급여를 검색하시오.
select empno, ename, sal
from emp
where deptno = (select deptno from emp where ename = 'clark')
and sal > (select sal from emp where empno = 7698);

1. 부서 테이블과 사원 테이블에서 사번, 사원명, 부서코드, 부서명을 검색하시오. 단, 출력시, 사원명을 기준으로 오름차순으로 정렬하시오.
select empno, ename, deptno, dname
from emp inner join dept
using (deptno)
order by dname;

2. 부서 테이블과 사원 테이블에서 사번, 사원명, 급여, 부서명을 검색하시오. 단, 급여가 2000 이상인 사원에 대하여 급여를 기준으로 내림차순으로 정렬하시오.
select empno, ename, sal,deptno
from emp inner join dept
using(deptno)
where sal >= 2000
order by sal desc;

3. 부서 테이블과 사원 테이블에서 사번, 사원명, 업무, 급여, 부서명을 검색하시오. 단, 업무가 MANAGER이며 급여가 2500 이상인 사원에 대하여 사번을 기준으로 오름차순으로 정렬하시오.
select empno, ename, job, sal, deptno
from emp inner join dept
using(deptno)
where job = 'manager' and sal >= 2500
order by empno;

4. 사원 테이블과 급여등급 테이블에서 사번, 사원명, 급여, 등급을 검색하시오. 단, 등급은 급여가 하한값과 상한값 범위에 포함되고 등급이 4이며 급여를 기준으로 내림차순으로 정렬하시오.
select empno, ename, sal, grade
from emp e inner join salgrade s
where (e.sal between s.losal and s.hisal)
and grade = 4
order by sal desc;

5. 부서 테이블, 사원 테이블, 급여등급 테이블에서 사번, 사원명, 부서명, 급여, 등급를 검색하시오. 단, 등급은 급여가 하한값과 상한값 범위에 포함되며 등급을 기준으로 내림차순으로 정렬하시오.
select empno, ename, deptno, sal, grade
from emp e inner join dept d using(deptno) inner join salgrade s
where (e.sal between s.losal and s.hisal)
order by grade desc;

6. 사원 테이블에서 사원명과 해당 사원의 관리자명을 검색하시오.
select e.ename, m.ename
from emp e inner join emp m
on (e.mgr = m.empno);

7. 사원 테이블에서 사원명, 해당 사원의 관리자명, 해당 사원의 관리자의 관리자명을 검색하시오.
select e.ename, m.ename, p.ename
from emp e inner join emp m inner join emp p
on (e.mgr = m.empno) and (m.mgr = p.empno);

혹은 

select e.ename, m.ename, p.ename
from emp e 
inner join emp m on (e.mgr = m.empno)
inner join emp p on (m.mgr= p.empno);

8. 7번 결과에서 상위 관리자가 없는 모든 사원의 이름도 사원명에 출력되도록 수정하시오.
select e.ename, m.ename, p.ename
from emp e left outer join emp m
on (e.mgr = m.empno)
left outer join emp p
on (m.mgr = p.empno);