阅读《SQL必知必会》（第五版）然后结合往常表做的练习记录

这里使用的数据库时sqlite3,使用的工具时navicat

表资源链接https://wenku.baidu.com/view/349fb3639b6648d7c1c74652.html

表录入后如上图所示。后面如果有多张表之间的操作，在引入新的表。

1、查找当前表中所有的名字

SELECT 姓名 FROM student;

2、查找当前表中所有的名字和其对应的语文分数

SELECT 姓名,语文 FROM student;

3、查找表中的所有数据

SELECT * FROM student;

4、查找考试中出现的所有类型的语文分数

SELECT DISTINCT 语文 FROM student;

5、 查找这次考试语文的前5名

SELECT 姓名 ,语文 FROM student ORDER BY 语文 DESC LIMIT 5 ;

6、查找当前表中从第五行开始向后面数五行

SELECT * FROM student LIMIT 5 OFFSET 5 ;

7、在SQL语句中添加注释

SELECT * FROM student LIMIT 5 OFFSET 5 ;  --这是一条注释

8、查找表数据并根据语文排列

SELECT * FROM student ORDER BY 语文;

上面这段SQL语句为从小到大的顺序排列，下面展示分数从大到小的顺序排列

9、查找表并根据语文排列，分数高的在前面

SELECT * FROM student ORDER BY 语文 DESC;

10、查找表并根据语文排分，如果语文相同则数学分数高的排在前面

SELECT * FROM student ORDER BY 语文 DESC, 数学 DESC;

也可以根据语文和数学的列顺序编写SQL语句，观察上面的表，语文在第四列，数学在第五列

SELECT * FROM student ORDER BY 4 DESC, 5 DESC;

11、查找显示语文不及格的学生姓名

SELECT 姓名,语文 FROM student WHERE 语文 < 60;

上面这条语句使用了操作符< 书中给出了更多的操作符

比如查找语文分数在90到100之间的学生

SELECT 姓名,语文 FROM student WHERE 语文 BETWEEN 90 AND 100;

12、查找显示语文分数大于90并且数学分数也大于90

SELECT 姓名,语文,数学 FROM student WHERE 语文 > 90 AND 数学 > 90;

13、查找显示语文分数大于90或者数学分数大于90

SELECT 姓名,语文,数学 FROM student WHERE 语文 > 90 OR 数学 > 90;

14、查找显示语文分数大于90或者数学分数大于90并且满足前面两个条件后英语分数大于90

一开始使用如下代码查询

SELECT 姓名,语文,数学,英语 FROM student WHERE 语文 > 90 OR 数学 > 90 AND 英语 > 90;

结果如下

观察发现，89这个结果并不符合查找意图，这个是书中的一个知识点，SQL语句中也有运算优先级，AND的优先级大于OR,所以SQL语句应改为下面

SELECT 姓名,语文,数学,英语 FROM student WHERE (语文 > 90 OR 数学 > 90) AND 英语 > 90;

 使用（）提高其优先级

结果如下

15、挑选语文分数等于98和100的信息

SELECT 姓名,语文,数学,英语 FROM student WHERE 语文 IN(100,98);

结果如下

16、挑选语文分数不等于100的信息

SELECT 姓名,语文 FROM student WHERE NOT 语文 = 100 ORDER BY 语文 DESC;

结果如下

上面使用NOT排除了语文分数等于100的数据

17、查找王姓的数据

SELECT 姓名,语文 FROM student WHERE 姓名 LIKE '王%';

上面使用 LIKE进行搜索，然后使用%进行模糊查询，后面可以是0-n个字符。%通配符仅能用于文本字段（字符串）的搜索。

结果如下

18、 查找语文+数学+英语的总成绩做为排序

SELECT 姓名 || '(' || (语文 + 数学 + 英语)  || ')' AS 总成绩 FROM student ORDER BY (语文+数学+英语) DESC;

       结果如下

上面操作使用了计算字段，在进行数据计算的时候使用（）将其包裹起来，使用AS重命名显示

19、查找学号等于5的学生的所有信息

SELECT * FROM student WHERE 学号 = 5;

20、继续上面的操作，但是计算出其总成绩

SELECT * , (语文 + 数学 + 英语 + 信息技术 + 体育) AS 总成绩 FROM student WHERE 学号 = 5;

21、现在这次考试的成绩占比为60%，显示占比后的成绩

SELECT * , (语文 + 数学 + 英语 + 信息技术 + 体育)* 0.6 AS 折分后总成绩 FROM student WHERE 学号 = 5;

输出

22、SQL语句中使用函数

 

    

23、显示语文的平均分数

SELECT AVG(语文) AS 语文平均分 FROM student;

输出

24、查询当前表中的所有人数

SELECT COUNT(*) AS 人数 FROM student;

输出

25、返回参加语文考试的人数

下面将语文的分数设为Null表示未参加考试，然后使用SQL语句显示参加语文考试的人数

SELECT COUNT(语文) AS 人数 FROM student;

 输出：

26、返回语文分数的最大值

SELECT MAX(语文) AS 语文最高分 FROM student;

27、返回语文分数的最小值

SELECT MIN(语文) AS 语文最低分 FROM student;

 

28、返回语文这一列所有分数的总和

SELECT SUM(语文) AS 语文分数总和 FROM student;

输出

 

29、使用一条语句返回语文考试人数，语文最高分，语文最低分，语文平均分

SELECT SUM(语文) AS 语文分数总和 ,
MAX(语文) AS 语文最高分,
MIN(语文) AS 语文最低分,
AVG(语文) AS 语文平均分FROM student;

输出

上面代码使用了组合聚集函数，用单条语句执行了4个聚集计算，返回四个值

30、创建分组

以语文分数为分组，显示每种分数的人数

SELECT 语文, COUNT(*) AS 考试人数 FROM student GROUP BY 语文 ORDER BY 语文 DESC;

 输出如下

上面这段SQL语句使用了GROUP BY， “因为使用了GROUP BY，就不必指定要计算和估值的每个组，系统会自动完成。GROUP BY子句知识DBMS分组数据，然后对每个组而不是整个结果集进行聚集。”

31、使用SQL语句显示 语文，数学，英语相加的总分数，并显示各个分数的人数

SELECT (语文 + 数学 + 英语) AS 考试总分数 , COUNT(*) AS 分数总人数 FROM student GROUP BY (语文 + 数学 + 英语) ORDER BY 考试总分数 DESC;

使用了GROUP BY,所以会进行分组 ，GROUP BY使用的一些规定如下

32、过滤分组

使用HAVING子句过滤，HAVING非常类似于WHERE。唯一的差别是WHERE过滤行，HAVING过滤分组。

下面使用HAVING子句，在上面例子30的基础上挑选出分组人数 >= 2的数据。

SELECT (语文 + 数学 + 英语) AS 考试总分数 , COUNT(*) AS 分数总人数 FROM student GROUP BY (语文 + 数学 + 英语) HAVING COUNT(*) >= 2 ORDER BY 考试总分数 DESC;

33、筛选分组总分数>= 260分的总人数

SELECT (语文 + 数学 + 英语) AS 考试总分数 , COUNT(*) AS 分数总人数 FROM student WHERE 考试总分数 >= 260 GROUP BY (语文 + 数学 + 英语) HAVING COUNT(*) >= 2 ORDER BY 考试总分数 DESC;

输出如下

34、上面的查询都是基于单个表，下面开始使用多表查询

引入一张新表

学生所属的班级，表数据使用学号更严谨，因为没有重名，这里先使用姓名

需求，查询student表中语文分数为100的学生所属的班级，SQL语句如下

SELECT 姓名,班级 FROM class WHERE 姓名 IN(SELECT 姓名 FROM student WHERE 语文 = '100');

输出如下

这段sql语句使用了子查询，子查询语句是

SELECT 姓名 FROM student WHERE 语文 = '100';

 输出结果是

所以上面的语句也等效于

SELECT 姓名,班级 FROM class WHERE 姓名 IN('张平光','赵永敏');

输出结果是

 

35、查询住校的同学的语文分数

SELECT 姓名,语文 FROM student where 姓名 IN (SELECT student."姓名" FROM student INNER JOIN dormitory WHERE student."姓名" = dormitory."姓名");

输出结果如下 

36、查询student表中的姓名和dormitory表中的姓名是否相同，如果相同设为住校，如果不相同设为不住校

SELECT student."姓名",CASE WHEN EXISTS (SELECT 1 FROM dormitory WHERE dormitory."姓名" = student."姓名") THEN '住校'ELSE '不住校'END AS 住校状态
FROM student;

输出

37、只显示住校的名单

SELECT student."姓名",'住校' AS 住校状态 			--硬编码为住校，因为通过下面通过WHERE过滤过了
FROM student
WHERE EXISTS (SELECT 1 FROM dormitory WHERE dormitory."姓名" = student."姓名");