SQL 이론 - 2과목 1장 (1)

1. SQL 기본

 

DB : 필요에 의해 데이터를 일정한 형태로 저장

DBMS : 데이터 관리, 데이터 손상 예방, 복구 SW

SQL : 관계형 DB에서 사용하는 언어

 

 

- DML : SELECT, INSERT, UPDATE, DELETE

- DDL : CREATE, ALTER, DROP, RENAME

- DCL : GRANT, REVOKE

- TCL : COMMIT, ROLLBACK

 

 

2. DDL

 

[ 데이터 유형(DATATYPE) ]

 

1) CHAR(s) : 고정 길이 문자열 정보. 최대 길이 (SQL, Oracle 둘다 사용)

                     ‘AA’ = ‘AA ’ (공백포함)

 

2) VARCHAR(s) : 가변 길이 문자열 정보 (SQL 경우)

                           할당된변수 값의 바이트만 적용 ‘AA’ ≠ ‘AA ’ (공백포함 X)

                           Oracle은 VARCHAR2(s)

 

3) NUMBER : 숫자 정보 (Oracle)

                      SQL은 10가지 이상의 숫자 타입 

 

4) DATETIME : 날짜와 시각 정보

                        Oracle은 DATA

 

 

- 테이블명, 칼럼명은 중복 X, 문자로 시작

 

- 칼럼은 , 로 구분되고 ; 로 끝난다.

 

- 칼럼 뒤에 데이터 유형은 꼭 지정

 

- A-Z, a-z, 0-9, _, $, #만 사용 가능

 

 

[ 제약조건 (데이터의 무결성 유지) ]

 

1) PRIMARY KEY(기본키)

: 1개 테이블에 1개 기본키만 제약

: 자동으로 UNIQUE 인덱스 생성, NULL 입력 X

 

2) UNIQUE KEY(고유키)

: 고유키 정의, NULL 입력 O

 

3) NOT NULL

: NULL 값 입력금지

 

4) CHECK

: 입력 값 범위 제한, 데이터 무결성 유지

 

5) FOREIGN KEY(외래키)

: NULL 가능, 여러 속성가능

 

 

[테이블 생성 CREATE]

 

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

 

(예제)

테이블명: PLAYER, 칼럼명: PLAYER_ID 문자 고정자릿수 7, PLAYER_NAME 문자 가변 자릿수 20

 

* SQL ↴

CREATE TABLE PLAYER ( PLAYER_ID CHAER(7) NOT NULL, PLAYER_NAME VARCHAR(20) NOT NULL);

 

* Oracle ↴ (VARCHAR 표기 차이)

CREATE TABLE PLAYER ( PLAYER_ID CHAER(7) NOT NULL, PLAYER_NAME VARCHAR2(20) NOT NULL);

 

* SQL 테이블 확인 -> sp_help ‘dbo.테이블명’

 

 

[테이블 구조 변경 ALTER]

 

ALTER TABLE (칼럼 추가/삭제, 제약조건 추가/삭제)

 

1) 칼럼 추가 : ALTER TABLE 테이블명 ADD 추가할 칼럼명 데이터 유형;

                     추가된 칼럼은 테이블 마지막 칼럼에 위치 (임의 위치지정 불가)

 

(예제)

PLAYER 테이블에 ADDRESS (가변 문자자릿수 80자리) 칼럼 추가

 

* SQL ↴

ALTER TABLE PLAYER ADD ADDRESS VARCHAR(80);

 

* Oracle ↴ (괄호, VARCHAR 표기 차이)

ALTER TABLE PLAYER ADD (ADDRESS VARCHAR2(80));

 

 

2) 칼럼 삭제 : ALTER TABLE 테이블명 DROP CALUMN 칼럼명;

                     데이터 유무 상관없이 삭제 가능, 한번에 1개 칼럼 삭제가능, 삭제 복구 불가,

                     삭제 후 테이블엔 1개 이상의 칼럼이 존재해야해

 

ALTER TABLE PLAYER DROP COLUMN ADDRESS; (SQL/Oracle 동일)

 

 

3) 칼럼 수정 : [SQL] ALTER TABLE 테이블명 ALTER 칼럼명1 데이터 유형 [DEFAULT 식] [NOT NULL],

                      칼럼명2 데이터 유형 …;

                    : [Oracle] ALTER TABLE 테이블명 MODIFY (칼럼명1 데이터 유형 [DEFAULT 식] [NOT NULL],

                      칼럼명2 데이터 유형 …);

 

(예제) TEAM 테이블의 ORIG_YYYY 칼럼 데이터 유형을 CHAR(4)→VARCHAR2(8)으로 변경

          향후 입력되는 데이터의 DEFAULT 값으로 '20020129'을 적용

          모든 행의 ORIG_YYYY 칼럼에 NULL이 없으므로 제약조건을 NULL → NOT NULL로 변경

 

* SQL ↴ (2줄로 변경/괄호 없어도 ok)

ALTER TABLE TEAM ALTER COLUMN ORIG_YYYY VARCAHR(8) NOT NULL;

ALTER TABLE TEAM ADD CONSTRAINT DF_ORIG_YYYY DEFAULT '20020129' FOR ORIG_YYYY;

 

* Oracle ↴(1줄로 해결가능)

ALTER TABLE TEAM MODIFY (ORIG_YYYY VARCHAR2(8) DEFAULT '20020129' NOT NULL);

 

 

4) 제약조건 추가 : ADD CONSTRAINT 조건명 조건 (칼럼명);

 

(예제) PLAYER 테이블에 TEAM 테이블과의 외래키 제약조건을 추가/ 제약조건명 = PLAYER_FK

          PLAYER 테이블의 TEAM_ID 칼럼이 TEAM 테이블의 TEAM_ID를 참조하는 조건

 

* SQL/Oracle ↴

ALTER TABLE PLAYER ADD CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID);

 

 

5) 제약조건 삭제 : DROP CONSTRAINT 조건명;

 

* SQL/Oracle ↴

ALTER TABLE PLAYER DROP CONSTRAINT PLAYER_FK;

 

 

[테이블명 변경 / 컬럼명 변경 RENAME]

 

: [Oracle] RENAME 변경 전 테이블명 TO 변경 후 테이블명; / RENAME COLUMN 전이름 TO 새이름

: [SQL] sp_rename ‘dbo.전 테이블명‘, ’변경 후 테이블명’; / sp_rename ‘dbo.전이름’,‘후이름’,‘CALUMN’

 

(예제) TEAM 테이블명을 다른 이름으로 변경/ 다시 TEAM 테이블로 변경

 

* Oracle ↴ * SQL ↴

RENAME TEAM TO TEAM_BACKUP; sp_rename 'dbo.TEAM','TEAM_BACKUP';

RENAME TEAM_BACKUP TO TEAM;

 

 

[테이블/ 테이블 데이터 삭제 DROP]

 

: DROP TABLE 테이블명; / TRUNCATE TABLE 테이블명; (SQL=Oracle)

 

 

2. DML

 

: DDL 명령어의 경우 실행 시 즉시 AUTO COMMIT, 하지만 DML의 경우 실시간은 X, COMMIT을 입력해

  트래잭션 종료

 

 

[데이터 입력 INSERT]

 

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

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

 

 

[데이터 수정 UPDATE]

 

: UPDATE 테이블명 SET 수정될 칼럼명 = 수정할 새로운 값;

 

 

[데이터 삭제 DELETE]

 

: DELETE [FROM] 삭제를 원하는 정보가 들어있는 테이블명;

 

 

[데이터 조회 SELCET]

 

: SELECT 조회하길 원하는 칼럼명 1, 2, 3 … FROM 해당 칼럼이 존재하는 테이블명;

 

- DISTINCT 옵션 : 중복되는 데이터를 한번만 출력

                              SELECT DISTINCT POSITION FROM PLAYER;

 

- 와일드카드 :  *  = 모든

                        % = 모든

                         -  = 한 글자

 

- 합성 연산자 : 문자와 문자 연결 = || (Oracle) , + (SQL)

 

- ALIAS : ALIAS(칼럼 별명)는 칼럼명 AS 바로 뒤에 붙여 (꼭 사용안해도 된다)

               SELECT PLAYER_NAME AS 선수명 FROM PLAYER;

             : 별명에 공백이 들어갈 경우 “”를 사용

 

 

3. TCL

 

- 트랜잭션 : 밀접히 관련되어 분리될 수 없는 1개 이상의 DB 조작. 논리적 연산단위

 

- COMMIT : 올바르게 반영된 데이터를 DB에 반영

 

- ROLLBACK : 트랜잭션 시작 이전의 상태로 복구, COMMIT 되지 않은 모든 트랜잭션 롤백함

 

- SAVEPOINT : 저장 지점

 

 

[ 트랜잭션의 특성 ] (원-일-고-지)

 

1) 원자성 : 트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않아야함

 

2) 일관성 : 트랜잭션 실행 전 DB 내용이 잘못되지 않으면 실행 후도 잘못되지 않음

 

3) 고립성 : 트랜잭션 실행 도중 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들면 X

 

4) 지속성 : 트랜잭션이 성공적으로 수행되면 DB 내용은 영구적으로 저장

 

* Oracle ↴

SAVEPOINT SVPT1;

ROLLBACK TO SVPT1;

COMMIT

 

* SQL ↴

SAVE TRAN SVPT1;

ROLLBACK TRAN SVPT1;

COMMIT;

 

 

4. 트랜잭션 격리성이 낮은 경우 문제점

- Dirty Read
: 다른 트랜잭션에 의해 수정됐지만 아직 커밋되지 않은 데이터를 읽는 것

- Non-Repeatable Read
: 한 트랜잭션에서 같은 쿼리를 두 번 수행 했는데, 그 사이 다른 트랜잭션의 값이 수정/삭제되어 두쿼리 결과가
  다르게 나타나는 현상

- Phantom Read
: 한 트랜잭션에서 같은 쿼리를 두 번 수행했는데, 첫 번째 쿼리에서 없던 유령 레코드가 두번째 쿼리에서
  나타나는 현상

 

** 참고사이트 **

https://dataonair.or.kr/db-tech-reference/d-guide/sql/?pageid=4&mod=list