SQLSERVER–一条SQL语句

下面是示例代码,在发生异常的时候会将异常信息存入日志表中,并继续运行后面的语句.

select语句
语法:select distinct | top 数字 [percent] 字段1 as 别名
,包含字段表达式,函数,常量
from 表或结果集
where 逻辑条件 | 模糊处理 | 范围处理 | null值处理
group by 分组字段
having 筛选条件
order by 排序依据;

如果您有更好的建议,望不吝赐教. 存储过程异常处理示例 复制代码 代码如下: —

— Routine DDL — Note: comments before and after the routine body will

执行流程:
from子句 -> where子句 ->group by子句 ->having子句
->select子句 ->order by子句

not be stored by the server —

DELIMITER $$ CREATE DEFINER=`driveradmin`@`%` PROCEDURE
`Merge_BrandProductKey`() BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION
begin insert into t_runninglog values(default,default,’exception in
MergeBrandProductKey’,concat(@@error_count,’ errors’)); commit; end;
DECLARE CONTINUE HANDLER FOR SQLWARNING begin insert into t_runninglog
values(default,default,’warnings in
MergeBrandProductKey’,concat(@@warning_count,’ warnings’)); commit;
end; insert into t_runninglog values(default,default,’start in
MergeBrandProductKey’,”); commit; — 任务执行主体 开始 — /* — normal
update brandproductkey as bpk, (select bp.brandproductid,
bp.brandproductenname, bp.brandid from brandproduct as bp inner join
(select brandid,brandproductid from brandproductdriverrelation group by
brandid,brandproductid) as bpr on bp.brandid=bpr.brandid and
bp.brandproductid = bpr.brandproductid ) as bpp set
bpk.brandproductid=bpp.brandproductid where bpk.brandproductid = 0 —
and bpk.computertype = 2 — 0 and bpk.brandid = bpp.brandid and
upper(bpk.brandproductkeyname) = upper(replace(bpp.brandproductenname,’
‘,”)); commit; insert into t_runninglog values(default,default,’rule
normal in MergeBrandProductKey’,”); commit; — sony rule 1 — VPCEA37EC
— (VPCEA37EC/B,VPCEA37EC/L,VPCEA37EC/P,VPCEA37EC/W) update
brandproductkey as bpk, (select bp.brandproductid,
bp.brandproductenname, bp.brandid from brandproduct as bp inner join
(select brandid,brandproductid from brandproductdriverrelation group by
brandid,brandproductid) as bpr on bp.brandid=bpr.brandid and
bp.brandproductid = bpr.brandproductid and bp.brandid=60 ) as bpp set
bpk.brandproductid=bpp.brandproductid where bpk.brandproductid = 0 —
and bpk.computertype = 2 — 0 and bpk.brandid = bpp.brandid and
bpp.brandproductenname like concat(bpk.brandproductkeyname,’/%’);
commit; insert into t_runninglog values(default,default,’rule sony 1 in
MergeBrandProductKey’,”); commit; — sony rule 2 — VGN-TZ37N_X —
VGN-TZ37N/X update brandproductkey as bpk, (select bp.brandproductid,
bp.brandproductenname, bp.brandid from brandproduct as bp inner join
(select brandid,brandproductid from brandproductdriverrelation group by
brandid,brandproductid) as bpr on bp.brandid=bpr.brandid and
bp.brandproductid = bpr.brandproductid and bp.brandid=60 ) as bpp set
bpk.brandproductid=bpp.brandproductid where bpk.brandproductid = 0 —
and bpk.computertype = 2 — 0 and bpk.brandid = bpp.brandid and
upper(bpk.brandproductkeyname) =
upper(replace(bpp.brandproductenname,’/’,’_’)); commit; insert into
t_runninglog values(default,default,’rule sony 2 in
MergeBrandProductKey’,”); commit; — lenovo rule 1 — ZHAOYANG E45 —
昭阳E45 update brandproductkey as bpk, (select bp.brandproductid,
bp.brandproductenname, bp.brandid,bpr.driverid from brandproduct as bp
inner join (select brandid,brandproductid,max(driverinfoid) as driverid
from brandproductdriverrelation group by brandid,brandproductid) as bpr
on bp.brandid=bpr.brandid and bp.brandproductid = bpr.brandproductid and
bp.brandid=37 ) as bpp set bpk.brandproductid=bpp.brandproductid where
bpk.brandproductid = 0 — and bpk.computertype = 2 — 0 and bpk.brandid
= bpp.brandid and bpk.brandproductkeyname ” and
instr(bpp.brandproductenname,SUBSTRING_INDEX(bpk.brandproductkeyname,’
‘,-1))0 and bpp.brandproductenname regexp concat(‘^[^\x00-\xff]+’,
SUBSTRING_INDEX(bpk.brandproductkeyname,’ ‘,-1),’$’); commit; insert
into t_runninglog values(default,default,’rule lenovo 1 in
MergeBrandProductKey’,”); commit; — HP rule 1 — HP Compaq 6535s — HP
Compaq 6535s 笔记本电脑 update brandproductkey as bpk, (select
bp.brandproductid, bp.brandproductenname, bp.brandid from brandproduct
as bp inner join (select brandid,brandproductid from
brandproductdriverrelation group by brandid,brandproductid) as bpr on
bp.brandid=bpr.brandid and bp.brandproductid = bpr.brandproductid and
bp.brandid=36 ) as bpp set bpk.brandproductid = bpp.brandproductid where
bpk.brandproductid = 0 — and bpk.computertype = 2 — 0 and bpk.brandid
= bpp.brandid and bpk.brandproductkeyname ” and bpp.brandproductenname
= concat(bpk.brandproductkeyname,’ 笔记本电脑’); insert into
t_runninglog values(default,default,’rule hp 1 in
MergeBrandProductKey’,”); commit; — HP rule 2 — HP Compaq 6535s — HP
Compaq 6535s Notebook PC update brandproductkey as bpk, (select
bp.brandproductid, bp.brandproductenname, bp.brandid from brandproduct
as bp inner join (select brandid,brandproductid from
brandproductdriverrelation group by brandid,brandproductid) as bpr on
bp.brandid=bpr.brandid and bp.brandproductid = bpr.brandproductid and
bp.brandid=36 ) as bpp set bpk.brandproductid = bpp.brandproductid where
bpk.brandproductid = 0 — and bpk.computertype = 2 — 0 and bpk.brandid
= bpp.brandid and bpk.brandproductkeyname ” and
upper(bpp.brandproductenname) = upper(concat(bpk.brandproductkeyname,’
Notebook PC’)); insert into t_runninglog values(default,default,’rule
hp 2 in MergeBrandProductKey’,”); commit; — */ — 任务执行主体 结束
insert into t_runninglog values(default,default,’finish in
MergeBrandProductKey’,”); commit; END 有关HANDLER的语法结构如下: 复制代码 代码如下: DECLARE handler_type
HANDLER FOR condition_value[,…] sp_statement handler_type:
CONTINUE | EXIT condition_value: SQLSTATE [VALUE] sqlstate_value |
condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION |
mysql_error_code Handlers类型: , EXIT: 发生错误时退出当前代码块 ,
CONTINUE: 发送错误时继续执行后续代码 condition_value:
condition_value支持标准的SQLSTATE定义;
SQLWARNING是对所有以01开头的SQLSTATE代码的速记 NOT
FOUND是对所有以02开头的SQLSTATE代码的速记
SQLEXCEPTION是对所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的速记
除了SQLSTATE值,MySQL错误代码也被支持 但是对于mysql而言,优先级如下:
MySQL Error code SQLSTATE code 命名条件

— 名字
— 作用(例子)
— 语法

— 子查询
— 就是在一个查询中嵌套一个查询
— 一般作用就是利用多张表查询一个信息
— 例如查询”濮阳语儿”的成绩
select * from TestDataBase..Student;
select * from TestDataBase..Score;
— 在学生表中查得stuId,然后再到分数表中查询分数
select stuId from TestDataBase..Student where stuName =’濮阳语儿’;

select * from TestDataBase..Score where stuId = 5723;

— 外部查询
select *
from TestDataBase..Score
where stuId in
( — 子查询、内部查询
select stuId from TestDataBase..Student where stuName =’濮阳语儿’
);
— 外部查询(子查询)

— 将一个查询的结果作为另一个查询的条件

— 考试成绩与课程查出来 Course
select * from TestDataBase..Course;

select className from TestDataBase..Course where classId in
(
select top 1 classId from TestDataBase..Student where
stuName=’濮阳语儿’
);

— 多个单值 外部查询 where 字段 in
(子查询)
select ‘濮阳语儿’ , (select className from TestDataBase..Course where
classId in
(
select top 1 classId from TestDataBase..Student where
stuName=’濮阳语儿’
));

— 表值 select * from (子查询) as
别名
select * from (
select stuName, case stuSex when ‘f’ then ‘女’ else ‘男’ end as stuSex,
DATEDIFF(YEAR, stuBirthdate, GETDATE()) as stuAge from
TestDataBase..Student where stuId <= 10
) as t
where t.stuAge between 20 and 30;


— 员工编号 基本工资 请假扣款 补贴 绩效奖金 项目奖金 社保扣款
/*
select
来自员工表的查询
, 来自工资级别表的查询
, 考勤表的查询
… …
*/

— 独立子查询(标量、多值)

— 相关子查询
— 查询濮阳语儿的三科平均分
select AVG(testBase), AVG(testBeyond), AVG(testPro) from
TestDataBase..Score where stuId = (select top 1 stuId from
TestDataBase..Student where stuName=’濮阳语儿’);

select
stuName
, (select AVG(TestBase) from TestDataBase..Score where stuId = t.stuId)
as 基础平均分
, (select AVG(testBeyond) from TestDataBase..Score where stuId =
t.stuId) as 中级平均分
, (select AVG(testPro) from TestDataBase..Score where stuId = t.stuId)
as 高级平均分
from
TestDataBase..Student as t
where
stuName = ‘濮阳语儿’;


use HeiMa8;

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);

case表达式:
if-else结构
case
when 条件1 then 值1
when 条件2 then 值2
。。。
else 值n
end

–switch-case结构
case 字段
when 匹配1 then 值1
when 匹配2 then 值2
。。。
else 值n
end
Set statistics io on;–打开监视磁盘IO操作
Set statistics time on;

select * from Score;
— 分组
select 学号, ‘语文’, ‘数学’, ‘英语’ from Score group by 学号;
–第一种结构示例:switch–case
select
学号
, case when 课程=’语文’ then 成绩 else 0 end as ‘语文’
, case when 课程=’数学’ then 成绩 else 0 end as ‘数学’
, case when 课程=’英语’ then 成绩 else 0 end as ‘英语’

from Score

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 学号;

第二种结构示例:if–else
select
sum(case when T.充值金额>=500 then T.充值金额 end) as ‘鲸鱼用户’
,sum(case when T.充值金额>=100 and T.充值金额<500 then T.充值金额
end) as ‘海豚用户’
,sum(case when T.充值金额>=10 and T.充值金额<100 then T.充值金额
end) as ‘小鱼用户’
from
(
select [ChannelUserKey] as
用户ID,sum(convert(float,[RechargeAmount])/100) as
充值金额,sum([RechargeCount]) as 充值用户
from [dbo].[FactRecharge]
where datekey>=20141201 and datekey<=20141210
and ChannelKey=1
group by [ChannelUserKey]
) T


— 透视变换

select * from Score pivot(
sum(成绩) for 课程 in (语文,数学,英语)
) as t


— 表连接
— 作用:将多张表变成一张表
— 用法与分类(案例)
— 分类:交叉连接、内连接、外连接

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);

select * from joinPerson;
select * from joinTitle;

select pName, titleName from joinPerson cross join joinTitle;
— 如果两章表中有重名的字段,就会出问题,就需要给表加别名
select t1.pName, t2.titleName from joinPerson as t1 cross join joinTitle
as t2;

— 内连接
select
*
from
joinPerson as t1
inner join
joinTitle as t2
on t1.titleId = t2.titleId;

— 左外连接
select
*
from
joinPerson as t1
left join
joinTitle as t2
on t1.titleId = t2.titleId;

— 右外连接
insert into joinTitle(titleName) values(‘班主任’);

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

— 全连接
select
*
from
joinPerson as t1
full join
joinTitle as t2
on t1.titleId = t2.titleId;

— 表表达式
— 就是通过表与表的运算,得到一个结果集作为from后面的数据源
— 1、派生表 返回结果集的子查询
— 语法: select … from (select 查询) as 别名;
— 注意: 不能使用游标
— 2、公用表表达式CTE
— 3、视图
— 4、内联表值函数

— 查询学生信息
select * 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
where
stuName = ‘濮阳语儿’;


select * from
(
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
) as t
where t.stuName = ‘濮阳语儿’;