MySQL Study 연습

SCHEMA(스키마) 만들기

테이블 만들기
– 고객의 정보 테이블
– 물건의 정보 테이블
– 영수증을 보관 테이블

고객의 정보를 저장하는 테이블
– uid와 name은 넉넉하게 45
– registration_date 는 맴버가 등록된 시간을 저장

고객의 정보를 저장하는 테이블 생성

이제 맴버를 등록해 보자
(아래는 공부하기 좋은 사이트)

https://www.w3schools.com/mysql/default.asp

오류……

0 10 04:25:12 INSERT INTO members_table(member_uid, member_name)
VALUE (03d64791-b24e-4769-b297-788929acee8f, 홍길동)
Error Code: 1054. Unknown column ’03d64791′ in ‘field list’ 0.000 sec
원인은 ‘ ‘ 추가하고 해결

반복 작업이니 PROCEDURE 를 사용하자

USE exam_data;
SELECT * FROM members_table;

#맴버 등록
-- 홍길동 03d64791-b24e-4769-b297-788929acee8f
-- 임꺽정 a108f00e-9c8a-42e4-8be9-9bb5ee700d9d
-- 장보고 6887898f-2b92-4c49-bf4c-6f12acd8ff91
-- 이순신 6bd03e52-6dc8-4ccc-ac4a-b649cfbd4162
-- 김흥부 a3709a4a-5c75-11ed-9b6a-0242ac120002
-- 김놀부 8acd150c-5c77-11ed-9b6a-0242ac120002

INSERT INTO members_table(member_uid, member_name)
VALUE ('03d64791-b24e-4769-b297-788929acee8f', '홍길동');

delimiter //
CREATE PROCEDURE add_members_item($member_uid varchar(45), $member_name  varchar(45))
BEGIN
	INSERT INTO members_table(member_uid, member_name)
    VALUE ($member_uid, $member_name);
END //
delimiter ;

-- 프로시져 드랍 
Drop PROCEDURE add_members_item;

CALL add_members_item('03d64791-b24e-4769-b297-788929acee8f','홍길동'); -- uid 가 겹침 적용 안됨 
CALL add_members_item('a108f00e-9c8a-42e4-8be9-9bb5ee700d9d','임꺽정');
CALL add_members_item('6887898f-2b92-4c49-bf4c-6f12acd8ff91','장보고');
CALL add_members_item('6bd03e52-6dc8-4ccc-ac4a-b649cfbd4162','이순신');
CALL add_members_item('a3709a4a-5c75-11ed-9b6a-0242ac120002','김흥부');
CALL add_members_item('8acd150c-5c77-11ed-9b6a-0242ac120002','김놀부');
SELECT * FROM members_table;

UID로 맴버 검색

#맴버를 uid로 검색
delimiter //
CREATE PROCEDURE search_member($member_uid varchar(45))
BEGIN
	SELECT member_name, registration_date FROM members_table
	WHERE member_uid = $member_uid;
END //
delimiter ;

-- 프로시져 드랍 
Drop PROCEDURE search_member;

CALL search_member('6bd03e52-6dc8-4ccc-ac4a-b649cfbd4162');

uid로 맴버 제거

#맴버를 uid로 삭제
delimiter //
CREATE PROCEDURE delete_member($member_uid varchar(45))
BEGIN
	delete from members_table 
	where member_uid = $member_uid;
    SELECT * FROM members_table;
END //
delimiter ;

-- 프로시져 드랍 
Drop PROCEDURE delete_member;

CALL delete_member('a3709a4a-5c75-11ed-9b6a-0242ac120002'); -- 김흥부 제거 

맴버를 uid로 수정
임꺽정 -> 임꺽정2

#맴버를 uid로 수정
delimiter //
CREATE PROCEDURE update_member($member_uid varchar(45), $member_name varchar(45))
BEGIN
	update members_table  set member_name = $member_name
	where member_uid = $member_uid;
    SELECT * FROM members_table;
END //
delimiter ;

-- 프로시져 드랍 
Drop PROCEDURE update_member;

CALL update_member('a108f00e-9c8a-42e4-8be9-9bb5ee700d9d','임꺽정2');

나스에서 적용 확인

상품의 정보를 저장하는 테이블 생성

#######################################################
######################### 상품 #########################
#######################################################
#100 음료
-- a61617dd-37ef-41c2-b264-b62477c9c985 콜라
-- 906ee7f0-7bb0-4259-97cf-551dd47987b8 사이다 
-- 91900f64-97bf-4e13-ac74-abc976e5959c 게토레이 
#200 과자
-- 50cb7fca-5dde-11ed-9b6a-0242ac120002 치토스
-- a4e298da-855a-49bc-9ff4-3a55658a5494 썬칩
-- b8702f3c-1d1a-4653-a8d4-fb80baca8517 오징어땅콩
#300 아이스크림
-- 5157547e-8187-4736-b304-81f256341392 뽕따
-- 96e42038-dc79-405c-be07-f3d1025a846e 월드콘
-- 8bd1592c-ce12-4773-9cb9-394cb6ea41b4 죠스바

use exam_data;
select * from products_table;

delimiter //
create procedure add_product($product_uid varchar(45), $product_category INT(11) , $product_name varchar(45), $product_price INT(11))
begin
insert into products_table(product_uid, product_category, product_name, product_price)
values ($product_uid, $product_category, $product_name, $product_price);
end //
delimiter ;

call add_product('a61617dd-37ef-41c2-b264-b62477c9c985', 100, '콜라', 1700);
call add_product('906ee7f0-7bb0-4259-97cf-551dd47987b8', 100, '사이다', 1500);
call add_product('91900f64-97bf-4e13-ac74-abc976e5959c', 100, '게토레이', 2000);

call add_product('50cb7fca-5dde-11ed-9b6a-0242ac120002', 200, '치토스', 1300);
call add_product('a4e298da-855a-49bc-9ff4-3a55658a5494', 200, '썬칩', 1400);
call add_product('b8702f3c-1d1a-4653-a8d4-fb80baca8517', 200, '오징어땅콩', 1500);

call add_product('5157547e-8187-4736-b304-81f256341392', 200, '뽕따', 1200);
call add_product('96e42038-dc79-405c-be07-f3d1025a846e', 200, '월드콘', 1500);
call add_product('8bd1592c-ce12-4773-9cb9-394cb6ea41b4', 200, '죠스바', 1000);

-- 프로시져 드랍 
Drop PROCEDURE add_product;




#상품을 이름으로 검색
delimiter //
CREATE PROCEDURE search_product($product_name varchar(45))
BEGIN
	SELECT product_uid , product_category, product_name, product_price, registration_date FROM products_table
	WHERE product_name = $product_name;
END //
delimiter ;

-- 프로시져 드랍 
Drop PROCEDURE search_member;

CALL search_product('월드콘');



#상품을 uid로 삭제
delimiter //
CREATE PROCEDURE delete_product($product_uid varchar(45))
BEGIN
	delete from products_table 
	where product_uid = $product_uid;
    
    SELECT * FROM products_table;
END //
delimiter ;

-- 프로시져 드랍 
Drop PROCEDURE delete_member;

CALL delete_product('a61617dd-37ef-41c2-b264-b62477c9c985');  -- 콜라 제거 

#맴버를 uid로 수정
delimiter //
CREATE PROCEDURE update_product($product_uid varchar(45), $product_price INT(11))
BEGIN
	update products_table  set product_price = $product_price
	where product_uid = $product_uid;
    
    SELECT * FROM products_table;
END //
delimiter ;

-- 프로시져 드랍 
Drop PROCEDURE update_product;

CALL update_product('96e42038-dc79-405c-be07-f3d1025a846e', 1500);




### 정리 ###
## 상품를 추가하는 PROCEDURE
-- call add_product('', , '', );

## 특정 이름으로 상품을 검색하는 PROCEDURE
-- CALL search_product('월드콘');

## 특정 상품를 제거하는 PROCEDURE
-- CALL delete_product('');

## 특정 맴버를 수정하는 PROCEDURE
-- CALL update_product('', );

Microsoft Windows [Version 10.0.19044.2130]
(c) Microsoft Corporation. All rights reserved.

C:\Users\서울IT>mysql -u root -p exam_data_dump_schema < C:\mysql_data/exam_data_dump.sql
Enter password: **********
ERROR 1049 (42000): Unknown database 'exam_data_dump_schema'

C:\Users\서울IT>mysql -u root -p exam_data_dump_schema < C:\mysql_data/exam_data_dump.sql
Enter password: **********

C:\Users\서울IT>mysqldump -u root --routines --triggers -u apptm -p exam_data > C:\mysql_data/exam_data_2.sql
Enter password: **********
mysqldump: Got error: 1045: Access denied for user 'apptm'@'localhost' (using password: YES) when trying to connect

C:\Users\서울IT>mysqldump -u root --routines --triggers -p exam_data > C:\mysql_data/exam_data_2.sql
Enter password: **********

C:\Users\서울IT>mysql -u root -p exam_data_dump2_schema < C:\mysql_data/exam_data_2.sql
Enter password: **********

C:\Users\서울IT>mysql -u root -p exam_data_dump2_schema < C:\mysql_data/exam_data_2.sq