top10_sql_skills

1
-- 01
2
-- bad
3
create table cm.tb_user(
4
    id int,
5
    name varchar(255),
6
    age int,
7
    gender varchar(16)
8
);
9
-- good
10
create table cm.tb_user(
11
    id int primary key,
12
    name varchar(255),
13
    age int,
14
    gender varchar(16),
15
    store_time datetime default now()
16
);
17

18
-- 02
19
-- bad
20
create table cm.tb_user(
21
    id int primary key ,
22
    name varchar(255) not null default 'NULL',
23
    age int,
24
    gender varchar(16),
25
    create_time datetime default now(),
26
    update_time datetime default now()
27
);
28
-- good
29
create table cm.tb_user(
30
    id int primary key ,
31
    name varchar(255) not null default '-',
32
    age int,
33
    gender varchar(16),
34
    store_time datetime default now(),
35
    update_time datetime default now()
36
);
37

38
-- 03
39
-- bad
40
select min(age), max(age) from cm.tb_user where age < 40 group by gender;
41
-- good
42
select
43
    min(age),
44
    max(age)
45
from cm.tb_user
46
where age < 40
47
group by gender;
48

49
-- 04
50
-- bad
51
select id,
52
       name
53
from cm.tb_user
54
where age > 10
55
  and id<=10010;
56
-- good
57
select id,
58
       name
59
from cm.tb_user
60
where 1=1
61
  and age > 10
62
  and id<=10010;
63

64
-- 05
65
-- bad
66
insert into cm.tb_user
67
values
68
    (1001, 'Mike', 20, 'M');
69
-- good
70
insert into cm.tb_user
71
(id, name, age, gender)
72
values
73
    (1001, 'Mike', 20, 'M');
74

75
-- 06
76
-- good
77
explain
78
select *
79
from cm.tb_user
80
where id=1001 and age = 20;
81

82
-- 07
83
-- bad
84
select *
85
from cm.tb_user;
86
-- good
87
select id,
88
       name
89
from cm.tb_user;
90

91
-- 08
92
-- 分批次+limit进行delete或update
93
delete
94
from cm.tb_user
95
where age > 35
96
limit 5;
97

98
-- 09
99
-- bad
100
delete from cm.tb_user
101
where age <= 20;
102
update cm.tb_user
103
set age = age + 1;
104
-- good
105
begin;
106
delete from cm.tb_user
107
where age <= 20;
108
update cm.tb_user
109
set age = age + 1;
110
commit;
111

112
-- 10
113
-- data sample
114
insert into cm.data_count
115
(dt, count)
116
values
117
    ('2023-06-20', 101),
118
    ('2023-06-21', 231),
119
    ('2023-06-22', 170),
120
    ('2023-06-23', 146),
121
    ('2023-06-24', 187),
122
    ('2023-06-25', 123),
123
    ('2023-06-26', 221),
124
    ('2023-06-27', 101),
125
    ('2023-06-28', 103),
126
    ('2023-06-29', 122),
127
    ('2023-06-30', 144);
128
-- bad
129
select dt, count
130
from cm.data_count t1;
131
-- good
132
select
133
    t1.dt,
134
    t1.count * 1.0 / t2.count as ratio
135
from cm.data_count t1
136
    join cm.data_count t2
137
on datediff(t1.dt, t2.dt)=7;