sql培训.docx
- 文档编号:29895180
- 上传时间:2023-08-03
- 格式:DOCX
- 页数:14
- 大小:21.96KB
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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- sql 培训
![提示](https://static.bdocx.com/images/bang_tan.gif)