7. SQL 응용

데이터 베이스의 SQL 유형

• 데이터 질의어(DQL) - 데이터베이스에 저장된 데이터를 검색하는데 사용하는 질의어 → SELECT
• 데이터 조작어(DML) - 데이터베이스에 저장된 데이터를 수정, 삭제, 추가하는 명령어 → INSERT, UPDATE, DELETE
• 데이터 정의어(DDL) - 데이터베이스 객체를 생성하고 수정, 삭제하는 명령어 → CREATE, ALTER, DROP, TRUNCATE
• 데이터 제어어(DCL) - 데이터베이스의 규정이나 기법을 정의하고 제어하는 언어 → GRANT, REVOKE, COMMIT, ROLLBACK

INSERT

INSERT INTO 테이블명(컬럼명1, ..., 컬럼명n)
VALUES (값1, ..., 값n)

ex) 
INSERT INTO DEPT(DNO, DEPTNAME, FLOOR)
VALUES(4,'HR', 7);

DELETE

DELETE FROM 테이블명
WHERE 조건;

ex) 부서 테이블에서 부서번호 4번인 부서를 삭제.
DELETE FROM DEPT WHERE DNO=4;

ex) 학생 테이블에서 모든 데이터 삭제.
DELETE FROM 학생;

UPDATE

UPDATE 테미블명
SET 컬럼명=값 또는 식
WHERE 조건

ex) 사원 테이블에서 사원번호 1234번의 부서번호를 3번으로 수정.
UPDATE EMPLOYEE
SET DNO=3
WHERE EMPNO=1234;

CREATE

CREATE TABLE [테이블명]
(속성명 data_type [NOT NULL] , ...,
PRIMARY KEY(기본키 속성명),
UNIQUE(대체키 속성명),
FOREIGN KEY(외래키_속성명, ...),
REFERENCES 참조테이블(기본키_속성명),
CONSTRAINT 제약조건명 CHECK(조건식));

ALTER

// 컬럼 추가
ALTER TABLE 테이블명 ADD 컬럼명 데이터타입;

// 컬럼 데이터 타입 변경
ALTER TABLE 테이블명 MODIFY 컬럼명 데이터타입;

// 컬럼 삭제
ALTER TABLE 테이블명 DROP 컬럼명;

// 테이블명 변경
ALTER TABLE 이전테이블명 RENAME TO 새로운테이블명;

// 유저의 비밀번호 변경
ALTER USER 계정명 IDENTIFIED BY 신규비밀번호;

CREATE OR REPLACE 문

뷰(View)가 정의된 기본 테이블을 재정의 하고자 할 때 뷰를 삭제하지 않고 변경이 가능한 명령어.

• 통상적으로 뷰를 삭제하고 다시 생성하면 되지만 삭제를 하는 순간 권한을 부여받아 참조하고 있던 계정의 권한도 회수되기 때문에 다시 권한을 재부여하는 절차가 필요
• 기본 테이블의 재정의로 인한 관계의 단절을 해소하기 위해 사용

// 뷰(View)
CREATE OR REPLACE VIEW 뷰명

// 트리거(Trigger)
CREATE OR REPLACE TRIGGER 트리거명

// 프로시저(Procedure)
CREATE OR REPLACE PROCEDURE 프로시저명

// 사용자 정의함수(Function)
CREATE OR REPLACE FUNCTION 사용자정의함수명

데이터 제어어(DCL)

• 무결성
  • COMMIT - 수행된 결과를 실제 물리적 디스크로 저장
  • ROLLBACK - 명령 수행 실패를 의미하며 수행된 결과를 원복 시킴
  • SAVEPOINT - 저장점 지정, 지정된 저장점 부터 현재까지 일부만 ROLLBACK 가능
• 데이터 보안
  • GRANT - 데이터베이스 사용자에게 사용 권한 부여

  ex) 유저 user02가 user03에게 학생테이블 SELECT, DELETE 권한을 부여
  GRANT SELECT, DELETE ON 학생 TO user03;
  
  ex) 유저 user03가 다른 사람에게 권한을 줄 수도있음(WITH GRANT OPTION)
  GRANT SELECT, DELETE ON 학생 TO user03 WITH GRANT OPTION;
  

  • REVOKE - 데이터베이스 사용자에게 부여된 사용 권한 취소

  ex) user02 계정의 [학생] 테이블에 대한 SELECT, DELETE, UPDATE 권한을 회수
  REVOKE SELECT, DELETE, UPDATE ON 학생 FROM user02;
  

JOIN

• INNER JOIN
  • 동등조인(EQUI JOIN) - 특정 칼럼을 비교하여 같은 값을 추출
  • 자연조인(NATURAL JOIN) - 두 테이블의 모든 칼럼을 비교하여 같은 컬럼명을 가진 모든 컬럼 값이 같은 경우를 추출
  • 교차조인(CROSS JOIN) - 조인 조건의 없는 모든 데이터의 조합을 추출
• OUTER JOIN
  • 왼쪽 외부 조인(LEFT OUTER JOIN) - 왼쪽 테이블의 모든 데이터와 오른쪽 테이블의 동일 데이터 추출
  • 오른쪽 외부 조인(RIGHT OUTER JOIN) - 오른쪽 테이블의 모든 데이터와 왼쪽 테이블의 동일 데이터 추출
  • 완전 외부 조인(FULL OUTER JOIN) - 양쪽의 모든 데이터 추출

UNION

• UNION - 여러 SQL문의 결과에 대한 합집합(중복 행 제거)
• UNION ALL - 여러 SQL문의 결과에 대한 합집합(중복 행 제거하지 않음)
• INTERSECTION - 여러 SQL문의 결과에 대한 교집합(중복 행 제거)
• EXCEPT(MINUS) - 앞의 SQL문의 결과와 뒤의 SQL문의 결과 사이의 차집합 (중복 행 제거)

GROUP BY

테이블에서 특정 속성의 값이 같은 튜플을 모아 그룹을 만들고, 그룹별로 개수, 합계, 평균, 최소, 최대 값 등을 검색하기 위해 GROUP BY 를 사용한다.

SELECT [DISTINCT] 그룹핑 컬럽, 집계함수
FROM 테이블명
[WHERE 조건]
GROUP BY 그룹핑 컬럼
[HAVING 그룹 제한조건]
[ORDER BY 정렬대상 컬럼 [ASC|DESC] ];

- 어떤 조건을 만족하는 그룹들에 대해서만 집단 함수를 적용하도록 제한 할 때는 WHERE절이 아니라 
	HAVING 절을 사용함

서브쿼리

다른 sql문에 포함되어 WHERE절, FROM절, SELECT절에 사용되는 또 다른 sql문

트리거 (Trigger)

데이터베이스에 특정한 변경이 가해졌을 때(명시된 이벤트가 발생할 때마다) DBMS가 이에 대응해서 자동적으로 호출하는 일종의 프로시저를 말함

트리거 예제

SQL> CREATE OR REPLACE TRIGGER triger_test
       BEFORE
       UPDATE ON dept
       FOR EACH ROW
	   
	   BEGIN
        DBMS_OUTPUT.PUT_LINE('변경 전 컬럼 값 : ' || : old.dname);
        DBMS_OUTPUT.PUT_LINE('변경 후 컬럼 값 : ' || : new.dname);
     END;
     /

-- DBMS_OUTPUT.PUT_LINE을 출력
SQL> SET SERVEROUTPUT ON ; 

-- UPDATE문을 실행시키면.. 
SQL> UPDATE dept SET dname = '총무부' WHERE deptno = 30

-- 트리거가 자동 실행되어 결과가 출력된다. 
변경 전 컬럼 값 : 인사과
변경 후 컬럼 값 : 총무부

1 행이 갱신되었습니다.