서브쿼리(subquery)와 조인(join) 마무리
-- query문은 동일한 결과를 다른 방법으로 처리 가능
-- 총무부에 근무하는 직원들이 관리하는 고객 출력
-- subquery
SELECT gogek_no,gogek_name,gogek_tel FROM gogek
WHERE gogek_damsano IN(SELECT jikwon_no FROM jikwon
WHERE buser_num=(SELECT buser_no FROM buser WHERE buser_name='총무부'));
-- join
SELECT gogek_no,gogek_name,gogek_tel FROM gogek
INNER JOIN jikwon ON jikwon.jikwon_no = gogek.gogek_damsano
INNER JOIN buser ON jikwon.buser_num = buser.buser_no
WHERE buser_name='총무부';
-- 직급 대리의 최대값보다 작은 연봉을 받는 직원 출력
SELECT jikwon_no, jikwon_name, jikwon_pay FROM jikwon
WHERE jikwon_pay <ANY (SELECT jikwon_pay FROM jikwon WHERE jikwon_jik='대리');
-- 30번 부서의 최대 연봉자보다 연봉을 많이 받는 직원은?
SELECT jikwon_no, jikwon_name, jikwon_pay FROM jikwon
WHERE jikwon_pay >ALL (SELECT jikwon_pay FROM jikwon WHERE buser_num='30');
-- 30번 부서의 최소 연봉자보다 연봉을 많이 받는 직원은?
SELECT jikwon_no, jikwon_name, jikwon_pay FROM jikwon
WHERE jikwon_pay >ANY (SELECT jikwon_pay FROM jikwon WHERE buser_num='30');
-- from 절에 subquery 사용
-- 전체 평균 연봉과 최대 연봉 사이의 연봉을 받는 직원 출력
SELECT jiktab.jikwon_no,jiktab.jikwon_name,jiktab.jikwon_pay
FROM jikwon jiktab,(SELECT AVG(jikwon_pay) avgs,MAX(jikwon_pay) maxs FROM jikwon) amtab
WHERE jiktab.jikwon_pay BETWEEN amtab.avgs AND amtab.maxs;
-- 각 부서별 최대 연봉자 출력
SELECT jiktab.jikwon_no,jiktab.jikwon_name,jiktab.jikwon_pay,jiktab.buser_num
FROM jikwon jiktab, (SELECT buser_num, MAX(jikwon_pay) maxpay FROM jikwon
GROUP BY buser_num) maxtab
WHERE jiktab.buser_num=maxtab.buser_num AND jiktab.jikwon_pay=maxtab.maxpay;
-- group by 내 having 절에 subquery 사용
-- 부서별 평균 연봉 중에서 30번 부서의 평균 연봉보다 큰 부서 출력
SELECT buser_num, AVG(jikwon_pay) FROM jikwon
GROUP BY buser_num HAVING AVG(jikwon_pay) > (SELECT AVG(jikwon_pay)
FROM jikwon WHERE buser_num=30);
-- exists : 반환 값이 true, false
-- 직원이 있는 부서 출력
SELECT buser_name, buser_tel FROM buser bu
WHERE EXISTS (SELECT 'imsi' FROM jikwon WHERE buser_num=bu.buser_no);
-- 직원이 없는 부서 출력
SELECT buser_name, buser_tel FROM buser bu
WHERE not EXISTS (SELECT 'imsi' FROM jikwon WHERE buser_num=bu.buser_no);
-- 상관 서브 쿼리
-- outer(main) query의 각 행을 inner query에서 참조하여 수행하는 subquery
-- 안쪽 질의에서 바깥쪽 질의를 참조하고, 다시 안쪽의 결과를 바깥쪽 질의에서 참조
-- 각 부서별 최대 연봉자 출력
SELECT jikwon_no,jikwon_name,jikwon_pay,buser_num FROM jikwon a
WHERE a.jikwon_pay=(SELECT MAX(b.jikwon_pay) FROM jikwon b
WHERE a.buser_num=b.buser_num);
-- 연봉 순위 3위 이내의 직원 자료 출력(내림차순)
SELECT a.jikwon_name,a.jikwon_pay FROM jikwon a
WHERE 3 > (SELECT COUNT(*) FROM jikwon b
WHERE b.jikwon_pay > a.jikwon_pay) AND jikwon_pay IS NOT null
ORDER BY jikwon_pay DESC;
-- create table 에서 subquery 사용
CREATE TABLE jik1 AS SELECT * FROM jikwon;
DESC jik1;
DESC jikwon;
SELECT * FROM jik1;
CREATE TABLE jik2 AS SELECT * FROM jikwon WHERE 1=0;
DESC jik2;
SELECT * FROM jik2;
-- insert에서 subquery 사용
-- insert into jik2 values --
INSERT INTO jik2 SELECT * FROM jikwon WHERE jikwon_jik='과장';
INSERT INTO jik2(jikwon_no,jikwon_name,buser_num) SELECT
jikwon_no, jikwon_name, buser_num FROM jikwon WHERE jikwon_jik='대리';
INSERT INTO jik2(jikwon_no,jikwon_name,buser_num) SELECT
jikwon_no FROM jikwon WHERE jikwon_jik='이사'; -- not null인 칼럼은 반드시 포함
SELECT * FROM jik2;
CREATE TABLE jik3 AS SELECT jikwon_no bunho,jikwon_name irum,jikwon_pay pay
FROM jikwon WHERE 1=0;
INSERT INTO jik3(bunho,irum,pay) SELECT
jikwon_no, jikwon_name, buser_num FROM jikwon WHERE jikwon_jik='대리';
SELECT * FROM jik3;
INSERT INTO jik3 VALUES(31,'신기해',8888);
SELECT * FROM jik3;
-- update에 subquery
SELECT * FROM jik1;
UPDATE jik1 SET jikwon_jik=(SELECT jikwon_jik FROM jikwon
WHERE jikwon_name='이순신') WHERE jikwon_no=1; -- 이순신의 직급은 과장
-- delete에 subquery
DELETE FROM jik1 WHERE jikwon_no IN(SELECT
DISTINCT gogek_damsano FROM gogek);
all, any에 대하여
-- all, any : null인 자료는 제외하고 작업함
-- <ANY : subquery의 반환값 중 최대값보다 작은...
-- >ANY : subquery의 반환값 중 최소값보다 큰...
-- <ALL : subquery의 반환값 중 최소값보다 작은...
-- >ALL : subquery의 반환값 중 최대값보다 큰...
Database Transactions
데이터베이스의 상태를 변화시키기 위해 수행하는 작업 단위
-- 단위(논리적인)별 데이터 처리
-- 클라이언트가 insert, update, delete 작업을 하면 작업단위가 시작된다.
-- 작업단위의 종료는 commit(DB 서버자료 원본 갱신) 또는 rollback(클라이언트의 insert, update, delete 작업을 취소한다)를 사용한다.
-- ACID : 데이터베이스 내에서 일어나는 하나의 트랜잭션(transaction)의 안전성을 보장하기 위해 필요한 성질이다.
-- Atomicity (원자성), Consistency (일관성), Isolation (격리성, 고립성), Durability (지속성)의 앞글자를 딴 규칙
-- 원자성(Atomicity) : 트랜젝션은 분해가 불가능한 최소의 단위인 하나의 원자처럼 동작한다는 의미
-- 일관성(Consistency) : 트랜잭션 작업이 시작되지 전에 데이터베이스 상태가 일관된 상태였다면 트랜잭션 작업이 종료된 후에도 일관성 있는 데이터 베이스 상태를 유지해아한다
-- 고립성(Isolation) : 트랜잭션 작업 수행 중에는 다른 트랜잭션에 영향을 주어서도 안되고, 다른 트랜잭션들에 의해 간섭을 받아서도 안 된다는 것을 의미
-- 지속성(Durablility) : 일련의 데이터 조작(트렌젝션 조작)을 완료 하고 완료 통지를 사용자가 받는 시점에서 그 조작이 영구적이 되어 그 결과를 잃지 않는 것을 나타낸다
-- 참고 블로그 출처 :https://hanamon.kr/%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4-%ED%8A%B8%EB%9E%9C%EC%9E%AD%EC%85%98%EC%9D%98-acid-%EC%84%B1%EC%A7%88/
-- 참고 블로그 출처 : https://covenant.tistory.com/85
-- MariaDB 제공 SQL 연습용 툴은 autocommit이 true임
SELECT * FROM jik1;
INSERT INTO jik1(jikwon_no,jikwon_name,buser_num) VALUES(31,'tom',10);
SET autocommit=FALSE; -- transaction을 수동으로 바꿈
INSERT INTO jik1(jikwon_no,jikwon_name,buser_num) VALUES(32,'james',20); -- transaction 시작
INSERT INTO jik1(jikwon_no,jikwon_name,buser_num) VALUES(33,'oscar',20);
SELECT * FROM jik1; -- local에서만 insert 되고있음
-- ROLLBACK; -- transaction 끝
COMMIT;
SELECT * FROM jik1;
SET autocommit=TRUE; -- transaction 자동
-- save point(저장 지점) 지정하기
SHOW VARIABLES LIKE 'autocommit%'; -- autocommit 상태확인
SET autocommit=FALSE;
SELECT * FROM jik1 WHERE jikwon_no=8;
UPDATE jik1 SET jikwon_pay=7777 WHERE jikwon_no=8;
SELECT * FROM jik1;
SAVEPOINT aa;
UPDATE jik1 SET jikwon_pay=6666 WHERE jikwon_no=10;
SELECT * FROM jik1;
ROLLBACK TO SAVEPOINT aa;
UPDATE jik1 SET jikwon_pay=5555 WHERE jikwon_no=7;
COMMIT;
SELECT * FROM jik1;
set autocommit=TRUE;
SHOW VARIABLES LIKE 'autocommit%';
-- deadlock : 교착상태 또는 데드락
-- 두 개 이상의 작업이 서로 상대방의 작업이 끝나기만을 기다리고
-- 있기 때문에 결과적으로 아무것도 완료되지 못하는 상태이다.
SET autocommit=FALSE;
SELECT * FROM jik1;
UPDATE jik1 SET jikwon_name='김밥' WHERE jikwon_no=7;
DELETE FROM jik1 WHERE jikwon_no IN(14,15,16);
SELECT * FROM jik1;
COMMIT; -- 작업단위 종료 : 주의:transaction 처리는 빠른 시간내에 마무리 필요
SET autocommit=TRUE;
view 파일
-- 데이터베이스에 존재하는 일종의 가상 테이블 뷰를 이용하여 실제 테이블처럼 행과 열을 가지고 있지만 실제로 데이터를 저장하고 있지는 않는다.
-- 여러 테이블을 번거롭게 들러서 확인을 해야 할 때 혹은 SELECT 문을 겹겹이 사용해서 어려운 쿼리문으로 조회를 수행해야 할 때 이러한 번거로움을 줄여주는 것
-- 어려운 쿼리문으로 조회를 수행해야 할 때 번거로움을 줄여줌
-- 장점
-- 특정 사용자에게 테이블 전체가 아닌 필요한 필드만 보여줄 수 있음(보안 강화)
-- 복잡한 쿼리를 단순화해서 사용할 수 있음
-- 위와 같이 사용한 쿼리를 재사용할 수 있음(독립성 확보)
-- 단점
-- 삽입, 삭제, 갱신 작업에 많은 제한 사항을 가짐
-- 뷰는 자신만의 인덱스를 가질 수 없음
-- 형식
-- CREATE (or replace) VIEW [view_name] AS SELECT 문;
-- DROP VIEW view_name;
CREATE OR REPLACE VIEW v_a AS
SELECT jikwon_no,jikwon_name,jikwon_pay FROM jikwon
WHERE jikwon_ibsail < '2010-12-31';
SHOW FULL TABLES IN mydb WHERE table_type LIKE 'view';
DESC v_a;
SELECT * FROM v_a;
SELECT jikwon_no, SUM(jikwon_pay) AS paysum FROM v_a;
SELECT COUNT(*) FROM v_a;
CREATE VIEW v_b AS SELECT * FROM jikwon
WHERE jikwon_name LIKE '김%' OR jikwon_name LIKE '박%';
SELECT * FROM v_b;
ALTER TABLE jikwon RENAME sbs; -- 테이블 이름 변경
SELECT * FROM v_a; -- 원본(물리적)테이블이 있어야함
SELECT * FROM v_b;
ALTER TABLE sbs RENAME jikwon;
SELECT * FROM v_a; -- 가능
CREATE VIEW v_c AS SELECT * FROM jikwon ORDER BY jikwon_pay DESC;
SELECT * FROM v_c;
CREATE OR REPLACE VIEW v_d AS SELECT jikwon_no 번호,jikwon_name 이름,jikwon_pay * 10000
AS 연봉 FROM jikwon ORDER BY jikwon_pay desc;
SELECT * FROM v_d;
SELECT jikwon_no, jikwon_name FROM v_d; -- X
SELECT 번호, 이름 FROM v_d; -- O
CREATE VIEW v_e AS SELECT * FROM v_d WHERE 연봉 >= 78000000;
SELECT * FROM v_e;
DESC v_e;
UPDATE v_e SET 이름='공기밥' WHERE 이름='홍길동';
SELECT * FROM v_e;
SELECT * FROM v_d;
SELECT * FROM v_c;
SELECT * FROM jikwon; -- 원본이 바뀜
UPDATE v_e SET 연봉=0 WHERE 번호=1; -- error 연봉 : 물리적인 칼럼 아님
DELETE FROM v_e WHERE 번호=18;
SELECT * FROM v_e;
SELECT * FROM jikwon; -- 원본 데이터 지워짐
DELETE FROM v_e WHERE 연봉=80000000;
SELECT * FROM v_e;
DESC jikwon;
CREATE VIEW v_f AS SELECT jikwon_no,buser_num,jikwon_jik FROM jikwon WHERE jikwon_jik='사원';
SELECT * FROM v_f;
INSERT INTO v_f VALUES(35,40,'대리'); -- error : jikwon_name은 not null
CREATE VIEW v_g AS SELECT jikwon_no,jikwon_name,jikwon_jik FROM jikwon WHERE jikwon_jik='사원';
SELECT * FROM v_g;
INSERT INTO v_g VALUES(35,40,'사원');
INSERT INTO v_g VALUES(36,'신사동','대리');
SELECT * FROM v_g;
SELECT * FROM jikwon;
CREATE VIEW v_h AS SELECT jikwon_jik, SUM(jikwon_pay) AS payhap FROM jikwon GROUP BY jikwon_jik;
SELECT * FROM v_h;
-- insert -- x
-- UPDATE v_h SET jikwon_jik='차장' WHERE jikwon_jik='과장'; -- x 연산에 의해 만들어져서(group by) 불가
-- delete -- x
CREATE OR REPLACE VIEW v_join AS SELECT jikwon_no, jikwon_name,buser_name,jikwon_jik
FROM jikwon INNER JOIN buser ON buser_num=buser_no WHERE buser_num IN(10,30);
SELECT * FROM v_join;
뷰(View)를 활용한 문제와 풀이
-- 문1) 사번 이름 부서 직급 근무년수 고객확보
-- 1 홍길동 영업부 사원 6 O or X
-- 조건 : 직급이 없으면 임시직, 전산부 자료는 제외. 위의 결과를 위한 뷰파일 v_exam1을 작성
CREATE OR REPLACE VIEW v_exam1 AS SELECT jikwon_no 사번, jikwon_name 이름, buser_name 부서,
case when jikwon_jik IS NULL then '임시직' ELSE jikwon_jik END AS 직급,
TIMESTAMPDIFF(YEAR ,jikwon_ibsail,NOW()) 근무년수,
case when gogek_damsano=jikwon_no then 'O' ELSE 'X' END AS 고객확보 FROM jikwon
INNER JOIN buser ON jikwon.buser_num=buser.buser_no
LEFT OUTER JOIN gogek ON jikwon.jikwon_no=gogek.gogek_damsano WHERE NOT buser_name='전산부';
SELECT DISTINCT * FROM v_exam1 ORDER BY 부서;
-- 문2) 부서명 인원수
-- 영업부 7
-- 조건 : 직원수가 가장 많은 부서 출력. 위의 결과를 위한 뷰파일 v_exam2을 작성
CREATE OR REPLACE VIEW v_exam2 AS SELECT buser_name 부서명, COUNT(*) 인원수
FROM buser INNER JOIN jikwon ON buser.buser_no=jikwon.buser_num
GROUP BY buser_name HAVING COUNT(*)=(SELECT COUNT(*) FROM jikwon
GROUP BY buser_num ORDER BY COUNT(*) DESC LIMIT 1);
SELECT * FROM v_exam2;
-- 문3) 가장 많은 직원이 입사한 요일에 입사한 직원 출력
-- 직원명 요일 부서명 부서전화
-- 한국인 수요일 전산부 222-2222
-- 위의 결과를 위한 뷰파일 v_exam3을 작성
CREATE OR REPLACE VIEW v_exam3 AS SELECT jikwon_name 직원명,
DATE_FORMAT(jikwon_ibsail,'%W') 요일, buser_name 부서, buser_tel 부서전화
FROM jikwon LEFT OUTER JOIN buser ON jikwon.buser_num = buser.buser_no
WHERE DATE_FORMAT(jikwon_ibsail,'%W')=(SELECT DATE_FORMAT(jikwon_ibsail,'%W')
FROM jikwon GROUP BY DATE_FORMAT(jikwon_ibsail,'%W') HAVING COUNT(*)=(SELECT COUNT(*)
FROM jikwon GROUP BY DATE_FORMAT(jikwon_ibsail,'%W') ORDER BY COUNT(*) DESC LIMIT 1));
SELECT * FROM v_exam3;