SQL Server 学习总结一、变量的使用1.1 变量分类局部变量仅在过程中使用必须以作为前缀如age使用前需先声明DECLARE再赋值全局变量任何时候均可使用必须以作为前缀如version由系统定义和维护只能读取不能修改1.2 局部变量定义与赋值声明语法DECLARE变量名 数据类型赋值方法SET变量名值-- 或SELECT变量名值注意使用SELECT赋值时确保筛选出来的记录只有1条示例查询周景及其学号相邻的学员useSMDB go-- 声明学号变量declarestuIdint,stuNamevarchar(20)setstuName周景-- 查询周景的学号selectstuIdStudentIdfromStudentswhereStudentNamestuNameprintstuId-- 查询与周景学号相邻的学员selectStudentId,StudentName,Gender,StudentIdNofromStudentswhereStudentId(stuId1)orStudentId(stuId-1)1.3 SET 与 SELECT 赋值比较使用场景SETSELECT同时对多个变量赋值不支持支持表达式返回多个值时出错将返回的最后一个值赋给变量表达式未返回值时变量被赋 NULL 值变量保持原值1.4 常用全局变量全局变量说明servername服务器名称versionSQL Server 版本IDENTITY获取最后一条插入数据的标识值error获取最后一条 SQL 语句的执行错误号rowcount获取受影响的行数Max_connections当前数据库允许的最大连接数servicename当前服务的实例名FETCH_STATUS获取游标数据的状态示例全局变量的使用print服务器名称servernameprintSQLServer版本versionselectIDENTITYas标识符printerror-- 获取错误号printrowcount-- 获取受影响行数二、数据类型转换2.1 CONVERT 函数CONVERT(数据类型,表达式,样式)第三个参数样式可省略常用于日期类型转换样式示例102年.月.日、103日/月/年、104日.月.年等2.2 CAST 函数CAST(表达式AS数据类型)2.3 示例对比-- 使用 CAST 转换selectStudentName的出生日期是cast(Birthdayasvarchar(50))as学生信息fromStudentswhereStudentId100005-- 使用 CONVERT 转换带样式selectStudentName的出生日期是convert(varchar(50),Birthday,102)as学生信息fromStudentswhereStudentId1000052.4 常用函数函数说明DATEDIFF(datepart, startdate, enddate)计算两个日期差DAYOFYEAR/DY返回日期差值的天数GETDATE()获取当前时间FLOOR(numeric_expression)返回小于或等于当前值的最大整数三、流程控制语句3.1 分支结构IF-ELSE 语句语法IF(条件)BEGIN-- 语句块1ENDELSEBEGIN-- 语句块2END示例判断软件1班C#考试平均分等级declarecAvgintselectcAvgAVG(CSharp)fromScoreListinnerjoinStudentsonStudents.StudentIdScoreList.StudentIdwhereClassId1printC#平均成绩convert(varchar(20),cAvg)if(cAvg80)print软件1班成绩优秀elseprint软件1班成绩一般CASE-END 语句语法CASEWHEN条件1THEN结果1WHEN条件2THEN结果2...ELSE默认结果END示例学员成绩评比90以上为A80-89为B70-79为C60-69为D60以下为不及格select学员StudentId,总评CASEwhen(CSharpSQLServerDB)/290thenAwhen(CSharpSqlserverDB)/2between80and89thenBwhen(CSharpSqlserverDB)/2between70and79thenCwhen(CSharpSqlserverDB)/2between60and69thenDelse不及格endfromScoreList3.2 循环结构WHILE 语句语法WHILE(条件)BEGIN-- 循环体[BREAK]-- 跳出循环[CONTINUE]-- 继续下一次循环END示例1向表中插入10条数据declarecountintsetcount0while(count10)beginsetcountcount1insertintoS2(name)values(姓名CONVERT(nvarchar(10),count))end示例2把C#成绩不够60的加到60分死循环退出条件declareCSharpint,StuIdintwhile(11)beginselecttop1CSharpCSharp,StuIdStudentIdfromScoreListwhereCSharp60if(CSharp60)updateScoreListsetCSharpCSharp1whereStudentIdStuIdif((selectCOUNT(*)fromScoreListwhereCSharp60)0)breakend3.3 游标Cursor游标用于逐行处理查询结果集主要步骤声明游标DECLARE 游标名 CURSOR FOR SELECT语句打开游标OPEN 游标名提取数据FETCH NEXT FROM 游标名 INTO 变量列表循环处理WHILE FETCH_STATUS 0关闭游标CLOSE 游标名释放游标DEALLOCATE 游标名示例遍历查询成绩大于50的学员信息declareaint;-- 成绩declarebnvarchar(10);-- 姓名declarecint;-- 学号-- 1. 声明游标declareCuresor_mycursorforselectScore,Name,Students.StudentIdfromStudentsinnerjoinScoreListonScoreList.StudentIdStudents.StudentId-- 2. 打开游标openCuresor_my-- 3. 开始提取第一条记录fetchnextFromCuresor_myintoa,b,c-- 4. WHILE循环读取游标whileFETCH_STATUS0beginif(a50)print学号:convert(varchar(10),c) 姓名:b 成绩:convert(varchar(10),a)-- 5. 读取下一条记录fetchnextFromCuresor_myintoa,b,cend-- 6. 游标关闭并释放closeCuresor_mydeallocateCuresor_my四、视图4.1 什么是视图是存储在服务器端的一个查询块是一张虚拟表表示一张表的部分数据或多张表的综合数据其结构和数据建立在对表的查询基础上视图中不存放数据数据存放在原始表中使用方式与普通表完全一致4.2 视图的用途筛选表中的行防止未经许可的用户访问敏感数据降低数据库的复杂程度将多个物理数据库抽象为一个逻辑数据库4.3 创建视图T-SQL语句创建语法CREATEVIEW视图名ASSELECT语句删除视图IFEXISTS(SELECT*FROMsysobjectsWHEREname视图名)DROPVIEW视图名示例创建学生成绩视图useSMDB go-- 判断视图是否存在ifexists(select*fromsysobjectswherenameView_StuScore)dropviewView_StuScore go-- 创建视图createviewView_StuScoreasselectStudents.StudentId,StudentName,ClassName,C# CSharp,SQLDBSqlserverDB,ScoreSum(CSharpSqlserverDB)fromStudentsinnerjoinStudentClassonStudentClass.ClassIdStudents.ClassIdinnerjoinScoreListonScoreList.StudentIdStudents.StudentId go-- 使用视图select*fromView_StuScore4.4 创建视图注意事项视图中可以使用多个表一个视图可以嵌套另一个视图尽量少套用影响性能和维护视图定义中的 SELECT 语句不能包含ORDER BY子句除非配合TOP子句INTO关键字引用临时表或表变量五、存储过程5.1 什么是存储过程预先存储好的 SQL 程序保存在 SQL Server 中通过名称和参数执行可带参数也可返回结果可包含数据操纵语句、变量、逻辑控制语句等5.2 存储过程的优点优点说明执行速度更快预编译减少语法检查和优化时间模块化程序设计一次编写多次调用提高系统安全性隐藏 SQL 逻辑控制访问权限减少网络流通量只需传输参数而非完整 SQL执行流程对比应用程序发送 SQL传输语句 → 语法检查 → 语句优化 → 语句编译 → 语句执行应用程序调用存储过程传输参数 → 语句执行5.3 存储过程的分类类型前缀说明系统存储过程sp_由 SQL Server 创建管理存放在 Master 数据库扩展存储过程xp_使用编程语言如 C#创建以 DLL 形式存在用户自定义存储过程无固定前缀用户在自己的数据库中创建5.4 常用系统存储过程execsp_databases-- 列出所有数据库execsp_helpdb SMDB-- 获取指定数据库的信息execsp_columns Students-- 列出表中的列信息execsp_helpconstraint Students-- 查看约束信息execsp_help Students-- 获取表的详细信息execsp_helpindex Students-- 获取索引信息5.5 自定义无参数存储过程语法CREATEPROCEDURE存储过程名AS-- SQL 语句块示例查询考试成绩和统计分析useSMDB goifexists(select*fromsysobjectswherenameusp_ScoreQuery)dropprocedureusp_ScoreQuery gocreateprocedureusp_ScoreQueryas-- 查询考试成绩selectStudents.StudentAddress,StudentName,ClassName,ScoreSum(CSharpSqlserverDB)fromStudentsinnerjoinScoreListonScoreList.StudentIdStudents.StudentIdinnerjoinStudentClassonStudentClass.ClassIdStudents.ClassIdorderbyScoreSumdesc-- 分析考试信息selectStudentClass.ClassName,C#Avg AVG(CSharp),DBAvgAVG(SqlserverDB)fromScoreListinnerjoinStudentsonStudents.StudentIdScoreList.StudentIdinnerjoinStudentClassonStudentClass.ClassIdStudents.ClassIdgroupbyClassNameorderbyClassName go-- 执行存储过程execusp_ScoreQuery5.6 自定义带输入参数的存储过程语法CREATEPROCEDURE存储过程名 参数1数据类型[默认值],参数2数据类型[默认值]AS-- SQL 语句块示例按自定义及格线查询成绩createprocedureusp_ScoreQuery2CSharpint60,-- C#及格线默认60DBint60-- 数据库及格线默认60asselectStudents.StudentId,StudentName,C# CSharp, DB SqlserverDBfromStudentsinnerjoinScoreListonStudents.StudentIdScoreList.StudentIdwhereCSharpCSharporSqlserverDBDBgo-- 调用方式execusp_ScoreQuery260,65-- 按顺序赋值execusp_ScoreQuery2DB65,CSharp60-- 指定参数名赋值execusp_ScoreQuery2-- 使用默认值execusp_ScoreQuery265-- 第一个参数赋值第二个使用默认值execusp_ScoreQuery2default,65-- 第一个使用默认值第二个赋值5.7 自定义带输出参数的存储过程语法CREATEPROCEDURE存储过程名 输出参数1数据类型 OUTPUT,输出参数2数据类型 OUTPUT,输入参数1数据类型[默认值],输入参数2数据类型[默认值]AS-- SQL 语句块-- 为输出参数赋值SELECT 输出参数 值示例查询成绩并输出缺考人数和不及格人数createprocedureusp_ScoreQuery3ASentCountintoutput,-- 缺考总人数FailedCountintoutput,-- 不及格总人数CSharpint60,DBint60as-- 查询成绩列表selectStudents.StudentId,StudentName,C# CSharp, DB SqlserverDBfromStudentsinnerjoinScoreListonStudents.StudentIdScoreList.StudentIdwhereCSharpCSharporSqlserverDBDB-- 为输出参数赋值selectASentCountCOUNT(*)fromStudentswhereStudentIdnotin(selectStudentIdfromScoreList)selectFailedCountCOUNT(*)fromScoreListwhereCSharpCSharporSqlserverDBDBgo-- 调用带输出参数的存储过程declareASentCountXint,FailedCountintexecusp_ScoreQuery3ASentCountXoutput,FailedCountoutput,65,70select缺考总人数ASentCountX,不及格总数FailedCount5.8 分页存储过程动态SQL示例实现分页查询存储过程createprocedureusp_test3pagesizeint,-- 每页大小currentpageint,-- 当前页码wherevarchar(max),-- 筛选条件totalPageintoutput-- 总页数输出参数asbegindeclaresql1nvarchar(max)-- 查询数据SQLdeclaresql2nvarchar(max)-- 查询总数SQLdeclareoffsetint-- 偏移量Setoffset(currentpage-1)*pagesize-- N 前缀表示 Unicode 字符串setsql1Nselect * from StudentInfo where 11 setsql2Nselect count count(*) from StudentInfo where 11 -- 添加筛选条件if(len(trim(where))0)beginsetsql1wheresetsql2whereend-- 添加排序和分页setsql1Norder by Score offset CAST(offsetasnvarchar)N rows fetch next CAST(pagesizeasnvarchar)N rows onlyend-- 调用declaref1intexecusp_test33,2,,f1output七、扩展存储过程扩展存储过程以xp_开头最常用的是xp_cmdshell用于执行 DOS 命令-- 执行 DOS 命令execxp_cmdshelldir注意xp_cmdshell默认可能未启用需要先启用才能使用。