《关系数据库语言》PPT课件.ppt

《《关系数据库语言》PPT课件.ppt》由会员分享，可在线阅读，更多相关《《关系数据库语言》PPT课件.ppt（124页珍藏版）》请在装配图网上搜索。

1、数据库原理与应用,第2章 关系数据库语言,数据库原理与设计,第2章 关系数据库语言,SQL概述 SQL Server数据库的基本管理与设置 数 据 定 义 数 据 查 询 数 据 更 新,SQL（Structured Query Language）是结构化查询语言，利用SQL语言可以实现对数据库的访问。Transact-SQL是Microsoft公司对SQL标准的一个实现，简称T-SQL。,数据库原理与设计,2.1 SQL概述,SQL简单易学、风格统一，利用简单的几条命令就可以完成所有的功能。 SQL有以下主要特点。 1综合统一 2高度非过程化 3面向集合的操作方式 4以同一种语法结构提供两种

2、使用方式 5语言简洁，易学易用,数据库原理与设计,2.1.2 SQL数据库的体系结构,视图（View） -类似于表 -虚表(窗口) -使数据管理更方便和安全,数据库原理与设计,2.1.3 SQL语句的分类,在SQL2标准中，SQL语句可大体分为以下三类 数据定义语言（Data Definition Language，DDL）： 用于定义SQL模式、基本表、视图、索引等结构。 数据操纵语言（Data Manipulation Language，DML）： 数据操作分成数据查询和数据更新两类。 数据控制语言（Data Control Language，DCL）： 包括事务管理功能和数据保护功能,数

3、据库原理与设计,数据库原理与设计,2.1.4 SQL的语法约定,1标识符 2标识符的类型 3SQL语句的语法,数据库原理与设计,1.了解数据库的存储结构 2.掌握数据库的创建和维护的方法,2.2 数据库的基本管理与设置,数据库原理与设计,2.2 数据库的基本管理与设置,1数据库的存储结构 分两种: 逻辑存储结构和物理存储结构。 数据库的逻辑存储结构：SQL Server的数据库是由诸如表、视图等不同的数据库对象所组成的。 数据库的物理存储结构：指的是数据库文件是如何在磁盘上存储的。SQL Server 2000中，数据存储在页（Page）中，页是数据存储的最小单位 数据库操作系统文件分为三类：

4、主数据文件、二级数据文件和日志文件.,数据库原理与设计,（1）主数据文件：扩展名“.mdf”，每个数据库有且仅有一个主数据文件，用于存储数据库的启动信息、数据和数据库对象。是数据库的起点，指向数据库中文件的其他部分。,（2）二级数据文件：扩展名为“.ndf” 。主要在一个数据库跨多个硬盘驱动器时使用。,数据库原理与设计,（3）日志文件：扩展名为“.ldf”。每个数据库至少拥有一个日志文件。用于存放对数据库进行操作的事务日志。 数据库的最精简装备：一个数据库至少应该包含一个主数据文件和一个事务日志文件。,数据库较完备装备：多个数据文件，三个日志文件；日志文件与数据文件不放在同一块物理磁盘上，以备

5、数据文件磁盘损坏时可以利用日志文件恢复。,数据库原理与设计,日志文件,数据库原理与设计,数据库的创建与管理,数据库原理与设计,SQL Server的系统数据库与示例数据库,SQL Server的数据库可以分为两大类：系统数据库和用户数据库。 系统数据库不能随便更改或删除。 用户数据库一般由用户创建。 SQL Server安装时建立了6个数据库，其中master，tempdb，model和msdb是4个系统数据库；pubs和Northwind是两个用户数据库，用以示例。,数据库原理与设计,2创建数据库,默认情况下，只有系统管理员和数据库拥有者可创建数据库。创建数据库的用户自动成为该数据库的所有者

6、。 创建数据库的过程实际上就是为数据库设计名称、设计所占用的存储空间和存放文件位置的过程。 1.企业管理器创建数据库 2.用T-SQL语言创建数据库： Create Database 数据库名 3.用向导创建数据库,一个数据库至少应该包含一个数据库文件和一个事务日志文件。,数据库原理与设计,使用T-SQL语言创建数据库，语法如下： Create Database 数据库名 ON PRIMARY ，n LOG ON ，n := (NAME=逻辑文件名， FILENAME=操作系统下的物理路径和文件名 ，SIZE=文件初始大小 ，MAXSIZE=文件最大大小|UNLIMITED ，FILEGROW

7、TH=增量值 ，n,数据库原理与设计,Create Database Company ON PRIMARY (NAME=Company_data, FILENAME= C:SQLCompany.mdf, SIZE=10, MAXSIZE=unlimited, FILEGROWTH=10%) LOG ON (NAME=Company_log, FILENAME=C:SQLCompany.ldf, SIZE=1, MAXSIZE=5, FILEGROWTH=1),创建一个Company数据库,主数据文件逻辑名称,物理文件名,初始大小,最大尺寸为无限大,增长速度,日志文件逻辑名称,物理文件名,数据库

8、原理与设计,2.2.3 修改数据库ALTER DATABASE,包括增、删数据文件和事务日志文件，修改数据文件和事务日志文件的初始容量、最大容量、增长方式等。 Alter Database 数据库名 ADD FILE ,n |ADD LOG FILE ,n |REMOVE FILE with delete |MODIFY FILE |MODIFY NAME=新数据库名,数据库原理与设计,2.2.4 删除数据库,删除数据库时也删除了该数据库的全部对象。 T-SQL语言删除数据库： DROP DATABASE 数据库名 ,.n 无法除去系统数据库 （msdb、model、master和tempdb

9、）,以下情况不能删除： 有用户使用此数据库时 数据库正在被恢复时 数据库正在参与复制时,数据库原理与设计,2.2.5 设置数据库选项,sp_dboption系统存储过程设置数据库选项。 基本语法如下： sp_dboption 数据库名 配置选项名设置值,将Company数据库设为只读,将Company数据库设为可写,数据库原理与设计,数据库操作总结,创建数据库： Create Database 数据库名 修改数据库: Alter Database 数据库名 ADD FILE ,n |REMOVE FILE |MODIFY FILE 删除数据库： Drop Database 数据库名 ,.n ,

10、数据库原理与设计,2.3.1 表的创建与管理,2.3 数 据 定 义,数据库原理与设计,表操作之一-表的建立,一个表由表结构、表记录构成。 定义表的结构，就是定义表的字段个数、字段名称、类型、宽度及小数位数等。,企业管理器中创建表 使用Create命令创建 Create Table 表名 (字段名1 类型(长度) ,字段名2 类型(长度),数据库原理与设计,数据库原理与设计,数据类型,注解：因为char类型总是会用空格填充使之达到一个固定宽度，在表中占用很大的存储空间。,数据库原理与设计,数据分类,完整性约束-建表中的完整性问题,数据库原理与设计,完整性约束-建表中的完整性问题,数据库原理与设

11、计,完整性约束-建表中的完整性问题,数据库原理与设计,完整性约束-建表中的完整性问题,数据库原理与设计,完整性约束,使用命令定义完整性约束条件:需要在定义完字段的数据类型和长度之后，定义该字段上的约束类型： 1.主码完整性约束 -如： sno char(5) primary key 2.Foreign Key约束-如： sno char(5) foreign key references student(sno) 3.check完整性约束-如： age int check（age between 18 and 30) 4.唯一完整性约束 unique 5.DEFAULT完整性约束 scredi

12、t numeric(3,0) default 4 6.非空完整性约束 sname char(6) not null,Create Table 表名 (字段名1 类型(长度) 约束, 字段名2 类型(长度) 约束 ),数据库原理与设计,例：使用命令的方式创建student,course,sc三个表。,Create table student ( Sno char(5) primary key, Clno char(5) not null, Sname varchar(8) not null, Ssex char(2) check (ssex in (男,女) , Sbir datetime, S

13、credit numeric(4,0) ),数据库原理与设计,Create table course ( Cno char(5) primary key, Cname varchar(50) unique, Ctno char(2), Cinfo varchar(50), Ccredits numeric(2,0) default 4, Cttime numeric(3,0), Cpno char(5), Cterm numeric(1,0) ),数据库原理与设计,Create table sc ( Sno char(5) foreign key(sno) references student

14、(sno), Cno char(5) foreign key(cno) references course(cno), Score numeric(3,1), Primary key(sno,cno) ),数据库原理与设计,引用完整性约束(外码约束)内容补充,先建主表，后建从表 当定义外码时候，子表中引用的必须是主表的主码 。 默认情况下创建了外码约束，当删除或修改主表记录时，若子表中有对应记录，不能修改或者删除。 如果用户需要，删除或者修改主表的记录的同时，系统自动删除或者修改子表中相关的记录，则需要在建外码的同时指定on delete cascade 级联删除。,数据库原理与设计,命名约束

15、,定义命名的完整性约束,CREATE TABLE tablename ( 字段名 类型长度 CONSTRAINT constraint_name ,Not null Unique Primary key Foreign key(字段) references 主表（主键） Check (条件) Default,数据库原理与设计,练习创建表和完整性约束 创建货物表goods和订单表orders，表结构如下。 所有的约束采用命名约束。 创建外键约束,级联删除.,数据库原理与设计,表操作之二-表的修改,修改表对象 ALTER TABLE 表名 ADD 列名 数据类型 完整性约束条件 ALTER COL

16、UMN 列名 数据类型 DROP COLUMN 列名 ADD CONSTRAINT 约束名 约束 DROP CONSTRAINT 约束名,数据库原理与设计,例2-12 向表Student中增加一列address。 Alter table Student add address varchar(40) 例2-14 将Student表中address列的最大长度修改为50，并允许为空。 ALTER Table Student ALTER Column address varchar(50) NULL 例2-15 删除Student表中的address列。 ALTER Table Student D

17、ROP Column address,数据库原理与设计,向表Student中增加列Email，且要求输入的电子邮件地址必须包括“”字符。 ALTER TABLE Student ADD Email VARCHAR(40) CONSTRAINT ck_ea CHECK(Email like %),数据库原理与设计,例2-13 在Student表中SName列上设置唯一约束。 ALTER TABLE Student ADD CONSTRAINT u_name Unique(SName) 例2-16 删除Student表中SName列上的唯一约束。 ALTER TABLE Student DROP

18、CONSTRAINT u_name,数据库原理与设计,完整性约束的作用时间:,在ALTER TABLE语句中使用 (1)nocheck constraint子句 (2)check constraint子句 来完成使约束无效和重新有效的设置。 ALTER TABLE student nocheck constraint ck_age,age int constraint ck_age check（age between 18 and 60),数据库原理与设计,表操作之三-表的删除,DROP TABLE 表名 例如：DROP TABLE eg1 注意：DROP TABLE语句不能删除系统表。 删除

19、表时，系统会删去该表及其索引 表上的视图往往仍然保留,无法引用,数据库原理与设计,SQL的数据定义语句,数据库原理与设计,查 询,数据库原理与设计,2.3.2 索引的创建与管理,2.3 数 据 定 义,数据库原理与设计,索引的分类,索引分为聚簇索引与非聚簇索引。 聚簇索引（Clustered Index）叶结点中存储的是实际的数据。 非聚簇索引（Nonclustered Index）叶结点存储组成索引的关键字值和指针,数据库原理与设计,3创建索引,（1）选择创建索引的列 表的主键列； 连接中频繁使用的列； 频繁搜索的列。 插入和更新数据时，维护索引要花费时间和空间 （2）创建索引的语法格式 C

20、REATE unique clustered| nonclustered INDEX 索引名 ON 表名 (列名次序, 列名次序） 次序:指定索引值的排列次序:ASC和DESC两种,数据库原理与设计,例2-18 在Course表上创建一个名cs_index的非聚簇复合索引，索引关键字为Cno和CTno，升序排列。 CREATE nonclustered INDEX cs_index ON Course (CNo ASC, CTno ASC),CREATE unique clustered| nonclustered INDEX 索引名 ON 表名 (列名次序, 列名次序）,数据库原理与设计,在

21、Course表CNo上创建一个唯一性的聚簇索引，索引排列顺序为降序。 CREATE unique clustered INDEX IX_CNo ON Course(CNo DESC ),唯一性索引 对于已含重复值的属性列不能建UNIQUE索引 对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束,数据库原理与设计,在Course表CName上创建一个非唯一性的非聚簇索引 CREATE nonclustered INDEX IX_CName ON Course(CName) WITH DROP_EXISTING,WITH子

22、句中可包含索引的创建选项,DROP_EXISTING是指如果表中已存在同名的索引则将其删除，重建索引。,数据库原理与设计,建立索引,在一个基本表上最多只能建立一个聚簇索引 聚簇索引可以提高某些类型的查询效率 聚簇索引的适用范围 很少对基表进行增删操作,数据库原理与设计,4管理索引,（1）查看索引 sp_helpindex可以查看特定表上的索引信息 例2-19 查看Course表的索引信息。 EXECUTE sp_helpindex Course （2）删除索引 DROP语句将其删除： DROP INDEX . DROP INDEX Course. IX_CName,数据库原理与设计,drop

23、index SC.pk_SC 在表中指定主键时，将自动创建一个名为“PK_”且后跟表名的主键索引,主键索引是唯一索引的特殊类型 ALTER TABLE SC DROP CONSTRAINT pk_SC,数据库原理与设计,注意：,如果索引是在CREATE TABLE语句中创建的，只能用ALTER TABLE语句删除索引； 如果索引是CREATE INDEX创建的，可用DROP INDEX删除。 DROP INDEX语句删除多个索引，索引之间需用逗号隔开 被删除的索引一定要以.”的形式给出,SELECT 目标列1,目标列2. FROM 表名或视图名 WHERE 条件表达式 GROUP BY 列名1

24、 HAVING 条件表达式 ORDER BY 列名2,数据查询 Data Query language,选取字段 Select,创建新表 Into,过滤记录 Where,对结果排序 Order by,SELECT -FROM-WHERE结构的使用,选取字段 Select,创建新表 Into,过滤记录 Where,对结果排序 Order by,选取字段 Select,创建新表 Into,过滤记录 Where,对结果排序 Order by,选取字段 Select,创建新表 Into,过滤记录 Where,对结果排序 Order by,设有一个SPJ数据库，包括S，P，J，SPJ四个关系模式： S(

25、 SNO，SNAME，STATUS，CITY)； P(PNO，PNAME，COLOR，WEIGHT)； J(JNO，JNAME，CITY)； SPJ( SNO，PNO，JNO，QTY)； 备注： 供应商表S由供应商代码（SNO）、供应商姓名（SNAME）、供应商状态（STATUS）、供应商所在城市（CITY）组成； 零件表P由零件代码（PNO）、零件名（PNAME）、颜色（COLOR）、重量（WEIGHT）组成； 工程项目表J由工程项目代码（JNO）、工程项目名（JNAME）、工程项目所在城市（CITY）组成； 供应情况表SPJ由供应商代码（SNO）、零件代码（PNO）、工程项目代码（JNO）

26、、供应数量（QTY）组成，表示某供应商供应某种零件给某工程项目的数量为QTY。,select -from -where-结构练习,供应商表 s,零件表 p,工程表 j,供应信息表spj,select -from -where-结构练习,查询所有的供应商号。 查询北京的供应商号。 查询蓝色的零件号。 查询供应项目j1零件p3的供应商号及相应数量。 查询供应项目j1零件p3的供应商号及相应数量，并按数量多少升序排列。,select -from -where-结构练习,集合函数 function,分组 Group by,对分组筛选 Having,分组统计 Compute,SELECT -FROM-W

27、HERE GROUP BY -HAVING-结构,集合函数 function,分组统计 Group by,对分组筛选 Having,分组统计 Compute,集合函数 function,分组 Group by,对分组筛选 Having,分组统计 Compute,集合函数 function,分组 Group by,对分组筛选 Having,分组统计 Compute,设有一个SPJ数据库，包括S，P，J，SPJ四个关系模式： S( SNO，SNAME，STATUS，CITY)； P(PNO，PNAME，COLOR，WEIGHT)； J(JNO，JNAME，CITY)； SPJ(SNO，PNO，JN

28、O，QTY)； 备注： 供应商表S由供应商代码（SNO）、供应商姓名（SNAME）、供应商状态（STATUS）、供应商所在城市（CITY）组成； 零件表P由零件代码（PNO）、零件名（PNAME）、颜色（COLOR）、重量（WEIGHT）组成； 工程项目表J由工程项目代码（JNO）、工程项目名（JNAME）、工程项目所在城市（CITY）组成； 供应情况表SPJ由供应商代码（SNO）、零件代码（PNO）、工程项目代码（JNO）、供应数量（QTY）组成，表示某供应商供应某种零件给某工程项目的数量为QTY。,单表操作练习,单表操作练习,供应商表 s,零件表 p,工程表 j,供应信息表spj,查询供应

29、商S1总共供应几个项目。 查询每个供应商供应项目的项目数。 查询供应项目多于2个的供应商号。 统计项目j1由几个供应商提供零件。 按照项目来统计其对应的供应商数量。 统计供应商多余3个的项目号。 查询最轻的零件的质量是多少。 查询质量最轻的零件号。 查询质量最重的零件号。,统计汇总操作练习,编写分类统计SQL的关键： （1）明确按照“什么”来统计（分类字段） （2）明确要统计的内容（统计字段）,统计汇总操作练习,查询固定资产表中有多少个不同的部门。 查询表中有多少种物资。 查询表中是否有重名的物资。 要查询每个使用部门到底使用了多少种物资。,2.4.2连接相关表中的数据,连接查询（多表）指从多

30、个有关联的表中查询数据，其基本语法跟单表查询类似。一般来说，多表查询要在where条件中指定表连接的条件，如果没有连接条件，则查询结果是这多个查询表的笛卡儿积。 建立两表之间关系的最简单的运算符是等号（=）。如果使用等号两个表连接起来。这种类型的连接把来自两个表的在指定列中具有相等值的行连接起来。,2.4.2连接相关表中的数据,相等连接（内连接、外连接） 自连接 不相等连接 优化连接查询,1 . 显示成绩单，包括学生姓名、课程名称、成绩。,数据库原理与设计,多表等值连接查询的注意事项： 1. 在from语句中指明数据所涉及的表，在where条件中须指明表和表之间的连接条件。 select sn

31、ame, cname, score from student,course,sc where student.sno=sc.sno and o=o 2.由于多个表中可能存在相同列，所以返回列的时候需要指明具体为哪个表中的列。 3.在SQL语句中，使用表别名可以简化代码，增强可读性。,连接之一- 等值连接,例： 查询选修了“数学”课的学生学号。,SELECT a.SNo, SName, Score FROM Student a, SC b, Course c WHERE a.SNo=b.Sno AND b.CNo=c.CNo AND CName= 数学 AND Score60,SELECT SN

32、o FROM SC a, Course b WHERE a.CNo=b.CNo AND CName= 数学,例： 查询“数学”课不及格的学生名单（输出学生的学号、姓名及成绩）,查询的结果来自两张表，而决定查询结果的因素涉及三张表的信息。,连接之一-等值连接练习,供应商表 s,零件表 p,工程表 j,供应信息表spj,查询为天津的项目提供零件的供应商号及其城市。 查询为天津的项目提供红色零件的供应商号及其城市。 查询为“三建”工程提供零件的供应商名称。 查询供应以“三”开头的工程的供应商号及其城市,连接之一-等值连接练习,数据库原理与设计,2等值连接的分类,-内连接和外连接,-内连接（简单连接）

33、，用于返回满足连接条件的记录，废弃两个表中不匹配的行。 -外连接是简单连接的扩展，它不仅会返回满足连接条件的所有行，而且还会返回不满足连接条件的某一张表的所有行。左外连接、右外连接和完全外连接,语法一：使用连接操作符（=、*=、=*）来实现 1）内连接 SELECT table1.col,table2.col FROM table1,table2 WHERE table1.col = table2.col,2）左外连接 SELECT table1.col,table2.col FROM table1,table2 WHERE table1.col * = table2.col,3）右外连接 S

34、ELECT table1.col,table2.col FROM table1,table2 WHERE table1.col =* table2.col,3、等值连接的语法,语法二：在FROM子句中指明连接类型，在ON子句中指明连接条件 SELECT table1.col,table2.col FROM table1 INNER | LEFT | RIGHT | FULL JOIN table2 ON table1.col=table2.col,3、等值连接的语法,数据库原理与设计,4、外连接的应用 例如：查找学生的基本情况（学号、姓名、年龄），及其选课情况（包括课程号、成绩） 例如：查找学

35、生表中哪些学生没有选修过课程。,select student.sno,sname,sage,cno,score from student, sc where student.sno*=sc.sno,数据库原理与设计,在teacher表中，查询职工“王刚”的上级领导姓名。,连接之二-自连接,处理的方法：将物理上独立存在的一张表，逻辑上看作两张表来处理。 实现的技巧：通过为表起不同的别名来实现。,将Teacher表看作t1(普通教师),将Teacher表看作t2（直接领导）,t1和t2的关系：t1.mgr=t2.tno,SQL命令： select t2.tname from teacher t1,

36、teacher t2 where t1.mgr=t2.tno,EMP表别名为worker,EMP表别名为manager,SQLselect manager.ename from emp worker,emp manager where worker.mgr=manager.empno and worker.ename=BLAKE;,数据库原理与设计,一张表的两个副本之间连接，可以将同一张表的不同行连接起来。必须为表指定两个不同的别名. SELECT . FROM 表1 新表名1,表1 新表名2 WHERE 新表名1.列名=新表名2.列名,连接之二-自连接,例: 在Student中查询姓名相同的

37、学生信息. 例: 和李刚同一个班的学生信息-自身连接,数据库原理与设计,例如： 在Student中查询姓名相同的学生信息 SELECT * FROM Student ST1, Student ST2 WHERE ST1.SName=ST2.SName AND ST1.SNoST2.Sno,数据库原理与设计,例如：和李刚同一个班的学生信息-自身连接,Select S2.SName From Student S1, Student S2 Where S1. CLno= S2. CLno AND S1.SName=李刚,连接之三-不等值连接,不等值连接 多表操作的连接条件中不为“=”运算符的查询叫不

38、等值连接。,关系R,关系S,BD,T-SQL命令表示： SELECT * FROM R , S WHERE R.B S.D,关系代数表示：,连接之三-不等值连接,例：显示教师的基本信息（包括教师的编号，姓名、性别，出生日期，年龄，工资，及工资的级别）,数据库原理与设计,多表连接的性能低下多数是因为过滤操作的次序不合理与SQL书写不规范造成的。,优化连接查询,（1）过滤操作次序问题 （2）书写规则问题,1、系统执行的效率：先过滤数据，后进行连接。 2、书写角度：先书写表连接，后书写过滤条件。,3、编写规则的SQL语句，不仅可以提高SQL命令的可读性，而且在网络数据库环境下有利于在服务器内存中命中

39、，从而提高SQL执行的效率,优化连接查询,（1）过滤操作次序问题,SQL SERVER数据库采用自下而上的顺序来解析WHERE子句, SQL SERVER建议表之间的连接应该写在其他WHERE条件之前。,？哪种书写方式更好呢？,优化连接查询,（2）书写规则问题,重做日志文件,数据文件,数据库原理与设计,SQL语句的命中率越高，共享缓冲区的利用率越高，数据库的性能就越好。 优化策略：-制定SQL语句编码标准。,优化连接查询,（2）书写规则问题,数据库原理与设计,2.4.4 集合查询（合并查询）,（1）什么是集合（合并）查询 （2）集合（合并）查询使用规则 （3）集合（合并）查询的优劣势分析,数据

40、库原理与设计,（1）什么是集合查询（合并查询）,例：查选修了00001 或00005课程的学生,为了合并多个SELECT语句的查询结果，可以使用集合运算符（set operator）进行合并查询。 -其基本语法： SELECT 语句1 UNION|UNION ALL| INTERSECT|MINUS SELECT 语句2,SQL Server只支持UNION 和UNION ALL操作,不支持INTERSECT和MINUS,数据库原理与设计,（2）合并查询使用规则： （1）Select 语句必须选择同样数量的列。 （2）相应的列必须具有相同的类型与长度。,（3）在合并查询的结果集合中，自动以第一

41、个查询的字段名作为列名，因此，排序只能按照第一个查询的列名或者为序进行排序。,分析一下查询实现哪个更好呢？,例，查询选修了00001号课程或者00005号课程的学生学号 SELECT sno FROM sc WHERE cno=00001 union SELECT sno FROM sc WHERE cno=00005 等价与 SELECT sno FROM sc WHERE cno=00001 or cno=00005 order by sno,分析一下查询实现哪个更好呢？,例，查询雇员表EMP中，工资大于2500 并且岗位为MANAGER SELECT ename,sal,job FROM

42、 emp WHERE sal2500 intersect SELECT ename,sal,job FROM emp WHERE job=MANAGER; 等价于 SELECT ename,sal,job FROM emp WHERE sal2500 AND job=MANAGER; ORDER BY ename,（3）合并查询的优劣势分析 劣势 a.非合并查询比合并查询的效率高 b.书写上来看非合并查询比合并查询书写简洁 优势 合并查询的可读性强，比较容易实现。,2.4.3 嵌套查询,在SQL中，一个SELECTFROMWHERE语句称为一个查询块，将一个查询块嵌套在另一个查询块的Selec

43、t语句、From语句、Where条件或是Having条件中的查询称为嵌套查询或子查询。前者为子查询（内查询），后者为父查询（外查询）。,嵌套查询的分类,（4）带有EXISTS谓词的子查询 （5）带有NOT EXISTS谓词的子查询,（1）带有比较运算符的子查询 （2）带有in关键词的子查询 （3）带有any或all关键词的子查询,-不相关子查询 -相关子查询,不相关子查询的特点: 先执行内层查询,后执行外层查询.内层查询执行的结果是外层查询执行的条件,相关子查询的特点: 父查询所查阅的当条记录，是否满足子查询中提供的条件，如果满足，子查询返回逻辑真，否则返回逻辑假。,嵌套查询-（1）带有比较运

44、算符的子查询,特点：子查询的返回结果是一个值。子查询返回的结果作为父查询的条件(先执行子查询,后执行父查询)。,例如,查找和李刚同一个班的其他学生信息,Select * from student where CLno =(select CLno from Student where SName=李刚),嵌套查询-（1）带有比较运算符的子查询,例：查询“计算机0301班”的学生姓名,-利用嵌套查询来实现 SELECT Sname FROM student WHERE CLno= (SELECT CLno FROM Class WHERE CLname=计算机0301班),-利用连接查询来实现 S

45、ELECT Sname FROM student , Class WHERE Class . CLno= student. CLno AND CLname=计算机0301班,例：查询“计算机0301班”的学生姓名,嵌套查询-（2）带有in关键字的子查询,特点：子查询的返回结果是一个集合。子查询返回的结果作为父查询的条件(先执行子查询,后执行父查询)。,例：查询选修了“数学”课的学生名单。,-利用连接查询来实现,select sname from student a,course b,sc c where a.sno=c.sno and o=o and cname=数学,-利用嵌套查询来实现,s

46、elect sname from student where sno in (select sno from sc where cno=(select cno from course where cname=数学),数据库原理与设计,例:查体育课不及格的男生名单: SELECT Sname FROM Student WHERE Ssex=男 AND Sno IN (SELECT Sno FROM SC WHERE Score60 AND cno= (SELECT Cno FROM Course WHERE Cname=体育),数据库原理与设计,Select sno from sc where

47、cno=00001 and cno=00002,例如，查询同时选修00001和00002两门课的学生学号,SELECT cno,cname FROM course WHERE cno NOT IN (SELECT cno FROM sc ),例如，查询从未被选修的课程。,嵌套查询-（3）带有any或all关键词的子查询,all 大于子查询结果中的所有值 any 大于子查询结果中的某个值 any 小于子查询结果中的某个值,例，查询其它班中比030002班所有学生年龄都大的学生名单。,select sname from student where clno030002 and sageall(se

48、lect sage from student where clno=030002), Select min( ), Select max( ), Select max( ), Select min( ),数据库原理与设计,查询其它班中比030002班所有学生学分都少的学生姓名 SELECT SName FROM Student WHERE CLno030002 AND Scredits ALL (SELECT Scredits FROM Student WHERE CLno=030002),嵌套查询-不相关子查询练习,供应商表 s,零件表 p,工程表 j,供应信息表spj,查询为天津的项目提供

49、零件的供应商号及其城市。 查询为天津的项目提供红色零件的供应商号及其城市。 查询为“三建”工程提供零件的供应商名称。,嵌套查询-不相关子查询练习,嵌套查询-不相关子查询练习,检索”联华公司”中低于本公司平均工资的职工工号和姓名.(10题2小题) 检索职工人数最多的公司的编号和名称(10题4小题).,(1)查找联华公司职工的平均工资.(涉及到works,comp) (2)查找联华公司工资低于(1)的职工工号和姓名 (涉及到emp,works,comp),(1) 按照公司来统计公司的人数(涉及到works) (2) 从(1)的结果中查找人数最多的公司的人数 (3) 根据(2)查询的结果,从(1)的

50、结果中查找人数最多的公司的编号. (4) 已知公司编号,在COMP表中查找公司编号和名称,嵌套查询-不相关子查询练习,3. 检索平均工资高于联华公司平均工资的公司编号和名称.(10题的6小题) (1) 查找联华公司职工的平均工资.(涉及到works,comp) (2) 统计各公司的平均工资. (3) 在(2)的结果集中查找满足条件(1)的公司编号. (4) 已知公司编号在COMP中查找公司编号和名称.,数据库原理与设计,Exists 表示“存在”的意思，表示父查询所查阅的当条记录，是否满足子查询中提供的条件，如果满足，子查询返回逻辑真，否则返回逻辑假。 例：查询选修了课程号为00001的学生的

51、学号和姓名。 SELECT sno,sname FROM student a WHERE EXISTS(SELECT * FROM sc b WHERE cno=00001 AND a.sno=b.sno );,嵌套查询-（4）带有EXISTS关键词的子查询,SELECT sno,sname FROM student WHERE sno in (SELECT sno FROM sc WHERE cno=00001 );,使用exists连接子查询注意事项： （1）exists前不需要加列名，内层查询的SELECT语句也不需要指明具体的列（仅用一个*就可以），因为内层查询不需要向外层查询传递具体

52、的值。 （2）在内层查询中必须指明与外层查询的连接条件，确保内外两层查询相关联。,数据库原理与设计,查询所有选了00008课程的学生姓名,SELECT SName FROM Student WHERE EXISTS (SELECT * FROM SC WHERE SNo= Student.SNo AND CNo=00008),03001,数据库原理与设计,2.查询从未被选修的课程号和 课程名,SELECT cno,cname FROM course a WHERE NOT EXISTS (SELECT * FROM sc b WHERE o=o),数据库原理与设计,2.5 数 据 更 新,包括

53、插入数据，修改数据和删除数据,数据库原理与设计,2.5.1 插入数据,1插入单个记录 例2-84 将学号为“06001”、姓名为“王立”、性别为“男”的学生记录插入Student表中。 INSERT INTO Student (SNo,SName,SSex) VALUES (06001, 王立, 男) 2插入子查询结果 例2-86 把Student表中所有男生的记录插入到表ss中。 INSERT INTO ss SELECT * FROM Student WHERE SSex=男,数据库原理与设计,2.5.2 修改数据,UPDATE 表名 SET 列名=表达式 , 列名=表达式 WHERE 条

54、件 例2-8 将所有学生的成绩置0。 例2-9 将030001班所有学生的成绩置0。 例2-10 课后题11的(1)-(2) UPDATE SC SET Score=0 WHERE sno in (select sno from student where clno=030001),数据库原理与设计,2.5.3 删除数据,1DELETE语句 例2-91 将Student表中的学生“郝枚”的记录删除 DELETE FROM Student WHERE SName=郝枚 2TRUNCATE TABLE语句 例2-93 将Student表中的所有数据全部清除。 TRUNCATE TABLE Student,数据库原理与设计,2.3.2 索引的创建与管理,2.3 数 据 定 义,