USE tuning;
-- 급여 → salaries
ALTER TABLE `급여` RENAME TO `salaries`;
ALTER TABLE `salaries`
CHANGE COLUMN `사원번호` `emp_no` INT NOT NULL,
CHANGE COLUMN `연봉` `salary` INT NOT NULL,
CHANGE COLUMN `시작일자` `from_date` DATE NOT NULL,
CHANGE COLUMN `종료일자` `to_date` DATE NOT NULL,
CHANGE COLUMN `사용여부` `use_yn` CHAR(1) DEFAULT '';
-- 부서 → departments
ALTER TABLE `부서` RENAME TO `departments`;
ALTER TABLE `departments`
CHANGE COLUMN `부서번호` `dept_no` CHAR(4) NOT NULL,
CHANGE COLUMN `부서명` `dept_name` VARCHAR(40) NOT NULL,
CHANGE COLUMN `비고` `remark` VARCHAR(40) DEFAULT NULL;
-- 부서관리자 → dept_manager
ALTER TABLE `부서관리자` RENAME TO `dept_manager`;
ALTER TABLE `dept_manager`
CHANGE COLUMN `사원번호` `emp_no` INT NOT NULL,
CHANGE COLUMN `부서번호` `dept_no` CHAR(4) NOT NULL,
CHANGE COLUMN `시작일자` `from_date` DATE NOT NULL,
CHANGE COLUMN `종료일자` `to_date` DATE NOT NULL;
-- 부서사원_매핑 → dept_emp
ALTER TABLE `부서사원_매핑` RENAME TO `dept_emp`;
ALTER TABLE `dept_emp`
CHANGE COLUMN `사원번호` `emp_no` INT NOT NULL,
CHANGE COLUMN `부서번호` `dept_no` CHAR(4) NOT NULL,
CHANGE COLUMN `시작일자` `from_date` DATE NOT NULL,
CHANGE COLUMN `종료일자` `to_date` DATE NOT NULL;
-- 사원 → employees
ALTER TABLE `사원` RENAME TO `employees`;
ALTER TABLE `employees`
CHANGE COLUMN `사원번호` `emp_no` INT NOT NULL,
CHANGE COLUMN `생년월일` `birth_date` DATE NOT NULL,
CHANGE COLUMN `이름` `first_name` VARCHAR(14) NOT NULL,
CHANGE COLUMN `성` `last_name` VARCHAR(16) NOT NULL,
CHANGE COLUMN `성별` `gender` ENUM('M','F') NOT NULL,
CHANGE COLUMN `입사일자` `hire_date` DATE NOT NULL;
-- 사원출입기록 → emp_access_logs (원본에는 없지만 보존 목적)
ALTER TABLE `사원출입기록` RENAME TO `emp_access_logs`;
ALTER TABLE `emp_access_logs`
CHANGE COLUMN `순번` `id` INT NOT NULL AUTO_INCREMENT,
CHANGE COLUMN `사원번호` `emp_no` INT NOT NULL,
CHANGE COLUMN `입출입시간` `access_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
CHANGE COLUMN `입출입구분` `access_type` CHAR(1) NOT NULL,
CHANGE COLUMN `출입문` `door` CHAR(1) DEFAULT NULL,
CHANGE COLUMN `지역` `area` CHAR(1) DEFAULT NULL;
-- 직급 → titles
ALTER TABLE `직급` RENAME TO `titles`;
ALTER TABLE `titles`
CHANGE COLUMN `사원번호` `emp_no` INT NOT NULL,
CHANGE COLUMN `직급명` `title` VARCHAR(50) NOT NULL,
CHANGE COLUMN `시작일자` `from_date` DATE NOT NULL,
CHANGE COLUMN `종료일자` `to_date` DATE DEFAULT NULL;
EXPLAIN SELECT * FROM employees;
-- p.122
EXPLAIN
SELECT * FROM employees
WHERE emp_no BETWEEN 100001 AND 200000;
-- p.123 3.2.2 id
EXPLAIN
SELECT E.emp_no, E.first_name, E.last_name, S.salary
, (SELECT MAX(DE.dept_no) FROM dept_emp DE
WHERE DE.emp_no = E.emp_no)
FROM employees E
INNER JOIN salaries S
ON S.emp_no = E.emp_no
WHERE E.emp_no = 10001;
-- select_type - simple
EXPLAIN
SELECT * FROM employees WHERE emp_no = 100000;
EXPLAIN
SELECT e.emp_no, e.first_name, e.last_name, s.salary
FROM employees e
INNER JOIN ( SELECT emp_no, salary FROM salaries WHERE salary > 80000 ) s
ON e.emp_no = s.emp_no
WHERE e.emp_no BETWEEN 10001 AND 10010;
EXPLAIN
SELECT e.emp_no, e.first_name, e.last_name, s.salary
FROM employees e
INNER JOIN salaries s
ON e.emp_no = s.emp_no
WHERE e.emp_no BETWEEN 10001 AND 10010
AND s.salary > 80000;
EXPLAIN
SELECT e.emp_no, e.first_name, e.last_name
, (SELECT MAX(dept_no) FROM dept_emp d WHERE d.emp_no = e.emp_no) as cnt
FROM employees e
INNER JOIN salaries s
ON e.emp_no = s.emp_no
WHERE e.emp_no = 10001;
EXPLAIN
SELECT emp_no, first_name, last_name
FROM employees s1
WHERE emp_no = 100001
UNION ALL
SELECT emp_no, first_name, last_name
FROM employees s2
WHERE emp_no = 100002;
EXPLAIN
SELECT ( SELECT COUNT(1) FROM dept_emp ) AS cnt
, ( SELECT MAX(salary) FROM salaries ) AS salary;
EXPLAIN
SELECT e.first_name, e.last_name
, ( SELECT COUNT(1)
FROM dept_emp de
INNER JOIN dept_manager dm
ON dm.dept_no = de.dept_no ) AS cnt
FROM employees e
WHERE e.emp_no = 10001;
-- 24 rows
SELECT COUNT(1) FROM dept_manager;
-- 331,603 rows
SELECT COUNT(1) FROM dept_emp;
EXPLAIN
SELECT e.first_name, e.last_name
, ( SELECT COUNT(1)
FROM dept_emp de
INNER JOIN dept_manager dm
ON dm.dept_no = de.dept_no
AND de.emp_no = e.emp_no ) AS cnt
FROM employees e
WHERE e.first_name = 'Matt';
EXPLAIN
SELECT s.emp_no, s.salary
FROM employees e
INNER JOIN (
SELECT emp_no, MAX(salary) AS salary
FROM salaries
WHERE emp_no BETWEEN 10001 AND 20000
GROUP BY emp_no
) s
ON e.emp_no = s.emp_no;
EXPLAIN
SELECT 'M' AS gender2, gender, MAX(hire_date) AS hire_date
FROM employees s1
WHERE gender = 'M'
UNION
SELECT 'F', gender, MAX(hire_date)
FROM employees s2
WHERE gender = 'F';
EXPLAIN
SELECT dm.emp_no, dm.dept_no
, ( SELECT s1.first_name
FROM employees s1
WHERE s1.gender = 'F'
AND s1.emp_no = dm.emp_no
UNION ALL
SELECT s2.first_name
FROM employees s2
WHERE s2.gender = 'M'
AND s2.emp_no = dm.emp_no ) AS manager_name
FROM dept_manager dm;
-- 아래 쿼리문으로 변경
EXPLAIN
SELECT *
FROM employees e1
WHERE exists (
SELECT e2.emp_no FROM employees e2 WHERE e1.emp_no = e2.emp_no and e2.first_name = 'Matt'
UNION
SELECT e3.emp_no FROM employees e3 WHERE e1.emp_no = e3.emp_no and e3.last_name = 'Matt'
);
SELECT @STATUS;
EXPLAIN
SELECT *
FROM employees
WHERE emp_no = ( SELECT @STATUS
FROM dept_emp
WHERE dept_no='d005' );
SELECT RAND() FROM dept_emp WHERE dept_no='d005';
-- NOT-DETERMINISTIC 함수를 사용
EXPLAIN
SELECT *
FROM employees
WHERE emp_no = ( SELECT RAND() FROM dept_emp WHERE dept_no='d005');
EXPLAIN
SELECT *
FROM employees
WHERE emp_no IN ( SELECT emp_no
FROM salaries
WHERE salary BETWEEN 100 AND 1000 );
EXPLAIN
SELECT *
FROM employees
WHERE emp_no = 10001;
EXPLAIN
SELECT *
FROM dept_emp
WHERE dept_no = 'd005'
AND emp_no = 10001;
EXPLAIN
SELECT COUNT(1)
FROM employees e1
WHERE first_name = ( SELECT first_name
FROM employees e2
WHERE emp_no = 100001 );
EXPLAIN
SELECT COUNT(1)
FROM employees e1
WHERE first_name = 'Jasminko';
EXPLAIN
SELECT STRAIGHT_JOIN e.emp_no, t.title
FROM titles t
INNER JOIN employees e
ON e.emp_no = t.emp_no
WHERE e.emp_no BETWEEN 10001 AND 10100;
EXPLAIN
SELECT e.emp_no, t.title
FROM employees e
INNER JOIN titles t
ON e.emp_no = t.emp_no
WHERE e.emp_no BETWEEN 10001 AND 10100;
EXPLAIN
SELECT STRAIGHT_JOIN e.emp_no, t.title
FROM employees e
INNER JOIN titles t
ON e.emp_no = t.emp_no
WHERE e.emp_no BETWEEN 10001 AND 10100;
EXPLAIN
SELECT * FROM titles
WHERE emp_no = 10004
AND title = 'Engineer'
AND from_date = '1986-12-01';
SELECT *
FROM titles
WHERE emp_no = 10001
AND title = 'Senior Engineer';
CREATE INDEX idx_titles_todate
ON titles(to_date);
DROP INDEX idx_titles_todate ON titles;
EXPLAIN
SELECT *
FROM titles
WHERE to_date = '1985-03-01'
OR to_date IS NULL;
-- NULL 허용 컬럼에 유니크 인덱스를 생성
CREATE TABLE unique_null_test (
id INT PRIMARY KEY AUTO_INCREMENT
, nm VARCHAR(10) UNIQUE NULL
);
-- nm 값이 있는 Row Insert
INSERT INTO unique_null_test
SET nm = 'aaa';
-- nm이 NULL인 Row Insert 2번
INSERT INTO unique_null_test
SET nm = NULL;
INSERT INTO unique_null_test
SET nm = NULL;
SELECT * FROM unique_null_test;
EXPLAIN
SELECT id
FROM unique_null_test
WHERE nm IS NULL OR nm = 'aaa';
SELECT id
FROM unique_null_test
WHERE nm = 'aaa';
EXPLAIN
SELECT * FROM employees
WHERE emp_no BETWEEN 10001 AND 100000;
EXPLAIN
SELECT * FROM employees
WHERE hire_date between '1987-10-01' AND '1987-11-11';
EXPLAIN
SELECT * FROM employees
WHERE hire_date < '2000-01-27';
SELECT MAX(hire_date) FROM employees;
EXPLAIN
SELECT * FROM employees
WHERE emp_no > 10002;
SELECT MIN(emp_no) FROM employees;
CREATE TABLE employee_name (
emp_no INT NOT NULL
, first_name VARCHAR(14) NOT NULL
, last_name VARCHAR(16) NOT NULL
, PRIMARY KEY (emp_no)
, FULLTEXT KEY fx_name(first_name, last_name) WITH PARSER ngram
);
INSERT INTO employee_name
(emp_no, first_name, last_name)
SELECT emp_no, first_name, last_name
FROM employees;
-- 63ms, 79ms, 47ms
EXPLAIN
SELECT *
FROM employee_name
WHERE MATCH(first_name, last_name)
AGAINST ('Facello' IN BOOLEAN MODE);
-- 125ms, 125ms, 110ms
EXPLAIN
SELECT *
FROM employee_name
WHERE first_name LIKE '%Facello%'
OR last_name LIKE '%Facello%';
EXPLAIN ANALYZE
SELECT emp_no
FROM titles
WHERE title = 'Manager';
/* 사원번호가 1100으로 시작하면서 사원번호가 5자리인 사원의 정보를 모두 조회 */
EXPLAIN
SELECT *
FROM employees
WHERE SUBSTRING(emp_no, 1, 4) = 1100
AND LENGTH(emp_no) = 5;
SELECT *
FROM employees
WHERE emp_no BETWEEN 11000 AND 11009;
EXPLAIN
SELECT *
FROM employees
WHERE emp_no >= 11000 AND emp_no <= 11009;
/* 성별 기준으로 몇 명의 사원이 있는지 출력하는 쿼리
null인 경우는 NO DATA로 표시 해달라.
*/
EXPLAIN
SELECT IFNULL(gender, 'NO DATA') AS group_gender
, COUNT(1) AS cnt
FROM employees
GROUP BY group_gender;
EXPLAIN
SELECT gender, COUNT(1) AS cnt
FROM employees
GROUP BY gender;
/* 사용여부 use_yn값이 1인 데이터의 row 수 */
-- 0.672ms
EXPLAIN
SELECT COUNT(1)
FROM salaries
WHERE use_yn = 1;
-- 0.000ms
EXPLAIN
SELECT COUNT(1)
FROM salaries
WHERE use_yn = '1';