데이터베이스(3)

Notice

Recent Posts

Recent Comments

Link

« 2025/06 »
일	월	화	수	목	금	토
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30

Tags more

Archives

Today

Total

관리 메뉴

생각정리

데이터베이스(3) - SQL기초 본문

카테고리 없음

데이터베이스(3) - SQL기초

공부중초보에요 2022. 10. 10. 22:29

728x90

SQL의 번역은 DBMS가 해준다.

SQL 기능에 따른 분류

데이터 정의어(DDL): 테이블이나 관계(릴레이션 간)의 구조를 생성하는데 사용함. CREATE, ALTER, DROP

데이터 조작어(DML): 테이블에 데이터를 검색,삽입,삭제 하는데 사용함. SELECT, INSERT, DELETE, UPDATE

데이터 제어어(DCL): 데이터의 사용 권한을 관리(제어)하는데 사용함. GRANT, REVOKE

데이터 조작어 - 검색 SELECT문

SELECT문은 데이터 조작어(DML) 중에서도 특별히 질의어(QUERY)라고 불림.

여기에서 SELECT, FROME, WHERE,GRIUP BY, HAVING, ORDER BY 등을 키워드라고 부름.

SELECT bookname,price

FROM Book;

=> Book테이블에서 bookname과 price 속성을 언급된 순서대로 나열해서 결과 릴레이션을 만들어서 보여줌.

만약 모든 속성을 보여주고 싶다면, * 애스터리스크를 쓰면 됨.

SELECT DISTINCT는 결과 릴레이션에서 튜플 간 중복을 제거해서 보여줌.

만약 어떤 한 테이블에서 한 속성의 도메인 값을 보고 싶다면, SELECT DISTINCT 속성 FROM 테이블; 을 하면 됨.

WHERE 조건

WHERE 절에 사용할 수 있는 술어는 다음과 같다. 비교, 범위, 집합, 패턴, NULL, 복합 조건

비교) =,<>,<,<=,>=,> 연산자. 여기서 <>는 다르다는 연산자임. ex: price<20000

범위) BETWEEN 연산자 NOT BETWEEN. ex: price BETWEEN 10000 AND 20000. 여기서 양쪽 사이드 값은 포함임.

집합) IN,NOT IN 연산자. ex: price IN (10000,20000,30000)

패턴) LIKE 연산자. ex: bookname LIKE '축구의 역사' 혹은 bookname LIKE '%축구%'

NULL) IS NULL, IS NOT NULL 연산자. ex: price is NULL. 주의: WHELE price is NULL 로 표현해야 함.

복합조건) AND, OR, NOT 연산자. ex: (price < 20000) AND (bookname LIKE '축구의 역사')

예제풀이

1. 가격이 20000원 미만인 도서를 검색하시오.

SELECT *

FROM Book

WHERE price < 20000;

2. 가격이 10000원 이상 20000원 이하인 도서를 검색하시오.

SELECT *

FROM Book

WHERE price BETWEEN 10000 AND 20000;

혹은

WHERE (price >= 10000) AND (price <= 20000);

=> 둘 중 뭐가 더 최적화 된 명령어라고 말 못함.

3. 출판사가 '굿 스포츠' 혹은 '대한 미디어' 가 아닌 도서를 검색하시오.

SELECT *

FROM Book

WHERE publisher NOT IN ('굿 스포츠','대한 미디어');

4. '축구의 역사'를 출간한 책 이름과 출판사를 검색하시오.

SELECT bookname,publisher

FROM Book

WHERE bookname LIKE '축구의 역사';

4-2. 도서이름에 '축구'가 포함된 출판사를 검색하시오.

SELECT bookname, publisher

FROM Book

WHERE bookname LIKE '%축구%';

//여기서 %는 0길이 이상의 문자열을 말함. 만약 한개의 문자가 올 수 있음을 표현하고 싶다면 _ 를 사용하면 됨.

이런걸 와이드 문자라고 함.

와이드 문자의 종류: + , %, [ ], [^], _

+는 문자열 연결을 의미함.

[ ] 는 여기 나열된 것들 중 한개의 문자가 오는 것을 의미. ex: [0-5]는 0-5가 0부터5까지의 범위 숫자이므로, 0-5 사이 숫자가 오는 문자열을 말한다.

[^] 는 여기 나열된 것들이 오지 않는 문자열을 가리키고 싶을때 사용.

5. 축구에 관한 도서 중 가격이 20000원 이상인 도서를 검색하시오.

SELECT *

FROM Book

WHERE bookname LIKE '%축구%' AND price >= 20000;

ORDER BY

=> 지정한 속성에 대해서 정렬을 하고 싶을 때 사용하는 명령어임. ASC 혹은 DESC를 지정해야함. 기본은 ASC 오름차순.

1. 도서를 이름순으로 검색하시오.

SELECT *

FROM Book

ORDER BY bookname;

2. 도서를 가격의 내림차순으로 검색하고, 가격이 같으면 출판사 이름의 오름차 순으로 검색하시오.

SELECT *

FROM Book

ORDER BY price DISC, publisher ASC;

집계함수와 GROUP BY 절.. 그리고 HAVING 절

집계 함수: SELECT 키워드 뒤에 속성명 대신 적는거임. 사실 원래 SELECT 문 자체가 출력하고 싶은 내용을 지정하면 해당 내용들을 출력해주는 거임.

종류: SUM, AVG, COUNT, MAX, MIN

SUM( [ALL | DISTINCT] 속성이름 ): 여기서 DISTINCT를 사용하면, 중복된 값들을 제거하고 통계함.

DISTINCT는 모든 집계함수에서 사용 가능함.

또한 COUNT 집계함수는 유일하게 * 애스터리스크를 사용 가능함. => COUNT(*)은 튜플의 수를 세준다.

또한 AS를 사용해서 결과 릴레이션에 나올 속성명의 이름을 별칭으로 바꿀 수 있음.

1. 2번 김연아 고객이 주문한 도서의 총 판매액을 구하시오.

SELECT SUM(saleprice) AS 총매출

FROM Order

WHERE cutid=2;

GROUP BY: 속성 값이 같은 값끼리 그룹을 만들 수 있음. 그리고 그룹을 나눴으면 각 그룹에 대한 통계를 내야함. 그래서 GROUP BY절을 사용했다면, SELECT절에는 집계함수와 GROUP BY절에서 사용한 속성만 올 수 있음.

1. 고객별로 주문한 도서의 총 수량과 총 판매액을 구하시오.

SELECT custid, COUNT(*) AS 도서수량, SUM(saleprice) AS 총액

FROM Orders

GROUP BY custid;

=> FROM 절을 따라서 주문 테이블을 꺼내고, custid 별로 그룹화 시킨 다음에 SELECT문의 집계함수를 따라서 통계를 낸다.

지금 GROUP BY 절에 custid로 그룹화를 시킨 것을 알 수 있음. 이렇게 그룹화 시킨 기준의 속성은 SELECT절에도 언급되어야 함. 안그러면, 그룹을 나눠서 통계를 낼때, 이게 무슨 그룹의 통계 값인지 알 수 없는 즉, 의미를 알 수 없는 데이터가 됨. custid를 제외한 나머지 검색 내용들은 모두 집계함수를 사용해야 함. 안그러면 에러...

HAVING: HAVING절은 GROUP BY 절 이후에 나온 중간 결과에서 조건을 거는 것임. 즉, 생성된 그룹들 중 필요없는 그룹은 제외시키는 용도라고 생각하면 됨. SELECT로 출력 전에 그룹 거르기.

HAVING 절의 사용 원칙)

1. GROUP BY 절과 같이 작성해야 함.

2. WHERE절보다 반드시 뒤에 와야 함.

3. HAVING 절의 검색조건에는 반드시 집계함수가 와야 함.

1. 가격이 8000원 이상인 도서를 구매한 고객에 대하여 고객별 주문 도서의 총 수량을 구하시오. 단, 두 권 이상 구매한 고객만 구한다.

SELECT custid, COUNT(*) AS 도서수량 // 실행순서5. 결과 릴레이션을 생성.

FROM Orders // 실행순서1. 테이블을 가져옴.

WHERE price >= 8000 // 실행순서2. 가격이 8000이상인 레코드만 남김. 그룹화전에 데이터 거름.

GROUP BY custid // 실행순서3. 남은 레코드들에 대해서, custid로 그룹화함.

HAVING count(*)>=2 // 실행순서4. 그룹화한 그룹들 중 레코드 수가 2개 이상인 그룹만 남김.

ORDER BY custid; // 실행순서6. 만들어진 결과 릴레이션을 custid의 오름차순으로 정렬.

두 개 이상 테이블에서 SQL 질의

여러 개의 테이블을 이용해서 질의하는 방법 두 가지: 조인 과 부속질의

1. 조인을 사용하는 방법

두 테이블을 아무런 조건을 주지 않고 SELECT 시키면 관계대수의 카티전 프로덕트 연산이 수행됨.EX) SELECT * FROM Customer, Orders; => 고객 테이블과 주문 테이블이 카티전 프로덕트 수행된 결과가 나옴. 아직 조인은 나오지 않음. 조인은 원래 카티전 프로덕트와 셀렉션으로 유도됨.

1. 고객과 고객의 주문에 관한 데이터를 모두 보이시오.SELECT *FROM Customer, OrdersWHERE Customer.custid = Orders.custid; // 두 테이블의 연결 조건을 추가. => 이것의 수행 결과 릴레이션에서는 custid가 두 번 나온다. 즉, 세타조인 중에서도 동등조인임. 자연조인 아님.

2. 고객별로 주문한 모든 도서의 총 판매액을 구하고, 고객별로 정렬하시오.SELECT name,SUM(saleprice)FROM Customer, OrdersWHERE Customer.custid=Orders.custidGROUP BY Customer.name ORDER BY Customer.name; => 여기서 알 수 있는 것: 아 만약 양쪽 테이블 모두에 속한 공통 속성이면, 속성명 앞에 무슨 테이블의 속성인지를 명시해줘야 함.

3. 고객의 이름과 주문한 도서의 책이름을 구하시오.

=> 이런 경우 고객 테이블에만 고객이름이 있고, 도서 테이블에만 도서 이름이 있고, 주문 테이블에서 주문 내역을 알 수 있기 때문에 세 개의 테이블을 조인해야 하는 상황임. SELECT Customer.name, Book.booknameFROM Customer, Orders, BookWHERE Customer.custid=Orders.custid AND Orders.bookid=Book.bookid;

셀프조인이란? 한 테이블에 대해서 같은 테이블을 조인시키는 것. 언제 사용할까? 회사 직원의 정보가 담긴 테이블에서 다음과 같은 속성들이 있다고 하자. 이름,사원번호,직급,자신을 관리하는 상사의 사원번호

이렇게 한 테이블 내 튜플 들 간에 서로 참조할 수 있는 경우가 존재한다면, 셀프조인을 할 수 있다.

이때는 FROM으로 테이블을 가져올 때 별칭을 지정해 줄 수 있음. 예시: FROM 테이블명 별칭,테이블명 별칭

조인 연산 중에서도 외부조인이 있음. 예를 들어 도서를 구매하지 않은 고객까지 포함 시키고 싶은경우.

4. 도서를 구매하지 않은 고객을 포함하여 고객의 이름과 고객이 주문한 도서의 판매가격을 구하시오.

SELECT Customer.name, saleprice

From Customer LEFT OUTER JOIN Orders

ON Customer.custid=Orders.custid;

=> 고객 테이블을 기준으로 하기 위해 왼쪽 외부 조인을 함. 그리고 조인 조건은 ON 이후에 적으면 됨.

일반 조인: 그냥 동등 조인임. 둘 중 뭘 써도 됨.

FROM 테이블명,테이블명

WHERE 조인조건 AND 검색조건

FROM 테이블1 INNER JOIN 테이블2 ON 조인조건

WHERE 검색조건

외부조인: 외부조인은 FROM 절에 구체적인 외부 조인 종류를 적고, ON 뒤에 조인조건을 명시.

FROM 테이블1 {LEFT|RIGHT|FULL [OUTER]} JOIN

테이블2 ON 조인조건

WHERE 검색조건

2. 부속 질의

SQL 문 내에 또 다른 SQL문을 작성하는 것을 부속질의(=중첩 질의) 라고 한다.

1. 가장 비싼 도서의 이름을 보이시오.

SELECT bookname

FROM Book

WHERE price = (SELECT MAX(price) FROM Book);

=> 정확히는 이 외부의 SELECT문 전체를 전체 질의 라고 부르고, WHERE절 안의 괄호를 부속 질의 라고 한다.

처리과정: 부속질의를 먼저 처리하고 전체질의를 처리.

주의: 부속질의로 나온 결과는 테이블임. 그런데, 테이블의 형태는 4가지가 존재함.

1*1 혹은 N*1 혹은 1*N 혹은 N*N

1*1의 테이블은 그냥 값을 리턴한다.

N*1의 테이블은 IN 키워드로 처리하면 됨. (n*1 구조는 한 속성만 뽑아내는 것 같은 경우임.)

2. 도서를 구매한 적이 있는 고객의 이름을 검색하시오.

SELECT name

FROM Customer

WHERE custid IN (SELECT custid

FROM Orders);

3. 대한미디어에서 출판한 도서를 구매한 고객의 이름을 보이시오.

SELECT name

FROM Customer

WHERE custid IN (SELECT custid

FROM Orders

WHERE bookid IN (SELECT bookid

FROM Book

WHERE publisher='대한미디어'));

상관 부속 질의: 부속 질의 중에서도, 상위 부속 질의의 튜플을 이용해서 하위 부속 질의를 계산하는데 이용하고 하위 부속질의의 결과로부터 해당 튜플이 타당한지 검사하는 방법임.

예: 출판사별로 출판사의 평균 도서 가격보다 비싼 도서를 구하시오.

SELECT b1.bookname

FROM Book b1

WHERE b1.price > ( SELECT AVG(price)

FROM Book b2

WHERE b1.publisher=b2.publisher );

=> b1의 튜플 하나를 빼서 하위 부속질의의 결과물을 도출하는데 이용함. 즉, 한개의 책b1을 뽑아서 모든 책들 중 이 책과 출판사가 같은 책만 남기고 거기에서 price의 평균 값을 찾음. 해당 테이블은 1*1이므로 스칼라값으로 사용이 됨.

구한 평균값을 b1.price와 비교해서 통과한다면 b1의 책이름을 최종 결과 테이블에 적음.

WHERE절의 EXISTS와 NOT EXISTS도 상관 부속 질의의 종류임.

EXISTS: 조건에 맞는 튜플이 하위 질의의 결과 테이블에 존재한다면 결과물에 포함시킴.

NOT EXISTS: 조건에 맞는 튜플이 존재하지 않으면 결과물에 포함시킴.

예: 주문이 있는 고객의 이름과 주소를 보이시오.

SELECT c1.name, c1.address

FROM Customer c1

WHERE EXISTS ( SELECT *

FROM Orders o1

WHERE c1.custid=o1.custid );

CREATE 문

테이블 구성, 속성과 속성에 관한 제약 정의, 기본키 및 외래키를 정의하는 명령어

단순히 테이블을 생성하는 거라면,

CREATE TABLE 테이블이름

(

속성이름 데이터타입,

...

속성이름 데이터 타입,

PRIMARY KEY 속성이름(들)

FOREIGN KEY 속성이름 REFERENCES 부모테이블이름(기본키속성이름)

ON DELETE [ CASCADE / SET NULL]

ON UPDATE [~]

)

=> 이렇게 테이블명과 속성들을 나열하고, 기본 키 지정과 외래키지정을 할 수 있다. 외래키를 지정할 때는 ON DELETE와 ON UPDATE 를 사용해서 참조무결성 제약 조건을 설정할 수 있다. 부모 테이블의 참조하는 튜플이 삭제되는 경우.

테이블 만드는 법)

1. 일단 속성과 속성 타입들을 쭉 나열한다.

2. PRIMARY KEY로 등록하고 싶은 키 옆에 PRIMARY KEY를 적거나, 아래에 PRIMARY KEY(속성명) 을 적는다.

=> 둘 중 아무거나 해도 됨. 만약 복합 키를 등록하고 싶다면, PRIMARY KEY(속성1, 속성2)를 적으면 됨.

3. 이제 각 속성 별로 도메인을 지정하고 싶거나 제약 조건을 걸고 싶다면, 해당 속성을 명시한 줄 에다가 이어서 적으면 된다. 널 값 허용은 NULL , 널 값을 못 갖게 하고 싶다면 NOT NULL , 같은 값이 있으면 안된다면 UNIQUE , 값이 입력되지 않을 경우 기본값을 지정하고 싶다면 DEFAULT 값, 만약 값의 범위를 지정하고 싶다면 CHECK(속성명 > 5000)

ON DELETE는 참조 무결성 원칙 중 부모 릴레이션의 튜플이 삭제될 때, 이 테이블에서 취할 동작을 지정하는 거임.

SET NULL은 널값으로 해당 외래키 값을 채움.

CASCADE는 연쇄 삭제로 해당 외래키가 들어간 자식 테이블의 튜플들을 삭제함.

NO ACTION은 기본값임. 어떠한 동작도 취하지 않음을 의미함.

ON UPDATE 옵션은 참조되는 튜플(부모의 튜플)이 수정될 때 취할 수 있는 동작을 지정함.

ALTER 문

생성된 테이블의 속성과 속성에 관한 제약을 변경하며, 기본키 및 외래키를 변경함.

ADD, DROP은 속성을 추가하거나 속성을 제거할때

MODIFY는 속성의 기본값을 설정하거나 속성의 기본값을 제거할 때, 속성 타입 변경, 속성에 제약조건 추가

ADD <제약이름>이나 DROP <제약 이름> 은 제약사항을 추가하거나 삭제할 때 사용함.

DROP문

테이블을 삭제하는 명령어임.

테이블의 구조와 데이터를 모두 삭제함. 단순하게 데이터를 삭제하고 싶은거라면 다른 명령어 사용해야 함.

DROP TABLE 테이블명;

만약 어떤 테이블이 이 테이블을 참조중이여서 삭제를 못한다는 에러가 발생한다면 해당 테이블을 먼저 삭제해야 함.

데이터 조작어 - 삽입, 수정, 삭제 , 검색(SELECT문 이미 배움 위에서)

INSERT문

테이블에 새로운 튜플을 삽입하는 명령어임.

INSERT INTO 테이블명(넣을 속성이름 나열) => 모든 속성넣을거라면, (넣을 속성이름 나열)제거 가능

VALUES (속성 값 나열); => 여기에 SELECT문 와서 테이블이 존재해도 됨.

위에서 나열한 속성 이름과 속성 값이 차례로 매칭돼서 값이 들어감.

예시)

수입 도서 목록을 Book테이블에 모두 삽입하세요.

INSERT INTO BOOK(bookid,bookname,price,publisher)

SELECT bookid,bookname,price,publisher

FROM Imported_book;

UPDATE문

특정 속성 값을 수정하는 명령어

UPDATE 테이블명

SET 속성이름1=값1, 속성이름2=값2, ...

[WHERE 조건] ;

예)

Customer 테이블에서 고객번호가 5인 고객의 주소를 '대한민국 부산'으로 변경하시오.

UPDATE Customer

SET address='대한민국 부산'

WHERE custid=5;

=> 참고: 위에서 '대한민국 부산' 대신에 SELECT 문이 와서 스칼라 값을 대체해줘도 됨.

DELETE문

테이블에 있는 기존 튜플을 삭제하는 명령임

DELETE FROM 테이블명

[WHERE 검색조건];

예시) Book 테이블에서 도서번호가 11인 도서를 삭제하시오.

DELETE FROM Book

WHERE bookid=11;

예시) 모든 고객을 삭제하시오.

DELETE FROM Customer;

=> 모든 데이터를 삭제함. 즉, 모든 릴레이션 인스턴스를 삭제하는거임. 릴레이션 스키마는 유지됨. 테이블은 살았으므로.

=> 만약 이 테이블을 참조하는 테이블이 있었다면, 데이터를 삭제하면서 오류 메시지가 발생할 가능성 있음.

SQL 심화

SQL 함수는 내장함수 와 사용자 정의 함수가 있다.

SQL 내장함수) 상수나 속성 이름을 입력 값으로 받아 단일 값으로 결과를 반환함.

숫자함수

ABS 절댓값 반환 함수

CEIL 숫자 이상의 최소 정수

FLOOR 숫자 이하의 최대 정수

ROUND(숫자, m) m이 반올림 기준 자릿수임(= 소수점 아래 m번째 자리까지 표현해라. 즉, m+1번째에서 반올림 시키기.)

LOG(n,숫자) 원래는 n이 밑수로 들어가는데, 그냥 LOG(10)같이 인자를 하나만 주면 밑이 e인 자연로그로 계산함.

POWER(2,3) => 8. 지수함수

SQRT(9.0)=> 3.0 제곱근 값 계산

SIGN(숫자) =>음수면 -1, 0이면 0, 양수면 1을 리턴함.

문자함수

CONCAT(s1,s2) 문자열 연결

LOWER(s) 문자열을 소문자로 바꾼다.

UPPER(s) 문자열을 대문자로 바꾼다.

LPAD(s,n,c)

RPAD(s,n,c)

REPLACE(s1,s2,s3) s1문자열 안에 있는 s2부분문자열을 모두 s3로 바꾼다.

SUBSTR(s,n,k) s문자열에서 n번째 글자부터(인덱스 아님) k개의 글자를 추출함.

TRIM(c FROM s ) s문자열의 좌우 끝에서 c를 최대한 지움. 사이에 낀건 어쩔 수 없음.

ASCII(c) 대상 알파벳 문자의 아스키 코드 값을 반환 ex: ASCII('D') 는 68의 값을 뱉는다.

LENGTH(s) 바이트 수 반환. 알파벳은 1바이트, 한글은 3바이트.

CHAR_LENGTH(s) 문자열의 문자 수를 반환. 한글도 한문자임.

예제) 같은 성을 가진 사람이 몇명이나 되는지 성별 인원수를 구하라.

SELECT SUBSTR(name,1,1) ,COUNT(*)

FROM Customer

GROUP BY SUBSTR(name,1,1);

=> 그룹 바이의 조건으로 사용도 가능함.

날짜 시간 함수

STR_TO_DATE(string,format) 문자열 데이터를 날짜형 DATE타입으로 반환

DATE_FORMAT(date,format) 날짜형 데이터를 문자열로 반환함.

ADDDATE(date,interval) 날짜 date에서 날짜를 늘리거나 뺀 날짜를 계산함.

SYSDATE() DBMS 상의 지금 날짜 및 시간 데이터를 DATE타입으로 반환 함.

format의 지정자

ex) 2019년 7월 1일 => %Y%m%d => 20190701

ex) 14시 20분 14초 => %H%i%s => 142014

ex) 주문일로부터 10일 후 매출을 확정한다. 각 주문의 확정일자를 구하시오.

SELECT orderid, orderdate, ADDDATE(orderdate,INTERVAL 10 DAY) '확정일자'

FROM Orders;

ex) 2014년 7월 7일에 주문받은 도서의 주문번호, 주문일, 고객번호, 도서번호를 보이시오.

주문일은 '%Y-%m-%d' 의 형태로 표시한다. 참고: 기존의 orderdate는 date타입의 속성임.

SELECT orderid, STR_TO_DATE(orderdate,'%Y-%m-%d') '주문일' , custid, bookid

FROM Orders

WHERE orderdate=DATE_FORMAT('20140707' , '%Y%m%d' );

=> 사실 WHERE orderdate='20140707' 해도 되는데, 그냥 연습용으로 한거임. STR_TO_DATE를 보면 알 수 있듯이 날짜 타입의 데이터를 우리가 표기하고 싶은대로 표기가능함.

NULL값이란? 아직 지정되지 않은 값임. 0도 아니고 공백도 아닌 특수한 특별한 값임.

NULL은 비교연산이 불가능함.

NULL이 들어간 연산을 수행하면 결과는 NULL임.

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

집계 함수 계산 했는데 다 NULL이여서 해당되는 행이 없다면, 집계함수의 결과는 NULL임.

단, 예외로 COUNT 함수의 결과는 NULL이 아니라 0 이 나온다. 애초에 이건 개수를 세는 개념이기 때문에. 그냥 NULL값 있는 데이터 제외하고 개수를 세면 0이 나왔다~ 이렇게 받아들이면 됨.

만약 price라는 속성에 NULL이 있으면, COUNT(*)과 COUNT(price)는 다르다.

NULL값을 확인하려면, IS NULL 혹은 IS NOT NULL을 사용해라.

IFNULL(속성,대체값) => 만약 해당 속성이 널이면, 대체값으로 바꿔서 연산하거나 출력한다.

MySQL에서는 변수를 사용할 수 있다. 행 번호 출력하는데도 사용할 수 있음.

변수 이름 앞에 @기호를 붙여서 표현함. 치환문 즉 값을 바꿀 때는 := 기호를 사용함.

변수를 선언할 때는 SET을 SQL문 바로 젤 위에 적어준다.

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

SET @seq:=0;

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

FROM Customer

WHERE @seq < 2;

=> 처음에 @seq변수를 선언해서 0으로 초기화해줌. 테이블에서 하나를 출력할 때 마다 seq를 하나씩 값을 올림.

seq가 2보다 작을 때까지만. 즉, 2개만 출력하고 나머지 튜플은 출력이 안되도록 한다.

부속 질의

=> 하나의 SQL 문 안에 다른 SQL 문이 중첩된 질의.

대량으로 데이터를 합쳐서 연산하는 조인보다 필요한 데이터만 찾아서 공급해주는 부속 질의가 더 성능 좋음.

주질의(=외부질의)와 부속질의(=내부질의) 로 구성된다.

부속질의 종류:

스칼라 부속 질의(주로 SELECT 절에 위치한 부속 질의) => 결과 값이 1*1크기 데이터임.

인라인 뷰(FROM 절에 위치한 부속 질의) => 테이블처럼 사용하기 위해 FROM절에 놓는거임.

중첩질의(WHERE절에 위치한 부속 질의) => 결과를 한정시키기 위해 즉, 조건을 걸어서 튜플 걸러내는데 사용.

스칼라 부속 질의

사실 꼭 SELECT 절에만 위치하는게 아니라, 그냥 스칼라 값이 들어갈 수 있는 곳에 모두 사용 가능함.

UPDATE 테이블 SET 절에도 많이 사용함.

상관/부상관 모두 가능함.

팁: 일단 처음 만들때는 기본에 충실하게 간단히 시작함. 그리고 확대해 나가는거임.

예제1) 고객별 판매액을 보이시오( 고객이름과 고객별 판매액을 출력).

큰 그림

SELECT custid, SUM(saleprice)

FROM Order

GROUP BY custid;

=> 고객 이름을 보여주기 위해 확대.

SELECT (custid말고 여기 이름을 보여줘라) , SUM(saleprice)

FROM Order od

GROUP BY od.custid;

SELECT ( SELECT name

FROM Customer cs

WHERE cs.custid = od.custid) 'name' , SUM(saleprice)

FROM Order od

GROUP BY od.custid;

=> 한개의 스칼라 값을 뱉어냄. 그리고 지금 상관 부속 질의로 이용되고 있음. 주질의의 튜플을 이용.

예제2) Orders 테이블에 각 주문에 맞는 도서이름을 입력하시오.

기존에 일단 bname이라는 속성이 Orders테이블에 없기 때문에 추가해줘야 함.

ALTER TABLE Orders ADD bname VARCHAR(40);

간단한 생각에서 출발

UPDATE Orders

SET bname = 'bookid x의 책 이름'

WHERE bookid=x;

=> 아... bookid를 알면 책이름이 딱 하나 나오는구나.

UPDATE Orders

SET bname = (SELECT bookname

FROM Book

WHERE Book.bookid = Orders.bookid);

=> 각 주문 별로 bookid 속성이 존재하며 해당 bookid와 매칭되는 책이름은 딱 하나뿐이다.

인라인 뷰

가상의 테이블인 뷰 형태로 제공되는 것이 필요한 경우 사용함. 단순히 테이블을 얻는 목적이므로 상관 부속질의로는 사용 될 수 없음.

예제1) 고객번호가 2 이하인 고객의 판매액을 보이시오. (고객이름과 고객별 판매액 출력)

SELECT custid, SUM(saleprice)

FROM Orders

WHERE custid<=2

GROUP BY custid;

=> 여기서 어떻게 개선할까? 문제를 잘보자. 단순히 고객번호가 2 이하인 고객에 대한 정보만 있으면 됨. 그러면, 고객번호가 2이하인 고객테이블을 추출해서 그것과 Orders테이블을 조인시키면 되지 않을까?

SELECT cs.name, SUM(od.price)

FROM (SELECT custid, name

FROM Customer

WHERE custid<=2 ) cs, Orders od

WHERE cs.custid = od.custid

GROUP BY cs.custid;

=> 카티전 프로덕트도 그냥 고객테이블과 하는 것보다, 훨씬 크기가 작은 cs테이블과 od테이블을 카티전 프로덕트하므로 성능 향상.

중첩질의

WHERE 절에서 사용되는 부속 질의.

중첩질의 연산자

1. 비교 연산자. 스칼라 값을 추출해서 비교하는 기준으로 사용하는거임. 상관/비상관 모두 가능한 경우가 있음.

2. 집합 연산자. IN, NOT IN . N*1크기의 데이터를 추출해서 집합의 기준으로 삼기 위한거임. 상관 가능.

3. 한정 연산자. ALL, SOME(=ANY) . 중첩질의로 얻은 테이블 내 모든 값들에 대해 모두 만족하는지,혹은 최소한 하나라도 만족하는게 있는지에 대한거임. WHERE 스칼라값 비교연산자 ALL (중첩질의로 얻은 속성값들) => 이런식으로 사용됨.

4. 존재 연산자. EXISTS , NOT EXISTS. 데이터의 존재 유무를 확인하는 연산자임. EXISTS로 설명: 주질의의 테이블에서 부속질의로 튜플을 한개씩 보냄. 그 튜플에 대해서 부속질의의 조건을 만족하는 튜플이 있는지 확인하고 있다면 주질의의 해당 튜플을 출력함. NOT EXISTS는 반대임. 부속질의의 조건을 만족하는 튜플이 없어야 주질의의 튜플이 출력됨.

존재연산자는 무조건 상관 질의로만 사용된다.

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

SELECT orderid, saleprice

FROM Orders

WHERE saleprice <= (SELECT AVG(saleprice)

FROM Orders) ;

=> 중첩질의가 비교연산자로만 사용될때는 스칼라값을 뱉어내야 할 필요가 있는 경우임.

예시2) 각 고객의 평균 주문금액보다 큰 금액의 주문 내역에 대해서 주문번호, 고객번호, 금액을 보이시오.

SELECT orderid, custid, saleprice

FROM Orders od

WHERE od.saleprice>(od의 테이블의 한 튜플에 속한 custid에 해당하는 사람이 주문한 금액의 평균)

SELECT orderid, custid, saleprice

FROM Orders od

WHERE od.saleprice>( SELECT AVG(saleprice)

FROM Customer cd

WHERE cd.custid=od.custid );

=> 상관 부속질의를 사용하기도 함. 만약 비교연산자를 사용하는데 대상이 각 튜플과 관련된 정보일 경우.

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

=> 주문 내역 중 대한민국에 거주하는 사람의 custid로 구성된 튜플들로만 집계한것.

SELECT SUM(saleprice) 'total'

FROM Orders od

WHERE od.custid IN ( SELECT custid

FROM Customer

WHERE address LIKE '%대한민국%' );

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

=> 주문 내역들 중 3번 고객이 주문한 모든 가격이 높은 경우.

SELECT orderid, saleprice

FROM Orders od

WHERE od.saleprice > ALL(SELECT saleprice

FROM Orders

WHERE custid=3);

예시5) 대한민국에 거주하는 고객에게 판매한 도서의 총 판매액 (단, EXISTS로 구하시오.)

=> 주문내역 중 그 주문내역의 custid가 대한민국에 사는 사람의 custid인 경우.

=> 주문내역의 custid를 가지고 고객테이블에서 해당 custid를 갖고, 대한민국에 거주하는 사람이 있는 경우 그 주문내역들을 더하면 됨.

SELECT SUM(saleprice)

FROM Orders od

WHERE EXISTS (SELECT *

FROM Customer

WHERE od.custid=custid AND address LIKE '%대한민국%' );

참고 도서: MySQL로 배우는 데이터베이스 개론과 실습

728x90

Comments

생각정리

데이터베이스(3) - SQL기초 본문

데이터베이스(3) - SQL기초

데이터 조작어 - 검색 SELECT문

집계함수와 GROUP BY 절.. 그리고 HAVING 절

두 개 이상 테이블에서 SQL 질의

1. 조인을 사용하는 방법

2. 부속 질의

데이터 조작어 - 삽입, 수정, 삭제 , 검색(SELECT문 이미 배움 위에서)

SQL 심화

부속 질의

스칼라 부속 질의

인라인 뷰

중첩질의

티스토리툴바