[10일차] ASAC 빅데이터 분석가 4기(SQL-2)

⚙️ SQL 기초

📌 CREATE문

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

CREATE TABLE 테이블이름
 ( { 속성이름 데이터타입
     [NOT NULL | UNIQUE | DEFAULT 기본값 | CHECK 체크조건]
    }
     [PRIMARY KEY 속성이름(들)]
     {[FOREIGN KEY 속성이름 REFERENCES 테이블이름(속성이름)]
 	 [ON DELETE [CASCADE┃SET NULL]
     }
 )

---

📌 테이블 복사

• LIKE를 이용하여 테이블 구조 복사하기

CREATE TABLE Test.Book LIKE madang.Book;

• SELECT를 이용하여 테이블 구조 및 데이터 복사하기

CREATE TABLE Test.Imported_Book
SELECT * FROM madang.Imported_Book;

---

📌 ALTER문

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

ALTER TABLE 테이블이름
[ADD 속성이름 데이터타입]
[DROP COLUMN 속성이름]
[MODIFY 속성이름 데이터타입]
[MODIFY 속성이름 [NULL┃NOT NULL]]
[ADD PRIMARY KEY(속성이름)]
[[ADD┃DROP] 제약이름]

---

📌 DROP문

• 테이블을 삭제하는 명령
• 테이블의 구조와 데이터를 모두 삭제하므로 사용에 주의해야 함
• 기본 문법

DROP TABLE 테이블이름

---

📌 INSERT문

• 테이블에 새로운 튜플을 삽입하는 명령
• 기본 문법

INSERT INTO 테이블이름[(속성리스트)]
VALUES (값리스트);

---

📌 UPDATE문

• 특정 속성 값을 수정하는 명령
• 기본 문법

UPDATE 테이블이름
SET 속성이름1=값1[, 속성이름2=값2, ...]
[WHERE <검색조건>];

---

📌 DELETE문

• 테이블에 있는 기존 튜플을 삭제하는 명령
• 기본 문법

DELETE FROM 테이블이름
[WHERE 검색조건];

---

⚙️ SQL 고급

📌 SQL 내장 함수

• 숫자 함수

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

• 문자 함수

반환 구분	함수	설명
문자값 반환 함수 s : 문자열 c : 문자 n : 정수 k : 정수	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 반환
	CHAR_LENGTH(s)	문자열의 문자 수를 반환

• 날짜 함수

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

• 날짜 format의 주요 지정자

인자	설명
%w	요일 순서(0~6, Sunday=0)
%W	요일(Sunday~Saturday)
%a	요일의 약자(Sun~Sat)
%d	1달 중 날짜(00~31)
%j	1년 중 날짜(001~365)
%h	12시간(01~12)
%H	24시간(00~23)
%i	분(0~59)
%m	월 순서(01~12, January=01)
%b	월 이름 약어(Jan~Dec)
%M	월 이름(January~December)
%s	초(0~59)
%Y	4자리 연도
%y	4자리 연도의 마지막 2자리

📌 NULL 값 처리

• NULL이란?
  • 아직 지정되지 않은 특별한 값
  • 비교 연산자로 비교가 불가능
  • NULL값의 연산 수행시 결과가 NULL값으로 반환됨
• 주의사항
  • NULL + 숫자 연산의 결과는 NULL
  • 집계 함수 계산시 NULL이 포함된 행은 집계에서 빠짐
  • 해당되는 행이 하나도 없을 경우 SUM, AVG 함수의 결과는 NULL이 되며, COUNT 함수의 결과는 0
• ISNULL, ISNOTNULL : NULL값 여부를 확인
• IFNULL : NULL값을 다른 값으로 대체하여 연산하거나 다른 값으로 출력

---

📌 행번호 출력

• MySQL에서 변수는 이름 앞에 '@' 기호를 붙이며, 치환문에는 SET과 ':=' 기호를 사용
• 자료를 일부분만 확인하여 처리할 때 유용함

---

📌 부속질의

• 부속질의의 종류

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

📌 뷰

• 뷰 : 하나 이상의 테이블을 합하여 만든 가상의 테이블
• 장점
  • 편리성 및 재사용성 : 자주 사용되는 복잡한 질의를 뷰로 미리 정의해 놓을 수 있음
  • 보안성 : 사용자별로 필요한 데이터만 선별하여 보여줄 수 있고, 중요한 질의의 경우 질의 내용을 암호화할 수 있음
  • 독립성 : 미리 정의된 뷰를 일반 테이블처럼 사용할 수 있기 때문에 편리하고, 사용자가 필요한 정보만 요구에 맞게 가공하여 뷰로 만들어 쓸 수 있음
• 특징
  • 원본 데이터 값에 따라 같이 변함
  • 독립적인 인덱스 생성이 어려움
  • 삽입, 삭제, 갱신 연산에 많은 제약이 따름
• 기본 문법 (생성)

CREATE VIEW 뷰이름 [(열이름 [ ... ])]
AS SELECT 문

• 기본 문법 (수정)

CREATE OF REPLACE VIEW 뷰이름 [(열이름 [ ... ])]
AS SELECT 문

• 기본 문법 (삭제)

DROP VIEW 뷰이름 [ ... ];

---

📌 인덱스

• 인덱스 : 도서의 색인이나 사전과 같이 데이터를 쉽고 빠르게 찾을 수 있도록 만든 데이터 구조
• 인덱스의 특징
  • 테이블에서 한 개 이상의 속성을 이용하여 생성함
  • 빠른 검색과 함께 효율적인 레코드 접근이 가능함
  • 순서대로 정렬된 속성과 데이터의 위치만 보유하므로 테이블보다 적은 공간을 차지함
  • 지정된 값들은 테이블의 부분집합이 됨
  • 일반적으로 B-Tree 형태의 구조를 가짐
  • 데이터의 수정, 삭제등의 변경이 발생하면 인덱스의 재구성이 필요함
• MySQL 인덱스의 종류

인덱스 명칭	설명 / 생성 예
클러스터 인덱스	기본적인 인덱스로, 테이블 생성 시 기본키를 지정하면 기본키에 대하여 클러스터 인덱스를 생성한다. 기본키를 지정하지 않으면 먼저 나오는 UNIQUE 속성에 대하여 클러스터 인덱스를 생성한다. 기본키나 UNIQUE 속성이 없는 테이블은 MySQL 이 자체 생성한 행번호(Row ID)를 이용하여 클러스터 인덱스를 생성한다.
보조 인덱스	클러스터 인덱스가 아닌 모든 인덱스는 보조 인덱스이며 보조 인덱스의 각 레코드는 보조 인덱스 속성과 기본키 속성 값을 갖고 있다. 보조 인덱스를 검색하여 기본키 속성 값을 찾은 다음 클러스터 인덱스로 가서 해당 레코드를 찾는다.

• 인덱스 생성 시 고려사항
  • WHERE절에 자주 사용되는 속성이어야 함
  • 조인에 자주 사용되는 속성이어야 함
  • 단일 테이블에 인덱스가 많으면 속도가 느려질 수 있음 (테이블당 4~5개 정도 권장)
  • 속성이 가공되는 경우 사용하지 않음
  • 속성의 선택도가 낮을 때 유리함 (속성의 모든 값이 다른 경우)
• 기본 문법

CREATE [UNIQUE] INDEX [인덱스이름]
ON 테이블이름 (컬럼 [ASC|DESC] [{컬럼 [ASC|DESC]} ... ])[;]

---

⚙️ 실습

📝 테이블 생성하기

💡 다음과 같은 속성을 가진 NewOrders 테이블을 생성하시오
 orderid(주문번호) - INTEGER, 기본키
custid(고객번호) - INTEGER, NOT NULL 제약조건, 외래키(NewCustomer.custid, 연쇄삭제)
bookid(도서번호) - INTEGER, NOT NULL 제약조건
saleprice(판매가격) - INTEGER  orderdate(판매일자) – DATE

CREATE TABLE NewOrders (
orderid INTEGER,
custid INTEGER NOT NULL,
bookid INTEGER NOT NULL,
saleprice INTEGER,
orderdate DATE,
PRIMARY KEY (orderid),
FOREIGN KEY(custid) REFERENCES NewCustomer(custid) ON DELETE CASCADE);

---

📝 테이블 변경하기

💡 NewBook 테이블에 VARCHAR(13)의 자료형을 가진 isbn 속성을 추가하시오

ALTER TABLE NewBook ADD isbn VARCHAR(13);

---

💡 NewBook 테이블의 isbn 속성의 데이터 타입을 INTEGER형으로 변경하시오

ALTER TABLE NewBook MODIFY isbn INTEGER;

---

💡 NewBook 테이블의 isbn 속성을 삭제하시오

ALTER TABLE NewBook DROP COLUMN isbn;

---

💡 NewBook 테이블의 bookid 속성에 NOT NULL 제약조건을 적용하시오

ALTER TABLE NewBook MODIFY bookid INTEGER NOT NULL;

---

💡 NewBook 테이블의 bookid 속성을 기본키로 변경하시오

ALTER TABLE NewBook ADD PRIMARY KEY(bookid);

---

📝 데이터 삽입하기

💡 Book 테이블에 새로운 도서 '스포츠 의학'을 삽입하시오
스포츠 의학은 한솔의학서적에서 출간했으며 가격은 90,000원이다

INSERT INTO Book VALUES(11, '스포츠 의학', '한솔의학서적', 90000)

---

💡 Book 테이블에 새로운 도서 ‘스포츠 의학’을 삽입하시오
스포츠 의학은 한솔의학서적에서 출간했으며 가격은 미정이다

INSERT INTO Book(bookid, bookname, publisher) VALUES(14, '스포츠 의학', '한솔의학서적');

---

💡 수입도서 목록(Imported_book)을 Book 테이블에 모두 삽입하시오

INSERT INTO Book(bookid, bookname, price, publisher)
SELECT bookid, bookname, price, publisher
FROM Imported_book;

---

📝 데이터 변경하기

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

UPDATE Customer
SET address = '대한민국 부산'
WHERE custid = 5;

---

💡 Book 테이블에서 14번 ‘스포츠 의학’의 출판사를 imported_book 테이블의 21번 책의 출판사와 동일하게 변경하시오

UPDATE Book 
SET    publisher = (SELECT publisher 
                    FROM   imported_book 
                    WHERE  bookid = '21') 
WHERE  bookid = '14' ;

---

📝 데이터 삭제하기

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

DELETE FROM Book
WHERE bookid = '11';

---

💡 모든 고객을 삭제하시오

DELETE FROM Customer;

---

📝 숫자 함수 사용하기

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

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

---

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

SELECT ROUND(4.875, 1)
FROM Dual;

---

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

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

---

📝 문자 함수 사용하기

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

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

---

📝 날짜 함수 사용하기

💡 마당서점은 주문일로부터 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 값에 대해 집계함수를 사용해보시오

SELECT SUM(price), AVG(price), COUNT(*), COUNT(price)
FROM book
WHERE bookid < 0;

---

📝 NULL값 확인하기

💡 NULL 값을 확인하시오

SELECT *
FROM book
WHERE price IS NULL;

---

📝 NULL값 처리하기

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

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

---

📝 행번호 출력하기

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

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

---

📝 부속질의 사용하기

💡 마당서점의 고객별 판매액을 보이시오

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

---

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

SELECT bookid,
(SELECT bookname FROM Book as B WHERE B.bookid = O.bookid)
FROM Orders as O;

---

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

---

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

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

---

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

SELECT orderid, custid, saleprice
FROM Orders od
WHERE saleprice > (SELECT AVG(saleprice) FROM Orders so WHERE od.custid = so.custid);

---

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

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

---

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

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

---

💡 EXIST 연산자로 대한민국에 거주하는 고객에게 판매한 도서의 총 판매액을 구하시오

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

---

📝 뷰 사용하기

💡 주소에 '대한민국'을 포함하는 고객들로 구성된 뷰를 만들고 조회하시오
뷰의 이름은 vw_Customer로 설정하시오

CREATE VIEW vw_Customer
AS SELECT * FROM Customer WHERE address LIKE '%대한민국%';

---

📝 인덱스 생성하기

💡 Book 테이블의 bookname 열을 대상으로 비 클러스터 인덱스 ix_Book을 생성하라

CREATE INDEX ix_Book ON Book (bookname);