1 --数据的准备
2 --创建一个数据库
3 create database python_test charset=utf8;4
5 --使用一个数据库
6 usepython_test;7
8 --显示使用的当前数据是哪个?
9 select database();10
11 --创建一个数据表
12 --students表
13 create tablestudents(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 tableclasses (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 * fromstudents;35 select * fromclasses;36 select id, name fromclasses;37
38 --查询指定字段
39 --select 列1,列2,... from 表名;
40 select name, age fromstudents;41
42 --使用 as 给字段起别名
43 --select 字段 as 名字.... from 表名;
44 select name as 姓名, age as 年龄 fromstudents;45
46 --select 表名.字段 .... from 表名;
47 select students.name, students.age fromstudents;48
49
50 --可以通过 as 给表起别名
51 --select 别名.字段 .... from 表名 as 别名;
52 select students.name, students.age fromstudents;53 select s.name, s.age from students ass;54 --失败的select students.name, students.age from students as s;
55
56
57 --消除重复行
58 --distinct 字段
59 select distinct gender fromstudents;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 byage;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 fromstudents;222 select max(age) fromstudents;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) fromstudents;235
236
237 --平均值
238 --avg
239 --计算平均年龄
240 select avg(age) fromstudents;241
242
243 --计算平均年龄 sum(age)/count(*)
244 select sum(age)/count(*) fromstudents;245
246
247 --四舍五入 round(123.23 , 1) 保留1位小数
248 --计算所有人的平均年龄,保留2位小数
249 select round(sum(age)/count(*), 2) fromstudents;250 select round(sum(age)/count(*), 3) fromstudents;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 bygender;261 select * from students group bygender;262 select gender from students group bygender;263 --失败select * from students group by gender;
264
265 --计算每种性别中的人数
266 select gender,count(*) from students group bygender;267
268
269 --计算男性的人数
270 select gender,count(*) from students where gender=1 group bygender;271
272
273 --group_concat(...)
274 --查询同种性别中的姓名
275 select gender,group_concat(name) from students where gender=1 group bygender;276 select gender,group_concat(name, age, id) from students where gender=1 group bygender;277 select gender,group_concat(name, "_", age, " ", id) from students where gender=1 group bygender;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 joinclasses;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 byc.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 byc.name,s.id;350
351 --left join
352 --查询每位学生对应的班级信息
353 select * from students as s left join classes as c on s.cls_id=c.id;354
355 --查询没有对应班级信息的学生
356 --select ... from xxx as s left join xxx as c on..... where .....
357 --select ... from xxx as s left join xxx as c on..... having .....
358 select * from students as s left join classes as c on s.cls_id=c.id having c.id is null;359 select * from students as s left join classes as c on s.cls_id=c.id where c.id is null;360
361 --right join on
362 --将数据表名字互换位置,用left join完成
363
364 --自关联
365 --省级联动 url:http://demo.lanrenzhijia.com/2014/city0605/
366
367 --查询所有省份
368 select * from areas where pid is null;369
370 --查询出山东省有哪些市
371 select * from areas as province inner join areas as city on city.pid=province.aid having province.atitle="山东省";372 select province.atitle, city.atitle from areas as province inner join areas as city on city.pid=province.aid having province.atitle="山东省";373
374 --查询出青岛市有哪些县城
375 select province.atitle, city.atitle from areas as province inner join areas as city on city.pid=province.aid having province.atitle="青岛市";376 select * from areas where pid=(select aid from areas where atitle="青岛市")377
378
379 --子查询
380 --标量子查询
381 --查询出高于平均身高的信息
382 select * from students where height> (select avg(height) fromstudents);383
384 --查询最高的男生信息
385 select * from students where height = 188;386 select * from students where height = (select max(height) fromstudents);387
388 --列级子查询
389 --查询学生的班级号能够对应的学生信息
390 select * from students where cls_id in (select id from classes);