MySQL 기초 복습하기

• 테이블  생성하기

-- 데이터베이스 생성
CREATE DATABASE shop;
USE shop;

-- 회원 테이블 생성 (PRIMARY KEY, UNIQUE KEY 사용)
CREATE TABLE member (
  id INT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(100) UNIQUE,
  name VARCHAR(50) NOT NULL,
  phone VARCHAR(20),
  join_date DATE NOT NULL
);

-- 상품 테이블 생성 (PRIMARY KEY 사용)
CREATE TABLE product (
  product_id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  price INT NOT NULL,
  stock INT NOT NULL
);

-- 주문 테이블 생성 (FOREIGN KEY 사용)
CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTO_INCREMENT,
  member_id INT,
  product_id INT,
  quantity INT NOT NULL,
  order_date DATE NOT NULL,
  FOREIGN KEY (member_id) REFERENCES member(id),
  FOREIGN KEY (product_id) REFERENCES product(product_id)
);

 

• 데이터 삽입하기

-- 회원 데이터 삽입
INSERT INTO member (email, name, phone, join_date) VALUES
('hong@test.com', '홍길동', '010-1234-5678', '2023-01-15'),
('kim@test.com', '김영희', '010-2345-6789', '2023-02-20'),
('lee@test.com', '이철수', '010-3456-7890', '2023-03-10');

-- 상품 데이터 삽입
INSERT INTO product (product_id, name, price, stock) VALUES
(1, '노트북', 1500000, 50),
(2, '스마트폰', 800000, 100),
(3, '헤드폰', 200000, 200);

-- 주문 데이터 삽입
INSERT INTO orders (member_id, product_id, quantity, order_date) VALUES
(1, 1, 1, '2023-04-01'),
(2, 2, 2, '2023-04-02'),
(3, 3, 3, '2023-04-03');

 

---

• user 테이블 설계하기

create table user(
	id int primary key auto_increment,
    username varchar(50) unique,
    email varchar(50) unique,
    password varchar(100) not null,
    created_at timestamp default current_timestamp
);

insert into user(username,email,password)
values ('홍길동','a@nate.com','asd123');

select * from user;

 

• category 테이블 설계하기

create table category(
	category_id int primary key,
    name varchar(50) not null
);

 

• product 테이블에 category_id 컬럼 추가 및 외래 키 설정하기

alter table product 
add category_id int,
add foreign key (category_id) references category (category_id);

 

• orders 테이블에 인덱스 추가하기

alter table orders
add index idx_order_date (order_date);

 

• category 테이블에 데이터 삽입

insert into category (category_id, name) values
(1, '전자제품'),
(2, '가전제품');

 

• product 테이블의 category_id 업데이트하기

update product set category_id = 1;

 

• 새로운 회원 추가하기

insert into member(email,name,phone,join_date) values
('park@test.com','박민지','010-4567-8901','2023-04-05');

 

• 2023년 3월 이후 가입한 회원 조회하기

select * from member
where join_date > '2023-03%';

 

• 가격이 500,000원 이상인 상품 조회하기

select * from product
where price >= 500000;

 

• 이철수의 전화번호를 '010-9999-0000'으로 변경하기

update member
set phone = '010-9999-0000'
where name ='이철수';

 

• 재고가 100개 미만인 상품의 가격을 10% 인상

update product
set price = price * 1.1
where stock < 100;

 

• 2023년 4월 1일 이전 주문 삭제하기

delete from orders
where order_date < '2023-04-01';

 

• 재고가 0인 상품 삭제하기

delete from orders where quantity = 0;

delete from product
where stock = 0;

 

• 중복 이메일 삽입 시도하기

insert into member(email, name, phone, join_date) VALUES
('hong@test.com', '홍길동', '010-1234-5678', '2023-01-15');

 

• 존재하지 않는 회원의 주문 추가 시도하기

INSERT INTO orders (member_id, product_id, quantity, order_date) VALUES
(1111, 1, 1, '2023-04-01');

 

• orders 테이블에서 member_id와 order_date로 복합 인덱스 생성하기

create index idx_member_id_order_date on orders(member_id, order_date);

 

• 인덱스 활용 확인하기

show index from orders;

 

• 새로운 사용자 생성 및 권한 부여하기

create database shop2;

create user 'user1'@'localhost' identified by 'password123';

grant select on shop.member to 'user1'@'localhost';

 

• 권한 확인하기

show processlist;

 

• 권한 회수하기

revoke select on shop.member from 'user1'@'localhost';