< SQL ( Structed Query Language ) >
관계 데이터베이스를 위한 표준 질의어
- 대화식 SQL : 데이터베이스 관리 시스템에 직접 접근해 질의를 작성하며 실행 ( SQL문만 집어넣고 실행하면 DB에서 가져옴)
- 삽입 SQL : 프로그래밍 언어 안에 SQL문 넣어 응용프로그램 작성
SQL의 분류
1. 데이터 정의어 ( DDL = Data Definition Language )
테이블을 생성하고 변경, 제거하는 기능 제공
※ DBMS를 실제 물리적으로 구현할때는 릴레이션(수학적인 데이터모델에서 사용하는 용어)을 테이블이라 표현
※ 릴레이션은 튜플의 순서가 없지만, 테이블은 튜플의 순서가 있다
2. 데이터 조작어 ( DML = Data Manipulation Language )
테이블에 데이터를 삽입, 삭제, 수정, 검색하는 기능 제공
SQL은 대부분 데이터 조작어이고, 데이터 조작어는 대부분 검색 기능을 수행한다
3. 데이터 제어어 ( DCL = Data Control Language )
보안을 위해 데이터에 대한 접근 및 사용 권한을 사용자별로 부여하거나 취소하는 기능 제공 ( 전체 데이터 권한 제어 )
< 데이터 정의어 ( DDL ) >
테이블 생성 : CREATE TABLE 문
- [ ]의 내용은 생략 가능
- SQL 질의문은 세미콜론(;)으로 문장의 끝을 표시, 대소문자 구분 X
CREATE TABLE 테이블_이름(
1. 속성_이름 데이터_타입 [NOT NULL] [DEFAULT 기본_값]
2. [PRIMARY KEY ( 속성_리스트 ) ]
3. [UNIQUE ( 속성_리스트 )]
4. [FOREIGN KEY (속성_리스트) REFERENCES 테이블_이름 (속성_리스트)] [ON DELETE 옵션] [ON UPDATE 옵션]
5. [CONSTRAINT 이름] [CHECK(조건)]
);
ex)
CREATE_TABLE 제품 (
제품번호 CHAR(3) NOT NULL,
제품명 VARCHAR(20),
재고량 INT,
제조업체 VARCHAR(20),
PRIMARY KEY(제품번호),
FOREIGN KEY(주문고객) REFERENCES 고객(고객아이디),
CHECK (재고량 >= 0 AND 재고량 <= 10000 )
);
1. 속성_이름 데이터_타입 [NOT NULL] [DEFAULT 기본_값]
- 속성의 정의
- 각 속성의 이름과 데이터 타입 결정
- NOT NULL : 속성이 NULL값을 허용하지 않음 ex) 고객아이디 VARCHAR(20) NOT NULL
- DEFAULT : 속성의 기본 값 지정 ex) 적립금 INT DEFAULT 0 ex) 담당자 VARCHAR(10) DEFAULT '김땡땡'
※ 문자열, 날짜 데이터는 작은 따옴표로 묶어서 표현 ( 작은 따옴표로 묶어진 문자열은 대소문자를 구분)
- 주요 데이터 타입
- INT, INTEGER : 정수
- CHAR(n) : 길이가 n인 고정 길이의 문자열
- VARCHAR(n) : 최대 길이가 n인 가변 길이의 문자열
- DECIMAL(p,s) : 고정(소수점 위 아래 숫자 정해져있음) 소수점 실수, p는 소수점 제외한 전체 숫자 길이이고 s는 소수점 이하 숫자의 길이 ex) 123.45 이면 p는 5이고 s는 2이다
- DATE : 연, 원, 일로 표현되는 날짜
- TIME : 시, 분, 초로 표현되는 시간
- DATETIME : 날짜와 시간, DATE와 TIME 합친 것
2. [ PRIMARY KEY ( 속성_리스트 ) ]
- 기본키를 지정
ex) PRIMARY KEY(고객아이디) ex) PRIMARY KEY(주문고객, 주문제품)
3. [ UNIQUE ( 속성_리스트 ) ]
- 대체키를 지정
- 대체키로 지정되는 속성의 값은 유일성을 가지며 기본키와 달리 NULL 허용된다
ex) UNIQUE(고객이름)
4. [ FOREIGN KEY (속성_리스트) REFERENCES 테이블_이름 (속성_리스트)] [ON DELETE 옵션] [ON UPDATE 옵션 ]
- 외래키를 지정
- 외래키가 어떤 테이블의 무슨 속성을 참조하는지 REFERENCES 키워드 다음에 제시
- ON DELETE 옵션, ON UPDATE 옵션 : 참조 무결성 제약조건 유지를 위해 참조되는 테이블에서 튜플 삭제 혹은 변경시 처리 방법을 지정하는 옵션
- NO ACTION ( 디폴트 ) : 튜플을 삭제 혹은 변경하지 못하게 함
- CASCADE : 관련 튜플을 함께 삭제 혹은 변경함
- SET NULL : 관련 튜플의 외래키 값을 NULL로 변경 ( NULL로 변경한다고 참조무결성 조건 위배하지 않는다 )
- SET DEFAULT : 관련 튜플의 외래키 값을 미리 지정한 기본값으로 변경함 c.f) 지원하지 않는 DBMS가 많다
ex) FOREIGN KEY(소속부서) REFERENCES 부서(부서번호) ON DELETE CASCADE
5. [CONSTRAINT 이름] [CHECK(조건)]
- 테이블에 정확하고 유효한 데이터를 유지하기 위해 특정 속성에 대한 제약조건을 지정
- CONSTRAINT 키워드와 함께 제약조건에 이름을 부여할 수도 있음
ex) CHECK ( 재고량 >= 0 AND 재고량 <= 1000 )
ex) CONSTRAINT CHK_CPY CHECK ( 제조업체 = '한빛제과' ) → CHECK ( 제조업체 = '한빛제과' )의 이름은 CHK_CPY
테이블 변경 : ALTER TABLE 문
1. 새로운 속성 추가
ALTER TABLE 테이블_이름 ADD 속성_이름 데이터_타입 [NOT NULL] [DEFAULT 기본_값];
ex) ALTER TABLE 고객 ADD 가입날짜 DATE;
2. 기존 속성 삭제
ALTER TABLE 테이블_이름 DROP COLUMN 속성_이름 [ CASCADE | RESTRICT ] ;
삭제할 속성과 관련된 제약조건이 존재하는 경우
→ RESTRICT : 속성 삭제가 수행되지 않음 / CASCADE : 관련된 제약 조건도 모두 삭제함
ex) ALTER TABLE 고객 DROP COLUMN 가입날짜;
3. 새로운 제약조건의 추가
ALTER TABLE 테이블_이름 ADD CONSTRAINT 제약조건_이름 제약조건_내용;
ex ) ALTER TABLE 고객 ADD CONSTRAINT CHK_AGE CHECK(나이 >= 20);
4. 기존 제약조건의 삭제
ALTER TABLE 테이블_이름 DROP CONSTRAINT 제약조건_이름;
※ 제약조건의 이름 없으면 DROP 못한다
테이블 삭제 : DROP TABLE 문
DROP TABLE 테이블_이름 [CASCADE | RESTRICT ];
삭제할 테이블을 참조하는 테이블이 있는 경우
→ RESTRICT : 테이블 삭제가 수행되지 않음 ( 삭제되지 않는 원인인 테이블을 해결하고 시도해야 함)
/ CASCADE : 관련된 테이블도 모두 삭제함
데이터 조작어 ( DML )
데이터 검색 : SELECT
1. 기본 검색
SELECT [ ALL | DISTINCT ] 속성_리스트
FROM 테이블_리스트;
SELECT : 검색하고 싶은 속성의 이름을 나열
- ALL ( 디폴트 ) : 결과 테이블이 튜플의 중복을 허용하도록 지정
- DISTINCT : 결과 테이블이 튜플의 중복을 허용하지 않도록 지정
FROM : 검색하고 싶은 속성이 있는 테이블의 이름을 나열
ex)
SELECT 제조업체
FROM 제품;
→ 제조업체가 중복됨
SELECT DISTINCT 제조업체
FROM 제품;
→ 제조업체 중복이 제거됨
ex2)
SELECT 제품명, 단가+500 AS "조정 단가"
FROM 제품;
※ AS는 결과 테이블에서 속성 이름을 변경하여 출력
2. 조건 검색
조건을 만족하는 데이터만 검색
SELECT [ALL | DISTINCT] 속성_리스트
FROM 테이블_리스트
[WHERE 조건];
WHERE : 비교 연산자와 논리 연산자를 이용한 검색 조건 제시
- 숫자뿐만 아니라 문자나 날짜 값을 비교하는 것도 가능
- 문자나 날짜 값은 작은 따옴표로 묶어서 표현
ex) '2022-01-14' < '2022-01-16'
- 비교 연산자 : =(같다) , <>(다르다), <, >, <=, >=
- 논리 연산자 : AND, OR, NOT
ex)
SELECT 제품명, 단가, 제조업체
FROM 제품
WHERE 단가>=2000 AND 단가<=3000;
3. LIKE를 이용한 검색
LIKE 키워드를 이용해 부분적으로 일치하는 데이터를 검색
문자열을 이용하는 조건에만 LIKE 키워드 사용 가능
- % : 0개 이상의 문자 ( 문자의 개수는 상관 X )
- - : 1개의 문자
- ex) LIKE '데이터%' : 데이터로 시작하는 문자열 ( 데이터로 시작하기만 하면 길이는 상관 없음 )
- ex2) LIKE '데이터_ _ _' : 데이터로 시작하는 6자 길이의 문자열
ex)
SELECT 고객아이디, 고객이름, 등급
FROM 고객
WHERE 고객아이디 LIKE '_ _ _ _ _';
4. NULL을 이용한 검색
IS NULL : 특정 속성의 값이 NULL 인지 비교
IS NOT NULL : 특정 속성의 값이 NULL 이 아닌지 비교
검색 조건에서 NULL 과 다른 값의 크기를 비교하면 모두 거짓이 됨
ex)
SELECT 고객이름
FROM 고객
WHERE 나이 IS NULL;
5. 정렬 검색
SELECT [ALL | DISTINCT ] 속성_리스트
FROM 테이블_리스트
[ WHERE 조건 ]
[ ORDER BY 속성_리스트 [ASC | DESC ] ];
ORDER BY : 결과 테이블 내용을 사용자가 원하는 순서로 출력
- 오름차순(디폴트) : ASC / 내림차순 : DESC
- NULL = 제일 큰 값 취급 → 오름차순에서는 맨 마지막, 내림차순에서는 맨 먼저 출력
- 정렬 기준이 되는 속성을 여러 개 적으면 맨 왼쪽부터 여러 기준에 따라 정렬 가능
ex)
SELECT 주문고객, 주문제품, 수량, 주문일자
FROM 주문
WHERE 수량>=10
ORDER BY 주문제품 ASC, 수량 DESC;
6. 집계 함수를 이용한 검색
특정 속성 값을 통계적으로 계산한 결과 검색 ( 개수, 합계, 평균, 최댓값, 최솟값의 계산 기능을 제공 )
집계 함수는 NULL 속성 값은 제외하고 계산함
SELECT 절이나 HAVING 절에서만 사용 가능 ( WHERE 절에서는 사용할 수 X )
집계함수
- COUNT : 속성 값의 개수
- MAX : 속성 값의 최댓값
- MIN : 속성 값의 최솟값
- SUM : 속성 값의 합계
- AVG : 속성 값의 평균
※ COUNT, MAX, MIN은 모든 데이터에서 사용가능하지만 SUM,AVG는 숫자 데이터에만 사용 가능하다
ex)
SELECT AVG(단가)
FROM 제품;
ex2)
SELECT COUNT(DISTINCT 제조업체)
FROM 제품;
→ DISTINCT 키워드를 이용해 중복을 없애고 서로 다른 제조업체의 수만 계산
ex3)
SELECT COUNT(고객아이디)
FROM 제품;
SELECT COUNT(*)
FROM 제품;
→ COUNT(기본키)는 COUNT(*)과 같다
7. 그룹별 검색
SELECT [ALL | DISTINCT ] 속성_리스트
FROM 테이블_리스트
[WHERE 조건]
[GROUP BY 속성_리스트 [HAVING 조건]]
[ORDER BY 속성_리스트 [ASC | DESC]];
GROUP BY : 특정 속성의 값이 같은 튜플을 모아 그룹을 만들고 그룹별로 검색 ( 그룹을 나누는 기준이 되는 속성 지정 )
HAVING : 그룹에 대한 조건 c.f) WHERE과 유사
ex)
SELECT 주문제품, SUM(수량)
FROM 주문
GROUP BY 주문제품;
→ GROUP BY 속성리스트는 반드시 SELECT 문에 포함되어야 한다!
ex2 )
SELECT 제조업체, COUNT(*), MAX(단가)
FROM 제품
GROUP BY 제조업체 HAVING COUNT(*)>=3;
ex3)
SELECT 주문제품, 주문고객, SUM(수량)
FROM 주문
GROUP BY 주문제품, 주문고객;
8. 조인 검색
여러 개의 테이블을 연결하여 데이터를 검색하는 것
- 조인속성 : 조인 검색을 위해 테이블을 연결해주는 속성
- 연결하려는 테이블 간에 도메인은 같아야 함 ( 이름은 달라도 된다! )
- 일반적으로 외래키( 다른 테이블에선 기본키)를 조인 속성으로 이용함
- FROM : 검색에 필요한 모든 테이블을 나열
- WHERE : 조인 속성의 값이 같아야 함을 의미하는 조인 조건 제시
- 같은 이름의 속성이 서로 다른 테이블에 존재할 수 있기 때문에 속성 이름 앞에 테이블의 이름도 표시 ex)주문.주문고객
ex)
SELECT 제품.제품명
FROM 제품, 주문
WHERE 주문.주문고객 = 'banana' AND 제품.제품번호 = 주문.주문제품;
→ 조인 연산 1번
ex2)
SELECT 제품.제품명
FROM 고객, 제품, 주문
WHERE 고객.고객이름 = '고명석' AND 고객.고객아이디=주문.주문고객 AND 제품.제품번호=주문.주문제품;
→ 조인 연산 2번 because 고객테이블과 제품테이블은 외래키가 없기 때문에 조인 불가
9. 부속 질의문을 이용한 검색
SELECT 문 안에 또 다른 SELECT 문을 포함하는 질의문
부속 질의문 먼저 수행하고 그 결과 이용해 상위 질의문 수행
- 상위 질의문 ( 주 질의문 ) : 다른 SELECT 문을 포함하는 SELECT
- 부속 질의문 ( 서브 질의문 ) : 다른 SELECT 문 안에 들어 있는 SELECT문
- 단일 행 부속 질의문 : 하나의 행을 결과로 반환 → 비교 연산자(=,<> ...) 사용 가능
- 다중 행 부속 질의문 : 둘 이상의 행을 결과로 반환
※ 괄호로 묶어서 작성
※ ORDER BY 절 사용 불가 because 출력하는 것이 아니라 결과만 다시 이용할 것이기 때문
ex) 단일 행 부속 질의문
다중 행 부속 질의문에 사용 가능한 연산자
- IN : 부속 질의문의 결과 값 중 일치하는 것이 있으면 참
- NOT IN : 부속 질의문의 결과 값 중 일치하는 것이 없으면 참
- EXISTS : 부속 질의문의 결과 값이 하나라도 존재하면 참
- NOT EXISTS : 부속 질의문의 결과 값이 하나도 존재하지 않으면 참
- ALL : 부속 질의문의 결과 값 모두와 비교한 결과가 참이면 참 ( 비교 연산자와 함께 사용 )
- ANY or SOME : 부속 질의문의 결과 값 중 하나라도 비교한 결과가 참이면 참 ( 비교 연산자와 함께 사용)
ex) 다중 행 부속 질의문
ex2) 다중 행 부속 질의문
SELECT 고객이름
FROM 고객
WHERE EXISTS ( SELECT *
FROM 주문
WHERE 주문일자='2020-01-14' AND 주문.주문고객 = 고객.고객아이디 );
→ 조인으로도 가능
ex3) 다중 행 부속 질의문
SELECT 제품명, 단가, 제조업체
FROM 제품
WHERE 단가 > ALL ( SELECT 단가
FROM 제품
WHERE 제조업체='대한식품' );
10. 질의 내용은 다양하게 표현가능하므로 선택해서 사용
ex ) 아래 세 가지는 같다
1. 조인 질의 이용
SELECT 제품.제품명, 제품.제조업체
FROM 제품, 주문
WHERE 제품.제품번호 = 주문.주문제품 AND 주문.주문고객 = 'banana';
2. EXISTS 사용한 부속질의문 이용
SELECT 제품명, 제조업체
FROM 제품
WHERE EXISTS ( SELECT *
FROM 주문
WHERE 제품.제품번호=주문.주문제품 AND 주문고객='banana');
3. IN 사용한 부속질의문 이용
SELECT 제품명, 제조업체
FROM 제품
WHERE 제품번호 IN ( SELECT 주문제품
FROM 주문
WHERE 주문고객='banana');
데이터 삽입 : INSERT
1. 데이터 직접 삽입
INSERT
INTO 테이블_이름 [ (속성_리스트) ]
VALUES (속성값_리스트)
INTO : 튜플 삽입할 테이블의 이름과 속성의 이름 나열 → 속성 리스트를 생략하면 테이블을 정의할 때 지정한 속성의 순서대로 값이 삽입된다
VALUES : 삽입할 속성 값들을 나열
INTO 절의 속성리스트와 VALUES 절의 속성값 리스트는 순서대로 일대일 대응되어야 함
ex) 위와 아래 질의문은 같다
INSERT
INTO 고객 (고객아이디, 고객이름, 나이, 등급, 직업, 적립금)
VALUES ('strawberry', '박땡땡', '30', 'vip', '공무원', 100 );
INSERT
INTO 고객
VALUES ('strawberry', '박땡땡', '30', 'vip', '공무원', 100 );
ex2)
INSERT
INTO 고객 (고객아이디, 고객이름, 나이, 등급, 적립금)
VALUES ('strawberry', '박땡땡', '30', 'vip', 100 );
INSERT
INTO 고객
VALUES ('strawberry', '박땡땡', '30', 'vip', NULL, 100 );
2. 부속 질의문을 이용한 데이터 삽입
SELECT 문을 이용해 다른 테이블에서 검색한 데이터 삽입
INSERT
INTO 테이블_이름[ (속성_리스트) ]
SELECT문;
ex)
INSERT
INTO 한빛제품 ( 제품명, 재고량, 단가)
SELECT 제품명, 재고량, 단가
FROM 제품
WHERE 제조업체 = '한빛제과';
데이터 수정 : UPDATE
테이블에 저장된 튜플에서 특정 속성의 값을 수정
UPDATE 테이블_이름
SET 속성_이름1 = 값1, 속성_이름2 = 값2, ...
[WHERE 조건];
SET : 속성 값을 어떻게 수정할 것인지 지정
WHERE 절에 제시된 조건을 만족하는 튜플에 대해서만 속성 값을 수정
→ WHERE 절을 생략하면 테이블에 존재하는 모든 튜플을 대상으로 수정
ex)
UPDATE 제품
SET 제품명='통근파이'
WHERE 제품번호='p03';
ex2)
UPDATE 주문
SET 수량=5
WHERE 주문고객 IN ( SELECT 고객아이디
FROM 고객
WHERE 고객이름='김땡땡' );
※ UPDATE나 DELETE 에서 테이블이 2개 이상 연관될때는 대상이 되는 테이블은 하나이므로 조인이 불가능하여 부속 질의문 써야한다
데이터 삭제 : DELETE
테이블에 저장된 데이터를 삭제
DELETE
FROM 테이블_이름
[WHERE 조건];
WHERE 절에 제시된 조건을 만족하는 튜플만 삭제
→ WHERE 절을 생략하면 테이블에 존재하는 모든 튜플을 삭제해 빈 테이블이 됨
ex)
DELETE
FROM 주문
WHERE 주문일자 = '2022-01-14';
ex 2)
DELETE
FROM 주문
WHERE 주문고객 IN ( SELECT 고객아이디
FROM 고객
WHERE 고객이름='김땡땡');
※ UPDATE나 DELETE 에서 테이블이 2개 이상 연관될때는 대상이 되는 테이블은 하나이므로 조인이 불가능하여 부속 질의문 써야한다
