[멋쟁이사자처럼] 11일차 회고

갈망했던 의자가 드디어 도착했다 !

 

 

오늘은 어제 마무리하지 못한 SQL을 거진 다 마무리한 것 같다.

어제 오늘 진도가 좀 빠르긴 했나보다..ㅎ 

 

필요없다고 생각이 드는 부분은 배제시키면서 필기하고 실기를 했지만 중간중간 강사님의 속도를 따라가지 못했다.

이번주 스터디그룹 발표가 나여서 git에 대한 발표를 준비하고 있으나 아직 많이 준비를 못했으니 오늘은 강의 종료 후에 아마.. 발표 준비를 좀 할 것 같다.

 

그리고 몇일 전 부터는 노션을 통해 필기를 하고 있는데 노션.. 생각 이상으로 많이 편해보인다.

기존에는 intellij에서 라이브템플릿을 많이 만들어서 최대한 md 파일을 작성하기 편하게 했었는데..

노션.. 이놈... 물건이다. 껄껄

 

내일부터 2일간은 강사님의 실시간 수업이 아닌 VOD 시청 수업이 진행된다고 하고 주제는 HTML, CSS, JAVA Script였으니.. 화이팅 해야겟다. 프론트는 너무 어렵...

 

오늘도 무탈한 하루였다 :)

 

 

2023.11.01

일일 학습 목표

• 9 ~ 6 수업
• 3시간 복습
• 블로그 글 1개
• 알고리즘 20분
• 기술영상 시청 2개

MySQL

Workbench error

MAC 복사 붙여넣기가 잘 안될 경우 :

cmd > 활성상태보기 > pboard 강제종료

(해도 잘 안되는 경우가 허다하다.. workbench 내부에서만 잘 안되는 걸로 보아 workbench에서 사용하는 메모리에 문제가 있는게 아닌지 의심이 된다.)

 

자동완성 기능 UPPERCASE로 :

setting > SQL Editor.Query Editor > Use UPPERCASE keywords on completion 체크

INNER JOIN

INNER JOIN은 관계형 데이터베이스에서 사용되는 조인 연산자입니다. 이를 사용하면 두 개 이상의 테이블에서 공통된 값을 기준으로 연결된 레코드를 가져올 수 있습니다.

INNER JOIN의 일반적인 구문은 다음과 같습니다:

SELECT column1, column2, ...
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

예를 들어, customers 테이블과 orders 테이블이 있다고 가정해봅시다. customers 테이블에는 고객 정보가, orders 테이블에는 주문 정보가 저장되어 있습니다. 이 두 테이블을 CustomerID 열을 기준으로 연결하려면 다음과 같이 INNER JOIN을 사용할 수 있습니다:

SELECT customers.CustomerName, orders.OrderID
FROM customers
INNER JOIN orders
ON customers.CustomerID = orders.CustomerID;

이 쿼리는 customers 테이블과 orders 테이블을 CustomerID 열을 기준으로 연결하고, 고객 이름과 주문 ID를 조회합니다.

INNER JOIN을 사용하여 두 개 이상의 테이블을 연결하면 데이터를 효율적으로 관리하고 필요한 정보를 가져올 수 있습니다.

GROUP BY

GROUP BY 구문은 SELECT 문과 함께 사용되며, 특정 열을 기준으로 결과를 그룹화하는 데 사용됩니다. 그룹화된 결과에 대한 집계 함수 (예: SUM, COUNT, AVG 등)를 사용하여 그룹 단위로 계산된 값을 얻을 수 있습니다.

GROUP BY 구문의 일반적인 구문은 다음과 같습니다:

SELECT column1, column2, ...
FROM table
GROUP BY column1, column2, ...;

예를 들어, article 테이블에서 title 열을 기준으로 그룹화하여 각 그룹 내의 게시물 수를 계산하려면 다음과 같이 쿼리를 작성할 수 있습니다:

SELECT title, COUNT(*)
FROM article
GROUP BY title;

이 쿼리는 article 테이블에서 각 게시물의 제목을 그룹화하고, 각 그룹 내의 게시물 수를 계산하여 조회합니다.

GROUP BY 구문을 사용하여 그룹화된 결과에 조건을 지정하려면 HAVING 구문을 사용할 수 있습니다. HAVING 구문은 WHERE 구문과 유사하지만, 그룹화된 결과에 대한 조건을 검사하여 필터링합니다.

예를 들어, '2023년 11월 1일' 이후에 등록된 게시물들 중에서 동일한 제목을 가진 게시물들을 그룹화하고, 각 그룹에 속하는 게시물의 수를 조회하려면 다음과 같이 쿼리를 작성할 수 있습니다:

SELECT title, COUNT(*)
FROM article
GROUP BY title
HAVING regDate > '2023-11-01';

이 쿼리는 '2023년 11월 1일' 이후에 등록된 게시물들 중에서 동일한 제목을 가진 게시물들을 그룹화하고, 각 그룹에 속하는 게시물의 수를 조회합니다.

GROUP BY 구문은 데이터를 그룹화하고 그룹 단위로 계산된 값을 얻기 위해 유용한 기능입니다.

HAVING

HAVING 절은 GROUP BY 절과 함께 사용되며, 그룹화된 결과에 대한 조건을 지정하는 데 사용됩니다. HAVING 절은 WHERE 절과 유사하지만, 그룹화된 결과에 대한 조건을 검사하여 결과를 필터링합니다.

HAVING 절의 구문은 다음과 같습니다:

SELECT column1, column2, ...
FROM table
GROUP BY column1, column2, ...
HAVING condition;

HAVING 절은 그룹화된 결과에서 특정 조건을 만족하는 그룹만 선택하고자 할 때 사용됩니다. 예를 들어, 그룹화된 결과에서 특정 조건을 만족하는 그룹의 합계, 평균, 최대값 등을 구하고자 할 때 HAVING 절을 사용할 수 있습니다.

예를 들어, 아래의 쿼리는 article 테이블에서 regDate가 '2023년 11월 1일' 이후인 그룹의 title과 COUNT(*)을 조회합니다:

SELECT title, COUNT(*)
FROM article
GROUP BY title
HAVING regDate > '2023-11-01';

이 쿼리는 '2023년 11월 1일' 이후에 등록된 게시물들 중에서 동일한 제목을 가진 게시물들을 그룹화하고, 각 그룹에 속하는 게시물의 수를 조회합니다.

HAVING 절은 GROUP BY 절과 함께 사용되므로, GROUP BY 절을 사용하지 않은 쿼리에서는 HAVING 절을 사용할 수 없습니다.

Mission

문제1 풀이

# 전체 데이터베이스 리스팅
SHOW DATABASES;

# `mysql` 데이터 베이스 선택
USE mysql;

# 테이블 리스팅
SHOW TABLES;

# 특정 테이블의 구조
DESC db;

# `test` 데이터 베이스 선택(없으면 먼저 만들어주세요.)
USE test;
CREATE DATABASE IF NOT EXISTS test;

# 테이블 리스팅
SHOW TABLES;

# 기존에 a1 데이터베이스가 존재 한다면 삭제
DROP DATABASE IF EXISTS a1;

# 새 데이터베이스(`a1`) 생성
CREATE DATABASE a1;

# 데이터베이스(`a1`) 선택
USE a1;

# 데이터베이스 추가 되었는지 확인
SHOW DATABASES;

# 테이블 확인
SHOW TABLES;

# 게시물 테이블 article(title, body)을 만듭니다.
# VARCHAR(100) => 문자 100개 저장가능
# text => 문자 많이 저장가능
CREATE TABLE article(
	title VARCHAR(100),
    body TEXT
);

# 잘 추가되었는지 확인, 리스팅과 구조까지 확인
DESC article;

# 데이터 하나 추가(title = 제목, body = 내용)
INSERT INTO article VALUE ("제목","내용");

# 데이터 조회(title 만)
SELECT title FROM article;

# 데이터 조회(title, body)
SELECT title,body FROM article;

# 데이터 조회(body, title)
SELECT body,title FROM article;

# 데이터 조회(*)
SELECT * FROM article;

# 데이터 또 하나 추가(title = 제목, body = 내용)
INSERT INTO article VALUE ("제목2","내용2");

# 데이터 조회(*, 어떤게 2번 게시물인지 알 수 없음)
SELECT * FROM article;

# 테이블 구조 수정(id 칼럼 추가, first)
ALTER TABLE article ADD id INT;

# 데이터 조회(*, id 칼럼의 값은 NULL)
SELECT * FROM article;

# 기존 데이터에 id값 추가(id = 1, id IS NULL)
UPDATE article
SET id = 1;

# 데이터 조회(*, 둘다 수정되어 버림..)
SELECT * FROM article;

# 기존 데이터 중 1개만 id를 2로 변경(LIMIT 1)
UPDATE article
SET id = 1
LIMIT 1;

# 데이터 조회(*)
SELECT * FROM article;

# 데이터 1개 추가(id = 3, title = 제목3, body = 내용3)
INSERT INTO article VALUE ("제목3","내용3", 3);

# 데이터 조회(*)
SELECT * FROM article;

# 2번 게시물, 데이터 삭제 => DELETE
DELETE FROM article WHERE id = 2;

# 데이터 조회(*)
SELECT * FROM article;

# 날짜 칼럼 추가(id 칼럼 뒤에) => regDate DATETIME
ALTER TABLE article ADD regDate DATETIME;

# 테이블 구조 확인
DESC article;

# 데이터 조회(*, 날짜 정보가 비어있음)
SELECT * FROM article;

# 1번 게시물의 비어있는 날짜정보 채움(regDate = 2018-08-10 15:00:00)
UPDATE article
SET regDate = "2018-08-10 15:00:00"
WHERE id = 1;

# 데이터 조회(*, 이제 2번 게시물의 날짜 정보만 넣으면 됩니다.)
SELECT * FROM article;

# NOW() 함수 실행해보기
SELECT NOW();

# 3번 게시물의 비어있는 날짜정보 채움(NOW())
UPDATE article
SET regDate = NOW()
WHERE id = 3;

# 데이터 조회(*)
SELECT * FROM article;

문제2 풀이

# 기존에 a2 데이터베이스가 존재 한다면 삭제
DROP DATABASE IF EXISTS a2;

# 새 데이터베이스(`a2`) 생성
CREATE DATABASE a2;

# 새 데이터베이스(`a2`) 선택
USE a2;

# article 테이블 생성(id, regDate, title, body)
CREATE TABLE article(
	id int,
    regDate TIMESTAMP,
    title VARCHAR(100),
    body TEXT
);

# article 테이블 조회(*)
SELECT * FROM article;

# article 테이블에 data insert (regDate = NOW(), title = '제목', body = '내용')
INSERT INTO article(regDate,title,body) VALUE (NOW(), "제목", "내용");

# article 테이블에 data insert (regDate = NOW(), title = '제목', body = '내용')
INSERT INTO article(regDate,title,body) VALUE (NOW(), "제목", "내용");

# article 테이블 조회(*)
## id가 NULL인 데이터 생성이 가능하네?
SELECT * FROM article;

# id 데이터는 꼭 필수 이기 때문에 NULL을 허용하지 않게 바꾼다.(alter table, not null)
## 기존의 NULL값 때문에 실패가 뜬다.
ALTER TABLE article MODIFY COLUMN id INT NOT NULL;

# 기존의 NULL값이 0으로 바뀐다.
UPDATE article 
SET id = 0;

# NULL을 허용하지 않게 바꾼다.(alter table, not null)
ALTER TABLE article MODIFY COLUMN id INT NOT NULL;

# article 테이블 조회(*)
SELECT * FROM article;

# 생각해 보니 모든 행(row)의 id 값은 유니크 해야한다.(ADD PRIMARY KEY(id))
## 오류가 난다. 왜냐하면 기존의 데이터 중에서 중복되는게 있기 때문에
ALTER TABLE article ADD PRIMARY KEY(id);

# id가 0인 것 중에서 1개를 id 1로 바꾼다.
UPDATE article
SET id = 1
LIMIT 1;

# article 테이블 조회(*)
SELECT * FROM article;

# id가 0인것을 id 2로 바꾼다.
UPDATE article
SET id = 2
WHERE id = 0;

# 생각해 보니 모든 행(row)의 id 값은 유니크 해야한다.(ADD PRIMARY KEY(id))
## 이제 적용이 잘 된다.
ALTER TABLE article ADD PRIMARY KEY(id);

# id 칼럼에 auto_increment 를 건다.
## auto_increment 를 걸기전에 해당 칼럼은 무조건 key 여야 한다.
ALTER TABLE article MODIFY COLUMN id INT AUTO_INCREMENT;

# article 테이블 구조확인(desc)
DESC article;

# 나머지 칼럼 모두에도 not null을 적용해주세요.
ALTER TABLE article MODIFY COLUMN regDate TIMESTAMP NOT NULL;
ALTER TABLE article MODIFY COLUMN title VARCHAR(100) NOT NULL;
ALTER TABLE article MODIFY COLUMN body TEXT NOT NULL;

# id 칼럼에 UNSIGNED 속성을 추가하세요.
ALTER TABLE article MODIFY COLUMN id INT UNSIGNED;

# 작성자(writer) 칼럼을 title 칼럼 다음에 추가해주세요.
ALTER TABLE article ADD writer VARCHAR(100) AFTER title;
SELECT * FROM article;

# 작성자(writer) 칼럼의 이름을 nickname 으로 변경해주세요.(ALTER TABLE article CHANGE oldName newName TYPE 조건)
ALTER TABLE article CHANGE writer nickname VARCHAR(100);

# nickname 칼럼의 위치를 body 밑으로 보내주세요.(MODIFY COLUMN nickname)
ALTER TABLE article MODIFY COLUMN nickname VARCHAR(100) AFTER body;

# hit 조회수 칼럼 추가 한 후 삭제해주세요.
ALTER TABLE article ADD hit int;
ALTER TABLE article DROP COLUMN hit;

# hit 조회수 칼럼을 다시 추가
ALTER TABLE article ADD hit int;

# 기존의 비어있는 닉네임 채워넣기(무명)
UPDATE article
SET nickname = "무명"
WHERE nickname  IS NULL;

# article 테이블에 데이터 추가(regDate = NOW(), title = '제목3', body = '내용3', nickname = '홍길순', hit = 10)
INSERT INTO article SET 
regDate = NOW(), title = '제목3', body = '내용3', nickname = '홍길순', hit = 10;

# article 테이블에 데이터 추가(regDate = NOW(), title = '제목4', body = '내용4', nickname = '홍길동', hit = 55)
INSERT INTO article SET 
regDate = NOW(), title = '제목4', body = '내용4', nickname = '홍길동', hit = 55;

# article 테이블에 데이터 추가(regDate = NOW(), title = '제목5', body = '내용5', nickname = '홍길동', hit = 10)
INSERT INTO article SET 
regDate = NOW(), title = '제목5', body = '내용5', nickname = '홍길동', hit = 10;

# article 테이블에 데이터 추가(regDate = NOW(), title = '제목6', body = '내용6', nickname = '임꺽정', hit = 100)
INSERT INTO article SET 
regDate = NOW(), title = '제목6', body = '내용6', nickname = '임꺽정', hit = 100;

SELECT * FROM article;
# 조회수 가장 많은 게시물 3개 만 보여주세요., 힌트 : ORDER BY, LIMIT
SELECT * FROM article
ORDER BY hit DESC 
LIMIT 3;

# 작성자명이 '홍길'로 시작하는 게시물만 보여주세요., 힌트 : LIKE '홍길%'
SELECT * FROM article
WHERE nickname LIKE '홍길%';

# 조회수가 10 이상 55 이하 인것만 보여주세요., 힌트 : WHERE 조건1 AND 조건2
SELECT * FROM article
WHERE hit >= 10 AND 55 >= hit;

# 작성자가 '무명'이 아니고 조회수가 50 이하인 것만 보여주세요., 힌트 : !=
SELECT 
*
FROM article
WHERE nickname != "무명"
AND hit <= 50;

# 작성자가 '무명' 이거나 조회수가 55 이상인 게시물을 보여주세요. 힌트 : OR
SELECT 
*
FROM article
WHERE nickname = "무명"
OR hit >= 55;

문제3 풀이

# a5 데이터베이스 삭제/생성/선택
DROP DATABASE IF EXISTS a5;
CREATE DATABASE a5;
USE a5;

# 부서(dept) 테이블 생성 및 홍보부서 기획부서 추가
CREATE TABLE dept(
	name VARCHAR(100)
);

INSERT INTO dept VALUE ("홍보");
INSERT INTO dept VALUE ("기획");

# 사원(emp) 테이블 생성 및 홍길동사원(홍보부서), 홍길순사원(홍보부서), 임꺽정사원(기획부서) 추가
CREATE TABLE emp(
	name VARCHAR(100),
    dept VARCHAR(100)
);

INSERT INTO emp VALUE ("홍길동","홍보");
INSERT INTO emp VALUE ("홍길순","홍보");
INSERT INTO emp VALUE ("임꺽정","기획");

SELECT * FROM emp;
SELECT * FROM dept;

# 홍보를 마케팅으로 변경
UPDATE dept
SET name = "마케팅"
WHERE name = "홍보";

# 마케팅을 홍보로 변경
UPDATE dept
SET name = "홍보"
WHERE name = "마팅";

# 홍보를 마케팅으로 변경
## 구조를 변경하기로 결정(사원 테이블에서, 이제는 부서를 이름이 아닌 번호로 기억)
ALTER TABLE dept ADD id INT;
ALTER TABLE emp ADD dept_id INT;
ALTER TABLE emp DROP COLUMN dept;

UPDATE emp 
SET dept_id = 1
WHERE dept = "홍보";

UPDATE emp 
SET dept_id = 2
WHERE dept = "기획";

UPDATE dept
SET id = 0;

UPDATE dept
SET id = 1
LIMIT 1;

UPDATE dept
SET id = 2
WHERE id = 0
LIMIT 1;

ALTER TABLE dept MODIFY COLUMN id INT NOT NULL;

DELETE FROM dept
WHERE id = 0;

# 사장님께 드릴 인명록을 생성
SELECT * FROM emp;

# 사장님께서 부서번호가 아니라 부서명을 알고 싶어하신다.
## 그래서 dept 테이블 조회법을 알려드리고 혼이 났다.
SELECT * FROM dept;

# 사장님께 드릴 인명록을 생성(v2, 부서명 포함, ON 없이)
## 이상한 데이터가 생성되어서 혼남
SELECT 
	*
FROM emp AS E
INNER JOIN dept AS D;

# 사장님께 드릴 인명록을 생성(v3, 부서명 포함, 올바른 조인 룰(ON) 적용)
## 보고용으로 좀 더 편하게 보여지도록 고쳐야 한다고 지적받음
SELECT 
	E.name, 
	D.name
FROM emp AS E
INNER JOIN dept AS D
ON E.dept_id = D.id;

# 사장님께 드릴 인명록을 생성(v4, 사장님께서 보시기에 편한 칼럼명(AS))
SELECT 
	E.name AS 사원명, 
	D.name AS 부서명
FROM emp AS E
INNER JOIN dept AS D
ON E.dept_id = D.id;

# 사장님께 드릴 인명록을 생성(v5, 테이블 AS 적용)
SELECT 
	E.name AS 사원명, 
	D.name AS 부서명
FROM emp AS E
INNER JOIN dept AS D
ON E.dept_id = D.id;

문제4 풀이

# 현재 세션에서 `ONLY_FULL_GROUP_BY` 모드 끄기
## 영구적으로 설정되는 것은 아닙니다.
SET SESSION sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));
SET sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));

# a6 DB 삭제/생성/선택
DROP DATABASE IF EXISTS a6;
CREATE DATABASE a6;
USE a6;

# 부서(홍보, 기획)
CREATE TABLE dept (
    id INT 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;
 
# 사원 수 출력
SELECT COUNT(*) FROM emp;

# 가장 큰 사원 번호 출력
SELECT MAX(id) FROM emp;

# 가장 고액 연봉
SELECT MAX(salary) FROM emp;

# 가장 저액 연봉
SELECT MIN(salary) FROM emp;

# 회사에서 1년 고정 지출(인건비)
SELECT SUM(salary) * 12 FROM emp;

# 부서별, 1년 고정 지출(인건비)
SELECT deptId, SUM(salary)
FROM emp
GROUP BY deptId;

SELECT D.name, SUM(E.salary) * 12 
FROM emp AS E
INNER JOIN dept AS D
ON E.deptId = D.id
GROUP BY E.deptId;

# 부서별, 최고연봉
SELECT deptId, MAX(salary)
FROM emp
GROUP BY deptId;

# 부서별, 최저연봉
SELECT deptId, MIN(salary)
FROM emp
GROUP BY deptId;

# 부서별, 평균연봉
SELECT deptId, AVG(salary)
FROM emp
GROUP BY deptId;

# 부서별, 부서명, 사원리스트, 평균연봉, 최고연봉, 최소연봉, 사원수 
## V1(조인 안한 버전)
SELECT IF(deptId = 1, '홍보', '기획') AS `부서명`,
GROUP_CONCAT(`name` ORDER BY id DESC SEPARATOR ', ') AS `사원리스트`,
CONCAT(TRUNCATE(AVG(salary), 0), '만원') AS `평균연봉`,
CONCAT(MAX(salary), '만원') AS `최고연봉`,
CONCAT(MIN(salary), '만원') AS `최소연봉`,
CONCAT(COUNT(*), '명') AS `사원수`
FROM emp
GROUP BY 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 AVG(E.salary) >= 5000;

## V4(V3에서 HAVING 없이 서브쿼리로 수행)
### HINT, UNION을 이용한 서브쿼리
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;

문제5 풀이

# 현재 세션에서 `ONLY_FULL_GROUP_BY` 모드 끄기
## 영구적으로 설정되는 것은 아닙니다.
SET SESSION sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));
SET sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));

# a6 DB 삭제/생성/선택
DROP DATABASE IF EXISTS a6;
CREATE DATABASE a6;
USE a6;

# 부서(홍보, 기획)
CREATE TABLE dept (
    id INT 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;

# 1단계 : 각 부서별 최고연봉자의 연봉을 구한다.
SELECT E.deptId, MAX(E.salary),E.name
FROM emp AS E
GROUP BY E.deptId;

# 2단계 : 사원테이블과 부서테이블(서브쿼리)을 조인한다.
SELECT # 이건 원하는 결과를 가져다주지 않는다.
D.name AS "부서", 
E.name AS "이름", 
DATE(E.regDate) AS "입사일",
CONCAT(FORMAT(salary,0),"만 원") AS "연봉"
FROM dept AS D
INNER JOIN emp AS E
ON D.id = E.deptId;

SELECT 
	E.name AS "사원",
	CONCAT(FORMAT(E.salary,0), '만원') AS '연봉'
FROM emp AS E
INNER JOIN(
	SELECT 
		E.deptID AS 'deptID',
		MAX(E.salary) AS 'Maxsalary'
        FROM emp AS E
        GROUP BY E.deptId
)AS E2
ON E.deptId = E2.deptId
AND E.salary = E2.Maxsalary;

# 3단계 : 사원테이블과 부서테이블(서브쿼리)을 조인할 때 사원의 연봉과 부서의 최고연봉이 일치해야한 다는 조건을 추가해서, 최고연봉자가 아닌 사람들이 자연스럽게 필터링 되도록 한다.
SELECT 
	E.name AS "사원",
    E.deptId AS 부서,
	CONCAT(FORMAT(E.salary,0), '만원') AS '연봉'
FROM emp AS E
INNER JOIN(
	SELECT 
		E.deptID AS 'deptID',
		MAX(E.salary) AS 'Maxsalary'
        FROM emp AS E
        GROUP BY E.deptId
)AS E2
ON E.deptId = E2.deptId
AND E.salary = E2.Maxsalary;

# 4단계 : 추가 JOIN 을 통해서 부서명을 얻는다.
SELECT 
	E.name AS "사원",
    D.name AS 부서,
	CONCAT(FORMAT(E.salary,0), '만원') AS '연봉'
FROM emp AS E
INNER JOIN(
	SELECT 
		E.deptID AS 'deptID',
		MAX(E.salary) AS 'Maxsalary'
        FROM emp AS E
        GROUP BY E.deptId
)AS E2
ON E.deptId = E2.deptId
INNER JOIN dept AS D
ON E.deptId = D.id
AND E.salary = E2.Maxsalary;

문제6 풀이

# a6 DB 삭제/생성/선택
DROP DATABASE IF EXISTS a6;
CREATE DATABASE a6;
USE a6;

# 부서(홍보, 기획, IT)
CREATE TABLE dept (
    id INT 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` = '기획';

INSERT INTO dept
SET regDate = NOW(),
`name` = 'IT';

# 사원(홍길동/홍보/5000만원, 홍길순/홍보/6000만원, 임꺽정/기획/4000만원)
## IT부서는 아직 사원이 없음
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;

# 전 사원에 대하여, [부서명, 사원번호, 사원명] 양식으로 출력(IT 부서는 안나옴)
SELECT 
	D.name AS '부서',
    E.id AS '사번',
    E.name AS '이름'
FROM emp AS E
INNER JOIN dept AS D
ON E.deptId = D.id;

# 전 사원에 대하여, [부서명, 사원번호, 사원명] 양식으로 출력(IT 부서가 아직 사원이 없더라도, 1줄이라도 나오도록 해주세요, LEFT JOIN 필요)
## IT부서는 [IT, NULL, NULL] 으로 출력
SELECT 
	D.name AS 부서,
    E.id AS 사번,
    E.name AS 이름
FROM dept AS D
LEFT OUTER JOIN emp AS E
ON D.id = E.deptId;

# 전 사원에 대하여, [부서명, 사원번호, 사원명] 양식으로 출력
## IT부서는 [IT, 0, -] 으로 출력

# 모든 부서별, 최고연봉, IT부서는 0원으로 표시
SELECT
	D.name AS 부서,
    CONCAT(FORMAT(IF(E.salary IS NULL, 0 ,MAX(E.salary) ),0),"만원") AS 최고연봉
FROM dept AS D
LEFT OUTER JOIN emp AS E
ON D.id = E.deptId
GROUP BY D.id;

# 모든 부서별, 최저연봉, IT부서는 0원으로 표시
SELECT
	D.name AS 부서,
    CONCAT(FORMAT(IF(E.salary IS NULL, 0 ,MIN(E.salary) ),0),"만원") AS 최저연봉
FROM dept AS D
LEFT OUTER JOIN emp AS E
ON D.id = E.deptId
GROUP BY D.id;

# 모든 부서별, 평균연봉, IT부서는 0원으로 표시
SELECT
	D.name AS 부서,
    CONCAT(FORMAT(IF(E.salary IS NULL, 0 ,AVG(E.salary) ),0),"만원") AS 평균연봉
FROM dept AS D
LEFT OUTER JOIN emp AS E
ON D.id = E.deptId
GROUP BY D.id;

# 하나의 쿼리로 최고연봉자와 최저연봉자의 이름과 연봉
(
SELECT 
	name AS 이름,
	FORMAT(E.salary,0) AS 연봉,
    '최고' AS 타입
FROM emp AS E
ORDER BY E.salary DESC
LIMIT 1
)
UNION
(
SELECT 
	name AS 이름,
	FORMAT(E.salary,0) AS 연봉,
    '최저' AS 타입
FROM emp AS E
ORDER BY E.salary ASC
LIMIT 1
);

문제7 풀이

# 현재 세션에서 `ONLY_FULL_GROUP_BY` 모드 끄기
## 영구적으로 설정되는 것은 아닙니다.
SET SESSION sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));
SET sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));

# a6 DB 삭제/생성/선택
DROP DATABASE IF EXISTS a6;
CREATE DATABASE a6;
USE a6;

# 부서(홍보, 기획)
CREATE TABLE dept (
    id INT 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` = '기획';

INSERT INTO dept
SET regDate = NOW(),
`name` = 'IT';

# 사원(홍길동/홍보/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;
SELECT * FROM dept;

## 1단계 : dept 테이블과 CASE 문법으로 정답을 흉내내주세요.
SELECT D.name AS `부서명`,
CASE 
WHEN D.id = 1
THEN 2
WHEN D.id = 2
THEN 1
ELSE 0
END AS `사원수`
FROM dept AS D
ORDER BY D.id ASC;

## 2단계 : emp 테이블을 통해서 각 부서별 부서번호와 사원수를 출력해주세요. IT 부서는 누락되어도 됩니다.
SELECT 
	deptId AS 부서번호,
    COUNT(*) AS 사원수
FROM emp AS E
GROUP BY deptId;

## 3단계 : 2단계에서 부서번호를 부서명으로 변경해주세요. INNER JOIN 사용. IT 부서는 누락되어도 됩니다.
SELECT 
	D.name AS 부서번호,
    COUNT(*) AS 사원수
FROM emp AS E
INNER JOIN dept AS D
ON E.deptId = D.id
GROUP BY deptId;

## 4단계 : LEFT JOIN 을 사용하여, IT부서가 노출되도록 하고 GROUP BY 를 해제해주세요. 사원이 없으면 인원이 0명으로 나오게 해주세요.
SELECT 
	D.name AS 부서명,
	D.id AS 부서번호,
    IF(E.id IS NOT NULL, 1, 0) AS 사원수
FROM dept AS D
LEFT JOIN emp AS E
ON D.id = E.deptId;

## 5단계 : GROUP BY 와 SUM 을 통해서 각 부서별 부서명과 사원수를 출력해주세요.
## 정답 v1 : SUM 과 IF 를 사용한 버전
SELECT 
	D.name AS 부서명,
	SUM(IF(E.id IS NOT NULL, 1, 0)) AS 사원수
FROM dept AS D
LEFT JOIN emp AS E
ON D.id = E.deptId
GROUP BY D.id
ORDER BY D.id ASC;

## 6단계 : COUNT 를 통해서 각 부서별 부서명과 사원수를 출력해주세요.
## 정답 v2 : COUNT 를 사용한 버전
SELECT 
	D.name AS 부서명,
	COUNT(E.id) AS 사원수
FROM dept AS D
LEFT JOIN emp AS E
ON D.id = E.deptId
GROUP BY D.id
ORDER BY D.id ASC;

문제8 풀이

# 데이터베이스 a4가 존재하면 삭제
DROP DATABASE IF EXISTS a4;

# 데이터베이스 a4 생성
CREATE DATABASE a4;

# 데이터베이스 a4 선택
USE a4;

# 회원 테이블 생성, loginId, loginPw, `name`
## 조건 : loginId 칼럼에 UNIQUE INDEX 없이
CREATE TABLE `member` (
    id INT 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`); 

## 인덱스 확인
SHOW INDEX FROM `member`;

# 검색속도 확인, loginId 가 'user1' 인 회원 검색
SELECT SQL_NO_CACHE *
FROM `member`
WHERE loginId = 'user1';

# 인덱스 삭제, `loginId` 이라는 이름의 인덱스 삭제
ALTER TABLE `member` DROP INDEX `loginId`;

## 인덱스 확인
SHOW INDEX FROM `member`;

# 회원 테이블 삭제
DROP TABLE `member`;

# 회원 테이블을 생성하는데, loginId에 uniqueIndex 까지 걸어주세요.
CREATE TABLE `member` (
    id INT 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
);

## 인덱스 확인
SHOW INDEX FROM `member`;

# 회원 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만명 넘을 때 까지 반복 실행
## 힌트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`;

# 인덱스 쓰는지 확인
## 힌트 : EXPLAIN SELECT SQL_NO_CACHE * ~
EXPLAIN SELECT SQL_NO_CACHE *
FROM `member`
WHERE loginId = 'user1';

# 인덱스 삭제, `loginId` 이라는 이름의 인덱스 삭제
ALTER TABLE `member` DROP INDEX `loginId`;

## 인덱스 확인
SHOW INDEX FROM `member`;

# 인덱스 안쓰는지 확인
## 힌트 : EXPLAIN SELECT SQL_NO_CACHE * ~
EXPLAIN SELECT SQL_NO_CACHE *
FROM `member`
WHERE loginId = 'user1';

CHAR 와 VARCHAR 차이

• CHAR : 속도 빠름, 실제저장되는 데이터양에 상관없이 저장용량 소모
• VARCHAR : 속도 느림, 실제저장되는 데이터양에 저장용량 절약

DELETE 와 TRUNCATE 의 차이

• DELETE : 데이터만 삭제
• TRUNCATE : 데이터와 인덱스 데이터도 삭제
  • TRUNCATE 를 한 후 INSERT 를 하면 id(AUTO_INCREMENT) 에 1번이 삽입 됩니다.

칼럼에 인덱스를 거는것에 대한 장단점

• 장점 : SELECT 속도 향상
  • 인덱스를 걸면 풀스캔이 아니라 좁은범위검색 으로 찾을 수 있다.
    • 인덱스가 유니크 인덱스라면 좁은범위검색 + 발견즉시검색종료 의 효과까지 발생합니다.
• 단점 : INSERT, UPDATE, DELETE 속도 하락
  • 데이터를 추가, 수정, 삭제할 때 마다 그에 대응되는 색인(INDEX) 데이터도 추가, 수정, 삭제 되어야 하기 때문에

언제 인덱스를 걸어야 할까?

• 시스템의 속도가 느려질 때
  • 어디에 걸어야 할지 잘 모르겠다면, 그냥 걸지마세요.
• SLOW QUERY 라는 느린 쿼리 필터링 기술을 이용하면, 쉽게 쿼리튜닝을 통한 속도향상을 할 수 있습니다.

EXPLAIN

• SQL 앞에 EXPLAIN 을 붙이면, 해당 SQL을 실행하기 위한 실행계획을 확인할 수 있다.
  • 내가 만든 SQL이 실행될 때 INDEX 를 이용하는지 여부를 알 수 있다.

인덱스 종류

• PRIMARY KEY : 주키 인덱스
  • 유니크 + NOT NULL
• UNIQUE KEY : 유니크 인덱스
  • 유니크
• NORMAL KEY : 일반 인덱스
  • 유니크 없는 일반 인덱스
• FULL TEXT : 풀텍스트 인덱스
  • 본문검색에 사용할 수 있느 인덱스
  • 한글을 지원하지 않는다.
    • 엘라스틱 서치가 필요한 이유