SQLSERVER–一条SQL语句

前言

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

正文是个体学习SQL Server 数据库时的陈年笔记的整合治理,内容首假如对数据库的宗旨增加和删除改查的SQL语句操作封锁,视图,存款和储蓄进度,触发器的着力领悟。

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

注:内容比较底子,符合入门者对SQL
Server 数据库的了然!!!

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

正文

— 子查询
— 正是在二个查询中嵌套多个询问
— 平常意义便是接收多张表查询多个音信
— 举个例子查询”呼伦贝尔语儿”的实际业绩
select * from TestDataBase..Student;
select * from TestDataBase..Score;
— 在学子表中查得stuId,然后再到分数表中查询分数
select stuId from TestDataBase..Student where stuName =’赤峰语儿’;

1.子查询

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 ‘英语’

–把五个查询结果作为别的多少个询问的查询源
select * from (select * from Student where tbage between 3 and 5)
as ct where tbname=5 –ct是新创的表名

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 = ‘呼伦贝尔语儿’;


–把其它叁个询问的结果作为当前查询的规范来采用。
–子查询中=、!= 、< 、> 、<=
、>=之后只好回去单个值,借使多个值就能够报错
–消除办法 能够用in 替代
select * from Student
where tbage in(select tbage from Student where tbname=3)

— 分页

select * from TestDataBase..Student;
— 当前页数、每页展现的条数 10
— SQL Server 2005+ row_number() 可以为表生成一个总是的数字列
— 语法 row_number() over(order by 字段)
— select ROW_NUMBER() over(order by stuId), * from
TestDataBase..Student

select top 10 * from TestDataBase..Student;

select top 10 * from TestDataBase..Student
where stuId not in(select top 10 stuId from TestDataBase..Student);

set statistics io on;
set statistics time on;

select top 10 * from TestDataBase..Student
where stuId not in(select top ((100-1)*10) stuId from
TestDataBase..Student);


select * from
(
select ROW_NUMBER() over(order by stuId) as num, * from
TestDataBase..Student
) as t
where
t.num between 1 and 10;

select * from
(
select ROW_NUMBER() over(order by stuId) as num, * from
TestDataBase..Student
) as t
where
t.num between 21 and 30;
/*
select * from
(
select ROW_NUMBER() over(order by stuId) as num, * from
TestDataBase..Student
) as t
where
t.num between (n-1) * m + 1 and n * m;
*/

— 公用表表明式(CTE)
— 语法
/*
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;

select * from HeiMa8..AreaFull as t1 inner join HeiMa8..AreaFull as t2
on t1.AreaPid = t2.AreaId ;


— 在供给每每的操作一些表表明式的时候
— 视图和内联表值函数

— 视图
— 就是将查询的讲话封装成多少个目的,每一趟查询的时候一向操作那些指标就能够
— 虚拟表
— 使用派生表
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 = ‘河源语儿’;

— 创设视图
— 语法:
/*
create view vw_视图名
as
select语句
;
*/

use TestDataBase;
go
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
;
go

— 设想的表
select * from vw_StuInfo where stuName=’清远语儿’;

select * from vw_StuInfo where stuId = 304;

update Testdatabase..Student set stuName = ‘嘉嘉’ where stuId=304;


— 视图能够立异数据,不过不提议更新和扩展以至去除
— 连接多张表、视图并未显式全数的字段


— 视图的二个主要成效(数据安全)
use HeiMa8;
go
create view Exe3.vw_StuInfo
as
select * from TestDataBase..vw_StuInfo;
go

— HeiMa8
select * from Exe3.vw_StuInfo;

— select * from sys.databases;

— 内联表值函数
— 带有参数的视图
— 效率: 将二个可变条件的询问封装成一个函数对象,实行结果是一张表
/*
create function fn_函数名
(@参数名 as 类型, …)
returns table
as
return 查询语句;
*/
— 分页
— @pageIndex 当前页码
— @pageSize 每页条数
use TestDataBase;
go

create function fn_FenYe
(@pageSize as int, @pageIndex as int)
returns table
as
return
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;
go
— 分页

— 1008
select * from fn_FenYe(10, 1008);

— 视图怎么提供权限,怎么安全
— 角色 访谈能力的聚合
— 架构 可访谈对象的集聚
— 视图 表的二个结出集

 

— 变量
— int num = 10;
— 声明 赋值 使用
— declare @变量名 类型;
— set @变量名 = 值;

declare @num varchar(2);
set @num = ’10’;

select @num + ‘a’;
go

— SQL Server 2008+
declare @num int = 10;
— 局地变量
— 系统变量 @@最早

select @@connections
select @@CPU_BUSY;

— @@error 如今叁次进行SQL语句的错误码
select @@ERROR;
— @@version
select @@version
print @@version;

— @@identity 近日叁回插入数据的活动增加编号
select @@IDENTITY;
use HeiMa8 ;
select * from Exe3.newStudent where stuId = (select @@IDENTITY);

insert into Exe3.newStudent
(stuName, stuSex, stuBirthdate, stuStudydate, stuAddress, stuEmail,
stuPhone, classId)
values(‘牛亮亮’, ‘m’, ‘1999-9-9 9:9:9.123’, ‘2001-1-1 1:1:1.111′,’123’,
‘123’, ‘12345678909’, 2);

insert into Exe3.newStudent(stuName, stuSex, stuBirthdate, stuStudydate,
stuAddress, stuEmail, stuPhone, classId)
output inserted.*
values(‘刘琦’, ‘m’, ‘1999-9-9 9:9:9.123’, ‘2001-1-1 1:1:1.111′,’123’,
‘123’, ‘12345678909’, 2);

— @@trancount
select @@TRANCOUNT — 获得当前政工的纵深

— @@SPID
select @@SPID; — session_id
kill 54

— set
— select
— select @变量=值
go

declare @num int;
select @num = 10;
select @num;
go

select top 3 * from Exe3.newStudent

declare @name nvarchar(1000) = ”;
— set @name = (select top 1 stuName from Exe3.newStudent);
select @name+=stuName from Exe3.newStudent where stuId<10;
select @name;

select @@ERROR;

select * from sys.messages where message_id = 208;

— 流程序调节制(C#的编程)
— 选取结构 if-else
— 循环结构 while
/*
if (bool表达式)
begin — {
脚本
end — }
else if bool表达式
begin
— 脚本
end
*/

declare @num int;
–set @num = 12;

if @num is null
select ‘是NULL’;
else if @num % 2 = 0
begin
select ‘是偶数’;
end
else
begin
select ‘是奇数’;
end
go
— 循环结构
/*
while bool表达式
begin
脚本
end
*/
declare @sum int;– = 0;
declare @i int = 0;

while @i <= 100
begin
set @sum += @i;
set @i += 1; — 没有 @i++ 的语法
end
select @sum;

— 注意set

 

 

 


select * from Student
where tbage=(select tbage from Student where tbname=3)

— 事务

— 最基本的操作以工作为单位
— 将一个生龙活虎度做到的数据库操作行为规定为八个职业
— 特点:
— 原子性——实行的结果是01特色(要么完成、要么战败)
— 长久性——试行的结果不可转败为胜
— 大器晚成致性——生龙活虎旦完毕工作,各样版本的结果都风华正茂律
— 隔离性——事务与作业之间不冲突

— 事务严谨定义:
找二个操作,假如满意原子性、长久性、大器晚成致性和隔绝性就称为一个政工

select * from Exe2.LoginTbl;

insert into Exe2.LoginTbl(uid, pwd)
values
(‘苏坤’, ‘susu123’)
, (‘Nicolas洛夫斯基斯巴达司机四司机的哥司机司机’, ‘123’)
, (‘牛亮亮’, ‘niuniu123’);


— 本人写的事务
–事务
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
;

— 怎么知道成功未有? — 使用变量
— 怎么利用职业? — 存储进程
declare @isSuccess bit;
begin tran
begin try
update bank set balance=balance – 900 where cid=’0001′;
update bank set balance=balance + 900 where cid=’0002′;
commit;
set @isSuccess = ‘true’;
end try
begin catch
rollback;
set @isSuccess = ‘false’;
end catch
select @isSuccess;
;

 

— 存储进度
— 不难的作为数据库中的方法
— 函数、视图、存款和储蓄进程

— 便是生龙活虎段试行代码

》》》》》》子查询分页《《《《《《

— 系统中常用的囤积进度 sp_ stored procedure

–sp_renamedb — 更正数据库的名字
–sp_detach_db — 分离
— sp_attach_db — 附加

–sp_executesql

— sp_renamedb

— exec 存款和储蓄进度名 参数;
exec sp_renamedb ‘MyThirdDataBase’, ‘my3thDataBase’;

exec sp_renamedb @dbname=’my3thDataBase’, @newname=’小编的第八个数据库’;

— sp_executesql
exec sp_executesql N’select @@version’; — unicode编码
exec(‘select ”张三”, ”李四”’);

— 存款和储蓄进程的语法
/*
create proc[edure] usp_积存进度名
参数名 类型名 [= 默认值] [output]
, 参数名 类型名 [= 默认值] [output]
, …
as
begin
脚本
end
*/
无参无重返值的存放进程
go
create proc Exe3.usp_StuInfo
as
select * from vw_StuInfo;
go
exec Exe3.usp_StuInfo;
go

有参有私下认可值的囤积进度
— 带有参数的
create proc Exe3.usp_StuSearch
@stuName nvarchar(10)
as
select * from Exe3.vw_StuInfo where stuName = @stuName;
go

exec Exe3.usp_StuSearch @stuName=’毕节语儿’;

exec Exe3.usp_StuSearch ‘通化语儿’;

包蕴私下认可值的仓库储存进度
— 分页
go

create proc Exe3.usp_FenYe
@pageIndex int = 1
, @pageSize int = 10
as
begin
select ‘今每二二十日气很好’;
select * from
(
select ROW_NUMBER() over(order by stuId) as num
, * from Exe3.newStudent
)as t
where t.num between (@pageIndex – 1) * @pageSize + 1 and @pageIndex *
@pageSize;
end
go

exec Exe3.usp_FenYe 2, 5;

exec Exe3.usp_FenYe @pageSize=11, @pageIndex=3;

有参有重回值的寄放进程
— return output

go
— return 返回值
create proc Exe3.usp_ReturnTest
as
return 123;
go

/*
public string Func()
{
return “赵晓虎就是牛,你让牛亮亮怎么做?”;
}
*/
declare @num int;
exec @num = Exe3.usp_ReturnTest;

select @num;
go

–1》突显第风度翩翩页的数量
–分页查询的时候首先是将数据排序
select * from Student order by id desc

— 银行转发的案例

create proc Exe3.usp_ZhuanZhang
@from char(4)
, @to char(4)
, @money money
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;
end try
begin catch
rollback;
end catch
end
go


select * from bank;

exec Exe3.usp_ZhuanZhang ‘0002’, ‘0001’, 900;

go
— 思谋再次来到值
create proc Exe3.usp_ZhuanZhangExt
@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

— 关键使用法
— 定义三个变量,不赋值,调用存款和储蓄进程,将参数字传送入 后跟output

declare @isSuccess int;

— exec Exe3.usp_ZhuanZhangExt ‘0001’, ‘0002’, 500, @isSuccess
output;
exec Exe3.usp_ZhuanZhangExt
@from = ‘0001’,
@to = ‘0002’,
@money = -500,
@isSuccess = @isSuccess output;

select @isSuccess;

— 注意,不要将变量名命名称叫与仓储进度的参数意气风发致
go

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


select * from Exe2.LoginTbl;

declare @isTrue int;

exec Exe2.usp_Login ‘苏坤1’, ‘1234’, @isTrue output;

select @isTrue;

用C#实行存款和储蓄进度
— 步骤
— -> 将sql语句改为存储进程名
— -> 修改CommandType命令(text)
— -> 看再次来到结果调用钦点方法
— -> 倘使有参数,与参数化查询用法相近
— -> 就算有重回值,设置参数方向就可以(难度)

exec Exe3.usp_FenYe;

— 触发器
— 在您试行四个操作的时候,自动的举办的二个存款和储蓄进度

— DML DDL
— 对作为的归类 update、delete、insert
— 发生情势 after | instead of

— 语法
/*
create trigger tr_in|del|up_触发器的名字 on 表名
for | after | instead of
update | delete | insert
as
begin
脚本
end
*/

— inserted deleted

select * from inserted;


select * from Exe2.LoginTbl;
go
create trigger Exe2.tr_del_deleteReturn on Exe2.loginTbl
after delete
as
insert into Exe2.LoginTbl(uid, pwd)
select uid, PWD from deleted;
go

delete from Exe2.LoginTbl;

— 作为数据评释的补充

— 索引正是数据的目录
— 新华字典
— 拼音(集中索引) she 与本文生机勃勃致
— 部首(非集中索引) 厍 也便是积存的三个键值对表

— 字 拼音 意思 词组。。。

— 怎么着加多索引

set statistics io on;
set statistics time on;

select * from Exe3.newStudent where stuName=’苍昊天’;
/*
SQL Server 解析和编写翻译时间:
CPU 时间 = 0 阿秒,占用时间 = 0 微秒。
SQL Server 深入分析和编写翻译时间:
CPU 时间 = 0 纳秒,占用时间 = 20 纳秒。

(2 行受影响)
表 ‘newStudent’。扫描计数 1,逻辑读取 2264 次,物理读取 0 次,预读 0
次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

SQL Server 推行时间:

–2》第一页 显示5条数据
select Top 5 * from Student order by id desc

CPU 时间 = 16 皮秒,占用时间 = 31 纳秒。

SQL Server 剖析和编译时间:
CPU 时间 = 0 纳秒,占用时间 = 0 纳秒。
SQL Server 剖析和编写翻译时间:
CPU 时间 = 0 飞秒,占用时间 = 0 皮秒。
SQL Server 深入分析和编写翻译时间:
CPU 时间 = 0 纳秒,占用时间 = 0 飞秒。

(2 行受影响)
表 ‘newStudent’。扫描计数 1,逻辑读取 5 次,物理读取 0 次,预读 0
次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

SQL Server 实施时间:
CPU 时间 = 15 飞秒,占用时间 = 24 微秒。
*/

— 索引不自然好

create unique nonclustered index ix_目录名字 on 表
(字段 desc, 字段 desc)

–3》第二页
select top 5 * from Student
where id not in (select top 5 * from Student order by id desc)
order by id desc

–4》第三页
select top 5 * from Student
where id not in (select top (2*5) * from Student order by id desc)
order by id desc

》》》开窗函数分页《《《

–第七页数据 每页5条数据
–over归属开窗函数

select * from
(
select * ,row_number() over( order by id desc) as paixu from Student
) as tbl
where tbl.paixu between 6*5+1 and 7*5

2.连表查询

–查询全数学员的全名、年龄及所在班级 (班级在另一个表中卡塔尔国
–当三个列在不一样的表中时,要跨表查询,所以日常可以动用inner join
–tc ts是对表名起的外号
select
ts.tsname,
ts.tsage,
tc.tclassname
from TblStudent as ts
inner join TblClass as tc on
ts.tsclassid=tc.tclassid(只询问三个表中都部分数据卡塔尔

–》》》full join 是询问全部的数额(未有的为空卡塔 尔(阿拉伯语:قطر‎

—子查询写法
select
tsname,
tsage,
班级名称=(select tclassname from TblClass where
TblClass.tclassid=TblStudent.tsclassid)
from TblStudent

–查询学生姓名、年龄、班级及战表(战表归属第三张表卡塔 尔(英语:State of Qatar)
select
ts.tsname,
ts.tsage,
tc.tclasssname,
tscore.tenglish,
tscore.tmath
from TblStudent as ts
inner join TblClass as tc on ts.tsclassid=tc.tclassid 
inner join TblScore as tscore on tscore.tsid=ts.tsid

 

–》》》左外联接(中国左翼小说家联盟接卡塔尔国

–查询未有加入考试的学子的姓名与数码
–把左表(left join
关键字侧面的表卡塔 尔(阿拉伯语:قطر‎中的全体记录都显得出来,对于那个在右表中能找到匹配的笔录,彰显对应特别数据,对于那么些右表中找不到非凡的记录突显为null
select
ts.tsid,
ts.tsname,
TblScore.*
from TblStudent as ts
left outer join TblSore.tsid=ts.tsid   –outer能够不写

–》》》右外对接
–表示要将右表(right join
左侧的表卡塔 尔(阿拉伯语:قطر‎中的全部数据都来得,左表中只呈现那多少个相配的数量。

select
ts.tsid,
ts.tsname,
TblScore.*
from TblStudent as ts
right outer join TblSore.tsid=ts.tsid

–右外联与左外联都以先将匹配的多少找到,然后再将那一个未有相称的多寡拉长进去,(注意:不是一只查询出来的,有前后相继顺序卡塔 尔(阿拉伯语:قطر‎

–》》》练习:查询全体学子(出席和未到位的考察卡塔尔的学习者姓名、年龄、成绩,若无加入考试显示缺考,要是小于english可能math
小于60分展现比不上格
select
ts.tsname,
ts.tsage,
tscore.tsid,
case
when tscore.tenglish is null then ‘缺考’
else convert(varchar(10),tscore.tenglish)
end as 乌克兰语成绩,
case
when tscore.tmath id null then ‘缺考’
else convert (varchar(10),tscore.tmath)
end as 数学成绩,
是还是不是报名考试=
case
when tscore.tscoreid is null then ‘是’
else ‘否’
end
from TblStudent as ts
left join TblScore as tscore on ts.tsid=tscore.tsid

3.视图

视图自己并不存款和储蓄数据,只是存款和储蓄的询问语句,如若把真实表中的多少改正后,则经过视图查询到的结果也变了。

视图的目标是福利查询,所以平常景观下不能够对视图进行增加和删除改查

–在视图中的查询语句,必需为每一列成立三个列名
create view vw2
as
select
tsname,
case
when tsage>13 and tsage<=16 then ‘少年’
when tsage>50 then ‘老年’
else ‘青壮年’
end as 称呼
from TblStudent

–在视图中无法选择order by语句。除非:其余还点名了top 或for xml
–错误
create view vw3
as
select * from TblStudent order by tsage desc

–正确
create view vw3
as
select top 3 * from TblStudent order by tsage desc

4.声称变量与应用

–》》》局地变量
–证明变量
declare @name varchar(10)
declare @age int

–赋值
set @name=’yhz’
set @age=17

–输出值
print @name
print @age

–使用set与select为变量赋值的分别
declare @rcount int
set @rcount=(select count(*) from TblStudent)
print @rcount

declare @rcount int
select @rcount=count(*) from TblStudent
print @rcount

–》》》全局变量
print @@language
print @@version
print ‘aaa’+100
–通过判别@@error变量中是或不是不为0,就足以推断上一条sql语句奉行是或不是出错了
–如若@@error为0,表示上一条sql语句实行没出错,即使@@error不为0,则象征上一条sql语句出错了。
print@@error

–》》》通过while总括1-100里头全数奇数的和

–注解变量并初步化

declare @sum int=0
declare @i int =1
while @i<=100
begin
if @i%2<>0
begin
set @sum=@sum+@i
end
end
print @sum

5.事务

事情有四个属性:原子性 风姿罗曼蒂克致性 隔开分离性 长久性
原子性:对于数据修改,要么全都试行,要么全都不实行
意气风发致性:当数码变成时,数据必需处于相近状态
隔开性:对数码进行校勘的享有并发事务时互相隔断的。那证明职业必得是独立的,它不应以其它方法信任于或影响别的事情
恒久性:事务实现后,他对数据库的改变被永远保持,事务日志可以保持工作的永恒性

–张开职业
begin transaction

–提交事务
commit transaction

–回滚事务
rollback transaction

–账户A给账户B转账 当一方出难题时,五个语句都不试行
begin tran
declare @sum int=0
update bank set balance =balance-1000 where cid=’0001′
set @sum=@sum+@@error
update banl set balance =balance+1000 where cid=’0002′
set @sum=@sum+@@error

if @sum<>0
begin
rollback tran
print ‘回滚’
end
else
begin
commit tran
print ‘提交了’
end

6.存款和储蓄进度

–创造八个自定义的存放进度
create proc usp_HelloWorld
as
begin
print ‘hello world’
end

–输出存款和储蓄进度
exec usp_HelloWorld

–制造二个仓库储存进程总括八个数的和
create procedure usp_Add
@num1 int,
@num2 int
as
begin
print @num1+@num2
end

–输出值
exec usp_Add 100,230

–存款和储蓄进程中的参数的主题材料
–存款和储蓄过程假设有参数,则调用的时候必须为参数赋值
exec usp_Add –不传参数则报错

–次之个参数固然顾客不传,则有一个暗许值
create procedure usp_Add
@num1 int,
@num2 int 1000 –为存储进程的参数设置默许值
as
begin
print @num1+@num2
end

–创造分页存款和储蓄进度
create proc usp_PageBum
@pageSize int, –每页显示的数额
@pageIndex int –第几页
as
begin
select * from (select *,row_number()over (order by CityID asc)as num
from S_City )as s
where s.num between (@pageIndex -1)*@pageSize +1 and @pageIndex
*@pageSize
end
–查询第5页内容每页显示10条数据
exec usp_PageBum 10,5

–删除一个囤积进度
drop proc usp_Add

7.触发器

尽量幸免在触发器中履行耗费时间操作,因为触发器会与sql语句感到在同三个事情中(事务不收场,就不能够自由锁卡塔尔国

–成立插入数据触发器
create trigger tri_Teacher_insert_after
on Teacher after insert
as
begin
declare @id int
declare @name varchar(10)
declare @phone int
declare @mail varchar(50)
select @id=tcid,@name=tcname,@phone=tcphone,@mail=tcmail from inserted

print @id
print @name
print @phone
print @mail
end

–插入数据
insert into Teacher values(‘网名好’,’12352536′,’Wjifdfji@qq.com’)

–创立删除数据触发器
–无法有主键
create trigger tri_Teacher_after
on Teacher after delete
as
begin
insert into TeacherBak
select * from deleted
end

–删除数据
–sql
server中的触发器是表级触发器,无论删除多少行照旧插入多少行,只触发一回
–是按语句来触发的,每一回推行一遍讲话,触发三次触发器
delete from Teacher where tcid>18

8.游标

–1.概念游标
declare cur_Student cursor fast_forward for select * from Student

–2.打开游标
open cur_Student

–2.1 对游标的操作
–将每条数据读取并出口

–2.1.1将游标向后移动一条
fetch next from cur_Student

–将游标循环向后移动,直到最终
while @@fetch_status=0
begin
fetch next from cur_Student
end

–3.关闭游标
close cur_Student

–4.自由能源
deallocate cur_Student

9.(补充卡塔尔全局有的时候表,局部不常表

局地临时表:表名以#为始发。只在这时候此刻对话中有效,不可能跨连接待上访谈。假如一向在接连会话中开创,则当前一而再断开后删除,要是是在仓储进度中创设的,则存款和储蓄进度进行达成后删除

大局有时表:表名以##为开端。多个会话可分享全局一时表。当创制全局有时表的对话断开,何况没有客商正在访谈全局有的时候表时删除

10.(补充)约束

–删除一列(EmpAddress列卡塔尔国
alter table Class drop column EmpAddress

–扩张一列(扩展一列EmpAddr varchar(1000卡塔尔卡塔 尔(英语:State of Qatar)
alter table Class Add EmpAddr varchar(1000)

–校订一下Emp 的数据类型(varchar(200卡塔尔国卡塔尔
alter table Class alter column Emp varchar(200)

–为EmpId扩张三个主键约束
alter table Class add constraint PK_Class_EmpId primary key(EmpId)

–为EmpName扩展多个唯黄金时代约束
alter table Class add constraint UQ_Class_EmpName unique(EmpName)

–为性别扩张二个暗中认可节制,默以为男
alter table Class add constraint DF_Class_EmpGender default(‘男’) for
EmpGender

–为年龄扩大二个反省节制,年龄必需在1—120岁以内(富含卡塔 尔(阿拉伯语:قطر‎
alter table Class add constraint CK_Class_EmpAge check(EmpAge>=0
and EmpAge<=120)

–增添外键限制,表Class中有一列EmpDeptId引用Student表中的DeptId
alter table Class add EmpDeptId int not null
alter table Student add constraint PK_Student_DeptId primary
key(DeptId)

alter table Class add constraint FK_Class_Student foreign
key(EmpDeptId)
references Student(DeptId)

–一条语句删除八个约束,节制名用 逗号 隔离
alter table Class drop constraint
PK_Student_DeptId,
FK_Class_Student,
CK_Class_EmpAge

–用一条语句为表扩大多少个限定
alter table Class add
constraint PK_Student_DeptId primary key(DeptId),
constraint CK_Class_EmpAge check(EmpAge>=0 and EmpAge<=120),
add constraint DF_Class_EmpGender default(‘男’) for EmpGender

后记

笔记不全,还请见谅!希望对您有所进步。

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注