Preface
이번 장에선 SQL의 기본적인 명령어를 공부했다.
SQL도 언어의 한 종류라 그런지 파이썬을 공부할 때처럼 재미있게 공부할 수 있었지만, 양이 너무 많아 공부할 때도, 블로그를 작성할 때도 시간이 정말 오래 걸렸다.
책에 나와 있는 예제를 실제로 작성해보며 공부를 할까 생각도 해봤지만, 책에선 내가 공부하고싶은 My SQL이 아닌, ORACLE로 예시를 들어 설명하고 있어 이번 책을 마친 후 실습 책을 시작할 때 본격적으로 코드를 작성해보기로 했다.
1. SQL의 소개
- SQL: 관계 데이터베이스를 위한 표준 질의어로 많이 사용되는 비절차적 언어
1) 데이터 정의어
2) 데이터 조작어
3) 데이터 제어어
- SQL은 DBMS에 직접 접근하여 대화식으로 질의를 작성할 수도 있지만, 프로그래밍 언어에 삽입하여 사용할 수도 있다.
2. SQL을 이용한 데이터 정의
- 데이터 정의 기능
2-1. 테이블 생성: CREATE TABLE
- CREATE TABLE 문의 기본 형식
- 모든 SQL 문은 세미콜론(;)으로 문장 끝을 표시한다.
- CREATE TABLE, NOT NULL과 같은 키워드는 대소문자를 구분하지 않는다.
- 각 속성의 데이터 타입을 선택한 후에는 속성의 널 값 허용 여부와 기본 값 필요 여부를 결정해야 한다.
1) 기본적으로 널 값이 허용된다.
2) 널 값을 허용하지 않으려면 속성을 정의할 때 속성의 이름과 데이터 타입 앞에 NOT NULL 키워드를 포함해야 한다.
- 속성의 데이터 타입
1) INT 또는 INTERGER: 정수
2) SMALLINT: INT보다 작은 정수
3) CHAR(n) 또는 CHARACTER(n): 길이가 n인 고정 길이의 문자열
4) VARCHAR(n) 또는 CHARACTER VARYING(n): 최대 길이가 n인 가변 길이의 문자열
5) NUMERIC(p, s) 또는 DECIMAL(p, s): 고정 소수점 실수
6) FLOAT(n): 길이가 n인 부동 소수점 실수
7) REAL: 부동 소수점 실수
8) DATE: 연, 월, 일로 표현되는 날짜
9) TIME: 시, 분, 초로 표현되는 시간
10) DATETIME: 날짜와 시간
- 속성에 기본 값을 지정해두지 않으면 널 값이 기본으로 저장된다.
→ DEFAULT 키워드를 사용해 기본 값을 명확히 지정해두면 이 기본 값이 저장된다.
※ DEFAULT 키워드로 값을 지정할 때 숫자 데이터는 그대로 표현하지만, 문자열이나 날짜 데이터는 작은따옴표로 묶어주어야 하며 문자열은 대소문자를 구분한다.
- 기본키는 PRIMARY KEY 키워드를 사용해 지정한다.
→ 모든 테이블에 하나만 지정할 수 있다.
- 대체키는 UNIQUE 키워드를 사용해 지정한다.
→ 널 값을 가질 수 있으며, 한 테이블에서 여러 개 지정할 수 있다.
- 외래키는 FOREIGN KEY 키워드를 사용해 지정한다.
→ 어떤 테이블의 무슨 속성을 참조하는지 REFERENCES 키워드 다음에 명확히 제시해야 한다.
- 참조되는 테이블에서 튜플을 삭제할 경우
1) ON DELETE NO ACTION: 튜플을 삭제하지 못하게 한다. (디폴트)
2) ON DELETE CASCADE: 관련 튜플을 함께 삭제한다.
3) ON DELETE SET NULL: 관련 튜플의 외래키 값을 NULL로 변경한다.
4) ON DELETE SET DEFAULT: 관련 튜플의 외래키 값을 미리 지정한 기본 값으로 변경한다.
- 참조되는 테이블에서 튜플을 변경할 경우
1) ON UPDATE NO ACTION: 튜플을 변경하지 못하도록 한다. (디폴트)
2) ON UPDATE CASCADE: 관련 튜플에서 외래키 값을 함께 변경한다.
3) ON UPDATE SET NULL: 관련 튜플의 외래키 값을 NULL로 변경한다.
4) ON UPDATE SET DEFAULT: 관련 튜플의 외래키 값을 미리 지정한 기본 값으로 변경한다.
cf. ALTER TABLE 문은 속성과 제약조건 자체를 추가, 삭제할 때 사용하지만, ON~ 문은 튜플과 관련있다.
ex) FOREIGN KEY(소속부서) REFERENCES 부서(부서번호)
ON DELETE CASCADE ON UPDATE CASCADE
- 테이블을 생성할 때 CHECK 키워드를 사용해 특정 속성에 대한 제약조건을 지정할 수 있다.
→ ex) CHECK(재고량 >= 0 AND 재고량 <= 10000)
- CHECK 키워드를 사용해 지정한 제약조건에 CONSTRAINT 키워드와 함께 고유 이름을 부여할 수 있다.
→ ex) CONSTRAINT CHK_CPY CHECK(제조업체 = '한빛제과')
2-2. 테이블 변경: ALTER TABLE
- 테이블에 새로운 속성을 추가하는 기본 형식
- 테이블의 기존 속성을 삭제하는 기본 형식
ALTER TABLE 테이블_이름 DROP COUMN 속성_이름;
- 테이블에 새로운 제약조건을 추가하는 기본 형식
ALTER TABLE 테이블_이름 ADD CONSTRAINT 제약조건_이름 제약조건_내용;
- 테이블의 기존 제약조건을 삭제하는 기본 형식
ALTER TABLE 테이블_이름 DROP CONSTRAINT 제약조건_이름;
2- 3. 테이블 삭제: DROP TABLE
- 생성한 테이블을 삭제하는 기본 형식
3. SQL을 이용한 데이터 조작
- 데이터 조작 기능
3-1. 데이터 검색: SELECT
- SELECT 문은 테이블을 대상으로 하고 수행 결과도 테이블이다.
- 기본 검색을 위한 기본 형식
- 테이블에 존재하는 모든 속성을 검색하기 위해 '*'를 사용할 수 있다.
→ 결과 테이블의 속성 순서가 원본 테이블이 정의한 속성 순서와 같다.
- SELECT 문의 수행 결과로 반환되는 결과 테이블에서는 동일한 튜플이 중복될 수 있다.
1) ALL 키워드를 사용해 중복의 허용을 명시적으로 사용할 수 있다.
2) DISTINCT 키워드를 사용해 결과 테이블에서 튜플의 중복을 제거하고 한 번씩만 출력할 수 있다.
- AS 키워드를 사용해 결과 테이블에 출력되는 속성의 이름을 다른 이름으로 바꾸어 출력할 수 있다.
1) 실제 테이블의 속성 이름이 바뀌는 것은 아니다.
2) 이름에 공백이 포함되어 있으면 오라클에선 큰따옴표로, MS SQL 서버에선 큰따옴표나 작은 따옴표로 묶어주어야 한다.
3) AS 키워드는 생략할 수 있다.
- SELECT 키워드와 함께 산술식(+, -, *, / 등)을 제시할 수 있다.
- 조건 검색을 위한 기본 형식
SELECT [ALL / DISTINCT] 속성_리스트
FROM 테이블_리스트
[WHERE 조건];→ 조건에서 숫자 값은 그대로 작성해도 되지만, 문자나 날짜 값은 작은 따옴표로 묶어야 한다.
※ 비교 연산자의 < > 은 '다름'을 의미한다. (파이썬에서의 != 와 동일)
- 검색 조건을 일부만 알고 있다면 LIKE 키워드를 이용해 검색할 수 있다.
→ 문자열을 이용하는 조건에만 사용할 수 있다.
1) %: 0개 이상의 문자
2) _: 1개의 문자
ex) LIKE '데이터%': '데이터'로 시작하는 문자열
LIKE '%데이터': '데이터'로 끝나는 문자열
LIKE '데이터___': '데이터'로 시작하는 6자 길이의 문자열
LIKE '__한%': 세 번째 글짜가 '한'인 문자열
- NULL을 이용한 검색
1) IS NULL 키워드: 검색 조건에서 특정 속성의 값이 널 값인지를 비교
2) IS NOT NULL 키워드: 특정 속성의 값이 널 값이 아닌지를 비교
→ 널 값은 다른 값과 크기를 비교하면 결과가 모두 거짓이 된다.
- 정렬 검색을 위한 기본 형식
SELECT [ALL / DISTINCT] 속성_리스트
FROM 테이블_리스트
[WHERE 조건]
[ORDER BY 속성_리스트 [ASC / DESC];→ 결과 테이블의 내용을 사용자가 원하는 순서로 출력하려면 ORDER BY 키워드를 사용한다.
1) ASC: 오름차순 정렬 (디폴트)
2) DESC: 내림차순 정렬
- 특정 속성 값을 통계적으로 계산한 결과를 검색하기 위해 집계 함수를 이용할 수 있다.
| 함수 | 의미 | 사용 가능한 속성의 타입 |
| COUNT | 속성 값의 개수 | 모든 데이터 |
| MAX | 속성 값의 최댓값 | |
| MIN | 속성 값의 최솟값 | |
| SUM | 속성 값의 합계 | 숫자 데이터 |
| AVG | 속성 값의 평균 |
→ 집계 함수 사용의 주의사항
1) 널인 속성 값은 제외하고 계산한다.
2) WHERE 절에서는 사용할 수 없고, SELECT 절이나 HAVING 절에서만 사용할 수 있다.
※ COUNT(*)의 결과는 고객 테이블에 있는 모든 튜플의 개수이다.
- 그룹별 검색을 위한 기본 형식
SELECT [ALL / DISCINCT] 속성_리스트
FROM 테이블 _리스트
[WHERE 조건]
[GROUP BY 속성_리스트 [HAVING 조건]]
[ORDER BY 속성_리스트 [ASC / DESC]];→ 테이블에서 특정 속성의 값이 같은 튜플을 모아 그룹을 만들고 그룹별로 검색을 하기 위해 GROUP BY 키워드를 사용한다.
→ 그룹에 대한 조건을 추가하려면 GROUP BY 키워드를 HAVING 키워드와 함께 사용하면 된다.
- 그룹별로 검색할 때는 그룹을 나누는 기준이 되는 속성을 SELECT 절에도 작성하는 것이 좋다.
→ 작성하지 않으면 어떤 그룹에 대한 검색 결과인지 확인이 어렵다.
- 그룹별로 검색할 때는 집계 함수나 GROUP BY 절에 있는 속성 외의 속성은 SELECT 절에 사용할 수 없다.
- 조인 검색: 여러 개의 테이블을 연결하여 데이터를 검색하는 것
1) 조인 속성: 테이블을 연결해주는 속성
→ 보통 외래키를 조인 속성으로 이용
- 조인 검색을 위한 SQL 문
1) FROM 절에 검색에 필요한 모든 테이블을 나열하고, WHERE 절에는 조인 속성의 값이 같아야 함을 의미하는 조인 조건을 제시한다.
→ 속성의 이름 앞에 해당 속성이 소속된 테이블 이름을 표시해주는 것이 좋다.
2) 테이블의 이름을 대신하는 별명을 사용할 수 있다.
→ FROM 절에 테이블의 이름과 별명을 함께 제시하면 된다. (AS 키워드는 생략 가능)
- 부속 질의문을 이용한 검색
1) 상위(주) 질의문: 다른 SELECT 문을 포함하는 SELECT 문
2) 부속(서브) 질의문: 다른 SELECT 문 안에 들어 있는 SELECT 문
→ 괄호로 묶어 작성하고 ORDER BY 절을 사용할 수 없으며, 상위 질의문보다 먼저 수행된다.
① 단일 행 부속 질의문: 하나의 행을 결과로 반환
→ 일반 비교 연산자 사용 가능
② 다중 행 부속 질의문: 하나 이상의 행을 결과로 반환
→ 일반 비교 연산자 사용 불가
- 다중 행 부속 질의문에 사용 가능한 연산자
| 연산자 | 설명 |
| IN | 부속 질의문의 결과 값 중 일치하는 것이 있으면 참 |
| NOT IN | 부속 질의문의 결과 값 중 일치하는 것이 없으면 참 |
| EXISTS | 부속 질의문의 결과 값이 하나라도 존재하면 참 |
| NOT EXISTS | 부속 질의문의 결과 값이 하나도 존재하지 않으면 참 |
| ALL | 부속 질의문의 결과 값 모두와 비교한 결과가 참이면 참 |
| ANY 또는 SOME | 부속 질의문의 결과 값 중 하나라도 비교한 결과가 참이면 참 |
3-2. 데이터 삽입: INSERT
- 데이터 삽입
1) 테이블에 튜플을 직접 삽입
2) 부속 질의문을 이용해 튜플을 삽입
- 테이블에 튜플을 직접 삽입하는 기본 형식
- INTO 절
1) 속성의 나열 순서대로 VALUES 키워드 다음의 속성 값들이 차례로 삽입된다.
2) 속성 이름의 리스트를 생략할 시, 테이블을 정의할 때 지정한 속성의 순서대로 속성 값이 삽입된다.
3) 특정 튜플의 값을 입력하지 않으면 널 값이 삽입된다.
- 부속 질의문을 이용해 데이터를 삽입하는 기본 형식
INSERT
INTO 테이블_이름[(속성_리스트)]
SELECT 문;
3-3. 데이터 수정: UPDATE
- UPDATE 문의 기본 형식
3-4. 데이터 삭제: DELETE
- DELETE 문의 기본 형식
- WHERE 절을 생략하면 주문 테이블이 비게 되지만, DROP TABLE 문으로 테이블 자체를 제거하는 것과는 다르다.
4. 뷰
- 뷰: 다른 테이블을 기반으로 만들어진 가상 테이블
- 기본 테이블: 뷰를 만드는 데 기반이 되는 물리적인 테이블
→ 다른 뷰를 기반으로 새로윤 뷰를 만들 수도 있다.
- CREATE VIEW: 뷰를 생성하기 위한 명령어
- AS 뒤의 SELECT 문은 ORDER BY를 사용할 수 없다.
- 뷰를 구성하는 속성의 이름 리스트를 생략하면 SELECT 절에 나열된 속성의 이름을 그대로 사용한다.
- WITH CHECK OPTION: 생성한 뷰에 삽입이나 수정 연산을 할 때 SELECT 문에서 제시한 뷰의 정의 조건을 위반하면 수행되지 않도록 하는 제약조건
- 검색 연산은 모든 뷰에 수행할 수 있지만, 삽입, 수정, 삭제 연산은 허용되지 않는 뷰가 있다.
1) 기본 테이블의 기본키를 구성하는 속성이 포함되어 있지 않은 뷰
2) 기본 테이블에 있던 내용이 아니라 집계 함수로 새로 계산된 내용을 포함하고 있는 뷰
3) DISTINCT 키워드를 포함하여 정의한 뷰
4) GROUP BY 키워드를 포함하여 정의한 뷰
5) 여러 개의 테이블을 조인하여 정의한 뷰
- 뷰의 장점
1) 질의문을 좀 더 쉽게 작성할 수 있다.
2) 데이터의 보안 유지에 도움이 된다.
3) 데이터를 좀 더 편리하게 관리할 수 있다.
- DROP VIEW: 뷰를 삭제하기 위한 명령어
DROP VIEW 뷰_이름;
5. 삽입 SQL
- 삽입 SQL: 프로그래밍 언어로 작성된 응용 프로그램 안에 삽입하여 사용하는 SQL 문
1) 프로그램 안에서 일반 명령문이 위치할 수 있는 곳이면 어디는 삽입할 수 있다.
2) 프로그램 안의 일반 명령문과 구분하기 위해 앞에 EXEC SQL을 붙인다.
3) 프로그램에 선언된 일반 변수를 사용할 땐 앞에 콜론(:)을 붙여 사용한다.
- 커서: 수행 결과로 반환된 여러 행을 한 번에 하나씩 가리키는 포인터
1) 커서가 필요한 SQL: 여러 개의 행을 반환하는 SELECT 문
2) 커서가 필요 없는 SQL: CREATE TABLE 문, INSERT 문, UPDATE 문, 행 하나만 반환하는 SELECT 문
- BEGIN/END DECLARE SECTION: 삽입 SQL 문에서 사용할 변수 선언의 시작과 끝을 알려주는 명령어
- DECLARE 명령어를 이용해 커서를 선언하는 삽입 SQL 문의 기본 형식
EXEC SQL DECLARE 커서_이름 CURSOR FOR SELECT 문;
ex) EXEC SQL DECLARE product_cursor CURSOR FOR SELECT 제품명, 단가 FROM 제품;
- 커서를 선언한 후 SELECT 문을 실행하는 명령
EXEC SQL OPEN 커서_이름;
- FETCH: 검색된 행들을 차례로 처리하기 위해 커서를 이동시키는 명령어
EXEC SQL FETCH 커서_이름 INTO 변수_리스트;
- 커서를 종료하는 명령어
EXEC SQL CLOSE 커서_이름;
댓글