MYSQL -3

# 문제.
# banana 계정을 만들어 test 데이터베이스에만 접근이 가능한 계정으로 권한 생성하기
# create user 'banana'@'%' identified by '2222';
# grant all privileges on test.*to 'banana'@'%';

create table product (
pro_idx varchar(10) not null,
pro_name varchar(50) not null,
pro_detail text,
pro_price int,
    pro_regate datetime default now()
    );

drop table product;

insert into product values ( '0000000001', '아이폰14', '예뻐요', 1400000, now());
insert into product values ( '0000000002', '갤럭시22', '좋아요', 1400000, now());
insert into product values ( '0000000003', '맥북에어', '가벼워요', 1400000, now());
insert into product values ( '0000000004', '갤럭시북', '싸요', 1400000, now());
insert into product values ( '0000000005', '로지텍웹카메라', '잘보여요', 1400000, now());
insert into product values ( '0000000006', 'Z플립', '잘접혀요', 1000000, now());
insert into product values ( '0000000006', '아이폰14', '예뻐요', 1000000, now());

create table product_new (
pro_idx varchar(10) not null,
pro_name varchar(50) not null,
pro_detail text,
pro_price int,
pro_regate datetime default now()
);

insert into product_new values ('0000000001', '아이폰14', '예뻐요', 1400000, now());
insert into product_new values ('0000000002', '아이폰15', '좋아요', 1400000, now());
select * from product;

update product_now set pro_regdate = '2022-10-17 10:43:00';

# union
# 합집합을 나타내는 연산자로, 중복된 값을 제거함
# 서로 같은 종류의 테이블(컬럼이 같아야 함)에서만 적용이 됨
# select * from 테이블1 union select * from 테이블 2
select * from product
union
select * from product_new;

# union all
# 합집합을 나타내는 연산자로, 중복된 값을 제거하지 않음
select * from product
union
select * from product_new;

# 서브쿼리(Subquery)
# 다른 쿼리 내부에 포함되어 있는 select 문을 의미
# 서브쿼리를 포함하고 있는 쿼리를 외부쿼리라고 부르며, 서브쿼리는 내부쿼리라고도 부름
# 서브쿼리는 괄호()로 감싸져서 포현
# 서브쿼리는 메인쿼리 컬럼 사용 가능
# 메인쿼리는 서브쿼리 컬럼 사용 불가
    
select pro_price from product where pro_idx = '0000000002';

select * from product where pro_price >= (select pro_price from product where pro_idx = '0000000002');


    #서브쿼리의 위치에 따른 명칭
    # select 컬럼명1, 컬럼명2 .. 테이블명 where 컬럼명 연산자 (select 컬럼명1 ...);
# 인라인뷰(테이블 대체 용도)
    
    # 서브쿼리의 위치에 따른 명칭
    # select 컬럼명1, (select ..) : 컬럼처럼 사용 -> 스칼라 서브쿼리
    # where 컬럼명 연산자 (select ..) : 하나의 변수(상수)처럼 사용 -> 일반 서브쿼리
    # select 컬럼명1 from (select ..) : 하나의 테이블처럼 사용 -> 인라인 뷰
    
    # 조건값이 결과가 하나일 때
    select * from product where pro_name = (select pro_name from product where pro_idx = '0000000002');
    
    # 조건값의 결과가 여러개일 때
    # 조건에 값이 여러개 리턴되면 any(in, or과 동일한 의미)
    select * from product where pro_name = any (select pro_name from product where pro_price in(1000000, 1400000));
    
    # all(and를 의미)은 도출된 모든 조건값에 대해 만족할 때
    select * from product where pro_name > all (select pro_name from product where pro_price in(1000000));
    
    # 서브쿼리 실행 조건
    # 서브쿼리는 select 문으로만 작성할 수 있음
    # 반드시 괄호() 안에 존재
    # 서브쿼리 괄호가 끝나고 끝에 세미콜론을 쓰지 않음
    # order by를 사용하지 못함
# MySQL에서는 서브쿼리를 포함할 수 있는 외부쿼리는 SELECT, INSERT, UPDATE, DELETE, SET, DO문
    
    select * from product;
    # 문제.
    
    # 서브쿼리를 사용하여 맥북에어의 상세설명을 출력
    select pro_detail from product where pro_name = ( select pro_name from product where pro_idx = '0000000003');
    # 갤럭시북보다 비싼 상품을 출력
    select * from product where pro_price > (select pro_price from product where pro_idx = '0000000004');
    # 가격이 100만원인 상품들을 출력
    select * from product where pro_price = any (select pro_price from product where pro_price = 1000000);
    # 가격이 100만원인 상품들의 이름과 상세설명을 출력
    select pro_name, pro_detail from product where pro_price = any ( select pro_price from product where pro_price = 1000000);
    # 로지텍카메라의 가격과 전체 가격의 평균을 출력
    select pro_name, pro_price, (select avg(pro_price) from product) as 'avg' from product where pro_idx ='0000000005';
    
    # product와 동일한 테이블 product2를 만들고 product에 존재하는 데이터를 모두 복사하여 product2에 저장
    create table product2 (select * from product);    
    insert into product2 (select * from product);
    select * from product2;
    # 가격이 100만원인 상품들의 가격을 모두 10% 인상 ( MYSQL은 동일한 테이블에서 서브쿼리로 뽑은 데이터를 update, delete 할 수 없음)
    update product set pro_price = pro_price * 1.1 where pro_price = (select pro_price from product where pro_price = 1000000)as p;
    
    # 가격이 100만원인 상품들을 모두 삭제
    delete from product where pro_idx = any (select idx from (select pro_idx as idx from product where pro_price = 1100000));
    
    # 트랜잭션(Transaction)
    # 사전적 의미: 거래 -> DB: 분할이 불가능한 업무처리 단위
    
    /*
성공 commit
    활성 ----> 부분 완료 ----> 완료
중단(Abort)
    (active) ----> 실패 ----> 철회
오류 rollback
    
    commit: 모든 작업들을 정상 처리하겠다고 확정하는 명령어로서, 해당 처리 과정을 DB에 영구 저장
    rollback: 작업 중 문제가 발생되어 트랜잭션의 처리 과정에서 발생한 변경사항을 모두 취소하는 명령어
    */
    
    #자동 커밋 확인
    show variables like '%commit%';
# autocommit: on 자동으로 commit 해줌
    # set autocommit=0 (off), set autocommit=1 (on)
    set autocommit =0;
    
    start transaction; -- 트랜잭션 시작: commit 또는 rollback을 해야 끝남
    insert into product values ('0000000011', '고철', '팔아요', 10, now());
    select * from product;
    
    commit; -- 트랜잭션을 DB에 적용
    
    select * from product;
    
    start transaction;
    insert into product values ('0000000012', '병', '팔아요', 5, now());
    select * from product;
    rollback; -- 트랜잭션을 취소하고 start transaction 실행 전 상태로 롤백함
    select * from product;
    
    # 트랜잭션 예외
    # DDL문(create, drop, alter, rename, truncate)은 트랜잭션의 rollback 대상이 아님
    
    # truncate
    # 개별적으로 행을 삭제할 수 없으며, 테이블 내부의 모든 행을 삭제
    # 트랜잭션 로그에 한 번만 기록하므로 delete보다 성능 면에서 더 빠름
    # rollback이 불가능
    # truncate table 테이블명;
    truncate table product2;
    
    select * from product2;
    set autocommit=1;
    show variables like '%commit%';