-- 主要内容
-- 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#里面的事件(约束只能在字段上使用,触发器可以跨表)
sql高级
最新推荐文章于 2024-05-09 18:02:16 发布