[DATABASE] SQL 심화

+)

ABS(숫자)	절대값 계산	ABS(-4.5)=4.5
CEILING(숫자)	숫자보다 크거나 같은 최소의 정수	CEILING(4.1)=5
FLOOR(숫자)	숫자보다 작거나 같은 최소의 정수	FLOOR(4.1)=4
ROUND(숫자, m)	숫자의 반올림, m은 반올림 기준 자릿수	ROUND(5.36, 1)=5.40
LOG(숫자)	숫자의 자연로그 값을 반환	LOG(10)=2.30259
POWER(숫자, n)	숫자 n승 값을 계산	POWER(2, 3)=8
SQRT(숫자)	숫자의 제곱근 값을 계산(숫자는 양수)	SQRT(9.0)=3.0
SIGN(숫자)	숫자가 음수면 -1, 0이면 0, 양수면 1	SIGN(3.45)=1

+)

CHAR(n)	CHAR	정수 아스키 코드를 문자로 반환	CHAR(68)=D
NCHAR(n)	CHAR	n 값의 유니코드에 대응하는 문자를 반환	NCHAR(68)=D
CHARINDEX(str1, str2)	INTEGER	str2에서 부분 문자열 str1의 시작 위치를 반환	CHARINDEX(‘System’, ‘Database System’)=10
LEFT(str, n)	VARCHAR	str의 왼쪽에서부터 n개 문자열을 반환	LEFT(‘abcdefg’, 2)=‘ab’
RIGHT(str, n)	VARCHAR	str의 오른쪽에서부터 n개 문자열을 반환	RIGHT(‘abcd’, 2)=‘cd’
LEN(str)	INTEGER	str의 문자열 길이를 반환	LEN(‘abcdefg’)=7
LOWER(str)	VARCHAR	str을 소문자로 변환하여 반환	LOWER(‘AbcD’)=‘abcd’
UPPER(str)	VARCHAR	str을 대문자로 변환하여 반환	UPPER(‘AbcD’)=‘ABCD’
LTRIM(str)	VARCHAR	str의 왼쪽 공백을 제거	LTRIM(‘ two space here’)=‘two space here’
RTRIM(str)	VARCHAR	str의 오른쪽 공백을 제거	RTRIM(‘two space here ’)=‘two space here’
PATINDEX(‘%str1%’, str2)	INTEGER	str2에서 str1 문자열의 시작 위치를 반환	PATINDEX(‘%en_ure%’, ‘I ensure it’)=3
REPLACE(str1, str2, str3)	VARCHAR	str1에서 str2를 str3로 변환하여 반환	REPLACE(‘abcdefg’, ‘cd’, ‘dc’)=‘abdcefg’
REPLICATE(str, n)	VARCHAR	str을 n만큼 반복	REPLICATE(‘a’, 4)=‘aaaa’
REVERSE(str)	VARCHAR	str을 역순으로 출력	REVERSE(‘abcd’)==‘dcba’
SPACE(n)	VARCHAR	n만큼의 공백 문자를 반환	‘야구’+SPACE(5)+‘농구’=‘야구 농구’
SUBSTRING(str, n, m)	VARCHAR	str에서 n번째부터 m개 문자를 반환	SUBSTRING(‘abcdefg’, 3, 2)=‘cd’
ASCII(str)	INTEGER	str의 제일 왼쪽 문자의 아스키 코드 값을 반환	ASCII(‘Data’)=68
UNICODE(str)	INTEGER	str의 제일 왼쪽 문자의 유니코드 값을 반환	UNICODE(‘Data’)=68

+) REPLACE 문

야구가 포함된 도서를 농구로 변경한 후 도서 목록을 보임

SELECT   bookid, REPLACE(bookname, '야구', '농구') bookname, publisher, price FROM   Book;

+) SUBSTRING : 지정한 길이만큼의 문자열 반환

SELECT   SUBSTRING(name, 1, 1) "성", COUNT(*) "인원"

FROM   Customer

GROUP BY SUBSTRING(name, 1, 1);

+)

SYSDATETIME()	DATETIME2	SQL Server가 동작하는 컴퓨터의 날짜 및 시간을 출력	SYSDATETIME()=2013-03-01 16:39:47.8281250
GETDATE()	DATETIME	SQL Server가 동작하는 컴퓨터의 날짜 및 시간을 출력	GETDATE()=2013-03-01 16:42:43.257
DATENAME (datepart, date)	VARCHAR	date 값 중 datepart에 표시된 값을 문자열로 반환	DATENAME(YEAR, ‘2013-03-01’)=‘2013’
DATEPART (datepart, date)	INTEGER	date 값 중 datepart에 표시된 값을 숫자로 반환	DATEPART(YEAR, ‘2013-03-01’)=2013
DAY(date)	INTEGER	date 값 중 일을 반환	DAY(‘2013-03-01’)=1
MONTH(date)	INTEGER	date 값 중 월을 반환	MONTH(‘2013-03-01’)=3
YEAR(date)	INTEGER	date 값 중 년을 반환	YEAR(‘2013-03-01’)=2013
DATEDIFF(datepart, startdate, enddate)	INTEGER	datepar t에 지정된 부분에 대하여 startdate와 enddate를 비교하여 차이값을 반환	DATEDIFF(MONTH, ‘2013-03-01’, ‘2013-09-01’)=6
DATEADD(datepart, number, date)	DATETIME	datepart에 지정된 부분에 대하여 date 값에서 number만큼 더해서 반환	DATEADD(DAY, 5, ‘2013-03-01’) =‘2013-03-06 00:00:00.000’
ISDATE(expression)	INTEGER	정상적인 날짜 값인지 판단하여 거짓이면 0, 정상이면 1을 반환	ISDATE(‘2013-02-30’)=0

+)

year	yy.yyyy	hour	hh
quarter	qq.q	minute	mi.n
month	mm.m	second	ss.s
dayofyear	dy.y	millisecond	ms
day	dd.d	microsecond	mcs
week	wk.ww	nanosecond	ns
weekday	dw	TZoffset	tz

+) DATEADD 문

주문일로부터 10일후 매출을 확정

SELECT   orderid "주문번호", orderdate "주문일", DATEADD(dd, 10, orderdate) "확정" FROM   Orders;

+) SYSDATETIME 문 : SQL Server의 현재 시간 반환

서버에 설정된 현재시간과 오늘날짜 확인

SELECT SYSDATETIME();

SELECT DAY(SYSDATETIME());

NULL

- 아직 지정되지 않은 값

- 0 빈문자 공백등의 의미

- 비교연산자로 비교가 불가능

- NULL연산을 수행하면 NULL

집계 함수 사용시 주의할 점

- NULL+숫자 = NULL

- 집계함수 계산 시 NULL이 포함된 행은 집계에서 빠진다.

- 해당되는 행이 하나도 없을 경우 SUM,AVG함수의 결과는 NULL, COUNT는 0

NULL값을 확인하는 방법 - IS NULL, IS NOT NULL

- NULL값을 찾을때는 =가 아닌 IS NULL사용

- NULL이 아닌 값을 찾을 때는 <>이 아닌 IS NOT NULL을 사용

ISNULL(속성,값)

- 속성이 NULL이면 값으로 대체한다

Top n : 위에서 n개 출력

ex) select top 2 custid,name,phone from customer order by name; (가,나,다 순으로 정렬)

부속질의(subquery) 

: 하나의 SQL문 안에 다른 SQL문이 중첩된 nested 질의를 말한다.

 다른 테이블에서 가져온 데이터로 현재 테이블에 있는 정보를 찾거나 가공할 때 사용

 일반적으로 데이터가 대량일 경우 데이터를 모두 합쳐서 하는 조인보다 주요한 데이터만 찾아서 공급해주는 부속질의가 성능이 더 좋다

 주질의와 부속질의로 구성 -> 주 질의 = 외부 질의, 부속질의 = 내부질의

ex)

select sum(saleprice) from orders where custid =      //주질의

(select custid from customer where name = '박지성'); //부속 질의

스칼라 부속질의(scalar subquery)

: select 절에서 사용되는 부속질의

ex) select custid, (select name from customer cs where cs.custid = od.custid) as name , sum(saleprice) from orders od group by custid;

+) order 테이블에 각 주문에 맞는 도서 이름을 입력하시오

UPDATE orders set bookname = (SELECT bookname from book where book.bookid = orders.bookid);

인라인 view(inline view)?

- from 절에서 사용되는 부속질의

- 테이블 이름대신 인라인 뷰 부속질의

ex) 고객 번호가 2 이하인 고객의 판매액을 보이시오.

select cs.name,sum(od.saleprice)"total"

from (select custid,name from customer where custid<=2) cs, orders od

where cs.custid = od.custid

group by cs.name;

비교	=, ＞, ＜, ＞=, ＜=, ＜＞	단일	단일	가능
집합	IN, NOT IN	다중	단일	가능
한정(quantified)	ALL, SOME(ANY)	다중	단일	가능
존재	EXISTS, NOT EXISTS	다중	다중	필수

+) 평균 주문 금액 이하의 주문에 대해서 주문번호와 금액을 보이시오

SELECT   orderid, saleprice

FROM   Orders

WHERE   saleprice <= (SELECT AVG(saleprice) FROM Orders);

+) 대한민국에 거주하는 고객에게 판매한 도서의 총판매액을 구하시오.

SELECT   SUM(saleprice) "total"

FROM   Orders

WHERE   custid 

IN (SELECT custid  FROM Customer WHERE address LIKE '%대한민국%');

ALL,SOME

: ALL은 모두 SOME은 어떠한(최소한 하나라도)

ex) 3번 고객이 주문한 도서의 최고 금액보다 더 비싼 도서를 구입한 주문의 주문번호와 금액을 보이시오.

SELECT   orderid, saleprice

FROM   Orders

WHERE   saleprice > ALL (SELECT saleprice FROM Orders WHERE custid='3');

EXIST

ex) 대한민국에 거주하는 고객에게 판매한 도서의 총 판매액

SELECT   SUM(saleprice) "total"

FROM   Orders od

WHERE   EXISTS (SELECT * FROM Customer cs  WHERE address LIKE '%대한민국%' AND cs.custid=od.custid);

VIEW

ex)

create view view_book 

as (select* from book where bookname like '%축구%');

DATABASE 물리적 저장

1. 실제 데이터가 저장되는 곳은 보조기억장치

2. 가장 많이 사용되는 장치는 하드디스크

3. 엑세스 시간 = 탐색시간 + 회전지연시간 + 데이터 전송 시간

인덱스와 B-tree

인덱스 : 도서의 색인이나 사전과 같이 데이터를 쉽고 빠르게 찾을 수 있도록 만든 데이터 구조이다.

- 인덱스는 테이블에서 한개 이상의 속성을 이용하여 생성

- 빠른 검색과 함께 효율적인 레코드 접근이 가능

- 순서대로 정렬된 속성과 데이터의 위치만 보유하므로 테이블보다 작은 공간

- 저장된 값들은 테이블의 부분집합

- B-tree형태의 구조

- 데이터의 수정 삭제 등의 변경이 발생하면 인덱스의 재구성이 필요

+) B-tree구조

참고 : http://ddmix.blogspot.kr/2015/01/cppalgo-18-b-tree-search.html

클러스터 인덱스

: 연속된 키 값의 레코드를 묶어서 같은 블록에 저장하는 방법

 테이블 당 하나만 생성

 테이블의 데이터가 키 값에 따라 정렬 -> 손쉽게 검색

비 클러스터 인덱스

: 테이블과 별도로 구성 테이블당 여러개 생성가능

 테이블과 인덱스가 별도의 페이지에 저장 -> 많은 저장 공간이 필요

인덱스 생성 시 고려사항

- 인덱스는 where 절에 자주 사용되는 속성이어야 한다

- 조인에 자주 사용되는 속성

- 단일 테이블에 인덱스가 많으면 속도가 느려질 수 있다. -> 대량의 데이터에서 빠름

- 속성의 가공되는 경우 사용되지 않는다.

- 속성의 선택도가 낮을 때 유리 (속성의 모든 값이 다른 경우)

CREATE [UNIQUE] [CLUSTERED┃NONCLUSTERED] INDEX 인덱스이름

ON 테이블이름 (속성이름 [ASC┃DESC] [ ,...n ])

생성된 인덱스는 개체 탐색기의 해당 테이블의 인덱스를 통해 확인 가능

인덱스 재구성

ALTER INDEX {인덱스이름┃ALL}

ON 테이블이름 {REBUILD┃DISABLE┃REORGANIZE};

ALTER INDEX ix_Book ON Book REBUILD;

삭제

DROP INDEX 인덱스이름

ON 테이블이름;

DROP INDEX ix_Book ON Book;