数据库技术与应用-课件-第7章.ppt

第七章 T-SQL在SQL Server 2005中的应用中的应用第一节 SQL 概述 SQL自从推出以来，就得到了广泛的应用，国际上很多计算机公司在它们开发和经销的数据库管理系统产品中都支持SQL的各种版本软件。目前广泛应用的，无论是像Oracle、Sybase、SQL Server、Informix这些大型的数据库管理系统，还是像PowerBuilder、Visual Foxpro、Access这些微机上常用的数据库开发系统，都支持了SQL标准。一、SQL 的发展历程二、T-SQL特点（一）（一）T-SQL的组成的组成。1.SQL语句的标准语言部分 SQL语句的标准部分，主要利用这些标准的SQL编写应用程序和脚本，利用编写好的程序和脚本可以自如地移到其他的关系型数据库管理系统中执行。2.在标准SQL语句上的扩充 Microsoft公司在标准SQL语句上增加了许多新的功能，如语句的注释、变量、运算符、函数和流程控制语句，而且还增加了可编程性和灵活性。1.一体化的特点2.T-SQL具有交互式和嵌入式两种使用方式3.高度非过程化、采用集合的操作方式4.语言简洁、易学易用5.支持三级数据模式结构（二）T-SQL的特点（一）T-SQL的语法格式约定（二）T-SQL的运算符 T-SQL所使用的运算符可以分为算术运算符、赋值运算符、按位运算符、比较运算符、逻辑运算符、字符串串联运算符和一元运算符7种。（三）函数 三、T-SQL的基础知识第二节 使用T-SQL进行数据定义（一）T-SQL语句创建数据库的语法格式CREATE DATABASE 数据库名 /设置数据库的名称 ON /定义数据库的数据文件 PRIMARY /设置主文件组,n /设置数据文件的属性,FILEGROUP 文件组名 ,n /设置次数据文件组及数据文件属性 LOG ON /定义数据库的日志文件 ,n /设置日志文件的属性一、数据库定义其中和为以下属性的组合：（NAME=逻辑文件名，/设置在SQL Server中引用时的名称 FILENAME=物理文件名 /设置文件在磁盘上存放的路径和名称,SIZE=文件初始容量 /设置文件的初始容量,MAXSIZE=文件最大容量|UNLIMITED /设置文件的最大容量,FILEGROWTH=文件增长率 /设置文件的自动增量。（二）使用T-SQL语句创建数据库实例1.创建数据库的步骤（1）在SQL Server Management Studio窗口中，单击工具栏上的“新建查询”按钮，打开查询编辑器。（2）在查询编辑器中输入要创建数据库的T-SQL语句（3）单击工具栏中的“分析”按钮，检查输入T-SQL语句的语法无错误后，单击“执行”按钮，运行结果如图所示。（二）使用T-SQL语句创建数据库实例（4）刷新“对象资源管理器”的“数据库”节点，就可看到定义的tsdgdb数据库，如图所示。二、基本表的定义（一）T-SQL语句创建基本表1.使用T-SQL语句创建基本表的语法格式CREATE TABLE 数据库名.表的所有者.表名 /设置表名（/定义列属性 /设置列约束,n /定义其他列)的语法为：列名 数据类型（长度）/设置列名和数据类型 DEFAULT 常量表达式 /定义默认值|IDENTITY（初值，增量）/定义标识列的语法为：CONSTRAINT 约束名 /设置约束名NULL|NOT NULL /设置空或非空值约束|DEFAULT /设置默认值约束|PRIMARY KEY|UNIQUE /设置主键或唯一性约束 CLUSTERED|NONCLUSTERED /设置聚簇或非聚簇索引|FOREIGN KEY（外关键字列1,n)/设置外键约束REFERENCES 参照表名（列1,n)|CHECK（逻辑表达式）/设置检查约束例：为tsdgdb数据库，创建图书表（ts）、客户表（kh）、图书订购表（tsdg），其表的结构要求为：图书表包含的字段有图书编号（tsbh）、图书名（tsm）、类别（lb）、出版社（cbs）、单价（dj）、作者（zz）、ISBN，其中图书编号为主键，图书名不能为空。客户表包含的字段有客户编号（khbh）、客户姓名（khm）、客户地址（khdz）、联系电话（lxdh），其中客户编号为主键，客户地址不能为空。图书订购表包含的字段有客户编号（khbh）、图书编号（tsbh）、订购日期（dgrq）、订购数量（dgsl），要求客户编号、图书编号分别来自客户表和图书表。创建表实例（1）在查询编辑器代码窗口输入如下创建ts表的语句：USE tsdgdb /切换当前数据库为tsdgdb GOCREATE TABLE ts（tsbh char(10)PRIMARY KEY,/设置主键约束 tsm char(20)NOT NULL,/设置非空值约束 lb char(10),cbs char(20),dj money,zz char(10),isbn char(13)）GO（2）单击工具栏的“分析”按钮，检查语法通过后，单击“执行”按钮，可以执行该SQL语句，并打开查询结果窗口，看到“命令已成功完成”执行信息，然后刷新“对象资源管理器”中的tsdgdb，会看到新建的图书表（ts），结果如图所示。（二）T-SQL语句修改基本表语法格式如下：ALTER TABLE 表名 ADD ,n /定义要添加的列，设置列约束|ADD ,n /定义要添加的列，设置列约束|DROP COLUMN 列名|CONSTRAINT 约束名,n /删除列或列约束|ALTER COLUMN 列名 /指定要修改的列名 新数据类型（新数据宽度）/设置新的数据类型 NULL|NOT NULL /设置是否为空|WITHCHECK|NOCHECK /启用或禁用约束检查CHECK|NOCHECK CONSTRAINT ALL|约束名,n /启用或禁用约束 （三）T-SQL语句删除基本表删除基本表使用命令动词DROP，其语法格式如下：DROP TABLE 基本表名三、视图的定义（一）T-SQL语句创建视图其语法格式为：CREATE VIEW （,）AS 子查询WITH CHECK OPTION例：在tsdgdb数据库，为图书表（ts）创建视图v_lb，要求通过该视图，可以查询类别为“计算机类”的所有图书信息。（1）在查询编辑器代码窗口输入如下语句：CREATE VIEW v_lbAS SELECT*FROM ts WHERE LB=计算机类（2）执行语句，看到“命令成功完成”的消息。在查询编辑器代码窗口输入如下查询视图语句，执行结果如图所示。（二）T-SQL语句修改和删除视图1.修改视图其语法格式如下：ALTER VIEW （,）AS 子查询WITH CHECK OPTION2.删除视图删除视图通常需要使用DROP VIEW语句进行，其语法格式如下：DROP VIEW 四、索引定义（二）使用T-SQL语句创建索引1.T-SQL语句创建视图的语法格式 其语法格式如下：CREATE UNIQUECLUSTEREDNONCLUSTEREDINDEX 索引名ON 表名|视图名（列名1ASC|DESC,n）例：为tsdgdb数据库的图书表（ts）创建一个唯一索引tsm_index，依据字段tsm进行升序排序。（1）在查询编辑器代码窗口输入如下语句：USE tsdgdbCREATE UNIQUE INDEX tsm_index ON ts（tsm）GO（2）执行语句，刷新“对象资源管理器”，即可看到所创建的索引。（二）使用T-SQL语句修改和删除索引1.修改索引 其语法格式如下：ALTER INDEX 索引名 ON 表名|视图名REBUILD|REORGANIZE|DISABLE2.删除索引 其语法格式如下：DROP INDEX 索引名五、存储过程定义（一）T-SQL语句创建存储过程 其语法格式为：CREATE PROCEDURE 参数 数据类型WITH RECOMPILE|ENCRYPTIONAS T-SQL语句块 （二）使用T-SQL语句执行存储过程在SQL Server中使用EXECUTE语句来执行存储过程，其语法格式如下：EXECUTE 存储过程名 参数=参量值|变量,n六、触发器定义（一）触发器的分类1.DML触发器 2.DDL触发器（二）T-SQL语句创建触发器 1.创建DML触发器语法格式如下：CREATE TRIGGER 触发器名 ON 表|视图|服务器|数据库 WITH ENCRYPTION FOR|AFTER|INSTEAD OF INSERT,UPDATE,DELETE AS T-SQL语句 2.创建DDL触发器语法格式如下：CREATE TRIGGER 触发器名 ON ALL服务器|数据库FOR|AFTER AS T-SQL语句第三节 使用T-SQL进行数据查询一、SELECT语句介绍 语法格式如下：SELECT ALL|DISTINCTTOP nPERCENT 字段列表INTO 新表名FROM WHERE GROUP BY HAVING ORDER BYALL ASC|DESC,nCOMPUTE ,nBY 列名,n二、数据查询（一）简单查询1.选择表中的若干列 SELECT语句格式如下：SELECT 字段列表 FROM 表名【例】查询客户表的客户编号（khbh）、客户姓名（khxm）、联系电话（lxdh）。USE tsdgdbGOSELECT khbh,khxm,lxdhFROM khGO2.选择表中若干行（1）通过TOP和DISTINCT关键字限制返回的行数 【例】查询图书表（ts）的前两条记录信息。SELECT top 2*FROM ts（2）查询满足条件的元组。基本的SELECT语句格式如下：SELECT 字段列表 FROM 表名 WHERE 查询条件【例】查询计算机类或管理类图书定价在20元以上的图书信息。SELECT*FROM ts WHERE lb=管理类 or lb=计算机类AND dj203.对查询结果进行排序基本语法格式如下：SELECT 字段列表FROM 表名ORDER BY 列名|列号 ASC|DESC,n【例】查询图书表中书名中有“数据库”的图书信息，并按定价降序排序。SELECT*FROM tsWHERE tsm LIKE%数据库%ORDER BY dj DESC4.汇总查询（1）使用聚集函数【例】查询图书表中计算机类图书的册数。SELECT count(*)AS 总册数FROM tsWHERE lb=计算机类（2）使用GROUP BY子句分类汇总 语法格式如下：GROUP BY 列名HAVING 筛选条件表达式其中“HAVING 筛选条件表达式”表示对生成的组筛选后，再对满足条件的组进行统计。（3）使用COMPUTE子句明细分类汇总 语法格式如下：COMPUTE ,n BY 列名,n【例】统计图书订购表中客户订购的图书总数，并显示明细信息。SELECT khbh,tsbh,dgslFROM tsdgCOMPUTE sum(dgsl)【例】统计图书订购表中每个客户订购的图书总数，并显示明细信息。SELECT khbh,tsbh,dgslFROM tsdgORDER BY khbhCOMPUTE sum(dgsl)BY khbh 其语法格式有两种：一种是ANSI语法形式，另一种是SQL Server语法形式。ANSI连接查询的语法格式如下：SELECT 目标列FROM 表名1连接类型 JOIN 表名2 JOIN 表名nON 连接条件SQL Server连接查询的语法格式如下：SELECT 目标列FROM 表名1,nWHERE 查询条件 AND|OR 连接条件n。2）删除单元格（二）连接查询1.内连接（INNER JOIN）内连接是将多个表中的共同列的值进行比较，把满足连接条件的记录横向连接起来，作为查询结果。当未指明连接类型时，默认为内连接。【例】查询订购日期在2010-5-10之后订购图书的客户姓名、图书名、订购数量。SELECT khxm,tsm,dgslFROM kh,tsdg,tsWHERE tsdg.khbh=kh.khbh AND tsdg.tsbh=ts.tsbh AND dgrq2010-5-102.外连接（OUTER JOIN）（1）左外连接（LEFT OUTER JOIN）【例】查询同一个图书编号的图书订购数量大于15册的图书编号（thbh）、图书名(tsm)和订购数量（dgsl）。SELECT ts.tsbh,tsm,dgslFROM ts LEFT JOIN tsdg ON ts.tsbh=tsdg.tsbh AND dgsl152.外连接（OUTER JOIN）（1）左外连接（LEFT OUTER JOIN）【例】查询同一个图书编号的图书订购数量大于15册的图书编号（thbh）、图书名(tsm)和订购数量（dgsl）。SELECT ts.tsbh,tsm,dgslFROM ts LEFT JOIN tsdg ON ts.tsbh=tsdg.tsbh AND dgsl15（2）右外连接（RIGHT OUTER JOIN）（3）全外连接（FULL OUTER JOIN）。3.自连接（JOIN）自连接，是使用内连接或外连接把一个表中的行与该表中另外一些行连接起来，主要用于查询比较相同的信息。【例】在查询结果集的每一行上显示每个客户订购的两种图书编号的订购数量。SELECT X.khbh,X.tsbh,X.dgsl,Y.tsbh,Y.dgslFROM tsdg X JOIN tsdg Y ON X.khbh=Y.khbhWHERE X.tsbh Y.tsbh（三）嵌套查询1.非相关子查询非相关子查询是指子查询的执行不依赖于父查询，并且每个子查询都只执行一次。2.相关子查询相关子查询指的是子查询的执行依赖于父查询，每个子查询都只执行多次。其执行方式为：首先父查询将值传递给子查询后，执行子查询，然后根据子查询的执行结果判断父查询的条件是否满足要求，如果条件为真则显示结果，否则不显示，重复直到父查询的表全部处理完。这类查询主要针对的是带有NOTEXISTS运算符的嵌套查询。【例】查询客户编号为kh0002的客户所订购图书的图书编号、图书名。SELECT tsbh,tsmFROM tsWHERE exists(SELECT*FROM tsdg WHERE ts.tsbh=tsdg.tsbh AND khbh=kh0002)（四）组合查询组合查询，就是将多个SELECT语句的查询结果再进行集合运算。SQL的组合查询运算符有UNION（并操作）、INTERSECT（交操作）、EXCEPT（差操作）3种。【例】查询订购了图书编号为ts10001，但没有订购图书编号为ts10002图书的客户的编号。解决本例题有两种方法。（1）使用EXCEPT操作，T-SQL语句如下：SELECT khbhFROM tsdg XWHERE tsbh=ts10001 EXCEPTSELECT khbhFROM tsdg WHERE tsbh=ts10002（2）使用EXISTS运算符，T-SQL语句如下：SELECT khbhFROM tsdg XWHERE tsbh=ts10001 and NOT EXISTS(SELECT*FROM tsdg Y WHERE X.khbh=Y.khbh AND tsbh=ts10002)第四节 使用T-SQL进行数据更新一、插入数据（一）使用常量插入单个元组（一）使用常量插入单个元组语法格式如下：INSERTINTO(,)VALUES(,)【例】向tsdgdb数据库的图书表（ts）插入两行数据。INSERT INTO ts(tsbh,tsm,lb,cbs,dj,zz,isbn)VALUES(ts20002,电子商务案例分析教程,管理类,电子工业出版社,29,斯林胜,9787121102486)INSERT INTO tsVALUES(ts20003,新编电子商务概论,管理类,电子工业出版社,20,孟泽云,9787121111570)语法格式如下：INSERTINTO(，)（二）使用子查询插入多个元组【例】把books数据库中的图书表数据记录插入tsdgdb数据库中ts表中。USE tsdgdbGOINSERT INTO tsSELECT *FROM books.dbo.图书表GO二、修改数据修改数据的T-SQL语句的一般格式为：UPDATE SET=，=，n WHERE【例】将将计算机类图书统一降价5元。UPDATE ts SET dj=dj-5WHERE lb=计算机类三、删除数据数据删除的语法格式如下：DELETE FROMWHERE T-SQL的扩展语句删除记录的语法格式为：DELETE FROM JOIN ON 连接条件WHERE【例】删除客户张燕的所有订购记录。DELETE tsdgFROM tsdg INNER JOIN kh ON tsdg.khbh=kh.khbhWHERE khxm=张燕或使用带有嵌套查询的DELETE语句：DELETE tsdgWHERE khbh IN(SELECT khbh FROM kh WHERE khxm=张燕)第五节 数据控制在SQL Server 2005的应用一、数据控制方法与SQL Server的数据库操作权限（一）数据控制方法1.授权定义2.存权处理3.查权操作（二）SQL Server的数据库操作权限1.对象特权对象特权是指用户访问和操作数据库中的表、视图、存储过程等对象的操作权限，类似于数据库操作语言DML的语句权限。有5个对象权限:用于表和视图的SELECT、INSERT、UPDATE、DELETE权限和用于存储过程的EXECUTE权限。2.系统权限系统权限又称为语句权限，是指用户创建或删除数据库、创建或删除用户、创建或修改数据库对象、执行数据库或事务日志备份的权限。系统权限包括CREATE DATABASE、CREATE TABLE、CREATE VIEW、CREATE DEFAULT、CREATE RULE、BACKUP DATABASE、BACKUP LOG。3.隐含权限隐含权限是SQL Server 2005系统内置权限，是不需要进行授权就可拥有的数据操作权。用户拥有的隐含特权与自己的身份有关，二、数据控制实例分析（一）对象权限的设置（一）对象权限的设置设置对象权限的语法格式如下：GRANT/REVOKE/DENY ALL|PRIVILIGES ON TO|FROM|PUBLICWITH GRANT OPTION;（二）语句权限的设置设置语句权限的语法格式如下：GRANT/REVOKE/DENY TO|FROM|PUBLICWITH GRANT OPTION【例】授予除了用户user3之外的其他所有用户创建视图的权限。GRANT CREATE VIEW TO PUBLICDENY CREATE VIEW TO user3本章结束谢谢！