MySQL generated columns 및 transaction

테이블 관리와 generated columns, 트랜잭션(transaction) 활용을 해보기 위해 새로운 테이블 만들어서 실습을 해봄.

-- 제품 테이블 (product) 생성
create table product(
num int auto_increment,
type_a varchar(50) not null,
type_b varchar(50) not null,
name varchar(100) not null,
price int default 0,
amount int default 100,
sale_amount int default 0,
register_date datetime,
primary key (num));

-- 제품 데이터 추가
insert into product(type_a, type_b, name, price, amount, sale_amount, register_date) values
('티셔츠','긴소매','폴라 티셔츠',15000,34,100,'2023-10-15'),
('티셔츠','긴소매','크롭 티셔츠',10000,50,100,'2023-10-15'),
('티셔츠','반소매','순면라운드 반팔티',15900,10,153,'2023-09-15'),
('티셔츠','민소매','에이 나시',9000,33,33,'2023-09-10'),
('패션운동복','트레이닝상의','피트니스상의',30000,34,55,'2023-12-05'),
('패션운동복','트레이닝하의','피트니스하의',50000,55,34,'2023-12-06'),
('패션운동복','트레이닝세트','피트니스상하의',90000,70,34,'2023-12-07'),
('아우터','재킷','양면 롱 후리스 자켓',23300,100,42,'2023-11-05'),
('아우터','코트','양털 겨울 코트',50000,50,30,'2023-10-31'),
('아우터','코트','트렌치 코트',40000,50,30,'2023-10-31'),
('아우터','패딩','롱 패팅 점퍼',47400,10,45,'2023-11-01');

-- 판매 테이블 생성 (buy)
create table buy(
num int auto_increment,
customer varchar(20),
product_name varchar(100),
price int default 0,
amount int default 0,
buy_date datetime default now(),
primary key(num));

--홍길동이 폴라티셔츠 5개를 구매
insert into buy(customer, product_name, price, amount)
values 
('홍길동', '폴라 티셔츠', 15000, 5);

--product 테이블에서 제품내용을 조회하여 추가
홍길순 크롭 티셔츠를 3장 구매 (num = 2)
insert into buy(customer, product_name, price, amount)
select '홍길순', name, price, 3 from product 
where num = 2;

--5명이 여러가지 상품으로 구매
insert into buy(customer, product_name, price, amount)
select '김기태', name, price, 1 from product
where num = 3;
insert into buy(customer, product_name, price, amount)
select '홍기원', name, price, 3 from product
where num = 4;
insert into buy(customer, product_name, price, amount)
select '하남일', name, price, 7 from product
where num = 5;
insert into buy(customer, product_name, price, amount)
select '송슬기', name, price, 3 from product
where num = 6;
insert into buy(customer, product_name, price, amount)
select '조아람', name, price, 2 from product
where num = 7;
insert into buy(customer, product_name, price, amount)
select '조미란', name, price, 4 from product
where num = 3;
insert into buy(customer, product_name, price, amount)
select '전한결', name, price, 2 from product
where num = 5;
insert into buy(customer, product_name, price, amount)
select '장우람', name, price, 1 from product
where num = 6;
insert into buy(customer, product_name, price, amount)
select '고담비', name, price, 5 from product
where num = 7;

-- 제품별 판매 수량 조회
select product_name, sum(amount) from buy group by product_name;

-- 제품별 판매 금액 조회
select product_name, sum(price) from buy group by product_name;

-- amount 뒤에 total 필드를 추가 (int)
alter table buy add total int after amount;

-- price * amount 곱한 값으로 나타내기
update buy set total = price * amount;

 

📢generated columns

컬럼을 생성시 계산된 값을 자동계산하여 생성
   stored : 값이 저장되는 방식
     => 데이터가 입력되거나 수정될때 해당 컬럼도 같이 갱신
   virtual : 데이터를 저장하지 않고 정의만
     => 해당 칼럼을 읽으려고 시도할 때 계산을 통해 보여주는 것만 

--방금전 생성한 total 필드를 삭제하고, 다시 generated columns으로 추가
alter table buy drop total;
alter table buy add total int generated always as 
(price * amount) stored after amount;

--데이터 추가했을 때 자동으로 total값 들어가는 것 확인
insert into buy(customer, product_name, price, amount)
select '고윤서', name, price, 3 from product
where num = 7;

--전체 총 판매수량과 총 매출액 출력
select sum(amount) as '총 판매수량', 
sum(total) as '총 매출액' 
from buy;


--product 테이블에서
--1. 최신 상품순으로 정렬
mysql> select * from product order by register_date desc;
+-----+------------+--------------+---------------------+-------+--------+-------------+---------------------+
| num | type_a     | type_b       | name                | price | amount | sale_amount | register_date       |
+-----+------------+--------------+---------------------+-------+--------+-------------+---------------------+
|   7 | 패션운동복 | 트레이닝세트 | 피트니스상하의      | 90000 |     70 |          34 | 2023-12-07 00:00:00 |
|   6 | 패션운동복 | 트레이닝하의 | 피트니스하의        | 50000 |     55 |          34 | 2023-12-06 00:00:00 |
|   5 | 패션운동복 | 트레이닝상의 | 피트니스상의        | 30000 |     34 |          55 | 2023-12-05 00:00:00 |
|   8 | 아우터     | 재킷         | 양면 롱 후리스 자켓 | 23300 |    100 |          42 | 2023-11-05 00:00:00 |
|  11 | 아우터     | 패딩         | 롱 패팅 점퍼        | 47400 |     10 |          45 | 2023-11-01 00:00:00 |
|   9 | 아우터     | 코트         | 양털 겨울 코트      | 50000 |     50 |          30 | 2023-10-31 00:00:00 |
|  10 | 아우터     | 코트         | 트렌치 코트         | 40000 |     50 |          30 | 2023-10-31 00:00:00 |
|   1 | 티셔츠     | 긴소매       | 폴라 티셔츠         | 15000 |     34 |         100 | 2023-10-15 00:00:00 |
|   2 | 티셔츠     | 긴소매       | 크롭 티셔츠         | 10000 |     50 |         100 | 2023-10-15 00:00:00 |
|   3 | 티셔츠     | 반소매       | 순면라운드 반팔티   | 15900 |     10 |         153 | 2023-09-15 00:00:00 |
|   4 | 티셔츠     | 민소매       | 에이 나시           |  9000 |     33 |          33 | 2023-09-10 00:00:00 |
+-----+------------+--------------+---------------------+-------+--------+-------------+---------------------+
11 rows in set (0.00 sec)

--2. type_a별로 상품의 개수와 price 합계
mysql> select type_a, count(*), sum(price) from product
    -> group by type_a;
+------------+----------+------------+
| type_a     | count(*) | sum(price) |
+------------+----------+------------+
| 티셔츠     |        4 |      49900 |
| 패션운동복 |        3 |     170000 |
| 아우터     |        4 |     160700 |
+------------+----------+------------+
3 rows in set (0.00 sec)

--3. price 20000이상인 제품은 할인상품에 해당, 할인상품을 출력
mysql> select * from product where price>=20000;
+-----+------------+--------------+---------------------+-------+--------+-------------+---------------------+
| num | type_a     | type_b       | name                | price | amount | sale_amount | register_date       |
+-----+------------+--------------+---------------------+-------+--------+-------------+---------------------+
|   5 | 패션운동복 | 트레이닝상의 | 피트니스상의        | 30000 |     34 |          55 | 2023-12-05 00:00:00 |
|   6 | 패션운동복 | 트레이닝하의 | 피트니스하의        | 50000 |     55 |          34 | 2023-12-06 00:00:00 |
|   7 | 패션운동복 | 트레이닝세트 | 피트니스상하의      | 90000 |     70 |          34 | 2023-12-07 00:00:00 |
|   8 | 아우터     | 재킷         | 양면 롱 후리스 자켓 | 23300 |    100 |          42 | 2023-11-05 00:00:00 |
|   9 | 아우터     | 코트         | 양털 겨울 코트      | 50000 |     50 |          30 | 2023-10-31 00:00:00 |
|  10 | 아우터     | 코트         | 트렌치 코트         | 40000 |     50 |          30 | 2023-10-31 00:00:00 |
|  11 | 아우터     | 패딩         | 롱 패팅 점퍼        | 47400 |     10 |          45 | 2023-11-01 00:00:00 |
+-----+------------+--------------+---------------------+-------+--------+-------------+---------------------+
7 rows in set (0.00 sec)

--4. 할인상품의 이름과 가격을 출력
mysql> select name as '할인상품명', price'정상가', round(price*0.9) as '할인가' from product
    -> where price>=20000;
+---------------------+--------+--------+
| 할인상품명          | 정상가 | 할인가 |
+---------------------+--------+--------+
| 피트니스상의        |  30000 |  27000 |
| 피트니스하의        |  50000 |  45000 |
| 피트니스상하의      |  90000 |  81000 |
| 양면 롱 후리스 자켓 |  23300 |  20970 |
| 양털 겨울 코트      |  50000 |  45000 |
| 트렌치 코트         |  40000 |  36000 |
| 롱 패팅 점퍼        |  47400 |  42660 |
+---------------------+--------+--------+
7 rows in set (0.00 sec)

--5. 11월에 입력된 상품만 출력
mysql> select * from product where month(register_date) = 11;
+-----+--------+--------+---------------------+-------+--------+-------------+---------------------+
| num | type_a | type_b | name                | price | amount | sale_amount | register_date       |
+-----+--------+--------+---------------------+-------+--------+-------------+---------------------+
|   8 | 아우터 | 재킷   | 양면 롱 후리스 자켓 | 23300 |    100 |          42 | 2023-11-05 00:00:00 |
|  11 | 아우터 | 패딩   | 롱 패팅 점퍼        | 47400 |     10 |          45 | 2023-11-01 00:00:00 |
+-----+--------+--------+---------------------+-------+--------+-------------+---------------------+
2 rows in set (0.00 sec)

--6. type_a별로 판매량 합계를 냈는데, 그 값이 가장 큰 값을 출력
mysql> select type_a, sum(sale_amount) s from product
    -> group by type_a
    -> order by s desc limit 1;
+--------+------+
| type_a | s    |
+--------+------+
| 티셔츠 |  386 |
+--------+------+
1 row in set (0.00 sec)

--서브쿼리 사용 (서브쿼리의 값을 이용할 경우 별칭은 필수)
select max(sum) from
(select type_a as t, sum(sale_amount) as sum
from product 
group by type_a ) p;

 

 

📢트랜잭션(transaction)

- 하나의 작업을 하기 위한 명령어 묶음 단위
- 하나의 트랜잭션에서 실행하는 모든 명령어가 완료되어야 전체가 완료되는 형태의 작업에서 사용
- 만약 하나라도 작업이 취소되면 모두 취소
- 완료와 취소를 나중에 결정하는 개념
- 임시 실행 개념.
- 데이터의 안전성을 확보하기 위한 방법

start transaction;
명령어1;
명령어2;
...
commit; => 완료
rollback; => 취소
🟢깃과 비슷하다.

 

- DDL (create, drop, alter, rename) => rollback 대상이 아님

- mysql 에서는 auto commit이 기본적으로 on 상태 

--트랜잭션을 이용하여
start transaction;

--1. 폴라 티셔츠 가격을 15000 = 17000 변경
update buy set price=17000 where num=1;

--2. 변경된 값을 확인
select * from buy;
commit;

--1. buy 테이블에서 customer의 이름을 홍O동 변경하여 출력(select)
select num, insert(customer, 2, 1, 'O') as 'name', product_name, price, amount, total, buy_date
from buy;

--2. product 테이블에서 price가 40000이상인 데이터만 조회
--   상품명(할인상품) 표시하여 출력 (전체데이터 표시)
select num, type_a, type_b,
if(price>=40000, concat(name,'(할인상품)'), name) as 'name',
price, amount, sale_amount, register_date from product;

--3. product 테이블에서 월별 매출(price * sale_amount) 합계 출력
select month(register_date) mon, sum(price*sale_amount) as sum from product
group by mon
order by mon;

--4. product 테이블에서 price가 가장 큰(가장비싼) 제품명, price 출력
select name, price from product order by price desc limit 1;

select name, price from product
where price = (select max(price) from product);


--5. buy 테이블의 구매내역을 3개 추가
--   => (product 테이블 조회하여 추가)
insert into buy(customer, product_name, price, amount)
select '이시연', name, price, 2 from product
where num = 1;
insert into buy(customer, product_name, price, amount)
select '채송화', name, price, 5 from product
where num = 4;
insert into buy(customer, product_name, price, amount)
select '선아영', name, price, 1 from product
where num=11;

--6. product_non 테이블을 product 테이블과 같은 형식으로 추가하여
--register_date의 9월 데이터를 이동
--   => 기존 product 테이블에서 제거
--   => transaction을 이용하여 첫 과정부터 완료되었을 때 commit 하기.

create table if not exists product_non like product;

start transaction;
insert into product_non
select * from product where month(register_date) = 9;
delete from product where month(register_date)=9;
commit;

--7. buy테이블에서 가장 많이 판매한 제품의 이름을 출력
select product_name, sum(amount) as sum from buy
group by product_name
order by sum desc
limit 1;

select product_name from buy
group by product_name
having sum(amount) = (
select max(total_sum) from
(select sum(amount) as total_sum from buy
group by product_name) as p);