주식 데이터로 sql 연습 (section 3)

#DDL 
create table sector(
	종목코드 varchar(8) not null,
    섹터 varchar(30),
    constraint sector_pk primary key (종목코드)
);

insert into sector(종목코드,섹터) values ("000210","기타 금융업");
insert into sector values ("163560","고무제품 제조업");
insert into sector values ("155660","1차 비철금속 제조업");
update sector set 섹터 = "제조업" where  종목코드 = "163560";
delete from sector where 종목코드 = "163560";

select * from sector;

create table sector_backup(
	종목코드 varchar(8) not null,
    섹터 varchar(30),
    constraint sector_pk primary key (종목코드)
);

#merge 테이블에 새로운데이터를 입력하거나 이미 저장되있는 데이터에 대한 변경 작업을 한번에 하는 명령어 
#mysql에서는 불가 
merge 
into sector_backup sb
using sector s
on(sb.종목코드=s.종목코드)
when match then
	update 
		set sb.종목코드 = s.종목코드 , sb.섹터 = s.섹터
when not match then 
	insert (sb.종목코드,sb.섹터) values (s.종목코드,s.섹터);
    
    
#merge처럼 이미 있는 데이터면 update를 하고 없으면 insert한다 
insert into sector(종목코드,섹터) 
values ("000080","소주")
on duplicate key update 
sector.섹터 = "교체";

select * from sector

#TCL (commit, rollback, savepoint)
#특징 - 원자성, 일관성, 고립성, 지속성 
#트랙젝션 - 쪼개질수 없는 작업 단위 

#commit insert, delete, update 후 변경된 내용을 확정,반영하는 명령어 
#commit전에는 휘발성인 메모리에 저장되었다 commit을 해야 데이터 파일에 기록되고 트렌젝션이 완료된다.
commit;

#rollback 트렌젝션 전으로 전체 작업을 되돌리거나 savepoint를 지정하면 그지점 까지 데이터복구 
set autocommit =0 ; # mysql에서 롤백을 하려면 오토커밋을 false해줘여한다 
insert into sector values ("000280","의약품 제조업");
savepoint A;
update sector set 섹터 = "제조업" where 종목코드 = "000230";
savepoint B;
rollback to A;
rollback;
commit;

# 테이블 복사 
create table price_backup as select * from price;
#add column 
alter table price_backup add test varchar(8);
#drop column
alter table price_backup drop column test;
#modify column 기존 컬럼의 유형, 디폴트 값, not null,제약조건 변경 
alter table price_backup modify open1 varchar(100) default 0; # mysql은 괄호 없음 
alter table price_backup modify (open1 varchar(100) default 0);
#rename column
alter table price_backup rename column open1 to open2;

#참조 제약조건도 함께 삭제해서 이 테이블을 참조하는 테이블이 있어도 삭제한다는 뜻 
drop table price_backup cascade constraint;  #mysql에서는 x 

ALTER TABLE price_backup DROP FOREIGN KEY `fk_emp_dept`; # mysql에서는 외래키를 삭제하고 테이블을 삭제 해야한다 
DROP TABLE price_backup  ;
#테이블안 데이터 모두 삭제 롤백이 불가능 delete와 비슷하지만 rollback이 불가능하단 이유로 ddl에 포함 
truncate table price_backup;

#DCL 
#유저생성
create user test1@localhost identified by "user1234";
#비밀번호 변경
alter user test1 identified by "123";
#유저 삭제 
drop user test1;
#유저 확인 
use mysql;
select * from user;

#권한 부여 grante 권한 회수 revoke 
#mysql에서 안됨 
grant create session to test1;
grant create user to test1;
grant create table to test1;

grant revoke session to test1;
grant revoke user to test1;
grant revoke table to test1;

use stock;
show tables;
#@localhost는 내부접근  @"%" 외부 접근 허용 
create user test2@localhost identified by "user1234";
create user "test3"@"%" identified by "1234";

#mysql에서는 user에 바로 권한을 주는게 힘들어진거 같다 그래서 롤을 만들어 권한 부여한다 

#roll 생성 
CREATE ROLE 'app_read', 'app_write';
#roll에 권한 부여 
GRANT SELECT ON stock.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON stock.* TO 'app_write';
#test1 유저에 roll 뷰여 
GRANT 'app_read' TO test1@localhost;
GRANT 'app_read','app_write' to 'test2'@'localhost';

#권한보기 
SHOW GRANTS FOR 'app_read'@'%';
SHOW GRANTS FOR 'test2'@'localhost'; 
SHOW GRANTS FOR 'test2'@'localhost' USING 'app_read'; 

select current_role();
SET DEFAULT ROLE ALL TO
'test2'@'localhost',
'test1'@'localhost';