Database CRUD

use cookDB;
create table testTBL1 (id int, userName char(3), age int);
insert into testTBL1 values(1,'뽀로로' , 16);
insert into testTBL1 (id, userName) values(2,'크롱');
insert into testTBL1 (userName, age, id) values('루피', 14,3);
SELECT * FROM testTBL1;

use cookDB;
/* auto_increment란 알아서 1씩 증가해준다. -> primary키라고 생각하면 됨. null 넣어도 알아서 1씩 증가시켜줌 */
create table testTBL2(
id int auto_increment primary key,  userName char(3), age int);
insert into testTBL2 values (null, '에디', 15), (null, '포비', 12), (null, '통통이', 11);
insert into testTBL2 values (null, '포비', 12);
insert into testTBL2 values (null, '통이', 13);
select * from testTBL2;

SELECT LAST_INSERT_ID(); -- 현재 테이블의 마지막 아이디를 알려줌

/* alter table이란 테이블 CRUD할 떄 사용하는 명령어 즉, 여기서는 testTBL2 테이블의 auto_increment 값을 100으로 바꾼다. */
alter table testTBL2 auto_increment=100; 
insert into testTBL2 values (null, '패티', 13);
select * from testTBL2;

/* testTBL3은 id가 auto_increment하게 primary key이고 username은 문자열 3, age는 숫자 -> autoIncrement 값을 1000으로 설정함.
set @@auto_increment_increment는 원래 auto_increment는 1씩 증가하는데 증가값을 설정해주는 명령어 현재 3씩 증가하게 되어있음. */
use cookDB;
create table testTBL3(
id int auto_increment primary key, userName char(3), age int);
alter table testTBL3 auto_increment=1000;
set @@auto_increment_increment=3;
insert into testTBL3 values (null, '우디', 20);
insert into testTBL3 values (null, '버즈', 18);
insert into testTBL3 values (null, '제시', 19);
select * from testTBL3;

insert into testTBL3 values (null, '토이', 17), (null, '스토리', 18), (null, '무비', 19); -- 한꺼번에 테이블에 값을 넣을 땐 이렇게 사용함.
select * from testTBL3;

/* testTBL4는 id가 숫자이고 fname이 문자열 50, lname이 문자열 50인 테이블인데, employees.employees에서 epm_no, first_name, last_name을 그대로 testTBL4에 저장 대량 데이터 넣을 때 */
use cookDB;
create table testTBL4 (id int, fname varchar(50), lname varchar(50));
insert into testtbl4 select emp_no, first_name, last_name from employees.employees; 
select * from testTBL4;

create table examTable3(select emp_no as "id", first_name as "Fname", last_name as "Lname" from employees.employees);
select * from examTable3;

/* create할 때 한꺼번에 select문을 이용하여 테이블 정의까지 생략할 수 있다. */
create table testTBL5 (select emp_no, first_name, last_name from employees.employees);
select * from testTBL5 limit 3;

/* 만약 다른 테이블 정보를 가지고 새로운 테이블을 만들 때, col name을 변경하고 싶으면 AS를 사용하면 된다. */
create table testTBL6 (select emp_no as id, first_name as Fname, last_name as Lname from employees.employees);
select * from testTBL6 limit 3;

use cookDB;
/* UPDATE문 
UPDATE 테이블 이름
SET 열1 = 값1, 열2 = 값2 ...
WHERE 조건; */

/* safe update mode로 인해 where 조건이 없으면 update랑 delete 막아; */
update testTBL4 set Lname='없음' where Fname = 'Kyoichi'; -- UPDATE와 SET은 필수지만 WHERE은 필수가 아니다. WHERE이 없다면 전체 열의 값이 변경되니 주의해야 한다. 
update buyTBL set price = price*1.5; 
select * from buyTBL;

use cookDB;
SELECT * FROM testTBL4 where Fname = "Aamer" and id = 11800;
delete from testTBL4 where Fname ='Aamer' and id = 11800; 
delete from testTBL4 where Fname ='Aamer' limit 5; 
select count(Fname) from testTBl4 where Fname = "Aamer";

use cookDB;
create table bigTBL1 (select * from employees.employees);
create table bigTBL2 (select * from employees.employees);
create table bigTBL3 (select * from employees.employees);

/* 테이블 삭제 명령어는 delete drop table truncate table */
delete from bigTBL1;
drop table bigTBL2;
truncate table bigTBL3;
/* 수행시간은 delete >>>>>>>> drop > truncate이다. delete는 모든 게 삭제되고, drop은 테이블 자체를 삭제, truncate는 테이블 내용만 삭제 테이블 구조는 유ㅈ지  */

use cookDB;
create table memberTBL (select userID, userName, addr from userTBL limit 3); 
alter table memberTBL add constraint pk_memberTBL primary key (userID); -- constraint는 제약 조건 추가, memberTBL의 userID 열을 primary key로 지정하는 조건 추가한 것
select * from memberTBL;

insert into memberTBL values ('KHD', '강후덜', '미국'); -- userID는 기본키인데 'KHD' 겹치는 데이터가 들어옴 -> 오류
insert into memberTBL values ('LSM', '이상민', '서울');
insert into memberTBL values ('KSJ', '김성주', '경기');

/* primary key는 고유한 값으로 중복이 있으면 안된다. ignore을 이용하면 error로 코드 실행을 막는게 아니고 자연스레 넘어ㄱ감   */
insert ignore into memberTBL values ('KHD', '강후덜', '미국'); 
insert ignore into memberTBL values ('LSM', '이상민', '서울');
insert ignore into memberTBL values ('KSJ', '김성주', '경기');
select * from memberTBL;

/* on duplicate key update가 있으면 만약 primary키가 중복되면 username을 딴  */
insert into memberTBL values ('KHD', '강후덜', '미국')  on duplicate key update userName ='강후덜', addr='미국'; 
insert into memberTBL values ('DJM', '동짜몽', '일본') on duplicate key update userName ='동짜몽', addr='일본';
select * from memberTBL;

insert into memberTBL values ('KHD', '강후덜', '미국')  on duplicate key update userName ='강후덜', addr='미국'; 
insert into memberTBL values ('DJM', '동짜몽', '일본') on duplicate key update userName ='동짜몽', addr='일본';
select * from memberTBL;

/* 순위 함수는 순번 또는 순위를 매기는 함수
<순위함수이름>() OVER(
	[PARTITION BY <partition_by_list>]
	ORDER BY <order_by_list>) */

USE cookDB;
select addr, row_number() over(PARTITION BY addr order by mDate ASC) as "지역별 가입일 빠른 순위", userName, mdate from userTBL;

select row_number() over (order by height DESC) "키큰 순위", userName, addr, height from userTBL;

select row_number() over (order by height DESC, userName) "키큰 순위", userName, addr, height from userTBL;
-- row_number():  결과에 순차적 정수를 할당
-- OVER (ORDER BY height DESC, userName ASC): 순위를 정하는 함수 괄호 안에 기준 정립
-- 바로 다음 파라미터에서 계산된 열의 이름을 지정 -> "키큰 순위"alter
-- userTBL에서 열 선택
-- select row_number() ~~ "키큰순위" 까지가 하나의 구절 이후에 select userName, select addr, select height가 붙은 것과 같은 의미

select addr, row_number() over(partition by addr order by height desc, userName asc) "지역별키큰순위" , userName, height from userTBL; 
/* addr, username, height 선택 + col_name : 지역별 키큰 순위 + partition by를 이용하여 먼저 지역을 구분하고 이후 orderby를 이용하여 정렬하기 height 내림차순 + 같으면 username 오름차순 */

select dense_rank() over (order by height DESC) "키큰 순위", userName, addr, height from userTBL;
-- row_number()방식이 아니라 dense_rank방식(같은 키는 같은 등수로) 
-- 이거 장점 order by 조건으로 동일선상에 있으면 index 순서대로 순위 매겨지는데 dense_rank를 사용하면 동일하게 순위가 적용된다.
select rank() over (order by height DESC) "키큰 순위", userName, addr, height from userTBL;
-- dense_rank는 같은게 오면 33 다음은 4로 적용되는데 rank는 33 다음 5로 적용

select ntile(2) over (order by height DESC) "반번호", userName, addr, height from userTBL;
select ntile(4) over (order by height DESC) "반번호", userName, addr, height from userTBL;
-- 2개, 4개의 그룹으로 분할

USE cookDB;
-- select username, addr, height as "키", height - LEAD(height, 1, 0) as "다음 사람과 키 차이" from userTBL ORDER BY height desc;
SELECT userName, addr, height AS "키", height - (LEAD(height, 1, 0) OVER(ORDER BY height DESC)) AS "다음 사람과 키 차이" FROM userTBL;
-- lead(가져올 열, 다음 행의 오프셋, 기본값) / (lead(height, 1,0): height열을 가져와 1번 뒤의 height와의 차이를 계산 만약 뒤의 행이 없으면 기본값(0)으로 지정

select addr, userName, height as "키", height - (first_value(height) over(partition by addr order by height desc)) 
as "지역별 최대키와 차이" from userTBL; 


select addr, userName, height as "키", (cume_dist() over (partition by addr order by height desc)) * 100 as "누적인원 백분율%" from userTBL;
-- cume_dist()는 주어진 그룹의 상대적인 누적분포도  0~1 사이의 값을 가짐

use cookDB;
create table pivotTest(uName char(3), season char(2), amount int);

insert into pivotTest values('유재석', '겨울', 10);
insert into pivotTest values('강호동', '여름', 15);
insert into pivotTest values('유재석', '가을', 25);
insert into pivotTest values('유재석', '봄', 3);
insert into pivotTest values('유재석', '봄', 37);
insert into pivotTest values('강호동', '겨울', 40);
insert into pivotTest values('유재석', '여름', 14);
insert into pivotTest values('유재석', '겨울', 22);
insert into pivotTest values('강호동', '여름', 64);

select uName, sum(case when season='봄' then amount end) as'봄',  sum(case when season='여름'  then amount end) as'여름',
 sum(case when season='가을' then amount end) as'가을',  sum(case when season='겨울' then amount end) as'겨울' from pivotTest group by uName;

select userid as '사용자', sum(price*amount) as '총 구매액' from buyTBL group by userid;

with abc(userid, total) as (select userid, sum(price*amount) from buyTBL group by userid)  select * from abc order by total DESC;  

-- select addr, max(height) from userTBL group by addr;
-- with cte_userTBL(addr, maxHeight) as (select addr, max(height) from userTBL group by addr)
-- select avg(키) from cte_테이블이름
with cte_userTBL (addr, maxHeight) as (select addr, max(height) from userTBL group by addr) select avg(maxHeight * 1.0) as '각 지역별 최고키의 평균' from cte_userTBL;