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

发布时间:2019-07-20  栏目:SQL  评论:0 Comments

前言

前言

正文是个体学习SQL Server 数据库时的过去笔记的重新整建,内容注重是对数据库的主导增加和删除改查的SQL语句操作封锁,视图,存款和储蓄进程,触发器的为主领悟。

正文是私家学习SQL Server 数据库时的陈年笔记的股价整理,内容重要是对数据库的宗旨增删改查的SQL语句操作自律,视图,存款和储蓄进度,触发器的焦点驾驭。

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

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

正文

正文

1.子查询

1.主键:

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

主键的成效:保障表中的每条数据的独一性
特色: 主键不能够再度 无法为空
分类:
逻辑主键:采取为表中追加的那多少个“自动编号”列或许“GUID”列为主键(未有实际专门的工作上的意义)的主键
(提出利用逻辑主键)
职业主键:选取表中那多少个在作业中有实际意义的列作为主键
》》》》》》》》》选拔主键的计策,选什么的列作为主键《《《《《《《《《
1》主键,提议接纳这几个一般不会被修改的列
2》选择单列,不选取多列(不用组合主键)
3》选取这几个轻易列(整数列(自动编号))

–把另外一个查询的结果作为当下询问的规范化来接纳。
–子查询中=、!= 、< 、> 、<=
、>=之后只可以回到单个值,如若几个值就能报错
–化解办法 能够用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)

2.char(),nchar(),varchar()之间的界别

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

》》》》》》》》》char(10)与varchar(10)的区别《《《《《《《《《
char(10)
固定长度,表示在数据库中寄存的时候占用13个字节的上空,要是过量10个则报错,假设相当不足12个则用空格补全。
varchar(10)
可变长度,表示该列最多能够积累12个字节,借使实际存款和储蓄远远不足10个字节,则会在仓库储存的时候自动总括一下其实的仓库储存个数,而动态的变动长度。【节省空间】

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

》》》》》》》》》char(10)与nchar(10)的区别《《《《《《《《《

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

char(10) 能够累积11个字母可能5个汉字。
用来积累数据的时候,克罗地亚(Croatia)语站1个字节,中文站2个字节。

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

nchar(10)
表示能够积攒12个假名或拾个汉字,因为各类字符都以遵从unicode方法来囤积的。当使用nchar(10),来存款和储蓄数据的时候无论存款和储蓄的是中文照旧德文都以种种字符占2个。

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

 

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

3. 创建数据库

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

–创立叁个数据库
create database School

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

–删除数据库
drop database School

2.连表查询

–创制数据库的时候,钦定一些数据库的相干参数。
create database School
on primary –主数据文件
(
name=’School’,
size=10mb,
filename=’c:school.mdf’,
filegrowth=10%,
maxsize=100mb
)
log on –日志文件
(
name=’School_log’,
filename=’c:school.ldf’,
size=5mb,
filegrowth=5mb,
maxsize=50mb
)

–查询全部学生的姓名、年龄及所在班级 (班级在另二个表中)
–当七个列在差别的表中时,要跨表查询,所以一般能够使用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(只询问八个表中都有些数据)

–切换数据库
use school
go

–》》》full join 是查询全数的多少(未有的为空)

4. 创建表

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

–创建表
create table Class
(
ClassId int identity(1,1) primary key,
ClassName varchar(50) not null,
ClassDesc varchar(50) not null
)

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

–删除表
drop table Class

 

–向Class表中插入数据
insert into Class(ClassName,ClsDesc)values(‘大三’,’三年’);

–》》》左外联接(中国左翼小说家联盟接)

–insert into…values.. 这种写法每便只好插入一条数据

–查询未有参预考试的学习者的人名与号码
–把左表(left join
关键字侧边的表)中的全体笔录都展现出来,对于那么些在右表中能找到相配的笔录,展现对应合营数据,对于那么些右表中找不到十分的笔录彰显为null
select
ts.tsid,
ts.tsname,
TblScore.*
from TblStudent as ts
left outer join TblSore.tsid=ts.tsid   –outer能够不写

–向Class表中插入多条数据
–重复数据不另行插入,union关键字笔者就具有去掉重复的意味
–union | union all (重复插入)
insert into Class
select ‘大三’,’三年’ union
select ‘三五’,’间谍’ union
select ‘一一’,’多久’ union
select ‘六七’,’得到’

–》》》右外对接
–代表要将右表(right join
左边的表)中的全部数据都呈现,左表中只彰显那多少个相称的多寡。

–将Class表中的数据备份到Student表中
–这种写法会将Class表中的全部数据插入到Student表中
–前提是Student表不真实,若是那一个表存在则报错。
select * into Student from Class

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

–向八个一度存在的表中插入数据,数据的发源是Class表
insert into Student(ClassName,ClsDesc)
select ClassName,ClsDesc from Class

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

 

–》》》演练:查询全数学员(参预和未到位的考试)的学生姓名、年龄、战绩,如果未有参预考试显示缺考,如果小于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 German成绩,
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

–查询表中数量
select * from Class

3.视图

5.update 数据

视图自身并不存款和储蓄数据,只是存储的询问语句,假使把实际表中的数量修改后,则透过视图查询到的结果也变了。

–将持有年龄低于20岁的人的年美国首都改成19(tage是Class表后加属性)
update Class set tage=19 where tage<20

视图的目标是便于查询,所以一般景色下无法对视图举行增加和删除改查

–将年龄为19岁的还要性别为0的人的人名两边★改为☆
update Class set ClassName =replace (tname,’★’,’☆’) where tage=19 and
tgender=0

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

6.去除数据

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

delete from Class –删除全数数据 自动编号未有复苏到私下认可值
能够根据法则来删除
truncate table Class –重置了机动编号
删除只好三遍性都清空,不能够根据规范来删除
清除速度(质量)比delete语句快的多

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

delete from Class where tage=19 or tage is null –删除19岁或许空值

4.扬言变量与利用

》》》》》》》》》删除重复数据只保留一条(id最小的一条)《《《《《《《《《
》》》》》》》》》删除表中多余的重复记录,重复记录是基于单个字段(peopleId)来判断,只留有rowid最小的记录 《《《《《《《《《
delete from people
where peopleName in (select peopleName from people group by peopleName
having count(peopleName) > 1)
and peopleId not in (select min(peopleId) from people group by
peopleName having count(peopleName)>1)

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

 

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

7.尺度查询,模糊查询

–输出值
print @name
print @age

–查询数学没有过关的学员的学号
select
fid as 学号,
fmath as 分数
from MyStudent where fmath<60

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

–查询年龄在20-28岁以内的男学生
select
fname as 姓名 from MyStudent where fage between 20 and 30 and
fgender=’男’

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

–查询班级id 1 2 3 的富有学员
select * from MyStudent where classid in (1,2,3)

–》》》全局变量
print @@language
print @@version
print ‘aaa’+100
–通过剖断@@error变量中是或不是不为0,就能够看清上一条sql语句试行是还是不是出错了
–要是@@error为0,表示上一条sql语句实践没出错,要是@@error不为0,则意味着上一条sql语句出错了。
print@@error

–查询全数姓赵的同学 (通配符%代表:任性七个随机字符)
select * from MyStudent where fname like ‘赵%’

–》》》通过while计算1-100里边有着奇数的和

–查询出姓名中即使包括二个‘民’字就能够。
select * from MyStudent where fname like ‘%民%’

–申明变量并开端化

–查询全数姓赵的同桌,並且姓名字数是3个
–通配符 _ :表示大肆的单个字符。
select * from MyStudent where fname like ‘赵__’
select * from MyStudent where fname like ‘赵%’ and len(fname)=3

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

–查询出姓名中满含‘民’或‘用’的校友
–通配符[]:表示中括号中的大四个字符,只选一个金童玉女
–通配符 ^a :表示除了a那些字符都行。
select * from MyStudent where fname like ‘%[民用]%’

5.事务

8.聚合函数

事务有多少个属性:原子性 一致性 隔开性 长久性
原子性:对于数据修改,要么全都实践,要么全都不执行
一致性:当数码产生时,数据必须处于同样状态
隔绝性:对数码实行修改的有所并发事务时互相隔开的。那证明职业必须是单独的,它不应以别的措施信赖于或影响其余事情
永世性:事务达成后,他对数据库的退换被恒久保持,事务日志能够保持职业的永远性

–查询数学成就最高低分
select max(fMath) as 数学战绩最高分 from MyStudent
select min(fMath) as 数学战表最低分 from MyStudent

–展开专门的学业
begin transaction

–平均分(计算平均分的时候对空值不管理)
select avg(fMath) as 平均分 from MyStudent

–提交事务
commit transaction

–求数据记录中的总条数(总人数)
select count(*) as 班级总人数 from MyStudent

–回滚事务
rollback transaction

select
参天分=(select max(fMath) as 数学战表最高分 from MyStudent),
低于分=(select min(fMath) as 数学成绩最低分 from MyStudent),
平均分=(select avg(fMath) as 平均分 from MyStudent)

–账户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

–分数评级
–90以上 优秀
–80以上 良好
–70以上 中
–70以下 差
select chengji,
评级=
case
when shuxue>=90 then ‘优秀’
when shuxue>=80 then ‘良好’
when shuxue>=70 then ‘中’
else ‘差’
end
from Student

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

9.null 问题

6.积累进度

–请查询出学生表中具备数学战绩为null的人的信息
–null在数据库中象征unknow(不知情),推断二个值是不是为null,也就无法用=恐怕<>来判别
select * from MyStudent where fMath=null 错误(不回去任何数据)

–成立四个自定义的累积进程
create proc usp_HelloWorld
as
begin
print ‘hello world’
end

正确 select * from MyStudent where fMath is null

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

–查询全部fmath为非null的值
select * from MyStudent where fMath is not null

–创制多个积存进程总结五个数的和
create procedure usp_Add
@num1 int,
@num2 int
as
begin
print @num1+@num2
end

–null值与其他数据运算后拿走的要么null值。
update MyStudent set fage=fage+1 where fid=1

–输出值
exec usp_Add 100,230

10.分组group by

–存款和储蓄进度中的参数的标题
–存款和储蓄进度假如有参数,则调用的时候必须为参数赋值
exec usp_Add –不传参数则报错

–总计出mystudent表中,男女同校的个数

–次之个参数假使用户不传,则有贰个默许值
create procedure usp_Add
@num1 int,
@num2 int 一千 –为存储进程的参数设置暗中同意值
as
begin
print @num1+@num2
end

select
fgender as 性别, –这时,count(*)总结的是每一组的笔录条数,
不是总条数
count(*) as 人数
from MyStudent group by fgender –先试行group by语句分组,分完组在总括每
组个数。 分出来多少个组,那么count(*)就统 计几次

–创立分页存款和储蓄进度
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

–查询班级的男同学的人数超过2的新闻

–删除三个仓库储存进度
drop proc usp_Add

–having是group
by的规格对分组后的多寡进行筛选(与where类似,都以筛选,只不过having是用来筛选分组后的组的)
select
classid as 班级号,
count(*) as 班级人数
from TblStudent
where fgender=’男’
group by classid
having count(*)>2

7.触发器

》》》》》》》》》语句执行顺序《《《《《《《《《

尽量制止在触发器中实行耗费时间操作,因为触发器会与sql语句以为在同二个作业中(事务不收场,就无法自由锁)

select
–distinct / top 之类的主要性字
fgender as 性别, –5》选择列
count(*) as 人数
from MyStudent –1》先从表中获得数量
where fage>30
–2》从MyStudent的多少中筛选出富有年龄超越二十八周岁的任的新闻
group by fgender –3》依照性别分组,分完组获得贰个新的结果集
having count(*)>500
–4》基于分组现在的结果集,然后再筛选,筛选出那个组中记录大于500的组
order by 人数 asc –6》最终把展现出来的结果排序

–创制插入数据触发器
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

–语句施行种种
from > where > group by > having > select > order by

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

11.日期函数

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

–请查询出富有入职一年以上的职员和工人音讯
select * from TblStudent
where dateadd(year,1,tsday)<getdate()

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

–总结四个时刻差
–查询90年至今是有个别年
select datediff(year,’1990-9-9′,getdate())

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

–查询叁个日期的特定部分
select year(getdate())
select datepart(year,getdate())

8.游标

–输出全体数据中通话时间最长的5条记下。
select top 5
*,’通话时长(秒)’=datediff(second,Startdatetime,Enddatetime) from
Calltecords order by datediff(second,Stardatetime,enddatetime) desc

–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

后记

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

 

留下评论

网站地图xml地图