数据库学习笔记
数据库系统的基本原理
介绍基本概念、发展阶段、数据模型、DBMS的功能及组成、DBS的组成及全局结构
数据库概述
1数据库系统的应用
广泛应用在电信业、银行业、金融业、销售业、电商、大学、航空业、人力资源、制造业
数据库系统的概念
数据Date
数据：数据库存储的基本对象，是描述现实世界中各种事物或抽象概念的、可存储并具有明确意义的符号记录。
具体事物：有形且看得见的事物 抽象事物：无形且看不见的事物
记录是数据库系统表示和存储数据的一种格式
数据库DB
简单上，数据库：相互关联的数据集合
严格上，数据库：长期存储在计算机内、有组织、可共享的大量数据的集合。
数据库具有较小的冗余度（不能杜绝数据冗余）、较高的数据独立性、易扩展性
数据库管理系统DBMS
数据库系统核心部分，介于用户和OS之间。提供访问数据库的方法（数据库定义、建立、查询、更新及其他数据控制）
主要功能
数据定义功能
使用数据定义语言（DDL），在数据库中定义数据对象（表、视图、索引、存储过程等）、数据的完整性约束等
数据的完整性（主要）：实体完整性、参照完整性、用户定义的完整性
数据操纵功能
使用数据操纵语言（DML），对数据库进行增、删、改、查操作（insert、delete、update、select）
查询功能使用频率最高
数据控制功能
使用数据控制语言（DCL），进行用户权限的变动，如授予或撤销，即安全性控制：解决多用户对数据库的并发使用所产生的事物处理问题（并发控制；数据库的转储、恢复功能；数据库的性能监视、分析功能）
数据库系统DBS
数据库系统：计算机系统引入数据库后的系统，由DB、DBMS、应用系统、数据库管理员DBA构成。
数据库应用系统DBAS
DBAS：指实现业务逻辑的应用程序
要求：提供一个有好的、人性化的操作数据的GUI
数据管理技术的发展阶段
人工管理阶段
用途：科学计算
处理方式：批处理
特点：数据不保存、不具有独立性、不共享；只有程序概念，无文件概念
文件管理系统阶段
用途：科学计算、信息管理
处理方式：批处理、联机实时处理
缺点：数据冗余性和不一致（数据重复存储且可能有差异）、数据独立性差、数据孤立
数据库系统阶段
用途：大规模管理
处理方式：批处理、实时联机处理、分布处理
特点：数据结构化；数据共享性高、冗余度低、易扩充；数据独立性高（物理独立性、逻辑独立性）；DBMS统一管理和控制（包括数据的完整性检查、并发控制、数据的安全性保护、数据库的恢复）
数据库系统的用户
数据库管理员
①参与数据库的设计
②定义数据的安全性要求和完整性约束条件
③日常维护：定期备份数据库、监视数据库的运行、保障有充足的空余磁盘空间
④数据库的改进、重组和重构
数据库用户
①最终用户：现实系统中的业务人员，数据库的主要用户
②专业用户：包括工程师、科学家、经济学家等具有较高科学技术背景的人员
③系统分析员和数据库设计人员
④应用程序员
数据模型
模型是对现实世界的抽象
数据抽象的过程
美国国家标准化协会（ANSI）根据数据抽象级别定义了四种模型：概念模型、逻辑模型、外部模型、内部模型
数据抽象过程：
①综合：根据用户需求设计数据库的概念模型
②转换：根据转换规则将概念模型转换为数据库的逻辑模型
③实现：根据用户业务特点设计不同的外部模型
④根据逻辑模型设计内部模型
模型介绍：
概念模型
抽象级别最高，表达用户需求观点的数据库全局逻辑结构的模型
特点：
①表达了数据库的整体逻辑结构，企业管理人员对整个企业组织的全面概述
②从用户需求的观点出发对数据建模
③独立于软硬件。不依赖于硬件设备，不依赖于实现时的DBMS软件
④数据库设计人员与用户之间进行交流的工具
现在概念模型多是实体-联系模型（E-R模型，使用E-R图表示）。
实体是现实世界或客观世界中可相互区别的对象（抽象或具体）。
联系：两个或多个实体之间的关联，分为三种：
①一对一（1:1）②一对多（1：N）③多对多（M:N）
逻辑模型
特点：
①表达了数据库的整体逻辑结构，设计人员对整个企业组织数据库的全面概述（与概念模型不同，主要区分）
②从数据库实现的观点出发对数据建模
③硬件独立，软件依赖
④数据库设计人员与应用程序员之间交流的工具
类别：层次模型（数据结构为树状结构）、网状结构（数据结构为有向图）、关系模型（数据结构为二维表格，现在关系型DBMS均采用关系模型）
外部模型
特点：
①逻辑结构的一个逻辑子集（可以有多个）
②硬件独立，软件依赖
③反映了用户使用数据库的观点
系统考查上，特点为：简化了用户观点、有助于数据库的安全性保护、对概念模型的支持
内部模型（物理模型）
数据库最底层的抽象（描述了数据在磁盘上的存储方式、存取设备、存取方法）。
与硬件和软件紧密相连的。
关系模型
1970年，IBM公司 E.F.Codd 首次提出
数据模型的三要素
①数据结构
所描述的对象类型的集合，对系统静态特性的描述。
②数据操作
对系统动态特性的描述。
③数据的完整性约束
一组完整性规则，保证数据的正确性、有效性、相容性、
必须满足实体完整性和参照完整性。
关系数据模型的数据结构
①关系（relation）
一个关系即为一个规范的二维表。
基本条件：关系的每一列不可再分。即不允许表中还有表
②元组（tuple）
二维表中除第一行外的每一行为一个元组。
一个元祖代表一条完整的信息。
③属性（attribute）
二维表中的每一列为一个属性。
④码（key）
码：关键字或关键码。
由一个属性或属性的组合能够唯一确定一个元组，则该属性或属性的组合称为码。
⑤关系模式
一般描述：
1 | 关系名（属性1，属性2，属性3，属性4，···，属性n） |
主要操作：查询、插入、删除、更新数据（必须满足关系的完整性约束条件）
关系的完整性约束条件：实体参照性、参照完整性、用户定义的完整性
数据库体系结构
数据库系统的三级结构
1975年，美国ANSI/SPARC报告提出三级划分法。
用户级数据库
对应外模式，最接近用户，用户可以看到和使用的数据库（又称用户视图）。
主要由外部记录组成，不同视图可重叠。
所有操作都是针对用户视图。
概念级数据库
对应概念模式，介于用户级和物理级之间。数据库管理人员看到和使用的数据库（又称DBA视图）。
物理级数据库
对应内模式，数据库底层表示。描述数据的实际存储组织，最接近与物理存储的级别（又称内部视图）。
由内部记录组成。
注意：物理级数据库不是真正的物理存储（只是对物理存储的抽象），最接近物理存储。
数据库系统的三级模式
1概念模式
又称模式或逻辑模式。
数据库中全体数据的逻辑结构和特征的描述，所有用户的公共数据视图。
具有唯一性。即一个数据库只有一个概念模式。
外部模式
又称子模式和用户模式。
数据库用户可以看到和使用的局部数据的逻辑结构和特征的描述。数据库用户的数据视图
具有不唯一性。
内部模式
又称存储模式或物理模式。
数据物理结构和存储方式的描述，数据在数据库内的表示方式。
具有唯一性。
数据库系统的二级映射与数据独立性
数据库系统的二级映射
外模式/模式映射 模式/内模式映射
三个抽象级通过二级映射进行相互转换，形成一个统一的整体。
数据独立性
应用程序与数据之间的独立性。包括两种如下：
①物理独立性
应用程序与磁盘上的数据库中的数据是独立的。
通过模式/内模式映射来实现的。
②逻辑独立性
应用程序与逻辑结构是相互独立的。通过外模式/模式映射来实现的。
关系数据库标准语言SQL
SQL 语言介绍
近期版本是SQL2016，主要新特性为行模式识别、支持json对象、多态表函数等
SQL数据库的体系结构
特点：
①SQL用户可以是应用程序或终端用户。
②使用SQL语言对基本表和视图进行查询。
③一个视图是从若干个基本表或其他视图上导出的表。
④一个或一些基本表对应一个数据文件。
⑤一个基本表可以带若干个索引。
⑥一个表空间可以由若干个数据文件组成。
⑦一个数据库可以由若干个存储文件组成。
SQL的特点
Structured Query Language 结构化查询语言
①综合统一
②高度非过程化
③面向对象的操作方式
④同一种语法结构提供两种使用方式（即是独立语言又是嵌入式语言）
⑤语言简介、易学易用（结构化查询语言）
SQL语言的组成
1.数据定义语言（DDL）
create：创建数据库、表、视图等
alter：修改
drop：删除数据库、表、视图等
rename：重命名
truncate：删除表中所有数据但不删除表
2.数据操作语言（DML）
select：查询
inset：添加新数据
update：更新现有的数据
delete：删除指定行的数据
3.数据控制语言（DCL）
DCL用于事务控制、并发控制、完整性和安全性控制等。
commit：保存修改
rollback：回滚修改
savepoint：设置保存点
locktable：锁定数据库中的一个表
数据库编程
常量
常量：程序本身不能改变其值的数据
（1）字符串常量
使用单引号或双引号括起来的字符串序列，一般使用单引号。
（2）数值常量
整数常量和小数常量。可使用科学计数法表示，如 1e10、1.25E10*。
（3）日期和时间常量
单引号括的特定格式的字符日期值。如 ‘yyyy\mm\dd hh:mm:ss’、yyyy-mm-dd hh:mm:ss.
日期时间之间可以使用比较符号比较大小
（4）布尔值常量
只有true、false两个值。SQL命令的运行结果：1表示true，0表示false。
（5）NULL值
NULL值适用于各种类型的字段，表示“不确定的值”。有NULL值参与的运算结果一定为NULL值。
变量
变量：程序运行过程中可以改变其值的数据
局部变量
格式：@+局部变量名
（1）定义与赋值
1 | set @局部变量名[,@局部变量名,···];#定义 |
（2）显示
1 | select @局部变量名; |
（3）其他
局部变量名可作为 select语句 中 where子句 的比较条件。
如：
1 | select * from 表名 where name = @局部变量名 |
全局变量
全局变量：MySQL 系统提供并赋值的变量。用户不能定义全局变量，只能使用。
格式：@@+全局变量名
| 全局变量名 | 说明 |
|---|---|
| @@back_log | 返回MySQL主要连接请求的数量 |
| @@basedir | 返回MySQL安装目录基准 |
| @@license | 返回服务器的许可类型 |
| @@port | 返回服务器侦听TCP/IP链接的端口号 |
| @@storage_engine | 返回存储引擎 |
| @@version | 返回服务器版本号 |
显示：
1 | select @@全局变量名 |
SQL查询语句的执行顺序
先执行 FROM 确定主表，再执行 JOIN 连接，然后 WHERE 进行过滤，接着 GROUP BY 进行分组，HAVING 过滤聚合结果，SELECT 选择最终列，ORDER BY 排序，最后 LIMIT 限制返回行数。
WHERE 先执行是为了减少数据量，HAVING 只能过滤聚合数据，ORDER BY 必须在 SELECT 之后排序最终结果，LIMIT 最后执行以减少数据传输。
这个执行顺序与编写 SQL 语句的顺序不同，这也是为什么有时候在 SELECT 子句中定义的别名不能在 WHERE 子句中使用得原因，因为 WHERE 是在 SELECT 之前执行的。
LIMIT 为什么在最后执行？
因为 LIMIT 是在最终结果集上执行的，如果在 WHERE 之前执行 LIMIT，那么就会先返回所有行，然后再进行 LIMIT 限制，这样会增加数据传输的开销。
ORDER BY 为什么在 SELECT 之后执行？
因为排序需要基于最终返回的列，如果 ORDER BY 早于 SELECT 执行，计算 COUNT(*) 之类的聚合函数就会出问题。
| 执行顺序 | SQL 关键字 | 作用 |
|---|---|---|
| ① | FROM | 确定主表，准备数据 |
| ② | ON | 连接多个表的条件 |
| ③ | JOIN | 执行 INNER JOIN / LEFT JOIN 等 |
| ④ | WHERE | 过滤行数据（提高效率） |
| ⑤ | GROUP BY | 进行分组 |
| ⑥ | HAVING | 过滤聚合后的数据 |
| ⑦ | SELECT | 选择最终返回的列 |
| ⑧ | DISTINCT | 进行去重 |
| ⑨ | ORDER BY | 对最终结果排序 |
| ⑩ | LIMIT | 限制返回行数 |
常用命令
MySQL 的常用命令主要包括数据库操作命令、表操作命令、行数据 CRUD 命令、索引和约束的创建修改命令、用户和权限管理的命令、事务控制的命令等。
数据库操作命令
CREATE DATABASE database_name; 用于创建数据库；DROP DATABASE database_name; 用于删除数据库；SHOW DATABASES; 用于显示所有数据库；USE database_name; 用于切换数据库。
表操作命令
CREATE TABLE table_name (列名1 数据类型1, 列名2 数据类型2,...); 用于创建表；
DROP TABLE table_name; 用于删除表；
SHOW TABLES; 用于显示所有表；
DESCRIBE table_name; 用于查看表结构；
ALTER TABLE table_name ADD column_name datatype; 用于修改表。
行数据的 CRUD 命令
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...); 用于插入数据；
SELECT column_names FROM table_name WHERE condition; 用于查询数据；
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition; 用于更新数据；
DELETE FROM table_name WHERE condition; 用于删除数据。
索引和约束的创建修改命令
CREATE INDEX index_name ON table_name (column_name); 用于创建索引；
ALTER TABLE table_name ADD PRIMARY KEY (column_name); 用于添加主键；
ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY (column_name) REFERENCES parent_table (parent_column_name); 用于添加外键。
用户和权限管理的命令
CREATE USER 'username'@'host' IDENTIFIED BY 'password'; 用于创建用户；
GRANT ALL PRIVILEGES ON database_name.table_name TO 'username'@'host'; 用于授予权限；
REVOKE ALL PRIVILEGES ON database_name.table_name FROM 'username'@'host'; 用于撤销权限；
DROP USER 'username'@'host'; 用于删除用户。
事务控制的命令
START TRANSACTION; 用于开始事务；
COMMIT; 用于提交事务；
ROLLBACK; 用于回滚事务。
连接
连接可分为三类：
inner join（内连接或等值连接）：获取两个表中字段匹配关系的记录，即2个表中值都存在的记录；
left join：获取左表中所有记录，即使右表中没有对应的匹配记录，右表中记录不存在则使用null替代；
right join：获取右表中所有记录，即使左表中没有对应的匹配记录，左表中记录不存在则使用null替代。
总结：
①A inner join B on {条件}：取交集；
②A left join B on {条件}：：取A全部，B没有对应的值，则为null；
③A right join B on {条件}：：取B全部，A没有对应的值，则为null；
④A full outer join B on {条件}：：取并集，彼此没有对应的值为null；
上述4种的对应条件，在on后填写。
limit 和 offset
在 SQL 查询中，LIMIT 和 OFFSET 子句用于控制查询结果的返回行数和起始位置。它们通常用于分页查询，以便从大量数据中获取特定范围的记录。
LIMIT 子句用于限制返回的记录数。它接受一个或两个整数参数：
- 一个参数：表示返回的最大记录数。
- 两个参数：第一个参数表示偏移量（即跳过的记录数），第二个参数表示返回的最大记录数。
1 | -- 返回前3行数据 |
OFFSET 子句用于指定查询结果的起始位置。它通常与 LIMIT 子句一起使用，以实现分页效果。
1 | -- 跳过前2行，返回第3行数据 |
