SQL 进阶

CASE 函数：相当于Switch语句

　　用法1：用来判断列的单个值

　　CASE expression

　　WHEN value1 THEN returnvalue1

　　WHEN value2 THEN returnvalue2

　　....

　　ELSE defaultreturnvalue

　　END

　　用法2：用来判断列的取值范围

　　CASE

　　WHEN condition1 THEN returnvalue1

　　WHEN condition2 THEN returnvalue2

　　...

　　ELSE defaultreturnvalue

　　END

　　注：then的值必须是同一种类型

--case 第一种用法，用来为列的单个值

select uid,name,case level

when 1 then '骨灰'

when 2 then '大虾'

when 3 then '菜鸟'

else '默认'

end

from [user]

--case 第二种用法，用来为列的取值范围

select SId,成绩=(

case

when english between 90 and 100 then 'A'

when english between 80 and 89 then 'B'

when english between 70 and 79 then 'C'

when english between 60 and 69 then 'D'

when english < 60 then 'E'

else '缺考'

end

)

any all 的用法 --匹配结果集使用

　　in/any/all匹配结果集使用时，结果集只能有一个列

　　select * from phoneNum where pTypeId = all(select ptId form phoneType)

　　　　==select * from phontNum where pTypeId = 1 or pTypeId = 2....

　　any关键字必须和其它比较运算符共同使用,而且必须将比较运算符放在any关键字之前,所比较的值需要匹配子查询中的任意一个值

　　即 =ANY 等价于 in 运算符,还可以使用>,<,>=,<=等

　　　　注:ANY运算符与in不同,ANY不能与固定的集合相匹配

　　select * from phoneNum where pTypeId = any(select ptId form phoneType)

　　　　==select * from phontNum where pTypeId = 1 and pTypeId = 2....

　　ALL运算符要求比较的值需要匹配子查询中的所有值,使用方式与ANY相同

EXISTS的用法:

　　1.先执行的是主查询，然后再执行子查询，将匹配的结果显示出来

　　　　（即看子查询中是否有结果，若有就表示条件满足）

　　select * from phoneNum where exists (select * from phoneType where phoneNum.pTypeId = phoneType.ptId)

　　2.判断结果集是否为空

　　if exists(select * from phoneType where ptId = -1)

　　　　select 1

　　else

　　　　select 2

　　EXISTS运算符与in,any,all不同,exists是单目运算符,它不与列匹配

　　EXISTS运算符用来检查每一行是否匹配子查询,可以认为exists就是用来测试子查询的结果是否为空的,如果为空,则不显示

--exists 用法

--1.先执行的是主查询,然后再执行子查询,将匹配行的数据显示出来

select * from PhoneNum where exists(select pCellPhone from PhoneType where PhoneNum.pTypeId=PhoneType.ptId)

select * from PhoneNum where not exists(select * from PhoneType where PhoneNum.pTypeId=PhoneType.ptId)

--2.判断结果集是否为null

if exists(select * from PhoneType where ptId=-1)

select 1

else

select 2

数据库中的分页

　　Row_Number() over (order by Id) --MSSqlServer自带函数根据id的升序生成了一列新的连续的序号

　　select * from (select Row_Number() over (order by Id) as rnum , * from Area) as t where rnum between 10 and 14

--ROW_NUMBER() over (order by ar_id) 根据ar_id的升序生成了一列新的连续的序号

select * from(

select ROW_NUMBER() over (order by ar_id) as rnum,* from Area

) as t

where rnum between 10 and 14--按照ROW_NUMBER()生成的序号来分页

Join 表连接 --join必须跟在from后面

　　inner join 内连接，只会将两张表中符合on条件的行拼接显示出来

　　left (outer) join 左(外)连接，左边表的行全部显示，右表符合条件的显示，不符合条件的显示null

　　right join 右连接 --显示右边表的所有记录

　　full join 全连接 --不管是否满足条件，两边表的记录都全部显示

　　cross join 交叉连接 --两张表的乘积

　　　　没有 WHERE 子句的交叉联接将产生联接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。

视图 - view

　　它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上

优点：

　　筛选表中的行

　　防止未经许可的用户访问敏感数据

　　降低数据库的复杂程度

创建视图：

　　create view vw_student as select * from studnet inner join class on studnet.cid = class.id

　　视图中的查询不能使用order by ，除非指定了top语句

　　create view vw_tab as select top 100 percent * from tab order by col1 desc

　　　　对视图做修改操作时,只能针对一个基表的列,不能同时更新或新增多个基表列

　　　　不提倡对视图新更新新增操作,因为视图的主要目的是查询

--创建视图,显示学员和学员所在班级

create view vw_StusInfo -- alter -- drop

select top 50 percent s.id,s.name as stuName ,c.Name as className,s.age

from Student as s

inner join Class as c

on s.cid = c .Id

order by stuName asc

变量:

　　局部变量: --用户自定义

　　声明:

　　　　DECLARE @name int

　　　　declare @age int

　　赋值:

　　　　1. set @age = 29 --直接赋值

　　　　2. select @age = 29 --查询赋值(如果表数据出现多行,则取最后一行)

　　　　　　select @age = age from Studnet where id = 18

　　　　print @age --显示消息选项卡中显示信息,一次只能输出一个值

　　　　注:使用变量时,要先赋值

　　局变量: --系统变量

　　　　全局变量必须以标记@@作为前缀,如@@version

　　　　全局变量由系统定义,用户不能定义,并且只能读取

　　@@ERROR 最后一个T-SQL错误的错误号

　　@@IDENTITY 最后一次插入的标识值

　　@@LANGUAGE 当前使用的语言的名称

　　@@MAX_CONNECTIONS 可以创建的同时连接的最大数目

　　@@ROWCOUNT 受上一个SQL语句影响的行数

　　@@SERVERNAME 本地服务器的名称

　　@@TRANSCOUNT 当前连接打开的事务数

　　@@VERSION SQL Server的版本信息

IF(条件表达式)

　　BEGIN --相当于C#里的{

　　　　句1

　　　　……

　　END --相当于C#里的}

　　ELSE

　　BEGIN

　　　　语句1

　　　　……

　　END

declare @age int

set @age = 12

if(@age>18)

begin--{

select '成年'

end --}

else

begin

select '未成年'

end

WHILE(条件表达式)

　　BEGIN --相当于C#里的{

　　　　语句

　　　　 ……

　　continue --退出本次循环

　　BREAK --退出整个循环

　　END --相当于C#里的}

--计算1-100之间所有奇数的和

declare @num int,@sum int

select @num=1,@sum=0

while(@num<=100)

begin

set @sum=@sum+@num

set @num=@num+2

--break;

--continue;

end

select @sum

事务的ACID特性：

　　事务是作为单个逻辑工作单元执行的一系列操作。一个逻辑工作单元必须有四个属性，称为原子性、一致性、隔离性和持久性 (ACID) 属性，只有这样才能成为一个事务。

　　原子性

　　　　事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。

　　一致性

　　　　事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构（如 B 树索引或双向链表）都必须是正确的。

　　隔离

　　　　由并发事务所作的修改必须与任何其他并发事务所作的修改隔离。事务识别数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是第二个事务修改它之后的状态，事务不会识别中间状态的数据。这称为可串行性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同。

　　持久性

　　　　事务完成之后，它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持。

SET IMPLICIT_TRANSACTIONS { ON | OFF }

如果设置为 ON，SET IMPLICIT_TRANSACTIONS 将连接设置为隐式事务模式。如果设置为 OFF，则使连接恢复为自动提交事务模式。

　　begin transactiong --开始事务

　　rollback transaction --回滚事务

　　commit transaction -- 提交事务

--事务用户转账

declare @err int--自定义错误号累计变量

set @err =0--初始值

begin transaction--1.开始事务(提交或回滚事务之前的所有sql语句都属于此事务)

update bank set balance=balance-1000 where cid='0001'

set @err=@err + @@ERROR--1.1累计错误号

update bank set balance=balance + 1000 where cid='0002'

set @err=@err + @@ERROR--1.1累计错误号

if(@err>0)--如果@err大于0，说明有某个SQL语句执行出错，那么就回滚整个事务，已保证完整性

begin

rollback transaction--2.回滚事务(当事务中语句出现异常时，将事务中执行成功的数据还原到执行之前)

print '回滚事务'

end

else

begin

commit transaction--3.提交事务（真正的更新数据）

print '提交事务'

end

存储过程---就像数据库中运行方法(函数)

　　和C#里的方法一样，由存储过程名/存储过程参数组成/可以有返回结果。

　　优点：

　　　　执行速度更快 - 在数据库中保存的存储过程语句都是编译过的

　　　　允许模块化程序设计 - 类似方法的复用

　　　　提高系统安全性 - 防止SQL注入

　　　　减少网络流通量 - 只要传输存储过程的名称

系统存储过程

　　由系统定义，存放在master数据库中

　　名称以“sp_”开头或”xp_”开头,自定义的存储过程可以以usp_开头。

自定义存储过程

　　由用户在自己的数据库中创建的存储过程

定义存储过程的语法:

　　CREATE PROC[EDURE] 存储过程名

　　@参数1 数据类型 = 默认值 OUTPUT, --output表示输出参数

　　@参数n 数据类型 = 默认值 OUTPUT

　　BEGIN

　　　　SQL语句

　　END

　　参数说明：

　　　　参数可选

　　　　参数分为输入参数、输出参数

　　　　输入参数允许有默认值

　　执行存储过程: EXEC 过程名 [参数]

　　　　declare @maxAge int = 0

　　　　exec usp_GetAge @maxAge output --执行存储过程并接收参数

　　当参数有默认值时,执行时可以不传参数,若有多个参数时,默认为按顺序指定值,也可以显示指定为哪个参数赋值

create procedure usp_GetPageList --创建存储过程 usp_GetPageList

@PageIndex int = 1, --存储过程所带的参数

@PageSize int = 5, --不要带declare关键字

@PageCount int output --输出类型的参数

begin --执行代码开始

declare @RowCount float --声明内部变量

select * from (select ROW_NUMBER() over(order by ar_id) as num, * from Ams_Area) as temp

where num between (@PageIndex-1)*@PageSize+1 and @PageIndex*@PageSize

select @RowCount = COUNT(*) from Ams_Area --总行数

set @PageCount = ceiling(@RowCount / @PageSize) --总页数

end

declare @PageCount int --声明要传递的参数

exec usp_GetPageList @pagecount = @PageCount output --执行存储过程

select @PageCount --获取返回参数的值

触发器:

　　自动化操作,减少了手动操作以及出错的机率

　　触发器是一种特殊的存储过程,但是没有参数,主要通过事件触发而执行

创建触发器:

　　CREATE TRIGGER triggerName ON tabelName

　　after(for) --（for与after都表示after触发器）| instead of

　　UPDATE --INSERT --DELETE

　　BEGIN

　　　　..........

　　END

　　触发器的触发时间 :操作执行之前(instead of) 操作之后(after/for)

　　触发器的触发条件: insert/delete/update

　　Insert触发器只有当触发器中的语句执行成功时才会插入成功

DML触发器

　　after触发器(for)、instead of触发器（不支持before触发器）

　　Insert、delete、update（不支持select）

DDL触发器

　　Create table、create database、alter、drop….

inserted 表保存了引发新增触发器的新增数据,只能在触发器中访问,即新的数据

selected 表保存了删除行的记录,即旧的数据

after触发器:

　　在语句执行完毕之后触发

　　按语句触发,而不是影响的行数,无论执行多少行,只触发一次

　　只能建立在常规表上,不能建立在视图和临时表上

　　触发器可以递归执行,最多可达32级

　　update(列)，在update语句触发时，判断某列是否被更新，返回布尔值。

instead of触发器:

　　用来替换原本的操作

　　不会递归触发

　　在约束被检查之前触发

　　可以建在表和视图上（*）

--insert after 触发器

--针对班级表的新增操作触发器

alter TRIGGER tg_Class ON Class

after-- after(操作完成后才调用此触发器) | instead of(操作完成前调用此触发器)

INSERT--UPDATE|INSERT|DELETE

begin

--触发器代码

insert into classbackup (Name,descr,number) select Name,descr,number from inserted

--select * into classbackup from inserted

--select * from inserted--保存了引发新增触发器的新增数据,只能在触发器中访问

insert into Class (Name,Descr,number)

values ('哈佛班4','美国哈佛大学联合培养2',9999)

end

--删除触发器

drop trigger tg_Class2

--2.**********insert instead of 触发器

alter trigger tg_Class_insteadof on Class

instead of--替换新增语句的操作,之后新增操作不再更新到数据表

insert

begin

select * from inserted--因为是在新增数据之前就触发，所以此时的inserted表中没有包含标识列的值

end

insert into Class (Name,Descr,number)

values ('哈佛班5','美国哈佛大学联合培养2',9999)

--3.*********delete after 触发器

create trigger tg_Class_delete_after on Class

after

delete

begin

insert into classbackup (Name,descr,number) select Name,descr,number from deleted

end

delete Class where ID=19

--4.*********update after 触发器

create trigger tg_Class_update_after on class

after

update

begin

select * from inserted--显示了修改之后的新数据

select * from deleted--显示了修改之前的数据

end

update class set Name='哈佛5班' where ID=25

索引:就是某个表的某个列的目录

　　索引必须建立在某个列上

索引分类：

　　聚集索引

　　　　实际数据的物理存储顺序按照集索引的存储顺序排列

　　　　一张表中只能有一个聚集索引。

　　非聚集索引

　　　　索引的存储顺序与实际数据的物理顺序无关。

　　　　可以有多个。

　　是否为唯一索引

　　当为某列建立唯一索引后这列数据就不能有重复。

填充因子:

　　就是指定索引树的每个索引页存放索引项时,默认占用索引页的大小,如果填充因子为60%,那么创建索引页时,每个索引页只存放60的数据,剩下40%预留给将来要增加的索引项使用

局部临时表:

　　create table #tbName(列信息);

　　表名前缀#

　　只在当前会话中有效，不能跨连接访问

　　作用域范围类似C#：

　　如果直接在连接会话中创建的，则当前连接断开后删除，如果是在存储过程中创建的则当前存储过程执行完毕后删除

全局临时表

　　create table ##tbName(列信息);

　　表名前缀##

　　多个会话可共享全局临时表

　　当创建全局临时表的会话断开，并且没有用户正在访问全局临时表时删除

表变量：declare @varT1 table(col1 int,col2 char(2));//存储更小量的数据，比临时表有更多的限制。

　　临时数据都存储在tempdb,当服务重新启动的时候，会重建tempdb.

--局部临时表

create table #tempUsers

(

id int identity(1,1),

name varchar(20)

)

select * from #tempUsers

insert into #tempUsers values('临时表好')

--全局临时表

create table ##tempUsers

(

id int identity(1,1),

name varchar(20)

)

insert into ##tempUsers values('临时表好')

select * from ##tempUsers

posted on 2015-05-10 16:33 阿兴的平凡世界阅读(137) 评论(0) 收藏举报

刷新页面返回顶部

SQL 进阶

导航

公告