DAY.01

-- single line comment, 한 줄 주석


/*
multi line comment
여러 줄 주석

*/

 

-- person 테이블
-- DDL, DML, DCL
/*
자연키(의미가 있는 키 - 업무키), 대리키(아무런 의미가 없는 데이터)
복합키 후보
- 이름, 생일
- 이름, 주소

문제
PK가 없음
예약어 사용 (NAME)


CRUD 의미
Create (생성 - insert)
Read (읽기 - select)
Update (수정 - update)
Delete (삭제 - delete)

*/

CREATE TABLE person (
	  name VARCHAR(40) 
	, eye_color CHAR(2) CHECK (eye_color IN ('BL', 'BR', 'GR'))
	, birth_date DATE
	, address VARCHAR(100)
	, favorite_foods VARCHAR(200)
);


INSERT INTO person
(NAME, eye_color, birth_date, address, favorite_foods)
VALUES
('홍길동', 'BL', '2020-10-10', '대구시 중구', '김치찌개');


INSERT INTO person
VALUES
('홍길동2', 'BL', '2020-10-10', '대구시 중구', '김치찌개');

-- 테이블 컬럼 순서 변경 후 실행하면 에러 발생
INSERT INTO person
VALUES
('홍길동3', 'BL', '2020-10-10', '대구시 중구', '김치찌개');


INSERT INTO person
(NAME, eye_color, birth_date, address, favorite_foods)
VALUES
('홍길동4', 'BL', '2020-10-10', '대구시 중구', '김치찌개');


INSERT INTO person
SET NAME = '신사임당'
, eye_color = 'GR'
, birth_date = '2019-01-02'
, address = '서울시 서초동'
, favorite_foods = '한우 스테이크';


INSERT INTO person
(NAME, eye_color, birth_date, address, favorite_foods)
VALUES
  ('홍길동5', 'BL', '2020-10-10', '대구시 중구', '김치찌개')
, ('홍길동6', 'BL', '2020-10-10', '대구시 중구', '김치찌개')
;


INSERT INTO person
(NAME, eye_color, birth_date, address, favorite_foods)
SELECT '홍길동7', 'BR', '2011-08-12', '경상북도 포항', '과메기'; 


-- check 제약조건
INSERT INTO person
SET NAME = '신사임당2'
, eye_color = 'BB'
, birth_date = '2019-01-02'
, address = '서울시 서초동'
, favorite_foods = '한우 스테이크';


-- select 
SELECT * FROM person;

-- 특정 컬럼만 보고 싶은 경우 select from 사이에 컬럼명 작성
SELECT NAME, birth_date FROM person;

-- 표시 컬럼명을 변경하고 싶을 때
SELECT name AS 이름, birth_date 생년월일 FROM person; 

-- where절(조건문)이 있으면 부분 rows를 가져오겠다는 의미
-- '대구시 중구'에 살고 있는 사람 정보
SELECT * FROM person
WHERE address = '대구시 중구';

-- 0:false, 1:true 결과가 true인 row만 가져오게 된다.
SELECT *, address = '대구시 중구' FROM person;

/*
조건을 추가하고 싶다면 AND, OR을 사용해야 한다.

1 AND 0 OR 1   >>> true
>> 1 AND 0  =  false
>> 0 OR 1  =  true


1 AND (0 OR 1)   >>> true
>> 0 OR 1  =  true
>> 1 AND 1  = true



*/

-- '대구시 중구'에 살면서 눈 색상이 'GR'인 사람의 정보를 보고 싶다.
SELECT * FROM person
WHERE address = '대구시 중구' -- 1천명
AND eye_color = 'GR'; -- 1만명

SELECT *, address = '대구시 중구', eye_color = 'GR'
FROM person;


SELECT * FROM person
WHERE address = '대구시 중구'
OR eye_color = 'GR';

-- 수정
UPDATE person
   SET eye_color = 'BR'
WHERE NAME = '홍길동2';	

-- 홍길동6, 눈색상은 GR, 좋아하는 음식은 '된장찌개'로 변경
UPDATE person
SET eye_color = 'GR'
  , favorite_foods = '된장찌개'
WHERE NAME = '홍길동6';

-- where 조건에 맞는 row가 없기 때문에 영향받은 행의 수는 0이다. 
-- 에러가 터지진 않는다. 에러는 문법상 문제가 있을 때 터진다.
UPDATE person
SET eye_color = 'GR'
  , favorite_foods = '된장찌개'
WHERE NAME = '홍길동9';

-- row 표시 순서 정렬 order by, 오름차순 ASC, 내림차순 DESC
SELECT * FROM person   
ORDER BY eye_color DESC, NAME DESC;


-- 그룹 정렬 (group by 문장이 있느냐 없느냐)
-- 그룹 함수 (min, max, count, avg, sum)

SELECT COUNT(*) FROM person;
SELECT MIN(address) FROM person;
SELECT MAX(address) FROM person;

SELECT eye_color, COUNT(*), MIN(address), MAX(address) FROM person
GROUP BY eye_color;

-- 중복 제거 처리는 내부적으로 group by로 해결한다.
SELECT distinct eye_color FROM person;

-- LIKE _ %
SELECT * FROM person
WHERE NAME LIKE '%길동%';

SELECT * FROM person
WHERE NAME LIKE '%길동_';


-- union(중복 제거), union all(중복 허용)

SELECT NAME FROM person;

SELECT address FROM person;

-- 10 rows 
SELECT NAME FROM person
UNION 
SELECT address FROM person;

-- 14 rows 
SELECT NAME FROM person
UNION ALL
SELECT address FROM person;


-- limit
-- limit 인자 1개
-- 이 결과에서
SELECT * FROM person;
-- 위 2row만 가져온다. 
SELECT * FROM person LIMIT 2;


-- 이 결과에서
SELECT * FROM person ORDER BY eye_color;
-- 위 3row만 가져온다. 
SELECT * FROM person ORDER BY eye_color LIMIT 3;


-- limit 인자 2개 (index, length)
-- 이 결과에서
SELECT * FROM person;

SELECT * FROM person LIMIT 2, 3;

-- 서브 쿼리
-- 위치: select-from(column) 사이, from 절(인라인 뷰), where 절

-- 스칼라 값 (Scalar) 

SELECT '홍길동';

-- select-from(column)사이는 서브 쿼리 결과가 스칼라 값이어야 한다.
-- 에러 발생
SELECT NAME, (SELECT address FROM person) FROM person;

SELECT address FROM person;

-- 스칼라값
SELECT address FROM person WHERE address LIKE '%포항%';

-- 문제 없음
SELECT NAME, (SELECT address FROM person WHERE address LIKE '%포항%')
FROM person;

SELECT NAME, (SELECT address FROM person WHERE address LIKE '%포항%') AS 'ㅋㅋ'
FROM person;


/*
 인라인 뷰(inline view), Alias 필수, from절에서 서브쿼리 사용
 테이블 이름도 alias(별명) 줄 수 있다. 
 i.g. 테이블 이름 AS B 
      테이블 이름 B
*/
SELECT *
FROM (
	SELECT NAME, address FROM person
) A;


-- where절에서 서브 쿼리
-- 스칼라 값인 경우 = (equal) 사용

-- 스칼라값
SELECT * FROM person
WHERE NAME = (SELECT name FROM person WHERE address LIKE '%포항%');

SELECT * FROM person
WHERE NAME = '홍길동7';


-- 컬럼1개 row가 많은 경우 (in 연산자 사용)
SELECT eye_color FROM person WHERE address = '대구시 중구';

SELECT * FROM person
WHERE eye_color IN (SELECT eye_color FROM person 
							WHERE address = '대구시 중구');
							
-- 컬럼2개 row가 많은 경우 (in 연산자 사용)
SELECT NAME, address FROM person WHERE favorite_foods = '김치찌개';

SELECT * FROM person
WHERE (NAME, address) IN (SELECT NAME, address FROM person 
									WHERE favorite_foods = '김치찌개')
;

-- delete, row삭제 
SELECT @@autocommit; -- (세션)autocommit 상태 확인
SET autocommit = 0; -- autocommit off (끄기)

DELETE FROM person
WHERE NAME = '홍길동5';

ROLLBACK; -- 원 상태로 복원
COMMIT; -- 현 상태로 적용

 

sakila 샘플 데이터베이스 import

 

address 테이블의 location 컬럼은 GEOMETRY 데이터 타입이라서 이상하게 표시된다. 사람이 보기 편하게 변경해보자.

SELECT ST_ASTEXT(location)
FROM address
LIMIT 10;

 

2. Mission

최소 일주일 동안 대여할 수 있는 G등급(rating)의 영화를 찾고 싶다. rental_duration (대여가능기간)

SELECT * 
  FROM film
 WHERE rating = 'G' 
   AND rental_duration >= 7;

 

최소 일주일 동안 대여할 수 있는 G등급(rating)의 영화이거나
 PG-13등급이면서 3일 이하로만 대여할 수 있는 영화의 정보
 rental_duration (대여가능기간)

SELECT * 
  FROM film
 WHERE (rating = 'G' AND rental_duration >= 7)
    OR (rating = 'PG-13' AND rental_duration <= 3);

 

40편 이상의 영화를 대여한 모든 고객의 정보 >> 고객의 PK
표시 컬럼: 이름, 성, 갯수
7 rows
고객: customer
대여: rental

-- 고객별 대여 수
SELECT customer_id, COUNT(*) cnt
FROM rental
GROUP BY customer_id;


-- group by 결과의 조건문을 사용하고 싶으면 having절을 사용
SELECT R.customer_id, C.first_name, C.last_name, COUNT(*) cnt
FROM rental R
INNER JOIN customer C
ON C.customer_id = R.customer_id
GROUP BY R.customer_id
HAVING cnt >= 40;


SELECT C.first_name, C.last_name, R.cnt
FROM customer C
INNER JOIN (
SELECT customer_id, COUNT(customer_id) AS cnt 
  FROM rental
 GROUP BY customer_id
 HAVING cnt >= 40
) R
ON C.customer_id = R.customer_id;


SELECT C.first_name, C.last_name, COUNT(*) cnt
FROM customer C
INNER JOIN rental R
ON C.customer_id = R.customer_id
GROUP BY R.customer_id
HAVING cnt >= 40;

 

-- 위험! 동명이인이 있을 수 있음
SELECT C.first_name, C.last_name, COUNT(*) cnt
FROM customer C
INNER JOIN rental R
ON C.customer_id = R.customer_id
GROUP BY C.first_name, C.last_name
HAVING cnt >= 40; 

 

2005년 06월 14일에 대여한 모든 고객 정보

SELECT * FROM rental;

SELECT rental_date, DATE(rental_date) FROM rental;

SELECT C.first_name, C.last_name, TIME(rental_date)
FROM customer C
INNER JOIN rental R
ON R.customer_id = C.customer_id
WHERE DATE(R.rental_date) = '2005-06-14';

SELECT C.first_name, C.last_name, R.rental_date
FROM customer C
INNER JOIN rental R
ON R.customer_id = C.customer_id
WHERE R.rental_date BETWEEN '2005-06-13 23:59:59' AND '2005-06-14 23:59:59';

 

 

date() 함수를 사용하면 모든 rows를 변경하는 작업이 추가되기 때문에 사용하지 않는 것이 좋다. 

 

프로파일링으로 쿼리 속도 확인

-- 프로파일링 상태 확인
SELECT @@profiling;

-- 프로파일링 활성화
SET profiling = 1;

-- 프로파일링 비활성화
SET profiling = 0;

-- 프로파일링 히스토리 리셋 후 저장 공간확보
SET @@profiling_history_size = 0;
SET @@profiling_history_size = 10;

-- 프로파일링 내용 확인
SHOW PROFILES;

 

3.8.1 실습 3-1
모든 배우의 배우 ID, 이름 및 성을 검색합니다.
성 기준으로 정렬한 다음 이름 기준으로 정렬합니다.

SELECT actor_id, first_name, last_name
FROM actor
ORDER BY last_name, first_name;

 

3.8.2 실습 3-2
성이 'WILLIAMS' 또는 'DAVIS'인 모든 배우의 배우 ID, 
이름 및 성을 검색합니다.

SELECT actor_id, first_name, last_name
FROM actor
WHERE last_name IN ('WILLIAMS', 'DAVIS');

 

3.8.3 실습 3-3
rental 테이블에서 2005년 7월 5일 영화를 대여한 고객의 ID를
반환하는 쿼리를 작성합니다. (rental.rental_date 열을 사용)
각 고객ID는 하나의 행을 포함합니다.

SELECT customer_id
FROM rental
WHERE rental_date BETWEEN '2005-07-05 00:00:00' 
AND '2005-07-05 23:59:59';


SELECT customer_id
FROM rental
WHERE date(rental_date) = '2005-07-05';

 

2005년 6월 14일 이외의 날짜에 대여한 영화에 대한 
모든 고객의 이메일 주소를 반환 (중복제거)
599 rows

SELECT DISTINCT C.email
   FROM customer C
  INNER JOIN rental R
        ON R.customer_id = C.customer_id
WHERE R.rental_date NOT BETWEEN '2005-06-14 00:00:00' 
      AND '2005-06-14 23:59:59';