[SQL] 실습 정리(SUB_QUARY, UPDATE)

지난주차 복습 문제

SUB QUARY

주로 SUBQUARY를 이용할 때 두 개의 SQL문을 따로 만들어 보고

  그 후에 합치면 조금 더 수월하게 작성할 수 있다 

 

0.SUB_QUARY.1 가격이 가장 비싼 도서의 도서 아이디, 도서명을 출력

SELECT bookid, bookname
FROM Book
WHERE price = (SELECT MAX(price) FROM Book);

RESULT

0.SUB_QUARY.2 도서를 구매한 내역이 있는 고객의 고객 아이디, 고객명을 출력

SELECT custid, name
FROM Customer
WHERE custid IN (SELECT custid FROM Orders);

RESULT

---

우선, 실습을 진행하기 전에 기존의 테이블을 바꿔주기 위한 SQL문을 입력한다

drop table if exists NewCustomer; 
drop table if exists NewBook;
drop table if exists NewOrders;

CREATE TABLE           NewCustomer (
 custid 	               INTEGER  PRIMARY KEY,
 name 	               VARCHAR(40),
 address 	               VARCHAR(40),
 phone 	               VARCHAR(30) );
 
CREATE TABLE 	NewBook (
  bookname	VARCHAR(20),
  publisher		VARCHAR(20),
  price 		INTEGER,
  PRIMARY KEY	(bookname, publisher)); 
  
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 );

입력 이후 테이블이 생성되어있는 구조를 보고 싶다면?

DESC 테이블명

NewBook

NewCustomer

NewOrders

ALTER

테이블 구조를 바꾸는 것으로 보통 개발자들은 쓰지 않는다

다만 데이터 분석하는 분들은 임시 자료구조를 만들어서 분석하곤 한다

 

ALTER TABLE + SQL문 = 테이블의 다음과 같은 구조조정 작업을 한다

 

관련 SQL문

ADD

MODIFY

DROM COLUMN

 

 

ALTER TABLE은 즉시 적용되나 변경된 테이블을 확인하려면

DESC TABLENAME을 이용해서 확인해 준다

 

1.ALTER.1 NewBook TABLE에 isdn COLUMN을 추가

ALTER TABLE NewBook ADD isdn VARCHAR(13);

RESULT

1.ALTER.2 NewBook TABLE에 isdn COLUMN을 INTEGER형으로 변경

ALTER TABLE NewBook MODIFY isdn INTEGER;

RESULT

1.ALTER.3 NewBook TABLE의 isdn COLUMN을 삭제

ALTER TABLE NewBook DROP COLUMN isdn;

RESULT

---

DROP

보통 기업에 들어가면 DROP을 할 수 있는 권한을 주지 않는다

만약 DROP을 해버린다면 미리 사표를 쓰고

기업에 선제공격을 날려버리자

정상적인 기업이라면 DROP권한을 가진 계정을 신입사원에게 주지 않는다

 

DROP이란?

테이블을 쥐도 새도 모르게 날려버릴 수 있는 SQL문

---

데이터 조작어

INSERT문 = 삽입

UPDATE문 = 수정

DELETE문 = 삭제

 

INSERT

테이블에 새로운 데이터를 삽입하는 SQL문

 

UPDATE

기존의 데이터를 다른 데이터로 바꿔주는 SQL문

UPDATE권한도 굉장히 조심스럽게 다루어야 하기 때문에

아무에게나 주지 않는 것들 중 하나이다

 

UPDATE를 사용할 때 WHERE절을 쓰지 않으면 모든 데이터가 전부 바뀌어버리니

주의하도록 하자

 

DELETE

TABLE에 있는 기존 투플을 삭제하는 명령

 

DELETE FROM TABLENAME

[WHERE 검색조건];

이 또한 UPDATE처럼 WHERE절을 신중하게 작성해야 한다

 

2.UPDATE.1 custid가 5인 소비자의 주소를 "jungwang"으로 바꾸기

UPDATE Customer
SET address = "jungwang"
WHERE custid = 5;

REUSLT

2.UPDATE.2 name이 Park Jisung인 소비자의 주소를 "TUKOREA"로 바꾸기

UPDATE Customer
SET address = "TUKOREA"
WHERE name = "Park Jisung";

REUSLT

2.UPDATE+SUB_QUARY.3 Book테이블에서 14번 책의 출판사를 imported_book테이블의 21번 책의 출판사와 동일하게 변경하시오 

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

 

 

2.UPDATE.4 bookid = 9인 도서의 가격을 수입도서인 22번 도서 가격으로 변경

UPDATE Book
SET price = (SELECT price
              FROM Imported_Book
              WHERE bookid = '22')
WHERE bookid = '9';

3.DELETE.1 도서번호가 11인 도서를 삭제하시오

DELETE FROM Book
WHERE bookid = 11;

 

기초 단계 종료

---

고급 단계 ON

내장 함수

TYPE 변환

뷰

파티션(FOR BIGDATA)

 

내장 함수란?

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

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

 

숫자 함수

ABS(숫자)

• 숫자의 절댓값 계산

CEIL(숫자)

• 숫자보다 크거나 같은 최소의 정수(올림 처리)

FLOOR(숫자)

• 숫자보다 작거나 같은 최소의 정수(내림 처리)

ROUND(숫자, m)

• 숫자의 반올림, m은 반올림 기준 자릿수

LOG(n, 숫자)

• 숫자의 자연로그값을 반환(LOG(n))

POWER(숫자, n)

• 숫자의 n제곱 값을 계산

SQRT(숫자)

• 숫자의 제곱근 값을 계산

SIGN(숫자)

• 숫자의 음/양/0인 수를 출력 ( -1/1/0)

---

실습

FROM DUAL이라는 더미 테이블을 통해 실행시키도록 하자

4.ABS.1 -4.5의 절대값을 출력

select abs(-4.5)
from dual;

4.ABS+CEIL+FLOOR+ROUND.2

SELECT ABS(-4.5), CEIL(4.5), FLOOR(4.5), ROUND(5.678,1) 
FROM DUAL;

---

문자 함수

CONCAT(s1, s2)

• 두 문자열을 연결, '마당 '+'서점' = 마당서점
• 많은 빈도수로 사용된다

5.CONCAT.1 name과 address를 연결하여 출력

SELECT CONCAT(name, address)
FROM Customer;

 

LOWER(s)

• 대문자를 소문자로 바꾸어준다

UPPER(s)

• 소문자를 대문자로 바꾸어준다

6.LOWER+UPPER.1 name을 소문자로, address를 대문자로

SELECT LOWER(name), UPPER(address)
FROM Customer;

 

SUBSTR(s,n,k)

• 대상 문자열(s)의 지정된 자리(n)에서부터 지정된 길이(k)만큼 잘라서 반환
• 정말 많은 빈도로 사용된다

7.SUBSTR.1 name을 1번째 자리에서 2개만 잘라서 반환

SELECT SUBSTR(name,1,2)
FROM Customer;

TRIM(LEADING/TRAILING '#' FROM '###MYSQL')

• 주어진 문자열의 앞에 존재하는 특정 문자 제거

REPLACE(s1,s2,s3)

• 대상 문자열(s1)의 지정한 문자(s2)를 원하는 문자(s3)로 변경
• 많이 쓰이지는 않지만 쓰이기는 한다

LPAD/RPAD(s,n,c)

• 대상 문자열(s)의 왼쪽/오른쪽부터 지정한 자리수(n)까지 지정한 문자(c)로 채운다

LENGTH(s)

• 대상 문자열(s)의 Byte반환
• 알파벳 = 1byte
• 한글 = 3byte
• (UTF8기준)

---

날짜/시간 함수

Y = 2022, 2023...

y = 22, 00, 99, 23....

M = Nobemver, January...

m = 01, 11, 12, 02....

D = 1st, 2nd, 18th....

d = 1, 2, 3, 18....

W = Monday, Tuesday...

w = 1, 2....

 

STR_TO_DATE(string, format)

• 반환 형태 = DATE
• 문자열(STRING) DATA를 날짜형(DATE)로 반환

DATE_FORMAT(date, format)

• 반환 형태 = FORMAT
• 날짜형(DATE) DATA를 FORMAT형태로 반환

SELECT DATE_FORMAT(SYSDATE(),'%Y%M%D')
FROM DUAL;

SELECT DATE_FORMAT(SYSDATE(),'%y%m%d')
FROM DUAL;

SELECT DATE_FORMAT(SYSDATE(),'%y%m%W')
FROM DUAL;

소문자w 입력 시 friday = 5가 된다

8.DATE_FORMAT.1 Orders TABLE의 orderdate를 STRING TYPE(yyyymmdd형태)로 변경

SELECT DATE_FORMAT(orderdate,'%Y%m%d')
FROM Orders;

8.DATE_FORMAT.2 월별 총 구매금액(yyyymm->yy월, 총 구매금액)

SELECT CONCAT(SUBSTR(DATE_FORMAT(orderdate, '%Y%m'),5,2),'Wall'), SUM(saleprice)
FROM Orders
GROUP BY CONCAT(SUBSTR(DATE_FORMAT(orderdate, '%Y%m'), 5, 2), 'Wall');

공모전 시 굉장히 유용하다고 한다(전처리 부문)

 

DATE_DIFF(date1, date2)

• date1과 date2의 날짜 차이를 반환

SELECT DATEDIFF('2022-11-05', '2022-12-03')

이것을 활용하여 평균구매주기를 알 수 있다

방법: 고객별로 돌아오는 날짜 구해서 평균내기