[SQLD Part3] 01 SQL 기본

1 관계형 데이터베이스

1.1 관계형 데이터베이스 등장
- 1970년대 E.F. Codd 박사의 논문에서 처음 소개된 데이터베이스
- 조인 연산을 통해 합집합,교집합,차집합 생성
1.2 DB와 DB 관리 시스템의 차이점
- DB 종류 : 계층형, 네트워크형, 관계형
① 계층형 : 트리형태의 자료구조, 1:N 관계
② 네트워크 : 오너와 멤버 형태로 데이터 저장, 1:N & M:N 둘다 가능
③ 관계형 : 릴레이션에 데이터 저장 관리, 릴레이션을 사용해 집합연산 관계연산 가능
- DB 관리 시스템 : DBMS, 데이터베이스 관리 위한 소프트웨어
1.3 관계형 데이터베이스 집합 연산과 관계 연산
- 집합연산
① 합집합(Union) : 두 개의 릴레이션을 하나로 합하는 것. 중복된 행 한번만 조회
② 차집합(Difference) : 본래 릴레이션에는 존재 다른 릴레이션에 존재하지 않음
③ 교집합(Intersection) : 두 릴레이션 간에 공통된 것 조회
④ 곱집합(Cartesian product) : 각 릴레이션에 존재하는 모든 데이터 조합 연산
- 관계연산
① 선택 연산(Selection) : 조건에 맞는 행만 조회
② 투영 연산(Projection) : 조건에 맞는 속성만 조회
③ 결합 연산(Join) : 공통된 속성으로 새로운 릴레이션 생성
④ 나누기 연산(Division) : ???? 중복된 행 제거 ????
1.4 테이블의 구조
- 릴레이션 = 테이블
- 기본키(Primary key) : 유일성(Unique),최소성,not null 만족. 해당 테이블 대표.
- 행(Row) = 튜플 = 레코드
- 칼럼(Column) = 필드(Field) = 속성(Attribute)
- 외래키(Foreign key) = 다른 테이블의 기본키를 참조하는 칼럼. 결합연산(Join)을 하기 위해서 사용

2 SQL 종류

2.1 SQL(Structured Query Language)
- 데이터 구조 정의, 조작, 제어 등을 할 수 있는 절차형+비절차형 언어
- ANSI/ISO 표준을 준수 하여 관리 시스템 변경이 되어도 그대로 사용
2.2 SQL 종류
① DDL : 관계형 데이터베이스의 구조를 정의 ex) CRATE, ALTER, DROP, RENAME, TRUNCATE
② DML : 테이블에서 데이터를 입력, 수정, 삭제, 조회 ex) INSERT, DELETE, UPDATE, SELECT
③ DCL : 권한부여, 회수 ex) GRANT, REVOKE
④ TCL : 트랜잭션 제어 ex) COMMIT, ROLLBACK, SAVEPOINT
2.3 트랜잭션
- DB 작업 처리 단위
* 트랜잭션 특성
① 원자성 : 전부 실행되거나 전부 실행 안되거나
② 일관성 : 실행 후 일관성이 있어야한다
③ 고립성 : 부분 실행 값 외부 확인 불가
④ 영속성 : 성공적 결과는 영구적 보장
2.4 SQL문 실행 순서
① 파싱 : 문법 확인, 구문 분석, Library Cache에 저장
② 실행 : 옵티마이저 실행 계획 따라 실행
③ 인출 : 데이터를 읽어서 전송

3 DDL

3.1 테이블 생성
① Create Table : 테이블 생성, 기본키/외래키/제약사항 등 설정
- 외래키를 지정하려면 마스터 테이블이 생성되어야 함. ex) 사원과 부서 중 부서가 마스터

Create Table EMP
(
empno number(10),
ename varchar2(20),
sal number(10,2) default 0, //기본값을 0으로, 소수점 둘째자리까지
createdate date default sysdate, //기본값을 오늘날짜 시분초로 정함
constraint emppk primary key(empno) //기본키(empno),기본키 이름(emppk)
constraint emppk primary key(empno,ename) //2개 기본키일 경우
constraint deptfk foreign key (deptno)
                  references dept (deptno) //외래키가 기본키를 참조
                  ON DELETE CASCADE //데이터 삭제시 참조테이블 데이터 같이 삭제, 참조무결성 준수
);
Create Table DEPT
(
deptno varchar2(4) primary key,
deptname varchar2(20)
);

* DESC 테이블명 : 테이블 구조 보고 싶을 때
3.2 테이블 변경
② Alter Table : 테이블 수정, 컬럼 추가/변경/삭제, 기본키/외래키 설정

-- *** 테이블명 변경
ALTER TABLE EMP
RENAME TO NEW EMP;

-- *** 칼럼 추가
ALTER TABLE EMP
ADD (age number(2) dfault 1);

-- *** 칼럼 변경
ALTER TABLE EMP
MODIFY (ename varchar2(40) not null);

-- *** 칼럼 삭제
ALTER TABLE EMP
DROP COLUMN age;

-- *** 칼럼명 변경
ALTER TABLE EMP
RENAME CLUMN ename to new_ename;

③ Drop Table : 테이블 삭제, 저장된 모든 것 같이

-- 테이블 삭제
DROP TABLE EMP;
-- 외래키로 참조한 테이블의 제약사항도 삭제
DROP TABLE EMP CASCADE CONSTRAINT;

④ 뷰생성과 삭제
- 실제 테이블을 참조한 가상 테이블
- 실제 데이터 없이 원하는 컬럼만 조회
- 데이터 딕셔너리에 SQL문 형태로 저장하고 실행시 참조
- ALTER로 수정 불가능, 한번 생성되면 삭제 후 재생성, 입력/수정/삭제 불가
- 특정 칼럼만 조회하여 보안성 향상
- 참조 테이블이 변경되면 뷰도 변경됨

-- 뷰 생성
CREATE VIEW T_EMP AS
SELECT * FROM EMP;

-- 뷰 조회 (테이블 조회처럼)
SELECT * FROM T_EMP;

-- 뷰 삭제
DROP VIEW T_EMP;

* 장점
- 특정 칼럼만 조회하여 보안 기능 있음
- 데이터 관리 간단, SELECT문이 간단
- 하나의 테이블에 여러 뷰 생성 가능
* 단점
- 독자적 인덱스 생성 불가
- 연산 제약 (삽입/수정/제거)
- 데이터 구조 변경할 수 없음

4 DML

4.1 INSERT
- 테이블에 데이터 입력

-- 특정 컬럼 삽입
INSERT INTO EMP(EMPNO,ENAME) VALUES(1000,'임베스트');

-- 모든 컬럼 삽입(컬럼명 생략 가능)
INSERT INTO EMP VALUES(1000,'임베스트');

-- SELECT문으로 삽입
INSERT INTO DEPT_TEST
SELECT * FROM DEPT;

4.2 UPDATE
- 조건이 없으면 모든 데이터 수정

UPDATE EMP
SET ENAME = '조조'
WHERE EMPNO = 100;

4.3 DELETE
- 데이터를 삭제한다고 용량이 초기화 되지는 않는다. 삭제 여부만 표시하고 용량은 초기화되지 않음. Extent의 크기가 MAX_EXTENTS를 넘으면 용량 초과 오류 발생.
- 조건 없으면 모든 데이터 삭제

-- 조건에 맞는 행 삭제
DELETE FROM EMP
WHERE EMPNO = 100;

-- 모든 데이터 삭제. 용량 초기화 안됨
DELETE FROM EMP;

-- 모든 데이터 삭제, 용량 초기화
TRUNCATE TABLE EMP;

4.4 SELECT
- ORDER BY : 데이터 출력 바로 전 정렬, 메모리 많이 사용해서 성능 저하, SORT_AREA_SIZE라는 내부 메모리 사용

-- 칼럼 뒤에 문자 삽입 조회
SELECT ENAME || '님'
FROM EMP;

-- ENAME은 오름차순 ASC, SAL은 내림차순 DESC
SELECT *
FROM EMP
ORDER BY ENAME, SAL DESC;

-- ORDER BY를 사용하지 않고 인덱스로 정렬 가능 (기본은 기본키 컬럼 오름차순)
SELECT /*+ INDEX_DESC(A) */
FROM EMP A;

-- 중복 값 제거 조회
SELECT DISTINCT DEPTNO
FROM EMP
ORDER BY DEPTNO;

-- ALIAS
SELECT ENAME AS "이름"
FROM EMP a
WHERE a.EMPNO=1000;

5 WHERE

* 부정 비교 연산자
- ^= : 같지 않은 것
- <> : 같지 않은 것
- NOT 칼럼명 = : 같지 않은 것
- NOT 칼럼명 > : 같지 않은 것
* SQL 연산자
- LIKE '%비교 문자열%' : 비교 문자열 조회, '%'는 모든 값을 의미
- IN (list) : list 안에 하나만 일치해도 조회, OR
- IS NULL
* 부정 SQL 연산자
- NOT IN (list) : list와 불일치한 것을 조회
- IS NOT NULL

-- test로 시작하는 모든 것
SELECT *
FROM EMP
WHERE ENAME LIKE 'test%';

-- 1로 끝나는 모든 것
SELECT *
FROM EMP
WHERE ENAME LIKE '%1';

-- 중간에 est가 있는 모든 것
SELECT *
FROM EMP
WHERE ENAME LIKE '%est%';

-- test1와 같은 것 (like를 쓰고 와일드카드 안쓰면 '='와 같다)
SELECT *
FROM EMP
WHERE ENAME LIKE 'test1';

-- test로 시작하고 하나의 글자만 더 있는 것
SELECT *
FROM EMP
WHERE ENAME LIKE 'test_';

5.1 BETWEEN

-- 1000과 2000 사이
SELECT * FROM EMP
WHERE SAL BETWEEN 1000 AND 2000;

-- 1000 미만 2000 초과
SELECT * FROM EMP
WHERE SAL NOT BETWEEN 1000 AND 2000;

5.2 IN

-- JOB이 CLERK or MANAGER
SELECT *
FROM EMP
WHERE JOB IN ('CLERK','MANAGER');

SELECT *
FROM EMP
WHERE (JOB,ENAME)
IN (('CLERK','test1'),('MANAGER','test4'));

5.3 NULL
- 모르는 값, 값의 부재, 숫자 혹은 날짜 더하기, 알 수 없음

SELECT *
FROM EMP
WHERE MGR IS NULL;
WHERE MGR IS NOT NULL;

-- NULL이면 지정값 아니면 원래값
SELECT NVL(EXP1, 0)
FROM EMP;

-- NULL이면 'N', NULL 아니면 'Y'
SELECT NVL2(EXP1, 'Y','N')
FROM EMP;

-- 두 값이 같으면 NULL, 아니면 EXP1
SELECT NULLIF(EXP1, EXP2)
FROM EMP;

-- NULL이 아닌 최초값을 출력, 전부 NULL이면 NULL
SELECT COALESCE(A,B)
FROM EMP;

6 GROUP BY

- 합계, 평균, 최댓값, 최소값
- HAVING : GROUP BY 조건절
* 집계 함수
- COUNT(*) : NULL 포함한 모든 행의 수
- COUNT(컬럼명) : NULL 제외한 행 수
- SUM, MAX, MIN, AVG, STDDEV(표준편차), VARIANCE(분산)

SELECT DEPTNO, SUM(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING SUM(SAL) > 1000;

-- 부서별(DEPTNO), 관리자별(MGR) 급여평균 계산
SELECT DEPTNO, MGR, AVG(SAL)
FROM EMP
GROUP BY DEPTNO, MGR;

-- 직업별(JOB) 급여합계 중에 급여(SAL)합계가 1000 이상인 직업
SELECT JOB, SUM(SAL)
FROM EMP
GROUP BY JOB
HAVING SUM(SAL) >= 1000;

-- 사원번호 1000~1003번의 부서별 급여합계
SELECT DEPTNO, SUM(SAL)
FROM EMP
WHERE EMPNO BETWEEN 1000 AND 1003
GROUP BY DEPTNO;

7 SELECT 실행 순서

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

8 형변환

- 명시적 형변환 : 함수를 사용해서 데이터 타입을 일치시킴
-- TO_NUMBER(문자열)
-- TO_CHAR(숫자 or 날짜 [FORMAT])
-- TO_DATE(문자열, FORMAT)
- 명시적 형변환 사용하면 인덱스 사용가능
- 암시적 형변환 : 시스템이 자동으로 형변환,인덱스 컬럼에 형변환 발생하면 인덱스 사용할 수 없다

-- EMPNO이 숫자형인데 암시적 형변환이 발생
SELECT *
FROM EMP
WHERE EMPNO = '1000';

-- 명시적 형변환 사용하면 인덱스 사용 가능
SELECT *
FROM EMP
WHERE EMPNO = TO_NUM('100');

9 내장형 함수

9.1 문자열 함수

SELECT ASCII('a'), SUBSTR('ABC',1,2), LENGTH('A BC'), LTRIM(' ABC'), LENGTH(LTRIM(' ABC'))
FROM DUAL;

-- 97 'AB' 4 'ABC' 3

- ASCII : 아스키 코드값으로 변환
- SUBSTR : 특정 위치에서 몇개를 출력할지, 특정 위치 부터 쭉 출력
- LENGTH : 공백을 포함한 길이 출력
- TRIM, LTRIM, RTRIM : 공백 제거 출력, 특정 지정값을 넣으면 지정값을 제거 출력, 특정 지정값을 여러개 썼을 때 반복이 되면 상관없이 한개 작성한것처럼 해당 문자를 제거
- CONCAT : 두 문자열 결합, 오라클('||'), MS-SQL('+')
- LOWER : 소문자로 변경
- UPPER : 대문자로 변경
 
9.2 날짜형 함수

SELECT SYSDATE, EXTRACT(YEAR FROM SYSDATE), TO_CHAR(SYSDATE, 'YYYYMMDD')
FROM DUAL;

-- 2023-08-28 10:40:19 / 2023 / 20230828

- SYSDATE : 오늘 날짜를 날짜 타입으로 출력
- EXTRACT : 년,월,일 출력 YEAR FROM SYSDATE
 
9.3 숫자형 함수

SELECT ABS(-1), SIGN(10), MOD(4,2), CEIL(10.9), FLOOR(10.1), ROUND(10.222,1)
FORM DUAL;

-- 1/1/0/11/10/10.2

- ABS : 절댓값
- SIGN : 양수(1), 0, 음수(-1)
- MOD : 나머지
- CEIL / FLOOR : 올림 / 내림
- ROUND / TRUNC : 지정된 자리까지 출력 반올림 / 지정 자리 아래로 버림
 

10 DECODE CASE

SELECT DECODE(EMPNO, 1000, TRUE, FALSE)
FROM EMP;

-- TRUE,FALSE

- DECODE : EMPNO이 1000이AUS TRUE, 아니면 FALSE

SELECT CASE
	WHEN EMPNO = 1000 THEN 'A'
        WHEN EMPNO = 1001 THEN 'B'
        ELSE 'C'
       END
FROM EMP;

11 ROWNUM ROWID

-- 오라클
SELECT *
FROM EMP
WHERE ROWNUM <= 1;

-- SQL Server
SELECT TOP(10)
FROM EMP;

-- MySQL
SELECT *
FROM EMP LIMIT 10; -- where 안씀 주의

-- inline view로 5행 조회
-- 서브쿼리 사용 이유 : 서브쿼리로 정렬을 하지 않고 사용할 경우 순번이 먼저 먹여져서 원하는 결과가 나오지 않을 수 있음
SELECT *
FROM (SELECT ROWNUM list FROM EMP)
WHERE list <= 5;

SELECT *
FROM (SELECT ROWNUM list, ENAME FROM EMP)
WHERE list BETWEEN 5 AND 10;

- ROWNUM : SELECT문 결과에 대한 논리적 일련번호, 순번을 매기고 싶거나 원하는 특정 개수만큼 원하는 데이터를 출력할 때
* 오라클:ROWNUM / SQL Server:TOP / MySQL:LIMIT

SELECT ROWID,EMPNO
FROM EMP;

-- AAAtJR AAJ AAA0gK AAV

-- AAAtJR : 오브젝트 번호
-- AAJ : 상대적 파일 번호, 각 데이터 파일에 할당되는 번호
-- AAA0gK : 블록 위치 번호
-- AAV : 블록 내 행 번호,

- ROWID : 데이터 구분하는 유일 값

12 WITH

- 임시 가상 테이블
- VIEW는 CREATE를 사용하여 DROP 할 때까지 모든 테이블에 사용가능하지만 WITH는 한번만 실행할 쿼리 내에서 수행

WITH viewData as
(SELECT * FROM EMP
	UNION ALL
 SELECT * FROM EMP
)
SELECT * FROM viewData WHERE EMPNO=1000;

WITH W_EMP as
(SELECT * FROM EMP WHERE DEPTNO=30)
SELECT * FROM W_EMP;

13 DCL

13.1 GRANT

GRANT SELECT, INSERT, UPDATE, DELETE
ON EMP
TO LIMBEST;

-- LIMBEST 사용자에게 EMP 테이블에 대해서 SELECT, INSERT, UPDATE, DELETE 권한을 부여

GRANT REFERENCES (dept_id)
ON department
TO kim;

-- kim에게 department 테이블을 참조하는 권한을 가진 테이블 생성 가능
-- dept_id는 department 테이블의 기본키

GRANT SELECT
ON student
TO kim
WITH GRANT OPTION;

-- kim에게 student 테이블을 select 할 수 있는 권한을 부여함과 동시에
-- 이 권한을 다른 사용자에게 부여할 수 있는 자격까지 부여

- SELECT / UPDATE / INSERT / DELETE
- REFERENCES : 외래키를 선언할 수 있는 권한
- ALTER : 지정된 테이블 수정
- INDEX : 지정 테이블 인덱스 생성 권한
- ALL : 모든 권한
 
- WITH GRANT OPTION : 지정한 권한을 부여. 그 권한을 다른 사용자에게 전파할 수 있는 자격 부여. 권한 철회가 연속적으로 일어난다. A가 B에게 권한을 부여하고, A의 권한을 취소하면 B도 취소된다.
- WITH ADMIN OPTION : 테이블에 대한 모든 권한 부여. 권한 철회가 연속적으로 이뤄지지 않는다. A가 B에게 권한 부여, B가 C에게 권한 부여, B의 권한을 철회할 경우 C의 권한 유지?
 
13.2 REVOKE

REVOKE SELECT ON EMP FROM kim;

-- kim에게 부여됐던 EMP 테이블에 대한 select 권한 회수

14 TCL

14.1 COMMIT
- commit이 완료되면 LOCK이 해제된다.
14.2 ROLLBACK
- 작업 모두 취소. 이전에 commit 한 곳까지만 복구
- rock 해제되고 다른 사용자도 데이터베이스 행 조작 가능.
14.3 SAVEPOINT

SAVEPOINT t1;

rollback to t1;