SQL 고급

※SQL 내장 함수

-상수나 속성 이름을 입력 값으로 받아 단일 값을 결과로 반환한다.

-모든 내장 함수는 최초에 선언될 때 유효한 입력 값을 받아야 한다.

-SELECT절,WHERE절,UPDATE절 등에서 모두 사용 가능

 

SELECT ...함수명(인자1,인자2,...)

FROM 테이블이름

WHERE ...열이름=함수명(인자1, 인자2,...);

 

UPDATE 테이블이름

SET ...열이름=함수명(인자1,인자2,...);

 

※MY SQL에서 제공하는 주요 내장 함수

※숫자 함수의 종류

※문자 함수

※날짜.시간함수

-날짜와 시간 부분을 나타내는 인수는 'format'으로 표기

※format의 주요 지정자

※NULL 값 처리

-NULL 값이란 아직 지정되지 않은 값, 알 수도없고, 적용할 수도 없다.(0,빈 문자, 공백 과는 다른 뜻)

-NULL값은 =,<>등과 같은 연산자로 비교하지 못한다.

 

-NULL값에 대한 연산과 집계함수

1.'NULL+숫자' 연산의 결과는 NULL

2.집계 함수를 계산할 때 NULL이 포함된 행은 집계에서 빠진다.

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

 

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

1.IS NULL: NULL인 값을 찾을 때 =연산자가 아닌 IS NULL

2.IS NOT NULL: NULL이 아닌 값을 찾을 때 <>연산자가 아니라 IS NOT NULL

 

-IFNULL함수

1.IFNULL 함수는 NULL 값을 다른 값으로 대치하여 연산하거나 다른 값으로 출력하는 함수

2.IFNULL함수를 사용하면 NULL값을 임의의 다른 값으로 변경할 수 있다.

 

ex)IFNULL(속성,값) //속성 값이 NULL이면 '값'으로 대치

 

※행번호 출력

-MY SQL에서는 변수 이름 앞에 @기호를 붙임

-치환문에는 SET과 := 기호를 사용

 

ex)고객 목록에서 고객번호,이름,전화번호를 앞의 두 명만 보이시오.

SET @seq:=0;

SELECT (@seq:=@seq+1)'순번',custid,name,phone

FROM Customer

WHERE @seq<2;

 

※부속질의(subquery)

-하나의 SQL 문 안에 다른 SQL 문이 중첩된(nested)질의

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

 

ex)

SELECT SUM(saleprice)

FROM Orders

WHERE custid = (SELECT custid

                       FROM Customer

                       WHERE name= '박지성'); //빨간 부분이 부속질의

 

-조인과 부속질의의 차이

조인: Customer 테이블과 Orders 테이블의 고객번호로 조인한 후 필요한 데이터를 추출

부속질의: Customer 테이블에서 박지성 고객의 고객번호를 찾고, 찾은 고객번호를 바탕으로 Orders 테이블에서 확인

 

-데이터가 대량일 경우 데이터를 모두 합쳐서 연산하는 조인보다, 필요한 데이터만 찾아서 공급해주는 부속질의의 성능이 더 좋다.

 

-주질의(main query)(외부질의)와 부속질의(subquery)(내부질의)로 구성된다.

 

-위치와 역할에 따라 SELECT 부속질의(스칼라 부속질의), FROM 부속질의(인라인 뷰), WHERE 부속질의(중첩질의)로 구분

1.스칼라 부속질의(scalar subquery)

-SELECT 절에서 사용되며 단일 값을 반환

-결과 값이 다중 행이거나 다중 열이라면 DBMS는 에러를 출력

-결과가 없는 경우 NULL 값을 출력

-일반적으로 SELECT 문과 UPDATE SET절에 사용된다.

 

-스칼라 값이란 벡터 값에 대응되는 말로 단일 값을 의미

 

2.인라인 뷰(inline view,table subquery)

-FROM 절에서 결과를 뷰 형태로 반환

-부속질의 결과 반환되는 데이터는 다중 행, 다중 열이어도 상관없다.

-가상의 테이블인 뷰 형태로 제공되기 때문에 상관 부속질의로 사용될 수는 없다.

 

3.중첩질의(nested subquery,predicate subquery)

-WHERE 절에 술어와 같이 사용되며 결과를 한정시키기 위해 사용

-주질의에 사용된 자료 집합의 조건을 WHERE절에 서술

-주질의의 자료 집합에서 한 행씩 가져와 부속질의를 수행하며, 연산 결과에 따라 WHERE 절의 조건이 참인지 거짓인지 확인하여 참일 경우 주질의의 해당 행을 출력

 

-Scalar_expression {비교연산자 (=,<>,!=,>,>=,!>,<,<=,!<) }

                   {ALL | SOME | ANY } (부속질의)

 

-WHERE [NOT] EXISTS (부속질의)

 

※뷰(VIEW)

-하나 이상의 테이블을 합하여 만든 가상의 테이블

-합한다는 말은 SELECT 문을 통해 얻은 최종 결과를 뜻함

-결과를 가상의 테이블로 정의하여 실제 테이블처럼 사용할 수 있도록 만든 데이터베이스 개체

 

-뷰는 테이블처럼 사용할 수 있지만 SELECT 문을 제외한 일부 물리적인 테이블의 갱신작업을 수행하는 데 제약이 있다.

 

-INSERT, UPDATE, DELETE 등의 DML작업은 경우에 따라 수행되지 않음

 

-기본키를 포함하지 않는 수정 요청이나 베이스테이블(base table) 두 개 이상에서 속성을 포함하는 수정 요청은 제약을 위반할 가능성이 있기 때문에 금지됨

 

-Orders 테이블에 고객이름과 도서이름을 추가하는 방법2가지

1.실제 물리적인 테이블에 열을 추가하여 데이터를 넣는 방법

-실제 데이터베이스에 물리적인 열을 추가하면, 사용중인 프로그램을 수정해야 하며, 데이터베이스의 저장 용량도 늘어남

 

2.Orders 테이블, Customer 테이블, Book테이블을 조인한 후 가상의 테이블인 뷰 Vorders를 생성하는 방법

-실제 데이터를 디스크에 저장하지 않고 단지 뷰를 생성할 때 사용한 SELECT 문의 정의를 DBMS가 저장한다.

-DBMS는 뷰의 정의를 참조하여 질의를 수행하고 그 결과를 사용자에게 반환

-사용자는 DBMS에 저장된 뷰의 정의를 알지 못해도 일반적인 테이블과 같이 사용할 수 있다.

-테이블에 사용할 수 있는 모든 연산을 사용할 수 있다.

 

※뷰의 장점

1.편리성 및 재사용성:

-미리 정의된 뷰를 일반 테이블처럼 사용할 수 있기 때문에 편리함

-사용자가 필요한 정보만 요구에 맞게 가공하여 뷰로 만들어 쓸 수 있다.

-자주 사용되는 질의를 뷰로 미리 정의해 재사용할 수 있다.

 

2.보안성:

-각 사용자별로 보안이 필요한 데이터를 제외하여 선별하여 보여줌

 

3.독립성:

-논리 데이터베이스의 원본 테이블의 구조가 변해도 응용 프로그램에 영향을 주지 않도록 하는 논리적 독립성을 제공하는 방법

 

※뷰의 생성

-CREATE VIEW 뷰이름[ (열이름[,...n])]

AS SELECT 문

 

ex)Book 테이블에서 '축구'라는 문구가 포함된 자료만 보여주는 뷰

 

뷰에 사용할 SELECT 문

SELECT *

FROM Book

WHERE bookname LIKE '%축구%';

 

뷰 정의문

CREATE VIEW vw_Book

AS SELECT *

FROM Book

WHERE bookname LIKE '%축구%';

 

※뷰의 수정

-CREATE OR REPLACE VIEW 뷰이름 [ (열이름 [,...n])]

AS SELECT 문

 

ex)뷰 vw_Customer는 주소가 대한민국인 고객, 이 뷰를 영국을 주소로 가진 고객으로 변경

 

CREATE OR REPLACE VIEW vw_Customer(custid,name,address)

AS SELECT custid,name,address

     FROM Customer

     WHERE address LIKE '%영국%';

 

결과

SELECT *

FROM vw_Customer;

 

※뷰의 삭제

-DROP VIEW 뷰이름 [,...n];

 

ex) 뷰 vw_Customer를 삭제

DROP VIEW vw_Customer;

 

결과

SELECT *

FROM vw_Customer;

 

※시스템 뷰(System VIEW)

-DBMS에서는 데이터베이스 개체(테이블, 함수, 뷰 등)나 시스템의 통계 정보 등을 사용자가 직접 확인할 수 있도록 시스템 뷰를 만들어 제공함

-데이터 딕셔너리 뷰(Data Dictionary View), 시스템 카탈로그(System Catalog)라고도 함

-DBMS 관련 정보를 테이블 형태로 만들어 실시간으로 제공

-데이터베이스 튜닝이나 기타 문제들을 해결

-MySQL 시스템 뷰는 INFORMATION_SCHEMA 데이터베이스에 저장되어 있으며 SELECT으로 조회 가능

 

※인덱스(INDEX)

-데이터를 DBMS만의 고유한 방식으로 저장하여 관리한다

-실제 데이터가 저장되는 곳은 보조기억장치로 하드디스크,SSD,USB 메모리 등이 있다.

-DB는 버퍼에 자주 사용하는 데이터를 저장해두며 LRU(Least Recently Used)알고리즘을 이용하여 사용빈도가 높은 데이터 위주로 저장하고 관리함

 

-빠른 연산속도와 느린 디스크의 액세스 속도차이의 문제를 줄이기 위해 주기억장치에 DBMS가 사용하는 공간 중 일부를 버퍼 풀(Buffer Pool Memory)로 만들어 사용하는 방법이 있다.

 

-데이터 검색 시 DBMS는 버퍼 풀에 저장된 데이터를 우선 읽어 들인 후 작업을 진행

-MySQL의 저장장치 엔진은 플러그인 방식으로 선택할 수 있다.(InnoDB 엔진이 기본으로 설치)

 

-MySQL의 InnoDB 엔진 데이터베이스의 파일

1.데이터 파일(ibdata)

-사용자 데이터와 개체를 젖아

-테이블과 인덱스로 구성

-확장자는 *.ibd

 

2.폼파일(frm File)

-테이블에 대한 각종 정보와 테이블을 구성하는 필드,데이터 타입에 대한 정보 저장

-데이터베이스 구조 등의 변경사항이 있을 때 자동으로 업데이트됨

 

※인덱스(index)

-인덱스(index,색인)란 자료를 쉽고 빠르게 찾을 수 있도록 만든 데이터 구조

-데이터베이스에서 인덱스란 원하는 데이터를 빨리 찾기 위해 투플의 키 값에 대한 물리적 위치를 기록해둔 자료구조

-일반적인 RDBMS의 인덱스는 대부분 B-tree 구조로 되어 있다.

 

-인덱스의 특징

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

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

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

4.저장된 값들은 테이블의 부분집합이 된다.

5.일반적으로 B-tree 형태의 구조를 가진다.

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

 

※B-tree(balanced-tree)

-데이터의 검색 시간을 단축하기 위한 자료구조

-루트(root) 노드, 내부(internal) 노드, 리프(leaf) 노드로 구성되며, 리프 노드가 모두 같은 레벨에 존재하는 균형(balanced) 트리

-B-tree의 각 노드는 키 값과 포인터를 가짐

-키 값은 오름차순으로 저장, 키 값 좌우에 있는 포인터는 각각 키 값보다 작은 값과 큰 값을 가진 다음 노드를 가리킴

-키 값을 비교하여 다음 단계의 노드를 쉽게 찾을 수 있음

-B-tree는 키 값이 새로 추가되거나 삭제될 경우 동적으로 노드를 분할하거나 통합하여 항상 균형 상태를 유지

 

※MySQL 인덱스

-클러스터 인덱스(clusterd index)와 보조 인덱스(secondary index)로 나누어지며 모두 B-tree 인덱스를 기본으로 함

 

-클러스터 인덱스:

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

2.테이블당 하나만 생성, B-tree 인덱스의 리프 노드에서 페이지의 주소 값 대신 테이블의 열 자체가 저장되는 형태

3.키 값에 의한 동등 및 범위(BETWEEN)검색 모두에 유리하다.

4.테이블의 데이터가 키 값에 따라 정렬된 형태로 저장 되어 특정 값을 쉽게 찾을 수 있으며 범위로 검색한다고 해도 이미 정렬되어 있으므로 손쉽게 검색할 수 있다.

 

-보조 인덱스:

1.속성의 값으로 B-tree 인덱스를 구성

2.리프 노드의 각 행은 해당 페이지의 주소 값을 저장

3.실제 테이블의 자료가 아무리 무작위로 저장되어 있더라도 쉽게 찾을 수 있다.

4.테이블당 여러 개를 만들 수 있다.

5.테이블의 컬럼(속성) 하나만을 대상으로 단일 컬럼 인덱스뿐만 아니라 여러 개의 컬럼을 복합적으로 결합하여 사용하는 인덱스도 만들 수 있다.

 

-클러스터 인덱스와 보조인덱스는 보통 같이 사용됨.

ex)Book 테이블에서 bookid를 클러스터 인덱스, bookname을 보조 인덱스로 사용하여 bookid와 bookname 모두 빠른 검색을 필요로 하는 경우

 

MySQL은 bookid를 검색할 경우 클러스터 인덱스를 이용하고, bookname을 검색할 경우 보조 인덱스를 이용하여 bookid를 찾은 다음, 다시 bookid에 대한 클러스터 인덱스를 사용

 

※인덱스의 생성

-인덱스 생성의 고려사항

1.인덱스는 WHERE 절에 자주 사용되는 속성이어야 한다.

2.인덱스는 조인에 자주 사용되는 속성이어야 한다.

3.단일 테이블에 인덱스가 많으면 속도가 느려질 수 있다(테이블 당 4~5개 정도 권장)

4.속성이 가공되는 경우 사용하지 않는다.

5.속성의 선택도(1/서로 다른 값의 개수)가 낮을 때 유리하다(속성의 모든 값이 다른 경우).

 

-CREATE [UNIQUE] INDEX [인덱스이름]

ON 테이블이름 (컬럼 [ASC | DEXC] [{, 컬럼 [ASC | DESC]}...])[;]

 

ex)Book 테이블의 publisher,price 열을 대상으로 인덱스 ix_Book2를 생성

 

CREATE INDEX ix_Book2 On Book(publisher, price);

 

생성된 인덱스는 SHOW INDEX FROM book;

 

※인덱스의 재구성과 삭제

-재구성:

ANALYZE TABLE 테이블 이름;

 

-B-tree 인덱스는 데이터의 수정, 삭제, 삽입이 잦으면 노드의 갱신이 주기적으로 일어나 단편화(fragmentation)현상이 나타남

 

-단편화: 삭제된 레코드의 인덱스 값 자리가 비게 되는 상태, 검색 시 성능 저하로 이어짐

 

-삭제:

DROP INDEX 인덱스이름 ON 테이블이름;