SQL 공부 일지 5일차 230816

## 공부정리

과목2 1장 SQL 기본

1) 관계형 데이터베이스 개요

2) DDL (CREATE, ALTER, DROP, RENAME)

3) DML (SELECT, INSERT, UPDATE, DELETE)

 

 

 

#1 관계형 데이터베이스 개요

1 DB : 데이터베이스

• 데이터를 일정한 형태로 저장해 놓은 것
• 계층형 DB : 트리 형태의 자료구조에 데이터 저장(1:N)
• 네트워크형 DB : 오너와 멤버 형태로 데이터 저장(M:N)
• 관계형 DB : 집합 연산과 관계 연산 가능

 

2 DBMS : 데이터베이스메니지먼트시스템

- 효율적인 데이터 관리 뿐만 아니라 예기치 못한 사건으로 인한 데이터의 손상을 피하고, 필요시 필요한 데이터를 복구하기 위한 강력한 기능의 SW(소프트웨어)

 

3 DB(데이터베이스)의 발전

• 1960 : 플로우차트 중심의 개발, 파일구조 사용
• 1970 : DB 관리기법이 처음 태동, 계층-망형 DB등장
• 1980 : 관계형 DB 상용화, Oracle, Sybase 등장
• 1990 : 객체 관계형 DB로 발전

 

 

4 관계형 데이터베이스 (RDB : Relational Database)

- 파일 시스템 단점 : 동시에 삽입/수정/삭제가 불가능해 데이터 관리가 어렵고 복사본 파일을 만들어 사용할 경우 데이터의 불일치성이 발생

- RDB의 장점 : 정규화를 통해 이상 현상과 중복제거, 데이터 무결성 보장, 데이터 회복/복구 가능, 병행 제어 동시성 관리를 통해 데이터 공유, 데이터 표현 방법 등 체계화

 

 

5 SQL 문장들의 종류

- SQL : 관계형 DB에서 데이터 정의, 조작, 제어를 위해 사용하는 언어

 

명령어의 종류	명령어	설명
데이터 조작어 (DML : Data Manipulation Language)	SELECT	데이터베이스에 들어 있는 데이터를 조회하거나 검색하기 위한 명령어를 말하는 것으로 RETRIEVE 라고도 한다.
	INSERT UPDATE DELETE	데이터베이스의 테이블에 들어 있는 데이터에 변형을 가하는 종류의 명령어들을 말한다.  예를 들어 데이터를 테이블에 새로운 행을 집어넣거나, 원하지 않는 데이터를 삭제하거나 수정하는 것들의 명령어들을 DML이라고 한다.
데이터 정의어 (DDL : Data Definition Language)	CREATE ALTER DROP RENAME	테이블과 같은 데이터 구조를 정의하는데 사용되는 명령어들로 그러한 구조를 생성하거나 변경하거나 삭제하거 이름을 바꾸는 데이터 구조와 관련된 명령어들을 DDL이라 한다.
데이터 제어어 (DCL : Data Control Language)	GRANT REVOKE	데이터베이스에 접근하고 객체들을 사용하도록 권한으르 주고 회수하는 명령어를 DCL이라고 부른다.
트랜잭션 제어어 (TCL : Transaction Control Language)	COMMIT ROLLBACK	논리적인 작업의 단위를 묶어서 DML에 의해 조작된 결과를 작업단위(트랜잭션) 별로 제어하는 명령어를 말한다.

 

 

6 테이블

: RDB의 기본 단위, 데이터를 저장하는 객체, 칼럼과 행의 2차원 구조

용어	설명
테이블 (Table)	행과 칼럼의 2차원 구조를 가진 데이터의 저자 장소이며, 데이터베이스의 가장 기본적인 개념
칼럼/열 (Column)	2차원 구조를 가진 테이블에서 세로 방향으로 이루어진 하나하나의 특정 속성(더 이상 나눌 수 없는 특성)
로우/행 (Row)	2차원 구조를 가진 테이블에서 가로 방향으로 이루어진 연결되어 있는 데이터

 

 

7 ERD(Entity Relationship Diagram)

- 관계의 의미를 직관적으로 표현할 수 있는 수단

- 구성요소 : 엔터티(Entity), 관계(Relationship), 속성(Attribute) 3가지

- 표기법 : IE(Information Engineering) 표기법, Barker(Case Method) 표기법

 

 

 

 

#2 DDL (CREATE, ALTER, DROP, RENAME)

1 데이터의 유형

① 숫자 타입

- ANSi / ISO 기준

: Numeric, Decimal, Dec, Small Int, Integer, Int, Big int, Float, Real, Double Precision

 

- SQL Server / Sybase

: 작은 정수, 정수, 큰 정수, 실수 등 + Money, Small Money

 

- Oracle

: 숫자형 타입에 대해서 NUmber 한 가지 타입만 지원

 

- 벤더에서 ANSI / ISO 표준을 사용할 땐 기능을 중심으로 구현, 표준과 다른 용어 사용 허용

 

② 문자열 유형

- CHAR(고정길이) 와 VARCHAR(가변길이)의 차이

- CHAR 문자열 비교 = 공백을 채워서 비교

- VARCHAR에서 비교 = 맨 처음부터 한 문자씩 비교 (공백도 하나의 문자로 취급)

 

③ 테이블의 칼럼이 가지고 있는 대표적인 4가지 유형

데이터 유형	설명
CHARACTER(s)	- 고정 길이 문자열 정보(Oracle, SQL Server 모두 CHAR로 표현) - s는 기본 길이 1바이트, 최대 길이 Oracle : 2,000바이트, SQL : 8,000 바이트 - s만큼 최대 길이를 갖고 고정 길이를 가지고 있으므로 할당된 변수 값의 길이가 s보다 작을 경우에는 그 차이 길이만큼 공간으로 채워진다.
VARCHAR(s)	- CHARACTER VARYING의 약자로 가변 길이 문자열 정보(Oracle : VARCHAR2로, SQL : VARCHAR로 표현) - s 는 최고 실이 1바이트, 최대 길이 Oracle : 4,000바이트, SQL Server : 8,000 바이트 - s만큼의 최대 길이를 갖지만 가변 길이로 조정이 되기 때문에 할당된 변수값의 바이트만 적용된다.(Limit 개념)
NUMERIC	- 정수, 실수 등 숫자 정보(Oracle은 NUMBER로, SQL은 10가지 이상의 숫자 타입을 가지고 있음) - Oracle은 처음에 전체 자리 수를 지정하고, 그 다음 소수 부분의 자리 수를 지정한다. 예를들어, 정수 부분이 6자리이고 소수점 부분이 2자리인 경우에는 'NUMBER(8,2)'와 같이 된다.
DATETIME	- 날짜와 시각 정보(Oracle은 DATE로, SQL은 DATETIME으로 표현) - Oracle은 1초 단위, SQL은 3.33ms(millisecond) 단위로 관리

 

 

2 CREATE TABLE - 테이블 생성

• 명명 규칙 : 알파벳 -> 숫자 -> '_'(언더바) -> '$'(달러) -> '#'(샵)
• 테이블 생성시 대/소문자 구분은 하지 않는다. => 기본적으로 테이블이나 칼럼명 = 대문자로 표기한다.
• DATETIME 데이터 유형에는 별도로 크기를 지정하지 않는다.
• 문자 데이터 유형은 반드시 가질 수 있는 최대 길이를 표시
• 칼럼과 칼럼의 구분은 콤마로 하되, 마지막 칼럼은 콤마 X
• 칼럼에 대한 제약조건이 있으면 CONSTRAINT 이용하여 추가

CREATE TABLE 테이블 이름
(칼럼명1 DATETYPE [DEFAULT형식],
칼럼명1 DATETYPE [DEFAULT형식],
칼럼명1 DATETYPE [DEFAULT형식]);

CREATE TABLE PLAYER
(PLAYER_ID CHAR(7) NOT NULL, PLAYER_NAME VARCHAR(20) NOT NULL,
TEAM_ID CHAR(3) NOT NULL, E_PLAYER_NAME VARCHAR(40),
NICKNAME VARCHAR(30), JOIN_YYYY CHAR(4), POSITION VARCHAR(10),
BACK_NO TINYINT, NATION VARCHAR(20), BIRTH_DATE DATE, SOLAR CHAR(1),
HEIGHT SMALLINT, WEIGHT SMALLINT,
CONSTRAINT PLAYER_PK PRIMARY KEY(PLAYER_ID),
CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID));

 

 

3 제약조건 (CONSTRAINT)

: 데이터 무결성 유지 방법, 사용자가 원하는 조건의 데이터만 유지

 

- PK(Primary Key) : 한 테이블에 하나만 지정 가능 -> 자동으로 unique 인덱스 생성, Null 입력 불가(기본키 제약 = 고유키 & not null 제약)

- UNIQUE : NULL 가능, 행을 고유하게 식별하기 위한 고유키

- NOT NULL : NULL 값 입력 금지

- CHECK : 데이터 무결성을 유지하기 위한 테이블의 특정 칼럼에 설정하는 제약, 입려 할 수 있는 값의 범위 등을 제한, TRUE or FALSE 논리식을 지정

- FK (Foreign Key) : 참조 무결성 옵션 선택 사능, 여러개 가능

- NULL : 아직 정의되지 않은 값, 데이터 입력하지 못하는 경우 -> 알 수 없는 값, 0과 공백(" ")은 NULL이 아님 -> NULL은 IS NULL, IS NOT NULL로만 비교 가능(<>NULL != NULL 사용 불가)

 

구분	설명
PRIMARY KEY (기본키)	- 테이블에 저장된 행 데이터를 고유하게 식별하기 위한 기본키를 정의한다. 하니의 테이블에 하나의 기본키 제약만 정의할 수 있다. - 기본키 제약을 정의하면 DBMS는 자동으로 UNIQUE 인덱스를 생성하며, 기본키를 구성하는 칼럼에는 NULL을 입력할 수 없다. - 결국, '기본키 제약 = 고유키 제약 & NOT NULL 제약'이 된다.
UNIQUE KEY (고유키)	- 테이블에 저장된 행 데이터를 고유하게 식별하기 위한 고유키를 정의한다. 단, NILL은 고유키 제약의 대상이 아니므로, NULL 값을 가진 행이 여러 개가 있더라도 고유키 제약 위반이 되지 않는다.
FOREIGN KEY (외래키)	- 관계형 데이터베이스에서 테이블 간의 관계를 정의하기 위해 기본키를 다른 테이블의 외래키로 복사하는 경우 외래키가 생성된다. - 외래키 지정시 참조 무결성 제약 옵션을 선택할 수 있다.
NOT NULL	- NULL 값의 입력을 금지한다. 디폴트 상태에서는 모든 칼럼에서 NULL을 허가하고 있지만, 이 제약을 지정함으로써 해당 칼럼은 입력 필수가 된다. NOT NULL을 CHECK의 일부분으로 이해 할 수도 있다.
CHECK	- 입력 할 수 있는 값의 범위 등을 제한한다. CHECK 제약으로는 TRUE or FALSE로 평가할 수 있는 논리식을 지정한다.

 

 

4 생성된 테이블 구조 확인

Oracle : DESCRIBE 테이블명;(간략히 DESC 테이블명;)

SQL : sp_help 'dbo.테이블명'

 

 

5 SELECT 문장을 통한 테이블 생성 사례

① CREATE TABLE AS (CTAS)

- CREATE TABLE AS 문장을 사용하는 경우 테이블의 구조를 복사하므로 따로 작성 X

- 기본 테이블 제약조건 중 NOT NULL 제약 조건만 새로운 테이블에 복제됨

- 다른 제약조건(기본키, 고유키, 외래키, CHECK 등) dms qhrtkrk ehlwl dksgdktj rktl wjrdydgodi gksek.

- 계약 조건 추가하기 위해 ALTER TABLE 기능 사용

[Oracle] CREATE TABLE 테이블명B AS SELECT * FROM 테이블명A;

[예제] 선수(PLAYER) 테이블과 같은 내용으로 TEAM_TEMP라는 복사 테이블을 만들어 본다.

Oracle CREATE TABLE TEAM_TEMP AS SELECT TEAM;(1개 행이 영행을 받음)

 

② SQL 에서는 Select ~ Into ~ 를 활용하여 같은 결과

[SQL] SELECT * INTO 테이블명B FROM 테이블명A;

SQL SELECT * INTO TEAM_TEMP FROM TEAM;(1개 행이 영향을 받음)

 

 

6 ALTER TABLE

: 칼럼을 추가/삭제하거나 제약조건을 추가/삭제

 

ALTER TABLE PLAYER ADD ADDRESS VARCHAR(80);
ALTER TABLE PLAYER DROP COLUMN ADDRESS;
ALTER TABLE TEAM_TEMP ALTER COLUMN ORIG_YYYY VARCHAR(8) NOT NULL;

 

① ALTER TABLE + ADD COLUMN : 칼럼 추가

② ALTER TABLE + DROP COLUMN : 칼럼 삭제

③ ALTER TABLE + MODIFY : 칼럼 속성 변경

[Oracle] ALTER TABLE 테이블명 MODIFY(칼럼명 데이터유형 DEFAULT NOT NULL);

[SQL] ALTER TABLE 테이블명 ALTER COLUMN 칼럼명 데이터유형 DEFAULT NOT NULL;

 

 

7 DROP TABLE

: 테이블 제거

 

* ALTER TABLE 테이블명 DROP COLUMN 삭제할 칼럼명;

 

DROP TABLE PLAYER; -> (테이블 정부 삭제, 회복 불가)

ALTER TABLE PLATER DROP COLUMN ADDRESS; -> (테이블의 일부 칼럼 삭제, 회복 불가)

 

* DROP TABLE 테이블명 [CASCADE CONSTRAINT]; 제약조건명;

 

- DROP 명령어를 사용하면 테이블의 모든 데이터 및 구조를 삭제

- CASCADE CONSTRAINT 옵션은 해당 테이블과 관계가 있었던 참조되는 제약조건에 대해서도 삭제한다는 것을 의미

- SQL 에서는 CASCADE 옵션이 존재하지 않으며 테이블을 삭제하기 전에 참조하는 FK(FOREIGN KEY)제약 조건 또는 참조하는 테이블을 먼저 삭제

 

 

8 TRUNCATE TABLE

: 테이블 비우기

 

* TRUNCATE TABLE 테이블명 DROP COLUMN 삭제할 칼럼명;

 

- 테이블 삭제가 아닌 해당 테이블의 모든 행 제거 후 저장공간을 재사용하도록 

- 구조 자체 삭제는 DROP COLUMN 삭제할 칼럼명

 

 

9 DDL과 DML의 삭제

- DDL은 반드시 AUTO COMMIT이 일어남 -> DROP, TRUNCATE 원상복구 불가

- DML은 사용자가 COMMIT 해야함 -> DELETE 테이블 삭제해도 ROLLBACK으로 복구 가능

 

 

10 참조 동작

- Automatic : 자식 삽입 시 부모 테이블에 pk가 없으면 부모 pk 생성 후 자식에 삽입

- Dependent : 자식 삽입 시 부모 테이블에 pk가 존재할 때만 자식 삽입허용

- Cascade : 부모 삭제 시 자식 같이 삭제

- Restrict : 부모 삭제 시 테이블에 pk가 없는 경우에만 부모 삭제 허용

 

 

 

#3 DML (SELECT, INSERT, UPDATE, DELETE)

* DML(Data Manipulation Language) : 입력 / 수정 / 삭제 / 조회

 

1 DML

- 호스트 프로그램 속에 삽입되어 사용, 데이터 부속어라고도 한다

- Procedural DML (절차적 데이터 조작어) : 초급언어, 사용자가 무슨 데이터를 원하고 어떻게 접근해 처리할 것인지 명세해야한다.

- Nonprocedural DML (비절차적 데이터 조작어) : 고급언, 사용자가 무슨 데이터를 원하는지만 명세하고 어떻게 접근할 것인지는 하지 않는다. 선언적 언어하고도 함.

 

 

2 INSERT

: 데이터 입력 방법

 

INSERT INTO 테이블명 (COLUMN_LIST) VALUES (COLUMN_LIST에 넣을 VALUE_LIST);

INSERT INTO 테이블명 VALUES (전체 COLUMN에 넣을 VALUE_LIST);

 

PLAYER INSERT INTO PLAYER
(PLAYER_ID, PLAYER_NAME, TEAM_ID, POSITION, HEIGHT, WEIGHT, BACK_NO) -> 칼럼 리스트
VALUES ('2002007', '박지성', 'K07', 'MF', 178, 73, 7); -> 밸류 리스트

 

- INSERT INTO 테이블명 (칼럼리스트 / 생략 = 전체칼럼)

VALUES (칼럼명 순서에 맞춰 입력할 값 매핑해 작성);

- 데이터가 문자형일 경우 "로 묶어서 입력

 

 

3 UPDATE

:정보 수정

 

UPDATE 테이블명 SET 수정되어야 할 칼럼명 = 수정되기를 원하는 새로운 값;

 

UPDATE PLAYER SET BACK_NO = 99;
UPDATE PLAYER SET POSITION = 'MF'; -> 문자값인 경우 ' '사용

- UPDATE 테ㅣ블명

SET 칼럼명 = 값 WHERE 조건;

 

 

4 합성 연산자

: 문자와 문자를 연결하는 합성(CONCATENATION) 연산자

[Oracle] -> ||

[SQL] -> +

 

Oracle >>

SELECT PLAYER_NAME || '선수,' || HEIGHT || 'cm,' || WEIGHT || 'kg'체격정보 FROM PLAYER;

Sql >>

SELECT PLAYER_NAME + '선수,' + HEIGHT + 'cm,' + WEIGHT + 'kg'체격정보 FROM PLAYER;

 

=> 정경량선수 173cm, 65kg 정은익 선수, 176cm 63kg 레오마르선수, 183cm, 77kg 명재용 선수, 173cm, 63kg