SQL Server基础

二. SQL Server数据库

1. 数据库简介

数据库本质是写成磁盘文件，在硬盘中存储

1. 用处
   • 存储大量数据，方便检索和访问
   • 保持数据信息的一致、完整
   • 共享和安全
   • 通过组合分析，产生新的有用信息
2. 数据库发展史
   • 萌芽阶段：文件系统，使用磁盘文件来存储数据
   • 初级阶段：第一代数据库，出现了网状模型和层次模型的数据库
   • 中级阶段：第二代数据库，关系型数据库和结构查询语言
   • 高级阶段：新一代数据库，“关系-对象”型数据库

2. 数据库软件

2.1 软件

1. 分类
   • 系统软件：底层系统（操作系统）
   • 应用软件
     • 通用应用软件：没有个性化，没有区别，大家用的都是一样的
     • 行业应用软件：有个性化，有区别，大家用的都不一样
2. 数据库大小区分
   • 该种数据库所能实现的业务逻辑部分功能的多少
   • 理解为：数据库能够完成的数据库功能的丰富性的多少
   • 功能越复杂，数据库越大

2.2 数据库软件（DBMS）

1. PostgreSQL数据库
   • 美国加州大学伯克利软件工程学院开发的
   • 优点：有很多内置函数
2. Derby数据库
   • 全世界最小的数据库与移动
   • 适用符合移动终端的应用
3. MySql数据库
   • 由几个美籍的丹麦人开发的，2008年SUN公司收购MySql，后SUN公司被Oracle（甲骨文）收购
   • 特点：免费开源的，纯字符端的数据库（要操作数据库必须用命令）
4. DB2数据库
   • IBM的产品，第一个层次型数据库管理系统
   • 特点：支持多操作系统、跨平台、与硬件的结合比较好
5. SQLServer数据库
   • 只能在Windows上运行
   • 特点：不跨平台、有很多个版本
6. Oracle数据库
   • 由拉里埃里森Oracle公司研发
   • 关系-对象型数据库
   • 管理界面由java开发的

3. 数据库

3.1 数据和信息

数据库里面存储的是数据，检索的是信息

• 数据：数据是描述事物的符号，它的形式是多样的，如：数字、文字、图像、声音等
• 信息：有价值的数据集合形成信息，信息可以加工、存储、压缩、传递、共享

3.2 四个名词

1. 数据库Database（DB）
   • 本质上是各种格式的数据文件
   • DB是数据存储的位置，是由操作系统指定的数据在一个硬盘上堆放的位置
2. 数据库管理系统（数据库软件）DataBase Management System（DBMS）
   • DBMS是架设在用户和操作系统之间的，让用户更好的维护操作系统里面数据库的应用程序
3. 数据库管理员Database Administrators（DBA）
4. 数据库应用程序
   • 数据库应用程序是指利用数据库管理系统或者某一种开发语言开发的一款应用程序

3.3 三个世界

现实世界 --> 建模 – 信息世界 --> 模型转换 – 数据库世界 --> 规范化 – 数据库

1. 现实世界
   • 真真正正存在的客观世界，一切事物之间的联系都存在于现实世界
   • 在数据库中表现为物体、物体和物体之间的联系
2. 观念世界
   • 现实世界在人们脑海里的反映，人们用文字、符号标识他们
   • 现实世界的事物在观念世界中以实体的形式被表现出来，反映事物之间的联系的是实体模型
   • 在数据库中表现为实体、实体和实体之间的联系
3. 数据世界
   • 数据世界是观念世界中信息的数据化表现形式

3.4 实体模型

1. 模型
   • 实体：实体是一个有着一系列显著的，易辨认的属性的物体
   • 属性：实体所具有的特性称为属性，一个实体可以有一个或多个属性
   • 实体标识：实体标识是实体的一个属性集，作用是唯一确定一个实例（主键）
   • 域：属性的取值范围称为该属性的域
   • 关系：现实世界中客观事物之间的联系反映在观念世界中以实体之间的关系表现出来
2. 实体关系图（E~R图）
   • 实体名：表的名字，有多少个实体就有多少张表
   • 属性名：表的字段
   • 关系
3. 实体关系
   • 一对一：没有方向，任意的把一个一端放到另外一个去作为外键形成的一对一的关系
   • 一对多：把一端的主键拿出来放在多端里边，作为一个外键形成一对多的关系
   • 多对多：把两个主键拿出来，另外再放一张表，这张表里面两个主键形成一个新表，这三张表形成那两张表的多与多的关系

4. 数据库设计

4.1 设计目标

• 节省数据的存储空间
• 能够保证数据的完整性
• 方便进行数据库应用系统的开发

4.2 数据库设计步骤

1. 收集信息
   • 与该系统有关的人员进行交流、座谈，充分理解数据库需要完成的人物（自然语言）
2. 标识实体（Entiy）
   • 标识数据库要管理的关键对象或实体
3. 标识每个实体的属性
   • 标记属性时没有确认主键
   • 用编号做主键的这种形式里的编号是捏造的
4. 绘制E-R图
   • 分析实体与实体之间的关系
5. 将E-R图转换为表
   • 将各实体转换成对应的表，将各属性转换成各表对应的列
   • 标识每个键的主键列（没有主键的表添加ID编号列，没有实际含义，只用于做主键或外键）
   • 在表之间建立主外键，体现实体之间的映射关系
   • 在转换表时，主外键已经添加进去了
   • 属性的名称就是字段名，属性的类型就是表里面的类型

4.3 物理视图和逻辑视图

1. 物理视图
   • 物理视图（英文名为Physical View）是指数据库中存储数据的实际物理结构，即数据在磁盘上的存储方式
   • 物理视图是与数据库的存储引擎相关的，包括数据表、索引、分区方式等
2. 逻辑视图
   • 逻辑视图（英文名为Logical View）是指用户对数据的逻辑组织和展现方式，在不考虑具体存储结构的情况下，所要求的数据格式
   • 逻辑视图更注重数据的抽象和逻辑关系，用户通过逻辑视图来查看和操作数据，而不需要关心数据的存储方式
3. 举例：
   • 假设有一个数据库中存储了员工信息，其中包括员工的姓名、工号、部门信息等
   • 在物理视图中，这些数据在磁盘上以表的形式存储，可能会涉及到数据页、索引等存储结构；
   • 在逻辑视图中，用户可能希望以表格的形式展示员工信息，包括姓名、工号和部门信息，而不需要了解具体的存储方式。在逻辑视图下，用户可以通过SQL语句查询员工信息，而数据库系统会根据逻辑视图的要求，访问并返回相应的数据。

5. 数据库操作

5.1 SQLServer中的数据库

SQLServer中的数据库是文件型数据库，因为Windows系统是文件型系统

1. 系统数据库
   • 系统数据库里存放的是数据的数据（源数据、结构数据）
   • 分类：master、model、tempdb、msdb
     • master：用来存储系统级信息，例如登录证书、链接信息等
     • model：用来作为创建新数据库的模板。当新数据库创建时，会以model数据库的结构作为基础
     • tempdb：用来存储临时数据、临时表等。在查询过程中使用的临时对象会被存储在tempdb中
     • msdb：用来存储管理数据库维护作业、备份恢复、SQL Server代理作业等相关的信息
2. 用户数据库
   • 用户自己建立的数据库

5.2 数据库文件

1. 数据文件

   每个数据库必须只能有一个.mdf，可以有多个.ndf

   • 数据文件是存放数据库数据和数据库对象的文件
   • 分类：主数据库文件（.mdf）、次要数据库文件（.ndf）
     • 主数据库文件（.mdf）：用来存储数据库的数据和部分或全部用户数据，数据的数据一定存储在主数据库文件
     • 次要数据库文件（.ndf）：主要用来存储用户数据和业务数据
2. 日志文件
   • 日志文件（.ldf）用来存放数据库的日志信息
   • 每个数据库必须至少有一个日志文件，理论上可以有200多个

5.3 文件组

• 组在现实生活中并没有，是人们的想象，逻辑划分
• 文件组是一个或者多个文件的集合，构成分配和管理的单元
• 文件组只包含数据文件，不包含日志文件
• 一个文件不能属于多个文件组
• 一个数据库可以包含主文件组和户定义文件组
• 默认存储在primary文件组中

5.4 数据库操作

1. 创建数据库
   • 应用名：显示在数据库位置的名字，也是将来要调用的名字，如：MyAppDatabase
   • 逻辑名：SQLServer自己操作要用到的名字，如：MyAppDB
   • 物理名（文件名）：放在操作系统里，操作系统要用到的名字，如：MyAppDB.mdf
2. 配置数据库
3. 移动数据库
   • 数据库文件的默认存储位置：
     C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data
4. 备份还原数据库
   • 备份文件默认存储路径为：
     C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Backup
5. 删除数据库

5.5 数据库命令操作

1. 新建数据库：create database 数据库名
2. 修改数据库：alter database 数据库名
3. 查看数据库：sp_helpdb 数据库名
4. 重命名数据库：sp_renamedb 旧名，新名
5. 删除数据库：drop database 数据库名

6. 数据

6.1 数据冗余

1. 数据冗余
   • 数据冗余：数据毫无意义的多余
   • 解决方法：表的拆分
2. 拆分规则
   • 第一范式（原子性范式）
     • 设计出来的表每一个字段必须是原子性的，不可再分的
     • 第一范式的目标是确保每列的原子性
   • 第二范式（主键依赖范式）
     • 在满足第一范式的前提下，除了主键以外的键都要依赖主键
     • 第二范式要求每个表值描述一件事情
   • 第三范式（传递性依赖范式）
     • 在满足第二范式的前提下，除了主键以外的键，不能对主键产生传递性依赖

6.2 数据完整性

1. 完整性
   • 数据完整性：存在不正确、不准确的数据，数据库就失去了完整性
   • 数据完整性：可靠性 + 准确性
2. 分类
   • 域完整性
     • 域完整性：保证域里面的属性的取值一定是对的
     • 约束方法：限制数据类型、检查约束、默认值、非空约束
   • 实体完整性
     • 实体完整性：保证这条记录在表里面唯一存在
     • 约束方法：唯一约束、主键约束、标识列
   • 引用完整性
     • 引用完整性：主表里有的，从表可以引也可以不引；从表里面引的，主表里面必须有（主表里面有的，从表里面不一定有；从表里面有的，主表里面一定有）
     • 约束方法：外键约束
   • 自定义完整性
     • 自定义完整性：现阶段遇见的大多都是自定义完整性