MySQL

/*
MySQL 계정관리
- MySQL 8.0 command line client실행(원격지에서서 서버접속 후 실행이 아닌 컴퓨터내에 존재하는 서버에 바로접속)
- 비번입력
- show databases;
- create database test;
- DataBase 사용자 생성
@'localhost' : 해당 컴퓨터에서만 접근이 가능
@'%' : 모든 클라이언트에서 접근이 가능
Create user '계정명'@'localhost' IDENTIFIED BY '비밀번호';
    
Create user '계정명' @ '%' IDENTIFIED BY '비밀번호';
- DataBase 사용자 권한 생성
grant all privileges on : 모든 데이터베이스의 모든테이블에 대한 권한 부여,(*.* : 모든 데이터베이스의 모든 테이블)
grant all privileges on *.* to '계정명'@'%';
grant all privileges on DB명.* to '계정명'@'%';
flush privileges; : 변경한 권한을 즉시 반영해주는 명령어

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

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

drop table product;

insert into product values('0000000001','iPhone14','예뻐요',1400000, now());
insert into product values('0000000002','Galaxy22','구려요',1000000, now());
insert into product values('0000000003','MacBookAir','무거워요',1400000, now());
insert into product values('0000000004','GalaxyBook','이런게있었나요?',900000, now());
insert into product values('0000000005','SamDaSoo','맛있어요',1000, now());
insert into product values('0000000007','zFlip','구려요',1000000, now());
insert into product values('0000000007','zFlip','구려요',1000000, now());

update product set pro_regdate='2022-10-17 11:16:46' where pro_regdate='2022-10-17 11:13:48';
select * from product;

create table product_new (
pro_inx varchar(10) not null,
    pro_name varchar(50) not null,
    pro_detail text,
    pro_price int,
    pro_regdate datetime default now()
);
select * from product;

insert into product_new values('0000000001','iPhone14','예뻐요',1400000, now());
insert into product_new values('0000000006','삼성모니터','구려요',1000000, now());
select * from product_new;

update product_new set pro_regdate = '2022-10-17 10:44:47';

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

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

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

# 서브쿼리의 실행 순서: 서브쿼리 실행 -> 메인 쿼리 실행

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

# 서브 쿼리의 위치에 따른 명칭
# select 컬렴명1, (select ..) : 컬럼처럼 사용 -> 스칼라 서브쿼리
# where 컬럼명 연산자 (select ..) : 하나의 변수(상수)처럼 사용 -> 일반 서브쿼리
# select 컬럼명1 from(select ..) : 하나의 테이블처럼 사용 -> 인라인 뷰

# 조건값이 결과가 하나일 때
select * from product where pro_name = (select pro_price from product where pro_inx ='0000000002');

# 조건값의 결과가 여러개일 때
# 조건값이 여러개 리턴되면 = any(in, or과 동일한 의미)
select * from product where pro_name = any (select pro_name from product where pro_price >= 1000000);

# all(and를 의미)은 도출된 모든 조건값에 대해 만족할 때 
select * from product where pro_price = all(select pro_price 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 in(select pro_name from product where pro_name='MacBookAir');
#갤럭시북보다 비싼 상품을 출력
select pro_name from product where pro_price > ( select pro_price from product where pro_name='GalaxyBook');
#가격이 100만원인 상품들을 출력
select pro_name from product where pro_price in ( select pro_price from product where pro_price=1000000);
#가격이 100만원인 상품들의 이름과 상세설명을 출력
select pro_name,pro_detail from product where pro_name = any (select pro_name from product where pro_price=1000000);
#로지텍카메라의 가격과 전체 가격의 평균을 출력
select pro_price, (select avg(pro_price) from product) 'avg' from product where pro_price in(select pro_price from product where pro_name='SamDaSoo');

# product와 동일한 테이블 product2를 만들고 product에 존재하는 데이터를 모두 복사하여 product2에 저장

create table product2(select * from product);
select * from product2;

#가격이 100만원인 상품들의 가격을 모두 10% 인상 (MySQL은 동일한 테이블에서 서브쿼리로 뽑은 데이터를 update, delete 할 수 없음)
select * from product2;
update product2 set pro_price = (pro_price * 1.1) where pro_name in (select pro_name from product where pro_price=1000000);

#가격이 100만원인 상품들의 모두 삭제
delete from product2 where pro_name in(select pro_name from (select * from product2)tmp where pro_price=1100000);


# 트랜잭션(Transaction)
# 사전적 의미: 거래 -> DB: 분할이 불가능한 업무처리 단위

/*
성공  commit
활성 -------> 부분 완료 ----> 완료
    (active)       중간(Aobrt)
-------> 실패 ----> 철회
오류     rollback
commit: 모든 작업들을 정상 처리하겠다고 확정하는 명령어로서, 해당 처리 과정을 DB에 영구저장
rollback: 작업 중 문제가 발생되어 트랜잭션의 처리 과정에서 발생한 변경사항을 모두 취소한 명령어
*/

# 자동 커밋 확인
show variables like '%commit%';
# autocommit:on 자동으로 commit 해줌
# set autocommit=0 (off), set autocommit=1 (on)
set autocommit=1;

start transaction; -- 트랜잭션 시작: commit 또는 rollback을 해야 끝남
insert into product values('0000000011','고철','팔아요',10,now());
commit; -- 트랜잭션을 DB에 적용
select * from product;

start transaction; -- 트랜잭션 시작: commit 또는 rollback을 해야 끝남
insert into product values('0000000012','병','팔아요',5,now());
select * from product;
rollback; -- 트랜잭션을 취소하고 start transcation 실행 전 상태로 롤백함
select * from product;

# 트랜잭션 예외
# DDL문(creat,drop,alter,rename,truncate)은 트랜잭션의 rollback 대상이 아님

# truncate
# 개별적으로 행을 삭제할 수 없으며, 테이블 내부의 모든 행을 삭제
# 트랜잭션 로그에 한 번만 기록하므로 delete보다 성능 면에서 더 빠름
# rollback이 불가능
# truncate table 테이블명;
truncate table product;
select * from product2;

set autocommit=1;
show variables like '%commit%';