SQLD 핵심 정리 sql 기본 및 활용

24 May 2019

Reading time ~6 minutes

SQL 기본 및 활용

SQL 문장들의 종류

DML( 데이터 조작어) : 데이터를 검색,조회, 수정, 삭제 등
DDL : 데이터 구조(테이블,데이터베이스)를 생성,수정,삭제 등
DCL : 데이터베이스에 대한 권한 설정
TCL : 트랜잭션 다위로 묶어서 실행. 실행 후 적용할지(commit), 취소 할 지 (rollback) 정할 수 있음

절차적, 비절차적 데이터 조작어

비절차적 데이터 조작어 : 사용자가 어떤 데이터를 원하는지 (AS-IS)
절차적 데이터 조작어 : 어떻게 데이터에 접근 해야 하는지 (TO-BE)

NULL

모르는 값을 의미
값의 부재를 의미
NULL과의 모든 비교는 Unknown을 반환. (ex NULL + 1 = NULL)

테이블 생성의 주의사항

테이블명은 객체를 의미 할 수 있는 적절한 이름 사용(약어사용X). 가능한 단수형 사용
테이블 명은 다른 테이블의 이름과 중복되지 않아야 한다.
한 테이블 내에서는 칼럼명이 중복되게 지정될 수 없다.
칼럼에 대해서는 다른 테이블까지 고려하여 데이터베이스 내에서는 일관성 있게 사용 하는 것이 좋음.(데이터 표준화 관점)
테이블명과 칼럼명은 반드시 문자로 시작. 벤더별로 길이에 대한 한계 존재
벤더에서 사전에 정의한 예약어(reserved word) 사용 불가능
알파벳, 0-9, _, $, # 문자만 허용됨. ( 단 utf-8 설정시 그외 도 사용 가능. 한글이라던지)

테이블의 카운트

NULL 로만 이루어진 로우도 1개로 카운트
만약 null 은 세기 싫다면, where [칼럼명] = (not null) 을 해주면 된다.

외래키 특징

테이블 생성시 설정 할 수 있고, 생성 후에도 alter 로 추가,삭제 할 수 있다.
외래키 값은 널 값을 가질 수 있다.
한 테이블에 여러개 존재 가능 하다.
외래키 값은 참조 무결성 제약을 받을 수 있다.

참조 동작

1) delete(modify) action

cascade : master 삭제 시 child 같이 삭제
set null : master 삭제 시 해당 필드 NULL
set default : master 삭제 시 해당 필드 default 값으로 설정
restrict : child 테이블에 PK 값이 없는 경우만 master 삭제 허용
no action : 참조무결성을 위반하는 삭제/수정 액션을 취하지 안흥ㅁ.

2) insert action

automatic : master 테이블에 pk가 없는 경우 master pk 를 생성 후 child 입력
set null : master 테이블에 pk가 없는 경우 child 외부키를 null 값으로 처리
set default : master 테이블에 pk가 없는 경우 child 외부키를 지정된 기본값으로 입력
dependent : master 테이블에 pk가 존재할 때만 child 입력 허용
no action : 참조무결성을 위반하는 입력 액션을 취하지 않음.

테이블 삭제

delete from [테이블명]; : 테이블 내의 모든 행 삭제. 로그 남음
truncate table [테이블명]; : 테이블 내의 모든 행 삭제 후 데이터 free(사용할 수 있는 공간으로). 로그 남지 않음
drop table [테이블명]; : 테이블 자체를 삭제

중복을 제거하는 명령어

distinct

ex) select distinct [칼럼명] from [테이블명];

트렌잭션의 4가지 특성

1) 원자성 : 트랜잭션에서 정의된 연산들은 모두 성공적으로 실행 or 전혀 실행X ( All or Nothing)

2) 일관성 : 트랜잭션이 실행 되기 전의 데이터베이스 내용이 정상 -> 트랜잭션 실행 후 데이터베이스도 정상

3) 고립성 : 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들면 안된다.

4) 지속성 : 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장된다.

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

1) Dirty Read : 다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 데이터를 읽는 것

2) non - repeatable read : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행 했는데, 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제하는 바람에 두 쿼리 결과가 다르게 나타나는 현상

3) Phantom read : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 첫번째 쿼리에서 없던 유령 레코드가 두번째 쿼리에서 나타나는 현상

ORACLE VS SQL server

1) Oracle 에서는 DDL 문장 수행 후 자동으로 COMMIT 수행 / SQL server에서는 DDL 문장 수행 후 자동으로 COMMIT 수행 X

2) Oracle에서 DDL 문장의 수행은 내부적으로 트랜잭션을 종료 시킴 / SQL server에서는 DDL 문장도 트랜잭션의 범주에 포함

3) insert ~ value(‘’) 로 입력하였을시 Oracle에서는 NULL 이, SQL server에는 ‘’ 으로 들어가며, select시도 동일하게 해야 한다.

4) null 확인 함수

sql server : isnull
oracle : nvl
mysql : ifnull

5) order by 의 null : oracle : 제일 큰값으로 간주(오름차순시 맨 뒤), mysql : 제일 작은값으로 간주(오름차순시 맨 앞)

트랜잭션은 이름표를 달아 범위 구분을 할 수 있다

ex)

  begin transaction
  save transaction sp1
  do A
  save transaction sp2
  do B
  roll back sp2
  commit

위와 같이 실행 시 B는 롤백되고, A는 적용 된다 .

## 연산자가 여럿일 떄 순서

괄호 > NOT > 비교 연산자 > AND > OR

부정 비교 연산자

칼럼명 !=
칼럼명 ^-
칼럼명 <> ( 칼럼명 > or 칼럼명 < 합친거와 같은 의미)
NOT 칼럼명 =
NOT 칼럼명 >
예외 NULL 판단은 칼럼명 is not null or is null 만 가능

날짜 표현식

 to_char([날짜데이트], 'YYYYMMDDHH24) : YYYY년 MM월 DD일 HH24 시 ( 00분~ 59분59초)
 to_char([날짜데이트], 'YYYYMM') 날짜의 년 월, 즉 일,시간은 상관 없음으로 yyyy년 m월 1일 ~ m월 마지막일
 //
 to_date ('[날짜]', 'YYYYMMDDHH24') : YYYY년 MM월 DD일 HH24 시 (00시 00분)
 to_date ('[날짜]', 'YYYYMM) : YYYY년 MM월 01일 00시 00분 00초
 +- [n] : n일만큼 더하고 빼고 .  +3 : 3일 뒤, + 1/24 : 1시간 뒤 , +1/24/60 : 1분 뒤 

단일행 함수 vs 다중행 함수

단일행 함수 : 각 행마다 변경점을 적용하는 함수
다중행 함수 : 여러개의 행을 통해서 결과나 변경 등을 적용하는 함수

![관련설명]”https://blog.naver.com/skeleton70/130004470034”

select case문

-select문은 c언어류의 switch와 비슷하다고 보시면 됩니다.

(searched_case_expression)
 select 
  case when [칼럼명] = [사례1] then [사례1 변환값]
       when [칼럼명] = [사례2] then [사례2 반환값]
       end as [새로운칼럼명]              <= 기조 칼럼명 사용하려면 앞에 칼럼명 = case 로시작하면 됩니다.
  from [테이블명]

(simple_case_expression) // searhed 와 다른건 문장의 순서가 다르고 = 이 안들어 가는 겁니다.
  select 
  case when [칼럼명] [사례1] then [사례1 변환값]
       when [칼럼명] [사례2] then [사례2 반환값]
       end as [새로운칼럼명]              
  from [테이블명]

예 ) temp = ( 1,1,2,3,3 ) selectcase

group by

같은 데이터를 묶어서 같이 묶은 것중 맨 앞의 하나만 보여줍니다. groupby

만약 group by 로 묶을 경우, 앞의 select 에서 언급한 내용만 뽑아와서 연산하기에, 그 외의 데이터 접근시 오류가 발생.

## having by

## order by

asc : 오름차순 / desc : 내림차순 ( 비어두면 asc)
sql 문장으로 조회된 데이터들을 다양한 목적에 맞게 특정 컬럼을 기준으로 정렬하는데 사용
dbms 마다 null 값에 대한 정렬 순서가 다름
group by 를 사용할 때에는 order by 절에 집계 함수를 사용 할 수 있음
order by 절에 컬럼명 대신 alias 명이나 칼럼 순서를 나타내는 정수를 사용 가능하다. (혼용도 가능)

(alias 명이란 ?

alias 처럼 다른 이름으로 표에 타나 탤 때 )

## 혼합된 select 문의 실행 순서

from -> where -> group by -> having -> select -> order by

## TOP()

제일 높은 순으로 위에서부터 갯수를 나타낸다.
동일한 등수를 같이 나열하려면 TOP() with ties 처럼 뒤에 with ties를 붙이면 된다.
select 바로 뒤에 쓴다.

n개의 테이블을 조인 할때 최소 필요한 join 의 갯수

equi 조인

equi join

 select [테이블1].칼럼명, [테이블2].칼럼명, [테이블3]
 from [테이블1],[테이블2],[테이블3]
 where [테이블1].칼럼명 = [테이블3].칼럼명
 and [테이블2].칼럼명 = [테이블3].칼럼명

식으로 where 절에서 = 을 이용해 join하기에 equi join이라고 한다.

ansi 표준은 위와 같다.

join 설명

1) 일반적으로 join 은 pk와 fk 값의 연관성에 의해 성립된다.

2) dbms 옵티마이저는 from 절에 나열된 테이블이 아무리 많아도 항상 2개의 테이블씩 짝을 지어 join을 수행한다.

3) equi join은 join에 관여하는 테이블 간의 컬럼 값들이 정확하게 일치하는 경우에 사용된다

4) equi join 은 ‘=’ 연산자에 의해서만 수행되며, 그 외의 비교 연산자를 사용하는 경우에는 모두 non equi join이다.

5) 대부분 non equi join을 수행할 수 있지만, 때로는 설계상의 이유로 수행이 불가능한 경우도 있다.