SQL的数据控制及其语言

1、第三章 关系数据库数据库原理及应用安徽工程大学 计算机科学学院3.7 SQL的数据控制 把授权的决定告知系统把授权结果存入数据字典当用户提出操作请求时，根据授权情况进行检查以决定是否执行操作请求一、GRANTGRANT语句的一般格式： GRANT ,. ON TO ,. WITH GRANT OPTION;语义：将对指定操作对象的指定操作权限授予指定的用户 说明： 发出GRANT：DBA数据库对象创建者（即属主Owner）拥有该权限的用户按受权限的用户 一个或多个具体用户PUBLIC（全体用户） WITH GRANT OPTION子句WITH GRANT OPTION子句:指定：可以再授予没有

2、指定：不能传播不允许循环授权用户定义帐户（用户）建立帐户建立某一数据库用户权限类型对象权限概念操作语句权限概念操作授权对象权限的方法授权语句权限的方法例题 例1 把查询Student表权限授给用户U1 GRANT SELECT ON TABLE Student TO U1;例题（续）例2 把对Student表和Course表的全部权限授予用户U2和U3 GRANT ALL PRIVILIGES ON TABLE Student, Course TO U2, U3;例题（续）例3 把对表SC的查询权限授予所有用户 GRANT SELECT ON TABLE SC TO PUBLIC;例题（续）例

3、4 把查询Student表和修改学生学号的权限授给用户U4 GRANT UPDATE(Sno), SELECT ON TABLE Student TO U4;对属性列的授权时必须明确指出相应属性列名 例题（续） 例5 把对表SC的INSERT权限授予U5用户，并允许他再将此权限授予其他用户 GRANT INSERT ON TABLE SC TO U5 WITH GRANT OPTION;传播权限执行例5后，U5不仅拥有了对表SC的INSERT权限， 还可以传播此权限： 例6 GRANT INSERT ON TABLE SC TO U6 WITH GRANT OPTION; 同样，U6还可以将此

4、权限授予U7： 例7 GRANT INSERT ON TABLE SC TO U7; 但U7不能再传播此权限。 传播权限（续） 下表是执行了例1到例7的语句后，学生-课程数据库中的用户权限定义表 授权用户名被授权用户名数据库对象名允许的操作类型能否转授权DBAU1关系StudentSELECT不能DBAU2关系StudentALL不能DBAU2关系CourseALL不能DBAU3关系StudentALL不能DBAU3关系CourseALL不能DBAPUBLIC关系SCSELECT不能DBAU4关系StudentSELECT不能DBAU4属性列Student.SnoUPDATE不能DBAU5关系

5、SCINSERT能U5U6关系SCINSERT能U6U7关系SCINSERT不能二、REVOKE授予的权限可以由DBA或其他授权者用REVOKE语句收回REVOKE语句的一般格式为： REVOKE ,. ON FROM ,.;REVOKE（续）例8 把用户U4修改学生学号的权限收回REVOKE UPDATE(Sno)ON TABLE Student FROM U4;REVOKE（续）例10 收回所有用户对表SC的查询权限REVOKE SELECT ON TABLE SC FROM PUBLIC; REVOKE（续）例10 把用户U5对SC表的INSERT权限收回REVOKE INSERT ON

6、 TABLE SC FROM U5 CASCADE ;将用户U5的INSERT权限收回的时候必须级联（CASCADE）收回 系统只收回直接或间接从U5处获得的权限 REVOKE（续） 执行例8到例10的语句后，学生-课程数据库中的用户权限定义表授权用户名被授权用户名数据库对象名允许的操作类型能否转授权DBAU1关系StudentSELECT不能DBAU2关系StudentALL不能DBAU2关系CourseALL不能DBAU3关系StudentALL不能DBAU3关系CourseALL不能DBAU4关系StudentSELECT不能小结:SQL灵活的授权机制DBA：拥有所有对象的所有权限不同的

7、权限授予不同的用户用户：拥有自己建立的对象的全部的操作权限GRANT：授予其他用户被授权的用户“继续授权”许可：再授予所有授予出去的权力在必要时又都可用REVOKE语句收回三、创建数据库模式的权限 DBA在创建用户时实现CREATE USER语句格式 CREATE USER WITHDBA | RESOURCE | CONNECT授权与回收（续）拥有的权限可否执行的操作CREATE USERCREATE SCHEMACREATE TABLE登录数据库 执行数据查询和操纵DBA可以可以可以可以RESOURCE不可以不可以不可以不可以CONNECT不可以不可以不可以可以，但必须拥有相应权限权限与可

8、执行的操作对照表 小结数据定义命令数据查询命令数据修改命令数据控制命令语言简洁3.8 Transact SQL 语言含义：微软公司在SQL标准的基础上做了大幅度扩充，并将SQL Server使用的SQL语言称为Transact-SQL语言(T-SQL)。一、 标识符 就像每个人都要有个名字一样，在SQL Server中，每一项对象也都要有一个作为标识用的名称，这就是标识符。例如数据库名称、数据表名称、字段名称等等，这些名称统称为标识符。可以用做标识符的字符:英文字符：AZ或az，在SQL中是不用区别大小写的。数字：010，但数字不得作为标识符的第一个字符。特殊字符：_、#、$，但$不得作为标识

9、符的第一个字符。特殊语系的合法文字：例如中文文字也可以作为标识符的合法字符。标识符不能是SQL的关键词，例如：“table”、“TABLE”、“select”、“SELECT”都不能作为标识符。标识符中不能有空格符，或_、#、$之外的特殊符号。标识符的长度不得超过128个字符长度。二、注释注释有两个作用说明代码的含义，增强代码的可读性；可以把程序中暂时不用的语句注释掉，使它们暂时不被执行，等需要这些语句时，再将它们恢复。SQL Server的注释有两种：-（两个减号）：用于注释单行。/*/：用于注释多行。数据类型 名称数据类型二进制数Binary(n) Varbinary(n)字 符Char(

10、n) Varchar(n)日期和时间Datetime Smalldatetime精确数值Decimal(p,s) Numeric(p,s)近似数值Float(n) Real整 数Int Smallint Tinyint货 币Money Smallmoney文 本Text图 象Image逻 辑 位Bit时 间 印Timestamp在SQL Server中数据类型分系统类型和用户自定义类型。系统数据类型三、 常量常量是指在程序运行过程中，其值不会改变的量。字符型常量 如：abcde整型常量 如：11，70，1200等实型常量 如：3.14，3.5等日期型常量 如：6/25/83，may 110 2

11、000等货币常量 如：$1000 等四、 变量（1）变量分类局部变量（local variable，以 为变量名称开头），是由用户定义的变量，这些变量可以用来保存数值、字符串等数据全局变量（global variable,为名称头），是由系统提供及赋值，用来保存一些系统的信息。 （2）局部变量定义DECLARE VariableName Data_Type说明：一次可以声明多个变量；局部变量在声明后均初始化为NULL。例1：声明一个长度为8个字符的变量id declare id char（8） DECLARE VAR1 CHAR(20)，VAR2 INT 变量名数据类型赋值格式一： SET 变

12、量名 = 表达式说明：变量名：是除 cursor、text、ntext 或 image 外的任何类型变量的名称。表达式：是任何有效的 SQL Server 表达式例2： SET VAR1 = AHJHFDSJF SET VAR2 = 123（2）局部变量赋值赋值格式二： SELECT 变量名 = 表达式或 SELECT子句例3 SELECT VAR1 = AHJHFDSJF SELECT VAR2 = 123例4 定义变量vcity，并将pubs例库中，authors表中作者编号（ au_id）为“172-32-1176”的作者，所在城市（city）的值赋予它。 SELECT vcity =

13、select city from authors where au_id = 172-32-1176 如果 SELECT 语句返回多个值，则将返回的最后一个值赋给变量。如果 SELECT 语句没有返回行，变量将保留当前值。 （2）局部变量赋值Example:Declare a Datetime,b int -声明2个变量Set a=2001-01-01Set b=(select min(salary) from EmployeesDECLARE last_error in -声明变量 SELECT last_error = 12 -给变量赋值五、 运算符 运算符用来执行列或变量间的数学运算或值

14、的比较，SQL Server支持的运算符有：算术运算符 +，-，*，/，%（取模）比较运算符 =、=、=、(不等于)字符串连接运算符 +逻辑运算符 and（与）、 or（或） 、not（非）位运算符 按位与(&)、或（| ）、异或()、求反（）（位运算符在中已经很少用到）（）、 、 、 & | NOT AND OR运算符的优先级由高到低为：六、 函数1 字符串函数ASC II( ) 返回字符串表达式最左面字符的ASC II码值 CHAR( ) 把一个表示ASC II代码的数值转换成对应的字符 CHARINDEX( ) 返回一个子串在字符串表达式中的起始位置 PATINDEX( ) 返回一个子串

15、在字符串表达式中的起始位置，在子串中可以使用通配符%，这个函数可以用在TEXT、CHAR和VARCHAR类型的数据上 DIFFERENCE( ) 返回两个字符串的匹配程度 SOUNDEX( ) 返回两个字符串发音的匹配程度LOWER( ) 把大写字母转换成小写字母 T-SQL提供了如下用于字符或字符串的函数 字符串函数UPPER( ) 将小写字母转换成大写字母 LTRIM( ) 删除字符串的前导空格 RTRIM( ) 删除字符串的尾部空格 REPLICATE( ) 重复一个字符表达式若干次 REVERSE( ) 取字符串的逆序 SPACE( ) 产生空格字符串 STR( )将数值转换成字符串S

16、TUFF( ) 用一个子串按规定取代另一个子串 RIGHT( ) 从字符的右部取子串 SUBSTRING( ) 取子串函数 字符串函数字符与ASCII码值转换函数ASSCII( )函数能将字符串转换成ASCII值;CHAR( )函数能将ASCII值转换成字符串。字符串函数求字符串字符个数函数能返回给定字符串表达式的字符个数，其中不包含尾随空格。语法：LEN（string_expression）参数：string_expression是要计算的字符串表达式。返回值即字符的个数，为int数据类型。字符串函数取子串函数LEFT( ) 、RIGHT( )及SUBSTRING( )函数都能从字符串中取出

17、子串，只是实现方法不同。返回空格函数SPACE( )函数：返回由重复的空格组成的字符串。语法：SPACE(integer_expression)参数：integer_expression是表示空格个数的正整数。如果integer_expression为负，则返回空字符串。返回值即是空格字符串，为char数据类型。字符串函数大小写转换函数UPPER( )函数将小写字符转换为大写字符LOWER( )函数则将大写字符转换为小写字符。删除空格函数LTRIM( )和RTRIM( )函数分别用于删除字符串的左部空格和右部空格。数字转换字符函数STR( )函数：将数字数据转换成字符数据。2 数学函数ABS(

18、 ) SIN ( ) COS( ) TAN( ) COT( ) ASIN( ) ACOS( ) 绝对值函数 正弦函数 余弦函数 正切函数 余切函数 反正弦函数 反余弦函数 本章首页本节首页ATAN( ) CEILING( ) FLOOR( ) ROUND( ) DEGREES( ) RADIANS( ) EXP( ) 反正切函数 向上取整函数 向下取整函数 四舍五入函数 将弧度转换成角度 将角度转换成弧度 指数函数 LOG( ) LOG 10( ) PI( )POWER( ) SQRT( ) RAND( ) SIGN( ) 自然对数函数 以10为底的对数函数 圆周率乘方函数 平方根函数 产生一

19、个随机数 返回+1、0或-1 数学函数返回整数值函数CEILING与FLOOR函数都用于返回数值表达式的整数值，但返回的值不同。乘方运算函数POWER(数值表达式1，数值表达式2)自然指数函数语法：EXP(float表达式)求指定的float表达式的自然指数值，返回float型的值。平方根函数语法：SQRT(float表达式)求指定的float表达式的平方根，返回float型的值。数学函数产生随机数函数用于返回一个位于0和1之间的随机数。语法：RAND(整数表达式)整型表达式在这里起着产生随机数的起始值的作用。四舍五入函数语法：ROUND(数值表达式，整数)该函数将数值表达式四舍五入成整数指定

20、精度的形式。在这里，整数可以是正数或负数。正数表示要进行运算的位置在小数点后，反之要运算的位置在小数点前。3 日期和时间函数日期（date）函数涉及与日期计算有关的一些功能 命令格式DATEADD( ) 在一个日期值上加上个间隔，返回值仍是日期值 DATEDIFF( ) 计算两个日期值之间的间隔 DATENAME( ) 返回表示日期中某部分的字符串 DATEPART( ) 返回表示日期中某部分的数值 GETDATE( ) 返回系统日期和时间 日期和时间函数返回当前的系统时间函数GETDATE()：返回当前的系统时间。返回日期时间的指定部分函数DATEPART和DATENAME函数都能返回给定日

21、期的指定部分，如：年、月、日等。改变数值后的日期时间函数DATEADD函数在指定日期时间的基础上加一段时间，返回新的日期时间值。Dateadd(datepart , number, date )日期和时间函数求两日期时间之间的差值函数DATEDIFF函数：返回开始日期和结束日期在给定日期部分上的差值。datediff(datepart , startdate , enddate )4 类型转换函数CAST和CONVERT函数能将某种数据类型的表达式显式转换为另一种数据类型CAST和CONVERT提供相似的功能，但CONVERT功能更强一些。CAST ( expression AS data_t

22、ype )CONVERT (data_type(length), expression , style)Eg:七 语句1 定义语句块2 返回客户端消息语句3 条件判断语句4 循环语句5 其他语句流程控制1. BEGIN.END2. IFELSE 3. WHILE、BREAK、CONTINUE4. GOTO5. RETURN1 定义语句块BEGINEND表示一个区块，凡是在BEGIN与END之间的程序都属于用一个流程控制，通常都是与IFELSE或WHILE等一起使用，如果BEGINEND中间只有一行程序，则可以省略BEGIN与END。语法BENGINSq1_statement1Sq1_state

23、ment2 END注意：Sq1_statement：是任何有效的T-SQL语句。BEGINEND语句块允许嵌套 。BEGIN.ENDIf Exists(SELECT * from employees where empID=001)BeginDelete employees where empID=001Print 001 Employee is deleted!EndElsePrint 001 Employee not found!2 返回客户端消息语句PRINT语句的功能是将用户定语的消息返回客户端。语法：PRINT string_sper | local_variable | FUNCT

24、ION注：local_variable必须是char或varchar，或者能够隐式转换为这里的数据类型。FUNCTION是返回字符串结果的函数，必须是char或varchar，或者能够隐式转换为这些的数据类型。string_sepr是返回字符串的表达式，可包含串联的字符串和变量。消息字符串最长可达8000个字符，超过8000个的任何字符均被截断。3 条件判断语句语法IF Boolean_expression Sq1_statementsELSE sq1_ statements注：Boolean_expression是条件表达式，Sq1_statements是要执行的T-SQL语句或语句块，EL

25、SE部分可以省略。在程序中如果IF后的条件成立，则执行其后的T-SQL语句或语句块。否则，若有ELSE语句，则执行ELSE后的T-SQL语句或语句块，然后执行IF语句后的其他语句；若无ELSE语句，则执行IF语句后的其他语句。IFELSEDeclare var floatSelect var=(select sum(salary) from employees)IF var10000 If var=0 Begin set i=i+1 if i=100 if (i%2)=0Continue elseset sum=sum+i else Begin print 1到100之間的奇數和str(sum

26、) break End End5 其他语句等待语句该语句可以指定它以后的语句在某个时间间隔之后执行，或未来的某一时间执行。语法：WAITFOR DELAYtime|TIMEtime返回语句RETURN用于从过程、批处理或语句块中无条件退出，不执行位于RETURN之后的语句。无条件转移语句在程序中执行到某个地方时，可以使用GOTO语句跳到另一个使用语句标号标识的地方继续执行。语法：GOTO label注：label是指向的语句标号。RETURNCreate Procedure Check_Employee(EmpID varchar(50)ASIf Exists(select * from employees w