[SQLD] 2과목 "SQL 기본" 파트 핵심 요약 정리

관계형 데이터베이스 개요

관계형 데이터베이스는
1. 동시성 관리, 병행 제어를 통해 많은 사용자들이 동시에 데이터를 공유 및 조작할 수 있으며
2. 정규화를 통한 테이블 모델링을 통해 이상현상을 제거하고 데이터 중복을 피할 수 있고,
3. 메타데이터를 관리하면서 데이터 체계화와 데이터 표준화를 보장하고,
4. 제약조건을 이용하여 데이터 무결성(integrity)를 보장하고,
5. 시스템 장애 발생 시 트랜잭션 제어를 통해 데이터를 회복/복구할 수 있는 기능이 있는
효율적인 데이터 관리 시스템이다.

SQL(Structured Query Language)⭐
관계형 데이터베이스에서 데이터 정의, 조작, 제어를 위해 사용하는 언어.

1) 데이터 정의어, DDL(Data Definition Language)
테이블, 뷰와 같은 데이터 구조를 정의하는 명령어. 구조를 생성/변경/삭제/이름변경한다.
⇨ `CREATE` `DROP` `RENAME` `ALTER` `TRUNCATE`

2) 데이터 조작어, DML(Data Manipulation Language)
데이터를 조회하거나 검색하기 위한 명령어. 데이터를 추가/삭제/수정한다.
⇨ `SELECT` `INSERT` `DELETE` `UPDATE`

3) 데이터 제어어, DCL(Data Control Language)
데이터베이스 접근 권한을 주거나 회수하는 명령어
⇨ `GRANT` `REVOKE`

4) 트랜잭션 제어어, TCL(Transaction Control Language)
트랜잭션 별로 제어하는 명령어
⇨ `COMMIT` `ROLLBACK` `SAVEPOINT`

DDL : CREATE, DROP, ALTER, RENAME, TRUNCATE

-- DDL : CREATE, ALTER, DROP, RENAME, TRUNCATE
-- DML : SELECT, INSERT, UPDATE, DELETE
-- DCL : GRANT, REVOKE
-- TCL : COMMIT, SAVEPOINT, ROLLBACK


-- CREATE : 테이블 생성
-- CREATE TABLE 테이블명(컬럼명 컬럼타입)
CREATE TABLE TEST(
    A NUMBER(10),   -- column 1 숫자
    B VARCHAR2(10), -- column 2 문자
    C NUMBER(10)    -- column 3 숫자
);
-- 실행 -->  ctrl + enter

-- 테이블명 제한 조건
-- 영문 대소문자, 한글, 숫자, 특수기호(_ # $)
-- 테이블명/컬럼명 첫 글자는 무조건 문자!


-- DROP : 테이블 삭제
DROP TABLE TEST;


-- ALTER : 테이블 컬럼 수정, 삭제, 추가
-- 컬럼 삽입
ALTER TABLE TEST ADD(D NUMBER(10));
ALTER TABLE TEST ADD(E NUMBER(10), F VARCHAR2(10));

-- 컬럼 삭제
ALTER TABLE TEST DROP COLUMN F;
ALTER TABLE TEST DROP (D, E); -- 두 개 이상이면 키워드 없이 괄호로 묶어주기

-- 컬럼 타입 변경 (데이터가 들어있으면 안 됨)
ALTER TABLE TEST MODIFY(B NUMBER(10));


-- RENAME : 테이블 명 변경
-- 방법 1)
RENAME TEST TO TEST1;

-- 방법 2)
ALTER TABLE TEST1 RENAME TO TEST;


-- TRUNCATE : 테이블 초기화
TRUNCATE TABLE TEST;

DML : SELECT, INSERT, DELETE, UPDATE

-- SELECT : 데이터 조회
SELECT * FROM TEST;    -- 전체 조회
SELECT A FROM TEST;    -- A column만 조회
SELECT A, B FROM TEST; -- A, B column만 조회 (순서 주의)
SELECT B, A FROM TEST; -- B, A column만 조회 (순서 주의)
SELECT a, b FROM TEST; -- 에러 (대소문자 구분!)


-- INSERT : 데이터 삽입
INSERT INTO TEST VALUES(11, 12, 13);    -- 1행 (각각 1열, 2열 3열 데이터)
INSERT INTO TEST(A, B) VALUES (21, 22); -- 삽입할 컬럼 순서 지키기! (나머지는 NULL)
INSERT INTO TEST VALUES (NULL, 31, 32);
INSERT INTO TEST VALUES (41, '', 42);


-- DELETE : 데이터 삭제
DELETE TEST; // 전체 행 삭제
DELETE FROM TEST;

ALTER TABLE TEST MODIFY(B VARCHAR(10));
INSERT INTO TEST VALUES(10, 'A', 20);   -- 문자/문자열 삽입은 모두 작은따옴표


-- UPDATE
UPDATE TEST SET A=1; 			-- A 컬럼의 모든 값을 1로 변경
UPDATE TEST SET A=11 WHERE C=20; 	-- 특정 행의 컬럼값을 변경
UPDATE TEST SET A=11 WHERE B IS NULL;   -- 특정 행의 컬럼값을 변경 (NULL값은 비교연산자 사용 안 함)
UPDATE TEST SET C=NULL WHERE B IS NULL; -- 대입연산은 가능


-- (+) 테이블 생성 시 무결성 제약조건 추가 가능 => 데이터 INSERT 시 영향을 줌
CREATE TABLE TEST1(
    A NUMBER(10) PRIMARY KEY, -- 컬럼 A => 주식별자 (유일성, 최소성, 불변성, 존재성 만족해야 함)
    B NUMBER(10) NOT NULL,    -- 컬럼 B => 존재성을 만족해야 함 (NULL값이 들어갈 수 없음)
    C NUMBER(10)
);

SELECT * FROM TEST1;
INSERT INTO TEST1 VALUES(10, 11, 12);
INSERT INTO TEST1 VALUES(10, 21, 22); -- 무결성 제약 조건 (PK 유일성) 때문에 삽입 불가

* DELETE vs. DROP vs. TRUNCATE

* VIEW (서브테이블)

-- VIEW : 기존 테이블에서 필요한 컬럼으로만 구성된 일종의 (서브)테이블
-- CREATE VIEW VIEW명 AS SELECT 컬럼명1, 컬럼명2 ... FROM 테이블명;

CREATE VIEW TESTVIEW AS SELECT A, C FROM TEST1;
SELECT * FROM TESTVIEW;
UPDATE TEST1 SET C = 33; -- 원본 테이블을 수정하면 뷰에도 반영됨

 

WHERE : SELECT문에 대한 조건절

사용 연산자 => 비교, 부정비교, 논리, SQL, 부정SQL

- 비교 : `=`, `>`, `<`, `>=`, `<=`
- 부정비교 : `!=`, `^=`, `<>` (셋 다 같은 의미), `NOT 컬럼명=`, `NOT 컬럼명>`
- 논리 : `AND`, `OR`, `NOT`
- SQL : `LIKE` 비교 문자열, `BETWEEN A AND B`, `IN`(=OR 합집합), `IS NULL`
- 부정SQL : `NOT BETWEEN A AND B`, `NOT IN`, `IS NOT NULL`

* 연산자 우선순위 : `() > ... > NOT > AND > OR`

* NULL 값은 WHERE 비교 결과에 포함되지 않기 때문에
  NULL값도 검색 결과에 포함하려면 무조건 `IS NULL` 또는 `IS NOT NULL` SQL문으로 조회해야 한다!

-- WHERE 절 : 조건절
-- 연산자 - 비교, 부정비교, 논리, SQL, 부정SQL

SELECT * FROM TEST2; -- 전체 결과 조회
SELECT * FROM TEST2 WHERE A >= 20; -- 컬럼 A의 값이 20 이상인 것만 조회

-- 아래 모두 같은 결과 (부정비교 연산자)
SELECT * FROM TEST2 WHERE A != 20;
SELECT * FROM TEST2 WHERE A ^= 20;
SELECT * FROM TEST2 WHERE A <> 20;
SELECT * FROM TEST2 WHERE NOT A = 20;

-- NULL 값은 WHERE 비교 결과에 포함 안 됨
-- 무조건 IS NULL 또는 IS NOT NULL SQL문으로 조회해야 함
SELECT * FROM TEST2 WHERE C != 13; -- NULL값이 포함된 행은 안 나옴
SELECT * FROM TEST2 WHERE C IS NULL OR C = 33;


-- 아래 연속되는 질의문 쌍은 같은 의미!
SELECT * FROM TEST2 WHERE A < 20;
SELECT * FROM TEST2 WHERE NOT A >= 20;

SELECT * FROM TEST2 WHERE A >= 20 AND A <= 30;
SELECT * FROM TEST2 WHERE A BETWEEN 20 AND 30;


-- OR 논리연산자는 IN SQL문으로 바꿔 쓸 수 있다. (같은 의미)
SELECT * FROM TEST2 WHERE A = 10 OR A = 20;
SELECT * FROM TEST2 WHERE A IN (10, 20); -- IN 괄호 안에 NULL이 있으면 무시

SELECT * FROM TEST2 WHERE A NOT IN (11, 31);
SELECT * FROM TEST2 WHERE NOT (A = 11 OR A = 31);

-- IN(A, NULL) --> IN(A) : NULL 생략
-- NOT IN (A, NULL) --> NULL: 실행 결과 없음
SELECT * FROM TEST2 WHERE A NOT IN (11, 31); -- 결과 없음


-- 아래는 다른 의미
SELECT * FROM TEST2 WHERE A = 11 OR C = 31;
SELECT * FROM TEST2 WHERE (A, C) IN ((11, 31)); -- A=11 AND C=31


-- TRUTHY FALSY
SELECT * FROM TEST2 WHERE 10 = 20; // 조건이 거짓 --> 아무것도 조회 X
SELECT * FROM TEST2 WHERE 20 = 20; // 조건이 참 --> 전체 조회

SQL 작성 순서⭐ : SELECT / FROM / WHERE / GROUP BY / HAVING / ORDER BY
SQL 실행 순서⭐ : FROM / WHERE / GROUP BY / HAVING / SELECT / ORDER BY

LIKE 문자열 비교 연산자

- `%` : 0개 이상의 문자 
- `_` : 1개 단일 문자

-- LIKE 연산자
-- % : 0개 이상의 문자 
-- _ : 1개 단일 문자

SELECT * FROM EMP;
-- 'A'로 시작하는 ENAME (A + 0개 이상 문자)
SELECT * FROM EMP WHERE ENAME LIKE 'A%';
-- 'S'로 끝나는 ENAME (0개 이상 문자 + S)
SELECT * FROM EMP WHERE ENAME LIKE '%S';
-- 두번째가 'A'인 ENAME (문자 1개 + A + 0개 이상 문자)
SELECT * FROM EMP WHERE ENAME LIKE '_A%';
-- A가 두 번 들어가는 사원의 ENAME
SELECT * FROM EMP WHERE ENAME LIKE '%A%A%';

ROWNUM

WHERE 조건절에 행 인덱스 번호를 이용하여 1번째 행부터 n번째 행까지 출력이 가능하다.
단, 인덱스 1부터 순서대로 연결된 행번호만 출력할 수 있음에 주의!

-- ROWNUM : 행 인덱스 번호

SELECT * FROM EMP WHERE ROWNUM=1; --> 첫 번째 행
SELECT * FROM EMP WHERE ROWNUM < 9; --> 1~8행
SELECT * FROM EMP WHERE ROWNUM = 1 OR ROWNUM = 2; --> 1행 2행 (1부터 순서대로 연결된 행은 그대로 출력)
SELECT * FROM EMP WHERE ROWNUM = 1 OR ROWNUM = 3; --> 1행만
SELECT * FROM EMP WHERE ROWNUM = 2 OR ROWNUM = 3; --> 결과 없음
SELECT * FROM EMP WHERE ROWNUM BETWEEN 1 AND 9; --> 1~9행
SELECT * FROM EMP WHERE ROWNUM BETWEEN 2 AND 9; --> 결과 없음

TCL : COMMIT, SAVEPOINT, ROLLBACK

-- DDL : CREATE DROP ALTER RENAME TRUNCATE (AUTO COMMIT 자동 동기화)
-- DML : SELECT INSERT UPDATE DELETE (수동 COMMIT, 커밋 전에 롤백하면 삭제됨)
-- DCL : GRANT 권한 부여, REVOKE 권한 해제
-- TCL : COMMIT 지금까지 변경사항 저장, SAVEPOINT 롤백 지점 설정, ROLLBACK 마지막 커밋 상태로 되돌아감

-- 커밋 될 시에는 현재까지의 모든 라인이 함께 저장됨
CREATE TABLE TEST6
(
    A NUMBER(10),
    B NUMBER(10),
    C NUMBER(10),
    D NUMBER(10)
);
INSERT INTO TEST6 VALUES(10, 20, 30, 40);
INSERT INTO TEST6 VALUES(11, 21, 31, 41);
INSERT INTO TEST6 VALUES(12, 22, 32, 42);
SELECT * FROM TEST6;

COMMIT; -- 앞까지 명령 저장
INSERT INTO TEST6 VALUES(13, 23, 33, 43);
INSERT INTO TEST6 VALUES(14, 24, 34, 44);
SELECT * FROM TEST6;

ROLLBACK; -- 커밋 상태로 되돌아감 (커밋 이후 변경 삭제)
SELECT * FROM TEST6;

INSERT INTO TEST6 VALUES(15, 25, 35, 45);
SAVEPOINT SV1;
INSERT INTO TEST6 VALUES(16, 26, 36, 46);
SELECT * FROM TEST6;

ROLLBACK TO SV1; -- SV1 시점 상태로 되돌아감 (커밋 이후 변경 삭제)
SELECT * FROM TEST6;