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

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

一.case表达式

    相当于选择结构表达式。

   语法格式如下:

   case

      when 条件1 then 值1

      when 条件2 then 值2

      ...

      else  值n

   end 

   示例代码1 纵表转横表

SQL基础和高级(语法格式)总结二
 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 成绩等级判断

SQL基础和高级(语法格式)总结二
 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两个表。

   示例代码如下:

SQL基础和高级(语法格式)总结二
 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);
View Code

   两张表的数据如下:

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

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

    语法

    from 表cross join 表

    示例代码如下:

SQL基础和高级(语法格式)总结二
1 如果两章表中有重名的字段,就会出问题,就需要给表加别名
2 
3 select t1.pName,t2.titleName
4 from 
5 joinPerson as t1
6 cross join
7 joinTitle as t2;
View Code

   结果如下:

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

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

    语法

    from  表as 别名

    inner join

    表as 别名

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

    示例代码如下:

SQL基础和高级(语法格式)总结二
1 select *  from
2 joinPerson as t1
3 inner join
4 joinTitle as t2
5 on t1.titleId=t2.titleId;
View Code

   结果如下:

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

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

SQL基础和高级(语法格式)总结二
1 select *
2 from 
3 joinPerson as t1
4 left join
5 joinTitle as t2
6 on t1.titleId=t2.titleId
View Code

  结果如下:

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

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

    示例代码如下:

SQL基础和高级(语法格式)总结二
 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;
View Code

    结果如下:

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

三.视图

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

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

    如下:

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

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

    语法:

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

    as

       select 语句

    示例代码如下:

SQL基础和高级(语法格式)总结二
 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 ;
View Code

四.公用表表达式

   语法

with 别名

    as

    (

       结果集

    )

    使用别名的一个查询;

   示例代码如下:

SQL基础和高级(语法格式)总结二
 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;
View Code

五.事务

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

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

特点:

    原子性

       执行的结果是特征(要么完成,要么失败)

    持久性

       执行的结果不可逆转

    一致性

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

    隔离性

       事务与事务之间不冲突

示例代码如下:

SQL基础和高级(语法格式)总结二
 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 ;
View Code

五.存储过程

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

   存储过程的语法

    create proc[edure] usp_存储过程名

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

    ,....

    as

    begin

       脚本(SQL语句)

    end

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

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

SQL基础和高级(语法格式)总结二
1 create proc usp_PersonInfo
2 as
3     select * from joinPerson;
4 go
5 exec usp_PersonInfo;
View Code

  2.有参数的存储过程

SQL基础和高级(语法格式)总结二
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=杨中科;
View Code

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

SQL基础和高级(语法格式)总结二
 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; --推荐使用
View Code

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

SQL基础和高级(语法格式)总结二
 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;
View Code

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

SQL基础和高级(语法格式)总结二
 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;
View Code

SQL基础和高级(语法格式)总结二,布布扣,bubuko.com

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

上一篇:SqlException 类


下一篇:Oracle 10g 创建 DB link