SQL Server
点击我看文档视频资源01数据库的基本概念
数据库(DataBase):按照一定的数据结构来组织,存储和管理数据的仓库,分为关系型数据库和非关系型数据库
数据库管理系统(DataBase Management System DBMS):为管理数据库而设计的一个电脑软件系统
数据库系统:由数据库和数据库管理系统组成
02创建数据库
登录数据库的时候如果数据库在本机上,服务器名称:local | . | 127.0.0.1
数据库在远程服务器上: ip地址,端口号(如果有的话)
身份验证:windows身份验证(windows身份拥有最高权限,相当于数据库管理员)
SQL Server身份验证(需要账号,密码,需要先以window身份登进去创建用户并赋予角色)
03数据库的组成
数据库是以文件的形式存在的,由文件和文件组组成
https://img-blog.csdnimg.cn/20210912170424922.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5a6L6Z-p,size_20,color_FFFFFF,t_70,g_se,x_16
数据库文件:
a.主要数据文件,扩展名.mdf,存放数据和数据库的初始化信息,每个数据库只能有一个主要数据文件
b.次要数据文件,扩展名.ndf,可以有0个到多个,当数据比较多的话,可以放到次要数据文件中
c.事务日志文件,扩展名.ldf,存放用于恢复数据库的所有日志信息.每个数据库至少有一个日志文件,可以有多个
数据库文件组:现在了解为时尚早,之后再说吧
https://img-blog.csdnimg.cn/20210912171414950.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5a6L6Z-p,size_20,color_FFFFFF,t_70,g_se,x_16
04数据库常用对象介绍
表 字段 视图 索引 存储过程 触发器 约束 缺省值
(不常用的:函数,事务)
视图:一个或多个表中导出的虚拟表,其数据和数据结构建立在表的查询基础上
索引:提供一种快速访问数据的方式,检索数据时不是对全表进行扫描,而是通过索引快速定位到要查找的数据
存储过程:完成某一功能的sql集合,编译后存储到数据库中,以名称进行调用.
触发器:在数据库中,属于用户自定义的事务命令集合,针对表来说,当对表进行增删改查操作时,命令就会自动触发而去执行
约束: 对数据表中的列进行的一种限制.可以更好的规范表中的列
缺省值:可以对表中的列指定一个默认值.
05数据库的数据类型
数值型:
a.整型:bigint int smallint tinyint 8->4->2->1->
b.浮点型:
float 近似数值,存在精度损失,避免使用等号,而是使用></p p decimal:精确数值,不存在精度的损失 decimal(18,2)/p pstrong货币类型:/strongmoney smallmoney 8->4
二进制数据类型:(使用不太多)
bit 允许0,1或者null
varbinary(n) 可变长度的二进制数据最多8000字节
varbinary(max) 可变长度的二进制数据最多2G字节
image 可变长度的二进制数据最多2G字节
字符型:
https://img-blog.csdnimg.cn/20210912175809812.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5a6L6Z-p,size_20,color_FFFFFF,t_70,g_se,x_16
https://img-blog.csdnimg.cn/2021091218014547.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5a6L6Z-p,size_20,color_FFFFFF,t_70,g_se,x_16
注意:
a.如果字符只有英文使用character字符串就可以,如果包含英文则使用unicode字符串,避免出现乱码问题.
b.有var前缀的字符类型是可变字符,比如:
定义varchar(100)属性的字段能够容纳的字符只有100个,不能超过100,但是如果存储的只有五个,他也不会浪费存储空间,这就是可变;定义char(100)属性的字段也是只能容纳100个字符,但是如果只存储5个,sql server会插入95个空格填满这列,这样就会很浪费空间了,所以一般使用varchar来存储字符串和文本值
c.我工作中的数据库使用的字符集是cp950(繁体中文),使用这种字符集时character字符一个长度占一个字节,存储一个英文或者数字用一个字节,存储一个中文用两个字节;unicode字符一个长度占两个字节,存储一个英文或数字或中文都是用两个字节.如果一个列的数据类型是char(1),那么该列是不能存储一个汉字的.
查看数据库使用编码的字符集: select serverProperty('SqlCharSetName')
在我工作的数据库中有如下情况:
https://img-blog.csdnimg.cn/a66775298d044f1a8503d48baf0ce39e.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5a6L6Z-p,size_14,color_FFFFFF,t_70,g_se,x_16
是不是很奇怪,aaa表中的name列数据类型是nchar(3),为什么"宋宝涛"保存到数据库中还是乱码?
这是因为在保存至数据库前使用的字符集是cp950(繁体中文),使用该字符集找不到"宝涛"二字符对应的编码,所以就默认保存"?"字符对应的编码. 在查找该数据时使用的字符集是Unicode字符集,巧合的是Unicode字符集和cp950字符集对"?"字符的编码数字值是一样的,所以就查出了"宋??".如果想要避免这种情况,请在插入数据的时候就告诉数据库aaa表中的name列使用的是Unicode字符集,即在前面加上N,如下:
` insert into aaa select N'宋宝涛'
https://img-blog.csdnimg.cn/2021091218203841.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5a6L6Z-p,size_20,color_FFFFFF,t_70,g_se,x_16
日期型:
https://img-blog.csdnimg.cn/20210912182300134.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5a6L6Z-p,size_20,color_FFFFFF,t_70,g_se,x_16
其他数据类型:
只需要记住uniqueidentifier 全球唯一标识符,能够保证生成的每一个标识都是全球唯一的(我的天这是真的吗?)
https://img-blog.csdnimg.cn/20210912182710448.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5a6L6Z-p,size_20,color_FFFFFF,t_70,g_se,x_16
06创建表及其主外键
主键:唯一标识一条数据,值不能重复,不能为空.(可以设置联合主键,两个列作为一个主键)
创建一个主键的同时也默认创建了一个唯一聚集索引.
标识列:一个列设置成标识列,他就不能再手动插入,每插入一条数据会自动生成,可以设置标识列的数据类型是整型(可以设置标识增量和标识种子)
外键:一般在两个表之间要建立关联的时候需要创建外键,一个列创建为外键他在另一个表中必须是主键.(如果在外键表插入一个外键不存在的值是插不进去的)
07数据库约束
约束定义:规定表中的数据规则.如果违反数据规则就会被阻止.
约束可以在表创建时或者创建后进行创建.()
约束分类:
主键 primary key约束
外键 foreign key约束
先建立主表中的主键,然后再定义从表中的外键,只有主表中的主键才能被外表中的外键使用.主表限制了从表的插入和更新操作.当删除主表中的数据应该先删除从表中的相关数据,再删除主表中的数据.
Unique约束 唯一性约束
确保表中的一列数据不能有相同的值,再给一个列设置为唯一键时会为该列自动创建一个唯一非聚集索引
Check约束
通过逻辑表达式来判断数据的有效性,用来限制输入一列或者多列值的范围
Dafault约束 默认值约束
08数据库脚本
数据库脚本:创建数据库对象(数据库对象请看上面)的语句集合.包含:存储过程[Procedure]，事务[transaction]等，索引[Index]，触发器[Trigger]，函数[Function]等。
SQL:
结构化查询语言(Structured Query Language) 简称SQL,是一种特殊目的的编程语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名
T-SQL:
T-SQL 即 Transact-SQL，是 SQL 在 Microsoft SQL Server 上的增强版，它是用来让应用程式与数据库沟通的主要语言。T-SQL为SQL的扩展语言，譬如加入了程序语言中的if,while 等语法，同时可以使用函数等数据库对象.
https://img-blog.csdnimg.cn/20210919205055985.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5a6L6Z-p,size_20,color_FFFFFF,t_70,g_se,x_16
09脚本创建数据库
创建数据库:
use master--选择要使用的数据库
go
create database TestSongDataBase
on primary
(
name='TestSongDataBase', --数据库主要数据文件的逻辑名
filename='D:\DataBase\TestSongDataBase.mdf', --主要数据文件的路径(绝对路径)
size=5MB, --数据库主要文件的初始大小
filegrowth=1MB --文件的增量
)
log on--创建日志文件
(
name='TestSongDataBase_log', --数据库日志文件的逻辑名
filename='D:\DataBase\TestSongDataBase_log.ldf', --日志文件的路径(绝对路径)
size=5MB, --数据库日志的初始大小
filegrowth=10% --文件的增量
)
go go --批处理命令
如果只是执行一条语句，有没有<a target="_blank" target="_blank" target="_blank" href="https://www.baidu.com/s?wd=GO&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1Y4rAc4nj-9PWbsrjnsnAP90ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6K1TL0qnfK1TL0z5HD0IgF_5y9YIZ0lQzqlpA-bmyt8mh7GuZR8mvqVQL7dugPYpyq8Q1fvPHm3Pjmd" title="GO">GO</a>都一样如果多条语句之间用<a target="_blank" target="_blank" target="_blank" href="https://www.baidu.com/s?wd=GO&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1Y4rAc4nj-9PWbsrjnsnAP90ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6K1TL0qnfK1TL0z5HD0IgF_5y9YIZ0lQzqlpA-bmyt8mh7GuZR8mvqVQL7dugPYpyq8Q1fvPHm3Pjmd" title="GO">GO</a>分隔开就不一样了每个被<a target="_blank" target="_blank" target="_blank" href="https://www.baidu.com/s?wd=GO&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1Y4rAc4nj-9PWbsrjnsnAP90ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6K1TL0qnfK1TL0z5HD0IgF_5y9YIZ0lQzqlpA-bmyt8mh7GuZR8mvqVQL7dugPYpyq8Q1fvPHm3Pjmd" title="GO">GO</a>分隔的语句都是一个单独的事务 为什么创建数据库要使用master?
master:系统数据库,它记录了SQL Server系统的所有系统级信息,还记录了其他数据库的存在,数据库的文件位置,sql server的初始化信息.
https://img-blog.csdnimg.cn/2021091716164369.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5a6L6Z-p,size_20,color_FFFFFF,t_70,g_se,x_16
删除数据库:
drop TestSongDataBase
10 T-SQL创建表
产品信息表:
标识列 int
编号 varchar(50)
名称 nvarchar(50)
产品类型 TypeId int
价格 decimal(18,2)
数量 int
产品类型表:
编号 TypeId int
名称 nvarchar(50)
tips:
1.哪个字段被设置成主键,那个字段就会被自动建立唯一聚集索引.
2.只有数据类型是整形的字段才能够被设置标识符
3.标识列和编号都可以被设置为主键,如果想要检索速度更快,将标识列设置成主键.因为标识列是整形设置成主键时创建的索引是聚集索引,而编号的数据类型是字符串类型,设置成主键时创建的索引是非聚集索引(检索时聚集索引要比非聚集索引性能更好)
create table aaasong
(
id int primary key,
num varchar(250)
)
create table aaasong2
(
id int ,
num varchar(250) primary key
) , 这两个表创建的都是聚集索引呀?所以这个观点就是错误的.不管整形还是字符类型的列都可以创建成聚集索引,在插入数据时会根据聚集索引列数据的排序决定其在物理存储位置的顺序
4.价格数据类型可以用decimal,也可以用money(精确到万分位),都是精确值
创建表:
use TestSongDataBase
create table ProductInfos
(
Id int primary key identity(1,1) not null,
ProNO varchar(50) not null,
ProName nvarchar(50) not null,
TypeId int not null,
Price decimal(18,2) default(0.00),
ProCount int default(0)
)
go
create table ProductType
(
TypeId int identity(1,1) primary key not null,
TypeName nvarchar(50) not null
)
go 删除表:
drop table ProductInfos --将表结构及其数据全部删除
go
truncate table ProductInfos --不删除表结构,只删除里面的数据
go
11 SQL修改表
--添加一列
alter table ProductInfos
add ProRemark nvarchar(max) null
--刪除一列
alter table ProductInfos
drop column ProRemark
--修改一列(注意:修改列名要慎重,因为修改列名可能会破坏脚本和存储过程)
alter table ProductInfos
alter column ProNo nvarchar(50) null
--修改列名(需要执行存储过程)
exec sp_rename 'ProductInfos.ProCount','Count','column' 12 T-SQL创建约束(主键,外键,Unique,Check,Default)
在创建表的时候创建外键约束
create table ProductInfos
(
Id int primary key identity(1,1) not null,--主键约束
ProNO varchar(50) not null unique,--unique约束
ProName nvarchar(50) not null,
TypeId int not null foreign key references ProductType(TypeId),--外键约束
Price decimal(18,2) default(0.00) check(price<10000),--default约束和check约束
ProCount int default(0)
) 20. 连接查询之内连接
连接查询:根据两个或者多个表之间的关系,从这些表中查询数据
目的:实现多表查询
分类:内连接,外连接,交叉连接
--显式连接
select userId,userName,age,d.deptId,d.deptName from UserInfos u
inner join DeptInfos d on u.deptid=d.deptid --on用于关联条件 u.deptid和d.deptid之间也可以用> < >= all( select invoice_total from invoices where client_id=3)--发票额度大于client_id=3的客户的任一发票额度的发票都会被筛选出来--any和some作用相同select * from invoiceswhere invoice_total>any( select invoice_total from invoices where client_id=3) --主键
alter table ProductInfos
add constraint PK_ProductInfos primary key(Id)
--外键
alter table ProductInfos
add constraint FK_ProductInfos foreign key(TypeId) references ProductType(TypeId)
--Unique
alter table ProductInfos
add constraint UQ_ProductInfos_ProNo unique(ProNo)
alter table ProductInfos
add constraint UQ_ProductInfos_ProNo unique(ProNo,ProName)--联合多个列的唯一约束
--Check
alter table ProductInfos
add constraint CK_ProductInfos_Price check(price<10000)
--Default
alter table ProductInfos
add constraint DF_ProductInfos_ProCount default(0) for ProCount --1.单条数据
insert into ProductType(TypeName)
values('衣服类')
insert into ProductType(TypeName)
select '食品类' --这种插入方式有点新颖
--2.多条数据
drop table aaa_test
create table aaa_test
(
c1 int primary key identity(1,1),
c2 nvarchar(10) null,
c3 nvarchar(10) null
)
insert into aaa_test(c2,c3)
values('c21','c31'),('c22','c32')
insert into aaa_test(c2,c3)
select 'c21','c31' union
select 'c21','c31'
--注意:这里只能插入一条数据因为union具有去重的操作,使用union all则可以插入两条重复的数据,所以使用union all的效率更高
select * from aaa_test
--3.克隆数据--将一个表中的数据复制到另一个表
--目标表在数据库中已经存在
insert into aaa_test(c2)
select TypeName from ProductType
--目标表在数据库中不存在(注意:创建的新表aaa_test2没有目标表所拥有的约束,索引)
select TypeName into aaa_test2 from ProductType
--更改(注意加where条件,不加where条件会更改整个表)
update aaa_test set c2='c23' where c1=2
--删除(注意:不加where会删除整个表的数据,同时删除后标识列会按照之前的最大值继续自增)
delete fromaaa_test where c1=2
--清空整个表的数据,并对表进行初始化(标识列会从初始值自增),表面上和delete from aaa_test
truncate table aaa_test
--注意:truncate的效率要比delete的高,因为delete每删除一条数据就记录一条日志;truancate 不会记录日志,不会激活触发器.
--truncate drop 是即时操作,不能rollback;delete insert upate 在事务中可以rollback
--慎用truncate,使用truncate删除数据一旦删除不能恢复
--1.在表的查询中最好只查询需要的列.因为这样既可以节省内存,又可以提高查询的效率
--2.给列命别名(三种方式)
select ProNO as '产品编号',ProName '产品名字','产品数量'=ProCount from ProductInfos
--3.排序
select * from ProductInfos order by ProNO,ProCount --默认升序排列(先排ProNO,再排ProCount)
select * from ProductInfos order by ProNO asc,ProCount desc
--统计各部门有多少个用户
select deptId,count(1) from UserInfos--出现在select里面的列必须出现在group by之后,或者包含在聚合函数中
where age>20
group by deptId
having count(1)>5 --分组后的筛选条件.
order by deptId 38.创建静态游标
--convert(类型(长度),表达式)
select convert(varchar,2256)
select convert(varchar(10),getdate(),120)--120日期格式
--cast(表达式 as 数据类型(长度)) --转换成时间时不能指定时间的格式
select cast(2 as varchar) 39.创建动态游标
--在数据库中索引开始的位置是1
select lower('ABC')--转化成小写
select upper('abc')--转化成大写
select ltrim(' ABC')
select rtrim('abc ')
select reverse('ABC') --CBA
select left('ABCDEFG',3) --ABC 返回字符串左边起三个字符
select right('ABCDEFG',3) --EFG 返回字符串右边起三个字符
select charindex('ab','cabdf') --2 返回字符串中指定的子串出现的开始位置,默认不区分大小写
select charindex('AB','cabdf'collate latin1_general_cs_as ) --0 大小写敏感cs代表case sensitive
select charindex('AB','cabdf'collate latin1_general_cs_as ) --0 大小写不敏感ci代表case insensitive
select patindex('%ab%','cabdf')--2 和上面的含义一样,只不过需要在子串前后添加%
select replicate('ABC',3)--ABCABCABC
select substring('ABCDEFG',3,4)--CDEF 从字符串左边起第三个开始,取四个字符
select replace('abcdef' ,'bc', 'gg' ) --aggdef
select stuff('abcdef',2,3,'tt') --attef
select LEN('123456')--6 得到字符串中字符的個數
select concat('first','last')--将两个字符串拼成一个字符串
select concat('first','-','-','last') --结果:first--last 40.自定义函数之标量函数
自定义函数：根据自己的需要，自己定义函数
自定义函数分类：标量函数，多语句表值函数，内嵌表值函数
标量函数：对单一值的操作，返回单一值 begin end
内嵌表值函数：相当于参数化的视图，返回一个表，没有begin end
多语句表值函数：返回的也是一个表，只不过多语句表值函数可以多次查询，筛选和合并，弥补了内嵌表值函数的不足 begin end
创建标量函数：
select GETDATE()
select DATEADD(DAY,2,'2020-03-24')
--获取SQL最小日期,在最小日期加上0天,0月,0年效果是一样的
select DATEADD(DAY,0,0)
--获取最小日期的前一天,也就是最小日期的上一个月的最后一天
select DATEADD(DAY,0,-1)
--计算当前日期加一个月的日期,也就是下个月的今天
select DATEADD(MONTH,1,GETDATE())
--计算GETDATE()与最小日期相差了多少月
select DATEDIFF(MONTH,0,GETDATE())
--计算当月最后一天的日期
select EOMONTH(GETDATE())
--DATEPART(datepart,date)--datepart:要获取时间的哪一部分;date:完整的时间
select datepart(YEAR,getdate())
select datepart(quarter,getdate())--获取时间中的季度
select datepart(MONTH,getdate())
select datepart(day,getdate())
select datepart(week,getdate())--一年中的第几周
select datename(WEEKDAY,getdate())--今天是星期几
select datepart(HOUR,getdate())--时
select datepart(MINUTE,getdate())--分
select datepart(SECOND,getdate())--秒
--用不同的格式显示日期
select CONVERT(varchar(10),GETDATE(),101) --USAmm/dd/yy结果:04/19/2021
select CONVERT(varchar(10),GETDATE(),102) --ANSIyy.mm.dd结果:2021.04.19
select CONVERT(varchar(10),GETDATE(),103) --British/Frenchdd/mm/yy结果:19/04/2021
select CONVERT(varchar(10),GETDATE(),111) --japanyy/mm/dd结果:2021/04/19
select convert(varchar(30),GETDATE(),120) --yyyy-mm-dd hh:mi:ss結果:2021-04-19 14:38:35
select convert(varchar(10),GETDATE(),120) --yyyy-mm-dd hh:mi:ss結果:2021-04-19 注意调用标量函数的时候需要带上架构(比如:dbo.)，而内嵌表值函数和多语句表值函数则不需要。
创建内嵌表值函数：
--isnull
--当@a=''或者' '或者null的时候都会输出11111111
declare @a varchar(50)=null
if ISNULL(@a,'')=''
print '11111111' 注意：1.内嵌表值函数里面是没有begin end的
2.内嵌表值函数的return后面有且仅有一句T-SQL语句
创建多语句表值函数：
--all any some
--select invoices larger than all invoices of client 3 (发票额度大于client_id=3的客户的所有发票额度的发票都会被筛选出来)
select * from invoices
where invoice_total>all
(
select invoice_total
from invoices
where client_id=3
)
--发票额度大于client_id=3的客户的任一发票额度的发票都会被筛选出来
--any和some作用相同
select * from invoices
where invoice_total>any
(
select invoice_total
from invoices
where client_id=3
) 函数总结：
a.函数一般做查询使用
b.如果使用内嵌表值函数就能实现的就不要使用多语句表值函数，因为多语句表值函数效率会低一些。
c.函数不能够修改基表中的数据，也就是不能使用insert，update，delete语句,但是多语句表值函数可以更改@userInfos表里的值,最终呈现用户所想要的数据,而行内表值函数不能完成这样的操作
42.事务的介绍
什么是事务：一系列T-SQL语句构成的逻辑工作单元。
事务应用：应用于多表操作。
当事务执行过程中出现异常，系统死机或者断电，数据并没有保存到数据库。使用事务最终的结果要么是所有的操作都成功执行要么是所有的操作都不执行，提高了数据的安全性。
事务的特性：四个特性 (CAID)
原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的操作要么都做，要么都不做。
一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性（durability）。持续性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。
事务分类：显式事务，隐式事务
事务执行的大概流程：
--isnull(expression1,expression2)
--如果expression1为null,则取expression2的值,不管expression是否为null
select isnull(null,null) 事务一般放在存储过程中。
显示事务和隐式事务的区别:
显式事务隐式事务开启事务执行命令begin tran开启先执行set implicit_transactions on语句,之后再执行sql命令时系统会自动为我们创建事务关闭事务执行命令commit tran或者rollback tran后关闭事务执行命令commit tran或者rollback tran后会关闭当前的事务,之后在执行sql命令系统又会帮我们创建一个新的事务.当我们执行命令set implicit_transactions on后,我们再执行sql命令时系统就不会为我们自动创建事务了总结:隐式事务和显式事务的不同之处在于隐式事务会为我们自动创建新事务,而显式事务需要我们begin tran 显式声明.
---------------------------------------------------华丽的分割线------------------------------------------------------------
如果你已经将上面的全部掌握,那么就可以开始进行下面知识的学习了!
SQL Server拓展知识及进阶
1.将一个表的结构复制到另一个新表中
select * into newSysUser from sysUser where 1=2
--where 1=2:不复制原表中的数据,只赋值结构
--原表中的约束条件(主键,外键,唯一,check,default)及其索引是没有复制到新表newSysUser 中
2.给列名别名的三种方式
select userno c1,UserNO as c2, c3=UserNO from aaa_user
3.exists 和 in 的使用
实例:
--coalesce(expression1,expression2,...)
--从左到右返回第一个不为null的值,expression至少有一个非null,即expression不能全为null
select coalesce(null,null,null) 上面两个语句的执行计划:
https://img-blog.csdnimg.cn/620f72336a0a4a65a160ce13b637edaa.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5a6L6Z-p,size_20,color_FFFFFF,t_70,g_se,x_16
其中hash match(right semi join)的使用可以高效的解决exists 和 in的子查询.
hash match(right semi join)的具体关联过程不知道博友是否知道,希望能有博友在评论区解释一下,小弟不胜感激!
4.数据库表格标椎化设计
在设计数据库表格之前我们要确保我们的设计是最优的,不存在冗余或者重复的数据,因为重复的数据增加了数据库的大小,而且会使插入,更新和删除操作复杂化. 比如:一个人的名字在许多不同的地方重复出现,然后此人决定改名,我们需要更新几个地方,否则数据会不一致,这就是标椎化意义的所在.
标椎化是审查我们的设计,确保它们遵循一些防止数据重复的预定义规则的过程. 基本上有七条规则,被称为七范式,对于几乎99%的应用场景我们只需要遵循前三条就可以了
第一范式:第一范式要求一行中的每个单元格都应该有单一值,且不能出现重复列. 比如courses表中的tag列就不满足第一范式,因为一个课程的标签可能会有多个,同时也不能建立tag1,tag2,tag3等多个标签列,这样会出现重复列
解决方法:为标签单独建立一个tags表与courses表形成多对多的关系,然而在关系型数据库中并没有多对多的关系,只有一对一,一对多的关系,所以为了实现courses和tags两表的多对多关系我们引入一个新的表(course_tags)被称之为链接表.有了链接表之后我们的设计就满足了第一范式,courses表中没有tag1,tag2,tag3等重复的列,然后在一列中也没有多个值,更改标签名字的时候只需要更改tags表中的一条记录就可以了
相反,之前的设计中,coures表中的每个标签会重复出现好多次,如果更改标签时就要更改多条记录
第二范式:第二范式要求每张表都应该有一个单一目的,换句话说,它只能代表一种且仅一种实体类型,那个表中的每一列都应该是描述这个实体.(第二范式满足的前提是第一范式满足)
根据第二范式如果一个表中有一列不是描述该表所代表的实体,就应该拿掉它并单独放入一张表中
比如现在course表里有course_id,title,price,instructor列,那么这个course表就不满足第二范式,因为这里的讲师列不属于这张表,如果同一个讲师教多门课,他的名字就会在这张表中重复出现,所以我们必须在好多个地方进行更改
第三范式:第三范式表示表中的列不应派生自其它的列(第三范式实现的前提是第二范式必须已经实现)
比如:invoices中有invoice_total,payment_total,balance,如果我们用发票总计(invoice_total)-付款总计(payment_total)=结余(balance),比如:100-20=80 , 当我们改变payment_total为40而忘记了更新结余,我们的数据就不一致了
比如:students表中有first_name,last_name,full_name, 那么full_name(first_name+last_name)就不满足第三范式,应该把他取消掉. 所以第三范式和其他范式一样减少重复,并提高数据的完整性
作者建议:不要刻意记忆这些范式,并刻意关注表的设计不满足哪一个范式,只需要专注于消除冗余就可以了,不需要对每张表每个列逐个应用标准化规则,所以当你看到重复值,且这些值不是像1.2.3这样的外键时,这就意味着你的设计没有标准化, 至于它违背了第几范式并不重要,还是想想如何消除重复数据
5.系统对象(列,约束,索引,表,视图,触发器,存储过程,函数,事务)
5.1系统内置全局变量
@@error:返回执行最后一个T-SQL语句的错误号(没有错误返回0)
@@rowcount:返回被最后一个T-SQL语句影响的行数(没有影响返回0)
@@identity:返回最后插入的标识列的值(在一个连接中一直没有插入返回null)
上面的全局变量的作用域都是在一个连接中
5.2系统内置存储过程
sp_helptext
--return的使用:
--return会无条件的结束查询和程序,return之后语句不会再执行,同时可以向发出呼叫的程序返回一个整数值
--语法:return --只能是整数值,如果不写默认返回0
alter proc sp_getUserInfo
as
begin
begin
select top 2 * from aaa_user
return 15
select top 4 * from aaa_user
end
end
declare @a varchar(20)
exec @a= aaaa--获取整数值
select @a
exec aaaa --也可以不要返回的整数值 sp_executesql
实例1:
--case
--语法:
--Simple CASE expression:
CASE input_expression
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END
--Searched CASE expression:
CASE
WHEN Boolean_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END
--实例:
select userNo,userName,
case siteCode
when 'LF' then '廊坊'
when 'LH' then '龍華'
else '其他'
end SiteName
from aaa_user
select userNo,userName,
case
when GradeLevel<30 then '高級領導'
when GradeLevel>100 then '低層領導'
else '中層領導'
end GradeLevel
from aaa_user 实例2:
--goto语句的使用:(不建议使用,因为会增加代码的阅读难度)
--语法:
lable:
Goto lable
declare @level int
select @level=GradeLevel from aaa_user where UserNO='P1187656'
if @level<20
goto print1
else
goto print2
print1:
print 'big boss'
goto theEnd
print '1111'
print2:
print 'lower boss'
goto theEnd
theEnd:
5.3系统表
select * from sysobjects
5.4 系统内置函数
CHECKSUM : 可以用于比较两条记录是否一致,如下,选出A表在B表中不存在的数据
--创建聚集索引
create clustered index PK_UserInfos
on UserInfos(UserId)
with
(
drop_existing=on --on：当存在PK_UserInfos索引的时候会先删除，然后再重新创建
) --off：当存在PK_UserInfos索引的时候会报错
--创建唯一非聚集索引
create unique nonclustered index uq_UserInfos
on UserInfos(userId)
with
(
pad_index=on, --索引页预留空间
fillfactor=70, --数据占整个页存储空间的百分比
ignore_dup_key=on --on:insert操作时，userId包含有重复值的数据会被拒绝，非重复的正常插入
--off:insert操作时，只要userId有重复值所有的数据都会被拒绝
)
--创建复合索引
create unique nonclustered index uq_UserInfos2
on UserInfos(userId,deptID)
with
(
pad_index=on, --索引页预留空间
fillfactor=70,
ignore_dup_key=on --on:insert操作时，userId包含有重复值的数据会被拒绝，非重复的正常插入
--off:insert操作时，只要userId有重复值所有的数据都会被拒绝
) PIVOT
create trigger trigger_UserInfos_insert
on UserInfos
for insert --for insert 也可以写成 after insert
as --触发器不能用beginend
declare @userId varchar(20)
declare @userName varchar(20)
select @userId=userId,@userName=userName from inserted--inserted表是只读的
print @userId+'--'+@userName+'--'+'数据插入成功！'
go create trigger trigger_UserInfos_update
on UserInfos
for update
as
declare @userId_before varchar(20)
declare @userName_before varchar(20)
declare @userId_after varchar(20)
declare @userName_after varchar(20)
select @userId_before=userid,@userName_before=userName from deleted
select @userId_after=userid,@userName_after=userName from inserted
print '修改前:'+@userId_before+@userName_before
print '修改后:'+@userId_after+@userName_after
go 5.运算符
rollup 运算符
with rollup只能应用于聚合函数的列(因为非聚合函数的列加起来没意义),可以对整个聚合函数列求和,并且其运算符要位于group by子句之后,
select salesPersonId,sum(salesVolume) as totalSales from employeePerformanceTable
where salesPersonId>20210101
group by salesPersonId with rollup
having sum(salesVolume)>1000
6.查询优化
30种sql语句优化
推荐阅读:
索引统计信息
索引统计信息 | Microsoft Docs
查询优化建议
查询优化建议 | Microsoft Docs
用于对运行慢的查询进行分析的清单
用于对运行慢的查询进行分析的清单 | Microsoft Docs
逻辑运算符和物理运算符引用
逻辑运算符和物理运算符引用 | Microsoft Docs
认识优化查询中的Merge Join、Nested Loops和Hash Match
如何在 SQL Server 的存储过程和函数里进行错误处理（try catch）
-等学完这个sql后再看
sqlserver中sql执行顺序
我们在开发过程，或多或少都会遇到锁表情况，特别是数据量大的时候..碰到经理客户都在催的时候，我们就需要尽快找到锁表并且解锁。
方法/步骤 1 ---查看锁表 select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName from sys.dm_tran_locks where resource_type='OBJECT'
---spid 锁表进程 ---tableName 被锁表名
---解锁语句 declare @spid int Set @spid = 136 --锁表进程 declare @sql varchar(1000)set @sql='kill '+cast(@spid as varchar)exec(@sql)
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！
页:
[1]