【计算机三级数据库技术】第7章 数据库及数据库对象--附思维导图

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
日志服务 SLS,月写入数据量 50GB 1个月
简介: 文章概述了数据库的创建、维护、架构、分区表、索引和索引视图的操作要点,并提供了SQL Server环境下的具体T-SQL命令示例。内容涵盖了数据库文件的管理、架构的使用、分区表的创建和优化、索引的创建与删除,以及索引视图的定义和应用场景。

1.png

1 创建及维护数据库

1.1 SQL Server五个数据库

    master:记录所有系统信息,包括实例范围的元数据、端点、连接服务器和系统配置设置。还记录了所有其他数据库的存在、数据库文件的位置以及SQLServer的初始化信息
    model:用作实例上创建的所有数据库的模板。当用户创建一个数据库时,系统将自动将Model数据库中的全部内容复制到新建数据库中
    temodb:临时数据库,用于保存临时对象或中间结果集,并未数据的排序等操作提供一个临时工作空间
    msdb:供SQL Server 代理服务调度博阿金和作业以及记录操作员时使用,保存关于调度报警、作业、操作员等信息。作业是在SQLserver中定义的自动执行的一系列操作的几何,作业的执行不需要人工干预
    Resource:是一个只读数据库,包含了所有系统对象,SQLServer系统对象在物理上保存在Resource数据库中,但在逻辑上却显示在每个数据库的sys架构中

1.2 组成

(1)数据文件

  • 主要数据文件
    拓展名.mdf,要求不能小于3M,大于model数据库的大小
  • 次要数据文件
    拓展名.ndf

(2)日志文件
拓展名.ldf
(3)数据库存储空间的分配
在SQLServer中,数据的存储分配单位是数据页,一页是一块8KB=(8060B+132B,8060是存放数据,132存放系统信息)的连续磁盘空间

1.3 数据库文件组

(1)主文件组
(2)用户定义文件组
通过create DataBase 或 Alter DataBase 语句中使用Filegroup 关键字指定的任何文件组

1.4 数据库文件的属性

    文件名及其位置
        一般情况下,如果有多个数据文件的话,为了获得更好的性能,建议将文件分散存储在多个物理磁盘上
    初始大小
        在指定主要数据文件的初始大小时,其大小不能小于model数据库主要文件的大小
    增长方式
    最大大小
        是指文件增长的最大空间限制

1.5 T-SQL创建数据库

Create Database database_name 
ON Primary%指定主文件组
(Name =主文件名
FileName =操作系统物理文件名
Size = 文件的初始大小
Maxsize = 文件最大值
FileGrowth =指定文件的自动增量
)
LOG %指定日志文件
(Name =
FileName =
Size = 
Maxsize =
FileGrowth =
)
FileGroup
(Name =
FileName =
Size = 
Maxsize =
FileGrowth =
)

(1)单个数据文件,单个日志文件
(2)多个数据文件,多个日志文件
(3)分组下的多个数据文件,多个日志文件

1.6 修改数据库

(1)扩大数据库空间

  • 添加数据文件
Alter DataBase database_name
ADD File(Name =
FileName =
Size = 
Maxsize =
FileGrowth =)
  • 添加日志文件
Alter DataBase database_name
ADD LOG File(Name =
FileName =
Size = 
Maxsize =
FileGrowth =)
  • 指定要修改的文件
Alter DataBase database_name
Modify File(Name =
FileName =
Size = 
Maxsize =
FileGrowth =)

(2) 收缩数据库空间

  • 收缩整个数据库大小
DBCC ShrinkDatabase
(DataBase_name,20)20表示20%
  • 收缩指定文件大小
DBCC ShrinkFIle
(DataFIle_name,4)4表示4M

(3)添加和删除数据库文件

  • 添加文件(和创建数据库相同)
ALter DataBase
  • 删除文件
Alter DataBase database_name
Remove File datafile_name

1.7 分离和附加数据库

(1)分离数据库

Exec sp_detach_db 'Database_name','True'
True表示跳过更新统计信息

(2)附加数据库

Create DataBase database_name
ON(FIlename=''
For Attach)

2 架构

2.1 概念

是数据库下的一个逻辑命名空间,可以存放表、视图等数据库对象,是一个数据库对象的容器

2.2 定义架构

Create Schema schema_name Authorization user_name

2.3 删除架构

Drop Schema schema_name Cascade|Restrict

Cascade:删除架构的同时将架构中的所有架构对象一起全部删除
Restrict:如果被删除的架构中包含架构对象,则拒绝删除

3 分区表

3.1 概念

是将表中的数据按水平方式划分成不同的子集,这些数据子集存储在数据库的一个或多个文件组中。使用分区可以快速且有效地管理和访问数据子集,从而使大型表或索引更易于管理

3.2 创建分区表

(1)条件

  • 如果表中有大量的数据都是经常使用的数据,而且她们的操作方式基本一样的,则最好不要使用分区表
  • 如果数据量大,而且数据是分段的,并且对不同段的数据使用的操作不同,则适用于使用分区表
    (2)步骤
  • 创建分区函数
Create Partition Function partition_function_name(input_type)
AS Range [ Left |Right] For Values(1,100,1000)

最后的数值表示分区指定边界值,表示四个分区

  • 创建分区方案
Create Partition Schema partition_schema_name As Partition partition_function_name [All] To (test1fg,test2fg,test3fg,test4fg)
  • 使用分区方案创建表
Create Partition Function myRangePF1(int)
AS Range Left For Values(1,100,1000)
Go
Create Partition Schema myRangePS1 As Partition myRangePF1 To (test1fg,test2fg,test3fg,test4fg)
GO 
Create Table PartitionTable(
coll int,
col2 char(10))
ON myRangePS1(col1);

4 索引

4.1 创建索引

Create [Unique][Clustered |NonClustered ] INDEX index_name 
ON 
[Include ]指定要添加到非聚集索引叶级别的非键列
[Where]
[ON {partition_shema_name}指定分区方案{filegroup_name}]为指定文件组创建索引

举例
(1)创建非聚集索引,要求索引键值Cname升序,CardID降序排序

Create Index Comp_index ON Table_customer(Cname ASC,CardID DESC)

(2)创建分区索引,在TransHistory表和ReferOrderID列上为现有分区方案TransactionsPS1创建非聚集分区索引

Create NonClustered INDEX index_name
ON TransHistory(RefreOrderID)
ON TransactionsPS1(TransactionDate);

4.2 删除索引

Drop INDEX Index_name

5 索引视图

5.1 基本概念

标准视图也成虚拟表,因为这种视图所返回的结果集的格式与基本表基本相同,都是由列和行组成,而且在SQL语句中索引视图的方式也与索引基本表的方式相同。标准视图的结果集并不永久地存储在数据库中,每次通过标准视图查询数据时,数据库管理系统都会在内部将视图的定义替换为对基本表的查询语句,然后再对基本表进行查询

5.2 适合建立索引视图的场合

    如果很少更新基础数据,则索引视图的效果最佳
    如果经常更新基础数据,则维护索引视图的成本可能超过使用索引视图带来的性能收益
    如果基础数据以批处理的形式定期更新,但是在更新之间主要作为只读数据进行处理,可考虑在更新前删除所有索引视图,然后再重建索引视图,这样做可以提高更新的性能
    处理大量行的连接和聚合操作
    许多查询经常执行的链接和聚合操作

5.3 定义索引视图

    基本要求
        定义索引视图时,视图不能引用任何其他视图,只能引用索引表
        视图引用的所有基本表必须与视图位于位于同一个数据库中,并且所有者也与视图相同
        必须使用SchemaBinding选项创建视图
        视图中的表达式引用的所有函数必须是确定的
        对视图创建的第一个索引必须是唯一聚集索引,之后再创建其他的非聚集索引
    %定义视图

举例
首先创建一个视图,并未该视图创建一个唯一聚聚索引,然后是一个使用该索引视图查询语句

Create VIew Slaes,vOrders
With SchemaBinding
As
....
%定义索引视图
Go
Create Unique Clustered INDEX Idx_V1
ON Sales.vOrders (OrderDate,ProductID);
Go
...查询语句,不用From 索引视图
相关实践学习
日志服务之使用Nginx模式采集日志
本文介绍如何通过日志服务控制台创建Nginx模式的Logtail配置快速采集Nginx日志并进行多维度分析。
目录
相关文章
|
2天前
|
前端开发 JavaScript Java
[Java计算机毕设]基于ssm的OA办公管理系统的设计与实现,附源码+数据库+论文+开题,包安装调试
OA办公管理系统是一款基于Java和SSM框架开发的B/S架构应用,适用于Windows系统。项目包含管理员、项目管理人员和普通用户三种角色,分别负责系统管理、请假审批、图书借阅等日常办公事务。系统使用Vue、HTML、JavaScript、CSS和LayUI构建前端,后端采用SSM框架,数据库为MySQL,共24张表。提供完整演示视频和详细文档截图,支持远程安装调试,确保顺利运行。
41 17
|
1月前
|
人工智能 JavaScript 安全
【01】Java+若依+vue.js技术栈实现钱包积分管理系统项目-商业级电玩城积分系统商业项目实战-需求改为思维导图-设计数据库-确定基础架构和设计-优雅草卓伊凡商业项目实战
【01】Java+若依+vue.js技术栈实现钱包积分管理系统项目-商业级电玩城积分系统商业项目实战-需求改为思维导图-设计数据库-确定基础架构和设计-优雅草卓伊凡商业项目实战
101 13
【01】Java+若依+vue.js技术栈实现钱包积分管理系统项目-商业级电玩城积分系统商业项目实战-需求改为思维导图-设计数据库-确定基础架构和设计-优雅草卓伊凡商业项目实战
|
10天前
|
人工智能 Cloud Native 多模数据库
实力见证!数据管理服务DMS、云原生多模数据库Lindorm荣获“2024技术卓越奖”
实力见证!数据管理服务DMS、云原生多模数据库Lindorm荣获“2024技术卓越奖”
|
1月前
|
SQL 存储 关系型数据库
【SQL技术】不同数据库引擎 SQL 优化方案剖析
不同数据库系统(MySQL、PostgreSQL、Doris、Hive)的SQL优化策略。存储引擎特点、SQL执行流程及常见操作(如条件查询、排序、聚合函数)的优化方法。针对各数据库,索引使用、分区裁剪、谓词下推等技术,并提供了具体的SQL示例。通用的SQL调优技巧,如避免使用`COUNT(DISTINCT)`、减少小文件问题、慎重使用`SELECT *`等。通过合理选择和应用这些优化策略,可以显著提升数据库查询性能和系统稳定性。
86 9
|
2月前
|
存储 运维 OLAP
【Meetup回顾 第1期】竟是这样的国产数据库,YashanDB技术内幕曝光
YashanDB是一款基于统一内核,支持单机/主备、共享集群、分布式等多种部署方式,覆盖OLTP/HTAP/OLAP交易和分析混合负载场景的新型数据库系统;YashanDB同时提供开发平台、运维平台和迁移平台3大工具平台以满足数据全生命周期管理。
49 2
【Meetup回顾 第1期】竟是这样的国产数据库,YashanDB技术内幕曝光
|
2月前
|
关系型数据库 分布式数据库 数据库
1月17日|阿里云云谷园区,PolarDB V2.0技术沙龙,畅聊国产数据库
为了助力国产化项目顺利推进,阿里云邀请企业开发者和数据库负责人到云谷园区,与PolarDB V2.0技术专家面对面交流。扫描海报二维码报名,我们将根据信息为您申请入园。欢迎参与,共同探讨PolarDB的最新技术和应用!
|
3月前
|
存储 JSON NoSQL
学习 MongoDB:打开强大的数据库技术大门
MongoDB 是一个基于分布式文件存储的文档数据库,由 C++ 编写,旨在为 Web 应用提供可扩展的高性能数据存储解决方案。它与 MySQL 类似,但使用文档结构而非表结构。核心概念包括:数据库(Database)、集合(Collection)、文档(Document)和字段(Field)。MongoDB 使用 BSON 格式存储数据,支持多种数据类型,如字符串、整数、数组等,并通过二进制编码实现高效存储和传输。BSON 文档结构类似 JSON,但更紧凑,适合网络传输。
94 15
|
3月前
|
人工智能 物联网 大数据
解密时序数据库的未来:TDengine Open Day技术沙龙精彩回顾
在数字化时代,开源已成为推动技术创新和知识共享的核心力量,尤其在数据领域,开源技术的涌现不仅促进了行业的快速发展,也让更多的开发者和技术爱好者得以参与其中。随着物联网、工业互联网等技术的广泛应用,时序数据库的需求愈发强烈,开源的兴起更是为这一技术的创新与普及提供了强有力的支持。
56 3
|
4月前
|
SQL Java 数据库连接
canal-starter 监听解析 storeValue 不一样,同样的sql 一个在mybatis执行 一个在数据库操作,导致解析不出正确对象
canal-starter 监听解析 storeValue 不一样,同样的sql 一个在mybatis执行 一个在数据库操作,导致解析不出正确对象
|
4月前
|
Cloud Native 关系型数据库 Serverless
阿里云数据库获中国计算机学会“科技进步一等奖”!
阿里云数据库获中国计算机学会“科技进步一等奖”!
55 0

热门文章

最新文章