주의: 잘못된 사용 방식 수정

키워드를 테이블 이름이나 컬럼 이름으로 사용할 경우, `` (백틱)을 사용해야 하며, ' ' (단일 인용부호)는 사용하지 않아야 합니다.

잘못된 예시

-- 1. 키워드로 지정 시 자동 대문자 처리
CREATE TABLE NAME(
   DATA DATETIME,
);

-- 2. 단일 인용부호 사용
CREATE TABLE 'name'(
    'data' DATETIME,
);

올바른 사용 방법

CREATE TABLE `name`(
    `data` DATETIME,
);

1. 테이블 생성

CREATE TABLE emp (
  id INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(32),
  sex CHAR(1),
  birthday DATE,
  entry_date DATETIME,
  job VARCHAR(32),
  salary DOUBLE,
  `resume` TEXT
) CHARSET utf8 COLLATE utf8_bin ENGINE INNODB;

-- 전체 컬럼 조회
SELECT * FROM emp;

-- 데이터 삽입 (모든 컬럼 포함)
INSERT INTO emp VALUES(1,'백골정','여','2001-01-01','2022-02-04 10:32:11','대왕',6000,'당사자를 잡는다');

-- 특정 컬럼만 삽입
INSERT INTO emp(id) VALUES(2);
INSERT INTO emp(id, `name`) VALUES(3,'노마왕');

2. 테이블 구조 변경

-- 컬럼 추가 (resume 이후에 image 컬럼 추가, 비어있지 않음, 기본값 '')
ALTER TABLE emp 
ADD COLUMN image VARCHAR(32) NOT NULL DEFAULT '' AFTER `resume`;

-- 컬럼 타입 수정
ALTER TABLE emp MODIFY COLUMN job VARCHAR(60);

-- 컬럼 삭제
ALTER TABLE emp DROP COLUMN sex;

-- 테이블 이름 변경
RENAME TABLE emp TO employee;

-- 구조 확인
DESC employee;

-- 문자셋 변경
ALTER TABLE employee CHARSET utf8;

-- 컬럼 이름 변경
ALTER TABLE employee CHANGE COLUMN `name` user_name VARCHAR(64);

3. 데이터 조작 (삽입, 수정, 삭제)

SELECT * FROM employee;

-- 모든 컬럼 삽입
INSERT INTO employee VALUES(1,'백골정','여','2001-01-01','2022-02-04 10:32:11','대왕',6000,'당사자를 잡는다');

-- 일부 컬럼 삽입
INSERT INTO employee(id) VALUES(2);
INSERT INTO employee(id, `name`) VALUES(3,'노마왕');

-- 전체 급여 조정 (주의: WHERE 없이 실행 시 모든 레코드 변경됨)
UPDATE employee SET salary = 5000;

-- 특정 이름의 급여 수정
UPDATE employee SET salary = 3000 WHERE `name` = '노마왕';

-- 백골정의 급여 1000원 인상
UPDATE employee SET salary = salary + 1000 WHERE `name` = '백골정';

-- 여러 컬럼 동시에 수정
UPDATE employee SET salary = 3000, job = '화염산 대왕' WHERE `name` = '노마왕';

-- 특정 조건의 레코드 삭제
DELETE FROM employee WHERE id = 2;

-- 테이블 완전 삭제
DROP TABLE employee;

4. 조회 연산

4.1 기본 조회

CREATE TABLE student (
  id INT NOT NULL DEFAULT 1,
  `name` VARCHAR(20) NOT NULL DEFAULT '',
  chinese FLOAT NOT NULL DEFAULT 0.0,
  english FLOAT NOT NULL DEFAULT 0.0,
  math FLOAT NOT NULL DEFAULT 0.0
);

INSERT INTO student VALUES
(1,'한함',89,78,90),
(2,'장비',67,98,56),
(3,'미강',87,78,77),
(4,'관우',88,98,90),
(5,'조운',82,84,67),
(6,'오양봉',55,85,15),
(7,'황융',75,65,30);

-- 모든 정보 조회
SELECT * FROM student;

-- 특정 컬럼 조회
SELECT `name`, english FROM student;

-- 중복 제거 (모든 컬럼이 동일하면 제거)
SELECT DISTINCT * FROM student;
SELECT DISTINCT english FROM student;
SELECT DISTINCT `name`, english FROM student;

-- 총점 계산 및 별칭 사용
SELECT `name`, (chinese + english + math) AS total_score FROM student;
SELECT `name`, (chinese + english + math + 10) AS adjusted_total FROM student;
SELECT `name` AS '이름', (chinese + english + math) AS '총점' FROM student;

-- 조건 필터링
SELECT * FROM student WHERE math > 60 AND id > 3;
SELECT * FROM student WHERE english > chinese;
SELECT * FROM student WHERE (chinese + english + math) > 200 AND math > chinese AND `name` LIKE '한%';
SELECT * FROM student WHERE english BETWEEN 80 AND 90;
SELECT * FROM student WHERE math IN (89, 90, 77);
SELECT * FROM student WHERE `name` LIKE '조%';
SELECT * FROM student WHERE (math - chinese) = 1;

-- 정렬
SELECT math FROM student ORDER BY math ASC;
SELECT `name`, (chinese + english + math) AS '총점' FROM student ORDER BY '총점' DESC;

-- 통계 함수
SELECT COUNT(*) AS '학생 수' FROM student;
SELECT COUNT(*) AS '수학 60점 이상' FROM student WHERE math > 60;
SELECT COUNT(*) AS '총점 250점 이상' FROM student WHERE (chinese + english + math) > 250;

-- COUNT(*) vs COUNT(컬럼)
CREATE TABLE count_test (`name` VARCHAR(20));
INSERT INTO count_test VALUES('jake'),('qimu'),('marry'),(NULL);
SELECT COUNT(*) FROM count_test; -- 4개
SELECT COUNT(`name`) FROM count_test; -- 3개 (NULL 제외)

-- 합계
SELECT SUM(math) FROM student;
SELECT SUM(chinese), SUM(english), SUM(math) FROM student;
SELECT SUM(chinese + english + math) AS '합계' FROM student;
SELECT SUM(chinese) / COUNT(*) AS '중국어 평균' FROM student;

-- 평균
SELECT AVG(math) FROM student;
SELECT AVG(chinese + english + math) FROM student;

-- 최대/최소
SELECT MAX(chinese + english + math) AS '최고점', MIN(chinese + english + math) AS '최저점' FROM student;
SELECT MAX(math) AS '수학 최고점', MIN(math) AS '수학 최저점' FROM student;

4.2 고급 조회

-- 부서 테이블 생성
CREATE TABLE dept (
  deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
  dname VARCHAR(20) NOT NULL DEFAULT '',
  loc VARCHAR(13) NOT NULL DEFAULT ''
);

INSERT INTO dept VALUES
(10,'ACCOUNTING','NEW YORK'),
(20,'RESEARCH','DALLAS'),
(30,'SALES','CHICAGO'),
(40,'OPERATIONS','BOSTON');

-- 직원 테이블 생성
CREATE TABLE emp (
  empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
  ename VARCHAR(20) NOT NULL DEFAULT '',
  job VARCHAR(9) NOT NULL DEFAULT '',
  mgr MEDIUMINT UNSIGNED,
  hiredate DATE NOT NULL,
  sal DECIMAL(7,2) NOT NULL,
  comm DECIMAL(7,2),
  deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0
);

INSERT INTO emp VALUES
(7369,'SMITH','CLERK',7902,'1990-12-17',800.00,NULL,20),
(7499,'ALLEN','SALESMAN',7698,'1991-02-20',1600.00,300.00,30),
(7521,'WARD','SALESMAN',7698,'1991-02-22',1250.00,500.00,30),
(7566,'JONES','MANAGER',7839,'1991-04-02',2975.00,NULL,20),
(7654,'MARTIN','SALESMAN',7698,'1991-09-28',1250.00,1400.00,30),
(7698,'BLAKE','MANAGER',7839,'1991-05-01',2850.00,NULL,30),
(7782,'CLARK','MANAGER',7839,'1991-06-09',2450.00,NULL,10),
(7788,'SCOTT','ANALYST',7566,'1997-04-19',3000.00,NULL,20),
(7839,'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),
(7844,'TURNER','SALESMAN',7698,'1991-09-08',1500.00,NULL,30),
(7900,'JAMES','CLERK',7698,'1991-12-03',950.00,NULL,30),
(7902,'FORD','ANALYST',7566,'1991-12-03',3000.00,NULL,20),
(7934,'MILLER','CLERK',7782,'1992-01-23',1300.00,NULL,10);

-- 급여 등급 테이블
CREATE TABLE salgrade (
  grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
  losal DECIMAL(17,2) NOT NULL,
  hisal DECIMAL(17,2) NOT NULL
);

INSERT INTO salgrade VALUES
(1,700,1200),
(2,1201,1400),
(3,1401,2000),
(4,2001,3000),
(5,3001,9999);

-- 그룹화 및 필터링
SELECT AVG(sal), MAX(sal), deptno FROM emp GROUP BY deptno;
SELECT AVG(sal), MIN(sal), deptno, job FROM emp GROUP BY deptno, job;
SELECT AVG(sal) AS avg_sal, deptno FROM emp GROUP BY deptno HAVING avg_sal < 2000;

-- 응용: 평균 급여가 1000 이상인 부서, 내림차순 정렬, 상위 2개 추출
SELECT AVG(sal) AS avg_sal, deptno 
FROM emp 
GROUP BY deptno 
HAVING avg_sal > 1000 
ORDER BY avg_sal DESC 
LIMIT 0, 2;

4.3 심화 조회

-- 1992년 1월 1일 이후 입사자 조회
SELECT * FROM emp WHERE hiredate > '1992-01-01';

-- 이름이 'S'로 시작하는 직원
SELECT ename, sal FROM emp WHERE ename LIKE 'S%';

-- 세 번째 글자가 'O'인 직원
SELECT ename, sal FROM emp WHERE ename LIKE '__O%';

-- 상사가 없는 직원
SELECT * FROM emp WHERE mgr IS NULL;

-- 급여 오름차순 정렬
SELECT * FROM emp ORDER BY sal;

-- 부서번호 오름차순, 급여 내림차순
SELECT * FROM emp ORDER BY deptno, sal DESC;

-- 페이지 나누기 (3개씩)
SELECT * FROM emp ORDER BY empno LIMIT 0, 3; -- 1페이지
SELECT * FROM emp ORDER BY empno LIMIT 3, 3; -- 2페이지
SELECT * FROM emp ORDER BY empno LIMIT 6, 3; -- 3페이지

-- 그룹 집계
SELECT COUNT(*), AVG(sal), job FROM emp GROUP BY job;
SELECT COUNT(*), COUNT(comm) FROM emp;
SELECT COUNT(*), COUNT(IF(comm IS NULL, 1, NULL)) FROM emp;
SELECT COUNT(DISTINCT mgr) FROM emp;
SELECT MAX(sal) - MIN(sal) FROM emp;

5. 다중 테이블 조인

5.1 카티시안 곱 피하기

-- 카티시안 곱 발생 (피해야 함)
SELECT * FROM emp, dept;

-- 조인 조건 명시
SELECT e.ename, e.sal, d.dname, e.deptno 
FROM emp e, dept d 
WHERE e.deptno = d.deptno;

-- 부서 10번 직원 정보
SELECT e.ename, e.sal, d.dname, e.deptno 
FROM emp e, dept d 
WHERE e.deptno = d.deptno AND e.deptno = 10;

-- 급여 등급 매칭
SELECT e.ename, e.sal, s.grade 
FROM emp e, salgrade s 
WHERE e.sal BETWEEN s.losal AND s.hisal;

-- 부서별 정렬
SELECT e.ename, e.sal, d.dname, e.deptno 
FROM emp e, dept d 
WHERE e.deptno = d.deptno 
ORDER BY e.deptno DESC;

5.2 자기 조인 (Self Join)

-- 상사와 직원 관계 표시
SELECT worker.ename AS '직원', boss.ename AS '상사'
FROM emp worker, emp boss
WHERE worker.mgr = boss.empno;

5.3 서브쿼리

5.3.1 단일 행 서브쿼리

-- SMITH와 같은 부서의 모든 직원
SELECT * FROM emp 
WHERE deptno = (
  SELECT deptno FROM emp WHERE ename = 'SMITH'
);

5.3.2 다중 행 서브쿼리

-- 부서 10번과 동일한 직무를 가진 직원 (10번 제외)
SELECT ename, job, sal, deptno 
FROM emp 
WHERE job IN (
  SELECT DISTINCT job FROM emp WHERE deptno = 10
) AND deptno != 10;

5.3.3 ALL & ANY

-- 부서 30번보다 급여가 높은 직원
SELECT ename, sal, deptno 
FROM emp 
WHERE sal > ALL (
  SELECT sal FROM emp WHERE deptno = 30
);

-- 또는
SELECT ename, sal, deptno 
FROM emp 
WHERE sal > (
  SELECT MAX(sal) FROM emp WHERE deptno = 30
);

-- 부서 30번 중 하나 이상보다 높은 급여
SELECT ename, sal, deptno 
FROM emp 
WHERE sal > ANY (
  SELECT sal FROM emp WHERE deptno = 30
);

-- 또는
SELECT ename, sal, deptno 
FROM emp 
WHERE sal > (
  SELECT MIN(sal) FROM emp WHERE deptno = 30
);

6. 유용한 함수

6.1 문자열 함수

SELECT CHARSET(ename) FROM emp;
SELECT CONCAT(ename, ' 는 ', job, '입니다') FROM emp;
SELECT INSERT('aaaaqimu666', 5, 4, 'hello') FROM DUAL;
SELECT UCASE(ename) FROM emp;
SELECT LCASE(ename) FROM emp;
SELECT LEFT(ename, 2) FROM emp;
SELECT RIGHT(ename, 2) FROM emp;
SELECT LENGTH(ename) FROM emp;
SELECT REPLACE(job, 'MANAGER', '매니저') FROM emp;
SELECT STRCMP('qimu', 'QIMU') FROM DUAL;
SELECT SUBSTRING(ename, 1, 4) FROM emp;
SELECT LTRIM('   공감'), RTRIM('공감   '), TRIM('   공감   ') FROM DUAL;

-- 이름 첫글자 소문자로 변환
SELECT CONCAT(LCASE(LEFT(ename, 1)), SUBSTRING(ename, 2)) AS name FROM emp;

6.2 수치 함수

SELECT ABS(-10) FROM DUAL;
SELECT BIN(10) FROM DUAL;
SELECT CEILING(1.1) FROM DUAL;
SELECT FLOOR(1.1) FROM DUAL;
SELECT CONV(8, 10, 2) FROM DUAL;
SELECT FORMAT(1.345211, 2) FROM DUAL;
SELECT LEAST(11, -1, -9, 8) FROM DUAL;
SELECT MOD(10, 3) FROM DUAL;
SELECT RAND() FROM DUAL;
SELECT RAND(1) FROM DUAL;

6.3 날짜/시간 함수

SELECT CURRENT_DATE(), CURRENT_TIME(), NOW();
SELECT YEAR(NOW()), MONTH(NOW());
SELECT DATEDIFF('2011-11-11', '1990-01-01') FROM DUAL;
SELECT UNIX_TIMESTAMP() FROM DUAL;
SELECT FROM_UNIXTIME(1662645690, '%Y-%m-%d %H:%i:%s') FROM DUAL;

-- 메시지 테이블 예제
CREATE TABLE messages (
  id INT,
  content VARCHAR(30),
  send_time DATETIME
);

INSERT INTO messages VALUES (1, '북경 뉴스', NOW());
INSERT INTO messages VALUES (2, '광저우 뉴스', CURRENT_TIMESTAMP());
INSERT INTO messages VALUES (3, '상하이 뉴스', '2022-09-19');
INSERT INTO messages VALUES (4, '허난 뉴스', NOW());

-- 날짜만 출력
SELECT id, content, DATE(send_time) FROM messages;

-- 최근 10분 이내 발행된 뉴스
SELECT * FROM messages WHERE send_time >= DATE_SUB(NOW(), INTERVAL 10 MINUTE);

6.4 암호화 및 시스템 함수

SELECT USER() FROM DUAL;
SELECT DATABASE();
SELECT MD5('qimu') FROM DUAL;

-- 사용자 테이블 예제
CREATE TABLE users (
  username VARCHAR(20) NOT NULL,
  password CHAR(32) NOT NULL
);

INSERT INTO users VALUES ('큐무', MD5('qimu'));
SELECT * FROM users WHERE username = '큐무' AND password = MD5('qimu');

SELECT PASSWORD('qimu') FROM DUAL;

6.5 제어 흐름 함수

SELECT IF(TRUE, '상하이', '베이징') FROM DUAL;
SELECT IFNULL(NULL, '큐무') FROM DUAL;

SELECT CASE 
  WHEN TRUE THEN 'jake'
  WHEN FALSE THEN 'tom'
  ELSE 'mary'
END;

-- 커미션 null 시 0.0 출력
SELECT ename, IF(comm IS NULL, 0.0, comm) FROM emp;
SELECT ename, IFNULL(comm, 0.0) FROM emp;

-- 직무별 설명
SELECT ename, 
  CASE 
    WHEN job = 'CLERK' THEN '사원'
    WHEN job = 'MANAGER' THEN '관리자'
    WHEN job = 'SALESMAN' THEN '영업사원'
    ELSE job
  END AS job_desc
FROM emp;

7. 서브쿼리로 임시 테이블 사용

CREATE TABLE goods (
  goods_id INT,
  cat_id INT,
  goods_sn VARCHAR(10),
  goods_name VARCHAR(60),
  goods_price DOUBLE,
  goods_model INT,
  goods_weight VARCHAR(20)
);

-- 데이터 삽입 (Java 코드로 처리, 생략)

-- 각 카테고리별 최고가 제품 조회
SELECT g.goods_id, g.cat_id, g.goods_name, g.goods_price
FROM goods g,
  (SELECT cat_id, MAX(goods_price) AS max_price
   FROM goods
   GROUP BY cat_id) temp
WHERE g.cat_id = temp.cat_id
  AND g.goods_price = temp.max_price
ORDER BY temp.cat_id;

-- 전체 데이터 확인
SELECT * FROM goods;

-- 테이블 삭제
DROP TABLE goods;