数据库系统概论【三】

发表于 更新于 分类于   |   字数统计: 7.7k(字)   |   阅读时长≈ 30(分)

导图

一、SQL语言概述

1、SQL语言的功能概述

  • DDL（Data Definition Languages）语句：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括 create、drop、alter等。
  • DML（Data Manipulation Language）语句：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的语句关键字主要包括 insert、delete、udpate 和select 等。(增添改查）
  • DCL（Data Control Language）语句：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括 grant、revoke 等。

SQL由分为：交互式SQL、嵌入式SQL、动态SQL等。

之后学习举例使用的数据库：

  • 学生：学号S#, 姓名Sname, 性别Ssex, 年龄Sage, 所属系别D#，班级Sclass

    Student ( S# char(8), Sname char(10), Ssex char(2),Sage integer, D# char(2), Sclass char(6) )

  • 院系：系别D#，系名Dname, 系主任Dean

    Dept ( D# char(2), Dname char(10), Dean char(10) )

  • 课程：课号C#, 课名Cname, 教师编号T#, 学时Chours，学分Credit

    Course ( C# char(3), Cname char(12), Chours integer,Credit float(1), T# char(3) )

  • 教师：教师编号T#，教师名Tname, 所属院系D#，工资Salary

    Teacher ( T# char(3), Tname char(10), D# char(2),Salary float(2) )

  • 选课：学号S#, 课号C#, 成绩Score

    SC ( S# char(8), C# char(3), Score float(1) )

2、利用SQL语言建立数据库

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

(1)SQL-DDL

DDL具有很多功能，这里我们学习简单的创建数据库，后面功能再陆续介绍

(2)创建数据库的语句—Create Database

数据库(Database)是若干具有相互关联关系的Table/Relation的集合, 数据库可以看作是一个集中存放若干Table的大型文件

示例：创建课程学习数据库SCT

1
create database SCT;

Create Database的其他特性例如定义完整性约束等，之后介绍

(3)创建关系/表的语句—Create Table

示例：定义学生表 Student

1
Create Table Student ( S# char(8) not null , Sname char(10),Ssex char(2), Sage integer, D# char(2), Sclass char(6) );

示例：定义课程表Course

1
Create Table Course ( C# char(3) , Cname char(12), Chours integer,Credit float(1), T# char(3) );

  • “ [ ] ”表示其括起的内容可以省略，“ | ” 表示其隔开的两项可取其一

  • Primary key: 主键约束。每个表只能创建一个主键约束。

  • Unique: 唯一性约束(即候选键)。可以有多个唯一性约束。

  • Not null: 非空约束。是指该列允许不允许有空值出现，如选择了Not null表明该列不允许有空值出现。

  • 在SQL-92标准中定义的数据类型：

    • char (n) :固定长度的字符串
    • varchar (n) :可变长字符串
    • int :整数 // 有时不同系统也写作integer
    • numeric (p，q) :固定精度数字，小数点左边p位，右边p-q位
    • real :浮点精度数字 //有时不同系统也写作float(n)，小数点后保留n位
    • date :日期 (如 2003-09-12)
    • time : 时间 (如 23:15:003)
    • …..

现行商用DBMS的数据类型有时和上面有些差异，请注意;和高级语言的数据类型，总体上是一致的，但也有些差异。

(4)SQL-DML

(5)向表中追加元组的值 –Insert Into

示例：追加学生表中的元组

1
2
3
Insert Into Student Values ( ‘98030101’ , ‘张三’, ‘男’, 20， ’03’， ‘980301’);
Insert Into Student ( S#, Sname, Ssex, Sage, D# , Sclass)
Values ( ‘98030102’ , ‘张四’, ‘女’, 20， ’03’， ‘980301’);

示例：追加课程表中的元组

1
2
Insert Into Course Values ( ‘001’ , ‘数据库’, 40, 6， ’001’); /*所有列名省略，须与定义或存储的列名顺序一致*/
Insert Into Course(Cname, C#, Credit, Chours, T#) Values (‘数据库’, ‘001’, 6, 40, ‘001’); /*如列名未省略，须与语句中列名的顺序一致*/
  • values后面值的排列，须与into子句后面的列名排列一致
  • 若表名后的所有列名省略， 则values后的值的排列，须与该表存储中的列名排列一致

3、利用SQL语言进行简单查询

(1)单表查询-Select-From-Where

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

1
2
3
Select Tname
From Teacher
Where (Salary < 1500 or Salary > 2000) and D# = ’03’;

  • 语义：从表名所给出的表中，查询出满足检索条件的元组，并按给定的列名及顺序进行投影显示。相当于对表进行了选择和投影操作。

  • Select语句中的select … , from… , where…, 等被称为子句，在以上基本形式基础上会增加许多构成要素，也会增加许多新的子句，满足不同的需求。

  • 如投影所有列，则可以用*来简写

  • 逻辑运算符用 and , or,not 来表示, 同时也要注意运算符的优先次序及括弧的使用。书写要点是注意
    对自然语言检索条件的正确理解。

(2)检索结果之去重复记录-Select Distinct-From-Where

关系模型不允许出现重复元组。但现实DBMS，却允许出现重复元组，但也允许无重复元组。

在Table中要求无重复元组是通过定义Primary key或Unique来保证的;而在检索结果中要求无重复元组, 是通过DISTINCT保留字的使用来实现的。

示例：在选课表中，检索成绩大于80分的所有学号

1
2
3
4
Select DISTINCT S#
From SC
Where Score > 80;
//重复元组被DISTINCT过滤掉，只保留一份

(3)检索结果之排序-Select-From-Where-Order By

DBMS可以对检索结果进行排序，可以升序排列，也可以降序排列。

ascend升序|descend降序，默认为升序

示例：按学号由小到大的顺序显示出所有学生的学号及姓名

1
2
Select S#, Sname From Student
Order By S# ASC ;

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

1
2
Select S# From SC Where C# = ‘002’ and Score > 80
Order By Score DESC ;

​

(4)模糊查询-SELECT-FROM-WHERE * LIKE *

模糊查询问题:比如检索姓张的学生，检索张某某；这类查询问题，Select语句是通过在检索条件中引入运算符like来表示的

找出匹配给定字符串的字符串。其中给定字符串中可以出现%, _等匹配符.

匹配规则：

  • “%” 匹配零个或多个字符
  • “_” 匹配任意单个字符
  • “ \ ” 转义字符，用于去掉一些特殊字符的特定含义，使其被作为普通字符看待, 如用 “%”去匹配字符%，用\_ 去匹配字符_

示例：检索所有姓张的学生学号及姓名

1
2
Select S#, Sname From Student
Where Sname Like ‘张%’ ;

示例：检索名字为张某某的所有同学姓名

1
2
Select Sname From Student
Where Sname Like ‘张_ _’;

示例：检索名字不姓张的所有同学姓名

1
2
Select Sname From Student
Where Sname Not Like ‘张%’;

4、利用SQL语言进行多表联合查询

多表联合检索可以通过连接运算来完成，而连接运算又可以通过广义笛卡尔积后再进行选择运算来实现。检索条件中要包含连接条件，通过不同的连接条件可以实现等值连接、不等值连接及各种连接

注意:检索条件的设定决定了何种连接

(1)表更名与表别名(重名处理)

连接运算涉及到重名的问题，如两个表中的属性重名，连接的两个表重名(同一表的连接)等，因此需要使用别名以便区分

  • 上述定义中的as 可以省略
  • 当定义了别名后，在检索条件中可以使用别名来限定属性
  • 有时表名很长时，为书写条件简便，也定义表别名，以简化书写

(2)等值连接

示例：按“001”号课成绩由高到低顺序显示所有学生的姓名(二表连接)

1
2
3
Select Sname From Student, SC
Where Student.S# = SC.S# and SC.C# = ‘001’
Order By Score DESC;

示例：按‘数据库’课成绩由高到低顺序显示所有同学姓名(三表连接)

1
2
3
Select Sname From Student, SC, Course
Where Student.S# = SC.S# and SC.C# = Course.C# and Cname = ‘数据库’
Order By Score DESC;

等值连接，检索条件就是=

(2)不等值连接

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

1
2
3
Select T1.Tname as Teacher1, T2.Tname as Teacher2
From Teacher T1, Teacher T2
Where T1.Salary > T2.Salary ;

示例：求年龄有差异的任意两位同学的姓名

1
2
3
Select S1.Sname as Stud1, S2.Sname as Stud2
From Student S1, Student S2
Where S1.Sage > S2.Sage ;

(3)多表联合查询训练

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

1
2
3
Select S1.S# 
From SC S1, SC S2
Where S1.S# = S2.S# and S1.C#=‘001’ and S2.C#=‘002 ;

求“001”号课成绩比“002”号课成绩高的所有学生的学号

1
2
3
Select S1.S# 
From SC S1, SC S2
Where S1.S# = S2.S# and S1.C#=‘001’ and S2.C#=‘002’ and S1.Score > S2.Score;

注意正确理解用自然语言表达的查询语义，并用SQL正确表达,有时候自然语言的直译是错误的！

该写法是错误的，一个人选择的李明的课又选择了其他老师的课也会被选进去

5、利用SQL语言进行增-删-改

(1)SQL-之Insert

之前我们学到insert的简单用法，用来插入一条指定元组值的元组，接下来我们学习批量插入。插入子查询结果中的若干条元组。待插入的元
组由子查询给出。

新建立Table: St(S#, Sname), 将检索到的满足条件的同学新增到该表中

1
2
3
Insert Into St (S#, Sname)
Select S#, Sname From Student
Where Sname like ‘%伟 ’ ;

示例：新建Table: SCt(S#, C#, Score), 将检索到的成绩及格同学的记录新增到该表中

1
2
3
Insert Into SCt (S#, C#, Score)
Select S#, C#, Score From SC
Where Score>=60 ;

示例：追加成绩优秀同学的记录

1
2
3
Insert Into SCt (S#, C#, Score)
Select S#, C#, Score From SC
Where Score>=90 ;

注意：当新增元组时，DBMS会检查用户定义的完整性约束条件等，如不符合完整性约束条件，则将不会执行新增动作（将在后面介绍）。

还可以有更复杂的“查询后插入到新表中”的语句，例如可以将中间结果存储成表—这很有用！

示例：新建Table: St(S#, Sname, avgScore), 将检索到的同学的平均成绩新增到该表中

1
2
3
4
Insert Into St (S#, Sname, avgScore)
Select S#, Sname, Avg(Score) From Student, SC
Where Student.S# = SC.S#
Group by Student.S# ;

此SELECT语句的书写语法后面再解释。

(2)SQL-之Delete

注意：当删除元组时，DBMS会检查用户定义的完整性约束条件等，如不符合完整性约束条件，则将不会执行删除动作

如果Where条件省略，则删除所有的元组。

示例：删除SC表中所有元组

1
Delete From SC ;

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

1
Delete From SC Where S# = ‘98030101’ ;

示例：删除自动控制系的所有同学(此是一简单的嵌套子查询，后面会有更详细解释。)

1
2
Delete From Student Where D# in
( Select D# From Dept Where Dname = ‘自动控制’);

示例：删除有四门不及格课程的所有同学(此SELECT语句的书写语法后面再解释)

1
2
3
Delete From Student Where S# in
( Select S# From SC Where Score < 60
Group by S# Having Count(*)>= 4 );

(3)SQL-之Update

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

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

1
2
Update Teacher
Set Salary = Salary * 1.05 ;

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

1
2
3
4
Update Teacher
Set Salary = Salary * 1.1
Where D# in
( Select D# From Dept Where Dname = ‘计算机’)

示例：当某同学001号课的成绩低于该课程平均成绩时，将该同学该门课成绩提高5%(此SELECT语句的书写语法后面再解释。)

1
2
3
4
5
Update SC
Set Score = Score * 1.05
Where C# = ‘001’ and Score < some
( Select AVG(Score) From SC
Where C# = ‘001’ ) ;

示例：将张三同学001号课的成绩置为其班级该门课的平均成绩(此SELECT语句的书写语法后面再解释。)

1
2
3
4
5
6
7
8
Update SC
Set Score = ( Select AVG(SC2.Score)
From SC SC1, Student S1, SC SC2, Student S2
Where S1.Sclass = S2.Sclass and SC1.S# = S1.S#
and SC2.S# = S2.S# and S1.Sname=‘张三’
and SC1.C# = ‘001’ and SC1.C# = SC2.C# )
Where C# = ‘001’ and S# in ( Select S# From Student
Where Sname = ‘张三’ ) ;

6、利用SQL语言修改与撤销数据库

(1)修改数据库

  • alter：改变
  • drop：删除
  • modify：修改
  • add：添加

示例：在学生表Student(S#,Sname,Ssex,Sage,D#,Sclass)基础上增加二列Saddr, PID

1
Alter Table Student Add Saddr char[40], PID char[18] ;

示例：将上例表中Sname列的数据类型增加两个字符

1
Alter Table Student Modify Sname char(10) ;

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

1
Alter Table Student Drop Unique( Sname );

(2)撤销

示例：撤消学生表Student

1
Drop Table Student;

示例：撤消教师表Teacher

1
Drop Table Teacher;

示例：撤消SCT数据库

1
Drop database SCT;

注意，SQL-delete语句只是删除表中的元组,而撤消基本表droptable的操作是撤消包含表格式、表中所有元组、由该表导出的视图等相关的所有内容，所以使用要特别注意。

(3)数据库指定与关闭命令

有些DBMS提供了操作多个数据库的能力，此时在进行数据库操作时需要指定待操作数据库与关闭数据库的功能。

指定当前数据库

1
use 数据库名;

关闭当前数据库

1
close 数据库名;

二、SQL语言之复杂查询与视图

1、子查询

为什么需要子查询？

答:现实中，很多情况需要进行下述条件的判断

  • 集合成员资格
    • 某一元素是否是某一个集合的成员
  • 集合之间的比较
    • 某一个集合是否包含另一个集合等
  • 集合基数的测试
    • 测试集合是否为空
    • 测试集合是否存在重复元组

子查询：出现在Where子句中的Select语句被称为子查询(subquery) , 子查询返回了一个集合，可以通过与这个集合的比较来确定另一个查询集合。

种类：IN、NOT IN子查询、SOME、OR子查询、Exists子查询

(1)IN、NOT IN子查询

示例：列出张三、王三同学的所有信息

1
2
Select * From Student
Where Sname in (“张三”, “王三”);

它相当于：

1
2
Select * From Student
Where Sname = “张三” or Sname = “王三”;

示例：列出选修了001号课程的学生的学号和姓名(该方法，是不连接表的方法)

1
2
Select S#, Sname From Student
Where S# in ( Select S# From SC Where C# = ‘001’ ) ;

示例：求既学过001号课程, 又学过002号课程的学生的学号(该方法，是不连接表的方法)

1
2
3
Select S# From SC
Where C# = ‘001’ and
S# in ( Select S# From SC Where C# = ‘002’ ) ;

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

1
2
3
Select Sname From Student
Where S# not in ( Select S# From SC, Course C, Teacher T
Where T.Tname = ‘李明’ and SC.C# = C.C#and T.T# = C.T# );

非相关子查询：内层查询独立进行，没有涉及任何外层查询相关
信息的子查询

相关子查询：内层查询需要依靠外层查询的某些参量作为限定条件
才能进行的子查询

  • 之前的举例都是非相关子查询。
  • 外层向内层传递的参量需要使用外层的表名或表别名来限定
  • 相关子查询只能由外层向内层传递参数，而不能反之；这也称为变量的作用域原则。

示例：求学过001号课程的同学的姓名

1
2
3
4
5
Select Sname
From Student Stud
Where S# in ( Select S#
From SC
Where S# = Stud.S# and C# = ‘001’ ) ;

(2)θ-SOME、θ-OR子查询

  • 语法中， θ是比较运算符： < , > , >= , <= , = , <>。

  • 语义：将表达式的值与子查询的结果进行比较：

    • 如果表达式的值至少与子查询结果的某一个值相比较满足θ 关系，则“表达式 θ some (子查询)”的结果便为真；
    • 如果表达式的值与子查询结果的所有值相比较都满足θ 关系，则“表达式 θ all (子查询)”的结果便为真；

  • 在SQL标准中，也有θ-any谓词，但由于其语义的模糊性：any,“任一”是指所有呢？还是指某一个？不清楚，所以被 θ-some替代以求更明晰

  • 表达式 = some (子查询）等价于 表达式 in (子查询)

  • *表达式 <> all (子查询)等价于 表达式 not in (子查询) *

    示例：找出工资最低的教师姓名

1
2
Select Tname From Teacher
Where Salary <= all ( Select Salary From Teacher );

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

1
2
3
Select S# From SC
Where C# = “001” and
Score < some ( Select Score From SC Where C# = “001” );

示例：找出所有课程都不及格的学生姓名(相关子查询)

1
2
3
Select Sname From Student
Where 60 > all ( Select Score From SC
Where S# = Student.S# );

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

1
2
3
Select S# From SC
Where C# = “001” and
Score >= all ( Select Score From SC Where C# = “001” );

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

1
2
3
Select C# From SC
Where S# = “98030101” and
Score <= all ( Select Score From SC Where S# = “98030101” );

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

1
2
3
4
Select C# From SC, Student S
Where Sname = “张三” and S.S#=SC.S# and
Score <= all ( Select Score From SC
Where S#=S.S# );

(3)【Not】Exists子查询（量词∃）

Exists就是量词∃

语义：子查询结果中有无元组存在

  • 不加not形式的Exists谓词可以不用,和直接查询等效，我们一般使用的是 not exists

  • 该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE 或 FALSE）来决定主查询的数据结果是否得以保留。而EXISTS符合为真，NOT EXISTS是不符合为真，详细解释

  • 由于其特性,只返回True或者False来决定。所以，一般需要将外表和内表关联起来，否则只是根据内标是否返回而判断

  • not exists很难理解，需要将肯定变成否定之否定，需要多多练习

示例：检索学过001号教师主讲的所有课程的所有同学的姓名

1
2
3
4
5
6
Select Sname From Student
Where not exists //不存在
( Select * From Course //有一门001教师主讲课程
Where Course.T# = ‘001’ and not exists //该同学没学过
( Select * From SC
Where S# = Student.S# and C# = Course.C# ) );

过程:首先从Student表中第一元组开始代入，查询Where语句，not exists（子查询）为真时即子查询无返回结果时保留该元组，进入中间层判断，选择Course表第一个元组开始代入，进入Where语句，当Course.T# = ‘001’和not exists（子查询）都为真即Course.T# = ‘001’并且子查询无返回结果时为真，该中间层元组进入结果表，进入内层判断，取SC表第一个元组代入，进入Where语句，只有满足 S# = Student.S# and C# = Course.C#才为真具有返回结果。

而内层为真，则中间层的not exists为假，并且，对SC表遍历只要有一个为真，则就有返回结果，则中间层的not exists就为假了，则本次中间层无返回结果，course继续下一个元组代入，同理进行检测后，只有所有元组检测完毕都没有返回结果才能判断外层的not exists为真，这样才能保留该元组，并且选择Sname。

也就是说，三层循环嵌套，最外层只是选择层，中间和内两层循环为判断是否选择，中间层全部为假则选择该元组，而保证中间层为假，则必须保证Course.T# = ‘001’和not exists不能同时为真。

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

1
2
3
4
5
Select Sname From Student
Where not exists //不存在
( Select * From Course, SC, Teacher //学过一门课程
Where Tname=‘李明’ and Course.T# =Teacher.T#
and Course.C# = SC.C# and S# = Student.S# );

示例：列出至少学过98030101号同学学过所有课程的同学的学号

1
2
3
4
5
6
Select DISTINC S# From SC SC1
Where not exists //不存在
( Select * From SC SC2 //有一门课程
Where SC2.S# = ‘98030101’ and not exists //该同学没学过
( Select * From SC
Where C# = SC2.C# and S# = SC1.S# ) );

关系代数和元组演算的表达:

(4)BETWEEN

BETWEEN 运算符用于 WHERE 表达式中，选取介于两个值之间的数据范围。BETWEEN 同 AND 一起搭配使用，语法如下：

1
2
WHERE column BETWEEN value1 AND value2
WHERE column NOT BETWEEN value1 AND value2

2、结果计算与聚集计算

(1)结果计算

  • Select-From-Where语句中，Select子句后面不仅可是列名，而且可是一些计算表达式或聚集函数，表明在投影的同时直接进行一些运算
  • expr可以是常量、列名、或由常量、列名、特殊函数及算术运算符构成的算术运算式。特殊函数的使用需结合各自DBMS的说明书
  • agfunc()是一些聚集函数

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

1
2
3
Select T1.Tname as TR1, T2.Tname as TR2, T1.Salary – T2.Salary
From Teacher T1, Teacher T2
Where T1.Salary > T2.Salary;

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

1
2
Select S.S#, S.Sname, 2015 – S.Sage+1 as Syear
From Student S;

(2)聚集计算

常用内置聚集函数：

  • COUNT([ALL|DISTINCT] [列名])：求个数
  • SUM([ALL|DISTINCT] [列名])：求 和
  • AVG([ALL|DISTINCT] [列名])：求平均
  • MAX([ALL|DISTINCT] [列名])：求最大
  • MIN([ALL|DISTINCT] [列名])：求最小

聚集函数是不允许用于Where子句中的：Where子句是对每一元组进行条件过滤，而不是对集合进行条件过滤

示例：求教师的工资总额

1
Select Sum(Salary) From Teacher;

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

1
2
Select Sum(Salary) From Teacher T, Dept
Where Dept.Dname = ‘计算机’ and Dept.D# = T.D#;

示例：求数据库课程的平均成绩

1
2
Select AVG(Score) From Course C, SC
Where C.Cname = ‘数据库’ and C.C# = SC.C#;

3、分组查询与分组过滤

(1)分组查询

分组：SQL可以将检索到的元组按照某一条件进行分类，具有相同条件值的元组划到一个组或一个集合中，同时处理多个组或集合的聚集运算。

示例： 求每一个学生的平均成绩

1
2
Select S#, AVG(Score) From SC
Group by S#;

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

1
2
Select C#, AVG(Score) From SC
Group by C#

(2)分组过滤

分组过滤：若要对集合(即分组)进行条件过滤，即满足条件的集合/分组留下，不满足条件的集合/分组剔除。

Having子句，又称分组过滤子句。需要有Groupby子句支持，换句话说，没有Groupby子句，便不能有Having子句。

示例：求不及格课程超过两门的同学的学号

1
2
3
Select S# From SC
Where Score < 60
Group by S# Having Count(*)>2;

示例：求不及格课程超过两门的同学的学号

1
2
3
Select C# From SC
Where Score < 60
Group by C# Having Count(*)>10;

分组查询仍需要注意语义问题

示例：求有两门以上不及格课程同学的学号及其平均成绩

1
2
3
Select S#, Avg(Score) From SC
Where Score < 60
Group by S# Having Count(*)>2;

这样写是错误的，求出的是“该同学那几门不及格课程的平均成绩”，而不是
“该同学所有课程的平均成绩”

正确写法:

1
2
3
4
5
6
Select S#, Avg(Score) From SC
Where S# in
( Select S# From SC
Where Score < 60
Group by S# Having Count(*)>2 )
Group by S# ;

4、实现关系代数操作

  • SQL语言：并运算 UNION, 交运算INTERSECT, 差运算EXCEPT。
  • 通常情况下自动删除重复元组：不带ALL。若要保留重复的元组，则要带ALL.
  • 假设子查询1的一个元组出现m次，子查询2的一个元组出现n次，则该元组在：
    • 子查询1 Union ALL 子查询2 ，出现m + n次
    • 子查询1 Intersect ALL 子查询2 ，出现min(m,n)次
    • 子查询1 Except ALL 子查询2 ，出现max(0, m – n)次
  • UNION运算符是Entry-SQL92的一部分、INTERSECT、EXCEPT运算符是Full-SQL92的一部分。它们都是Core-SQL99的一部分，但有些DBMS并不支持这些运算，使用时要注意！

1、并运算 UNION

示例：求学过002号课的同学或学过003号课的同学学号

1
2
3
Select S# From SC Where C# = ‘002’
UNION
Select S# From SC Where C# = ‘003’;

它等同于

1
Select S# From SC Where C# = ‘002’ OR C# = ‘003’;

但有时UNION不能转换掉

示例：已知两个表Customers(CID, Cname, City, Discnt)、Agents(AID, Aname, City, Percent)

求客户所在的或者代理商所在的城市

1
2
3
Select City From Customers
UNION
Select City From Agents ;

2、交运算INTERSECT

示例：求既学过002号课，又学过003号课的同学学号

1
2
3
Select S# From SC Where C# = ‘002’
INTERSECT
Select S# From SC Where C# = ‘003’;

它等同于

1
2
Select S# From SC Where C# = ‘002’ and S# IN
(Select S# From SC Where C# = ‘003’);

交运算符Intersect并没有增强SQL的表达能力，没有Intersect， SQL也可以用其他方式表达同样的查询需求。只是有了Intersect更容易表达一些，但增加了SQL语言的不唯一性。

3、差运算EXCEPT

示例： 假定所有学生都有选课，求没学过002号课程的学生学号

1
2
3
Select DISTINCT S# From SC
EXCEPT
Select S# From SC Where C# = ‘002’;

它等同于

1
2
3
Select DISTINCT S# From SC SC1
Where not exists ( Select * From SC
Where C# = ‘002’ and S# = SC1.S#);

差运算符Except也没有增强SQL的表达能力，没有Except， SQL也可以用其他方式表达同样的查询需求。只是有了Except更容易表达一些，但增加了SQL语言的不唯一性。

5、空值的处理

在SQL标准中和许多现流行的DBMS中，空值被用一种特殊的符号Null来标记，使用特殊的空值检测函数来获得某列的值是否为空值。

示例：找出年龄值为空的学生姓名

1
2
Select Sname From Student
Where Sage is null ;

注意：上例条件不能写为Where Sage = null; 空值是不能进行运算的

现行DBMS的空值处理小结

  • 除is[not]null之外，空值不满足任何查找条件
  • 如果null参与算术运算，则该算术表达式的值为null
  • 如果null参与比较运算，则结果可视为false。在SQL-92中可看成unknown
  • 如果null参与聚集运算，则除count(*)之外其它聚集函数都忽略null

6、内连接、外连接

https://www.cnblogs.com/pcjim/articles/799302.html

连接类型：

  • inner [outer] join：即关系代数中的θ-连接运算
  • left [outer] join：左外连接
  • right [outer] join：右外连接
  • full [outer] join：全外连接

连接条件:

  • natural：出现在结果关系中的两个连接关系的元组在公共属性上取值相等，且公共属性只出现一次

  • on 连接条件：出现在结果关系中的两个连接关系的元组取值满足连接条件，且公共属性出现两次

  • using (Col1, Col2, …, Coln)：(Col1, Col2, …, Coln)是两个连接关系的公共属性的子集，元组在(Col1, Col2, …, Coln)上取值相等，且(Col1, Col2, …, Coln)只出现一次其实就是natural的部分，natural是全部公共属性，而这个是其公共属性的子集

示例: 求所有教师的任课情况并按教师号排序(没有任课的教师也需列在表中)

1
2
3
4
Select Teacher.T#, Tname, Cname
From Teacher Inner Join Course
ON Teacher.T# = Course.T#
Order by Teacher.T# ASC;

示例: 求所有教师的任课情况(没有任课的教师也需列在表中)

1
2
3
4
Select Teacher. T#, Tname, Cname
From Teacher Left Outer Join Course
ON Teacher.T# = Course.T#
Order by Teacher.T# ASC ;

7、视图及其应用

对应概念模式的数据在SQL中被称为基本表(Table),而对应外模式的数据称为视图(View)。视图不仅包含外模式，而且包含其E-C映像。

  • 基本表是实际存储于存储文件中的表，基本表中的数据是需要存储的
  • 视图在SQL中只存储其由基本表导出视图所需要的公式，即由基本表产生视图的映像信息，其数据并不存储，而是在运行过程中动态产生与维护的
  • 对视图数据的更改最终要反映在对基本表的更改上。

(1)定义视图

如果视图的属性名缺省，则默认为子查询结果中的属性名；也可以显式指明其所拥有的列名。

with checkoption指明当对视图进行insert，update，delete时，要检查进行insert/update/delete的元组是否满足视图定义中子查询中定义的条件表达式

示例：定义一个视图 CompStud 为计算机系的学生，通过该视图可以将Student表中其他系的学生屏蔽掉

1
2
3
4
5
Student表中其他系的学生屏蔽掉
Create View CompStud AS
( Select * From Student
Where D# in ( Select D# From Dept
Where Dname = ‘计算机’ ) );

示例：定义一个视图Teach为教师任课的情况，把Teacher表中的个人隐私方面的信息，如工资等屏蔽掉，仅反映其教哪门课及其学分等。

1
2
3
4
Create View Teach AS
( Select T.Tname, C.Cname, Credit
From Teacher T, Course C
Where T.T# = C.T# ) ;

(2)使用视图

使用视图：定义好的视图，可以像Table一样，在SQL各种语句中使用

示例：检索主讲数据库课程的教师姓名，我们可使用Teach

1
2
Select T.Tname From Teach T
Where T.Cname = ‘数据库’ ;

视图究竟有什么作用?

答：定义视图，有时可方便用户进行检索操作。

示例：定义视图StudStat, 描述学生的平均成绩、最高成绩，最低成绩等

1
2
3
4
Create View StudStat(S#, Sname, AvgS, MinS, MaxS, CNT)
as ( Select S#, Sname, AVG(Score), MIN(Score), Max(Score), Count(*)
From Student S, SC Where S.S# = SC.S#
Group by S.S# ) ;

示例：基于视图StudStat检索某一学生平均成绩

1
Select Sname, AvgS From StudStat Where Sname = ‘张三’ ;

(3)更新视图

SQL视图更新：是比较复杂的问题，因视图不保存数据，对视图的更新最终要反映到对基本表的更新上，而有时，视图定义的映射不是可逆的。

SQL视图更新的可执行性

  • 如果视图的select目标列包含聚集函数，则不能更新
  • 如果视图的select子句使用了unique或distinct，则不能更新
  • 如果视图中包括了groupby子句，则不能更新
  • 如果视图中包括经算术表达式计算出来的列，则不能更新
  • 如果视图是由单个表的列构成，但并没有包括主键，则不能更新

例如：

1
2
3
4
5
6
create view ClassStud(Sname, Sclass)
as ( select Sname, Sclass
from Student );

Insert into ClassStud
Values ( ‘张三’, ‘980301’ );

无法更新，缺失主键。

(4)撤销视图

示例：撤消视图Teach

1
Drop View Teach;
-----------------------本文结束 感谢阅读-----------------------
坚持原创技术分享，您的支持将鼓励我继续创作！恰饭^.^~