第四章关系数据库查询语.ppt

1、第4 章 关系数据库查询语言SQL,要点： SQL基本概念、特点 数据定义语句、数据查询语句、数据操纵语句 视图 嵌入式SQL 存储过程与函数,SQL的基本概念,SQL的发展 1974年由Boyce和Chamberlin首先提出 其后经历了ANSI（美国国家标准机构）SQL、SQL-92、SQL-99、SQL-2003、SQL-2006和SQL-2008等标准,SQL的特点,SQL功能强大集数据定义语言（DDL）、数据操纵语言（DML）和数据控制语言（DCL）于一体，使用统一的语法形式，完成数据定义、数据查询、数据更新和数据控制功能，易学易用。 对SQL语句的解释由DBMS完成，语句对在何处断

2、行没有特别的要求，对大小写不敏感。 SQL的操作对象和操作结果都是元组的集合。 SQL是高度非过程化的语言。,SQL数据类型, 字符串： CHAR(n), NCHAR(n), VARCHAR(n), NVARCHAR(n) 整数：SHORTINT、INT（或INTEGER） 浮点数：FLOAT, REAL, DOUBLE 定点数：DECIMAL(n, d)或NUMERIC(n, d)表示由n位有效数字（不包括符号和小数点）组成的十进制定点数，小数点后有d位数字。 日期和时间：分别用DATE和TIME表示，实际上是某个特定格式的字符串，日期形如YYYY-MM-DD（年-月-日），时间形如HH:M

3、M:SS（时:分:秒）。 其它类型：大文本TEXT，BLOB大二进制，Geometry等,数据定义语句,DDL（Data Definition Language，数据定义语言） 定义和修改关系数据库的逻辑结构，包括基本表（关系模式）、视图、索引和域。 定义信息保存在数据字典中。 SQL Server 中 以 sys 开始的一系列表 数据字典是数据库系统中各类数据描述的一个集合。,关系模式的定义与删除,CREATE TABLE表名( 属性列名数据类型列级完整性约束 , 属性列名数据类型列级完整性约束 , , 表级完整性约束 );,数据定义语言DDL,完整性约束的定义形式主要有以下5种： NOT

4、NULL：列级完整性约束，表示某个属性不能取空值。 UNIQUE：列级完整性约束，表示某个属性的取值必须唯一。 PRIMARY KEY：可以作为列级完整性约束，表示某个属性为主码；也可以作为表级完整性约束，用PRIMARY KEY()子句来定义，表示属性列表共同构成这个表的主码；,数据定义语言DDL, CHECK()子句：作为表级完整性约束，说明每个进入表中的元组必须满足的条件。 FOREIGN KEY() REFERENCES (属性名2)子句：作为表级完整性约束，说明表的外码，表示所定义表中的属性（即“属性名1”）与另一个表中的属性（即“属性名2”）相对应。,服装销售 数据库 数据,数据定

5、义语言 Create Table,【例4-1】 建立第3章服装销售系统数据库中的各个表。 CREATE TABLE 服装 ( 服装编号 char(4) NOT NULL UNIQUE, 品牌 char(20), 型号 char(5), 颜色char(2), 价格 unsigned int, PRIMARY KEY(服装编号) );,/与NOT NULL UNIQUE等价，可省略,数据定义语言 Create Table,CREATE TABLE 顾客 ( 顾客编号 char(4), 姓名 char(20), 性别 char(2), 年龄 unsigned int , 电话 char(11), P

6、RIMARY KEY(顾客编号) );,CREATE TABLE 顾客 ( 顾客编号 char(4) PRIMARY KEY, 姓名 char(20), 性别 char(2), 年龄 unsigned int , 电话 char(11);,数据定义语言 Create Table,CREATE TABLE 购买记录( 服装编号 char(4), 顾客编号 char(4), 购买日期 date, 数量 unsigned int, PRIMARY KEY(服装编号, 顾客编号), FOREIGN KEY(服装编号) REFERENCES 服装(服装编号), FOREIGN KEY(顾客编号) REF

7、ERENCES 顾客(顾客编号) );,数据定义语言 Drop Table,DROP TABLE ; 用DROP TABLE语句将某个基本表删除后表中的数据连同表的结构都从数据库中消失了。 【例4-2】 删除顾客关系。 DROP TABLE 顾客;,数据定义语言 Alter Table,基本表建立以后，可根据实际需要对其结构进行修改，如增加列或删除约束等。 增加列默认为空，不能使用NOT NULL约束。 ALTER TABLE ADD 完整性约束1, , 完整性约束n DROP MODIFY () ;,数据定义语言 Alter Table,【例4-3】 向顾客关系中增加“地址”属性。 ALTE

8、R TABLE 顾客 ADD 地址 char(50); 【例4-4】 修改顾客关系属性姓名为30位定长字符串。 ALTER TABLE 顾客 MODIFY 姓名 char(30);,域定义,域约束是最基本的完整性约束形式。当向数据库中插入数据时，数据库管理系统会检测插入的数据是否符合域的约束。域定义语句的语法如下： CREATE DOMAIN NOT NULL DEFAULT CHECK ; 【例4-5】 定义地址域，允许为空值。 CREATE DOMAIN 地址 CHAR(50) NULL;,索引Index,索引的定义有利于提高查询速度，SQL可以创建和删除索引文件。 基本表建立以后，数据库

9、管理员或表的建立者可以根据需要在基本表上建立一个或多个索引文件，以提供多种存取路经，加快存取速度。,索引Index,索引的定义： CREATE UNIQUE CLUSTER INDEX 索引名 ON表名( 属性列名ASC|DESC , 属性列名ASCDESC ); UNIQUE表示索引的每一个索引值只对应唯一的数据记录 CLUSTER：建聚簇索引，即索引项顺序与表中记录的物理顺序一致，一个基表只能建一个聚簇索引 ASC(升序，缺省)、DESC(降序) 索引建立后由系统使用和维护，不需用户干预,索引Index,【例4-6】 为顾客关系按姓名的升序建立聚簇索引。 CREATE CLUSTERED

10、INDEX IdxCname ON 顾客(姓名); 【例4-7】 为购买记录关系按顾客编号的升序、服装编号的降序建立索引文件。 CREATE INDEX IdxCCID ON 购买记录(顾客编号, 服装编号 DESC); 【例4-8】 为服装关系按价格的降序建立唯一性索引。 CREATE UNIQUE INDEX IdxCprice ON 服装(价格 DESC);,索引Index,索引的删除： 当一个索引不再需要时，就可以用DROP INDEX语句将其删除，格式如下： DROP INDEX ; 【例4-9】 删除会员信息关系的索引IdxCname。 DROP INDEX IdxCname;,数

11、据查询语句,所谓数据查询，就是从数据库所保存的众多数据中挑出符合某种条件的一部分数据，或者将这些数据挑出来之后对它们进行适当的运算，然后得到某种汇总结果（即统计信息）。 数据查询的对象可以是基本表，也可以是视图,查询语句的基本结构,SELECT ALL|DISTINCT, FROM , WHERE GROUP BY HAVING ORDER BY ASC|DESC;,数据查询,整个SELECT语句的含义是， 根据WHERE子句的条件表达式，从FROM子句指定的基本表或视图中找出满足条件的元组， 再按SELECT子句中的目标列表达式，选出元组中的属性值形成结果表。 如果有GROUP BY子句，则

12、将结果按“列名1”的值进行分组，该属性列值相等的元组为一个组。通常会对每组中的记录用集函数。 如果有GROUP子句带HAVING短语，则只有满足指定条件的组才予输出。 如果有ORDER BY子句，则结果表还要按“列名2”的值的升序（ASC，系统默认值）或降序（DESC）排序。,简单SQL查询,简单查询又叫单表查询，是指查询条件和内容都只涉及一个基本表的查询。 【例4-10】 查询所有顾客的信息。 SELECT * FROM 顾客; SELECT子句指出被选择的目标列表的名称，“*”表示表的所有属性，FROM子句指出表的名称，查询结果就是会员信息关系中所有顾客的信息。,简单SQL查询,【例4-1

13、1】 查询所有顾客的编号、姓名及电话。 SELECT 顾客编号, 姓名, 电话 FROM 顾客;,简单SQL查询DISTINCT,【例4-12】 查询购买了服装的顾客编号。 SELECT DISTINCT 顾客编号 FROM 购买记录; 用DISTINCT去掉重复行。如果没有指定DISTINCT短语，则默认为ALL，即保留结果表中取值重复的行。,简单SQL查询WHERE子句,WHERE用来设定关系中的元组选择条件，只有满足这些条件的元组才能出现在结果中，相当于关系代数中的选择操作。 WHERE子句常用的查询条件可以是关系表达式或逻辑表达式，可以使用(NOT) IN、IS (NOT) NULL、

14、(NOT) BETWEEN AND、(NOT) LIKE等谓词。,简单SQL查询 复合条件,【例4-13】 查询20岁以下的女顾客的编号和姓名。 SELECT 顾客编号, 姓名 FROM 顾客 WHERE 年龄（或!=）、=、=等比较运算符的关系表达式，也可以是由多个条件通过AND、OR、NOT等逻辑运算符组成的逻辑表达式。如果指定的条件是字符串类型的，需要把字符串用单引号括起来。,简单SQL查询Between,【例4-14】 查询年龄在1830岁的顾客的姓名和性别。 SELECT 姓名, 性别 FROM 顾客 WHERE 年龄 BETWEEN 18 AND 30; 表达式“A BETWEEN

15、 B AND C”能够确定范围，等价于“A=B AND A=C”。与之相对的表达式是“A NOT BETWEEN B AND C”，用于查找属性值不在指定范围内的元组。,简单SQL查询 in,【例4-15】查询来自“佐丹奴”和“李宁”两个品牌的服装的信息。 SELECT * FROM 服装 WHERE 品牌 IN (佐丹奴, 李宁); 谓词IN用来确定集合，可以用来查找属性值属于指定集合的元组。与之相对的谓词是NOT IN，用于查找属性值不属于指定集合的元组。,简单SQL查询 Like,【例4-16】 查询所有姓王的顾客的信息。 SELECT * FROM 顾客 WHERE 姓名 LIKE 王

16、%; 使用LIKE谓词，可以查询指定的属性列值与相匹配的元组。其格式如下： NOT LIKE ESCAPE 可以是一个完整的字符串，也可以含有通配符“%”或“_”。 “%”表示可以用任意长度的字符串替代。 “_”表示可以用任意单个字符替代。 如acb、aaccb、ab等都与匹配串“a%b”匹配，acb、adb等都与匹配串“a_b”匹配。,简单SQL查询字符 _ %,【例4-17】 查询所有姓李但全名为两个字的顾客的信息。 SELECT * FROM 顾客WHERE 姓名 LIKE李_ _; 注意：一个汉字要占两个字符的位置，所以匹配串李后面需要跟两个空格。 【例4-18】 查询所有不姓李的顾客

17、的信息。 SELECT * FROM 顾客WHERE 姓名 NOT LIKE 李%;,简单SQL查询字符_%转意,但当“%”或“_”不是作为匹配串而是数据库中某字符串属性值的组成部分时，就需要告诉系统“%”或“_”需要进行转义。转义的方法是使用“ESCAPE”短语，用户可以自己设定换码字符，如“”、“y”等。 例如，“LIKE % ESCAPE ”中使用了“ESCAPE ”短语，就表明“”是换码字符，匹配串“%”中的“%”指的是普通字符“%”，因此该匹配串将匹配所有以“%”开始并以“%”结束的字符串。,简单SQL查询字符_%转意,【例4-19】 查询姓名包含Kim_Jae的顾客的信息。 SEL

18、ECT * FROM 顾客 WHERE 姓名 LIKE %Kim_Jae%ESCAPE ; 通过转义，“”后面的“_”作为一个普通字符使用。,简单SQL查询NULL空值,判断属性值或输入值是否为空值，可以用谓词IS NULL和IS NOT NULL，这里的“IS”不能用“”代替。 【例4-20】 查询电话是空值的顾客的姓名和性别。 SELECT 姓名, 性别 FROM 顾客 WHERE 电话 IS NULL; 【例4-21】 查询电话不是空值的顾客的姓名和性别。 SELECT 姓名, 性别 FROM 顾客WHERE 电话 IS NOT NULL;,简单SQL查询ORDER BY,返回查询结果时

19、，元组的排列顺序与数据的存储顺序相同。如果用户需要按照某种指定的顺序来显示查询结果，就需要使用ORDER BY子句。该子句只作用于查询结果，并不会改变数据库中的实际存储顺序。 排序的依据可以是基于一个或多个属性数据。当依据多个属性排序时，如果第一个属性值相等，就按照第二个属性值排序，以此类推。,简单SQL查询ORDER BY,【例4-22】 查询所有顾客的姓名和年龄，并按照年龄降序排列。 SELECT 姓名, 年龄 FROM 顾客 ORDER BY 年龄 DESC; 【例4-23】 查询顾客C001的购买记录，按购买日期降序排列，相同购买日期的按数量升序排列。 SELECT * FROM 购买

20、记录 WHERE 顾客编号=C001 ORDER BY 购买日期 DESC, 数量;,简单SQL查询聚集函数,聚集函数（Aggregation Function）是一组对查询结果中的某属性列进行统计的函数，包括： COUNT(DISTINCT ) ：统计该属性列中值的个数，如果加DISTINCT，表示统计时不考虑重复值； COUNT(*) ： 统计关系中元组的个数。 SUM(DISTINCT )：统计该属性列中值的总和。该属性必须是数值型的。 AVG(DISTINCT )：统计该属性列中值的平均。该属性必须是数值型的。 MAX()：统计该属性列中的最大值。 MIN()：统计该属性列中的最小值。

21、 特别地，除COUNT(*)之外，聚集函数在做统计之前，都先把属性列中的空值去掉。如果该属性列中都是空值，则COUNT函数返回0，其他函数返回NULL。,简单SQL查询聚集函数,【例4-24】 查询顾客的最低年龄。 SELECT MIN(年龄) FROM 顾客; 【例4-25】 查询女顾客的平均年龄。 SELECT AVG(年龄) FROM 顾客 WHERE 性别=女; 【例4-26】 查询顾客的总人数。 SELECT COUNT(*) FROM 顾客; 【例4-27】 查询购买了服装的顾客总人数。 SELECT COUNT(DISTINCT 顾客编号) FROM 购买记录;,简单SQL查询G

22、ROUP BY,使用GROUP BY子句的查询称为分组查询。GROUP BY子句将一个表按照指定属性组值相等的记录进行分组，再对每个组的数据进行相应的操作。当查询语句中使用聚集函数时，GROUP BY子句将控制聚集函数运算的范围。 通常，与GROUP BY子句一起使用的还有一个HAVING子句。与WHERE子句相同的是，HAVING子句也描述条件，不同的是，HAVING子句描述的是分组条件，只有满足分组条件的组才选出来处理。,简单SQL查询GROUP BY,【例4-28】 查询每天的购买记录数。 SELECT 购买日期, COUNT(*) 购买记录数 FROM 购买记录 GROUP BY 购买

23、日期;,简单SQL查询GROUP BY,【例4-29】 查询购买记录数在20笔以上的购买日期。 SELECT 购买日期 FROM购买记录 GROUP BY 购买日期 HAVING COUNT(*)20; 【例4-30】 查询平均价格低于300元的品牌及其平均价格。 SELECT 品牌, AVG(价格) FROM 服装GROUP BY 品牌 HAVING AVG(价格)300;,连接查询,又称为多关系查询。包括等值连接、自然连接、非等值连接、自身连接、外连接查询等类型。 1等值与非等值连接查询 将两个表中对应属性列值相等的行连接起来，即当连接条件运算符为“=”时，称为等值连接。使用其他运算符称为

24、非等值连接。若在等值连接中把目标列中重复的属性去掉则为自然连接。,连接查询内连接,【例4-31】 查询购买了任意服装的顾客的编号和姓名。 SELECT DISTINCT 顾客.顾客编号, 姓名 FROM 顾客, 购买记录 WHERE 顾客.顾客编号=购买记录.顾客编号; SELECT子句和WHERE子句中都用到了“表名.列名”这种格式来表示某一列属于哪个表，以消除属性列的二义性。但是如果某一列名在参加连接的各表中是唯一的，那么该列名前的表名是可以省略的。 利用SELECT语句进行表的连接时，必须在WHERE子句中指明连接条件，否则就是做两个表的笛卡儿积，其连接结果一般是无意义的。,SELECT

25、 DISTINCT 顾客.顾客编号, 姓名 FROM 顾客, 购买记录,连接查询自身连接,2自身连接 连接操作可以在不同的表之间进行，也可以在同一个表中进行。对同一个表进行的连接查询称为自身连接查询。 【例4-32】 查询跟张珊年龄相同的顾客ID和姓名。 SELECT C1.顾客编号, C1.姓名 FROM 顾客 C1,顾客 C2 WHERE C1.年龄= C2.年龄 AND C2.姓名=张珊; FROM子句中会员信息被打开两次，为区分两者所以分别赋以不同的别名C1、C2。,连接查询外连接,3外连接(Outer Join) 内连接的查询结果都是满足连接条件的元组。但是，在内连接的查询结果中，一

26、些重要的信息可能会因为连接条件不满足而被丢失。如果允许结果关系中出现的不满足连接条件的某些元组，这种连接称为外连接。外连接的表示方法为 SELECT , , FROM LEFT/RIGHT/FULL OUT JOIN ON 约束条件; 左外连接列出左边关系中所有的元组，右外连接列出右边关系中所有的元组。,连接查询外连接,【例4-33】 查询每个顾客的基本信息及其购物信息。若顾客没有购物，则显示基本信息，其购物信息用空值表示。 SELECT 顾客.顾客编号, 姓名, 性别, 年龄, 电话, 服装编号, 购买日期, 数量 FROM 顾客 LEFT JOIN 购买记录 ON(顾客.顾客编号=购买记录

27、.顾客编号);,连接查询外连接,SELECT 顾客.顾客编号, 姓名, 性别, 年龄, 电话, 服装编号, 购买日期, 数量 FROM 顾客 LEFT OUT JOIN 购买记录 ON(顾客.顾客编号=购买记录.顾客编号);,连接查询多表,4多表连接查询 如果查询涉两个以上的表，则称为多表连接查询。要注意定义表之间的连接条件。 【例4-34】 查询每个顾客的基本信息、购物信息及其所购服装的详细信息，结果按顾客编号升序排列。 SELECT * FROM 顾客, 购买记录, 服装 WHERE 顾客.顾客编号=购买记录.顾客编号 AND 购买记录.服装编号=服装.服装编号 ORDER BY 顾客.顾

28、客编号;,连接查询多表,【例4-35 查询购买过“李宁”服装的顾客信息。 SELECT DISTINCT 顾客.* FROM 顾客, 购买记录, 服装 WHERE 顾客.顾客编号=购买记录.顾客编号 AND 购买.服装编号=服装.服装编号 AND 品牌=李宁;,子查询 (Subquery),在一个查询语句的WHERE或HAVING条件子句中嵌入另一个查询，这种具有层次关系的查询称为嵌套查询。两个查询互相称为父查询和子查询。嵌套查询可以是多层的。 【例4-36】 查询购买了“W003”服装的顾客姓名和电话。 SELECT 姓名, 电话 FROM 顾客 WHERE 顾客编号 IN (SELECT

29、顾客编号 FROM 购买记录 WHERE 服装编号=W003);,子查询,不相关子查询：子查询的条件中不涉及父查询中的属性列 系统对子查询先行求值，然后把子查询的结果作为父查询的条件组成部分，进行父查询的处理。子查询只执行一次，处理过程较为简单。 相关子查询：子查询的条件中涉及父查询中的属性列 逐一考察父查询中的每个元组（当前元组），得到相应属性值后传入子查询作为条件，执行子查询，子查询得到的结果又作为父查询的条件组成部分，继续对当前元组进行处理。依此类推，直至父查询中所有元组被处理完。子查询多次运行，每次执行都是针对父查询中的一个元组。,子查询,根据父查询与子查询之间的连接符的不同，可以将子

30、查询分为： 带有比较运算符的子查询， 带有IN谓词的子查询， 带有ANY或ALL谓词的子查询， 带有EXISTS谓词的子查询。,子查询比较运算符,1带有比较运算符的子查询 当确定子查询返回的结果是单值时，可以使用比较运算符（、=、=、）来连接父查询与子查询。 【例4-37】 查询跟张珊年龄相同的顾客编号和姓名。 SELECT 顾客编号, 姓名 FROM 顾客C1 WHERE C1.年龄= (SELECT 年龄 FROM 顾客C2 WHERE C2.姓名=张珊);,子查询IN谓词,2带IN谓词的子查询 当子查询的查询结果包含多个值时，经常会使用IN谓词来连接子查询和父查询。 【例4-38】 查询

31、购买了“李宁”服装的顾客姓名。 SELECT 姓名 FROM 顾客 WHERE 顾客编号 IN( SELECT 顾客编号 FROM 购买记录 WHERE 服装编号 IN( SELECT 服装编号 FORM 服装 WHERE 品牌=李宁) ); 有些嵌套查询可以用连接运算替代，到底采用哪种方法，用户可以根据自己的习惯确定。,子查询ANY/ALL谓词,3带有ANY或ALL谓词的子查询 ANY谓词表示子查询结果中的某个值，ALL谓词表示子查询结果中的所有值。ANY或ALL谓词必须与比较运算符一起使用。 【例4-39】 查询没有购买W001 服装的顾客姓名。 SELECT 姓名 FROM 顾客 WHE

32、RE 顾客编号ALL (SELECT 顾客编号 FROM 购买记录 WHERE 服装编号=W001); 这个查询可以用另一种形式表示： SELECT 姓名 FROM 顾客 WHERE 顾客编号 NOT IN (SELECT 顾客编号 FROM 购买记录 WHERE 服装编号=W001);,子查询ANY/ALL谓词,【例4-40】 查询比“佐丹奴”任意一款服装价格低的服装ID。 SELECT 服装编号 FROM 服装 WHERE 价格 ANY (SELECT 价格 FROM 服装 WHERE 品牌=佐丹奴); 这个查询可以用另一种形式表示： SELECT 服装编号 FROM 服装 WHERE 价

33、格 (SELECT MAX(价格) FROM 服装 WHERE 品牌=佐丹奴);,子查询ANY/ALL谓词,事实上，用比较运算符与ANY 或ALL 谓词配合使用所表示的子查询，其查询效果等价于用集函数或INANY 或ALL 谓词来表示的子查询。其对应关系如下图所示。,子查询EXISTS谓词,4带有EXISTS 谓词的子查询 EXISTS 谓词用于判断是否有值存在。如果在一个子查询之前加EXISTS 谓词，则子查询不管返回的结果是什么，只要有值返回，就取“真”。与之对应的是NOT EXISTS 谓词，放在子查询之前，表示当子查询没有任何值返回时就取“真”。,子查询EXISTS谓词,【例4-41】

34、 查询没有购买过任何服装的顾客的顾客编号和姓名。 SELECT 顾客编号, 姓名 FROM 顾客 WHERE NOT EXISTS (SELECT * FROM 购买记录 WHERE 购买记录.顾客编号=顾客.顾客编号); 显然，这个查询是相关子查询，子查询的每次执行都与父查询中某个特定元组相关。,子查询EXISTS谓词,在SQL 中只有对应于存在量词的EXIST 谓词，而没有对应于“所有”、“全部”等全称量词的谓词。要实现全称量词的查询，只能借助存在量词，依据等价转换原则来实现，即,子查询EXISTS谓词,【例4-42】 查询购买了所有服装的顾客的姓名。 即查询这样一些顾客的姓名，没有一种服

35、装他没有购买。 SELECT 姓名 FROM 顾客 WHERE NOT EXISTS (SELECT * FROM 服装 WHERE NOT EXISTS (SELECT * FROM 购买记录 WHERE 顾客编号=顾客.顾客编号 AND 服装编号=服装.服装编号) );,SELECT FROM 顾客 WHERE NOT EXISTS (SELECT * FROM 服装 WHERE NOT EXISTS (SELECT * FROM 购买记录 WHERE ) );,集合查询,SQL 查询的结果是元组的集合，所以多个查询的结果可以进行集合操作。 集合操作主要包括： 并UNION 交INTERS

36、ECT 差EXCEPT 参加集合操作的各查询结果的列数必须相同，对应列的数据类型也必须相同。 默认按照集合处理，先去重复，然后进行集合计算,集合查询UNION,1并UNION 【例4-43】 查询女顾客和年龄小于30岁的顾客的信息。 SELECT * FROM 顾客 WHERE 性别=女 UNION SELECT * FROM 顾客 WHERE 年龄30; 以上查询可以等价的表示如下： SELECT * FROM 顾客 WHERE 性别=女 OR 年龄30;,集合查询INTERSECT,2交INTERSECT 【例4-44】 查询女顾客和年龄小于30岁的顾客的交集。 SELECT * FROM

37、 顾客 WHERE 性别=女 INTERSECT SELECT * FROM 顾客 WHERE 年龄30; 以上查询可以等价地表示如下： SELECT * FROM 顾客 WHERE 性别=女 AND 年龄30;,集合查询EXCEPT,3差EXCEPT 【例4-45】 查询女顾客和年龄小于30岁的顾客的差集。 SELECT * FROM 顾客 WHERE 性别=女 EXCEPT SELECT * FROM 顾客 WHERE 年龄=30;,数据操纵语句,数据操纵是指对数据库中的对象（基本表和视图）进行更新，即修改、插入和删除等操作。 UPDATE、INSERT和DELETE语句来完成的。,数据操

38、纵语句 INSERT,1插入数据 （1）用VALUES子句向表中插入一条记录 INSERT语句形式为： INSERT INTO , , VALUES(, , ) “列名”是将要输入值的列名，它们与VALUES子句中的值要相对应。如果缺省“列名”，则必须由VALUES子句提供所有列的值。尤其要注意的是，被定义为NOT NULL的列必须给值。 【例4-46】 把顾客赵陆的记录加入到会员信息中。 INSERT INTO 顾客 VALUES(C004, 赵陆, 男,27,;,数据操纵语句DML INSERT,（2）用子查询向表中插入多条记录 INSERT语句形式为： INSE

39、RT INTO (, , ) SELECT , , FROM WHERE 这种形式的INSERT语句可把取自其他表中的数据插入到一个表中，不限制插入的行数。同时，SELECT语句可以是简单的查询，也可以是复杂查询，其查询结果即是插入的数据。,数据操纵语句DML INSERT,【例4-47】 创建服装及其购买者平均年龄的表，并根据数据库中数据填入内容。 CREATE TABLE 服装_顾客年龄 (服装编号CHAR(4), 顾客平均年龄 int, PRIMARY KEY (服装编号); INSERT INTO 服装_顾客年龄(服装编号, 顾客平均年龄) SELECT 服装编号, AVG(年龄) F

40、ROM 顾客, 购买记录 WHERE 顾客.顾客编号=购买记录.顾客编号 GROUP BY 服装编号;,数据操纵语句 UPDATE,2更新数据 更新数据库中的记录用UPDATE语句。语法如下： UPDATE SET=, =, WHERE; SET子句提供要修改的列名和将要存储的新值。如果指定WHERE子句，则将确定这些列中的哪些行将被修改；如果WHERE子句缺省，则这些列中的所有行都将被修改。,数据操纵语句 UPDATE,（1）修改单个元组的值 【例4-48 修改顾客张珊的电话号码为。 UPDATE 顾客 SET 电话WHERE 姓名=张珊;

41、 （2）修改多个元组的值 【例4-49 把所有顾客的年龄加1岁。 UPDATE 顾客 SET 年龄=年龄+1;,数据操纵语句 UPDATE,（3）带子查询的修改 在标准SQL中，UPDATE语句中的WHERE子句可以包含子查询，用于构造修改的条件。 【例4-50 把在“20100516”这一天有过销售记录的服装价格减20。 UPDATE 服装 SET 价格=价格-20 WHERE 服装编号 IN (SELECT 服装编号 FROM 购买记录 WHERE 购买日期=20100516);,数据操纵语句 DELETE,3删除数据 删除数据的语句格式为： DELETE FROM WHERE; DELE

42、TE命令用于删除表中指定的某些行，如果有WHERE子句，则所有满足条件的行全被删除；如果没有，则表中所有行都被删除。 如果省略WHERE子句，表示删除表中全部元组，但表的定义仍在数据字典中。也就是说，DELETE语句删除的是表中的数据，而不是关于表的定义。,数据操纵语句 DELETE,【例4-51】 删除所有的购买记录。 DELETE FROM 购买记录; 子查询同样也可以嵌套在DELETE语句中，用以构造执行删除操作的条件。删除数据时，应该考虑删除掉相关联的信息，保证数据库中数据的完整性。 【例4-52】 删除“李宁”牌服装及其所有购买记录。 DELETE FROM 购买记录 WHERE 服

43、装编号 IN(SELECT 服装编号 FROM 服装 WHERE 品牌=李宁); DELETE FROM 服装 WHERE 品牌=李宁;,数据操纵语句DML,数据更新语句INSERT、UPDATE和DELETE，事项： 更新操作一次只能对一个表进行，如果希望更新多个表，则必须做多次更新操作。 在进行更新操作时，必须考虑数据库的完整性。如例4-49中两个语句如果交换顺序，会破坏数据库的参照完整性。,视图View,视图的概念： 视图是从一个或几个基本表（或视图）导出的表，与基本表不同，是一个虚表。数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。 基本表中的数据发生

44、变化，从视图中查询出的数据也就随之改变了。 视图就像一个窗口，透过它，可以看到数据库中自己感兴趣的数据及其变化。 视图一经定义，就可以和基本表一样被查询、被删除，我们也可以在一个视图之上再定义新的视图，但对视图的更新（增加、删除、修改）操作则有一定的限制。,视图View,视图的作用 ： 视图是关系数据库系统提供给用户以多种角度观察数据库中的数据的重要机制。 视图可以简化数据库用户的操作。 视图给数据库的安全性控制带来方便。 视图为数据库系统提供了一定程度的逻辑独立性。,视图View创建,语句格式如下： CREATE VIEW 视图名(视图列名表) AS WITH CHECK OPTION; 视

45、图的数据是子查询的结果。 视图列名表是个可选项，当不选该项时，新生成视图的列名与SELECT命令所选择数据列的名称相同。如果选择该项时，则给SELECT命令所选择的数据重新起个名字作为视图的列名，它们的对应关系是按顺序对应。 “WITH CHECK OPTION”也是一个选择项，当选择该项时，用户必须保证在向视图中插入数据时，该数据能够满足视图定义中SELECT命令所指定的条件。,视图View创建,【例4-53】 创建一个视图，显示“李宁”牌服装的信息。 CREATE VIEW 服装_李宁 AS SELECT 服装编号, 型号, 颜色, 价格 FROM 服装 WHERE 品牌=李宁;,视图Vi

46、ew,组成视图的属性列名或者全部省略或者全部指定，没有第三种选择。如果省略了视图的各属性列名，则隐含该视图由子查询中SELECT子句目标列中的诸字段组成。但在下列三种情况下必须明确指定组成视图的所有列名： 某个目标列不是单纯的属性名，而是集函数或列表达式。 多表连接时选出了几名同名列作为视图的字段。 需要在视图中为某个列启用新的更适合的名字。,视图View,【例4-54】 建立一个30岁以上顾客的ID、姓名、性别、出生年份的视图。 CREATE VIEW 顾客1(顾客编号, 姓名, 性别, 出生年份) AS SELECT顾客编号, 姓名, 性别, 2010-年龄 FROM 顾客 WHERE 年

47、龄30; 如果希望今后可以对视图进行正确的更新操作，还必须在定义视图时加上WITH CHECK OPTION子句，如上面的语句可以改为： CREATE VIEW 顾客1(顾客编号, 姓名, 性别, 出生年份) AS SELECT顾客编号, 姓名, 性别, 2010-年龄 FROM 顾客 WHERE 年龄30 WITH CHECK OPTION;,视图View,【例4-55】 建立一个30岁以上女顾客的编号、姓名、出生年份的视图。 CREATE VIEW 顾客2 AS SELECT顾客编号, 姓名, 出生年份 FROM 顾客1 WHERE 性别=女; 【例4-56】 建立购买了“李宁”服装的顾客

48、信息及服装信息的视图。 CREATE VIEW 顾客_李宁 AS SELECT 顾客.*, 服装.* FROM 顾客, 购买记录, 服装 WHERE 顾客.顾客编号=购买记录.顾客编号 AND 服装.服装编号=购买记录.服装编号 AND 品牌=李宁；,视图View删除,语句格式如下： DROP VIEW ; DROP VIEW语句将视图的定义从数据字典中删除，由此视图导出的其他视图也将自动删除；若导出此视图的基本表删除，则此视图也将自动删除。 【例4-57】 删除视图“顾客-李宁”。 DROP VIEW 顾客_李宁; 删除视图命令DROP VIEW仅仅删除视图的定义，对基本表及其数据无任何影响

49、。,视图View使用,【例4-58】 查询价格大于400元的“李宁”牌服装的信息 SELECT * FROM 服装-李宁 WHERE 价格400; 对视图进行查询时，系统首先从数据字典中取出该视图的定义，然后把定义中的子查询和视图查询语句结合起来，形成一个修正的查询语句，本例修正后的查询语句为： SELECT 服装编号, 型号, 颜色, 价格 FROM 服装 WHERE 价格400 AND 品牌=李宁; 由于视图的查询实质是对基本表的查询，因此基本表的变化可以反映到视图上，视图就如同“窗口”一样，通过视图可以看到基本表动态的变化。,视图View更新,更新视图是指通过视图来插入、删除和修改数据。

50、由于视图是不实际存储数据的虚表，因此对视图的更新，最终要转换为对基本表的更新。 为防止用户通过视图对数据进行增加、删除、修改时，有意无意地对不属于视图范围内的基本表数据进行操作，可在定义视图时加上WITH CHECK OPTION子句。这样在视图上增删改数据时，DBMS会检查视图定义中的条件，若不满足条件，则拒绝执行该操作。,视图View更新,【例4-59】 修改服装“W003”的价格为360。 UPDATE 服装_李宁 SET 价格=360 WHERE 服装编号=W003; 将转换成对基本表商品信息的更新： UPDATE 服装 SET价格=360 WHERE 品牌=李宁 AND 服装编号=W

51、003;,视图View更新,常见的视图形式可以总结如下。 行列子集视图：视图是从单个基本表导出的，并且只是去掉了基本表的某些行和某些列，但保留了码。 WITH CHECKOPTION视图：定义视图时加上了WITH CHECKOPTION子句。 基于多个基表的视图：定义的视图是由两个以上的基本表导出。 基于视图的视图：定义的新视图是由旧的视图导出。 带表达式的视图：定义的视图中的字段来自字段表达式或常数。 分组视图：定义视图时含有GROUP BY子句。 一般地，行列子集视图是可更新的。除行列子集视图外，还有些视图理论上是可更新的，但它们的确切特征还是尚待研究的课题。还有些视图从理论上是不可更新的

52、。,嵌入式SQL,任何可以联机交互使用的标准SQL语句都可以嵌入到COBOL、C、PASCAL、PL/1、FORTRAN、Ada等宿主语言中，从而满足不同应用开发的需求。 嵌入式SQL的语句分为两类： 可执行SQL语句实现对数据库的操作，包括DDL、DML和DCL语句。 说明性SQL语句用于定义变量.。,内嵌SQL语句的C程序组成,【例4-60】 在服装销售数据库中建立一个名为雇员（EMP）的表，表中有列雇员编号（EMPNO）、姓名（EMPNAME）、工作（JOB）、出生年月（BIRTH）、聘用日期（HIREDATE）、性别（SAL）、部门编号（DEPTNO）。创建者是WANG。,无游标的操作

53、,常用的无游标操作有INSERT、UPDATE、DELETE和只返回一行的SELECT。INSERT、UPDATE和DELETE操作不返回数据行，只返回一个代码，表明操作的成功或失败。 【例4.61】 向雇员表（EMP）中插入一个新记录行，其EMPNO、NAME、JOB、BIRTH、HIREDATE、SAL、DEPTNO分别由宿主变量PNO、PNAME、PJOB、PBIRTH、PHIRE、SEX、PTNO给出。 EXEC SQL INSERT INTO EMP(EMPNO, NAME, JOB, BIRTH, HIREDATE, SAL, BPLACE) VALUES(:PNO,:PNAME,

54、 :PJOB, :PBIRTH, :PHIRE, :SEX, :PTNO),带游标的查询操作,一般地，一条SQL的SELECT语句产生一个结果表（含有多行的数据集合）返回给应用程序，但宿主语言（C语言）一般每次只能处理一个元组的数据。这就存在一个所谓的“阻抗失配”问题，即SQL是在集合上操作的，而宿主语言（C语言）是在集合的成员上操作的。为此需要用游标机制，将集合操作转换成单个元组处理。 游标的作用是：数据库管理系统建立一个结果表，包含通过嵌入在C应用程序中的SQL语句查询获得的所有数据行，通过游标标识并指向结果表的当前行。 一个已命名的游标和一条SELECT语句相关联。游标必须先定义，然后通

55、过三条专门的SQL语句对数据进行操作。,带游标的查询操作,有关游标的命令有四条，如下所示： DECLARE CURSOR /定义游标 OPEN CURSOR /打开游标 FETCH /取一行数据 CLOSE CURSOR /关闭游标 打开游标就是执行相应的SELECT语句的查询操作，检索多行数据，把所有满足查询条件的数据行组成一个集合，称为游标活动集（Active set）或结果表，并把游标指针置于其首端。然后通过取数据操作（FETCH），一行一行地移动游标指针，返回活动集中的数据，把它们传送给宿主变量。查询完成后，应当关闭游标。,带游标的查询操作,游标定义语句的形式为： EXEC SQL D

56、ECLARE 游标名 CURSOR FOR SELECT语句 例如： EXEC SQL DECLARE CSR1 CURSOR FOR SELECT EMPNO, NAME, JOB, BIRTH FROM EMP,带游标的查询操作,打开游标的语句形式为： EXEC SQL OPEN 游标名 例如，打开游标CSR1的命令为： EXEC SQL OPEN CSR1 OPEN语句检查WHERE子句中的宿主变量，并给它赋值（若存在），从而标识出哪些记录行满足查询条件构成活动集。OPEN语句执行后，游标指针指向活动集的第一行数据。,带游标的查询操作,通过游标取数据的语句形式为： EXEC SQL FE

57、TCH 游标名 INTO:宿主变量l, :宿主变量2, 例如，把游标CSR1检索结果赋给宿主变量c#和grade。 EXEC SQL FETCH CSR1 INTO :c#, :grade,带游标的查询操作,关闭游标的语句形式为： EXEC SQL CLOSE 游标名 例如，关闭游标CSR1的命令为： EXEC SQL CLOSE CSR1 关闭游标操作使游标的活动集无定义，不能再进行取数操作。,存储过程,建立存储过程可以指定所使用的程序设计语言。PL/SQL（Procedure Language/SQL，PL/SQL）是编写数据库存储过程的一种过程语言，结合了SQL的数据操作能力和过程化语言

58、的流程控制能力，是SQL的过程化扩展。,PL/SQL的块结构和变量常量的定义,基本的SQL是高度非过程化的语言。ESQL将SQL语句嵌入程序设计语言，借助高级语言的控制功能实现过程化。PL/SQL是对SQL的扩展，使其增加了过程化语句功能。 PL/SQL程序的基本结构是块。所有的PL/SQL程序都是由块组成的。这些块之间可以互相嵌套，每个块完成一个逻辑操作。以下是PL/SQL块的基本结构：,PL/SQL的块结构,/*定义部分*/ DECLARE /*定义的变量、常量等只能在该基本块中使用*/ -变量、常量、游标、异常等 /*当基本块执行结束时，定义就不再存在*/ /*执行部分*/ BEGIN -SQL语句、PL/SQL语句的流程控制语句 EXCEPTION /*遇到不能继续执行的情况称为异常*/ -异常处理部分 /*在出现异常时，采取措施来纠正错误或报告错误*/ END;,PL/SQL的块结构,PL/SQL中定义变量的语法形式如下： 变量名数据类型NOT NULL:=初值表达式 或 变量名数据类型NOT NULL初值表达式 常量的定义类似于变量的如下： 常量名数据类型CONSTANT:=常量表达式 常量必须给一个值，并且该值在存在期间或常量的