目录
一般格式:
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子句实现
查询条件 | 谓词 |
---|---|
比较 | =,>,<,>=,<=,!=,<>,!>,!<;NOT+上述比较运算符 |
确定范围 | BETWEEN AND,NOT BETWEEN AND |
确定集合 | IN,NOT IN |
字符匹配 | LIKE,NOT LIKE |
空值 | IS NULL,IS NOT NULL |
多重条件 | AND,OR,NOT |
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. 聚集函数
是针对同类型的一组数据进行统计运算
COUNT(*) | 统计元组个数 |
COUNT( [ALL | DISTINCT] <列名>) | 统计一列中值的个数 |
SUM( [ALL | DISTINCT] <列名>) | 计算一列值的总和(此列必须是数值型) |
AVG( [ALL | DISTINCT] <列名>) | 计算一列值的平均值(此列必须是数值型) |
MAX( [ALL | DISTINCT] <列名>) | 求一列值中的最大值 |
MIN( [ALL | DISTINCT] <列名>) | 求一列值中的最小值 |
--查询学生总人数
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短句作用于组,从中选择满足条件的组。