[2과목] SQL 기본

😸 SQL 문장의 종류

1) 데이터 조작어 (Data Manipulation Language)

2) 데이터 정의어 (Data Definition Language)

3) 데이터 제어어 (Data Control Language)

4) 트랜잭션 제어어 (Transaction Control Language)

 * 트랜잭션? 데이터베이스의 논리적 연산단위로 분리될 수 없는 1개 이상의 DB 조작

 

👉 데이터 조작어 (DML)

- 데이터의 입력/수정/삭제/조회를 담당

- INSERT/UPDATE/DELETE/SELECT

• SELECT 사용법

SELECT [ALL | DISTINCT] 컬럼명 AS 별명
FROM 테이블명;

 

• INSERT 사용법

INSERT INTO 테이블명(컬럼명) VALUES (입력값);

ex) INSERT INTO EMP(EMP_ID, EMP_NAME) VALUES (101, '김철수');

      INSERT INTO EMP VALUES (102, '홍길동', 10, 101, 400000);    => 모든 컬럼 대상

 

• UPDATE 사용법

UPDATE 테이블명 SET 컬럼명 = 입력값 WHERE 조건;

ex) UPDATE EMP SET DEPT_ID = 10, SALARY = 4500000 WHERE EMP_ID = 101;

 

• DELETE 사용법

DELETE [FROM] 테이블명 WHERE 조건;

ex) DELETE FROM EMP WHERE EMP_ID = 101;

 

 

• Delete Action 명령어 (부서 - 사원)

- Cascade : 부모테이블 삭제 시 자식테이블도 삭제됨

- Set Null : 부모테이블 삭제 시 자식테이블의 해당 필드 Null 값으로 처리

- Set Default : 부모테이블 삭제 시 자식테이블의 해당 필드 기본값으로 처리

- Restrict : 자식테이블에 PK값이 없는 경우만 부모테이블 삭제 허용

- No Action : 참조무결성을 위반하는 삭제/수정 액션을 취하지 않음

 

• Insert Action 명령어 (부서 - 사원)

- Automatic : 부모테이블에 PK가 없는 경우 부모테이블에 PK 생성 후 자식테이블에 입력

- Set Null : 부모테이블에 PK가 없는 경우 자식테이블의 FK를 Null 값으로 처리

- Set Default : 부모테이블에 PK가 없는 경우 자식테이블의 FK를 기본값으로 처리

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

- No Action : 참조무결성을 위반하는 입력 액션을 취하지 않음

 

👉 데이터 정의어 (DDL)

- 데이터베이스 생성 및 테이블 생성/구조 변경/삭제/이름 변경을 담당

-  CREATE/ALTER/DROP/RENAME

• CREATE 사용법

CREATE TABLE 테이블명 (컬럼명 데이터 유형 [NOT NULL], ...)
CONSTRAINT 기본키명 PRIMARY KEY (컬럼명) 
CONSTRAINT 고유키명 UNIQUE (컬럼명[ 컬럼명,...],)

ex) CREATE TABLE EMP ( EMP_ID  CHAR(8) NOT NULL,

                                            EMP_NAME VARCHAR2(20) NOT NULL,

                                            DEPT_ID  CHAR(4)

                                            BIRTH_DATE DATE

                                            CONSTRAINT EMP_PK PRIMARY KEY(EMP_ID));

 

• ALTER 사용법

- ADD(컬럼 추가), MODIFY(컬럼 수정), DROP COLUMN(컬럼 삭제), RENAME COLUMN(컬럼명 수정)

- DROP CONSTRAINT(제약조건 삭제), ADD CONSTRAINT(제약조건 추가)

# ADD, MODIFY
ALTER TABLE 테이블명 (ADD, MODIFY) 컬럼명 데이터 유형 [기본값] [NOT NULL];

# DROP, ADD CONSTRAINT
ALTER TALBE 테이블명 (DROP COLUMN, DROP CONSTRAINT, ADD CONSTRAINT) (제약조건) 컬럼명;

# RENAME COLUMN
ALTER TABLE 테이블명 RENAME COLUMN 기존 컬럼명 TO 새로운 컬럼명;

• DROP 

- 테이블 내 데이터와 구조를 삭제

- Cascade constraint 옵션은 종속된 제약조건도 모두 삭제

ex) DROP TABLE 테이블명 (CASCADE CONSTRAINT);

 

• TRUNCATE

- 테이블의 구조는 남겨두고 내부의 데이터, 행만 삭제

- 테이블이 차지하던 저장공간을 반납

 

👉 데이터 제어어 (DCL)

- 데이터베이스에 접근하고 객체들을 사용하도록 권한을 주고 회수 담당

- GRANT(권한 부여), REVOKE(회수)

• GRANT

GRANT 시스템 권한 TO 사용자 [WITH ADMIN OPTION];

# 오브젝트 권한 부여
GRANT 오브젝트 권한 ON 오브젝트 TO 사용자 [WITH GRANT OPTION];

 

• REVOKE

REVOKE 시스템 권한 FROM 사용자

# 오프젝트 권한 취소
REVOKE 오브젝트 권한 ON 오브젝트 FROM 사용자

 

👉 트랜잭션 제어어 (TCL)

- 실행 여부를 결정하는 제어어

- COMMIT(승인), ROLLBACK(취소), SAVEPOINT(트랜잭션을 분할하여 저장)

 

• 트랜잭션의 특징

1) 원자성

 : 트랜잭션을 통해 정의된 연산은 실행 완료, 실행 미완료인지로만 남아야 한다.

2) 일관성

 : 트랜잭션 실행 전과 실행 후 DB 내용이 같아야 한다. 

3) 고립성(격리성)

 : 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들면 안된다.

4) 지속성

 : 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 DB내용은 영구적으로 저장된다.

 

• 트랜잭션의 고립성이 낮을 때 발생하는 문제

1) Dirty Read 

 : 다른 트랜잭션에 의해 수정되었음에도 불구하고 COMMIT되지 않은 데이터를 읽는 것

2) Non-Repeatable Read

 : 한 트랜잭션 내에서 같은 쿼리를 두 번 수정했는데, 그 사이 다른 트랜잭션이 값을 수정하거나 삭제하여 두 쿼리 결과가       다르게 나는 현상

3) Phantom Read

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

 

• 트랜잭션 예시

# COMMIT
INSERT INTO EMP VALUES (102, '홍길동', 20, 101, 'SQL 개발자', '고급', 4000000);
COMMIT;

# ROLLBACK
UPDATE EMP SET EMP_NAME = '이순신' WHERE EMP_NAME ='홍길동';
ROLLBACK;

# SAVEPOINT
SAVEPOINT SP1;
INSERT INTO EMP VALUES (102, '홍길동', 20, 101, 'SQL 개발자', '고급', 400000):
SAVEPOINT SP2;
UPDATE EMP SET EMP_NAME = '이순신' WHERE EMP_NAME = '홍길동';
ROLLBACK TO SP2;   # ROLLBACK 명령어로 SP2 지점까지 롤백

• SAVE POINT 예시

 

-  테이블명 : Category

상품ID	상품명
001	TV

CREATE TABLE Category
(
   PRODUCT_ID VARCHAR(4) NOT NULL,
   PRODUCT_NAME VARCHAR(20) NOT NULL
);

BEGIN TRANSACTION;
SAVE TRANSACTION SP1;
UPDATE Category SET PRODUCT_NAME = 'LCD-TV' WHERE PRODUCT_ID = '001';
SAVE TRANSACTION SP2;
UPDATE Category SET PRODUCT_NAME = '평면 TV' WHERE PRODUCT_ID = '001';
ROLLBACK TRANSACTION SP2;
COMMIT;

# 001은 LCD-TV

- SAVEPOINT로 인해 SP2부터 ROLLBACK까지 코드 무시 (주석처리와 같음)

 

👉 함수

• 숫자함수

- ABS(n) : n의 절대값을 반환

- CEIL(n)/CEILING : n보다 같거나 큰 정수를 반환 

- FLOOR(n) : n보다 작거나 같은 정수를 반환 

- ROUND(n, i) : n을 소숫점 i+1번째 자리에서 반올림

- TRUNC(n,i) : n을 소숫점 i+1번째 자리에서 버림

 

• 문자함수

- LOWER : 소문자로 변환

- UPPER : 대문자로 변환

- TRIM, LTRIM, RTRIM : 양쪽 공백 제거, 왼쪽 공백 제거, 오른쪽 공백 제거

- LPAD, RPAD(char1, n, char2) : 특정 자리를 정하고, 왼쪽/오른쪽의 공백을 채워주는 함수

                          ex) SELETE LPAD('A', 5, '*') FROM DUAL;

                                 -> ****A 출력

- SUBSTR(char, pos, len) : char의 pos번째 문자부터 len만큼 잘라서 반환 (문자열 자르기)

                                        ex) SELECT SUBSTR('KOREA', 2, 2) FROM DUAL;

                                              -> OR 출력

- INSTR (char1, char2): char1에서 char2를 찾은 후 첫 번째 자리가 char1에서 몇 번째인지 출력

                                    ex) SELECT INSTR('CORPORATE FLOOR', 'PO') AS IDX FROM DUAL;

                                          -> 4 출력

 

• 단일행함수의 일반함수

- NVL(인수1, 인수2) : NULL이 있을 때 처리하는 함수

                                    인수1이 NULL이면 인수2를 반환하는 함수. 인수1이 NULL이 아니면 인수1

- NVL(인수1, 인수2, 인수3) : 인수1이 NULL이 아니면 인수2를 실행하고 인수1이 NULL이면 인수3 실행

- NULLIF(인수1, 인수2) : 인수1과 인수2가 같으면 NULL 값으로 출력. 같지 않으면 인수1로 출력

- COALESCE(인수1, 인수2,.. 인수N) : 계속적으로 들어오는 값이 NULL값이었다가 NULL이 아닌 값을 출력

 

• 그룹함수 (NULL 값을 포함하지 않는다.)

- COUNT() : 조건을 만족하는 데이터값들의 개수를 출력

- SUM() : 조건을 만족하는 데이터 값들의 합을 출력

- AVG() : 조건을 만족하는 데이터 값들의 평균을 출력

- MAX(), MIN() : 조건을 만족하는 데이터 값들 중 최대값/최소값을 출력

- STDDEV() : 조건을 만족하는 데이터 값들의 표준편차를 출력

- VARIANCE() : 조건을 만족하는 데이터 값들의 분산값을 출력

 

• 날짜 함수

- SYSDATE : 현재 날짜와 시간 반환

- EXTRACT (YEAR| MONTH| DAY FROM 날짜) : 날짜 데이터에서 년, 월, 일 데이터를 반환

ex) SELECT SYSDATE AS "SYSDATE" EXTRACT (YEAR FROM SYSDATE) AS "YEAR",

                                                                EXTRACT (MONTH FROM SYSDATE) AS "MONTH"

                                                                EXTRACT (DAY FROM SYSDATE) AS "DAY" FROM DUAL;

 -> SYSDATE 가 21/03/22 일 경우, YEAR 2021, MONTH 3, DAY 22

 

• 형 변환 함수

- TO_NUMBER(char) : char를 숫자로 변환

- TO_CHAR(숫자 | 날짜[,포맷]) : 숫자나 날짜를 포맷에 맞는 문자열로 변환

- TO_DATE(char[, 포맷]) : char를 포맷에 맞는 날짜 타입으로 변환

ex) SELECT TO_NUMBER('10') AS "A",

                     TO_CHAR(1000, '9,999') AS "B",

                     TO_CHAR(SYSDATE,"YYYY-MM-DD") AS "C",

                     TO_DATE('2021-03', "YYYY-MM") AS "D" FROM DUAL;

-> A 10, B 1,000 , C 2021-03-22, D 21/03/01

 

👉 조건문

•  DECODE

- IF문처럼 사용 가능

- 한 컬럼에서 여러 조건을 적용하여 출력 시 사용

  ex) SELECT DECODE ( EMPNO, 1000, 'TRUE' 'FALSE') 

        -> EMPNO에서 1000이면 TRUE 아니면 FALSE 출력

 

•  CASE

- IF~THEN~ELSE-END문 처럼 사용 가능

- WHEN구에 조건을 사용하며, 조건이 참인 경우 THEN구를 실행, 거짓인 경우 ELSE구를 실행

   ex) SELECT CASE WHEN EMPNO = 1000 THEN 'A' WHEN EMPNO = 1001 THEN 'B' ELSE 'C' END;

        (= SELECT CASE EMPNO WHEN 1000 THEN 'A' WHEN 1001 THEN 'B' ELSE 'C' END; )

           -> EMPNO가 1000인 경우 'A' , 1001인 경우 'B', 그 외는 'C'를 출력

 

- ORDER BY CASE 문법

SELETE 컬럼명 FROM 테이블명
ORDER BY (CASE 컬럼명 WHENE 조건A, THEN 1
					  WHEN 조건B, THEN 2 ELSE 3 END):

ex) SELECT DEPT_ID, SALARY FROM EMP ORDER BY (CASE DEPT_ID WHEN 30 THEN 1 ELSE 2 END),

      SALARY DESC;

      -> EMP에서 첫 번째 정렬 조건으로 DEPT_ID가 30이면 첫 번째 순서로 정렬하여 출력 SALARY는 내림차순으로 정렬

 

👉  조인문

• EQUI JOIN

- 두 테이블 간 컬럼 값이 서로 정확하게 일치할 경우 사용

ex) SELECT EMP.EMP_NAME, DEPT.DEPT_NAME FROM EMP, DEPT WHERE EMP.DEPT_ID = DEPT.DEPT_ID;

 

• NON EQUI JOIN

- 두 테이블 간 컬럼 값이 서로 다를 경우 사용

ex) SELECT EMP.EMP_NAME, EMP.SALARY, SALGRD.GRADE FROM EMP, SALGRD

      WHERE EMP.SALARY BETWEEN SALGRD.LOSAL AND SALGRD.HISAL;

 

 

 

👉 제약조건과 PK / FK

• NULL 

- 모르는 값, 정의되지 않은 값

- 산술 연산에서 NULL이 있을 경우 NULL이 출력

- 공백이나 0과는 다름

- 조건절에 NULL이 들어갈 경우 FAlSE를 반환

- 정렬 시 Oracle에서는 가장 큰 값으로 인식하고, SQL Server에서는 가장 작은 값으로 인식

#SQL Server 기준
CREATE TABLE PRODUCT
(
    PROD_ID VARCHAR2(10) NOT NULL,
    PROD_NM VARCHAR2(20) NOT NULL,
    REG_DT DATE NOT NULL,
    REGR_NO NUMBER(10) NULL
);

ALTER TABLE PRODUCT ADD CONSTRAINT PRODUCT_PK PRIMARY KEY (PROD_ID);

#Oracle 기준 

CREATE TABLE PRODUCT
(
    PROD_ID VARCHAR2(10) NOT NULL,
    PROD_NM VARCHAR2(20) NOT NULL,
    REG_DT DATE NOT NULL,
    REGR_NO NUMBER(10) NULL,   #NULL은 생략가능, NOT NULL 생략불가 
 );
 
 CONSTRAINT PRODUCT_PK PRIMARY KEY (PROD_ID);

- 제약 조건 추가 : CREATE 밖일 때 ALTER (테이블명) ADD CONSTRAINT (테이블명_PK) PRIMARY KEY (컬럼명);

                             CREATE 안일 때 CONSTRAINT (테이블명_PK) PRIMARY KEY (컬럼명);

 

• 불필요한 컬럼 삭제

ALTER TABLE
		(테이블명)
DROP COLUNM
		(삭제할 컬럼명);

 

• 기본키(PK) / 외래키 (FK)

1) 기본키(PK)

- 테이블당 1개만 생성 가능

- 단일 컬럼, 여러개의 컬럼으로 구성 가능

- 기본키 제약을 정의하면 DBMS가 자동으로 UNIQUE 인덱스 생성

- 기본키 컬럼은 NULL 입력 불가

- 개채 무결성 

 

2) 외래키 (FK)

- 외래키로 테이블당 여러 개 생성 가능

- 테이블 생성 시 설정 가능

- NULL 값을 가질 수 있음

- 참조 무결성 제약을 받을 수 있음

 

3) 고유키 (UNIQUE) 

- 개체 무결성

- 중복 불가

- NULL 값을 가질 수 있음

 

* 삭제 명령어

1) DELETE(DML)

- 데이터를 삭제

- 로그(삭제기록)가 남음 => Commit 이전 Rollback 가능

- 사용자 Commit

2) DROP(DDL)

- 테이블 정의 자체를 삭제

- 로그가 남지 않음 => Rollback 불가능

- Auto Commit 

3) TRUNCATE

- 초기화 상태 (디폴트 설정. 로그가 남지 않음)

-Auto Commit

 

 

• VIEW

- 실제 데이터를 가지고 있지 않고 SELECT SQL만 가지고 있음

- 참조된 테이블이 변경되면 뷰의 결과도 변경됨

- 독립성(테이블 구조가 변경되어도 응용프로그램 변경하지 않아도 됨), 편리성, 보안성의 장점을 가지고 있음

- 실제 데이터를 저장하고 있는 뷰를 생성하는 기능을 지원하는 DBMS도 존재

 

 

- 예시1)

- 형식

# 테이블 생성
CREATE TABLE TBL
(
	ID NUMBER PRIMARY KEY,
    AMT NUMBER NOT NULL,
    DEGREE VARCHAR2(1)
 );
 
 # 컬럼 삽입
 INSERT INTO 테이블명 (컬럼명1,컬럼명2,...) VALUES (컬럼1 데이터, 컬럼2 데이터,...)

- 예시2)

# 테이블 생성
CREATE TABLE TBL
(
	ID NUMBER PRIMARY KEY,
    AMT NUMBER NOT NULL,
    DEGREE VARCHAR2(1)
 );
 
 # 컬럼 삽입
 INSERT INTO TBL (ID, AMT) VALUES (1001, 001);
 INSERT INTO TBL VALUES (1001, 001, 'A');

 

 

- 예시3) 테이블명 : Category

품목ID	단가
001	1000
002	2000
003	1000
004	2000

 

# 테이블 생성
CREATE TABLE Category
( 
	PRODUCT_ID VARCHAR(4) NOT NULL,
    PRICE INT NOT NULL
 );
 
 # 트랜잭션 시작
 BEGIN TRANSACTION
 INSERT INTO Category(PRODUCT_ID, PRICE)   # 005 2000 삽입 
 	VALUES ('005', 2000);  
    
 # 승인
 COMMIT;
 
 BEGIN TRANSACTION
 DELETE Category WHERE PRODUCT_ID = '002';  # 002 삭제
 
 BEGIN TRANSACTION
 UPDATE Category SET PRICE = 2000 WHERE PRICE = 1000;  # 1000 -> 2000 변경

# 취소
ROLLBACK; # 002 삭제, 2000 변경 초기화로 005 2000만 생성

SELECT COUNT(PRODUCT_ID) FROM Category
	WHERE PRICE = 2000;