SQL授予用户权限的语句.docx

1、SQL授予用户权限的语句插入多行数据的语法格式为：INSERT INTO table_or_view (column_list) 子查询例3-7 求出各位学生的平均成绩，把结果存放在新表AVGSCORE中。程序清单如下：/*首先建立新表AVGSCORE，用来存放学号和学生的平均成绩。*/CREATE TABLE AVGSCORE(SNO CHAR(10),AVGSCORE SMALLINT) Go/*利用子查询求出SC表中各位学生的平均成绩，把结果存放在新表AVGSCORE中。*/INSERT INTO AVGSCORESELECT SNO,AVG(SCORE) FROM SCGROUP BY

2、 SNO 例3-12 创建把讲授C5课程的教师的工资增加100元。 程序清单如下： /*T表（教师基本情况表）的结构为T(TNO,TN,SEX,AGE,PROF,SAL,DEPT)分别表示教师的编号，姓名，性别，年龄，职称，工资，系别。TC表（教师授课表）的结构为TC(TNO,CNO)分别表示教师的编号，课程编号。*/ UPDATE T SET SAL=SAL+100 WHERE TNO IN (SELECT T.TNO FROM T,TC WHERE T.TNO=TC.TNO AND TC.CNO=C5) /*通过连接查询找到讲授C5课程的教师编号。*/ 下面是删除一行记录的例子。 例3-1

3、4 删除张益琳教师的记录。 程序清单如下： DELETE FROM T WHERE TN= 张益琳 例3-16 删除李明同学选课的记录。 DELETE FROM SC WHERE SNO= (SELECT SNO FROM S WHERE SN= 李明)三、数据控制语言 数据控制语言（DCL）是用来设置或更改数据库用户或角色权限的语句，包括GRANT，DENY，REVOKE等语句。在默认状态下，只有sysadmin，dbcreator，db_owner或db_securityadmin等人员才有权力执行数据控制语言。3.1 语句权限与角色的授予 SQL语言使用GRANT语句为用户授予语句权限的

4、语法格式为： GRANT | ,| TO |PUBLIC,| WITH ADMIN OPTION 其语义为：将指定的语句权限授予指定的用户或角色。其中：（1）PULBIC代表数据库中的全部用户；（2）WITH ADMIN OPTION为可选项，指定后则允许被授权的用户将指定的系统特权或角色再授予其他用户或角色。 例3-17给用户 Mary 和 John 以及 Windows NT 组 CorporateBobJ 授予多个语句权限。 程序清单如下： GRANT CREATE DATABASE, CREATE TABLE TO Mary, John, CorporateBobJ 例3-18 为用户

5、ZhangYiLin授予CREATE TABLE的语句权限。 程序清单如下： GRANT CREATE TABLE TO ZhangYiLin 同语句权限的授予类似，SQL语言使用GRANT语句为用户授予对象权限，其语法格式为： GRANT ALL|(列名,列名),ON TO |PUBLIC,| WITH ADMIN OPTION 其语义为：将指定的操作对象的对象权限授予指定的用户或角色。其中： （1）ALL代表所有的对象权限。 （2）列名用于指定要授权的数据库对象的一列或多列。如果不指定列名，被授权的用户将在数据库对象的所有列上均拥有指定的特权。实际上，只有当授予INSERT、UPDATE权

6、限时才需指定列名。 （3）ON子句用于指定要授予对象权限的数据库对象名，可以是基本表名、视图名等。 （4）WITH ADMIN OPTION为可选项，指定后则允许被授权的用户将权限再授予其他用户或角色。 例3-19在权限层次中授予对象权限。首先，给所有用户授予 SELECT 权限，然后，将特定的权限授予用户 Mary，John 和 Tom。 程序清单如下： GRANT SELECT ON s TO public GO GRANT INSERT, UPDATE, DELETE ON s TO Mary, John, Tom GO 例3-20 将查询T表和修改教师职称的权限授予USER3，并允许将

7、此权限授予其他用户。 程序清单如下： GRANT SELECT,UPDATE(PROF) ON T TO USER3 WITH ADMIN OPTION 上例中，USER3具有此对象权限，并可使用GRANT命令给其他用户授权，如下例，USER3将此权限授予USER4： GRANT SELECT,UPDATE(PROF) ON T TO USER4 数据库管理员可以使用REVOKE语句收回语句权限，其语法格式为： REVOKE | ,| FROM |PUBLIC,| 例：收回用户ZHANGYILIN所拥有的CREATE TABLE的语句权限。 REVOKE CREATE TABLE FROM Z

8、HANGYILIN所有授予出去的权力在必要时都可以由数据库管理员和授权者收回，收回对象权限仍然使用REVOKE语句，其语法格式为： REVOKE | ,| FROM |PUBLIC,| 例3-21 收回用户USER1对C表的查询权限。程序清单如下：REVOKE SELECT ON CFROM USER1 例3-22 收回用户USER3查询T表和修改教师职称的权限。程序清单如下：REVOKE SELECT,UPDATE(PROF)ON TFROM USER3 在上例中，USER3将对T表的权限授予了USER4，在收回USER3对T表的权限的同时，系统会自动收回USER4对T表的权限。 例3-23

9、首先从 public 角色中收回 SELECT 权限，然后，收回用户 Mary，John 和 Tom 的特定权限。程序清单如下：USE pubs GOREVOKE SELECT ON s FROM publicGOREVOKE INSERT, UPDATE, DELETE ON s FROM Mary, John, Tom DENY语句用于拒绝给当前数据库内的用户或者角色授予权限，并防止用户或角色通过其组或角色成员继承权限。 否定语句权限的语法形式为： DENY ALL| ,| TO |PUBLIC,| 否定对象权限的语法形式为： DENY ALL|(列名,列名),ON TO |PUBLIC,

10、| 例3-24首先给 public 角色授予 SELECT 权限，然后，拒绝用户 Mary，John 和 Tom 的特定权限。 程序清单如下： USE pubs GO GRANT SELECT ON s TO public GO DENY SELECT, INSERT, UPDATE, DELETE ON s TO Mary,John,Tom四、系统存储过程系统存储过程的部分示例如下： sp_addtype：用于定义一个用户定义数据类型； sp_configure：用于管理服务器配置选项设置； xp_sendmail：用于发送电子邮件或寻呼信息； sp_stored_procedures：用于

11、返回当前数据库中的存储过程的清单； sp_help：用于显示参数清单和其数据类型； sp_depends：用于显示存储过程依据的对象或者依据存储过程的对象； sp_helptext：用于显示存储过程的定义文本； sp_rename：用于修改当前数据库中用户对象的名称。五、变量 变量是一种语言中必不可少的组成部分。Transact-SQL语言中有两种形式的变量，一种是用户自己定义的局部变量，另外一种是系统提供的全局变量。 1. 局部变量 局部变量是一个能够拥有特定数据类型的对象，它的作用范围仅限制在程序内部。局部变量被引用时要在其名称前加上标志“”，而且必须先用DECLARE命令定义后才可以使用

12、。 定义局部变量的语法形式如下： DECLAER local_variable data_type n 其中，参数local_variable用于指定局部变量的名称，变量名必须以符号开头，并且局部变量名必须符合SQL Server的命名规则。参数data_type用于设置局部变量的数据类型及其大小。data_type可以是任何由系统提供的或用户定义的数据类型。但是，局部变量不能是 text，ntext 或 image 数据类型。 使用DECLARE命令声明并创建局部变量之后，会将其初始值设为NULL，如果想要设定局部变量的值，必须使用SELECT命令或者SET命令。其语法形式为： SET lo

13、cal_variable = expression 或者 SELECT local_variable = expression ,.n 其中，参数local_variable是给其赋值并声明的局部变量，参数expression是任何有效的SQL Server表达式。 例3-26创建一个myvar 变量，然后将一个字符串值放在变量中，最后输出 myvar 变量的值。 程序清单如下： DECLARE myvar char(20) select myvar = This is a test SELECT myvar GO 例3-27通过查询给变量赋值。 程序清单如下： USE adventurewo

14、rks GO DECLARE rows int SET rows = (SELECT COUNT(*) FROM humanresources.employee) 2. 全局变量 除了局部变量之外，SQL Server系统本身还提供了一些全局变量。全局变量是SQL Server系统内部使用的变量，其作用范围并不仅仅局限于某一程序，而是任何程序均可以随时调用。全局变量通常存储一些SQL Server的配置设定值和统计数据。用户可以在程序中用全局变量来测试系统的设定值或者是Transact-SQL命令执行后的状态值。在使用全局变量时应该注意以下几点： （1）全局变量不是由用户的程序定义的，它们是在

15、服务器级定义的。 （2）用户只能使用预先定义的全局变量。 （3）引用全局变量时，必须以标记符“”开头。 （4）局部变量的名称不能与全局变量的名称相同，否则会在应用程序中出现不可预测的结果。 例3-29显示到当前日期和时间为止试图登录SQL Server的次数。 程序清单如下： SELECT GETDATE( ) AS 当前的时期和时间, CONNECTIONS AS 试图登录的次数六、运算符 运算符是一些符号，它们能够用来执行算术运算、字符串连接、赋值以及在字段、常量和变量之间进行比较。在SQL Server 2005中，运算符主要有以下六大类：算术运算符、赋值运算符、位运算符、比较运算符、逻

16、辑运算符和字符串串联运算符。 1算术运算符 算术运算符可以在两个表达式上执行数学运算，这两个表达式可以是数字数据类型分类的任何数据类型。算术运算符包括加（+）、减（）、乘（*）、除（/）和取模（%）。 2赋值运算符 Transact-SQL 中只有一个赋值运算符，即（=）。赋值运算符使我们能够将数据值指派给特定的对象。另外，还可以使用赋值运算符在列标题和为列定义值的表达式之间建立关系。3. 位运算符 使我们能够在整型数据或者二进制数据（image 数据类型除外）之间执行位操作。此外，在位运算符左右两侧的操作数不能同时是二进制数据。表3-1列出了所有的位运算符及其含义。 运算符含义&（按位 AN

17、D） 按位 AND（两个操作数） |（按位 OR） 按位 OR（两个操作数） （按位互斥 OR） 按位互斥 OR（两个操作数） 4比较运算符 比较运算符亦称为关系运算符，用于比较两个表达式的大小或是否相同，其比较的结果是布尔值，即TRUE（表示表达式的结果为真）、FALSE（表示表达式的结果为假）以及UNKNOWN。除了 text，ntext 或 image 数据类型的表达式外，比较运算符可以用于所有的表达式。5逻辑运算符 逻辑运算符可以把多个逻辑表达式连接起来。逻辑运算符包括AND、OR和NOT等运算符。逻辑运算符和比较运算符一样，返回带有 TRUE 或 FALSE 值的布尔数据类型。 三个

18、运算符的优先级别为：NOT，AND，OR。6字符串串联运算符 字符串串联运算符允许通过加号（+）进行字符串串联，这个加号即被称为字符串串联运算符。例如对于语句SELECT abc+def，其结果为abcdef。 在SQL Server 2005中，运算符的优先等级从高到低如下所示，如果优先等级相同，则按照从左到右的顺序进行运算。（1）括号：（）；（2）乘、除、求模运算符：*，/，%；（3）加减运算符：，；（4）比较运算符：=，=，=，!=，!，!；（5）位运算符：，&，|；（6）逻辑运算符：NOT；（7）逻辑运算符：AND；（8）逻辑运算符：OR。 七、函数 在Transact-SQL语言中，

19、函数被用来执行一些特殊的运算以支持SQL Server的标准命令。SQL Server包含多种不同的函数用以完成各种工作，每一个函数都有一个名称，在名称之后有一对小括号，如：gettime( )。大部分的函数在小括号中需要一个或者多个参数。 Transact-SQL 编程语言提供了四种函数：行集函数、聚合函数、Ranking函数、标量函数。 1. 行集函数 行集函数可以在Transact-SQL语句中当作表引用。 例3-33通过行集函数OPENQUERY()执行一个分布式查询，以便从服务器local中提取表department中的记录。 程序清单如下： select * from openqu

20、ery(local,select * from department) 2. 聚合函数 聚合函数用于对一组值进行计算并返回一个单一的值。除COUNT 函数之外，聚合函数忽略空值。聚合函数经常与 SELECT 语句的 GROUP BY 子句一同使用。仅在下列项中聚合函数允许作为表达式使用：SELECT 语句的选择列表（子查询或外部查询）；COMPUTE 或 COMPUTE BY 子句；HAVING 子句。 计算 Adventure Works Cycles 的副总所用的平均休假小时数以及总的病假小时数。对检索到的所有行，每个聚合函数都生成一个单独的汇总值。 程序清单如下。 USE Adventu

21、reWorks; GO SELECT AVG(VacationHours)as Average vacation hours, SUM (SickLeaveHours) as Total sick leave hours FROM HumanResources.Employee WHERE Title LIKE Vice President% Ranking函数为查询结果数据集分区中的每一行返回一个序列值。依据此函数，一些行可能取得和其他行一样的序列值。Transact-SQL提供以下一些Ranking函数：RANK；DENSE_RANK；NTILE；ROW_NUMBER。 表3-4 标量函数

22、的分类 函数分类解释配置函数 返回当前的配置信息 游标函数 返回有关游标的信息 日期和时间函数 对日期和时间输入值进行处理 数学函数 对作为函数参数提供的输入值执行计算 元数据函数 返回有关数据库和数据库对象的信息 安全函数 返回有关用户和角色的信息 字符串函数 对字符串（char 或 varchar）输入值执行操作 系统函数 执行操作并返回有关SQL Server中的值、对象和设置的信息 系统统计函数 返回系统的统计信息 文本和图像函数 对文本或图像输入值或列执行操作，返回有关这些值的信息 （1）字符串函数 字符串函数可以对二进制数据、字符串和表达式执行不同的运算，大多数字符串函数只能用于c

23、har和varchar数据类型以及明确转换成char和varchar的数据类型，少数几个字符串函数也可以用于binary和varbinary数据类型。 字符串函数可以分为以下几大类： 基本字符串函数：UPPER，LOWER，SPACE，REPLICATE，STUFF，REVERSE，LTRIM，RTRIM。 字符串查找函数：CHARINDEX，PATINDEX。 长度和分析函数：DATALENGTH，SUBSTRING，RIGHT。 转换函数：ASCH，CHAR，STR，SOUNDEX，DIFFERENCE。 例3-38使用 LTRIM 函数删除字符变量中的起始空格。 程序清单如下： DECL

24、ARE string_to_trim varchar(60) SET string_to_trim = Five spaces are at the beginning of this string. SELECT Here is the string without the leading spaces: + LTRIM(string_to_trim) 例3-39使用可选的start_location参数从addressline1列的第2个字符开始查找“shoe” 程序清单如下。 USE adventureworks SELECT CHARINDEX(shoe, addressline1,2

25、) FROM person.address WHERE addressid = 5（2）日期和时间函数 函数参数功能DATEADD（datepart,number,date） 以datepart指定的方式，返回date加上number之和 DATEDIFF（datepart,date1,date2） 以datepart指定的方式，返回date2与date1之差 DATENAME（datepart,date） 返回日期date中datepart指定部分所对应的字符串 DATEPART（datepart,date） 返回日期date中datepart指定部分所对应的整数值 DAY（date） 返回

26、指定日期的天数 GETDATE（） 返回当前的日期和时间 MONTH（date） 返回指定日期的月份数 YEAR（date） 返回指定日期的年份数 例3-42显示在humanresources.employee 表中雇用日期到当前日期间的天数。程序清单如下。USE adventureworks SELECT DATEDIFF(day, hiredate, getdate() AS diffdays FROM humanresources.employee 例3-43从GETDATE函数返回的日期中提取月份名。程序清单如下：SELECT DATENAME(month, getdate() AS

27、Month Name 数学函数数学函数用于对数字表达式进行数学运算并返回运算结果。数学函数可以对SQL Server提供的数字数据（decimal、integer、float、real、money、smallmoney、smallint 和 tinyint）进行处理。例3-45在同一表达式中使用CEILING()，FLOOR()，ROUND()函数。程序清单如下：select ceiling(13.4), floor(13.4), round(13.4567,3) 系统函数 系统函数用于返回有关SQL Server系统、用户、数据库和数据库对象的信息。系统函数可以让用户在得到信息后，使用条件语

28、句，根据返回的信息进行不同的操作。与其他函数一样，可以在SELECT语句的SELECT和WHERE子句以及表达式中使用系统函数。 转换函数有两个：CONVERT和CAST。 CAST函数允许把一个数据类型强制转换为另一种数据类型，其语法形式为： CAST( expression AS data_type ) CONVERT函数允许用户把表达式从一种数据类型转换成另一种数据类型，还允许把日期转换成不同的样式，其语法形式为： CONVERT (data_type(length),expression ,style) 例3-46 示例检索列表价格的第一位是 3 的产品的名称，并将ListPrice转

29、换为 int。程序清单如下。USE AdventureWorks GOSELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice FROM Production.Product WHERE CAST(ListPrice AS int) LIKE 3%GO例3-47用style 参数将当前日期转换为不同格式的字符串。程序清单如下：SELECT 101=CONVERT(char, GETDATE(), 101),1=CONVERT(char, GETDATE(), 1),112=CONVERT(char, GETDATE(), 112)例3-48从adventureworks数据库中返回person.contact表的首列名称程序清单如下。USE adventureworks SELECT COL_NAME(OBJECT_ID(person.contact), 1) 例3-49检查