数据库基础笔记分享

前言

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

本文是私有学习SQL Server 数据库时的陈年笔记的整治,内容根本是对数据库的焦点增加和删除改查的SQL语句操作封锁,视图,存款和储蓄进程,触发器的基本精晓。

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

注:内容相比较基础,适合入门者对SQL
Server 数据库的刺探!!!

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

正文

— 子查询
— 就是在贰个查询中嵌套2个查询
— 一般意义正是使用多张表查询1个音讯
— 举例查询”三明语儿”的成绩
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 =’梅州语儿’
);
— 外部查询(子查询)

— 将3个询问的结果作为另三个查询的尺度

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

select className from TestDataBase..Course where classId in
(
select top 一 classId from TestDataBase..Student where
stuName=’玉溪语儿’
);

— 八个单值 外部查询 where 字段 in
(子查询)
select ‘赤峰语儿’ , (select className from TestDataBase..Course where
classId in
(
select top 一 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 一 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 ‘英语’

–把一个查询结果作为其余2个询问的查询源
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.充钱金额>=拾 and T.充值金额<100 then T.充钱金额
end) as ‘小鱼用户’
from
(
select [ChannelUserKey] as
用户ID,sum(convert(float,[RechargeAmount])/拾0) 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 别名;
— 注意: 无法选用游标
— 二、公用表表明式CTE
— 3、视图
— 四、内联表值函数

— 查询学生音信
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;
— 当前页数、每页呈现的条数 十
— SQL Server 2005+ row_number() 可以为表生成3个接连的数字列
— 语法 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;


— 视图可以立异数据,可是不建议更新和充实以及去除
— 连接多张表、视图并不曾显式全部的字段


— 视图的2位命关天作用(数据安全)
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);

— 视图怎么提供权限,怎么安全
— 剧中人物 访问本事的集中
— 架构 可访问对象的联谊
— 视图 表的2个结实集

 

— 变量
— 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)

— 事务

— 最主旨的操作以工作为单位
— 将八个早已到位的数据库操作行为规定为贰个事情
— 特点:
— 原子性——实践的结果是0一特点(要么完毕、要么失利)
— 长久性——试行的结果不可转败为胜
— 1致性——壹旦完结专门的学问,各种版本的结果都一致
— 隔断性——事务与专业之间不争辩

— 事务严刻定义:
找三个操作,假如满意原子性、持久性、一致性和隔开性就叫做贰个业务

select * from Exe2.LoginTbl;

insert into Exe2.LoginTbl(uid, pwd)
values
(‘苏坤’, ‘susu123’)
, (‘Nicolas洛夫斯基斯巴达司机4司机的哥司机司机’, ‘1二三’)
, (‘牛亮亮’, ‘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;
;

 

— 存款和储蓄进程
— 轻巧的当作数据库中的方法
— 函数、视图、存储进程

— 正是1段施行代码

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

— 系统中常用的仓库储存进度 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=’my叁thDataBase’, @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

–壹》彰显第二页的多寡
–分页查询的时候首先是将数据排序
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;

— 注意,不要将变量名命名称叫与存款和储蓄进度的参数1致
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;

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

— 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 皮秒。

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

SQL Server 推行时间:

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

CPU 时间 = 1陆 皮秒,占用时间 = 3一 阿秒。

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

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

SQL Server 试行时间:
CPU 时间 = 一5 微秒,占用时间 = 贰四 纳秒。
*/

— 索引不必然好

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

二.连表查询

–查询全部学员的真名、年龄及所在班级 (班级在另贰个表中)
–当多少个列在差别的表中时,要跨表查询,所以一般可以利用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

–查询学生姓名、年龄、班级及战表(战绩属于第三张表)
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

–右外联与左外联都以先将协作的数量找到,然后再将这3个尚未相配的数目增加进去,(注意:不是联合查询出来的,有先后顺序)

–》》》练习:查询全部学员(参预和未参与的考试)的学生姓名、年龄、战绩,假使未有出席考试展现缺考,借使小于english或然math
小于伍1柒分显示比不上格
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

肆.申明变量与应用

–》》》局部变量
–表明变量
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-十0之间具有奇数的和

–注解变量并开头化

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.事务

业务有八个属性:原子性 1致性 隔断性 持久性
原子性:对于数据修改,要么全都实行,要么全都不实施
壹致性:当数码变成时,数据必须处于相同状态
隔开分离性:对数据开始展览改动的全部并发事务时相互隔开分离的。那标记专门的学业必须是单身的,它不应以任何方法依赖于或影响其余职业
永恒性:事务达成后,他对数据库的修改被永世保持,事务日志可以保持业务的恒久性

–张开工作
begin transaction

发表评论

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