[SQL] SQL 고급 문법
Chapter04 SQL 고급 문법
4.1 MySQL의 데이터 형식
4.2 두 테이블을 묶는 조인
4.3 SQL 프로그래밍
4.3 SQL 프로그래밍
1. IF문
2. CASE문
3. WHILE문
4. 동적 SQL
스토어드 프로시저: MySQL에서 프로그래밍 기능을 구현할 때 사용하는 데이터베이스 개체
DELIMITER $$
CREATE PROCEDURE
BEGIN
-- SQL 프로그래밍 코드
END $$
DELIMITER ;
CALL 스토어드프로시저명(); -- 실행구분 문자: $$를 주로 사용. /, &, @ 등도 무관. 다른 기호와 구별하기 위해 2개 연속 사용 권장
1. IF문
if 조건식 then
-- 실행할 SQL문
end if;실행할 SQL문이 두 문장 이상이라면 BEGIN~END 필요
delimiter $$
create procedure ifProc3()
begin
declare debutDate DATE;
declare curDate date;
declare days int;
select debut_date into debutDate
from market_db.member
where mem_id = 'APN';
set curDate = current_date();
set days = datediff(curDate, debutDate);
if (days/365) >= 5 then
select concat('데뷔한 지', days, '일이나 지났습니다. 핑순이들 축하합니다!');
else
select '데뷔한 지 ' + days + '일밖에 안 되었네요. 핑순이들 화이팅~';
end if;
end $$
delimiter ;
call ifProc3();
declare: 변수 선언
SELECT 변수1 INTO 변수2: 변수1의 값을 변수2에 대입
CURRENT_DATE(): 오늘 날짜 출력
CURRENT_TIMESTAMP(): 오늘 날짜와 시간
DATEDIFF(날짜1, 날짜2): 날짜 차이 출력
2. CASE문
CASE
WHEN 조건1 THEN
-- SQL문
WHEN 조건2 THEN
-- SQL문
WHEN 조건3 THEN
-- SQL문
ELSE
-- SQL문
END CASE;
delimiter $$
create procedure caseProc()
begin
declare point int;
declare credit char(1);
set point = 88;
case
when point >= 90 then
set credit = 'A';
when point >= 80 then
set credit = 'B';
when point >= 70 then
set credit = 'C';
when point >= 60 then
set credit = 'D';
else
set credit = 'F';
end case;
select concat('취득점수: ', 'point'), concat('학점: ', credit);
end $$
delimiter ;
call caseProc();
CASE문 활용: 쇼핑몰 구매액으로 회원별 등급 분배
1. 회원별 총구매액(group by)
select mem_id, sum(price*amount) "총구매액"
from buy
group by mem_id;
2. 정렬(order by)
select mem_id, sum(price*amount) "총구매액"
from buy
group by mem_id
order by sum(price*amount) desc;
3-1. 회원 이름 출력(inner join)
select b.mem_id, m.mem_name, sum(price*amount) "총구매액"
from buy b
inner join member m
on b.mem_id = m.mem_id
group by b.mem_id
order by sum(price*amount) desc;
3-2. 구매하지 않은 회원도 출력(outer join)
select m.mem_id, m.mem_name, sum(price*amount) "총구매액"
from buy b
right outer join member m
on b.mem_id = m.mem_id
group by m.mem_id
order by sum(price*amount) desc;
4. 가상 칼럼으로 회원등급 출력(case)
select m.mem_id, m.mem_name, sum(price*amount) "총구매액",
case
when (sum(price*amount) >= 1500) then '최우수고객'
when (sum(price*amount) >= 1000) then '우수고객'
when (sum(price*amount) >= 1) then '일반고객'
else '유령고객'
end "회원등급"
from buy b
right outer join member m
on b.mem_id = m.mem_id
group by m.mem_id
order by sum(price*amount) desc;
여기서 CASE문은 컴마로 구별되어있는 하나의 칼럼이다.
3. WHILE문
WHILE 조건식 DO
-- SQL문
END WHILE;drop procedure if exists whileProc;
delimiter $$
create procedure whileProc()
begin
declare i int;
declare hap int;
set i = 1;
set hap = 0;
while(i<=100) do
set hap = hap + i; -- 변수에 값을 할당할 때는 항상 set 사용
set i = i + 1;
end while;
select '1부터 100까지의 합: ', hap;
end $$
delimiter ;
call whileProc();
WHILE문의 응용
ITERATE: 자바의 CONTINUE와 유사. 지정한 레이블로 가서 계속 진행
LEAVE: 자바의 BREAK와 유사. 지정한 레이블을 빠져나감
drop procedure if exists whileProc2;
delimiter $$
create procedure whileProc2()
begin
declare i int;
declare hap int;
set i = 1;
set hap = 0;
mywhile: -- 레이블 지정
while(i<=100) do
if(i%4 = 0) then
set i = i+1;
iterate mywhile; -- mywhile(while문)로 돌아가서 계속 진행
end if;
set hap = hap + i;
if(hap>1000) then
leave mywhile; -- mywhile(while문)을 빠져나감
end if;
set i = i+1;
end while;
select '1부터 100까지의 합(4의 배수 제외), 1000 넘으면 종료: ', hap, i;
end $$
delimiter ;
call whileProc2();
4. 동적 SQL
PREPARE: SQL문 준비
EXECUTE: 준비한 SQL문 실행
DEALLOCATE PREPARE: SQL문 해제
prepare myquery from 'select * from member where mem_id = "BLK"';
execute myquery;
deallocate prepare myquery;
변수 사용
create table gate_table(
id int auto_increment primary key,
entry_time datetime
);
set @curdate = current_timestamp();
prepare myquery from 'insert into gate_table values(null, ?)';
execute myquery using @curdate;
deallocate prepare myquery;
select * from gate_table;
Java의 preparedStatement와 유사하다.
Chapter 05. 테이블과 뷰
1. 테이블 만들기
2. 제약조건
3. 뷰
1. 테이블 만들기
1.1 데이터베이스와 테이블 설계
1.2 GUI 환경에서 테이블 만들기
1.3 SQL로 테이블 만들기
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);AUTO INCREMENT로 지정한 열은 반드시 PRIMARY KEY 또는 UNIQUE로 지정
PRIMARY KEY = UNIQUE + NOT NULL
2. 제약조건
2.1 제약조건의 기본 개념과 종류
2.2 기본 키 제약조건
2.3 외래 키 제약조건
컬럼이 PRIMARY KEY이거나 UNIQUE여야 외래 키로 참조할 수 있다.
다른 테이블에서 FOREIGN KEY로 지정된 칼럼의 경우, 값을 변경하거나 삭제할 수 없다. 단, CASCADE를 사용하면 변경과 삭제가 FOREIGN KEY를 사용하는 테이블에도 적용되면서 가능하게 된다.
create table buy(
num int auto_increment not null primary key,
mem_id varchar(8) not null,
prod_name varchar(6) not null
);
alter table buy
add constraint
foreign key(mem_id) references member(mem_id)
on update cascade
on delete cascade;
2.4 기타 제약조건
고유 키(UNIQUE)
중복되지 않는 유일한 값
NULL 허용
*PRIMARY KEY ≒ UNIQUE + NOT NULL
PRIMARY KEY는 테이블당 하나만 존재할 수 있지만 UNIQUE는 여러 개 존재할 수 있다.
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 into member values('APN', '에이핑크', 164, 'pink@gmail.com');Error Code: 1062. Duplicate entry 'pink@gmail.com' for key 'member.email'
체크
입력되는 데이터 점검(가능하면 HTML 기능 사용 권장)
create table member(
mem_id char(8) not null primary key,
mem_name varchar(10) not null,
height tinyint unsigned null check(height>=100), -- 100 이상 값만 가능하도록 함
phone1 char(3) null
);
insert into member values('BLK', '블랙핑크', 163, null);
insert into member values('TWC', '트와이스', 99, null);
Error Code: 3819. Check constraint 'member_chk_2' is violated.
기본값 정의
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('SPC', '우주소녀', default, default);
insert into member(mem_id, mem_name, phone1) values ('RED', '레드벨벳', '054');기본값이 입력되지 않았을 때 또는 DEFAULT로 설정했을 때 DEFUALT값 적용(NULL값을 넣었을 때 X)
3. 뷰
3.1 뷰의 개념
뷰: 가상의 테이블. 데이터가 아니라 데이터의 주소를 가지고 있음
뷰에 접근하면 SELECT 실행, 결과가 화면에 출력
3.1.1 뷰 생성
create view v_member
as
select mem_id, mem_name, addr from member;
3.1.2 뷰의 작동
사용자 ―조회/변경→뷰(SELECT문)―쿼리 실행→테이블(데이터)
사용자 ←결과― 뷰(SELECT문)←쿼리 결과값―테이블(데이터)
기본적으로 읽기 전용이지만 뷰를 통해 원본 테이블의 데이터 수정 가능(비권장)
3.1.3 뷰를 사용하는 이유
보안: 원하는 컬럼만 보여줄 수 있음
복잡한 쿼리를 간단하게 만들 수 있음
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;
-- 위의 쿼리를 대신하기 위해 inner join된 뷰를 만듦
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 where mem_name = '블랙핑크';
3.2 뷰의 실제 작동
3.2.1 뷰를 통한 데이터의 수정/삭제
별칭 사용→테이블과 칼럼명을 다르게 지정 가능
별칭에 공백 가능, 공백이 있으면 백틱(`)으로 묶어줌
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;
뷰 수정: ALTER VIEW
칼럼명에 한글 사용 가능...비권장
alter view v_viewtest1
as
select b.mem_id '회원 아이디', m.mem_name AS '회원 이름', b.prod_name "제품 이름", concat(m.phone1, m.phone2) AS "AS 연락처"
from buy b
inner join member m
on b.mem_id = m.mem_id;
select distinct `회원 아이디`, `회원 이름` from v_viewtest1;
뷰 삭제: DROP VIEW
drop view v_viewtest1;
3.2.2 뷰의 정보 확인
DESCRIBE(DESC)
뷰의 정보에서는 PRIMARY KEY 등의 정보가 확인되지 않음
SHOW CREATE VIEW: 뷰의 소스 코드 확인
3.2.3 뷰를 통한 데이터의 수정/삭제
데이터 입력시 뷰에서 보이지 않는 테이블의 컬럼에 NOT NULL이 지정되지 않아야 함
-- 뷰 생성
create view v_height167
as
select * from member where height >= 167;
-- 조회되지 않는 행 삭제
delete from v_height167 where height < 167; -- 삭제되는 데이터 없음
-- 조회되지 않는 행 추가
insert into v_height167 values('TRA', '티아라', 6, '서울', NULL, NULL, 159, '2005-01-01');
-- 추가됨, 조회는 되지 않음
WITH CHECK OPTION: 조건이 만족되지 않으면 데이터 입력 불가
alter view v_height167
as
select * from member where height >= 167
with check option;
-- 조회되지 않는 데이터 추가
insert into v_height167 values('TOP', '텔레토비', 4, '영국', NULL, NULL, 140, '1995-01-01');Error Code: 1369. CHECK OPTION failed 'market_db.v_height167'
*단순 뷰와 복합 뷰
단순 뷰: 하나의 테이블로 만든 뷰
복합 뷰: 여러 테이블로(join) 만든 뷰. 읽기 전용
3.2.4 뷰가 참조하는 테이블 삭제
관련 뷰가 있어도 테이블은 삭제됨
drop table if exists buy, member;
select * from v_height167;
CHECK TABLE: 뷰 상태 확인
check table v_height167;