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