第6章SQLServer关系数据库管理系统.

第六章SQLServer关系数据库管理系统一、T-SQL语言Transact-SQL（T-SQL）是微软公司在SQLServer数据库管理系统中ANSISQL-99的实现。语句功能说明CREATE创建数据库或数据库对象不同数据库对象，其CREATE语句的语法形式不同ALTER对数据库或数据库对象进行修改不同数据库对象，其ALTER语句的语法形式不同DROP删除数据库或数据库对象不同数据库对象，其DROP语句的语法形式不同数据定义语言（DDL）1、T-SQL概述语句功能说明SELECT从表或视图中检索数据是使用最频繁的SQL语句之一INSERT将数据插入到表或视图中UPDATE修改表或视图中的数据既可修改表或视图的一行数据，也可修改一组或全部数据DELETE从表或视图中删除数据可根据条件删除指定的数据连接查询：INNERJOIN,LEFTJOIN,RIGHTJOIN数据操纵语言语句功能说明GRANT授予权限可把语句许可或对象许可的权限授予其他用户和角色REVOKE收回权限与GRANT的功能相反，但不影响该用户或角色从其他角色中作为成员继承许可权限DENY收回权限，并禁止从其他角色继承许可权限功能与REVOKE相似，不同之处：除收回权限外，还禁止从其他角色继承许可权限数据控制语言2、T-SQL的语言元素(1)变量①全局变量全局变量由系统提供且预先声明，通过在名称前加两个“@”符号区别于局部变量。②局部变量局部变量由用户自己定义的，用于保存单个数据值。例如，保存运算的中间结果，作为循环变量等。1）局部变量的定义用DECLARE语句声明局部变量，所有局部变量在声明后均初始化为NULL。语法格式：DECLARE{@local_variabledata_type}[,...n]2）用SET语句赋值将DECLARE语句创建的局部变量设置为给定表达式的值。语法格式：SET@local_variable＝expression3）用SELECT语句赋值语法格式：SELECT{@local_variable=expression}[,…n][FROM….]例1创建局部变量@var1、@var2，并赋值。DECLARE@var1,@var2char(20)SET@var1＝’中国’/*一个SET语句只能给一个变量赋值*/SET@var2＝@var1+’是一个伟大的国家’Go例2使用查询给变量赋值。DECLARE@Xmnvarchar(12)SELECT@Xm=xmFROMReaderwherekh='11001'GO(2)控制语句1）BEGIN---END语法：BEGIN语句|语句块[…n]END2）IF.ELSE语法：IF条件表达式语句1|语句块1[ELSE语句2|语句块2]3）GOTO语法：标号:语句组GOTO标号4）WHILE语法：WHILE条件表达式语句|语句块5）CONTINUE和BREAK6）RETURN语法：RETURN[整数]其中的整数为过程返回的状态值，0表示过程运行成功，1-99表示过程运行失败，每个数代表一种错误代码。7）CASE表达式形式1：CASEWHEN逻辑表达式1THEN结果表达式1WHEN逻辑表达式2THEN结果表达式2…..ELSE结果表达式n+1END形式2：CASE输入表达式WHEN表达式1THEN结果表达式1WHEN表达式2THEN结果表达式2…..ELSE表达式n+1END例3列出借还表(Borrow)中长期不还书记录的卡号、书号和已借阅期限（按借1年以上、2年以上和3年以上的形式表示。）selectkh,sh,yjyqx=caseyear(getdate())-year(jsrq)when1then'借1年以上'when2then'借2年以上'when3then'借3年以上'else‘借时间太长’endfromBorrowwhereyear(getdate())-year(jsrq)0（3）其它语句1）PRINT语句语法：PRINT字符串|@局部变量|@@全局变量注意：PRINT语句只能输出字符型数据，不能输出其它类型值。例4如果“数据库原理及应用”图书的借阅人数大于100，则显示“借阅人数多”。否则显示“借阅人数少”。if(selectcount(*)fromBorrow,BookwhereBorrow.sh=Book.shandsm='数据库原理及应用')100print'借阅人数多'elseprint'借阅人数少'2)注释语句多行注释:/*注释*/单行注释:--注释3、创建存储过程（1）定义存储过程是在SQL服务器上存储的已经编译过的SQL语句组。存储过程分为三类：系统提供的存储过程、用户定义的存储过程和扩展存储过程。系统提供的存储过程：在安装SQLServer时，系统创建了很多系统存储过程。自定义的存储过程：是由用户为完成某一特定功能而编写的存储过程。扩展存储过程：是对动态链接库（DLL）函数的调用。（2）作用存储过程具有减少网络流量、增强代码的重用性和共享性、加快系统运行速度和加强安全性。（3）创建存储过程创建存储过程的语法为：CREATEPROCEDURE过程名[@参数名数据类型[=默认值][OUTPUT][,...n][WITHRECOMPILE|ENCRYPTION]ASSQL语句组（4）执行存储过程执行存储过程的语法：[EXEC[UTE]]过程名[@参数名=]参数[，@参数名=]参数例5创建一个存储过程，用于根据性别和部门对读者表查询。并利用该存储过程查询数计学院的男读者。createprocedureXbBm_Query@Xbnchar(1),--性别@Bmnvarchar(30),--部门asselect*fromReaderwherexb=@Xbandbm=@BmgoexecXbBm_Query'男','数计学院'（5）删除存储过程删除存储过程语法格式DROPPROCEDURE存储过程名4、触发器（1）定义触发器（Trigger）是用户对某一表中的数据做插入、更新和删除操作时被触发执行的一段程序，通常我们使用触发器来检查用户对表的操作是否合乎整个应用系统的需求，是否合乎商业规则以维持表内数据的完整性和正确性。一个触发器只适用于一个表，每个表做多只能有3个触发器，分别是INSERT触发器、UPDATE触发器和DELETE触发器。（2）创建触发器语法CREATETRIGGER触发器名ON表名[WITHENCRYPTION]FOR[INSERT][,][UPDATE][,][DELETE]ASSQL语句组创建触发器的语句中用到以下两个逻辑表：INSERTED表：存放由于INSERT或UPDATE语句的执行而导致要加到该触发器作用的表中去的所有新行。即把插入或更新表的新行值，在插入或更新表的同时，也将其副本存入INSERTED表中。DELETED表：存放由于DELETE或UPDATE语句的执行而导致要从被该触发器作用的表中删除的所有行。例6对Borrow关系表创建一个INSERT触发器以保证在Borrow关系表中插入的数据满足参照完整性要求。createtriggerInsert_TriggeronBorrowforinsertASif（selectcount(*)fromReader,Inserted,BookwhereReader.kh=Inserted.khandInserted.sh=Book.sh)=0rollback例7对Reader表创建一个级联删除的触发器，要求删除Reader表中记录时要把Borrow表中相应的借还记录也删除。createtriggerDelete_TriggeronReaderfordeleteasdeleteBorrowfromBorrow,DeletedwhereBorrow.kh=Deleted.kh（3）删除触发器DROPTRIGGER触发器名（4）执行触发器触发器的执行由DBMS自动完成，当用在定义了触发器的关系表中做数据更新操作时，系统自动执行相应的触发器，不需要人为执行。二、SQLServer2008安全管理1、数据库安全控制方法数据库的安全性是指保护数据库，以防止不合法的使用造成的数据泄密、更改或破坏。不合法的使用是指不具有数据操作权的用户进行了越权的数据操作。数据库管理系统通过各种防范措施以防止用户越权使用数据库。（1）用户标识与鉴别用户标识与鉴别是系统提供的最外层安全保护措施。当用户要求进入系统时，都要输入用户标识，系统进行核对后，对于合法的用户才提供机器的使用权。常用的用户标识与鉴别有以下几种方式：用输入用户名来标明用户身份。通过回答口令标识用户身份。通过回答对随机数的运算结果表明用户身份。（2）存取控制存取控制是数据库安全的一个重要保证，它确保具有数据库使用权的用户访问数据库，同时使未被授权的人员无法接近数据。①存取控制的构成存取控制机制主要包括两部分：定义用户权限，并将用户权限登记到数据字典中。系统进行权限检查，拒绝用户的非法操作。②存取控制的类别自主存取控制强制存取控制（3）视图在系统中为不同的用户定义不同的视图，通过视图把数据对象限制在一定范围内，把要保密的数据对无权存取的用户隐藏起来。（4）审计所谓审计就是把用户对数据库的所有操作自动记录下来放入审计日志中，一旦发生数据的非法存取，DBA利用审计跟踪的信息重现导致数据库现有状况的一系列事件、找出非法存取数据的人、时间和内容等。（5）数据加密对于高度敏感数据，例如财务数据、军事机密、国家机密、银行交易数据等，除以上安全性措施外，还可以采取数据加密技术。明文密文DB加密解密2、SQLServer2008的安全体系结构SQLServer2008有4道安全防线：Windows操作系统的安全防线、SqlServer的运行安全防线、SqlServer数据库的安全防线、SQLServer数据库对象的安全防线。3、SQLServer2008的安全认证模式SQLServer2008的有两种安全认证模式：Windows安全认证模式、混合认证模式。SQLServer的登录管理、用户管理、角色管理和权限管理在实验中讲。三、SQLServer2008的完整性控制1、完整性约束条件数据完整性约束可以分为表级约束、元组级约束和属性级约束。表级约束是若干元组间、关系中以及关系之间联系的数据约束；元组级约束则是元组中的字段组和字段间联系的约束；属性级约束是针对列的数据类型、取值范围、精度、排序等而制定的约束条件。根据约束条件所涉及对象的状态不同，完整性约束可分静态约束和动态约束：静态约束是数据库确定状态时，数据对象应满足的约束条件；动态约束是数据库从一种状态转变为另一种状态时，新旧值之间应满足的约束条件。（1）静态级约束①静态列级约束静态列级约束是对一个列的取值域的说明，静态列级约束包括以下几个方面：1)对数据类型的约束。对数据类型的约束，包括数据的类型、长度、单位、精度等。2)对数据格式的约束。3)对取值范围或取值集合的约束。4)对空值的约束。5)其他约束。例如，关于列的排序说明等。②静态元组级约束静态元组级约束是对元组的属性组值的限定，即规定了属性之间的值或结构的相互制约关联。③静态表级约束在一个关系的各个元组之间或者若干关系之间，常常存在各种关联或制约约束，这种约束称为静态表级约束。常见的静态表级约束有：实体完整性约束、参照完整性约束、函数依赖约束、统计约束。（2）动态级约束①动态列级约束动态列级约束是指修改列定义或修改列值时必须满足的约束条件。1)修改列定义时的约束。例如，将允许空值的列改为不允许空值时，如果该列目前已存在空值，则拒绝这种修改。2)修改列值时的约束。修改列值有时需要参照其旧值，并且新旧值之间需要满足某种约束条件。例如，职工的工龄只能增加，职工的工资不得低于原来的工资等。②动态元组级约束动态元组级约束