Sql Service 补漏

1.数据类型

1.1数值型
1.1.1整性类型
bigint -2^63 - 2^63 -1   8子节
int  -2^31 - 2^31 -1       4子节
smallint -2^15 - 2^15 -1  2子节
tinyint 0-255  1子节

1.1.2浮点型
float 存在精度损失 近似值
real 存在精度损失 近似值
decimal 精确数值 （x,y）x总数位 y小数数位

1.1.3货币型
smallmoney
money
 
1.2字符型
1.2.1 非Unicode
char 固定长度不足8000会填满 8000字符
varchar(n) 可变长度字符 8000字符
varchar(max) 2^31-1 字符 ->代替text 类型
text
 
1.2.2Unicode
nchar 固定长度不足4000会填满 4000字符
nvarchar(n) 可变长度字符 4000字符
nvarchar(max) 可变长度字符 2^31-1 / 2 (大概2G) ->代替ntext类型
ntext

带n的存储都是1子节 不带的英文，数字1子节 中文2子节 容易乱码
 
1.3日期型
datetime 1753-1-1 -> 9999-12-31 精度3.33毫秒
datetime2 1753-1-1 -> 9999-12-31 精度100纳秒
samalldatetime 1900-1-1 -> 2079-6-6 精度1分钟
date 仅放日期 0001-1-1 -> 9999-12-31
datetimeoffset 在datetime2上加上时区偏移
timestamp 当表被修改时候 会跟新 时间戳
2.创建数据库
创建新的数据库 需要选择master
master: 系统数据库，记录了sql service系统的所有系统信息，所有其他数据库的村在，数据库文件的位置和sql
service 初始化信息

use master ->选择要操作的数据库
go ->批处理
--创建数据库
create database TestNewBase -- 数据库名称
on primary --主文件组
(
   name='TestNewBase', --主要数据文件的逻辑名
   filename='E:\Test\TestNewBase.mdf' --绝对路径
   size=5MB --数据库初始大小
   filegrowth=1MB --增加量
）
log on --日志文件
(
   name='TestNewBase_log', --日志文件的逻辑名
   filename='E:\Test\TestNewBase_log.ldf' --绝对路径
   size=1MB --日志初始大小
   filegrowth=1MB --增加量 
)
go
3.创建表
use [TestNewBase]
go
create table sys_user --创建表
(
 Id int identity(1,1) primary key not null, --主键 自增长 从1开始 增量1 不为空
 Name nvarchar(20) not null
)
alter table sys_user add Name1 nvarchar(25) null --增加新列
alter table sys_user drop column Name --删除列
alter table sys_user alter column Name1  nvarchar(55) -- 修改列
exec sp_rename 'sys_user.Name1','Name2','column' --重命名列

4.创建约束

create table sys_user
(
id int identity(1,1) not null primary key,
name nvarchar(100) unique, -- 唯一约束
age int not null check(age > 0 or age < 120) --约束
)

create table sys_user1
(
id int identity(1,1) not null primary key,
user1Id int foreign key references sys_user(id) --外键约束
)

在已经生成的表上增加约束

use [TestNewBase]
alter table [dbo].[sys_user2] add constraint PK_ID primary key(ID)
--增加主键
alter table [dbo].[sys_user4] add constraint PPK foreign key (pid)
references [dbo].[sys_user2](id)  --增加外键
5.插入数据
--单条数据插入
insert into [dbo].[sys_user](name,age) values ('1',1)
insert [dbo].[sys_user] (name,age) select '2',2
--多条数据插入
insert into [dbo].[sys_user](name,age) values ('3',3),('4',4),('5',5),('6',6)
--union 去重 union all 可以重复
insert [dbo].[sys_user] (name,age)
select '7',7 union
select '7',7 union
select '8',8 union all
select '8',8 union
select '9',9
--克隆数据
insert into [dbo].[sys_user1](user1Id)
select id from [dbo].[sys_user]
-- 会生成一个表xxx
select id into xxx
from [dbo].[sys_user]
6.删除和修改
update [dbo].[sys_user] set name='aaa' where id = 21
update 表名 set 字段名=xxx,... where 条件
 
 
--删除数据
delete from [dbo].[sys_user] --删除表数据
delete from [dbo].[sys_user] where 条件  -- 按照条件删除
truncate table [dbo].[sys_user] --删除表数据 并恢复表状态到最初值（标识列也恢复）
truncate --不记录日志 不激活触发器 不能回滚 效率高
7.查询
7.1 查询单表
select * from [dbo].[sys_user] --查询表中所有列的所有数据
select * from [dbo].[sys_user] where 条件 --根据条件查询表中所有列的所有数据

--根据条件查询表中所选列的所有数据
select 列名,列名... from [dbo].[sys_user] where 条件
--列加别名

1.列名 as 别名
2.列名 别名
3.别名=列明

 --排序 order by  必须放在最后
desc --从大到小
asc --从小到大
select * from [dbo].[sys_user] order by age desc
逗号隔开排序多个列
 
7.2 模糊查询
--%
--匹配任意位置和长度的数据
select * from [dbo].[sys_user]
where [name] like '%s%'
--_匹配单个字符并控制长度
select * from [dbo].[sys_user]
where [name] like '123___asdq'
--[] 范围匹配 括号中所有字符中的一个
select * from [dbo].[sys_user]
where [name] like '123d[sz]fasdq'
--[^] 范围匹配 不在括号中所有字符中的一个
select * from [dbo].[sys_user]
where [name] like '123d[^a-k][a-f]__dq'
 
7.3范围比较
--范围查询
--比较运算符 < > = >= <=
select * from xxx where xxx.xx > yy and | or xxx.x < yy 等
--in () 和 not in ()
--in 在某个范围内
--not in 不在某个范围内
select * from xxx where xxx.xx in (1,2,3)
select * from xxx where xxx.xx in (select id from yyy where yyy.xx = xx)
--查询前面多少条
select top 100 from xxx --查询前100条数据
--between and
select * from xxx where xxx.xx between 20 and 30 --在20到30的范围内
 
7.4--聚合函数
count --求行数
max --求某列最大值
avg --求某列平均值
sum --求某列总和
min --求某列最小值
 
7.5分组查询
--分组
select * from xxx
where
group by 列明,列明。。。。
select x,count(1) from user
group by x --其中x必须在group by中出现
--having 筛选
select x,count(1) as y from user
group by x --其中x必须在group by中出现
having y > 1 -- 分组后筛选y大于1的数据
 
7.6连接查询
 --inner join on 内连
select * from A a inner join B b on a.x=b.x where ...
--相对一样的
select * from A a,B b where a.a = b.b
--左连接 left join on 返回左表的所有行 如果右边没有匹配则为null
select * from A a left jon B b on a.x = b.x  --显示A中的所有记录
--右连接 right join on 返回右表的所有行 如果左边没有匹配则为null
select * from A a right join B b on a.x = b.x --显示B中的所有记录
--全连接 full outer join on 返回左表和右表的所有值 如果一行中某个表中没有则为null
select * from A a full outer join B b on a.x = b.x 
--迪卡尔积 cross join where  A表和B表行的乘积
select * from [dbo].[sys_user] a cross  join [dbo].[sys_user2] b
8 转换函数和字符串操作函数
--返回字符在字符串中的开始位置
select CHARINDEX('a','sdadaf')
--返回模糊匹配字符的开始位置
--可以输入%a% %a a% a [] [^] 匹配方式和like 一致
select PATINDEX('%a%','ggdfa')
--大小写转换
select UPPER('aaa') --转换为大写
select LOWER('asdBBa') --转换为小写
--取长度
select len('aaaaaa')
--去空格
select LTRIM('   aa    ') --去掉左边的空格
select RTRIM('1   aa    ') --去掉右边的空格
--取子字符串
select LEFT('avcf',2) --从左边开始取几个字符
select RIGHT('abcdfasdf',4) --从右边开始取几个字符
select SUBSTRING('asdasd',1,4) --从字符串的位置开始取几个字符 从左边开始第一位是1
--重复字符
select REPLICATE('abc',3)
--字符串反转
select REVERSE('abcfgfasd')
--字符串替换
select REPLACE('abcd','d','aa')
--从位置2开始3个字符长度的字符替换为t
select STUFF('abacd',2,3,'t')
9索引
--索引分为 聚集索引 和 非聚集索引

聚集索引,表中存储的数据按照索引的顺序存储,检索效率比普通索引高,但对数据新增/修改/删除的影响比较大

非聚集索引,不影响表中的数据存储顺序,检索效率比聚集索引低,对数据新增/修改/删除的影响很小

---- ①.有大量重复值、且经常有范围查询
（between, >,< ，>=,< =）和order by
、group by发生的列，可考虑建立群集索引；
---- ②.经常同时存取多列，且每列都含有重复值可考虑建立组合索引；
---- ③.组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。
 索引失效
条件中有or或者in,not in,  null,not nill
like '%xxx'
条件中有计算和函数
第一个条件不在联合索引里
10 视图
--视图 虚拟表 由一个多个表通过查询定义 将查询定义保存起来
--作用 简化查询 增加数据保密
--缺点 不能增加查询速度，增加维护成本
--分类 标准视图 索引视图 分区视图
--标准视图 保存查询定义 没有数据
--索引视图 能提高查询性能 具体化 不适合经常跟新
标准视图创建
create view Aast
as
  select a.id,b.name from [dbo].[sys_user] a,[dbo].[sys_user2] b
  where a.id = b.name
查询视图
select * from Aast
11 存储过程
分类
1.系统存储过程 
2.自动以存储过程
exec 存储过程名称 参数列表（多个参数逗号隔开）
优势 1.提高sql执行速度
        2.减少服务器压力
        3.提高可移植性
缺点 需要专门维护 占用数据库空间
最基本的形式
 create PROCEDURE 存储过程名称
参数
as
begin
 T-sql语句
end
go
11.1 无参数的存储过程
 create proc AAAA
as
begin
 select * from [dbo].[sys_user]
end
go
执行
exec AAAA
11.2 带参数的存储过程
create proc AddUserNew
@useName nvarchar(50)
as
begin
  declare @age int  --定义变量
  set @age = 10 --变量赋值
 insert into [dbo].[sys_user] (name,age) values (@age,@useName)
end
go
11.3 带输出和输入参数的存储过程
 create proc seleUser
@useName nvarchar(50) out, --输出
@id int
as
begin
  select @useName=name from [dbo].[sys_user] where id = @id
end
go
declare
@name nvarchar(50)
exec seleUser @name out,1002
print @name
12 触发器
触发器：对标进程操作 插入 跟新 删除自动执行
分类
DDL触发器 --create alter drop
DML触发器 --insert update delete

DML触发器分2种
after触发器 insert update delete 操作之后触发
instead of 触发器  insert update delete 不执行定义的操作 而是执行触发器
 
触发器语句中使用了两种特殊的表：deleted 表和 inserted 表。
Deleted 表用于存储 DELETE 和 UPDATE 语句所影响的行的复本。在执行 DELETE 或 UPDATE 语句时，行从触发器表中删除，并传输到 deleted 表中。Deleted 表和触发器表通常没有相同的行。
Inserted 表用于存储 INSERT 和 UPDATE 语句所影响的行的副本。在一个插入或更新事务处理中，新建行被同时添加到 inserted 表和触发器表中。Inserted 表中的行是触发器表中新行的副本。
1.插入操作（Insert）
Inserted表有数据，Deleted表无数据
2.删除操作（Delete）
Inserted表无数据，Deleted表有数据
3.更新操作（Update）
Inserted表有数据（新数据），Deleted表有数据（旧数据）
 
12.1 after-insert 触发器 
create trigger trigger_a
on [dbo].[sys_user]
for insert
as
    declare @name nvarchar(50)
 select @name = name from inserted
 if @name = '123'
 begin
   update [dbo].[sys_user]  set name='2222' from [dbo].[sys_user] where name = '123'
 end
 go

--结构 创建一个插入的触发器
create/alter trigger 触发器名称
on 表名字
for/after insert
as
  T-sql
go
12.2 after-update 触发器
create trigger trigger_b
on [dbo].[sys_user]
for update
as
 declare @befName nvarchar(50)
 declare @aftName nvarchar(50)
 select @befName=name from deleted
 select @aftName=name from inserted
 print @befName
 print @aftName
go
update [dbo].[sys_user] set name='1122334455' where id = 5
--结构 创建一个修改的触发器
create/alter trigger 触发器名称
on 表名字
for/after update
as
  T-sql
go
12.3 after-delete 触发器
create trigger trigger_c
on [dbo].[sys_user]
for delete
as
 declare @befName nvarchar(50)
 declare @aftName nvarchar(50)
 select @befName=name from deleted
 select @aftName=name from inserted
 print @befName
 print @aftName
 if (OBJECT_ID('UserBack','U') is not null)
  insert into UserBack select * from  deleted
 else
  select * into UserBack from deleted
go
--结构 创建一个删除的触发器
create/alter trigger 触发器名称
on 表名字
for/after delete
as
  T-sql
go
12.4 instead of insert 触发器
 create trigger trigger_a
on [dbo].[sys_user]
instead of insert  --不执行sql 而执行触发器中的内容
as
    declare @id int
 declare @name nvarchar(50)
 select @id = id,@name=name from inserted
 print @id
 print @name
go
--id 会是0 表示并没有执行保存操作
--for insert 查询id是一个自增数据
--这是2者的区别
--结构 创建一个instead of insert的触发器
create/alter trigger 触发器名称
on 表名字
instead of insert
as
  T-sql
go
12.5 instead of update触发器
create trigger trigger_a
on [dbo].[sys_user]
instead of update --不执行sql 而执行触发器中的内容
as
 declare @name nvarchar(50)
  declare @name1 nvarchar(50)
 select @name=name from inserted
 select @name1=name from deleted
 print @name
  print @name1
go
--结构 创建一个instead of update的触发器
create/alter trigger 触发器名称
on 表名字
instead of update
as
  T-sql
go
12.6  instead of delete触发器
create trigger trigger_b
on [dbo].[sys_user]
instead of delete
as
  declare @name nvarchar(50)
  select @name=name from deleted
  print @name
go
--结构 创建一个instead of update的触发器
create/alter trigger 触发器名称
on 表名字
instead of delete
as
  T-sql
go
13 游标

游标则是处理结果集的一种机制吧，它可以定位到结果集中的某一行，多数据进行读写，也可以移动游标定位到你所需要的行中进行操作数据。

一般复杂的存储过程，都会有游标的出现，他的用处主要有：

定位到结果集中的某一行。

对当前位置的数据进行读写。

可以对结果集中的数据单独操作，而不是整行执行相同的操作。

是面向集合的数据库管理系统和面向行的程序设计之间的桥梁

根据游标检测结果集变化的能力和消耗资源的情况不同，SQL Server支持的API服务器游标分为一下4种：

静态游标: 静态游标的结果集，在游标打开的时候建立在TempDB中，不论你在操作游标的时候，如何操作数据库，游标中的数据集都不会变。例如你在游标打开的时候，对游标查询的数据表数据进行增删改，操作之后，静态游标中select的数据依旧显示的为没有操作之前的数据。如果想与操作之后的数据一致，则重新关闭打开游标即可。

动态游标:这个则与静态游标相对，滚动游标时，动态游标反应结果集中的所有更改。结果集中的行数据值、顺序和成员在每次提取时都会变化。所有用户做的增删改语句通过游标均可见。如果使用API函数或T-SQL Where Current of子句通过游标进行更新，他们将立即可见。在游标外部所做的更新直到提交时才可见。

只进游标：只进游标不支持滚动，只支持从头到尾顺序提取数据，数据库执行增删改，在提取时是可见的，但由于该游标只能进不能向后滚动，所以在行提取后对行做增删改是不可见的。

键集驱动游标：打开键集驱动游标时，该有表中的各个成员身份和顺序是固定的。打开游标时，结果集这些行数据被一组唯一标识符标识，被标识的列做删改时，用户滚动游标是可见的，如果没被标识的列增该，则不可见，比如insert一条数据，是不可见的，若可见，须关闭重新打开游标。

静态游标在滚动时检测不到表数据变化，但消耗的资源相对很少。动态游标在滚动时能检测到所有表数据变化，但消耗的资源却较多。键集驱动游标则处于他们中间，所以根据需求建立适合自己的游标，避免资源浪费。

游标的生命周期包含有五个阶段：声明游标、打开游标、读取游标数据、关闭游标、释放游标。

基本游标创建

declare user_cu cursor local --Global 全局游标
for
 select name from [dbo].[sys_user]
 --打开游标
 open user_cu
declare @cursor_row cursor, @name nvarchar(50)
set @cursor_row = user_cu
--next 向下 prior向上 fist 第一个 last 最后一个 absolute n 绝对位置  relative n 从当前位置第几个
fetch next from @cursor_row into @name --游标前进into 赋值
while @@FETCH_STATUS = 0
begin
 print @name
 fetch next from @cursor_row into @name --游标前进into 赋值
end
close user_cu
deallocate user_cu

动态游标

declare user_cu cursor dynamic local --Global 全局游标
for
 select name from [dbo].[sys_user]
 --打开游标
 open user_cu
declare @cursor_row cursor, @name nvarchar(50)
set @cursor_row = user_cu
--next 向下 prior向上 fist 第一个 last 最后一个 absolute n 绝对位置  relative n 从当前位置第几个
fetch next from @cursor_row into @name --游标前进into 赋值
while @@FETCH_STATUS = 0
begin
 print @name
 if @name = '3'
    update [dbo].[sys_user] set name = '99999' where name = '11111'
 fetch next from @cursor_row into @name --游标前进into 赋值
end
close user_cu
deallocate user_cu

 
 
 
 
 
 
 
posted @ 2020-08-19 17:36  wujh123  阅读(122)  评论(0)    收藏  举报