CODO Day33_DB(Constraint/Relation/Sub Query)

CONSTRAINT(제약조건)

기본키 제약조건(PK - Primary Key)

• 컬럼에 데이터를 넣을 때 특정조건을 만족시켜야 하는 경우 사용
• no : 중복 X / Null X(PRIMARY KEY)
• name : 중복 O / Null X
• 테이블 종속적(빌려줄 수 없음, 테이블이 사라지면 함께 사라짐, 테이블에 1개만 가능) ex) 신체
• 하나 이상의 컬럼으로 구성됨(최대 16개)

 

생성방법

• 테이블 생성시 함께 생성(많이 사용)*

create table pk_test(
	first_col int(3) primary key,
	second_col varchar(4)
);

desc pk_test;

insert into pk_test (first_col)values(1);
-- Error : Field 'first_col' doesn't have a default value : first_col null
insert into pk_test (second_col)values('test');
-- Error : Duplicate entry '1' for key 'PRIMARY' / Duplicate* : 중복값이 있을 시
insert into pk_test (first_col)values(1);

 

• 기존 테이블에 추가
  • ALTER TABLE [테이블명] ADD CONSTRAINT [제약조건 종류] (대상컬럼);

alter table employees add constraint primary key (emp_no);
alter table employees add primary key (emp_no);
desc employees;

 

• 중복값 추가

-- Error : Duplicate entry '112' for key 'PRIMARY'
insert into employees (emp_no, first_name, family_name, email, mobile, salary)
values(112,'태근','김','email@naver.com','01020343054',99999999);

-- upsert(key가 없으면 insert, 있으면 update)
insert into employees (emp_no, first_name, family_name, email, mobile, salary)
values(112,'태근','김','email@naver.com','01020343054',99999999)
on duplicate key update first_name = '태곤', family_name = '박';

 

• 복합키(한개 이상의 컬럼으로 키를 생성 - 사용지양)
  • 복합키에 추가되는 컬럼이 외래키일 경우 upodate나 insert에 제한을 줄 수 있음

create table pk_two_test(
	first_col int(5),
	second_col varchar(10),
	third_col date,
	primary key(first_col,second_col)
);

 

 

NOT NULL(NULL을 허용하지 않음)

• NOT NULL은 컬럼의 속성으로 취급

create table pk_two_test(
	first_col int(5),
	second_col varchar(10),
	third_col date not null,
	primary key(first_col,second_col)
);
-- 컬럼속성 변경
-- ALTER TABLE [테이블명] MODIFY COLUMN [컬럼명] [데이터타입](사이즈) NOT NULL;
alter table pk_test modify column second_col varchar(4) not null;
select * from pk_test;
delete from pk_test;  -- delete : 데이터만 삭제 / 테이블 남김
desc pk_test;

 

 

Relation

• RDBMS의 R은 Relation의 약자
• 테이블 간에 “부모-자식” 관계 가능

Written by zer0box@naver.com

• O  : 0개
•  |   : 1개
• ㅌ : 1개 이상

 

연결되는 선은 실선과 점선에 따라 의미가 다름

• 실선(식별 관계) : 부모의 primary key를 자식이 primary key로 사용
• 점선(비식별 관계) : 부모의 primary key를 자식이 primary key로 사용 X 

Written by zer0box@naver.com

 

 

참조 제약 조건 : FOREIGN KEY(외래키)

• 다른(외부) 테이블의 PK를 가져와(참조 - references) 사용

-- 1) 만들면서 설정
-- 부모먼저 생성
create table parent_table(
	user_id varchar(30) primary key
	,user_name varchar(20)	
	,user_phone varchar(20)
	,user_addr varchar(20)
);
desc parent_table;

-- 자식 생성
create table child_table(
	oder_id int(10)
	,user_id varchar(30)
	,product_name varchar(20)
	,price int(10)
	,qty int(5)
	-- ,foreign key(user_id) references parent_table(user_id)
);
desc child_table;

-- 2) 이미 만들어진 테이블에 추가
-- ALTER TABLE [테이블명] ADD CONSTRAINT [제약조건 타입] (컬럼) REFERENCES [참조테이블](참조컬럼)
alter table child_table add constraint foreign key (user_id) references parent_table(user_id);

-- parent_table의 PK인 user_id를 child_table에서 가져다 쓰는 경우를 '비식별'관계라고 함
-- iden_table은 parent_table의 PK를 자신의 PK로 사용할 경우 '식별'관계라고 함
create table iden_table(
	user_id varchar(30) primary key
	,user_no varchar(20)
	,foreign key(user_id) references parent_table(user_id)
);
desc iden_table;

 

 

연계 참조 무결성 제약조건

• 부모자식(연계참조) 간에 논리적(무결성)으로 말이 되게 만들어주는 제약조건
• ex) 부모가 없는데 자식이 있을 수 없음/부모가 없어질 경우 자식도 제거

-- 부모
create table supplier(
	supplier_id int(10) primary key
	,supplier_name varchar(50) not null
	,phon varchar(12)
);
alter table supplier rename column phon to phone;

-- 자식
drop table products;
create table products(
	product_id int(10) primary key
	,supplier_id int(10)
	,product_price int(10)
	,foreign key(supplier_id) references supplier(supplier_id) on delete cascade
);

-- 부모데이터
insert into supplier (supplier_id,supplier_name,phone) values(1,'김철수','02-123-1234');
insert into supplier (supplier_id,supplier_name,phone) values(2,'홍길동','032-568-0078');
insert into supplier (supplier_id,supplier_name,phone) values(3,'박영수','042-323-3234');
select * from supplier;

-- 자식데이터
insert into products (product_id,supplier_id,product_price) values(1111,1,6000);
-- 컬럼의 순서와 동일하고 모든 컬럼에 값을 넣을 경우 컬럼명 생략 가능 (추천x - 테스트용으로 사용)
insert into products values(1112,2,7000);
insert into products values(1113,3,8000);
-- 4번 supplier_id는 부모에 없는값으로 추가 불가능
insert into products values(1114,4,9000);

-- 자식이 있는 부모를 지우기
-- Error : Cannot delete or update a parent row
-- 자식 있는 부모가 지워지면 부모 없는 자식이 발생하므로 연계 참조 무결성에 위배됨
-- 위배하지 않기 위해서는 부모를 참조한 자식들이 데이터를 하나씩 쫓아다니면서 지워야함
-- 힘든 작업으로 연계참조 무결성 제약조건을 활용
-- on delete cascade - 자식 테이블 생성 시 부모참조할 때 추가하면 부모테이블과 함께 지워짐
delete from supplier where supplier_id = 1;
select * from supplier;
select * from products;

 

 

Unique 제약조건

• 중복허용X
• unique + not null = primary key >> unique : 후보키(언제든 키가 될 수 있음)
• null 허용
• 테이블에 여러개 둘 수 있음

-- ALTER TABLE [테이블명] ADD CONSTRAINT [제약조건타입](적용컬럼)
alter table supplier add constraint unique (supplier_name);
-- Duplicate entry '박영수' for key 'supplier_name' - 중복허용x
insert into supplier values(4,'박영수','02-123-1234');
desc supplier;

 

 

Check 제약조건(사용x)

• 조건에 맞지 않으면 데이터를 받지 않음

-- ALTER TABLE [테이블명] ADD CONSTRAINT [제약조건타입](적용 컬럼 조건)
alter table products add constraint check(product_price between 5000 and 10000);
desc products;
insert into products values(1114,2,9000);
-- 조건에 맞지 않으면 들어가지 않음
insert into products values(1115,2,11000);

-- check 제약조건 사용하지 않는 이유
-- 1. 제약조건에 대한 표시가 없음(조건을 알아내기도 어렵)
select * from  information_schema.table_constraints where TABLE_NAME = 'products'	;
-- 2. 조건이 바뀌었을 경우 제약조건을 추가해줘야함

 

 

 

Sub Query

• “쿼리 안의 쿼리”라는 의미
• 서브쿼리는 사전에 추출된 내용에서 재검색 하거나, 다른 테이블에서 추출된 내용을 가져와 쓸 때 사
• 가져온 데이터를 재정제하기 위해 사용

 

• 테이블 생성

-- 부서 테이블
create table dept(
	deptno varchar(10)	 primary key
	,deptname varchar(20)
	,loc varchar(10)
);

-- 직원 테이블
create table emp(
	ename varchar(20)
	,job varchar(50)
	,deptno varchar(10)
	,hiredate date
);

alter table emp add constraint foreign key(deptno) references dept(deptno);

 

• 데이터 삽입

insert into dept(deptno,deptname,loc)values(1,'sales','newyork');
insert into dept(deptno,deptname,loc)values(2,'dev01','LA');
insert into dept(deptno,deptname,loc)values(3,'pernonnel','newyork');
insert into dept(deptno,deptname,loc)values(4,'delevery','boston');

select * from dept;

insert into emp(ename,job,deptno,hiredate)
values('kim','manager',1,str_to_date('16/01/02','%Y/%m/%d'));
insert into emp(ename,job,deptno,hiredate)
values('lee','staff',1,str_to_date('15/01/02','%Y/%m/%d'));
insert into emp(ename,job,deptno,hiredate)
values('han','staff',1,str_to_date('16/03/02','%Y/%m/%d'));
insert into emp(ename,job,deptno,hiredate)
values('kim','assistant',1,str_to_date('15/09/22','%Y/%m/%d'));
insert into emp(ename,job,deptno,hiredate)
values('ahn','staff',2,str_to_date('15/11/02','%Y/%m/%d'));
insert into emp(ename,job,deptno,hiredate)
values('hwang','manager',2,str_to_date('15/08/12','%Y/%m/%d'));
insert into emp(ename,job,deptno,hiredate)
values('cha','assistant',2,str_to_date('12/03/02','%Y/%m/%d'));
insert into emp(ename,job,deptno,hiredate)
values('hong','staff',2,str_to_date('14/08/02','%Y/%m/%d'));
insert into emp(ename,job,deptno,hiredate)
values('gang','staff',2,str_to_date('16/01/02','%Y/%m/%d'));
insert into emp(ename,job,deptno,hiredate)
values('nam','leader',4,str_to_date('10/01/02','%Y/%m/%d'));

-- 데이터 확인
select * from dept;
select * from emp;

 

• 문제 1> han이 일하는 근무 부서는? 

-- sales : emp에서 han 찾기 > han의 deptno 확인(1) > dept에서 deptno가 1인 값 찾기
-- han?
select * from emp where ename = 'han';
-- deptno = 1 ?
select deptname from dept where deptno = 1;
-- 서브쿼리
select deptname from dept where deptno = (select deptno from emp where ename = 'han');

 

• 문제 2> 부서위치가 LA나 boston인 부서에 속한 사람들의 이름과 직책은?

-- select deptno from dept where loc in ('LA','boston');
-- select ename,job from emp where deptno in (2,4);
select ename,job from emp where deptno in (select deptno from dept where loc in ('LA','boston'));

 

• 문제 3> sales 부서에서 일하는 사원의 전체 데이터는?

-- select deptno from dept where deptname = 'sales';
-- select * from emp where deptno = 1;
select * from emp where deptno = (select deptno from dept where deptname = 'sales');

 

• 문제 4> 직책(job)이 manager인 사원들(여러명일 경우 가장 빠른 날짜 기준)보다 입사일이 빠른 직원은?

-- min : 최솟값
select min(hiredate) from emp where job = 'manager';  -- 2015-08-12
select * from emp where hiredate < '2015-08-12' order by hiredate;
select * from emp where hiredate < (select min(hiredate) from emp where job = 'manager') order by hiredate;

 

• 문제 5> 부서별로 직원이 몇명인지 데이터 가져오기

-- deptno별로 몇명인지 확인
select deptno,count(deptno) as dept_cnt from emp group by deptno;
-- deptno별로(1,2,4) 이름 확인
select deptname from dept where deptno in(1,2,4);
-- 서브쿼리
-- 상하관계 쿼리 : 서브쿼리가 메인쿼리 결과물에 일부분으로 소속되는 것
-- emp e : 테이블에 별칭주기
select (select d.deptname from dept d where d.deptno = e.deptno) as 부서, count(deptno) as 사원수 
from emp e group by deptno;

=================================================================================================

-- group by 사용x
-- deptno가 1,2,4일 경우 각각의 수를 확인
select count(deptno) from emp where deptno = 1;
select count(deptno) from emp where deptno = 2;
select count(deptno) from emp where deptno = 4;

-- deptno 별 정보
select deptname from dept where deptno = 1;
select deptname from dept where deptno = 2;
select deptname from dept where deptno = 4;

-- 상하관계 쿼리
select deptname,(select count(deptno) from emp where deptno = 1) from dept where deptno = 1;
select deptname,(select count(deptno) from emp where deptno = 2) from dept where deptno = 2;
select deptname,(select count(deptno) from emp where deptno = 4) from dept where deptno = 4;

================================================================================================

-- 단순화
select deptname,(select count(deptno) from emp where deptno = d.deptno) as cnt from dept d;