3、关系数据库标准语言SQL

关系数据库标准语言SQL SQL概述

• 结构化查询语言SQL （Structured Query Language）是一种介于关系代数与关系演算之间的语言，其功能包括查询、操纵、定义和控制四个方面，是一个通用的功能极强的关系数据库标准语言。

SQL的特点

综合统一

高度非过程化

• 非关系数据模型的数据操纵语言“面向过程”，必须制定存取路径
• SQL只要提出“做什么”，无须了解存取路径。
• 存取路径的选择以及SQL的操作过程由系统自动完成。

面向集合的操作方式

• 非关系数据模型采用面向记录的操作方式，操作对象是一条记录

• SQL采用集合操作方式

  操作对象、查找结果可以是元组的集合
  一次插入、删除、更新操作的对象可以是元组的集合

以同一种语法结构提供多种使用方式

• SQL是独立的语言

  能够独立地用于联机交互的使用方式

• SQL又是嵌入式语言

  SQL能够嵌入到高级语言（例如C，C++，Java）程序中，供程序员设计程序时使用

语言简洁，易学易用

SQL 基本概念

• SQL语言支持关系数据库三级模式结构，

  外模式对应于视图(view)和部分基本表 (basetable)
  模式对应于基本表
  内模式对应于存储文件

• 基本表是本身独立存在的表，在SQL中一个关系对应一个表

• 视图是从基本表或其他视图中导出的表，它本身不独立存储在数据库中，视图是一个虚表。

数据定义 基本表的定义

• 基本表构成

  列
  完整性约束

列 完整性约束

• 完整性约束条件被存入系统的数据字典中，当用户操作表中数据时由DBMS自动检查该操作是否违背这些完整性约束条件。
• 如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上；
• 如果只涉及一个属性列，则既可以定义在列级也可以定义在表级。在表中，可以指定许多种完整性约束

完整性约束定义

举例：

Create Table Student(
S# Varchar2(10) Constraint PK_S Primary Key,
Sname Varchar2(20),
Age Number(3) Constraint CK_S Check (age>14 and age<30),
Sex Char(1)，
Constraint UQ_S Unique(Sname),
Constraint CK_SS Check (Sex IN (‘M’,’F’))
)

主码约束

• 定义主键: 不许有空值，也不可重复
• 一个表只能包含一个主键约束。
• 当一个表的主关键字由多个列构成时，需要表级的完整性约束说明。

外码约束

• 外键 (Foreign Key)约束：实施参照完整性约束。
• 表中某列值只能引用其它表的主键列或唯一键列，参照完整性含义。
• 若在Foreign Key约束的列中键入NOT NULL(非空)约束，则此值必须在被引用的列中存在，否则将违反外部关键字约束。

CHECK约束 NOT NULL约束

• 非空约束：值不可以为空
• NOT NULL约束是CHECK约束的一种。
• NOT NULL约束只能在列级定义
• 如想在表级定义，只能转化为Check约束

Unique约束

• 唯一性约束：值不可重复，但可以为空。
• 每个Unique约束都生成一个唯一索引。
• 当某个列不是主关键字，但取值必须唯一时，可以使用Unique约束。
• 建立Unique约束后系统自动为其创建一个唯一索引

Unqiue约束对空值的处理

Constraint UQ_D Unique(NAME，SCHOOL)

DEFAULT约束

• 默认值：当往表中插入一条新记录时，如果某列上有默认值，并且新记录中未指定该列的值，则自动以默认值填充。
• Default只能在列级定义

基本表的修改 列的增加和删除 列的修改 约束的增加和删除

• 约束一旦建成就允许被删除，当禁用UNIQUE或PRIMARY KEY约束时需要小心，因为禁用这些约束可能导致它所生成的索引被删除。如果想删除一个已经存在的约束，可以使用ALTER语句

基本表的删除 索引的建立与删除

• 索引通过提供一种直接存取的方法来取代默认的全表扫描检索的方法，通过使用索引可以提高改善数据检索性能，可以快速的定位一条数据。
• 一般来说，建立与删除索引需要由表的创建者或者数据库管理员，或具有创建、删除索引权限的用户完成。

建立索引

• UNIQUE:表明此索引的每一个索引值只对应唯一的数据记录。
• CLUSTER:表示要建立的索引是聚簇索引。所谓聚簇索引是指索引项的顺序与表中记录的物理顺序一致的索引组织，聚簇索引在Oracle中只能在定义表的同时定义。用户可以在最常查询的列上建立聚簇索引以提高查询效率。在一个基本表上最多只能建立一个聚簇索引。建立聚簇索引后，更新索引列数据时，往往导致表中记录的物理顺序的变更，代价较大，因此对于经常更新的列不宜建立聚簇索引。
  在Oracle中，对一个表中的主键的字段不能建立聚簇索引，因为创建主键约束时，系统已经自动生成一个聚簇索引。

主键保证数据库里面的每一行都是唯一的，比如身份证，学号等，在表中要求唯一，不重复。唯一索引的作用跟主键的作用一样。不同的是，在一张表里面只能有一个主键，主键不能为空，唯一索引可以有多个，唯一索引可以有一条记录为空，即保证跟别人不一样就行。
比如学生表，在学校里面一般用学号做主键，身份证则弄成唯一索引；而到了教育局，他们就把身份证号弄成主键，学号换成了唯一索引。
选谁做表的主键，要看实际应用，主键不能为空。

删除索引

数据查询 SELECt语句查询 SELECT…FROM …

• 可能有这样的情况，对于查出来的列有时列名不能清晰表达列的含义，或显示的是列的表达式。这些情况用户可以在列的后面(用空格分开)指定相应列的别名，如果别名中含有空格，则用双引号进行包含。

• DISTINCT 关键字可从SELECt语句的结果中除去重复的行。如果没有指定DISTINCT,则默认为ALL，那么将返回所有行，包括重复的行。

• DISTINCT关键字是作用于用户所选择的元组，而不是仅仅作用于DISTINCT后面的一列，对于选择出的每个行，如果有两个或多个同学有同样的名字和年龄，则在结果中只显示一次。

WHERe

• 使用BETWEEN AND操作符可以选中排列于两值(包括这两个值)之间的数据。这些数据可以是数字，文字或是日期。也就是说，通过BETWEEN AND确定一个范围，并且把这个范围内的数据库中的值输出。

• 由于数据存储方式的原因，使用包含Char数据模式的字符串比较无法通过LIKE比较。例如Student表中Sname属性的数据类型是Char(6)，存在姓名为Dtt的学生，但是通过WHERe Sname LIKE '_tt’语句查不到记录，这是因为Char是定长的数据类型，在存储“Dtt”时，默认以空格补足后面的3位长度。

对于空值的处理

ORDER BY GROUP BY

聚集函数

HAVINg子句

• GROUP BY子句可以将查询结果表的各行按一列或多列取值相等的原则进行分组，也就是将聚集函数应用到关系中每个由多个分组组成的行上。
• 当在用聚集函数的时候，一般都要用到GROUP BY 先进行分组，然后再进行聚集函数的运算。运算完后可能要用到HAVINg 子句进行判断，例如判断聚集函数的值是否大于某一个值等等。

• WHERe子句与HAVINg短语的根本区别在于作用对象不同。

  WHERe子句作用于基本表或视图，从中选择满足条件的元组。
  HAVINg短语作用于分组，从中选择满足条件的组。

• Having子句中必须是聚集函数的比较式，而且聚集函数的比较式也只能通过Having子句给出

• Having中的聚集函数可与Select中的不同

• 使用聚集函数时，一般都要用到GROUP BY子句先进行分组，然后再进行聚集函数的运算。运算完后可能要用到HAVINg子句根据各分组的聚集结果进行判断，确定出现在结果集中的分组结果

连接查询

• 有时用户查询可能涉及多个表，才能查出所需要的信息。如果一个查询需要对多个表进行操作，就称为连接查询。
• 连接可以在SELECT 语句的FROM子句或WHERe子句中建立，不同的子句有不同的分类方式，用WHERe子句连接的查询一般分为等值连接查询、非等值连接查询、自然连接查询和复合条件连接查询；用FROM子句连接的查询一般分为内连接、外连接和交叉连接。

等值连接和非等值连接

• 连接查询的WHERe子句中用来连接两个表的条件称为连接条件或连接谓词，其一般格式为：

  [<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2> 　　
  其中比较运算符主要有：=、>、<、>=、<=、!=

• 当连接运算符为=时，称为等值连接。使用其它运算符称为非等值连接。

• 连接谓词中的列名称为连接字段。连接条件中的各连接字段类型必须是可比的，但不必是相同的。

笛卡尔积连接 自然连接 自身连接 复合条件连接

• 查询选修2号课程，成绩在90分以上,且为男同学的所有学生姓名和学号。

  SELECt Student.Sno, Sname FROM Student, SC WHERe Student.Sno=SC.Sno AND SC.Cno=2 AND SC.Grade>90 and student.ssex=‘男’；

  SELECt Student.Sno, Sname FROM Student inner join sc on student.sno=sc.sno where SC.Cno=2 AND SC.Grade>90 and student.ssex=‘男’;

外连接

• 在通常的连接操作中，有满足连接条件的元组才能作为结果输出。
• 在特殊的连接操作中，即使没有满足连接条件的元组也需要作为结果输出，就需要使用外连接(Outer Join)。
• 外连接的运算符通常为*，Oracle中为十。
• 外连接符若出现在连接运算符的右边，称其为右外连接（Right Join）。
• 相应地，如果外连接符出现在连接运算符的左边，则称为左外连接(Left Join)。

FROM子句中的连接查询

• 内连接(INNER JOIN)使用比较运算符进行表间某(些)列数据的比较操作，并列出这些表中与连接条件相匹配的数据行。根据所使用的比较方式不同，内连接又分为等值连接、自然连接和不等值连接三种。
• 外连接分为**左外连接(LEFT OUTER JOIN或LEFT JOIN)、右外连接(RIGHT OUTER JOIN或RIGHT JOIN)和全外连接(FULL OUTER JOIN或FULL JOIN)三种。与内连接不同的是，外连接不只列出与连接条件相匹配的行，而是列出左表(左外连接时)、右表(右外连接时)或两个表(全外连接时)**中被舍弃的数据行。
• 交叉连接(CROSS JOIN)没有ON子句，它返回连接表中所有数据行的笛卡尔积，其结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。
• 连接操作中的ON (join_condition) 子句指出连接条件，它由被连接表中的列和比较运算符、逻辑运算符等构成。

内连接

• 内连接查询操作列出与连接条件匹配的数据行，它使用比较运算符比较被连接列的列值。内连接分三种：

  • 等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。
  • 不等值连接：在连接条件使用除等于运算符以外的其它比较运算符比较被连接列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。
  • 自然连接：在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。

外连接

• 内连接时，返回查询结果集合中的仅是符合查询条件( WHERe 搜索条件或 HAVINg 条件)和连接条件的行。
• 采用外连接时，它返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接时)、右表(右外连接时)或两个边接表(全外连接)中的所有数据行。

交叉连接

• 交叉连接不带WHERe 子句，它返回被连接的两个表所有数据行的笛卡尔积 。

利用连接查询的分组查询 嵌套查询

• 嵌套查询是将其他查询嵌套在另一个查询里面的查询，嵌套在查询中的查询称为子查询。当然，子查询本身也可以是嵌套查询，这样就可以形成更深层次的查询。

• 子查询通常出现在WHERe子句中，有时候也出现FROM子句中，有时候也出现在HAVINg短语中。

• 子查询的SELECt语句中不能使用ORDER BY子句，ORDER BY子句永远只能对最终查询结果排序。

• 子查询都在其父查询处理前求解，它的执行不依赖于父查询的任何条件，这类查询叫做不相关子查询。每个子查询仅执行一次，子查询的结果集为父查询的WHERe条件所用。

• 有的查询中，子查询的执行依赖于父查询的某个条件，子查询不只执行一次。这类子查询的查询条件往往依赖于其父查询的某属性值，称为相关子查询。

• 不相关子查询：

  子查询的查询条件不依赖于父查询

  由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。

• 相关子查询：子查询的查询条件依赖于父查询

  首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERe子句返回值为真，则取此元组放入结果表
  然后再取外层表的下一个元组
  重复这一过程，直至外层表全部检查完为止

带有比较运算符的子查询 带有IN谓词的子查询

• 带有IN谓词的子查询指的是父查询与子查询用谓词IN连接，判断某个属性列值是否在子查询的结果中。

• 例3-56 查询所有选修了1号课程的学生的学号、姓名。

  SELECT Sno,Sname
  FROM Student
  WHERe Sno IN
  (SELECt Sno
  FROM SC
  WHERe Cno=1)；

带有between…and…的子查询

• ［NOT］BETWEEN…AND 也可以作为嵌套查询的连接词。子查询可以跟在BETWEEN后面，也可以跟在AND后面。

• 例3-57 查找从19岁到Student表中年龄最大之间的学生学号和姓名。

  SELECt Sno,Sname
  FROM Student
  WHERe Sage BETWEEN 19 AND
  (SELECt MAX(Sage)
  FROM Student );

带有ANY（SOME）或ALL谓词的子查询

• 当子查询返回多值时可以用ANY或ALL谓词来连接主查询，但要注意的是，在ALL或ANY前要加上比较运算符。ALL表示所有，ANY表示某一个 。

• 例3-58查询年龄比数学系最小的学生还小的学生学号和姓名。

  SELECt Sno,Sname FROM Student
  WHERe Sage < ALL
  (SELECt Sage FROM Student
  WHERe Sdept=‘MA’);

带有EXISTS谓词的子查询

• 例3-59查询所有选修了1号课程的学生姓名。

  SELECt Sname
  FROM Student
  WHERe EXISTS
  (SELECt *
  FROM SC
  WHERe Sno=Student.Sno AND Cno=1);

  • 思路分析：

    本查询涉及Student和SC关系
    在Student中依次取每个元组的Sno值，用此值去检查SC关系
    若SC中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno= ‘1’，则取Student.Sname送入结果关系

  • 本例中的查询也可以用含有IN或者=ANY的嵌套查询来实现：

    SELECt Sname
    FROM Student
    WHERe Sno IN
    (SELECt Sno
    FROM SC
    WHERe Cno=1);
    或：
    SELECt Sname
    FROM Student
    WHERe Sno=ANY
    (SELECt Sno
    FROM SC
    WHERe Cno=1);

• 查询所有未选修1号课程的学生姓名。

  SELECt Sname
  FROM Student
  WHERe NOT EXISTS
  (SELECt *
  FROM SC
  WHERe Sno=Student.Sno AND Cno=1);

• 查询选修了全部课程的学生姓名。

Select Sname
From Student
Where NOT EXISTS
( Select *
From Course
Where NOT EXISTS
(Select *
From SC
Where Sno= Student.Sno
And Cno= Course.Cno
)
)；

对于每个学生，选出选修了全部课程的学生----------> 对于每个学生，选出不存在未选修课程的学生

• 不同形式的查询间的替换

  一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换
  所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换

• 用EXISTS/NOT EXISTS实现全称量词和逻辑蕴含(难点)

集合查询

• 使用UNIOn将多个查询结果合并起来，形成一个完整的查询结果时，系统会自动去掉重复的元组。如果不想去除重复的元祖，用UNIOn ALL合并查询结果。
• 需要注意的是，参加UNIOn操作的各数据项数目必须相同，对应项的数据类型也必须相同。

SELECt语句的一般格式

SELECT [ALL|DISTINCT]
<目标列表达式> [别名] [ ，<目标列表达式> [别名]] …
FROM <表名或视图名> [别名] [ ，<表名或视图名> [别名]] …
[WHERe <条件表达式>]
[GROUP BY <列名1> [ HAVINg <条件表达式>]]
[ORDER BY <列名2> [ASC|DESC]]；

聚集函数的一般格式

条件表达式格式

数据更新

DML：

Insert： 插入记录
Delete：删除记录
Update: 修改记录

》》 DDL 、 DML（INSERT DELETE UPDATE）、 DQL（SELECT）、 DCL

数据更新操作有3个：

向表中添加若干行数据
修改表中的数据
删除表中的若干行数据

INSERT 插入数据

• INTO子句

指定要插入数据的表名及属性列
属性列的顺序可与表定义中的顺序不一致
没有指定属性列：表示要插入的是一条完整的元组，且属性列属性与表定义中的顺序一致
指定部分属性列：插入的元组在其余属性列上取空值

• VALUES子句

  提供的值必须与INTO子句匹配
  值的个数
  值的类型

插入子查询结果

• INTO子句(与插入单条元组类似)

  指定要插入数据的表名及属性列
  属性列的顺序可与表定义中的顺序不一致
  没有指定属性列：表示要插入的是一条完整的元组
  指定部分属性列：插入的元组在其余属性列上取空值

• 子查询

  SELECT子句目标列必须与INTO子句匹配
  值的个数和值的类型都必须一致

• DBMS在执行插入语句时会检查所插元组是否破坏表上已定义的完整性规则

  实体完整性
  参照完整性
  用户定义的完整性

  对于有NOT NULL约束的属性列是否提供了非空值
  对于有UNIQUE约束的属性列是否提供了非重复值
  对于有值域约束的属性列所提供的属性值是否在值域范围内

UPDATE 修改数据

• SET子句

  指定修改方式
  要修改的列
  修改后取值

• WHERe子句

  指定要修改的元组
  缺省表示要修改表中的所有元组

• DBMS在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则

修改一个元组

修改多个元组的值

带子查询的修改语句 DELETE 删除数据

删除一个元组

删除多个元组

带子查询的删除语句 视图

• 视图是基于一个表或多个表或视图的逻辑表，本身不包含数据，是一个虚表。

• 视图在存储时只存储视图的定义，而没有存储对应的数据，视图只在刚刚打开的一瞬间，通过定义从基表中搜集数据，并展现给用户。视图基于的表称为基表。

• 通过视图可以对表中的数据进行查询和修改。

• 视图中数据虽然没有实际存储，但可以和表一样操作。

• 视图具有和表一样的逻辑结构定义

• 但视图没有相应的存储文件，而每个表都有相应的存储文件

• 视图和查询的异同点：

  相同点：视图和查询都是用由SQL语句组成。
  但是视图和查询有着本质区别：
  视图存储为数据库设计的一部分，而查询则不是；
  更新限制的要求不一样，视图来自于表，所以通过视图可以间接对表进行更新，但是有很多限制；
  通过SQL语句，可以对一个表进行排序，而视图则不行。

视图的用途 视图的特点

视图的简单性
视图的安全性
逻辑数据独立性

创建视图

• 如果CREATE VIEW语句仅指定了视图名，省略了组成视图的各个属性列名，则隐含该视图由子查询中SELECT子句目标列中的诸字段组成。但在下列三种情况下必须明确指定组成视图的所有列名：

  (1)其中某个目标列不是单纯的属性名，而是集函数或列表达式；
  (2)多表连接时选出了几个同名列作为视图的字段；
  (3)需要在视图中为某个列启用新的更合适的名字。

• 需要说明的是，组成视图的属性列名必须依照上面的原则，或者全部省略或者全部指定，没有第三种选择

• 实际上，DBMS执行CREATE VIEW语句的结果只是把对视图的定义存入数据字典，并不执行其中的SELECT语句。只是在对视图查询时，才按视图的定义从基本表中将数据查出。

对于 with check option

1.对于update,有with check option，要保证update后，数据要被视图查询出来

2.对于delete,有无with check option都一样

3.对于insert,有with check option，和update相同 要保证insert后，数据要被视图查询出来

4.对于没有where 子句的视图，也就没有视图自己的约束，因此使用with check option是多余的

• 定义基本表时，为了减少数据库中的冗余数据，表中只存放基本数据，由基本数据经过各种计算派生出的数据一般是不存储的。但由于视图中的数据并不实际存储，所以定义视图时可以根据应用的需要，设置一些派生属性列。

• 这些派生属性由于在基本表中并不实际存在，所以有时也称他们为虚拟列。

• 带虚拟列的视图称为带表达式的视图。

• 例 定义一个反映学生出生年份的视图。

  CREATE VIEW BT_S(Sno, Sname, Sbirth)
  AS SELECT Sno, Sname, 2014-Sage
  FROM Student;

  • 由于BT_S视图中的出生年份值是通过一个表达式计算得到的，不是单纯的属性名，所以定义视图时必须明确定义该视图的各个属性列名。BT_S视图是一个带表达式的视图。
  • 可以用带有聚集函数和GROUP BY子句的查询来定义视图。这种视图称为分组视图。

查询视图

• 视图定义后，用户就可以象对基本表进行查询一样对视图进行查询了。

• DBMS执行对视图查询时，先进行有效性检查，然后对视图消解。

  有效性检查：检查查询中涉及的表、视图等是否存在；
  视图消解：从数据字典中查询视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后执行转换后的查询。这一过程成为视图消解。

• 视图查询与基本表的查询相同

• 更新视图包括插入(INSERT)、删除(DELETE)和修改(UPDATE)三类操作。由于视图是不实际存储数据的虚表，因此对视图的更新，最终要转换为对基本表的更新。
• 为防止用户通过视图对数据进行增删改时，无意或故意操作不属于视图范围内的基本表数据，可在定义视图时加上WITH CHECK OPTION子句，这样在视图上增删改数据时，DBMS会进一步检查视图定义中的条件，若不满足条件，则拒绝执行该操作。

更新视图

• 不是所有视图都是可更新的

  基于连接查询的视图不可更新
  使用了函数的视图不可更新
  使用了分组操作的视图不可更新
  只有建立在单个表上而且没有使用函数的视图才是可更新的

视图的删除

视图的作用

视图能够简化用户的操作；
视图使用户能以多种角度看待同一数据；
视图对重构数据库提供了一定程度的逻辑独立性；
视图能够对机密数据提供安全保护。