MySQL查询
时间：2021-07-01 10:21:17
帮助过：6人阅读
-- 数据的准备
2 -- 创建一个数据库
3 create database python_test charset
=utf8;
4
5 -- 使用一个数据库
6 use python_test;
7
8 -- 显示使用的当前数据是哪个?
9 select database();
10
11 -- 创建一个数据表
12 -- students表
13 create table students(
14 id
int unsigned
primary key auto_increment
not null,
15 name
varchar(
20)
default ‘‘,
16 age
tinyint unsigned
default 0,
17 height
decimal(
5,
2),
18 gender enum(
‘男‘,
‘女‘,
‘中性‘,
‘保密‘)
default ‘保密‘,
19 cls_id
int unsigned
default 0,
20 is_delete
bit default 0
21 );
22
23 -- classes表
24 create table classes (
25 id
int unsigned auto_increment
primary key not null,
26 name
varchar(
30)
not null
27 );
28
29
30
31 -- 查询
32 -- 查询所有字段
33 -- select * from 表名;
34 select * from students;
35 select * from classes;
36 select id, name
from classes;
37
38 -- 查询指定字段
39 -- select 列1,列2,... from 表名;
40 select name, age
from students;
41
42 -- 使用 as 给字段起别名
43 -- select 字段 as 名字.... from 表名;
44 select name
as 姓名, age
as 年龄
from students;
45
46 -- select 表名.字段 .... from 表名;
47 select students.name, students.age
from students;
48
49
50 -- 可以通过 as 给表起别名
51 -- select 别名.字段 .... from 表名 as 别名;
52 select students.name, students.age
from students;
53 select s.name, s.age
from students
as s;
54 -- 失败的select students.name, students.age from students as s;
55
56
57 -- 消除重复行
58 -- distinct 字段
59 select distinct gender
from students;
60
61
62 -- 条件查询
63 -- 比较运算符
64 -- select .... from 表名 where .....
65 -- >
66 -- 查询大于18岁的信息
67 select * from students
where age
>18;
68 select id,name,gender
from students
where age
>18;
69
70 -- <
71 -- 查询小于18岁的信息
72 select * from students
where age
<18;
73
74 -- >=
75 -- <=
76 -- 查询小于或者等于18岁的信息
77
78 -- =
79 -- 查询年龄为18岁的所有学生的名字
80 select * from students
where age
=18;
81
82
83 -- != 或者 <>
84
85
86 -- 逻辑运算符
87 -- and
88 -- 18到28之间的所以学生信息
89 select * from students
where age
>18 and age
<28;
90 -- 失败select * from students where age>18 and <28;
91
92
93 -- 18岁以上的女性
94 select * from students
where age
>18 and gender
="女";
95 select * from students
where age
>18 and gender
=2;
96
97
98 -- or
99 -- 18以上或者身高查过180(包含)以上
100 select * from students
where age
>18 or height
>=180;
101
102
103 -- not
104 -- 不在 18岁以上的女性 这个范围内的信息
105 -- select * from students where not age>18 and gender=2;
106 select * from students
where not (age
>18 and gender
=2);
107
108 -- 年龄不是小于或者等于18 并且是女性
109 select * from students
where (
not age
<=18)
and gender
=2;
110
111
112 -- 模糊查询
113 -- like
114 -- % 替换1个或者多个
115 -- _ 替换1个
116 -- 查询姓名中 以 "小" 开始的名字
117 select name
from students
where name
="小";
118 select name
from students
where name
like "小
%";
119
120 -- 查询姓名中 有 "小" 所有的名字
121 select name
from students
where name
like "
%小
%";
122
123 -- 查询有2个字的名字
124 select name
from students
where name
like "__";
125
126 -- 查询有3个字的名字
127 select name
from students
where name
like "__";
128
129 -- 查询至少有2个字的名字
130 select name
from students
where name
like "__
%";
131
132
133 -- rlike 正则
134 -- 查询以 周开始的姓名
135 select name
from students
where name rlike "
^周.
*";
136
137 -- 查询以 周开始、伦结尾的姓名
138 select name
from students
where name rlike "
^周.
*伦$";
139
140
141 -- 范围查询
142 -- in (1, 3, 8)表示在一个非连续的范围内
143 -- 查询 年龄为18、34的姓名
144 select name,age
from students
where age
=18 or age
=34;
145 select name,age
from students
where age
=18 or age
=34 or age
=12;
146 select name,age
from students
where age
in (
12,
18,
34);
147
148
149
150 -- not in 不非连续的范围之内
151 -- 年龄不是 18、34岁之间的信息
152 select name,age
from students
where age
not in (
12,
18,
34);
153
154
155 -- between ... and ...表示在一个连续的范围内
156 -- 查询 年龄在18到34之间的的信息
157 select name, age
from students
where age
between 18 and 34;
158
159
160 -- not between ... and ...表示不在一个连续的范围内
161 -- 查询 年龄不在在18到34之间的的信息
162 select * from students
where age
not between 18 and 34;
163 select * from students
where not age
between 18 and 34;
164 -- 失败的select * from students where age not (between 18 and 34);
165
166
167 -- 空判断
168 -- 判空is null
169 -- 查询身高为空的信息
170 select * from students
where height
is null;
171 select * from students
where height
is NULL;
172 select * from students
where height
is Null;
173
174 -- 判非空is not null
175 select * from students
where height
is not null;
176
177
178
179 -- 排序
180 -- order by 字段
181 -- asc从小到大排列，即升序
182 -- desc从大到小排序，即降序
183
184 -- 查询年龄在18到34岁之间的男性，按照年龄从小到到排序
185 select * from students
where (age
between 18 and 34)
and gender
=1;
186 select * from students
where (age
between 18 and 34)
and gender
=1 order by age;
187 select * from students
where (age
between 18 and 34)
and gender
=1 order by age
asc;
188
189
190 -- 查询年龄在18到34岁之间的女性，身高从高到矮排序
191 select * from students
where (age
between 18 and 34)
and gender
=2 order by height
desc;
192
193
194 -- order by 多个字段
195 -- 查询年龄在18到34岁之间的女性，身高从高到矮排序, 如果身高相同的情况下按照年龄从小到大排序
196 select * from students
where (age
between 18 and 34)
and gender
=2 order by height
desc,id
desc;
197
198
199 -- 查询年龄在18到34岁之间的女性，身高从高到矮排序, 如果身高相同的情况下按照年龄从小到大排序,
200 -- 如果年龄也相同那么按照id从大到小排序
201 select * from students
where (age
between 18 and 34)
and gender
=2 order by height
desc,age
asc,id
desc;
202
203
204 -- 按照年龄从小到大、身高从高到矮的排序
205 select * from students
order by age
asc, height
desc;
206
207
208 -- 聚合函数
209 -- 总数
210 -- count
211 -- 查询男性有多少人，女性有多少人
212 select * from students
where gender
=1;
213 select count(
*)
from students
where gender
=1;
214 select count(
*)
as 男性人数
from students
where gender
=1;
215 select count(
*)
as 女性人数
from students
where gender
=2;
216
217
218 -- 最大值
219 -- max
220 -- 查询最大的年龄
221 select age
from students;
222 select max(age)
from students;
223
224 -- 查询女性的最高 身高
225 select max(height)
from students
where gender
=2;
226
227 -- 最小值
228 -- min
229
230
231 -- 求和
232 -- sum
233 -- 计算所有人的年龄总和
234 select sum(age)
from students;
235
236
237 -- 平均值
238 -- avg
239 -- 计算平均年龄
240 select avg(age)
from students;
241
242
243 -- 计算平均年龄 sum(age)/count(*)
244 select sum(age)
/count(
*)
from students;
245
246
247 -- 四舍五入 round(123.23 , 1) 保留1位小数
248 -- 计算所有人的平均年龄，保留2位小数
249 select round(
sum(age)
/count(
*),
2)
from students;
250 select round(
sum(age)
/count(
*),
3)
from students;
251
252 -- 计算男性的平均身高 保留2位小数
253 select round(
avg(height),
2)
from students
where gender
=1;
254 -- select name, round(avg(height), 2) from students where gender=1;
255
256 -- 分组
257
258 -- group by
259 -- 按照性别分组,查询所有的性别
260 select name
from students
group by gender;
261 select * from students
group by gender;
262 select gender
from students
group by gender;
263 -- 失败select * from students group by gender;
264
265 -- 计算每种性别中的人数
266 select gender,
count(
*)
from students
group by gender;
267
268
269 -- 计算男性的人数
270 select gender,
count(
*)
from students
where gender
=1 group by gender;
271
272
273 -- group_concat(...)
274 -- 查询同种性别中的姓名
275 select gender,group_concat(name)
from students
where gender
=1 group by gender;
276 select gender,group_concat(name, age, id)
from students
where gender
=1 group by gender;
277 select gender,group_concat(name, "_", age, " ", id)
from students
where gender
=1 group by gender;
278
279 -- having
280 -- 查询平均年龄超过30岁的性别，以及姓名 having avg(age) > 30
281 select gender, group_concat(name),
avg(age)
from students
group by gender
having avg(age)
>30;
282
283 -- 查询每种性别中的人数多于2个的信息
284 select gender, group_concat(name)
from students
group by gender
having count(
*)
>2;
285
286
287
288 -- 分页
289 -- limit start, count
290
291 -- 限制查询出来的数据个数
292 select * from students
where gender
=1 limit
2;
293
294 -- 查询前5个数据
295 select * from students limit
0,
5;
296
297 -- 查询id6-10（包含）的书序
298 select * from students limit
5,
5;
299
300
301 -- 每页显示2个，第1个页面
302 select * from students limit
0,
2;
303
304 -- 每页显示2个，第2个页面
305 select * from students limit
2,
2;
306
307 -- 每页显示2个，第3个页面
308 select * from students limit
4,
2;
309
310 -- 每页显示2个，第4个页面
311 select * from students limit
6,
2;
-- -----> limit (第N页-1)*每个的个数, 每页的个数;
312
313 -- 每页显示2个，显示第6页的信息, 按照年龄从小到大排序
314 -- 失败select * from students limit 2*(6-1),2;
315 -- 失败select * from students limit 10,2 order by age asc;
316 select * from students
order by age
asc limit
10,
2;
317
318 select * from students
where gender
=2 order by height
desc limit
0,
2;
319
320
321
322 -- 连接查询
323 -- inner join ... on
324
325 -- select ... from 表A inner join 表B;
326 select * from students
inner join classes;
327
328 -- 查询 有能够对应班级的学生以及班级信息
329 select * from students
inner join classes
on students.cls_id
=classes.id;
330
331 -- 按照要求显示姓名、班级
332 select students.
*, classes.name
from students
inner join classes
on students.cls_id
=classes.id;
333 select students.name, classes.name
from students
inner join classes
on students.cls_id
=classes.id;
334
335 -- 给数据表起名字
336 select s.name, c.name
from students
as s
inner join classes
as c
on s.cls_id
=c.id;
337
338 -- 查询 有能够对应班级的学生以及班级信息，显示学生的所有信息，只显示班级名称
339 select s.
*, c.name
from students
as s
inner join classes
as c
on s.cls_id
=c.id;
340
341 -- 在以上的查询中，将班级姓名显示在第1列
342 select c.name, s.
* from students
as s
inner join classes
as c
on s.cls_id
=c.id;
343
344 -- 查询 有能够对应班级的学生以及班级信息, 按照班级进行排序
345 -- select c.xxx s.xxx from student as s inner join clssses as c on .... order by ....;
346 select c.name, s.
* from students
as s
inner join classes
as c
on s.cls_id
=c.id
order by c.name;
347
348 -- 当时同一个班级的时候，按照学生的id进行从小到大排序
349 select c.name, s.
* from students
as s
inner join classes
as c
on s.cls_id
=c.id
order by c.name,s.id;
350
351 -- left join
352 -- 查询每位学生对应的班级信息
353
