[Day16] 2022-02-17(목) MariaDB 2 - SQL 기본 - 이진만 강사님

[1] SQL 기본

  1. SELECT문

-- select 구문
/*
SQL 공부
SQL 공부
*/
SELECT * FROM emp;
SELECT empno,empname,salary, salary / 12 AS msalary FROM emp;
DESC emp;
SELECT * FROM emp WHERE empname = '킹';
SELECT * FROM emp WHERE salary > 3000;
SELECT * FROM emp WHERE DATE_FORMAT(hdate, '%Y') > 2000;
SELECT * FROM emp WHERE DATE_FORMAT(hdate, '%m') = 01;
SELECT * FROM emp WHERE DATE_FORMAT(hdate, '%d') = 01;
SELECT empname, DATE_FORMAT(hdate, '%Y년 %m월 %d일') AS hdate FROM emp;
SELECT * FROM emp WHERE DAY(hdate) = 01;
SELECT * FROM emp WHERE MONTH(hdate) = 01;
SELECT * FROM emp WHERE YEAR(hdate) = 2000;

SELECT * FROM emp;
-- 월급이 4000 ~ 5000 인 직원을 조회 하시오(BETWEEN)
SELECT * FROM emp WHERE salary >= 4000 AND salary <= 5000;
SELECT * FROM emp WHERE salary BETWEEN 4000 AND 5000;

-- deptno 가 10, 30, 40 인 직원을 조회하시오(IN, NOT IN)
SELECT * FROM emp WHERE deptno = '10' OR deptno = '30' OR deptno = '40';
SELECT * FROM emp WHERE deptno IN ('10', '30', '40');
SELECT * FROM emp WHERE deptno NOT IN ('20');

-- 이 씨를 조회('%'와 '_' 활용)
SELECT * FROM emp WHERE empname LIKE '이%';
SELECT * FROM emp WHERE empname LIKE '_영자';

-- 서브 쿼리 (쿼리 안에 쿼리 중첩)
-- 이영자가 속한 부서원 중 연봉이 3000 이상인 직원들을 조회 하시오
SELECT deptno FROM emp WHERE empname = '이영자';
SELECT * FROM emp WHERE deptno = '30';

SELECT * FROM emp
WHERE deptno = (SELECT deptno FROM emp WHERE empname = '이영자')
AND salary >= 3000;

-- titleno 가 30 번인 직원들의 deptno와 같은 직원들의 사번과 이름을 출력
SELECT empno, empname FROM emp
WHERE deptno IN (SELECT deptno FROM emp WHERE titleno = '30');

-- salary 오름차순 정렬(ORDER BY 필드명 ASC) -- ASC는 생략 가능
SELECT * FROM emp ORDER BY salary;
-- salary 내림차순 정렬(ORDER BY 필드명 DESC)
SELECT * FROM emp ORDER BY salary DESC;

-- 1차 정렬 : deptno 오름차순, 2차 정렬 : empname 오름차순
SELECT * FROM emp ORDER BY deptno, empname;
SELECT * FROM emp ORDER BY 3, 4;  -- 3번 4번 컬럼 기준 정렬

-- 중복 제거하고 출력(DISTINCT)
SELECT DISTINCT(titleno) FROM emp;

-- 상위 5개만 출력(LIMIT 개수)
SELECT * FROM emp ORDER BY salary DESC LIMIT 5;
-- 0번째부터 3개 출력(LIMIT 오프셋, 개수 or LIMIT 개수 OFFSET 오프셋)
SELECT * FROM emp ORDER BY salary DESC LIMIT 0, 3;
SELECT * FROM emp ORDER BY salary DESC LIMIT 3 OFFSET 0;

-- CASE WHEN~ THEN~ ELSE~ END구문
-- salary가 4000 이상이면 '상' 출력, 4000 미만이면 '하' 출력
-- 컬럼 명은 grade로 출력
-- 단, empname, salary, grade로 출력 하시오
SELECT empname, salary, 
CASE
	WHEN salary >= 4000 THEN '상'
	ELSE '하'
END AS grade
FROM emp;

-- NULL 제외시키기( IS NOT NULL, NOT IN ('NULL'), NOT IN ('') )
SELECT * FROM emp WHERE manager IS NOT NULL;
SELECT * FROM emp WHERE manager NOT IN ('NULL');
SELECT * FROM emp WHERE manager NOT IN ('');

-- NULL 체크해서 다른 것으로 마킹
SELECT empno, IFNULL(manager,'NONE') FROM emp;

  2. workshop

-- <workshop>
-- 1. 직원정보를 출력 한다. 직원의 연봉 정보와 연봉의 세금 정보를 같이 출력 한다.
-- 세금은 10%
SELECT *, salary * 0.1 AS tax FROM emp;

-- 2. 직원정보 중 2001 이전에 입사 하였고 월급이 4000만원 미만인 직원을 조회
SELECT * FROM emp WHERE DATE_FORMAT(hdate, '%Y') < '2001' AND salary < 4000;

-- 3. manager가 있는 직원 중 이름에 '자' 가 들어가 있는 직원정보 조회
SELECT * FROM emp WHERE manager IS NOT NULL AND empname LIKE '%자%';

-- 4. 월급이 2000미만은 '하' 4000미만은 '중' 4000이상은 '고' 를 출력
--단, empname, salary, grade로 컬럼명을 만드시오
SELECT empname, salary,
CASE
	WHEN salary < 2000 THEN '하'
	WHEN salary < 4000 THEN '중'
	ELSE '고'	
END
AS grade
FROM emp;

  3. GROUP BY문

-- GROUP BY
-- 항상 집계 함수와 함께 사용
-- SUM, AVG, MIN, MAX, 

SELECT * FROM emp;

SELECT deptno, AVG(salary) FROM emp
GROUP BY deptno
HAVING AVG(salary) < 4000 AND deptno = 30;

-- (이말숙과 같은 부서)의 연봉의  평균을 구하시오
SELECT deptno, AVG(salary) FROM emp
GROUP BY deptno
HAVING deptno = (SELECT deptno FROM emp WHERE empname = '이말숙');


-- 이말숙과 홍영자가속한 부서의 연봉의 평균이 3500 초과일 경우 출력.
-- 단, 부서명, 연봉 평균을 출력
SELECT deptno, AVG(salary) AS average FROM emp
GROUP BY deptno
HAVING deptno IN (SELECT deptno FROM emp WHERE empname IN ('이말숙', '홍영자'))
AND AVG(salary) > 3500;

-- 직급 별 월급의 평균을 구하시오
SELECT titleno, AVG(salary / 12) AS msalaryavg FROM emp
GROUP BY titleno;

-- COUNT() 집계 함수
SELECT deptno, COUNT(*) AS cnt FROM emp
GROUP BY deptno;

-- 우리 회사 직급의 개수를 구하시오
SELECT COUNT(DISTINCT(titleno)) AS titlecnt FROM emp;

-- 직급별 최소, 최대 연봉
SELECT deptno, MIN(salary) AS minsalary, MAX(salary) AS maxsalary FROM emp
GROUP BY deptno;

-- 이영자가 속한 부서의 연봉 평균 보다 많이 받는 직원을 조회
SELECT * FROM emp
WHERE salary > (
SELECT AVG(salary) FROM emp
WHERE deptno IN (SELECT deptno FROM emp WHERE empname = '이영자')
);

  4. workshop2

-- 5. 부서 별 월급의 평균을 구하시오
-- 단, 평균이 3000 이상인 부서만 출력
SELECT deptno, AVG(salary / 12) AS msalaryavg FROM emp
GROUP BY deptno
HAVING AVG(salary) >= 3000;
​
-- 6. 부서 별 대리와 사원 연봉의 평균을 구하시오
-- 단, 평균이 2500 이상인 부서만 출력
SELECT deptno, AVG(salary) as salaryavg FROM emp
WHERE titleno IN (
SELECT titleno FROM title WHERE titlename IN ('사원', '대리')
)
GROUP BY deptno
HAVING AVG(salary) >= 2500;

-- 7. 2000년 부터 2002년에 입사는 직원들의
-- 월급의 평균을 구하시오
SELECT AVG(salary / 12) AS msalaryavg FROM emp
WHERE YEAR(hdate) BETWEEN 2000 AND 2002;
​
-- 8. 서울에서 근무하는 직원들을 조회 하시오
SELECT * FROM emp
WHERE deptno = (
SELECT deptno FROM dept WHERE deptloc = '서울');
​
-- 9. 이영자가 속한 부서의 직원 월급평균 보다 많이 받는 직원들을  조회 하시오
SELECT * FROM emp
WHERE salary /12 > (
SELECT AVG(salary / 12) FROM emp
WHERE deptno IN (SELECT deptno FROM emp WHERE empname = '이영자')
);

​-- 10. 김강국의 타이틀과 같은 직원들의 연봉보다 많이 받는 직원 들 중 
-- 2000 이전 입사한 직원들을 조회 하시오
SELECT * FROM emp
WHERE salary > (
SELECT AVG(salary) FROM emp WHERE titleno = (SELECT titleno FROM emp WHERE empname = '김강국')
)
AND YEAR(hdate) < 2000;

 

  5. MariaDB Data Format Options

 

  6. AUTO_INCREMENT & INSERT, UPDATE, DELETE

DROP TABLE itemtbl;
CREATE TABLE itemtbl (
	id INT AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(20),
	price INT,
	regdate DATETIME
);
ALTER TABLE itemtbl AUTO_INCREMENT = 1000;


INSERT INTO itemtbl VALUES (id, 'pants1', 10000, NOW());
INSERT INTO itemtbl VALUES (id, 'pants2', 20000, NOW());
INSERT INTO itemtbl VALUES (id, 'pants3', 30000, NOW());
INSERT INTO itemtbl VALUES (id, 'pants4', 30000, NOW());


-- 마지막에 추가한 데이터
SELECT LAST_INSERT_ID();

SELECT id, name, DATE_FORMAT(regdate, '%Y%m%d %H:%i:%s') AS regtime FROM itemtbl;

SELECT * FROM itemtbl;

UPDATE itemtbl SET NAME = 'shirts' WHERE id = 1000;
DELETE FROM itemtbl WHERE id = 1003;