SQLServer数据库开发经典案例完整版课件

1、021世纪高职高专创新精品规划教材 SQL Server 数据库开发经典案例教程 1第1章 数据库基础2本章目标数据库存在的必要性和数据库的发展SQL Server2008数据库以及它所支持的操作系统平台SQL Server2008的安装方法服务器上的后台服务SQL Server2008服务端组件和客户端工具学会使用SSMS创建登录 3数据库存在的必要数据存在的必要性可以存储大量的数据，便于用户进行检索和管理 可以保持数据的一致性、完整性，降低数据冗余 实现应用程序的数据共享和安全 利用数据库可以智能的对数据进行分析和统计 数据库的发展史 第一代数据库层次模型和网状模型 各种DBMS 软件都是

2、基于某种数据模型的 IBM 公司在1969 年推出了世界上第一个层次数据库系统IMS 第二代数据库关系型数据库 关系数据库是建立在关系模型之上的数据库 第三代数据库面向对象数据库 处于发展阶段，未来趋势4数据库的基本概念数据库的基本概念数据和信息数据中所包含的意义就是信息 数据库保存有组织的数据的容器关系数据库关系数据库是基于关系模型建立的，由一系列二维表格组成，将数据分类存储在多个二维表中，用关系（外键）来体现各个表之间的关系。 订单 客户 产品 产品数据库表数 据 表存储过程视 图.5数据库的基本概念数据库的基本概念表、实体和记录 实体是客观存在的事物，比如：桌子，椅子，电脑行(记录)6数

3、据库的基本概念数据库的基本概念数据冗余和数据完整性 数据冗余即相同的数据存在了多个地方 数据完整性是指数据库中数据的有效性和相容性编号姓名年龄民族部门1王涛33汉族人事管理部2李梅27汉族人事管理部市场营销部回族27李梅2财务管理部汉族33王涛1部门民族年龄姓名编号7SQL Server2008简介SQL Server2008简介 SQLServer是微软公司开发的一款关系型数据库产品，具有成本低，易上手，工具全等优点。适用于大型或超大型数据库服务器端。微软的软件的特点是版本分得细，可适合各种使用者不同的需要 SQL Server2008的体系结构 SQL Server2008系统由4个部分组

4、成：数据库引擎Analysis ServicesReporting ServicesIntegration Services数据库引擎 数据库引擎是Microsoft SQL Server2008系统的核心服务，是存储和处理关系（表格）类型的数据或XML文档数据的服务，负责完成数据的存储、处理和安全管理。例如：创建数据库、创建表、创建视图、查询数据和访问数据库等操作，都是由数据库引擎完成的 8SQL Server2008的安装和版本 安装：下载软件并参照书步骤进行安装版本企业版（SQL Server2008 Enterprise Edition） 标准版 (SQL Server2008 Sta

5、ndard Edition） 工作组版(SQL Server2008 Workgroup Edition）开发版(SQL Server2008 Developer Edition) 简易版（SQL Server2008 Express Edition) 9SQL Server2008后台服务服务器上的后台服务 在控制面板上选择【管理工具】- 【服务】选项。SQL Server服务 SQL Server Agent服务 SQL Server Browser SQL Server Integration Services服务 SQL Server Reporting Services服务 客户端管

6、理工具 SQL Server Management StudioSQL Server Configuration Manager Reporting Services配置SQL Server错误和使用情况报告 SQL Server Profiler 数据库引擎优化顾问10SQL Server2008的工作模式客户端应用程序SQL Server服务器批处理语句：语句1语句2GOSQL Server2008支持C/S工作模式，C/S模式即客户机/服务器（Client/Server）模式的简称，在该种模式下，服务器通常是在硬件配置和性能比较高、安装了服务器软件的计算机，客户机就是安装了客户端软件的一

7、般的计算机 11连接与断开数据库服务器连接与断开数据库服务器身份验证：连接之前，需要选择身份验证模式，系统提供了两种身份验证模式：Windows身份验证：用当前登录操作系统的身份去登录.SQL Server身份验证：用Windows加SQL Server的混合模式进行验证，需要用合法的SQL Server登录用户去访问默认系统在安装以后会自动产生一个sa的登录用户，具有最高权限。断开数据库服务器 12启动和停止SQL Server服务启动、停止、暂停和重新启动SQL Server服务 使用SQL Server配置管理器用SQL Server Management Studio (SSMS) 使

8、用操作系统的服务管理功能使用命令提示符启动SQL Server默认实例： net start mssqlserver停止SQL Server默认实例： net stop mssqlserver 13SQL Server Management Studio(SSMS)介绍 SSMS简介 SQL Server是作为单独的Windows进程在基于Windows的计算机上运行的，它可以运行在独立的桌面计算机上，也可以运行在服务器或者网络计算机上 .SQL Server作为服务运行，该服务由Windows自身进行监控 SSMS中的一个重要的工具就是是查询编辑器（query editor )SQL Ser

9、ver Management Studio(SSMS)的使用 14创建数据库登录账户 创建数据库登录账户在实际应用中，经常要根据不同的用户设置不同的访问权限来限制对数据库的操作 15总结数据库存在的必要性和数据库的发展使用数据库可以安全，高效的实现数据的存储和管理数据和信息的概念SQL Server2008数据库只适合Windows平台SQL Server2008的安装方法服务器上的后台服务SQL Server2008服务端组件和客户端工具创建登录的方法16使用数据库可以安全，高效的实现数据的存储和管理数据和信息数据库文件包括：主数据文件，次要数据文件和日志文件，每个数据库至少要包含一个数据文

10、件和日志文件文件组是逻辑上对数据文件进行管理的一种方式通过分离数据库可以将数据库分离成物理的文件附加数据库可以将物理文件恢复成数据库第2章 数据库管理17本章目标学会创建和维护数据库的方法掌握数据库的组成及数据文件的含义掌握数据库的分离和附加方法学会使用帮助文档18数据库组成19数据文件（主要/次要）和日志文件数据文件包含数据和对象：如表、索引、视图等日志文件包含恢复数据库中的所有事务所需的信息数据库主数据文件(.mdf)次要数据文件(.ndf)日志文件(.ldf)数据库文件和文件组简介主数据文件包含数据库的启动信息，用户数据和对象存储在此文件中，每个数据库只能有一个主数据文件，建议扩展名是

11、.mdf次要数据文件次要数据文件是可选的，由用户定义并存储用户数据。建议扩展名.ndf事务日志文件保存用于恢复数据库的日志信息。每个数据库必须至少有一个日志文件，建议扩展名.ldf20数据库系统数据库21mastertempdbmsdbStudentsDBBookShopDB用户数据库model4个系统数据库用户自己创建系统数据库4个系统数据库Master它存储 SQL Server 系统的所有系统级信息 ,它包括：登录帐户、链接服务器和系统配置设置。所有其他数据库的存在、数据库文件的位置SQL Server的初始化信息 model在 SQL Server 实例上创建的所有数据库的模板 Msd

12、b 由 SQL Server 代理用来计划警报和作业 tempdb 是连接到 SQL Server 实例的所有用户都可用的全局资源，它保存所有临时表和临时存储过程 22文件组BookShopDB的物理文件结构23BookShopDB.ldfE:日志文件 BookInfo Publisher UserInfo 主文件组BookShopDB.mdfC:用户定义文件组Book1.ndf Book2.ndfD:sys sys sysusers sysobjects 24数据库的建立-可视化方式数据库的建立-使用代码创建代码创建数据库25数据库设置数据库的设置数据库右键-属性 -选项-只读 -收缩26数

13、据库的修改修改数据库：可以在【文件】选项页通过修改数据文件修改数据库 27数据库删除可视化方式 代码方式Drop database test28分离数据库分离数据库 分离数据库是指将数据库从 SQL Server 实例中删除29附加数据库附加数据库附加是分离的反操作。就可以使用分离后的数据文件和日志文件将数据库附加到任何 SQL Server 实例，包括分离该数据库的服务器 30总结数据库文件包括：主数据文件，次要数据文件和日志文件，每个数据库至少要包含一个数据文件和日志文件文件组是逻辑上对数据文件进行管理的一种方式创建数据库可视化方式创建代码方式创建维护数据库数据库的修改和删除通过分离数据库

14、可以将数据库分离成物理的文件附加数据库可以将物理文件恢复成数据库使用帮助文档31第3章-1 SQLServer表管理32本章目标掌握数据表、数据完整性、主键和外键的基本概念掌握SQLServer数据类型创建表的时候要设定字段名，选取合适的数据类型和约束如果要保证某个列唯一并且自动增长，设定IDENTITY约束主键是保证实体完整性的约束，建议每个表有一个主键外键保证表之间的引用完整性，从表中的数据依赖于主表在建表时设置主键约束外键约束检查约束默认约束空值约束标识列 33数据表基本概念表书数据库中最重要的数据对象，数据存储在表里数据表由行和列组成34数据完整性数据完整性实体完整性将行定义为特定表的

15、唯一实体。即表中的每一行数据都代表一个不同的实体，不能存在相同的数据行 域完整性域完整性指特定列项的有效性 引用完整性用来限制两个有关联关系的表之间的数据 35主键和外键 主键表通常具有包含唯一标识表中每一行值的一个或一多个字段 特点一个表只能有一个主键主键中的列不能为空如果一个表的主键由多个列组成，该主键也叫“组合主键”外键外键约束保证了数据库中两个表中数据的引用完整性 将一个表的一列（或列组合）定义为引用其他表的主键，则引用表中的这个列（或列组合）就称为外键 36主键和外键37主键外键数据表建立数据表，建表时需考虑：字段的名称数据类型是否允许为Null是否自动增长等 38数据类型Sqlse

16、rver有以下数据类型数据类型类别 具体类型 精确数据 bigint，decimal，int，numeric，smallint，money，tinyint，smallmoney，bit 近似数字 float ,real字符串 char ,varchar, text Unicode字符串 nchar , nvarchar, ntext 二进制字符串 binary ,image, varbinary 日期类型 datetime ,smalldatetime 其他类型 Uniqueidentifier, xml, table等 39创建表创建表的实质就是定义表结构以及约束等属性。SQL Server

17、2008种提供了两种方式创建表。一种是通过SQL Server Management Studio(SSMS)一种是通过T-SQL代码。本书重点介绍使用T-SQL代码来创建表 40T-SQL代码建表通过执行Transact-SQL的CREATE TABLE 语句可以创建表，建表的语法如下：例如: CREATE TABLE mytable ( col1 char(10) primary key, col2 int not null )41CREATE TABLE 表名( 字段1 数据类型 列的特征， 字段2 数据类型 列的特征， 。 )建表【示例3-1】 ：建立用户表UserInfo, 建表时设

18、置主键并设置相应字段的not null（非空）属性 42USE BookShopDb -将当前数据库设置为BookShopDb GO CREATE TABLE UserInfo -创建用户表( UserID char(3) PRIMARY KEY, -UerID为主键 UserName char(10) not null, Sex char(2) not null, Age int , hiredate datetime, tele char(15), Addr varchar(100)GO 建表表的IDENTITY 属性标识列字段的数据类型只能是decimal，int，numeric，sma

19、llint，bitint，tinyint每个表最多有一列具有IDENTITY属性，且该列不能为空、不允许有默认值IDENTITY属性的语法: IDENTITY(seed,increment),seed称为种子，表示装载到表中第一行使用的值；如IDENTITY(1,1),表示该列值从1开始，每条记录增1.IDENTITY属性与CREATE TABLE和ALTER TABLE一起使用 43建表时使用标识列【示例3-2】建立图书类别表Category 44USE BookShopDb -将当前数据库设置为BookShopDb GOCREATE TABLE Publisher( PublisherID

20、 int IDENTITY(1,1) PRIMARY KEY, -自动编号（标识列） PublisherName varchar(50) not null -出版社名称，必须输入)GO 建表时使用约束45USE BookShopDb -将当前数据库设置为BookShopDbGO CREATE TABLE BookInfo /*创建BookInfo数据表*/( BookID char(15) PRIMARY KEY, -书号 ，主键 BookName varchar(100) not null, -书名 Author varchar(50) not null, -作者 ISBN char(13)

21、 , -ISBN,null可以省略 PublishDate datetime not null, -出版日期 PublisherID int not null REFERENCES Publisher(PublisherID),-外键 CategoryID int not null REFERENCES Category(CategoryID), -外键 Price money , -价格 Content text -介绍)GO 建表时使用约束46USE BookShopDb -将当前数据库设置为BookShopDbGO Drop table userInfoCREATE TABLE User

22、Info -创建用户表( UserID char(3) PRIMARY KEY CHECK(UserID LIKE 0-90-90-9), UserName char(10) UNIQUE, -用户名，唯一约束 Sex char(2) CHECK(Sex in (男,女), -性别，Check约束 Age int not null, -年龄,非空约束 hiredate datetime , -入职日期 tele char(15), -电话 Addr varchar(100) DEFAULT 广东广州 -地址，默认约束 )GO 【示例3-6】重建UserInfo表，演示在建表时如何设定约束建表时

23、使用约束47USE BookShopDb -将当前数据库设置为BookShopDb,GO CREATE TABLE SalesMaster /*创建SalesMaster销售主表*/(SalesMasterID int identity primary key, -销售id,自动增长，主键 SalesDate datetime not null, -销售日期 UserID char(3) not null), CONSTRAINT fk_UserID FOREIGN KEY (UserID) REFERENCES UserInfo(UserID) ON DELETE CASCADE 【示例3-

24、7】建立SalesMaster销售主表，通过设定外键与UserInfo表之间建立联系总结掌握数据表的概念和含义数据完整性、主键和外键的基本概念主键是保证实体完整性的约束，建议每个表有一个主键外键保证表之间的引用完整性，从表中的数据依赖于主表创建数据表创建表的时候要设定字段名，选取合适的数据类型和约束如果要保证某个列唯一并且自动增长，设定IDENTITY约束掌握SQLServer数据类型，三大类：数字、字符和日期在建表时设置主键约束外键约束检查约束默认约束空值约束标识列 48第3章-2 SQLServer表管理49本章目标约束的维护修改表添加约束删除约束建立数据库关系图 以图形方式显示数据库表之

25、间的关系SQL Server中的特殊表类型 已分区表临时表系统表修改和删除表修改表结构、删除表50约束的维护添加约束语法51ALTER TABLE 表名ADD CONSTRAINT 约束名约束类型具体的约束说明 USE BookShopDb -选择数据库，以下示例从略GOALTER TABLE UserInfo ADD CONSTRAINT pk_ UserID PRIMARY KEY(UserID)添加主键约束添加外键约束ALTER TABLE SalesDetails ADD CONSTRAINT fk_BookID FOREIGN KEY(BookID) REFERENCES BookI

26、nfo(BookID) 约束的维护删除约束语法52ALTER TABLE 表名DROP CONSTRAINT 约束名 ALTER TABLE UserInfo DROP CONSTRAINT pk_ UserID 数据库关系图 已建立了数据库中各个数据表之间的关系，可通过SQL Server提供的数据库关系图，即可以图表的形式显示各个数据表之间的关系如果表之间的关系没有建立，也可在通过数据库关系图建立数据表之间的关系 53数据库关系图54SQL Server中的特殊表类型 分区表当表数据非常大时可以将一个表水平划分为多个表单独存储，并且可以存储在多个不同的物理文件中。这样的表就是分区表 临时表

27、在操作过程中系统需要维护一些临时数据，比如果用户需要将查询结果排序，则会创建临时表并在临时表中将数据排序后返回给用户 系统表SQL Server 将定义服务器配置及其所有表的数据存储在一组特殊的表中，这组表称为系统表。任何用户都不应该直接更改系统表 55修改表结构修改表结构使用ALTER TABLE语句修改表结构增加列删除列修改列的数据类型改变列的长度等在表没有数据的情况下，可以对列进行任意修改或删除 56修改表结构-增加列和修改列57USE BookShopDbGOALTER TABLE UserInfo ADD email char(20) GO 增加列 给UserInfo表增加列emai

28、l,字符类型长度是20，非空修改列USE BookShopDbGOALTER TABLE UserInfo ALTER COLUMN addr varchar(150) not null增加UserInfo表的字段addr 长度，将其增加到150，类型不变 修改表结构-删除列58删除列删除UserInfo表 email列USE BookShopDbGOALTER TABLE UserInfo DROP COLUMN emailGO删除表删除表 DROP TABLE UserInfo如果删除的表和其他表有关联关系，必须先删除子表，然后再删除主表。否则系统会弹出错误 59总结约束的维护修改表添加约

29、束Alter table add constraint 约束名称 约束类型删除约束 alter table drop constraint 约束名称建立数据库关系图 以图形方式显示数据库表之间的关系SQL Server中的特殊表类型 已分区表临时表系统表修改和删除表修改表结构：修改添加字段Alter table 表名 add 字段名称 数据类型修改表修改字段 alter table 表名 alter column 字段名称 数据类型修改表删除字段 alter table 表名 drop 字段名称删除表Drop table 表名 60第4章 SQLServer数据管理61本章目标了解SQL和T-

30、SQLT-SQL实现数据的增加、修改、删除数据操作62SQL和T-SQL SQL(Structure Query Language,结构化查询语言) 主要功能就是同各种数据库建立联系，进行沟通。用于存取数据以及查询、更新和管理关系数据系统 SQL语言主要包括 数据操纵语言（DML）用来操作数据库中的数据，包括插入数据（INSERT）、修改数据（UPDATE）、删除数据（DELETE）、查询数据（SELECT）等操作 数据定义语言（DDL）用于创建或修改数据库里的对象，如创建Create table、修改Alter table 、删除Drop table等 数据控制语言（DCL） 用来控制用户对

31、数据库数据的访问权限,如：Grant、Revoke等63T-SQL介绍 T-SQLT-SQL（Transact-SQL）是SQL Server的核心 所有与SQL Server实例通信的应用程序，都是通过发送T-SQL语句到服务器来完成对数据库的操作 T-SQL是对标准SQL的扩展 是在标准SQL的基础上增加了变量定义、函数、流程控制等编程要素 语法约定 语句不区分大小写 64插入数据 65语法格式 INSERT INTO 表名（列名，n) VALUES (值，n) 【例4-1】 向BookShopDb 数据库的UserInfo表中插入所有列数据USE BookShopDb GO -批处理结束

32、标志，表示执行 INSERT INTO UserInfo(UserID,UserName,Sex,Age,hiredate,tele,Addr) VALUES(001,王琳琳,女,26,2005-1-24,07313677867,湖南长沙)INSERT INTO UserInfo VALUES(002,张家辉,男,27,2004-3-5,02034657654,广东广州)INSERT INTO UserInfo(UserID,UserName) VALUES(003,张田田)插入部分列时列名不能省略 插入所有列时列名可省略 插入数据66 【例4-3】向UserInfo表插入数据，尝试输入性别为

33、其他内容 INSERT INTO UserInfo(UserID,UserName,Sex,Age,hiredate,tele,Addr)VALUES(004,周晨阳,中,30,2003-4-2,02734652345,湖北宜昌)标识列字段数据的插入 【例4-5】向Category表插入数据, Category带有标识列CategoryID INSERT INTO Category(CategoryName) VALUES(计算机基础理论) 使用SELECT INTO 语句拷贝表SELECT INTO 可将几个表或视图中的数据组合成一个表 查看表数据：select * from copyUse

34、rInfo若只拷贝表结构： SELECT * INTO NewUserInfo FROM UserInfo where 1=2 67【例4-9】 使用SELECT . INTO拷贝UserInfo表年龄大于等于30的数据 生成一新表CopyUserInfoSELECT * INTO CopyUserInfo FROM UserInfo WHERE Age=30 更新数据语法格式68UPDATE 表名 SET WHERE 更新条件【例4-10】修改UserInfo表数据，把UserID为007的用户的住址改为甘肃酒泉UPDATE UserInfo SET Addr =甘肃酒泉 WHERE Use

35、rID =007修改数据69 【例4-11】如图完善UserInfo表数据,把用户号003用户的年龄、参加工作日期分别修改为33，2004-8-30 UPDATE UserInfo SET Age =33, hiredate =2004-8-30 WHERE UserID =003【例4-12】把用户001的参加工作日期加1天，地址改为湖南湘潭UPDATE UserInfoSET hiredate=hiredate+1, Addr=湖南湘潭WHERE UserID =001 注意：修改数据如果只修改一条记录，一般都按主键字段修改。删除数据 语法：70DELETE FROM 表名 WHERE 条

36、件 【例4-14】删除参加工作日期在2004-1-1之后的用户信息DELETE FROM UserInfo WHERE hiredate2004-1-1 【例4-15】 删除BookInfo表中书号是B0001的记录 DELETE FROM BookInfo WHERE BookID=B0001 因为要删除BookInfo表的书号是B0001的记录被SalesDetails表引用，所以必须先删除SalesDetails表的相应记录，才能删除BookInfo表的记录 使用TRUNCATE TABLE语句 语法：71 TRUNCATE TABLE 表名【例4-16】 删除CopyUserInfo表

37、的所有数据 TRUNCATE TABLE CopyUserInfo总结增加单行数据用INSERT INTOVALUES语句修改数据用UPDATE 表名 SET 字段名=新值 WHERE 条件。语句删除数据用DELETE FROM WHERE 语句快速删除整个表中的数据用TRUNCATE TABLE 语句 TRUNCATE TABLE 表名72第5章 简单查询和函数73本章目标掌握查询的基本语法掌握各种函数的应用字符串函数日期函数数字函数掌握函数在查询中的应用74查询75查询请求查询结果集结果集如何查询查询性别“男”的所有用户76查询 语法：77 SELECT FROM WHERE ORDER

38、BY ASC或DESC查询数据返回所有的行和列查询表中所有字段时，可以用星号*来代替字段名返回部分行和列筛选部分字段，必须明确指定字段名【例5-2】查询BookInfo表的BookName(书名),作者(Author),出版日期(publishDate),类别ID(CategoryID),价格(Price)等字段的数据 78 SELECT * FROM BookInfo SELECT BookName,Author,publishDate,CategoryID FROM BookInfo查询-使用别名79SELECT bookName as 书名,Author as 作者,publishDat

39、e as 出版日期,CategoryID as 类别,price as 价格FROM bookInfo 1，用as关键字 【例5-3】 用中文名称来显示书的信息,3种不同使用别名方法 2，用空格3，用=SELECT bookName 书名,Author 作者,publishDate 出版日期,CategoryID 类别,price 价格FROM bookInfoSELECT 书名= bookName,作者= Author, 出版日期=publishDate,类别=CategoryID , 价格=price FROM bookInfo查询-使用where使用WHERE子句筛选满足条件的数据行 8

40、0【例5-4】查询2007年以后出版的所有书籍的书名、作者、出版日期和价格SELECT bookName,author,publishDate,price FROM BookInfo WHERE PublishDate2007-01-01函数T-SQL函数表示可以接受零个、一个或多个输入值，并返回一个标量值或一组值81 表5-1 常用T-SQL函数函数类别 说明 字符串函数 对字符串（char 或 varchar）输入值执行运算，然后返回一个字符串或数字值。日期和时间函数 对日期和时间输入值执行运算，然后返回字符串、数字或日期和时间值。数学函数 基于作为函数的参数提供的输入值执行运算，然后返回

41、数字值。系统函数返回系统配置信息、返回有关数据库和数据库对象的信息、执行运算后返回 SQL Server 实例中有关值、对象和设置的信息，返回有关用户和角色的信息等。字符串函数 字符串函数 字符串函数用于对字符串数据进行处理，如字符串截取、查找子串、计算字符串长度等，并返回一个字符串或数值 82字符串函数83字符串函数的使用84 SELECT bookName,Author,PATINDEX(%数据库%,bookName) POSFROM BookInfo WHERE PATINDEX(%数据库%,bookName)0【例5-8】使用日期函数获取今天的日期时间及明天的日期和时间【例5-7】使用

42、PATINDEX函数查询BookInfo表中包含数据库的书名和作者以及数据库出现的位置 SELECT GETDATE() 今天, DATEADD(DAY,1,GETDATE() 明天日期和时间函数85系统函数86总结掌握查询的基本语法从表中筛选记录的时候用SELECT语句通过给列起别名的方式简化代码掌握各种函数的应用字符串函数日期函数数字函数掌握函数在查询中的应用87第6章 检索数据88本章目标掌握WHERE子句中运算符的使用掌握使用Top关键字和使用Distinct消除重复行掌握模糊查询Like,between,in的用法89在WHERE子句中使用运算符 SELECT通过WHERE子句构造记

43、录的筛选条件，从而选择出满足条件的记录 记录的筛选条件：运算符 函数使用 IS NULL条件90运算符使用比较运算符和逻辑运算符 91【例6-1】 查询BookInfo表中价格低于的书名、作者和价格 SELECT BookName,Author,Price FROM BookInfo WHERE Price 40【例6-2】 查询BookInfo表中价格在30到80之间（包括边界） 的书名、价格和说明 SELECT BookName,price,Content FROM BookInfo WHERE Price =30 使用 IS NULL条件 使用 IS NULL条件92【例6-3】 查询U

44、serInfo中电话（tele）为空的用户信息 SELECT UserID,UserName,Sex,Age,hiredate,tele,Addr FROM UserInfo WHERE tele is null 查询中使用函数93【例6-5】 在BookInfo表中，查询书名长度小于10的图书书名、作者和书名长度 SELECT bookname,Author ,LEN(bookName) FROM BookInfo WHERE LEN(bookname)10设置结果集格式 使用ORDER BY排序数据 94【例6-6】 在BookInfo表中，按出版日期的先后顺序，查询价格低于45的图书的书

45、号、书名、价格、作者和出版日期。 SELECT bookid,bookName,Price,Author,PublishDate FROM BookInfo WHERE Price b.PriceWHERE b.BookID =B0002总结对数据进行统计使用SQL内置的聚合函数MaxMinSumAvgCount对数据进行分组用GROUP BY，分组条件筛选用Having查询的数据来自于多个表用关联查询关联查询连接内联接内联接筛选多表中完全匹配的行外部联接左外联接以左表为主筛选数据右外联接以右表为主筛选数据自联接自联接将表自身通过别名关联125第8章 子查询126本章目标掌握子查询的使用子查询

46、返回单一值子查询返回值列表子查询的存在性测试EXISTS掌握集合运算并集(Union)交集(Intersection)减(Except)127子查询问题：显示价格最高书籍的书号、书名和价格 解决方法：查询出最高价格select MAX(price) from BookInfo以价格为条件查询出书号、书名和价格select bookid,bookname,price from BookInfo where Price=(select MAX(price) from BookInfo) 128外部查询内部查询子查询子查询就是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语

47、句或其他子查询中的查询，子查询也称为内部查询，而包含子查询的语句也称为外部查询 子查询的SELECT 查询总是使用圆括号括起来。基本语法：129SELECT 列名.FROM 表名WHERE 条件 =(SELECT 列名 FROM 表名 WHERE 条件）返回单个值的子查询 当子查询返回单个值时，外部查询条件和子查询之间使用比较运算符（、=、(SELECT AVG(price) FROM BookInfo) 比较运算符返回单个值的子查询查询书名为“JavaWeb开发技术详解”的出版社ID和出版年限 SELECT publisherID FROM BookInfo WHERE BookName =

48、JavaWeb开发技术详解SELECT YEAR(publishDate) FROM BookInfo WHERE BookName =JavaWeb开发技术详解 代码为：SELECT BookID,BookName,publishDate,PublisherID FROM BookInfo WHERE PublisherID =(SELECT publisherID FROM BookInfo WHERE BookName =JavaWeb开发技术详解) AND YEAR(PublishDate) = (SELECT YEAR(publishDate) FROM BookInfo WHERE

49、 BookName =JavaWeb开发技术详解 )131【例8-2】 查询具有和“JavaWeb开发技术详解”相同出版社和相同出版年限的书籍的书号、书名和出版日期解决方法：返回值列表的子查询 子查询返回值列表即子查询返回的不是单个值，外部查询的检索条件WHERE和子查询之间使用以下运算符 132返回值列表的子查询【例8-3】查询有销售记录的人员信息先求子查询，即在销售主表SalesMaster中查找销售人员ID SELECT UserID FROM SalesMaster因为销售人员不止一个，所以外部查询和子查询之间使用INSELECT * FROM UserInfo WHERE UserI

50、D IN (SELECT UserID FROM SalesMaster) 133返回值列表的子查询【例8-3】显示价格高于类别JAVA的所有书籍的书号、书名、作者和价格。分析：先求出类别是“JAVA“的所有书籍的价格列表 SELECT Price,BookID,CategoryID FROM BookInfo WHERE CategoryID = (SELECT categoryid FROM Category WHERE CategoryName =JAVA)外部查询和子查询之间应该使用ALLSELECT BookID ,BookName ,Author,Price FROM BookIn

51、fo WHERE Price ALL(SELECT Price FROM BookInfo WHERE CategoryID =(SELECT categoryid FROM Category WHERE CategoryName =JAVA) ) 134值列表EXISTS存在性测试相关子查询在子查询中引用外部查询的列，即子查询的执行与外部查询有关 135 【例8-4】显示同类图书中价格最高的的书籍信息，显示内容包括书号、书名、价格和类别。分析：对BookInfo表的每一条记录，与同类图书的价格比较，求出最高价格的书籍SELECT BookID ,BookName ,Price ,Catego

52、ryID FROM BookInfo a WHERE a.Price =( SELECT max(b.price) FROM BookInfo b WHERE a.CategoryID =b.CategoryID ) EXISTS存在性测试EXISTS存在性测试 指定一个子查询，测试行是否存在，也就是对于外部查询的每一行，检查子查询是否是空集合，如不是空集合，主查询返回值，否则主查询没有返回值 该题目涉及销售细表SalesDetails和书籍表BookInfo,因为查询的是书籍信息，所以BookInfo表应该放在外部查询位置，销售细表SalesDetails应放在子查询位置 136【例8-5】

53、查询已销售的书籍信息，显示内容包括书号、书名和作者。SELECT BookID,BookName,Author FROM BookInfo WHERE EXISTS(SELECT * FROM SalesDetails WHERE BookID=BookInfo.BookID)NOT Exists子查询【例8-6】查询没有销售记录的用户信息，显示内容包括用户ID、用户名和入职月份。分析：该题目外部查询是用户UserInfo表，子查询是销售主表SalesMaster,由于查询的是没有销售记录的用户，所以使用NOT EXISTS关键字判断子查询是否有记录返回 137用户id用户idSELECT a

54、.UserId,a.UserName,YEAR(a.hiredate) 入职年份FROM UserInfo a WHERE NOT EXISTS (SELECT * FROM SalesMaster b WHERE b.UserID=a.UserID)集合操作 集合操作是一种以两个或多个SELECT 查询结果集为操作对象，通过对这些查询结果集实施并、交、差等集合运算，获取新的结果集的一种查询方法 集合操作的运算符主要包括以下3种 并集(UNION)交集(INTERSECT)差(EXCEPT)138并集（UNION） 并集并集是将两个或更多查询的结果合并为单个结果集，该结果集包含联合查询中的所有

55、查询的全部行 UNION的使用限制：集合操作符所涉及的查询应有相同的列数，对应的列必须具有相同的数据类型。所显示的行上面的标签来自第一个SELECT语句。UNION和UNION ALLUNION ALL:返回两个查询所选定的所有行，包括重复行UNION:未指定ALL,返回的结果集中不包括重复行 139并集（UNION）【例8-7】 把“机械工业出版社”出版的图书和“人民邮电出版社”出版社的图书一起显示输出查询“机械工业出版社”的图书的T-SQL如下查询“人民邮电出版社”的图书的T-SQL如下 140SELECT BookID ,BookName,Author,PublisherID FROM

56、BookInfo WHERE PublishERID=(SELECT PublisherID FROM Publisher WHERE PublisherName =机械工业出版社)SELECT BookID ,BookName,Author,PublisherID FROM BookInfo WHERE PublishERID=(SELECT PublisherID FROM Publisher WHERE PublisherName =人民邮电出版社 )SELECT BookID ,BookName,Author,PublisherID FROM BookInfo WHERE Publis

57、hERID=(SELECT PublisherID FROM Publisher WHERE PublisherName =机械工业出版社)UNION SELECT BookID ,BookName,Author,PublisherID FROM BookInfo WHERE PublishERID=(SELECT PublisherID FROM Publisher WHERE PublisherName =人民邮电出版社)并集（UNION）【例8-8】将新数据显示在查询中141SELECT 009 AS 用户ID,张三 as 用户名, 男 as 性别, 20 as 年龄UNION SELE

58、CT UserID ,UserName,Sex,Age FROM UserInfo交集(INTERSECT) 交集(INTERSECT) INTERSECT运算符比较两个查询的结果，返回重复值，即返回两个结果集中的交集，两者都包含的记录 INTERSECT运算符的使用限制与UNION运算符相同。【例8-9】查询有销售记录的书籍142SELECT BookID as 书ID FROM SalesDetailsINTERSECT SELECT BookID FROM BookInfo 差(EXCEPT) 差(EXCEPT)运算符比较两个查询的结果，返回非重复值。EXCEPT运算符获取在结果集A中但

59、不在结果集B中的记录，即结果集A减去结果集B EXCEPT运算符的使用限制与UNION运算符相同 143差(EXCEPT)【例8-10】查询BookInfo表中价格低于40的书籍中去掉2006年出版的书籍信息，显示内容包括书号、书名、价格和出版日期 144SELECT BookID 书ID ,BookName 书名 ,Price 价格 ,PublishDate 出版日期 FROM BookInfo WHERE Price 40EXCEPTSELECT BookID ,BookName ,Price ,PublishDate FROM BookInfo WHERE YEAR(PublishDat

60、e) =2006总结子查询就是嵌套在SELECT，INSERT，UPDATE和DELETE中的查询。一般子查询不依赖于外部查询，执行时先执行内部查询再执行外部查询，通常用比较运算符和IN，NOT IN关键字。相关子查询即子查询的条件依赖于外部查询，执行时先执行外 部查询，然后根据外部查询返回的记录行数重复执行内部查询通 常用EXISTS，NOT EXISTS关键字。集合运算结果集要进行合并时用UNION运算符。结果集取交集用INTERSECT运算符。结果集相减用EXCEPT运算符。145第9章 数据库的设计本章目标了解设计数据库的步骤掌握数据库设计模型ER模型掌握使用范式规范化数据库为什么需要