231027_DB 설계와 SQL 활용

수업 시작 전 교수님의 이야기

교재는 오라클을 기준으로 나와 있기 때문에 실습할 때 MySQL이랑 다른 부분이 있으므로 오라클과 MySQL 모두 사용할 수 있도록 할 것. 두가지 신택스는 모두 알고 있어야 함. 

실제로 DB 서비스에서는 프로그램을 만들어서 사용, 웹은 DB가 백앤드와 연결이 됨. 가령 웹사이트에서 고객이라는 테이블 안에 정보가 저장이 되고, 로그인이나 주문을 할 시 연동이 됨. 연동하는 기술로 라이브러리가 필요함. 라이브러리를  import시켜서 코딩을 하는 것. 

C언어 계열의 프로그래밍은 ODBC라는 라이브러리를 이용해서, 자바 계열은 JDBC을 이용해서. 프로그램을 만들 때 임포트를 시켜서 라이브러리에서 제공하는 연결함수 또는 호출함수를 던져주는 것. MySQL에 연결을 할거라면 MySQL 홈페이지에서 제공하는 JDBC랑 ODBC를 사용하면 됨. 

백엔드 쪽에서 여러 가지 백엔드 프레임워크들을 사람들이 만들어서 쓰기에는 어려움. 백엔드 하는 사람이 DB를 잘 모르더라도 DB에 데이터를 더 쉽게 주고받게끔 해 주기 위한 라이브러리는 따로 있음.ORM? 자바 계열의 스프링부트에서는 이걸 JPA라고 얘기하고, 장고 계열과 파이썬 계열 쪽에서는 ORM이라고 얘기함. 따라서 나중에 실제 웹서비스를 개발할 쨈직접 SQL로 create 테이블이나 select 할 필요는 없음. 다만 백앤드 개발자를 목표로 한다면 DB에 대해서는 어느정도 이해가 있는 편이 좋고, 그래서 SQL에 대해 이해하고 있는 것이 좋음.

 

빅데이터는 세가지 용어를 가지고 정의함.

V, 볼륨.양의 크기. 서버마다 스펙은 다 다르기 때문에 용량의 기준은 없음. 빅데이터는 하나의 서버를 가지고 처리하는게 아닌 여러개의 서버를 가지고 처리함. 그것을 클러스터라고 함. 

V벨로시티, IT에서는 실시간을 의미. 그때그때 데이터를 저장해야 되고 그때그때 데이터를 처리해야 한다는 것.

V버라이어티, 문자, 음악, 동영상 등... 다양성을 의미.

빅데이터는 단순히 양이 많은 데이터를 뜻하는게 아닌, 실시간성과 크기, 다양한 형태를 가지고 있기 때문에 사람들이 빅데이터라고 불림. 

비지도학습 unsupervised learning 정답을 안 가르쳐주면서 학습하는건 비지도학습. 데이터가 쌓이다보면 학습을 하게 됨, 이러한 방식으로 학습을 시키는 것을 러닝머신이라고 함.

지도학습 supervised learning 정답을 가르치면서 학습하는건 지도학습.

딥러닝, 수많은 반복으로 인해 컴퓨터도 학습을 함. 학습하는 소프트웨어 쪽으로 학습하는 단계를 여러 레이어로 나눠서 그 레이어를 반복해서 돌림. 이 레이어를 통해 학습을 할 때마다 정보를 주고받고, 다음 레이어로 넘어갈 때 이전 레이어에서 학습된 결과를 넘겨받는 과정이 사람의 뇌세포 학습과정과 비슷함. 학습에 깊이가 여러 단계로 이루어진다고 해서 딥러닝이라고 불림. 

 

그 외 추가 주저리

a>b를 비교할 땐 아스키값을 비교하면 됨

만약 문자  abc>adc 를 비교할 때 앞에서 비교하다가 두번째 거 b와 d를 비교하고, b가 작으므로 틀렸다고 함.

컴페어라는 함수는 0 아니면 0이 아닌 값을 반환.

보통 1 아니면 양수나 음수를 반환, 가령 abc는 아스키코드값으로 비교하듯. 

cpmpere('abc','adc') 같은 경우 abc는 아스키코드값으로 65, 66, 67이고 adc는 65,64,67임,

각각 아스키코드값을 빼면 0, 2, 0이 되고 가운데가 0이 아니므로 abc, adc는 같지 않다고 판별.

 

---

반올림 해서 출력하기

0은 반올림 하고자 하는 소수점 자릿수를 의미, 따라서 소수점 이하를 반올림 하지 않고 가장 가까운 정수값으로 반올림 됨.

 

나눈 나머지 값

10에는 나누고자 하는 값을 의미, 10으로 나누지 못하는 나머지 값은 그대로 출력되고 10으로 나눌 수 있는 값은 10으로 나눈 뒤 나머지 값을 출력했다. 즉, 200과 같은 숫자는 10으로 나누었을 때 나머지 값이 없으므로 0으로 출력. 45.20 같은 숫자는 10으로 나누었을 때 최대 4번 나누고 나머지 값으로 5.20을 출력한다.

 

날짜 간 개월 수 출력

TIMESTAMPDIFF  함수를 이용해 가입일로부터 오늘의 날짜 사이 시간 간격을 MONTH 월 단위로 계산할 것, membership은 고객의 가입일을 의미하고 NOW()함수는 현재 날짜와 시간을 의미한다. 

 

가입일 이후로 지금까지 며칠이 지났는지 표기

개월 수를 구한 것에 이어, 가입일 이후 지금까지의 기간을 일수로 표기해본 것. 위와 달리 이번에는 DATEDIFF 함수를 이용해, 가입일membership 로부터 현재 날짜 NOW() 까지의 일수를 구했다.

 

개월 수 더한 날짜 출력하기

가입일 (membership) 로부터 5개월 (5 MONTH )을 더한 날짜를 출력하기 위해, DATE_ADD 함수를 사용했다.

가입날짜로부터 100일을 구하기 위해  5 MONTH  가 아닌 100DAY를 사용.

 

특정 날짜 뒤에 오는 요일 날짜 출력하기

WEEKDAY(membership)  함수를 이용해 가입일( membership )로부터 다음 화요일의 날짜를 출력했다. (7 - WEEKDAY(membership))을 통해 membership 날짜로부터 해당 주의 일요일까지의 날짜를 계산하고, +2를 더해 다음주의 화요일로 이동한다. DATE_ADD 함수를 이용해 위에서 계산한 일수를 더하여 화요일의 날짜를 얻고, YYYY-MM-DD 형식으로 출력값을 얻기 위해 ' %Y-%m-%d' 형식으로 형식화 했다. 

WEEKDAY()의 특징

0 : 월요일
1 : 화요일
2 : 수요일
3 : 목요일
4 : 금요일
5 : 토요일
6 : 일요일

 

특정 날짜가 있는 달의 마지막 날짜 출력하기

가입일 membership 의 데이터 정보를 기반으로 LAST_DAY 함수 이용, 가입일을 기준으로 한 달의 마지막 날짜 출력

 

오늘부터 이번달 말일까지 남은 날짜 출력

(LAST_DAY(NOW()))는 NOW() 함수를 이용해 현재 날짜와 시간을 가져온 뒤, LAST_DAY 함수를 이용해 현재 월의 마지막 날짜를 계산한다. (NOW())는 현재 날짜를 출력하며, DATEDIFF는 두 날짜(현재날짜와 현재 월의 마지막 날짜)간의 남은 일 수를 계산한다. 

 

이름이 kim인 사람의 이름, 가입일, 가입한 달의 마지막 날짜를 출력

WHERE customer_name = "kim" 조건을 걸어 이름이 kim인 사람만 필터링, LAST_DAY(membership)로 가입일의 마지막 날짜를 출력.

 

형변환(형변환 (TO_CHAR, TO_DATE, TO_NUMBER)

이름이 kim 인 사람의 이름과 가입한 요일을 출력(TO_CHAR).

My SQL는 TO_CHAR 함수를 지원하지 않음. customer_name = 'kim' 조건을 걸어 이름이 kim인 사람만 필터링, DATE_FORMAT(membership, '%W')은 membership 에서 날짜를 불러온 뒤 '%W' 을 이용해 날짜를 요일 이름으로 포맷한다 (예를 들어, 날짜 '2023-10-27'는 'Thursday'로 변환됨). 출력된 요일을 한국어로 출력하기 위해 추가적으로 SET lc_time_names = 'ko_KR'를 사용했다.

다만 요일을 출력하기 위해 날짜를 불러온 뒤 해당 날짜를 통해 요일을 불러왔는데, 이를 좀 더 간편하게 작성해봤다. 결과는 동일함.

 

가입일이 2019-07-17 인 사람의 이름과 가입일을 출력(TO_DATE).

My SQL 은 TO_DATE를 지원하지 않음, 이에 따라 조건을 가입일  DATE ('2019-07-17') 로 작성.

 

문자형 '100.00' 을 숫자형 100.00 과 비교, 암시적 형변환 사용(TO_NUMBER).

'100.00' 숫자형으로 필터링 했으나, 숫자로 입력된 100.00 이 필터링 됨. 이는 컴파일에서 암시적 형변환을 사용했기 때문.

 

NULL값 대신 다른 데이터 출력

My SQL 은 NVL을 지원하지 않음. 대신 COALESCE, IFNULL 사용. price 에서 NULL 값은 "없음", "가격이 없습니다."로 출력.

 

IF문을 SQL로 구현하기(DECODE)

 

IF문을 SQL로 구현하기(CASE)

 

최대값 출력하기

price 중 MAX 금액 출력

category 중 shoes을 필터링, 그 중 MAX 값 출력

 

최소값 출력하기

price 중 MIN 값을 출력

category 중 ring을 필터링, 그 중 MIN 값 출력

 

평균값 출력하기

NULL 값은 값이 없으므로 평균계산에서 제외, 제외한 price 값은 총 10개이므로 NULL값을 제외한 값/10의 결과를 출력.

NULL값을 0으로 치환 한 뒤 평균값 출력

 

토탈값 출력하기

각 카테고리 별 합계 출력

ROUP BY 를 이용해 각 카테고리로 그룹화하고, SUM(price) 함수를 사용해서 각 그룹 내의 가격을 더한다. 그 뒤에 HAVING 절을 사용하여 가격 합계가 50 이상인 그룹만을 결과로 반환한다. 

 

GROUP BY category을 통해 HAVING SUM(price) >= 50 조건에 맞게 그룹화 한다. 그룹화 된 "category" 중 "price" 의 금액을 합산한 뒤, WHERE category != 'ring' 를 이용해 ring이 아닌 경우를 필터링한다. 최종적으로 50 이상 조건에 맞는 나머지 카테고리에 대한 데이터를 가져온다.

 

 

건수 출력하기

product_name에 입력된 모든 카테고리의 개수는 총 16개, 두 SELECT의 결과는 동일하다.

이미지 주석 참고

 

---

이하 SQL 실습을 진행하면서 작성했던 코드는 아래 더보기 참고

CREATE DATABASE Customer_list_3;
USE Customer_list_3;

CREATE TABLE Customer_Information ( -- 고객정보
    seq INT NOT NULL AUTO_INCREMENT,
    customer_name VARCHAR (20),
    Address VARCHAR (50),
    tell VARCHAR (20),
    email VARCHAR (20),
    membership DATE,
    PRIMARY KEY(seq)
);
CREATE TABLE product_name ( -- 상품명
    seq INT NOT NULL AUTO_INCREMENT,
    product_number VARCHAR(30),
    category VARCHAR(30),
    material VARCHAR(30),
    weight DECIMAL(10, 2),
    price  DECIMAL(10, 2),
    customer_information_id INT,
    PRIMARY KEY (seq),
    FOREIGN KEY (customer_information_id) REFERENCES Customer_Information(seq) -- 고객정보와 1:N
);
CREATE TABLE product_reservation ( -- 예약
    seq INT NOT NULL AUTO_INCREMENT,
    customer_name VARCHAR(20),
    Product_number INT,
    confirmation_of_deposit DATE,
    Address VARCHAR(50),
    tell VARCHAR(20),
    email VARCHAR(20),
    customer_information_id INT,
    PRIMARY KEY (seq),
    FOREIGN KEY (customer_information_id) REFERENCES Customer_Information(seq), -- 고객정보와 1:N
    FOREIGN KEY (Product_number) REFERENCES product_name(seq) -- 상품명과 1:1
);
CREATE TABLE confirmation_of_deposit ( -- 입금확인
    seq INT NOT NULL AUTO_INCREMENT,
    customer_name VARCHAR(20),
    tell VARCHAR(20),
    email VARCHAR(20),
    deposit_type VARCHAR(40),
    card_number VARCHAR(50),
    price INT,
    product_reservation_id INT,
    product_name_id INT,
    customer_information_id INT,
    PRIMARY KEY(seq),
    FOREIGN KEY (customer_information_id) REFERENCES Customer_Information(seq), -- 고객정보와 1:N 
    FOREIGN KEY (product_reservation_id) REFERENCES product_reservation(seq), -- 예약정보와 1:N
    FOREIGN KEY (product_name_id) REFERENCES product_name(seq) -- 상품명과 1:N
);
CREATE TABLE Questions ( -- 문의사항 
    seq INT NOT NULL AUTO_INCREMENT,
    customer_name VARCHAR(20),
    tell VARCHAR(20),
    Inquiry_code VARCHAR(20),
    email VARCHAR(20),
    date_of_inquiry DATE,
    customer_information_id INT,
    PRIMARY KEY(seq),
    FOREIGN KEY (customer_information_id) REFERENCES Customer_Information(seq) -- 고객정보와 1:N
);

CREATE TABLE Questions_ProductName ( -- 문의사항과 상품명 (N:M)
    question_id INT,
    product_name_id INT,
    PRIMARY KEY (question_id, product_name_id), 
    FOREIGN KEY (question_id) REFERENCES Questions(seq),
    FOREIGN KEY (product_name_id) REFERENCES product_name(seq)
);

CREATE TABLE Questions_ProductReservation ( -- 문의사항과 예약 (N:M)
    question_id INT,
    product_reservation_id INT,
    PRIMARY KEY (question_id, product_reservation_id),
    FOREIGN KEY (question_id) REFERENCES Questions(seq),
    FOREIGN KEY (product_reservation_id) REFERENCES product_reservation(seq)
);

CREATE TABLE Questions_ConfirmationOfDeposit ( -- 문의사항과 입금정보 (N:M)
    question_id INT,
    confirmation_id INT,
    PRIMARY KEY (question_id, confirmation_id),
    FOREIGN KEY (question_id) REFERENCES Questions(seq),
    FOREIGN KEY (confirmation_id) REFERENCES confirmation_of_deposit(seq)
);

select * from Customer_Information; -- 고객정보 데이터
INSERT INTO Customer_Information (customer_name, Address, tell, email, membership) VALUES ('kim', 'seoul', '01012345678', 'qwer@gmail.com', '2020-05-14');
INSERT INTO Customer_Information (customer_name, Address, tell, email, membership) VALUES ('Lee', 'gyeonggi-do', '01023456789', 'asdf@gmail.com', '2022-01-26');
INSERT INTO Customer_Information (customer_name, Address, tell, email, membership) VALUES ('hong', 'gyeonggi-do', '01022223333', 'zxcv@gmail.com', '2019-07-17');
INSERT INTO Customer_Information (customer_name, Address, tell, email, membership) VALUES ('gang', 'gangwon-do', '01033334444', 'wert@gmail.com', '2018-03-20');
INSERT INTO Customer_Information (customer_name, Address, tell, email, membership) VALUES ('jeong', 'busan', '01055556666', 'sdfg@gmail.com', '2015-04-08');
INSERT INTO Customer_Information (customer_name, Address, tell, email, membership) VALUES ('choi', 'daejeon', '01066667777', 'xcvb@gmail.com', '2016-12-24');
INSERT INTO Customer_Information (customer_name, Address, tell, email, membership) VALUES ('Lee', 'busan', '01077778888', 'erty@gmail.com', '2019-08-27');
INSERT INTO Customer_Information (customer_name, Address, tell, email, membership) VALUES ('kim', 'chungnam', '01088889999', 'dfgh@gmail.com', '2021-04-06');
INSERT INTO Customer_Information (customer_name, Address, tell, email, membership) VALUES ('song', 'seoul', '01044445555', 'cvbn@gmail.com', '2023-01-18');
INSERT INTO Customer_Information (customer_name, Address, tell, email, membership) VALUES ('Lee', 'seoul', '01011112222', 'rtyu@gmail.com', '2020-11-13');

select * from product_name; -- 상품명 데이터
INSERT INTO product_name (product_number, category, material, weight, price, customer_information_id) VALUES ('1', 'ring', 'silver', 7.50, 100.00 , 1);
INSERT INTO product_name (product_number, category, material, weight, price, customer_information_id) VALUES ('2', 'ring', 'gold', 3.08, 80.00, 2);
INSERT INTO product_name (product_number, category, material, weight, price, customer_information_id) VALUES ('3', 'earring', 'silver', 10.00, 70.30, 3);
INSERT INTO product_name (product_number, category, material, weight, price, customer_information_id) VALUES ('4', 'earring', 'gold', 7.54, 23.40, 4);
INSERT INTO product_name (product_number, category, material, weight, price, customer_information_id) VALUES ('5', 'top', 'silk', 200.00, 110.20, 5);
INSERT INTO product_name (product_number, category, material, weight, price, customer_information_id) VALUES ('6', 'top', 'linen', 340.00, 54.06, 6);
INSERT INTO product_name (product_number, category, material, weight, price, customer_information_id) VALUES ('7', 'bottom', 'leather', 45.20, 14.06, 7);
INSERT INTO product_name (product_number, category, material, weight, price, customer_information_id) VALUES ('8', 'bottom', 'linen', 300.00, 102.49, 8);
INSERT INTO product_name (product_number, category, material, weight, price, customer_information_id) VALUES ('9', 'shoes', 'rubber', 460.20, 48.78, 9);
INSERT INTO product_name (product_number, category, material, weight, price, customer_information_id) VALUES ('10', 'shoes', 'leather', 400.64, 100.75, 10);

select * from product_reservation; -- 예약 데이터
INSERT INTO product_reservation (customer_name, Product_number, confirmation_of_deposit, Address, tell, email, customer_information_id) VALUES ('kim', '1', '2023-10-14', 'seoul', '01012345678', 'qwer@gmail', 1);
INSERT INTO product_reservation (customer_name, Product_number, confirmation_of_deposit, Address, tell, email, customer_information_id) VALUES ('Lee', '2', '2023-10-11', 'gyeonggi-do', '01023456789', 'asdf@gmail', 2);
INSERT INTO product_reservation (customer_name, Product_number, confirmation_of_deposit, Address, tell, email, customer_information_id) VALUES ('hong', '3', '2020-08-20', 'gyeonggi-do', '01022223333', 'zxcv@gmail', 3);
INSERT INTO product_reservation (customer_name, Product_number, confirmation_of_deposit, Address, tell, email, customer_information_id) VALUES ('gang', '4', '2021-10-14', 'gangwon-do', '01033334444', 'wert@gmail', 4);
INSERT INTO product_reservation (customer_name, Product_number, confirmation_of_deposit, Address, tell, email, customer_information_id) VALUES ('jeong', '5', '2016-11-06', 'busan', '01055556666', 'sdfg@gmail', 5);
INSERT INTO product_reservation (customer_name, Product_number, confirmation_of_deposit, Address, tell, email, customer_information_id) VALUES ('choi', '6', '2018-02-25', 'daejeon', '01066667777', 'xcvb@gmail', 6);
INSERT INTO product_reservation (customer_name, Product_number, confirmation_of_deposit, Address, tell, email, customer_information_id) VALUES ('Lee', '7', '2020-01-18', 'busan', '01077778888', 'erty@gmail', 7);
INSERT INTO product_reservation (customer_name, Product_number, confirmation_of_deposit, Address, tell, email, customer_information_id) VALUES ('kim', '8', '2023-10-23', 'chungnam', '01088889999', 'dfgh@gmail', 8);
INSERT INTO product_reservation (customer_name, Product_number, confirmation_of_deposit, Address, tell, email, customer_information_id) VALUES ('song', '9', '2023-06-10', 'seoul', '01044445555', 'cvbn@gmail', 9);
INSERT INTO product_reservation (customer_name, Product_number, confirmation_of_deposit, Address, tell, email, customer_information_id) VALUES ('Lee', '10', '2021-07-15', 'seoul', '01011112222', 'rtyu@gmail', 10);

select * from confirmation_of_deposit; -- 입금 데이터
INSERT INTO confirmation_of_deposit (customer_name, tell, email, deposit_type, card_number, price, product_reservation_id, product_name_id, customer_information_id) VALUES ('kim', '01012345678', 'qwer@gmail', 'card', '123', 10, 1, 1, 1);
INSERT INTO confirmation_of_deposit (customer_name, tell, email, deposit_type, card_number, price, product_reservation_id, product_name_id, customer_information_id) VALUES ('Lee', '01023456789', 'asdf@gmail', 'card', '234', 13, 2, 2, 2);
INSERT INTO confirmation_of_deposit (customer_name, tell, email, deposit_type, card_number, price, product_reservation_id, product_name_id, customer_information_id) VALUES ('hong', '01022223333', 'zxcv@gmail', 'card', '345', 17, 3, 3, 3);
INSERT INTO confirmation_of_deposit (customer_name, tell, email, deposit_type, card_number, price, product_reservation_id, product_name_id, customer_information_id) VALUES ('gang', '01033334444', 'wert@gmail', 'card', '456', 20, 4, 4, 4);
INSERT INTO confirmation_of_deposit (customer_name, tell, email, deposit_type, card_number, price, product_reservation_id, product_name_id, customer_information_id) VALUES ('jeong', '01055556666', 'sdfg@gmail', 'card', '678', 55, 5, 5, 5);
INSERT INTO confirmation_of_deposit (customer_name, tell, email, deposit_type, card_number, price, product_reservation_id, product_name_id, customer_information_id) VALUES ('choi', '01066667777', 'xcvb@gmail', 'card', '789', 45.6, 6, 6, 6);
INSERT INTO confirmation_of_deposit (customer_name, tell, email, deposit_type, card_number, price, product_reservation_id, product_name_id, customer_information_id) VALUES ('Lee', '01077778888', 'erty@gmail', 'card', '912', 25, 7, 7, 7);
INSERT INTO confirmation_of_deposit (customer_name, tell, email, deposit_type, card_number, price, product_reservation_id, product_name_id, customer_information_id) VALUES ('kim', '01088889999', 'dfgh@gmail', 'card', '987', 18.4, 8, 8, 8);
INSERT INTO confirmation_of_deposit (customer_name, tell, email, deposit_type, card_number, price, product_reservation_id, product_name_id, customer_information_id) VALUES ('song', '01044445555', 'cvbn@gmail', 'card', '876', 20, 9, 9, 9);
INSERT INTO confirmation_of_deposit (customer_name, tell, email, deposit_type, card_number, price, product_reservation_id, product_name_id, customer_information_id) VALUES ('Lee', '01011112222', 'rtyu@gmail', 'card', '765', 8, 10, 10, 10);

select * from Questions; -- 문의 데이터
INSERT INTO Questions (customer_name, tell, Inquiry_code, email, date_of_inquiry, customer_information_id) VALUES ('kim', '01012345678', '1', 'qwer@gmail', '2023-10-17', 1);
INSERT INTO Questions (customer_name, tell, Inquiry_code, email, date_of_inquiry, customer_information_id) VALUES ('Lee', '01023456789', '2', 'asdf@gmail', '2023-10-10', 2);
INSERT INTO Questions (customer_name, tell, Inquiry_code, email, date_of_inquiry, customer_information_id) VALUES ('hong', '01022223333', '3', 'zxcv@gmail', '2021-07-13', 3);
INSERT INTO Questions (customer_name, tell, Inquiry_code, email, date_of_inquiry, customer_information_id) VALUES ('gang', '01033334444', '4', 'wert@gmail', '2021-10-17', 4);
INSERT INTO Questions (customer_name, tell, Inquiry_code, email, date_of_inquiry, customer_information_id) VALUES ('jeong', '01055556666', '5', 'sdfg@gmail', '2016-10-30', 5);
INSERT INTO Questions (customer_name, tell, Inquiry_code, email, date_of_inquiry, customer_information_id) VALUES ('choi', '01066667777', '1', 'xcvb@gmail', '2018-02-25', 6);
INSERT INTO Questions (customer_name, tell, Inquiry_code, email, date_of_inquiry, customer_information_id) VALUES ('Lee', '01077778888', '2', 'erty@gmail', '2020-01-20', 7);
INSERT INTO Questions (customer_name, tell, Inquiry_code, email, date_of_inquiry, customer_information_id) VALUES ('kim', '01088889999', '3', 'dfgh@gmail', '2023-10-22', 8);
INSERT INTO Questions (customer_name, tell, Inquiry_code, email, date_of_inquiry, customer_information_id) VALUES ('song', '01044445555', '4', 'cvbn@gmail', '2023-06-15', 9);
INSERT INTO Questions (customer_name, tell, Inquiry_code, email, date_of_inquiry, customer_information_id) VALUES ('Lee', '01011112222', '5', 'rtyu@gmail', '2021-07-15', 10);

select * from Questions_ProductName; -- 문의와 상품 연결
INSERT INTO Questions_ProductName (question_id, product_name_id) VALUES (1, 1);
INSERT INTO Questions_ProductName (question_id, product_name_id) VALUES (2, 2);
INSERT INTO Questions_ProductName (question_id, product_name_id) VALUES (3, 3);
INSERT INTO Questions_ProductName (question_id, product_name_id) VALUES (4, 4);
INSERT INTO Questions_ProductName (question_id, product_name_id) VALUES (5, 5);
INSERT INTO Questions_ProductName (question_id, product_name_id) VALUES (6, 6);
INSERT INTO Questions_ProductName (question_id, product_name_id) VALUES (7, 7);
INSERT INTO Questions_ProductName (question_id, product_name_id) VALUES (8, 8);
INSERT INTO Questions_ProductName (question_id, product_name_id) VALUES (9, 9);
INSERT INTO Questions_ProductName (question_id, product_name_id) VALUES (10, 10);

select * from Questions_ProductReservation; -- 문의와 예약 연결
INSERT INTO Questions_ProductReservation (question_id, product_reservation_id) VALUES (1, 1);
INSERT INTO Questions_ProductReservation (question_id, product_reservation_id) VALUES (2, 2);
INSERT INTO Questions_ProductReservation (question_id, product_reservation_id) VALUES (3, 3);
INSERT INTO Questions_ProductReservation (question_id, product_reservation_id) VALUES (4, 4);
INSERT INTO Questions_ProductReservation (question_id, product_reservation_id) VALUES (5, 5);
INSERT INTO Questions_ProductReservation (question_id, product_reservation_id) VALUES (6, 6);
INSERT INTO Questions_ProductReservation (question_id, product_reservation_id) VALUES (7, 7);
INSERT INTO Questions_ProductReservation (question_id, product_reservation_id) VALUES (8, 8);
INSERT INTO Questions_ProductReservation (question_id, product_reservation_id) VALUES (9, 9);
INSERT INTO Questions_ProductReservation (question_id, product_reservation_id) VALUES (10, 10);

select * from Questions_ConfirmationOfDeposit; -- 문의와 입금 정보 연결
INSERT INTO Questions_ConfirmationOfDeposit (question_id, confirmation_id) VALUES (1, 1);
INSERT INTO Questions_ConfirmationOfDeposit (question_id, confirmation_id) VALUES (2, 2);
INSERT INTO Questions_ConfirmationOfDeposit (question_id, confirmation_id) VALUES (3, 3);
INSERT INTO Questions_ConfirmationOfDeposit (question_id, confirmation_id) VALUES (4, 4);
INSERT INTO Questions_ConfirmationOfDeposit (question_id, confirmation_id) VALUES (5, 5);
INSERT INTO Questions_ConfirmationOfDeposit (question_id, confirmation_id) VALUES (6, 6);
INSERT INTO Questions_ConfirmationOfDeposit (question_id, confirmation_id) VALUES (7, 7);
INSERT INTO Questions_ConfirmationOfDeposit (question_id, confirmation_id) VALUES (8, 8);
INSERT INTO Questions_ConfirmationOfDeposit (question_id, confirmation_id) VALUES (9, 9);
INSERT INTO Questions_ConfirmationOfDeposit (question_id, confirmation_id) VALUES (10, 10);

SELECT seq, tell, customer_name
FROM confirmation_of_deposit;

SELECT seq, membership, customer_name
FROM customer_information;

SELECT *
FROM product_name;

SELECT *
FROM questions;

SELECT customer_name as 성함, tell as 전화번호, membership as "가입일자" FROM customer_information;

SELECT customer_information.*, customer_name from customer_information;

SELECT concat(customer_name, email) FROM customer_information;

SELECT CONCAT(customer_name, '님의 이메일은 "', email, '" 입니다.') AS 고객이메일 FROM customer_information;

select * from product_name;
select distinct category from product_name;

select * from customer_information;
SELECT customer_name, membership FROM customer_information ORDER BY membership asc;
SELECT customer_name, membership FROM customer_information ORDER BY membership asc, customer_name desc;

SELECT category, material, weight FROM product_name WHERE category = 'ring';

select customer_name, Address, tell, email, membership FROM customer_information WHERE customer_name = 'kim';

select * from product_name;
SELECT category, product_number, price FROM product_name ORDER BY price asc;
select category, material, weight, price from product_name where price >= 50.00;
select category, material, weight, price from product_name 
where price between 50.00 and 100.00;

select * from customer_information;

select customer_name, Address, membership from customer_information where Address Like 'g%';

select customer_name, Address, tell, email, membership from customer_information where membership = '2022-01-26';

select product_number, material, weight, price from product_name where category in ('ring', 'earring', 'bottom');

select product_number, material, weight, price from product_name where category not in ('ring', 'earring', 'bottom');

select category, material, weight, price from product_name where category='top' and price >= 100.00;

select UPPER(customer_name), LOWER(customer_name), initcap(customer_name) from customer_information; 
-- MySQL에는 initcap 함수가 내장 함수로 포함되어 있지 않음.
select UPPER(customer_name), LOWER(customer_name) from customer_information;
SELECT UPPER(customer_name) FROM customer_information; -- 대문자로 출력
SELECT LOWER(customer_name) FROM customer_information; -- 소문자로 출력
select tell, membership from customer_information where UPPER(customer_name)='kim'; 
-- 이름이 kim 인 사람의 데이터가 대문자인지 소문자인지 확실하지 않을때, 대문자로 이름을 모두 조회한 뒤 kim 을 찾고 번호와 가입일을 출력함.

select customer_name from customer_information;
SELECT SUBSTR(customer_name, 1, 2) FROM customer_information;

select customer_name, length(customer_name) FROM customer_information;

SELECT INSTR(customer_name,'k') FROM customer_information;
select INSTR(email,'@') FROM customer_information;
SELECT SUBSTR(email, INSTR(email,'@')+1) FROM customer_information;
SELECT RTRIM( SUBSTR(email, INSTR(email,'@')+1), 'il') FROM customer_information; -- 데이터에 com 이 없어서 안되는건지? 다시해보기

SELECT price, REPLACE(price, '0', '*') FROM product_name; 
-- REPLACE 함수는 문자열을 대체하는 함수이므로 문자열을 지정해야 함. '0'을 문자열로 사용.
SELECT price, REGEXP_REPLACE (price, '[0-1]', '*') FROM product_name; -- REGEXP_REPLACE 함수 제공 안함
SELECT REPLACE(customer_name, SUBSTR(customer_name,2,1),'*') as "전광판_이름" FROM customer_information; 

SELECT Address, LPAD(sal, 11,'*') as Address1, RPAD(sal,11,'*') as Address2 FROM customer_information; -- 데이터 타입이 맞지 않아 사용 불가. 변환 필요...

-- My SQL 은 오라클과 달리 특정 철자 잘라내는 방식이 다르다.
-- 오라클은 TRIM, RTRIM, LTRIM 을 이용하지만 My SQL 은 LEADING, BOTH, TRAILING 를 사용한다.
SELECT TRIM(LEADING 'x' FROM'xxxbarxxx');
-> 'barxxx'
SELECT TRIM(BOTH 'x' FROM'xxxbarxxx');
-> 'bar'
SELECT TRIM(TRAILING 'xyz' FROM'barxxyz');
-> 'barx'

-- My SQL 에서 문자를 잘라내는 방법은 옆과 같음. 010 을 없애고 싶다면 ''에 공백을, XXX로 바꾸고 싶다면 'XXX' 로 작성하면 됨. 
SELECT REPLACE(tell, '010', '') FROM Customer_Information; 


-- LEADING 을 이용하면 아래처럼 쓸 수 있다 
SELECT tell as "전화번호", TRIM(LEADING '010' FROM tell) as "뒷번호"
FROM customer_information
WHERE tell like '010%';

-- 반올림 해서 출력하기
SELECT round(weight, 0) as "무게(반올림)", weight
FROM product_name;

SELECT '400.64' as 무게_반올림, ROUND(400.64) -- TRUNC (400.64) 함수는 사용할 수 없음
FROM product_name;

-- 나눈 나머지 값 출력
SELECT weight, MOD(weight,10) as "무게를 10으로 나눈 값"
FROM product_name;

SELECT weight, MOD(weight, 3) as "3으로 나눈 나머지 값"
FROM product_name;

-- 날짜 간 개월 수 출력
SELECT customer_name, 
MONTHS_BETWEEN(sysdate, membership) -- MONTHS_BETWEEN 함수 지원하지 않음.
FROM customer_information;

-- 고객이 가입한 후 몇개월이 지났는지 확인.
SELECT customer_name, 
TIMESTAMPDIFF(MONTH, membership, NOW()) 
AS "가입이후 개월 수"
FROM Customer_Information;

-- 응용해본 것. 가입일 이후로 지금까지 며칠이 지났는지 표기

SELECT customer_name, DATEDIFF(NOW(), membership) 
AS 가입날짜로부터_지금까지_일수, membership
FROM Customer_Information;

-- 개월 수 더한 날짜 출력하기

SELECT DATE_ADD(DATE(membership), interval 5 MONTH) 
AS 가입날짜로부터_5개월, membership
FROM Customer_Information; 

SELECT DATE_ADD(DATE(membership), INTERVAL 100 DAY) 
AS 가입날짜로부터_100일, membership
FROM Customer_Information;

-- 특정 날짜 뒤에 오는 요일 날짜 출력하기

SELECT membership as 날짜, NEXT_DAY('membership', '일요일') -- NEXT_DAY 함수 지원 안함 
FROM Customer_Information;

SELECT membership AS 날짜,
  DATE_FORMAT(
	DATE_ADD(membership, INTERVAL ((7 - WEEKDAY(membership)) + 2) DAY), '%Y-%m-%d') 
    AS 다음_화요일
FROM Customer_Information;

-- 특정 날짜가 있는 달의 마지막 날짜 출력하기
SELECT membership as membership, LAST_DAY (membership) 
as "마지막 날짜"
FROM Customer_Information;

-- 형변환 (TO_CHAR, TO_DATE, TO_NUMBER)
-- 이름이 kim 인 사람의 이름과 가입한 요일을 출력(TO_CHAR).
SELECT customer_name, TO_CHAR(membership, 'DAY') as 요일 -- TO_CHAR 는 지원하지 않음
FROM Customer_Information WHERE customer_name = 'kim';

SELECT customer_name, DATE_FORMAT(membership, '%W') as 가입한_요일
FROM Customer_Information WHERE customer_name = 'kim';

SET lc_time_names = 'ko_KR'; -- 한국어로 요일을 표시하는 설정

-- 가입일이 2019-07-17 인 사람의 이름과 가입일을 출력(TO_DATE).
SELECT customer_name, membership from Customer_Information -- TO_DATE 지원 안함
WHERE membership = TO_DATE('2019-07-17','RRRR/MM/DD'); 

SELECT customer_name, membership from Customer_Information 
WHERE membership = DATE ('2019-07-17'); 

-- 문자형 '100.00' 을 숫자형 100.00 과 비교, 암시적 형변환 사용(TO_NUMBER).
SELECT category, price FROM product_name WHERE price = '100.00';

-- if문 구현 (DECODE)
SELECT product_number, material, -- DECODE 지원 안됨
  IF(material = 'gold', 50, IF(material = 'silver', 30, 0)) AS 포인트
FROM product_name;

-- if문 구현 (CASE)
SELECT product_number, material,
  CASE
    WHEN material = 'gold' THEN 50
    WHEN material = 'silver' THEN 30
    WHEN material = 'silk' THEN 20
    WHEN material = 'leather' THEN 10
    WHEN material = 'linen' THEN 5
    ELSE 0
  END AS 포인트
FROM product_name;

INSERT INTO product_name (product_number, category, material, weight, price, customer_information_id) VALUES ('11', 'ring', 'platinum', 0, 120.00, 1);
INSERT INTO product_name (product_number, category, material, weight, price, customer_information_id) VALUES ('12', 'ring', 'silver', 0, 50.00, 2);
INSERT INTO product_name (product_number, category, material, weight, price, customer_information_id) VALUES ('13', 'ring', 'platinum', 0, 78.50, 3);

INSERT INTO product_name (product_number, category, material, weight, price, customer_information_id) VALUES ('14', 'ring', "NULL" , NULL , NULL , 4);
INSERT INTO product_name (product_number, category, material, weight, price, customer_information_id) VALUES ('15', 'ring', NULL , NULL , NULL , 5);
INSERT INTO product_name (product_number, category, material, weight, price, customer_information_id) VALUES ('16', 'ring', NULL , NULL , NULL , 6);

-- NULL 값 출력하기
SELECT category, price, NVL(price, 0) FROM product_name; -- NVL 지원 안함

SELECT category, price, COALESCE(price, "없음" ) as 가격 FROM product_name;
SELECT category, price, IFNULL(price, "가격이없습니다." ) as 가격 FROM product_name;

-- 최대값 구하기, WHERE절을 걸어서 그룹핑 후 최대값 구하기
-- 가장 높은 가격 찾기
SELECT MAX(price) as "가장비싼 금액"
FROM product_name
WHERE price;

-- shoes 중에서 가장 높은 가격 찾기
SELECT category, MAX(price) as 가장비싼_신발
FROM product_name
WHERE category = 'shoes';

SELECT MIN(price) as "가장 싼 금액"
FROM product_name
WHERE price;

-- ring 중에서 가장 낮은 가격 찾기
SELECT category, MIN(price) as "가장 싼 반지"
FROM product_name
WHERE category = 'ring';

-- 평균값 출력하기(AVG)
SELECT AVG(price) as 평균가격 FROM product_name;

-- NULL값을 0으로 치환 한 뒤 평균값 출력
SELECT ROUND(AVG(IFNULL(price,0)))
FROM product_name;

-- 토탈 값 출력
SELECT category, SUM(price)
FROM product_name GROUP by category;

-- 카테고리 별 금액이 50 이상인 것만 토탈 값 출력
SELECT category, SUM(price)
FROM product_name GROUP by category HAVING sum(price) >= 50;

-- 카테고리 중, 링을 제외한 50 이상인 것만 토탈 값 출력
SELECT category, SUM(price)
FROM product_name WHERE category !='ring' GROUP by category HAVING sum(price) >= 50;

-- 건수 출력하기
SELECT count(category) FROM product_name;
SELECT count(*) FROM product_name;

-- price 에는 NULL 값이 3개가 포함되어 있음. 카운트는 NULL 값을 카운트 하지 않으므로 13개만 카운트 됨.
SELECT count(price) FROM product_name;