【数据库原理及应用教程】第三章 SQL

重点难点

• SQL-SELECT：In ｜ Not In, Some, all, exists | not exists
• SQL-SELECT：聚集函数，Group By, Having
• 视图及其应用

建立数据库

学生选课数据库SCT

学生表：Student(SNo char(8), Sname char(10), Ssex char(2), Sage integer, DNo, char(2), Sclass char(6))

院系表：Dept(DNo char(2), Dname char(10), Dean char(10))

课程表：Course(CNo char(3), Cname char(12), Chourse integer, Credit float(1), TNo char(3))

教师表：Teacher(TNo char(3), Tname char(10), DNo char(2), Slaary float(2))

选课表：SC(SNo char(8), CNo char(3), Score float(1))

建立数据库包括：定义数据库和表（使用DDL），向表中追加元组（DML），两件事。

DDL：创建database，创建table，定义.约束.View.Index…；删除，drop；更改，Alter。
DML：insert, delete, select, update
DCL：授权/取消授权，Grant, Revoke

DDL通常由DBA使用，也可以DBA授权之后的程序

创建数据库

表达式：

Create database 数据库名

示例：创建SCT

Create database SCT

定义数据库的同时，也可以定义很多物理特性，这里指介绍最简单的定义。然后可以创建表。
表达式：

Create table 表名（列名 数据类型[primary key|unique][Not null]
					[列名 数据类型],...）

定义表的时候，最少要有一列

• Primary Key每个表只能创建一个主键约束
• Unique唯一性约束（候选键），可以有多个
• Not null非空约束。该列不允许出现空值
• 其它
  示例：定义学生表Student

Create Student(SNo char(10) not null, Sname char(8), Ssex char(2), Sage integer, DNo char(2), Sclass char(6));

选择元组select

表达式

Select 列名     				（子句）
	From 表明				（子句）
	Where 检索条件			（子句）

此操作就相当于对一个关系，进行条件查找后再进行投影。
示例：检索学生表中所有学生的信息

Select * 
	From Student;

示例：检索学生表中所有学生的姓名及年龄

Select Sname, Sage
	From Student

示例：检索学生表中所有年龄大于等于19岁的学生的年龄及姓名

Select Sage, Sname
	From Student
	Where Sage >= 19;

关键：检索条件书写
not, and, or运算优先级

示例：检索教师表中所有工资少于1500元或者工资大于2000元 并且是03系的教师姓名

Select Tname
	From Teacher
	Where (Salary < 1500 or Salary > 2000) and DNo = '03'; 
	#注意 not, and, or 的运算优先级

示例：求或者学过001号课程，或者学过002号课程学生的学号

Select
	From SC
	Where CNo='001' or CNo = '002';

示例：求既学过001课程，又学过002课程学生的学号

Select SNo
	From SC as SC1, SC as SC2
	Where SC1.SNo=SC2.SNo and SC1.CNo = '001' and SC2.CNo = '002';
#下面的查询结果是空，也就是select语句书写有问题
Select SNo # 检索结果为空
	From SC
	Where CNo='001' and CN0='002'; # 不可能既等于001，又等于002

结果唯一性问题

• 关系模型不允许出现重复的元组，但是现实的DMBS中，允许出现重复元组
• 在Table中，要求无重复元组是通过Primary Key或Unique保证
• 在检索结果中，要求无重复元组，通过DISTINCT保留字实现
  示例：在选课表中，检索成绩大于80分的所有学号

Select DISTINCT SNo
	From SC
	Where Score > 80;

结果排序问题
在select语句中增加order by子句实现排序功能，asc表示升序，desc表示降序
示例：按学号由小到大的顺序显示出所有学生的学号及姓名

Select SNo, SName
	From Student
	Order By SNo asc, Sname asc

示例：检索002号课程大于80分的所有同学学号并按成绩由高到低排序

Select SNo
	From SC
	Where Score > 80 and DNo = ='002'
	Order By Score desc;

模糊查询问题
%：匹配0个或多个字符
_：匹配任意单一字符
****：转义字符。比如只想检索百分号，而不是百分号当作通配符使用。

多表查询

多表查询通过连接运算完成，连接运算通过笛卡尔积完成。将连接条件放到Where子句中即可完成。
示例：按“001”号课程成绩由高到低顺序显示所有写上姓名（两表）

Select Student.SName
	From SC, Student
	Where SC.SNo=Student.SNo and SC.CNo='001'
	Order By SC.CNo desc;

示例：按‘数据库’课程成绩由高到低顺序显示所有同学姓名（三表）

Select Student.SName
	From Course, SC, Student
	Where Course.CNo=SC.CNo and SC.SNo=Student.SNo and Course.CName='数据库'
	Order By SC.Score;

对同一个表进行连接操作：对同一个表，（From子句中）使用不同的别名进行处理
示例：求有薪水差额的任意两位教师

Select T1.TName, T2.TName
	From Teacher T1, Teacher T2
	Where T1.Salary > T2.Salary;

示例：求‘001’号课程有成绩差的任意两位同学

Select S1.SName, S2.SName
	From SC as SC1, SC as SC2, Student as S1, Student as S2
	Where S1.SNo=SC1.SNo and S2.SNo=SC2.SNo and SC1.Score > SC2.Score

示例：求既学过’001’号课又学过‘002’号课的所有学生的学号

Select S1.SNo
	From SC as S1, SC as S2
	Where S1.SNo=S2.SNo and S1.CNO='001' and S2.CNo='002';

示例：列出没学过李明老师教授课程的所有同学姓名？

Select Student.SName
	From Student, Teacher, Course, SC
	Where Course.CNo = Teacher.CNo and Course.CNo=SC.CNo and Course.SNo = Student.SNo and (not Teacher.TName='李明')
	

子查询

为什么需要子查询？
1、集合成员资格查询：某一元素是否是某一个集合成员
2、集合之间比较：两个集合之间进行比较
3、集合基测试：测试集合是否为空、测试集合是否存在重复元组？

子查询：出现在Where子句中的Select语句被称为子查询。子查询返回一个集合。
三种类型子查询：In | Not In , ⊝-Some | ⊝-ALL, Exists | Not Exists
1、【In | Not In】 子查询

表达式	[not] in (子查询)

判断某一表达式的值是否在子查询的结果中
示例：列出张三、王三同学的所有信息

Select * 
	From Student
	Where SName="张三" or SName ="王三";
	Where SName in ("张三", "王三");

示例：列出选修001号课程的学生的学号和姓名

Select SNo, SName
	From Student
	Where SNo in 
		(select S# From SC Where CNo = '0001');

示例：求既学过001号课程，又学过002号课程的学生学号

Select SNo
	From SC
	Where CNo ='002' and SNo in # 依旧使用SNo进行连接查询
		(Select SNo From SC Where CNo == '002);
		

同一种查询多种书写方式：用户书写更灵活。

示例：列出没学过李明老师讲授课程的所有同学的姓名？

错误示范
Select	SName
	From Student
	Where DNo not in
		(Select DNo From Teacher 
		Where TName = '李明')
正确示范
Select SName 
	From Student
	Where SNo not in 
	(Select SNo From SC, Student, Teacher
	Where SC.SNo=Student.SNo and SC.TNo=Teacher.TNo and Teacher.name = '李明');

子查询分为内层查询和外层查询，同样受到定义域约束，将子查询分为两个部分：
（1）非相关子查询：内层查询中，不使用外层查询的变量
（2）相关子查询：内层查询需要依靠外层查询的变量（只能由外层查询向内层传递参数，不能内层向外层传递参数）

2、【⊝-some 和⊝-all】子查询
语法中，⊝表示比较运算符：<, >, >=, <=, =, <>
将表达式的值与子查询的结果进行比较：
（1）与子查询某一个（不是全部）比较下，满足⊝关系，为⊝-some。存在量词
（2）与子查询结果所有值进行比较，全部满足⊝关系，为⊝-all。全称量词
示例：找出工资最低的教师姓名

Select TName
	From Teacher
	Where Salary <= all(Select Salary From Teacher)

示例：找出001号课成绩不是最高的所有学生的学号

Select SNo
	From SC
	Where CNo='001' and Score < some(
	Select Score From SC Where CN0='001);

示例：找出所有课程都不及格的学生姓名

Select SName
	From Student
	Where SNo = all(.      Where 60 > all (Select Score From SC Where SNo = Student.SNo)
	Select Student.SNo 
		From SC,Student 
		Where SC.SNo=Student.SNo and Score < 60);

示例：找出001号课程成绩最高的所有学生学号

Select SNo
	From SC
	Where CNo='001' and Score >= all(
	Select Score From SC where CNo='001');

示例：找出98030101号同学成绩最低的课程号

Select CNo
	From SC
	Where SNo='98030101' and Score <= all(
	Select Score From SC Where SNo='98030101');

示例：找出张三同学成绩最低的课程号

等价变换：
“表达式= some（子查询）” 和 “表达式 in（子查询）”
“表达式<> all（子查询）” 和 “表达式 not in（子查询）”
3、【Exists | Not Exists】
子查询结果中有无元组存在

追加元组 insert

表达式：

insert into 表明【列名...】
		values （值...）

【注意】需要值与列一一对应
示例：向Student表中追加元组

insert into Student
	values('98030101', '张三', '男', '20', '03', '980301');

嵌套子查询

将values换成select…from…where子句
示例：新建立Table：St(SNo, SName），将以“伟”结尾的同学新增到该表中

Insert into St(SNo, SName)
	Select SNo, SName
		From Student
		Where SName like '%伟';

【注意】新增元组时，DMBS会检查用户定义的完整性约束条件，如不符合完整性约束条件，则不会执行新增动作。

元组删除命令 delete

表达式：

Delete From 表名 [Where 条件表达式];

如果Where条件省略，则删除所有的元组
Select…From…Where句子很重要
1、当不写Where时，就是删除所有元组
2、Where满足条件的元组
示例：删除SC表中所有元组

Delete From SC;

示例：删除98030101号同学所选的所有课程

Delete From SC Where SNo = '98030101';

示例：【删除自动控制系的所有同学】：嵌套语句，属于子查询部分（属于后面内容）。

Delete From Student Where in
	(Select DNo From Dep Where Dname='自动控制');

【更复杂的条件删除语句】
示例：删除有四门不及格课程的所有同学

Delete From Student Where SNo in
	(Select SNo From SC Where Score<60
	Group by SNo Having Count(*)>=4);

• 此SELECT语句书法语法后面解释
• 当删除元组时，DBMS会检查用户定义的完整性约束条件，如不符合完整性约束条件，则不会执行删除动作

**Select…From…Where…**语句很重要

Update命令

元组更新Update命令：用指定要求的值更新指定表中满足指定条件的元组

Update 表名
	Set 列名 = 表达式 ｜ （子查询）
		[[,列名=表达式｜（子查询）]]
		[Where 条件表达式];

如果Where 条件省略，则更新所有的元组

示例：将所有教师工资上调5%

Update Teacher
	Set Sal = Sal + Sal * 0.05;

示例：将所有计算机系教师工资上调10%

Update Teacher
	Set Sal = Sal + Sal*0.1
	Where DNo in
		(Select DNo From Department WHere Dname='计算机');

结果计算

示例：求有差额（差额>0）的任意两位教师的薪水差额

Select T1.TName, T2,TName, T1.Salary-T2.Salary
	From Teacher as T1, Teacher as T2
	Where T1.Salary > T2.Salary;

示例：依据学生年龄求学生的出生年份，当前是2015年

Select 2015-SAge
	From Student;

聚集函数

在Select子句中，使用聚集函数
Count：求个数
sum：求和
avg：求平均
max：求最大值
min：求最小值
示例：求所有教师工资的总额

Select Sum(Salary) From Teacher;

示例：求计算机系教师工资总额

Select sum(Salary)
	From Teacher
	Where TDept = '计算机系';

如何使用一条语句，实现：求每一门课的平均成绩？求每一位学生的平均成绩？

分组查询和分组过滤

分组：将检索到的元组按照某一条件进行分类
在Where子句后面，增加Group By
分组条件可以是：列名1，列名2，…
示例：求每一学生的平均成绩

Select SNo, avg(Score)
	From SC
	group by SNo; # 根据学生进行分组

示例：求每一门课程的平均成绩

Select CNo, avg(Score)
	From SC
	group by CNo;

问：如何查找超过两门不及格的同学学号？

Select SNo
	From SC
	Where Score < 60 and Count(*)>2 # 错误，因为where子句不能加聚合函数
	Group by SNo;

分组过滤

对集合（分组）的条件进行过滤。即：满足条件的集合留下，不满足条件的集合删除。
需要先分组，再过滤。所以只能在Group by子句后面。

Select SNo
	From SC
	Where Score < 60
	Group by SNo Having Count(*)>2;

示例：求有10人以上不及格的课程号

Select CNo
	From SC
	Where Score < 60
	Group by CNo having count(*)>10

每一分组检查满足与否的条件要用Having子句
每一行都检查是否满足条件用Where子句
语句执行：from where select group by having

修正与撤销数据库

SQL-DDL撤销与修改

**修正数据库：**修正数据库的定义，主要是修正表的定义
修正基本表的定义

Alter table tablename
ADD {columnname datatype,...}
drop 约束
modify {columnname datatype,...};

示例：在学生表Student(SNp, Sname, Ssex, Sage, DNo, Scalss)基础上，增加两列：Saddr, PID

Alter table Student ADD Saddr char(40), PID char(18);

示例：将上例表中Sname列的数据类型（原char(8)）增加两个字符

Alter table Student Modify Sname char(10);

示例：删除学生姓名必须取名唯一值的约束

Alter table Student Drop Unique(Sname);

撤销基本表

drop table 表名;

【注意】

1、Delete...From...是删除表中的元组，并不删除表；Drop table 是撤销基本表，包括表的格式、表中所有元组、由该表导出的视图等相关所有内容。 2、只有Delete From，没有Delete table 3、是Drop table

drop database 数据库名;

指定数据库

use 数据库名;

关闭数据库

close 数据库名;

视图

三级模式 — SQL
外模式 – 视图
模式 ---- 基本表

SQL语言的视图：外模式+外模式/模式映像

• 基本表是实际存储于存储文件的表，基本表中的数据是需要存储的
• 视图由基本表导出，数据并不保存
• 对视图数据的更改反应在基本表上

定义视图

create view view_name
	as 子查询[with check option]

使用视图的目的：提升数据的安全性，能够一定程度上防止用户对数据的失误操作。

不可更新：
使用聚集函数、unique\distince、group by、经过计算表达式得到的列、单个表但是没有主键。