数据库第八章Transact-SQL.ppt

《数据库第八章Transact-SQL.ppt》由会员分享，可在线阅读，更多相关《数据库第八章Transact-SQL.ppt（106页珍藏版）》请在三一办公上搜索。

1、An Introduction to Database Systems,数据库系统及应用 基于SQL Server 2000,屠菁,An Introduction to Database Systems,第八章 Transact-SQL,8.1 SQL语言8.2 数据库的操作语言8.3 表的操作语言8.4 数据查询8.5 T-SQL语言基础,An Introduction to Database Systems,8.1 SQL语言,SQL(Structured Query Language,结构化查询语言)分类数据定义语言（DDL,Data Definition Language）数据操纵语言

2、（DML,Data Manipularion Language）数据控制语言（DCL,Data Control Language）数据查询语言（DQL,Data Query Language）,An Introduction to Database Systems,数据定义语言(DDL)是指用来定义和管理数据库以及数据库中的各种对象的语句，这些语句包括CREATE、ALTER和DROP等语句。在SQL Server 2000中，数据库对象包括表、视图、触发器、存储过程、规则、缺省、用户自定义的数据类型等。这些对象的创建、修改和删除等都可以通过使用CREATE、ALTER、DROP等语句来完成。

3、,An Introduction to Database Systems,数据控制语言（DCL）是用来设置或者更改数据库用户或角色权限的语句，这些语句包括GRANT、DENY、REVOKE等语句，在默认状态下，只有sysadmin、dbcreator、db_owner或db_securityadmin等角色的成员才有权利执行数据控制语言。,An Introduction to Database Systems,数据操纵语言(DML)是指用来添加、修改和删除数据库中数据的语句，这些语句包括INSERT、UPDATE、DELETE等。在默认情况下，只有sysadmin、dbcreator、db_o

4、wner或db_datawriter等角色的成员才有权利执行数据操纵语言。,An Introduction to Database Systems,8.2 数据库的操作语句,1.创建数据库使用CREATE DATABASE语句创建数据库在SQL Server 2000中，可用CREATE DATABASE语句来创建一个新数据库和存储该数据库文件。其语法为：,An Introduction to Database Systems,CREATE DATABASE database_nameONPRIMARY(NAME=logical_file_name,FILENAME=os_file_name,

5、SIZE=size,MAXSIZE=max_size,FILEGROWTH=growth_increment,),nLOG ON(NAME=logical_name,FILENAME=os_file_name,SIZE=size,n,An Introduction to Database Systems,其中：l database_name：表示为数据库取的名字，在同一个服务器内数据库的名字必须惟一。数据库的名字必须符合SQL Server系统的标识符命名标准，即最大不得超过128个字符。l PRIMARY：该选项用于指定主文件组中的文件。一个数据库只能有一个主文件。如果没有使用PRIMARY

6、关键字，默认列在语句中的第一个文件即为主文件。l NAME：指定数据库的逻辑名称，这是在SQL Server系统中使用的名称，是数据库在SQL Server中的标识。l FILENAME：指定数据库所在文件的操作系统文件名称和路径，该操作系统文件名和NAME的逻辑名称一一对应。并且路径必须存在。l SIZE：指定数据库的初始容量大小。指定大小的数字size可以使用KB、MB后缀，默认的后缀为MB。Size中不能使用小数，其最小值为512KB，默认值为1MB。如果没有指定主文件的大小，则SQL Server默认为1MB。主文件的size不能小于1MB。l MAXSIZE：指定操作系统文件可以增长

7、到的最大尺寸。计量单位为MB或KB。可以不指定计量单位，则系统默认为MB。如果没有指定可以增长的最大尺寸，则文件可以不断增长直到充满整个磁盘空间。l FILEGROWTH：指定文件增量的大小，当指定数据为0时，表示文件不增长。l 如果没有指定FILEGROWTH，则默认值为10%，每次扩容的最小值为64KB。,An Introduction to Database Systems,【例】创建一个XJGL数据库，该数据库的主文件逻辑名称为XJGL_data，物理文件名为XJGL.mdf，初始大小为1MB，最大尺寸为3MB，增长速度为10%；数据库的日志文件逻辑名称为XJGL_log，物理文件名为

8、XJGL.ldf，初始大小为1MB，最大尺寸为5MB，增长速度为1MB。文件存放在C:下。在查询分析器中通过执行以下语句可以创建此数据库。CREATE DATABASE XJGLON(NAME=XJGL_Data,FILENAME=C:XJGL_Data.MDF,SIZE=1,MAXSIZE=3,FILEGROWTH=10%)LOG ON(NAME=XJGL_Log,FILENAME=C:XJGL_Log.LDF,SIZE=1,MAXSIZE=5,FILEGROWTH=10%)GO,An Introduction to Database Systems,图 创建XJGL数据库成功,An Int

9、roduction to Database Systems,8.2 数据库的操作语句,2.修改数据库使用ALTER DATABASE语句修改数据库3.使用数据库USE DATABASE database_name4.删除数据库DROP DATABASE database_name,An Introduction to Database Systems,8.2 表的操作语句,1.创建表使用CREATE TABLE语句创建数据库使用CREATE TABLE语句创建表其语法形式如下：CREATE TABLE 数据库名.拥有者名.表名(列名 列的属性，n)）其中：列的属性包括列的数据类型、列的长度、列

10、上的约束等。,An Introduction to Database Systems,【例】使用SQL语句创建“班级表”、“课程信息表”、“成绩表”。在查询分析器中运行如下命令：,USE XJGLGOCREATE TABLE 班级表(班级编号 char(8)NOT NULL,系部编号 char(2)NOT NULL,班级名称 char(16)NOT NULL)GO,An Introduction to Database Systems,CREATE TABLE 课程信息表(课程编号 char(4)NOT NULL,课程名称 char(30)NOT NULL,学分 decimal(5)NOT N

11、ULL,学时 decimal(5)NOT NULL,考核类型 char(6)NOT NULL,任课教师 char(12)NOT NULL,系部编号 char(2)NOT NULL,上课时间 char(40)NOT NULL),GOCREATE TABLE 成绩表(学号 char(8)NOT NULL,课程编号 char(4)NOT NULL,成绩 decimal(9)NOT NULL)GO,An Introduction to Database Systems,2.修改表,使用ALTER TABLE语句修改表结构（1）修改表设置主键基本语法如下：ALTER TABLE 表名ADD CONSTR

12、AINT 约束名称PRIMARY KEY CLUSTERED(列名,)其中：ADD CONSTRAINT 表示增加约束。PRIMARY KEY 表示主键。CLUSTERED 表示聚集索引，一般主键为聚集索引。,An Introduction to Database Systems,【例】使用SQL语句把“课程信息表”的“课程编号”列设置为主键“PX_ 课程信息表”。在查询分析器中运行如下命令即可：USE XSCJGOALTER TABLE 课程信息表ADD CONSTRAINT PX_ 课程信息表PRIMARY KEY CLUSTERED(课程编号)GO,An Introduction to

13、Database Systems,（2）添加列向表中增加一列时，应使新增加的列有默认值或允许为空值，SQL Server将向表中已存在的行填充新增列的默认值或空值，如果既没有提供默认值也不允许为空值，那么新增列的操作将出错，因为SQL Server不知道该怎么处理那些已经存在的行。向表中添加列的语句格式如下：ALTER TABLE表名ADD列名 列的描述,An Introduction to Database Systems,【例4-6】向“学生基本信息表”中添加“Email：”列。在查询分析器中运行如下命令：USE XSCJ GOALTER TABLE 学生基本信息表ADD Email：va

14、rchar(20)NULLGO,An Introduction to Database Systems,（3）删除列删除一列的语句格式为：ALTER TABLE表名DROP列名,【例4-7】将“学生基本信息表”中添加的“Email：”列删除。在查询分析器中运行如下命令：USE XSCJ GOALTER TABLE 学生基本信息表DROP COLUMN Email：GO,An Introduction to Database Systems,（4）修改列定义表中的每一列都有其定义，包括列名、数据类型、数据长度以及是否允许为空值等，这些值都可以在表创建好以后修改。修改列定义的语句格式为：ALTER

15、 TABLE 表名ALTER COLUMN 列名 列的描述,An Introduction to Database Systems,【例】将“学生基本信息表”中的“姓名”列改为最大长度为20的varchar型数据，且不允许空值。在查询分析器中运行如下命令：USE XSCJGOALTER TABLE 学生基本信息表ALTER COLUMN 姓名 varchar(20)NOT NULLGO默认状态下，列是被设置为允许空值的，将一个原来允许空值的列改为不允许空值，必须在以下两个条件满足时才能成功：列中没有存放是空值的记录在列上没有创建索引,An Introduction to Database Sy

16、stems,3.表的删除利用DROP TABLE语句删除表DROP TABLE语句可以删除一个表和表中的数据及其与表有关的所有索引、触发器、约束、许可对象。DROP TABLE语句的语法形式如下：DROP TABLE 表名,【例4】使用SQL语句删除XSCJ数据库中的“学生基本信息表”。在查询分析器中运行如下命令：USE XSCJ GO DROP TABLE 学生基本信息表 GO,An Introduction to Database Systems,4.向表中插入数据使用INSERT语句基本语法为：INSERT INTO 表名(列名)VALUES(表达式)（1）添加数据到一行中的所有列当将数

17、据添加到一行的所有列时，使用VALUES关键字来给出要添加的数据。INSERT语句中无需给出表中的列名，只要VALUES中给出的数据与用CREATE TABLE定义表时给定的列名顺序相同即可。,An Introduction to Database Systems,【例4-13】向“班级表”中输入一行数据。在查询分析器中运行如下命令：USE XSCJGOINSERT INTO 班级表VALUES(20051004,01,05高职网络)GO,返回的结果为：（所影响的行数为 1 行）需要注意的是：输入的顺序和数据类型必须与表中列的顺序和数据类型一致。可以不给全部列赋值，但没有赋值的列必须是可以为空

18、的列。,字符型和日期型值插入时要用单引号扩起来。,An Introduction to Database Systems,（2）添加数据到一行中的部分列要将数据添加到一行中的部分列时，则需要同时给出要使用的列名以及要赋给这些列的数据。,【例】向“学生基本信息表”中输入一行数据。在查询分析器中运行如下命令：USE XSCJINSERT INTO 学生基本信息表(学号，姓名，家庭住址)VALUES(000107,巴尔夏提,新疆库尔勒市)GO返回的结果为：（所影响的行数为 1 行）对于这种添加部分列的操作，在添加数据前应确认未在VALUES列表中出现的列允许不允许为NULL；只有允许为NULL的列，

19、才可以不出现在VALUES列表中。,An Introduction to Database Systems,5.使用UPDATE语句更新表中数据语法格式为：UPDATE 表名SET 列名=表达式WHERE 条件,【例4-16】将刘瑞恒的出生日期改为。在查询分析器中运行如下命令：USE XSCJGO UPDATE 学生基本信息表 SET 出生日期=1985/10/31 WHERE 姓名=刘瑞恒 GO返回的结果为：（所影响的行数为 1 行）,An Introduction to Database Systems,6.使用DELETE语句删除表中数据使用T-SQL中的DELETE语句可以删除数据表中

20、的一个或多个记录。DELETE语句最简单的形式如下：DELETE FROM 表名 WHERE 条件表达式其中，表名是要删除数据的表的名字。如果DELETE语句中没有WHERE子句限制，表中的所有记录都将被删除。,An Introduction to Database Systems,【例】删除“学生基本信息表”中姓名为杨文利的学生记录。在查询分析器中运行如下命令：USE XSCJ GO DELETE FROM 学生基本信息表 WHERE 姓名=杨文利 GO 返回的结果为：（所影响的行数为 1 行）项目实训,An Introduction to Database Systems,8.4 数据查询

21、,SQL 92标准中的SQL SELECT语句格式,SELECT FROM JOIN JOIN ON ON ON WHERE GROUP BY HAVING ORDER BY 注意：JOIN的顺序和ON的顺序是逆着的。,An Introduction to Database Systems,查询的分类,简单查询 排序 连接查询 嵌套查询 分组及计算查询,An Introduction to Database Systems,简单查询,基于单个关系、简单条件的查询。从职工关系中检索所有工资值。检索仓库关系中的所有元组。检索工资多于1230元的职工号。检索哪些仓库有工资多于1210元的职工。给出在

22、仓库WH1或WH2工作，并且工资少于1250元的职工号。检索出工资在1220元到1240元范围内的职工信息。从供应商关系中检索出全部公司的信息（不要工厂或其他供应商的信息）。找出不在北京的全部供应商信息。找出尚未确定供应商的订购单。列出已经确定了供应商的订购单信息。,An Introduction to Database Systems,从职工关系中检索所有工资值,SELECT 工资 FROM 职工,结果是：12201210125012301250,SELECT DISTINCT工资 FROM 职工,结果是：1220121012501230,An Introduction to Databas

23、e Systems,检索仓库关系中的所有元组,SELECT*FROM 仓库,SELECT 仓库号,城市,面积 FROM 仓库,An Introduction to Database Systems,检索工资多于1230元的职工号,SELECT 职工号FROM 职工WHERE 工资 1230,结果是：E4E7,An Introduction to Database Systems,检索哪些仓库有工资多于1210元的职工,SELECT DISTINCT 仓库号FROM 职工WHERE 工资 1210,结果是：WH2WH3WH1,An Introduction to Database Systems

24、,给出在仓库WH1或WH2工作，并且工资少于1250元的职工号,SELECT 职工号FROM 职工WHERE 工资 1250 AND(仓库号=WH1 OR 仓库号=WH2),结果是：E1E3,An Introduction to Database Systems,检索出工资在1220元到1240元范围内的职工信息,SELECT*FROM 职工WHERE 工资 BETWEEN 1220 AND 1240,结果是：WH2 E1 1220WH3 E6 1230,表达式“工资 BETWEEN 1220 AND 1240“等价于(工资=1220)AND(工资=1240),An Introduction

25、to Database Systems,从供应商关系中检索出全部公司的信息,SELECT*FROM 供应商WHERE 供应商名 LIKE%公司,结果是：S4 华通电子公司 北京,这里的LIKE是字符串匹配运算符，通配符“%”表示0个或多个字符，另外还有一个通配符“_”（下划线）表示一个字符。,An Introduction to Database Systems,找出不在北京的全部供应商信息,SELECT*FROM 供应商WHERE 地址!=北京 或SELECT*FROM 供应商WHERE NOT(地址=北京),结果是：S3 振华电子厂 西安S6 607 厂 郑州,NOT的应用范围很广，比如，

26、可以有NOT IN、NOT BETWEEN等。,An Introduction to Database Systems,找出尚未确定供应商的订购单,SELECT*FROM 订购单WHERE 供应商号 IS NULL,结果是：E6 NULL OR77NULLE1 NULL OR80NULLE3 NULL OR90NULL,注意：查询空值时要使用IS NULL，而=NULL 是无效的，因为空值不是一个确定的值，所以不能用“=”这样的运算符进行比较。,An Introduction to Database Systems,列出已经确定了供应商的订购单信息,SELECT*FROM 订购单WHERE 供

27、应商号 IS NOT NULL,结果是：E3 S3 OR91 2002-07-13 00:00:00.000E7 S4 OR76 2002-05-25 00:00:00.000E3 S4 OR79 2002-06-13 00:00:00.000E1 S4 OR73 2002-07-28 00:00:00.000E3 S7 OR67 2002-06-23 00:00:00.000,An Introduction to Database Systems,排序,可以对查询的结果进行排序，可以是升序或降序，可以按多列排序。按职工的工资值升序检索出全部职工信息 先按仓库号排序，再按工资排序并输出全部职工

28、信息,An Introduction to Database Systems,按职工的工资值升序检索出全部职工信息,SELECT*FROM 职工ORDER BY 工资,结果是：WH1 E3 1210WH2 E1 1220WH3 E6 1230WH2 E4 1250WH1 E7 1250,An Introduction to Database Systems,先按仓库号排序再按工资排序输出全部职工信息,SELECT*FROM 职工ORDER BY 仓库号,工资,结果是：WH1 E3 1210WH1 E7 1250WH2 E1 1220WH2 E4 1250WH3 E6 1230,An Intro

29、duction to Database Systems,连接查询,当查询的结果出自多个表时，需要通过表之间的连接操作来完成。一般连接 别名和自连接查询 广义笛卡尔积 内连接 外连接,An Introduction to Database Systems,一般连接,常规的两个表或多个表之间的连接。找出工资多于1230元的职工号和他们所在的城市 找出工作在面积大于400的仓库的职工号以及这些职工工作所在的城市给出有北京仓库订购单的北京供应商的名称,An Introduction to Database Systems,找出工资多于1230元的职工号和他们所在的城市,SELECT 职工号,城市FRO

30、M 职工,仓库WHERE(工资 1230)AND(职工.仓库号=仓库.仓库号),SELECT 职工号,城市FROM 职工 JOIN 仓库ON 职工.仓库号=仓库.仓库号WHERE 工资 1230,或,结果是：E4 上海E7 北京,An Introduction to Database Systems,找出工作在面积大于400的仓库的职工号以及这些职工工作所在的城市,SELECT 职工号,城市FROM 仓库,职工WHERE(面积 400)AND(职工.仓库号=仓库.仓库号),SELECT 职工号,城市FROM 职工 JOIN 仓库ON 职工.仓库号=仓库.仓库号WHERE 面积 400,或,结果

31、是：E1 上海E4 上海,An Introduction to Database Systems,给出有北京仓库订购单的北京供应商的名称,SELECT 供应商名FROM 供应商,订购单,职工,仓库WHERE地址=北京 AND 城市=北京AND 供应商.供应商号=订购单.供应商号AND 订购单.职工号=职工.职工号AND 职工.仓库号=仓库.仓库号,SELECT 供应商名FROM 供应商 JOIN 订购单 JOIN 职工 JOIN 仓库ON 职工.仓库号=仓库.仓库号ON 订购单.职工号=职工.职工号ON 供应商.供应商号=订购单.供应商号WHERE 地址=北京 AND 城市=北京,或,An I

32、ntroduction to Database Systems,别名和自连接查询,一个表通过不同的属性到自身的连接称作自连接。这种关系中的一些元组，根据出自同一值域的两个不同的属性，可以与另外一些元组有一种对应关系（一对多的联系）。为了实现自连接需要将一个关系看作两个逻辑关系，为此需要给关系指定别名。,An Introduction to Database Systems,自连接查询,设有如下图所示的雇员关系，其中雇员号和经理两个属性出自同一个值域，同一元组的这两个属性值是“上、下级”关系。,查询：根据雇员关系列出上一级经理及其职员（被其领导）的清单。,SELECT S.雇员姓名,领导,E.雇

33、员姓名 FROM 雇员 S,雇员 EWHERE S.雇员号=E.经理,结果是：赵涌 领导 钱潮赵涌 领导 孙洁孙洁 领导 李渌,An Introduction to Database Systems,广义笛卡尔积,在新的SQL标准中还支持广义笛卡尔积（CROSS）运算，SQL Server也支持该运算。广义笛卡尔积（CROSS）运算的一般格式是：SELECT FROM CROSS JOIN WHERE 其中FROM CROSS JOIN 指出了广义笛卡尔积运算，如下命令将得到纯粹的广义笛卡尔积运算的结果：SELECT*FROM CROSS JOIN,An Introduction to Dat

34、abase Systems,广义笛卡尔积运算实例,得到仓库关系和职工关系的广义笛卡尔积的运算结果 SELECT*FROM 仓库 CROSS JOIN 职工 对仓库关系和职工关系进行传统的连接 SELECT*FROM 仓库 CROSS JOIN 职工 WHERE 仓库.仓库号=职工.仓库号,结果是：WH2 上海 500WH2 E1 1220WH1 北京 370WH1 E3 1210WH2 上海 500WH2 E4 1250WH3 广州 200WH3 E6 1230WH1 北京 370WH1 E7 1250,An Introduction to Database Systems,内连接,在新的SQ

35、L标准中内连接（INNER）运算的一般格式是：SELECT FROM INNER JOIN ON WHERE 内连接就是就是传统的连接操作，其中INNER可以省略，这里用ON短语指定连接条件，用WHERE短语指定其它限定条件。,An Introduction to Database Systems,外连接,外连接与前面所介绍的等值连接和自然连接不同。原来的连接是只有满足连接条件，相应的结果才会出现在结果表中；而外连接可以使不满足连接条件的元组也出现在结果表中。按连接方式外连接又可以分为左连接（LEFT）、右连接（RIGHT）和全连接（FULL）三种。,An Introduction to Da

36、tabase Systems,外连接（OUTER）运算的一般格式,SELECT FROM LEFT|RIGHT|FULL OUTER JOIN ON WHERE,An Introduction to Database Systems,OUTER 外连接例如：对于表A,B,其内容为A表 B表 a1 a2 b1 b2-1 4 2 32 5 4 56 7 6 7 3 4 8 97 8,左外连接 LEFT OUTER JOIN 除了检索出满足条件的行之外还包括左表的所有行 右外连接 RIGHT OUTER JOIN 除了检索出满足条件的行之外还包括右表的所有行 完全外连接 FULL OUTER JOI

37、N 除了检索出满足条件的行之外还包括两个表的所有行,例如：左连接select a.a1,a.a2,b.b2 from a left outer join b on a.a2=b.a1执行结果为:a1 a2 b2-1 4 5 3 4 5 7 8 9 2 5 6 7,例如：右连接select a.a1,a.a2,b.b2 from a right outer join b on a.a2=b.a1执行结果为:a1 a2 b2-1 4 5 3 4 5 7 8 9 7 3,例如：广义笛卡尔积连接select a.a1,a.a2,b.b2 from a cross join b执行结果为:a1 a2 b

38、2-1 4 3 2 5 3 6 7 3 3 4 3 7 8 3 1 4 5.,An Introduction to Database Systems,举例,设有仓库和职工两个关系，如果是等值或自然连接 SELECT 仓库.仓库号,城市,面积,职工号,工资 FROM 仓库 JOIN 职工 ON 仓库.仓库号=职工.仓库号,结果是：WH2 上海 500E1 1220WH1 北京 370E3 1210WH2 上海 500E4 1250WH3 广州 200E6 1230WH1 北京 370E7 1250,An Introduction to Database Systems,举例,设有仓库和职工两个关

39、系，如果是左连接 SELECT 仓库.仓库号,城市,面积,职工号,工资 FROM 仓库 LEFT JOIN 职工 ON 仓库.仓库号=职工.仓库号,结果是：WH1 北京 370E3 1210WH1 北京 370E7 1250WH2 上海 500E1 1220WH2 上海 500E4 1250WH3 广州 200E6 1230WH4 武汉 400NULLNULL,An Introduction to Database Systems,嵌套查询,普通嵌套查询 使用量词的嵌套查询 使用EXISTS的嵌套查询,An Introduction to Database Systems,普通嵌套查询,当检索

40、关系X中的元组时，它的条件依赖于相关的关系Y中的元组的属性值，这时使用普通的嵌套查询将非常方便。哪些城市至少有一个仓库的职工的工资为1250元？找出和职工E4挣同样工资的所有职工。找出哪些城市的仓库向北京的供应商发出了订购单。,An Introduction to Database Systems,哪些城市至少有一个仓库的职工的工资为1250元？,SELECT 城市FROM 仓库WHERE 仓库号 IN(SELECT 仓库号 FROM 职工 WHERE 工资=1250),结果是：北京上海,An Introduction to Database Systems,找出和职工E4挣同样工资的所有职工

41、。,SELECT 职工号FROM 职工WHERE 工资=(SELECT 工资 FROM 职工 WHERE 职工号=“E4”),结果是：E4E7,An Introduction to Database Systems,找出哪些城市的仓库向北京的供应商发出了订购单,SELECT 城市 FROM 仓库 WHERE 仓库号 IN(SELECT 仓库号 FROM 职工 WHERE 职工号 IN(SELECT 职工号 FROM 订购单 WHERE 供应商号 IN(SELECT 供应商号 FROM 供应商 WHERE 地址=北京),结果是：北京上海,An Introduction to Database S

42、ystems,使用量词的嵌套查询,在嵌套查询中可以使用ANY、SOME、ALL等量词，它们的形式是：ANY|ALL|SOME(子查询)其中ANY和SOME是同义词，在进行比较运算时只要子查询中有一行能使结果为真，则结果就为真；而ALL则要求子查询中的所有行都使结果为真时，结果才为真。,An Introduction to Database Systems,检索有职工的工资大于或等于WH1仓库中任何一名职工的工资的仓库号,SELECT DISTINCT 仓库号 FROM 职工 WHERE 工资=ANY(SELECT 工资 FROM 职工 WHERE 仓库号=WH1),SELECT DISTINC

43、T 仓库号 FROM 职工 WHERE 工资=(SELECT MIN(工资)FROM 职工 WHERE 仓库号=WH1),等价于：,结果是：WH1WH2WH3,An Introduction to Database Systems,检索有职工的工资大于或等于WH1仓库中所有职工的工资的仓库号,SELECT DISTINCT 仓库号 FROM 职工 WHERE 工资=ALL(SELECT 工资 FROM 职工 WHERE 仓库号=WH1),SELECT DISTINCT 仓库号 FROM 职工 WHERE 工资=(SELECT MAX(工资)FROM 职工 WHERE 仓库号=WH1),等价于：

44、,结果是：WH1WH2,An Introduction to Database Systems,使用EXISTS的嵌套查询,在嵌套查询中还可以使用NOT EXISTS，具体形式是：NOT EXISTS(子查询)EXISTS或NOT EXISTS是用来检查在子查询中是否有结果返回（即存在元组或不存在元组）。,An Introduction to Database Systems,检索那些仓库中还没有职工的仓库的信息。,SELECT*FROM 仓库 WHERE NOT EXISTS(SELECT*FROM 职工 WHERE 仓库号=仓库.仓库号),SELECT*FROM 仓库 WHERE 仓库号

45、NOT IN(SELECT 仓库号 FROM 职工),等价于,结果是：WH4 武汉 400,An Introduction to Database Systems,检索那些仓库中至少已经有一个职工的仓库的信息,SELECT*FROM 仓库 WHERE EXISTS(SELECT*FROM 职工 WHERE 仓库号=仓库.仓库号),等价于,SELECT*FROM 仓库 WHERE 仓库号 IN(SELECT 仓库号 FROM 职工),结果是：WH1 北京 370WH2 上海 500WH3 广州 200,An Introduction to Database Systems,分组及计算查询,SQL

46、语言不仅可以从数据库中查询原始信息，而且还可以直接对查询结果进行计算和汇总。SQL语言支持分组的计算和汇总。用于计算检索的函数主要有：COUNT计数 SUM求和 AVG计算平均值 MAX求最大值 MIN求最小值,An Introduction to Database Systems,找出供应商所在地的数目,SELECT COUNT(DISTINCT 地址)FROM 供应商,结果为3,An Introduction to Database Systems,求支付的工资总数,SELECT SUM(工资)FROM 职工,结果是：6160,An Introduction to Database Sys

47、tems,求在“上海”仓库工作的职工的最高工资值,SELECT MAX(工资)FROM 职工WHERE 仓库号 IN(SELECT 仓库号 FROM 仓库 WHERE 城市=上海),结果是：1250,An Introduction to Database Systems,求每个仓库的职工的平均工资,SELECT 仓库号,AVG(工资)FROM 职工GROUP BY 仓库号,结果是：WH1 1230WH2 1235WH3 1230,An Introduction to Database Systems,求至少有两个职工的每个仓库的平均工资,SELECT 仓库号,COUNT(*),AVG(工资)F

48、ROM 职工GROUP BY 仓库号HAVING COUNT(*)=2,结果是：WH1 2 1230WH2 2 1235,An Introduction to Database Systems,COMPUTE子句,GROUP BY子句能完成汇总，但是却不能显示细节。利用COMPUTE子句，汇总结果是附加在细节之后显示的，这样用户既能看到细节，又能看到汇总行。,An Introduction to Database Systems,COMPUTE子句的格式,COMPUTE(),()BY,这里只能是用于SELECT计算查询的函数SUM、AVG、MIN、MAX和COUNT等。COMPUTE子句中的B

49、Y子句是用来说明分组的，如果在COMPUTE子句中不使用BY子句，则是对整个表进行汇总。这里使用BY子句，也必须使用ORDER BY子句，BY子句指出的列必须和ORDER BY子句指出的列顺序相同，但BY子句的列数可以少于ORDER BY子句的列数。,An Introduction to Database Systems,列出职工全部记录并计算各仓库的平均工资和工资小计，最后给出全体职工的平均工资和工资总和,SELECT 仓库号,职工号,工资 FROM 职工ORDER BY 仓库号COMPUTE AVG(工资),SUM(工资)BY 仓库号 COMPUTE AVG(工资),SUM(工资),结果是

50、：WH1 E3 1210WH1 E7 1250Avgsum12302460WH2 E1 1220WH2 E4 1250Avgsum12352470WH3 E6 1230Avgsum12301230Avgsum1232 6160,An Introduction to Database Systems,列出职工全部记录并计算全体职工的平均工资和工资总和,SELECT 仓库号,职工号,工资FROM 职工COMPUTE AVG(工资),SUM(工资),结果是：WH2 E1 1220WH1 E3 1210WH2 E4 1250WH3 E6 1230WH1 E7 1250Avgsum1232 6160,A