数据库原理三章节关系数据库标准语言SQL.ppt

数据库原理 第三章：关系数据库标准语言SQL,授课教师：王哲,复习上节课内容,视图基础概念 管理视图 定义视图 修改视图 删除视图 操作视图数据 查询视图,本节课内容,T-SQL语言 常量、变量与数据类型 运算符与表达式 系统内置函数 用户自定义数据类型 用户定义函数,一、T-SQL语言,Transact-SQL语言主要组成部分 数据定义语言（DDL，Data Definition Language） 数据操纵语言（DML，Data Manipularion Language） 数据控制语言（DCL，Data Control Language） 一些附加的语言元素,数据定义语言（DDL）,数据定义语言是指用来定义和管理数据库以及数据库中的各种对象的语句，这些语句包括CREATE、ALTER和DROP等语句。在SQL Server 中，数据库对象包括表、视图、触发器、存储过程、规则、缺省、用户自定义的数据类型等。这些对象的创建、修改和删除等都可以通过使用CREATE、ALTER、DROP等语句来完成。,数据操纵语言（DML）,数据操纵语言是指用来查询、添加、修改和删除数据库中数据的语句，这些语句包括SELECT、INSERT、UPDATE、DELETE等。在默认情况下，只有sysadmin、dbcreator、db_owner或db_datawriter等角色的成员才有权利执行数据操纵语言。,数据控制语言（DCL）,数据控制语言（DCL）是用来设置或者更改数据库用户或角色权限的语句，这些语句包括GRANT、DENY、REVOKE等语句，在默认状态下，只有sysadmin、dbcreator、db_owner或db_securityadmin等角色的成员才有权利执行数据控制语言。,其它语言元素,这部分是微软为了用户编程方便增加的语言元素，这些语言元素包括运算符、变量、函数和流程控制语句。,运算符与表达式 变量 用户自定义数据类型 系统内置函数 用户自定义函数,1、运算符与表达式,运算符是一些符号，它们能够用来执行算术运算、字符串连接、赋值以及在字段、常量和变量之间进行比较。 在SQL Server 2005中，运算符主要有以下六大类：算术运算符、赋值运算符、位运算符、比较运算符、逻辑运算符以及字符连接运算符、赋值运算符。,1）字符串连接运算符,字符串串联运算符允许通过加号 (+) 进行字符串串联，这个加号即被称为字符串串联运算符。例如对于语句SELECT abc+def，其结果为abcdef。,例1：列出student表中记录，其中姓名和院系显示在一列。 use stu select sno, sname+sdept, sage from student go,例2：显示xs表中记录，其中姓名和院系名显示在一列，以逗号隔开。 use stu select sno, sname+，+sdept, sage from student go,2）赋值运算符,Transact-SQL 中只有一个赋值运算符，即等号（=）； 赋值运算符使我们能够将数据值指派给特定的对象。给局部变量赋值的SET和SELECT语句中使用=运算符。 另外，还可以使用赋值运算符在列标题和为列定义值的表达式之间建立关系。,2、变量,变量用于临时存放数据，变量中的数据随着程序的运行而变化。 变量定义时，必须有名字及数据类型两个属性。 变量是一种语言中必不可少的组成部分。Transact-SQL语言中有两种形式的变量，一种是用户自己定义的局部变量，另外一种是系统提供的全局变量。,1）局部变量,局部变量可以保存数据值，以供控制流语句测试以及保存由存储过程返回的数据值等。 它的作用范围仅限制在程序内部。 另外，局部变量被引用时要在其名称前加上标志“”，而且必须先用DECLARE命令定义后才可以使用。,局部变量的使用,局部变量的定义 语法格式： DECLARE local_variable datatype ,n 局部变量的赋值 用SET语句赋值： SET local_variableexpression /*一个set语句只能给一个变量赋值*/ 用SELECT语句赋值 SELECT local_variableexpression ,n,例1：创建局部变量xuehao并赋值，然后输出所有等于该值的记录。 DECLARE xuehao char(9) Set xuehao=200515001 Select sname, ssex, sdept from student where sno= xuehao Go,例2：创建局部变量sex、zym并赋值，然后输出数据表中等于变量的值的所有记录。 DECLARE sex char(2),zym char(10) Set sex=男 Set yx=CS Select sno，sname, ssex, sdept from student where sdept =yx and ssex = sex Go,例3：将学号为200515001的记录的姓名赋值给变量name。 DECLARE name char(8) Set name= (Select sname from student where sno=200518001) select name as 姓名 Go,例4：将学号为的记录的姓名赋值给变量name。 DECLARE sno char(9) set sno=200515001 Select sname as 姓名 from xs where sno= sno Go,2）全局变量,引用全局变量时，必须以标记符“”开头。 局部变量的名称不能与全局变量的名称相同。 全局变量是SQL Server系统内部使用的变量，其作用范围并不仅仅局限于某一程序，而是任何程序均可以随时调用。,3、用户自定义数据类型,对于数据库stu，数据表student和sc都有字段“学号” ，有相同的数据类型、字段长度及是否为空的属性。为了使用方便，并使含义明确，可以先定义一数据类型，来描述“学号”的类型属性，然后让数据表student和sc的 “学号”字段定义为该类型。 定义方式：调用系统存储过程 sp_addtype,语法格式： 例1：使用Sp_addtype创建数据类型sno。 Use student exec sp_addtype sno , char(9) , not null,删除用户自定义数据类型,语法格式： Sp_droptype typename= type 例1：删除数据类型sno。 Use xsbook Exec sp_droptype sno,4、系统内置函数的使用,SQL Server 2005的内置函数非常多，常用的系统内置函数: 数学函数 字符串函数 日期时间函数,1）数学函数,例1：数学函数综合应用：求绝对值。 Select abs(8.9),abs(-5.0) ,abs(0.0),abs(0) 例2：数学函数综合应用：返回半径为1英寸、高为5英寸的圆柱面积。 declare h float,r float Select h=5,r=2 Select pi()*square(r)*h as 圆柱面积,2）字符串函数,LEFT函数和RIGHT函数 函数格式： LEFT（expression1,n） 功能：返回字符串expression1从左边开始n个字符组成的字符串。如果n=0，则返回一个空字符串。 返回值数据类型：varchar 例：LEFT(abcde, 3)结果为abc。 RIGHT(abcde, 3)结果为cde,LEN函数 函数格式： LEN（expression1） 功能：返回字符串expression1中的字符个数，不包括字符串末尾的空格。 返回值数据类型：int 例：LEN(abcde )结果为5 LOWER函数和UPER函数 函数格式： LOWER（expression1） 功能：将字符串expression1中的大写字母替换为小写字母。 返回值数据类型：varchar 例：LOWER(12asABC45*%def)结果为12asabc45*%def。 UPER(12ABC45*%def)结果为12ABC45 *% DEF。,3）日期时间函数,GETDATE函数 功能：按SQL Server内部标准格式返回系统日期和时间。 返回值数据类型：datetime 例如：getdate() -结果为2007-04-13 21:51:32.390 YEAR函数 功能：返回指定日期date中年的整数。 返回值数据类型：int 例如： year(2004-3-5) -结果为2004,DATENAME函数 函数格式：DATENAME（datepart,date） 功能：返回日期date中由datepart指定的日期部分的字符串。 返回值数据类型：nvarchar 例如： datename(yy,1993-3-4) -结果为1993 datename (m,1993-3-4) -结果为03 datename (d,1993-3-4) -结果为4,DATEPART函数 函数格式：DATEPART（datepart,date） 功能：与DATENAME类似，只是返回值为整数。 返回值数据类型：int 例如： datepart(yy,1993-3-4) -结果为1993 datepart (m,1993-3-4) -结果为3 datepart (d,1993-3-4) -结果为4,5、用户自定义函数,内置函数大大方便了用户进行程序设计，但用户编程时常常需要将一个或多个TSQL语句组成子程序，以便反复调用。 SQL SERVER 2005允许用户根据需要自己定义函数。 标量函数 内嵌表值函数 多语句表值函数 关键字Create Function,例1：定义一函数，按院系统计各院系的平均年龄。 CREATE FUNCTION avg_age(sdept char(4) RETURNS int AS BEGIN declare avg int select avg =(select avg(sage) from student where sdept=sdept) Return avg END,利用select语句 Declare sdept char(4), avg int Set sdept =cs select avg=dbo.avg_age(sdept) select avg as 平均年龄 利用exec 语句 Declare sdept char(4), avg int exec avg=dbo.avg_age sdept =CS select avg as 平均年龄,调用函数,练习1：定义一函数，按学号显示院系。 CREATE FUNCTION sdept(sno char(9) RETURNS char AS BEGIN declare yx char select yx=(select sdept from student where sno=sno) Return yx END,例2：定义一函数，按根据院系名查询该专业学生的基本信息。 CREATE FUNCTION student_info(yx char(4) RETURNS table AS RETURN(select sno,sname,ssex,sage,sdept from