복잡한 쿼리, 트리거, view, 스키마 변경

NULL의 의미

 1. 알수 없는 값

 2. 보류되거나 이용할 수 없는 값

 3. 적용할 수 없는 attribute

 

각 NULL은 각자 다르게 생각해야한다. 그리고 sql에서는 NULL값이 들어 있는 지 상태를 체크해서 query문을 실행할 수 있다. 

IS or IS NOT NULL

 

중첩 질의

중첩 질의는 query안에 쿼리를 넣어서 쿼리문을 실행하는 방법이다. 아래와 같이 WHERE 문에 서브 쿼리문을 적어서 중첩질의를 할 수 있다.

SELECT name, price, FROM cars
WHERE brand_id = (SELECT brand_id 
FROM car Brand
WHERE brandName = 'Kia');

 

비교 연산 IN

WHERE 뒤에 작성한 IN에 있는 데이터가 해당 테이블에 포함되어 있는 값만 출력한다. 물론 IN에도 중첩 질의가 가능하다.

WHERE table IN ('data1', 'data2', 'data3);

SELECT DISTINCT Essn 
FROM WORKS_ON
WHERE (Pno, Hours) IN (SELECT Pno, Hours
FROM WORKS_ON
WHERE Essn='123456789');

 

ANY 

any는 다수의 값중 하나의 값이라도 참이면 true이다. 다른 비교문과의 차이점이라면 비교연산을 사용할 수 있다. 

SELECT * FROM emp WHERE sal = ANY(950, 3000, 1250);

 

ALL

ALL은 전체 값을 모두 비교해 모두 만족하면 참이다.  

SELECT * FROM market WHERE price > ALL(950, 1000, 1200);

아래의 명령어의 뜻은  price > 950 AND price > 1000 AND price > 1200 와 같다.

 

 

*위의 명령어는 모두 중첩으로 사용할 수 있습니다.*

 

 

EXISTS

EXISTS(서브 쿼리) 서브 쿼리의 결과가 한건이라도 존재하면 TRUE 없으면 FALSE이다.

SELECT a.empno
  FROM emp a
 WHERE a.job = 'MANAGER'
   AND EXISTS (SELECT 1
                 FROM dept_history aa
                WHERE aa.empno = a.empno);

비스무리하게 NOT EXISTS도 사용할 수 있다. 

 

NOT EXISTS의 경우에는 존재하지 않을 때 TRUE가 된다. 

 

UNIQUE

유니크는 제약조건의 하나로 해당 칼럼에 동일한 값이 들어가지 않도록 하는 제약조건입니다. 

 CREATE TABLE exam (
       id int UNIQUE NOT NULL,
       name varchar(6) NOT NULL,
       grade varchar(6) NOT NULL
   );

 

EXCEPT

가볍게 생각하면 차집합이다. EMPLOYEE 테이블에만 있거나 내용이 다른 데이터를 출력한다.  

SELECT name
FROM EMPLOYEE
EXCEPT SELECT * FROM TableB

 

Join

두개 이상의 테이블이나 데이터베이스를 연결해서 데이터를 검색하는 방법입니다. 여러 개의 테이블을 연결해 하나의 테이블을 사용하는 것처럼 사용할 수 있다. 

 

Natural Join(EQUI Join)

두 테이블에 동일한 이름을 가지는 칼럼의 데이터를 모두 반환한다. 

SELECT name, age, sex
FROM student1 NATURAL JOIN student2

 

INNER JOIN

INNER JOIN은 쉽게 말해서 교집합 데이터를 반환한다. 교집합은 ON의 동일한 데이터다. 

 

SELECT e.no, e.name
FROM dept d INNER JOIN emp e
ON d.deptno=e.deptno;

 

OUTER JOIN

조건에 맞지 않는 데이터도 반환한다. 즉 교집합 밖의 데이터도 가져올 수 있다.

 

LEFT OUTER JOIN

LEFT OUTER JOIN은 교집합의 값과 왼쪽 테이블의 값을 가져온다.

SELECT e.no, d.name
FROM dept d LEFT OUTER JOIN emp e
ON d.deptno=e.deptno;

 

RIGHT OUTER JOIN

RIGHT OUTER JOIN은 교집합과 오른쪽의 데이터가 반환한다. 

SELECT e.no, d.name
FROM dept d RIGHT OUTER JOIN emp e
ON d.deptno=e.deptno;

 

FULL OUTER JOIN

Aggregate Function

집계함수인데 아래와 같은 것들이 있다. 딱보면 딱 그거다. 딱히 설명할게 없는 것 같다. 사용법만 알아보자!

COUNT, SUM, MAX, MIN, AVG

SELECT SUM(Salary), MAX(Salary), MIN(Salary), AVG(Salary)
FROM EMPLOYEE;

AS로 별명을 붙이고 싶습니다. 
SELECT SUM(Salary) AS Total_Sal, MAX(Salary) AS Highest_Sal,
MIN(Salary) AS Lowest_Sal, AVG(Salary) AS Average_Sal
FROM EMPLOYEE;

 

Grouping  - GROUP BY

특성 속성을 기준으로 그룹화한다. 반드시 Aggregation 함수와 함께 사용되어야한다. 

SELECT Dno, COUNT(*), AVG(Salart)
FROM EMPLOYEE
GROUP BY Dno;

 

 

Having

그룹을 선택할 수 있는 조건이다. 반드시 Aggregation 함수가 쓰이는 WHERE 절과 비슷하다. 

SELECT Pnumber, Pname, COUNT(*)
FROM PROJECT, WORKS_ON
WHERE Pnumber = Pno
GROUP BY Pnumber, Pname
HAVING COUNT(*) > 2;

 

WITH 

이전에 SubQuery문을 사용할 때 상당했을 때 편리하긴 했지만 너무 복잡해 보이는 단점이 있었다. 쿼리문 또한 더 복잡해지면 당연히 그렇게 보일 것이다. 그래서 WITH절은 변수에 SubQuery를 등록하는 것 처럼 사용해서 편리하게 재사용할 수 있다. 아래의 예제를 보자!

WITH [ 별명1 ] [ (컬럼명1 [,컬럼명2]) ] AS (
    SUB QUERY
)

칼럼명은 생략할 수 있다. 

WITH BIGDEPTS AS (
	SELECT Dno 
    FROM EMPLOYEE
    GROUP BY Dno
    HAVING COUNT (*) > 5)
    
 SELECT Salary
 FROM BIGDEPTS;

 

Case

조건에 따라 값을 정해주는 명령어이다. 

UPDATE EMPLOYEE
SET Salary = 
CASE WHEN Dno = 5 THEN Salary + 2000
	 WHEN Dno = 4 THEN Salary + 1500
     WHEN Dno = 1 THEN Salary + 3000
     

Dno가 5일 때 1일 때 4일 때 마다 다르게 월급을 올려준다. 

 

 

Creation Trigger

특정 이벤트가 발생할 때 자동으로 사전에 정의한 Event를 실행한다. 해당 EVENT이 일어나기전 사전에 정의한 조건을 확인하는데 해당 조건을 만족하지 못하면 실행하지 않는다. 아래 예제에서는 INSERT나 UPDATE가 발생하기 전에 이벤트가 발생하게 만들었다. 

CREATE TRIGGER SALARY_VIOLATION
BEFORE INSERT OR UPDATE OF Salary, Supervisor_ssn ON EMPLOYEE

 

Creation Assertions

추가적이거나 직접적인 제약을 만들어서 사용한다. 

CREATION ASSERTION SALARY_CONSTARINT 
CHECK (NOT EXISTS (SELECT *
				   FROM EMPLOYEE E, EMPLOYEE M,
                   DEPARTMENT D
                   WHERE E.Salary > M.Salary
                   AND E.Dno = D.Dnumber
                   AND D.Mgr_ssn = M.ssn));

 

View

View는 하나의 테이블에서 원하는 칼럼만 보여주거나 특정 조건에 맞는 레코드들만 보여준다. 또는 두개 이상의 테이블을 조인해 하나의 VIEW로 사용작에게 보여주는데 이용할 수 있다. 실제 데이터를 갖고 있지 않고 SELECT문의 정의 만을 가질 수 있다. 

CREATE VIEW WORKS_ON1
AS SELECT Fname, Lname, Pname, Hours
FROM EMPLOYEE, PROJECT, WORKS_ON
WHERE Ssn=Essn AND Pno=Pnumber;

SELECT Fname, Lname, Pname FROM WORKS_ON1;

 

 

View Update

aggregate 함수 없이 정의된 하나의 테이블을 베이스로 한 view를 업데이트할 수 있다. 다수의 View를 업데이트 시킬 수 없다.

아래의 예에는 Total_sal이 view definition안에 있는 값이기 때문에 실행될 수 없다. 

UPDATE DEPT_INFO
SET Total_sal =100000
WHERE Dname='Research';

View에 조인이 포함되어 있을 경우 의도한 업데이트를 진행할 수 없을 수 있습니다. 

 

WITH CHECK OPTION

 

view를 만들 때 해당 제약 조건 안에서만 삽입 삭제 수정이 가능하게 합니다. 즉 아래의 경우에는 Dno가 5일 때만 수정이나 삽입 등이 가능해지는 것이다. 

CREATE VIEW EMP_DNO5
AS SELECT Fname, Lname, Ssn, Dno
FROM EMPLOYEE
WHERE Dno=5
WITH CHECK OPTION;

 

IN-line View

FROM절안에서 SQL query를 정의한다.

SELECT Lname, Fname, Pno
FROM EMPLOYEE AS E
	(SELECT Essn, Pno
    FROM WORK_ON
    WHERE Hours > 30.0) AS W)
WHERE E.Ssn = W.Essn;

 

ALTER

column을 없애거나 바꾸거나 할 수 있다. 또는 테이블의 제약을 추가하거나 없앨 수 있다. 

ALTER TABLE COMPANY.EMPLOYEE ADD 
COLUMN Job VARCHAR(12);

 

제약 추가 및 없앰

 

ALTER TABLE COMPNAY.EMPLOYEE
DROP CONSTRAINT EMPSUPERFK CASCADE;