(DB)MySQL 기초 복습 하기 - 14

 

학습 목표

- MySQL에서 배운 DDL,DML,DCL의 개념 복습

- PRIMARY KEY. UNIQUE KEY , FOREIGN KEY, INDEX 활용실습

- SELECT, INSERT , UPDATE , DELETE 구문으로 데이터 조작 연습

 

1. 테스트 환경 설정(샘플 데이터 입력)

---> 연습영 데이터베이스 와 테이블을 생성해주세요.

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

-- 회원 테이블 생성(PRIMARTY 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');

2. 연습문제 풀어 보기

user 테이블 설계

다음 조건을 만족하는 user 테이블을 생성하세요.

• id: 기본 키, 자동 증가
• username: 고유해야 함
• email: 고유해야 함
• password: 필수 입력
• created_at: 현재 시간 기본값 설정 (TIMESTAMP 사용하기)

2.1. DDL 연습 (테이블 생성 및 수정)

1. 새로운 테이블 category 생성

-컬럼: category_id (기본 키), name (필수)

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

2. product 테이블에 category_id 컬럼 추가 및 외래 키 설정

alter table product
add column category_ud int;

alter table product 
ADD CONSTAINT fk_product_category
foreign key (category_id) references category(category_id);

 

3. orders 테이블에 인덱스 추가

-order_date 컬럼에 idx_order_date 인덱스 생성

alter table product
add index idx_order_data(order_date);

 

==============================================================

2.2. DML 연습 (데이터 조작)

INSERT 연습

1. category 테이블에 데이터 삽입

- category_id=1, name='전자제품', category_id=2, name='가전제품

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

 

2. product 테이블의 category_id 업데이트

-모든 상품의 category_id를 1로 설정

update product
set category_id =1;

 

3.새로운 회원 추가

- email='park@test.com', name='박민지', phone='010-4567-8901', join_date='2023-04-05'

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

 

==================================================================

SELECT 연습

1.2023년 3월 이후 가입한 회원 조회

select * from
where join_date >= '2023-03-01';

 

 

2. 가격이 500,000원 이상인 상품 조회

select * frpm product
where price >= 500000;

 

=====================================================================

UPDATE 연습

1. 이철수의 전화번호를 '010-9999-0000'으로 변경

select * from member
where name = '이철수';

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

 

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

select * from product 
where stock < 100;

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

 

=============================================================

DELETE 연습

1. 2023년 4월 1일 이전 주문 삭제

 

select * from orders
where order_data < '2023-04-01;'

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

 

2. 재고가 0인 상품 삭제

select * from product 
where stock = 0;

delete from product
where stock = 0;

 

참고: FOREIGN KEY 제약으로 삭제 전 관련 orders 데이터 삭제 필요.

 

2.3 . 키 제약 조건 및 인덱스 연습

1. 중복 이메일 삽입 시도

isert into member (email, name, phone, join_date)
values('park@test.com','장그래','010-1267-84501','2023-03-05');

 

-기대 결과: UNIQUE KEY 제약 조건 위반으로 오류 발생.

 

2. 존재하지 않는 회원의 주문 추가 시도

select * from orders;
insert into orders (member_id, product_id, quantity, order_date) VALUES
(1, 4, 1, '2023-04-01');

 

- 기대 결과: FOREIGN KEY 제약 조건 위반으로 오류 발생.

 

3. orders 테이블에서 member_id와 order_date로 복합 인덱스 생성

create index member_id_order_id on orders (member_id,order_date);

 

4. 인덱스 활용 확인

 

- key 컬럼에서 idx_member_date 사용 여부 확인.

select * from orders
where order_date ='2023-04-01';

2.4 DCL 연습 (간단한 권한 관리)

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

create user 'user1'@'lacalhost'identified by 'password123';
grant select on shop.member to 'user1'@'localhost';

권한 확인

 

권한 회수

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