sql培训.docx
《sql培训.docx》由会员分享，可在线阅读，更多相关《sql培训.docx（14页珍藏版）》请在冰点文库上搜索。
sql培训
SQL培训大纲
文档作者：
郭勇
完成日期：
2008-9-20
文档审核：
审核日期：
一、培训准备
1.目的
新员工进入公司默认已经掌握数据库基本知识，该大纲围绕开发人员在日常工作中应掌握的SQLServer数据库知识为线索，列出新员工要掌握的基本知识点，指导新员工学习，更快掌握日常开发所必需技能
2.具备知识
SQLServer2000或2005数据库常用工具使用、数据定义语言（DDL）、数据操纵语言（DML）、创建和管理表、视图、存储过程、触发程序等数据库对象、备份数据库和事务日志、恢复数据库
3.学习方式
以SQL培训大纲为线索进行学习。
掌握每个章节重点内容，配合参考MicrosoftSQLServer2000联机丛书（更新-2007），《T-SQL查询》上机做练习题加深理解
二、数据库常用工具
1.企业管理器
管理数据库，管理数据库对象，管理备份，管理SQLServerAgent
2.查询分析器
显示查询执行计划，索引优化向导，T-SQL编程
3.SQL事件探查器
跟踪监视数据库
三、数据定义语言（DDL），数据操纵语言（DML）
1.DDL
CREATE,ALTER,DROP
2.DML
SELECT,INSERT,UPDATE,DELETE
四、视图，自定义函数，存储过程，触发器
1.视图createviewviewsheet：
视点集中、简化操作、定制数据、合并分割数据、安全性
ifexists（select*fromsysobjectswhereid=object_id（'viewsheet'）andtype='v'）
dropviewviewsheet
go
createviewviewsheet
as
selecta.id,a.[name],b.[bname],b.[price]fromstudentsa,booksb
2.自定义函数createfunctionmyfun（@iint）：
自定义函数主要用于数据的计算，功能性很强，不能在函数体内对数据库进行insert,update,delete操作
alterfunctionmy_function（@ivarchar）
returnsint
as
Begin
Declare@varmyvarchar（50）
Set@varmy='12345'+@i
return@varmy
End
调用自定义函数
selectdbo.my_function
（1）asa,*fromstudents
3.存储过程：
允许标准组件式编程、能够实现较快的执行速度、能够减少网络流量、可被作为一种安全机制来充分利用
创建存储过程
createprocliyue
（
@idint,
@bunamevarchar（40）
）
AS
BEGIN
insertintostudents（id,name）values（@id,@buname）
select*fromstudents
updatestudentssetname='14'wherename='gdf'
deletefromstudentswherename='sss'
END
调用存储过程
execliyue@id=11,@buname='sss'
1.触发器：
必须清楚inserted,deleted的意义
inserted表和deleted表用于存放对表中数据行的修改信息。
他们是触发器执行时自动创建的，放在内存中，是临时表。
当触发器工作完成，它们也被删除。
它们是只读表，不能向它们写入内容。
触发器里面的两个临时的表：
Deleted,Inserted。
注意Deleted与Inserted分别表
示触发事件的表“旧的一条记录”和“新的一条记录”。
五、工作中常用的知识点
1.innerjoin,leftjoin,rightjoin
2.selectintofrom,insertintoselectfrom
INSERTINTOSELECT语句复制表数据
InsertintoTable2（a,c,d）selecta,c,5fromTable1
InsertintoTable2（field1,field2,...）selectvalue1,value2,...fromTable1
要求目标表Table2必须存在，由于目标表Table2已经存在，所以我们除了插入源表Table1的字段外，还可以插入常量。
SELECTINTOFROM语句创建表Table2并复制数据
selecta,cintoTable2fromTable1
selectvale1,value2intoTable2fromTable1
要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中。
3.IDENTITY
标识列，这一列将自动编号：
只能在新建表,或者修改列时才能设置
createtabletb（idintidentity（1,1））
alerttabletbaddkidintidentity（1,1）
例如identity（1,1）
表示这一列将自动从1开始编号，每插入一行，这一列就增1，并且插入数据时不能手动为这列插入，这列的值是系统自动插入的
@@rowcount（全局变量，统计受影响的行数；只对它上一个操作有影响）
4.updateaseta.col1=b.col1 frombwherea.col2=b.col2（将那些满足在a表中a.col2=b.col2条件的数据修改为满足在a表中a.col1=b.col1条件的数据从b表中读取）
5.利用临时表来做循环运算,exec（'动态SQL语句'）
利用临时表来做循环运算
createtable#temp01（idintidentity（1,1）,cntint）
begin
declare@iint
set@i=1
while（@i<100）
begin
insertinto#temp01（cnt）values（@i）
set@i=@i+1
end
end
exec（'动态SQL语句'）
1:
普通SQL语句可以用Exec执行
eg:
Select*fromtableName
Exec（'select*fromtableName'）
Execsp_executesqlN'select*fromtableName' --请注意字符串前一定要加N
2:
字段名，表名，数据库名之类作为变量时，必须用动态SQL
eg:
declare@fnamevarchar（20）
set@fname='FiledName'
Select@fnamefromtableName --错误,不会提示错误，但结果为固定值FiledName,并非所要。
Exec（'select'+@fname+'fromtableName'） --请注意加号前后的单引号的边上加空格
当然将字符串改成变量的形式也可
declare@fnamevarchar（20）
set@fname='FiledName'--设置字段名
declare@svarchar（1000）
set@s='select'+@fname+'fromtableName'
Exec（@s） --成功
execsp_executesql@s --此句会报错，@s不是nvarchar类型
declare@sNvarchar（1000） --注意此处改为nvarchar（1000）
set@s='select'+@fname+'fromtableName'
Exec（@s） --成功
execsp_executesql@s --此句正确
3.输出参数
declare@numint,
@sqlsnvarchar（4000）
set@sqls='selectcount（*）fromtableName'
exec（@sqls）
--如何将exec执行结果放入变量中？
declare@numint,@sqlsnvarchar（4000）
set@sqls='select@a=count（*）fromtableName'
execsp_executesql@sqls,N'@aintoutput',@numoutput－－定义@a为输出变量,@num获取变量
select@num
6.Exists,noexists,
7.Begintrans,commit,rollback
8.Xact_abort,数据类型转换convertconvert（varchar
（2）,datepart（hh,getdate（）））+convert（varchar
（2）,datepart（mi,getdate（）））+convert（varchar
（2）,datepart（ss,getdate（）））convert（varchar（100）,getdate（）,23）
selectconvert（varchar（20）,getdate（）,120）
selectconvert（decimal（7,2）,'111.00'）
castcast（'123'asint）,while,if（if@age>12begin…endelsebegin…end）,likelike‘%M%’
9.Altertablealtercolumn,altertableadd,altertabledropcolumn
Altertablealtercolumn修改表的某列的定义altertableUsersaltercolumnbnchar
（2）
altertableadd为表添加新的列altertableUsersaddaint
altertabledropcolumn删除表的某列altertableUsersdropcolumna
10.dateadd,datediff,datename,datepart,day,getdate,monty,year
dateadd（）函数计算一个日期通过给时间间隔加减来获得一个新的日期
dateadd（yy,1,'2008-10-1'）
年yy,yyyy;季度qq,q;月mm,m;年中的日dy,y;日dd,d;周wk,ww;星期dw,w;小时hh;
分钟mi,n;秒ss,s;毫秒ms;微妙mcs;纳秒ns。
Datediff（）函数计算两个日期之间的小时、天、周、月、年等时间间隔总数。
datediff（datepart,startdate,enddate）
datediff（ mm, '1999/07/19', '1999/08/23' ）
datename（）函数的主要用处是从日期中提取指定部分数据,比如我们想得到当前日期中的年份,月份等信息,就可以使用该函数.返回类型是nvarchar
datename（param,date）;
datename（yy,getdate（））
datepart（）是指定应返回的日期部分的参数
datepart（yy,getdate（））
11.abs,exp,rand,round
abs（）函数返回给定数字的绝对值。
selectabs（-6）*ceil（3.1）*floor（2.9）fromdual
ABS（-6）=6取绝对值
ceil（3.1）=4取得大于该的最小整数
floor（2.9）=2向下取整
结果：
6*4*2=48
exp（）将会返回以给定的参数为指数以e为底数的幂值
e=2.718281828459
exp
（2）
rand（）随机函数
A：
select floor（rand（）*N） ---生成的数是这样的：
12.0
B：
select cast（ floor（rand（）*N） as int） ---生成的数是这样的：
12
A：
select ceiling（rand（） * N） ---生成的数是这样的：
12.0
B：
select cast（ceiling（rand（） * N） as int） ---生成的数是这样的：
12
其中里面的N是一个你指定的整数，如100，可以看出，两种方法的A方法是带有.0这个的小数的，而B方法就是真正的整数了。
大致一看，这两种方法没什么区别，真的没区别？
其实是有一点的，那就是他们的生成随机数的范围：
方法1的数字范围：
0至N-1之间，如cast（ floor（rand（）*100） as int）就会生成0至99之间任一整数
方法2的数字范围：
1至N之间，如cast（ceiling（rand（） * 100） as int）就会生成1至100之间任一整数
Round（）四舍五入
表达式一round（742.00,-2）
表达式二round（742.00,-1,-1）
12.sum,min,max,count,avg
13.char,charindex,left,len,lower,ltrim,replace,right,rtrim,space,str,stuff,substring,upper
trim 函数:
去除数据中的空格。
rtrim代表去除右边的空格，ltrim代表去除左边的空格
declare@achar（10）
set@a='364654'
select@a
charindex（'y','liyue'）结果：
3
left（'123456',4）结果：
1234
len（'abcd'）结果：
4
lower（'READ'）结果：
read将大写字符数据转换为小写字符数据后返回字符表达式
ltrim（'Iamagirl'）结果：
Iamagirl删除起始空格后返回字符表达式
replace（'abcdefghicde','cde','xxx'）结果：
abxxxfghixxx
right（'123456',4）结果：
3456
rtrim（'Iamagirl'）结果：
Iamagirl舍去字符串右边的空字符串
'J'+space（5）+'M'结果：
JM返回由重复的空格组成的字符串
str（98.546）结果：
99str（98.446）结果：
98
Stuff（expression1_Str,startIndex,lengthInt,expression2_Str）函数共有四个参数，其功能是将expression1_Str中自startIndex位置起删除lengthInt个字符，然后将expression2插入到expression1_Str中的startIndex位置。
stuff（'abcde',3,1,'*.*'）结果：
ab*.*de
substring（'liyue',1,3）
upper（'liyue'）结果：
LIYUE返回将小写字符数据转换为大写的字符表达式
useStudyDatabase;
--创建表，为表定义字段
createtableUsers
（
useridintnotnullprimarykey,
usernamevarchar（20）,
userpwdvarchar（16）,
addtimedatetimedefaultgetdate（）,
addressvarchar（50）
）
--向表中添加数值
insertintoUsers（userid,username,userpwd）values（'1','liyue','123456'）
insertintoUsers（userid,username,userpwd）values（'2','qiaokai','123456'）
insertintoUsers（userid,username,userpwd）values（'3','lixing','123456'）
insertintoUsers（userid,username,userpwd）values（'4','zhangling','123456'）
insertintoUsers（userid,username,userpwd）values（'5','guoyong','123456'）
insertintoUsers（userid,username,userpwd）values（'6','xiaobai','123456'）
--删除表中的某行
deletefromUserswhereuserid=1
--更新表中的某个单元格
updateUserssetusername='yueyue'whereusername='liyue'
--为表添加新的列
altertableUsersaddpostcodenchar（6）
altertableUsersaddaint
altertableUsersaddbint
--删除表的某列
altertableUsersdropcolumna
--修改表某列的定义
altertableUsersaltercolumnbnchar
（2）
--修改表的名称
sp_rename'Users','users'
--修改表中某列的名称
sp_rename'Users.a','test'
--删除表
droptableusers
--查询语句
select*fromUsers
ifexists
（select*
fromsysobjectsa
wherea.id=object_id（'users'）andtype='u'）
droptableusers
ifobject_id（'users'）isnotnull
droptableusers
go
select*fromusersa
创建函数
创建没有返回值与没有参数的函数
Createfunctionmy_function（）
As
Begin
Declare@varmyvarchar（50）
Set@varmy=’12345’
End
创建没有返回值有参数的函数
Createfunctionmy_function（@user_namevarchar（50）,@user_pwdint（6））
As
Begin
Declare@varmy=@user_name+convert（varchar（50）,@user_pwd）
End
创建有返回值与有参数的函数
Createfunctionmy_function（@user_Namevarchar（128）,@passwordint（6））
returnsvarchar（5）声明返回值的类型及长度
as
begin
declare@resultvarchar（5）
declare@faglevarchar（5）
set
@result=selectusers.user_NamefromUSERS
as
userswhereusers.user_Name=@user_Nameandusers.password=@passwordif@result=''
begin
set@fagle='NO'
end
else
begin
set@falge='YES'
end
return@result--返回结果
end
sql创建函数
2009年11月05日星期四11:
01
IFexists（select*fromsysobjectswherename='getarea'）
dropfunctiongetarea
GO
createfunctiongetarea（@stuNamechar（30））
returnsvarchar（100） --声明返回值的类型及长度
AS
begin
declare@strVarchar（100）
set@str=''
select@str=@str+stu_area+'、'fromstudentwherestu_name=@stuName
if（len（@str）>0）
set@str=left（@str,len（@str）-1）--减1主要为去掉最后面的顿号
else
set@str=''
return@str--返回值
end
GO
删除一个函数语法dropfunctionmy_function
selecttop100*into#temp
fromp_room
select*From#tempa
/*---检查是否存在触发器---*/
ifexists（selectnamefromsysobjectswherename=tri_delete）
droptriggertri_delete
go
/*--在表table2上创建delete触发器---*/
createtriggertri_deleteontable2
fordelete
as
print'开始数据备份.....'
ifnotexists（select*fromsysobjectswherename='table1'）
select*intotable1fromdeleted---从deleted表中获取被删除的数据
else
insertintotable1select*fromdeleted
print'备份成功，备份表中数据为：
'
select*fromtable1
go
/*---测试触发器：
删除数据---*/
SETNOCOUNTON---不显示T—SQL语句影响的行数
deletetable2
升级会员 