use MyFirstDB;-- 主要内容-- SQL Server 实用脚本-- 1、case语句-- 2、子查询-- 3、连接查询-- 4、脚本变量与流程控制(选择与循环等)-- 5、事务-- 6、存储过程-- 7、触发器----------------------------------- case 语句-- 类似于C#中的三元表达式，好比n元表达式-- 语法-- 1）写在哪里？ 在sql语句中需要值（标量）的地方-- 2）-- if-else结构/*casewhen 条件then 值when 条件then 值...else 默认值end*/selectstuName, casewhen stuSex=‘m‘ then ‘男‘when stuSex=‘f‘ then ‘女‘    else ‘其他‘end as 性别, datediff(YEAR, stuBirthdate, GETDATE()) as 年龄--,*fromTestDataBase..StudentwherestuId<20;---- switch-case结构/*case 字段when 值then 结果when 值 then 结果...else 默认值end*/selectDATEDIFF(YEAR, stuBirthdate, GETDATE()) as 年龄,case stuSexwhen ‘m‘ then ‘男‘when ‘f‘ then ‘女‘    else ‘其他‘end as 性别, *fromTestDataBase..StudentwherestuId<20;-- 练习create table test(number varchar(10),amount int)insert into test(number,amount) values(‘RK1‘,10)insert into test(number,amount) values(‘RK2‘,20)insert into test(number,amount) values(‘RK3‘,-30)insert into test(number,amount) values(‘RK4‘,-10)--select * from test;select number, ‘收入‘, ‘支出‘ from test;selectnumber, case when amount>=0 then amount else 0 end as ‘收入‘, case when amount<0 then abs(amount) else 0 end as ‘支出‘from Test-------------------------------------create table Score(学号 nvarchar(10),课程 nvarchar(10),成绩 int)insert into Score values(‘0001‘,‘语文‘,87);insert into Score values(‘0001‘,‘数学‘,79);insert into Score values(‘0001‘,‘英语‘,95);insert into Score values(‘0002‘,‘语文‘,69);insert into Score values(‘0002‘,‘数学‘,84);--select * from Score;-- 表的透视变换ANSI-SQL的做法select学号, sum(case when 课程=‘语文‘ then 成绩 else 0 end) as ‘语文‘, sum(case when 课程=‘数学‘ then 成绩 else 0 end) as ‘数学‘, sum(case when 课程=‘英语‘ then 成绩 else 0 end) as ‘英语‘fromScoregroup by学号-- T-SQL2008 透视变换与你透视变换PIvote UnPivote------------------------------ 2、子查询-- 什么是子查询？-- 在一个查询中，一个查询的结果作为另一个查询的条件，那么这个--  查询称为子查询，这个使用条件的查询称为外部查询-- 查询“纪明杰”的考试成绩select stuId from TestDataBase..Student where stuName=‘纪明杰‘;select * from TestDataBase..Score where stuId=1;select * from TestDataBase..Score where stuId =(select top 1 stuId from TestDataBase..Student where stuName=‘纪明杰‘);-- 查询“濮阳语儿”-- select * from TestDataBase..Student order by stuId;select * from TestDataBase..Score where stuId in(select stuId from TestDataBase..Student where stuName=‘濮阳语儿‘);-- 标量子查询与多值子查询-- 独立子查询-- 相关子查询-- 查询"纪明杰"的考试平均分(三个科目的综合求avg)selectstuName, (select avg(testBase+testBeyond+testPro)from TestDataBase..Scorewhere stuId = t.stuId -- 此时子查询需要使用外部查询的stuId)‘分数‘ -- 只需要放至id号位纪明杰同学的分数即可fromTestDataBase..Student as twherestuName=‘濮阳语儿‘;-- 3、连接查询(表连接)-- 为什么要使用这个，这个是什么-- 就是将多张表合并成一张表-- 查询"纪明杰"的信息-- 个人信息(Student)、分数信息(Score)、课程信息(Course)selectt1.*, t2.className, t2.classDescription, t3.testBase,t3.testBeyond,t3.testProfromTestDataBase..Student as t1inner joinTestDataBase..Course as t2on t1.classId = t2.classIdinner Join(selectstuId, avg(testBase) as TestBase, AVG(testBeyond) as testBeyond, avg(testPro) as testProfromTestDataBase..Scoregroup bystuId) as t3on t1.stuId = t3.stuIdwheret1.stuName = ‘纪明杰‘;-- 连接操作的分类：交叉连接、内链接、外连接(左外、右外、全连接)--select * into TestDataBase..Employee from(values (1, ‘马伦‘, 2),(2, ‘赵晓虎‘, 1),(3, ‘杨中科‘, null)) as Employee(empId, empName, titleId);select * into TestDataBase..Title from(values(1, ‘讲师‘), (2, ‘学科负责人‘)) as title(titleId, titleName);select * from TestDataBase..Employee;select * from TestDataBase..Title;select * fromTestDataBase..Employeecross joinTestDataBase..Title;select * from(select * from(values (1, ‘马伦‘, 2),(2, ‘赵晓虎‘, 1),(3, ‘杨中科‘, null)) as Employee(empId, empName, titleId)) as t1cross join(select * from(values(1, ‘讲师‘), (2, ‘学科负责人‘)) as title(titleId, titleName)) as t2;-------------------------------- numTbl(num)  1-10000create table numTbl(num int not null);insert into numTbl(num) values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);select * from numTbl;selectt1.num + t2.num * 10 + t3.num*100 + t4.num * 1000 + t5.num*10000 + 1 as nfromnumTbl as t1cross joinnumTbl as t2cross joinnumTbl as t3cross joinnumTbl as t4cross joinnumTbl as t5order bynselect * from sys.objects;-- 内链接的实现use TestDataBase;select stuId, stuName from Student;select * from Course;select t1.stuId, t1.stuName, t2.className fromStudent as t1inner joinCourse as t2on t1.classId = t2.classIdorder byt1.stuId;-- 语法注意-- ANSI-SQL 89select * from Employee, Title;select * from Employee t1, Title t2 where t1.titleId=t2.titleId;-- ANSI-SQL 92-- 外连接select * fromEmployee as t1full joinTitle as t2on t1.titleId = t2.titleId;-- 4、表表达式-- 派生表、公用表表达式(CTE)、视图（、内联表值函数）-- 派生表就是由一个查询得到的结果集，作为数据源被查询，这个结果集就是派生表/*select*from -- 学生的完整信息(select * from ...) as twheret.stuName=‘濮阳语儿‘;*/-- 使用派生表实现分页select * from TestDataBase..Student order by stuId;-- 第一页，每页十条select top 10 * from Student order by stuId;-- 第二页，每页十条(11-20)-- 第三页(21-30)select top 10*fromStudentwherestuId not in(select top ((@pageIndex-1) * @pageCount) stuId from Student order by stuId)order bystuId;-- between andselect * from Student where stuId between 1 and 10;select * from Student where stuId between 11 and 20;select * from Student where stuId between 21 and 30;select * from Student where stuId between (@pageIndex - 1) * @pageCount + 1 and @pageIndex * @pageCount;-- 显示第n也，每页显示m条记录select * from( -- 派生表selectROW_NUMBER() over(order by stuId) as num, *fromStudent) as twheret.num between (n-1)*m + 1 and n * m;-- 公用表表达式(CTE common table expression)-- 为什么要有这个东西--with 别名--as--(-- 结果集--)--查询with tas(selectROW_NUMBER() over(order by stuId) as num, stuName, stuSex, stuBirthdate, stuStudydate, stuAddress, stuEmail, stuPhone, stuIsDel, stuInputtime, classIdfromStudentwherestuIsDel = 0)selectt.num as 编号, t.stuName as 姓名, datediff(year, t.stuBirthdate, getdate()) as 年龄, convert(varchar, t.stuStudydate, 111) as 入学时间, t.stuEmail as 电子邮件fromtwheret.num between (3-1)*10 + 1 and 3 * 10;-- 视图(view)-- 为什么要使用视图-- 视图的本质，是对系统中具体的物理表的复杂查询的一个映射，其本质还是一个查询-- 可以认为是一个查询的别名/*create view 架构.vw_视图名as结果集*/gocreate schema pub authorization dbo;gocreate view pub.vw_StuInfoasselectROW_NUMBER() over(order by stuId) as num, stuName, stuSex, stuBirthdate, stuStudydate, stuAddress, stuEmail, stuPhone, stuIsDel, stuInputtime, className, classDescriptionfromStudent as t1inner joinCourse as t2on t1.classId = t2.classIdwheret1.stuIsDel = 0;goselect * from vw_StuInfo where num between 11 and 20;-- 在使用非常复杂的sql语句查询数据的时候-- 将查询进行打包(记录的sql语句，是结果集)，取了一个名字-- 在查询的时候就不需要使用非常复杂的sql语句了-- 直接使用这个结果集select * from pub.vw_StuInfo;-- 5、脚本变量与流程控制(选择与循环等)-- 变量的使用(先声明，在赋值，后使用)declare @varible varchar(10); -- 声明变量-- set @varible = ‘测试数据‘; -- 赋值-- set @varible = (select ‘测试的数据‘); -- 标量子查询赋值select @varible=stuName from Student where stuId = 3;select @varible; -- 使用变量-- 选择结构if ()begin语句endelse if ()beginendelsebeginend-- 循环结构while(表达式)beginend-- 求到中所有偶数的和declare @sum int;declare @i int;select @sum=0, @i=0;while @i <= 100beginif (@i % 2 = 0)beginset @sum = @sum + @i;endset @i = @i + 1;endselect @sum;-- 6、事务--事务create table bank(cId char(4) primary key,balance money --余额)alter table bankadd constraint CH_balance check(balance >=10)go--delete from bankinsert into bank values(‘0001‘,1000)insert into bank values(‘0002‘,10)goselect * from bank;update bank set balance=balance - 1000 where cid=‘0001‘update bank set balance=balance + 1000 where cid=‘0002‘-- 如何使用事务？-- 在SQL Server中每一条sql语句默认为一个事务insert into Employee(empId, empName, titleId) values(4, ‘赵晓雪‘, null),(5, ‘赵晓飞雪‘, null),(6, ‘赵大雪‘, null)-- 原子性-- 手动使用事务begin transaction写代码-- 提交事务，让这里的代码全部生效commit transaction-- 回滚事务，这里所有的操作无效rollback transactionbegin transactiondelete from Score where Score.testId > 1;delete from Student where stuId > 1;delete from Course where classId > 1;rollback transactionselect * from Student;select * from Course;select * from Score;----------------------- @变量名 自定义变量-- @@系统变量@@error -- 记录最近一次sql语句执行的状态码，如果大于表示这条有错误select @@ERROR;begin transactiondeclare @myError int;set @myError = 0;update bank set balance=balance - 500 where cid=‘0001‘set @myError = @myError + @@ERROR;update bank set balance=balance + 500 where cid=‘0002‘set @myError = @myError + @@ERROR;if @myError > 0beginrollback transactionendelsebegincommit transactionendselect * from bank;begin transactionbegin tryupdate bank set balance=balance + 600 where cid=‘0001‘update bank set balance=balance - 600 where cid=‘0002‘commit transactionend trybegin catchrollback transactionend catch-- 事务的特征：原子性、持久性、隔离性、一致性-- 隔离级别、快照、锁定排查-- 7、存储过程-- 为什么需要存储过程？-- 如何使用？(存储过程好比C#中的方法)use master;exec sp_detach_db ‘mfb‘;CREATE DATABASE Archive      ON (FILENAME = ‘d:\db\hei14\MyFirstDB.mdf‘)      FOR ATTACH ;GOexec sp_renamedb ‘MyFirstDB‘, ‘mfb‘-- 存储过程语法  usp_--create procedure 存储过程的名字--@变量类型[= 默认值] [output]--,@变量类型[= 默认值] [output]--,@变量类型[= 默认值] [output]--as--begin-- 。。。--end-- 转账，每次转元gouse TestDataBase;gocreate proc usp_转账asbeginbegin transactionbegin tryupdate bank set balance=balance - 100 where cid=‘0001‘update bank set balance=balance + 100 where cid=‘0002‘commit transactionend trybegin catchrollback transactionend catchend;goexec usp_转账;select * from bank;gocreate proc usp_转账@from char(4), @to char(4), @money moneyasbeginbegin transactionbegin tryupdate bank set balance=balance - @money where cid=@fromupdate bank set balance=balance + @money where cid=@tocommit transactionend trybegin catchrollback transactionend catchend;go-- 调用带有参数的存储过程exec usp_转账 ‘0001‘, ‘0002‘, 200.00exec usp_转账 @to=‘0001‘, @from=‘0002‘, @money=1000select * from bank;gocreate proc usp_转账@from char(4), @to char(4), @money money, @isSuccess int outputasbeginbegin transactionbegin tryupdate bank set balance=balance - @money where cid=@fromupdate bank set balance=balance + @money where cid=@tocommit transactionset @isSuccess = 1;end trybegin catchrollback transactionset @isSuccess = 0;end catchend;go-- 需要一个变量declare @res int-- exec usp_转账‘0001‘, ‘0002‘, 500, @res output;exec usp_转账 @isSuccess=@res output, @to=‘0001‘, @from=‘0002‘, @money=500select @res;select * from bank;-- 使用C#执行存储过程的步骤：-- 1、将sql语句变成存储过程的名字-- 2、将SqlCommand对象的CommandType设置为StoredProcedureexec sp_help;-- 8、触发器-- 什么是?-- 是存储过程，类似于c#中的事件-- 什么时候使用？-- 跨数据库表等对象所做的验证-- 监视数据变动-- 语法?-- 触发器的分类：after触发器、instead of触发器-- 针对增删改操作进行触法-- 语法/*create trigger tr_触发器名字on 表名after | instead ofupdate | delete | insertasbegin代码end*/-- inserted表deleted表-- 是在增加数据、删除数据或修改数据的过程中临时创建的表-- 如何得到刚刚插入数据的id号select * from Employee;insert into Employee(empName) values(‘赵晓虎‘);insert into Employee(empName) output inserted.* values(‘赵晓‘);gocreate trigger tr_永远删除不了的数据 on Employeeafterdeleteas-- 将删除的数据插入回来insert into Employee(empName, titleId)select empName, titleId from deleted;godelete from Employee;truncate table Employee;select SUSER_SNAME();gocreate trigger tr_watch_insert on Employeeafterinsertasbegindeclare @res varchar(500);set @res = ‘用户‘‘‘ + suser_name() + ‘‘‘添加数据:‘;set @res = @res + (select empName from inserted);print @res;endselect * from Employee;insert into Employee(empName) values(‘wcw‘);-- 什么是?-- 干什么用?-- 语法