[비트교육센터] MySql 21일차 DDL, ERD

1. DDL

- DML (Data Manipulation Language): 데이터베이스 내에서 데이터를 삽입, 업데이트 및 삭제하는 데 사용

ex) Select , Insert , Update , Delete

- DDL (Data Definition Language): 테이블(데이터베이스 객체)과 같은 데이터 구조를 정의하는데 사용되는 명령어

ex) Create , Alter , Drop , Truncate

- DCL (Data Control Language): 데이터베이스에 접근하고 객체들을 사용하도록 권한을 주고 회수하는 명령어

ex) Grant , Revoke , Commit , Rollback

 

1) Create, 테이블 다루기

[1] 

use scottdb; -- 이용할 db를 선택한다.
create table book(
	book_id int,
    title varchar(50),
    author varchar(10),
    public_data date
);

생성된 book 테이블

[2] 테이블에 데이터 추가하기

insert into book values(1, 'The Martian','Andy Weir','2015-07-31');
insert into book values(1, 'Doctor Sleep','Stepheb','2014-09-24');

- 데이터는 입력할 수 있지만 제약조건이 없다.

- book_id가 Primary Key라는 제약이 없어서 같은 숫자 1을 썼는데도 오류가 나지 않는다.

select*from book;

--> 결과

 

[3] 테이블에 데이터 삭제하기

delete from book;

1) 만약 오류가 뜬다면 Edit -> Preferences -> SQL Editor로 들어간다.

2) 그 다음 Safe Updates를 체크 해제후 MySql Workbench에 재접한다.

3)재접후 해당 코드를 다시 실행시 데이터 삭제가 진행된다.

 

TRUNCATE, DELETE, DROP 차이점

- delete: 테이블 내부의 행을 모두 삭제하며, where 절을 사용해 개별적으로 행을 삭제할 수 있따. rollback(실행취소)가 가능하다.

- truncate: 개별적으로 행을 삭제할수 없고, 테이블 내부의 모든행을 삭제한다. rollback,이 불가능하다.

- drop: 데이터베이스에서 테이블 정의 및 해당 테이블에 대한 모든 데이터, 인덱스, 트리거, 제약 조건 및 권한을 제거한다.

rollback이 불가능하다.

 

2) Alter

[1] 제약조건 생성하기

- 데이터의 무결성을 지키기 위해, 데이터를 입력받을 때 실행되는 검사 규칙을 의미한다.

1. not null: NOT NULL 제약 조건을 설정하면, 해당 필드는 NULL 값을 저장할 수 없다.

2. unique: 해당 필드는 서로 다른 값을 가져야 한다. 즉, 이 제약 조건이 설정된 필드는 중복된 값을 저장할 수 없다.

3. primary key: 해당 필드는 NOT NULL과 UNIQUE 제약 조건의 특징을 모두 가진다. 따라서 이 제약조건이 설정된 필드는 NULL 값을 가질 수 없으며, 또한 중복된 값을 가질 수 없다.

4. foreign key: 한 테이블을 다른 테이블과 연결해주는 역할을 한다. 외래 키가 설정된 테이블에 레코드를 입력하면, 기준이 되는 테이블의 내용을 참조해서 레코드가 입력된다.

5. default: 해당 필드의 기본값을 설정할 수 있게 해준다. 만약 레코드를 입력할 때 해당 필드 값을 전달하지 않으면, 자동으로 설정된 기본 값을 저장한다.

 

alter table book add constraint PK_book_id Primary key(book_id);

- book_id에 Constraint 제약조건 Primary Key를 부여한다.

- 이렇게 되면 1)-[2]에서 선언한 insert문에 오류가 날것이다.(book_id에 1을 연속으로 써놓았지만 이제 제약 조건이 걸리면서 중복된 숫자를 못쓰기 때문이다.)

[2] 칼럼 타입 바꾸기

alter table book modify title varchar(100);

desc book;

--> 결과

[3] 칼럼 추가

alter table book add pubs varchar(50);

desc book;

--> 결과

[4] 칼럼 삭제

alter table book drop author;

desc book;

--> 결과

[5] 타입을 지정해 칼럼 추가

alter table book add author_id int;

desc book;

--> 결과

3) Update

- Update: 기존에 입력되어 있는 값을 변경한다.

 

[1] 데이터 수정하기

update book set author_id = 100 where book_id = 1;
update book set author_id = 200 where book_id = 2;

- book_id가 1인 행에 author_id를 150으로 변경하고, book_id가 2인 행에 author_id를 200으로 변경한다.

--> 결과

- NULL이었던 author_id 칼럼에 값이 들어왔다.

 

4) Foreign Key

- 외래키: 두 테이블의 관계를 맺어주는 키를 말한다.

[1] author 테이블 생성, 데이터 넣기

create table author(
	author_id int,
    author_name varchar(100) not null,
    author_desc varchar(500),
    constraint pk_author primary key(author_id)
);

insert into author values(100, 'The Martian','Andy Weir');
insert into author values(200, 'Doctor Sleep','Stepheb');
select * from author;

--> 결과

[2] 제한 만들기

alter table [테이블명] add constraint foreign key( [컬럼명] )
references [참조한 테이블]( [참조할 컬럼명] ) [옵션];

- alter로 참조할 테이블을 만드는 방법은 이러하다.

alter table book add constraint fk_book_author foreign key(author_id)
references author(author_id);

- author 테이블에서 author_id 컬럼을 참조한다.

 

update book set author_id = 500 where book_id = 2;

- 외래키가 적용되면 참조되고 있는 값을 삭제 또는 변경하려고 하면, 자료의 정합성이 깨지게 되므로 다음과 같이 오류가 발생된다.

--> 결과

5) 함수만들기

[1] 권한 부여하기

show variables like '%log_bin_trust_function_%';

- 해당 코드를 입력해 function권한이 있는지 확인한다.

- 만약 Value가 OFF일 경우 권한을 부여해야 한다.

--

set global log_bin_trust_function_creators=1;

- root계정으로 가서 함수권한을 부여한다.

- 함수권한은 절대적인 권한을 가진 root에서만 가능하다.

--

set global log_bin_trust_function_creators=1; -- 권한을 풀어주기, 해당 코드는 localhost에서 실행해야 한다.

- 그리고 돌아와서 해당 코드를 입력해 권한을 풀어준다.

--> 결과

[2] 함수만들기

- 사용자 정의함수로 특정 기능들을 모듈화, 재사용 할 수 있어서 복잡한 쿼리문을 간결하게 만들수 있다. 

- 기본문법

CREATE FUNCTION 함수이름 (
   INPUT 파라미터
) RETURNS 리턴타입
AS
BEGIN
    QUERY
    RETURN 리턴값;
END

- 함수만들기

delimiter $$ -- 이 이점부터 코드 끝에 붙는 문자는 세미콜론(;)이 아닌 $$이다.
create function FN_GET_SALARY(IN_EMPNO int) returns int
begin
	declare OUT_SAL int;
    set OUT_SAL = 0;
    
    select sal
    into OUT_SAL
    from emp
    where empno = IN_EMPNO;
    
    return OUT_SAL;
end $$
delimiter ; -- 다시 세미콜론으로 돌아온다.

select FN_GET_SALARY(7900);

--> 결과

6) Stored Precedure(저장 프로시저)

[1] function과의 다른점

- function은 리턴시 값을 하나면 저장 프로시저는 여러개 가능.
- function은 select문과 함께 실행, 프로시저는 단독실행.

 

[2] 저장 프로시저 만들기

create procedure SP_GET_SALARY
(
	in IN_EMPNO int
)
begin
	select sal
    from emp
    where empno = IN_EMPNO;
end $$
delimiter ;

call SP_GET_SALARY(7900);

--> 결과

7) commit, rollback

- TCL: 트랜잭션의 DML작업단위를 제어하는 명령어이다.

 

- commit: 모든 작업을 정상 처리하겠다고 확정하는 명령어이다. 트랜잭션의 처리과정을 데이터베이스에 반영하기 위해, 변경된 내용을 모두 영구 저장한다. COMMIT 수행하면, 하나의 트랜잭션 과정을 종료하게 된다.

- rollback: 작업 중 문제 발생 시, 트랜잭션의 처리과정에서 발생한 변경 사항을 취소하고, 트랜잭션 과정을 종료시킨다. 트랜잭션으로 인한 하나의 묶음 처리가 시작되기 이전 상태로 되돌아 간다.

set autocommit=0; -- sql 특성상 자동으로 커밋된다. 그래서 잠시 자동커밋을 꺼 놓는다.
start transaction; -- 트랜잭션을 설정해 작업 단위를 설정한다.

-- 1. 월급 10퍼 인상
update emp set sal = (sal+sal*0.1);
-- update emp set sal = round(sal); -- 소수점이라서 반올림 해야할 때

-- 2. 10번부서의 100달러 인상, comm을 0을 주어라
update emp set sal=sal+100, comm=0 where DEPTNO = 10;

-- 3. 최고급여를 찾아서 모든 직원의 급여를 최고급여로 만들어라. 그중 scott이 있는 부서 적용해라.
update emp set sal = (select msal from (select max(sal) as msal from emp) temp_tbl)
where job = 
(select t_job from (select job as t_job from emp where ename = 'SCOTT') temp2_tbl);
select * from emp where job = (select job as t_job from emp where ename = 'SCOTT');
-- update에서 사용하는 테이블은 서브쿼리 from에 같이 쓸 수 없다.

rollback; -- 트랜잭션 구문 아래도 실행한 것들을 취소하려면 rollback을 실행하면 된다.

--> 1. 결과

update전

update후

--> 2. 결과

update 전

update후, comm 칼럼에 값이 채워지고, sal 값이 올랐다.

--> 3. 결과

update후 scott과 같은 부서에 있는 사람들의 월급이 올라갔다.

--> rollback 이후

데이터가 원래대로 돌아갔다.

2. ERD(feat. sakila 데이터를 이용한 실습예제)

 

1) sakila 데이터를 이용할 수 있는 권한 부여하기

[1] 

- root 계정 localhost로 가서 sakila 데이터를 사용할 수 있도록 권한을 부여한다.

-- sakila, 기본 실험용 db

grant all privileges on sakila.* to 'lastcoder'@'%';
grant all privileges on sakila.* to 'lastcoder'@'localhost';

-- 방금 권한 준것을 즉시적용
flush privileges;

--> 결과

- sakila 데이터를 사용할 수 있게된다.

 

2) ERD

sakila 데이터의 ERD이다.

[1] 정의

- ERD: Entity Relational Diagram, 엔터티의 관계를 나타내는 다이어그램이다.

 

[2] 표기법

- ERD의 표기법은 다음과 같다.

3) 실습문제

use sakila;

- sakila 데이터베이스를 이용한다.

[1] 

-- 고객별 지불 금액의 총합을 구하라
-- customer_id, 총금액
-- 총 금액이 많은 순서로

-- 나
select c.customer_id, sum(p.amount) as 총금액  from customer as c 
join payment as p 
on c.customer_id = p.customer_id
group by c.customer_id
order by sum(p.amount)
desc;

-- 선생님
select customer_id, sum(amount)
from payment
group by customer_id
order by sum(amount) desc;

--> 결과(나)

--> 결과(선생님)

 

[2]

-- 위 쿼리에서 고객 id 대신 고객의 first_name, last_name을 출력해라

-- 나
select c.first_name, c.last_name from customer as c 
join payment as p 
on c.customer_id = p.customer_id
group by c.first_name, c.last_name
order by sum(p.amount)
desc;

-- 선생님
select cs.customer_id, cs.first_name, cs.last_name, sum(amount)
from payment pm join customer cs on pm.customer_id = cs.customer_id
group by cs.customer_id, cs.first_name, cs.last_name;

--> 결과(나)

--> 결과(선생님)

[3]

-- 고객ID별 총대여수 출력(대여 수 많은 고객부터)
-- customer_id, 총 대여 수

select rn.customer_id, count(rn.inventory_id)
from rental rn join customer cs on rn.customer_id = cs.customer_id
group by rn.customer_id
order by count(rn.inventory_id)
desc;

-- 선생님
select customer_id, count(rental_id) -- 주키를 카운트해서 NULL이 없다.
from rental
group by customer_id
order by count(rental_id) desc;

--> 결과(나)

--> 결과(선생님)

[4]

-- 위 쿼리에서 고객ID 대신 고객의 first_name, last_name을 출력해라.

-- 나
select cs.first_name, cs.last_name, count(rn.inventory_id)
from rental rn join customer cs on rn.customer_id = cs.customer_id
group by cs.first_name, cs.last_name
order by count(rn.inventory_id)
desc;

-- 선생님
select cs.first_name, cs.last_name, count(rental_id)
from rental rt join customer cs
on rt.customer_id = cs.customer_id
group by cs.first_name, cs.last_name
order by count(rental_id) desc;

--> 결과(나)

--> 결과(선생님)

[5]

-- action 분야 영화의 다음 속성들을 출력하시오
-- 필름번호, 제목, 설명, 분야, 발매년도, 언어

-- 나
select fm.film_id as 필름번호, fm.title as 제목, fm.description as 설명, 
c.name as 분야, fm.release_year as 발매년도, l.name as 언어
from film fm
join language l on fm.language_id = l.language_id
join film_category fc on fm.film_id = fc.film_id
join category c on fc.category_id = c.category_id
where c.name='Action'
group by fm.film_id, fm.title, fm.description, c.name ,fm.release_year, l.name;

-- 선생님
select FL.film_id as 필름번호, FL.title as 제목,
FL.description as 설명, CT.name as 분야, FL.release_year as 발매년도,
LG.name as 언어
from film FL join film_category FC
on FL.film_id = FC.film_id
join category CT on FC.category_id = CT.category_id
join language LG on FL.language_id = LG.language_id
where CT.name = 'Action';

--> 결과(나)

--> 결과(선생님)

[6]

-- 출연작이 많은 순으로 배우의 id, first_name, last_name, 작품 수를 출력하시오

-- 나
select a.actor_id, a.first_name, a.last_name, count(fa.actor_id) as '작품 수'
from film_actor fa
join actor a on a.actor_id = fa.actor_id
group by a.actor_id, a.first_name, a.last_name
order by count(fa.actor_id) desc;

-- 선생님
select a.actor_id, a.first_name, a.last_name, count(fa.film_id) as '작품수'
from film_actor fa join actor as a
on fa.actor_id = a.actor_id
group by a.actor_id, a.first_name, a.last_name
order by count(fa.film_id) desc;

--> 결과(나)

--> 결과(선생님)