46. SQLD 2과목 (1)SQL기본

용어
DB : 특정 기업이나 조직 또는 개인이 필요에 의해 데이터를 일정한 형태로 저장해 놓은 것
DBMS : 효율적인 데이터 관리 뿐만 아니라 예기치 못한 사건으로 인한 데이터의 손상을 피하고, 필요시 필요한 데이터를 복구하기 위한 강력한 기능의 SW
SQL : 관계형 DB에서 데이터 정의, 조작, 제어를 위해 사용하는 언어

테이블 : DB 기본 단위 데이터를 저장하는 객체
가로 : 행 = 로우 = 튜플 = 인스턴스
세로 : 열 = 컬럼


명령어

DML (Manipulation : 조작어)	SELECT, INSERT, DELETE, UPDATE
DDL (Definition : 정의어)	CREATE, ALTER, MODIFY, DROP, RENAME, TRUNCATE
DCL (Control : 제어어)	GRANT, REVOKE
TCL (트랜잭션 제어어)	ROLLBACK, COMMIT

PK키 설정
테이블생성 때 지정 : CONSTRAINT 이름 PRIMARY KEY (칼럼1, 칼럼2..)
테이블생성 이후 지정 : ALTER TABLE 테이블명 ADD CONSTRAINT 이름 PRIMARY KEY (칼럼1, 칼럼2..)


DDL
CHAR(s) : 고정 길이. 문자열 정보 최대 길이 만큼 공간 채움
VARCHAR2(s) : 가변 길이. 문자열 정보 할당된 변수 값의 바이트만 적용
NUMBER : 정수, 실수 등 숫자 정보
DATE : 날짜와 시각 정보

테이블 명은 다른 테이블의 이름과 중복되면 안 된다.
테이블 내의 칼럼명은 중복될 수 없다.
각 칼럼들은 ,로 구분되고 ;로 끝난다
칼럼 뒤에 데이터 유형은 꼭 지정되어야 한다.
테이블명과 칼럼명은 반드시 문자로 시작해야한다.
A-Z,a-z,0-9,_,$,#만 사용 가능


★ 제약조건 Constraint : 데이터의 무결성 유지

NOT NULL	칼럼에 NULL값이 포함되지 못한다
UNIQUE KEY (고유키)	칼럼 값이 해당 테이블 전체에서 유일
PRIMARY KEY (기본키)	UNIQUE + NOT NULL. 하나만 있다 (대표)
FOREIGN KEY (외래키)	NULL과 여러속성 가능
CHECK	해당 조건 만족하는 값으로만 입력 제한

참조무결성 제약조건
① 삭제규칙

CASCADE : Master삭제 시 Child 같이 삭제
SET NULL : Master삭제 시 Child 해당 필드 NULL
SET DEFAULT : Master 삭제 시 Child 해당 필드 Default값으로 설정RESTRICT : Child 테이블에 PK값이 없는 경우만 Master 삭제 허용
NO ACTION : 참조무결성을 위반하는 삭제/수정 액션 취하지 않음

② 생성규칙
AUTOMATIC : Master 테이블에 PK없는 경우, Master PK생성 후 Child 입력
SET NULL : Master 테이블에 PK없는 경우, Child 외부키를 NULL값으로 처리
SET DEFAULT : Master 테이블에 PK없는 경우, Child 외부키를 지정된 기본값으로 입력
DEPENDENT : Master 테이블에 PK가 존재할때만 Child입력 허용
NO ACTION : 참조무결성을 위반하는 삭제/수정 액션 취하지 않음


테이블 생성 공식
CREATE TABLE player (player_id CHAER(7) NOT NULL, player_name VARCHAR2(20) NOT NULL);

테이블 구조 변경 공식
추가 : ALTER TABLE player ADD(adress VARCHAR2(80));
삭제 : ALTER TABLE player DROP COLUMN adress;
수정 : ALTER TABLE temp_temp MODIFY (ORIG_YYYY VARCHAR2(8) DEFAULT '20020129' NOT NULL);
* SQL Server에서는 여러 개의 칼럼 동시 수정 불가 (오류발생)


제약조건 수정
추가 : ADD CONSTRAINT 조건명;
삭제 : DROP CONSTRAINT 조건명 조건 (칼럼명);
테이블명 변경 : RENAME player TO player_backup;
테이블 삭제 : DROP TABLE player;
테이블 데이터 삭제 : TRUNCATE TABLE player;
칼럼명 변경 : RENAME COLUMN team_id TO t_id;

TRUNCATE	DROP	DELETE
DDL	DDL	DML
데이터, 인덱스 삭제 (테이블 최초 생성 초기상태로 만듬)	테이블, 인덱스 삭제 (테이블 구조 자체 삭제)	데이터만 삭제
구조 남는다	구조 삭제	구조, 로그 남는다
사용공간 반납	사용공간 반납	사용공간 남아있음
Auto COMMIT	Auto COMMIT	COMMIT이전 ROLLBACK 가능

DML
* DDL 명령어의 경우 실행시 AUTO COMMIT, 하지만 DML의 경우 COMMIT을 입력해야 한다.
INSERT, UPDATE, DELETE : ROLLBACK, COMMIT(TCL) 가능
MERGE : insert, update, delete를 한번에 수행하는 명령어
MERGE INTO 테이블명 USING 테이블명2 ON 조건 WHEN MATCHED THEN UPDATE SET 변경내용

DISTINCT
중복 시 1회만 출력
DISTINCT (dept, mgr) : 괄호 안에 대해 집약. GROUPBY(dept, mgr)와 동일

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





TCL
COMMIT : 올바르게 반영된 데이터를 DB에 반영
ROLLBACK : 트랜잭션 시작 이전의 상태로 되돌림 COMMIT 되지 않은 모든 트랜잭션을 롤백
SAVEPOINT : 저장 지점

auto commit : INSERT, UPDATE, DELETE 또는 PL/SQL 블록이 성공적으로 실행될 때 자동으로 커밋이 이루어지게 할 것인가를 결정하는 것
begin transaction : 검증 쿼리이면 COMMIT으로 내용 반영, 실수 쿼리이면 ROLLBACK으로 유지


트랜잭션이란?
데이터베이스의 논리적 연산단위
데이터베이스의 상태를 변화시키기 위해서 수행하는 작업의 단위
질의어(SQL)를 이용하여 데이터베이스에 접근하는 것

트랜잭션의 특성
1. 원자성 : 트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않아야 함
2. 일관성 : DB 트랜잭션 실행 전 내용이 잘못 되지 않으면 실행 후도 잘못되지 않아야 함
3. 고립성 : 트랜잭션 실행 도중 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다
4. 지속성 : 트랜잭션이 성공적으로 수행되면 DB의 내용은 영구적으로 저장

트랜잭션의 격리성 문제
1. Dirty Read : 데이터 캐시에는 변경이 되었지만, 디스크에는 변경되지 않은 데이터를 읽는 작업
2. Non-Repeatable Read : 트랜잭션 내 다른 시점에 읽은 하나의 데이터가 값이 다른 것
3. Phantom Read : 트랜잭션 수행 중 없던 행이 추가되어 새로운 데이터를 읽게 되거나 존재하던 데이터가 사라지는 것, 트랜잭션 내 동일한 조건으로 읽은 데이터의 개수가 달라지는 것






논리연산자
AND : 둘 다 만족
OR : 둘 중 하나라도 만족 (=IN)
NOT : 둘 다 아니다

★ NOT - AND - OR (나오) 순으로 연산 실행
ex) NOT (조건) AND (조건) AND NOT (조건) OR (조건)일 때
NOT (조건)과 NOT (조건) 각각 실행 > AND AND 한번에 실행 > 마지막에 OR (조건) 실행

BETWEEN 1 AND 2 : 1 ≤ A ≤ 2
A IN (1, 2, 3) : A = 1 or A = 2 or A = 3

★LIKE
_ : 미지의 한 글자
% : 0 이상의 글자
ex) _L% : 이름의 두 번째 글자가 L인 사원

ESCAPE
LIKE 연산으로 '%'나 '_'가 들어간 문자를 검색하기 위해 사용한다. '_'나 '%'앞에 ESCAPE로 특수문자를 지정.
ex) ename LIKE 'A_A' -> A@_A
@말고도 아무 문자나 사용 가능

★NULL
① 모르는 값
② NULL의 산술연산 결과는 알 수 없음(UNKOWN)
③ NULL의 비교연산 결과는 FALSE
④ 정렬 상 의미가 Oracle에서는 ∞ SQL Sever에서는 -∞

nVL (값1, 값2) : 값1이 NULL면 값2
nVL2 (값1, 값2, 값3) : 값1이 NULL이면 값3, NOT NULL이면 값2
IS NULL (값1, 값2) : nVL과 동일
NULL IF (값1, 값2) : 값1 = 값2 이면 NULL, 값1 ≠ 값2 이면 첫번째 값
COALESCE (값1, 값2... ) : NULL 아닌 첫번째 값

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

ROWNUM
Oracle
집합의 각 행에 대해 임시로 부여되는 일련번호. 상위 n개 행을 가져온다.
WHERE절에서 행의 개수 제한
ex) SELECT empno, sal FROM emp WHERE ROWNUM ≤ 3 ORDER BY sal, desc일 때, ORDER BY셀이 가장 마지막에 나온다.
정렬 전에 ROWNUM 조건절 실행 > ROWNUM에 의해 출력된 결과만 ORDER BY로 정렬

TOP(n) 칼럼명
SQL Server
SELECT절에서 상위 n개 행을 가져온다
TOP(n) WITH TIES : ORDER BY절 뒤의 오는 컬럼을 기준으로 동일한 데이터가 있을 경우 함께 출력
ex) SELECT TOP 3 WITH TIES * FROM emp ORDER BY sal DESC;




단일행 함수
1. SELECT, WHERE, ORDER BY 절에서 사용 가능
2. 행에 개별적 조작
3. 여러 인자가 있어도 결과는 1개만 출력
4. 함수 인자에 상수 변수 표현식 사용 가능
5. 함수 중첩 가능


문자형 함수
UPPER : 대문자 변경
LOWER : 소문자 변경

ASCII : ASCII 문자의 값 반환
CHR : ASCII 값에 해당하는 문자 반환

CONCAT (인수가 무조건 2개) : 문자열 연결
+ : SQL Server
|| : Oracle

LPAD(값, 총 문자길이, 채움문자) : 왼쪽부터 지정한 문자로 채우기
RPAD(값, 총 문자길이, 채움문자) : 오른쪽부터 지정한 문자로 채우기
* 채움문자 지정하지 않으면 공백으로 남게됨

LTRIM(대상문자열, 제거문자열) : 문자열의 왼쪽 공백 제거, 문자 왼쪽 반복적인 문자 제거
RTRIM(대상문자열, 제거문자열) : 문자열의 오른쪽 공백 제거, 문자 오른쪽 반복적인 문자 제거
* 제거 문자열 지정하지 않으면 공백 제거

SUBSTR(문자열, 시작위치, 길이) : 문자단위로 시작위치와 자를 길이를 지정하여 문자열을 자른다
INSTR(찾을 대상, 검색 문자열) : 지정 문자열을 검색해서 위치를 찾아 리턴. 입력된 문자열이 일치하면 그 첫번째 인덱스값을 반환하고, 일치하는 문자가 없으면 0을 반환


숫자형 함수
SIGN(숫자) : 숫자가 양수면 1, 음수면 -1, 0이면 0 반환
MOD( 1, 2) : 숫자 1을 숫자 2로 나누어 나머지 반환
ROUND (인수) : (인수) 만큼 자릿수 반올림
CEIL : Oracle에서 가장 가깝고 높은 정수로 올림 ex) 6.2 -> 7
CEILING : SQL Sever에서 가장 가깝고 높은 정수로 올림
FLOOR (숫자) : 작거나 같은 최대 정수 리턴


날짜형 함수
TO_CHAR : 문자형
TO_DATE : 숫자형

SYSDATE : Oracle. 현재 날짜와 시각 출력
GETDATE : SQL Sever. 현재 날짜와 시각 출력

* 날짜data + 100 = 100일 이후 (day로 인식)
ex) 1 = , 1/24 = 1 , 1/24/60 = 1 하루 시간 분
[예제] 다음 중 문자열의 형변환을 일으키는 함수는?


NULL 함수 (NULL은 0이나 공백이 아님)
NVL(식1,식 2) : 식1의 값이 NULL이면 식2 출력. 공집합을 바꿔주진 않음
NULLIF(식1,식 2) : 식1이 식2와 같으면 NULL을, 아니면 식1 출력
COALESCE(식1,식 2) : NULL이 아닌 최초의 표현식. 모두 NULL이면 NULL반환


다중행 집계 함수
1. 여러 행들의 그룹이 모여서 그룹당 단 하나의 결과를 돌려주는 함수이다.
2. GROUP BY 절은 행들을 소그룹화 한다.
3. SELECT, HAVING, ORDER BY 절에 사용 가능
- ALL : Default옵션 생략 가능
- DISTINCT : 같은 값을 하나의 데이터로 간주하는 옵션

NULL과의 관계
COUNT(*) : NULL 값을 포함한 행의 수 * 조건절에 해당하는 데이터가 없을 때 COUNT(*)의 결과 값은 0
COUNT(표현식) : 표현식의 값이 NULL 값인 것을 제외한 행의 수
SUM() : NULL 값을 제외한 합계
AVG() : NULL 값을 제외한 평균
STDDEV : 표준 편차
VARIAN : 분산
MAX, MIN : 최대값 최소값

ex)

A	B	C	A+B+C
NULL	NULL	1	NULL
3	2	2	7
NULL	2	3	NULL

일 때, SUM(A) = 3, SUM(B) = 4, COUNT(A) = 1, COUNT(*) = 3
[문제] SUM(A+B+C) = ? [정답] 7
SUM(A) + SUM(B) = ? [정답] NULL


GROUP BY, HAVING 절의 특징
1. GROUP BY절을 통해 소그룹별 기준을 정한 후, SELECT절에 집계 함수를 사용한다.
2. 집계 함수의 통계 정보는 NULL값을 가진 행을 제외하고 수행한다.
3. GROUP BY ALIAS 절에서 사용 불가
4. 집계 함수는 WHERE절에 올 수 없다.
5. HAVING절에는 집계함수를 이용하여 조건 표시 가능
6. HAVING GROUP BY절은 일반적으로 뒤에 위치


DECODE / CASE
DECODE(A, B, '1', null) : A 가 B 일 경우 '1'을, 아닐 경우 null(생략 가능)
DECODE(A, B, '1', C, '2', '3') : A 가 B 일 경우 '1'을, A 가 C 일 경우 '2', 둘 다 아닐 경우 '3'
* DECODE 함수는 대소비교(<, >, =) 불가

CASE 칼럼 WHEN 1 THEN 'a' ELSE 'b' END : 칼럼값=1이면 a, 아니면 b
CASE WHEN 칼럼 = 1 THEN 'a' ELSE 'b' END : 칼럼값=1이면 a, 아니면 b
* ELSE없으면 NULL



ORDER BY
1. SQL 문장으로 조회된 데이터들을 다양한 목적에 맞게 특정한 칼럼을 기준으로 정렬하여 출력하는데 사용
2. ORDER BY ALIAS절에 칼럼명 대신 명이나 칼럼 순서를 나타내는 정수도 사용 가능
3. DEFAULT값으로 오름차순(ASC)이 적용되며 DESC 옵션을 통해 내림차순으로 정렬 가능
4. SQL 문장의 제일 마지막에 위치
5. SELECT 절에서 정의하지 않은 칼럼 사용 가능
* Oracle에서는 NULL을 가장 큰 값으로 취급하며, SQL Server에서는 NULL을 가장 작은 값으로 취급한다


SEELCT
문장실행 순서 : FROM > WHERE > GROUPBY > HAVING > SELECT > ORDERBY





JOIN
두 개 이상의 테이블들을 연결 또는 결합하여 데이터를 출력
일반적으로 행들은 PK나 FK 값의 연관에 의해 JOIN이 성립
어떤 경우에는 PK, FK 관계가 없어도 논리적인 값들의 연관만으로 이 JOIN 성립 가능
5가지 테이블을 JOIN하기 위해서는 최소 4번의 JOIN과정이 필요하다. (N-1)

EQUI JOIN
2개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하는 경우에 사용
대부분 PK, FK 관계를 기반
컬럼명 앞에 테이블 명을 기술해줘야 함

NON EQUI JOIN
2개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하지 않는 경우에 사용
‘=’ 연산자가 아닌 BETWEEN, >, <= 등 연산자 사용

① NATURAL JOIN : 중복 칼럼을 하나로 통합, 제일 앞에 등장, 별칭 사용 불가
② LEFT OUTER JOIN : A LEFT OUTER JOIND은 A COLL = B COLL (+)과 동일
③ RIGHT OUTER JOIN
④ FULL OUTER JOIN
* JOIN은 하면 할수록 칼럼이 늘어난다
* FROM A, B, C일 때 A와 B를 먼저 JOIN하고, 묶인 칼럼을 C와 JOIN한다






참고 : https://youtu.be/PC3ypt_VGWI (SELECT문장 ~ 트랜잭션 관리 언어(TCL))