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

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

《SQL授予用户权限的语句.docx》由会员分享，可在线阅读，更多相关《SQL授予用户权限的语句.docx（41页珍藏版）》请在冰点文库上搜索。

SQL授予用户权限的语句

插入多行数据的语法格式为：

INSERTINTOtable_or_view[（column_list）]子查询

例3-7求出各位学生的平均成绩，把结果存放在新表AVGSCORE中。

程序清单如下：

/*首先建立新表AVGSCORE，用来存放学号和学生的平均成绩。

*/

CREATETABLEAVGSCORE

（SNOCHAR（10）,

AVGSCORESMALLINT）

Go

/*利用子查询求出SC表中各位学生的平均成绩，把结果存放在新表AVGSCORE中。

*/

INSERTINTOAVGSCORE

SELECTSNO,AVG（SCORE）

FROMSC

GROUPBYSNO

•例3-12创建把讲授C5课程的教师的工资增加100元。

程序清单如下：

/*T表（教师基本情况表）的结构为T（TNO,TN,SEX,AGE,PROF,SAL,DEPT）分别表示教师的编号，姓名，性别，年龄，职称，工资，系别。

TC表（教师授课表）的结构为TC（TNO,CNO）分别表示教师的编号，课程编号。

*/

UPDATETSETSAL=SAL+100

WHERETNOIN

（SELECTT.TNOFROMT,TC

WHERET.TNO=TC.TNOANDTC.CNO='C5'）

/*通过连接查询找到讲授C5课程的教师编号。

*/

•下面是删除一行记录的例子。

例3-14删除张益琳教师的记录。

程序清单如下：

DELETEFROMTWHERETN=’张益琳’

•例3-16删除李明同学选课的记录。

DELETEFROMSCWHERESNO=

（SELECTSNOFROMSWHERESN=’李明’）

三、数据控制语言

•数据控制语言（DCL）是用来设置或更改数据库用户或角色权限的语句，包括GRANT，DENY，REVOKE等语句。

在默认状态下，只有sysadmin，dbcreator，db_owner或db_securityadmin等人员才有权力执行数据控制语言。

3.1语句权限与角色的授予

•SQL语言使用GRANT语句为用户授予语句权限的语法格式为：

GRANT<语句权限>|<角色>[,<语句权限>|<角色>]…

TO<用户名>|<角色>|PUBLIC[,<用户名>|<角色>]…

[WITHADMINOPTION]

•其语义为：

将指定的语句权限授予指定的用户或角色。

其中：

（1）PULBIC代表数据库中的全部用户；

（2）WITHADMINOPTION为可选项，指定后则允许被授权的用户将指定的系统特权或角色再授予其他用户或角色。

•例3-17　给用户Mary和John以及WindowsNT组Corporate\BobJ授予多个语句权限。

程序清单如下：

GRANTCREATEDATABASE,CREATETABLE

TOMary,John,[Corporate\BobJ]

•例3-18为用户ZhangYiLin授予CREATETABLE的语句权限。

程序清单如下：

GRANTCREATETABLE

TOZhangYiLin

•同语句权限的授予类似，SQL语言使用GRANT语句为用户授予对象权限，其语法格式为：

GRANTALL|<对象权限>[（列名[,列名]…）][,<对象权限>]…ON<对象名>

TO<用户名>|<角色>|PUBLIC[,<用户名>|<角色>]…

[WITHADMINOPTION]

•其语义为：

将指定的操作对象的对象权限授予指定的用户或角色。

其中：

（1）ALL代表所有的对象权限。

（2）列名用于指定要授权的数据库对象的一列或多列。

如果不指定列名，被授权的用户将在数据库对象的所有列上均拥有指定的特权。

实际上，只有当授予INSERT、UPDATE权限时才需指定列名。

（3）ON子句用于指定要授予对象权限的数据库对象名，可以是基本表名、视图名等。

（4）WITHADMINOPTION为可选项，指定后则允许被授权的用户将权限再授予其他用户或角色。

•例3-19　在权限层次中授予对象权限。

首先，给所有用户授予SELECT权限，然后，将特定的权限授予用户Mary，John和Tom。

程序清单如下：

GRANTSELECT

ONs

TOpublic

GO

GRANTINSERT,UPDATE,DELETE

ONs

TOMary,John,Tom

GO

•例3-20将查询T表和修改教师职称的权限授予USER3，并允许将此权限授予其他用户。

程序清单如下：

GRANTSELECT,UPDATE（PROF）

ONTTOUSER3

WITHADMINOPTION

•上例中，USER3具有此对象权限，并可使用GRANT命令给其他用户授权，如下例，USER3将此权限授予USER4：

GRANTSELECT,UPDATE（PROF）

ONT

TOUSER4

数据库管理员可以使用REVOKE语句收回语句权限，其语法格式为：

REVOKE<语句权限>|<角色>[,<语句权限>|<角色>]…

FROM<用户名>|<角色>|PUBLIC[,<用户名>|<角色>]…

•例：

收回用户ZHANGYILIN所拥有的CREATETABLE的语句权限。

REVOKECREATETABLE

FROMZHANGYILIN

所有授予出去的权力在必要时都可以由数据库管理员和授权者收回，收回对象权限仍然使用REVOKE语句，其语法格式为：

REVOKE<对象权限>|<角色>[,<对象权限>|<角色>]…

FROM<用户名>|<角色>|PUBLIC[,<用户名>|<角色>]…

•例3-21收回用户USER1对C表的查询权限。

程序清单如下：

REVOKESELECT

ONC

FROMUSER1

•例3-22收回用户USER3查询T表和修改教师职称的权限。

程序清单如下：

REVOKESELECT,UPDATE（PROF）

ONT

FROMUSER3

•在上例中，USER3将对T表的权限授予了USER4，在收回USER3对T表的权限的同时，系统会自动收回USER4对T表的权限。

•例3-23　首先从public角色中收回SELECT权限，然后，收回用户Mary，John和Tom的特定权限。

程序清单如下：

USEpubs

GO

REVOKESELECTONsFROMpublic

GO

REVOKEINSERT,UPDATE,DELETE

ONs

FROMMary,John,Tom

•DENY语句用于拒绝给当前数据库内的用户或者角色授予权限，并防止用户或角色通过其组或角色成员继承权限。

•否定语句权限的语法形式为：

DENYALL|<语句权限>|<角色>[,<语句权限>|<角色>]…

TO<用户名>|<角色>|PUBLIC[,<用户名>|<角色>]…

•否定对象权限的语法形式为：

DENYALL|<对象权限>[（列名[,列名]…）][,<对象权限>]…ON<对象名>

TO<用户名>|<角色>|PUBLIC[,<用户名>|<角色>]…

•例3-24　首先给public角色授予SELECT权限，然后，拒绝用户Mary，John和Tom的特定权限。

程序清单如下：

USEpubs

GO

GRANTSELECT

ONs

TOpublic

GO

DENYSELECT,INSERT,UPDATE,DELETE

ONs

TOMary,John,Tom

四、系统存储过程

系统存储过程的部分示例如下：

•sp_addtype：

用于定义一个用户定义数据类型；

•sp_configure：

用于管理服务器配置选项设置；

•xp_sendmail：

用于发送电子邮件或寻呼信息；

•sp_stored_procedures：

用于返回当前数据库中的存储过程的清单；

•sp_help：

用于显示参数清单和其数据类型；

•sp_depends：

用于显示存储过程依据的对象或者依据存储过程的对象；

•sp_helptext：

用于显示存储过程的定义文本；

•sp_rename：

用于修改当前数据库中用户对象的名称。

五、变量

•变量是一种语言中必不可少的组成部分。

Transact-SQL语言中有两种形式的变量，一种是用户自己定义的局部变量，另外一种是系统提供的全局变量。

•1.局部变量

局部变量是一个能够拥有特定数据类型的对象，它的作用范围仅限制在程序内部。

局部变量被引用时要在其名称前加上标志“＠”，而且必须先用DECLARE命令定义后才可以使用。

•定义局部变量的语法形式如下：

DECLAER{@local_variabledata_type}[…n]

•其中，参数@local_variable用于指定局部变量的名称，变量名必须以符号@开头，并且局部变量名必须符合SQLServer的命名规则。

参数data_type用于设置局部变量的数据类型及其大小。

data_type可以是任何由系统提供的或用户定义的数据类型。

但是，局部变量不能是text，ntext或image数据类型。

•使用DECLARE命令声明并创建局部变量之后，会将其初始值设为NULL，如果想要设定局部变量的值，必须使用SELECT命令或者SET命令。

其语法形式为：

SET{@local_variable=expression}或者SELECT{@local_variable=expression}[,...n]

•其中，参数@local_variable是给其赋值并声明的局部变量，参数expression是任何有效的SQLServer表达式。

•例3-26　创建一个@myvar变量，然后将一个字符串值放在变量中，最后输出@myvar变量的值。

程序清单如下：

DECLARE@myvarchar（20）

select@myvar='Thisisatest'

SELECT@myvar

GO

•例3-27　通过查询给变量赋值。

程序清单如下：

USEadventureworks

GO

DECLARE@rowsint

SET@rows=（SELECTCOUNT（*）FROMhumanresources.employee）

•2.全局变量

除了局部变量之外，SQLServer系统本身还提供了一些全局变量。

全局变量是SQLServer系统内部使用的变量，其作用范围并不仅仅局限于某一程序，而是任何程序均可以随时调用。

全局变量通常存储一些SQLServer的配置设定值和统计数据。

用户可以在程序中用全局变量来测试系统的设定值或者是Transact-SQL命令执行后的状态值。

在使用全局变量时应该注意以下几点：

（1）全局变量不是由用户的程序定义的，它们是在服务器级定义的。

（2）用户只能使用预先定义的全局变量。

（3）引用全局变量时，必须以标记符“@@”开头。

（4）局部变量的名称不能与全局变量的名称相同，否则会在应用程序中出现不可预测的结果。

•例3-29　显示到当前日期和时间为止试图登录SQLServer的次数。

程序清单如下：

SELECTGETDATE（）AS'当前的时期和时间',

@@CONNECTIONSAS'试图登录的次数'

六、运算符

•运算符是一些符号，它们能够用来执行算术运算、字符串连接、赋值以及在字段、常量和变量之间进行比较。

在SQLServer2005中，运算符主要有以下六大类：

算术运算符、赋值运算符、位运算符、比较运算符、逻辑运算符和字符串串联运算符。

1．算术运算符

算术运算符可以在两个表达式上执行数学运算，这两个表达式可以是数字数据类型分类的任何数据类型。

算术运算符包括加（+）、减（－）、乘（*）、除（/）和取模（%）。

2．赋值运算符

Transact-SQL中只有一个赋值运算符，即（=）。

赋值运算符使我们能够将数据值指派给特定的对象。

另外，还可以使用赋值运算符在列标题和为列定义值的表达式之间建立关系。

3.位运算符使我们能够在整型数据或者二进制数据（image数据类型除外）之间执行位操作。

此外，在位运算符左右两侧的操作数不能同时是二进制数据。

表3-1列出了所有的位运算符及其含义。

运算符

含义

&（按位AND）

按位AND（两个操作数）

|（按位OR）

按位OR（两个操作数）

^（按位互斥OR）

按位互斥OR（两个操作数）

4．比较运算符

比较运算符亦称为关系运算符，用于比较两个表达式的大小或是否相同，其比较的结果是布尔值，即TRUE（表示表达式的结果为真）、FALSE（表示表达式的结果为假）以及UNKNOWN。

除了text，ntext或image数据类型的表达式外，比较运算符可以用于所有的表达式。

5．逻辑运算符

逻辑运算符可以把多个逻辑表达式连接起来。

逻辑运算符包括AND、OR和NOT等运算符。

逻辑运算符和比较运算符一样，返回带有TRUE或FALSE值的布尔数据类型。

三个运算符的优先级别为：

NOT，AND，OR。

6．字符串串联运算符

字符串串联运算符允许通过加号（+）进行字符串串联，这个加号即被称为字符串串联运算符。

例如对于语句SELECT’abc’+’def’，其结果为abcdef。

•在SQLServer2005中，运算符的优先等级从高到低如下所示，如果优先等级相同，则按照从左到右的顺序进行运算。

（1）括号：

（）；

（2）乘、除、求模运算符：

*，/，%；

（3）加减运算符：

＋，－；

（4）比较运算符：

=，>，<，>=，<=，<>，!

=，!

>，!

<；

（5）位运算符：

^，&，|；

（6）逻辑运算符：

NOT；

（7）逻辑运算符：

AND；

（8）逻辑运算符：

OR。

七、函数

•在Transact-SQL语言中，函数被用来执行一些特殊的运算以支持SQLServer的标准命令。

SQLServer包含多种不同的函数用以完成各种工作，每一个函数都有一个名称，在名称之后有一对小括号，如：

gettime（）。

大部分的函数在小括号中需要一个或者多个参数。

•Transact-SQL编程语言提供了四种函数：

行集函数、聚合函数、Ranking函数、标量函数。

1.行集函数

•行集函数可以在Transact-SQL语句中当作表引用。

•例3-33　通过行集函数OPENQUERY（）执行一个分布式查询，以便从服务器local中提取表department中的记录。

程序清单如下：

select*fromopenquery（local,’select*fromdepartment’）

2.聚合函数

•聚合函数用于对一组值进行计算并返回一个单一的值。

除COUNT函数之外，聚合函数忽略空值。

聚合函数经常与SELECT语句的GROUPBY子句一同使用。

仅在下列项中聚合函数允许作为表达式使用：

SELECT语句的选择列表（子查询或外部查询）；COMPUTE或COMPUTEBY子句；HAVING子句。

•计算AdventureWorksCycles的副总所用的平均休假小时数以及总的病假小时数。

对检索到的所有行，每个聚合函数都生成一个单独的汇总值。

•程序清单如下。

•USEAdventureWorks;

•GO

•SELECTAVG（VacationHours）as'Averagevacationhours',

•SUM（SickLeaveHours）as'Totalsickleavehours'

•FROMHumanResources.Employee

•WHERETitleLIKE'VicePresident%'

•Ranking函数为查询结果数据集分区中的每一行返回一个序列值。

依据此函数，一些行可能取得和其他行一样的序列值。

Transact-SQL提供以下一些Ranking函数：

RANK；DENSE_RANK；NTILE；ROW_NUMBER。

表3-4标量函数的分类

函数分类

解释

配置函数

返回当前的配置信息

游标函数

返回有关游标的信息

日期和时间函数

对日期和时间输入值进行处理

数学函数

对作为函数参数提供的输入值执行计算

元数据函数

返回有关数据库和数据库对象的信息

安全函数

返回有关用户和角色的信息

字符串函数

对字符串（char或varchar）输入值执行操作

系统函数

执行操作并返回有关SQLServer中的值、对象和设置的信息

系统统计函数

返回系统的统计信息

文本和图像函数

对文本或图像输入值或列执行操作，返回有关这些值的信息

（1）字符串函数

●字符串函数可以对二进制数据、字符串和表达式执行不同的运算，大多数字符串函数只能用于char和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函数删除字符变量中的起始空格。

Ø程序清单如下：

ØDECLARE@string_to_trimvarchar（60）

ØSET@string_to_trim='Fivespacesareatthebeginningofthisstring.'

ØSELECT'Hereisthestringwithouttheleadingspaces:

'+

ØLTRIM（@string_to_trim）

Ø例3-39　使用可选的start_location参数从addressline1列的第2个字符开始查找“shoe”

Ø程序清单如下。

ØUSEadventureworks

ØSELECTCHARINDEX（'shoe',addressline1,2）FROMperson.address

ØWHEREaddressid='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）

返回指定日期的天数

GETDATE

（）

返回当前的日期和时间

MONTH

（date）

返回指定日期的月份数

YEAR

（date）

返回指定日期的年份数

例3-42　显示在humanresources.employee表中雇用日期到当前日期间的天数。

程序清单如下。

USEadventureworks

SELECTDATEDIFF（day,hiredate,getdate（））ASdiffdays

FROMhumanresources.employee

例3-43　从GETDATE函数返回的日期中提取月份名。

程序清单如下：

SELECTDATENAME（month,getdate（））AS'MonthName'

数学函数

数学函数用于对数字表达式进行数学运算并返回运算结果。

数学函数可以对SQLServer提供的数字数据（decimal、integer、float、real、money、smallmoney、smallint和tinyint）进行处理。

例3-45　在同一表达式中使用CEILING（），FLOOR（），ROUND（）函数。

程序清单如下：

selectceiling（13.4）,floor（13.4）,round（13.4567,3）

系统函数

•系统函数用于返回有关SQLServer系统、用户、数据库和数据库对象的信息。

系统函数可以让用户在得到信息后，使用条件语句，根据返回的信息进行不同的操作。

与其他函数一样，可以在SELECT语句的SELECT和WHERE子句以及表达式中使用系统函数。

•转换函数有两个：

CONVERT和CAST。

•CAST函数允许把一个数据类型强制转换为另一种数据类型，其语法形式为：

CAST（expressionASdata_type）

•CONVERT函数允许用户把表达式从一种数据类型转换成另一种数据类型，还允许把日期转换成不同的样式，其语法形式为：

CONVERT（data_type[（length）],expression[,style]）

例3-46示例检索列表价格的第一位是3的产品的名称，并将ListPrice转换为int。

程序清单如下。

USEAdventureWorks

GO

SELECTSUBSTRING（Name,1,30）ASProductName,ListPrice

FROMProduction.Product

WHERECAST（ListPriceASint）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表的首列名称

程序清单如下。

USEadventureworks

SELECTCOL_NAME（OBJECT_ID（'person.contact'）,1）

例3-49　检查