[DB] 기초공부 2

2025.02.13 데이터베이스 기초공부2 Start.

-- 권한 부여
grant all privileges on shop.* to 'mysqluesr'@'localhost' with grant option;

-- 권한 적용
flush privileges;

-- mysqluser 유저 변경
mysql -umysqluser -pmysql

---

새로운 Table 생성

Product table

use shop
- 제품테이블(product)
- 구매테이블(buy)

select * from product;

product table
-- 번호
- num int auto_increment pk

-- type_a
- type_a varchar(50) not null

-- type_b
- 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


create table product (
    -- 번호
    num int auto_increment,
    -- type_a
    type_a varchar(50) not null,
    -- type_b
    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)
);

 

Buy table

buy table

select * from buy;


-- 번호
- num int ai pk

-- 고객
- customer varchar(20)

-- 제품명
product_name varchar(100)

-- 가격
price int default 0

-- 사가는 개수
amount int default 0

-- 사간 날짜(default는 현재시간)
buy_date datetime default now()

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)
)

---

데이터 삽입

 

product table

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

 

buy table

이번에는 값을 다 명시해서 하는것이 아니라 product table에서 값을 검색해서 갖고오는 형식으로 진행했다.

insert into buy(customer, product_name, price, amount)
(select '홍길동', name, price, 5 from product where num = 1)

insert into buy(customer, product_name, price, amount)
(select '이순신', name, price, 2 from product where num = 4);

insert into buy(customer, product_name, price, amount)
(select '을지문덕', name, price, 7 from product where num = 6);

insert into buy(customer, product_name, price, amount)
(select '이성계', name, price, 3 from product where num = 7);

insert into buy(customer, product_name, price, amount)
(select '주몽', name, price, 4 from product where num = 10);

insert into buy(customer, product_name, price, amount)
(select '대조영', name, price, 1 from product where num = 8);

insert into buy(customer, product_name, price, amount)
(select '선덕여왕', name, price, 2 from product where num = 9);

insert into buy(customer, product_name, price, amount)
(select '박혁거세', name, price, 10 from product where num = 3);

insert into buy(customer, product_name, price, amount)
(select '김구', name, price, 10 from product where num = 2);

---

부가기능

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

-- 제품명 별 판매금액 조회
select product_name, sum(price) from buy group by product_name;
select product_name as 제품명, sum(amount * price) as 합계금액 from buy group by product_name;

-- total 필드를 buy 테이블에 추가
alter table buy add total int after amount;

-- total 값 채우기
	update buy set total = amount * price;
	
-- total 필드 삭제
alter table buy drop total;

-- 칼럼 생성시 계산된 값을 자동계싼하여 생성하고 싶을 경우
-- ( generated columns ), columns는 생략 가능
	-- stored : 값이 저장되는 방식
	-- (데이터가 입력되거나, 수정될 때, 해당 칼럼도 같이 갱신)
	
	-- virtual : 데이터를 저장하지 않고 정의만
	-- (해당 칼럼을 읽으려고 시도할 때 계산을 통해 보여주는 것만)
alter table buy add total int generated always as (price * amount) stored after amount;

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

-- 오늘 판매수량 / 판매합계 조회
select sum(amount) as 판매수량, sum(price) as 매출 from buy;

---

1. 최신 상품순으로 정렬 - product 테이블
select * from product order by register_date desc;

2. type_a 별로 카테고리 수, 가격 합계
select type_a ,count(type_a), sum(price * amount) from product group by type_a ;

3. price가 16000이상인 제품을 할인상품에 해당함. 해당 상품 목록 출력
select name as 할인상품 from product where price >=16000;

4. 할인 상품의 이름과 할인가격을 출력
		할인가격(16000원 이상인 제품만 대상으로 10% 할인된 가격)
		할인상품명, 정상가, 할인가
		select name as 할인상품, price as 정상가, price*0.9 as 할인가 from product where price >=16000;

		
5. 11월에 입고된 상품만 출력 - 내장함수 사용
select name as 상품명, register_date as 입고된날짜 from product where month(register_date)=11;

6. 월별 price 합계
select month(register_date) as 월별, sum(price) as 월별매출 from product group by 월별 order by 월별 desc;

select date_format(register_date, %y-%m') as mon, sum(price) as sum from product group by mon order by mon;

---

트랜잭션(transaction)
- 하나의 작업을 하기위한 명령어 묶음 단위
- 연관있는 작업들의 모임.
- 하나라도 명령어가 완료되지 못한다면(error) 취소
- 모든 명령어가 완료되어야 진행
- 완료와 취소를 나중에 결정하는 개념
- 데이터의 안전성 확보

start transaction;
명령어1;
명령어2;
...
rollback; => 위에 작업한 명령어 모두 취소
commit; => 작업완료
DDL / DCL 포함 안 됨. 오직 DML만 가능.

start transaction;
1. insert into product(type_a, type_b, name, price, amount, sale_amount, register_date)
('티셔츠', '긴소매', '라운드 티셔츠', 15000, 34, 100, '2025-11-15');
2. 해당 제품 구매
3. 구매테이블 확인

start transaction;
1. insert into product(type_a, type_b, name, price, amount, sale_amount, register_date)
values ('티셔츠', '긴소매', '라운드 티셔츠', 15000, 34, 100, '2025-11-15');
insert into buy(customer, product_name, price, amount)
(select '도우너', name, price, 3 from product where num = 12);
select * from buy;
commit;

DB에서 변수의 값을 저장할 경우
- 사용자 변수(전역변수), 지역변수
- 사용자 변수 Session 영역
- 한 클라이언트에서 정의한 사용자 변수는 다른 클라이언트가 보거나 사용할 수 없음.
SET @cnt := 값; (:=, = 모두 사용이 가능하지만 :=는 변수로 구분하기 쉬움)
select @cnt;

ex)
SET @cnt := (select max(num) from product);
insert into buy (customer, product_name, price, amount)
select '도우너', name, price, 5 form product where num = @cnt);

---

1. buy 테이블에서 customer 이름을 홍O동 변경하여 출력 => select
select num, CONCAT(LEFT(customer, 1), 'O', RIGHT(customer, 1)) as customer, product_name, price, amount, total, buy_date FROM buy

2. product 테이블에서 price가 40000이상인 데이터만 상품명(할인상품)으로 표시 => select
select num, type_a, type_b,
       case when price >= 40000 then concat(name, '(할인상품)')
       else name
       end as name, price, amount, sale_amount, register_date from product;

3. 월별 매출(price * sale_amount) 합계 출력
select month(register_date) as 월별, sum(price * sale_amount) as 월별매출 from product group by 월별 order by 월별 desc;

4. price가 가장 높은(가장 비싼상품) 출력
select * from product order by price asc limit 9,10;

5. transaction을 사용하여 9월에 입고된 재고 상품을 product_non로 이동 => 미리
   product_non 테이블은 product와 같은 형식으로 생성
   기존 product 테이블에서는 삭제
   완료되면 commit;  
  -- 트랜잭션 시작
start transaction;

-- product와 같은 형식으로 product_non 테이블 생성
create table if not exists product_non like product;

-- 9월에 입고된 재고 상품을 product_non으로 이동
insert into product_non (select * from product where month(product.register_date) = 9);

-- 기존 product 테이블에서는 삭제
delete from product where month(register_date) = 9;

-- 완료되면 commit
commit;
   
6. 가장 많이 판매한 제품의 이름을 출력 => buy 테이블 기준
select product_name, sum(amount) from buy group by product_name order by sum(amount) desc limit 1;