-- GUI환경에서 테이블 만들기
create database naver_db;
CREATE TABLE buy
( num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
mem_id CHAR(8) NOT NULL,
prod_name CHAR(6) NOT NULL,
group_name CHAR(4) NULL ,
price INT UNSIGNED NOT NULL,
amount SMALLINT UNSIGNED NOT NULL ,
FOREIGN KEY(mem_id) REFERENCES member(mem_id)
);
INSERT INTO member VALUES('TWC', '트와이스', 9, '서울', '02', '11111111', 167, '2015-10-19');
INSERT INTO member VALUES('BLK', '블랙핑크', 4, '경남', '055', '22222222', 163, '2016-8-8');
INSERT INTO member VALUES('WMN', '여자친구', 6, '경기', '031', '33333333', 166, '2015-1-15');
INSERT INTO buy VALUES( NULL, 'BLK', '지갑', NULL, 30, 2);
INSERT INTO buy VALUES( NULL, 'BLK', '맥북프로', '디지털', 1000, 1);
INSERT INTO buy VALUES( NULL, 'APN', '아이폰', '디지털', 200, 1);
select * from member;
select * from buy;
DROP TABLE IF EXISTS buy, member;
CREATE TABLE member
( mem_id CHAR(8) NOT NULL PRIMARY KEY,
mem_name VARCHAR(10) NOT NULL,
height TINYINT UNSIGNED NULL
);
CREATE TABLE buy
( num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
user_id CHAR(8) NOT NULL,
prod_name CHAR(6) NOT NULL,
FOREIGN KEY(user_id) REFERENCES member(mem_id)
);
DROP TABLE IF EXISTS buy;
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);
INSERT INTO member VALUES('BLK', '블랙핑크', 163);
INSERT INTO buy VALUES(NULL, 'BLK', '지갑');
INSERT INTO buy VALUES(NULL, 'BLK', '맥북');
select M.mem_id, M.mem_name, B.prod_name
from buy B
inner join member M
on B.mem_id = M.mem_id;
-- 안지워진다.
delete from member where mem_id='BLK';
-- on update cascade / on delete cascade
-- drop table if exists buy;
-- 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;
-- set foreign_key_checks=0;
-- insert into buy values(null, 'BLK', '지갑');
-- insert into buy values(null, 'BLK', '맥북');
-- update member set mem_id = 'PINK' where mem_id='BLK';
-- select M.mem_id, M.mem_name, B.prod_name
-- from buy B
-- inner join member M
-- on B.mem_id = M.mem_id;
--
-- delete from member where mem_id='PINK';
create database test1;
use test1;
create table m(
m1 varchar(10) primary key,
m2 int
);
create table b(
num int auto_increment primary key,
mfk varchar(10) not null,
foreign key(mfk) references m(m1) on delete cascade on update cascade
);
insert into m values("a",4);
insert into b values(null,'a');
select * from m;
select * from b;
update m set m1="b" where m1='a';
-- 기타 제약조건
-- 고유 키 제약 조건
drop table if exists buy, member;
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
);
insert into member values('BLK','블랙핑크',163,'pink@gmail.com');
insert into member values('TWC','트와이스',167,null);
-- 고유 키가 중복되기 떄문에 insert 되지 않는다.
insert into member values('APN','에이핑크',164,'pink@gmail.com');
-- 체크 제약조건
drop table member;
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
);
insert into member values('BLK','블랙핑크',163,null);
-- 100이 넘지 않기 떄문에 인서트가 안된다.
insert into member values('TWC','트와이스',99,null);
-- alter table문으로 제약조건을 추가
alter table member
add constraint
check (phone1 in ('02', '031', '032', '054', '055', '061')) ;
insert into member values('TWC','트와이스',167,'02');
-- 제약조건에 해당하지 않는 '010' 이라서 추가가 되지 않는다.
insert into member values('OMY','오마이걸',167,'010');
-- 기본값 정의alter
drop table if exists member;
CREATE TABLE member
( mem_id CHAR(8) NOT NULL PRIMARY KEY,
mem_name VARCHAR(10) NOT NULL,
height TINYINT UNSIGNED NULL default 160,
phone1 char(3) null
);
alter table member
alter column phone1 set default '02';
insert into member values('RED','레드벨벳',161,'054');
insert into member values('SPC','우주소녀',default,default);
select * from member;
-- 5-3
use market_db;
create view v_member
as
select mem_id, mem_name, addr from member;
select * from v_member;
-- 필요한 열만 보거나 조건식을 넣을 수도 있다.
select mem_name, addr from v_member
where addr in ('서울', '경기');
create view v_memberbuy
as
select B.mem_id, M.mem_name, B.prod_name, M.addr,
concat(M.phone1, M.phone2) '연락처'
from buy B
inner join member M
on B.mem_id = M.mem_id;
select * from v_memberbuy;
-- 뷰의 실제 작동
-- 뷰의 실제 생성, 수정, 삭제
use market_db;
create view v_viewtest1
as
select B.mem_id 'member id', M.mem_name as 'member name', B.prod_name "product name",
concat(M.phone1, M.phone2) as "office phone"
from buy B
inner join member M
on B.mem_id = M.mem_id;
select distinct `member id`,`member name` from v_viewtest1;
-- view의 수정 ( alter view )
alter view v_viewtest1
as
select B.mem_id '회원 아이디', M.mem_name as '회원 이름', B.prod_name "제품 이름",
concat(M.phone1, M.phone2) as "연락처"
from buy B
inner join member M
on B.mem_id = M.mem_id;
select distinct `회원 아이디`,`회원 이름` from v_viewtest1;
drop view v_viewtest1;
-- view를 통한 데이터 수정/삭제
update v_member set addr = '부산' where mem_id = 'BLK';
-- v_member가 참조하는 member에서 mem_number 열을 참조하고 있지 않아서 입력할 방법이 없다.
insert into v_member(mem_id, mem_name, addr) values('BTS','방탄소년단','경기');
-- 평균 키가 167 이상인 뷰를 생성
create view v_height167
as
select * from member where height >= 167;
select * from v_height167;
-- 삭제
delete from v_height167 where height < 167;
-- 뷰를 통한 데이터의 입력
insert into v_height167 values('TRA', '티아라', 6, '서울', null, null, 159, '2005-01-01');
select * from v_height167;
-- with check option 설정된 값의 범위가 벗어나는 값은 입력이 안된다.
alter view v_height167
as
select * from member where height >= 167
with check option;
insert into v_height167 values('TOB', '텔레토비', 4, '영국', null, null, 140, '1995-01-01');
-- create view v_complex
-- as
-- select B.mem_id, M.mem_name, B.prod_name, M.addr
-- from buy B
-- inner join
