数据库概述

JAY.LIN 收录于数据库

2024-12-02 约 6266 字预计阅读 13 分钟

https://bing.ee123.net/img/rand?artid=141602455

数据库概述

一、数据库的定义

数据库（Database）是存储在计算机系统中的有组织的、通常是结构化的数据集合。数据库系统允许用户通过特定的方式（如查询语言）来插入、更新、删除和检索数据。

在数据库管理系统（DBMS）中，数据、表和 数据库 是三个层次不同的概念，它们之间有着明确的层次结构和关系

1. 数据（Data）

定义：数据是数据库中存储的基本信息单位，是数据库的内容。数据可以是数字、文本、日期、图像、声音等形式，表示现实世界中的实体或事件的特定属性。
举例：例如，员工的姓名 “Alice”，员工的工资 5000，都是数据。

2. 表（Table）

定义：表是数据库中存储数据的结构化集合。表由行（记录）和列（字段）组成，每一行代表一个数据记录（数据项），每一列代表一个特定的属性或字段。
组成：
- 列（Column） ：列是表的垂直部分，每列存储特定类型的数据。例如，员工表中的列可能包括“姓名”、“年龄”、“职位”、“工资”等。
- 行（Row） ：行是表的水平部分，每行代表一个完整的数据记录。例如，员工表中的一行可能包含一个员工的所有信息：姓名为 “Alice”，年龄为 30 岁，职位为“经理”，工资为 5000。
数据与表的关系 ：表格中的每个单元格存储的是具体的数据，即表格的行和列交叉处的值。
举例：例如，员工表 (Employees) 可以包括以下列和行：

ID	姓名	年龄	职位	工资
1	Alice	30	经理	5000
2	Bob	25	开发员	4000
3	Charlie	28	设计师	4500

在这个例子中：

“Alice”、“Bob”等是具体的数据。
“姓名”、“年龄”、“职位"等是表的列（字段）。
整个表格即为一个表，它存储了多条员工的记录。

3. 数据库（Database）

定义：数据库是一个存储多个表及其相关数据的容器，是更高一级的数据组织形式。数据库不仅包含表，还包括视图、索引、存储过程、触发器等数据库对象，以及管理这些对象的元数据（如表结构定义）。
表与数据库的关系 ：数据库包含了多个表，每个表存储特定类型的数据。一个数据库可以看作是数据表的集合，而每个表又是数据记录的集合。
组织结构 ：数据库中的表之间可能存在关系，例如通过外键实现的表间关联，反映了数据的逻辑关系。
举例：例如，一个公司数据库 (CompanyDB) 可以包含多个表：
- Employees 表（存储员工信息）
- Departments 表（存储部门信息）
- Salaries 表（存储工资信息）

在这个例子中， CompanyDB 是数据库，里面包含了 Employees 、 Departments 和 Salaries 等多个表，每个表中又包含具体的数据信息。

总结

数据是数据库中最小的存储单元，是表中记录的内容。
表是数据库中存储数据的结构化单位，由行和列组成，每个表保存了一类相关的数据记录。
数据库 就是表的集合。它是以一定的组织方式存储的相互有关的数据集合

4.数据库管理系统

数据库管理系统（DatabaseManagementSystem，DBMS）是实现对数据库资源有效组织、管理和存取的系统软件。它在操作系统的支持下，支持用户对数据库的各项操作

DBMS 的核心功能

数据定义 ：创建和修改数据库结构，如表格、视图、索引等。
数据操控 ：执行数据的插入、更新、删除和查询操作。
事务管理 ：确保一系列操作（事务）的完整性和一致性。
并发控制 ：管理多个用户同时访问数据，防止数据冲突。
安全性管理 ：控制对数据的访问权限，保护数据安全。
数据备份和恢复 ：提供数据的备份和恢复功能，以防止数据丢失。

DBMS的工作模式如下：

接受应用程序的数据请求和处理请求
将用户的数据请求（高级指令）转换为复杂机器代码（底层指令）
实现对数据库的操作
从数据库的操作中接受查询结果
对查询结果进行处理（格式转换）
将处理结果返回给用户

二、数据库的发展

数据库的发展可以分为几个阶段，通常被称为 第一代 、 第二代 和 第三代 数据库。每一代数据库技术都在前一代的基础上进行了改进，以适应不断变化的需求和技术进步。

第一代：层次型和网状型数据库

时间：20世纪60年代至70年代

1.1 层次型数据库

数据模型 ：层次型数据库采用树状结构（类似于文件系统的目录结构）来表示数据。数据以父子层次关系组织，每个节点（记录）有且只有一个父节点。
优点：数据结构简单，数据存取速度快，适合于某些特定的应用场景，如组织结构和物料清单管理。
缺点：层次模型不支持多对多关系，数据冗余较高，结构不灵活，修改数据库结构复杂。
示例：IBM 的 IMS（Information Management System）是典型的层次型数据库管理系统。

1.2 网状型数据库

数据模型 ：网状型数据库采用图结构来表示数据，允许记录有多个父节点，支持多对多关系。数据节点之间可以有多条连接路径。
优点：灵活性较高，支持复杂的多对多关系，减少了数据冗余。
缺点：数据库设计和维护复杂，查询操作难度较大，数据结构的修改不方便。
示例：CODASYL DBTG 模型是网状型数据库的代表，典型系统包括 IDMS（Integrated Database Management System）。

第二代：关系型数据库

时间：20世纪70年代末至今

2.1 关系型数据库（RDBMS）

数据模型 ：关系型数据库采用关系模型，将数据组织为表格形式（关系），表中的每一行代表一个记录，每一列代表一个字段。关系模型通过主键和外键来建立表之间的关联。
优点：
- 数据结构简单、直观，易于理解和使用。
- 使用 SQL（结构化查询语言）进行数据操作，查询灵活。
- 支持事务、并发控制、数据完整性和安全性。
- 易于维护，适合大多数业务场景。
缺点：
- 对于海量数据和高并发场景下的扩展性相对较弱。
- 处理复杂关系和嵌套数据时效率较低。
示例：典型的关系型数据库系统包括 Oracle Database、MySQL、PostgreSQL、Microsoft SQL Server 等。

第三代：面向对象数据库与NoSQL数据库

时间：20世纪90年代至今

3.1 面向对象数据库（OODBMS）

数据模型 ：面向对象数据库结合了面向对象编程的概念，将数据以对象的形式存储，支持对象的继承、多态性和封装。数据与操作行为绑定在一起，使得数据管理更加符合面向对象的思维方式。
优点：
- 支持复杂数据结构和对象关系映射（ORM）。
- 高度兼容面向对象编程语言，减少了编程难度。
- 能处理复杂的嵌套和层次结构数据。
缺点：
- 不如关系型数据库通用，标准化程度较低。
- 查询语言复杂，不如 SQL 那么广泛接受。
示例：ObjectDB、db4o、Versant 等。

3.2 非关系型数据库（NoSQL）

数据模型 ：NoSQL 数据库采用多种数据模型，如键值对、列族、文档、图数据库等，以应对不同类型的应用场景。NoSQL 数据库通常放弃了关系型数据库中的部分特性（如严格的 ACID 属性），以获得更好的扩展性和性能。
优点：
- 高扩展性，能够处理大规模数据和高并发。
- 数据模型灵活，适应不同类型的数据存储需求。
- 高效的读取和写入性能，适合分布式系统。
缺点：
- 一致性保证较弱，复杂查询功能较少。
- 缺乏统一的标准，系统设计和使用较为复杂。
示例：MongoDB（文档型）、Cassandra（列族型）、Redis（键值型）、Neo4j（图数据库）等。

总结

第一代数据库 ：层次型和网状型数据库，强调数据的层次和网络结构管理，但在灵活性和易用性上有所不足。
第二代数据库 ：关系型数据库，引入了关系模型和 SQL，成为最广泛使用的数据库类型，支持复杂的事务和数据操作。
第三代数据库 ：面向对象数据库和 NoSQL 数据库，针对复杂对象管理和大规模分布式数据处理提出了解决方案，适应了现代多样化的数据需求。

关系型数据库和非关系型数据库

关系型数据库（RDBMS）

数据结构 ：数据以表格形式存储，每个表由行和列组成，行代表记录，列代表字段。
查询语言 ：使用 SQL（结构化查询语言）进行数据操作。
特点：支持复杂查询和事务处理，数据一致性强（ACID属性）。
适用场景 ：适用于传统业务系统，如财务系统、ERP、CRM等。
示例：MySQL、PostgreSQL、Oracle、SQL Server。

非关系型数据库（NoSQL）

数据结构 ：支持多种数据模型，如键值对、文档、列族和图，不采用固定的表格结构。
查询语言 ：没有标准的查询语言，各数据库有不同的查询方式。
特点：高度扩展性，适合处理大量数据和高并发，通常在一致性上做出一定让步。
适用场景 ：适用于大数据、实时分析、社交网络等需要灵活性和高扩展性的场景。
示例：MongoDB（文档型）、Redis（键值型）、Cassandra（列族型）、Neo4j（图数据库）。


数据模型	表格形式，行和列	多样化：键值对、文档、列族、图


查询语言	标准化的 SQL	多种查询语言，非标准化


事务管理	支持 ACID 事务	通常不支持或部分支持 ACID，强调 CAP 理论


扩展性	垂直扩展（通过增加单个服务器的能力）	水平扩展（通过增加服务器数量）


数据一致性	强一致性	最终一致性（在一些场景下）


适用场景	复杂查询、事务处理、结构化数据	大规模数据、高并发、非结构化数据


代表系统	MySQL、PostgreSQL、Oracle	MongoDB、Cassandra、Redis

简要对比

关系型数据库 ：结构化数据，使用 SQL，适合复杂查询。
非关系型数据库 ：多种数据模型，灵活性高，适合大数据和动态应用。

三、MySQL数据库管理

1.了解数据库信息、表结构与存储引擎

1.2 查看数据库信息

在MySQL中，可以使用以下命令来查看当前服务器上存在的所有数据库：

SHOW DATABASES;

这个命令会列出所有可用的数据库。要切换到一个特定的数据库，可以使用 USE 命令：

USE database_name;

切换到目标数据库后，可以查看该数据库中的所有表：

SHOW TABLES;

或者，指定数据库名称来查看该数据库中的表：

SHOW TABLES IN database_name;

1.2 查看表的结构信息

在管理数据库时，了解表的结构（即字段信息）是至关重要的。可以使用 DESCRIBE 命令来查看表的结构：

DESCRIBE table_name;

这将显示以下信息：

Field
字段名称。
Type
数据类型。
Null
是否允许为空。
Key
主键或索引信息。
Default
默认值。
Extra
扩展属性，例如标志符列

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | YES  |     | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

1.3 常用的数据类型

在定义表结构时，选择合适的数据类型非常重要。以下是MySQL中常用的一些数据类型：

INT ：用于存储整数。
FLOAT ：单精度浮点数（4字节）。
DOUBLE ：双精度浮点数（8字节）。
CHAR ：固定长度的字符类型。如果实际存储的数据长度小于指定长度，会自动补空格。
VARCHAR ：可变长度的字符类型，节省存储空间。
TEXT ：用于存储长文本。
DECIMAL(5,2) ：精确的定点数类型，例如存储5位数，其中2位是小数。

CREATE TABLE example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    salary DECIMAL(10, 2),
    description TEXT
);

1.4 主键与复合主键

在数据库设计中，主键用于唯一标识表中的每一行记录。主键可以由一个或多个字段组成，后者被称为复合主键。例如：

CREATE TABLE orders (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

1.5 MySQL数据文件存储

MySQL的数据文件存储在服务器的文件系统中，不同的存储引擎会使用不同的文件格式。默认情况下，MySQL的数据文件存放在 /usr/local/mysql/data 目录下。

MyISAM存储引擎 ：
- .frm ：存储表的结构定义。
- .MYD ：存储表的数据。
- .MYI ：存储表的索引。
InnoDB存储引擎 ：
- .ibd ：存储表的数据和索引（如果使用独立表空间）。
- ibdata文件 ：如果使用共享表空间，所有表的数据和索引存放在一个或多个 ibdata 文件中。

1.6 MyISAM与InnoDB存储引擎

MySQL提供了多种存储引擎，其中最常用的是MyISAM和InnoDB。

MyISAM ：适合读操作频繁的应用，支持全文索引，文件存储更为简单。
InnoDB ：支持事务处理，提供外键约束，数据安全性更高，适合需要并发写操作的应用。

MyISAM表的数据文件和索引文件分别存储在 .MYD 和 .MYI 文件中。而InnoDB的存储则更为复杂，可以选择独立表空间模式（每个表一个 .ibd 文件），或者共享表空间模式（使用 ibdata 文件）。

2. SQL语言分类

SQL语言主要分为以下几类：

DDL（数据定义语言） ：用于创建和修改数据库对象，如数据库、表、索引等。
DML（数据操纵语言） ：用于对数据库中的数据进行管理，包括插入、删除和更新记录。
DQL（数据查询语言） ：用于从数据表中查询符合条件的记录。
DCL（数据控制语言） ：用于设置或更改数据库用户或角色的权限，如控制访问级别和许可。

2.1 DDL：数据定义语言

DDL 语句用于定义和管理数据库结构。常用的DDL语句包括 CREATE 、 DROP 和 ALTER 。

创建新的数据库

CREATE DATABASE 数据库名;

CREATE DATABASE kgc_school;

选择数据库

USE 数据库名;

创建新的表

CREATE TABLE 表名 (
    字段1 数据类型,
    字段2 数据类型,
    ...,
    PRIMARY KEY (主键名)
);

示例

CREATE TABLE KY13 (
    id INT NOT NULL,
    name CHAR(10) NOT NULL,
    score DECIMAL(5,2),
    passwd CHAR(48) DEFAULT '',
    PRIMARY KEY (id)
);

NOT NULL
不允许为空值。
DEFAULT
默认值。
PRIMARY KEY
主键字段，确保唯一且不为空。

显示表结构

DESC 表名;

删除指定的数据表

DROP TABLE 表名;

如果没有选择数据库，则需要指定数据库名：

DROP TABLE 数据库名.表名;

删除指定的数据库

DROP DATABASE 数据库名;

2.2 DML：数据操纵语言

DML 语句用于对表中的数据进行管理，包括插入、更新和删除记录。

插入数据

INSERT INTO 表名 (字段1, 字段2, ...) VALUES (值1, 值2, ...);

INSERT INTO KY13 (id, name, score, passwd) VALUES (1, 'zhangsan', 70.5, PASSWORD('123456'));

更新数据

UPDATE 表名 SET 字段名1 = 字段值1[, 字段名2 = 字段值2] [WHERE 条件];

UPDATE KY13 SET passwd = PASSWORD('newpassword') WHERE name = 'zhangsan';
UPDATE KY13 SET name = 'wangxiaoer', passwd = '' WHERE id = 3;

删除数据

DELETE FROM 表名 [WHERE 条件];

DELETE FROM KY13 WHERE id = 4;

2.3 DQL：数据查询语言

DQL 语句用于从数据表中查询符合条件的数据记录。

查询数据

SELECT 字段名1, 字段名2, ... FROM 表名 [WHERE 条件];

SELECT * FROM KY13;
SELECT id, name, score FROM KY13 WHERE id = 2;

限制查询结果数量

SELECT * FROM 表名 LIMIT 开始位置, 行数;

SELECT * FROM KY13 LIMIT 2;         -- 显示前2行
SELECT * FROM KY13 LIMIT 2, 3;      -- 从第3行开始显示3行

以列表方式显示

SELECT * FROM 表名 \G;

SELECT * FROM KY13 \G;

2.4 DCL：数据控制语言

DCL 语句用于设置和管理数据库的访问权限和安全级别。

GRANT
授予用户权限。
REVOKE
撤销用户权限。
COMMIT
提交当前事务。
ROLLBACK
回滚当前事务。

这些命令通常用于数据库的安全管理，确保数据的保护和用户权限的控制。

3. 数据表高级操作

3.1 清空表

清空表是指删除表中的所有数据而保留表结构。常用的方法有两种： DELETE 和 TRUNCATE 。

方法一：使用 DELETE

DELETE FROM 表名;

特点：逐行删除数据，删除后的结果内包含删除的记录条目。
自增长字段 ： DELETE 操作不会重置自增长字段的值，新插入的记录会从原来的最大ID继续递增。
事务处理 ： DELETE 操作是可回滚的，意味着在事务中可以恢复数据。

方法二：使用 TRUNCATE

TRUNCATE TABLE 表名;

特点：快速删除所有数据，不返回被删除的条目。 TRUNCATE 实际上重新创建表，因此速度通常比 DELETE 快。
自增长字段 ： TRUNCATE 会重置自增长字段，新的记录ID从1开始。
事务处理 ： TRUNCATE 操作是不可回滚的。

3.2 创建临时表

临时表在数据库会话中有效，连接关闭后会自动删除。临时表不能拥有外键约束。

创建临时表 ：

CREATE TEMPORARY TABLE 表名 (
    字段1 数据类型,
    字段2 数据类型,
    ...
    [, PRIMARY KEY (主键名)]
);

示例：

CREATE TEMPORARY TABLE temp_table (
    id INT(4) ZEROFILL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(10) NOT NULL,
    cardid INT(18) NOT NULL UNIQUE KEY,
    hobby VARCHAR(50)
);

操作：

在临时表中执行增、删、改、查操作与普通表相同。
临时表不会出现在 SHOW TABLES 的结果中。

注意：

临时表在会话结束时会被自动删除，无法创建外键。

3.3 克隆表

克隆表包括克隆表结构和数据，可以用于备份或迁移数据。

克隆表结构 ：

CREATE TABLE 新表名 LIKE 原表名;

克隆表数据 ：

INSERT INTO 新表名 SELECT * FROM 原表名;

示例：

CREATE TABLE backup_table LIKE original_table;
INSERT INTO backup_table SELECT * FROM original_table;

获取表结构 ：

SHOW CREATE TABLE 新表名\G;

3.4 修改表结构

重命名表 ：

ALTER TABLE 旧表名 RENAME 新表名;

扩展表结构（增加字段） ：

ALTER TABLE 表名 ADD 字段名 数据类型 [DEFAULT 默认值];

修改字段（列）名和数据类型 ：

ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 [unique key];

修改字段数据类型 ：

ALTER TABLE 表名 MODIFY COLUMN 字段名 新数据类型;

删除字段 ：

ALTER TABLE 表名 DROP 字段名;

示例：

ALTER TABLE users ADD address VARCHAR(50) DEFAULT 'Unknown';
ALTER TABLE users CHANGE name username VARCHAR(20) UNIQUE;
ALTER TABLE users MODIFY COLUMN age INT(3);
ALTER TABLE users DROP address;

扩展说明 ：

IF NOT EXISTS ：在创建表时，如果表已存在则不会报错。
AUTO_INCREMENT ：自增长字段，默认从1开始递增。
UNIQUE KEY ：确保字段值唯一，但允许空值。
NOT NULL ：字段不能为空。