[SQLD 암기] SQL 기본

* SQL 종류

 - DML : SELECT, INSERT, UPDATE, DELETE

   절차적 DML : PL/SQL, T-SQL 무슨 데이터를 원하고, 어떻게 접근해야 하는 지

   데이터 부속어 : 호스트 프로그램 속에 삽입되어 사용되는 DML 명령어

 - DDL : CREATE, ALTER, DROP, RENAME

 - DCL : GRANT, REVOKE

 - TCL : COMMIT, ROLLBACK

 

* PK 제약조건

 CONSTRAINT TABLE_NAME PRIMARY KEY (COLUMN) (CREATE문 내부)

 CREATE TABLE TABLE_NAME (COLUMN DATA_TYPE PRIMARY KEY)

 ALTER TABLE TABLE_NAME ADD CONSTRAINT TABLE_PK PRIMARY KEY(COLUMN) (CREATE 외부)

 

* 테이블 컬럼 변경

 ALTER TABLE TABLE_NAME MODIFY (COLUMN DATA_TYPE DEFAULT NOTNULL)

 ALTER TABLE TABLE_NAME ALTER (COLUMN DATA_TYPE DEFAULT NOTNULL)

 

* 물리적 테이블 명

 - 문자로만 시작

 - 소문자, 대문자, _, $, #만 허용

 

* 외부 키

 - 테이블 생성 시 설정할 수 있음

 - 외래 키 값은 널 값을 가질 수 있음

 - 외래 키 값은 참조 무결성 제약을 받을 수 있음

 

* 테이블 제약조건

 - CHECK CONSTRAINT는 데이터베이스에서 데이터의 무결성을 위해 테이블의 특정 컬럼에 설정하는 제약

 - 기본 키는 반드시 테이블 당 하나의 제약만을 정의할 수 있음

 - 고유 키로 지정된 컬럼은 NULL 값이 있어도 됨

 - 외래 키는 테이블 간의 관계를 정의하기 위해 기본 키를 다른 테이블의 외래키가 참조

 

* 컬럼 삭제

 ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME

 

* 테이블 이름 변경

 RENAME TABLE_NAME TO NEW_NAME

 

* DELETE/MODIFY

 - CASCADE : MASTER 삭제 시 CHILD 삭제

 - SET NULL : MASTER 삭제 시 CHILD 해당 필드 NULL

 - SET DEFAULT : MASTER 삭제 시 CHILD 해당 필드 DEFAULT 값 설정

 - RESTRICT : CHILD 테이블에 PK 값이 없는 경우에만 MASTER 삭제 허용

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

 

* INSERT

 - AUTOMATIC : MASTER 테이블에 PK가 없는 경우 MASTER PK 생성 후 CHILD 입력

 - SET NULL : MASTER 테이블에 PK 가 없는 경우 CHILD 외부 키를 NULL 값으로 처리

 - SET DEFAULT : MASTER 테이블에 PK가 없는 경우 CHILD 외부 키를 기본 값으로 입력

 - DEPENDENT : MASTER 테이블에 PK 가 존재할 때만 CHILD 입력 허용

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

 

* 삽입

 INSERT INTO TABLE_NAME(COLUMN_NAME) VALUES(VALUE)

 INSERT INTO TABLE_NAME VALUES(VALUE)

 

* 수정

 UPDATE TABLE_NAME SET COLUMN = NEW_VALUE (WHERE)

 

* 삭제

 TRUNCATE TABLE TABLE_NAME : 스키마 정의 유지, 디스크 사용량 초기화, DDL, AUTO COMMIT

 DELETE FROM TABLE_NAME : 스키마 정의 유지, 디스크 사용량 초기화X, DML, 사용자 COMMIT

 DROP TABLE TABLE_NAME : 스키마 제거, 디스크 사용량 초기화, DDL, AUTO COMMIT

 DROP명령어는 테이블 삭제, TRUNCATE는 테이블을 초기상태로 만듦

 TRUNCATE는 UNDO를 위한 데이터를 생성하지 않아서 DELETE 보다 빠름

 

* 트랜잭션 특징

 - 원자성 : 모두 성공하든지 아니면 전혀 실행되지 않은 상태

 - 일관성 : 트랜잭션 실행 전 잘못되지 않으면 실행 후에도 잘못된 내용이 있으면 안됨

 - 고립성 : 트랜잭션 실행 도중에 다른 트랜잭션 영향 받으면 안됨

 - 지속성 : 성공적으로 수행되면 갱신한 데이터베이스 내용은 영구적으로 저장

 

* SAVEPOINT

 SAVE TRANSACTION SVPT1; ROLLBACK TRANSACTION SVPT1; (SQL server)

 SAVEPOINT SVPT1; ROLLBACK TO SVPT1; (ORACLE)

 

* NULL 연산

 - NULL과 연산하면 NULL 반환

 - NULL과 비교 연산하면 FALSE 반환

 

* 부정 비교 연산자

 !=, ^=, <> : 같지  않다

 NOT COLUMN = : 같지 않다

 NOT COLUMN > : 크지 않다

 

* NULL

 - ORACLE 에서는 IS NULL 사용

 - SQL server 에서는 =’’ 사용

 

* 내장함수

 - 함수의 입력 행수에 따라 단일 행 함수와 다중 행 함수로 나눔

 - 단일 행 함수는 SELECT, WHERE, ORDER BY, UPDATE의 SET 절에 사용 가능

 - 1:M 관계의 두 테이블을 조인할 경우 M 쪽에 단일 행이 출력되어 단일 행 함수 사용 불가능

 

* 단일 행 함수

 - LOWER(STRING), UPPER(STRING), ASCII(CHAR), CHAR(ASCII), LENGTH(STRING)

 - CONCAT(STRING1, STRING2) : 문자열1과 2를 연결, 오라클에서는 ||, SQLserver에서는 +도 사용

 - SUBSTRING(STRING, M, N) : M부터 N까지의 문자열 반환, N 생략 가능

 - LTRIM(STRING, CHAR) : 문자열 중 CHAR를 모두 제거, SQLserver에서는 지정문자 사용불가 공백제거 가능

 - RTRIM(STRING, CHAR) : CHAR가 나타날 때까지 문자열 모두 제거, SQLserver에서는 지정문자 사용불가

 - TRIM(leading|trailing|both CHAR FROM STRING) : 문자열에서 머리말, 꼬리말, 양쪽에 있는 지정 문자 제거

 - 날짜형 함수 : 1/24/60은 1분, 1/24/(60/10)은 10분

 - NVL, ISNULL(A,B) : A가 NULL이면 B출력

 - NULLIF(A,B) : A와 B가 같으면 NULL을 같지 않으면 A 반환

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

 

* 집계함수

 - COUNT(*) : NULL 포함한 행 수

 - COUNT(표현식), SUM(표현식), AVG(표현식) : NULL 제외한 행 수, 합, 평균

 

* GROUP BY와 HAVING

 - GROUP BY 절로 소그룹별 기준을 정하고, SELECT 절에 집계함수 사용

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

 - GROUP BY 절에서 SELECT 절과 달리 ALIAS 사용할 수 없음

 - 집계함수는 WHERE 절에 올 수 없음, WHERE절 수행 후 집계함수를 사용할 수 있는 GROUP BY 절

 - WHERE절은 전체 데이터를 GROUP으로 나누기 전에 행 미리 제거

 - HAVING 절은 GROUP BY 절의 기준 항목이나 소그룹의 집계함수를 이용한 조건을 표시할 수 있음

 - GROUP BY절에 의한 소그룹별로 만들어진 집계 데이터 중, HAVING절에서 제한 조건을 만족하는 내용만 출력

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

 

* ORDER BY 절

 - 기본적인 정렬 순서는 오름차순

 - 날짜형 데이터 타입을 오름차순으로 정렬하면 옛날 얘기가 더 먼저 출력

 - ORACLE에서는 NULL 값을 가장 큰 값으로 간주, SQLserver에서는 가장 작은 값으로 간주

 - SQL 문장으로 조회된 데이터들을 다양한 목적에 맞게 특정 컬럼을 기준으로 정렬

 - 컬럼명 대신 ALIAS 명이나 컬럼 순서를 나타내는 정수 사용 가능, 혼용 가능

 - GROUP BY 절을 사용하는 경우 ORDER BY 절에 집계함수를 사용할 수도 있음

 

* SELECT 절 실행 순서

 FROM – WHERE – GROUP BY – HAVING – SELECT – ORDER BY

 

*WITH TIES

 해당 값이 동일한 경우 함께 출력되도록 함, ORDER BY절과 함께 사용되어야 함

 

* JOIN

 - 여러 테이블에서 원하는 데이터 조회를 위해 N개의 테이블 중 최소 N-1개의 JOIN 조건 필요

 - 일반적으로 PK와 FK 연관성에 의해 성립

 - DBMS 옵티마이저는 FROM 절에 나열된 테이블들을 임의로 2개 정도씩 묶어서 JOIN

 - EQUI JOIN은 JOIN에 관여하는 테이블 간 컬럼 값들이 일치하는 경우 사용

 - EQUI JOIN은 ‘=’연산자에 의해서만 사용, 비교연산자는 NON EQUI JOIN

 - 대부분 NON EQUI JOIN을 수행할 수 있지만, 때로는 설계 상의 이유로 수행 불가능한 경우도 있음