MySQL Study 연습

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

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;

1
2
3
4
5
6
7
8
9
10
11
12
13

#맴버를 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');

1
2
3
4
5
6
7
8
9
10
11
12
13
14

#맴버를 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'); -- 김흥부 제거 

1
2
3
4
5
6
7
8
9
10
11
12
13
14

#맴버를 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');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108

#######################################################
######################### 상품 #########################
#######################################################
#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