1. TABLE
- row(=record) : 테이블의 행
- column(=field) : 테이블의 열
--예시) create table member( mem_id char(8) not null primary key, mem_name varchar(10) not null, mem_number tinyint not null, addr char(2) not null, phone1 char(3) null, phone2 char(8) null, height tinyint unsigned null, debut_date date null );
2. TABLE WITH CONSTRAINT
(1) primary key
--예시1) create만 사용 create table member( mem_id char(8) not null primary key, mem_name varchar(10) not null, height tinyint unsigned null );--예시2) alter 사용 create table member( mem_id char(8) not null, mem_name varchar(10) not null, height tinyint unsigned null ); alter table member add constraint primary key(mem_id);
(2) foreign key
--예시1) create만 사용 create table buy( num int auto_increment not null primary key, mem_id char(8) not null, prod_name char(6) not null, foreign key (mem_id) references member(mem_id) );--예시2) alter 사용 create table buy( num int auto_increment not null primary key, mem_id char(8) not null, prod_name char(6) not null ); alter table buy add constraint foreign key (mem_id) references member(mem_id);
member 테이블과 buy 테이블이 PK-FK로 연결되어 있다. 이때 member을 기준테이블, buy를 참조테이블이라고 한다. 기준테이블과 참조테이블이 'KEY'로 연결되어 있기 때문에 해당 열을 바꾸면 오류가 날 것이다. 하지만 기준테이블의 PK 열의 데이터를 바꿀 때 연결된 참조테이블도 자동으로 바꿔줄 수 있다.
--예시3) 기준테이블을 PK 열의 데이터가 바뀔 때 참조테이블의 FK 열의 데이터가 바뀌도록 테이블 만들기
create table buy(
num int auto_increment not null primary key,
mem_id char(8) not null,
prod_name char(6) not null
);
alter table buy
add constraint foreign key (mem_id) references member(mem_id)
on update cascade
on delete cascade;(3) unique
중복되지 않는 유일한 값으로 기본키(primary key)와 비슷하지만, null값을 수용한다는 점과 동 테이블에 고유키(unique)를 여러 개 설정할 수 있다는 점이 다르다.
--예시) 이메일이 같은 데이터 추가 불가, null 수용 create table member( mem_id char(8) not null primary key, mem_name varchar(10) not null, height tinyint unsigned null, email char(30) null unique );
(4) check
제약조건이 입력되는 데이터를 점검한다.
--예시) 키는 100을 넘는 입력값만 받고, phone1은 02, 031, 032, 055, 061만 입력받는다. create table member( mem_id char(8) not null primary key, mem_name varchar(10) not null, height tinyint unsigned null check (height>=100), phone1 char(3) null ); alter table member add constraint check (phone1 in ('02', '031', '032', '055', '061'));
(5) default
값을 입력하지 않았을 떄 자동으로 입력될 값을 지정하는 명령어다.
--예시) 키의 default값은 160 create table member( mem_id char(8) not null primary key, mem_name varchar(10) not null, height tinyint unsigned null default 160 ); insert into member3 values ('RED', '레드벨벳', default);
3. VIEW
VIEW를 사용하는 이유는 보안에 있다. 지금은 root 사용자로 접속하기 때문에 테이블의 생성, 삭제, 조작에 대해 강한 권한이 있다. 하지만 일반 회사의 경우 모든 직원에게 이러한 권한을 부여한다면 데이터에 신뢰성도 떨어질 뿐더러 고객 데이터의 노출 위험이 상당히 크다. 또한 복작합 코드를 일일히 칠 필요 없이 VIEW를 만들어 놓으면 코드를 단순화 할 수 있다는 장점도 있다.
--예시1) column에 별칭을 사용할 경우 백틱(`)을 사용할 것 create view v_viewtest as select B.mem_id `Member_ID` , M.mem_name `Member_Name`, B.prod_name `Product_Name` from buy B inner join member M on B.mem_id = M.mem_id; select `Member_ID`, `Member_Name` from v_viewtest;--예시2) 데이터 수정/ 삭제 update v_member set addr = '부산' where mem_id = 'BLK'; delete from v_member where addr = '경남';
--예시3) 데이터 삽입 오류! create view v_member as select mem_id, mem_name, addr from member; insert into v_member values ('BTS', '방탄소년단', '경기');
예시3의 경우 오류가 난다. v_member는 member table을 참조하는데, 이 table은 mem_number열이 not null로 설정되어 있기 때문이다. 즉, not null로 설정되어 있는 mem_number열에 null값을 입력하는 꼴이므로 데이터 삽입의 경우 무조건 오류가 나온다.
해결방법은 (1) v_member가 mem_number을 참조하도록 변경한다. (2) mem_number의 속성을 null로 바꾼다. (3) mem_number의 default를 설정한다.
--예시4) with check option ; create view v_height167 as select * from member where height >= 167 with check option ; insert into v_height167 values ('TRA', '티아라', 4, '서울', null, null, 159, '2005-1-1');
예시4처럼 with check option을 사용하지 않아도 v_height167를 select해서 확인해보면 똑같은 결과가 나온다. 하지만 키가 167을 넘지 않은 데이터를 삽입할 때, check를 하지 않으면 오류가 나오지 않아서 입력되면 안 되는 데이터를 입력한 것과 같은 느낌이 들 수 있다.
즉, check문을 통해 잘못된 데이터를 입력하게 되면 오류가 나오도록 설정해 놓는게 바람직하다고 볼 수 있다.
--예시5) view의 소스 코드 확인 show create view v_height167;--예시6) view의 상태 확인 check table v_height167;
저자 우재남, 혼자 공부하는 SQL, 한빛미디어
