sql教程.docx
《sql教程.docx》由会员分享，可在线阅读，更多相关《sql教程.docx（20页珍藏版）》请在冰豆网上搜索。
sql教程
第5章Transact-SQL及其程序设计基础
5.2Transact-SQL基础
5.2.2数据查询
1．查询表中所有的列
例如，要查询表book中的所有书籍的信息，可在SQL查询分析器中输入如下命令：
SELECT*FROMbook
2．查询表中指定的列
例如，要查询所有书籍的名称和价格，可输入下面的SQL语句：
SELECTbook_name,priceFROMbook
可以重新排列列的次序，在SELECT后的列名的顺序决定了显示结果中的列序。
如果想把价格放在前面，则上面的SQL语句应该写成：
SELECTprice,book_nameFROMbook
3．使用单引号加入字符串
例如，要查询所有书籍的名称和价格，并在价格前面显示字符串“价格为：
”，可输入下面的SQL语句：
SELECTbook_name,'价格为：
',priceFROMbook
4．使用别名
例如，查询所有书籍的名称和价格，并在标题栏种显示“书名”和“价格”字样，而不是显示book_name和price。
可输入下面的SQL语句：
SELECTbook_nameAS书名,priceAS价格FROMbook
或者：
SELECTbook_name书名,price价格FROMbook
或者：
SELECT'书名'=book_name,'价格'=priceFROMbook
5．查询特定的记录
例如，要查询《Windows2000网络管理》一书的信息，则可以输入以下SQL语句：
SELECT*FROMbookWHEREbook_name='Windows2000网络管理'
6．对查询结果进行排序
例如，依照价格高低来显示所有书籍的信息，输入以下SQL语句：
SELECT*FROMbookORDERBYpriceDESC
7．多表查询
输入下面的SQL语句：
SELECTbook.book_name,authors.author_name
FROMbook,authors
WHEREbook.author_id=authors.author_id
8．消除重复的行
例如，查询所有书籍所属的出版社。
输入SQL语句如下：
SELECTDISTINCTpublisherFROMbook
5.2.3数据插入和删除
在表authors中插入一笔记录，即新增一个作者。
输入SQL语句如下：
INSERTauthors（author_id,author_name）VALUES（3,'张英魁'）
表示加入了一笔记录。
使用SELECT语句查询authors表，可看到新增加的记录。
输入如下SQL语句：
SELECT*FROMauthors
例如，删除book表中《“Windows2000Professional看图速成》一书的记录，可以输入以下SQL语句：
DELETEbook
WHEREbook_name='Windows2000Professional看图速成'
下面的例子即为删除authors表中的所有数据：
TRUNCATETABLEauthors
5.2.4数据修改
例如，将authors表中作者为“王小明”全部改为“王晓明”，SQL语句如下：
UPDATEauthorsSETauthor_name='王晓明'
WHEREauthor_name='王小明'
5.2.5使用函数
例如，在orderform表中，提交一笔订单，在插入数据时，即可使用GETDATE（）函数来获取当前的日期。
为了保持数据完整性，首先在clients表中插入一个客户，然后再在orderform表中提交一笔订单。
输入下面的SQL语句：
INSERTclientsVALUES（1,'刘明耀','北京市海淀区'）
INSERTorderformVALUES（1,2,50,GETDATE（）,1）
按F5键，然后使用SELECT语句可查看执行结果：
SELECT*FROMorderform
5.2.6使用公式
例如，要查询所有订单中的书名、数量和总额。
此时，就可以使用公式来计算总额。
输入的SQL语句如下：
SELECTbook.book_name,orderform.book_number,
'总额为：
',（book.price*orderform.book_number）
FROMorderform,book
WHEREorderform.book_id=book.book_id
5.2.7数据库的操作语句
5.2.7.1创建数据库
例如，建立一个名为test的数据库，可以输入如下的SQL语句：
CREATEDATABASEtest
例如，要创建一个销售数据库，并设定数据文件为d:
\销售.LDF，大小为5MB，最大为20MB，每次增长5MB。
事务日志文件为d:
\销售.LDF，大小为5MB，最大为10MB，每次增长为1MB。
则创建的SQL语句为：
CREATEDATABASE销售数据库
ON（
NAME=销售数据,
FILENAME='d:
\ProgramFiles\MicrosoftSQLServer\MSSQL\data\销售数据.MDF',
SIZE=10MB,
MAXSIZE=50MB,
FILEGROWTH=10MB
）
LOGON（
NAME=销售数据日志,
FILENAME='d:
\ProgramFiles\MicrosoftSQLServer\MSSQL\data\销售数据日志.LDF',
SIZE=10MB,
MAXSIZE=20MB,
FILEGROWTH=5MB
）
5.2.7.2修改数据库
例如，为销售数据库新增一个逻辑名为“销售数据2”的数据文件，其大小及其最大值分别为10MB和50MB。
输入的SQL语句为：
ALTERDATABASE销售数据库
ADDFILE（
NAME=销售数据2,
FILENAME='d:
\ProgramFiles\MicrosoftSQLServer\MSSQL\data\销售数据2.MDF',
SIZE=10MB,
MAXSIZE=50MB,
FILEGROWTH=5M
）
5.2.7.3使用和删除数据库
例如，可以使用如下SQL语句来删除销售数据库：
DROPDATABASE销售数据库
5.2.8表的操作语句
5.2.8.1表的创建
1．基本用法
在test数据库中创建一个clients表，SQL语句如下：
USEtest
CREATETABLEclients（
client_idint,
client_namechar（8）,
addresschar（50）
）
2．段属性参数
下面的SQL语句是在test数据库中建立一个book表，并指定book_id为主键，而book_name为非空：
CREATETABLEbook（
book_idintNOTNULLPRIMARYKEY,
book_namechar（8）NOTNULL,
author_idchar（50）
）
3．与其他表建立关联
例如，可以将book表中的author_id字段关联到authors表的author_id字段。
在企业管理器中将上面创建的book表删除，然后执行下面的语句：
CREATETABLEauthors（
author_idintNOTNULLPRIMARYKEY,
author_namechar（8）NOTNULL,
addresschar（50）NULL
）
CREATETABLEbook（
book_idintNOTNULLPRIMARYKEY,
book_namechar（8）NOTNULL,
author_idintFOREIGNKEYREFERENCESauthors（author_id）
）
5.2.8.2修改表
例如，在test数据库中给book表增加一个“简介”字段：
ALTERTABLEbookADD简介text
5.2.8.3删除关联和表
例如，要删除表book，可执行下述SQL语句：
DROPTABLEbook
5.3Transact-SQL程序设计基础
5.3.1标识符
5.3.1.1常规标识符
例如：
SELECT*FROMTableXWHEREKeyCol=124
5.3.1.2分隔标识符
例如，打开和关闭该选项的SET语句如下：
SETQUOTED_IDENTIFIERON
SETQUOTED_IDENTIFIEROFF
5.3.1.3使用标识符
例如，一个用户名为bookadm的用户登录到MyServer服务器上，并使用book数据库。
使用下述语句创建了一个MyTable表：
CREATETABLEMyTable
（
column1int,
column2char（20）
）
5.3.2数据类型
5.3.2.1系统数据类型
1．整数型
例如，下面的语句创建了一个表Int_table，其中的4个字段分别使用这4种整数类型：
USEtest
CREATETABLEInt_table
（
c1tinyint,
c2smallint,
c3int,
c4bigint,
）
INSERTInt_tableVALUES（50,5000,50000,500000）
SELECT*FROMInt_table
2．小数数据类型
例如，在下面的表Decimal_table中，字段c1就是一个decimal数据类型：
CREATETABLEDecimal_table
（
c1decimal（3,2）
）
INSERTDecimal_tableVALUES（4.5678）
SELECT*FROMDecimal_table
在为小数数值型数据赋值时，应保证所赋数据整数部分的位于小于或者等于定义的长度，否则会出现溢出错误。
例如，给Decimal_table插入一笔记录，在SQL查询分析器中执行下面语句：
INSERTDecimal_tableVALUES（45.678）
3．近似数值型
4．字符型数据类型
例如，下面的SQL语句将局部变量MyCharVar声明为char类型，长度为25：
DECLARE@MyCharVarCHAR（25）
SET@MyCharVar='Thisisastring'
下面则是使用两个单引号来表示嵌入单引号：
SET@MyCharVar='Thisisa''string'''
CHAR函数可以把一个整数转换为ASCII字符。
当确定控制字符时（比如回车或换行），这是很有用的。
在字符串中用CHAR（13）和CHAR（10）产生一个回车并生成一个新行。
例如：
PRINT'Firstline.'+CHAR（13）+CHAR（10）+'Secondline.'
5．逻辑数据类型
例如，下面是使用bit数据类型的例子：
CREATETABLEBit_table
（
c1bit,
c2bit,
c3bit
）
INSERTBit_tableVALUES（12,1,0）
SELECT*FROMBit_table
6．货币型
下面是使用货币数据类型的例子：
CREATETABLEMoney_table
（
c1money,
c2smallmoney
）
INSERTMoney_tableVALUES（$12345678,$1234）
SELECT*FROMMoney_table
7．二进制数据类型
下面是使用二进制数据类型的例子：
CREATETABLEBinary_table
（
c1binary（10）,
c2varbinary（20）,
c3image
）
INSERTBinary_tableVALUES（0x123,0xfffff,0x14fffff）
SELECT*FROMBinary_table
8．日期时间类型
下面是一个使用smalldatetime数据类型的例子：
SELECTCAST（'2000-05-0812:
35:
29.998'ASsmalldatetime）
以下是使用日期时间数据类型的例子：
CREATETABLEDatetime_table
（
c1datetime,
c2smalldatetime,
）
INSERTDatetime_tableVALUES（'2001-05-1500:
04:
39.257','04/15/199614:
30:
20PM'）
SELECT*FROMDatetime_table
9．Unicode字符型
使用Unicode字符时，应该在前面加一个标识符N，但是存储时并不存储该标识符。
例如：
DECLARE@MyUnicodeVarNCHAR（25）
SET@MyUnicodeVar=N'ThisisaUnicodestring.'
PRINT@MyUnicodeVar
10．其他数据类型
下面的示例使用NEWID对声明为uniqueidentifier数据类型的变量赋值，并将其打印出来：
DECLARE@MyIDuniqueidentifier
SET@MyID=NEWID（）
PRINT'Valueof@MyIDis:
'+CONVERT（varchar（255）,@MyID）
5.3.2.2用户定义数据类型
2．使用存储过程
下面的示例为国内及国际电话和传真号码另外创建两个用户定义的数据类型telephone和fax：
EXECsp_addtypetelephone,'varchar（24）','NOTNULL'
EXECsp_addtypefax,'varchar（24）','NULL'
5.3.3运算符
2．赋值运算符
例如，下面的SQL语句先声明一个变量，然后将一个取模运算的结果赋给该变量，最后是打印该变量的值：
DECLARE@MyCounterINT
SET@MyCounter=17%3
PRINTCONVERT（varchar（255）,@MyCounter）
也可以使用赋值运算符在列标题和为列定义值的表达式之间建立关系。
例如，下面的SQL语句是将bookdb数据库中的book表的book_id均以“书籍”显示：
USEbookdb
SELECTbook_id='书籍',book_name,priceFROMbook
3．按位运算符
例如，下面的SQL语句对两个变量进行按位运算：
DECLARE@aINT,@bINT
SET@a=5
SET@b=10
SELECT@a&@b,@a|@b,@a^@b
4．比较运算符
下面的SQL语句查询book表中价格大于35.0的书籍信息：
USEbookdb
SELECT*FROMbookWHEREprice>35.0
5．逻辑运算符
例如，下面的SQL语句在book表中查询书名包含“网络管理”，而且价格在20到50之间的书籍的信息：
SELECT*FROMbook
WHERE（book_nameLIKE'%网络管理%'）AND（priceBETWEEN20AND50）
6．字符串连接运算符
例如，下面的SQL语句将两个字符串连接在一起：
SELECT（'abc'+'def'）
7．一元运算符
例如，下面的SQL语句，首先声明一个变量，并对变量赋值，然后对变量取负：
DECLARE@Num1int
SET@Num1=5
SELECT-@Num1
8．运算符优先级
例如，在下面的示例中，在SET语句中使用的表达式中，在加号运算符之前先对减号运算符进行求值：
DECLARE@MyNumberint
SET@MyNumber=6-5+7
SELECT@MyNumber
例如：
DECLARE@MyNumberint
SET@MyNumber=3*（5+（7-3））
SELECT@MyNumber
5.1.4变量
1．局部变量
在SQLServer中，一次可以定义多个变量。
例如：
DECLARE@maxpricefloat,@pubchar（12）
例如，下面首先定义了两个变量，并分别使用SET和SELECT为其赋值，然后使用这两个变量查询价格小于50，且出版社为“明耀工作室”的书籍信息：
DECLARE@maxpricefloat,@pubchar（12）
SET@maxprice=50
SELECT@pub='明耀工作室'
SELECT*FROMbookWHEREprice<@maxpriceANDpublisher=@pub
5.3.5批处理
下面的SQL语句创建一个视图。
因为CREATEVIEW必须是批处理中的唯一语句，所以需要GO命令将CREATEVIEW语句与其周围的USE和SELECT语句隔离：
USEbookdb
GO
CREATEVIEWauth_titlesASSELECT*FROMauthors
GO
SELECT*FROMauth_titles
GO
5.3.6注释
下面就是使用注释的例子：
USEbookdb
GO
--这是双连字符注释
SELECT*FROMbook--从表book中查询书籍信息
GO
/*这是正斜杠-星号对
注释*/
SELECT*FROMauthors/*查询作者信息*/
GO
5.3.7控制流语句
5.3.7.1BEGIN...END语句
BEGIN...END语句可以嵌套使用。
例如：
BEGIN
DECLARE@MyVarfloat
SET@MyVar=456.256
BEGIN
PRINT'变量@MyVar的值为：
'
PRINTCAST（@MyVarASvarchar（12））
END
END
5.3.7.2IF...ELSE语句
IF...ELSE语句地执行方式是：
如果布尔表达式的值为True，则执行IF后面的语句块；否则执行ELSE后面的语句块。
例如：
USEbookdb
IF（SELECTpriceFROMbookWHEREbook_nameLIKE'%网络管理%'）>50
BEGIN
PRINT'这本书太贵了！
'
PRINT'我承受不起！
'
END
ELSE
BEGIN
PRINT'这本书还可以！
'
PRINT'我要买一本！
'
END
5.3.7.3CASE语句
1．简单CASE格式
例如：
USEpubs
GO
SELECTau_fname,au_lname,
CASEstate
WHEN'CA'THEN'California'
WHEN'KS'THEN'Kansas'
WHEN'TN'THEN'Tennessee'
WHEN'OR'THEN'Oregon'
WHEN'MI'THEN'Michigan'
WHEN'IN'THEN'Indiana'
WHEN'MD'THEN'Maryland'
WHEN'UT'THEN'Utah'
ENDASStateName
FROMpubs.dbo.authorsWHEREau_fnameLIKE'M%'
2．搜索CASE格式
例如：
USEbookdb
GO
SELECTbook_name,
CASE
WHENprice>=50THEN'太贵了！
'
WHENprice>=40THEN'还可以，考虑考虑！
'
ELSE'挺便宜的，买一本'
ENDAS价格
FROMbook
GO
5.3.7.4WHILE语句
例如，下面的SQL语句是计算从1加到100的值：
DECLARE@MyResultint,@MyVarint
SET@MyVar=0
SET@MyResult=0
WHILE@MyVar<=100
BEGIN
SET@MyResult=@MyResult+@MyVar
SET@MyVar=@MyVar+1
END
PRINTCAST（@MyResultASchar（25））
5.3.7.5GOTO语句
例如，使用下面的语句重新计算从1加到100的值：
DECLARE@MyResultint,@MyVarint
SET@MyVar=0
SET@MyResult=0
my_loop:
--定义标号
SET@MyResult=@MyResult+@MyVar
SET@MyVar=@MyVar+1
IF@MyVar<=100
GOTOmy_loop--如果小于100，跳转到my_loop标号处
PRINTCAST（@MyResultASchar（25））
5.3.7.6RETURN语句
例如，首先执行下面的SQL语句创建一个存储过程：
USEbookdb
GO
CREATEPROCMyPro@booknamechar（50）--创建存储过程MyPro
AS
IF（SELECTpriceFROMbookWHEREbook_nameLIKE@bookname）>=50
RETURN1
ELSE
RETURN2
然后执行下面的SQL语句：
DECLARE@Return_valueint
EXEC@Return_value=MyPro'%网络管理%'
IF@Return_value=1
PRINT'这本书太贵了！
'
ELSE
PRINT'这本书还可以，值得考虑购买！
'
GO
5.3.7.7WAITFOR语句
例如，下面的SQL语句指定在1点58时执行一个语句：
BEGIN
WAITFORTIME'1:
58:
00'
PRINT'现在是1:
58:
00'
END
5.3.8函数
5.3.8.1内置函数
例如，下面是一个使用内置函数的例子：
PRINT'现在日期和时间是：
'+CAST（GETDATE（）ASchar（50））
5.3.8.2用户定义函数
例如：
下面的SQL语句在test数据库中定义了一个CubicVolume用户定义函数，然后使用该函数计算一个长方体的体积：
USEtest
GO
CREATEFUN
升级会员 