SQLD 요약2 SQL 기본 및 활용

데이터 모델링의 결과물인 ER모델을 기반으로 관계형 DB가 구축된다.

 

SQL(Structed Query Language)

= SQL은 관계형 DB의 표준언어이다.

= 구조적, 집합적, 선언적인 언어이다.

 

SQL문의 종류

1. select : 데이터를 조회하는 명령문 

2. DML(Data Manipualtion Language) : 데이터 조작(입력, 수정, 삭제)하는 명령문 ->(UPDATE, DELETE, INSERT, SELECT)

3. TCL(Transaction Control Language) : 논리적 작업 단위 (DML)에 의해 조작된 결과를 트랜잭션으로 제어하는 명령문 (Rollback, Commit)

4. DDL(Data Definition Language) : 데이터 구조를 정의(생성, 변경, 삭제)하는 명령문 -> (CREATE, DROP, ALTER, RENMAE)
-> (테이블 명 변경) RENAME STADIUM TO SATIUM_JSC

5. DCL(Data Control Language) : 데이터베이스 객체에 대한 권한을 부여.회수하는 명령문->(Grant, Revoke)

 

DELETE 옵션

- 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 외부 키를 default값으로 처리

- Dependent : Master PK가 존재할 때만 Child 입력 가능

 

+ DROP/ TRUNCATE/ DELETE 차이

DROP	TRUNCATE	DELETE
DDL	DDL	DML
ROLLBACK 불가능	ROLLBACK 불가능	COMMIT 이전 ROLLBACK 가능
AUTO COMMIT	AUTO COMMIT	사용자 COMMIT
테이블이 사용한 storage 모두 release	최초 생성시 할당된 storage만 남기고 release	데이터 모두 삭제 but storage는 release 되지 않음
테이블 정의 자체를 삭제	최초 생성된 초기 상태로 만듦	데이터만 삭제

집계함수 

= 다중행을 입력받아 단일행 값을 리턴하는 함수

= 괄호가 여러가 있다면 가장 안쪽에 있는 괄호부터 실행된다.

 

JOIN종류

1. INNER JOIN

2. OUTER JOIN

   - Oracle에서는 (+) 기호를 사용해 기준집합을 지정 ( (+) 붙지 않은 테이블이 기준집합)

   - ANSI에서는 LEFT, RIGHT, FULL로 기준집합 지정

3. CROSS JOIN

   - 조인조건없이 조인을 할 때(결과 : 카티션 곱 (MxN))

 

JOIN 

- PK와 FK의 연관에 의해 성립된다.

- PK, FK관계가 없어도 논리적인 값들의 연관으로 성립 가능하다.

 

1. Euqi JOIN (동등조인, 이퀄조인)

- JOIN에 관여하는 두 테이블 간 칼럼 값이 정확하게 일치하는 경우에만 사용한다

- '=' 연산자로 하는 조인

 

2. Non Equi JOIN

- JOIN에 관여하는 두 테이블 간 칼럼 값이 정확하게 일치하지 않는 경우에 사용됨.

- Between, 부등호 등으로 하는 조인

- 대부분 적용할 수 있띠만 설계상 (데이터 모델에 따라) 수행이 불가능한 경우도 있다.

 

단일 행 함수 (Single Row Functions)

- DBMS에서 제공하는 내장 함수를 이용하면 각 행별로 데이터를 가공하거나 변환할 수 있다

- 함수는 인자 값(Argument)과 리턴 값이 존재 (인자 값은 여러 개가 될 수 있으며 리턴 값은 항상 1개이다)

 

함수의 종류는 다양하지만 몇개 적어본다면..

ORACLE/MSSQL순서
LOWER/UPPER	소문자/대문자로 변환하는 함수
ASCII	문자나 숫자를 ASCII번호로 바꿔줌
CHR/CHAR	아스키 번호를 문자나 숫자로 바꿔줌
CONCAT(문자열1, 문자열2)	두 문자열을 연결해주는 함수
SUBSTR(문자열, m [,n]) / SUBSTRING(문자열,m [,n])	m의 위치에서 n개의 문자 길이에 해당하는 문자를 리턴
LENGTH / LEN (문자열)	문자열의 길이 리턴
LTRIM(문자열 [, 지정문자])	가장 왼쪽부터 지정문자가 연속되는 동안 해당 문자를 제거한다(디폴트 : 공백)
RTRIM(문자열 [, 지정문자])	가장 오른쪽부터 지정문자가 연속되는 동안 해당 문자를 제거한다(디폴트 : 공백)
TRIM(문자열 [, 지정문자])	양쪽 끝에있는 지정문자가 연속되는 동안 해당 문자를 제거한다

 

비절차적 데이터 조작어 (DML)는 사용자가 무슨 (WHAT) 데이터를 원하는 지만을 명세한다

절차적 데이터 조작어는 어떻게(HOW) 데이터에 접근해야 하는지 명세한다. 절차적 데이터 조작어로는 PL/SQL, T-SQL(SQL-SERVER)가 있다.

 

기본키 특징 (Primary Key)

- 한 테이블에 하나만 지정 가능 

- 자동으로 UNIQUE한 인덱스 생성

- NULL값 입력 불가

 

UNIQUE : NULL 가능, 행을 고유하게 식별하기 위한 고유 키( 중복 x)

외래키 특징

1. 테이블 생성시 설정할 수 있다.

2. 외래키 값은 NULL값을 가질 수 있다.

3. 한 테이블에 여러개 존재할 수 있다.

4. 외래키 값은 참조 무결성 제약을 받을 수 있다.

 

TRANSACTION의 4가지 특성

1. 원자성 (Automicity)

: 정의된 연산들이 모두 성공적으로 실행되던지 전혀 실행되지 않은 상태로 남아 있어야 함.

2. 일관성 (Consistency)

: 실행전 db의 내용이 잘못되어 있지 않다면 실행 이후 db에  내용이 잘못되어 있으면 안된다.

3. 고립성 (Isolation)

: 트랜잭션의 실행 중 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들면 안된다.

4. 지속성 (Durability)

: 트랜잭션이 성공적으로 수행되면, 영구적으로 반영되어 저장된다.

 

트랜잭션 격리성이 낮을 경우 발생할 수 있는 문제점

1. Dirty Read

: 다른 트랜잭션에 의해 수행되었지만, 아직 commit 되지 않은 Dirty한 데이터를 읽는 것

2. Non-Repeatable Read

: 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데,

그 사이 다른 트랜잭션이 값을 수정/삭제 해서 두 쿼리 결과가 다르게 나타나는 현상

3. Phantom Read

: 한 트랜잭션 내에서 같은 쿼리를 두번 수행했는데,

첫 번째 쿼리에서 없던 유령 레코드(팬텀 레코드)가 두 번째 쿼리에서 나타나는 현상

 

ROLLBACK은 COMMIT되지 않은 모든 트랜잭션을 롤백한다.

 

다중행 함수도 단일행 함수와 동일하게 단일 값만을 반환한다.

NULL

= 알 수 없는 값, 0도 아니고 공백(' ')도 아님

= [SQL Server]에서 a=''로 넣으면 -> NULL이 아니고 공백으로 들어간다.

 

단일행 NULL관련 함수

1. NVL(표현식1 / 표현식2 ) / ISNULL(표현식1, 표현식2)

: 표현식1의 결과값이 NULL이면 표현식2의 값을 출력 (표현식1과 표현식2의 데이터 타입이 같아야 한다.)

2. NULLIF(표현식1, 표현식2)

: 표현식1과 표현식2가 같으면 NULL을 반환, 같지 않으면 표현식1을 반환

3. COALESCE(표현식1, 표현식2 .... )

: NULL이 아닌 최초의 표현식을 나타냄, 모두 NULL이면 NULL을 리턴

 

TOP n 질의문에서

n에 해당하는 값이 동점인 경우 함께 출력되도록 하는 옵션 : WITH TIES

SELECT TOP 3 WITH TIES 팀명, 승리건수

FROM 팀별선정

ORDER BY 승리건수 DESC