MySQL/MySQL_Null.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
-- null & default
desc employee;
select * from employee;

-- null
insert into employee(employee_id, employee_name)
values
(10, 'Tom');

create table cars(
car_brand varchar(50) not null,
car_color varchar(50) not null,
car_sale_price int
);

desc cars;

insert into cars(car_brand, car_sale_price)
value('Luxgen', 490000);

show warnings;
# Field 'car_color' doesn't have a default value
# Column count doesn't match value count at row 2

select * from cars;

insert into cars(car_brand, car_color, car_sale_price)
value
('Luxgen', 'gold', 400000),
('Luxgen', 'black', null);

-- Default
create table cars_02(
car_brand varchar(50) not null default 'unknown',
car_color varchar(50) not null default 'unknown',
car_sale_price int default 50000
);

desc cars_02;

insert into cars_02()
value
();

select * from cars_02;

insert into cars_02(car_color)
value
('black');

-- null with default
create table cars_03(
car_brand varchar(50) default '不知道',
car_color varchar(50) default 'unknown',
car_sale_price int default 50000
);

desc cars_03;

#1
insert into cars_03(car_brand, car_color, car_sale_price)
value
(null, 'black', null);

#2
insert into cars_03(car_color)
value
('black');

select * from cars_03;

-- practice #3 --
insert into cars_03(car_brand, car_color)
value
('Toyota', null),
('Honda', null);

insert into cars_03(car_brand, car_color, car_sale_price)
value
(null, 'black', null),
(default, 'black', default),
('Toyata', null, default),
('Honda', null, default);

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