sql高级

-- 主要内容
-- SQL Server 实用脚本
-- 1、case语句
-- 2、子查询
-- 3、连接查询
-- 4、表表达式
-- 5、变量与流程控制(选择与循环等)
-- 6、事务
-- 7、存储过程
-- 8、触发器
1、case语句
-- if-else结构
/*
	case 
		when 条件 then 值
		when 条件 then 值
		...
		else 默认值
	end
*/
-- switch-case结构
/*
	case 字段
		when 值1 then 结果1
		when 值2 then 结果2
		...
		else 默认值
	end
*/
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的做法(-- T-SQL2008 透视变换与你透视变换PIvote UnPivote)
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 '英语'
from 
	Score
group by
	学号
	
	
2、子查询：在一个查询中，一个查询的结果作为另一个查询的条件，
           那么这个查询称为子查询，这个使用条件的查询称为外部查询
	-->独立子查询：可以单独执行的，标量子查询和多值子查询就是独立子查询
			-->标量子查询：返回一个值（一般加上top 1 关键字）
			   select * from TestDataBase..Score where stuId = 
			  (select top 1 stuId from TestDataBase..Student where stuName='纪明杰');
			-->多值子查询：返回多个值（in）
			   select * from TestDataBase..Score where stuId in 
			  (select stuId from TestDataBase..Student where stuName='濮阳语儿');
	-->相关子查询
		select 
		stuName
		, (
			select avg(testBase+testBeyond+testPro)
			from TestDataBase..Score 
			where stuId = t.stuId -- 此时子查询需要使用外部查询的stuId
		)'分数'
		from
			TestDataBase..Student as t
		where
			stuName='濮阳语儿';
3、连接查询（表连接：将多张表合并成一张表）
    -->交叉连接：cross join 计算笛卡尔集
	-->内连接：  inner join 在交叉连接的基础上筛选得到的结果
	-->外连接：在内连接的基础上找回部分数据
		     -->左外连接：left  join
		     -->右外连接：right join 
		     -->全连接：full join
	/*
	   ANSI-SQL 89的写法
	   select * from Employee,Title     --交叉连接
	   select * from Employee,Title on Employee.TitleId=Title.TitleId  --内连接
	 */
4、表表达式
    -->派生表:就是由一个查询得到的结果集，作为数据源被查询，这个结果集就是派生表
		select
			*
		from
			(select * from Stuent) as t
		where
			t.stuName='濮阳语儿';
	-->公用表表达式(CTE common table expression):
	/*
	with 别名([列名],[列名][列名],...)
	as
	(
		结果集
	)
	查询
	*/
	with sales_CTE(salesPersonId,numberOfOrders) --每个经理报告的雇员的数目。
	as
	(
		select salesPersonId,count(*)
		from sales.salesOrderHeader
		where salesPersonId is not null
		group by salesPersonId
	)
	select salesPersonId,numberOfOrders
	from sales_CTE
	order by salesPersonId
	-->视图：视图的本质，是对系统中具体的物理表的复杂查询的一个映射，
	     --> 其本质还是一个查询,可以认为是一个查询的别名
	/*
	create view 架构.v_视图名
	as
		结果集
	*/
5、变量与流程控制
    --> 变量的使用(先声明，在赋值，后使用)
	declare @varible varchar(10);	-- 声明变量
	set @varible = '测试数据'; -- 赋值
	set @varible = (select '测试的数据'); -- 标量子查询赋值
	select @varible=stuName from Student where stuId = 3;
	--> if..else..
	/*
		if(表达式)
			begin
				...
			end
		else
			bein
				...
			end
	*/
	-->循环结构
	/*
		while(表达式)
		begin
			../
			[break]|[contine];
		end
	*/
6、事务：事务是单个的工作单元。如果某一事务成功，则在该事务中进行的所有数据修改均会提交，成为数据库中的永久组成部分。如果事务遇到错误且必须取消或回滚，则所有数据修改均被清除。
	-->在SQL Server中每一条sql语句默认为一个事务(下面的语句有一条执行失败，则全部插入失败，因为一条sql就是默认为一个事务)
	insert into Employee(empId, empName, titleId) values
	(4, '赵晓雪', null),
	(5, '赵晓飞雪', null),
	(6, '赵大雪', null)
	-->手动使用事务
	begin transaction
	-->提交事务，让这里的代码全部生效
	commit transaction
	-->回滚事务，这里所有的操作无效
	rollback transaction
	

	begin transaction  --@@error 记录最近一次sql语句执行的状态码，如果大于0表示这条有错误
	declare @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 > 0
	begin
		rollback transaction
	end
	else
	begin
		commit transaction
	end
	-->事务的特征：
	-->原子性(Atomicity)：原子性意味着数据库中的事务执行是作为原子。即不可再分，整个语句要么执行，要么不执行。
	-->持久性(Durability)：事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。
	-->隔离性(Isolation)：事务的执行是互不干扰的，一个事务不可能看到其他事务运行时，中间某一时刻的数据。
	-->一致性(Consistency)：事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
	begin transaction
	begin try
		update bank set balance=balance + 600 where cid='0001'
		update bank set balance=balance - 600 where cid='0002'
		commit transaction
	end try
	begin catch
		rollback transaction
	end catch
7、存储过程：类似于C#里面的方法
8、触发器：是存储过程，类似于C#里面的事件（约束只能在字段上使用，触发器可以跨表）