SQL基础和高级(语法格式)总结二
上一篇中，我们讲到了SQL基础语句的语法格式以及样例。本文将介绍一些高级的SQL语句语法格式比如说case用法，公用表表达式(with),存储过程，视图等。
一.case表达式
相当于选择结构表达式。
语法格式如下:
case
when 条件1 then 值1
when 条件2 then 值2
...
else 值n
end
示例代码1 纵表转横表
1 create table Score 2 ( 3 学号 nvarchar(10), 4 课程 nvarchar(10), 5 成绩 int 6 ) 7 8 insert into Score values('0001','语文',87); 9 insert into Score values('0001','数学',79); 10 insert into Score values('0001','英语',95); 11 insert into Score values('0002','语文',69); 12 insert into Score values('0002','数学',84); 13 14 select * from Score; 15 16 select 学号, 17 sum(case when 课程 ='数学' then 成绩 end )as '数学' 18 , 19 sum(case when 课程 ='英语' then 成绩 else 0 end )as '英语' 20 , 21 sum(case when 课程 ='语文' then 成绩 end ) as '语文' 22 from Score 23 group by 学号
示例代码2 成绩等级判断
1 select *, 2 case 3 when testBase>=90 then 'A' 4 when testBase>=80 then 'B' 5 when testBase>=70 then 'C' 6 else 'E' 7 end as '等级' 8 from Score where stuID=2; 9 10 11 12 //相当于switch case 13 Select 14 tscoreId, 15 tsid, 16 tenglish, 17 评级= 18 Case tenglish 19 When 100 then ‘满分’ 20 End 21 From TblScore
二.表连接
作用：将多张表变成一张表。包括:交叉连接，内连接，外连接(左右外连接)。
在这里我们先创建joinPerson，joinTitle两个表。
示例代码如下：
1 create table joinPerson 2 ( 3 pId int identity(1,1) not null 4 , pName nvarchar(10) not null 5 , titleId int null 6 ); 7 alter table joinPerson 8 add constraint PK_joinPerson_pId primary key(pId); 9 10 create table joinTitle 11 ( 12 titleId int identity(1,1) not null 13 , titleName varchar(10) not null 14 ); 15 alter table joinTitle 16 add constraint PK_joinTitle_titleId primary key(titleId); 17 18 insert into joinTitle(titleName) values('Teacher'),('Master'); 19 insert into joinPerson(pName, titleId) values('牛亮亮', 1),('苏坤', 2),('杨中科', NULL);
两张表的数据如下：
1.交叉连接:将第一张表的每一行与第二张表匹配一次，得到的结果作为结果。
语法
from 表cross join 表
示例代码如下:
1 如果两章表中有重名的字段，就会出问题，就需要给表加别名 2 3 select t1.pName,t2.titleName 4 from 5 joinPerson as t1 6 cross join 7 joinTitle as t2;
结果如下：
2.内连接:先交叉，在筛选，最后将重要的数据在加回来。
语法
from 表as 别名
inner join
表as 别名
on 别名.列名=别名.列名(条件)
示例代码如下:
1 select * from 2 joinPerson as t1 3 inner join 4 joinTitle as t2 5 on t1.titleId=t2.titleId;
结果如下:
3.左外连接:把左边中的全部记录都显示出来，对于那些在右表中能找到匹配的记录全部显示，不能匹配的显示为null。
1 select * 2 from 3 joinPerson as t1 4 left join 5 joinTitle as t2 6 on t1.titleId=t2.titleId
结果如下:
4.右外连接:把右边中的全部记录都显示出来，对于那些在左表中能找到匹配的记录全部显示，不能匹配的显示为null。
示例代码如下:
1 //先插入一条数据 2 insert into joinTitle(titleName) values('班主任'); 3 4 select 5 * 6 from 7 joinPerson as t1 8 right join 9 joinTitle as t2 10 on t1.titleId = t2.titleId;
结果如下:
三.视图
视图里面存储的是查询语句,就是将查询的语句封装成一个对象，每次查询的时候直接操作这个对象即可。本质是虚拟表,即一个查询过程。
视图是一个虚拟表，存储的只是查询语句，并不是数据。如果把真实表的数据修改之后，视图查询的结果也会随之改变，命名用Vw_开头。那有什么优点呢。
如下：
优点1：降低数据库的复杂程度
优点2：防止未经许可的用户访问敏感数据（就是视图把表隐藏起来，让你看不到表）
语法：
create view vw_视图名(一般名字以vw_开头)
as
select 语句
示例代码如下:
1 create view vw_StuInfo 2 as 3 select 4 ROW_NUMBER() over(order by t1.stuId) as n 5 , t1.stuId 6 , t1.stuName 7 , case t1.stuSex when 'f' then '女' else '男' end as stuSex 8 , datediff(year, t1.stuBirthdate, GETDATE()) as stuAge 9 , t1.stuEmail 10 , t1.stuAddress 11 , t1.stuPhone 12 , t2.className 13 , t3.testBase 14 , t3.testBeyond 15 , t3.testPro 16 from 17 TestDataBase..Student as t1 18 inner join 19 TestDataBase..Course as t2 20 on t1.classId = t2.classId 21 inner join 22 TestDataBase..Score as t3 23 on t1.stuId = t3.stuId 24 ;
四.公用表表达式
语法：
with 别名
as
(
结果集
)
使用别名的一个查询;
示例代码如下:
1 with t 2 as 3 ( 4 select 5 t1.stuId 6 , t1.stuName 7 , case t1.stuSex when 'f' then '女' else '男' end as stuSex 8 , datediff(year, t1.stuBirthdate, GETDATE()) as stuAge 9 , t1.stuEmail 10 , t1.stuAddress 11 , t1.stuPhone 12 , t2.className 13 , t3.testBase 14 , t3.testBeyond 15 , t3.testPro 16 from 17 TestDataBase..Student as t1 18 inner join 19 TestDataBase..Course as t2 20 on t1.classId = t2.classId 21 inner join 22 TestDataBase..Score as t3 23 on t1.stuId = t3.stuId 24 ) 25 select * from t where t.stuName = '濮阳语儿'; 26 27 -- t可以重用 28 -- 自交差 29 -- 生成一个数字表 30 31 select num from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as t(num); 32 33 -- 自交差 10000 34 select 35 t1.num * 10 + t2.num + 1 36 from 37 (select num from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as t(num)) as t1 38 cross join 39 (select num from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as t(num)) as t2 40 ; 41 -- 用公用表表达式 42 with t 43 as 44 ( 45 select num from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as tt(num) 46 ) 47 select 48 t1.num * 1000 + t2.num * 100 + t3.num * 10 + t4.num + 1 as orderId 49 from 50 t as t1 51 cross join 52 t as t2 53 cross join 54 t as t3 55 cross join 56 t as t4 57 order by 58 orderId;
五.事务
最基本的操作以事务为单位
将一个已经完成的数据库操作行为规定为一个事务
特点：
原子性
执行的结果是特征（要么完成，要么失败）
持久性
执行的结果不可逆转
一致性
一旦完成事务，各个版本的结果都一样。
隔离性
事务与事务之间不冲突
示例代码如下：
1 --事务 2 create table bank 3 ( 4 cId char(4) primary key, 5 balance money, --余额 6 ) 7 8 alter table bank 9 add constraint CH_balance check(balance >=10) 10 11 go 12 --delete from bank 13 insert into bank values('0001',1000) 14 insert into bank values('0002',10) 15 go 16 17 select * from bank; 18 -- 0001 -> 0002 1000元 19 20 -- 默认的事务方式——隐式事务 21 update bank set balance=balance - 1000 where cid='0001'; 22 update bank set balance=balance + 1000 where cid='0002'; 23 24 /* 25 手动执行事务 26 开启事务->执行语句->判断满足与否->提交或回滚 27 语法 28 开启 begin transaction 29 提交 commit 30 回滚 rollback 31 */ 32 select @@TRANCOUNT; 33 34 begin transaction 35 36 delete from TestDataBase..Score; 37 delete from TestDataBase..Student; 38 39 40 select * from HeiMa8.Exe3.vw_StuInfo; 41 42 43 rollback 44 45 -- 46 select * from bank; 47 48 begin tran 49 declare @myError int; 50 update bank set balance=balance - 900 where cid='0001'; 51 set @myError = @@ERROR; 52 update bank set balance=balance + 900 where cid='0002'; 53 set @myError += @@ERROR; 54 if @myError > 0 55 rollback 56 else 57 commit 58 ; 59 60 /* try-catch 61 begin try 62 end try 63 begin catch 64 end catch 65 */ 66 67 begin tran 68 begin try 69 update bank set balance=balance - 1000 where cid='0001'; 70 update bank set balance=balance + 1000 where cid='0002'; 71 commit; 72 end try 73 begin catch 74 rollback; 75 end catch 76 ;
五.存储过程
简单的看成数据库中的方法。执行语法：exec 存储过程名字参数;
存储过程的语法
create proc[edure] usp_存储过程名
参数名类型名[=初始值] output
,....
as
begin
脚本(SQL语句)
end
在这里举几个常用的例子。
1.无参数无返回值的存储过程
1 create proc usp_PersonInfo 2 as 3 select * from joinPerson; 4 go 5 exec usp_PersonInfo;
2.有参数的存储过程
1 create proc usp_PersonSearch 2 @PersonName nvarchar(20) 3 as 4 select * from joinPerson where pName=@PersonName; 5 go 6 exec usp_PersonSearch @PersonName='杨中科';
3.带参数默认值的存储过程
1 create proc usp_FenYe 2 @pageIndex int=1, 3 @pageSize int =10 4 as 5 begin 6 select '今天天气还行'; 7 select * from 8 ( 9 select row_number() over(order by stuId) as num,* from 10 Student 11 )as t 12 where t.num between (@pageIndex-1)*@pageSize+1 and @pageIndex * @pageSize; 13 end 14 go 15 16 exec usp_FenYe 2,5; 17 exec usp_FenYe @pageSize=11,@pageIndex=5; --推荐使用
4.有参数有返回值的存储过程
1 create proc usp_Bank 2 @from char(4), 3 @to char(4), 4 @money money, 5 @isSuccess int output 6 as 7 begin 8 begin tran 9 begin try 10 update bank set balance=balance-@money where cid=@from; 11 update bank set balance=balance+@money where cid=@to; 12 commit; 13 set @isSuccess = 1; 14 end try 15 begin catch 16 rollback; 17 set @isSuccess = 0; 18 end catch 19 end 20 go 21 22 declare @isSuccess1 int; 23 24 -- exec Exe3.usp_ZhuanZhangExt '0001', '0002', 500, @isSuccess output; 25 exec usp_Bank 26 @from = '0001', 27 @to = '0002', 28 @money = 500, 29 @isSuccess = @isSuccess1 output; 30 select @isSuccess1; 31 select * from bank;
5.创建一个登陆的存储过程
1 create proc usp_Login 2 @uid varchar(20), 3 @pwd varchar(20), 4 @isOK int output 5 as 6 select @isOK=count(*) from Exe2.LoginTbl 7 where uid=@uid and pwd=@pwd; 8 go 9 10 select * from Exe2.LoginTbl; 11 12 declare @isLogin int; 13 exec usp_Login 'zdp','zdp140',@isOK=@isLogin output; 14 select @isLogin;
