5장 집계와 서브쿼리
COUNT
행 개수 구하기
- COUNT(집합)
NULL값 다루기
- NULL이 있을 경우 제외하고 처리
DISTINCT
데이터가 서로 중복되지 않는 경우 -> '유일한 값을 가진다'
중복 제거하기
- DISTINCT 함수로 중복 제거
- ALL과 DISTINCT 중 어느것도 지정하지 않은 경우 중복 제거 X
집계함수에서 DISTINCT
중복을 제거한 뒤 개수 구하기
- WHERE구 X -> DISTINCT를 인수로 사용
COUNT 외의 집계함수
- SUM, AVG, MIN, MAX
SUM
- sum으로 quantity열의 합계 구하기
- 마찬가지로 null값은 무시한다
AVG
-
SUM/COUMT -> AVG
-
수치형만 가능
-
AVG로 평균값 구하기
-
NULL을 0으로 반환하여 평균값 계산하기
MIN/MAX
- 최솟값 최댓값 구하기
그룹화 - GROUP BY
그룹화를 통해 집계함수의 활용범위를 넓힐 수 있다
GROUP BY로 그룹화
-
지정된 열의 값이 같은 행이 하나의 그룹으로 묶인다
-
name 열로 그룹화하기
DISTINCT와 같이 중복을 제거하는 효과가 있다
-
name열을 그룹화하여 계산하기
GROUP BY구와 집계함수를 조합
HAVING 구로 조건 지정
집계함수는 where구의 조건식에서 사용X
-> where구의 검색 처리가 그룹화보다 앞순서이기 때문!!
-> HAVING을 사용하자
-
HAVING 구는 GROUP bY구 뒤에 기술하며 where구와 동일하게 조건식 지정이 가능하다
-
HAVING구로 걸러내어 검색하기
-
내부처리 순서
SELECT구보다 먼저 처리되는 구에서는 별명 사용 불가
-
아래의 명령은 실행이 불가능하다
복수열의 그룹화
그룹화에 지정한 열 외의 열은 집계함수를 사용하지 않고 select구에 지정불가
- select구에 기술할 수 없는 열
- 이 때 집계함수를 사용하면 집합은 하나의 값으로 계산되므로, 그룹마다 하나의 행 출력이 가능
아래의 명령어는 실행 가능
결과값 정렬
- ORDER BY로 집계한 결과 정렬하기
name 열로 그룹화하여 합계를 구하고 내림차순으로 정렬
서브쿼리
- 서브쿼리는 select 명령에 의한 데이터 질의로 상부가 아닌 하부의 부수적 질의이다
- 하부 select 명령으로 괄호를 묶어 지정한다
DELETE의 WHERE구에서 서브쿼리 사용하기
- 최솟값을 가지는 행 삭제하기
- 괄호 먼저 실행 -> DELETE 명령 실행
- Mysql에서는 아래의 쿼리 실행 불가 (DELETE대신 select로 바꾸면 실행 가능)
스칼라 값
서브쿼리를 사용할 때는 select명령이 어떤 값을 반환하는지 주의해야한다
- 서브쿼리의 패턴
- 하나의 값을 반환하는 패턴
SELECT MIN(a) FROM sample54;- 복수의 행이 반환되지만 열은 하나인 패턴
SELECT no FROM sample41; - 하나의 행이 반환되지만 열이 복수인 패턴
SELECT MIN(a), MAX(no) from sample54; - 복수의 행, 복수의 열이 반환되는 패턴
SELECT no, a from sample54;
- 복수의 행이 반환되지만 열은 하나인 패턴
SELECT 명령이 하나의 값만 반환하는 것을 '스칼라 값을 반환한다'라고 한다!
- 스칼라 값을 반환하는 select 명령은 서브쿼리로서 사용하기 쉽다
- = 연산자를 사용하여 비교할 경우 스칼라 값끼리 비교하자
DELETE FROM sample54 WHERE a = (SELECT MIN(a) FROM sample51);여기에서 서브쿼리 부분은 스칼라 값을 반환하는 select명령으로 되어 있으므로 = 연산자를 사용해 열 a의 값과 비교가 가능하다!SELECT 구에서 서브쿼리 사용하기
SET 구에서 서브쿼리 사용하기
- set구에서 서브쿼리를 사용할 시에도 스칼라 값을 반환하도록 서브쿼리 지정하기
UPDATE sample54 SET a = (select MAX(a) from sample54);FROM 구에서 서브쿼리 사용하기
- select, set구와 달리 from구에서는 스칼라값으로 서브쿼리 지정하지 않아도 OK
select 명령 안에 select 명령이 들어있는 구조 -> 네스티드 구조/중첩구조/내포구조 - FROM 구에서 서브쿼리 사용하기(AS로 지정)
- 3단계로 사용도 가능
INSERT 명령과 서브쿼리
- VALUES 구에서 서브쿼리 사용하기 -> 스칼라값으로 지정
- select 결과를 INSERT하기
- 열 구성이 똑같은 테이블 사이에서 테이블 행 복사하기
상관 서브쿼리
- EXISTS (SELECT 명령)
- EXIST는 반환된 행이 있는지를 확인하고 있으면 참, 없으면 거짓 -> 패턴 상관 없음
EXISTS
- 552에 no열의 값과 같은 행이 있다면 '있음'이라는 값으로 갱신하기
NOT EXISTS
- not exists를 사용하여 값을 부정해 없음으로 갱신하기
상관 서브쿼리
- 상관 서브쿼리에서는 부모 명령과 연관되어 처리되기 때문에 서브쿼리 부분만을 따로 떼어내어 실행시킬 수 없다
- 열에 테이블명 붙이기
IN
-
스칼라끼리 비교할 때는 = 연산자를 사용한다. 다만 집합 비교시는 사용 불가 -> IN을 사용하면 집합 안의 값이 존재하는지 조사 가능
-
열명 IN(집합)
-
OR로 사용할 때보다 훨씬 조건식이 깔끔해진다
-
IN을 사용해 조건식 기술하기
-
IN의 오른쪽을 서브쿼리로 지정하기
NOT IN의 경우, 집합 안에 NULL값이 있으면 왼쪽 값이 집합 안에 포함되어 있지 않아도 참을 반환하지 않는다 -> 결과가 '불명'이 된다
6장
데이터베이스 객체
데베의 객체란 테이블,뷰,인덱스 등 데베 내에 정의하는 모든 것을 일컫는 말이다
객체 = 데이터베이스 내에 실체를 가진다
이름을 붙일때는 제약사항을 따른다
스키마
데이터베이스 객체는 스키마라는 그릇 안에서 만들어진다.-> 스키마가 다르면 이름이 같아도 됨
- 네임스페이스
: 이름이 충돌하지 않도록 기능하는 그릇
테이블 작성/삭제/변경
테이블 작성
- create table 테이블명(열 정의1, 열정의2, ...)
테이블 삭제
- drop table 테이블명
테이블 행 삭제
데이터만 삭제시 DELETE명령어 사용 where조건을 지정하지 않으면 모든 행 삭제 주의!!
- TRUNCATE TABLE 테이블명
: 삭제할 데이터가 많을 경우 빠른 속도로 삭제 가능(where구 지정 불가)
테이블 변경
- alter table 테이블명 변경명령
- 변경으로 할 수 있는 일 : 열 추가,삭제,변경 / 제약 추가,삭제
-
열 추가
ALTER table 테이블명 ADD 열 정의
NOT NULL 제약이 걸린 열을 추가할 때는 기본값을 지정해줘야한다 -
열 속성 변경
ALTER TABLE 테이블명 MODIFY 열 정의
-
열 이름 변경
alter table 테이블명 CHANGE (기존열이름) (신규 열 정의)
-
열 삭제
alter table 테이블명 DROP 열명
ALTER TABLE로 테이블 관리
- 최대길이 연장
alter table sample MODIFY col VARCHAR(30) - 열 추가
alter table sample ADD new_col INTEGER
제약
테이블 작성시 제약 정의
- 테이블 열에 제약 정의하기
- 테이블에 '테이블 제약' 정의하기
한 개의 제약으로 복수의 열에 제약을 설명하는 경우 -> 테이블제약
- 테이블 제약에 이름 붙이기
제약 추가
- 열 제약 추가하기
- 테이블 제약 추가
제약 삭제
- 열 제약 삭제하기
- 테이블 제약 삭제하기
- 테이블 기본키 제약 삭제하기
기본키
-
열 p가 기본키인 테이블 생성
-
sample634에 행 추가하기
-
sample634에 중복하는 행 추가하기
기본키 제약에 중복되어 에러 표시
-
sample634를 중복된 값으로 갱신하기
기본키 제약이 설정된 열에는 중복된 값을 저장할 수 없다! -
복수의 열로 기본키 구성하기
기본키로는 null값이 허용X
기본키를 구성하는 열은 복수라도 상관 X
인덱스 구조
인덱스
인덱스는 테이블에 붙여진 색인이라고 할 수 있다.
인덱스의 역할은 검색속도의 향상으로 테이블에 인덱스가 지정되어 있으면 효율적으로 검색할 수 있으므로 where로 조건이 지정된 select 명령의 처리 속도가 향상된다.
인덱스는 테이블과는 별개로 독립된 데이터베이스 객체로 작성된다.
테이블을 삭제 -> 인덱스도 같이 삭제됨
검색에 사용하는 알고리즘
-
풀 테이블 스캔
: 인텍스가 지정되지 않은 테이블 검색시 사용한다.
테이블에 저장된 모든 값을 처음부터 차례로 조사해 나가는 것
행이 1000건 -> 최대 1000건 값 비교 -
이진 탐색
: 차례로 나열된 집합에 대한 유효한 검색 방법
but 데이터가 미리 정렬되어 있어야함
집합을 반으로 나누어 조사하는 방법이다
풀 테이블 스캔으로 했다면 열 번 비교해야했지만 이진탐색이라 3회로 끝남
대량의 데이터 검색시 이진 탐색이 빠르다 -
이진 트리
: 테이블에 인덱스를 작성하면 테이블 데이터와 별개로 인덱스용 데이터가 저장장치에 만들어짐
이때 이진 트리라는 데이터 구조로 작성된다.
검색은 트리의 가지를 더듬어 가면서 행해진다.
원하는 수치와 비교해서 더 크면 오른쪽 가지를, 작으면 왼쪽의 가지를 조사해 나감
10이라는 값 검색하기
유일성
이진 트리에서는 집합 내에 중복하는 값을 가질 수 없다.
-> 같은 값을 허용하기 위해서는 제 3의 가지를 가져야함
-> 하지만 이진트리에서 '같은 값을 가지는 노드를 여러 개 만들 수 없다'라는 특성은 키에 대하여 유일성을 가지게 할 경우에만 유용
-> 주로 기본키 제약을 이진 트리로 인텍스를 작성함
인덱스 작성과 삭제
CREATE INDEX
DROP INDEX인덱스 작성
인덱스 삭제
인덱스를 작성해두면 검색이 빨라짐
where구로 조건지정 -> select명령으로 검색하면 처리속도 빠름
EXPLAIN
인덱스를 사용해 검색하는지를 확인하는 명령
- sample62의 a 열에는 isample65라는 인덱스가 작성되어 있다 -> select 명령은 a 열의 값을 참조해 검색하므로 isample65를 사용해 검색
- where조건을 바꾸어 검색 -> a열을 사용하지 않도록 조건 변경하여 인덱스 사용 불가
최적화
select명령 실행시 인덱스의 사용 여부를 선택하는 것은 내부의 최적화에 의해 처리되는 부분이다. 내부 처리에서는 select명령을 실행하기에 앞서 실행계획(where조건으로 지정되어 있으니 인덱스를 사용하자와 같은) 을 세운다.EXPLAIN명령은 이 실행계획을 확인하는 명령.
뷰 작성과 삭제
FROM구에서 기술된 서브쿼리에 이름을 붙이고 데이터베이스 객체화하여 쓰기 쉽게한 것을 '뷰'라함
뷰
본래 데이터베이스 객체로 등록할 수 없는 select명령을, 객체로서 이름을 붙여 관리할 수 있도록 한 것이 뷰다.
뷰는 select명령을 기록하는 데이터베이스 객체다!
- 가상 테이블
뷰는 '실체가 존재하지 않는다'라는 의미로 '가상 테이블'이라 불리기도 한다.
테이블처럼 데이터를 쓰거나 지울 수 있는 저장공간을 가지지 X
select 명령에서만 사용하는 것을 권장
뷰의 작성과 삭제
- 뷰 작성하기
- 열을 지정해 뷰 작성하기
- 뷰 삭제
뷰의 약점
뷰는 저장공간을 소비하지 않는 대신 CPU 자원을 사용한다.
뷰를 참조하면 -> SELECT 명령이 실행됨 -> 실행 결과 일시적 보존
뷰를 참조할 때마다 select명령이 실행된다.
-
머티리얼라이즈드 뷰
테이블에 보관 데이터양이 많은 경우, 뷰가 사용된다면 처리속도가 많이 떨어질 수밖에 없다.
뷰를 중첩해 사용해도 처리속도가 떨어짐.
이를 회피하기 위해 사용하는 것이 바로 머티리얼라이즈드 뷰
머티리얼라이즈드 뷰는 데이터를 테이블처럼 저장장치에 저장해두고 사용한다.(처음 참조되었을 때 데이터를 저장해두고 이후에 다시 그대로 사용함)
-> 매번 select명령을 실행할 필요X (다만 데이터 변경시에는 select명령어 재실행하여 데이터 저장해야함)
but mysql에서는 사용 불가 ㅠㅠ -
함수 테이블
부모 쿼리와 어떤 식으로든 연관된 서브쿼리의 경우에는 뷰의 select 명령으로 사용불가
-> 이를 회피하기위해 함수 테이블을 사용
함수테이블은 테이블을 결괏값으로 반환해주는 사용자정의 함수이다.
지정한 인수의 값에 따라 where조건을 붙여 결괏값을 바꿀 수 있다. -> 서브쿼리처럼 동작 가능
