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
最后一个Ｔ－ＳＱＬ错误的错误号
@@IDENTITY
最后一个插入的标识值
@@LANGUAGE
当前使用语言的名称
@@MAX_CONNECTIONS
可以创建的同时链接的最大数目
@@ROWCOUNT
受上一个ＳＱＬ语言影响的行数
@@SERVERNAME
本地服务器的名称
@@SERVICENAME
该计算机上的ＳＱＬ服务的名称
@@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’）
升级会员 