Day16 PythonWeb全栈课程课堂内容

SQL语法

模糊查询

Like关键字用来进行模糊查询，并且结合%以及_使用。

• % 表示任意多个任意字符
• _ 表示一个任意字符

1.查询名字以a开始的学生信息

SELECT * FROM students WHERE NAME LIKE “a%”;

2.查询名字含有a的学生信息

SELECT * FROM students WHERE NAME LIKE “%a%”;

3.查询名字仅有2个字符的学生信息

SELECT * FROM students WHERE NAME LIKE " __";

4.查询名字至少有2个字符的学生信息

SELECT * FROM students WHERE NAME LIKE “%__%”;

范围查询

关键字	描述
in	表示在一个非连续的范围内
between…and…	表示在一个连续的范围内

练习

1.查询id是1或者4或者6的学生信息

SELECT * FROM students WHERE id IN (1,3,6);

2.查询年龄为18或者20的学生信息

SELECT * FROM students WHERE age=18 OR age=21;

SELECT * FROM students WHERE age IN (18,21);

3.查询年龄不是18,20的学生信息

SELECT * FROM students WHERE age!=18 AND age!=21;

SELECT * FROM students WHERE age NOT IN (18,21);

4.查询id是2至4的学生信息

SELECT * FROM students WHERE id BETWEEN 2 AND 5;

5.查询id是3-5的男同学信息

SELECT * FROM students WHERE id BETWEEN 3 AND 5 AND gender=‘男’;

SELECT * FROM students WHERE (id BETWEEN 3 AND 5) AND gender=‘男’;

6.查询年龄不在18至20之间的学生信息

SELECT * FROM students WHERE age NOT BETWEEN 18 AND 21;

空判断

关键字	描述
is null	判断空
is not null	判断非空

练习

1.查询没有填写年龄的学生

SELECT * FROM students WHERE cls_id IS NULL;

2.查询已分配班级的学生

SELECT * FROM students WHERR cls_id NOT NULL;

常用聚合函数

count 总数

• 求students总人数

SELECT COUNT(*) FROM students;

• 求男性的人数

SELECT COUNT(*) FROM students WHERE gender=‘男’;

• 求女性的人数

SELECT COUNT(*) FROM students WHERE gender=‘女’;

max() 最大值

• 查询最大的年龄

SELECT MAX(age) FROM students; – 可以

SELECT MAX(age),NAME FROM students; – 报错

• 女性最大的id

SELECT MAX(id) FROM student

min() 最小值

• 查询未删除的学生的最小编号

SELECT MIN(id) FROM students WHERE Is_Del=‘0’;

sum 求和

• 查询男生年龄和

SELECT SUM(age) FROM students WHERE gender=‘Male’;

avg 平均值

• 查询未删除女生的年龄的平均值

SELECT AVG(age) FROM students WHERE gender=‘Female’;

• 计算男性的平均年龄，保留2位小数

分组与分组之后的筛选

分组

在数据库中，通过 group by 将查询结果按照1个或多个字段进行分组，字段值相同的为一组。

select … from students group by 需要分组字段;

SELECT * FROM students GROUP BY gender;-- 报错。里面具体值无法呈现。
SELECT COUNT(*) FROM students GROUP BY gender;
SELECT gender,COUNT(*) FROM students GROUP BY gender;

练习：

• 计算男生和女生中的人数

SELECT gender,COUNT(*) FROM students GROUP BY gender;

• 需要显示性别对应人数

SELECT gender,COUNT(*) FROM students GROUP BY gender;

• 男生最大年龄与女生最大年龄

SELECT NAME,MAX(age) FROM students GROUP BY gender;

• 显示性别人数，还需要显示总人数

SELECT gender,COUNT(*) FROM students GROUP BY gender WITH ROLLUP;

group_concat(…)

我们通过group_concat(…)查看每组的详细信息

练习：

• 查询同种性别(分组后)的姓名

SELECT gender,GROUP_CONCAT(NAME) FROM students GROUP BY gender;

• 查询组内年龄,姓名

SELECT GROUP_CONCAT(NAME,age) FROM students GROUP BY gender;

SELECT GROUP_CONCAT(NAME,age),GROUP_CONCAT(age) FROM students GROUP BY gender;

SELECT GROUP_CONCAT(gender),GROUP_CONCAT(NAME,":",age) FROM students GROUP BY gender;

• 查询对应性别的总数之后，需要筛选出总数>2的

SELECT gender,COUNT(*) FROM students GROUP BY gender HAVING COUNT(*)>2;

分组后的筛选

练习：

• 查询男生女生总数大于2

SELECT gender,COUNT(*) FROM students GROUP BY gender HAVING COUNT(*)>2;

• 查询男生女生总数大于2的姓名

SELECT gender,GROUP_CONCAT(NAME),COUNT(*) FROM students GROUP BY gender HAVING COUNT(*)>2;

• 查询男生女生平均年龄超过18岁的性别，以及姓名

SELECT gender,ROUND(AVG(age),0),GROUP_CONCAT(NAME) FROM students GROUP BY gender HAVING AVG(age)>18;

排序

order by 字段 默认升序

order by 字段 asc 指定升序

order by 字段 desc 指定降序

练习：

• 查询年龄在18到26之间的男同学，按照年龄从小到大排序

SELECT * FROM students WHERE (age BETWEEN 18 AND 27) AND gender=1 ORDER BY age;

• 查询年龄在18到20岁之间的女同学，id从高到低排序

SELECT * FROM students WHERE (age BETWEEN 18 AND 21) AND gender=2 ORDER BY id DESC;

• 查询年龄在18-23岁之间的女性，年龄从高到低降序，当年龄相同时，按照身高从低到高升序。

SELECT gender,NAME,age,height FROM students WHERE (age BETWEEN 18 AND 23) AND gender=2 ORDER BY age DESC;

限制

limit,start,count

SELECT * FROM students LIMIT 0,2; – 只显示前两条。

• start 为偏移量，默认起始0

• count 为条数

注意：

• limit 不能写数学公式
• limit 只能写在末尾