SQL的数据控制及其语言.ppt

第三章关系数据库 数据库原理及应用 安徽工程大学计算机科学学院 3 7SQL的数据控制 把授权的决定告知系统把授权结果存入数据字典当用户提出操作请求时 根据授权情况进行检查以决定是否执行操作请求 一 GRANTGRANT语句的一般格式 GRANT ON TO WITHGRANTOPTION 语义 将对指定操作对象的指定操作权限授予指定的用户 说明 发出GRANT DBA数据库对象创建者 即属主Owner 拥有该权限的用户按受权限的用户一个或多个具体用户PUBLIC 全体用户 WITHGRANTOPTION子句 WITHGRANTOPTION子句 指定 可以再授予没有指定 不能传播不允许循环授权 用户 定义帐户 用户 建立帐户建立某一数据库用户 权限类型 对象权限概念操作语句权限概念操作授权对象权限的方法授权语句权限的方法 例题 例1 把查询Student表权限授给用户U1GRANTSELECTONTABLEStudentTOU1 例题 续 例2 把对Student表和Course表的全部权限授予用户U2和U3GRANTALLPRIVILIGESONTABLEStudent CourseTOU2 U3 例题 续 例3 把对表SC的查询权限授予所有用户GRANTSELECTONTABLESCTOPUBLIC 例题 续 例4 把查询Student表和修改学生学号的权限授给用户U4GRANTUPDATE Sno SELECTONTABLEStudentTOU4 对属性列的授权时必须明确指出相应属性列名 例题 续 例5 把对表SC的INSERT权限授予U5用户 并允许他再将此权限授予其他用户GRANTINSERTONTABLESCTOU5WITHGRANTOPTION 传播权限 执行例5后 U5不仅拥有了对表SC的INSERT权限 还可以传播此权限 例6 GRANTINSERTONTABLESCTOU6WITHGRANTOPTION 同样 U6还可以将此权限授予U7 例7 GRANTINSERTONTABLESCTOU7 但U7不能再传播此权限 传播权限 续 下表是执行了 例1 到 例7 的语句后 学生 课程数据库中的用户权限定义表 二 REVOKE授予的权限可以由DBA或其他授权者用REVOKE语句收回REVOKE语句的一般格式为 REVOKE ON FROM REVOKE 续 例8 把用户U4修改学生学号的权限收回REVOKEUPDATE Sno ONTABLEStudentFROMU4 REVOKE 续 例10 收回所有用户对表SC的查询权限REVOKESELECTONTABLESCFROMPUBLIC REVOKE 续 例10 把用户U5对SC表的INSERT权限收回REVOKEINSERTONTABLESCFROMU5CASCADE 将用户U5的INSERT权限收回的时候必须级联 CASCADE 收回系统只收回直接或间接从U5处获得的权限 REVOKE 续 执行 例8 到 例10 的语句后 学生 课程数据库中的用户权限定义表 小结 SQL灵活的授权机制 DBA 拥有所有对象的所有权限不同的权限授予不同的用户用户 拥有自己建立的对象的全部的操作权限GRANT 授予其他用户被授权的用户 继续授权 许可 再授予所有授予出去的权力在必要时又都可用REVOKE语句收回 三 创建数据库模式的权限DBA在创建用户时实现CREATEUSER语句格式CREATEUSER WITH DBA RESOURCE CONNECT 授权与回收 续 权限与可执行的操作对照表 小结 数据定义命令数据查询命令数据修改命令数据控制命令语言简洁 3 8TransactSQL语言 含义 微软公司在SQL标准的基础上做了大幅度扩充 并将SQLServer使用的SQL语言称为Transact SQL语言 T SQL 一 标识符 就像每个人都要有个名字一样 在SQLServer中 每一项对象也都要有一个作为标识用的名称 这就是标识符 例如数据库名称 数据表名称 字段名称等等 这些名称统称为标识符 可以用做标识符的字符 英文字符 A Z或a z 在SQL中是不用区别大小写的 数字 0 10 但数字不得作为标识符的第一个字符 特殊字符 但 不得作为标识符的第一个字符 特殊语系的合法文字 例如中文文字也可以作为标识符的合法字符 标识符不能是SQL的关键词 例如 table TABLE select SELECT 都不能作为标识符 标识符中不能有空格符 或 之外的特殊符号 标识符的长度不得超过128个字符长度 二 注释 注释有两个作用说明代码的含义 增强代码的可读性 可以把程序中暂时不用的语句注释掉 使它们暂时不被执行 等需要这些语句时 再将它们恢复 SQLServer的注释有两种 两个减号 用于注释单行 用于注释多行 数据类型 在SQLServer中数据类型分系统类型和用户自定义类型 系统数据类型 三 常量 常量是指在程序运行过程中 其值不会改变的量 字符型常量如 abcde 整型常量如 11 70 1200等实型常量如 3 14 3 5等日期型常量如 6 25 83 may1102000等货币常量如 1000等 四 变量 1 变量分类局部变量 localvariable 以 为变量名称开头 是由用户定义的变量 这些变量可以用来保存数值 字符串等数据全局变量 globalvariable 为名称头 是由系统提供及赋值 用来保存一些系统的信息 2 局部变量定义DECLARE VariableNameData Type说明 一次可以声明多个变量 局部变量在声明后均初始化为NULL 例1 声明一个长度为8个字符的变量iddeclare idchar 8 DECLARE VAR1CHAR 20 VAR2INT 变量名 数据类型 赋值格式一 SET变量名 表达式说明 变量名 是除cursor text ntext或image外的任何类型变量的名称 表达式 是任何有效的SQLServer表达式例2 SET VAR1 AHJHFDSJF SET VAR2 123 2 局部变量赋值 赋值格式二 SELECT变量名 表达式或SELECT子句例3SELECT VAR1 AHJHFDSJF SELECT VAR2 123例4定义变量 vcity 并将pubs例库中 authors表中作者编号 au id 为 172 32 1176 的作者 所在城市 city 的值赋予它 SELECT vcity selectcityfromauthorswhereau id 172 32 1176 如果SELECT语句返回多个值 则将返回的最后一个值赋给变量 如果SELECT语句没有返回行 变量将保留当前值 2 局部变量赋值 Example Declare aDatetime bint 声明2个变量Set a 2001 01 01 Set b selectmin salary fromEmployeesDECLARE last errorin 声明变量SELECT last error 12 给变量赋值 五 运算符 运算符用来执行列或变量间的数学运算或值的比较 SQLServer支持的运算符有 算术运算符 取模 比较运算符 不等于 字符串连接运算符 逻辑运算符and 与 or 或 not 非 位运算符按位与 或 异或 求反 位运算符在 中已经很少用到 NOTANDOR 运算符的优先级由高到低为 六 函数1字符串函数 T SQL提供了如下用于字符或字符串的函数 字符串函数 字符串函数 字符与ASCII码值转换函数ASSCII 函数能将字符串转换成ASCII值 CHAR 函数能将ASCII值转换成字符串 字符串函数 求字符串字符个数函数能返回给定字符串表达式的字符个数 其中不包含尾随空格 语法 LEN string expression 参数 string expression是要计算的字符串表达式 返回值即字符的个数 为int数据类型 字符串函数 取子串函数LEFT RIGHT 及SUBSTRING 函数都能从字符串中取出子串 只是实现方法不同 返回空格函数SPACE 函数 返回由重复的空格组成的字符串 语法 SPACE integer expression 参数 integer expression是表示空格个数的正整数 如果integer expression为负 则返回空字符串 返回值即是空格字符串 为char数据类型 字符串函数 大小写转换函数UPPER 函数将小写字符转换为大写字符LOWER 函数则将大写字符转换为小写字符 删除空格函数LTRIM 和RTRIM 函数分别用于删除字符串的左部空格和右部空格 数字转换字符函数STR 函数 将数字数据转换成字符数据 2数学函数 本章首页 本节首页 数学函数 返回整数值函数CEILING与FLOOR函数都用于返回数值表达式的整数值 但返回的值不同 乘方运算函数POWER 数值表达式1 数值表达式2 自然指数函数语法 EXP float表达式 求指定的float表达式的自然指数值 返回float型的值 平方根函数语法 SQRT float表达式 求指定的float表达式的平方根 返回float型的值 数学函数 产生随机数函数用于返回一个位于0和1之间的随机数 语法 RAND 整数表达式 整型表达式在这里起着产生随机数的起始值的作用 四舍五入函数语法 ROUND 数值表达式 整数 该函数将数值表达式四舍五入成整数指定精度的形式 在这里 整数可以是正数或负数 正数表示要进行运算的位置在小数点后 反之要运算的位置在小数点前 3日期和时间函数 日期 date 函数涉及与日期计算有关的一些功能 日期和时间函数 返回当前的系统时间函数GETDATE 返回当前的系统时间 返回日期时间的指定部分函数DATEPART和DATENAME函数都能返回给定日期的指定部分 如 年 月 日等 改变数值后的日期时间函数DATEADD函数在指定日期时间的基础上加一段时间 返回新的日期时间值 Dateadd datepart number date 日期和时间函数 求两日期时间之间的差值函数DATEDIFF函数 返回开始日期和结束日期在给定日期部分上的差值 datediff datepart startdate enddate 4类型转换函数 CAST和CONVERT函数能将某种数据类型的表达式显式转换为另一种数据类型CAST和CONVERT提供相似的功能 但CONVERT功能更强一些 CAST expressionASdata type CONVERT data type length expression style Eg 七语句 1定义语句块2返回客户端消息语句3条件判断语句4循环语句5其他语句 流程控制 1 BEGIN END2 IF ELSE3 WHILE BREAK CONTINUE4 GOTO5 RETURN 1定义语句块 BEGIN END表示一个区块 凡是在BEGIN与END之间的程序都属于用一个流程控制 通常都是与IF ELSE或WHILE等一起使用 如果BEGIN END中间只有一行程序 则可以省略BEGIN与END 语法BENGINSq1 statement1Sq1 statement2 END注意 Sq1 statement 是任何有效的T SQL语句 BEGIN END语句块允许嵌套 BEGIN END IfExists SELECT fromemployeeswhereempID 001 BeginDeleteemployeeswhereempID 001 Print 001Employeeisdeleted EndElsePrint 001Employeenotfound 2返回客户端消息语句 PRINT语句的功能是将用户定语的消息返回客户端 语法 PRINTstring sper local variable FUNCTION注 local variable必须是char或varchar 或者能够隐式转换为这里的数据类型 FUNCTION是返回字符串结果的函数 必须是char或varchar 或者能够隐式转换为这些的数据类型 string sepr是返回字符串的表达式 可包含串联的字符串和变量 消息字符串最长可达8000个字符 超过8000个的任何字符均被截断 3条件判断语句 语法IFBoolean expressionSq1 statementsELSEsq1 statements注 Boolean expression是条件表达式 Sq1 statements是要执行的T SQL语句或语句块 ELSE部分可以省略 在程序中如果IF后的条件成立 则执行其后的T SQL语句或语句块 否则 若有ELSE语句 则执行ELSE后的T SQL语句或语句块 然后执行IF语句后的其他语句 若无ELSE语句 则执行IF语句后的其他语句 IF ELSE Declare varfloatSelect var selectsum salary fromemployees IF var 10000If var 20000Print 工资总和在10000到20000之间 elseprint 工资总和大于20000 Elseprint 工资总和小于10000 4循环语句 WHILE语句用来处理循环 在条件为TRUE的时候 重复执行一条或一个包含多条T SQL语句的语句块 直到条件表达式为FALSE时退出循环体 执行循环体外的语句 语法如下 WHILEBoolean expressionSq1 statement注 与IF ELSE语句一样 WHILE语句只能执行一条SQL语句 如果执行多条语句 就必须使用BEGIN END把多条语句定义成语句块 若要中途离开循环的话 可使用BREAK或CONTINUE语句 BREAK可以跳出目前执行的循环 若有好几层循环 表示跳出现在的这一层循环 回到上一层循环 若使用CONTINUE 表示循环执行到此 立刻回到循环的开头继续做WHILE判断 重新开始循环 WHILE BREAK CONTINUE Declare iint sumintSet i 0 sum 0While i 0Beginset i i 1if i 100if i 2 0Continueelseset sum sum ielseBeginprint 1到100之間的奇數和 str sum breakEndEnd 5其他语句 等待语句该语句可以指定它以后的语句在某个时间间隔之后执行 或未来的某一时间执行 语法 WAITFOR DELAY time TIME time 返回语句RETURN用于从过程 批处理或语句块中无条件退出 不执行位于RETURN之后的语句 无条件转移语句在程序中执行到某个地方时 可以使用GOTO语句跳到另一个使用语句标号标识的地方继续执行 语法 GOTOlabel注 label是指向的语句标号 RETURN CreateProcedureCheck Employee EmpIDvarchar 50 ASIfExists select fromemployeeswhereEmpID EmpID Return1ElseReturn 100 GOTO Declare sint timesintSet s 1 times 1Label1 set s s time