다양한 query 사용

quary문은 동일 결과를 얻기위해 다양한 방법을 사용할수있다.

<총무부에 근무하는 직원들이 관리하는 고객자료 출력>

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_no = gogek_damsano
INNER JOIN buser ON buser_num = buser_no
WHERE buser_name = '총무부';

from절에 subquery.

테이블처럼사용

<전체 평균 연봉과 최대연봉 사이의 연봉을 받는 직원>
SELECT jikwon_no,jikwon_name,jikwon_pay FROM jikwon a,
(SELECT AVG(jikwon_pay) avgs, MAX(jikwon_pay) maxs FROM jikwon) b
WHERE a.jikwon_pay BETWEEN b.avgs AND b.maxs;

< 각 부서별로 최고 연봉을 받는 직원>
SELECT a.jikwon_no,a.jikwon_name, a.jikwon_pay FROM jikwon a,
(SELECT buser_num, MAX(jikwon_pay) maxpay FROM jikwon GROUP BY buser_num) b
WHERE a.buser_num = b.buser_num AND a.jikwon_pay = b.maxpay;

group by 의 having 절에 subquery

<부서별 평균 연봉 중 10번 부서의 평균 연봉보다 더 큰 자료 출력>
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 = 20);

exists 연산자 사용.

조건에 맞는 데이터가 하나라도 있는지 true여부

<직원이 있는 부서>
SELECT buser_name , buser_loc FROM buser bu WHERE 
EXISTS (SELECT 'imsi' FROM jikwon WHERE buser_num = bu.buser_no);

<직원이 없는 부서>
SELECT buser_name , buser_loc FROM buser bu WHERE 
NOT EXISTS (SELECT 'imsi' FROM jikwon WHERE buser_num = bu.buser_no);

상관 서브 쿼리

안쪽 query에서 바깥쪽 query를 참조하고,
다시 안쪽의 결과를 바깥쪽 query에서 참조하는 형태. 별명을 사용해야함

<각 부서의 최대 연봉자는?>
SELECT * 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 a.jikwon_pay IS NOT NULL
ORDER BY jikwon_pay DESC;

subquery insert에서 사용

CREATE TABLE mytab AS SELECT * FROM jikwon;
SELECT * FROM mytab;


CREATE TABLE mytab2 AS SELECT * FROM jikwon WHERE 1=0; -- 구조만 만듬
INSERT INTO mytab2 (SELECT * FROM jikwon WHERE buser_num = 10);
DROP TABLE mytab2;

INSERT INTO mytab2(jikwon_no,jikwon_name,buser_num) 
SELECT jikwon_no,jikwon_name,buser_num FROM jikwon WHERE buser_num=20;
SELECT * FROM mytab2;

UPDATE, delete에서 subquery

<update>
SELECT * FROM mytab;
UPDATE mytab SET jikwon_jik=
(SELECT jikwon_jik FROM jikwon WHERE jikwon_name='이순신')
WHERE jikwon_no = 1;

<delete>
DELETE FROM mytab WHERE jikwon_no IN (SELECT DISTINCT gogek_damsano FROM gogek);

트랜젝션

transaction : 단위별 데이터 처리를 말한다. 한 사용자에 의해 한개 이상의
sql문을 포한하는 가장작은 논리적인 작업을 의미한다.
사용자에 의해 insert,update,delete에 따른 변화가 발생하면 commit이나 rollback으로
작업을 마무리 해야 한다. 이로인해 데이터의 일관성을 보장받을 수 있다.

HOW VARIABLES LIKE 'autocommit%';

CREATE TABLE jiktab AS SELECT * FROM jikwon;
SELECT * FROM jiktab;

SET autocommit = FALSE;
SHOW VARIABLES LIKE 'autocommit%';

DELETE FROM jiktab WHERE jikwon_no <= 5;
COMMIT;

autocommit을 끈 상태에서는 작업을 해도 DB상에서는
commit / rollback 하기 전까진 변화가 없는것을 확인 할 수 있다.

연습1

SET autocommit = FALSE;
DELETE FROM jiktab WHERE jikwon_no = 8; -- TRANSACTION 시작 
COMMIT; -- 내컴에 내용을 근거로 원본이 갱신. Transction 종료 
SELECT * FROM jiktab;

DELETE FROM jiktab WHERE jikwon_no = 9; -- TRANSACTION 시작 
ROLLBACK; -- 내 컴에 내용을 취소하고 원래대로 돌아감.  Transction 종료
SELECT * FROM jiktab;
SET autocommit = TRUE;

연습2 savepoint / rollback

SET autocommit = FALSE;
SELECT * FROM jiktab WHERE jikwon_no = 10;
UPDATE jiktab SET jikwon_pay=777 WHERE jikwon_no = 10;  <TRANSACTION 시작>
SELECT * FROM jiktab WHERE jikwon_no = 10;

SAVEPOINT a;

UPDATE jiktab SET jikwon_pay=888 WHERE jikwon_no = 11;
ROLLBACK TO SAVEPOINT a; <세이브까지 돌아감>

SELECT * FROM jiktab WHERE jikwon_no IN(10,11);
ROLLBACK;  <전부돌아옴 TRANSACTION 끝>
SELECT * FROM jiktab WHERE jikwon_no IN(10,11);
UPDATE jiktab SET jikwon_pay = 999 WHERE jikwon_no=12;  <TRANSACTION 시작>
COMMIT;			<TRANSACTION 끝>
SELECT * FROM jiktab;
SET autocommit = TRUE;
SHOW VARIABLES LIKE 'autocommit%';

savepoint 좌표에 저장된곳으로 rollback. 진행상황이 복귀된다

deadlock

특정 트랜젝션이 진행을 막고 충돌하는 현상.
autocommit 이 꺼져있을때 발생하는 상황으로, 주의해야한다.

mariadb에서 데이터를 붙잡고 있어 다른 사람 입장인 콘솔에서 해당 데이터를 접근하려다가 deadlock 에러가 걸려버렸다.

해결책은 충돌한 트랜젝션에 대해 commit이나 rollback을 진행해야한다.

VIEW

일반 사용자 입장에서는 동일하게 사용하는 개체이다.
뷰는 SELECT 문으로 만들어진 테이블이라고 생각하면 되는데
뷰는 기본적으로 읽기전용으로 많이 사용되지만 뷰를 통해 원 테이블의 데이터를 수정할 수도 있다.

장점

• 보안에 도움이 된다는 점이 있다.
  특정사용자에게 어떤 테이블의 모든정보가 아닌 공개가능한 정보만을 뷰로 생성하여 제공 할 수 있기 때문이다.
• 물리적으로 데이터를 포함하지 않으므로 메모리를 절약할 수 있다.
• 복잡하고 긴 SELECT문을 단순화, 보안을 강화, 자료의 독립성 확보.,.
  매번 조인을 해서 검색하던 자료를 뷰로 한번 생성해놓으면, 그후로는 그 뷰를 SELECT로 조회하기만 하면 된다는 것이다.

CREATE OR REPLACE VIEW 뷰파일명

<예시>
CREATE OR REPLACE VIEW V_A AS
SELECT JIKWON_NO, JIKWON_NAME,JIKWON_PAY FROM JIKWON
WHERE JIKWON_IBSAIL < '2012-12-31';

<테이블의 사용과 큰 차이가 없음>
SELECT * FROM v_a;
SELECT v_a.jikwon_no, v_a.JIKWON_NAME FROM v_a
WHERE jikwon_pay >= 8000; 

<이름 변경 가능>
ALTER TABLE jikwon RENAME mbc;

뷰를 이용하여 뷰 파일을 재작성 할수도 있다.

<b와 c를 만듬>
CREATE VIEW v_b AS
SELECT * FROM jikwon ORDER BY jikwon_pay DESC;
SELECT * FROM v_b;

CREATE VIEW v_c AS
SELECT jikwon_name, jikwon_pay * 10000 AS ypay FROM jikwon;
SELECT * FROM v_c;

<뷰로 뷰파일 작성>
CREATE OR REPLACE VIEW v_d AS SELECT * FROM v_c WHERE ypay >= 50000000;

<b는 c, c는 d가 계승되어 만들어졌다. 
원본이 수정되면 비롯되어 만들어진 뷰데이터도 함께 바뀐다 >

UPDATE jikwon SET jikwon_name= '홍두깨' WHERE jikwon_name='홍길동';

SELECT * FROM jikwon;
SELECT * FROM v_b;
SELECT * FROM v_c; <세 뷰 전부 바뀜>
SELECT * FROM v_d;

특성

UPDATE v_d SET ypay = 123 WHERE jikwon_name = '이순신';

위는 view를 update문으로 커밋하려는 코드이다.
뷰는 읽기전용.물리적 테이블이 아닌 연산에 의해 작성된 컬럼이므로 에러가 발생한다.