数据查询--单表查询

目录

1. 选择表中的若干列

1.1 查询指定列

1.2 查询全部列

1.3 查询经过计算的值

2. 选择表中的若干元组

2.1 消除取值重复的行

2.2 查询满足条件的元组

2.2.1 比较大小

2.2.2 确定范围

2.2.3 确定集合

2.2.4 字符匹配

2.2.5 涉及空值的查询

2.2.6 多重条件查询

3. ORDER BY 子句

4. 聚集函数 

5. GROUP BY 子句

---

一般格式：

SELECT [ALL | DISTINCT] <目标列表达式> [,<目标列表达式>]···
FROM <表名或视图名> [,< 表名或视图名 >] … | (<SELECT 语句>) [AS]<别名> 
[WHERE <条件表达式>] 
[GROUP BY <列名1> [HAVING <条件表达式>]] 
[ORDER BY <列名2> [ASC | DESC]];

单表查询：仅涉及一个表的查询

1. 选择表中的若干列

选择表中的全部或部分列即关系代数的投影运算

1.1 查询指定列

通过在SELECT子句的<目标列表达式>中指定要查询的属性列

--查询全体学生的学号及姓名
SELECT Sno,Sname
FROM Student;

1.2 查询全部列

• 将<目标列表达式>指定为*
• 列出所有列名

--查询所有学生的全部记录
--方法一：
SELECT *
FROM Student;
 
--方法二：
SELECT Sno,Sname,Ssex,Sage,Sdept
FROM STUDENT;

1.3 查询经过计算的值

SELECT子句的<目标列表达式>不仅可以是表中的属性列，也可以是表达式，字符串常量，函数等

--查询全体学生的姓名，出生年份和所在的院系（要求系名小写）
SELECT Sname,'Year of Birth',2022-Sage,LOWER(Sdept)
From Student;
--Sname是变量
--'Year of Birth'是文本常量（固定不变）
--2022-Sage是运算得到的变量
--LOWER(Sdept)是函数

这个结果中除Sname有列名外，其他三个是无列名的，我们可以通过指定别名来改变查询结果的列标题，这对表达式，常量，函数名的目标列表达式很有用

SELECT Sname NAME,'Year of Birth' BIRTH,2022-Sage BIRTHDAY,LOWER(Sdept) DEPARTMENT
From Student ;

2. 选择表中的若干元组

2.1 消除取值重复的行

两个并不完全相同的元组在投影到指定的某些列上后，可能会变成相同的行，可以用DISTINCT消除它们

--查询选修了课程的学生学号
SELECT DISTINCT Sno
FROM SC;

如果没有指定DISTINCT关键词，则默认为ALL

2.2 查询满足条件的元组

查询满足指定条件的元组可以通过WHERE子句实现

2.2.1 比较大小

--查询计算机科学系全体学生的名单
SELECT Sname 
FROM Student 
WHERE Sdept = 'CS';
 
--查询所有年龄在20岁以下的学生姓名及其年龄
SELECT Sname,Sage 
FROM Student 
WHERE Sage < 20;

2.2.2 确定范围

--查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄
SELECT Sname,Sdept,Sage 
FROM Student 
WHERE Sage BETWEEN 20 AND 23;

2.2.3 确定集合

--查询计算机科学系（CS）、数学系（MA）和信息系（IS）学生的姓名和性别
SELECT Sname,Ssex 
FROM Student 
WHERE Sdept IN ('CS','MA','IS');
 
--找出年龄小于23岁，籍贯是湖南或湖北的学生的性别和姓名
SELECT Sname,Sex
FROM Student
WHERE Sage<23 and Bplace IN （'湖南','湖北')

2.2.4 字符匹配

LIKE是一种运算，是一种文本运算

一般语法格式：

[NOT] LIKE ‘<匹配串>’  [ESCAPE ‘ <换码字符>’]

查找指定的属性列值与<匹配串>相匹配的元组

<匹配串>可以是一个完整的字符串，也可以含有通配符%和_

• % （百分号） ：代表任意长度（长度可以为0）的字符串

例如：a%b — acb，addgb，ab

• _ （下横线）：代表任意单个字符

例如：​​​​​​a_b — acb，afb

--查询学号为201215121的学生的详细情况
SELECT * 
FROM Student 
WHERE Sno LIKE '201215121';
--如果LIKE后面的匹配串不含通配符，则可以用=运算符取代LIKE谓词
SELECT * 
FROM Student 
WHERE Sno = '201215121';
 
--查询所有姓刘学生的姓名、学号和性别
SELECT Sname,Sno,Ssex 
FROM Student 
WHERE Sname LIKE '刘%';
 
--查询姓"欧阳"且全名为三个汉字的学生的姓名
SELECT Sname 
FROM Student 
WHERE Sname LIKE '欧阳_';
 
--查询名字中第二个字为"阳"字的学生的姓名和学号
SELECT Sname,Sno 
FROM Student 
WHERE Sname LIKE '_阳%';

当用户要查询的字符串本身就含有通配符%或_，这时就要使用ESCAPE'<换码字符>'短语对通配符进行转义

ESCAPE '\'表示 “\”为换码字符，可以自定义

--查询DB_Design课程的课程号和学分
SELECT Cno,Ccredit 
FROM Course 
WHERE Cname LIKE 'DB\_Design' ESCAPE '\';
 
--查询以"DB_"开头，且倒数第3个字符为 i的课程的详细情况
SELECT * 
FROM Course 
WHERE Cname LIKE 'DB\_%i__' ESCAPE '\';

2.2.5 涉及空值的查询

--查询籍贯为空的学生信息
SELECT *
FROM Student
WHERE BPLACE IS NULL;

 注意：IS不能用“=”代替

2.2.6 多重条件查询

• 逻辑运算符AND和OR可用来连接多个查询条件
• AND的优先级高于OR
• 用括号可以改变优先级

--查询计算机系年龄在20岁以下的学生姓名
SELECT Sname 
FROM Student 
WHERE Sdept = 'CS' AND Sage < 20;
 
--找出年龄小于23岁，籍贯是湖南或湖北的学生的性别和姓名
SELECT Sname,Sex
FROM Student
WHERE Sage<23 and (Bplace='湖南' or Bplace='湖北')

 IN谓词实际上是多个OR运算符的缩写

--查询计算机科学系（CS）、数学系（MA）和信息系（IS）学生的姓名和性别
--方法一：
SELECT Sname,Ssex 
FROM Student 
WHERE Sdept IN ('CS','MA','IS');
--方法二：
SELECT Sname,Ssex 
FROM Student 
WHERE Sdept = 'CS' OR Sdept = 'MA' OR Sdept = 'IS';
 

3. ORDER BY 子句

对查询结果按照一个或多个属性列的升序（ASC）或降序（DESC）排列，默认值为升序

--查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列
SELECT Sno,Grade
FROM SC
WHERE Cno='3'
ORDER BY GRADE DESC;
 
--查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列
SELECT *
FROM Student
ORDER BY Sdept,Sage DESC;

 对于空值排序时显示的次序由具体系统实现来决定

4. 聚集函数 

是针对同类型的一组数据进行统计运算

--查询学生总人数
SELECT COUNT(*) 
FROM Student;
 
--查询选修了课程的学生人数
SELECT COUNT(DISTINCT Sno) 
FROM SC;
 
--计算1号课程的学生平均成绩
SELECT AVG(Grade)
FROM SC
WHERE Cno='1';
 
--查询选修1号课程的学生最高分数
SELECT MAX(Grade)
FROM SC
WHERE Cno='1';
 
--查询学生201215012选修课程的总学分数
SELECT SUM(Ccredit)
FROM SC
WHERE Sno='201215012' AND SC.Cno=Course.Cno;
 
--统计每个学生的选课门数和平均成绩，并按学号的升序显示结果
SELECT Sno,COUNT(*) AS 选课门数,AVG(Grade) AS 平均成绩
FROM SC
GROUP BY Sno
ORDER BY Sno ASC;

当聚集函数遇到空值时，除COUNT(*)外，都跳过空值而只处理非空值

COUNT(*)是对元组进行计数，某个元组的一个或部分列取空值不影响COUNT的统计结果

注意：聚集函数只能用于SELECT子句和GROUP BY中的HAVING子句，WHERE子句中不能用聚集函数作为条件表达式。用聚集函数后，SELECT子句中不能出现其他列名

5. GROUP BY 子句

细化聚集函数的作用对象

• 如果未对查询结果分组，聚集函数将作用于整个查询结果
• 对查询结果分组后，聚集函数将分别作用于每个组
• 按指定的一列或多列值分组，值相等的为一组

--求各个课程号及相应的选课人数
SELECT Cno,COUNT(Sno) 
FROM SC 
GROUP BY Cno;
 
--统计每门课程的选课人数和最高分
SELECT Cno,COUNT(Sno),MAX(Grade)
FROM SC
GROUP BY Cno;

分组之后，目标列表达式不能随便写 ，目标列表达式中只能出现两种数据 

• 第一种是只有分组的列进到目标列表达式，其他列名不能进入目标列表达式
• 第二种就是聚集函数

如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则可以使用HAVING短句指定筛选条件

--查询选修了3门以上课程的学生学号
SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*)>3;
 
--查询平均成绩大于等于90分的学生学号和平均成绩
SELECT Sno，AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>=90;
 
--统计选修两门以上的课程的学生的选课门数和平均成绩，并按学号的升序显示结果
SELECT Sno,COUNT(*) AS 选课门数,AVG(Grade) AS 平均成绩
FROM SC
GROUP BY Sno
ORDER BY Sno ASC
HAVING COUNT(*) >=2;

WHERE子句与HAVING短语的区别在于，作用对象不同。

• WHERE子句作用于基本表或视图，从中选择满足条件的元组。
• HAVING短句作用于组，从中选择满足条件的组。