MySQL/MySQL_Primary.sql at main · django-cheng/MySQL · GitHub

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
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
-- Primary Key
use my_database;

create table cars_04(
car_id int primary key,
car_brand varchar(50),
car_color varchar(50),
car_sale_price int
);

desc cars_04;

-- insert data
insert into cars_04(car_id, car_brand, car_color, car_sale_price)
value
(1, 'Luxgen', 'white', 300000);

select * from cars_04;

-- insert data one more time
insert into cars_04(car_id, car_brand, car_color, car_sale_price)
value
(1, 'Luxgen', 'white', 300000);

show warnings;
# Duplicate entry '1' for key 'cars_04.PRIMARY'

-- insert data with same car_id one more time
insert into cars_04(car_id, car_brand, car_color, car_sale_price)
value
(2, 'Luxgen', 'white', 300000);

-- insert data without car_id (primary key)
insert into cars_04(car_brand, car_color, car_sale_price)
value
('Luxgen', 'white', 300000);

show warnings;
# Field 'car_id' doesn't have a default value

-- auto increment
create table cars_05(
car_id int primary key auto_increment,
car_brand varchar(50),
car_color varchar(50),
car_sale_price int

);

desc cars_05;

--
insert into cars_05(car_brand, car_color, car_sale_price)
values('Luxgen', 'green', 150000);

select * from cars_05;

insert into cars_05(car_brand, car_color, car_sale_price)
values('Luxgen', 'green', 150000);

select * from cars_05;

-- auto increment interrupted
insert into cars_05(car_id, car_brand, car_color, car_sale_price)
values(5566, 'Luxgen', 'green', 150000);

select * from cars_05;

insert into cars_05(car_id, car_brand, car_color, car_sale_price)
values(5, 'Luxgen', 'green', 150000);

select * from cars_05;

-- auto increment larger than previous
insert into cars_05(car_id, car_brand, car_color, car_sale_price)
values(6666, 'Luxgen', 'green', 150000);

select * from cars_05;

-- test
insert into cars_05(car_brand, car_color, car_sale_price)
values('Luxgen', 'green', 150000);

select * from cars_05;

alter table cars_05 auto_increment = 6;

insert into cars_05(car_brand, car_color, car_sale_price)
values('Luxgen', 'green', 150000);

select * from cars_05;
#6668

-- auto increment customized
create table cars_06(
car_id int primary key auto_increment,
car_brand varchar(50),
car_color varchar(50),
car_sale_price int
);

alter table cars_06 auto_increment = 101;

insert into cars_06(car_brand, car_color, car_sale_price)
values('Luxgen', 'red', 120000);

select * from cars_06;
#101-103

-- unique key
create table user_account(
user_id int primary key auto_increment,
user_name varchar(100) not null unique,
user_password varchar(100) not null
);

desc user_account;
--

insert into user_account(user_name, user_password)
values('iloveLuxgen', '5566');

select * from user_account;

insert into user_account(user_name, user_password)
values('ihateLuxgen', '5566');

select * from user_account;

-- pracetice #3 --
create table my_product(
product_id int primary key auto_increment,
product_name varchar(100),
product_price int
);

alter table my_product auto_increment = 21;

desc my_product;

insert into my_product(product_name, product_price)
values('Luxgen', 100000),
('Honda', 200000),
('Nissan', 300000),
('Toyota', 150000),
('Subaru', 250000);

select * from my_product;
-- practice #3 --