SQL基础和高级(语法格式)总结二

上一篇中，我们讲到了SQL基础语句的语法格式以及样例。本文将介绍一些高级的SQL语句语法格式比如说case用法，公用表表达式(with),存储过程，视图等。

一.case表达式

    相当于选择结构表达式。

   语法格式如下:

   case

      when 条件1 then 值1

      when 条件2 then 值2

      ...

      else  值n

   end 

   示例代码1 纵表转横表

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;

select 学号,
sum(case when 课程 ='数学' then 成绩 end )as '数学'
,
sum(case when 课程 ='英语' then 成绩 else 0 end )as '英语' 
,
sum(case when 课程 ='语文' then 成绩 end ) as '语文'
from Score
group by 学号

   示例代码2 成绩等级判断

select *,
    case
        when testBase>=90 then 'A'
        when testBase>=80 then 'B'
        when testBase>=70 then 'C'
        else 'E'
    end as '等级'
from Score where stuID=2;



//相当于switch case
Select
    tscoreId,
    tsid,
    tenglish,
    评级=
    Case tenglish
        When 100 then ‘满分’
    End
From TblScore

二.表连接

   作用：将多张表变成一张表。包括:交叉连接，内连接，外连接(左右外连接)。

   在这里我们先创建joinPerson，joinTitle两个表。

   示例代码如下：

create table joinPerson
(
    pId int identity(1,1) not null
    , pName nvarchar(10) not null
    , titleId int null
);
alter table joinPerson
add constraint PK_joinPerson_pId primary key(pId);

create table joinTitle
(
    titleId int identity(1,1) not null
    , titleName varchar(10) not null
);
alter table joinTitle
add constraint PK_joinTitle_titleId primary key(titleId);

insert into joinTitle(titleName) values('Teacher'),('Master');
insert into joinPerson(pName, titleId) values('牛亮亮', 1),('苏坤', 2),('杨中科', NULL);

   两张表的数据如下：

   

   1.交叉连接:将第一张表的每一行与第二张表匹配一次，得到的结果作为结果。

    语法

    from 表cross join 表

    示例代码如下:

如果两章表中有重名的字段，就会出问题，就需要给表加别名

select t1.pName,t2.titleName
from 
joinPerson as t1
cross join
joinTitle as t2;

   结果如下：

   

   2.内连接:先交叉，在筛选，最后将重要的数据在加回来。

    语法

    from  表as 别名

    inner join

    表as 别名

    on 别名.列名=别名.列名(条件)

    示例代码如下:

select *  from
joinPerson as t1
inner join
joinTitle as t2
on t1.titleId=t2.titleId;

   结果如下:

   

   3.左外连接:把左边中的全部记录都显示出来，对于那些在右表中能找到匹配的记录全部显示，不能匹配的显示为null。

select *
from 
joinPerson as t1
left join
joinTitle as t2
on t1.titleId=t2.titleId

  结果如下:

  

    4.右外连接:把右边中的全部记录都显示出来，对于那些在左表中能找到匹配的记录全部显示，不能匹配的显示为null。

    示例代码如下:

//先插入一条数据
insert into joinTitle(titleName) values('班主任');

select
    *
from
    joinPerson as t1
    right join
    joinTitle as t2
    on t1.titleId = t2.titleId;

    结果如下:

   

三.视图

    视图里面存储的是查询语句,就是将查询的语句封装成一个对象，每次查询的时候直接操作这个对象即可。本质是虚拟表,即一个查询过程。

视图是一个虚拟表，存储的只是查询语句，并不是数据。如果把真实表的数据修改之后，视图查询的结果也会随之改变，命名用Vw_开头。那有什么优点呢。

    如下：

    优点1：降低数据库的复杂程度

    优点2：防止未经许可的用户访问敏感数据（就是视图把表隐藏起来，让你看不到表）

    语法：

    create view vw_视图名(一般名字以vw_开头)

    as

       select 语句

    示例代码如下:

create view vw_StuInfo
as
    select
        ROW_NUMBER() over(order by t1.stuId) as n
        , t1.stuId
        , t1.stuName
        , case t1.stuSex when 'f' then '女' else '男' end as stuSex
        , datediff(year, t1.stuBirthdate, GETDATE()) as stuAge
        , t1.stuEmail
        , t1.stuAddress
        , t1.stuPhone
        , t2.className
        , t3.testBase
        , t3.testBeyond
        , t3.testPro
    from 
        TestDataBase..Student as t1
        inner join 
        TestDataBase..Course as t2
        on t1.classId = t2.classId
        inner join
        TestDataBase..Score as t3
        on t1.stuId = t3.stuId
;

四.公用表表达式

   语法：

with 别名

    as

    (

       结果集

    )

    使用别名的一个查询;

   示例代码如下:

with t
as
(
    select
        t1.stuId
        , t1.stuName
        , case t1.stuSex when 'f' then '女' else '男' end as stuSex
        , datediff(year, t1.stuBirthdate, GETDATE()) as stuAge
        , t1.stuEmail
        , t1.stuAddress
        , t1.stuPhone
        , t2.className
        , t3.testBase
        , t3.testBeyond
        , t3.testPro
    from 
        TestDataBase..Student as t1
        inner join 
        TestDataBase..Course as t2
        on t1.classId = t2.classId
        inner join
        TestDataBase..Score as t3
        on t1.stuId = t3.stuId
)
select * from t where t.stuName = '濮阳语儿';

-- t可以重用
-- 自交差
-- 生成一个数字表

select num from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as t(num);

-- 自交差 10000
select 
    t1.num * 10 + t2.num + 1
from
    (select num from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as t(num)) as t1
    cross join
    (select num from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as t(num)) as t2
;
-- 用公用表表达式
with t
as
(
    select num from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as tt(num)
)
select
    t1.num * 1000 + t2.num * 100 + t3.num * 10 + t4.num + 1 as orderId
from 
    t as t1
    cross join
    t as t2
    cross join
    t as t3
    cross join
    t as t4
order by
    orderId;

五.事务

最基本的操作以事务为单位

将一个已经完成的数据库操作行为规定为一个事务

特点：

    原子性

       执行的结果是特征（要么完成，要么失败）

    持久性

       执行的结果不可逆转

    一致性

       一旦完成事务，各个版本的结果都一样。

    隔离性

       事务与事务之间不冲突

示例代码如下：

--事务
create table bank
(
    cId char(4) primary key,
    balance money,            --余额
)

alter table bank
add constraint CH_balance check(balance >=10)

go
--delete from bank
insert into bank values('0001',1000)
insert into bank values('0002',10)
go

select * from bank;
-- 0001 -> 0002 1000元

-- 默认的事务方式——隐式事务
update bank set balance=balance - 1000 where cid='0001';
update bank set balance=balance + 1000 where cid='0002';

/*
    手动执行事务
    开启事务->执行语句->判断满足与否->提交或回滚
    语法
    开启    begin transaction 
    提交    commit
    回滚    rollback
*/
select @@TRANCOUNT;

begin transaction 

delete from TestDataBase..Score;
delete from TestDataBase..Student;


select * from HeiMa8.Exe3.vw_StuInfo;


rollback

--
select * from bank;

begin tran
    declare @myError int;
    update bank set balance=balance - 900 where cid='0001';
    set @myError = @@ERROR;
    update bank set balance=balance + 900 where cid='0002';
    set @myError += @@ERROR;
    if @myError > 0
        rollback
    else
        commit
;

/*    try-catch
    begin try
    end try
    begin catch
    end catch
*/

begin tran
    begin try
        update bank set balance=balance - 1000 where cid='0001';
        update bank set balance=balance + 1000 where cid='0002';
        commit;
    end try
    begin catch
        rollback;
    end catch
;

五.存储过程

    简单的看成数据库中的方法。执行语法：exec 存储过程名字参数;

   存储过程的语法

    create proc[edure] usp_存储过程名

    参数名类型名[=初始值] output

    ,....

    as

    begin

       脚本(SQL语句)

    end

   在这里举几个常用的例子。

   1.无参数无返回值的存储过程

create proc usp_PersonInfo
as
    select * from joinPerson;
go
exec usp_PersonInfo;

  2.有参数的存储过程

create proc usp_PersonSearch
@PersonName nvarchar(20)
as
    select * from joinPerson where pName=@PersonName;
go
exec usp_PersonSearch @PersonName='杨中科';

  3.带参数默认值的存储过程

create proc usp_FenYe
@pageIndex int=1,
@pageSize int =10
as
begin
    select '今天天气还行';
    select * from
    (
        select row_number() over(order by stuId) as num,* from
        Student
    )as t
    where t.num between (@pageIndex-1)*@pageSize+1 and @pageIndex * @pageSize;
end
go

exec usp_FenYe 2,5;
exec usp_FenYe @pageSize=11,@pageIndex=5; --推荐使用

  4.有参数有返回值的存储过程

create proc usp_Bank
@from char(4),
@to char(4),
@money money,
@isSuccess int output
as
begin
    begin tran        
        begin try
            update bank set balance=balance-@money where cid=@from;
            update bank set balance=balance+@money where cid=@to;
            commit;
            set @isSuccess = 1;
        end try
        begin catch
            rollback;
            set @isSuccess = 0;
        end catch    
end
go

declare @isSuccess1 int;

-- exec Exe3.usp_ZhuanZhangExt '0001', '0002', 500, @isSuccess output;
exec usp_Bank
    @from = '0001', 
    @to = '0002', 
    @money = 500, 
    @isSuccess = @isSuccess1 output;
select @isSuccess1;
select * from bank;

  5.创建一个登陆的存储过程

create proc usp_Login
@uid varchar(20),
@pwd varchar(20),
@isOK int output
as
    select @isOK=count(*) from Exe2.LoginTbl 
    where uid=@uid and pwd=@pwd;
go

select * from Exe2.LoginTbl;

declare @isLogin int;
exec usp_Login 'zdp','zdp140',@isOK=@isLogin output;
select @isLogin;