Datebase Study

/* 일반적인 SELECT 문 */
SELECT 열이름 
FROM 테이블이름
WHERE 조건;

/* 사용할 데이터베이스 불러오는 방법 */
USE 데이터베이스이름;

USE employees;

/* employees 데이터베이스에 있는 title 테이블에 있는 모든 열의 내을 가져와라 */
SELECT * FROM titles;

/* 원하는 열만 검색 여러 열 검색하고 싶으면 쉼표(,)로 구분 */
SELECT first_name FROM employees;

DROP DATABASE IF EXISTS cookDB; -- 만약 cookDB가 존재하면 우선삭제
CREATE DATABASE cookDB;


USE cookDB;
create table userTBL(
userID CHAR(8) NOT NULL PRIMARY KEY ,
userName varchar(10) NOT null,
birthYear int not null,
addr char(2) not null, 
mobile1 char(3),
mobile2 char(8),
height smallint,
mDate DATE );

create table buyTBL(
num int auto_increment not null primary key,
userID CHAR(8) NOT NULL,
prodName char(6) not null,
groupName char(4),
price int not null,
amount smallint not null,
foreign key(userID) references userTBL(userID));

insert into userTBL values('YJS' ,'유재석' ,1972 , '서울', '010', '11111111',178 ,'2008-08-08' );
insert into userTBL values('KHD' ,'강호동' ,1970 , '경북', '011','2222222' , 182,'2007-07-07');
insert into userTBL values('KKJ' ,'김국진' ,1965 , '서울', '019','33333333' , 171,'2009-09-09');
insert into userTBL values('KYM' ,'김용만' ,1967 , '서울', '010','44444444' , 177,'2015-5-5');
insert into userTBL values('KJD' ,'김제동' ,1974 , '경남', NULL , NULL , 173,'2013-3-3');
insert into userTBL values('NHS' ,'남희석' ,1971 , '충남', '016','2222222' , 180,'2007-07-07');
insert into userTBL values('SDY' ,'신동엽' ,1971 , '경기',  NULL , NULL , 176,'2007-07-07');
insert into userTBL values('LHJ' ,'이휘재' ,1972 , '경기', '011','88888888' , 180,'2006-04-04');
insert into userTBL values('LKK' ,'이경규' ,1960 , '경남', '018','99999999' , 170,'2004-12-12');
insert into userTBL values('PSH' ,'박수홍' ,1970 , '서울', '010','00000000' , 183,'2012-5-5');


insert INTO buyTBL values(null, 'KHD', '운동화' , null, 30,2);
insert INTO buyTBL values(null, 'KHD', '노트북' , '전자', 1000,1);
insert INTO buyTBL values(null, 'KYM', '모니터' , '전자', 200,1);
insert INTO buyTBL values(null, 'PSH', '모니터' , '전자', 200,5);
insert INTO buyTBL values(null, 'KHD', '청바지' , '의류', 50,3);
insert INTO buyTBL values(null, 'PSH', '메모리' , '전자', 80,10);
insert INTO buyTBL values(null, 'KJD', '책' ,'서적', 15, 5);
insert INTO buyTBL values(null, 'LHJ', '책' , '서적', 15,2);
insert INTO buyTBL values(null, 'LHJ', '청바지' , '의류', 50,1);
insert INTO buyTBL values(null, 'PSH', '운동화' , null, 30,2);
insert INTO buyTBL values(null, 'LHJ', '책' , '서적', 15,1);
insert INTO buyTBL values(null, 'PSH', '운동화' , null, 30,2);

/* 두 테이블 데이터 확인하기 */
SELECT * FROM userTBL;
SELECT * FROM buyTBL;

/* cookDB 데이터베이스 내에 userTBL 테이블에 열 이름이 userName이 강호동인 행 모두 선택 */
SELECT * FROM userTBL WHERE userName = "강호동";

/* userTBL에 1970년 이후에 출생했고, 키가 182cm 이상인 사람의 아이디와 이름을 조회 */
SELECT userID, userName FROM userTBL WHERE birthYear >= 1970 AND height >= 182;
/* userTBL에 1970년 이후에 출생했거나, 키가 182cm 이상인 사람의 아이디와 이름을 조회 */
SELECT userID, userName FROM userTBL WHERE birthYear >= 1970 OR height >= 182;

SELECT userID, userName FROM userTBL WHERE height >= 180 AND height <= 182;
/* 위에 퀴리문과 동일 */
SELECT userID, userName FROM userTBL WHERE height BETWEEN 180 AND 182;

SELECT userName, addr FROM userTBL WHERE addr = '경남' OR addr = '충남' OR addr = '경북';
/* 위에 쿼리문과 동일 */
SELECT userName, addr FROM userTBL WHERE addr IN('경남', '충남', '경북');

/* userName 중에 성이 '김'이고, 뒤에 무엇이든(%) 허용한다는 의미 -- 결과값 userName == 김제동, 김국진, 김용만 */
SELECT userName, height FROM userTBL WHERE userName LIKE '김%';

/* _경규 -> 맨 앞의 한 글자가 무엇이든 상관없고 그 다음 경규인 사람을 조회 */
SELECT userName, height FROM userTBL WHERE userName LIKE '_경규';
/* _경% 첫 번째 글자는 어떤 글자든 상관없고 두 번째 글자는 반드시 경, 세 번째 글자 이후는 몇 글자든 상관 없는 값을 추출 -> 이경규 / 구경하는 사람 / 존경하는 시민 */

SELECT userName, height FROM userTBL WHERE height > 177;
/* == */
SELECT userName, height FROM userTBL WHERE height > (SELECT height FROM userTBL WHERE userName = '김용만');
/* error 발생 이유 - user 테이블에 주소가 경기인 사람이 두명(176 / 180)이기 때문에 -> 서브 쿼리가 둘 이상 값을 반환하기 때문 */
SELECT userName, height FROM userTBL WHERE height > (SELECT height FROM userTBL WHERE addr = '경기');

/* 176 이상이거나 180 이상이거나 */
SELECT userName, height FROM userTBL WHERE height >= ANY (SELECT height FROM userTBL WHERE addr = '경기');
/* 176 이상 그리고 180 이상인 사람 */
SELECT userName, height FROM userTBL WHERE height >= ALL (SELECT height FROM userTBL WHERE addr = '경기');

/* ANY(SOME)는 서브 쿼리의 여러 결과 중 한 가지만 만족해도 출력, ALL은 서브 쿼리의 여러 결과를 모두 만족해야 출력 */
/* 176 또는 180인 사람 userName과 height 출력 */
SELECT userName, height FROM userTBL WHERE height = ANY (SELECT height FROM userTBL WHERE addr = '경기');
/* 동일한 쿼리 문 */
SELECT userName, height FROM userTBL WHERE height IN (SELECT height FROM userTBL WHERE addr = '경기');

/* 오름차순 ORDER BY, 뒤에 DESC를 넣으면 내림차순*/
SELECT * FROM userTBL ORDER BY mDate;
SELECT * FROM userTBL ORDER BY mDate DESC;

/* userTBL에 있는 userName과 height를 불러오는데, 정렬 순을 먼저 height를 내림차순으로 하고 만약 height가 동일하면 userName을 오름차순으로 정렬 */
SELECT userName, height FROM userTBL ORDER BY height DESC, userName; -- ORDER BY는 무조건 열 마지막에 와야한다.

SELECT addr FROM userTBL ORDER BY addr;
/* 중복 지역을 없애는 방법 DISTINCT 명령어를 사용 */
SELECT DISTINCT addr FROM userTBL ORDER BY addr;
/* DISTINCT는 앞에 와야한다 뒤에 오면 syntax error 발생 */
/* SELECT userName, DISTINCT addr FROM userTBL ORDER BY addr; */

USE employees;
/* ORDER BY 이후 상위 5개만 뽑고 싶으면 LIMIT 갯수를 이용하여 원하는 개수만 출력 */
SELECT emp_no, hire_date FROM employees ORDER BY hire_date LIMIT 5; -- LIMIT 초기인덱스, 갯수도 가능 (초기 인덱스 default 값은 0)

USE CookDB;
/* 테이블 복사할 떄 사용 */
CREATE TABLE buyTBL2 (SELECT * FROM buyTBL);
SELECT * FROM buyTBL2;

/* 지정한 일부 열만 복사 가능 */
CREATE TABLE buyTBL3 (SELECT userID, prodName FROM buyTBL);

/* GROUP BY 절은 말 그대로 그룹으로 묶는 역할 */
USE cookDB;
/* buyTBL 테이블에 있는 userID와 amount를 불러오는데, userID 순으로 오름차순 */
SELECT userID, amount FROM buyTBL ORDER BY userID;

/* buyTBL에 있는 userID와 amount의 총량을 불러오는데 amount의 총량은 userID 기준으로 */
SELECT userID, sum(amount) FROM buyTBL GROUP BY userID;
/* AS를 이용하여 열 이름을 변경할 수 있다. */
SELECT userID AS '사용자 아이디', sum(amount) AS '총 구매 개수' FROM buyTBL GROUP BY userID;
/* buyTBL에 있는 userID와 price * amount의 총량을 불러오는데 price * amount의 총량은 userID 기준으로 */
SELECT userID AS '사용자 아이디', sum(price * amount) AS '총 구매액' FROM buyTBL GROUP BY userID;

/* SUM() 총합, AVG() 평균, MIN() 최솟값, MAX() 최댓값, COUNT() 행의 개수, COUNT(DISTINCT) 중복 없이 행의 개수, STDEV() 표준편차, VAR_SAMP() 분산 */
USE cookDB;
SELECT AVG(amount) AS '평균 구매 개수' FROM buyTBL;

SELECT userID, AVG(amount) AS '평균 구매 개수' FROM buyTBL GROUP BY userID;

/* userTBL에 가장 큰 키와 가장 작은 키 찾는 방법 */
/* 1. group by를 사용하지않고 그냥 MAX와 MIN 집계함수를 이용하여 찾아보기 */
/* 실습 책에서는 error가 발생 안 하는데 내가 실습하니깐 error가 발생하네 */

/* 2. group by를 사용하여 가장 큰키와 작은 키를 찾아보기 */
/* 당연히 안 되는게, userTBL 테이블에 있는 userName과 MAX(height), MIN(height)를 출력해야하는데, 묶는 기준을 userName이야.. 
그럼 강호동을 기준으로 가장 큰 키와 가장 작은 키를 구분해야하는데,, height는 1대 다가 아니라 1대 1이므로 큰 값과 작은 값 나누는 의미가 1도 없지 */
SELECT userName, MAX(height), MIN(height) FROM userTBL GROUP BY userName;

/* 3. 서브쿼리와 group by를 이용하여 가장 큰 키와 작은 키를 찾아보기 */
SELECT userName, addr FROM userTBL WHERE addr IN('경남', '충남', '경북');
SELECT userName, height FROM userTBL WHERE height = (SELECT MAX(height) FROM userTBL) OR height = (SELECT MIN(height) FROM userTBL); 
/* 4. IN 연산자를 이용하여 OR을 없애려고 했지만 아직 안 배운 문법으로 인해 문제발생 */
SELECT userName, height FROM userTBL WHERE height IN(SELECT MAX(height), MIN(height) FROM userTBL);
/* chatgpt를 이용하여 문제해결.. union은 추후 배움 */
SELECT userName, height FROM userTBL WHERE height IN (SELECT MAX(height) FROM userTBL UNION SELECT MIN(height) FROM userTBL);

/* count() 함수를 이용하여 전체 회원 수 알아보기 */
SELECT count(*) as "전체 회원수" FROM userTBL;

/* 아이디별 총 구매액 */
use cookDB;
SELECT userID as "사용자", SUM(price * amount) as "총 구매액" FROM buyTBL GROUP BY userID;
/* 1000원 이상 구매자에게 사은품을 증정하고 싶을 때,, 조건문 where을 사용하는 게 일반적이지만, 집계함수에 대한 조건은 HAVING을 사용한다. */
SELECT userID as "사용자", SUM(price * amount) as "총 구매액" FROM buyTBL GROUP BY userID HAVING SUM(price * amount) > 1000;

/* ORDER BY절을 이용하여 정렬도 가능 */
SELECT userID as "사용자", SUM(price * amount) as "총 구매액" FROM buyTBL GROUP BY userID HAVING SUM(price * amount) > 1000 ORDER BY SUM(price * amount);
-- WITH ROLLUP; 이게 머노 넘어갑시다

/* SELECT select_expr
	[FROM table_references]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}] */