第五章 SQL语言
一、SQL简介
1．SQL：结构化查询语言，关系数据库的标准语言。
2．SQL数据库的体系结构
SQL数据库的体系结构基本上也是三级结构，但术语与传统的关系模型术语不同。在SQL中，关系模式称为“基本表”，存储模式称为“存储文件”，子模式称为“视图”，元组称为“行”，属性称为“列”。
SQL数据库的体系结构要点：
1）一个SQL模式是表和约束的集合。
2）一个表由行集构成，一行是列的序列，每列对应一个数据项。
3）表有三种类型：基本表、视图和导出表。
基本表：实际存储在数据库中的表。
视图：由若干基本表或其他视图构成的表的定义。
导出表：执行了查询时产生的表。
4）一个基本表可以跨一个或多个存储文件，一个存储文件也可以存放一个或多个基本表。每个存储文件与外部存储器上一个物理文件对应。
5）用户可以用SQL语句对基本表和视图进行查询等操作。在用户看来，两者一样，都是表。
6） SQL语句可嵌在C、FORTRAN等主语言的程序中使用，也可在交互环境下供终端用户使用。
3．SQL的组成
核心SQL主要有四个部分：
1）数据定义语言，即DDL，用于定义SQL模式、基本表、视图、索引等结构。
2）数据操纵语言，即SQL DML。数据操纵分成数据查询和数据更新两类。而数据更新又分成插入、删除和修改三种操作。
3）嵌入式SQL语言的使用规定
4）数据控制语言，即SQL DCL，这一部分包括对基本表和视图的授权、完整性规则的描述、事务控制等内容。
4．SQL的特点
1）SQL具有十分灵活和强大的查询功能。
2）SQL不是一个应用开发语言，它只提供对数据库的操作功能。但SQL既可作为交互式语言独立使用，也可作为子语言嵌入在主语言中使用，成为应用开发语言的一部分。
3）SQL是国际标准语言，有利于各种数据库之间交换数据，有利于程序的移植，有利于实现高度的数据独立性，有利于实现标准化。
4）SQL完成核心功能只用9个英语动词，语法结构接近英语，容易学习和使用。
二、SQL的数据定义
1．SQL模式的创建
在SQL中，一个SQL模式定义为基本表的集合。一个SQL模式由模式名和模式拥有者的用户名或账号来确定，并包含模式中每一个元素（基本表、视图、索引等）的定义。创建SQL模式，就是定义了一个存储空间。
创建模式语法：
CREATE  SCHEMA  <模式名>  AUTHORIZATION  <用户名>
2．SQL模式的撤销
语法：DROP  SCHEMA  <模式名>  [CASCADE | RESTRICT]
说明：
CASCADE（级联式）方式：执行DROP语句时，把SQL模式及其下属的基本表、视图、索引等所有元素全部撤销。
 RESTRICT（约束式）方式：只有当SQL模式中没有任何下属元素时，才能撤销SQL模式，否则拒绝执行DROP语句。
3．SQL的基本数据类型
1）数值型
INTEGER（或INT）    长整数
SMALLINT   短整数
REAL                  浮点数
DOUBLE  PRECISION   双精度浮点数
FLOAT（n）    浮点数，精度至少为n位数字
NUMERIC（p，d） 定点数，有p位数字（不包括符号、小数点）组成，小数点后有d位数字，也可写成DECIMAL（p，d）或DEC（p，d）
2）字符串型
CHAR（n） 长度为n的定长字符串
VARCHAR（n） 具有最大长度为n的变长字符串
3）位串型
BIT（n） 长度为n的二进制位串
BIT  VARYING（n） 最大长度为n的变长二进制位串
4）时间型
DATE 日期，包含年、月、日，形为YYYY-MM-DD
TIME 时间，包含时、分、秒，形为HH：MM：SS
4．基本表结构的创建
语法：CREATE  TABLE  <基本表名>
（<列名  类型>，
……
<完整性约束>，
……）
说明：完整性约束常用以下几种子句：主键子句（PRIMARY  KEY）、外键子句（FOREIGN  KEY）等
NOT  NULL：表示不允许某一列的值为空值
例：对于教学数据库中的四个关系：
教师关系T（T#，TNAME，TITLE）
课程关系C（C#，CNAME，T#）
学生关系S（S#，SNAME，AGE，SEX）
选课关系SC（S#，C#，SCORE）
CREATE  TABLE  T
  （T#  CHAR（4）  NOT  NULL，
TNAME  CHAR（8）  NOT  NULL，
TITLE  CHAR（10），
PRIMARY  KEY（T#））
 
CREATE  TABLE  C
（C#  CHAR（4）  NOT  NULL，
  CNAME  CHAR（10）  NOT  NULL，
  T#  CHAR（4），
  PRIMARY  KEY（C#），
  FOREIGN  KEY（T#）  REFERENCES  T（T#））
 
CREATE  TABLE  S
（S#  CHAR（4）  NOT  NULL，
SNAME  CHAR（8）  NOT  NULL，
AGE  SMALLINT，
SEX  CHAR（2），
PRIMARY  KEY（S#））
 
CREATE  TABLE  SC
（S#  CHAR（4），
C#  CHAR（4），
  SCORE  SMALLINT，
PRIMARY  KEY（S#，C#），
FOREIGN  KEY（S#）  REFERENCES  S（S#），
FOREIGN  KEY（C#）  REFERENCES  C（C#））
5．基本表结构的修改
1）增加新的列
语法：ALTER  TABLE  <基本表名>  ADD  <列名>  <类型>
2）删除原有的列
语法：ALTER  TABLE  <基本表名>  DROP  <列名>  [CASCADE | RESTRICT]
说明：CASCADE方式表示在基本表中删除某列时，所有引用到该列的视图和约束也要一起自动地被删除；RESTRICT方式表示在没有视图或约束引用该属性时，才能在基本表中删除该列，否则拒绝删除。
3）修改原有列的类型、宽度
语法：ALTER  TABLE  <基本表名>  MODIFY  <列名>  <类型>
6．基本表的撤销
语法：DROP  TABLE  <基本表名>   [CASCADE | RESTRICT]
7．索引的创建
语法：CREATE  [UNIQUE]  INDEX  <索引名>  ON  <基本表名>（<列名序列>）
说明：UNIQUE表示每个索引值对应惟一的数据记录。
  索引在用户查询时会自动起作用。
  一个索引键可以对应多个列。索引排列时可以升序，也可以降序，升序排列用ASC表示，降序排列用DESC表示，默认时表示升序。
8．索引的撤销
语法：DROP  INDEX  <索引名>
三、SQL的数据查询
1．SELECT查询语句句型
在关系代数中最常用的式子是下列表达式：
Π_{A1，…，An}(σ_F(R1×…×Rm))
这里，R1、…、Rm为关系，F是公式，A1、…、An为属性。
与该表达式对应，SQL设计了SELECT—FROM—WHERE句型：
SELECT  A1，…，An
FROM  R1，…，Rm
WHERE  F
注意：1）在学习时，应把SELECT语句和关系代数表达式联系起来考虑问题。
2）在WHERE子句的条件表达式F中可使用下列运算符：
比较运算符：〈、 〈=、 〉、 〉=、 =、 〈〉或！=
逻辑运算符：AND、OR、NOT
集合成员资格运算符：IN、NOT  IN
谓词：EXISTS、ALL、SOME
聚合函数：AVG、MIN、MAX、SUM、COUNT
F中运算对象还可以是另一个SELECT语句，即SELECT语句可以嵌套。
4．SELECT语句完整的句法
SELECT  <目标表的列名或列表达式序列>
FROM  <基本表名和（或）视图序列>
[WHERE  <行条件表达式>]
[GROUP  BY  <列名序列>
[HAVING  <组条件表达式>]]
[ORDER  BY  <列名[ASC | DESC]>,…]
说明：[]表示其中的内容根据需要可以省略。
|表示二选一。
   各子句的作用：SELECT子句用于指定输出的内容；FROM子句用于指定要检索的数据的来源表；WHERE子句称为“行条件子句”，用于指定对元组的选取条件；GROUP  BY子句称为“分组子句”，作用是指定对元组进行分类后再检索；HAVING子句称为“组条件子句”，用于指定对分类后的元组的选取条件；ORDER  BY子句称为“排序子句”，作用是对检索到的元组进行排序。
整个语句的执行过程为：
1）读取FROM子句中基本表、视图的数据，执行笛卡儿积操作。
2）选取满足WHERE子句中给出的条件表达式的元组。
3）按GROUP子句中指定列的值分组，同时提取满足HAVING子句中组条件表达式的那些组。
4）按SELECT子句中给出的列名或列表达式求值输出。
5）ORDER子句对输出的目标表进行排序，按附加说明ASC升序排列，或按DESC降序排列。
例1：统计每门课程的学生选修人数。
SELECT  COUNT（S#）
FROM  SC
GROUP BY  C#
例2：统计每门课程的学生选修人数，要求只查询出选修人数超过50人的结果。
SELECT  COUNT（S#）
FROM  SC
GROUP BY  C#
HAVING  COUNT（S#）>50
例3：检索每名学生的学号和年龄，查询结果按年龄降序排列，年龄相同按学号升序排列。
SELECT  S#，AGE>
FROM  S
ORDER  BY  AGE  DESC，S#  ASC
5.SELECT子句的具体规定
SELECT  [ALL | DISTINCT] 〈列名或列表达式序列>|*
说明：1）DISTINCT选项保证重复的行将从结构中去除；而ALL选项是默认的，将保证重复的行留在结果中，一般可不必写出。
2）*是对于在FROM子句中命名表的所有列的简写。
3）列表达式中允许出现+、-、*、/等运算符以及列名和常数、聚合函数等。
例1：检索男同学选修的课程的课程号。
SELECT  DISTINCT  C#
FROM  S，SC
WHERE  S.S#=SC.S#  AND  SEX=’男’
例2：检索所有的学生信息。
SELECT *
FROM  S
例3：检索每个学生的学号和出生年份。
SELECT  S#，2008-AGE
FROM  S
6．列和基本表的改名操作
有时，一个基本表在SELECT语句中多次出现，即这个表被多次调用，为区别不同的引用，应给每次的引用加上不同的名字。当然，除此情况之外，也可以给任何一个表起别名。但要注意：一旦给表起了别名，在该语句中，凡是引用该表名时，必须用别名而不能用原表名。
有时，用户也可以要求输出的列名与基本表中列名不一致，可在SELECT子句用“旧名  AS  新名”形式改名。其中AS可以省略。
例：检索每个学生的姓名和出生年份，输出的列名分别为STUDENT_NAME和BIRTH_YEAR。
SELECT  S#  AS  STUDENT_NAME，2008-AGE  AS  BIRTH_YEAR
FROM  S
7．条件表达式中的比较操作
1）算术比较操作：常用运算符有〈、〈=、〉、〉=、=、<>或!=
[NOT]  BETWEEN…AND用来查找（不）在给定范围内的数据，下限在前，上限在后，包括上、下限。
例：检索18—20岁的学生姓名。
SELECT  SNAME
FROM  S
WHERE  AGE〉=18  AND  AGE〈=20
或：
SELECT  SNAME
FROM  S
WHERE  AGE  BETWEEN  18  AND  20
2）字符串的匹配操作
语法：<字符串>  [NOT]  LIKE  <匹配模式>
说明：匹配模式中常使用两个通配符：
%：与零个或多个字符组成的字符串匹配。
_：与单个字符匹配。
例：检索姓名以字符D打头的学生姓名。
SELECT  SNAME
FROM  S
WHERE  SNAME  LIKE  ‘D%’
转义字符：为使字符串中包含特殊字符（即%和_），允许定义转义字符。转义字符紧靠特殊字符并放在它前面，表示该特殊字符被当成普通字符。使用ESCAPE定义转义字符。
例：LIKE  ‘ab\%cd%’  ESCAPE  ‘\’  匹配所有以ab%cd开头的字符串。
LIKE  ‘ab\\cd%’  ESCAPE  ‘\’  匹配所有以ab\cd开头的字符串。
3）空值的比较操作
使用IS  [NOT]  NULL来比较。
例：检索年龄为空值的学生姓名。
SELECT  SNAME
FROM  S
WHERE  AGE  IS  NULL
4）集合成员资格的比较
语法：[NOT]  IN  （<集合>）
例1：检索年龄分别是18、20、21岁的学生姓名。
SELECT  SNAME
FROM  S
WHERE  AGE  IN（18，20，21）
例2：检索男同学选修课程的课程号。
SELECT  DISTINCT  C#
FROM  SC
WHERE  S#  IN（SELECT  S#  FROM  S  WHERE  SEX=‘男’）
5）集合成员的算术比较
语法：元组  θ  ALL|SOME|ANY（〈集合〉）
说明：ALL表示左边那个元组与右边集合中每一个元组满足θ运算；
ANY和SOME含义相同，表示左边那个元组与右边集合中至少一个元组满足θ运算。
例1：检索学习C2课程的学生学号和姓名。
SELECT  S#，SNAME  
FROM  S
WHERE  S#  =  SOME（SELECT  S#
FROM  SC
WHERE  C#=‘C2’）
例2：检索不学C2课程的学生姓名与年龄。
SELECT  SNAME，AGE
FROM  S
WHERE  S#〈〉ALL（SELECT  S#
FROM  SC
WHERE  C#=‘C2’）
6）集合空否的测试
使用[NOT]EXISTS。见前面例子。
四、数据更新
1．数据插入
语法：1）单元组的插入：
INSERT  INTO  <基本表名>  [（<列名序列>）]  VALUES（<元组值>）
2）多元组的插入
INSERT  INTO  <基本表名>  [（<列名序列>）]  VALUES（<元组值>）,
（<元组值>）,…, （<元组值>）
  3）查询结果的插入
INSERT  INTO  <基本表名>  [（<列名序列>）]  <SELECCT查询语句>
4）表的插入
INSERT  INTO  <基本表名1>  [（<列名序列>）]  TABLE   <基本表名2>
说明：在上述各种插入语句中，如果插入的值在属性个数、顺序与基本表的结构完全一致，那么基本表后的（<列名序列>）可省略，否则必须详细列出。
2．数据删除
语法：DELETE  FROM  <基本表名>
[WHERE  <条件表达式>]
说明：如果省略WHERE子句，表示删除基本表中所有元组。
例：把课程名为MATHS的成绩从基本表SC中删除。
DELETE  FROM  SC
WHERE  C#  IN（SELECT  C#  FROM  C  WHERE  CNAME=‘MATHS’）
3．数据修改
语法：UPDATE  <基本表名>
SET  <列名>=<值表达式>[,<列名>=<值表达式>…]
| ROW=(<元组>)  [WHERE  <条件表达式>]
例1：把C5课程的课程名改为DB。
UPDATE  C
SET  CNAME=‘DB’
WHERE  C#=‘C5’
例2：把课程号为C5的元组修改为（C5，DB，T3）
UPDATE  C
SET  ROW=（‘C5’，‘DB’，‘T3’）
WHERE  C#=‘C5’
五、视图
1．在SQL中，外模式一级数据结构的基本单位是视图。
2．视图的定义：视图是从若干基本表和（或）其他视图构造出来的表。这种构造方法采用SELECT语句实现。在我们创建一个视图时，只是把其视图的定义存放在数据字典中，而不存储视图对应的数据，在用户使用视图时才去求对应的数据。因此，视图被称为“虚表”。
3．视图的创建
语法：CREATE  VIEW  <视图名>（<列表序列>）
AS  <SELECT查询语句>
4．视图的撤销
语法：DROP  VIEW  <视图名>
5．对视图的操作
1）视图的查询操作，与基本表一样。
2）视图的更新操作，一般仅限于“行列子集视图”。
行列子集视图：如果视图是从单个基本表中只使用选择、投影操作导出的，并且包含了基本表的主键，那么这样的视图称为“行列子集视图”。
六、嵌入式SQL
1．SQL语言有两种使用方式：一种是在终端交互方式下使用，称为交互式SQL；另一种是在主语言的程序中使用，称为嵌入式SQL。
2．嵌入式SQL的实现，有两种处理方式：一种是扩充主语言的编译程序，使之能处理SQL语句；另一种是采用预处理方式。目前多数系统采用后一种方式。
预处理方式是先用预处理程序对源程序进行扫描，识别出SQL语句，并处理成主语言的函数调用形式；然后再用主语言的编译程序编译成目标程序。
3．嵌入式SQL的使用规定：
1）在程序中要区分SQL语句与主语言语句。
2）允许嵌入的SQL语句引用主语言的程序变量（成为共享变量）。
3）通过游标协调SQL的集合处理方式和主语言的单记录处理方式
七、存储过程
1．存储过程是使用SQL语句和流程控制语句编写的模块，存储过程经编译和优化后存储在数据库服务器端的数据库中，使用时调用即可。
2．使用存储过程的优点：
1）提高运行速度；
2）增强了SQL的功能和灵活性。
3）可以降低网络的通信量；
4）减轻了程序编写的工作量；
5）间接实现安全控制功能。