[데이터 베이스] SQL 고급 문법 1 (내장 함수, NULL 값 처리, 부속 질의)

 

mysql로 배우는 데이터베이스 개론과 실습 책을 보고 정리했습니다.

https://www.hanbit.co.kr/academy/books/book_view.html?p_code=B8548562053 

IT CookBook , MySQL로 배우는 데이터베이스 개론과 실습

 

 

 

내장 함수

 

sql 함수는 크게 두 개로 나뉜다. DBMS가 제공하는 내장 함수와 사용자가 필요에 따라 직접 만드는 사용자 정의 함수이다. SQL 내장함수는 SELECT절과 WHERE절, UPDATE절 등에서 모두 사용이 가능하다. 

 

구분		함수
단일행 함수	숫자 함수	ABS, CEIL, COS, EXP, FLOOR, LN, LOG, MOD, POWER, RAND, ROUND, SIGN
	문자 함수(문자 반환)	CHAR, CONCAT, LEFT, RIGHT, LOWER, UPPER, LPAD, RPAD
	문자 함수(숫자 반환)	ASCII, INSTR, LENGTH
	날짜 시간 함수	ADDDATE, CURRENT_DATE, DATE, DATEDIFF, DAYNAME, LAST_DAY
	변환 함수	CAST, CONVERT, DATE_FORMAT, STR_TO_DATE
	정보 함수	DATABASE, SCHEMA, ROW_COUNR, USER, VERSION
	NULL 관련 함수	COALESCE, ISNULL, IFNULL, NULLIF
집계함수		AVG, COUNT, MAX, MIN, STD, STDDEV, SUM
윈도우 함수		CUME_DIST, DENSE_RANK, FIRST_VALUE, LAST_VALUE, LEAD, NTILE, RANK, ROW_NUMBER

 

 

숫자 함수는 SQL문에서 수학의 기본적인 사칙 연산자와 나머지 연산자 기호를 그대로 사용한다. 그러나 실제 수학에서는 사칙 연산자 외에도 여러 연산자가 있다. 

 

함수	설명
ABS()	절대값 계산
CEIL()	숫자보다 크거나 같은 최소의 정수
FLOOR()	숫자보다 작거나 같은 최소의 정수
ROUND(숫자, m)	숫자의 반올림, m은 반올림 기준 자릿수
LOG()	숫자의 자연로그 값 반환
POWER(숫자, n)	숫자의 n제곱 계산
SORT()	숫자의 제곱근 값을 계산
SIGN()	숫자가 음수면 -1, 0이면 0, 양수면 1

 

 

-78과 +78의 절댓값을 구하시오

MYSQL, SQL Server는 FROM 절이 없어도 SELECT문을 사용할 수 있다. 그러나 오라클 DB는 불가능하다. 

SELECT ABS(-78), ABS(+78);

 

 

4.875를 소수 첫째자리까지 반올림한 값을 구하시오

SELECT ROUND(4.875, 1);

 

 

고객별 평균 주문 금액을 백원 단위로 반올림한 값을 구하시오

SELECT custid '고객번호', ROUND(SUM(saleprice)/COUNT(*), -2) '평균금액'
FROM Orders
GROUP BY custid;

 

 

문자 함수는 주로 CHAR나 VARCHAR의 데이터 타입을 대상으로 단일 문자나 문자열을 가공한 결과를 반환한다. 

 

반환 구분	함수	설명
문자값 반환 함수	CONCAT(s1, s2)	두 문자열 연결
	LOWER(s)	대상 문자열을 모두 소문자로 변환
	LPAD(s, n, c)	대산 문자열의 왼쪽부터 지정한 자리수까지 지정한 문자로 채움
	REPLACE(s1, s2, s3)	대상 문자열의 지정한 문자를 원하는 문자로 변경
	RPAD(s, n, c)	대상 문자열의 오른쪽부터 지정한 자리수까지 지정한 문자로 채움
	SUBSTR(s, n, k)	대상 문자열의 지정된 자리에서부터 지정된 길이만큼 잘라서 반환
	TRIM(c FROM s)	대상 문자열의 양쪽에서 지정된 문자를 삭제
	UPPER(s)	대상 문자열을 모두 대문자로 변환
숫자값 반환 함수	ASCII(c)	대상 알파벳 문자의 아스키 코드 값을 반환
	LENGTH(s)	대상 문자열의 byte 반환, 알파벳 1byte, 한글 3byte
	CHAR_LENGTH(s)	문자열의 문자 수 반환

 

 

도서제목에 야구가 포함된 도서를 농구로 변경한 후 도서 목록을 보이시오

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

 

 

굿스포츠에서 출판한 도서의 제목과 제목의 문자 수, 바이트 수를 보이시오

SELECT bookname '제목', CHAR_LENGTH(bookname) '문자수', LENGTH(bookname) '바이트수'
FROM Book
WHERE publisher='굿스포츠';

 

 

마당서점의 고객 중에서 같은 성을 가진 사람이 몇 명이나 되는지 성별 인원수를 구하시오

SELECT SUBSTR(name, 1, 1) '성', COUNT(*) '인원'
FROM Customer
GROUP BY SUBSTR(name, 1, 1);

 

 

날짜, 시간 함수

 

함수	반환형	설명
STR_TO_DATE(string, format)	DATE	문자열 데이터를 날짜형으로 변환
DATE_FORMAT(date, format)	STRING	날짜형 데이터를 문자열로 변환
ADDDATE(date, interval)	DATE	DATE 형의 날짜에서 INTERVAL 지정한 시간만큼 더함
DATE(date)	DATE	DATE 형의 날짜 부분을 반환
DATEDIFF(date1, date2)	INTEGER	DATE 형의 date1 - date2 날짜 차이를 반환
SYSDATE	DATE	DBMS 시스템상의 오늘 날짜를 반환하는 함수

 

 

마당서점은 주문일로부터 10일 수 매출을 확정한다. 각 주문의 확정일자를 구하시오

SELECT orderid '주문번호', orderdate '주문일', ADDDATE(orderdate, INTERVAL 10 DAY) '확정'
FROM Orders;

 

 

마당서점이 2014년 7월 7일에 주문받은 도서의 주문번호, 주문일, 고객번호, 도서번호를 모두 보이시오 단, 주문일은 '%Y-%m-%d' 형태로 표시한다

SELECT orderid '주문번호', STR_TO_DATE(orderdate, '%Y-%m-%d') '주문일', custid '고객번호', bookid '도서번호'
FROM Orders
WHERE orderdate=DATE_FORMAT('20140707', '%Y-%m-%d');

 

 

DBMS 서버에 설정된 현재 날짜와 시간, 요일을 확인하시오

SELECT SYSDATE(), DATE_FORMAT(SYSDATE(), '%Y/%m/%d %M %h:%s') 'SYSDATE_1';

 

 

 

NULL 값 처리

 

NULL이란 아직 지정되지 않은 값을 말한다. NULL 값에 대한 처리는 실제 프로그래밍 과정에서 실수하기 쉬우므로 사용에 주의해야 한다. '0', ''(공백) 등 과 다른 특별한 값임을 명심해야한다. 이는 비교 연산자로도 비교 불가능하고 연산 수행 결과 또한 NULL로 반환된다. 

 

 

NULL값이 포함된 행의 연산 결과

- NULL + 숫자 : NULL

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

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

 

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

 

 

이름, 전화번호가 포함된 고객목록을 보이시오 단, 전화번호가 없는 고객은 '연락처없음'으로 표시하시오

SELECT name '이름', IFNULL(phone, '연락처없음') '전화번호'
FROM Customer;

 

 

행번호 출력

 

 

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

SET @seq:=0;
SELECT (@seq:=@seq+1) '순번', custid, name, phone
FROM Customer
WHERE @seq < 2;

 

 

 

부속질의 (subquery) 

 

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

 

고객 이름과 주문 내역을 같이 보려면 Orders 테이블과 Customer 테이블을 연관시켜야 한다.  두 테이블의 관계를 기초로 박지성 고객의 주문 내역을 확인하려면 어떻게 해야 할까?

 

그 방법에는 조인 혹은 부속 질의를 사용한다. 

 

1. 조인을 사용한 경우

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

2. 부속질의를 사용할 경우

- Customer 테이블에서 박지성 고객의 고객번호를 찾고, 찾은 고객번호를 바탕으로 Orders 테이블에서 확인한다. 

 

 

 

부속질의 종류

명칭	위치	영문 및 동의어	설명
스칼라 부속질의	SELECT 절	scalar subquery	SELECT벌에서 사용되며 단일 값을 반환하기 때문에 슼칼라 부속질의라고 한다.
인라인 뷰	FROM 절	inline view, table subquery	FROM 절에서 결과를 뷰 형태로 반환하기 때문에 인라인 뷰라고 한다.
중첩 질의	WHERE 절	nested subquey, predicate subquery	WHERE 절에서 술어와 같이 사용되며 결과를 한정시키기 위해 사용된다. 상관 혹은 비상관 형태다.

 

1. 스칼라 부속질의

- SELECT 절에서 사용되는 부속질의로 부속질의의 결과 값을 단일 행, 당일 열의 스칼라 값으로 반환한다. 반환 값이 다중 행이거나 다중 열이면 에러를 출력한다. 

 

 

마당서점의 고객별 판매액을 보이시오 (고객이름과 고객별 판매액 출력)

SELECT (SELECT name
        FROM Customer cs
        WHERE cs.custid=od.custid) 'name', SUM(saleprice) 'total'
FROM Orders od
GROUP BY od.custid;

 

 

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

ALTER TABLE Orders ADD bname VARCHAR(40);

UPDATE Orders
SET bname=(SELECT bookname
           FROM Book
           WHERE Book.booid=Orders.bookid);

 

 

 

2. 인라인 뷰 - FROM 부속질의

- FROM 절에는 테이블 이름이 위치하는데 여기에 테이블 이름 대신 인라인 뷰 뷰속질의를 사용하면 보통의 테이블과 같은 형태로 사용할 수 있다. 다만, 가상의 테이블인 뷰 형태로 제공되기 때문에 상관 부속질의로 사용될 수는 없다. 

 

 

고객번호가 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;

 

 

3. 중첩 질의 - WHERE 부속질의

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

 

 

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

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);

 

 

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

SELECT SUM(saleprice) 'total'
FROM Orders
WHERE custid IN (SELECT custid
                 FROM Customer
                 WHERE address LIKE `%대한민국%`);

 

 

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

ALL, SOME(ANY) 연산자는 비교 연산자와 함께 사용되며 ALL은 모든, SOME은 어떠한 이라는 의미를 가진다. 

ALL은 최대값과 같다고 볼 수 있고 SOME은 최소값과 같다고 볼 수 있다. 

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

 

 

EXISTS 연산자를 사용하여 대한민국에 거주하는 고객에게 판매한 도서의 총 판매액을 구하시오

EXISTS와 NOT EXISTS는 데이터의 존재 유무를 확인하는 연산자이다. 부속질의에 조건에 만족하여 값이 존재하면 참이되고, 그렇지 않으면 거짓이 도니다. NOT EXISTS의 경우 이와 반대로 동작한다. 

SELECT SUM(saleprice) 'total'
FROM Orders od
WHERE EXISTS (SELECT *
              FROM Customer cs
              WHERE address LIKE `%대한민국%` AND cs.csutid=od.custid);