SQL 서브쿼리/사용자 관리/Transaction

1. 서브 쿼리(subquery)

서브 쿼리(subquery)는 다른 SQL 쿼리 안에 포함된 내장 쿼리입니다. 서브 쿼리를 포함하고 있는 쿼리를 외장 쿼리, 서브 쿼리를 내장 쿼리라고 부르고 괄호()를 사용해서 표현합니다. 서브 쿼리는 주로 SELECT 문, WHERE 절, HAVING 절, FROM 절 등에서 사용됩니다. 이를 통해 데이터베이스에서 더 정확한 데이터 추출 및 조작이 가능하며, 복잡한 질의를 수행할 때 매우 유용합니다.

select price from product where code = '00001';

-- where 절 (0001번 제품보다 높은 가격인 제품 보여주기)
select * from product where price >= (select price from product where code = '00001');

-- select 절 (가장 높은 가격 같이 보여주기)
select code, name, price, (select max(price) from product) as max_price from product;

***auto_increment

필드의 identitiy한 숫자를 자동으로 부여
MySQL에서만 auto_increment를 사용하면 primary key를 꼭 넣어줘야한다.

-- 상품을 최소 2번이상 구입한 횟수가 있는 회원의 아이디와 이름, 성별을 출력
select userid, name, gender 
  from member 
 where  userid in (  select userid 
                       from orders 
                      group by userid 
                     having count(cnt) >= 2);

-- 조인을 사용, 상품을 최소 2번 이상 구입한 아이디의 앞 두글자와,이름,상품 구입 횟수를 출력
select left(m.userid,2),name, count(o.no) as cnt 
from member m 
inner join orders o 
        on m.userid = o.userid
     group by m.userid 
    having cnt >= 2;

*** Table 생성시 다른 테이블 정보 모두 복사해오기

-- 테이블 생성과 동시에 데이터도 복사넣기.
create table orders_new_new(select * from orders);
select * from orders_new_new;
-- 이미 만들어져 있는 테이블에 데이터 복사 넣기
select * from orders_new;
insert into orders_new(select * from orders);

2. 사용자 관리

1. 사용자 추가하기
- MySQL 8.0 Command Line Client에서 root로 로그인
- 접속 가능한 사용자 추가하기
create user '사용자명'@'localhost' identified by '비밀번호';

create user 'apple'@'localhost' identified by '1111';  

2. 사용자 목록 조회

use mysql;   # mysql이라는 데이터베이스에 사용자가 저장되어있음.  
select user, host from user; 

*** 할당 권한 상세 옵션
- create, drop, alter: 테이블에 대한 생성, 삭제, 변경 권한
- select, insert, update, delete: 테이블의 데이터를 조회,삽입,변경,삭제에 대한 권한
- all: 모든 권한
- usage: 권한을 부여하지 않고 계정만 생성

grant select on 데이터베이스명.테이블명 to '사용자'@'localhost';   # 사용자에게 select 권한 부여  
grant all on \*.\* to '사용자'@'localhost';    # 사용자에게 모든 권한 부여

flush privileges; # 새로고침 역할/ 내용이 바로 적용된다.

grant select on 데이터베이스명.테이블명 to '사용자'@'%'; -- 모든 IP에서 접근이 가능  
grant select on 데이터베이스명.테이블명 to '사용자'@'특정 IP주소'; -- 특정 IP에서 접근이 가능

2.사용자 삭제하기

drop user 계정명;

3.사용자 권한 제거하기

revoke 권한명 privileges on 데이터베이스명.테이블명 from '계정명'@'localhost';

4.사용자 권한 조회하기

show grants for '계정명'@'localhost';

-- apple 데이터베이스 생성
create database apple;
 -- apple 데이터베이스에 kdt.member 테이블을 복사
create table apple.member(select * from kdt.member);
use apple;
-- apple.member에 select 권한만 가능한 apple계정 생성
grant select on apple.member to 'apple'@'localhost';
-- apple 데이터베이스에 모든 권한을 가진 사용자 'orange'를 생성
create user 'orange'@'localhost' identified by '2222';
grant all on apple.* to 'orange'@'localhost';
show grants for 'orange'@'localhost';
-- orange의 모든 권한을 제거
revoke all privileges on apple.* from 'orange'@'localhost';
-- orange 사용자를 삭제
drop user 'orange'@'localhost';

3. Transaction(트랜젝션)

트랜젝션은 분할이 불가능한 업무처리의 단위입니다. 한꺼번에 수행해되어야 할 연산 모음으로 볼 수 있습니다.
• commit : 모든 작업들을 정상 처리하겠다고 확정하는 명령어. 해당 처리 과정을 DB에 영구적으로 저장
• rollback : 작업 중 문제가 발생되어 트랜젝션의 처리 과정에서 발생한 변경사항을 모두 취소하는 명령어

> start transaction
블록안의 명형어들은 하나의 명령어처럼 처리됨
...
성공하던지, 실패하던지 둘 중 하나의 결과가 발생
> 문제가 발생하면 rollback;
> 정상적인 처리가 완료되면 commit;

• 자동 커밋 확인

show variables like '%commit%';

• autocommit: ON 상태 -> 자동으로 commit 해주는 상태

set autocommit = 0 (off)
set autocommit = 1 (on)  

set autocommit = 0;
select * from word;
start transaction;
insert into word values ('car', '자동차', 1);
select * from word;
rollback;
-- insert해준 내용이 모두 취소된다.

insert into word values ('car', '자동차', 1);
commit;
-- insert 내용이 확정됨.

*** 트랜젝션의 예외

DDL문(create, drop, alter, rename, truncate)에 대해 예외 적용 : rollback이 안됨.
• truncate
: 개별적으로 행을 삭제할 수 없으며, 테이블 내부의 모든 데이터를 삭제합니다.
DDL문이기 때문에 rollback이 불가능합니다.
트렌젝션 로그에 한번만 기록하므로 delete 구문보다 성능 면에서 빠릅니다.

use kdt;
select * from product_new;
start transaction;
delete from product_new;
rollback;
-- 데이터 복구 됨.
truncate table product_new;
rollback;
-- 데이터 복구 안됨. truncate는 rollback 불가능

-- auto commit 켜기
set autocommit = 1;
show variables like '%commit%';
-- autocommit: ON