SQL 첫걸음 5~6장

교재: SQL 첫걸음

5장. 집계와 서브쿼리

20강. 행 개수 구하기- COUNT

1. COUNT 함수는 인수로 주어진 집합의 개수를 구해 반환한다. 즉, 행 개수를 구할 수 있다.

+a) COUNT 함수 만이 인수로 *을 가질 수 있다.

WHERE구 사용 시 WHERE 구의 조건에 맞는 행의 개수 구할 수 있다.

2. 집계함수는 집합 안에 NULL값이 있을 경우 무시한다.

3. DISTINCT 함수로 중복값을 제거한다.

4. 중복을 제거한 뒤 개수 구하기: 집계함수의 인수로 DISTINCT을 사용한 수식을 지정

21강. COUNT 이외의 집계함수

1. SUM으로 합계 구하기

SUM 집계함수에 지정되는 집합은 수치형 뿐, NULL값은 무시

2. AVG로 평균내기

평균값을 구하기 위해선 아래 두 식을 사용할 수 있다.

AVG(quantity)
SUM(quantity)/COUNT(quantity)

NULL을 0로 간주해서 평균을 내고 싶다면 CASE를 사용해 변환 후 AVG 함수로 계산한다

SELECT AVG(CASE WHEN quantity IS NULL THEN 0 ELSE quantity END) AS avgnull0 FROM sample51;

3. MIX, MAX로 최솟값, 최댓값 구하기

문자열형, 날짜시간형에도 사용가능, NULL값 무시

22강. 그룹화 - GROUP BY

1. GROUP BY로 그룹화하기

지정된 열의 값이 같은 행이 하나의 그룹으로 묶인다.

그룹화하면 DISTINCT와 같이 중복 제거 효과가 있다.

2. HAVING 구로 조건 지정

WHERE 구에서는 집계함수 사용 불가능 -> HAVING 구로 조건식 지정

3. 복수열의 그룹화

GROUP BY에서 지정한 열 이외의 열은 집계함수를 사용하지 않은 채 SELECT에 지정할 수 없다.

SELECT no, name, quantity FROM sample51 GROUP BY name; -- ERROR, no와 quatity 지정 불가
SELECT MIN(no), name, SUM(quantity) FROM sample51 GROUP BY name; -- 실행됨

4. 결괏값 정렬 - ORDER BY

23장. 서브쿼리

서브쿼리: SELECT 명령에 의한 데이터 질의로, 상부가 아닌 하부의 부수적인 질의

1. DELETE의 WHERE 구에서서브쿼리 사용하기

최솟값을 가지는 행 삭제하기

DELETE FROM sample54 WHERE a=(SELECT MIN(a) FROM sample54);

하지만, MySQL에서는 에러가 난다.

2. 스칼라 값

하나의 값 반환

SELECT MIN(a) FROM sample54;

복수의 행 반환, 열 하나

SELECT no FROM sample54;

하나의 행 반환, 열 복수

SELECT MINA(a), MAX(no) FROM sample54;

복수의 행, 복수의 열 반환

SELECT no, a FROM sample54;

=연산자를 사용하여 비교한 경우 스칼라 값끼리 비교할 필요가 있다.

DELETE FROM sample54 WHERE a=(SELECT MIN(a) FROM sample54);

3. SELECT 구에서 서브쿼리 사용하기

SELECT
(SELECT COUNT(*) FROM sample51) AS sq1,
(SELECT COUNT(*) FROM sample54) AS sq2;

4. SET 구에서 서브쿼리 사용하기

UPDATE sample54 SET a=(SELECT MAX(a) FROM sample54);

5. FROM 구에서 서브쿼리 사용하기

SELECT * FROM (SELECT * FROM sample54) sq;
SELECT * FROM (SELECT * FROM sample54) AS sq; -- AS로 지정

6. INSERT 명령과 서브쿼리

VALUES 구의 일부를 서브쿼리를 사용하는 경우와 VALUES 구 대신 SELECT명령을 사용하는 경우가 있다.

-- VALUES 구에서 서브쿼리 사용하기
INSERT INTO sample541 VALUES (
(SELECT COUNT(*) FROM sample51),
SELECT COUNT(*) FROM samole54)
);

SELECT * FROM sample541;

-- VALUES 구 대신에 SELECT 명령 사용하기
INSERT INTO sample541 SELECT 1, 2;
SELECT * FROM sample541;

테이블 행 복사하기

INSERT INTO sample542 SELECT * FROM sample543;

24강. 상관 서브쿼리

1. EXISTS

: 데이터가 존재하는지 아닌지 판별

2. NOT EXISTS

'없음'의 경우, 행이 존재하지 않는 상태가 참이 되므로 NOT EXISTS 사용

3. 상관 서브쿼리

: 부모 명령과 자식인 서브쿼리가 특정 관계를 맺는 것

UPDATE sample551 SET a ='있음' WHERE --UPDATE: 부모 명령
EXISTS (SELECT * FROM sample552 WHERE no_2 = no); --( ) 괄호 안: 서브쿼리(자식)

DELETE의 경우 상관 서브쿼리가 아니다.

상관 서브쿼리는 부모 명령과 연관되어 처리되어서 서브쿼리 부분만 따도 떼어내어 실행시킬 수 없다.

열에 테이블명 붙이기

'테이블명.' + '열명'

sample552.no_2=sample551.no

4. IN

: 집합 안에 값이 존재하는지 조사

열명 IN (집합)

IN에서는 NULL값이 있어도 무시하지 않고, IS NULL을 사용해 NULL을 비교한다.

NOT IN의 경우, 집한 안에 NULL값이 있으면 반환하지 않는다.

6장. 데이터베이스 객체 작성과 삭제

25강. 데이터베이스 객체

1. 데이터베이스 객체

: 테이블, 뷰, 인덱스 등 데이터베이스 내에 정의하는 모든 것(실체를 가짐)

명명규칙

기존 이름이나 예약어와 중복x
숫자로 시작하지 않음
언더스코어(_) 이외의 기호 사용 불가능
한글 사용할때 더블쿼트(MySQL에서는 백쿼트)로 둘러쌈
시스템이 허용하는 길이 초과하지 않는다

2. 스키마

: 데이터베이스의 구조를 설계하는것

ex)테이블은 열 정의, 스키마는 테이블 정의 가능

26강. 테이블 작성, 삭제, 변경

DML: 데이터 조작 명령; SELECT, INSERT, DELETE, UPDATE

DDL: 데이터 정의 명령; 스키마 내 객체 관리; CREATE, ALTER, DROP

1. 테이블 작성 - CREATE TABLE 명령

자료형은 INTEGER, VARCHAR 등, 문자열형 지정할 때는 최대길이를 괄호로 묶음

NOT NULL 제외하고는 NULL 허용

CREATE TABLE sample62 (
no INTEGER NOT NULL,
a VARCHAR(30),
b DATE);

DESC sample62;

2. 테이블 삭제 - DROP TABLE 명령

DROP TABLE 테이블명;

-데이터행 삭제

:테이블 정의는 그대로 둔 채 데이터만 삭제

TRUNCATE TABLE 테이블명;

3. 테이블 변경 - ALTER TABLE 명령

열추가, 변경, 삭제
제약 추가, 삭제

-열 추가; ADD

ALTER TABLE ADD 열 정의

+a) NOT NULL 제약 걸린 열 추가할 때 기본값 지정해야함

-열 속성 변경; MODIFY

ALTER TABLE 테이블명 MODIFY 열 정의

-열 이름 변경; CHANGE

ALTER TABLE 테이블명 CHANGE [기존 열 이름][신규 열 정의]

-열 삭제; DROP

ALTER TABLE 테이블명 DROP 열명

4. ALTER TABLE로 테이블 관리

-최대길이 연장: MODIFY 사용

-열 추가: ADD 사용

27강. 제약

1. 제약 정의

제약: 테이블에 설정하는 것

열 제약: 하나의 열에 대해 설정하는 제약; NOT NULL 제약, UNIQUE 제약 등

테이블 제약: 한 개의 제약으로 복수의 열에 제약을 설명하는 경우; 기본키 제약

CONSTRAINT 키워드: 테이블 제약에 이름 붙일 때 사용

CONSTRAINT pkey_sample PRIMARY KEY(no, sub_no);
-- pkey_sample 이라는 이름을 붙임

2. 제약 추가

-열 제약 추가

ALTER TABLE로 열 정의 변경

ALTER TABLE sample631 MODIFY c VARCHAR(30) NOT NULL;
-- c열에 NOT NULL 제약 걸기

-테이블 제약 추가

ALTER TABLE의 ADD 하부명령으로 추가가능

ALTER TABLE sample631 ADD CONSTRAINT pkey_sample631 PRIMARY KEY(a);
-- 기본키 제약 추가하기

3. 제약 삭제하기

- 열 제약 삭제

ALTER TABLE sample631 MODIFY c VARCHAR(30);
-- c열에 NOT NULL 제약 없애기

-테이블 제약 삭제; ALTER TABLE의 DROP 하부명령

삭제 시 제약명 지정

ALTER TABLE sample631 DROP CONSTRAINT pkey_sample631;
-- pkey_sample631 제약 삭제하기

-테이블 제약 삭제

삭제 시 제약명 지정안해도 됨

ALTER TABLE sample631 DROP PRIMARY KEY;

4. 기본키

기본키 제약이 설정된 열에는 중복된 값을 저장할 수 없다.

CREATE TABLE sample634(
p INTEGER NOT NULL,
A VARCHAR(30)
CONSTRAINT pkey_sample634 PRIMARY KEY(p)
);

+a) 기본키로 지정할 열은 NOT NULL 제약이 설정되어 있어야 한다.

예를 들어, 열 p가 sample634 테이블의 기본키일 때,

중복하는 행 추가 불가(INSERT INTO~), 중복된 값으로 갱신(UPDATE~)이 불가하다.

-복수의 열로 기본키 구성하기

예를 들어, a열에 중복값이 있더라고 b열이 다르면 키 전체로서 중복하지 않는다.

28강. 인덱스 구조

인덱스는 데이터베이스 객체 중 하나

1. 인덱스

: 테이블에 붙여진 색인으로 검색속도를 향상시킨다

: 검색 시 쓰이는 키워드와 대응하는 데이터 행의 장소가 저장되어 있다.

2. 검색 알고리즘

- 풀 테이블 스캔(full table scan)

:인덱스가 지정되지 않은 테이블 검색할 때 쓰는 방법으로.

테이블에 저장된 모든 값을 처음부터 차례로 조사해나가는 것

-이진 탐색(binary search)

: 차례로 나열된 집합에 대해 유효한 검색 방법으로 대량 데이터 검색할때 빠르다,

집합을 반으로 나누어 조사하는 검색방법

-이진 트리(binary tree)

노드(node)로 구성되며, 각 노드는 두 개의 가지로 나뉨(왼쪽 가지는 작은 값, 오른쪽 가지는 큰 값을 가짐)

유일성: 이진 트리에는 중복하는 값을 등록할 수 없다.

29강. 인덱스 작성과 삭제

1. 인덱스 작성 -CREATE INDEX 명령

CREATE INDEX 인덱스명 ON 테이블명 (열명1, 열명2, ...)

2. 인덱스 삭제 - DROP INDEX 명령

DROP INDEX 인덱스명
-- 스키마 객체의 경우

DROP INDEX 인덱스명 ON 테이블명
-- 테이블 내 객체의 경우

3. EXPLAIN 명령

:실제로 인덱스를 사용해 검색하는지 확인

EXPLAIN SQL명령;

4. 최적화

SELECT 명령을 실행할 때 인덱스의 사용 여부를 선택하는데, 이는 데이터베이스 내부의 최적화에 의해 처리한다.

실행 계획은 인덱스의 품질을 고려해 세운다.

EXPLAIN 명령은 실행계획을 확인하는 명령이다

30강. 뷰 작성과 삭제

1. 뷰 (가상 테이블)

:데이터베이스 객체가 아닌 SELECT 명령을, 객체로서 이름을 붙여 관리할 수 있도록 한 것

테이블처럼 데이터 쓰고 지울 수 있는 저장공간 가지지 않는다 -> SELECT 명령에서만 사용하는 것을 권장

FROM 구에는 서브쿼리를 사용할 수 있다.

뷰를 이용해 복잡한 SELECT 명령을 간략히 표현할 수 있다.

SELECT * FROM (SELECT * FROM sample54) sq; -- SELECT 명령

SELECT * FROM sample_view_67; 
-- 뷰 정의, 뷰의 이름: sample_view_67

2. 뷰 작성과 삭제

-뷰의 작성; CREATE VIEW

CREATE VIEW 뷰명 AS SELECT명령;
CREATE VIEW sample_view_67 AS SELECT * FROM sample54; --예시

AS 생략 불가

CREATE VIEW 뷰명 (열명1, 열명2, ... ) AS SELECT 명령

열 지정시 괄호로 묶어 나열

-뷰의 삭제; DROP VIEW

DROP VIEW 뷰명;
DROP VIEW sample_view_67; -- 예시

3.뷰의 약점

뷰는 저장공간 소비하지 않는 대신 CPU 자원 사용

뷰	뷰의 약점	극복
Materialized View	테이블에 보관하는 데이터양이 많은 경우 집계처리 할때, 뷰를 중첩해서 사용할 때 처리속도가 떨어진다	테이블처럼 데이터를 저장장치에 저장해두고 사용 매번 SELECT 명령 실행할 필요없다.(변경된 경우 재실행)
함수 테이블	SELECT 명령은 단독으로도 실행할 수 있어야 한다. 부모 쿼리와 연관된 서브쿼리의 경우, 뷰의 SELECT 명령으로 사용할 수 없다	테이블을 결괏값으로 반환하는 사용자정의 함수 함수에는 인수 지정 가능하기 때문에 인수의 값에 따라 WHERE 조건 붙여 결괏값 변경 가능 -> 상관 서브쿼리처럼 동작

'SQL' 카테고리의 다른 글

SQL 첫걸음 3장~4장 (0)	2025.03.30
SQL 첫걸음 1장~2장 (0)	2025.03.22

내 블로그 - 관리자 홈 전환	`Q` `Q`
새 글 쓰기	`W` `W`

글 수정 (권한 있는 경우)	`E` `E`
댓글 영역으로 이동	`C` `C`

이 페이지의 URL 복사	`S` `S`
맨 위로 이동	`T` `T`
티스토리 홈 이동	`H` `H`
단축키 안내	`Shift` + `/` `⇧` + `/`

SQL 첫걸음 5~6장

'SQL' 카테고리의 다른 글

티스토리툴바

단축키

내 블로그

블로그 게시글

모든 영역

SQL 첫걸음 5~6장

'SQL' 카테고리의 다른 글

'SQL' Related Articles

티스토리툴바

단축키

내 블로그

블로그 게시글

모든 영역