浅显易懂 SQLite3 笔记（03）— SQL数据查询（超级详细）

---

前言

   SQL，指的是结构化查询语言，全称为 Structured Query Language。SQL是一种标准的计算机语言，我们可以使用SQL语句完成对数据库的大部分操作，如数据库、表、视图的创建、数据的增删改查等等。本篇，我们将学习如何使用SQL语句进行数据查询。

---

一、单表查询

1. 选择表中的若干列（投影）

【例1】查询所有学生的sno（学号）和sname（姓名）。

	SELECT sno,sname FROM student;

【例2】查询全体学生的详细记录。

	SELECT * FROM student;							//方法一
	SELECT sno,sname,ssex,sage,sdept FROM student;	//方法二

【例3】查询所有学生的sno和sdept，并将字段重命名为学号和院系。

	SELECT sno AS '学号',sdept AS '院系' FROM student;	

【例4】查询全体学生的姓名及出生年份。

	SELECT sname,strftime('%Y',DATE())-sage AS '出生日期' FROM student;

注意：DATE()为SQLite查询时间函数，%Y代表仅输出年份。

2. 选择表中的若干元组（选择）

【例1】查询选修了课程的学生学号。

	SELECT DISTINCT userid FROM Mark;

DISTINCT关键字：用于取消重复的记录；如果没有指定DISTINCT，则默认为ALL，即保留结果表中取值重复的记录。

【例2】查询计算机系全体学生的名单。

	SELECT * FROM student WHERE sdept = '计算机系';

【例3】查询年龄在17-20之间的学生姓名、系和年龄。

	SELECT sname,sdept,sage FROM student WHERE sage>=17 AND sage<=20;		//方法一
	SELECT sname,sdept,sage FROM Student WHERE sage BETWEEN 17 AND 20;	//方法二

注意：不能写成 sage BETWEEN 23 AND 20，即数字要从小到大。

【例4】查询计算机系、数学系和信息系的学生姓名和性别。

	//方法一
	SELECT sname,ssex FROM student WHERE sdept IN ('计算机系','信息系','数学系');
	//方法二
	SELECT sname,ssex FROM student WHERE sdept='计算机系' OR sdept='信息系' OR sdept='数学系';

【例5】查询所有姓刘的学生的姓名、学号和性别。

	SELECT sname,sno,ssex FROM student WHERE sname LIKE '刘%' ;

【例6】查询姓为“欧阳”且全名为3个汉字的学生姓名。

	SELECT sname FROM student WHERE sname LIKE '欧阳_';

【例7】查询以“DB_”开头，且倒数第2个字符为i的课程的详细情况。

	SELECT * FROM course WHERE cname LIKE 'DB\_%i_' ESCAPE'\';

• ESCAPE’ \ ’ 表示将 \ 定义为转义字符。
• 默认情况下，字符串的查询与大小写无关。
• 要进行大小写相关的查询，则需要在查询条件后面加上：collate Chinese_PRC_CS_AS_WS，如下所示。

	SELECT cname FROM courseWHERE cname LIKE 'DB\_%i__' ESCAPE'\' collate Chinese_PRC_CS_AS_WS;

3.ORDER BY 子句

• ORDER BY 语句用于根据指定的列对结果集进行排序。

【例1】查询选修了3001号课程的学生的学号和成绩，查询结果按分数的降序排列。

	SELECT userid,score FROM Mark WHERE courseid = '3001' ORDER BY score DESC;

注意：ORDER BY 子句中，降序关键字为DESC，升序关键字为ASC（无关键字默认升序）。

【例2】查询全体学生情况，查询结果按所在系的系号升序排序，同一系中的学生按年龄降序排列。

	SELECT * FROM student ORDER BY sdept,sage DESC;

【例3】TOP语句用于限制输出的记录行数。

	SELECT TOP(3)* FROM student ORDER BY sage DESC;	//显示前三行

【例4】忽略前N行然后取M行出来。

	SELECT * FROM student ORDER BY sno desc OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;

4.聚集函数

【例1】查询学生总人数。

	SELECT COUNT(*) AS '学生人数' FROM student;

【例2】查询选修了课程的学生人数。

	SELECT COUNT(DISTINCT userid) AS '选课人数' FROM Mark;

【例3】计算3001号课程的学生平均成绩和最高分。

	SELECT AVG(score) AS '平均分',MAX(score) AS '最高分' FROM Mark WHERE courseid = 3001;

5.GROUP BY 子句

• GROUP BY 语句根据一定的规则将一个数据集划分成若干个小区域，然后针对每个区域进行数据处理，即分组查询。一般是和聚合函数配合使用。

【例1】求各个课程号及相应的选课人数。

	SELECT courseid,COUNT(*) AS '课程人数' FROM Mark GROUP BY courseid;

【例2】某门课程成绩高于80分的学生的学号以及该生高于80分的课程平均成绩。

	SELECT userid,AVG(score) AS '平均成绩' FROM Mark WHERE score>80 GROUP BY userid;

【例3】查询选修了3门及以上课程的学生学号。

	//HAVING 子句在由 GROUP BY 子句创建的分组上设置条件。
	SELECT userid FROM Mark GROUP BY userid HAVING COUNT(*)>= 3;

重点区分WHERE和HAVING与GROUP BY语句的配合使用：

• WHERE+GROUP BY，先筛选符合条件的数据，再进行分组。
• HAVING+GROUP BY，先进行分组，再筛选符合条件的数据。

二、连接查询

   若一个查询同时涉及两个以上的表，称为连接查询。连接查询是关系数据库最主要的查询，包含等值连接查询、自身连接查询、外连接查询等。

1.等值连接

【例1】查询每个学生及其选修课程的情况。

	//方法一
	SELECT student.*,sc.* FROM student,sc WHERE student.sno = sc.sno
	//方法二
	SELECT student.*,sc.* FROM student INNER JOIN sc ON student.sno = sc.sno

【例2】查询选修了3002号课程且成绩在90分以上的所有学生的学号和姓名。

	SELECT student.sno,sname FROM student,sc WHERE student.sno = sc.sno AND score > 90 AND cno = 3002;

注：DBMS在执行连接查询语句的时候，通常会先对表中的记录进行选择操作，然后再进行等值连接。

【例3】查询选修了课程号为“C001”的课程且成绩在80分以上的学生的姓名、课程名及成绩。

	SELECT s.sname,c.cname,sc.grade FROM student s, sc,course c
		WHERE s.sno = sc.sno AND c.cno = sc.cno AND c.cno = 'C001' AND sc.grade >= 80;

注：表名较长时，可使用别名以简化代码。

2.自身连接

【例1】查询每一门课的间接先修课程号。

	SELECT c1.cno,c2.cpno FROM course c1,course c2 WHERE c1.cpno = c2.cno

3.左外连接

【例1】列出所有学生的基本情况和选课情况。如果该生没有选课，相关信息也一并列出。

	SELECT * FROM student s LEFT OUTER JOIN sc ON s.sno = sc.sno;

三、嵌套查询

• 在SQL语言中，一个SELECT-FROM-WHERE语句，被称为一个查询块。
• 将一个查询块嵌套在另一个查询块的WHERE或者HAVING短语的条件中的查询，称为嵌套查询。
• 嵌套查询使用户可以使用多个简单查询构成复杂的查询。

1.使用IN的嵌套查询

【例1】查询与“刘晨”在同一个系学习的学生姓名。

	SELECT sname,sdept FROM student WHERE sdept IN (SELECT sdept FROM student WHERE sname = '刘晨');

【例2】查询选修了课程名为“信息系统”的学生学号和姓名。

	SELECT sno,sname FROM student WHERE sno IN
		(SELECT sno FROM sc WHERE cno = (SELECT cno FROM course WHERE cname = '信息系统'));

2.带有比较运算符的嵌套查询

【例1】查询每个学生超过他自己选修课程平均成绩的课程号。

	SELECT sno,cno FROM sc s1 WHERE grade >= (SELECT AVG(grade) FROM sc WHERE sno = s1.sno);

3.带有EXISTS的嵌套查询

【例1】查询选修了3001号课程的学生姓名。

	SELECT sname FROM student WHERE EXISTS 
		(SELECT * FROM sc WHERE sno = student.sno AND cno = 3001);

四、集合查询

1.并运算UNION

• UNION 会对查询结果自动去重
• UNION ALL则直接将多个查询结果合并

【例1】查询计算机系的学生及年龄不大于19岁的学生。

	SELECT sno,sname FROM student WHERE sdept = '计算机系' 
		UNION SELECT sno,sname FROM student WHERE sage<=19;

2.交运算INTERSECT

【例1】查询计算机系的学生与年龄不大于19岁的学生的交集。

	SELECT * FROM student WHERE sdept = '计算机系' 
		INTERSECT SELECT * FROM student WHERE sage<=19;

3.差运算EXCEPT

【例1】查询计算机系的学生与年龄不大于19岁的学生的差集。

	SELECT * FROM student WHERE sdept = '计算机系' 
		EXCEPT SELECT * FROM student WHERE sage<=19;

---

总结

   以上就是浅显易懂 SQLite3 笔记（03）— SQL数据查询（超级详细）的所有内容，希望大家阅读后都能有所收获！原创不易，转载请标明出处，若文章出现有误之处，欢迎读者留言指正批评！