Ms Sql server 数据库整理

最新推荐文章于 2024-06-10 20:16:00 发布

Silen_p

最新推荐文章于 2024-06-10 20:16:00 发布

阅读量418

点赞数 1

分类专栏： Sql 工作交接整理记录

本文链接：https://blog.csdn.net/weixin_40683787/article/details/104924989

版权

Sql 同时被 2 个专栏收录

8 篇文章 0 订阅

订阅专栏

工作交接整理记录

4 篇文章 0 订阅

订阅专栏

（因工作交接需要而整理，囊括了大部分用过的一些总结，部分遗漏或错误欢迎指出）
Ms Sql Server:
微软出的一款关系型数据库
没有My Sql那么轻量级，也没有Oracle那么大型，并且前期不支持在linux上部署（5，6年前开始支持），作为主流数据库中处于不愠不火的地位

1.基础操作：增删改查

--增
insert into [table_Name]([field1],[field2],....) values([val1],[val1],......)
--删
delete [table_Name] where [condition1] and/or [condition2]......
--改
update [table_Name] set [field1]=[val1],[field2]=[val2],......
--查
select [field1],[field2],..... from [table_Name] where [condition1] and/or [condition2] ......

2.创建数据库

--指定路径创建数据库
CREATE DATABASE DBName ON(NAME=N'DBName ',FILENAME=N'D:\DBName ')
--不指定（默认在C盘）
CREATE DATABASE DBName

界面操作：
右键点击数据库->新建数据库->确定
在这里插入图片描述

3.创建表

3.1.字段

CREATE TABLE [dbo].[Table_Name](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[field1] [int] NOT NULL,
	[field2] [datetime] NULL,
	[field3] [smallint] Default(0)
	......
)

或右键点击数据库展开后的表，新建表
在这里插入图片描述
Not Null 和 Null：非空和可空，根据实际需要选择
Default：默认值，当往表中插入一行时，该字段不指定时则为默认值
一般不指定表的创建位置，则会默认创建在primary文件组上
对于时间：dateTime类型，可写 Default(getDate())
IDENTITY：标识种子（初始值1，自增1）
字段命名规范：避免和关键字冲突，如果实在没办法需要重名，实际使用需要用中括号括着（不推荐）

3.2.主键，外键

主键：用于标识该行在表中的唯一性，类比我们的身份证号
外键：用于约束该表中的某字段取值，外键必然是某个表的主键，类比我们填写资料时，需要填写我们个人的信息，一般填写的都是身份证号，而假如我们填写错了身份证号，可能这个身份证不存在，则录入无效，（或者填错别人的身份证号）

选中字段右键设置主键即可
在这里插入图片描述
等价于

CREATE TABLE [dbo].[Table_Name](
	[id] [int] NOT NULL PRIMARY KEY,
	[field1] [int] NOT NULL,
	[field2] [datetime] NULL,
	[field3] [smallint] Default(0)
	......
)

在这里插入图片描述
等价于

CREATE TABLE [dbo].[Table_Name](
	[testid] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[field1] [int] NOT NULL,
	[field2] [datetime] NULL,
	[field3] [smallint] Default(0)
)

复合主键：
在图形界面中貌似只能设置一个主键
可以通过脚本设置：

alter table Table_Name add primary key(field1,field2)

外键：

CREATE TABLE [dbo].[Table_Name](
	[testid] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[field1] [int] FOREIGN KEY REFERENCES Table_Name2(IdFieldName)
)

在设计界面中添加：关系->添加->表和列规范->指定外键对应的主键表表名及字段
在这里插入图片描述

3.3.索引

用途：在表中创建索引，以便更加快速高效地查询数据。
用户无法看到索引，它们只能被用来加速搜索/查询。

更新一个包含索引的表需要比更新一个没有索引的表更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。
索引存储也占用存储空间

索引主要用到的几个：

3.3.1.不可重复的索引：

1.主键索引 即平常的索引，比如我们的身份证号
（主键可以为聚集的或非聚集的，在上述身份证号场景，应该为非聚集）
在这里插入图片描述

2.唯一索引 类似主键但是又不是主键，这一列是唯一的，不可重复的（比如），比如我们每个人的基因序列都是唯一的（但是我们的主键还是用身份证号，因为通过身份证号找到具体哪个人要更方便一点）
在这里插入图片描述

3.复合索引 在身份证号出来之前，怎么区分具体的哪一个人，没法单通过名字来区分，简单举例：我们可以通过住在A村的张三（男），住在B村的张三（女），几姓名，住址，性别这三个能标识一个唯一的人，可以拿来当做复合主键，也可以不作为复合主键

3.3.2.可重复的索引：

1.聚集索引 简单理解就是：有一堆货物，他们有各自的名称，然后产品叫A的，我们将它放在仓库A，B的放在仓库B，当下次我们要找B，就跑去B仓库找，即
在聚集索引中，表中各行的物理顺序与键值的逻辑（索引）顺序相同。
一个表仅能有一个聚集索引
在这里插入图片描述
如图，该索引为创建在primary文件组的聚集索引

2.非聚集索引 简单理解就是：有一堆货物，他们按照生产日期进行保存，比如说2020.3.18有100件货物，里面有A，B，C，2020.3.17有20件货物，里面有C，D。。。就是按照生产日期进行排序
一个表可以有多个非聚集索引
在这里插入图片描述
如图，该索引为创建在primary文件组的非聚集索引

3.全文索引 这个没怎么用过（可能前端搜索时需要考虑这个问题，主要是大量字符串的模糊查询，搜索等）

3.4.临时表及拷贝

临时表的创建：
select filed1,filed2,… into #tmpTable from table_Name
临时表的释放：
drop table #tmpTable

去掉＃号则不是临时表了，而是生成实际存在的表（拷贝）

使用临时表的好处：
作为中间结果，避免多次重复执行某个语句，从而提高查询效率
并且作为中间结果，可以对部分新定义的经过某个运算规则的字段进行命名，参与下一步的语句构建

临时表在每次连接中创建，在连接断开后释放，如果保持长连接时，则这个临时表会一直不释放，从内存和性能角度上考虑，当你不需要使用这个临时表时，最好立即释放（drop）
临时表在Ms Sql Server中比较简便，其他数据库可能也有类似的临时表创建方法甚至不支持临时表，仅支持创建表，其他数据库使用时需要注意如何释放

3.5.表交互

3.5.1.笛卡尔积：

select * from A,B
返回结果是A，B表的笛卡尔积，结果的列数是A和B列数之和，结果条数是A，B中每行两两之间的组合

3.5.2.表连接：

JOIN: 如果表中有至少一个匹配，则返回行
LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行，右表的字段为null
RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行，左表的字段为null
FULL JOIN: 等于左连接和右连接的交集

select * from A (/LEFT/RIGHT/FULL) JOIN B on A.id=B.id

一般应用场景：
往往我们存储个人信息，存储的是身份证号，公司id（外键）
然后公司存储的是公司id，公司名称，公司地址，公司法人信息。。。。。。
然后通过表连接可以查看每个人所属的身份证号及公司名称及一系列详细信息
（分开存储是为了降低数据的耦合性，防止产生过多重复数据，在需要时将其进行连接）
对于经常使用表连接的场景，根据不同应用场合，判断是否有必要将其设为外键

3.5.3表合并：

union
具体使用方法：
select * from A union select * from B
注意A表和B表的字段要（类型）相同，才能完成合并，如果只想让部分字段合并，则修改查询条件
合并后的表名以第一个表的字段名为准

4.构建较复杂sql语句的常用手段及函数

4.1.变量

声明：

declare @varName varType

varType可以是int ，float，varChar(50)…等等
赋值：
直接赋值：

set @varName=value

通过查询语句赋值

select @varName=FieldName from [Table_Name] where [Condition]...

需要注意返回结果与变量类型是否匹配，数量是否匹配（比如赋值一个变量，查出了很多行结果，就存在问题了，多行结果用临时表可以用存储）

4.2.Order by,Group by, Distinct,Count,AVG

4.2.1.排序：

在前面的字段排序优先级更高，排序可按顺序asc（小到大）或逆序desc排（大到小）

select * from table_Name Order by Field1 (desc), Field2 (asc),.....

4.2.2.分组：

当使用分组时，查询的所有字段都需要分组（即不能在查字段ABC的时候仅对字段A分组，如果想实现类似的功能可以通过对字段A排序）

select  Field1,Field2,.... from table_Name Group by Field1,Field2,.....

4.2.3.去重,计数,求平均：

select  count(Field1) from table_Name  
--计数
select  Avg(Field1) from table_Name  
--平均值
select  distinct(Field1) from table_Name  
--Field1中不会出现重复
select  count(distinct(Field1)) from table_Name  
--去重后计数
select distinct(Field1),Field2 from table_Name 
--Field1中可能会出现重复，但是重复时Field2取值不同

对于经常需要使用这类操作的字段，或者要在搜索条件中出现的字段，可以考虑对字段建立索引（不常用作为搜索条件的字段不建议建立索引）

4.3.条件判断

主要是case 和 if

4.3.1.case:

CREATE TABLE [dbo].[test](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[field1] [bit] NULL
)
insert into test values(1),(0),(NULL)
select * from test

在这里插入图片描述

select id,
(case 
When field1 is NULL then 'Null' 
when field1=1 then 'TRUE' 
else 'False' 
end) as casewhen_eg from test

在这里插入图片描述

4.3.2.IF:

多个IF可以互相嵌套

declare @bool bit;
set @bool=1;

if( @bool=1)
Begin
	print('@bool is true')
end
else
	print('@bool is false')

4.3.3.IF EXISTS:

判断内容是否存在

if exists(select * from test)
	print('table test has Rows')

4.4.其他：

4.4.1.DateDiff

判断时间差
左边的时间是被减数，右边的是减数

print datediff(Year,'2015-5-1 23:59:55.000','2019-11-20 23:59:55.000')
print datediff(Month,'2019-5-1 23:59:55.000','2019-11-20 23:59:55.000')
print datediff(Week,'2019-11-1 23:59:55.000','2019-11-20 23:59:55.000')
print datediff(Day,'2019-11-16 23:59:55.000','2019-11-17 23:59:55.000')
print datediff(Hour,'2019-11-16 23:59:55.000','2019-11-17 23:59:55.000')
print datediff(Minute,'2019-11-16 23:59:55.000','2019-11-17 23:59:55.000')
print datediff(Second,'2019-11-16 23:59:55.000','2019-11-17 23:59:55.000')
print datediff(MILLISECOND,'2019-11-16 23:59:55.000','2019-11-17 23:59:55.000')

4.4.2.Row_Number() over(),Lead,Lag

按照某行排序生成序列号
仍然以上述的test表为栗

select ROW_NUMBER() over(Order by id desc) as RowId, * from test

在这里插入图片描述
lead和lad：好像是2012版本才出来的一个东西，目的是能和自身偏移多少行产生联系，看几行之前的某个字段是什么（具体怎么使用就不写了，毕竟电脑用的数据库版本不支持，是2008的）

可以通过Row_Number() over()代替，生成带有行id的临时表，和自身做表连接，连接条件中A.RowID=B.RowID+n 即可

5.存储过程，触发器，视图

这三个都是差不多的东西，将你所需要的一些sql语句操作，封装在了函数中，在某些条件或人为调用时触发，返回想要的结果
存储过程：
存储过程可以有一个或多个返回，也可以没有返回，只是做了点什么操作（和函数差不多）

CREATE PROC [dbo].[sp_test]
    (
      @input int,
      @input2 tinyint,
      @input3 float
    )--变量声明
AS  
	--具体操作
Go

视图：

CREATE VIEW [dbo].[test_vw]
AS

select * from table1 inner join table2 on table1.id=table2.id

GO

视图一般返回的是一个表，调用时将视图当成一个表来用即可,目的主要是为了简化sql语句

select * from [test_vw]

触发器
触发器有多种，针对四种基本操作，增删改查，触发方式有之前，之后，代替，根据实际需要自己设计
能直接使用表格的字段名称作为该字段的值

CREATE  TRIGGER [dbo].[triger_test] ON [dbo].[table1]
    INSTEAD OF INSERT
AS
--具体操作
GO

6.表分区

表分区一般应用在数据量很大的表中，分区为了加快查找速度，但同时带来很多不易维护的问题，有利有弊
还有一种是分表，分表没有太多的难度或维护问题，但是会给前端带来跨表查询的痛苦
可以根据具体场合选择不同的方式
之前有过相关的博客，但是感觉可能写的还不是很清楚，所以将再唠叨一遍

几个概念：
表分区：
物理存储为多个文件，逻辑上是一个表
分区方案：
与分区函数关联的一套方案
分区函数：
描述怎么进行分区，以什么类型的字段作为分割点，以及存储方向，分割点往左还是分割点往右
文件组：
内包含一组文件，类似文件夹，一般数据库包含primary（主）文件组
文件：
数据库物理存储的具体单位，必须为其指定所属某个文件组

分区步骤：
以实际开发过程中的脚本为例（至于名字什么的，根据实际情况改就可以了）
1.创建初始文件组，以及初始文件

DECLARE @sql VARCHAR(2048),@SensorDatagroup varChar(50),@filePath varChar(50)
declare @fileName varChar(50),@tableName varChar(50)

--创建当天的加速度文件
SELECT  @filePath = itemValue FROM  SensorSettings WHERE   item = 'FileGroupPath';
set @SensorDatagroup='AccelData' + CONVERT(varchar(8),GETDATE(),112)
IF NOT EXISTS ( SELECT  1 FROM  sysfilegroups WHERE  groupname = @SensorDatagroup ) 
begin
	SET @sql = 'alter database [kjtx_TVSensor] add filegroup['+@SensorDatagroup+']';  --创建文件组
	print @sql
	exec (@sql)

	set @tableName='AccelOriginData' + CONVERT(varchar(8),GETDATE(),112)
	SET @fileName = @filePath + @tableName + '.ndf';
	SET @sql = 'alter database [kjtx_TVSensor] add file(name=''' + @tableName 
	+ ''' ,filename=''' + @fileName + ''',size=10MB,maxsize=10GB,filegrowth=10MB) to filegroup[' + @SensorDatagroup + ']';
	EXEC(@sql);
end

2.创建分区函数，指定分区参数类型（比如按日期）（一般是日期或某个索引index）
3.创建分区方案，关联指定的分区函数

--加速度分区函数和分区方案
CREATE PARTITION FUNCTION PARTITION_FUNCTION_AccelData( DATETIME )--创建分区函数，按日期分区
AS RANGE RIGHT
FOR VALUES()
--------------------
set @sql = 'CREATE PARTITION SCHEME PARTITION_SCHEME_AccelData
AS PARTITION PARTITION_FUNCTION_AccelData To ('+@SensorDatagroup+')'
exec (@sql)

注：
分区函数中的方向为右（AS RANGE RIGHT），即假设时间界限为t0，在t0之后的则落在最新的分区中
由于文件组是动态的的变量，所以这边选择拼接成一个sql语句后再执行

4.创建分区表
注意创建分区表所指定的文件组应为上述步骤创建的分区方案（并且参数中填的类型要与之对应）

on [PARTITION_SCHEME_AccelData]([OccTime]);

根据平时查询常用的字段为其创建聚集索引与非聚集索引

--加速度存储表
USE [kjtx_TVSensor]
GO
CREATE TABLE [dbo].[AccelData](
[SensorNum] [int] NOT NULL,
[OccTime] [datetime] NOT NULL,
[GroupSeqNum] [smallint] NOT NULL,
[FrameSeqNum] [smallint] NOT NULL,
[rssi] [smallint] NOT NULL,
[Data] [varchar](750) NOT NULL,
[isHisData] [bit] NOT NULL,
[inDbTime] [datetime] NOT NULL,
) on [PARTITION_SCHEME_AccelData]([OccTime]);
ALTER TABLE [dbo].[AccelData] ADD  CONSTRAINT [DF_AccelData_isHisData]  DEFAULT ((0)) FOR [isHisData]
GO

create clustered index idx_SensorNum
on AccelData(SensorNum)

on PARTITION_SCHEME_AccelData(occTime);
go
CREATE NONCLUSTERED INDEX [IX_AccelData_SensorNum_1] ON [dbo].[AccelData] 
(
	[occTime] DESC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) 
on PARTITION_SCHEME_AccelData(occTime);

GO

5.维护：按日分区
首先判断是否存在当天的文件组，不存在则创建

DECLARE @sql VARCHAR(2048),@SensorDatagroup varChar(50),@filePath varChar(50)
begin
SELECT  @filePath = itemValue FROM  [kjtx_TVSensor].[dbo].[SensorSettings] WHERE   item = 'FileGroupPath';

--创建加速度分区
set @SensorDatagroup='AccelData' + CONVERT(varchar(8),GETDATE(),112)

IF NOT EXISTS ( SELECT  1 FROM  sysfilegroups WHERE  groupname = @SensorDatagroup ) --不存在文件组时
begin
	SET @sql = 'alter database [kjtx_TVSensor] add filegroup['+@SensorDatagroup+']';  --创建文件组
	print @sql
	exec (@sql)		
end

判断是否为当天文件组分配文件

declare @fileName varChar(50),@tableName varChar(50)
DECLARE @result INT   --返回值 1代表有 0代表没有
set @tableName='AccelOriginData' + CONVERT(varchar(8),GETDATE(),112) --给新文件组指定文件
SET @fileName = @filePath + @tableName + '.ndf';
EXEC MASTER.dbo.xp_fileexist @fileName, @result OUT

不存在文件时，为其分配文件，并且更新分区函数

if (@result=0)--不存在文件时创建文件
begin
	SET @sql = 'alter database [kjtx_TVSensor] add file(name=''' + @tableName 
	+ ''' ,filename=''' + @fileName + ''',size=10MB,maxsize=10GB,filegrowth=10MB) to filegroup[' + @SensorDatagroup + ']';
	print @sql
	EXEC(@sql);
	set @sql = 'ALTER PARTITION SCHEME PARTITION_SCHEME_AccelData	
	NEXT USED ['+@SensorDatagroup+']'--分区方案：使用新文件组
	print @sql
	exec (@sql)

	alter partition function PARTITION_FUNCTION_AccelData() --分区函数：将明天以后的数据划在新分区文件中
	split range(CONVERT(varchar(8),GETDATE(),112))
	--将此之后的数据放入新分区
end

一般会将这些步骤抽取出来为存储过程，定期调用（通过程序或者sql server 的作业）
6.维护删除历史数据
收缩文件，移除文件，移除文件组
举例：文件组及文件的命名方式都是前缀+年月日时：

set @Sql='
		DBCC SHRINKFILE (_exFileName'+CONVERT(varchar(100), @Day, 112) +', EMPTYFILE);
		ALTER DATABASE DataBaseName REMOVE FILE _exFileName'+CONVERT(varchar(100), @Day, 112)+';      
		ALTER DATABASE DataBaseName REMOVE FILEGROUP _exFileGroupName'+CONVERT(varchar(100), @Day, 112) 
		Exec (@Sql)

简单可通过移除某段时间以前的所有历史数据
如果需要涉及到带权重的，例如之前设计过的一个清理模型是每个月定期检查一次，将3个月以前的数据减半清理，可能就需要结合到游标遍历等，具体需求决定怎么写，就不细谈了

7.代理作业

SQL Server部分有趣的整理（5）通过代理定期执行维护作业
可以结合表分区使用，定期创建，定期清理
注意sql server 的代理服务可能默认没开启或禁用了，在部署数据库的脚本中需要考虑这一点

Silen_p

关注

1
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
Ms Sql server 数据库整理

（因工作交接需要而整理，囊括了大部分用过的一些总结，部分遗漏或错误欢迎指出）Ms Sql Server:微软出的一款关系型数据库没有My Sql那么轻量级，也没有Oracle那么大型，并且前期不支持在linux上部署（5，6年前开始支持），作为主流数据库中处于不愠不火的地位目录1.基础操作：增删改查2.创建数据库3.创建表3.1.字段3.2.主键，外键3.3.索引3.3.1.不可重复的索引...
复制链接

扫一扫