SQLD 2과목_2 SQL 기본

# SQLD 2과목_2 기본

## SQL 문장 종류

1. DML (데이터 조작어) 

• SELECT : 데이터를 조회, 검색하기 위한 명령어로 RETRIEVE라고도 함
• INSERT / UPDATE / DELETE : 데이터에 변형을 가하는 종류의 명령어

2. DDL (데이터 정의어)

• CREATE / ALTER / DROP / RENAME : 테이블과 같은 데이터 구조를 정의하는데 사용

3. DCL (데이터 제어어)

• GRANT / REVOKE : 데이터베이스에 접근하고 객체들을 사용하도록 권한을 주고, 화수하는 명령어

4. TCL (트랜잭션 제어어)

• COMMIT / ROLLBACK / Savepoint : 논리적인 작업의 단위를 묶어 DML에 의해 조작된 결과를 작업단위(트랜잭션) 별로 제어하는 명렁어

 

## 테이블 칼럼에 대한 정의 변경

1. 오라클

• ALTER TABLE 테이블명 MODIFY (컬럼명1 데이터 유형, 컬럼명2 데이터 유형 ...);

2. SQL Server

• ALTER TABLE 테이블명 ALTER (컬럼명1 데이터 유형, 컬럼명2 데이터 유형 ...);

 

## NULL

• 공백, 숫자 0과는 전혀 다른 값
• 조건에 맞는 데이터가 없을 때의 공집합과도 다름
• 아직 정의되지 않은 미지의 값 또는 현재 데이터를 입력하지 못하는 경우를 의미.
• (모르는 값, 값의 부재를 의미, NULL과의 모든 비교는 알 수 없음(Unknown을 반환)

 

## 남아있는 데이터 관련 문제 풀이

• DELETE CASCADE 시 모두 삭제
• DELETE SET NULL 시 필드 값 NULL로 변경

 

## 제약 조건의 종류

• PRIMARY KEY (기본키) : 주키로 테이블당 1개만 생성 가능
• UNIQUE KEY (고유키) : 테이블 내에서 중복값이 없으며, NULL 입력 가능
• NOT NULL : 명시적으로 NULL 입력 방지
• CHECK : 데이터의 무결성을 유지하기 위해 특정 칼럼에 설정하는 제약
• FOREIGN KEY (외래키) : 테이블 생성 시 설정할 수 있고, NULL값을 가질 수 있으며, 테이블당 여러 개 생성 가능하고, 외래키 값은 참조 무결성 제약을 받을 수 있다.

 

## 테이블 생성의 주의사항

• 테이블명은 객체를 의미할 수 있는 적절한 이름 사용, 가능한 단수명 사용
• 테이블명은 다른 테이블의 이름과 중복되지 않아야 함
• 한 테이블 내 칼럼명은 중복되게 지정할 수 없음
• 테이블명을 지정 후 각 칼럼들은 괄호로 묶어 지정
• 각 칼럼은 콤마로 구분, 테이블 생성문 끝은 항상 세미콜론으로 끝남
• 칼럼에 대해 다른 테이블까지 고려, 일관성 있게 사용하는 것이 좋다
• 칼럼 뒤에 데이터 유형은 꼭 지정해야 함
• 테이블명, 칼럼명은 반드시 문자로 시작, 벤더별로 길이에 대한 한계 존재
• 벤더에서 사전에 정의한 예약어는 사용 불가
• A-Z, a-z, 0-9, _, $, # 문자만 허용된다

 

## COUNT(*), COUNT(PK칼럼)

• PK는 NULL값을 가질 수 없고, COUNT(*), COUNT(PK칼럼)은 항상 같은 값이 나온다.

 

## 테이블 칼럼 삭제 SQL 구문

• ALTER TABLE 테이블명 DROP COLUMN 삭제할 칼럼명;

 

## 테이블 이름 변경 SQL 구문

• RENAME 기존테이블명 TO 신규테이블명;

 

## 데이터 입력 SQL 구문

• INSERT INTO 테이블명 (칼럼 리스트) VALUES (칼럼 리스트에 넣을 VALUE값 리스트);
• INSERT INTO 테이블명 VALUES (전체 칼럼에 넣을 VALUE값 리스트);

 

## 데이터 수정 SQL 구문

• UPDATE 테이블명 SET 수정할 칼럼명 = 수정될 새로운 값;

 

## 데이터 조회 SQL 구문

• SELECT [ALL / DISTINCT] FROM 테이블명;
• ALL : Default 옵션, 중복된 데이터 모두 출력
• DISTINCT : 중복된 데이터 1건으로 처리해서 출력

 

## 참조동작

• CASCADE : Master 테이블 삭제 시 같이 삭제
• RESTRICT : Master 테이블에 PK없을 경우 외부키를 NULL값 처리
• AUTOMATIC : Master 테이블에 PK없을 경우 PK생성 후 입력
• DEPENDENT : Master 테이블에 PK 존재할때만 입력 허용

 

## 로그 관련

• DELETE : 로그를 남김
• DROP / TRUNCATE : 로그를 남기지 않음

 

## TRUNCATE TABLE

• 해당 테이블에 들어있던 모든 행들이 제거, 저장 공간을 재사용 가능하도록 해제 한다. (데이터 삭제, 디스크 사용량 초기화, 스키마 정의 유지)
• 테이블 구조를 완전히 삭제하기 위해서는 DROP TABLE 실행.

 

## TRUNCATE vs DELETE 비교

1. TRUNCATE : DDL, ROLLBACK 불가, AUTO COMMIT, 테이블을 최초 생성된 초기상태로 만든다.
2. DELETE : DML, COMMIT와 ROLLBACK 사용 가능, 사용자 COMMIT, 데이터만 삭제한다.

 

## 트랜잭션의 특성

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

 

## ROLLBACK

• 데이터 변경 사항이 취소되어 데이터의 이전 상태로 복구되며, 관련된 행에 대한 잠금(LOCKING)이 풀리고 다른 사용자들이 데이터 변경을 할 수 있게 된다.
• 롤백 구문을 만나면 최초의 BEGIN TRANSACTION시점까지 모두 롤백이 수행된다.

 

## 저장점 (SAVEPOINT)

• 롤백할 때 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아닌, 현 시점에서 저장점까지 트랜잭션의 일부만 롤백할 수 있다.

1. 오라클

• SAVEPOINT 저장점;
• ROLLBACK TO 지정한 저장점;

2. SQL SERVER

• SAVE TRANSACTION 저장점;
• ROLLBACK TRANSACTION 지정한 저장점;

 

## 용어 설명

• 트랜잭션은 데이터베이스의 논리적 연산단위로서 밀접히 관련되어 분리될 수 없는 한개 이상의 데이터베이스 조작을 의미
• 트랜잭션의 종료를 위한 대표적인 명령어로서 데이터에 대한 변경사항을 데이터베이스에 영구적으로 반영하는 커밋과 데이터에 대한 변경사항을 모두 폐기하고 변경전의 상태로 되돌리는 롤백이 있다.

 

## WHERE 절

• SQL을 사용하면서 테이블의 데이터를 조회할 때 원하는 데이터만 검색하기 위해 SELECT, FROM 절과 함께 WHERE 절을 이용, 조회되는 데이터의 조건을 지정하여 데이터를 제한할 수 있다.

 

## NULL의 연산

• NULL 값과의 연산은 NULL 값을 리턴
• NULL 값과의 비교연산은 거짓(FALSE)를 리턴
• NULL 값은 특정 값보다 크다, 작다라고 표현할 수 없다.
• NULL 값을 조건절에서 찾는 경우 IS NULL , IS NOT NULL만 사용한다.

 

## BETWEEN a AND b

• a와 b의 값 사이에 있으면 됨

 

## IN (리스트)

• 리스트에 있는 값 중 어느 하나라도 일치하면 됨

 

## 내장함수

• 함수의 입력 행수에 따라 단일행 함수, 다중행 함수로 구분한다.
• 단일행 함수는 SELECT, WHERE, ORDER BY, UPDATE의 SET절에 사용 가능하다.
• 1:M 조인이라도 M쪽에 출력된 행이 하나의 단일행 함수의 입력값으로 사용되므로 단일행 함수는 사용할 수 있다.
• 다중행 함수도 단일행 함수와 동일하게 단일값만을 반환한다.

 

## 단일행 문자형 함수 종류

• LOWER : 소문자로 바꿔줌
• UPPER : 대문자로 바꿔줌
• CONCAT (문자열1, 문자열2) : 문자열1, 2를 연결
• SUBSTR / SUBSTRING (문자열, m, n) : 문자열 중 m위치에서 n개의 문자 길에 해당하는 문자를 반환
• LENGTH / LEN : 문자열의 개수 반환
• LTRIM (문자열, 지정문자) : 첫 문자부터 확인, 지정 문자가 나타나면 해당 문자 제거
• RTRIM (문자열, 지정문자) : 마지막 문자부터 확인, 지정 문자가 나타나면 해당 문자 제거
• TRIM : 문자열에서 머리말, 꼬리말 또는 양쪽에 있는 지정 문자 제거

 

## NULL 관련 함수 종류

1. NVL / ISNULL (표현식1, 표현식2) : 표현식1의 결과값이 NULL이면 표현식2 값을 출력
2. NULLIF (표현식1, 표현식2) : 표현식1이 표현식2와 같으면 NULL을, 다르면 표현식1 출력
3. COALESCE (표현식1, 표현식2, ..) : 임의의 개수 표현식에서 NULL이 아닌 최초의 표현식을 나타낸다. 모든 값이 NULL이면 NULL 리턴

 

## 집계함수의 종류

1. COUNT (*) : NULL 포함, 행 수 출력
2. COUNT(표현식) : 표현식 값이 NULL인 값 제외, 행 수 출력
3. SUM (표현식) : NULL 값 제외 합계 출력
4. AVG (표현식) : NULL 값 제외 평균 출력
5. MAX (표현식) : 최대값 출력
6. MIN (표현식) : 최소값 출력
7. STDDEV (표현식) : 표준 편차 출력
8. VARIAN (표현식) : 분산 출력

 

## DUAL 테이블 특징

• SYS가 소유, 모든 사용자가 액세스 가능한 테이블
• 일종의 DUMMY 테이블
• DUMMY라는 문자열 유형의 칼럼에 'X'라는 값이 들어 있는 행을 1건 포함하고 있다.

 

## ORDERY BY 절

• 기본적인 정렬 순서는 오름차순(ASC : 낮->높)
• 숫자형 데이터는 가장 작은 값부터 출력
• 날짜형 데이터는 가장 빠른 값부터 출력
• 오라클에서는 NULL 값을 가장 큰 값으로 간주
• SQL SERVER는 NULL 값을 가장 작은 값으로 간주

 

## SELECT 문장 실행 순서

• FWGHSO : FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY

 

## TOP 관련

• EMP 테이블에서 급여가 높은 2명 내림차순으로 출력 : SELECT TOP(2) WITH TIES NAME, SAL FROM EMP ORDER BY SAL DESC;

 

## JOIN 조건 개수

• 여러 테이블로부터 원하느 데이터 조회 시 전체 테이블 개수에서 최소 (N-1)개 만큼의 JOIN 조건 필요

 

## JOIN

• 일반적으로 조인은 PK와 FK 값의 연관성에 의해 성립된다.
• DBMS 옵티마이저는 FROM 절에 나열된 테이블이 아무리 많아도 항상 2개의 테이블씩 짝지어 조인을 수행한다.
• EQUI JOIN은 조인에 관여하는 테이블 간의 컬럼 값들이 정확하게 일치하는 경우 사용되는 방법이다.
• EQUI JOIN은 '=' 연산자에 의해서만 수행, 그 외의 비교 연사자를 사용하는 경우 모두 NON EQUI JOIN이다.
• 대부분 NON EQUI JOIN을 수행할 수 있지만, 때로는 설계상의 이유로 수행이 불가능한 경우도 있다.