MySQL Chapter4-Chapter5[13-3]

Chapter4

SQL 프로그래밍

스토어드 프로시저는 MySQL에서 프로그래밍 기능이 필요할 떄 사용하는 데이터베이스 개체입니다. SQL 프로그래밍은 기본적으로 스토어드 프로시저 안에 만들어야 합니다.

delimiter $$
create procedure -- 스토어드 프로시저_이름()
begin
-- 이 부분에 SQL 프로그래밍 코딩
end $$ -- 스토어드 프로시저 종료
delimiter ; -- 종료 문자를 다시 세미콜론(;)으로 변경
call 스토어드_프로시저_이름(); -- 스토어드 프로시저 실행

if문
:if문은 조건문으로 가장 많이 사용되는 프로그래밍 문법 중 하나입니다. if문을 활용하면 다양한 조건을 처리할 수가 있습니다.

-- SQL 프로그래밍(if,변수,case문,while문,동적 SQL)
-- if문의 활용
drop procedure if exists ifproc3;
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();​

case문
:여러가지 조건 중에서 선택해야 하는 경우도 있습니다. 이럴 경우에는 case문을 사용해서 조건을 설정할 수 있습니다.

-- case문
drop procedure if exists caseproc;
delimiter $$
create procedure caseproc()
-- 변수 생성, point를 88로 지정.
begin
	declare point int ; 
    declare credit char(1);
    set point = 88;
-- 조건문 작성 point의 점수에 따라 부합하는 조건식의 credit 출력
    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();

활용으로 group by를 사용을 할 수 있습니다.

-- group by
-- step1
select mem_id, sum(price*amount) "총 구매액"
	from buy
    group by mem_id;
    
-- step2
select mem_id, sum(price*amount) "총 구매액"
	from buy
    group by mem_id
	order by sum(price*amount) desc ; 
    
-- step3
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 ; 

-- step4
select M.mem_id, M.mem_name, sum(price*amount) "총 구매액"
	from buy B
		inner join member M
        on B.mem_id = M.mem_id
    group by M.mem_id
	order by sum(price*amount) desc ; 

-- step5
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 ;

while문
:프로그래밍에서 꼭 필요한 부분 중 하나가 반복인데, while문은 필요한 만큼 계속 같은 내용을 반복을 할 수가 있습니다.

-- 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 i = i + 1;
	end while;
    select '1부터 100까지의 합 ==>', hap;
end $$
delimiter ;
call whileproc();

-- while문의 응용
drop procedure if exists whileproc;
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;
		end if;
        set hap = hap + i;
        if( hap> 1000) then
			leave mywhile;
		end if;
        set i = i + 1;
	end while;

    select '1부터 100까지의 합 (4의 배수 제외), 1000넘으면 종료', hap;
end $$
delimiter ;
call whileproc2();

동적 SQL
: SQL문은 내용이 고정되어 있는 경우가 대부분 입니다. 하지만 상황에 따라 내용 변경이 필요할 때 동적 SQL을 사용하면 변경되는 내용을 실시간으로 적용시켜 사용할 수 있습니다.

-- 동적 SQL
drop table if exists gate_table;
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;

Chapter5

테이블 만들기

테이블은 표 형태로 구성된 2차원 구조로 행과 열로 구성되어 있습니다.

테이블 생성

-- 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';​

제약조건

테이블을 만들 때는 테이블의 구조에 필요한 제약 조건을 설정해줘야하는데,앞에서 확인한 기본 키와 외래 키가 대표적인 제약 조건입니다. 이것들은 DB만 할 수 있는 제약 조건들입니다.

기타 제약 조건

고유 키 제약조건
:고유 키 제약 조건은 중복되지 않는 유일한 값을 입력해야 하는 조건입니다. 이는 기본 키 제약조건과 거의 비슷하지만, 차이점은 고유 키 제약조건은 null 값을 허용한다는 것입니다.

-- 기타 제약조건
-- 고유 키 제약 조건
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');​

체크 제약조건
:체크 제약조건은 입력되는 데이터를 점검하는 기능을 합니다. 예를 들어서 평균 키에 마이너스 값이 입력되지 않도록 하거나 연락처 국번에 02,,031, 등등을 입력되도록 할 수 있습니다.

-- 체크 제약조건

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;​

가상 테이블 뷰

뷰는 데이터베이스 개체 중에 하나입니다. 모든 데이터베이스 개체는 테이블과 관련이 있지만 특히 뷰는 테이블과 아주 밀접하게 연관되어 있습니다. 뷰는 한 번 생성해 놓으면 테이블이라고 생각해도 될 정도로 사용자들의 입장에서는 테이블과 거의 동일한 개체로 취급하는데, 이런 뷰는 단순 뷰와 복합 뷰로 나뉩니다. 단순 뷰는 하나의 ㅌ테이블과 연관된 뷰를 말하고, 복합 뷰는 2개 이상의 테이블과 연관된 뷰를 말합니다.

뷰의 기본 생성

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');