Sql语言程序设计教程.docx
《Sql语言程序设计教程.docx》由会员分享,可在线阅读,更多相关《Sql语言程序设计教程.docx(44页珍藏版)》请在冰点文库上搜索。
Sql语言程序设计教程
第一章Sql语言程序设计基础
第一节数据类型
Sqlsever的数据类型
数据类型
范围
通常存储
Tinyint
0~255(28-1)(一个字节)
整型数据
Smalint
-215~215-1(两个字节)
整型数据
Int
-231~231(四个字节)
整型数据
Bigint
-263~263-1(个个字节)
整型数据
Real
-3.40E+38~3.40E+38
近似浮点精度数据
Float
-1.79E+308~1.79E+308
近似浮点精度数据
decimal(numeric)
精确小数数据
Money
-263~263-1
货币数据
smallmoney
-263~263-1
货币数据
Datetime
1753.1.1~9999.12.31
日期和时间数据
smalldatetime
1900.1.1~2079.6.6
日期和时间数据
Char(n)
N可以是1~8000
不定长字符数据
Nchar
最大长度是4000字节
定长Unicode数据
Varchar(n)
N可以是1~8000
可变长字符数据
Nvarchar
最大长度是4000字节
可变长Unicode数据
Text
最大长度是231-1字符
字符串数据
Ntext
最大长度是230-1字符
可变长Unicode数据
Bit
0或1(一个二进制位)
0或1的数据
Image
最大长度是231-4字节(4个字节)
存储图像二进制数据
Binary
最大长度是8000字节
定长二进制数据
Varbinary
最大长度是8000字节
可变长二进制数据
Sql_variant
最大长度是8016字节
Timestamp
最大存储大小是8字节
uniqueidentifier
分配16字节空间
该数据一般由newid()函数生成
一、整型数据数据类型
整型数据常量可以用十进制直接表示,也可以以0X开始的十六进制数表示,例如25、0x15。
二、小数数据类型
Real和flaot数据类型为近似浮点数据类型,decimal(numeric)为精确小数数据类型
Decimal(P,s)
P为小数点左边和右边数字之和,不包括小数点。
如123.45,则p=5,s=2。
指定精度或对象能够控制的数字个数。
s为可放到小数点右边的小数位数或数字个数。
p和s必须遵守以下规则:
0<=s<=p<=38。
对于Real和flaot数据类型的数据不能用于等于和不等于判断。
float和real常量使用科学记数法来表示。
三、字符串数据类型
字符串两边必须加单引号
Char(n)、varchar(n)、text和nChar(n)、nvarchar(n)、ntext数据类型的区别:
无n字母数据类型存储的为非unicode,即与输入法的半角相似,如果内容非汉字时,最好用无n字母数据类型。
Char、varchar数据类型的区别:
char(n)为定长字符串数据类型,即如果输入的内容不足n字符时,用空格补齐,用其定义的变量或字段存储空间为n个字节。
varchar(n)为变长数据类型,即n为输入字符个数的最大值,很相当于数据结构的链表结构,用其定义的变量或字段存储空间根据输入的内容的多少决定,输入多少个字符就有多大的空间,但最大不能超过n个字节空间。
Text数据类型定义的变量或字段用于存储内容很多,比如文章内容。
四、日期时间数据类型
日期时间数据两边必须加单引号,格式为“yyyy-mm-dd”
五、货币数据
货币数据是小数位保留四位的小数,超过四舍五入。
六、uniqueidentifier数据类型
该数据显示形式为xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx,其中每个x是0-9或a-f范围内的一个十六进制的数字,例如:
6F9619FF-8B86-D011-B42D-00C04FC964FF即为有效的uniqueidentifier值,允许对uniqueidentifier值执行的操作只有比较(=,<>,<,>,<=,>=)和检查NULL(ISNULL和ISNOTNULL)。
不允许使用其它算术运算符。
该值的使用与全局唯一标识符(GUID)一样。
GUID是一个唯一的二进制数字;世界上的任何两台计算机都不会生成重复的GUID值。
GUID主要用于在拥有多个节点、多台计算机的网络中,分配必须具有唯一性的标识符。
GUID数值是通过API内部函数获得。
Transact-SQLNEWID函数以及应用程序API函数和方法从它们网卡上的标识数字以及CPU时钟的唯一数字生成新的uniqueidentifier值。
每个网卡都有唯一的标识号。
由NEWID返回的uniqueidentifier使用服务器上的网卡生成。
由应用程序API函数和方法返回的uniqueidentifier使用客户机上的网卡生成。
uniqueidentifier常量的方法有两种格式:
字符串格式:
'6F9619FF-8B86-D011-B42D-00C04FC964FF'
二进制格式:
0xff19966f868b11d0b42d00c04fc964ff
第二节变量
一、局部变量
局部变量是用户可自定义的变量,局部变量是有作用域的,它的作用域是定义它的批处理或者过程。
在一个批处理中是不能引用其他批处理中定义的局部变量的。
。
在程序中通常用来储存从表中查询到的数据,或当作程序执行过程中暂存变量使用。
局部变量必须以“@”开头(是局部变量的标识符),而且必须先用DECLARE命令说明后才可使用(即定义变量)。
用户定义的变量不能定义为Text、Ntext、Image数据类型。
二、全局变量
全局变量是SQLServer系统内部使用的变量,其作用范围并不局限于某一程序,而是任何程序均可随时调用全局变量通常存储一些SQLServer的配置设定值和效能统计数据。
用户可在程序中用全局变量来测试系统的设定值或Transact-SQL命令执行后的状态值。
注意:
全局变量不是由用户的程序定义的,它们是在服务器级定应义的。
引用全局变量时,必须以“@@”开头。
局部变量的名称不能与全局变量的名称相同、否则会在应用中出错。
全局变量名称
功能
@@ERROR
最后一个T-SQL错误的错误号
@@IDENTITY
最后一个插入的标识值
@@LANGUAGE
当前使用语言的名称
@@MAX_CONNECTIONS
可以创建的同时链接的最大数目
@@ROWCOUNT
受上一个SQL语言影响的行数
@@SERVERNAME
本地服务器的名称
@@SERVICENAME
该计算机上的SQL服务的名称
@@TIMETICKS
当前计算机上每刻度的微秒数
@@TRANSCOUNT
当前连接打开的事务数
@@VERSION
SQLServer的版本信息
@@DATEFIRST
返回一周第一天的星期数
三、定义变量数据类型
DECLARE@变量名变量类型[,@变量名变量类型…]
第三节Sql基本语句
一、赋值语句
set
select
同时对多个变量同时赋值
不支持
支持
表达式返回多个值时
出错
将返回的最后一个值赋给变量
表达式未返回值
变量被赋null值
变量保持原值
SELECT@局部变量=变量值[,@局部变量=变量值····]
SET@局部变量=变量值
二、输出语句
print字符串常量|@变量名
向客户端返回用户定义消息,返回信息出现在消息选项卡内。
select@变量名
返回信息出现在表格选项卡内。
三、注释语句
可以在批量或过程中使用注解语句以书写代码的描述。
多行注解语句在/*和*/中。
单行注解语句以—(两个连字符)开始。
例如1:
/*注解语句1,
注解语句2。
*/
例如2:
--注解语句。
四、Go语句
不是Transact-SQL语句;而是可为SQLServer查询分析器识别的命令。
GO向SQLServer实用工具发出一批Transact-SQL语句结束的信号。
go是把t-sql語句分批次執行。
(一步成功了才会执行下一步,即一步一个GO)
Go语句可以限制局部(用户定义)变量的作用域范围
例如:
Declare@aa
Set@aa=’abc’
Go
Print@aa
执行以上批处理语句,出现报错信息“@aa变量未声明”,因@aa变量的作用域在Go语句之前。
五、Use语句
改变当前操作数据库
例如:
UsePubs
将当前操作数据库切换为Pubs。
USE在存储过程中不能直接使用,但可以这样用:
createproctest
as
exec('usepubsselect*fromjobs')
go
--使用Exec(ute)语句配合USE来改变当前数据库,但数据库环境的更改只在Execute语句结束前有效,所以必须把后面的语句和Execute放到一起
createproctest
as
exec('usepubs')
select*fromjobs
go
执行exec('usepubs')语句后,当前数据库又恢复到原来的数据库。
所以以上语句会报错。
但也可以以下写:
createproctest
as
select*frompubs.dbo.jobs
go
在引用其它数据库中数据表:
数据库名.dbo.数据表名
六、Begin…End语句
将多条语句逻辑上作为一个语句。
相当于C语言的花括号。
Begin
多条语句
End
七、return语句
return语句用于无条件地终止一个查询、存储过程或者批处理,此时位于return之后的程序将不会被执行。
return语句的语法形式为:
return 整数表达式
通常,存储过程使用返回代码表示存储过程执行的成功或失败。
无错误,则返回0,否则,返回非零值。
例如:
print12
return
print13
以上语句是输出12,不输出13。
八、.waitfor语句
waitfor语句用于暂时停止执行SQL语句、语句块或者存储过程等,直到所设定的时间已过或者所设定的时间已到才继续执行waitfor语句之后的语句。
语法:
waitfor delay时间间隔
例如:
Waitfordelay’00:
00:
5’
等待5秒钟。
其中:
时间间隔指定执行waitfor语句之前需要等待事件,最多为24小时。
也可写成:
waitfor time时间值
例如:
Waitfortime’11:
50:
20’
等待到上午11:
50:
20。
其中,时间值指定waitfor语句将要执行的时间。
第四节Sql编程语句
一、条件语句
ifelse语句是条件判断语句,最简单的if语句没有else子句部分。
ifelse语句用来判断当某一条件成立时执行某段程序,条件不成立时执行另一段程序。
SQLServer允许嵌套使用ifelse语句,而且嵌套层数没有限制。
if boolean_expression
{sql_statement|statement_block}
[else
{sql_statement|statement_block}]
boolean_expression是布尔表达式,如果表达式为True,则执行语句块1,如果表达式为False,则执行语句块2。
例如:
If‘张纬’in(select姓名from学生表where班次=’001’)
Print“张纬在001班”
Else
Print“张纬不在001班”
还可以这样写:
IfExists(select姓名from学生表where班次=’001’and姓名=’张纬’)
Print“张纬在001班”
Else
Print“张纬不在001班”
二、循环语句
while语句是SQL语句中的循环语句。
只要指定的条件为真,就重复执行语句,指定条件为假,就退出循环。
其中,continue语句可以使程序跳过continue语句后面的语句,回到while循环的第一行命令。
break语句则使程序完全跳出循环,结束while语句的执行。
总之,Continue、Break语句都相当于C语言中的Continue、Break语句
注意:
如果select语句用作while语句的条件,那么,select语句必须包含在英文括号中。
declare@aint,@Sumint
set@sum=0
set@a=1
while@a<7
begin
if@a%2=0
set@sum=@sum+@a
set@a=@a+1
end
print@sum
三、多条件函数(Case)
CASE
首先,Case是函数,应该说是条件表达式,不是条件语句。
计算条件列表并返回多个可能结果表达式中的一个。
CASE具有两种格式:
1、简单CASE函数将某个表达式与一组简单表达式进行比较以确定结果。
语法
CASEinput_expression
WHENwhen_expression1THENresult_expression1
[WHENwhen_expression2THENresult_expression2[·····]]
[
ELSEelse_result_expression
]
END
执行过程:
计算input_expression的值,并依次与when_expression1、when_expression2、·····等表达式比较,如果与when_expression相等,则返回相应的result_expression表达式的值(即作为Case表达式的值,不再与以下的when_expression比较)。
如果没有相等,则返回else_result_expression表达式的值。
如果没有else_result_expression,则表达式返回NULL。
2、CASE搜索函数计算一组布尔表达式以确定结果。
语法
CASE
WHENBoolean_expression1THENresult_expression1
[WHENBoolean_expression2THENresult_expression2[·····]]
[
ELSEelse_result_expression
]
END
执行过程:
依次计算Boolean_expression1、Boolean_expression2、·····等表达式,一旦Boolean_expression结果为True,则返回相应的result_expression表达式的值(即作为Case表达式的值,不再计算以下的Boolean_expression)。
如果Boolean_expression结果全为False,则返回else_result_expression表达式的值。
如果没有else_result_expression,则表达式返回NULL。
例子1:
有表Student(Snovarchar(20),Snamevarchar(20),Ssexvarchar(20),Sageint,Sdeptvarchar(20))。
SnoSnameSsexSageSdept
200215121李勇男20CS
200215122刘晨女19CS
200215123王敏女18MA
200215124张立男19IS
要求用一条SQL语句把Student表的Ssex字段的值('男')变成'女','女'变成'男','null'不变
updatestudent
setssex=
casessex
when'男'then'女'
when'女'then'男'
end
select*fromstudent
例子2:
2:
有一张表table1,有三个字段分别是:
年度,季度,数量;现在数据如下:
年度季度数量
1991123
1991232
1991315
1991420
1992125
1992265
要求用一条SQL语句查出结果为:
年份1季度1季度2季度3季度4
199123321520
1992256500
如果实现以上功能,需分两步:
第一步:
创建以下查询
select年度,
case
when季度='1'then数量
else'0'
endas季度1,
case
when季度='2'then数量
else'0'
endas季度2,
case
when季度='3'then数量
else'0'
endas季度3,
case
when季度='4'then数量
else'0'
endas季度4
fromtable1
结果为:
年份1季度1季度2季度3季度4
199123000
199103200
199100150
199100020
199225000
199206500
第二步:
对以上表进行进行年份汇总
select年度,sum(季度1)as季度1,sum(季度2)as季度2,sum(季度3)as季度3,sum(季度4)as季度4from
(
select年度,
case
when季度='1'then数量
else'0'
endas季度1,
case
when季度='2'then数量
else'0'
endas季度2,
case
when季度='3'then数量
else'0'
endas季度3,
case
when季度='4'then数量
else'0'
endas季度4
fromtable1
)b
groupby年度
切记:
如果Select语句中的表为Select语句,必须为该语句加表别名。
第五节运算符
一、算术运算符
+(加)、―(减)、*(乘)、/(除)、%(取余)
运算符
数据类型
+、-、*、/
int,smallint,tinyint,numeric,decimal,float,real,money,smallmoney
%
int,smallint,tinyint
二、关系运算符
比较运算符能够进行除text、ntext和image数据类型之外的其它数据类型表达式的比较操作,注意:
sql不能直接输出比较操作结果。
Select5>6
执行上述语句是会报错的。
Select8where5>7
执行上述语句,是根据5>7表达式结果是否输出8。
>(大于)
<(小于)
=(等于)
>=(大于等于)
<=(小于等于)
<>(不等于)
!
=(不等于)
!
>(不大于)
!
<(不小于)
其中!
=、!
>、!
<不是ANSI标准的运算符。
三、逻辑运算符
AND逻辑与
OR逻辑或
NOT逻辑非
&按位与
|按位或
~按位非
^按位异或
逻辑运算符用于测试条件是否为真,但Sql不会直接输出真或假,只能通过sql语句体现出结果。
例如:
select5where7>8and8>9
可通过以上语句能够反映出逻辑运算表达式的结果。
按位运算可直接输出:
例如:
select5|2输出结果为:
7
Select5&2输出结果为:
0
对于按位逻辑运算:
左操作数
右操作数
binary,varbinary
int,smallint,tinyint
int,smallint,tinyint
int,smallint,tinyint,binary,varbinary
bit
int,smallint,tinyint,bit
求反运算(~)是一个单目运算,它只能对int,smallint,tinyint或bit 类型的数据进行求反运算
四、连接运算符
连接运算符“+”用于连接两个或两个以上的字符或二进制串、列名或者串和列的混合体,将一个串加入到另一个串的末尾
注意:
连接运算符两边操作数数据类型必须为字符型,
例如:
selectsname+’同学的年龄为:
’+ltrim(str(sage))fromstudent
五、范围运算符
Between起始值and终止值。
范围在二者之间
NotBetween起始值and终止值。
范围不在二者之间
例如:
declare@aint,@bint
set@a=20
set@b=25
SelectSnamefromStudentwhereSagebetween@aand@b
六、列表运算符
IN(列表)值在列表之内
NOTIN(列表)值不在列表之内
例如:
例如:
查询与李勇在同一系的同学姓名。
selectsnamefromstudentwheresdeptin(selectsdeptfromstudentwheresname='李勇')andsname<>'李勇'
七、表达式的执行顺序
在Transact-SQL中运算符的处理顺序如下所示,如果相同层次的运算出现在一起时则处理顺序位从左到右。
括号()
按位非运算符~
算术运算符*、/、%
算术运算符+、-
按位异或运算符^
按位与运算符&
按位或运算符|
逻辑运算符NOT
逻辑运算符AND
逻辑运算符OR
八、通佩符
通配符
描述
%
替代零个或多个字符
_
仅替代一个字符
[charlist]
字符列中的任何单一字符
[^charlist]
或者
[!
charlist]
不在字符列中的任何单一字符
Charlist:
表示字符列表。
例如:
[a,c]
Select*fromstudentwheresnamelike‘%李%’
查询sname包括李字符所有记录。
Select*fromstudentwheresnamelike‘李_’
查询sname包括第一个字符为李,并sname为两个字符的所有记录。
select*fromstudentwheresnamelike'[李,刘]%'
查询sname第一个字符为李或刘的所有记录。
select*fromstudentwheresnamelike'[^李,刘]%'
查询sname第一个字符不为李或刘的所有记录。
第六节函数
一、字符串函数
函数名
例子
描述
ASCII(char)
ASCII(‘ABC’)