[필기정리] Day60 - 상관 서브쿼리, exist, in 등

# 상관 서브쿼리

# EXISTS : 서브쿼리의 반환값 여부 반환(참, 거짓)

EXISTS(SELECT명령)

EXISTS를 사용하는 경우에는 서브쿼리가 반드시 스칼라 값을 반환할 필요는 없다.

 

ex)

CREATE TABLE sample551(no int, a char(10));
INSERT INTO sample551 VALUES(1, NULL);
INSERT INTO sample551 VALUES(2, NULL);
INSERT INTO sample551 VALUES(3, NULL);
INSERT INTO sample551 VALUES(4, NULL);
INSERT INTO sample551 VALUES(5, NULL);

CREATE TABLE sample552(no2 int);
INSERT INTO sample552 VALUES(3);
INSERT INTO sample552 VALUES(5);

 

ex) sample551에 no열이 sample552의 no2열과 같은 행이 있으면 a열을 '있음'으로 갱신하기.

UPDATE sample551 SET a = '있음' WHERE
	EXISTS(SELECT * FROM sample552 WHERE no2 = no);

 

ex) sample551에 no열이 sample552의 no2열과 같은 행이 아니면면 a열을 '없음'으로 갱신하기.

UPDATE sample551 SET a = '없음' WHERE
	NOT EXISTS(SELECT * FROM sample552 WHERE no2 = no);
SELECT * FROM sample551;

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

 

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

UPDATE 명령(부모)에서 WHERE 구에 괄호로 묶은 부분이 서브쿼리(자식)가 된다.

부모 명령에서는 sample551를 갱신한다.

자식인 서브쿼리에서는 sample552 테이블의 no2 열 값이 부모의 no 열 값과 일치하는 행을 검색한다.

 

부모 명령과 연관되어 처리되기 때문에 상관 서브쿼리 부분만을 따로 떼어내어 실행시킬 수 없다.

UPDATE sample551 SET a = '있음' WHERE
	EXISTS(SELECT * FROM sample552 WHERE no2 = no );

SELECT * FROM sample552 WHERE no2 = no; # 에러, no가 불명확하다

ex) 열에 테이블명 붙이기

UPDATE sample551 SET a = '있음' WHERE
	EXISTS (SELECT * FROM sample552 WHERE sample552.no2 = sample551.no);

 

# IN

칼럼명 IN(집합)

 

IN은 집합 안에 값이 포함되어 있으면 참이 된다.

반면 NOT IN으로 지정하면 집합에 값이 포함되어 있지 않을 경우 참이 된다.

 

- IN을 사용해 조건식 기술

SELECT * FROM sample551 WHERE no IN (3, 5);

 

- 집합은 서브쿼리로도 지정할 수 있다.

SELECT * FROM sample551 WHERE no IN (SELECT no2 FROM sample552);

이 같은 경우 서브쿼리는 스칼라 서브쿼리가 될 필요는 없다.

 

 

- IN과 NULL

IN에서는 집합안에 NULL 값이 있어도 무시하지는 않는다.

다만 NULL = NULL을 제대로 계산할 수 없으므로 IN을 사용해도 NULL 값은 비교할 수 없다.

즉, NULL을 비교할 때는 IS NULL을 사용해야 한다.

 

NOT IN은 왼쪽 값이 집합에 포함되어 있으면 거짓을, 그렇지 않으면 NULL을 반환한다.

결국 NOT IN의 경우 집합에 NULL이 포함되어 있다면 그 결과값은 0건이 된다.

 

NULL을 반환한다는 것은 비교할 수 없다는 것을 의미한다.

 

ex)

SELECT * FROM SAMPLE551 WHERE NO IN(3, 5, NULL);
SELECT * FROM SAMPLE551 WHERE NO NOT IN(3, 5, NULL);
SELECT * FROM SAMPLE551 WHERE NO NOT IN(3, 5);

 

Q1. 테이블의 행 개수를 알고 싶을 때 사용하는 집계함수는 무엇인가?

① COUNT

② SUM

③ MAX

A. ① COUNT : COUNT는 행의 개수를 구하는 집약함수이다.

                   SUM은 합계를, MAX는 최대값을 구하는 집약함수이다.

 

Q2. SELECT 명령에 GROUP BY를 지정한 경우 데이터베이스 내부에서 수행되는 처리는 무엇인가?

① 정렬

② 그룹화

③ 최적화

A. ② 그룹화 : GROUP BY구에서는 그룹화 방법을 지정한다.

                  데이터베이스 내부에서 그룹화가 행해진다.

 

Q3. SELECT 구에서 지정할 수 있는 서브쿼리는 무엇인가?

① 스칼라 서브쿼리

② 상관 서브쿼리

③ 서브쿼리를 지정할 수 없다.

A. ① 스칼라 서브쿼리 : SELECT 구에는 하나의 값을 반환하는 스칼라 서브쿼리를 지정할 수 있다.

 

# 데이터베이스 객체
데이터베이스 내에 실체를 가진다. ex)테이블, 뷰, 인덱스

// Tip : 뷰 안에 데이터는 없지만 뷰 자체는 존재하기 때문에 객체라 볼 수 있다.

- 명명규칙
① 기존 이름이나 예약어와 중복하지 않는다.
② 숫자로 시작할 수 없다.
③ 언더스코어(_) 이외의 기호는 사용할 수 없다.
④ 한글을 사용할 때는 더블쿼트(MySQL에서는 백쿼트)로 둘러싼다.
⑤ 시스템이 허용하는 길이를 초과하지 않는다.

# 스키마 : 데이터베이스의 설계도
데이터베이스 객체는 '스키마 객체'라 불리기도 한다. 

실제로 데이터베이스에 테이블을 작성해서 구축해나가는 작업을 '스키마 설계'라고 부른다. 
이때 스키마는 SQL 명령의 DDL을 이용하여 정의한다.

MySQL에서는 CREATE DATABASE 명령으로 작성한 '데이터베이스'가 스키마가 된다. 
한편 Oracle 등에서는 데이터베이스와 데이터베이스 사용자가 계층적 스키마가 된다.

 

테이블과 스키마는 무엇인가를 담는 그릇 역할을 한다는 점에서 비슷하다. 

테이블 안에는 열을 정의할 수 있고 스키마 안에는 테이블을 정의할 수 있다. 
각각의 그릇 안에서는 중복하지 않도록 이름을 지정한다.

이처럼 이름이 충돌하지 않도록 기능하는 그릇을 '네임스페이스(namespace)'라고 부르기도 한다.

 

# 테이블 생성, 삭제, 변경

CREATE TABLE 테이블명(열 정의1, 열 정의2, ...) 
DROP TABLE 테이블명 
ALTER TABLE 테이블명 하부명령

SELECT, INSERT, DELETE, UPDATE는 SQL 명령 중에서도 DML로 분류된다. 

DML은 데이터를 조작하는 명령이다. 

한편 DDL은 데이터를 정의하는 명령으로, 스키마 내의 객체를 관리할 때 사용한다.

1. 테이블 생성 
DDL은 모두 같은 문법을 사용한다. 

CREATE로 작성, DROP으로 삭제, ALTER로 변경할 수 있다. 

뒤이어 어떤 종류의 객체를 작성, 삭제, 변경할지를 지정한다. 

예를 들어 테이블을 작성한다면 CREATE TABLE, 뷰를 작성한다면 CREATE VIEW와 같이 지정하면 된다.

 

- CREATE TABLE 

CREATE TABLE 테이블명( 
열 정의1, 
열 정의2, 
... 
) 

ex) 

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

DESC sample62;

 

2. 테이블 삭제
- DROP TABLE

DROP TABLE 테이블명

- 데이터 행 삭제

① DELETE : 테이블 정의는 그대로 둔 채 데이터만 삭제할 때 사용
                하지만 행 단위로 여러 가지 내부처리가 일어나므로 

                삭제할 행이 많으면 처리속도가 상당히 늦어진다. 

 

② TRUNCATE TABLE

   삭제할 행을 지정할 수 없고 WHERE 구를 지정할 수도 없지만, 

   모든 행을 삭제해야 할 때 빠른 속도로 삭제할 수 있다.  

TRUNCATE TABLE 테이블명

3. 테이블 변경

테이블에 저장되어 있는 데이터는 그대로 남긴 채 구성만 변경할 수 있다. 

ALTER TABLE로 할 수 있는 일은 크게 다음과 같이 두 가지로 분류할 수 있다.
① 열 추가, 삭제, 변경
② 제약 추가, 삭제

 

- 열 추가

ALTER TABLE 테이블명 ADD 열 정의 

ex)

ALTER TABLE sample62 ADD newcol INTEGER; 

DESC sample62;

ALTER TABLE ADD로 테이블에 열을 추가할 수 있다!
NOT NULL 제약이 걸린 열을 추가할 때는 기본 값을 지정해야 한다!

- 열 속성 변경

ALTER TABLE 테이블명 MODIFY 열 정의

MODIFY로 열 이름은 변경할 수 없지만, 

자료형이나 기본값, NOT NULL 제약 등의 속성은 변경할 수 있다.

ex)

ALTER TABLE sample62 MODIFY newcol VARCHAR(20); 
DESC sample62;

기존의 데이터 행이 존재하는 경우, 속성 변경에 따라 데이터 역시 변환된다.

이 때 만약 자료형이 변경되면 테이블에 들어간 데이터의 자료형 역시 바뀐다.

다만 그 처리과정에서 에러가 발생하면 ALTER TABLE 명령은 실행되지 않는다.

MODIFY는 MySQL과 Oracle에서 사용할 수 있는 ALTER TABLE의 하부명령이다. 

다른 데이터베이스에서는 ALTER 하부명령으로 열 속성을 변경하기도 한다.

- 열 이름 변경

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

 

열 이름을 변경할 때는 MODIFY가 아닌 CHANGE를 사용한다. 

CHANGE는 열 이름뿐만 아니라 열 속성도 변경할 수 있다. 

한편, Oracle에서는 열 이름을 변경할 경우 RENAME TO 하부명령을 사용한다.

ex)

ALTER TABLE sample62 CHANGE newcol c VARCHAR(20); 
DESC sample62; 

- 열 삭제

ALTER TABLE 테이블명 DROP 열명

ex)

ALTER TABLE sample62 DROP c; 
DESC sample62;

 

 

# 데이터 베이스 용어

- 릴레이션 (relation) 
같은 성격의 데이터들의 집합을 의미. 

흔히 테이블이라고 말하는 용어와 같은 의미로 이론적인 용어. 
릴레이션은 튜플과 에트리뷰트로 데이터를 정렬하여 관리한다. 

- 튜플 (tuple)
릴레이션의 각 행을 의미. 

흔히 일반적인 용어로 레코드(record)와 로우(row)와 같은 의미로 사용된다. 

- 어트리뷰트(attribute)
릴레이션에서 이름을 가진 하나의 열을 말한다. 

흔히 일반적인 용어로 칼럼(column)과 같은 의미로 사용된다. 

- 디그리(degree)
에트리뷰트의 수를 말한다. 

- 카디널러티(cardinality)
튜플들의 수를 말한다. 

- 릴레이션의 특징 
① 한 릴레이션에 정의된 튜플들은 모두 다르다.
② 한 릴레이션에 정의된 튜플들은 순서에 무관하다. 
③ 튜플들은 시간에 따라 변한다. 
④ 릴레이션 스키마를 구성하는 에트리뷰트의 값은 동일해도 된다. 
⑤ 에트리뷰트는 더 이상 쪼갤 수 없는 원자값으로 구성된다. 
⑥ 릴레이션을 구성하는 튜플을 유일하게 식별하기 위한 속성들의 부분집합을 키(Key)로 설정한다. 

- 도메인(Domain)
도메인이란 릴레이션에 포함된 각각의 속성들이 가질 수 있는 값들의 집합이라고 할 수 있다. 

도메인이라는 개념이 필요한 이유는 

릴레이션에 저장되는 데이터 값들이 본래 의도했던 값들만 저장되고 관리하기 위해서이다.

예를 들어 '성별'이라는 속성이 있다면 이 속성이 가질 수 있는 값은 '남' 또는 '여' 일 것이다.

 

데이터베이스 설계자는 성별의 도메인으로 'SEX'를 정의하고 그 값으로 '남', '여'로 지정한 뒤

'성별'이라는 속성은 'SEX' 도메인에 있는 값만을 가질 수 있다고 지정해 놓으면

사용자들이 실수로 남, 여 이외의 값을 입력하는 것을 방지할 수 있다.

도메인의 이름은 속성 이름과 같을 수도 있고 다를 수도 있다.

하나의 도메인을 여러 속성에서 공유할 수도 있다.

 

+ between : 범위 지정 함수

ex)

create table sample200(no int); 
insert into sample200 values(1); 
insert into sample200 values(2); 
insert into sample200 values(3); 
insert into sample200 values(4); 
insert into sample200 values(5); 
insert into sample200 values(6); 
insert into sample200 values(7); 
select * from sample200 where no between 3 and 5; 
select * from sample200 where no >=3 and no <=5;