课本:《数据库原理及其应用教程》（第四版） (主编)黄德才&(副主编)陆亿红
实验：学校实验课材料
其他：
- [ ]表示可以被删除，也表示可以被替换，请自行判断。
- 如果有一些截图或照片，是暂时懒得整理格式，后面有空补。
- 如果遇到了没见过的表、视图、属性什么的，上下自己翻一下，我的顺序一部分是乱的。
目录
- 1 基础知识
- 1.1数据定义
- 1.2数据查询
- 1.2.1一般格式
- 1.2.2单表查询
- 1.2.2.1 SELECT
- 1.2.2.2 DISTINCT
- 1.2.2.2 WHERE
- 1.2.2.3 ORDER BY
- 1.2.2.4 GROUP BY
- 1.2.2.5 HAVING
- 1.2.3表达式
- 1.2.3.1增加列标题
- 1.2.3.2函数
- 1.2.3.3字符串常量
- 1.2.3.4算术表达式
- 1.2.4连接查询
- 1.2.5嵌套查询
- 1.2.6集合运算
- 实验例子
- 数据定义
- 数据查询
- 单表查询
- 连接查询
- 笛卡儿连接和等值连接。
- 自连接。
- 外连接
- 复合条件连接。
- 多表连接。
- 嵌套查询
- 通过实验验证对子查询的两个限制条件。
- 体会相关子查询和不相关子查询的不同。
- 考察四类谓词的用法，包括：
- 集合运算
- 使用保留字UNION进行集合并运算。
- 采用逻辑运算符AND或OR来实现集合交和减运算。
1 基础知识
1.1数据定义
1.2数据查询
1.2.1一般格式
SELECT[ALL|DISTINCT] <目标列表达式>[,<目标列表达式>]//规定属性列
FROM <表名或视图名>[,<表名或视图名>]//元组的来源
[WHERE <条件表达式>]//设定条件
[GROUP BY <列名1>[HAVING<条件表达式>]]//分组
[ORDER BY <列名2>[ASC|DESC]]//排序
1.2.2单表查询
1.2.2.1 SELECT
【查询所有列】
SELECT *
fROM [students]
【查询指定的列】
SELECT [Sname,Sno,Dno]
FROM [Students]
【查询指定的列的运算】
见1.2.3
1.2.2.2 DISTINCT
消去查询结果表中相同的元组
eg.查询选修了课程的学生学号（这样子学号就不会重复出现了）
SELECT DISTINCT Sno
FROM Reports
1.2.2.2 WHERE
BETWEEN AND / NOT BETWEEN AND
eg.WHERE Sage NOT BETWEEN 18 AND 20//Sage年龄
IN / NOT IN
eg. WHERE Dno IN('D01','D02','D03')
LIKE / NOT LIKE 匹配
[NOT] LIKE '<匹配串>'[ESCAPE '<换码字符>']
%百分号：任意长度字符串
_下划线：任意单个字符
eg.姓刘的
WHERE Sname LIKE '刘%'
eg.姓刘的，且全名不多于3个汉字
WHERE Sname LIKE '刘__'//两个下划线
ESCAPE ‘<换码字符>’
如果查询的字符串本身带有%或者_
eg.查询课程名为'DB_设计'的课程
WHERE Cname LIKE 'DB\_设计' ESCAPE '\'//换码字符常用\
IS NULL / IS NOT NULL
eg,有一门课没有成绩
WHERE Grade IS NULL
COUNT
【COUNT(*) 统计元组个数】
eg.查询学生总人数
SELECT COUNT(*)
FROM Students
【COUNT(列名) 统计列值个数】
eg.查询选修了课程的学生人数
SELECT COUNT(DISTINCT Sno)
FROM Reports
1.2.2.3 ORDER BY
eg.按系别编号升序，编号相同按年龄降序排序
ORDER BY Dno，Sage DESC//DESC表示降序，没有的话默认升序
对于空值
1、不是长度为0的字符串
2.升序的话，空值在最前面
1.2.2.4 GROUP BY
将查询结果表按某一列或多列值分组
eg.求各个课程号以及相应的选课人数
SELECT Cno,COUNT(Sno) CntSno
FROM Reports
GROUP BY Cno
//把选了相同Cno的划分为一组，然后计算每一组中Sno有几个
1.2.2.5 HAVING
分组后还要筛选的话，只能作用于组
eg.查询选修了三门及以上课程的学生学号
SELECT Sno
FROM Reports
GROUP BY Sno
HAVING COUNT(Cno) >= 3
1.2.3表达式
可以是表中的属性列、函数、字符串常量、算术表达式等
1.2.3.1增加列标题
直接在目标列表达式后面加上列名即可
SELECT Sno,YEAR（Birthday） BirthY,Sname
FROM Students
1.2.3.2函数
- 日期
YEAR（Birthday）//获得日期属性里面的年数值的函数
MONTH（Birthday）
DAY（Birthday）
eg.
SELECT Sno,Sname,YEAR（Birthday）
FROM Students
GETDATE()//获取系统当前日期
DATE(yyyy,GETDATE())//获得当前提起的年数值的函数
DATE(mm,GETDATE())
DATE(dd,GETDATE())
eg.查询所有年龄大于等于18岁的学生姓名和各自的年龄
SELECT Sname,DATE(yyyy,GETDATE())-YEAR（Birthday） Sage//用现在的年份减去这个人出生年份得到年龄
FROM Students
WHERE DATE(yyyy,GETDATE())-YEAR（Birthday）>=18
- List item
1.2.3.3字符串常量
这一列所有的属性值都会是这个字符串常量
SELECT Sno,Sname,‘Birth:’ Title
FROM Students
1.2.3.4算术表达式
1.2.4连接查询
笛卡儿连接和等值连接。
自连接。
外连接
复合条件连接。
多表连接。
1.2.5嵌套查询
通过实验验证对子查询的两个限制条件。
体会相关子查询和不相关子查询的不同。
考察四类谓词的用法，包括：
第一类，IN、NOT IN；
第二类，带有比较运算符的子查询；
第三类，SOME、ANY或ALL谓词的子查询，查询最大值和最小值；
第四类，带有EXISTS谓词的子查询，实现“所有”等情况（如王宏的“所有”课程，“所有”女生选修的课程）
1.2.6集合运算
使用保留字UNION进行集合并运算。
采用逻辑运算符AND或OR来实现集合交和减运算。
实验例子
数据定义
学校实验要求命名加上自己的名字，给库、表、视图、属性命名
之后的例子中名字首字母会时有时没有，大家自行理解一下。
| 表名 | 表名 | 属性 |
|---|---|---|
| 学生表 | Pansx_Students | Sno学号,Sname, Semail,Scredit学分,Ssex性别 |
| 教师表 | Pansx_Teachers | Tno工号,Tname,Temail,Tsalary薪水 |
| 课程表 | Pansx_Courses | Cno,Cname,Ccredit课程学分 |
| 成绩表 | Pansx_Reports | Sno,Tno,Cno,Score |
其中：
psx_Sno、psx_Tno、psx_Cno分别是表Pansx_Students、表Pansx_Teachers、表Pansx_Courses的主键，具有唯一性约束；
psx_Scredit具有约束“大于等于0”；
Pansx_Reports中的psx_Sno,psx_Tno,psx_Cno是外键，它们共同组成Pansx_Reports的主键。
数据查询
先插入一些数据，方便进行查询
【Students】
【Teachers】
【Courses】
【Reports】
单表查询
（1） 查询性别为“男”的所有学生的名称并按学号升序排列。
SELECT psx_sname
FROM pansx_students
WHERE psx_ssex='男'
ORDER BY psx_sno
&（2） 查询学生的选课成绩合格的课程成绩，并把成绩换算为积分。积分：[1+(考试成绩-60)*0.1]*Ccredit。考试成绩>=60 否则=0
select psx_cname,(1+(psx_score-60)*0.1)*psx_ccredit as psx_point
FROM pansx_reports
JOIN pansx_courses ON pansx_courses.psx_cno=pansx_reports.psx_cno
WHERE psx_score>=60
（3） 查询学分是3或4的课程的名称。
SELECT psx_cname
FROM pansx_courses
WHERE psx_ccredit= 3 or 4
（4） 查询所有课程名称中含有“算法”的课程编号。
SELECT psx_cno
FROM pansx_courses
WHERE psx_cname LIKE '%算法%'
（5） 查询所有选课记录的课程号（不重复显示）。
SELECT DISTINCT psx_cno
FROM pansx_courses
（6） 统计所有老师的平均工资。
SELECT AVG(psx_tsalary)
FROM pansx_teachers
&（7） 查询所有教师的编号及选修其课程的学生的平均成绩，按平均成绩降序排列。
SELECT psx_tno,psx_cno,ROUND(AVG(psx_score),2) AS psx_avg
FROM pansx_reports
GROUP BY psx_cno,psx_tno
ORDER BY psx_avg DESC
（8） 统计各个课程的选课人数和平均成绩。
SELECT psx_cno,COUNT(psx_sno),AVG(psx_score)
FROM pansx_reports
GROUP BY psx_cno
连接查询
笛卡儿连接和等值连接。
【【等待分类！！！】】
（9） 查询至少选修了三门课程的学生编号和姓名。
SELECT pansx_students.psx_sno,psx_sname
FROM pansx_students
JOIN pansx_reports ON pansx_reports.psx_sno=pansx_students.psx_sno
GROUP BY pansx_students.psx_sno
HAVING COUNT(psx_cno)>=3
（10） 查询编号S26的学生所选的全部课程的课程名和成绩。
SELECT pansx_courses.psx_cname,psx_score
FROM pansx_students
JOIN pansx_reports ON pansx_reports.psx_sno=pansx_students.psx_sno
JOIN pansx_courses ON pansx_courses.psx_cno=pansx_reports.psx_cno
WHERE pansx_students.psx_sno='S26'
&（11） 查询所有选修了“数据库原理及应用”课程的学生编号和姓名。
SELECT pansx_students.psx_sno,psx_sname
FROM pansx_students
JOIN pansx_reports ON pansx_reports.psx_sno=pansx_students.psx_sno
JOIN pansx_courses ON pansx_courses.psx_cno=pansx_reports.psx_cno
WHERE psx_cname='数据库原理及应用'
&（12） 求出选修了同一个课程的学生对
SELECT DISTINCT a.psx_sno,b.psx_sno
FROM pansx_reports a,pansx_reports b
where a.psx_cno=b.psx_cno AND a.psx_sno <b.psx_sno
（13） 求出至少被两名学生选修的课程编号。
SELECT psx_cno
FROM pansx_reports
GROUP BY psx_cno
HAVING COUNT(psx_sno)>=2
（14） 查询选修了编号S26的学生所选的某个课程的学生编号。
SELECT DISTINCT psx_sno
FROM pansx_reports
WHERE psx_cno IN (
SELECT psx_cno
FROM pansx_reports
WHERE psx_sno='S26'
)
（15） 查询学生的基本信息及选修课程编号和成绩。
SELECT pansx_students.*,pansx_reports.psx_cno,psx_score
FROM pansx_students
JOIN pansx_reports ON pansx_reports.psx_sno=pansx_students.psx_sno
JOIN pansx_courses ON pansx_courses.psx_cno=pansx_reports.psx_cno
&（16） 查询学号S52的学生的姓名和选修的课程名称及成绩。
SELECT psx_sname,psx_cname,psx_score
FROM pansx_students
JOIN pansx_reports ON pansx_reports.psx_sno = pansx_students.psx_sno
JOIN pansx_courses ON pansx_courses.psx_cno = pansx_reports.psx_cno
WHERE pansx_reports.psx_sno='S52'
&（17） 查询和学号S52的学生同性别的所有学生资料。
SELECT *
FROM pansx_students
WHERE psx_sno!='S52' AND psx_ssex=(
SELECT psx_ssex
FROM pansx_students
WHERE psx_sno='S52'
)
（18） 查询所有选课的学生的详细信息。
SELECT DISTINCT pansx_students.*
FROM pansx_students
JOIN pansx_reports ON pansx_reports.psx_sno = pansx_students.psx_sno
WHERE pansx_students.psx_sno IN (
SELECT DISTINCT psx_sno
FROM pansx_reports
)
&（19） 查询没有学生选的课程的编号和名称。
SELECT psx_cno,psx_cname
FROM pansx_courses
WHERE psx_cno NOT IN(
SELECT psx_cno
FROM pansx_reports
)
（20） 查询选修了课程名为C++的学生学号和姓名。
SELECT DISTINCT pansx_students.psx_sno,psx_sname
FROM pansx_students
JOIN pansx_reports ON pansx_reports.psx_sno = pansx_students.psx_sno
JOIN pansx_courses ON pansx_courses.psx_cno = pansx_reports.psx_cno
WHERE psx_cname='C++'
（21） 找出选修课程UML或者课程C++的学生学号和姓名。
SELECT DISTINCT pansx_students.psx_sno,psx_sname
FROM pansx_students
JOIN pansx_reports ON pansx_reports.psx_sno = pansx_students.psx_sno
JOIN pansx_courses ON pansx_courses.psx_cno = pansx_reports.psx_cno
WHERE psx_cname='C++' OR psx_cname='UML'
（22） 找出和课程UML或课程C++的学分一样课程名称。
SELECT DISTINCT psx_cname
FROM pansx_reports
JOIN pansx_courses ON pansx_courses.psx_cno = pansx_reports.psx_cno
WHERE psx_cname NOT IN ('C++','UML') AND psx_ccredit IN (
SELECT psx_ccredit
FROM pansx_courses
WHERE psx_cname = 'C++' OR psx_cname ='UML'
)
（23） 查询所有选修编号C01的课程的学生的姓名。
SELECT DISTINCT psx_sname
FROM pansx_reports
JOIN pansx_students ON pansx_students.psx_sno = pansx_reports.psx_sno
WHERE psx_cno = 'C01'
&（24） 查询选修了所有课程的学生姓名。
SELECT psx_sname
FROM pansx_students
WHERE NOT EXISTS(
SELECT psx_cno
FROM pansx_courses
WHERE NOT EXISTS(
SELECT psx_sno
FROM pansx_reports
WHERE pansx_reports.psx_sno=pansx_students.psx_sno
AND pansx_reports.psx_cno=pansx_courses.psx_cno
)
)
（25） 利用集合并运算，查询选修课程C++或选择课程JAVA的学生的编号、姓名和积分。
SELECT pansx_students.psx_sno, psx_sname, psx_scredit
FROM pansx_students
JOIN pansx_reports ON pansx_reports.psx_sno = pansx_students.psx_sno
JOIN pansx_courses ON pansx_courses.psx_cno = pansx_reports.psx_cno
WHERE psx_cname = 'C++'
UNION
SELECT pansx_students.psx_sno, psx_sname, psx_scredit
FROM pansx_students
JOIN pansx_reports ON pansx_reports.psx_sno = pansx_students.psx_sno
JOIN pansx_courses ON pansx_courses.psx_cno = pansx_reports.psx_cno
WHERE psx_cname = 'JAVA';
（26） 实现集合交运算，查询既选修课程C++又选修课程JAVA的学生的编号、姓名和积分。
同（25）将UNION改成INTERSECT即可
（27） 实现集合减运算，查询选修课程C++而没有选修课程JAVA的学生的编号。
同（25）将UNION改成 MINUS 即可
自连接。
代码
外连接
代码
复合条件连接。
代码
多表连接。
代码
嵌套查询
通过实验验证对子查询的两个限制条件。
代码
体会相关子查询和不相关子查询的不同。
代码
考察四类谓词的用法，包括：
代码
第一类，IN、NOT IN；
第二类，带有比较运算符的子查询；
第三类，SOME、ANY或ALL谓词的子查询，查询最大值和最小值；
第四类，带有EXISTS谓词的子查询，实现“所有”等情况（如王宏的“所有”课程，“所有”女生选修的课程）
集合运算
使用保留字UNION进行集合并运算。
代码
采用逻辑运算符AND或OR来实现集合交和减运算。
代码
![[lesson24]经典问题解析二](https://img-blog.csdnimg.cn/direct/d0c949d9eab24ac8b9f711040b7edba4.png#pic_center)
![[StartingPoint][Tier2]Included](https://img-blog.csdnimg.cn/img_convert/38f16309a0218758905767f3d2ad38a9.jpeg)
