数据库原理

数据库原理
第三章：关系数据库标准语言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_variable＝expression
/*一个set语句只能给一个变量赋值*/
用SELECT语句赋值
SELECT {@local_variable＝expression} [,…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、用户自定义函数
 内置函数大大方便了用户进行程序设计，但用户编程时常常需
要将一个或多个T－SQL语句组成子程序，以便反复调用。
 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 student
where sdept= @yx )
调用函数student_info
Use stu
select * from student_info(‘CS')
6、了解系统表sysobjects
 在sql server 中，用于描述数据库对象的信息均在系统表
中。
 例如数据库对象（表、视图、用户函数、存储过程及触
发器等）都要在系统表sysobjects登记，如果该数据对象
已经存在，再对其进行定义，则会报错。
总结
本次课学习内容
 T-SQL语言
 运算符
 常量、变量
 自定义数据类型
 自定义函数

Download Report