5/20 23일차

1. 데이터베이스

           운영, 관리  => DBA <= 관리SQL

           \* 데이터 조작  => 데이터베이스 프로그램 <= 표준SQL

2. 데이터베이스 종류

           대형 -  IBM DB2 / Oracle Oracle

           중형 -  Oracle MySQL / MariaDB / MS MSSql

표준 SQL + 확장 SQL(관리용, 추가기능 ..)

한 개의 테이블에서 데이터 조회

~

~
SubQuery

두 개 이상의 테이블에서 데이터 조회 = join

                테이블의 결합

                 => cartesian product

cartesian product

select * from emp cross join dept;

select * from emp inner join dept;

Join 조건 => inner join

Equi Join (등치 조인)

       =

select *

from emp inner join dept

where emp.deptno = dept.deptno;

Non-Equi Join (비등치 조인)

      <= >= > <

select * form emp inner join dept

-> where emp.deptno = dept.deptno

-> and emp.deptno=10;

on 명령어 사용 - join조건과 검색 조건 분리

select * from emp inner join dept
-> on (emp.deptno = dept.deptno)
-> where emp.deptno=10;

select * from emp inner join dept

-> using (deptno)

-> where emp.deptno=10;

* Table Alias

select * from emp e inner join dept d

-> on(e.deptno = d.deptno)

-> where e.deptno=10;

select e.empno, e.ename, e.sal, d.dname, d.loc, d.deptno  
\-> from emp e inner join dept d  
\-> on(e.deptno = d.deptno)  
\-> where e.deptno=10;

+-------+--------+---------+------------+----------+--------+  
| empno | ename | sal | dname | loc | deptno |  
+-------+--------+---------+------------+----------+--------+  
| 7782 | CLARK | 2450.00 | ACCOUNTING | NEW YORK | 10 |  
| 7839 | KING | 5000.00 | ACCOUNTING | NEW YORK | 10 |  
| 7934 | MILLER | 1300.00 | ACCOUNTING | NEW YORK | 10 |  
+-------+--------+---------+------------+----------+--------+

담당업무가 clerk인 사원에 대한 사원번호, 사원이름, 관리자, 부서번호, 부서이름, 부서위치 출력

select e.empno, e.ename, e.mgr, d.deptno, d.dname, d.loc  
\-> from emp e inner join dept d  
\-> on(e.deptno = d.deptno)  
\-> where e.job = 'clerk';  
+-------+--------+------+--------+------------+----------+  
| empno | ename | mgr | deptno | dname | loc |  
+-------+--------+------+--------+------------+----------+  
| 7369 | SMITH | 7902 | 20 | RESEARCH | DALLAS |  
| 7876 | ADAMS | 7788 | 20 | RESEARCH | DALLAS |  
| 7900 | JAMES | 7698 | 30 | SALES | CHICAGO |  
| 7934 | MILLER | 7782 | 10 | ACCOUNTING | NEW YORK |  
+-------+--------+------+--------+------------+----------+

select \* from emp e inner join salgrade s  
\-> on(e.sal >= s.losal and e.sal <= s.hisal);  
+-------+--------+-----------+------+------------+---------+---------+--------+-------+---------+---------+  
| empno | ename | job | mgr | hiredate | sal | comm | deptno | grade | losal | hisal |  
+-------+--------+-----------+------+------------+---------+---------+--------+-------+---------+---------+  
| 7369 | SMITH | CLERK | 7902 | 2010-12-17 | 800.00 | NULL | 20 | 1 | 700.00 | 1200.00 |  
| 7499 | ALLEN | SALESMAN | 7698 | 2011-02-20 | 1600.00 | 300.00 | 30 | 3 | 1401.00 | 2000.00 |  
| 7521 | WARD | SALESMAN | 7698 | 2011-02-22 | 1250.00 | 500.00 | 30 | 2 | 1201.00 | 1400.00 |  
| 7566 | JONES | MANAGER | 7839 | 2011-04-02 | 2975.00 | NULL | 20 | 4 | 2001.00 | 3000.00 |  
| 7654 | MARTIN | SALESMAN | 7698 | 2011-09-28 | 1250.00 | 1400.00 | 30 | 2 | 1201.00 | 1400.00 |  
| 7698 | BLAKE | MANAGER | 7839 | 2011-05-01 | 2850.00 | NULL | 30 | 4 | 2001.00 | 3000.00 |  
| 7782 | CLARK | MANAGER | 7839 | 2011-06-09 | 2450.00 | NULL | 10 | 4 | 2001.00 | 3000.00 |  
| 7788 | SCOTT | ANALYST | 7566 | 2017-07-13 | 3000.00 | NULL | 20 | 4 | 2001.00 | 3000.00 |  
| 7839 | KING | PRESIDENT | NULL | 2011-11-17 | 5000.00 | NULL | 10 | 5 | 3001.00 | 9999.00 |  
| 7844 | TURNER | SALESMAN | 7698 | 2011-09-08 | 1500.00 | 0.00 | 30 | 3 | 1401.00 | 2000.00 |  
| 7876 | ADAMS | CLERK | 7788 | 2017-07-13 | 1100.00 | NULL | 20 | 1 | 700.00 | 1200.00 |  
| 7900 | JAMES | CLERK | 7698 | 2011-12-03 | 950.00 | NULL | 30 | 1 | 700.00 | 1200.00 |  
| 7902 | FORD | ANALYST | 7566 | 2011-12-03 | 3000.00 | NULL | 20 | 4 | 2001.00 | 3000.00 |  
| 7934 | MILLER | CLERK | 7782 | 2012-01-23 | 1300.00 | NULL | 10 | 2 | 1201.00 | 1400.00 |  
+-------+--------+-----------+------+------------+---------+---------+--------+-------+---------+---------+

\====> where between A and b;

select \* from emp e inner join salgrade s  
\-> where e.sal  
\-> between s.losal and s.hisal;

select \* from emp e inner join salgrade s  
    -> where e.sal  
    -> between s.losal and s.hisal;

입사년도가 2011년인 사원에 대한 사원번호, 사원이름, 급여, 호봉 출력

 select e.empno, e.ename, e.sal, s.grade

 from emp e inner join salgrade s

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

where hiredate between '2011-01-01' and '2011-12-31';

사원에 대한 사원번호, 사원이름,급여등급,기존급여,인상급여를 출력

1호봉 => 40%인상

2호봉 => 30% 인상

3호봉 => 20% 인상

기타 -> 인상없음

select e.empno, e.ename, s.grade,e.sal '기존 급여',    
case s.grade  
when 1 then sal \*1.4  
when 2 then sal \* 1.3  
when 3 then sal \*1.2  
else sal  
end '인상 급여'

 from emp e inner join salgrade s

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

+-------+--------+-------+---------------+---------------+  
| empno | ename  | grade | 기존 급여     | 인상 급여     |  
+-------+--------+-------+---------------+---------------+  
|  7369 | SMITH  |     1 |        800.00 |      1120.000 |  
|  7499 | ALLEN  |     3 |       1600.00 |      1920.000 |  
|  7521 | WARD   |     2 |       1250.00 |      1625.000 |  
|  7566 | JONES  |     4 |       2975.00 |      2975.000 |  
|  7654 | MARTIN |     2 |       1250.00 |      1625.000 |  
|  7698 | BLAKE  |     4 |       2850.00 |      2850.000 |  
|  7782 | CLARK  |     4 |       2450.00 |      2450.000 |  
|  7788 | SCOTT  |     4 |       3000.00 |      3000.000 |  
|  7839 | KING   |     5 |       5000.00 |      5000.000 |  
|  7844 | TURNER |     3 |       1500.00 |      1800.000 |  
|  7876 | ADAMS  |     1 |       1100.00 |      1540.000 |  
|  7900 | JAMES  |     1 |        950.00 |      1330.000 |  
|  7902 | FORD   |     4 |       3000.00 |      3000.000 |  
|  7934 | MILLER |     2 |       1300.00 |      1690.000 |  
+-------+--------+-------+---------------+---------------+

3개 테이블

사원번호, 사원이름, 급여, 부서이름, 부서위치 ,급여등급

select e.empno, e.ename, e.sal, d.dname, d.loc, s.grade  
    ->  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);  
+-------+--------+---------+------------+----------+-------+  
| empno | ename  | sal     | dname      | loc      | grade |  
+-------+--------+---------+------------+----------+-------+  
|  7369 | SMITH  |  800.00 | RESEARCH   | DALLAS   |     1 |  
|  7499 | ALLEN  | 1600.00 | SALES      | CHICAGO  |     3 |  
|  7521 | WARD   | 1250.00 | SALES      | CHICAGO  |     2 |  
|  7566 | JONES  | 2975.00 | RESEARCH   | DALLAS   |     4 |  
|  7654 | MARTIN | 1250.00 | SALES      | CHICAGO  |     2 |  
|  7698 | BLAKE  | 2850.00 | SALES      | CHICAGO  |     4 |  
|  7782 | CLARK  | 2450.00 | ACCOUNTING | NEW YORK |     4 |  
|  7788 | SCOTT  | 3000.00 | RESEARCH   | DALLAS   |     4 |  
|  7839 | KING   | 5000.00 | ACCOUNTING | NEW YORK |     5 |  
|  7844 | TURNER | 1500.00 | SALES      | CHICAGO  |     3 |  
|  7876 | ADAMS  | 1100.00 | RESEARCH   | DALLAS   |     1 |  
|  7900 | JAMES  |  950.00 | SALES      | CHICAGO  |     1 |  
|  7902 | FORD   | 3000.00 | RESEARCH   | DALLAS   |     4 |  
|  7934 | MILLER | 1300.00 | ACCOUNTING | NEW YORK |     2 |  
+-------+--------+---------+------------+----------+-------+

select e.empno, e.ename, e.sal, d.dname, d.loc, s.grade  
    ->  from emp e inner join dept d inner join salgrade s  
    -> on(e.deptno = d.deptno and e.sal between s.losal and s.hisal );

outer join

한개 테이블의 데이터 중심으로 다른 테이블 연결

right outer join

left outer join

full outer join

\=>  사원이 없는 부서

\=> 주문이 없는 상품

               equi join   ,  non equi join     + null

self join

         한 개 테이블 내부에서 조인

         inner join

         outer join 포함

select d.deptno, d.dname, d.loc  
    -> from dept d left outer join emp e  
    -> on(d.deptno = e.deptno)  
    -> where e.empno is null;  
+--------+------------+--------+  
| deptno | dname      | loc    |  
+--------+------------+--------+  
|     40 | OPERATIONS | BOSTON |  
+--------+------------+--------+

select e.empno, e.ename, e.mgr, m.empno, m.ename  
    -> from emp e inner join emp m  
    -> on(e.mgr = m.empno);  
+-------+--------+------+-------+-------+  
| empno | ename  | mgr  | empno | ename |  
+-------+--------+------+-------+-------+  
|  7369 | SMITH  | 7902 |  7902 | FORD  |  
|  7499 | ALLEN  | 7698 |  7698 | BLAKE |  
|  7521 | WARD   | 7698 |  7698 | BLAKE |  
|  7566 | JONES  | 7839 |  7839 | KING  |  
|  7654 | MARTIN | 7698 |  7698 | BLAKE |  
|  7698 | BLAKE  | 7839 |  7839 | KING  |  
|  7782 | CLARK  | 7839 |  7839 | KING  |  
|  7788 | SCOTT  | 7566 |  7566 | JONES |  
|  7844 | TURNER | 7698 |  7698 | BLAKE |  
|  7876 | ADAMS  | 7788 |  7788 | SCOTT |  
|  7900 | JAMES  | 7698 |  7698 | BLAKE |  
|  7902 | FORD   | 7566 |  7566 | JONES |  
|  7934 | MILLER | 7782 |  7782 | CLARK |  
+-------+--------+------+-------+-------+

select e.empno '사원번호', e.ename '사원이름', m.ename '관리자 이름'

from emp e left outer join emp m

on(e.mgr = m.empno);

+-------+--------------+------------------+  
| empno | 사원이름     | 관리자 이름      |  
+-------+--------------+------------------+  
|  7369 | SMITH        | FORD             |  
|  7499 | ALLEN        | BLAKE            |  
|  7521 | WARD         | BLAKE            |  
|  7566 | JONES        | KING             |  
|  7654 | MARTIN       | BLAKE            |  
|  7698 | BLAKE        | KING             |  
|  7782 | CLARK        | KING             |  
|  7788 | SCOTT        | JONES            |  
|  7839 | KING         | NULL             |  
|  7844 | TURNER       | BLAKE            |  
|  7876 | ADAMS        | SCOTT            |  
|  7900 | JAMES        | BLAKE            |  
|  7902 | FORD         | JONES            |  
|  7934 | MILLER       | CLARK            |  
+-------+--------------+------------------+

\*\*\*사원번호, 사원이름 ,관리자 이름을 출력(관리자가 없으면 '없음'으로 출력)

SELECT e.empno  '사원번호', e.ename  '사원이름',

IFNULL(m.ename, '없음')  '관리자 이름'

FROM emp e LEFT OUTER JOIN emp m ON e.mgr \= m.empno;

SQL

DML

        select

        기타

DDL (data definition lang.)

        데이터 저장구조 = 데이터베이스 객체

                       데이터베이스

                       테이블

        create

        alter

        drop

DCL

TCL

데이터베이스  -  테이블 담는 장소

시스템 데이터베이스 - 관리용(DBA)

               information\_schema    
               mysql             

              performance\_schema   
              sys

데이터용(사용자) 데이터베이스

확인

show databases;

생성

create database test1;

create database if not exists test2;

수정 개념 없음

삭제

drop database test1;

drop database if exists test2;

사용

use 데이터베이스명;

테이블 생성

           => 열에 대한 내용 규정

create table 테이블명 (

열이름 데이터형식(크기)옵션,

열이름 데이터형식(크기)옵션,

열이름 데이터형식(크기)옵션,

)

테이블명 / 컬럼

                 대소문자 구분 없이(리눅스는 구분) = > 소문자, \_

                 예약어 사용 X

                 식별자규칙 따름

데이터형식 - java datatype

문자열

           char - 고정형 문자열(0~ 255 byte)

           varchar - 가변형 문자(0~65535 byte) \*\*\*

           tinytext / text / mediumtext / longtext

숫자

        정수 - int / tinyint / smallint / mediumint / bigint 

        실수 - decimal / float / double

날짜

       date / datetime / time / timestamp

바이너리타입(특수목적)

create table if not exists tbl2(  
    -> col1 varchar(2)  
    -> );

\*\*\*subquery

 create table dept1 as select \* from sample.dept;

사원번호, 사원이름, 급여, 연봉,입사일자

기록되어 있는 emp\_year 테이블 생

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

from sample.emp;

+--------------+--------------+---------+----------+--------------+  
| 사원번호     | 사원이름     | 급여    | 연봉     | 입사일자     |  
+--------------+--------------+---------+----------+--------------+  
|         7369 | SMITH        |  800.00 |  9600.00 | 2010-12-17   |  
|         7499 | ALLEN        | 1600.00 | 19500.00 | 2011-02-20   |  
|         7521 | WARD         | 1250.00 | 15500.00 | 2011-02-22   |  
|         7566 | JONES        | 2975.00 | 35700.00 | 2011-04-02   |  
|         7654 | MARTIN       | 1250.00 | 16400.00 | 2011-09-28   |  
|         7698 | BLAKE        | 2850.00 | 34200.00 | 2011-05-01   |  
|         7782 | CLARK        | 2450.00 | 29400.00 | 2011-06-09   |  
|         7788 | SCOTT        | 3000.00 | 36000.00 | 2017-07-13   |  
|         7839 | KING         | 5000.00 | 60000.00 | 2011-11-17   |  
|         7844 | TURNER       | 1500.00 | 18000.00 | 2011-09-08   |  
|         7876 | ADAMS        | 1100.00 | 13200.00 | 2017-07-13   |  
|         7900 | JAMES        |  950.00 | 11400.00 | 2011-12-03   |  
|         7902 | FORD         | 3000.00 | 36000.00 | 2011-12-03   |  
|         7934 | MILLER       | 1300.00 | 15600.00 | 2012-01-23   |  
+--------------+--------------+---------+----------+--------------+

noselect

 create table emp\_dept  
    -> select empno, ename, deptno, dname, loc from sample.dept inner join sample.emp  
    -> where 1!=1;

사원번호, 사원이름,부서번호,부서이름,부서위치를 저장할 수 있는 emp\_dept 테이블 생성 스크립트(단 데이터 없음)

create table emp\_dept   
select e.empno, e.ename, e.deptno, d.dname, d.loc  
from sample.emp e  
inner join sample.dept d 

on (e.deptno = d.deptno)  
where 1!=1;

테이블 수정

컬럼 수정

              추가

              수정

                      데이터타입변경(잘 안씀)

                      데이터크기변경(small -> big ->  (데이터 없을때)   ->small)

              삭제

옵션 수정

alter table emp\_alter

rename colum job to work;

alter table emp\_alter

drop work;

show table status

           like

           where

dml

        1.select

        2.insert / update / delete

               => table 구조를 알아야 테스트 가능

insert

insert into 테이블명 values (deptno, dname, loc);

insert into dept1 values (10, 개발부, 서울);

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

컬럼명 1 : 1 값

insert into dept1 (deptno, dname , loc) values (20, '연구부', '수원');insert into dept1 (dname, deptno, loc) values('연구부', 20, '수원');

bulky

insert into dept1 values (20, '연구부', '대전')

insert into dept1 values (30, '기획부','부산')

insert into dept1 values (40, '생산부','광주')

insert into dept1 values (20, '연구부','대전'), (30, '기획부','부산'), (40, '생산부','광주')

insert into dept1 select \* from sample.dept;

필수 입력 : not null

옵션 입력 : null

전부 null을 허용

create table dept2 (

deptno int(2),

dname varchar(14),

loc varchar(13)

);

create table dept3 (

deptno int(2) not null,

dname varchar(14) not null,

loc varchar(13) not null

);

디폴트 값

create table dept2 (  
    -> deptno int(2) default 90,  
    -> dname varchar(14),  
    -> loc varchar(13)  
    -> );

insert into dept2 values (default, '개발부', '서울');

전체행 변경

update 테이블명 set 컬럼명 = 값....

특정행 변경

update 테이블명 set 컬럼명 = 값.... where 조건

급여가 1000에서 2000사이인 사원들의 부서번호를 30으로 변경

\*delete전체행 삭제delete from 테이블명

특정행 삭제delete from 테이블명 where 조건

1\. 테이블 생성

2\. insert

3\. select

         join