-- 제약조건 삭제
alter table member drop constraint PK_M_ID; -- oracle 기준
alter table member drop primary key; -- auto_increment 시 삭제 불가
alter table member drop constraint UQ_M_T;
PK에 Auto_increment를 설정했을 시, 삭제가 불가능하다.
-- 제약조건 설정(추가)
alter table member add constraint PK_M_ID primary key (M_ID); -- oracle 기준
alter table member add primary key(M_ID);
alter table member add constraint UQ_M_TEL unique(M_tel);
제약 조건을 줄 때 이름을 설정해야 나중에 삭제해야 할 때 용이하다.
-- not null/ null 수정
-- alter table member modify M_TEL null; -- oracle 기준
alter table member modify M_TEL char(12) null; -- 타입도 변경 가능
alter table member modify M_TEL char(11) not null;
-- 기본값 변환
alter table member modify M_age int default 1;
alter table member modify M_age tinyint unsigned default 1;
타입을 꼭 적어야 하고, 다른 타입으로 적을 시 타입이 변경된다. (Oracle은 안 적어도 된다)
맨 위의 코드는 참조되는 member(부모 테이블)에 없는 ID라서 에러가 뜨고 저장되지 않는다.
두번 코드처럼 여러개를 동시에 추가할 수 있다.
-- 'A' 회원 탈퇴
delete from member where M_ID='A'; -- 외래키 때문에 안 됨
delete from member where M_ID='D'; -- 자식 테이블에서 글이 없기 때문에 성공
delete from board where B_MID='A'; -- 자식 레코드(데이터) 삭제
delete from member where M_ID='A'; -- 자식 레코드(데이터)를 삭제했기 때문에 삭제 성공
-- member(부모) 테이블 삭제
drop table member; -- 자식 테이블이 있기 때문에 안 됨
MEMBER 테이블을 BOARD 테이블에 외래키로 넣어놨기 때문에 BOARD 테이블에서 데이터를 사용했는데 그냥 삭제하려고 하면 삭제되지 않는다.
create table MEMBER(
M_ID varchar(20),
M_PW varchar(20) not null,
M_NAME varchar(20) not null,
M_TEL varchar(13) not null,
M_EMAIL varchar(30) not null,
constraint primary key PK_M_ID (M_ID),
constraint unique UQ_M_E (M_EMAIL)
);
NULL 값을 허용하지 않는다고 했으니 PRIMARY KEY를 넣을 M_ID를 제외한 칼럼에 NOT NULL을 입력했다.
테이블 단에서 PK를 설정할 때 MySQL은 이름을 정해줄 수 없지만 Oracle에서는 정할 수 있으므로 연습하는 겸 이름도 적었다.
create table PRODUCT(
P_NO int auto_increment,
P_NAME varchar(30) not null,
P_PRICE int unsigned not null default 10000,
P_Inven int unsigned not null default 0,
P_Intro text not null,
constraint primary key PK_P_NO (P_NO)
);
select*from dept; -- 부모 테이블 1개
select*from emp; -- 자식 테이블 n개(여러개 가능)
select*from salgrade;
join: 두 개 이상의 테이블을 행끼리 결합하는 DB의 꽃
inner(내부) join: 양 쪽(테이블) 모두에 값이 있는 행 반환(not null)
기본이 inner join이기 때문에 'inner' 생략 가능!
모호성(ambiguous)에 주의할 것! 테이블명에 알리어스(as)를 넣어 별칭을 지어주기.
만족하는 모든 행을 결합(join)한다.
-- 표준(ANSI) SQL 조인
select * from dept D
join emp E
on D.deptno=E.deptno -- 조인조건 / 동일조인
where JOB='CLERK'; -- 일반조건
표준(ANSI) SQL 조인
join [join 할 테이블]
on [join 할 조건]
-- 비표준 SQL 조인
select * from dept D, emp E
where D.deptno=E.deptno;
select * from dept D, emp E
where D.deptno=E.deptno and job='CLERK';
join을 적지 않고 join하는 방법
from에 테이블을 여러개 적고, join 조건을 where절에 적는다.
밑의 코드는 where절에 join조건과 일반 조건이 함께 들어있다. where [join 조건] and [일반 조건]
예제 ㄱ
-- 테이블 2개 조인
select e.ename, e.sal, concat(s.grade, '호봉') grade
from salgrade s
join emp e on e.sal between s.losal and s.hisal; -- 비동일 조인(>= <=)
-- 테이블 3개 조인
select* from dept d
join emp e on d.deptno=e.deptno
join salgrade s on e.sal between s.losal and s.hisal;
-- 3번
select e.ename, e.sal, s.grade, s.stdsal from emp e
join salgrade2 s on truncate(e.sal,-3)=truncate(s.stdsal,-3);
인라인뷰(view): from절에 서브쿼리가 들어간 것
하드디스크가 아닌 메모리 상에서만 존재하는 논리적인 테이블
인라인끼리 혹은 내/외부 조인과도 같이 조인이 가능하다.
-- 10번 부서와 20번 부서 사원 중에서 직급이 같은 사원들의 이름, 부서번호, 직급을 검색
select e1.job, e1.deptno, e1.ename, e2.deptno, e2.ename
from (select ename, deptno, job from emp where deptno=10) e1
join (select ename, deptno, job from emp where deptno=20) e2
where e1.job=e2.job;
select e.empno '사원번호', e.ename '사원 명', e.mgr '상사번호', m.ename '상사'
from emp e join emp m on e.mgr=m.empno order by e.empno; -- 상사가 없는 경우 검색되지 않음
self join: 내부 조인. 같은 테이블끼리 join한다.
해당 코드는 상사가 없는 경우엔 출력되지 않는다.
select e.empno '사원번호', e.ename '사원 명', e.mgr '상사번호', m.ename '상사'
from emp e left join emp m on e.mgr=m.empno order by e.empno;
잉여 데이터가 있는 경우에도 출력할 수 있는 Left / Right Join
Left / Right outer join: 외부 조인. 반대쪽에 데이터가 있든 없든(null), 선택된 방향에 있으면 출력 - 행 수 결정 (Left join: 오른쪽에 잉여데이터 / Right join: 왼쪽에 잉여데이터)
-- 1) comm을 받는 사원의 부서명, 부서위치, 이름, 커미션(comm) 검색
-- 2) 'dallas'(부서위치)에서 근무하는 사원 부서번호, 부서명, 이름, 직급 검색
-- 3) 'ward'(사원이름)의 부서이름과 이름 검색
-- 4) 사원이름, 급여, 급여등급 검색
-- 5) 부서명, 사원이름, 급여등급 검색
-- 6) 부서별 부서이름, 최소급여, 최대급여, 평균급여 검색
-- 7) 부서 내에서 직급별 부서번호, 부서명, 직급, 평균급여, 인원수 검색
풀이
-- 1) comm을 받는 사원의 부서명, 부서위치, 이름, 커미션(comm) 검색
select d.dname, d.loc, e.ename, e.comm from dept d
join emp e on d.deptno=e.deptno where ifnull(comm,0)<>0;
부서명, 부서위치, 이름, 커미션은 검색했으나 comm을 받는 사람만 출력하는 것에서 실패하고 시간이 끝났다.
부서명, 부서위치가 한 테이블, 사원이름, 커미션이 한 테이블로 각 두 테이블이 있으니 join을 했다.
join 조건은 두 테이블 모두 동일하게 가지고 있는 deptno
comm을 받는 사원이 조건이므로 where절에 comm이 null이 아닐 때라는 조건을 준다.
ifnull(comm,0)<>0은 comm<>0으로도 변경이 가능하다.
-- 2) 'dallas'(부서위치)에서 근무하는 사원 부서번호, 부서명, 이름, 직급 검색
select d.deptno, d.dname, e.ename, e.job from dept d
join emp e on d.deptno=e.deptno where d.loc='dallas';
부서명은 dept 테이블에 있고 / 사원이름, 사원직급은 emp 테이블에 있고 / deptno는 두 테이블 모두에 있으므로 dept 테이블과 emp 테이블을 join해주면서 deptno를 join 조건으로 줬다.
일반 조건으로는 부서위치가 'dallas'인 조건이 있으므로 dept 테이블에 있는 loc(부서위치)가 'dallas'인 것만 이라는 조건을 준다.
-- 3) 'ward'(사원이름)의 부서이름과 이름 검색
select d.dname, e.ename from dept d join emp e on d.deptno=e.deptno where e.ename='ward';
부서명은 dept 테이블, 사원 이름은 emp 테이블에 있으므로 두 테이블을 join해주고, 조건은 동일하게 가지고 있는 칼럼인 deptno를 준다.
'ward'의 부서명과 이름을 검색하라고 했으니 emp 테이블에 있는 ename의 값이 'ward'인 것만 이라는 조건을 준다.
-- 4) 사원이름, 급여, 급여등급 검색
select e.ename, e.sal, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
사원이름은 emp 테이블, 급여등급은 salgrade 테이블에 있으므로 두 테이블을 join해준다.
join 조건으로는 emp 테이블에 있는 sal이 salgrade 테이블에 있는 losal과 hisal의 사이일 경우로 준다.
-- 5) 부서명, 사원이름, 급여등급 검색
select d.dname, e.ename, s.grade from dept d
join emp e on d.deptno=e.deptno join salgrade s on e.sal between s.losal and s.hisal;
부서명, 사원이름, 급여등급이 각 다른 테이블에 있기 때문에 각 테이블을 모두 join해준다.
join 조건은 dept에 emp를 join할 때는 deptno가 동일할 경우로 주고, salgrade를 join할 때는 emp의 sal이 salgrade의 losal과 hisal의 사이일 경우로 준다.
-- 6) 부서별 부서이름, 최소급여, 최대급여, 평균급여 검색
select d.dname '부서명', min(e.sal) '최소급여', max(e.sal) '최대급여', round(avg(e.sal),2) '평균급여'
from dept d join emp e on d.deptno=e.deptno
group by d.deptno order by d.dname;
부서명은 dept 테이블, 나머지는 emp 테이블에서 구하면 되기 때문에 dept 테이블과 emp 테이블을 join해준다.
join 조건은 deptno가 동일할 경우로 준다.
최소 급여, 최대 급여, 평균 급여 값은 각 min, max, avg 함수를 사용하여 구한다.
-- 7) 부서 내에서 직급별 부서번호, 부서명, 직급, 평균급여, 인원수 검색
select d.deptno '부서번호', d.dname '부서명', e.job '직급',
round(avg(e.sal),2) '평균급여', count(e.job) '인원수'
from dept d join emp e on d.deptno=e.deptno group by e.deptno;
select round(avg(e.sal)) '평균급여', concat(count(*), '명') '인원 수', d.deptno, d.dname, e.job
from dept d join emp e on d.deptno=e.deptno
group by d.deptno, e.job, d.dname;
위에 코드가 내가 풀다가 실패한 코드이고, 밑에 코드가 선생님이 풀이해주신 코드이다.
내가 코드를 짤 때 그룹으로 묶을 것을 적게 줘서 오류가 났었다.
우선 필요한 테이블인 dept와 emp를 join해주고 조건은 두 테이블 모두에 있는 deptno가 동일할 경우로 준다.
그룹화를 해야 하는데 select에 여러 칼럼을 적었으므로 적은 칼럼을 모두 group by에 적는다. (SQL에선 d.dname은 그룹으로 안 묶어도 실행된다. Oracle에서는 안 됨!)
-- 문)
-- 본인의 사원 이름, 부서번호를 검색하고,
-- 같은 부서의 동료사원의 사원이름, 부서번호를 검색하시오
-- 이름순으로 오름정렬하시오
-- 실행예
-- 본인 부서번호 동료 부서번호
-- allen 30 blake 30
-- allen 30 james 30
-- ...
-- blake 30 allen 30
select e.ename '본인', e.deptno '부서번호', d.ename '동료', d.deptno '부서번호'
from emp e join emp d on e.deptno=d.deptno where e.ename<>d.ename order by e.ename, d.ename;
같은 테이블(emp) 내의 값이 필요하므로 self join을 해준다.
join 조건은 같은 부서의 동료 사원이 필요하므로 deptno로 주고, 본인이 나오면 안 되기 때문에 일반 조건에 첫번째 ename과 두번째 ename이 같을 경우를 제외해준다.
이름순으로 오름정렬하라고 했기 때문에 order by에 첫번째와 두번째 ename을 모두 적어준다.
drop table if exists member;
create table member(
M_id int auto_increment primary key,
M_nick varchar(20) unique not null,
M_name varchar(20) not null,
M_age tinyint unsigned default 0 not null,
M_birth date default (current_date) not null
);
desc member;
select*from member;
테이블을 만들기 전, 테이블이 이미 있을 수 있으므로 테이블이 존재한다면, 삭제한다는 코드를 추가한다. (drop table if exists)
desc member: 테이블 구조를 확인한다.
select*from member: 테이블에 있는 모든 칼럼과 데이터를 확인한다.
current_date / curdate는 아직 잘 모르는 코드
생성 시 제약
auto_increment: 새 행 생성 시마다 자동으로 1씩 증가 (1부터 시작)
primary key: 중복 입력이 불가능하고, null(빈 값)이 불가능하다. 테이블에 하나만 존재할 수 있다.
update member set M_name='n번째친구'; -- 1
update member set M_name='n번째친구' where M_id=1; -- 2
-- update member set M_age+=10; -- 3
update member set M_age=M_age+10 where M_id=3; -- 4
-- 첫번째 나이 10% 증가
update member set M_age=M_age*1.1 where M_id=1;
1번 코드처럼 작성하면 member 테이블에 있는 M_name은 모두 'n번째친구'로 데이터가 바뀌어버린다.
2번 코드처럼 where절에 조건을 줘서 바꿔줘야 한다.
3번 코드처럼 자바에서 사용하던 += 연산은 불가능하다. 10을 더하고 싶다면 4번 코드처럼 작성해야 한다.
마지막 코드는 %를 사용하지 못하기 때문에 곱하기(*)를 이용하여 계산해주어야 한다.
where절에서 조건을 줄 때 primary key로 주는 것이 바꾸려는 안전하게 데이터만 바꿀 수 있고, 에러가 나지 않을 수 있다. (중복값이 있을 수 있기 때문에)
-- 각 부서별 인원수, 최대 급여
select min(sal), max(sal), sum(sal)/count(sal) as 평균, round(avg(sal),1), count(sal) from emp;
group by: 칼럼을 그룹으로 묶는다. 여러 칼럼으로 그룹을 묶을 수 있다.
having: group by 이후 조건 따지기
select deptno 부서번호, min(sal) min, max(sal) max, round(avg(sal),2) as 평균, count(sal) '사원 수'
from emp
group by deptno order by deptno;
select*from emp group by deptno; -- 부서번호를 그룹으로 했는데 모든 칼럼을 출력하려고 해서 에러
select에 칼럼을 적으려는 경우, 그룹화 한 칼럼만 적을 수 있다.
그룹화하지 않은 다른 칼럼을 적을 경우 에러가 난다.
문제)
-- 문) 부서별 근무 인원 출력 / 출력 예) 10 3명
select deptno, concat(count(deptno), '명') as 인원 from emp group by deptno order by deptno;
select deptno, concat(count(*), '명') as 인원 from emp group by deptno order by deptno;
count(*): 모든 칼럼을 비교해서 null값이 아닌 행의 수를 측정한다.
예제)
-- 10번 부서를 제외한 부서별 근무 인원 출력
select deptno, concat(count(*), '명') as 인원 from emp
where deptno<>10 group by deptno order by deptno;
-- 10번 부서 제외한 부서, 직급 별 근무 인원, 2명 이상
select deptno, job, concat(count(*), '명') as 인원 from emp
where deptno<>10 group by deptno, job having count(*)>1 order by deptno, job;
문제)
-- 문) 부서 내 직급별 인원수와 최대급여 검색, 부서번호 오름차순 검색
select deptno '부서번호', job '직급', count(*) '인원 수', max(sal) '최대급여'
from emp group by deptno, job order by deptno;
select deptno '부서번호', job '직급', count(*) '인원 수', max(sal) '최대급여'
from emp group by deptno, job with rollup;
with rollup: count, min 등 select에서 사용한 칼럼 그룹 단위로 집계 값을 출력한다.
단, order by와 같이 사용하지 못한다.
-- 부서별 입사일 1981-01-01 이후 입사한 사원이 2명 이상인 경우
select deptno '부서', count(*) '인원' from emp
where hiredate>'1981-01-01' group by deptno having count(*)>1;
hiredate가 날짜이기 때문에 문자열로 '1981-01-01'이라고만 해도 비교가 된다.
부서 별 2명 이상은 그룹을 묶은 후 비교를 할 수 있기 때문에 having으로 조건을 적는다.
-- 급여가 1000이상인 사원 중에서 부서별 중위급여가 2500미만이고
-- 평균 급여가 1200~2000 사이인 최대급여, 최소급여, 중위급여, 평균급여 검색
select deptno '부서', max(sal), min(sal),
round((max(sal)+min(sal))/2,2) '중위급여',
round(avg(sal),2) '평균급여', count(*) '인원'
from emp
where sal>=1000
group by deptno
having (max(sal)+min(sal))/2<2500 and avg(sal) between 1200 and 2000;
-- job이 salesman이 아닌 사원 중에서 job별로 급여 합계가 5000이상인 직급, 급여합계를 검색
-- 단, 급여 합계 내림정렬
select job '직급', sum(sal) '급여합계' from emp
where job<>'salesman' group by job having sum(sal)>=5000 order by '급여합계' desc;
상관(연관) 서브쿼리: 메인쿼리와 서브쿼리가 관계를 맺음, 서브쿼리 내에 메인쿼리의 칼럼 존재
처리 순서: 메인쿼리 -> 서브쿼리 -> 메인쿼리
예제)
-- jones가 근무하는 부서 이름 검색
select dname from dept where deptno=(select deptno from emp where ename='jones');
where에서 서브쿼리로 'jones'의 'deptno'를 검색(선택)하고 그것을 조건으로 사용했다.
문제)
-- 평균급여보다 많이 받는 사원 검색
select ename, sal from emp where sal>(select avg(sal) from emp);
-- jones보다 급여를 많이 받는 사원 검색 / 단, 급여 내림차 순
select ename, sal from emp where sal>(select sal from emp where ename='jones')
order by sal desc;
예제)
-- 10번 부서에서 근무하는 사원의 이름과 10번 부서의 부서명, 부서번호를 출력
select d.deptno 부서번호, d.dname 부서명, emp.ename '사원 명'
from (select deptno, dname from dept where deptno=10) d, emp order by ename;
select*from emp e, (select deptno, dname from dept where deptno=10) d where e.deptno=d.deptno;
-- 10번 부서와 20번 부서 사원 중에서 직급이 같은 사원들의 이름, 부서번호, 직급을 검색
select e1.job, e1.deptno, e1.ename, e2.deptno, e2.ename
from (select ename, deptno, job from emp where deptno=10) e1,
(select ename, deptno, job from emp where deptno=20) e2
where e1.job=e2.job;
-- 직급별로 직급, 최대급여를 출력하되, 최대 급여가 salseman의 최대급여보다 큰 그룹만 출력
select job, max(sal) maxsal from emp group by job
having max(sal)>(select max(sal) from emp where job='salesman');
-- blake와 같은 부서에 있는 사원 이름, 입사일 출력 / 단, 본인 제외
-- blake가 서로 다른 부서에 있을 수 있다.
select ename, hiredate
from emp
where deptno in(select deptno from emp where ename='blake') and ename<>'blake';
'blake'가 동명이인으로 서로 다른 부서에 있을 수 있다는 것을 가정한 조건이 붙었다.
in을 통해 여러 조건 중 하나를 만족하면 조건에 충족하는 것으로 하고 서브쿼리를 통해 'blake'의 'deptno'를 검색/선택해왔다.
예제)
select (select dname from dept d where d.deptno = e.deptno) as dname, empno, ename
from emp e;
~ all: 서브쿼리의 모든 결과에 대해 ~하다.
~ any: 서브쿼리의 하나 이상의 결과에 대해 ~하다.
select ename, sal, deptno from emp
where deptno = any(select deptno from emp where sal>=3000);
where deptno in(select deptno from emp where sal>=3000); < 해당 코드와 두번째 코드가 동일하다.
in도 any도 괄호 안의 코드(값) 중 하나라도 만족하면 조건에 충족한다.
select ename, sal from emp
where sal>all(select sal from emp where deptno=30);
where sal>(select max(sal) from emp where deptno=30); < 해당 코드와 두번째 코드가 동일하다.
inner(내부) join: 양 쪽(테이블) 모두에 값이 있는 행(not null) 반환 / inner 생략 가능
-- 사원 이름, 호봉, 부서명 검색
select d.deptno, d.dname, e.ename, e.sal
from emp e join dept d on d.deptno=e.deptno
join salgrade s on e.sal between s.losal and s.hisal;
on에는 테이블을 합하는 join의 조건을 적는다.
해당 코드에서는 dept를 emp에 합할 때, 동일하게 가지고 있는 열인 deptno를 이용하여 합한다.
전체 피드백
밥 먹고 나서 집중력이 급격히 떨어지는 바람에 활용하는 부분에서 많이 헤맨 것 같다.
초반에는 쉬웠는데 역시 갈수록 많이 어려워지는 것 같다. db랑 자바랑 연결할 때도 크게 헤맬 것 같은 기분이 든다.
