# a5 데이터베이스 삭제/생성/선택
DROP DATABASE IF EXIT `a5`;
CREATE DATABASE `a5`;
USE `a5`;
# 부서(dept) 테이블 생성 및 홍보부서 기획부서 추가
DROP TABLE dept;
CREATE TABLE dept(
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY(id),
regDate DATETIME NOT NULL,
`name` CHAR(100) NOT NULL
);
INSERT INTO dept
SET regDate = NOW(),
`name` = '홍보';
INSERT INTO dept
SET regDate = NOW(),
`name` = '기획';
# 사원(emp) 테이블 생성 및 홍길동사원(홍보부서), 홍길순사원(홍보부서), 임꺽정사원(기획부서) 추가
CREATE TABLE emp(
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY(id),
regDate DATETIME NOT NULL,
`name` CHAR(100) NOT NULL,
deptName CHAR(100) NOT NULL
);
INSERT INTO emp
SET regDate = NOW(),
`name` = '홍길동',
deptName = '홍보';
INSERT INTO emp
SET regDate = NOW(),
`name` = '홍길순',
deptName = '홍보';
INSERT INTO emp
SET regDate = NOW(),
`name` = '임꺽정',
deptName = '기획';
DELETE
FROM emp
WHERE id = 5;
SELECT *
FROM emp;
# 홍보를 마케팅으로 변경
UPDATE dept
SET `name` = '마케팅'
WHERE `name` = '홍보';
# 마케팅을 홍보로 변경
UPDATE dept
SET `name` = '홍보'
WHERE `name` = '마케팅';
# 홍보를 마케팅으로 변경
UPDATE dept
SET `name` = '마케팅'
WHERE `name` = '홍보';
# 구조를 변경하기로 결정(사원 테이블에서, 이제는 부서를 이름이 아닌 번호로 기억)
ALTER TABLE emp ADD COLUMN deptId INT(10) UNSIGNED NOT NULL;
UPDATE emp
SET deptId = 1
WHERE deptName = '홍보';
UPDATE emp
SET deptId = 2
WHERE deptName = '기획';
# 사장님께 드릴 인명록을 생성
SELECT *
FROM emp;
# 사장님께서 부서번호가 아니라 부서명을 알고 싶어하신다.
# 그래서 dept 테이블 조회법을 알려드리고 혼이 났다.
# 사장님께 드릴 인명록을 생성(v2, 부서명 포함, ON 없이)
# 이상한 데이터가 생성되어서 혼남
# 사장님께 드릴 인명록을 생성(v3, 부서명 포함, 올바른 조인 룰(ON) 적용)
# 보고용으로 좀 더 편하게 보여지도록 고쳐야 한다고 지적받음
# 사장님께 드릴 인명록을 생성(v4, 사장님께서 보시기에 편한 칼럼명(AS))
SELECT emp.id AS `사원번호`,
emp.name AS `사원명`,
DATE(emp.regDate) AS `입사일`,
dept.name AS `부서명`
FROM emp
INNER JOIN dept
ON emp.deptId = dept.id
ORDER BY `부서명`, `사원번호`;
SHOW TABLES;
DROP TABLE dept;
DROP TABLE emp;
# 부서(홍보, 기획)
CREATE TABLE dept(
id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
regDate DATETIME NOT NULL,
`name` CHAR(100) NOT NULL UNIQUE
);
INSERT INTO dept
SET regDate = NOW(),
`name` = '홍보';
INSERT INTO dept
SET regDate = NOW(),
`name` = '기획';
# 사원(홍길동/홍보/5000만원, 홍길순/홍보/6000만원, 임꺽정/기획/4000만원)
CREATE TABLE emp (
id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
regDate DATETIME NOT NULL,
`name` CHAR(100) NOT NULL,
deptId INT UNSIGNED NOT NULL,
salary INT UNSIGNED NOT NULL
);
INSERT INTO emp
SET regDate = NOW(),
`name` = '홍길동',
deptId = 1,
salary = 5000;
INSERT INTO emp
SET regDate = NOW(),
`name` = '홍길순',
deptId = 1,
salary = 6000;
INSERT INTO emp
SET regDate = NOW(),
`name` = '임꺽정',
deptId = 2,
salary = 4000;
SELECT *
FROM emp;
DELETE
FROM emp
WHERE id = 4;
# 사원 수 출력
SELECT COUNT(*)
FROM emp;
# 가장 큰 사원 번호 출력
SELECT MAX(id)
FROM emp;
# 가장 고액 연봉
SELECT MAX(salary)
FROM emp;
# 가장 저액 연봉
SELECT MIN(salary)
FROM emp;
# 회사에서 1년 고정 지출(인건비)
SELECT SUM(salary)
FROM emp;
# 부서별, 1년 고정 지출(인건비)
SELECT deptId, SUM(salary)
FROM emp
GROUP BY deptId;
# 부서별, 최고연봉
SELECT deptId, MAX(salary)
FROM emp
GROUP BY deptId
LIMIT 1;
# 부서별, 최저연봉
SELECT deptId, MIN(salary)
FROM emp
GROUP BY deptId;
# 부서별, 평균연봉
SELECT deptId, AVG(salary)
FROM emp
GROUP BY deptId;
# 부서별, 부서명, 사원리스트, 평균연봉, 최고연봉, 최소연봉, 사원수
## V1(조인 안한 버전)
SELECT E.deptId AS 부서번호,
GROUP_CONCAT(E.name) AS 사원리스트,
TRUNCATE(AVG(E.salary), 0) AS 평균연봉,
MAX(E.salary) AS 최고연봉,
MIN(E.salary) AS 최소연봉,
COUNT(*) A`jam``jam``jam`S 사원수
FROM emp AS E
GROUP BY E.deptId;
## V2(조인해서 부서명까지 나오는 버전)
SELECT D.name AS 부서,
GROUP_CONCAT(E.name) AS 사원리스트,
TRUNCATE(AVG(E.salary), 0) AS 평균연봉,
MAX(E.salary) AS 최고연봉,
MIN(E.salary) AS 최소연봉,
COUNT(*) AS 사원수
FROM emp AS E
INNER JOIN dept AS D
ON E.deptId = D.id
GROUP BY E.deptId;
## V3(V2에서 평균연봉이 5000이상인 부서로 추리기)
SELECT D.name AS 부서,
GROUP_CONCAT(E.name) AS 사원리스트,
TRUNCATE(AVG(E.salary), 0) AS 평균연봉,
MAX(E.salary) AS 최고연봉,
MIN(E.salary) AS 최소연봉,
COUNT(*) AS 사원수
FROM emp AS E
INNER JOIN dept AS D
ON E.deptId = D.id
GROUP BY E.deptId
HAVING `평균연봉` >= 5000;
## V4(V3에서 HAVING 없이 서브쿼리로 수행)
### HINT, UNION을 이용한 서브쿼리
# SELECT *
# FROM (
# select 1 AS id
# union
# select 2
# UNION
# select 3
# ) AS A
SELECT *
FROM (
SELECT D.name AS `부서명`,
GROUP_CONCAT(E.`name`) AS `사원리스트`,
TRUNCATE(AVG(E.salary), 0) AS `평균연봉`,
MAX(E.salary) AS `최고연봉`,
MIN(E.salary) AS `최소연봉`,
COUNT(*) AS `사원수`
FROM emp AS E
INNER JOIN dept AS D
ON E.deptId = D.id
WHERE 1
GROUP BY E.deptId
) AS D
WHERE D.`평균연봉` >= 5000;
#################
#상황 : 커뮤니티 사이트 DB를 구축해야 합니다.
#조건 : member(회원), article(게시물) 테이블을 구현해주세요.
#조건 : 비회원은 글을 쓸 수 없습니다.
#조건 : 게시물 상세페이지에서는 제목, 내용, 작성날짜, 작성자가 보여야 합니다.
#조건 : 특정 게시물을 어떤 회원이 작성했는지 알 수 있어야 합니다.
#조건 : 회원이 자신의 이름을 바꾸면, 그 회원이 예전에 쓴 글에서도 작성자가 자동으로 변경되어야 합니다.
#조건 : 회원 아이디와 비번의 칼럼명은, loginId 와 loginPw 로 해주세요.
#조건 : loginId에는 unique 인덱스를 걸어주세요.
#조건 : 회원 2명이 가입을 햇습니다.
#조건 : 1번 회원은 글을 2개(글 1번, 3번), 2번 회원은 글을 1개(2번) 썻습니다.
# 회원 테이블 생성, loginId, loginPw, `name`
## 조건 : loginId 칼럼에 UNIQUE INDEX 없이
CREATE TABLE `member` (
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
regDate DATETIME NOT NULL,
loginId CHAR(50) NOT NULL,
loginPw VARCHAR(100) NOT NULL,
`name` CHAR(100) NOT NULL
);
# 회원 2명 생성
## 조건 : (loginId = 'user1', loginPw = 'user1', `name` = '홍길동')
## 조건 : (loginId = 'user2', loginPw = 'user2', `name` = '홍길순')
INSERT INTO `member`
SET regDate = NOW(),
loginId = 'user1',
loginPw = 'user1',
`name` = '홍길동';
INSERT INTO `member`
SET regDate = NOW(),
loginId = 'user2',
loginPw = 'user2',
`name` = '홍길순';
# 회원 2배 증가 쿼리만들고 회원이 백만명 넘을 때 까지 반복 실행
## 힌트1 : INSERT INTO `tableName` (col1, col2, col3, col4)
## 힌트2 : SELECT NOW(), UUID(), 'pw', '아무개'
INSERT INTO `member` (regDate, loginId, loginPw, `name`)
SELECT NOW(), UUID(), 'pw', '아무개'
FROM `member`;
# 회원수 확인
SELECT COUNT(*)
FROM `member`;
# 검색속도 확인
## 힌트 : SQL_NO_CACHE
SELECT SQL_NO_CACHE *
FROM `member`
WHERE loginId = 'user1';
# 유니크 인덱스를 loginID 칼럼에 걸기
## 설명 : mysql이 loginId의 고속검색을 위한 부가데이터를 자동으로 관리(생성/수정/삭제) 한다.
## 설명 : 이게 있고 없고가, 특정 상황에서 어마어마한 성능차이를 가져온다.
## 설명 : 생성된 인덱스의 이름은 기본적으로 칼럼명과 같다.
ALTER TABLE `member` ADD UNIQUE INDEX (`loginId`);
# 검색속도 확인, loginId 가 'user1' 인 회원 검색
SELECT SQL_NO_CACHE *
FROM `member`
WHERE loginId = 'user1';
# 인덱스 삭제, `loginId` 이라는 이름의 인덱스 삭제
ALTER TABLE `member` DROP INDEX `loginId`;
# 회원 테이블 삭제
DROP TABLE `member`;
# 회원 테이블을 생성하는데, loginId에 uniqueIndex 까지 걸어주세요.
CREATE TABLE `member` (
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
regDate DATETIME NOT NULL,
loginId CHAR(50) UNIQUE NOT NULL,
loginPw VARCHAR(100) NOT NULL,
`name` CHAR(100) NOT NULL
);
# 회원 2명 생성
## 조건 : (loginId = 'user1', loginPw = 'user1', `name` = '홍길동')
## 조건 : (loginId = 'user2', loginPw = 'user2', `name` = '홍길순')
INSERT INTO `member`
SET regDate = NOW(),
loginId = 'user1',
loginPw = 'user1',
`name` = '홍길동';
INSERT INTO `member`
SET regDate = NOW(),
loginId = 'user2',
loginPw = 'user2',
`name` = '홍길순';
# 회원수 확인
SELECT COUNT(*) FROM `member`;
# 인덱스 쓰는지 확인
## 힌트 : EXPLAIN SELECT SQL_NO_CACHE * ~
EXPLAIN SELECT SQL_NO_CACHE *
FROM `member`
WHERE loginId = 'user1';
# 게시물 테이블 생성(title, body, writerName, memberId)
DROP TABLE `article`;
CREATE TABLE `article` (
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
regDate DATETIME NOT NULL,
title CHAR(200) NOT NULL,
`body` TEXT NOT NULL,
`writerName` CHAR(100) NOT NULL,
memberId INT(10) UNSIGNED NOT NULL
);
# 1번 회원이 글 1 작성(title = '글 1 제목', `body` = '글 1 내용')
INSERT INTO article
SET regDate = NOW(),
title = '글 1 제목',
`body` = '글 1 내용',
writerName = '홍길동',
memberId = 1;
# 2번 회원이 글 2 작성(title = '글 2 제목', `body` = '글 2 내용')
INSERT INTO article
SET regDate = NOW(),
title = '글 2 제목',
`body` = '글 2 내용',
writerName = '홍길순',
memberId = 2;
# 1번 회원이 글 3 작성(title = '글 3 제목', `body` = '글 3 내용')
INSERT INTO article
SET regDate = NOW(),
title = '글 3 제목',
`body` = '글 3 내용',
writerName = '홍길동',
memberId = 1;
# 전체글 조회
SELECT *
FROM article;
# 1번회원의 이름변경 홍길동 => 홍길동2
UPDATE `member`
SET `name` = '홍길동2'
WHERE id = 1;
# 전체글 조회, 여전히 게시물 테이블에는 이전 이름이 남아 있음
SELECT *
FROM article;
# 게시물 테이블에서 writerName 삭제
ALTER TABLE article DROP COLUMN writerName;
# INNER JOIN 을 통해서 두 테이블을 조회한 결과를 합침, ON 없이
SELECT * FROM article
INNER JOIN `member`;
# INNER JOIN 을 통해서 두 테이블을 조회한 결과를 합침, 올바른 조인 조건
## 힌트 : 이걸로 조인조건을 걸 칼럼 조사
## SELECT article.id, article.memberId, member.id AS "회원테이블_번호"
## FROM article
## INNER JOIN `member`;
# 조인 완성, ON 까지 추가
SELECT A.*, M.name AS writerName
FROM article AS A
INNER JOIN `member` AS M
ON A.memberId = M.id;