티스토리 뷰
1. DDL (Data Definition Language) - CREATE, ALTER, DROP
스키마, 도메인, 테이블, 뷰, 인덱스를 정의/생성, 변경, 제거할 때 사용하는 SQL
1-1. CREATE
CREATE TABLE 직원
(직원번호 INT,
이름 CHAR (10) NOT NULL,
전화번호 CHAR (8),
성별 GENDER,
나이 INT,
봉급 DECIMAL (8,2),
부서번호 CHAR (4),
PRIMARY KEY (직원번호),
FOREIGN KEY (부서번호) REFERENCES 부서 (부서번호),
CONSTRAINT CHECK (나이>=20 AND 나이<=60) );
- NULL값을 허용하지 않으려면 NOT NULL
- PRIMARY KEY는 하나만 쓸 수 있지만 UNIQUE는 여러개 쓸 수 있다 (기본키가 아닌 후보키, 즉 대체키)
ㄴ PRIMARY KEY는 NOT NULL을 명시하지 않더라도 기본적으로 NULL일 수 없으나 UNIQUE는 NOT NULL을 명시하지 않으면 NULL값 가능
- FOREIGN KEY에는 REFERENCES 말고도 ON DELETE (옵션), ON UPDATE (옵션) 가 붙을 수 있음. 옵션들은
NO ACTION: 참조 무결성이 위배되는 경우 참조되고 있는 튜플의 삭제를 거절
CASCADE: 참조되는 튜플이 삭제될 때 참조하는 튜플도 같이 삭제
SET NULL: 외래키를 NULL값으로 변경
SET DEFAULT: 외래키를 디폴트값으로 변경
- CONSTRAINT CHECK: 테이블의 행이 갱신/삽입될 때 유지되어야 할 무결성 제약조건을 표현
- SQL에서 TABLE 말고도 CREATE로 만들 수 있는 것들은 더 있다:
1) 스키마: CREATE SCHEMA 스키마_이름 AUTHORIZATION 사용자_id;
테이블이나 기타 구성요소를 하나의 사용자가 다룰 수 있도록 묶어서 권한을 지정해준 것.
2) 도메인: CREATE DOMAIN 도메인_이름 데이터_타입 [DEFAULT 기본값 CONSTRAINT 제약조건 CHECK 범위]
범위가 지정된 데이터 타입을 사용할 수 있다. 일반 프로그래밍 언어의 클래스마냥 기본 데이터타입 대신 쓰일 수 있음
3) 인덱스: CREATE [UNIQUE] INDEX 인덱스_이름 ON 테이블_이름 (열_이름 ASC | DESC) [CLUSTER]
사용자가 생성하면 시스템에 의해 자동관리되는 컬럼. MySQL에서 id 같은 값에다 쓰는 AUTO_INCREMENT 생각하면 될 듯.
4) 트리거: CREATE TRIGGER 트리거_이름 [시기옵션] [동작옵션] ON 테이블_이름 REFERENCING [NEW | OLD] TABLE AS 테이블_이름 FOR EACH NOW WHEN 조건식 (BEGIN~Body~END)
DB의 상태를 시스템이 감시하다가 특정한 조건이 만족되면 자동으로 문장을 실행하게 만들 수 있다.
1-2. ALTER
테이블에 대한 정의를 변경하는 명령문.
ALTER TABLE 학생 ADD 성적 CHAR DEFAULT 'F';
// 학생 테이블에 '성적' 이라는 컬럼이 추가되고 기본값으로 'F' 가 들어감
ALTER TABLE 학생 DROP 학점 CASCADE;
// 학생 테이블의 '학점' 컬럼을 삭제함 (참고: 컬럼이 '학점' 하나뿐인 경우 실행 불가)
ALTER TABLE 학생 ALTER 학점 SET DEFAULT 0;
// '학점' 컬럼의 기본값을 0으로 변경함
1-3. DROP
테이블이나 위에서 설명한 다른 것들 (스키마, 도메인, 인덱스, 트리거, 뷰, 제약조건) 을 삭제할 때 사용
DROP TABLE 테이블_이름 [CASCADE | RESTRICT]
- CASCADE: 얘를 참조하고 있던 뷰나 제약조건이 있으면 함께 삭제
- RESTRICT: 얘를 참조하고 있던 뷰나 제약조건이 있으면 실행 거부
//
2. DML (Data Manipulation Language) - SELECT, INSERT, DELETE, UPDATE
테이블에서 CRUD를 하기 위한 SQL 명령들.
2-1. SELECT
SELECT [ALL | DISTINCT] (열_리스트 | *) FROM 테이블_리스트
[WHERE 조건]
[GROUP BY 열_리스트 [HAVING 조건]]
[ORDER BY 열_리스트 [ASC | DESC]];
- 집계 함수: COUNT(), MAX(), MIN(), SUM(), AVG()
- ORDER BY: ASC나 DESC와 함께 쓰여 오름차순 또는 내림차순 정렬을 해서 결과를 출력.
- GROUP BY: 특정 열의 값이 같은 튜플끼리 모아서 출력.
ㄴ HAVING: GROUP BY랑 함께 쓰여서 그룹에 제약조건을 걸어줄 수 있음
- LIKE: WHERE와 함께 쓰여 문자열의 형태를 지정해줄 수 있음. 정규표현식 생각하면 됨. %랑 _이 있다
- IS NULL, IS NOT NULL: 널값을 검색조건에 명세할 경우 이렇게 쓴다. =, ==, !=가 아님에 주의
- 부속 질의어 (Nested): IN, NOT IN, = SOME 등을 사용하여 SELECT를 여러번 중첩시켜 쓸 수 있음
- 여러 테이블에서의 검색 (조인): 테이블 2개에서 연관된 튜플을 결합한 뒤 검색. INNER JOIN, NATURAL INNER JOIN, OUTER JOIN이 있다.
- SELECT문 예시
SELECT 부서장 FROM 부서 WHERE 부서명 = '인사과'; // 인사과의 부서장 이름을 검색
SELECT * FROM 부서 WHERE 부서명 = '인사과'; // 부서명이 인사과인 행의 내용 모두 검색
SELECT DISTINCT 봉급 FROM 직원; // DISTINCT를 쓰면 중복된 결과값 제거
SELECT 이름 FROM 직원 WHERE 봉급 >= 300 ORDER BY 나이 ASC, 봉급 DESC; // 나이의 오름차순으로 정렬해서 출력 (나이가 같을 시 봉급 내림차순으로 정렬)
SELECT COUNT (직원번호) FROM 직원 WHERE 부서번호 = '300'; // 집계함수를 사용하여 '300' 부서의 직원 수 출력
SELECT 부서번호 FROM 직원 GROUP BY 부서번호 HAVING COUNT(*) >=3; // 소속 직원이 3명 이상인 부서번호를 검색
SELECT 이름 FROM 직원 WHERE 전화번호 LIKE '02%'; // 전화번호가 02로 시작하는 직원 이름 검색
SELECT 직원번호, 이름 FROM 직원 WHERE 부서번호 IS NULL; // 부서번호가 NULL인 직원번호, 이름 검색
SELECT 이름 FROM 직원 WHERE 부서번호 IN (SELECT 부서번호 FROM 부서 WHERE 부서명 = '인사과'); // 인사과의 직원 이름을 검색. '직원' 테이블과 '부서' 테이블 두 개가 쓰였다.
SELECT 이름 FROM 직원 NATURAL INNER JOIN 부서 WHERE 부서명 = '인사과'; // 인사과의 직원 이름을 검색. 바로 위의 문장과 같은 기능을 하는 자연조인을 이용한 예시
2-2. INSERT
INSERT INTO 테이블 [열_리스트] VALUES [값_리스트]
- 모든 열에 값을 입력할 때는 열_리스트를 생략 가능
INSERT INTO 직원 VALUES (400, '김민지', 25, 300, 'A50');
INSERT INTO 직원 (직원번호), 이름, 나이, 봉급, 부서번호) VALUES (400, '김민지', 25, 300, 'A50');
- 값_리스트 부분에 SELECT문을 부속 질의문으로 쓸 수도 있다
INSERT INTO 직원 (직원번호, 이름, 봉급)
SELECT 직원번호, 이름, 봉급 FROM 직원 WHERE 부서번호 = 'A50';
2-3. DELETE
DELETE FROM 테이블 WHERE ~
- WHERE를 생략하면 테이블의 모든 튜플을 삭제함 (DROP과는 달리 테이블 자체를 삭제하진 않음)
- SELECT에서와 마찬가지로 WHERE 뒤에 부속 질의문을 쓸 수도 있음
DELETE FROM 직원 WHERE 부서번호 = SOME (SELECT 부서번호 FROM 직원 WHERE 이름 = '이지영'); // 이지영과 동일 부서에 근무하는 직원들을 모두 삭제
2-4. UPDATE
UPDATE 테이블 SET 열_이름 = 값/식 WHERE ~
- SET절에는 그냥 단순값이 들어갈 수도 있지만 산술식이 들어갈 수도 있음
- WHERE 뒤에 마찬가지로 부속 질의문이 들어갈 수도 있음
UPDATE 성적 SET 기말 = 기말 - 10 WHERE 학번 IN
( SELECT 학번 FROM 학생 WHERE 학과 = '컴공' ); // 컴공과 학생들의 기말성적을 10점씩 감점
//
3. DCL (Data Control Language) - COMMIT, ROLLBACK, GRANT, REVOKE
데이터의 보안, 무결성, 회복, 병행 제어 등을 정의하는 SQL로 DBA가 사용하게 됨
3-1. COMMIT
트랜잭션의 모든 변경내용들을 DB에 영구히 반영
3-2. ROLLBACK
변경내용들을 취소하고 DB를 이전 상태로 되돌리는 명령어
3-3. GRANT
GRANT 권한명 ON 데이터_객체 TO 사용자 [WITH GRANT OPTION]
- 권한명은 SELECT, INSERT 등 다른 SQL 명령어이며 데이터객체는 주로 테이블
- WITH GRANT OPTION을 붙이면 그 사용자가 또 다른 사용자에게 해당 권한을 부여할 수 있음
GRANT INSERT, DELETE ON 학생 TO user1 WITH GRANT OPTION; // user1이 학생 테이블에 대한 INSERT, DELETE 권한을 가지며, 다른 유저에게도 권한을 줄 수 있다
3-4. REVOKE
REVOKE 권한명 [GRANT OPTION FOR] ON 데이터_객체 FROM 사용자 [CASCADE | RESTRICT]
- GRANT OPTION FOR: 접근 권한을 취소하는게 아니라 GRANT OPTION 준걸 취소하는 것
- CASCADE: 해당 유저가 다른 유저에게 줬던 접근 권한도 같이 회수
- TO가 아니라 FROM임에 유의 (사용자 '로부터' 권한을 회수한다)
REVOKE SELECT ON 학생 FROM user2 CASCADE; // user2의 학생 테이블에 대한 SELECT 권한을 회수하고, user2가 다른 유저들에게 부여했던 권한도 같이 회수