一、什么是存储过程 存储过程类似于C#中的方法，体现了封装的概念，它是SQL语句和控制流语句的预编译集合二、存储过程的优点 1、允许模块化程序设计 2、允许更快的执行 3、减少网络流量 4、可作为安全机制使用三、常用的系统存储过程 所有系统存储过程的名称都以“sp”开头，并存放在master数据库中。 sp_datebase 列出服务器上的所有数据库 sp_helpdb 报告有关指定数据库或所有数据库的信息 sp_renamedb 更改数据库的名称 sp_tables 返回当前环境下可查询的对象的列表 sp_columns 返回某个表列的信息 sp_help 查看某个表的所有信息 sp_helpconstraint 查看某个表的约束 sp_helpindex 查看某个表的索引 sp_stored_procedures 列出当前环境中所有存储过程 sp_password 添加或修改登陆账户的密码 sp_helptext 显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本四、用户自定义的存储过程 1、创建不带参数的存储过程 语法: create proc[edure] 存储过程名 [ {@参数1 数据类型}[= 默认值] [output], { @参数2 数据类型}[=默认值] [output], ................ ]--可选 as SQL语句 示例： use stuDB go /*--检测是否存在：存储过程放在系统表sysobjects中--*/ if exists (select * from sysobjects where name ='proc_stu') drop procedure proc_stu go /*--创建存储过程--*/ create procedure proc_stu as declare @writtenAvg float,@labAvg float --笔试和机试平均分变量 select @writtenAvg=avg(writtenExam),@labAvg=avg(labExam) from stmarks print '笔试平均分：' + convert(varchar(5),@writtenAvg) --int型转换为varchar型 print '机试平均分：' + convert(varchar(5),@labAvg) go /*--调用执行存储过程--*/ exec pro_stu ２、创建带输入参数的存储过程 参数分两种： (1)、输入参数：可以在调用时向存储过程传递参数。 (2)、输出参数：有返回值，输出参数后有‘outpt’标记 语法： create proc[edure] 存储过程名 @参数1 数据类型 [= 默认值] [output], --[output]为可选 @参数2 数据类型 [=默认值] [output], ................ as SQL语句 示例： use stuDB go /*--检测是否存在：存储过程放在系统表sysobjects中--*/ if exists (select * from sysobjects where name ='proc_stu') drop procedure proc_stu go /*--创建存储过程--*/ create procedure proc_stu ＠writtenPass int, --输入参数：笔试及格 @labPass int --输入参数：机试及格 as select * from stuinfo inner join stumarks on stuinfo.stuno=stumarks.stuno where writtenexam <@writtenpass or labexam <@labpass go /*--调用执行存储过程--*/ --假定本次考试机试偏难，机试的及格线定为55分。笔试及格线定为60分 exec pro_stu 60，55 --或这样调用： exec pro_stu @labpass=55,@writtenpass=60 3、创建带输出参数的存储过程 示例： use stuDB go /*--检测是否存在：存储过程放在系统表sysobjects中--*/ if exists (select * from sysobjects where name ='proc_stu') drop procedure proc_stu go /*--创建存储过程--*/ create procedure proc_stu @notpasssum int output --output关键字，否则视为输入参数 ＠writtenPass int=60, --输入参数：笔试及格 @labPass int=60 --输入参数：机试及格 as select * from stuinfo inner join stumarks on stuinfo.stuno=stumarks.stuno where writtenexam <@writtenpass or labexam <@labpass /*--统计并返回没有通过考试的学员人数--*/ select @notpasssum=count(stuno) from stuinfo inner join stumarks on stuinfo.stuno=stumarks.stuno where writtenexam <@writtenpass or labexam <@labpass go /*--调用执行存储过程--*/ declare @sum int --定义变量，用于存放调用存储过程时返回的结果 exec pro_stu @sum output,60 ---调用时也带output关键字，机试及笔试及格线默认为60注意：在传递参数时，参数值必须时根据存储过程里的参数顺序给定值，输出参数必须带关键字output 函数：---函数---1.日期函数---获得今天的日期select getdate()---使用日期函数查询获得今天的日期select date (yy,gedate())这里得到的是年，因为你写的是yy,如果你写MM得到的是月,如果是dd那么就是天数--请使用日期函数查询获得今天的星期select datename(dw,getdate())//这个获得的是你要的星期几，这个获得的就是文字，也就是星期六select datepart(dw,getdate())//这个获得的是你要的第几天，比如星期六，那么就是第七天---请使用日期函数查询获得今天距离过年还有几天（新年2011年二月四号）select datediff(dd,getdate(),'2011-2-4');---请使用日期函数查询获得今天距离过年还有多少周select datediff(ww,getdate,'2011-2-4')----奥运会一共有多少天select * from king select datediff(dd,'2008-8-8','2008-8-24');---奥运后的9个月的日期是多少select dateadd(mm,9,'2008-8-8');select dateadd(mm,4,'05/01/2000');-----今天减去5天是哪天select dateadd(dd,-5,getdate());-----五天前星期几select dataname(dw,dateadd(dd,-5,getdate()));-2、字符串函数--计算字符串函数len,datalength--计算'中国aoyu'的字符数和长度select len('中国aoyu')select datalength('中国aoyu')--计算'china 奥运'的字符数和长度select len('china 奥运')--搜索字符串中的某个字符的位置charindex--找出'脚' 在佛山无影脚??字符串的第几位select charindex('脚','佛山无影脚',)--字符串替代函数replace--将'PLMM' 替换成'kLMM'select replace('PLMM','PL','KL')--将'芙蓉姐姐' 替换成'神仙姐姐'select replace('芙蓉姐姐是芙蓉','芙蓉','神仙')--字符串的左截取和右截取left right--请截出-1234567中的区号select left('0731-1234567',4)--010-55555555select left('010-55555555',3)--020-12345678select right('020-12345678',8)--去空格函数ltrim,rtrim--去掉' 龟〓仙〓波〓动〓拳'的中的空格select ltrim(rtrim(' 龟〓 仙〓波〓动〓拳'))select replace(' 龟〓 仙〓波 〓动〓拳',' ','')---提取字符串substring---将'佛山无影脚'中的'无影'提取出来select substring('佛山无影脚',3,2)---删除并替换stuff---将'佛山无影脚'中的'无影'删除，并替换为'霹雳'select stuff('佛山无影脚',3,2,'霹雳')--函数结合查询--查询8月9号一共产生多少金牌select * from kingselect * from kingwhere datename(dd,getkingdate)=9--查询8月9号中国军团一共产生多少金牌select *from kingwhere country='中国' and datename(dd,getkingdate)=9--奥运会开幕三天后，中国军团获得了多少金牌 自由发挥select * from kingwhere country='中国' and (datename(dd,getkingdate)=9 or datename(dd,getkingdate)=10 or datename(dd,getkingdate)=11)--查询（如果按每个学员入学时间1年半之后学员将毕业）所有学员的毕业日期（用日期函数完成）。use studentselect * from studentinfoselect dateadd(mm,18,stujointime)from studentinfo--查询学生信息表中学员身份证号码第9，10位为‘89’的学员信息（用字符串函数完成）。select * from studentinfowhere substring(stucard,9,2)='89'--查询入职时间超过五年的员工select * from studentinfowhere datediff(yy,stujointime,getdate())>2--查出3月份入职的员工--将所有email为163.net的邮箱改为126.com (尝试完成)select * from teacherinfoupdate teacherinfoset teacheremail=replace(teacheremail,'yahoo','126')一、事务 1、什么是事务 事务是一个不可分割的工作逻辑单元，它包含了一组数据库的操作命令，并且所有命令作为一个整体一起向系统提交或撤销操作请求，即要么都执行，要么都不执行 ２、事务的4个属性 (1)、原子性(Atomicity)：事务是一个完整的操作，事务个元素不可分 (2)、一致性（Consistency）：当事务完成时，数据必须处于一致状态 (3)、隔离性（Isolation）：对数据进行修改的所有并发事务时彼此隔离的，这表明事务必须时独立的，它不应该以任何方式依赖于或影响其他事务 (4)、持久性（Durability）：事务完成后，它对于系统的影响是永久性的 3、创建事务的语法： --开始事务-- begin transaction --定义变量，累计事务执行过程中的错误-- declare @errorSum int set @errorSum=0 --初始化为0，即无错误 --增删改操作--- set @errorSum = @errorSum +@@error --累计是否有错误 ........ --根据是否有错误，确定事务是提交还是撤销-- if(@errorSum=0) commit transaction --提交事务 else rollback transaction --撤销事务 注意：变量@errorSum只对增删改进行操作，查询并不需要 4、什么时候使用事务 当一个操作由多个增删改组成的时候，则应用事务二、索引 1、什么是索引 它是SQL Server编排数据的内部方法，为SQL Server提供一种方法来编排查询数据的路径，相当于字典里的目录 2、索引的分类 (1)、唯一索引：唯一索引不允许两行具有相同的索引值 (2)、主键索引：在数据库关系图中为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型 (3)、聚集索引: 表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能由一个 (4)、非聚集索引：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置,索引中包含指向数据存储位置的指针。可以是多个，小于249个 3、创建索引 语法： create [unique] [clustered | nonclustered] index index_name on table_name (column_name[，column_name]....) [ fillfactor=x ] unique指定唯一索引，可选 clustered、nonclustered指定是聚集索引还是非聚集索引，可选 fillfactor表示填充因子，指定一个0~100的值，该值指示索引页填满的空间所占的百分比 示例： use stuDB go /*--检测是否存在该索引（索引存放在系统表sysindexes中）--*/ if exists(select name from sysindexes where name='ix_stumarks_writtenexam') drop index stumarks.ix_stumarks_writtenexam --删除索引 /*--笔试列创建非聚集索引：填充因子为30%--*/ create nonclustered index ix_stumarks_writtenexam on stumarks(writtenexam) with fillfactor=30 go 3、应该创建索引和不应该创建索引 应该创建索引： 经常以这个列进行查询 经常以这个列进行排序 不应该创建索引： 表的数据量小时，不应创建（会先查索引页，然后再表） 当某列的值经常发生变更（修改数据会影响到索引页） 当该列的值比较单一的情况（比如该列的值为性别 男和女）三、视图 1、什么是视图 视图是另一种查看数据库中一个或多个表中的数据的方法，视图是一种虚拟表 2、语法 create view view_name as <select语句> 注意：视图可以对表进行增删改查， 但建议不对其使用增删改，多用来进行查询
一、简单子查询 简单子查询就是将一个select查询语句作为子部分嵌入到另一个select查询语句中作为其的一部分 示例： select * from stuInfo where stuAge >（select stuAge from stuInfo where stuName =’李斯文‘） 其中（select stuAge from stuInfo where stuName =’李斯文‘）就是子查询 注意：简单子查询的结果只能是一行一列二、in 和 not in子查询 in 和not in子查询的结果是多行一列 子查询可返回多行记录 示例： select stuName from stuInfo where stuNo in （select stuNo from stuMarks where writtenExam=60）三、exists和not exists子查询 exists子查询一般用于if语句的存在检测，基本语法如下 if exists（子查询） 语句 如果子查询的结果非空，则exists（子查询）将返回真（TRUE），否则返回假（FALSE） not 取反 表示不存在
