[50] 230310 SQL Ch. 6 데이터베이스와 SQL: 25강 데이터베이스 객체, 26강 테이블 작성, 삭제, 변경, 27강 제약, 28강 인덱스 구조, 29강 인덱스 작성과 삭제, 30강 뷰 작성과 삭제 [K-디지털 트레이닝 50일]

230310 Fri 50th class

Ch. 6 데이터베이스와 SQL
진도: p. 245 (27.3 제약 삭제) ~ (교재: SQL 첫걸음, 저자: 아사이 아츠시)
MySQL Workbench Manual: https://dev.mysql.com/doc/workbench/en/
MySQL 튜토리얼: https://www.tutorialspoint.com/mysql/
SQLD 요약 pdf: https://yurimac.tistory.com/40
책 요약 (9p) pdf: https://brunch.co.kr/@ydy702/10

SQL 첫걸음 | 아사이 아츠시 - 교보문고

오늘 배운 것 중 기억할 것을 정리했다.

---

몰랐던 것

VARCHAR Datatype

The VARCHAR datatype is synonymous with the VARCHAR2 datatype. To avoid possible changes in behavior, always use the VARCHAR2 datatype to store variable-length character strings.
결론: varchar2로 쓰는 게 낫다. 
참고: https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1824

A query in a database is a request for information from a database management system (DBMS), which is the software program that maintains data. Users can make a query to retrieve data or change information in a database, such as adding or removing data.

쿼리는 웹 서버에 특정한 정보를 보여달라는 웹 클라이언트 요청(주로 문자열을 기반으로 한 요청)에 의한 처리이다. 

---

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

책의 목차
__25강__데이터베이스 객체
1. 데이터베이스 객체
2. 스키마

__26강__테이블 작성, 삭제, 변경
1. 테이블 작성
2. 테이블 삭제
3. 테이블 변경
4. ALTER TABLE로 테이블 관리

__27강__제약
1. 테이블 작성시 제약 정의
2. 제약 추가
3. 제약 삭제
4. 기본키

__28강__인덱스 구조
1. 인덱스
2. 검색에 사용하는 알고리즘
3. 유일성

__29강__인덱스 작성과 삭제
1. 인덱스 작성
2. 인덱스 삭제
3. EXPLAIN
4. 최적화

__30강__뷰 작성과 삭제
1. 뷰
2. 뷰 작성과 삭제
3. 뷰의 약점

25강 데이터베이스 객체

25.1 데이터베이스 객체

데이터베이스 객체: 테이블이나 뷰, 인덱스 등 데이터베이스 내에 정의하는 모든 것
여기서 객체 = 데이트베이스 객체 
객체: 실체를 가지는 어떤 것 ex) 테이블 

이름 제약 사항(명명규칙)
- [포인트] 의미없는 이름X
- 기존 이름이나 예약어와 중복X
- 숫자로 시작X
- 언더스코어(_) 외의 기호 허용X
- 한글을 사용할 때는 " "더블쿼트로 둘러싼다. 
- 시스템이 허용하는 길이 초과X

---

25.2 스키마

데이터베이스 객체는 스키마라는 그릇에서 만들어진다.
객체의 이름이 같아도 스키마가 서로 다르면 상관 없다. (데이터베이스 객체 = 스키마 객체)

The database schema is the structure of a database described in a formal language supported by the database management system. 

스키마 설계: 데이터베이스에 테이블을 작성해서 구축해나가는 작업 

스키마 예시
ex) MySQL: CREATE DATABASE 명령으로 작성한 '데이터베이스'(저장장치 내에 정리되어 저장된 데이터의 집합) 
Oracle: 데이터베이스와 데이터베이스 사용자가 계층적 스키마

네임스페이스(namespace): 이름이 충돌하지 않도록 기능하는 것
ex) 스키마, 테이블 

---

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

Ctrl + Enter로 실행 => SCHEMAS에서 새로고침 누름 

데이터 유형

- CHAR(s): 고정 길이 문자열 정보, 최대 길이만큼 공간 채움 ‘AA’ = ‘AA ’
- VARCHAR(s) : 가변 길이 문자열 정보, 할당된 변수 값의 바이트만 적용 ‘AA’ != ‘AA’
- NUMBER: 정수, 실수 등 숫자 정보
- DATE: 날짜와 시각 정보

no	a	b	newcol
1	10ABC	2014-01-01	NULL
2	20ABC	2014-01-02	NULL

---

26.1 테이블 작성 CREATE TABLE 테이블명

테이블 생성
create table 테이블명(
	열 정의1,
	열 정의2,
	.....
)

create table sample62(
    no,
    a,
    b,
    newcol
)

열 정의

열명 자료형 [DEFAULT 기본값] [NULL|NOT NULL]

테이블 생성
create table 테이블명(
	열이름1 자료형 [기본값] [NULL|NOT NULL],
	열이름2 자료형 [기본값] [NULL|NOT NULL],
	.....
)

create table sample62(
    no INTEGER NOT NULL,
    a varchar(30),
    b,
    newcol
)

---

26.2 테이블 삭제 DROP TABLE 테이블명 

테이블과 저장된 데이터도 함께 삭제한다. 

DROP TABLE 테이블명

SQL 명령의 경우 재확인 메시지가 표시되지 않으니 신중하게 DROP TABLE을 실행한다. 

- 데이터 행 삭제

TRUNCATE TABLE 테이블명

TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. To remove the table definition in addition to its data, use the DROP TABLE statement.

---

26.3 테이블 변경
ALTER TABLE 테이블명 ADD 열 정의 (INTERGER)
ALTER TABLE 테이블명 MODIFY 열 정의 (VARCHAR(20))
ALTER TABLE 테이블명 CHANGE 기존열이름 신규열명 신규자료형
ALTER TABLE 테이블명 DROP 열명

열 구성을 변경한다. (CREATE TABLE 사용이 아니다.)
ex) 열을 추가하거나 데이터 최대길이를 변경한다. 

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

ALTER TABLE 테이블명 변경명령

ALTER TABLE 테이블명 ADD 열 정의 -- 열 추가

ALTER TABLE 테이블명 MODIFY 열 정의 -- 열 속성 변경

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

ALTER TABLE 테이블명 DROP 열명 -- 열 삭제

create database sample; -- sample 데이터베이스 생성
use sample;				-- 수많은 데이터베이스 중에서 sample 데이터베이스를 사용
create table sample62(	-- sample62테이블 생성
	no integer not null,
    a varchar(30),
    b date
);
-- drop table sample62 -- sample62테이블을 삭제
-- 테이블 변경
alter table sample62 add newcol int; -- newcol열을 추가(add)
alter table sample62 modify a varchar(20); -- a열의 문자 최대 길이를 30->20으로 변경(modify) 
alter table sample62 change newcol c varchar(20); -- newcol열의 이름을 c열로 바꾸고, 타입을 int->varchar(20) (change)sample62sample62
desc sample62; -- descending order

insert into sample62(no, a,c) values(1, 'abc','def')

---

26.4 ALTER TABLE로 테이블 관리 p. 240

- 용량이 부족한 경우 ALTER TABLE로 열의 자료형만 변경해 최대 길이 연장

데이터 하나의 크기를 최적화하는 것만으로도 저장 공간을 절약할 수 있다.

ex) 문자열형의 경우 최대길이를 지정하는데, 이 최대길이를 ALTER TABLE로 늘릴 수 있다.

ALTER TABLE sample MODIFY col VARCHAR(30)

최대길이를 늘리는 경우는 많지만 줄이는 경우는 별로 없다.

- 테이블에 ALTER TABLE로 열 추가

ALTER TABLE sample ADD new_col INTEGER

---

문제 풀이)

create database sample; -- sample 데이터베이스 생성
use sample;
create table sample631(
	a int not null,
    b int primary key,
    c varchar(30)
    );

1. 아래 sql문장은 실행이 되지 않습니다. 그 이유를 적어보세요.

create database sample; -- sample 데이터베이스 생성
use sample;
create table sample631(
	a int not null,
    b int primary key,
    c varchar(30)
    );
    
insert into sample631(b,c) values(3,'그린이');
select * from sample631;

해결: a를 넣어서 어떤 값을 지정한다. 

2. 아래 sql문장은 실행이 되지 않습니다. 그 이유를 적어보세요.

create database sample; -- sample 데이터베이스 생성
use sample;
create table sample631(
	a int not null,
    b int primary key,
    c varchar(30)
    );
    
insert into sample631(a,b) values(2,2);
select * from sample631;

primary key 해당 필드가 NOT NULL과 UNIQUE 제약 조건의 특징을 모두 가지게 됩니다. ex) 주민등록번호

2, 2 중복이어서 안 된다. 

3. 아래 sql 문장이 실행되는지 안 되는지 예상해 보고, 그 이유를 적어보세요

insert into sample631 values(1,3,'그린이'); -- 된다.
insert into sample631 values(1,3); -- 안 된다.

괄호를 생략하면 테이블의 모든 열에 다 넣는다는 뜻이다. 

---

27강 제약

null	중복값
X	O	not null	해당 필드는 NULL 값을 저장할 수 없게 됩니다. (필수 입력)
O	X	unique (고유키)	해당 필드는 서로 다른 값을 가져야만 합니다.
X	X	primary key (기본키)	해당 필드가 NOT NULL과 UNIQUE 제약 조건의 특징을 모두 가지게 됩니다. ex) 주민등록번호, 회원가입할 때 ID

제약조건: 데이터의 무결성 유지
1. PRIMARY KEY(기본키) : UNIQUE & NOT NULL 
2. UNIQUE KEY(고유키) : 고유키 정의
3. NOT NULL : NULL 값 입력금지
4. CHECK : 입력 값 범위 제한
5. FOREIGN KEY(외래키) : NULL 가능 여러 속성가능
참고: https://www.w3schools.com/sql/sql_foreignkey.asp

제약 조건(constraint): 데이터의 무결성을 지키기 위해 데이터를 입력받을 때 실행되는 검사 규칙을 의미합니다.
이러한 제약 조건은 CREATE 문으로 테이블을 생성할 때나, ALTER 문으로 필드를 추가할 때도 설정할 수도 있습니다.
 
CREATE TABLE 문에서 사용할 수 있는 제약 조건은 다음과 같습니다.
 
1. NOT NULL : 해당 필드는 NULL 값을 저장할 수 없게 됩니다.
2. UNIQUE : 해당 필드는 서로 다른 값을 가져야만 합니다.
3. PRIMARY KEY : 해당 필드가 NOT NULL과 UNIQUE 제약 조건의 특징을 모두 가지게 됩니다.
4. FOREIGN KEY : 하나의 테이블을 다른 테이블에 의존하게 만듭니다.
5. DEFAULT : 해당 필드의 기본값을 설정합니다.

---

27.1 테이블 작성 시 제약 정의

---

27.2 제약 추가

create database sample; -- sample 데이터베이스 생성
use sample;
create table sample631(
	a integer not null,
    b integer not null unique,
    c varchar(30)
    );
insert into sample631 (a,b,c) values(1,1,'정자바');
select * from sample631;
insert into sample631 (a,b,c) values(1,1,'정자바'); -- Error Code: 1062. Duplicate entry '1' for key 'sample631.b'

create table sample631(
	a int not null,
    b int primary key
    c varchar(30)
    );
    
create table sample631(
	a int not null,
    b int,
    c varchar(30)
    primary key(b) -- 나중에 삭제 가능하므로 이 형태로 쓴다.
    );
    
create table sample631(
	a int not null,
    b int,
    c varchar(30)
    constraint 제약조건명 primary key(b) -- 제약 이름은 constraint 키워드를 사용해서 지정한다.
    );

- 열 제약 조건 추가
c 열에 NOT NULL 제약을 설정하는 예

ALTER TABLE sample631 MODIFY c VARCHAR (30) NOT NULL;

- 테이블 제약 추가 
기본키 제약을 추가하는 예

ALTER TABLE sample631 ADD CONSTRAINT pkey_sample631 PRIMARY KEY(a);

---

27.3 제약 삭제 p. 245

create database sample; -- sample 데이터베이스 생성
use sample;
CREATE TABLE sample631 (
    a INT NOT NULL,
    b INT PRIMARY KEY,
    c VARCHAR(30)
);
insert into sample631 values(1,3,'그린이');
SELECT * FROM sample631;

-- c열에 not null 제약 추가
alter table sample631 modify c varchar(30) not null;
-- c열에 not null 제약 삭제
alter table sample631 modify c varchar(30);
desc sample631;
-- a열에 primary key(기본키) 제약 추가
alter table sample631 add constraint pkey_sample631 primary key(a);
-- a열에 primary key(기본키) 제약 삭제 (제약조건이름이 있을 경우: pkey_sample631)
alter table sample631 drop constraint pkey_sample631;
-- a열에 primary key(기본키) 제약 삭제 (제약조건이름이 없을 경우)
alter table sample631 drop primary key;
desc sample631;
select * from sample631;

delete from sample631;

---

27.4 기본키 p. 246

기본키(primary key): 해당 필드가 NOT NULL과 UNIQUE(중복값X) 제약 조건의 특징을 모두 가지게 된다.
ex) 주민등록번호, 회원가입할 때 ID

기본키 제약: 열을 기본키로 지정해 유일한 값을 가지도록 하는 구조, 유일성 제약으로도 불린다.

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

create database sample; -- sample 데이터베이스 생성
use sample;
-- sample634 테이블 생성
create table sample634(
	-- p라는 이름의 열에 null값을 허용하지 않음
    p integer not null,
    -- a라는 이름은 최대 문자열을 30으로 설정
    a varchar(30),
    -- p열에 기본키(제약조건)를 설정하고 그 제약조건의 이름을 pkey_smaple634로 설정
    constraint pkey_sample634 primary key(p)
    );
    
insert into sample634 values (1,'첫째줄');
insert into sample634 values (2,'둘째줄');
insert into sample634 values (3,'셋째줄');

insert into sample634 values (2, '넷째줄');
-- Error Code: 1062. Duplicate entry '2' for key 'sample634.PRIMARY' : p열의 값이 중복이므로

update sample634 set p=2 where p=3; -- where: 필터링 조건, 이 필터링이 없으면 모든 데이터가 2로 된다. 
-- Error Code: 1062. Duplicate entry '2' for key 'sample634.PRIMARY' : p = 2인 열이 두 개나 존재하므로 

select * from sample634;

- 복수의 열로 기본키 구성하기 p. 248
= p열, a열을 합쳐서 기본키 구성하기 (슈퍼키)

키의 종류 (정처기에 나옴, 참고: 시나공 정처기 요약정리 173번)

- 후보키(Candidate Key): 기본키가 될 수 있는 열 ex) 주민등록번호, 학번
•릴레이션을 구성하는 속성들 중에서 튜플을 유일하게 식별하기 위해 사용하는 속성들의 부분집합, 즉 기본키로 사용할 수 있는 속성들을 말함
•후보키는 릴레이션에 있는 모든 튜플에 대해서 유 일성과 최소성을 만족시켜야 함

- 대체키(Alternate Key): 기본키를 대체할 수 있는 키 ex) 학번 
•후보키가 둘 이상일 때 기본키를 제외한 나머지 후 보키를 의미함
•보조키라고도함

- 기본키: 하나의 열로 구성된 유일성을 갖춘 키 
•후보키 중에서 특별히 선정된 주키(Main Key)로 중 복된 값을 가질 수 없음
•한 릴레이션에서 특정 튜플을 유일하게 구별할 수 있는 속성
•기본키는 NULL 값을 가질 수 없다. 즉 튜플에서 기 본키로 설정된 속성에는 NULL 값이 있어서는 안 됨

- 슈퍼키(Super Key): 두 개 이상의 열을 합쳐서 구성된 유일성을 갖춘 키
•한 릴레이션 내에 있는 속성들의 집합으로 구성된 키로서 릴레이션을 구성하는 모든 튜플들 중 슈퍼 키로 구성된 속성의 집합과 동일한 값은 나타나지 않음
•슈퍼키는 릴레이션을 구성하는 모든 튜플에 대해 유일성은 만족시키지만, 최소성은 만족시키지 못함

- 외래키(Foreign Key)
•다른 릴레이션의 기본키를 참조하는 속성 또는 속성들의 집합을 의미함
•한 릴레이션에 속한 속성 A와 참조 릴레이션의 기본 키인 B가 동일한 도메인 상에서 정의되었을 때의 속성 A를 외래키라고 함

예제) a열과 b열로 이루어진 기본키

create table sample635(
	a int not null,
    b int not null,
    constraint skey_sample635 primary key(a,b)
    );
    
desc sample635;
insert into sample635 values(1,1);
insert into sample635 values(1,2);
insert into sample635 values(1,3);
insert into sample635 values(2,1);
insert into sample635 values(2,2);

select * from sample635;

결과)

a	b
1	1
1	2
1	3
2	1
2	2

2개 묶음으로 판단한다. (1,1), (1,2), (1,3), (2,1), (2,2)

---

28강 인덱스 구조

28.1 인덱스

인덱스: 테이블에 붙여진 색인

---

28.2 검색에 사용하는 알고리즘

데이터베이스의 인덱스에 쓰이는 대표적인 검색 알고리즘
ex) 이진 트리(binary tree), 해시

이진탐색(binary search): 집합을 반으로 나누어 조사하는 검색방법
[포인트] 대량의 데이터를 검색할 때는 이진 탐색이 빠르다!

이진트리(binary tree): 테이블에 인덱스를 작성하면 테이블 데이터와 별개로 인덱스용 데이터가 저장장치에 만들어지며 이진 트리라는 데이터 구조로 작성된다. 

---

28.3 유일성 

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

---

29강 인덱스 작성과 삭제 p. 257

CREATE INDEX
DROP INDEX

-- 인덱스 생성
-- sample62 테이블의 no 열에 isample65라는 인덱스를 지정한다. 
create index isample65 on sample62(no);

select * from sample62; #index를 전혀 사용하고 있지 않음.

-- explain 인덱스 사용 여부 조회
-- possible_keys열, key열이 null이면 index를 사용하고 있지 않다는 거임. 
explain select * from sample62; 

select * from sample62 where a='abc'; -- index사용해서 검색

explain select * from sample62 where a='abc';
desc sample62;

29.1 인덱스 작성

문법

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

예제) 인덱스 작성하기

-- sample62 테이블의 no 열에 isample65라는 인덱스를 지정한다. 
CREATE INDEX isample65 ON sample62(no);

---

29.2 인덱스 삭제

문법

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

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

예제) 인덱스 삭제하기

DROP INDEX isample65 ON sample62;

다음과 같은 명령으로 인덱스를 작성했다고 가정한다.

CREATE INDEX isample65 ON sample62(a);

WHERE 구에 a 열에 대한 조건식을 지정한 경우 SELECT 명령은 인덱스를 사용해 빠르게 검색할 수 있다. 그러나 WHERE 구의 조건식에 a 열이 전혀 사용되지 않으면 SELECT 명령은 isample62라는 인덱스를 사용할 수 없다.

SELECT * FROM sample62 WHERE a = 'a';

---

29.3 EXPLAIN

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

문법

EXPLAIN SQL 명령

예시) EXPLAIN으로 인덱스 사용 확인하기1 (MySQL)

EXPLAIN SELECT * FROM sample62 WHERE a = 'a';

---

29.4 최적화

실행계획에서는 인덱스의 유무뿐만 아니라 인덱스를 사용할 것인지 여부에 대해서도 데이터베이스 내부의 최적화 처리를 통해 판단된다. 

---

30강 뷰 작성과 삭제 p. 263

CREATE DATABASE 데이터베이스명
CREATE TABLE 테이블명
CREATE INDEX 인덱스명
CREATE VIEW 뷰명 AS SELECT절 

SELECT * FROM 테이블명

서브쿼리 = 쿼리 안에 쿼리를 넣음 
SELECT (SELECT * FROM 테이블명) FROM 테이블명 - 서브쿼리 (스칼라 서브쿼리)

SELECT * FROM (SELECT * FROM 테이블명) A - 서브쿼리 (인라인 뷰)

SELECT * FROM 테이블명 WHERE A = (SELECT * FROM 테이블명) - 서브쿼리 (중첩 서브쿼리 - WHERE 씀)

---

30.1 뷰

뷰 : 테이블은 실제로 데이터를 가지고 있는 반면, 뷰는 실제 데이터를 가지고 있지 않다.
가상테이블이라고도 함.
실행 시점에 SQL 재작성하여 수행됨.
쿼리를 단순화하기 위해 뷰를 쓴다. 

문법

CREATE VIEW 뷰명 AS SELECT 명령
DROP VIEW 뷰명

SELECT * FROM sample635;

-- CREATE VIEW AS
CREATE VIEW sample_view_365 AS SELECT * FROM sample635;

SELECT * FROM V1
#아래 첫 문단 코드랑 같다. SELECT절을 단순화시킨다.
SELECT *
FROM (SELECT *
		FROM TB1
        WHERE col1 = (SELECT * FROM TB2 WHERE col2 = 'b')
        AND col3 = 'c'
        ORDER BY c1
        GROUP BY c1
        HAVING c4 >= 100
)

CREATE VIEW V1 AS
SELECT *
FROM (SELECT *
		FROM TB1
        WHERE col1 = (SELECT * FROM TB2 WHERE col2 = 'b')
        AND col3 = 'c'
        ORDER BY c1
        GROUP BY c1
        HAVING c4 >= 100
)

Point: 뷰는 SELECT 명령을 기록하는 데이터베이스 객체다.

Point: 뷰는 작성하는 것으로 복잡한 SELECT 명령을 간략하게 표현할 수 있다.

---

30.2 뷰 작성과 삭제

- 뷰의 작성

CREATE VIEW 뷰명 AS SELECT 명령

예제) 뷰 작성하기

CREATE VIEW sample_veiw_67 AS SELECT * FROM sample54;
SELECT * FROM sample_view_67;

CREATE VIEW에서 열 지정하기

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

예제) 열을 지정해 뷰 작성하기

CREATE VIEW sample_view_672(n, v, v2) AS
	SELECT no, a, a*2 FROM sample54;
SELECT * FROM sample_view_672 WHERE n = 1;

- 뷰 삭제

DROP VIEW 뷰명

예제) 뷰 삭제하기

DROP VIEW sample_view_67;

---

30.3 뷰의 약점

- 머리티얼라이즈드 뷰 (Materialized View)
데이터를 일시적으로 저장해 사용하는 것이 아니라 테이블처럼 저장장치에 저장해두고 사용한다.
단점: MySQL에서는 사용X
Oracle, DB2에서만 사용O

- 함수 테이블 
부모 쿼리와 어떤 식으로든 연관된 서브쿼리의 경우에는 뷰의 SELECT 명령으로 사용할 수 없다. 이를 함수 테이블을 사용하여 회피할 수 있다.

---