[SQL] SQL 기본 및 활용 - DDL, DCL, DML 예제
정보처리기사와 SQLD가 유사한 내용이 많길래 한번 따보기로 마음먹었다.
무엇보다 SQL이 꽤나 재밌어 보이기도 하고, 활용도도 높은듯 하여,,
정처기때 정리해 둔 내용들 위주로 기록!
👩🏻💻 목차
- SQL - DDL
- SQL - DCL
- SQL - DML
✅ SQL - DDL(Data Define Language - 데이터 정의어)
DB를 구축하거나 수정할 목적으로 사용
CREATE, ALTER, DROP
CREATE
SCHEMA, DOMAIN, TABLE, VIEW, INDEX 정의
CREATE SCHEMA 스키마 정의 명령문
스키마: 데이터베이스의 구조와 제약 조건에 관한 전반적인 명세를 기술한 것.
CREATE SCHEMA 스키마명 AUTHORIZATION 사용자_id;
ex) 소유권자인 사용자 ID‘홍길동’이 스키마‘대학교’를 정의
CREATE SCHEMA 대학교 AUTHRIZATION 홍길동;
CREATE DOMAIN 도메인 정의 명령문
도메인: 하나의 속성이 취할 수 있는 동일한 유형의 원자값들의 집합을 의미.
CREATE DOMAIN 도메인명 [AS] 데이터_타입
[DEFAULT 기본값]
[CONSTRAINT 제약조건명 CHECK (범위값)];
데이터타입: SQL에서 지원하는 데이터 타입
기본값: 데이터를 입력하지 않았을 때 자동으로 입력되는 값
ex) 성별이 ‘남’또는 ‘여’와 같이 정해진 1개이 문자로 표현되는 도메인 SEX를 정의
CREATE DOMAIN SEX CHAR(1)
DEFAULT ‘남’
CONSTRAINT VALID-SEX CHECK(VALUE IN(’남’, ‘여’));
CREATE TABLE
테이블: 데이터베이스의 설계 단계에서는 테이블을 주로 릴레이션이라 부르고, 조직 검색 시에는 테이블이라 부름.
테이블을 정의하는 명령문
CREATE TABLE 테이블명
(속성명 데이터 타입 [DEFAULT 기본값][NOT NULL] ...
[,PRIMARY KEY(기본키_속성명, ...)]
[,UNIQUE(대체키_속성명, ...)]
[,FOREIGN KEY(외래키_속성명, ...)]
[REFERENCES 참조테이블(기본키_속성명, ...)]
[ON DELETE 옵션]
[ON UPDATE 옵션]
[, CONSTRAINT 제약조건명][CHECK(조건식]);
Primary key: 기본키로 사용할 속성을 지정
Unique: 대체키로 사용할 속성, 중복값을 가질 수 없음
Foreign Key ~ Reference ~ : 외래키로 사용할 속성을 지정
- On delete 옵션: 참조 테이블의 튜플이 삭제되었을 때 기본 테이블에 취해야 할 사항을 지정
- On update 옵션: 참조 테이블의 참조 속성 값이 변경되었을 때 기본 테이블에 취해야 할 사항을 지정
Constaint: 제약 조건의 이름 지정
Check: 속성 값에 대한 제약 조건을 정의
ex) 다음과 같은 요구사항을 만족하는 테이블 <patient>를 정의하는 SQL문
- ‘id(문자 5)’, ‘name(문자10)’, ‘sex(문자1)’, ‘phone(문자 20)’ 속성을 가짐
- ‘id’속성은 기본키
- ‘sex’ 속성은 ‘f’ 또는 ‘m’ 값만 갖도록 함 (제약조건명:sex_ck)
- ‘id’는 <doctor> 테이블에 있는 ‘doc_id’를 참조 (제약조건명: id_fk)
CREATE TABLE patient
(id CHAR(5) PRIMARY KEY,
name CHAR(10),
sex CHAR(1),
phone CHAR(20),
CONSTRAINT sex_ck CHECK(sex='f' or sex='m'),
CONSTRAINT id_fk FOREIGN KEY(id) REFERENCES doctor(doc_id));
아래의 조건을 만족하는 테이블 <Instructor> 정의
- id(문자5), name(문자15), dept(문자15) 속성을 가짐
- id 속성은 기본키
- name 속성은 NULL이 올수 없음
- dept 속성은 <Department> 테이블의 ‘dept’속성을 참조하는 외래키
- <Department> 테이블에서 튜플이 삭제되면 관련된 모든 튜플의 ‘dept’ 값은 NULL로 변경
- <Department> 테이블의 ‘dept’ 속성이 변경되면 <Instructor> 테이블의 관련된 모든 속성 값도 같은 값으로 변경되어야 함
CREATE TABLE Instructor
( id CHAR(5) PRIMARY KEY,
name CHAR(15) NOT NULL,
dept CHAR(15)FOREIGN KEY,
dept REFERENCES Department(dept)
ON DELETE SET NULL
ON UPDATE CASCADE
);
CREATE VIEW
뷰를 정의하는 명령문
CREATE VIEW 뷰명 [(속성명[, 속성명, ...]]
AS SELECT 문;
ex) <고객> 테이블에서 ‘주소’가 ‘안산시’인 고객들의 ‘성명’과 ‘전화번호’를 ‘안산고객’이라는 뷰로 정의
CREATE VIEW 안산고객(성명, 전화번호)
AS SELECT 성명, 전화번호
FROM 고객
WHERE 주소 = ‘안산시’;
아래의 <요구사항>을 만족하는 뷰<CC>정의
- <Course>와 <Instructor> 릴레이션 이용
- <Course>와 ‘Instructor’ 속성 값과 <Instructor>의 ‘id’ 속성이 같은 자료에 대한 view를 정의
- <cc> 뷰는 ‘ccid’, ‘ccname’, ‘instname’ 속성을 가짐
- <cc> 뷰는 <Course> 테이블의 ‘id’, ‘name’, <Instructor> 테이블의 ‘name’ 속성을 사용
CREATE VIEW CC (ccid, ccname, instname) AS
SELECT Course.id, Course.name, Instructor.name
FROM Course, Instructor
WHERE Course.instructor = Instructor.id;
CREATE INDEX
인덱스를 정의하는 명령문
CREATE [UNIQUE] INDEX 인덱스명
ON 테이블명(속성명[ASC|DESC] ,[속성명[ASC|DESC]])
[CLUSTER];
UNIQUE 가 사용된 경우: 중복 값이 없는 속성으로 인덱스 생성!, 생략된 경우: 중복 값 허용
ASC: 오름차순, DESC: 내림차순, 생략: 오름차순으로 정렬!
CLUSTER 사용 시 인덱스가 클러스터드 인덱스로 설정됨 (인덱스 키의 순서에 따라 데이터가 정렬되어 저장됨)
ex) <고객>테이블에서 UNIQUE한 특성을 갖는 ‘고객 번호’속성에 대해 내림차순으로 정렬하여 ‘고객번호_idx’ 라는 이름으로 인덱스 정의
CREATE UNIQUE INDEX 고객번호_idx
ON 고객(고객번호 DESC);
ALTER TABLE
TABLE에 대한 정의를 변경하는데 사용
ALTER TABLE 테이블명 ADD 속성명 데이터_타입 [DEFAULT '기본값']; #새로운 속성(열)추가
ALTER TABLE 테이블명 ALTER 속성명 [SET DEFAULT '기본값']; #변경
ALTER TABLE 테이블명 DROP COLUMN 속성명 [CASCADE]; #삭제
ex) <학생>테이블에 20자의 가변길이를 가진 ‘주소’속성을 추가하려면?
ALTER TABLE 학생 ADD 주소 VARCHAR(20);
<patient>테이블에 데이터 타입이 문자 20자리인 ‘job’ 속성을 추가하려면?
ALTER TABLE patient ADD job char(20);
<학생>테이블의 ‘학번’필드의 데이터 타입과 크기를 VARCHAR(10)으로 하고 NULL 값이 입력되지 않도록 변경
ALTER TABLE 학생 ALTER 학번 VARCHAR(10) NOT NULL;
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 다른 개체가 제거할 요소를 참조중일 때는 제거를 취소
ex) <학생>테이블을 제거하되, <학생> 테이블을 참조하는 모든 데이터를 함께 제거
DROP TABLE 학생 CASCADE;
✅ SQL - DCL(Data Control Language - 데이터 제어어)
DCL은 데이터의 보안, 무결성, 회복, 병행 제어 등을 정의하는 데 사용
DCL은 데이터베이스 관리자(DBA)가 데이터 관리를 목적으로 사용
COMMIT, ROLLBACK, GRANT, REVOKE
GRANT/REVOKE
데이터베이스 관리자가 사용자에게 권한을 부여하거나 취소하는 명령어
GRANT 권한부여 / REVOKE 권한취소
사용자등급 지정 및 해제
사용자 등급
- DBA: 데이터베이스 관리자
- RESOURCE: 데이터베이스 및 테이블 생성 기능자
- CONNECT: 단순 기능자
GRANT 사용자등급 TO 사용자_ID_리스트 [IDENTIFIED BY 암호];
REVOKE 사용자등급 FROM 사용자_ID_리스트;
ex) 사용자 ID가 ‘NABI’ 인 사람한테 데이터베이스 및 테이블을 생성할 수 있는 권한을 부여하려면?
GRANT RESOURCE TO NABI;
ex) 사용자 ID가 ‘STAR’ 인 사람에게 단순히 데이터베이스의 정보 검색 권한만 부여하면?
GRANT CONNECT TO STAR;
테이블 및 속성에 대한 권한 부여 및 취소
- 권한종류: ALL, SELECT, INSERT, DELETE, UPDATE 등
- WITH GRANT OPTION: 부여받은 권한을 다른 사용자에게 다시 부여할 수 있는 권한을 부여
- GRANT OPTION FOR: 다른 사용자에게 권한을 부여할 수 있는 권한을 취소
- CASCADE: 권한 취소 시 권한을 부여받았던 사용자가 다른 사용자에게 부여한 권한도 연쇄적으로 취소
GRANT 권한_리스트 ON 개체 TO 사용자 [WITH GRANT OPTION];
REVOKE [GRANT OPTION FOR] 권한_리스트 ON 개체 FROM 사용자 [CASCADE];
ex) 사용자 ID가 ‘NABI’인 사람에게 <고객> 테이블에 대한 모든 권한과 다른 사람에게 권한을 부여할 수 있는 권한까지 부여?
GRANT ALL ON 고객 TO NABI WITH GRANT OPTION
ex) 사용자 ID가 ‘STAR’인 사람에게 부여한 <고객> 테이블에 대한 권한 중 UPDATE 권한을 다른 사람에게 부여할 수 있는 권한만 취소?
REVOKE GRANT OPTION UPDATE ON 고객 FROM STAR
COMMIT
- COMMIT은 트랜잭션 처리가 정상적으로 완료된 후 트랜잭션이 수행한 내용을 데이터베이스에 반영
- COMMIT 명령을 실행하지 않아도 DML문이 성공적으로 완료되면 자동으로 COMMIT되고, DML이 실패하면 자동으로 ROLLBACK되도록 Auto Commit 기능을 설정할 수 있음
ROLLBACK
- ROLLBACK은 변경되었으나 아직 COMMIT되지 않은 모든 내용들을 취소하고 데이터베이스를 이전 상태로 되돌리는 명령어
SAVEPOINT
- SAVEPOINT는 트랜잭션 내에 ROLLBACK 할 위치인 저장점을 지정하는 명령어
- 저장점을 저장할 때는 이름을 부여
- ROLLBACK을 할 때 지정된 저장점까지의 트랜잭션 처리내용이 모두 취소됨
트랜잭션은 데이터베이스에서 하나의 논리적 기능을 수행하기 위한 일련의 연산집합, 작업의 단위!
하나의 트랜잭션은 COMMIT되거나, ROLLBACK되야함
ex) 김하늘에게 <학생> 테이블에 대한 접근 및 조작에 관한 모든 권한 부여
GRANT ALL ON 학생 TO 김하늘;
김하늘에게 <강좌> 테이블에 대해 삭제하는 권한을 부여하고, 이 삭제 권한을 다른 사람에게도 부여할 수 있는 권한을 부여
GRANT DELETE ON 강좌 TO 김하늘 WITH GRANT OPTION;
임꺽정에게 부여된 <교수> 테이블에 대한 SELECT, INSERT, DELETE 권한을 취소
REVOKE SELECT, INSERT, DELETE ON 교수 FROM 임꺽정;
<수강> 테이블에 대해 임꺽정에게 부여된 UPDATE 권한과 임꺽정이 다른 사람에게 UPDATE 권한을 부여할 수 있는 권한, 그리고 임꺽정이 다른사람에게 부여한 UPDATE 권한도 모두 취소
REVOKE UPDATE ON 수강 FROM 임꺽정 CASCADE;
✅ SQL - DML(Data Manipulation Language - 데이터 조작어)
DML은 데이터베이스 사용자가 저장된 데이터를 실질적으로 관리하는데 사용됨
DML은 데이터베이스 사용자와 데이터베이스 관리 시스템 간의 인터페이스를 제공
SELECT, INSERT, DELETE, UPDATE
삽입문 (INSERT INTO~)
기본 테이블에 새로운 튜플을 삽입할 때 사용
날짜 데이터는 숫자로 취급하지만 ‘ ‘ 또는 # # 으로 묶어주기
INSERT INTO 테이블명 ([속성명1, 속성명2, ...])
VALUES (데이터1, 데이터2, ...);
- 대응하는 속성과 데이터는 개수와 데이터 유형이 일치해야함
- 기본 테이블의 모든 속성을 사용할때는, 속성 생략 가능
- SELECT문을 사용하여 다른 테이블의 검색 결과를 삽입 가능
ex) <사원> 테이블에 (이름-홍승현, 부서-인터넷)을 삽입
INSERT INTO 사원(이름,부서) VALUES(’홍승현’,’인터넷’);
<사원> 테이블에 (장보고, 기획, 05/03/07, 홍제동, 90)을 삽입
INSERT INTO 사원 VALUES (’장보고’, ‘기획’, #05/03/07#, ‘홍제동’, 90);
<사원>테이블에 있는 편집부의 모든 튜플을 편집부원(이름, 생일, 주소, 기본급) 테이블에 삽입
INSERT INTO 편집부원(이름, 생일, 주소, 기본급)
SELECT 이름, 생일, 주소, 기본급
FROM 사원
WHERE 부서 = ‘편집’;
삭제문(DELETE FROM~)
삭제문은 기본 테이블에 있는 튜플들 중에서 특정 튜플(행)을 삭제할 때 사용
DELETE
FROM 테이블명
[WHERE 조건];
모든 레코드 삭제시 WHERE 절 생략
DELETE문은 테이블 구조나 테이블 자체는 남겨두고, 테이블 내의 튜플만 삭제
테이블을 완전히 제거하려면 DROP 사용
ex) <사원> 테이블에서 “임꺽정”에 대한 튜플을 삭제
DELETE
FROM 사원
WHERE 이름 = “임꺽정”;
<사원> 테이블에서 “인터넷”부서에 대한 모든 튜플을 삭제
DELETE
FROM 사원
WHERE 부서 = “인터넷”;
<사원> 테이블의 모든 레코드를 삭제
DELETE
FROM 사원
갱신문(UPDATE ~ SET ~)
갱신문은 기본 테이블에 잇는 튜플들 중에서 특정 튜플의 내용을 변경할 떄 사용
UPDATE 테이블명
SET 속성명 = 데이터[, 속성명 = 데이터, ...]
[WHERE 조건];
ex) <사원> 테이블에서 ‘홍길동’의 ‘주소’를 ‘수색동’으로 수정
UPDATE 사원
SET 주소 = ‘수색동’
WHERE 이름 = ‘홍길동’;
<사원> 테이블에서 ‘황진이’의 ‘부서’를 ‘기획부’로 변경하고 ‘기본급'을 5만원 인상
UPDATE 사원
SET 부서 = ‘기획’ , 기본급 = 기본급 + 5
WHERE 이름 = ‘황진이’;
DML - SELECT1
SELECT[PREDICATE][테이블명.]속성명[AS 별칭][,테이블명.[속성명] ,,,]
[, 그룹함수(속성명)[AS 별칭]]
FROM 테이블명[, 테이블명 ...]
[WHERE 조건]
[GROUP BY 속성명, 조건명, ...]
[HAVING 조건]
[ORDER BY 속성명 [ASC | DESC]];
- SELECT절
- PREDICATE: 검색할 튜플 수를 제한하는 명령어를 기술
- DISTINCT: 중복된 튜플이 있으면 첫번째 한개만 표시
- 속성명: 검색하여 불러올 속성(열) 또는 속성을 이용한 수식을 지정
- AS: 속성이나 연산의 이름을 다른 이름으로 표시하기 위해 사용
- FROM절: 검색할 데이터가 들어있는 테이블 이름!
- WHERE절: 검색할 조건을 기술
- ORDER BY절: 데이터를 정렬하여 검색할 때 사용
- 속성명: 정렬의 기준이 되는 속성명 기술
- ASC: 오름차, DESC: 내림차, 생략시 오름차순 정렬
기본 검색
<사원> 테이블의 모든 튜플을 검색
SELECT * FROM 사원;
SELECT 사원.* FROM 사원;
SELECT 이름,부서,생일,주소,기본급 FROM 사원;
SELECT 사원.이름, 사원.부서, 사원.생일, 사원.주소, 사원.기본급 FROM 사원;
<사원> 테이블에서 ‘주소’를 검색하되 한번만 출력
SELECT DISTINCT 주소
FROM 사원;
<사원> 테이블에서 ‘기본급’에 특벌수당 10을 더한 월급을 ‘XX부서의 XXX의 월급 XXX’ 형태로 출력
SELECT 부서 + ‘부서의’ AS 부서2.이름 + ‘의 월급’ AS 이름2.기본급 + 10 AS 기본급2
FROM 사원;
조건 지정 검색
<사원> 테이블에서 ‘기획’부의 모든 튜플을 검색
**SELECT ***
FROM 사원
WHERE 부서 = ‘기획’;
<사원> 테이블에서 ‘기획’부서에 근무하면서 ‘대흥동’에 사는 사람의 튜플
**SELECT ***
FROM 사원
WHERE 부서 = ‘기획’ AND 주소 = ‘대흥동’;
<사원> 테이블에서 ‘부서’가 ‘기획’이거나 ‘인터넷’인 튜플을 검색
**SELECT ***
FROM 사원
WHERE 부서 = ‘기획’ OR ‘인터넷’;
<사원> 테이블에서 성이 ‘김’인 사람의 튜플을 검색
**SELECT ***
FROM 사원
WHERE 이름 LIKE “김%”;
LIKE 연산자: 대표 문자를 이용해 지정된 속성의 값이 문자 패턴과 일치하는 튜플을 검색
% 모든 문자 대표
_ 문자 하나 대표
# 숫자 하나 대표
<사원> 테이블에서 ‘생일’이 ‘01/01/69’에서 ‘12/31/73’사이인 튜플을 검색
**SELECT ***
FROM 사원
WHERE 생일 BETWEEN ‘#01/01/69#’ AND ‘#12/31/73#’;
<사원>테이블에서 주소가 NULL인 튜플 검색
**SELECT ***
FROM 사원
WHERE 주소 IS NULL;
정렬 검색
ORDER BY 절에 특정 속성을 지정하여 지정된 속성으로 자료를 정렬하여 검색
<사원>테이블에서 ‘주소’를 기준으로 내림차순 정렬시켜 상위 2개 튜플만 검색
**SELECT TOP 2 ***
FROM 사원
ORDER BY 주소 DESC;
<사원> 테이블에서 ‘부서’를 기준으로 오름차순으로 정렬하고, 같은 ‘부서’에 대해서는 ‘이름’을 기준으로 내림차순 정렬시켜서 검색
**SELECT ***
FROM 사원
ORDER BY 부서 ASC, 이름 DESC;
하위 질의
조건절에 주어진 질의를 먼저 수행하여 그 검색 결과를 조건절의 피연산자로 사용
ex) ‘취미’가 ‘나이트댄스’인 사원의 ‘이름’과 ‘주소’를 검색
SELECT ‘이름’, ‘주소’
FROM 사원
WHERE 취미 = (SELECT 이름 FROM 여가활동 WHERE 취미 = ‘나이트댄스’);
취미활동을 하지 않는 사원들 검색
**SELECT ***
FROM 사원
WHERE 이름 NOT IN (SELECT 이름 FROM 여가활동);
취미활동을 하는 사원들의 부서 검색
SELECT 부서
FROM 사원
WHERE 이름 EXISTS (SELECT 이름 FROM 여가활동 WHERE 여가활동.이름 = 사원.이름);
복수 테이블 검색
여러 테이블을 대상으로 검색 수행
ex) ‘경력’이 10년 이상인 사원의 ‘이름’, ‘부서’, ‘취미’, ‘경력’을 검색
SELECT 사원.이름, 사원.부서, 여가활동.취미, 여가활동.경력
FROM 사원, 여가활동
WHERE 여가활동.경력 >= AND 사원.이름 = 여가활동.이름;
SELECT 문제모음
다음 처리조건에서 요구하는 SQL문?
- 3,4 학년의 학번, 이름 조회
- IN 예약어 사용
- 속성명 아래의 괄호는 속성의 자료형을 의미
SELECT 학번, 이름
FROM 학생
WHERE 학년 IN (3,4);
<Sale>테이블에서 판매량(psale)이 10이상 20이하인 상품의 코드(pid)를 검색하는 SQL문?
SELECT pid
FROM Sale
WHERE psale BETWEEN 10 AND 20;