SQL 내장 함수
상수나 속성 이름을 입력 값으로 받아 단일 값을 결과로 반환
최초에 선언될 때 유효한 입력 값을 받아야 함
선언에 위배된 값이 입력되면 질의는 실행을 중지하고
에러 메시지 출력
숫자 함수
-> ABS, CEIL, FLOOR, ROUND, LOG, POW, SQRT, SIGN
'고객별' 평균 주문 금액을 백원 단위로 반올림한 값을 구하시오
SELECT custid '고객번호', ROUND(SUM(saleprice) / COUNT(*), -2) '평균 금액'
FROM Orders
GROUP BY custid;
문자 함수
CONCAT(S1, S2) -> 두 문자열을 연결
CONCAT(s1, ' * ', mby, ' = ', mby * s2)
도서제목에 야구가 포함된 도서를 농구로 변경 한 후
도서 목록을 보이시오
SELECT bookid, REPLACE(bookname, '야구', '농구')bookname, publisher, price
FROM Book;
CHAR_LENGTH(bookname) -> 문자의 수
LENGTH(bookname) -> 바이트 수를 가져옴 (한글은 3바이트)
마당서점의 고객 중 같은 성을 가진 사람이
묶자! substr(name,1,1)
몇 명이나 되는지
성 별 인원수를 구하시오
SUBSTR(속성, 시작위치, 길이)
SELECT SUBSTR(name, 1, 1) '성', COUNT(*) '인원'
FROM Customer
GROUP BY SUBSTR(name, 1, 1);
SELECT에는 SUBSTR과 집계함수만 가능!
날짜 시간 함수
날짜 / 시간 함수에서 날짜와 시간 부분을 나타내는 인수는
'format' 으로 표기
'%Y-%m-%d'
마당서점은 주문일로부터 10일 후 매출을 확정함.
각 주문의 확정일자를 구하시오.
SELECT orderid '주문번호', orderdate '주문일',
ADDDATE(orderdate, INTERVAL 10 DAYS) '확정'
FROM Orders;
ADDDATE(orderdate, INTERVAL 10 DAYS)
INTERVAL 간격
마당서점이 2014년 7월 7일에 주문받은 도서의
주문번호, 주문일, 고객번호, 도서번호를 모두 보이시오
SELECT orderid '주문번호',
STR_TO_DATE(orderdate, '%Y-%m-%d') '주문일',
custid '주문번호', bookid '도서번호'
FROM Orders
WHERE orderdate = DATE_FORMAT('20140707', '%Y%m%d');
STR_TO_DATE 함수는 CHAR 형으로 저장된 날짜를
DATE 형으로 변환하는 함수
DATE_FORMAT 함수는 날짜형을 문자형으로 변환
SYSDATE 함수는 DBMS 시스템 상의 오늘 날짜를 반환
NULL 값 처리
1. NULL + '숫자' 는 NULL이다
1. 집계 함수를 계산 할 때 NULL이 포함된 행은 집계에서 빠짐
1. 해당되는 행이 하나도 없을 경우 SUM, AVG 함수 결과는 NULL
COUNT 함수의 결과는 0
NULL 값을 찾을 때는 '=' 쓰면 안됨!!!
IS NULL을 사용
아닌 값을 찾을 때는 IS NOT NULL
0이나 '' 이런거는 NULL이 아니다!
이름, 전화번호가 포함된 고객목록을 보이시오.
단, 전화번호가 없는 고객은 '연락처없음'으로 표기 -> IFNULL 사용
IFNULL(속성, '값') -> 속성 값이 NULL이면 '값'으로 대치
SELECT name '이름', IFNULL(phone, '연락처없음') '전화번호'
FROM Customer;
행번호 출력
MySQL에서 변수는 이름 앞에 @ 기호를 붙이고
치환문에는 SET과 := 기호를 사용
고객 목록에서 고객번호, 이름, 전화번호를 앞의 두 명만 보이시오
SET @seq := 0;
SELECT (@seq := @seq + 1) '순번', custid, name, phone
FROM Customer
WHERE @seq < 2;
부속질의 -> 튜플 변수를 이용하자
스칼라 부속질의, 인라인 뷰, 중첩질의
스칼라 부속질의
SELECT 절에서 일어남
단일 값을 반환함
인라인 뷰
FROM 절에서 일어남
결과를 view의 형태로 반환함
중첩질의 = (3장에서의 부속질의)
WHERE 절에서 일어남
결과를 한정 시키기 위해 사용됨.
상관 또는 비상관 형태임
상관 부속질의 : 주질의의 특정 열값을 부속질의가 상속받아 사용
비상관 부속질의 : 독립된 질의를 수행
1. 스칼라 부속질의
부속질의의 결과 값이 단일 행, 단일 열의 스칼라 값으로 반환됨
일반적으로 SELECT 문과 UPDATE SET 절에 사용됨
상관 / 비상관 모두 가능
마당서점의 고객별 판매액을 보이시오
(고객이름과 고객별 판매액 출력)
SELECT (SELECT name
FROM Customer cs
WHERE cs.custid = od.custid) 'name',
SUM(saleprice) 'total'
FROM Orders od
GROUP BY od.custid;
이걸 조인으로도 했었지!
FROM Customer cs JOIN Orders od ON cs.custid = od.custid
불필요한 속성들이 붙으니까 스칼라 부속질의
순서가 어떻게 되냐?
일단 custid와 total이 만들어지고
custid 1의 이름을 스칼라 부속질의로 찾는다 -> 단일행, 단일열
Orders 테이블에 각 주문에 맞는 도서이름을 입력
ALTER TABLE Orders ADD bname VARCHAR(40);
UPDATE Orders
SET bname = (SELECT bookname
FROM Book
WHERE Book.bookid = Orders.bookid);
인라인 뷰
FROM 절에서 사용되는 부속질의
뷰는 기존 테이블로부터 일시적으로 만들어진 가상의 테이블
다중 행, 다중 열 노상관
가상 테이블인 뷰 형태로 제공되므로 상관 부속질의는 불가능
고객번호가 2 이하인 고객의 판매액을 보이시오
고객번호가 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
(SELECT custid, name
FROM Customer
WHERE custid <= 2)
cs 테이블을 계산해서 가상의 테이블(view)를 만들고
od 테이블과 조인을 한다.
위의 방식은 Customer 테이블과 Orders 테이블을 동등조인하고
고객번호가 2 이하인 고객만 출력하는 방식으로 작성 가능
하지만 조인 결과 테이블에서 필요 없는 데이터를 제거해야 하므로
성능저하가 발생
중첩질의에서 다른 연산자들
평균 주문금액 이하의 주문에 대해 주문번호와 금액을 보이시오
SELECT orderid, saleprice
FROM Orders
WHERE saleprice <= (SELECT AVG(saleprice)
FROM Orders);
각 고객의 평균 주문금액보다 큰 금액의 주문 내역에 대해서
주문번호, 고객번호, 금액을 보이시오
상관 부속질의는 '튜플 변수'를 사용한다
<상관 부속질의>
SELECT orderid, custid, saleprice
FROM Orders md
WHERE saleprice > (SELECT AVG(saleprice)
FROM Orders so
WHERE md.custid = so.custid);
Orders md가 custid 행을 부속질의에 공급
그 값들을 모아서 AVG 값을 구함
AVG 함수는 집계 함수로 단일 행을 반환하므로
비교 연산자 > 가 사용 가능하다
IN, NOT IN
WHERE '속성' IN, NOT IN
대한민국에 거주하는 고객에게 판매한 도서의 총 판매액
SELECT SUM(saleprice) 'total'
FROM Orders
WHERE custid IN (SELECT custid
FROM Customer
WHERE address LIKE '%대한민국%');
custid의 집합에 있는가? WHERE 속성 IN
IN 연산자에 사용 가능한 부속질의는 결과로
다중 행, 다중 열을 반환 가능
ALL, SOME
SOME은 어떠한 값 -> 최소값에 사용
ALL은 모든 값 -> 최대값에 사용
3번 고객이 주문한 도서의 최고 금액보다 더 비싼 도서를 구입한
주문번호와 판매금액을 보이시오
SELECT orderid, saleprice
FROM Orders
WHERE saleprice > ALL(SELECT saleprice
FROM Orders
WHERE custid = '3');
custid = 3이 구매한 '모든' 가격
이게 SOME이 되면 그냥 하나만 커도 되겠지.
EXISTS, NOT EXISTS -> 데이터의 존재 유무
WHERE (NOT) EXISTS 부속질의
EXISTS 연산자를 사용하여 '대한민국에 거주하는 고객'에게
판매한 도서의 총 판매액을 구하시오
SELECT SUM(saleprice) total;
FROM Orders od
WHERE EXISTS (SELECT *
FROM Customer cs
WHERE address LIKE '%대한민국%'
AND cs.cusid = od.cusid);
뷰
하나 이상의 테이블을 합하여 만든 가상의 테이블
1. 편리성 및 재사용성
미리 정의된 뷰를 일반 테이블처럼 사용할 수 있으므로 편리
또 사용자가 필요한 정보만 요구에 맞게 가공 가능
자주 사용되는 질의를 뷰로 미리 정의해 재사용 가능
2. 보안성
각 사용자별로 보안이 필요한 데이터를 제외하여 선별
3. 독립성
논리적 독립성을 제공함
뷰는 테이블처럼 사용이 가능하지만
일부 물리적인 테이블의 갱신 작업을 수행하는데 제약이 있다.
INSERT, UPDATE, DELETE 같은 DML 작업은
경우에 따라 수행되지 않는다
뷰의 생성
CREATE VIEW vw_Book (여기에 속성 넣으면)
AS SELECT * (여기도 순서 같아야 한다)
FROM Book
WHERE bookname LIKE '%축구%';
뷰의 수정 CREATE OR REPLACE
CREATE OR REPLACE VIEW vw_Customer (custid,
name,address)
AS SELECT custid, name, address
FROM Customer
WHERE address LIKE '%영국%';
뷰의 삭제
DROP VIEW vw_Customer;
CREATE VIEW highorders (bookid,bookname,name,publisher,saleprice)
AS SELECT od.bookid,bk.bookname,
cs.name,bk.publisher,od.saleprice
FROM Orders od, Customer cs, Book bk
WHERE cs.custid=od.custid AND od.bookid=bk.bookid AND saleprice >= 20000;
JOIN 대신 FROM 과 WHERE에
조건 AND로 붙이기 -> 동등 조인
카테고리 없음