Day 4 복습

• union 절은 언제 쓸까?
• union 절의 조건은?
• 서브쿼리를 사용해야할 때는?
• 파생테이블을 CTE로 바꾸기 위한 문법 구조를 생각해보자.
• csv 파일을 MySQL 테이블로 변환하는 방법은?

1. 오늘 배운 내용

CRUD 즉, 생성, 조회, 수정, 삭제를 배웠다. 그리고 게임할 때 저장, 마지막 저장시점으로 돌아가기 기능과 같은 트랜젝션과 집계함수를 보다 쉽게 사용할 수 있는 윈도우함수를 마지막으로 배웠다.

2. 이해하기 어려웠던 부분

1) 트랜젝션

자동커밋이 활성화 되어 있으면 한 문장마다 commit이 자동으로 실행된다. 만약 비활성화 상태라면 commit을 하기 전까지 rollback으로 데이터를 복구할 수 있다.

a)START TRANSACTION, SAVEPOINT(중간저장 기점을 만든다)

자동커밋이 활성화 상태에서 start transaction;을 하면 잠시 비활성화가 된다. rollback이나 commit을 하기 전까지 한 모든 명령문을 묶어 그대로 저장(commit), 되돌리기(rollback)으로 처리할 수 있다. 중간 저장 기점도 만들 수 있다.(savepoint) 만약 중간 저장 기점인 a까지로 되돌리고 싶다면 ROLLBACK TO SAVEPOINT a'로 가능하다. 그대로 반영해도 문제 없다면 'COMMIT'으로 트랙젝션 종료하면 된다 .

select @@autocommit; #기본이 활성화 상태
set autocommit = 0; #비활성화
set autocommit = 1; #활성화

start transaction;
delete from emp_tran1
	where emp_no >= 1006;
update emp_tran1
	set salary = 0
    where salary is null;
    
rollback; -- 여기까지 다시 자동커밋 활성화 

-- savepoint 활용
start transaction;
savepoint a; 

delete from emp_tran1
	where salary is null;
    
savepoint b;    

delete from emp_tran1
	where emp_name = '브래드피트'
    order by emp_no
    limit 1;
 rollback to savepoint b; -- b전까지로 이동 

3. 기억해야할 부분

1) 구별하여,

A) 생성, 수정, 삭제 기본 형식

a) CREATE

• 생성시 PK 설정은 두 가지 방법이 있다.
• 생성된 컬럼에 테이터 입력시 insert문을 쓴다.

create table 테이블명(
	컬럼명1 컬럼타입 not null(null 값 허용하지 않는 경우) primary key -- option 1,
    컬럼명2 varchar(30) (기본이 null값 허용이다),
    컬럼명3 date,
    primary key(pk로 쓸 컬럼명) --option2
);

-- INSERT 문으로 데이터 입력
-- 단일문 입력
insert into 테이블명(컬럼명1,컬럼명2) -- 모든 컬럼에 맞춰 입력시 컬러명은 생략가능
			values(1001, '티모시살라메','2022-04-08', 2200);
-- 여러문 입력
insert into 테이블명 values
			(1007, '엠마 왓슨','2022-04-01', 2200),
            (1008, '레오나르도 디카프리오','2022-04-05', 3300),
            (1009, '호아킨 피닉스','2022-04-05', 4400);
-- 다른 테이블을 조건에 맞게 입력
insert into 테이블1 (컬럼1,컬럼2)
select 컬럼1,컬럼2 -- 다 넣을 거면 위 컬럼명은 생량 select절에는 *
from 테이블2
where emp_no in (1001, 1002); -- 조건

-- 변형하여 입력하기
insert into 테이블1 (컬럼1,컬럼2)
select emp_no + 10 ,컬럼2  -- 사번을 10씩 더해서 입력한다
from 테이블2
where emp_no > 1011; 

b) ALTER, UPDATE

• select문 없이 수정할 경우 MySQL에서 오류가 난다.
  따로 설정시 쓸 수는 있지만 데이터 보호를 위해 비추한다.
  • Edit → Preferences → SQL Editor → Safe Updates 체크 해제

-- PK 설정
alter table 테이블명
add constraint primary key (컬럼명);
-- FK 설정
alter table 테이블명
add foreign key(컬럼명1) references 부모 테이블명1(컬럼명3),
add foreign key(컬러명2) references 부모 테이블명2(컬럼명4);

-- 기존 데이터 수정
update 테이블1 a
	set a.컬럼1 = '값1'
    	a.컬럼2 = '값2'
	where 조건;
    
-- 기존 데이터 있으면 수정 없으면 새로 만듦
insert into 테이블1
	select *
	from 테이블2 a
	where 조건
	on duplicate key update 컬럼1 = a.컬럼2;
    
-- NULL 값 처리
update 테이블1 a, 테이블2 b
	set b.컬럼1 = ifnull(b.컬럼1, 0);

C) DELETE

-- 단일 테이블 삭제
drop table  테이블1;

-- 다중 테이블 삭제
delete a, b
	from(using) 테이블1 a, 테이블2 b
	where a.pk컬럼1 = b.pk컬럼2;

2) 잘 쓸 거 같아,

A) 윈도우 함수

--기본 형태
SELECT
	함수 over (PARTITION BY 묶을 컬럼1 ORDER BY 1) 별칭
    #묶을 필요 없다면 파티션없이 순서만 나타내도 된다)
    from 테이블1

Name	Description
ROW_NUMBER()	윈도우 안에서 순번 반환
CUME_DIST()	누적분포 반환
RANK()	윈도우 내에 순위 반환
DENSE_RANK()	누적 순위 반환(그냥 RANK와 차이는 공동순위 처리가 다르다) 공동순위를 같은 라인으로 인식한다. 예를 들어, 3위가 공동이라면 RANK는 1,2,3,3,5위로 반환하고 DENSE_RANK는 1,2,3,3,4로 반환한다.
PERCENT_RANK()	비율로 반환(상위 몇 %)
LAG(기준컬럼, 1(기본), 없을 경우 가져올 값(null기본))	현재 로우의 순위상 바로 위의(기본일 때) 로우 값 만약 1이 아닌 N값을 낸다면 N값 위의 로우 값을 반환한다.
LEAD(기준컬럼, 1(기본), 없을 경우 가져올 값(null기본))	현재 로우의 순위상 바로 아래의(기본일 때) 로우 값
FIRST_VALUE()	지정된 범위에서 첫 번째 로우의 값
LAST_VALUE()	지정된 범위에서 마지막 로우의 값
NTH_VALUE(컬럼명, n)	지정된 범위에서 N번째 로우의 값

B) 뷰(View)

a) 생성,사용,수정,삭제

• CTE와 파생테이블과 비슷하게 봤지만 차이라면 SELETE 절과 떨어져도 하나의 객체처럼 언제든 불러올 수 있다.

create (or replace) view 새 테이블명 as
	select * from 테이블1 where 조건;
  
-- 뷰 수정하기
alter view 뷰로 만든 테이블명 as
	select * from 테이블1 where 조건;
    
-- 뷰 삭제하기
drop view 뷰로 만든 테이블명;