第三章关系数据库标准语言SQL-7

第三章：关系数据库标准语言SQL本章学习内容一．SQL概述、数据库的体系结构二．创建及管理数据库三．管理数据表四．管理表数据五．数据库单表查询六．数据库综合查询七．视图的创建及删除上次课总结本次课学习内容管理视图创建、查询、更新、编辑及删除本节课内容T-SQL语言常量、变量与数据类型运算符与表达式系统内置函数用户自定义数据类型用户定义函数一、T-SQL语言Transact-SQL语言主要组成部分数据定义语言（DDL，DataDefinitionLanguage）数据操纵语言（DML，DataManipularionLanguage）数据控制语言（DCL，DataControlLanguage）一些附加的语言元素数据定义语言（DDL）数据定义语言是指用来定义和管理数据库以及数据库中的各种对象的语句，这些语句包括CREATE、ALTER和DROP等语句。在SQLServer中，数据库对象包括表、视图、触发器、存储过程、规则、缺省、用户自定义的数据类型等。这些对象的创建、修改和删除等都可以通过使用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、运算符与表达式运算符是一些符号，它们能够用来执行算术运算、字符串连接、赋值以及在字段、常量和变量之间进行比较。在SQLServer2008中，运算符主要有以下六大类：算术运算符、赋值运算符、位运算符、比较运算符、逻辑运算符以及字符连接运算符、赋值运算符。1）字符串连接运算符字符串串联运算符允许通过加号(+)进行字符串串联，这个加号即被称为字符串串联运算符。例如对于语句SELECT‘abc’+’def’，其结果为abcdef。例1：列出student表中记录，其中姓名和院系显示在一列。usestuselectsno,sname+sdept,sagefromstudentgo例2：显示xs表中记录，其中姓名和院系名显示在一列，以逗号隔开。usestuselectsno,sname+‘，’+sdept,sagefromstudentgo2）赋值运算符Transact-SQL中只有一个赋值运算符，即等号（=）；赋值运算符使我们能够将数据值指派给特定的对象。给局部变量赋值的SET和SELECT语句中使用=运算符。另外，还可以使用赋值运算符在列标题和为列定义值的表达式之间建立关系。2、变量变量用于临时存放数据，变量中的数据随着程序的运行而变化。变量定义时，必须有名字及数据类型两个属性。变量是一种语言中必不可少的组成部分。Transact-SQL语言中有两种形式的变量，一种是用户自己定义的局部变量，另外一种是系统提供的全局变量。1）局部变量局部变量可以保存数据值，以供控制流语句测试以及保存由存储过程返回的数据值等。它的作用范围仅限制在程序内部。另外，局部变量被引用时要在其名称前加上标志“＠”，而且必须先用DECLARE命令定义后才可以使用。局部变量的使用局部变量的定义语法格式：DECLARE{@local_variabledatatype}[,…n]局部变量的赋值用SET语句赋值：SET@local_variable＝expression/*一个set语句只能给一个变量赋值*/用SELECT语句赋值SELECT{@local_variable＝expression}[,…n]例1：创建局部变量xuehao并赋值，然后输出所有等于该值的记录。DECLARE@xuehaochar(9)Set@xuehao='200515001'Selectsname,ssex,sdeptfromstudentwheresno=@xuehaoGo例2：创建局部变量@sex、@zym并赋值，然后输出数据表中等于变量的值的所有记录。DECLARE@sexchar(2),@zymchar(10)Set@sex='男'Set@yx=‘CS'Selectsno，sname,ssex,sdeptfromstudentwheresdept=@yxandssex=@sexGo例3：将学号为200515001的记录的姓名赋值给变量@name。DECLARE@namechar(8)Set@name=(Selectsnamefromstudentwheresno='200518001')select@nameas姓名Go例4：将学号为的记录的姓名赋值给变量@name。DECLARE@snochar(9)set@sno=‘200515001’Selectsnameas姓名fromxswheresno=@snoGo2）全局变量引用全局变量时，必须以标记符“@@”开头。局部变量的名称不能与全局变量的名称相同。全局变量是SQLServer系统内部使用的变量，其作用范围并不仅仅局限于某一程序，而是任何程序均可以随时调用。3、用户自定义数据类型对于数据库stu，数据表student和sc都有字段“学号”，有相同的数据类型、字段长度及是否为空的属性。为了使用方便，并使含义明确，可以先定义一数据类型，来描述“学号”的类型属性，然后让数据表student和sc的“学号”字段定义为该类型。定义方式：调用系统存储过程sp_addtype语法格式：例1：使用Sp_addtype创建数据类型sno。Usestudentexecsp_addtypesno,'char(9)‘,'notnull'删除用户自定义数据类型语法格式：Sp_droptype[@typename=]type例1：删除数据类型sno。UsexsbookExecsp_droptypesno4、系统内置函数的使用SQLServer2008的内置函数非常多，常用的系统内置函数:数学函数字符串函数日期时间函数1）数学函数例1：数学函数综合应用：求绝对值。Selectabs(8.9),abs(-5.0),abs(0.0),abs(0)例2：数学函数综合应用：返回半径为1英寸、高为5英寸的圆柱面积。declare@hfloat,@rfloatSelect@h=5,@r=2Selectpi()*square(@r)*@has'圆柱面积'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函数和UPPER函数函数格式：LOWER（expression1）功能：将字符串expression1中的大写字母替换为小写字母。返回值数据类型：varchar例：LOWER(’12asABC45*%^def’)结果为’12asabc45*%^def’。UPPER(‘12ABC45*%^def’)结果为'12ABC45*%^DEF'。3）日期时间函数GETDATE函数功能：按SQLServer内部标准格式返回系统日期和时间。返回值数据类型：datetime例如：getdate()--结果为2007-04-1321:51:32.390YEAR函数功能：返回指定日期date中年的整数。返回值数据类型：int例如：year('2004-3-5')--结果为2004DATENAME函数函数格式：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')--结果为1993datepart(m,'1993-3-4')--结果为3datepart(d,'1993-3-4')--结果为45、用户自定义函数内置函数大大方便了用户进行程序设计，但用户编程时常常需要将一个或多个T－SQL语句组成子程序，以便反复调用。SQLSERVER2008允许用户根据需要自己定义函数。标量函数内嵌表值函数多语句表值函数关键字CreateFunction例1：定义一函数，按院系统计各院系的平均年龄。CREATEFUNCTIONavg_age(@sdeptchar(4))RETURNSintASBEGINdeclare@avgintselect@avg=(selectavg(sage)fromstudentwheresdept=@sdept)Return@avgEND利用select语句Declare@sdeptchar(4),@avgintSet@sdept='cs'select@avg=dbo.avg_age(@sdept)select@avgas平均年龄利用exec语句Declare@sdeptchar(4),@avgintexec@avg=dbo.avg_age@sdept=‘CS'select@avgas平均年龄调用函数练习1：定义一函数，按学号显示院系。CREATEFUNCTIONsdept(@snochar(9))RETURNScharASBEGINdeclare@yxcharselect@yx=(selectsdeptfromstudentwheresno=@sno)Return@yxEND例2：定义一函数，根据院系名查询该专业学生的基本信息。CREATEFUNCTIONstudent_info(@yxchar(4))RETURNStableASRETURN(selectsno,sname,ssex,sage,sdeptfromstudentwheresdept=@yx)调用函数student_infoUsestuselect*fromstudent_info(‘CS')6、了解系统表sysobjects在sqlserver中，用于描述数据库对象的信息均在系统表中。例如数据库对象（表、视图、用户函数、存储过程及触发器等）都要在系统表sysobjects登记，如果该数据对象已经存在，再对其进行定义，则会报错。总结本次课学习内容T-SQL语言运算符常量、变量自定义数据类型自定义函数