[SQLD] 2과목_1장. SQL 기본

제 1절. 관계형 데이터베이스 개요

1) SQL 명령어 종류

: 관계형 데이터베이스에 대한 전담 접속 용도로 사용되며 독립되어 있음

 

- DML (데이터 조작어)

:  SELECT (=RETRIEVE), INSERT, UPDATE, DELETE  -> SIDU(시더)

 

- DDL (데이터 정의어) 

: CREATE, ALTER, DROP, RENAME (생성, 변경, 삭제, 이름변환) -> CARD(카드)

- DCL (데이터 제어어)

: GRANT, REVOKE (권한을 주고 회수하는 명령어)

 

- TCL (트랜잭션 제어어)

: COMMIT, ROLLBACK (트랜잭션 졀로 에어하는 명령어) 

 

제 2절. DDL

1) 데이터 유형

- CHAR(s) : 고정 길이 문자열 정보 ‘AA’ = ‘AA ’

- VARCHAR(s) : 가변 길이 문자열 정보 ‘AA’ != ‘AA ’

- NUMERIC : 정수, 실수 등 숫자 정보

- DATETIME : 날짜와 시각 정보

 

2) CREATE TABLE

# 테이블 생성

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

- 테이블 생성 시 주의사항

• 테이블명은 객체를 의미할 수 있는 적절한 이름을 사용한다. 가능한 단수형을 권고한다.
• 테이 블명은 다른 테이블의 이름과 중복되지 않아야 한다.
• 한 테이블 내에서는 칼럼명이 중복되게 지정될 수 없다.
• 테이블 이름을 지정하고 각 칼럼들은 괄호 "( )" 로 묶어 지정한다.
• 각 칼럼들은 콤마 ","로 구분되고, 테이블 생성문의 끝은 항상 세미콜론 ";"으로 끝난다.
• 칼럼에 대해서는 다른 테이블까지 고려하여 데이터베이스 내에서는 일관성 있게 사용하는 것이 좋다.
• 칼럼 뒤에 데이터 유형은 꼭 지정되어야 한다. - 테이블명과 칼럼명은 반드시 문자로 시작해 야 하고, 벤더별로 길이에 대한 한계가 있다.
• 벤더에서 사전에 정의한 예약어(Reserved word)는 쓸 수 없다.
• A-Z, a-z, 0-9, _, $, # 문자만 허용된다.

 

- 제약조건

• PRIMARY KEY(기본키) : 기본키 정의
• UNIQUE KEY(고유키) : 고유키 정의
• NOT NULL : NULL 값 입력금지
• CHECK : 입력 값 범위 제한
• FOREIGN KEY(외래키) : 외래키 정의

3) ALTER TABLE

- 컬럼 추가, 삭제

ALTER TABLE 테이블명
ADD 칼럼명 데이터 유형;

ALTER TABLE 테이블명
DROP COLUMN 칼럼명;

- 컬럼 데이터 유형, 조건, 이름 변경

# Oracle
ALTER TABLE 테이블명 MODIFY (칼럼명1 데이터 유형 [DEFAULT 식] [NOT NULL], 칼럼명2 데이터 유형 …);

# SQL Server 
ALTER TABLE 테이블명 ALTER (칼럼명1 데이터 유형 [DEFAULT 식] [NOT NULL], 칼럼명2 데이터 유형 …);


## 컬럼 이름 변경
# Oracle
ALTER TABLE 테이블명 RENAME COLUMN 변경해야 할 칼럼명 TO 새로운 칼럼명;

# SQL Server
sp_rename 변경해야 할 칼럼명, 새로운 칼럼명, 'COLUMN';

- 제약 조건 추가, 삭제

### 제약 조건 추가
ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건 (칼럼명);

## [예제] PLAYER 테이블에 TEAM 테이블과의 외래키 제약조건을 추가한다. 제약조건명은
## PLAYER_FK 로 하고, PLAYER 테이블의 TEAM_ID 칼럼이 TEAM 테이블의 TEAM_ID 를 참조하
## 는 조건이다.
# Oracle 
ALTER TABLE PLAYER ADD CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID)
REFERENCES TEAM(TEAM_ID);

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

# -> TRAM이라는 테이블을 삭제하려고 하면 외래키에 의해 참조되고 있기 때문에 에러가 뜸

### 제약 조건 삭제
ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;

##[예제] PLAYER 테이블의 외래키 제약조건을 삭제한다.
# Oracle 
ALTER TABLE PLAYER DROP CONSTRAINT PLAYER_FK;

# SQL Server
Server ALTER TABLE PLAYER DROP CONSTRAINT PLAYER_FK;

4) RENAME TABLE

# Oracle
RENAME 변경전 테이블명 TO 변경후 테이블명;

# SQL Server
sp_rename 변경전 테이블명, 변경후 테이블명;

5) DROP TABLE

DROP TABLE 테이블명 [CASCADE CONSTRAINT];

#  CASCADE CONSTRAINT 옵션은 해당 테이블과 관계가 있었던 참조되는 제약조건에 대해서도 
# 삭제한다는 것을 의미한다. SQL Server 에서는 CASCADE 옵션이 존재하지 않으며 
# 테이블을 삭제하기 전에 참조하는 FOREIGNKEY 제약 조건 또는 참조하는 테이블을 먼저 삭제해야 한다.

6) TRUNCATE TABLE

TRUNCATE TABLE 테이블명;

# -> TRUNCATE는 테이블의 행만 삭제하고 구조는 남겨둠 (= DELETE)
# 아예 삭제하고 싶으면 DROP 이용

 

제 3절. DML

1) INSERT

: 테이블에 데이터를 입력하는 방법은 두 가지 유형이 있으며 한 번에 한 건만 입력된다.

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

2) UPDATE

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

# [예제] 선수 테이블의 백넘버를 일괄적으로 99 로 수정해라
UPDATE PLAYER SET BACK_NO = 99;

3) DELETE

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

# [예제] 선수 테이블의 데이터를 전부 삭제한다.
DELETE FROM PLAYER;

* DDL 명령어의 경우 실행시 AUTO COMMIT 하지만 DML의 경우 COMMIT을 입력해야 한다.

단, SQL Server의 경우 DML도 AUTO COMMIT

 

4) SELECT

SELECT [ALL/DISTINCT] 보고 싶은 칼럼명, 보고 싶은 칼럼명, … FROM 해당 칼럼들이 있는 테이블명;

# 와일드카드
# * : 모든
# % : 모든
# - : 한 글자

 

제 4절. TCL

1) 트랜잭션 개요

: 밀접히 관련되어 분리될 수 없는 1개 이상 의 DB 조작

 

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

- ROLLBACK : 트랜잭션 시작 이전의 상태로 되돌림

- SAVEPOINT : 저장 지점

 

2) 트랜잭션의 특성

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

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

3. 고립성 : 트랜잭션 실행 도중 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다.

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

 

제 6절. 함수

1) 문자형 함수

- LOWER : 문자열을 소문자로

- UPPER : 문자열을 대문자로

- ASCII : 문자의 ASCII 값 반환

- CHR/CHAR : ASCII 값에 해당하는 문자 반환

- CONCAT : 문자열1, 2를 연결

- SUBSTR/SUBSTRING : 문자열 중 m 위치에서 n개 의 문자 반환

- LENGTH/LEN : 문자열 길이를 숫자 값으로 반환

CONCAT(‘RDBMS’,‘ SQL’) # -> ‘RDBMS SQL’
SUBSTR(‘SQL Expert’,5,3) # -> ‘Exp’
LTRIM(‘xxxYYZZxYZ’,‘x’) # -> ‘YYZZxYZ’
RTRIM(‘XXYYzzXYzz’,‘z’) # -> ‘XXYYzzXY’
TRIM(‘x’ FROM ‘xxYYZZxYZxx’) # -> ‘YYZZxYZ

2) 숫자형 함수

- SIGN(숫자) : 숫자가 양수면1 음수면-1 0이면 0 반환

- MOD(숫자1, 숫자2) : 숫자1을 숫자2로 나누어 나머지 반환

- CEIL/CEILING(숫자) : 크거나 같은 최소 정수 반환

- FLOOR(숫자) : 작거나 같은 최대 정수 리턴

- ROUND(숫자[, m]) : 숫자를 소수점 m자리에서 잘라서 버린다.

- TRUNC(숫자[, m]) : 숫자를 소수 m자리에서 잘라서 버린다. (SQL Server에서는 제공 X)

 

3) 날짜형 함수

- SYSDATE/GETDATE() : 현재날짜와 시각 출력

- EXTRACT/DATEPART('YEAR' | 'MONTH' | ' DAY' from d) : 날짜에서년/월/일 데이터 출력

- TO_NUMBER(TO_CHAR(d,‘YYYY’))/YEAR(d) : 날짜에서년/월/일 데이터 출력

 

4) CASE 표현

: IF-THEN-ELSE 논리와 유사한 방식으로 표현식을 작성해서 SQL 의 비교 연산 기능을 보완하는 역할을 한다

SELECT ENAME,
 CASE WHEN SAL >=3000 THEN ‘HIGH’
 	WHEN SAL >=1000 THEN ‘MID’
 	ELSE ‘LOW’
 END AS SALARY_GRADE
FROM EMP;

# SEARCHED_CASE_EXPRESSION
CASE WHEN LOC = ‘a’ THEN ‘b’

# SIMPLE_CASE_EXPRESSION
CASE LOC WHEN ‘a’ THEN ‘b’

# -> 위의 두 문장은 같은 의미

5) NULL 관련 함수

- NVL(식1,식2)/ISNULL(식1,식2) : 식1의 값이 NULL 이면 식2 출력

- NULLIF(식1,식2) : 식1이 식2와 같으면 NULL을 아 니면 식1을 출력

- COALESCE(식1,식2) : 임의의 개수표현식에서 NULL 이 아닌 최초의 표현식, 모두 NULL이면 NULL 반환

COALESCE(NULL,NULL,‘abc’) # -> ‘abc’

 

제 7절. GROUP BY, HAVING 절 

1) 집계 함수

- 여러 행들의 그룹이 모여서 그룹당 단 하나의 결과 를 돌려주는 함수이다.

- GROUP BY 절은 행들을 소그룹화 한다.

- SELECT, HAVING, ORDER BY 절에 사용 가능

- ALL : Default 옵션

- DISTINCT : 같은 값을 하나의 데이터로 간주 옵션

• COUNT(*) : NULL 포함 행의 수
• COUNT(표현식) : NULL 제외 행의 수
• SUM, AVG : NULL 제외 합계, 평균 연산
• STDDEV : 표준 편차
• VARIAN : 분산
• MAX, MIN : 최대값, 최소값

2) GROUP BY, HAVING 절의 특징

- GROUP BY 절을 통해 소그룹별 기준을 정한 후, SELECT 절에 집계 함수를 사용한다.

- 집계 함수의 통계 정보는 NULL 값을 가진 행을 제외하고 수행한다.

- GROUP BY 절에서는 ALIAS 사용 불가

- 집계 함수는 WHERE 절에 올 수 없다.

- HAVING 절에는 집계함수를 이용하여 조건 표시o

- HAVING 절은 일반적으로 GROUP BY 뒤에 위치

 

제 8절. ORDER BY 절

1) ORDER BY 특징

- SQL 문장으로 조회된 데이터들을 다양한 목적에 맞게 특정한 칼럼을 기준으로 정렬하여 출력하는데 사용한다.

- ORDER BY 절에 칼럼명 대신 ALIAS 명이나 칼럼 순서를 나타내는 정수도 사용 가능하다.

- DEFAULT 값으로 오름차순(ASC)이 적용되며 DESC 옵션을 통해 내림차순으로 정렬이 가능하다.

- SQL 문장의 제일 마지막에 위치한다.

- SELECT 절에서 정의하지 않은 칼럼 사용 가능.

- Oracle에서는 NULL을 가장 큰 값으로 취급하며 SQL Server에서는 NULL을 가장 작은 값으로 취급한다.

 

2) SELECT 문장 실행 순서

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

 

SELECT TOP(2) WITH TIES ENAME, SAL
FROM EMP
ORDER BY SAL DESC;

# 위는 급여가 높은 2명을 내림차순으로 출력하는데 같은 급여를 받는 사원은 같이 출력한다(WITH TIES)

 

제 9절. 조인(JOIN) 

1) JOIN

- 두 개 이상의 테이블들을 연결 또는 결합하여 데이터를 출력하는 것

- 일반적으로 행들은 PK나 FK 값의 연관에 의해 JOIN 이 성립된다.

- 어떤 경우에는 PK, FK 관계가 없어도 논리적인 값들의 연관만으로 JOIN이 성립가능하다.

- 5가지 테이블을 JOIN 하기 위해서는 최소 4번의 JOIN 과정이 필요하다. (N-1)

 

2) EQUI JOIN

: 2 개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하는 경우에 사용, 대부분 PK, FK의 관계를 기반으로 한다.

SELECT 테이블1.칼럼명, 테이블2.칼럼명, ... 
FROM 테이블1, 테이블2 
WHERE 테이블1.칼럼명1 = 테이블2.칼럼명2; -> where절에 조인 조건을 넣는다.

3) Non EQUI JOIN

: 2개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하지 않는 경우에 사용

‘=’ 연산자가 아닌 BETWEEN, >, <= 등 연산자 사용

SELECT E.ENAME, E.JOB, E.SAL, S.GRADE
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HSAL;
# ->위는 E의 SAL의 값을 S의 LOSAL과 HSAL 범위에서 찾는 것이다.