SQL Server 数据库基础笔记分享(下)

前言

正文是私有学习SQL Server 数据库时的陈年笔记的整治,内容首假若对数据库的着力增删改查的SQL语句操作自律,视图,存储过程,触发器的主旨领会。

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

正文

1.子查询

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

–把此外一个询问的结果作为当前查询的准绳来利用。
–子查询中=、!= 、< 、> 、<=
、>=之后只可以回到单个值,假诺四个值就会报错
–解决办法 可以用in 代替
select * from Student
where tbage in(select tbage from Student where tbname=3)

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

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

–1》显示第一页的多寡
–分页查询的时候首先是将数据排序
select * from Student order by id desc

–2》第一页 显示5条数据
select Top 5 * from Student order by id 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

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

后记

笔记不全,还请见谅!希望对你有所提高。

 

相关文章