SQL Server数据库笔记

1.主键

主键的作用：保证表中每条数据的唯一性
特点：主键不能重复，不能为空
主键，建议选择那些一般不会被修改的列
选择单列，不选择多列（不用组合主键）
选择那些简单列（整数列（自动编号））

 

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

char(10)与varchar(10)的区别
char(10) 固定长度，表示在数据库中存储的时候占用10个字节的空间，如果超出10个则报错，如果不够10个则用空格补全。 
varchar(10) 可变长度，表示该列最多可以存储10个字节，如果实际存储不够10个字节，则会在存储的时候自动计算一下实际的存储个数，而动态的改变长度。【节省空间】
char(10)与nchar(10)的区别
char(10) 可以存储10个字母或者5个汉字。 用来存储数据的时候，英文站1个字节，中文站2个字节。
nchar(10) 表示可以存储10个字母或10个汉字，因为每个字符都是按照unicode方法来存储的。当使用nchar（10），来存储数据的时候无论存储的是中文还是英文都是每个字符占2个。

 

3. 创建数据库

创建一个数据库
create database School 
删除数据库
drop database School 
创建数据库的时候，指定一些数据库的相关参数。
create database School 
on primary 主数据文件
(
name='School',
size=10mb,
filename='c:school.mdf',
filegrowth=10%,
maxsize=100mb
)
切换数据库
use school
go

 

4.创建表

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

 

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

 

6.删除数据

delete from Class --删除所有数据 自动编号没有恢复到默认值 可以根据条件来删除
truncate table Class --重新设置了自动编号 删除只能一次性都清空，不能根据条件来删除 清除速度（性能）比delete语句快的多

delete form Class where tage = 19 or tage is null --删除19岁或者空值
删除重复数据只保留一条（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)

 

7.条件查询，模糊查询

查询数学没有及格的学生的学号
select 
fid as 学号,
fmath as 分数
from MyStudent where fmath<60
查询年龄在20-30岁之间的男学生
select
fname as 姓名 from MyStudent where fage between 20 and 30 and fgender='男'
查询班级id 1 2 3 的所有学生
select * from MyStudent where classid in (1,2,3)
查询所有姓赵的同学 （通配符%表示：任意多个任意字符）
select * from MyStudent where fname like '赵%'
查询出姓名中只要包含一个‘民’字即可。
select * from MyStudent where fname like '%民%'
查询所有姓赵的同学，并且姓名字数是3个
通配符 _ ：表示任意的单个字符。
select * from MyStudent where fname like '赵__'
select * from MyStudent where fname like '赵%' and len(fname)=3
查询出姓名中包含‘民’或‘用’的同学
通配符[]：表示中括号中的任意个字符，只选一个匹配
通配符 ^a ：表示除了a这个字符都行。
select * from MyStudent where fname like '%[民用]%'

 

8.聚合函数

查询数学成绩最高低分
select max(fMath) as 数学成绩最高分 from MyStudent
select min(fMath) as 数学成绩最低分 from MyStudent
平均分（计算平均分的时候对空值不处理）
select avg(fMath) as 平均分 form MyStudent
求数据记录中的总条数（总人数）
select count(*) as 班级总人数 from MyStudent
分数评级
90以上 优秀
80以上 良好
70以上 中
70以下 差
select chengji,
评级=
case
when shuxue >= 90 then '优秀'
when shuxue >= 80 then '良好'
when shuxue >= 70 then '中'
else '差'
end
from Student

 

9.null 问题

请查询出学生表中所有数学成绩为null的人的信息
select * from MyStudent where fMath is null
查询所有fmath为非null的值
select * from MyStudent where fMath is not null
null值与任何数据运算后得到的还是null值。
update MyStudent set fage=fage+1 where fid=1

 

10.分组group by

统计出mystudent表中，男女同学的个数
select 
fgender as 性别, --这时，count（*）统计的是每一组的记录条数， 不是总条数
count(*) as 人数
from MyStudent group by fgender --先执行group by语句分组，分完组在统计每 组个数。 分出来几个组，那么count（*）就统计几次
查询班级的男同学的人数大于2的信息
having是group by的条件对分组后的数据进行筛选（与where类似，都是筛选，只不过having是用来筛选分组后的组的）
select 
classid as 班级号,
count(*) as 班级人数
from TblStudent
where fgender='男'
group by classid
having count(*)>2
语句执行顺序
select
distinct / top 之类的关键字
fgender as 性别, --5》选择列
count(*) as 人数
from MyStudent --1》先从表中拿到数据
where fage>30 --2》从MyStudent的数据中筛选出所有年龄大于30岁的任的信息
group by fgender --3》按照性别分组，分完组得到一个新的结果集
having count(*)>500 --4》基于分组以后的结果集，然后再筛选，筛选出那些组中记录大于500的组
order by 人数 asc --6》最后把显示出来的结果排序
语句执行顺序
from > where > group by > having > select > order by

 

11.日期函数

请查询出所有入职一年以上的员工信息
select * from TblStudent
where dateadd(year,1,tsday)<getdate()
计算两个时间差
查询90年距今是多少年
select datediff(year,'1990-9-9',getdate())
查询一个日期的特定部分
select year(getdate())
select datepart(year,getdate())
输出所有数据中通话时间最长的5条记录。
select top 5 *,'通话时长（秒）'=datediff(second,Startdatetime,Enddatetime) from Calltecords order by datediff(second,Stardatetime,enddatetime) desc

 

12.子查询

把一个查询结果作为另外一个查询的查询源 
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)
子查询分页
显示第一页的数据
分页查询的时候首先是将数据排序
select * from Student order by id desc
第一页 显示5条数据
select Top 5 * from Student order by id desc
第二页
select top 5 * from Student 
where id not in (select top 5 * from Student order by id desc)
order by id desc
第三页
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

 

13.连表查询

查询所有学生的姓名、年龄及所在班级 （班级在另一个表中）
当多个列在不同的表中时，要跨表查询，所以一般可以使用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 是查询所有的数据（没有的为空）
左外联接（左联接）
查询没有参加考试的学生的姓名与编号
把左表（left join 关键字左边的表）中的全部记录都显示出来，对于那些在右表中能找到匹配的记录，显示对应匹配数据，对于那些右表中找不到匹配的记录显示为null
select
ts.tsid,
ts.tsname,
TblScore.*
from TblStudent as ts
left join TblSore.tsid=ts.tsid 
右外联接
表示要将右表（right join 右边的表）中的所有数据都显示，左表中只显示那些匹配的数据。
select
ts.tsid,
ts.tsname,
TblScore.*
from TblStudent as ts
right 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

 

14.视图

视图本身并不存储数据，只是存储的查询语句，如果把真实表中的数据修改后，则通过视图查询到的结果也变了。
视图的目的是方便查询，所以一般情况下不能对视图进行增删改查
在视图中的查询语句，必须为每一列创建一个列名
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

 

15.声明变量与使用

局部变量
声明变量
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

 

16.事务

事务有四个属性：原子性 一致性 隔离性 持久性
原子性：对于数据修改，要么全都执行，要么全都不执行
一致性：当数据完成时，数据必须处于一致状态
隔离性：对数据进行修改的所有并发事务时彼此隔离的。这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务
永久性：事务完成后，他对数据库的修改被永久保持，事务日志能够保持事务的永久性
打开事务
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

 

17.存储过程

创建一个自定义的存储过程
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

 

18.触发器

尽量避免在触发器中执行耗时操作，因为触发器会与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

 

19.游标

定义游标
declare cur_Student cursor fast_forward for select * from Student
打开游标
open cur_Student
对游标的操作
将每条数据读取并输出
将游标向后移动一条
fetch next from cur_Student
将游标循环向后移动，直到末尾
while @@fetch_status=0
begin
fetch next from cur_Student
end
关闭游标
close cur_Student
释放资源
deallocate cur_Student

 

20.（补充）全局临时表，局部临时表

局部临时表：表名以#为开头。只在当前会话中有效，不能跨连接访问。如果直接在连接会话中创建，则当前连接断开后删除，如果是在存储过程中创建的，则存储过程执行完毕后删除
全局临时表：表名以##为开头。多个会话可共享全局临时表。当创建全局临时表的会话断开，并且没有用户正在访问全局临时表时删除

 

21.（补充）约束

删除一列（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

 

转载于:https://www.cnblogs.com/songhe123/p/9505431.html