点击我看文档视频资源

01数据库的基本概念

        数据库(DataBase):按照一定的数据结构来组织,存储和管理数据的仓库,分为关系型数据库和非关系型数据库         

        数据库管理系统(DataBase Management System DBMS):为管理数据库而设计的一个电脑软件系统

        数据库系统:由数据库和数据库管理系统组成

02创建数据库

登录数据库的时候如果数据库在本机上,服务器名称:local | . | 127.0.0.1

        数据库在远程服务器上: ip地址,端口号(如果有的话)

身份验证:windows身份验证(windows身份拥有最高权限,相当于数据库管理员)

               SQL Server身份验证(需要账号,密码,需要先以window身份登进去创建用户并赋予角色)

03数据库的组成

数据库是以文件的形式存在的,由文件和文件组组成

数据库文件:

        a.主要数据文件,扩展名.mdf,存放数据和数据库的初始化信息,每个数据库只能有一个主要数据文件

        b.次要数据文件,扩展名.ndf,可以有0个到多个,当数据比较多的话,可以放到次要数据文件中

        c.事务日志文件,扩展名.ldf,存放用于恢复数据库的所有日志信息.每个数据库至少有一个日志文件,可以有多个

数据库文件组:现在了解为时尚早,之后再说吧

 04数据库常用对象介绍

表    字段    视图   索引           存储过程   触发器   约束   缺省值

(不常用的:函数,事务)

视图:一个或多个表中导出的虚拟表,其数据和数据结构建立在表的查询基础上

索引:提供一种快速访问数据的方式,检索数据时不是对全表进行扫描,而是通过索引快速定位到要查找的数据

存储过程:完成某一功能的sql集合,编译后存储到数据库中,以名称进行调用.

触发器:在数据库中,属于用户自定义的事务命令集合,针对表来说,当对表进行增删改查操作时,命令就会自动触发而去执行

约束: 对数据表中的列进行的一种限制.可以更好的规范表中的列

缺省值:可以对表中的列指定一个默认值.

05数据库的数据类型

数值型:

        a.整型:bigint  int  smallint  tinyint    8->4->2->1->

        b.浮点型:

                float  近似数值,存在精度损失,避免使用等号,而是使用><

                decimal:精确数值,不存在精度的损失   decimal(18,2)

货币类型:money   smallmoney     8->4

二进制数据类型:(使用不太多)

        bit     允许0,1或者null

        varbinary(n)    可变长度的二进制数据最多8000字节

        varbinary(max)     可变长度的二进制数据最多2G字节

        image     可变长度的二进制数据最多2G字节

字符型:

注意:

        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')

        在我工作的数据库中有如下情况:

 是不是很奇怪,aaa表中的name列数据类型是nchar(3),为什么"宋宝涛"保存到数据库中还是乱码?

这是因为在保存至数据库前使用的字符集是cp950(繁体中文),使用该字符集找不到"宝涛"二字符对应的编码,所以就默认保存"?"字符对应的编码. 在查找该数据时使用的字符集是Unicode字符集,巧合的是Unicode字符集和cp950字符集对"?"字符的编码数字值是一样的,所以就查出了"宋??".如果想要避免这种情况,请在插入数据的时候就告诉数据库aaa表中的name列使用的是Unicode字符集,即在前面加上N,如下:

`        insert into aaa select N'宋宝涛'
 

日期型:

其他数据类型:

        只需要记住uniqueidentifier 全球唯一标识符,能够保证生成的每一个标识都是全球唯一的(我的天这是真的吗?)

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 等语法，同时可以使用函数等数据库对象.

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 --批处理命令

        如果只是执行一条语句，有没有GO都一样如果多条语句之间用GO分隔开就不一样了每个被GO分隔的语句都是一个单独的事务

为什么创建数据库要使用master? 

        master:系统数据库,它记录了SQL  Server系统的所有系统级信息,还记录了其他数据库的存在,数据库的文件位置,sql server的初始化信息.

删除数据库:

        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) 
)

在表创建完成后创建约束

--主键
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

13. T-SQL插入数据   --insert DML(数据操纵语言)

--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

14. T-SQL更新删除数据

--更改(注意加where条件,不加where条件会更改整个表)
update aaa_test set c2='c23' where c1=2

--删除(注意:不加where会删除整个表的数据,同时删除后标识列会按照之前的最大值继续自增)
delete from  aaa_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删除数据一旦删除不能恢复

T-SQL查询数据之单表查询

--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

16. SQL查询之模糊查询

17.SQL查询之范围查询

select top 10 * from ProductInfos
select top 10 percent * from ProductInfos

> 
< 
>= 
<= 

<>
in  
not in
between  相當於>= and <=(推荐使用between,因为>= and <=会一条一条的进行判断,而between给一个区间效率会高一些.)(这一句说的对吗?我持怀疑态度)

18. 聚合函数

select count(1) 伪造列,求一个表的记录数
count(1) count(*) 一般使用count(1),因为count(1)的效率要比count(*)的效率要高(是吗?)
2.sum()
3.max()
4.min()
5.avg()
 

19. 分组查询

--统计各部门有多少个用户
select deptId,count(1) from UserInfos--出现在select里面的列必须出现在group by之后,或者包含在聚合函数中
where age>20
group by deptId
having count(1)>5   --分组后的筛选条件.
order by deptId

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之间也可以用> < >= <= <>比较运算符,一般不使用
                                            --如果关联条件有多个可以u.deptid=d.deptid and ...
--隐式连接
select userId,userName,age,d.deptId,d.deptName from UserInfos u, DeptInfos  d 
where u.deptid=d.deptid

--上面这两种写法在效果和效率方面都是一样的.
--如果用户表某个用户的deptid在部门表里不存在,则是查不出该用户的.

21.连接查询之外连接

外连接的分类:左外连接,右外连接,全外连接,简称左链接,右连接,全连接,

左链接:left (outer) join : 右表的行数和左表相同,如果没有匹配上显示null
select userId,userName,age,d.deptId,d.deptName from UserInfos u
left join DeptInfos  d on u.deptid=d.deptid 

右链接:right (outer) join: 左表的行数和右表相同,如果没有匹配上显示null
select userId,userName,age,d.deptId,d.deptName from UserInfos u
right join DeptInfos  d on u.deptid=d.deptid 

全连接:full (outer) join : 左表和右表中的数据都会显示出来(当左表中存在匹配不到右表中的数据显示null,
当右表中存在匹配不到左表中的数据显示null)
select userId,userName,age,d.deptId,d.deptName from UserInfos u
full join DeptInfos  d on u.deptid=d.deptid 
 

22.连接查询之交叉连接

--连接查询之交叉连接  迪卡尔积(用的不多)
--交叉连接:cross join : 如果不带where条件返回的是两个表行数乘积条数据,带where子句等价于inner join 
--交叉连接不能使用on筛选条件
select userId,userName,age,d.deptId,d.deptName from UserInfos u
cross join DeptInfos  d on u.deptid=d.deptid 
where u.deptid=d.deptid 
 

23. 类型转换函数

--convert(类型(长度),表达式)
select convert(varchar,2256)
select convert(varchar(10),getdate(),120)--120日期格式
--cast(表达式 as 数据类型(长度))    --转换成时间时不能指定时间的格式
select cast(2 as varchar)  

24. 数据库函数操作

        24.1.字符串函数操作

--在数据库中索引开始的位置是1
select lower('ABC')--转化成小写
select upper('abc')--转化成大写
select ltrim(' ABC')
select rtrim('abc ')
select reverse('ABC')--从字符串左边起第三个开始,取四个字符

select left('ABCDEFG',3)  --返回字符串左边起三个字符
select right('ABCDEFG',3) --返回字符串右边起三个字符

select charindex('ab','cabdf')----返回字符串中指定的子串出现的开始位置,默认不区分大小写
select charindex('AB','cabdf'collate latin1_general_cs_as )  --大小写敏感  cs代表case sensitive
select charindex('AB','cabdf'collate latin1_general_cs_as )  --大小写不敏感  ci代表case insensitive
select patindex('%ab%','cabdf')--和上面的含义一样,只不过需要在子串前后添加%

select replicate('ABC',3)--从字符串左边起第三个开始,取四个字符

select substring('ABCDEFG',3,4)--从字符串左边起第三个开始,取四个字符

select replace('abcdef' ,'bc', 'gg' ) 
select stuff('abcdef',2,3,'tt')        --attef

select LEN('123456')  --結果:6   得到字符串中字符的個數
select concat('first','last')--将两个字符串拼成一个字符串
select concat('first','-','-','last')   --结果:first--last

24.2.日期函数操作

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) --USA  mm/dd/yy  结果:04/19/2021
select CONVERT(varchar(10),GETDATE(),102) --ANSI  yy.mm.dd  结果:2021.04.19
select CONVERT(varchar(10),GETDATE(),103) --British/French  dd/mm/yy  结果:19/04/2021
select CONVERT(varchar(10),GETDATE(),111) --japan  yy/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

24.3.其他函数操作或关键字

--isnull
--当@a=''或者'     '或者null的时候都会输出11111111
declare @a varchar(50)=null
if ISNULL(@a,'')=''
print '11111111'

--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
)

--isnull(expression1,expression2) 
--如果expression1为null,则取expression2的值,不管expression是否为null
select isnull(null,null)

--coalesce(expression1,expression2,...)
--从左到右返回第一个不为null的值,expression至少有一个非null,即expression不能全为null
select coalesce(null,null,null)

--return的使用:
        --return会无条件的结束查询和程序,return之后语句不会再执行,同时可以向发出呼叫的程序返回一个整数值

        --语法:return [integer_expression]--只能是整数值,如果不写默认返回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 --也可以不要返回的整数值

--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

--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:

25. 索引介绍

索引的作用：提高查询速度

索引是什么？索引类似于查询字典时的拼音和偏旁部首，通过拼音和偏旁部首能够快速的定位到想要查找的字。这样就不用进行全书查找了（全表扫描）

索引会占用存储空间，设置索引时并不是越多越好，索引并不是总能提高系统性能

给一个表的列设为主键时该列会自动被创建为唯一聚集索引

索引分为：聚集索引和非聚集索引

聚集索引：clustered  逻辑顺序和物理顺序是一致的

非聚集索引：nonClustered  逻辑顺序和物理顺序并不一致

聚集索引的查询效率高于非聚集索引

一个表中最多只能有一个聚集索引，可以有0个以上非聚集索引

如果需要在多个列上创建索引，这些列可以创建组合索引

在小数据类型的列上创建的索引访问速度会比较快

索引是看不见的，但是当你查询大数据的时候，他的优势是显而易见的

26. 创建索引

索引分类:聚集索引,非聚集索引,主鍵索引,唯一索引
如果不指定索引的类型，一般创建的都是非聚集索引

--创建聚集索引
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有重复值所有的数据都会被拒绝
)

注意：a.一般我们不会这样创建索引，在我们创建主键和唯一键的时候就自动创建了索引，所以大多数情况下我们不需要创建索引

b.一个表中已经存在了主键（即存在了唯一聚集索引），就不能再创建聚集索引，一个表中只能有一个聚集索引

c.删除索引:     drop index index_name on table_name

27.视图介绍

视图：虚拟表，不包含数据

与表的区别：表存储的是数据，视图存储的是查询语句（索引视图除外）

作用：可以简化我们的查询，增加数据的保密性和安全性(视图只能查询所以安全性有保证)。

缺点：增加了维护成本，并没有提高查询效率。

分类：

        标准视图（经常使用），

        索引视图：被具体化的视图；可以创建索引来显著提高查询性能，聚集了很多行的查询，不太适合经常更新基本数据集

        分区视图：一台或者多台服务器间水平连接一组成员表的分区数据，使数据看起来就像来自一个表

注意：

        我们不仅可以在select中使用视图,我们也可以在insert/delete/update中使用视图,但是要局限于特定情况:
        视图中没有distinct关键字/group by或者having子句/任何聚合函数/union运算符. 此时这个视图已经upate和delete条件了,当视图中有基础表中所有不为空的列才满足insert条件

        但是出于安全考虑我们可能没有某张表的直接权限,只能通过视图修改数据(增删改查),前提是:视图是可更新视图.

28.脚本创建视图

a.创建标椎视图：

create view vUserInfos
as
select u.*,d.deptName from UserInfos u
left join dept d on u.deptID=d.deptID
 

b.创建索引视图：

create view vUserInfos_index with schemabinding
as
select u.userId,u.userName,u.deptID,u.age  from dbo.UserInfos u
go

create unique clustered index uq_vUserInfos_index--给视图创建唯一聚集索引
on vUserInfos_index(userId)
go
 

注意：1.with schemabinding后需要在关联的表名前添加架构(如:dbo等),同时不能使用select  *，需要把所需要的列给一一列举出来，

        2.视图的sql语句有外连接(左外连接,右外连接,全外连接),该视图是不能创建索引的

        3.数据是实际存在的删除视图里面的数据，基础表里面的数据也会被删除（慎用）

(如果基础表有两个及以上，就算视图创建了索引也是删除不了数据的)

创建分区视图：

create view vTest
as
select * from Test1
union all
select * from Test2
 

29.存储过程介绍

存储过程：一种为了完成特定功能的一个或一组sql语句的集合。经编译后存储在服务器端的数据库中，可以利用存储过程加速sql语句的执行。

分类：

        系统存储过程：存在master数据库中，其他数据库中是可以直接调用的，就不需要在前面加上数据库名。创建新的数据库时这些存储过程在新的数据库中自动创建

        自定义存储过程：用户自己创建，可以传入参数，也可以有返回值，表示存储过程是否执行成功。

30.脚本创建存储过程

create proc sp_proc1
@p1 int,
@p2 varchar(20)
as
begin
    declare @sql varchar(2000)
    select * from userInfos
end

31.触发器的介绍

触发器：一种特殊类型的存储过程。

触发器的调用：通过事件触发，自动调用执行。

对表进行插入，更新，删除操作时，如果定义了相应的触发器，系统会自动执行。

分类:DDL(数据定义语言)触发器：create  alter  drop                      不常用

        DML(数据操纵语言)触发器：insert delete update         

DML触发器分两种：after触发器：insert delete update ：操作之后触发

                                instead of触发器：insert delete update ：不执行定义的操作，而执行相应的触发器

32.触发器涉及的两个表：inserted       deleted    

inserted   deleted : 插入表  删除表

两个表都是逻辑表，也是虚表，系统在内存中创建的，不会存储到数据库中。只读的，读取但不能修改数据。结构与操作表的结构相同。触发器执行过程中存在并且可以访问，工作完成之后这两张表会自动删除。

        对表的操作：                                inserted                                deleted

        insert                                      存放插入的数据                             无                     

        delete                                              无                                  存放删除的数据                

        update                                    存放更新后的数据                  存放更新前的数据

inserted：存放插入或者更新后的数据

deleted：存放删除或者更新前的数据

触发器在执行过程中这2个表都是可以访问的，提交之前是不是可以撤销呢--事务的回滚？

答：可以，触发器是一种特殊的事务，可以设置一些数据校验，如果不满足让其回滚。

33.after触发器之insert  delete update

after insert：

create trigger trigger_UserInfos_insert
on UserInfos 
for insert  --for insert 也可以写成 after insert
as    --触发器不能用begin  end
    declare @userId varchar(20)
    declare @userName varchar(20)
    select @userId=userId,@userName=userName  from inserted--inserted表是只读的
    print @userId+'--'+@userName+'--'+'数据插入成功！'
go

after update：

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

   after delete：                                         

alter trigger trigger_UserInfos_delete
on UserInfos
for delete
as
	if(OBJECT_ID('UserInfosBackup','u') is  null)--判断表UserInfosBackup是否存在
	begin
		print 'null'
		select * into UserInfosBackup from deleted
	end
	else
	begin
		print 'not null'
		insert into UserInfosBackup select * from deleted
	end
	declare @userId varchar(20)
	declare @userName varchar(20)
	print '备份成功'
go

34.instead of 触发器之insert  delete update

        instead of触发器和after触发器差不过，只不过after触发器是在执行了定义的操作（insert delete update）后要执行的操作，instead of触发器是执行的定义的操作由触发器里面的操作代替。

instead of  insert：

create trigger trigger_UserInfos_insteadOfInsert
on UserInfos
instead of insert
as
	declare @age  int
	declare @userName varchar(20)
	select @age=age,@userName=userName from inserted
	print '插入操作已被取代'

。。。。。。

36.游标介绍及分类

游标是什么：对数据查询结果集的一种访问机制，用户对结果集进行逐条访问。

游标访问对象:结果集

游标的作用：定位到结果集的某一行，对当前数据进行读取。

数据的结果集是放在TempDB里，相当于内存中，所以游标适用于数据量比较小的情况下使用。

游标的分类：静态，动态，只进，键集驱动游标

静态：对数据库如何操作结果集都不会变，支持滚动，

动态：与静态相对，结果集获取所有的改变，对数据库的操作都可以通过游标可见。api函数或t-sql where current of子句通过游标进行更新。游标外部所做的更新直到提交时才可见。前后滚动。

只进：不支持滚动，从头读到尾，结果集获取所有的改变，对数据库所做的更改是可见的（仅限于提取前）

游标的声明周期：声明游标->打开游标->读取数据->关闭游标->释放游标

37.创建只进游标

--1.声明游标  只进游标是不允许向前翻动的
declare user_cursor cursor local--local创建局部游标，global创建全局游标
for
select userId,userName,age from [dbo].[user] where age>14
--2.打开游标
open user_cursor
--3.取值
declare @userid uniqueidentifier,@userName nvarchar(10),@age int
--提取数据：   next向下，prior向上，first第一个数据，last最后一个数据， 
--absolute n 绝对位置第n个，relative n 相对位置第n个(相对位置正值向前,负值向后)
fetch next from user_cursor  into @userId,@userName,@age

while @@FETCH_STATUS=0  --判断值是否提取成功
begin
	print cast(@userId as varchar(100))+','+@userName+','+cast(@age as varchar)
	fetch next from user_cursor  into @userId,@userName,@age
end
--关闭游标
close user_cursor
--释放游标
deallocate user_cursor

38.创建静态游标

--1.声明游标  static标识该游标是静态游标
declare user_cursor_static cursor static local--local创建局部游标，global创建全局游标
for
select userId,userName,age from [dbo].[user] where age>14
--2.打开游标
open user_cursor_static
--3.取值
declare @userid uniqueidentifier,@userName nvarchar(10),@age int
--提取数据：   next向下，prior向上，first第一个数据，last最后一个数据， 
--absolute n 绝对位置第n个，relative n 相对位置第n个(相对位置正值向前,负值向后)
fetch next from user_cursor_static  into @userId,@userName,@age

while @@FETCH_STATUS=0  --判断值是否提取成功
begin	
	update [dbo].[user] set age=19 where userid='32BECDFF-9886-4ED9-ACF2-AAEA5799B18E'

	print cast(@userId as varchar(100))+','+@userName+','+cast(@age as varchar)
	fetch next from user_cursor_static  into @userId,@userName,@age
end
--关闭游标
close user_cursor_static
--释放游标
deallocate user_cursor_static

39.创建动态游标

--1.声明游标  dynamic标识该游标是动态游标
declare user_cursor_dynamic cursor dynamic local--local创建局部游标，global创建全局游标
for
select userId,userName,age from [dbo].[user] where age>14
--2.打开游标
open user_cursor_dynamic
--3.取值
declare @userid uniqueidentifier,@userName nvarchar(10),@age int
--提取数据：   next向下，prior向上，first第一个数据，last最后一个数据， 
--absolute n 绝对位置第n个，relative n 相对位置第n个(相对位置正值向前,负值向后)
fetch next from user_cursor_dynamic  into @userId,@userName,@age

while @@FETCH_STATUS=0  --判断值是否提取成功
begin	
	update [dbo].[user] set age=33 where current of user_cursor_dynamic--更改当前动态游标所在位置的数据

	print cast(@userId as varchar(100))+','+@userName+','+cast(@age as varchar)
	fetch next from user_cursor_dynamic  into @userId,@userName,@age
end
--关闭游标
close user_cursor_dynamic
--释放游标
deallocate user_cursor_dynamic

40.自定义函数之标量函数

自定义函数：根据自己的需要，自己定义函数

自定义函数分类：标量函数，多语句表值函数，内嵌表值函数

标量函数：对单一值的操作，返回单一值     begin end

内嵌表值函数：相当于参数化的视图，返回一个表，没有begin end

多语句表值函数：返回的也是一个表，只不过多语句表值函数可以多次查询，筛选和合并，弥补了内嵌表值函数的不足  begin end

创建标量函数：

alter function GetAge(@userid uniqueidentifier='2F7A94F2-A41B-40B5-8CEC-0C7B3793443C')
returns int
with encryption --此处可写可不写，如果指定encryption，则函数被加密
as  --此处可写可不写
begin
	declare @age int
	select @age=age from [user] where userid=@userid--加上[]标识user不是关键字或者预留字
	return @age
end

select dbo.GetAge('2F7A94F2-A41B-40B5-8CEC-0C7B3793443C')
--可以使用默认值进行调用函数，如上面的@userid uniqueidentifier='2F7A94F2-A41B-40B5-8CEC-0C7B3793443C'和下面的dbo.GetAge(default)，如果上面的@userid没有指定默认值，下面的dbo.GetAge(default)会返回null
select dbo.GetAge(default)

注意调用标量函数的时候需要带上架构(比如:dbo.)，而内嵌表值函数和多语句表值函数则不需要。

创建内嵌表值函数：

alter function GetUserInfo(@userid uniqueidentifier='2F7A94F2-A41B-40B5-8CEC-0C7B3793443C')
returns table
as
return select * from [user] where userid=@userid
go
--调用函数
select * from GetUserInfo('2F7A94F2-A41B-40B5-8CEC-0C7B3793443C')

select * from GetUserInfo(default)

注意：1.内嵌表值函数里面是没有begin end的

           2.内嵌表值函数的return后面有且仅有一句T-SQL语句

创建多语句表值函数：

create function GetUserInfo(@deptid uniqueidentifier)
returns @userInfos table (userName nvarchar(10),deptId uniqueidentifier,createDate datetime,age int)
with encryption --此处可写可不写，如果指定encryption，则函数被加密
as  --此处可写可不写
begin
	insert into @userInfos select userName,deptID,createDate,age from [user] where deptID=@deptid
	--这里可以写多条插入语句
	return  --后面什么也不跟
end

--调用
select * from GetUserInfo('921EC616-FFD4-4AFB-83CD-702751D1189B')

函数总结：

         a.函数一般做查询使用

         b.如果使用内嵌表值函数就能实现的就不要使用多语句表值函数，因为多语句表值函数效率会低一些。

        c.函数不能够修改基表中的数据，也就是不能使用insert，update，delete语句,但是多语句表值函数可以更改@userInfos表里的值,最终呈现用户所想要的数据,而行内表值函数不能完成这样的操作

42.事务的介绍

什么是事务：一系列T-SQL语句构成的逻辑工作单元。

事务应用：应用于多表操作。

        当事务执行过程中出现异常，系统死机或者断电，数据并没有保存到数据库。使用事务最终的结果要么是所有的操作都成功执行要么是所有的操作都不执行，提高了数据的安全性。

事务的特性：四个特性 (CAID)

原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的操作要么都做，要么都不做。

一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。

隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

持久性（durability）。持续性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。

事务分类：显式事务，隐式事务

事务执行的大概流程：

begin try
begin tran  --开启显式事务
	--T-SQL操作（增删改）
commit tran --提交事务
end try
begin catch
rollback tran --回滚事务
end catch




set implicit_transactions on  --开启隐式事务
--T-SQL操作
commit tran
rollback tran 
set implicit_transactions off  --关闭隐式事务

事务一般放在存储过程中。

显示事务和隐式事务的区别:

总结:隐式事务和显式事务的不同之处在于隐式事务会为我们自动创建新事务,而显式事务需要我们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 的使用

实例:

select * from aaa_user u 
where exists(select DeptCode from aaa_dept d where d.DeptCode=u.DeptCode)

select * from aaa_user u 
where u.DeptCode in(select DeptCode from aaa_dept d)

--这两个sql语句的执行结果是相同的,执行计划中的实际执行方式也是相同的

上面两个语句的执行计划:

其中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

         @@rowcount

        (目前我工作中用的不多,这两个全局变量仅遇到过一次)

        5.2系统内置存储过程

         sp_helptext

exec sp_helptext viewName  --查看创建视图的sql
exec sp_helptext procName  --查看创建存储过程的sql

        5.3系统表

        select * from sysobjects
 

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

如何在 SQL Server 的存储过程和函数里进行错误处理（try catch）

-等学完这个sql后再看

如何在 SQL Server 的存储过程和函数里进行错误处理（try catch）