SQL案例.docx
《SQL案例.docx》由会员分享，可在线阅读，更多相关《SQL案例.docx（31页珍藏版）》请在冰点文库上搜索。
SQL案例
第1章SQLServer2005系统概述
自己动手：
SQLServer2005功能比较，请你自己在Internet上搜索、查阅、比较不同SQLServer2005版本的功能差异。
推荐地址如：
自己动手：
了解各版本的系统安装需求，如：
①SQLServer2005企业版（32位和64位），其系统需求参见网址：
enterprise/sysreqs.mspx；②SQLServer2005标准版（32位和64位），其系统需求参见网址：
Server2005开发版（32位和64位），其系统需求参见网址：
自己动手：
①下载并安装SQLServerExpress：
安装SQLServer2005ExpressEdition（SQLServerExpress）时，您必须做出决策以选择最适合您环境的选项。
下面是SQLServer2005ExpressEdition中文版的下载网址：
②下载或订购SQLServer2005180天试用版：
下载或订购SQLServer2005180天试用软件（SQLServer2005EnterpriseEvaluationEdition）。
下载网址：
或SQLServer2005EnterpriseEdition180-DayDVDEvaluation，下载网址：
③下载SQLServer2005例子及样例数据库（2005.12）：
下载SQLServer2005SamplesandSampleDatabases（Dec2005）的网址：
第2章Transact-SQL
selectgetdate（）,getdate（）-10,cast（（getdate（）-cast（'2006-01-18'asdatetime））asint）,cast（（getdate（）-（'2006-01-18'））asint）--查询当前日期、10天前日期、日期间隔天数等。
DECLARE@MyCounterINT;SET@MyCounter=10;
USEAdventureWorks;--缺省时均认为使用AdventureWorks数据库
SELECTFirstColHeading='xyz',SecondColHeading=ProductIDFROMProduction.Product;
如：
select+（-8）,-（-8）,~（-8）,~8，其查询结果为：
-8、8、7、-9
~8值的说明：
（8）10=（0000000000001000）2，（~8）10=（1111111111110111）2=（-9）10
DECLARE@MyProductint;SET@MyProduct=380;
IF（@MyProduct<>0）SELECTProductID,Name,ProductNumber
FROMProduction.ProductWHEREProductID=@MyProduct
自己动手：
在SQLServer集成管理器查询窗口中通过“SELECT含运算符表达式”形式了解并熟悉各种运算符及其组合的使用情况。
如：
SELECT180*3.14-100。
DECLARE@BinaryVariable2BINARY
（2）;SET@BinaryVariable2=123456
SET@BinaryVariable2=@BinaryVariable2+1;SELECTCAST（@BinaryVariable2ASINT）
最终结果为57921，而不是123457。
例2-1
USEAdventureWorks;--此示例将uniqueidentifier值转换为char数据类型。
SELECTSalesPersonID,CAST（SalesYTDASvarchar（12））,CONVERT（VARCHAR（12）,GETDATE（）,3）
FROMSales.SalesPersonWHERECAST（SalesYTDASvarchar（20））LIKE'1%'
DECLARE@myiduniqueidentifier;SET@myid=NEWID（）
SELECTCONVERT（char（255）,@myid）AS'char'--此示例将当前日期转换为样式3，dd/mm/yy。
SELECTCONVERT（char（12）,GETDATE（）,3）
例2-2
DECLARE@mydate_smsmalldatetime;SET@mydate_sm='12/08/2006'
SELECTCAST（@mydate_smASvarchar）ASSM_DATE_VARCHAR
DECLARE@mydatedatetime;SET@mydate='12/08/2006'
SELECTCAST（@mydateASbinary）ASDATE_BINARY--结果略，请自己运行查看
例2-3
DECLARE@mymoney_smsmallmoney;SET@mymoney_sm=3148.29;
SELECTCAST（@mymoney_smASvarchar）AS'SM_MONEYvarchar';
DECLARE@mymoneymoney;SET@mymoney=3148.29;
SELECTCAST（@mymoneyASdecimal）AS'MONEYDECIMAL';
ELECTCONVERT（money,COALESCE（hourly_wage*40*52,salary,commission*num_sales））AS"TotalSalary"FROMwages
SELECT'Thecurrenttimeis:
'+CONVERT（char（30）,CURRENT_TIMESTAMP）
USEAdventureWorks;--缺省时均认为使用AdventureWorks数据库
SELECTlength=DATALENGTH（Name）,NameFROMProduction.ProductORDERBYName;
UPDATEHumanResources.EmployeeSETVacationHours=241WHERENationalIDNumber=509647174;
IF@@ERROR=547PRINTN'发生了check约束冲突.';
例2-6
BEGINTRY
SELECT2/0;--产生除以零的错误
ENDTRY
BEGINCATCH
SELECTERROR_NUMBER（）ASErrorNumber,ERROR_SEVERITY（）ASErrorSeverity,ERROR_STATE（）ASErrorState,ERROR_PROCEDURE（）ASErrorProcedure,ERROR_LINE（）ASErrorLine,ERROR_MESSAGE（）ASErrorMessage;
ENDCATCH;
例2-7
SELECT*FROMfn_helpcollations（）WHEREnamelike'Chinese_PRC_CS%'
（13）@@IDENTITY：
返回最后插入的标识值的系统函数。
INSERTINTOProduction.Location（Name,CostRate,Availability,ModifiedDate）VALUES（'DamagedGoods',5,2.5,GETDATE（））;
SELECT@@IDENTITYAS'Identity';
SELECTMAX（LocationID）FROMProduction.Location;
DECLARE@datestrvarchar（8）;SET@datestr='2007-01-28'
SELECTISDATE（@datestr）--请问返回是0，还是1呢？
例2-10
SELECTCity,PostalCodeFROMPerson.AddressWHEREISNUMERIC（PostalCode）=1;
例2-11
DECLARE@myiduniqueidentifier--创建局部变量
SET@myid=NEWID（）;PRINT'@myid的值是：
'+CONVERT（varchar（255）,@myid）
例2-12
SELECTPARSENAME（'AdventureWorks..Product',1）AS'ObjectName';
SELECTPARSENAME（'AdventureWorks..Product',2）AS'SchemaName';
SELECTPARSENAME（'AdventureWorks..Product',3）AS'DatabaseName;'
SELECTPARSENAME（'AdventureWorks..Product',4）AS'ServerName';
请执行命令：
selectABS（-10）,sin（pi（）/4）,asin（1.0）,exp（1.0）,log（exp（1.0））,
pi（）,LOG10（10）,sqrt（SQUARE（3））,rand（），查看执行结果，了解各函数功能。
例2-19
DECLARE@nstringnchar（8）;SET@nstr=N'København'
SELECTUNICODE（SUBSTRING（@nstr,2,1））,NCHAR（UNICODE（SUBSTRING（@nstr,2,1）））
执行如下SELECT命令，体会不同日期格式形式：
selectconvert（varchar（10）,getdate（）,101）/*美国*/,
convert（varchar（10）,getdate（）,102）/*ANSI*/,convert（varchar（10）,getdate（）,110）/*美国*/,
convert（varchar（10）,getdate（）,120）/*ODBC*/,convert（varchar（10）,getdate（）,3）/*英国/法国*/
--显示的日期格式形如：
01/26/20062006.01.2601-26-20062006-01-2626/01/06
SELECTDAY（'01/28/2007'）AS'几号'
SELECT"月份"=MONTH（'01/28/2007'）
SELECT"年份"=YEAR（'01/28/2007'）
举一例如下：
CREATEFUNCTIONdbo.DaysBetweenDates（@D1datetime,@D2datetime）RETURNSINT
ASBEGINRETURN（SELECTcast（（@d2-@d1）asint））END--定义
go
selectdbo.DaysBetweenDates（getdate（）,cast（'2006-01-28'asdatetime））--使用
例2-25本例
DECLARE@LastNamenvarchar（30）,@FirstNamenvarchar（20）,@Statenchar
（2）
SET或SELECT赋值语句的语法如下：
SELECT@局部变量=变量值或SET@局部变量=变量值
例2-26
CREATETABLETestTb（colaint,colbchar（3））--创建表
DECLARE@MyCounterint;/*定义变量*/SET@MyCounter=0/*初始化变量*/
WHILE（@MyCounter<26）--使用变量控制循环次数
BEGIN
INSERTINTOTestTbVALUES
（@MyCounter,CHAR（（@MyCounter+ASCII（'a'））））--自动生成列值并插入行到表
SET@MyCounter=@MyCounter+1--循环控制变量加1
END
例2-28
USEpubs
CREATETRIGGERdeltitleONtitlesFORdelete
ASIF（SELECTCOUNT（*）FROMdeleted,salesWHEREsales.title_id=deleted.title_id）>0
BEGIN
ROLLBACKTRANSACTION;PRINT'Youcannotdeleteatitlewithsales.'
END
例2-29
USEpubs
IF（SELECTAVG（price）FROMtitlesWHEREname='DB原理'）<15
BEGINPRINT'书价不正确！
'END
ELSEPRINT'DB原理书的总价高于15元'
例2-30从学生表S中，选取SNO，SEX，如果SEX为“男”则输出“M”，如果为“女”则输出“F”。
SELECTSNO,SEX=CASESEX
WHEN'男'THEN'M'
WHEN'女'THEN'F'
END
FROMS
例2-31
SELECTSNO,CNO,SCORE=
CASE
WHENSCOREISNULLTHEN'未考'
WHENSCORE<60THEN'不及格'
WHENSCOREBETWEEN60AND69THEN'及格'
WHENSCOREBETWEEN70AND89THEN'良好'
WHENSCORE>=90THEN'优秀'
END
FROMSC
例2-32
WHILEEXISTS（SELECT*FROMEMPLOYEEWHERESALARY*0.3<300）
BEGIN
UPDATEEMPLOYEESETSALARY=SALARY+500
IF（SELECTMAX（SALARY）FROMEMPLOYEE）>3000BREAK
ELSECONTINUE
END
例2-33等待1小时3分24秒后才执行SELECT语句。
WAITFORDELAY'01:
03:
24';SELECT*FROMS;
例2-34
DECREASE_SALARY:
……
IF（SELECTMAX（SALARY）FROMEMPLOYEE）>10000GOTODECREASE_SALARY
USEAdventureWorks;
--单行注释.
SELECTEmployeeID,TitleFROMHumanResources.Employee;
GO
/*多行注释的第一行
多行注释的第二行*/
SELECTName,ProductNumber,ColorFROMProduction.Product;
--在调试T-SQL命令时使用注释
SELECTContactID,/*FirstName,*/LastNameFROMPerson.Contact;
--在代码行后使用注释
UPDATEProduction.ProductSETListPrice=ListPrice*0.9;--降低价格，赢得市场
第3章创建与使用数据库
例3-1
USEmaster;--操作数据库，往往要求master为当前数据库，以下略该命令
IFDB_ID（N'jxgl'）ISNOTNULLDROPDATABASEjxgl;--判断是否已有该库？
有则删
CREATEDATABASEjxgl;--创建数据库
SELECTname,size,size*1.0/128AS[SizeinMBs]
FROMsys.master_filesWHEREname=N'jxgl';--验证数据库文件和其文件大小
例3-2
IFDB_ID（N'Sales'）ISNOTNULLDROPDATABASESales;
--得到SQLServer存放数据库文件的路径
DECLARE@data_pathnvarchar（256）;--@data_path中存放SQLServer数据库路径
SET@data_path=（SELECTSUBSTRING（physical_name,1,CHARINDEX（N'master.mdf',LOWER（physical_name））-1）FROMmaster.sys.master_filesWHEREdatabase_id=1ANDfile_id=1）;
EXECUTE（'CREATEDATABASESalesON（NAME=Sales_dat,FILENAME='''+@data_path+'saledat.mdf'',SIZE=10,MAXSIZE=50,FILEGROWTH=5）LOGON（NAME=Sales_log,FILENAME='''+@data_path+'salelog.ldf'',SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=5MB）'）;--执行CREATEDATABASE命令
例3-3
IFDB_ID（N'student'）ISNOTNULLDROPDATABASEstudent;
--@data_path的取值请参阅例3-2，此处略
EXECUTE（'CREATEDATABASEstudentONPRIMARY
（NAME=student1_dat,FILENAME='''+@data_path+'student1_dat.mdf'',SIZE=5,MAXSIZE=50,FILEGROWTH=15%）,（NAME=student2_dat,FILENAME='''+@data_path+'student2_dat.ndf'',SIZE=5,MAXSIZE=50,FILEGROWTH=15%）,
FILEGROUPstudentGroup1
（NAME=studentg11_dat,FILENAME='''+@data_path+'studentg11_dat.ndf'',SIZE=5,MAXSIZE=50,FILEGROWTH=5）,（NAME=studentg12_dat,FILENAME='''+@data_path+'studentg12_dat.ndf'',SIZE=5,MAXSIZE=50,FILEGROWTH=5）,
FILEGROUPstudentGroup2
（NAME=studentg21_dat,FILENAME='''+@data_path+'studentg21_dat.ndf'',SIZE=5,MAXSIZE=50,FILEGROWTH=5）,（NAME=studentg22_dat,FILENAME='''+@data_path+'studentg22_dat.ndf'',SIZE=5,MAXSIZE=50,FILEGROWTH=5）
LOGON（NAME=student_log,FILENAME='''+@data_path+'studentlog.ldf'',
SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=5MB）'）;--以执行字符串命令形式执行
例3-4
sp_detach_dbSales;
GO--得到SQLServer存放数据库文件的路径，@data_path的取值请参阅例3-2此处略，下同
EXEC（'CREATEDATABASESalesON（FILENAME='''+@data_path+'saledat.mdf''）FORATTACH'）;--执行CREATEDATABASE附加Sales数据库
例3-5
EXECUTE（'CREATEDATABASEsales_snapshot0800ON（NAME=Sales_dat,FILENAME='''+@data_path+'saledat_0800.ss''）ASSNAPSHOTOFSales'）;
例3-6
EXECUTE（'ALTERDATABASEAdventureWorksADDFILE（NAME=Test1dat2,FILENAME='''+@data_path+'t1dat2.ndf'',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB）'）;
例3-7
ALTERDATABASEAdventureWorksADDFILEGROUPTest1FG1;
EXECUTE（'ALTERDATABASEAdventureWorksADDFILE（NAME=test1dat3,FILENAME='''+@data_path+'t1dat3.ndf'',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB）,
升级会员 