《数据库应用技术》PPT课件.ppt

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

1、数据库应用技术,总复习,Sql Server数据库中常见的数据库对象主要包括表、存储过程、触发器、视图、规则、默认值、索引等。,第一章SQL Server 2000高级安装,1.1 SQL Server 2000 版本信息 1.2 使用SQL Server命名实例和多实例 1.3 确定启动服务的帐户 1.4 选择安全机制 1.5 SQL Server 2000的排序规则 1.6 网络库 1.7 验证安装的正确性 1.8 无人值守安装 1.9 升级SQL Server 1.10 故障排除,1.1 SQL Server 2000 版本信息,共有6个版本：企业版、企业评估板、标准版、个人版、开发版、

2、Windows CE版 每个版本支持的操作系统,1.2 使用SQL Server命名实例和多实例,实例是关系数据库引擎，是SQL SERVER 2000的工作单元。每一个实例都由系统数据库和用户数据库组成，拥有独立的管理和运行环境。 默认实例：一台计算机只能存在一个默认实例 命名实例：除默认实例以外，其他所有的实例都称为命名实例,1.2 使用SQL Server命名实例和多实例,多实例：一台计算机允许安装多个SQL Server 2000实例。一台计算机最多可以运行16个命名实例。 实例名称及命名规则：必须将实例名限制为 16 个字符等等（P5）。,1.3 确定启动服务的帐户,帐户类型为本地系

3、统帐户或域用户帐户。可使用同一帐户启动两个服务，也可以为每个服务分别指定帐户。,1.4 选择安全机制,在安装SQL Server的过程中，用户可以选择Windows 身份验证模式或者选择混和身份验证模式作为安全机制，限制和验证用户对SQL Server的访问。,1.5 SQL Server 2000的排序规则,排序规则是SQL Server 对字符数据进行存储和排序的方式。SQL Server具有两种类型的排序规则：Windows排序规则和SQL 排序规则。,1.6 网络库,网络库也称作通讯协议，用于在运行SQL Server的客户端和服务器之间传递网络数据包。 SQL Server默认实例网

4、络库为命名管道和TCP/IP协议网络库,1.7 验证安装的正确性,数据库的类型：系统数据库和用户数据库。 master 数据库 tempdb 数据库 model 数据库 msdb 数据库 northwind数据库 pubs数据库,第二章 Transact-SQL程序设计逻辑,系统提供的数据类型 2.1 数据定义语言(DDL) 2.2 使用SELECT 语句查询数据 2.3 使用Insert向表中插入数据 2.4 使用Update修改表中的数据 2.5 使用Delete删除表中的数据 2.6 大对象数据的访问控制 2.7 T-SQL中数据库对象的引用,系统提供的数据类型,整数数据类型 int、s

5、mallint 、tinyint 、bigint 浮点数据类型 单精度类型（real） 、双精度类型（float） 、精确数值类型（decimal、numeric） 字符数据类型 char 、nchar 、varchar 、nvarchar,系统提供的数据类型,日期和时间数据类型 datetime 、smalldatetime 二进制数据类型 binary 、varbinary 逻辑数据类型 bit数据类型占用1个字节的存储空间，其值为0或1。如果输入0或1以外的值，将被视为1。bit类型不能定义为NULL值。,系统提供的数据类型,文本和图形数据类型 text 、ntext 、image 货币

6、数据类型 money 、smallmoney,2.1 数据定义语言(DDL),数据定义语言用来定义数据的结构，如创建、修改或者删除数据库对象，常用的数据定义语言有：CREATE、ALTER、DROP等。,创建一张表合同表Contract，该表中含有五个字段，分别是合同号ContractID字符型数据、单位代码 CompanyID 字符型数据，来源于公司信息表、合同名称 ContractName 字符型数据、合同总金额 ContractVolume 数值型数据、签定日期 SignDate 日期型数据，SQL语句如下： CREATE TABLE Contract ( ContractID varc

7、har (10) NOT NULL , CompanyID varchar (10) NOT NULL , ContractName varchar (30) NULL , ContractVolume numeric(18, 2) NULL, SignDate Datetime NULL ),Create Table 语句,创建一个临时表MyTempQuery，用来保存当前用户的合同号与合同总金额 CREATE TABLE #MyTempQuery ( ContractID Varchar(10) PRIMARY KEY, ContractVolume Numeric(18, 0) ),Al

8、ter Table 语句,例程2.4：为表Contract增加一个新的字段CompanyID，数据类型为Varchar，数据长度为20个字符，允许空值。SQL语句如下： ALTER TABLE Contract ADD CompanyID VARCHAR(20) NULL 例程2.5：改变表Contract 中的字段ContractVolume的数据类型 ，使其数据类型为Money类型，SQL语句如下： ALTER TABLE Contract ALTER COLUMN ContractVolume money,Drop Table 语句,例程2.6：从当前数据库中删除 Contract 表及

9、其数据和索引，SQL 语句如下： DROP TABLE Contract 注：DROP TABLE 不能用于除去由 FOREIGN KEY 约束引用的表。必须先除去引用的 FOREIGN KEY 约束或引用的表。除去表时，表上的规则或默认值将解除绑定，任何与表关联的约束或触发器将自动除去。,Drop Table 语句,例程2.6：从当前数据库中删除 Contract 表及其数据和索引，SQL 语句如下： DROP TABLE Contract 注：DROP TABLE 不能用于除去由 FOREIGN KEY 约束引用的表。必须先除去引用的 FOREIGN KEY 约束或引用的表。除去表时，表上

10、的规则或默认值将解除绑定，任何与表关联的约束或触发器将自动除去。,SELECT 子句,例程2.7：要查询Contract表中前50条记录，要求返回两列数据：合同名称和合同总金额，SQL语句如下： Select TOP 50 ContractName as 合同名称, ContractVolume as 合同总金额 From Contract 例程2.8：查询与多少个单位签了合同，需要查询Contract表中的CompanyID列，按要求不能显示重复的列，需要使用DISTINCT 关键字，SQL语句如下：SELECT DISTINCT CompanyID FROM Contract,FROM 子

11、句,例程2.9：查询每个合同的合同明细金额之和，要求返回列合同号与合同明细金额之和，合同表与合同明细表是主子表的关系。合同明细表有三个字段分别是：ContractID、ContractDetailID、Volume。 SELECT a.ContractID ,sum(Volume) FROM Contract a LEFT JOIN ContractDetail b ON a.ContractID=b.ContractID GROUP BY a.ContractID,WHERE 子句,例程2.10：查询合同表中所有签订日期在2002年1月1日以后的记录，SQL语句如下： SELECT Cont

12、ractID,ContractName,ContractVolume,SignDate FROM Contract WHERE SignDate2002-01-01,WHERE 子句,例程2.11：查询与“北京怡神工贸公司”签定的合同总金额大于100万的所有合同。此查询需要公司信息表，该表有两个字段：CompanyID、CompanyName，与合同表是主子表的关系，每个单位对应多个合同。SQL语句如下： SELECT ContractID,ContractName,ContractVolume,SignDate FROM Contract,Company WHERE Contract.Com

13、panyID=Company.CompanyID AND CompanyName= 北京怡神工贸公司 AND ContractVolume1000000,WHERE 子句,例程2.12：查询总金额小于10万的合同，或者签定日期在2002年5月1日与2002年8月1日之间的合同。因为查询条件限定词AND 和OR 的执行顺序是按先后顺序执行，因此需要先执行的部分应加括号。SQL语句如下： SELECT ContractID,ContractName,ContractVolume,SignDate FROM Contract WHERE ContractVolume2002-05-01 AND Si

14、gnDate2002-08-01),GROUP BY 子句,例程2.13：查询与各公司签定的所有合同的总金额之和，在此需要聚合函数SUM，SQL语句如下： SELECT CompanyName,SUM(ContractVolume) FROM Contract,Company WHERE Contract.CompanyID=Company.CompanyID GROUP BY CompanyName,GROUP BY 子句,例程2.14：查询各合同的合同明细金额的最小值，需要用到函数MIN，SQL语句如下： SELECT ContractName,MIN(Volume) FROM Contr

15、act,ContractDetail WHERE Contract.ContractID=ContractDetail.ContractID GROUP BY ContractName,HAVING 子句,例程2.16：查询每个单位每个合同明细金额之和，要求该合同为2002年1月1日之后签名册定的合同，SQL语句如下： SELECT CompanyName,ContractName,SUM(Volume) FROM Contract,ContractDetail,Company WHERE Contract.ContractID=ContractDetail.ContractID AND Co

16、ntract.CompanyID=Company.CompanyID GROUP BY CompanyName,ContractName,SignDate HAVING SignDate2002-01-01,ORDER BY 子句,例程2.17：按合同总金额从小到大的顺序，对于所有合同排序输出。SQL语句如下： SELECT ContractID,ContractName,ContractVolume,SignDate FROM Contract ORDER BY ContractVolume ASC,ORDER BY 子句,例程2.18：查询每个单位每个合同明细金额之和，要求该合同为2002

17、年1月1日之后签名册定的合同，并且按单位名称和签定日期升序排序，SQL语句如下： SELECT CompanyName,ContractName,SUM(Volume) FROM Contract,ContractDetail,Company WHERE Contract.ContractID=ContractDetail.ContractID AND Contract.CompanyID=Company.CompanyID GROUP BY CompanyName,ContractName,SignDate HAVING SignDate2002-01-01 ORDER BY Company

18、Name,SignDate,NULL关键字,例程2.19：检查合同表中SignDate为NULL的记录，SQL语句如下： SELECT * FROM Contract WHERE SignDate IS NULL,LIKE关键字,例程2.20：查询所有公司名称中含有“北京”的合同，可以使用%通配符，SQL语句下如： SELECT ContractID,ContractName,ContractVolume FROM Contract,Company WHERE Contract.CompanyID=Company.CompanyID AND CompanyName LIKE %北京%,例程2.

19、21：查询所有合同号以“01”结尾的合同，并且该合同号为四位字符长度，可以使用_通配符，_通配符代表一个字符位置，SQL语句下如： SELECT ContractID,ContractName,ContractVolume FROM Contract WHERE ContractID LIKE _01 例程2.22：查询所有合同号以“01”结尾的合同，并且该合同号为三位字符长度，且第一位介于0-9之间，SQL语句下如： SELECT ContractID,ContractName,ContractVolume FROM Contract WHERE ContractID LIKE 0-901,

20、CUBE 关键字,例程2.23：查询与各公司签定的所有合同的总金额之和，并且要显示汇总行，SQL语句如下： SELECT CompanyName,SUM(ContractVolume) FROM Contract,Company WHERE Contract.CompanyID=Company.CompanyID GROUP BY CompanyName WITH CUBE,CASE关键字,例程2.24：查询所有公司的合同，如果合同总金额之和小于10000元，则显示该公司为“小公司”，如果大于10000则显示为“大公司”，使用CASE 关键字，SQL语句如下： SELECT CompanyNa

21、me,公司规模= CASE WHEN SUM(ContractVolume)10000 THEN 大公司 WHEN SUM(ContractVolume)=10000 THEN 小公司 END FROM Contract,Company WHERE Contract.CompanyID=Company.CompanyID GROUP BY CompanyName,IN关键字,例程2.25：分别用OR 关键字和IN关键字实现查询，要求查出所有合同号为“101”或“102”的合同。SQL语句如下： SELECT ContractID,ContractName,ContractVolume FROM

22、 Contract WHERE ContractID IN(101 ,102) 例程2.26：用IN关键字实现查询，要求查出所有合同号不为“101”或“102”的合同。SQL语句如下： SELECT ContractID,ContractName,ContractVolume FROM Contract WHERE ContractID NOT IN （101 ,102）,2.3 使用Insert向表中插入数据,例程2.29：向公司信息表中添加一条记录，单位代码为“0001”，单位名称为“北京怡神工贸公司”，SQL 语句如下： INSERT INTO Company(CompanyID,Com

23、panyName) VALUES (0001, 北京怡神工贸公司) 例程2.30：向合同表中添加一条记录，合同号为“1001”，合同名称为“2003年1001号合同”，单位代码为“0001”，合同总金额为10万元，签定日期为2003年1月1日，SQL语句如下： INSERT INTO Contract (ContractID,ContractName,CompanyID,ContractVolume,SignDate) VALUES (1001, 2003年1001号合同,0001,100000,2003-01-01),使用 InsertSelect 语句,例程2.31：创建一个临时的合同表，

24、把合同表中2002年1月1日以后签定的合同记录添加到临时合同表中，SQL语句如下： 创建临时表 CREATE TABLE #TempContract( ContractID varchar (10) NOT NULL , CompanyID varchar (10) NOT NULL , ContractName varchar (30) NULL , ContractVolume numeric(18, 2) NULL, SignDate Datetime NULL) 向临时表中插入符合条件的记录 INSERT INTO #TempContract (ContractID,CompanyID

25、,ContractName,ContractVolume,SignDate) SELECT ContractID,CompanyID,ContractName,ContractVolume,SignDate FROM Contract WHERE SignDate2002-01-01,使用 Select Into 语句,例程2.32：创建一个新的表，把合同表中合同总金额大于10万元的合同记录添加到新表中，新表名为NewContract，SQL语句如下： SELECT ContractID,CompanyID,ContractName,ContractVolume, SignDate INTO

26、NewContract FROM Contract WHERE ContractVolume100000,2.4 使用Update修改表中的数据,例程2.33：更新合同表中所有单位代码为“0001”的合同记录，使其单位代码为“0002”，SQL语句如下： UPDATE Contract SET CompanyID=0002 WHERE CompanyID=0001,基于其他表更新数据行,例程2.34：把合同明细表中金额添加到合同表中的合同总金额中，以反应合同表中最新的合同明细金额，SQL语句下： UPDATE Contract SET ContractVolume=Contract.Contr

27、actVolume+ContractDetail.Volume FROM Contract,ContractDetail WHERE Contract.ContractID=ContractDetail.ContractID,使用子查询更新指定的行,例程2.35：更新合同表中合同总金额字段，条件是如果在合同明细中该合同没有记录，则使其合同总金额为0，SQL语句如下： UPDATE Contract SET ContractVolume=0 WHERE ContractID NOT IN (SELECT ContractID FROM ContractDetail),2.5 使用Delete删除

28、表中的数据,例程2.36：用DELETE删除表的所有行，删除合同明细表中的所有记录，SQL语句如下： DELETE FROM ContractDetail 例程2.37：用DELETE删除表的指定行，删除合同表中所有合同总金额为0并且签定日期在2002年5月1日之前的合同记录，SQL语句如下： DELETE FROM Contract WHERE ContractVolume=0 AND SignDate2002-05-01,2.6 大对象数据的访问控制,例程2.38：在合同表中增加一个字段：合同备注 ALTER TABLE Contract Add ContractMemo nText NU

29、LL 现在查询合同备注的前100个字符，SQL语句如下： SET TEXTSIZE 100 SELECT (ContractMemo) FROM Contract 例程2.39：读取合同表中合同备注字段的第11到31个字符 DECLARE temp varbinary(16) SELECT temp = TEXTPTR(Contract Memo) FROM Contract READTEXT Contract.ContractMemo temp 10 30,2.7 T-SQL中数据库对象的引用,在T-SQL中，数据对象的命名规则是：Server.Database.Owner.Object，前

30、三个限定是可以省略的。省略了某一个或多个对象的限定称为部分限定。,完全限定,格式：Server.Database.Owner.Object SELECT * FROM RED.Selling.Dbo.Contract,部分限定,省略Server: 用户当前连接的Server SELECT * FROM Selling.dbo.Contract 省略Database: SELECT * FROM dbo.Contract 省略Owner: SELECT * FROM Contract,第三章 面向复杂应用的Transact-SQL,3.1 使用系统内建函数 3.2 子查询 3.3 联合查询 3.

31、4 其他复杂应用 3.5 查询语句的性能优化,3.1 使用系统内建函数,3.1.1 日期和时间函数 3.1.2 聚合函数 3.1.3 字符串函数 3.1.4 系统统计函数,日期和时间函数,DATEADD：在向指定日期加上一段时间的基础上，返回新的日期值； DATEDIFF：返回跨两个指定日期的日期和时间边界数； DATENAME：返回代表指定日期的指定日期部分的字符串； DATEPART：返回代表指定日期的指定日期部分的整数； DAY：返回代表指定日期的天的日期部分的整数； GETDATE：回当前系统日期和时间； GETUTCDATE：返回表示当前 UTC 时间（世界时间坐标或格林尼治标准时间

32、）的值； MONTH：返回代表指定日期月份的整数； YEAR：返回表示指定日期中的年份的整数。,日期和时间函数,列出所有合同的签定日期，要求在签定日期上加5天显示，列出合同号，签定日期，SQL语句如下： SELECT ContractID, DATEADD(day, 5, SignDate) FROM Contract 列出所有合同签定日期与现在日期所差的天数，列出合同号、签定日期，SQL语句如下： SELECT ContractID, DATEDIFF(day, signdate, getdate() FROM Contract,聚合函数,AVG：取平均值； MAX：取最大值； MIN：取最

33、小值； SUM：求和函数； COUNT：返回组中项目的数量。,聚合函数,列出所有公司的所有合同的合同总金额的平均值，要求列出公司名称，合同平均值，SQL语句如下： SELECT CompanyName,AVG(ContractVolume) as 合同平均值 FROM Contract,Company WHERE Contract.CompanyID=Company.CompanyID GROUP BY CompanyName,聚合函数,列出各合同的合同明细的数量，即一个合同有几条合同明细记录，显示合同号，明细数量，SQL语句如下： SELECT ContractID,COUNT(Contra

34、ctID) FROM ContractDetail GROUP BY ContractID,字符串函数,ASCII：返回字符表达式最左端字符的 ASCII 代码值； CHAR：将ASCII 代码转换为字符的字符串函数； SPACE：返回由重复的空格组成的字符串； REPLACE：用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式； STR：由数字数据转换来的字符数据； LEFT：返回从字符串左边开始指定个数的字符； SUBSTRING：返回字符串表达式的一部分；,字符串函数,LEN：返回给定字符串表达式的字符（而不是字节）个数，其中不包含尾随空格； REVERSE：返回字符

35、表达式的反转； LOWER：将大写字符数据转换为小写字符数据后返回字符表达式； RIGHT：返回字符串中从右边开始指定个数的字符； UPPER：返回将小写字符数据转换为大写的字符表达式； LTRIM：删除起始空格后返回字符表达式； RTRIM：截断所有尾随空格后返回一个字符串 。,字符串函数,用“有限公司”替换公司信息表中公司名称字段中的“公司”，SQL语句如下： UPDATE Company SET CompanyName=REPLACE(CompanyName,公司,有限公司),字符串函数,返回公司信息表中公司名称的字符长度，显示列，公司名称、字符长度，SQL语句如下： SELECT Co

36、mpanyName,LEN(CompanyName) as 字符长度 FROM Company,字符串函数,把合同表中的合同号的小写字母全部变为大写字母显示，要求显示列：合同号、合同名称，SQL语句如下： SELECT UPPER(ContractID),ContractName FROM Contract,系统统计函数,CONNECTIONS：返回自上次启动SQL Server 以来连接或试图连接的次数； CPU_BUSY：返回自上次启动 SQL Server以来 CPU 的工作时间，单位为毫秒（基于系统计时器的分辨率）； TIMETICKS：返回一刻度的微秒数； IDLE：返回 SQL S

37、erver 自上次启动后闲置的时间，单位为毫秒（基于系统计时器的分辨率）；,系统统计函数,TOTAL_ERRORS：返回SQL Server自上次启动后，所遇到的磁盘读/写错误数； TOTAL_READ：回SQL Server 自上次启动后读取磁盘（不是读取高速缓存）的次数；、 TOTAL_WRITE ：返回SQL Server自上次启动后写入磁盘的次数。,系统统计函数,显示自SQL SERVER启动后到当前日期和时间为止的总的磁盘读写次数（不是读取高速缓存），SQL语句如下： SELECT TOTAL_READ AS Reads, TOTAL_WRITE AS Writes, GETDATE

38、() AS As of,系统统计函数,显示自SQL SERVER启动后到当前日期和时间为止 SQL Server CPU 的工作时间，SQL语句如下： SELECT CPU_BUSY AS CPU ms, GETDATE() AS As of,3.2 子查询,在SQL语言中，当一个查询语句嵌套在另一个查询的查询条件之中时，称为子查询。使用子查询可以实现一些比较复杂的查询。,带有IN关键字的子查询,查询所有在合同明细表中，明细金额为20000元的合同，显示列：合同号、合同名称，SQL语句如下： SELECT ContractID,ContractName FROM Contract WHERE

39、ContractID IN (SELECT ContractID FROM ContractDetail WHERE Volume=20000),带有比较运算符的子查询,查询出所有合同总金额不等于合同明细表中金额之和的合同，显示合同号、合同名称，该查询可以列出所有合同总金额没有实时反应合同明细金额之和的合同，SQL语句如下： SELECT ContractID,ContractName FROM Contract WHERE ContractVolume (SELECT SUM(Volume) FROM ContractDetail),带有ANY或ALL关键字的子查询,查询出所有合同总金额大于

40、合同明细表中任何一个明细金额的合同，显示合同号、合同名称，SQL语句如下： SELECT ContractID,ContractName FROM Contract WHERE ContractVolume any (SELECT Volume FROM ContractDetail),带有EXISTS关键字的子查询,查询所有合同明细中，明细金额中有一条记录是10000元的合同，列出合同号、合同名称，SQL语句如下： SELECT ContractID,ContractName FROM Contract WHERE exists (SELECT Volume FROM ContractDet

41、ail WHERE Volume=10000 AND ContractID=Contract.ContractID),子查询的限制（P39）,查询所有合同明细中，明细金额中有一条记录是10000元的合同，可以用如下SQL语句实现： SELECT Contract.ContractID,ContractName FROM Contract,ContractDetail WHERE Contract.CompanyID=Company.CompanyID AND Volume=10000,3.3 联合查询,3.3.1 使用UNION 创建联合查询 3.3.2 联合查询的关键字ALL 3.3.3 使

42、用INNER JOIN 3.3.4 使用LEFT JOIN 和RIGHT JOIN 建立连接 3.3.5 使用交叉连接（CROSS JOIN）,3.3.1 使用UNION 创建联合查询,查询在2002年5月1日签定的合同以及在合同总金额大于10000元的合同，SQL语句如下： SELECT * FROM Contract WHERE SignDate=2002-05-01 UNION SELECT * FROM Contract WHRE ContractVolume10000,3.3.1 使用UNION 创建联合查询,查询在与“北京怡神工贸公司”签定的合同以及在合同明细金额中有一条记录是10

43、000元的合同，显示公司名称、合同号，SQL语句如下： SELECT CompanyName,ContractID FROM Contract,Company WHERE Contract.CompanyID=Company.CompanyID AND CompanyName= 北京怡神工贸公司 UNION SELECT CompanyName,Contract.ContractID FROM Contract,Company,ContractDetail WHERE Contract.CompanyID=Company.CompanyID AND Contract.ContractID=Co

44、ntractDetail.ContractID AND Volume=10000,3.3.2 联合查询的关键字ALL,查询在2002年5月1日签定的合同以及在合同总金额大于10000元的合同，如果使用了ALL关键字，SQL语句如下： SELECT * FROM Contract WHERE SignDate=2002-05-01 UNION ALL SELECT * FROM Contract WHRE ContractVolume10000,3.3.3 使用INNER JOIN,查询在合同表中存在的公司的基本信息，为此需要建立合同表与公司信息表的连接，SQL语句如下： SELECT Comp

45、any.CompanyID,CompanyName FROM Company INNER JOIN Contract ON Company.CompanyID=Contract.CompanyID,3.3.4 使用LEFT JOIN 和RIGHT JOIN 建立连接,查询所有公司的合同签定总额，不管该公司有没有签定合同，分别使用LEFT JOIN 和ROGHT JOIN ，SQL语句如下 用LEFT JOIN SELECT CompanyName,SUM(ContractVolume) FROM Company LEFT JOIN Contract ON Company.CompanyID=C

46、ontract.CompanyID GROUP BY CompanyName 用RIGHT JOIN SELECT CompanyName,SUM(ContractVolume) FROM Contract RIGHT JOIN COMPANY ON Contract.CompanyID=Company.CompanyID GROUP BY CompanyName,3.3.5 使用交叉连接（CROSS JOIN）,现有部门表（Depart）与职员表(Employee)，现在要生成一张新的表NewTable，要求在新表中包括每个部门的每个职员，部门表结构：Depart(DepartID,Depa

47、rtName)，职员表结构：Employee(EmployeeID,EmployeeName)，SQL语句如下： SELECT DepartName,EmployeeName INTO NewTable FROM Depart CROSS JOIN Employee,3.4 其他复杂应用,3.4.1 使用Compute（By）子句 3.4.2 交叉表查询,3.4.1 使用Compute（By）子句,COMPUTE BY 子句使您得以用同一 SELECT 语句既查看明细行，又查看汇总行。可以计算子组的汇总值，也可以计算整个结果集的汇总值。这对于需要是显示查询的总计与小计非常有用。COMPUTE

48、所生成的汇总值在查询结果中显示为分离的结果。,3.4.2 交叉表查询,以合同表为例，查询各公司在本年的付款金额，要求每公司用一行记录显示，每个月用一列显示，SQL语句如下： SELECT Company.CompanyName as 公司名称, SUM(CASE WHEN MONTH(PaymentDate)=1 THEN ContractPayment.Volume ELSE 0 END) as 1月, SUM(CASE WHEN MONTH(PaymentDate)=2 THEN ContractPayment.Volume ELSE 0 END) as 2月, SUM(CASE WHEN

49、 MONTH(PaymentDate)=3 THEN ContractPayment.Volume ELSE 0 END) as 3月, SUM(CASE WHEN MONTH(PaymentDate)=4 THEN ContractPayment.Volume ELSE 0 END) as 4月, SUM(CASE WHEN MONTH(PaymentDate)=5 THEN ContractPayment.Volume ELSE 0 END) as 5月, SUM(CASE WHEN MONTH(PaymentDate)=6 THEN ContractPayment.Volume ELSE

50、 0 END) as 6月, SUM(CASE WHEN MONTH(PaymentDate)=7 THEN ContractPayment.Volume ELSE 0 END) as 7月, SUM(CASE WHEN MONTH(PaymentDate)=8 THEN ContractPayment.Volume ELSE 0 END) as 8月, SUM(CASE WHEN MONTH(PaymentDate)=9 THEN ContractPayment.Volume ELSE 0 END) as 9月, SUM(CASE WHEN MONTH(PaymentDate)=10 THE

51、N ContractPayment.Volume ELSE 0 END) as 10月, SUM(CASE WHEN MONTH(PaymentDate)=11 THEN ContractPayment.Volume ELSE 0 END) as 11月, SUM(CASE WHEN MONTH(PaymentDate)=12 THEN ContractPayment.Volume ELSE 0 END) as 12月 FROM Company LEFT JOIN Contract ON Company.CompanyID=Contract.CompanyID LEFT JOIN Contra

52、ctDetail ON Contract.ContractID=ContractDetail.ContractID LEFT JOIN ContractPayment ON ContractDetail.ContractDetailID=ContractPayment.ContractDetailID WHERE YEAR(PaymentDate)=2002 GROUP BY CompanyName,3.5 查询语句的性能优化,当数据量不是很大的时候，SQL语句的优劣基本无法比较，但是当数据达到海量以后，劣质的SQL语句与优质的SQL语句的执行效率可能会相差几十倍甚至几百倍。因此提高SQL语句

53、的执行效率，也就是提高了应用程序的性能。当然，只有在实际应用中才能分出SQL语句的优劣，不过还是有一些优化经验，它可以使我们避免在新系统犯低级的错误。,3.5 查询语句的性能优化,3.5.1 通配符%的使用 3.5.2 Order by语句 3.5.3 尽量不用NOT 3.5.4 尽量不要连接列 3.5.5 表连接需要注意的地方,第四章 视图的灵活应用,4.1 视图的用途和需要 4.2 创建和管理视图 4.3 利用视图简化查询操作 4.4 更新视图中的数据 4.5 加密视图 4.6 利用视图加强数据安全,4.1 视图的用途和需要,视图作为一种基本的数据库对象，是查询一个表或多个表的一种另方法，

54、通过把预先定义的查询存储在数据库中，然后就可以在查询语句中调用它。 视图是虚拟的表，它只包含表的一部分。,4.2 创建和管理视图,4.2.1 定义视图 4.2.2 修改视图定义 4.2.3 删除视图,4.2.1 定义视图,例程4.1：创建一个视图，该视图用于显示2002年1月1日以后签定的所有合同。 CREATE VIEW View_Contract1 AS SELECT * FROM Contract WHERE SignDate2002-01-01 例程4.2：创建一个视图，用于显示各单位签定的所有合同。 CREATE VIEW View_Contract2 AS SELECT Compa

55、nyName,ContractID,ContractName FROM Contract,Company WHERE Contract.CompanyID=Company.CompanyID,4.2.1 定义视图,例程4.3：创建一个视图，用于显示各单位的付款总额。 CREATE VIEW View_Contract 3 AS SELECT CompanyName,SUM(ContractPayment.Volume) as TotalPayment FROM Company LEFT JOIN Contract ON Contract.CompanyID=Company.CompanyID

56、LEFT JOIN ContractDetail ON Contract.ContractID=ContractDetail.ContractID LEFT JOIN ContractPayment ON ContractDetail.ContractDetailID=ContractPayment.ContractDetailID GROUP BY CompanyName,4.2.2 修改视图定义,例如要更新上一节中例程一中的创建的视图View_Contract1，使其显示所有在2002年1月份签定的合同，用如下的SQL语句： ALTER VIEW View_Contract1 AS SEL

57、ECT * FROM Contract WHERE SignDate=2002-01-01 AND SignDate=2002-01-31,4.2.3 删除视图,用DROP VIEW从当前数据库中删除视图。删除视图时，将从 sysobjects、syscolumns、syscomments、sysdepends 和 sysprotects 系统表中删除视图的定义及其它有关视图的信息。 例程4.4：删除视图View_Contract1 DROP VIEW View_Contract1,4.3 利用视图简化查询操作,4.3.1 查询视图 4.3.2 使用视图关联多张表 4.3.3 使用视图提供聚合

58、值,4.3.1 查询视图,视图定义后，用户就可以象对基本表进行查询一样对视图进行查询了。 例程4.5：创建一个视图，用于显示各单位签定的所有合同，并用SQL语句查询视图。 创建视图(如果该视图已存在，先删除它) IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = View_Contract ) DROP VIEW View_Contract CREATE VIEW View_Contract AS SELECT CompanyName,ContractID,ContractName FROM

59、 Contract,Company WHERE Contract.CompanyID=Company.CompanyID 查询视图 SELECT CompanyName,ContractID,ContractName FROM View_Contract,4.3.2 使用视图关联多张表,视图可以和其他基本表一起使用，实现连接查询或嵌套查询。 例程4.6：先创建一个视图，该视图存放各公司各合同情况，然后通过该视与合同明细表联接，查询各公司合同明细情况。 建立视图 CREATE VIEW View_Contract AS SELECT CompanyName,ContractID FROM Con

60、tract,Company WHERE Contract.CompanyID=Company.CompanyID 通过视图查询： SELECT CompanyName,View_Contract.ContractID,ContractDetailID,ContractDetail.Volume FROM View_Contract,ContractDetail WHERE View_Contract.ContractID=ContractDetail.ContractID,4.3.3 使用视图提供聚合值,可以使用视图来提供聚合值，这样可以简化查询语言，建议对于常用的聚合值都采用预先定义视图的方

61、式，以此降低SQL语句的实现难度。 例程4.7：创建一个视图，用于保存各合同的付款情况，用该视图与公司信息表联接，可以查询各公司合同付款情况。 建立视图 CREATE VIEW View_Contract AS SELECT Contract.ContractID,CompanyID, SUM(ContractPayment.Volume) as TotalPayment FROM Contract LEFT JOIN ContractDetail ON Contract.ContractID=ContractDetail.ContractID LEFT JOIN ContractPaymen

62、t ON ContractDetail.ContractDetailID=ContractPayment.ContractDetailID GROUP BY Contract.ContractID,CompanyID 查询各公司的合同付款情况 SELECT CompanyName,SUM(TotalPayment) FROM Company,View_Contract WHERE Company.CompanyID=View_Contract.CompanyID GROUP BY CompanyName,4.4 更新视图中的数据,更新视图包括插入（INSERT）、删除（DELETE）和修改（U

63、PDATE）三类操作。由于视图是不实际存储数据的虚表，因此对视图的更新，最终要转换为对基本表的更新。,4.4 更新视图中的数据,例程4.8：创建一个公司信息视图，并向其中添加一条记录：公司代码0001、公司名称：上海日化三厂。 创建视图 CREATE VIEW View_Company AS SELECT * FROM Company 添加数据 INSERT INTO View_Company(CompanyID,CompanyName) VALUES (0001,上海日化三厂),4.4 更新视图中的数据,例程4.9：更新视图View_Company中的数据，重新设置公司名称为“上海日化三厂”

64、的公司代码为“8888”。 UPDATE View_Company SET CompanyID=8888 WHERE CompanyName=上海日化三厂,4.4 更新视图中的数据,例程4.10：删除视图View_Company中，公司名称为“上海日化三厂”的记录。 DELETE FROM View_Company WHERE CompanyName=上海日化三厂,4.5 加密视图,例如创建一个加密的视图：View_Company，SQL语句如下： CREATE VIEW View_Company WITH ENCRYPTION AS SELECT * FROM Company 对视图加密后，

65、因为再也看不到视图定义，所以永远不能再对视图进行修改。如果需要修改加密视图，则必须删除该视图并重新创建一个。,4.6 利用视图加强数据安全,4.6.1 限制用户的视线 4.6.2 视图权限管理 4.6.3 调用链问题初探,第五章 触发器与存储过程,变量 5.1 SQL SERVER编程逻辑 5.2 存储过程的创建与管理 5.3 触发器的创建与管理 5.4 综合实例,变量,局部变量 Declare Variable_name Datatype , Variable_name Datatype 其中Variable_name 是局部变量的名字，它必须以开始，遵循SQL Server 2000的标识

66、符和对象的命名规范； Datatype可以是系统数据类型，也可以是用户自定义数据类型。 例：declare s1 char(20) set s1=Im a student,变量,全局变量 是SQL Server 2000系统提供并赋值的变量，用户不能建立全局变量，也不能用SET语句去修改全局变量的值，它以开头，大部分全局变量的值是报告SQL Server 2000系统启动后发生的活动，通常将该值赋给局部变量处理。,5.1 SQL SERVER编程逻辑,5.1.1 流程控制语句 5.1.2 游标的使用,5.1.1 流程控制语句,IF.ELSE条件分支语句 例程5.1：如果合同号为“101”的合同总金额大于10000元，则显示“该合同金额超过1万元”，否则显示“该合同金额不足1万元”。 IF (SELECT ContractVolume FRO