SQL문 총 정리

 

SQL(Structured Query Language)

• 관계형 데이터베이스의 표준 질의어
• 사용 용도에 따라 정의, 조작, 제어 기능으로 분류된다

 

---

 

DDL(Data Define Language, 데이터 정의어)

DB를 구축하거나 수정할 목적으로 사용하는 언어

---

CREATE

SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 정의

'이름', '학번', '전공', '성별', '생년월일'로 구성된 <학생> 테이블 정의하는 SQL문 작성

• '이름'은 NULL 이 올 수 없고, '학번'은 기본키
• '전공'은 <학과> 테이블의 '학과코드'를 참조하는 외래키로 사용
• <학과> 테이블에서 삭제가 일어나면 관련된 튜플들의 전공 값을 NULL로 만든다.
• <학과> 테이블에서 '학과코드'가 변경되면 전공 값도 같은 값으로 변경한다.
• '생년월일'은 1980-01-01 이후의 데이터만 저장할 수 있다.
• 제약 조건의 이름은 '생년월일제약'으로 한다.
• 각 속성의 데이터 타입은 적당하게 지정한다. 단 '성별'은 도메인 'SEX'를 사용한다.

CREATE TABLE 학생
    (이름 VARCHAR(15) NOT NULL,
    학번 CHAR(8),
    전공 CHAR(5),
    성별 SEX,
    생년월일 DATE,
    PRIMARY KEY(학번),
    FOREIGIN KEY(전공) REFERENCES 학과(학과코드)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    CONSTRAINT 생년월일제약
    	CHECK(생년월일>='1980-01-01'));

 

CREATE VIEW 안산고객(성명, 전화번호)
AS SELECT 성명, 전화번호
FROM 고객
WHERE 주소='안산시';

CREATE UNIQUE INDEX 고객번호_idx
ON 고객(고객번호 DESC);

 

ALTER

TABLE에 대한 정의를 변경

새로운 속성(열)을 추가할 때
ALTER TABLE 테이블명 ADD 속성명 데이터_타입
(ALTER TABLE 학생 ADD 학년 VARCHAR(3);)

특정 속성의 DEFAULT 값을 변경할 때
ALTER TABLE 테이블명 ALTER 속성명
(ALTER TABLE 학생 ALTER 학번 VARCHAR(10) NOT NULL;)

특정 속성을 삭제할 때
ALTER TABLE 테이블명 DROP COLUMN 속성명

 

DROP

SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 삭제

DROP SCHEMA 스키마명 [CASCADE | RESTRICT];
DROP DOMAIN 도메인명 [CASCADE | RESTRICT];
DROP TABLE 테이블명 [CASCADE | RESTRICT];
DROP VIEW 뷰명 [CASCADE | RESTRICT];
DROP INDEX 인덱스명 [CASCADE | RESTRICT];
DROP CONSTRAINT 제약조건명;

CASCADE : 제거할 요소를 참조하는 다른 모든 개체를 함께 제거
RESTRICT : 다른 개체가 제거할 요소를 참조중일 때는 제거를 취소

 

 

DCL(Data Control Language, 데이터 제어어)

데이터의 보안, 무결성, 회복, 병행 제어 등을 정의하는데 사용하는 언어

데이터베이스 관리자(DBA)가 데이터 관리 목적으로 사용

---

COMMIT

명령에 의해 수행된 결과를 실제 물리적 디스크로 저장하고,

데이터베이스 조작 작업이 정상적으로 완료되었음을 관리자에게 알려줌

 

ROLLBACK *

변경되었으나 아직 COMMIT되지 않은 모든 내용들을 취소하고

데이터베이스를 이전 상태로 되돌리는 명령어

 

GRANT

데이터베이스 사용자에게 사용 권한을 부여함

GRANT 권한_리스트 ON 개체 TO 사용자 [WITH GRANT OPTION];

 

REVOKE

데이터베이스 사용자의 사용 권한을 취소함

REVOKE [GRANT OPTION FOR] 권한_리스트 ON 개체 FROM 사용자 [CASCADE];

 

• 권한 종류 : ALL, SELECT, INSERT, DELETE, UPDATE 등
• WITH GRANT OPTION : 부여받은 권한을 다른 사용자에게 다시 부여할 수 있는 권한을 부여함
• GRANT OPTION FOR : 다른 사용자에게 권한을 부여할 수 있는 권한을 취소함
• CASCADE : 권한 취소시 권한을 부여받았던 사용자가 다른 사용자에게 부여한 권한도 연쇄적으로 취소함

 

 

DML(Data Manipulation Language, 데이터 조작어)

데이터베이스 사용자가 저장된 데이터를 실질적으로 관리하는데 사용되는 언어

---

SELECT

테이블에서 튜플 검색

SELECT [PREDICATE] [테이블명]속성명 [AS 별칭]
FROM 테이블명
[WHERE 조건]
[GROUP BY 속성명]
[HAVING 조건]
[ORDER BY 속성명 [ASC | DESC]];

PREDICATE : 검색할 튜플 수를 제한하는 명령어
(DISTINCT : 중복된 튜플이 있으면 그 중 첫번째 한개만 표시)
GROUP BY : 특정 속성을 기준으로 그룹화하여 검색할 때 HAVING(그룹의 조건)과 함께 사용

 

INSERT

테이블에 새로운 튜플 삽입

INSERT INTO 테이블명(속성명) VALUES 데이터
(INSERT INTO 사원(이름, 부서) VALUES ('홍승현', '인터넷');)

• SELECT문 사용
INSERT INTO 편집부원(이름, 생일, 주소, 기본급)
SELECT 이름, 생일, 주소, 기본급
FROM 사원
WHERE 부서='편집';

 

DELETE

테이블에서 튜플 삭제

DELETE FROM 테이블명
[WHERE 조건];

 

UPDATE

테이블에서 튜플 내용 갱신

UPDATE 테이블명
SET 속성명 = 데이터
[WHERE 조건];

 

 

그룹 함수

GROUP BY절에 지정된 그룹별로 속성의 값을 집계할 때 사용

---

• COUNT(속성명) : 그룹별 튜플 수를 구하는 함수
• SUM(속성명) : 그룹별 합계를 구하는 함수
• AVG(속성명) : 그룹별 평균을 구하는 함수
• MAX(속성명) : 그룹별 최대값을 구하는 함수
• MIN(속성명) : 그룹별 최소값을 구하는 함수
• STDDEV(속성명) : 그룹별 표준편차를 구하는 함수
• VARIANCE(속성명) : 그룹별 분산을 구하는 함수

 

 

JOIN

JOIN은 2개의 릴레이션에서 연관된 튜플들을 결합하여 하나의 새로운 릴레이션을 반환

크게 INNER JOIN과 OUTER JOIN으로 구분됨

---

• LEFT(RIGHT) OUTER JOIN : INNER JOIN의 결과를 구한 후, 우(좌)측 항 릴레이션의 어떤 튜플과도 맞지 않는 좌(우)측 항의 릴레이션에 있는 튜플들에 NULL 값을 붙여서 INNER JOIN의 결과에 추가함

SELECT 학번, 이름, 학생.학과코드, 학과명
FROM 학생 LEFT OUTER JOIN 학과
ON 학생.학과코드 = 학과.학과코드;

 

 

집합 연산자

집합 연산자를 사용해 2개 이상의 테이블의 데이터를 하나로 통합

---

• UNION : 두 SELECT문의 결과를 통합해 모두 출력하되 중복된 행은 한 번만 출력(합집합)
• UNION ALL : 두 SELECT문의 결과를 통합해 중복된 행까지 모두 출력(합집합)
• INTERSECT : 두 SELECT문의 결과 중 공통된 행만 출력(교집합)
• EXCEPT : 첫번째 SELECT문의 결과에서 두번째 SELECT문의 결과를 제외한 행을 출력(차집합)

SELECT 속성명 FROM 테이블명
UNION | UNION ALL | INTERSECT | EXCEPT
SELECT 속성명 FROM 테이블명

 

 

하위 질의

SQL의 WHERE절에서는 함수를 직접 호출하여 사용하는 것이 불가능하므로

하위질의(Subquery)를 사용해 원하는 조건의 결과를 얻을 수 있다

 

SELECT 이름, 기본급, 주소 FROM 사원
WHERE 기본급 < ALL (SELECT 기본급 FROM 사원 WHERE 주소='망원동');

SELECT * FROM 사원
WHERE 이름 NOT IN (SELECT 이름 FROM 여가활동);

SELECT 이름, 주소 FROM 사원
WHERE 이름 = (SELECT 이름 FROM 여가활동 WHERE 취미='나이트댄스');