[MySQL] 기초 문법 정리 (2 / 2)

mySql 데이터베이스

이전 글에서 이어집니다.

[Mysql] 기초 문법 정리 (1 / 2)

---

1-0. 사전 테이블, 데이터 세팅

# DB a3 삭제, 생성, 선택
DROP DATABASE IF EXISTS a3;
CREATE DATABASE a3;
USE a3;

# article 테이블 생성
CREATE TABLE article (
    id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    regDate DATETIME NOT NULL,
    `subject` VARCHAR(100) NOT NULL,
    `body` TEXT NOT NULL
);

# article 테이블에 데이터 3개 넣기
INSERT INTO article
SET regDate = NOW(),
`subject` = '제목1',
`body` = '내용1';

INSERT INTO article
SET regDate = NOW(),
`subject` = '제목2',
`body` = '내용2';

INSERT INTO article
SET regDate = NOW(),
`subject` = '제목3',
`body` = '내용3';

# comment 테이블 생성
CREATE TABLE `comment` (
    id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    regDate DATETIME NOT NULL,
    articleId INT UNSIGNED NOT NULL,
    `body` TEXT NOT NULL
);

# comment 테이블에 데이터 3개 넣기
## 1번글에 댓글 2개
## 2번글에 댓글 1개
## 3번글에 댓글 0개
INSERT INTO `comment`
SET regDate = NOW(),
articleId = 1,
`body` = '댓글 내용 1';

INSERT INTO `comment`
SET regDate = NOW(),
articleId = 1,
`body` = '댓글 내용 2';

INSERT INTO `comment`
SET regDate = NOW(),
articleId = 2,
`body` = '댓글 내용 3';

comment

article

 

1. Join : 테이블 결합 관련

`INNER JOIN` : 두 테이블 간의 합집합을 출력합니다. (교집합의 중복은 삭제)
`OUTER JOIN ` : from `Table A` (left, right) Outer Join `Table B` : 방향에 따라 교집합 + (A-B / B-A)를 출력합니다.
      ex ) `A left outer Join B` : A와 B의 교집합과 A-B 집합 데이터를 포함
      ex ) `A right outer Join B` : A와 B의 교집합과 B-A 집합 데이터를 포함

 

INNER JOIN

SELECT 
	c.articleId AS '게시물 번호',
	c.id AS '댓글 번호',
	c.body AS '댓글 내용' 
FROM `article` AS a
INNER JOIN `comment` AS c
ON a.id = c.articleId;

 

 

LEFT OUTER JOIN (comment와 연결되어 있지 않은 article 제목 3에 내용 3 row가 포함된다)

SELECT *
FROM `article` AS a
LEFT OUTER JOIN `comment` AS c
ON a.id = c.articleId;

 

RIGHT OUTER JOIN(comment의 row는 모두 연결되어 있으므로 세 쌍이 출력된다)

SELECT *
FROM `article` AS a
RIGHT OUTER JOIN `comment` AS c
ON a.id = c.articleId;

 

---

 

2-0. 사전 테이블, 데이터 세팅

CREATE TABLE board (
    id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    regDate DATETIME NOT NULL,
    `name` CHAR(100) NOT NULL UNIQUE
);

INSERT INTO board
SET regDate = NOW(),
`name` = '공지';

INSERT INTO board
SET regDate = NOW(),
`name` = '자유';

 

2-1. 서브쿼리

서브쿼리를 통해 select 의 결과도 테이블로서 활용이 가능합니다.

SELECT *
FROM (
    SELECT 1 AS id, NOW() AS regDate, '공지' AS `name` 		# 해당 쿼리를 테이블로 사용
UNION								# 해당 쿼리를 테이블로 사용
    SELECT 2 AS id, NOW() AS regDate, '자유' AS `name`    	# 해당 쿼리를 테이블로 사용 
) AS board;

SELECT *
FROM board; # 위의 쿼리와 결과는 동일

 

---

3-0. 사전 테이블, 데이터 세팅

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

table dept

# 부서(홍보, 기획)
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 (regDate, `name`)
VALUES (NOW(), '홍보');

INSERT INTO dept (regDate, `name`)
VALUES (NOW(), '기획');

SELECT * FROM dept;

 

table emp

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 (regDate, `name`, deptId, salary)
VALUES (NOW(), '홍길동', 1, 5000);
INSERT INTO emp (regDate, `name`, deptId, salary)
VALUES (NOW(), '홍길순', 1, 6000);
INSERT INTO emp (regDate, `name`, deptId, salary)
VALUES (NOW(), '임꺽정', 2, 4000);

SELECT * FROM emp;

 

3-1. SUM, MAX, MIN, AVG, COUNT : 그룹함수, 집계함수

특정 칼럼의 데이터 특성에 대해 집계하여 한 개의 row로 출력합니다.

SELECT COUNT(*) FROM emp; # 사원 수 출력

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

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

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

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

SELECT AVG(salary) FROM emp; # 회사에서 평균 연봉

예시, sum(salary)

 

3-2. GROUP BY : 그룹핑

특정 칼럼의 데이터 특성에 대해 집계한 값에서 그룹핑을 지정한 단위로 나누어 출력합니다.

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

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

예시 : sum(salary), deptId

 

3-3. HAVING : 기 연산된 2차 테이블 대상 연산

HAVING은 GROUP BY로 집계된 테이블에 한해서 조건을 걸어 출력할 수 있습니다.

SELECT SUM(salary) AS s, deptId
FROM emp
GROUP BY deptid
HAVING s > 5000;

 

 

HAVING이 WHERE 과 다르게 FROM 테이블과 같이 1차 테이블에서 연산이 안 되는 이유?

SQL 구문별 실행 순서 때문입니다.

[SQL 구문별 실행순서]
1. FROM, JOIN
2. ON, WHERE
3. 2차 테이블 완성
4. GROUP BY
5. 그룹함수
6. 3차 테이블 완성(그룹작업이 있을 경우에만)
7. HAVING
8. 4차 테이블 완성(HAVING작업이 있을 경우에만)
9. ORDER BY
10. LIMIT
11. 고객(MySQL 클라이언트)

따라서, `HAVING`은 그룹 함수로 집계한 테이블에 한해서 연산을 실시할 수 있습니다.

 

3-4. IF, CASE : 조건부 select 값 변환

조건을 걸어 select 값을 변환해 반환합니다.

# SELECT IF(조건, 조건이 true, 조건이 false)
SELECT IF(deptId=1, '홍보', '기획') AS '부서명'
FROM emp
GROUP BY deptid;

# 자매품 IFNULL도 있다. IFNULL(NULL값일 때 결과값, 아닐 시 결과값)

# CASE WHEN 조건, 맞을 시 THEN의 결과값 출력, 모두 아닐 시 ELSE의 결과값 출력
SELECT CASE
WHEN deptId = 1
THEN '홍보'
WHEN deptId = 2
THEN '기획'
ELSE '무소속'
FROM emp
GROUP BY deptid;