MYSQL 数据库各类属性说明 (持续更新)

妇男主任

已于 2023-06-14 22:51:21 修改

阅读量1.9k

点赞数 2

分类专栏：数据库文章标签：数据库 mysql

于 2022-11-13 01:07:30 首次发布

本文链接：https://blog.csdn.net/weixin_46596227/article/details/126825367

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、使用数据库的原因？

因为 EXCEL 不能供一个大型系统使用，它的性能不够好。我们需要一个可以供多人同时访问、操纵、安全的数据存储系统。

通俗来说，数据库是一个更大型的EXCEL表格，可以多人同时地进行访问、更改数据。在数据量很大（0～300G）的时候，数据库的性能将会非常地好。

（想象一下一个几十G的Excel，操作他将会非常困难）

二、安装与使用

安装

以下是在Windows上安装MySQL数据库的详细步骤：

下载MySQL安装程序。可以从MySQL官方网站上下载MySQL Community Server安装程序。
运行安装程序。双击下载的安装程序，选择“Custom”安装，这样可以选择需要安装的组件。
选择需要安装的组件。在安装程序中，可以选择需要安装的组件，例如MySQL Server、MySQL Workbench等。默认情况下，所有组件都会被安装。
配置MySQL Server。在安装过程中，会提示输入root用户的密码，这是MySQL的管理员用户。可以选择是否启用MySQL的自动启动服务。另外，在“Configuration”选项卡中，可以选择MySQL的安装路径以及数据文件存放路径等。
完成安装。安装过程会在操作系统中配置MySQL的服务，完成后可以启动MySQL服务。
配置防火墙规则。如果系统开启了防火墙，需要配置防火墙规则，以允许MySQL服务可以被外部访问。在Windows系统中，可以在控制面板中的“Windows Defender 防火墙”中进行配置。
测试安装。可以使用MySQL Workbench等工具来连接MySQL数据库，执行SQL语句等操作，以测试MySQL安装是否成功。

以上就是在Windows上安装MySQL数据库的详细步骤。如果在其他操作系统上安装，可能会有所不同，但基本步骤类似。

基础查询

接下来是一些常用的MYSQL基础语句：（它使用基于结构化查询语言（SQL）的语法进行操作。）

1. 创建数据库

下面是一个示例，可以使用 MySQL 命令行工具创建一个名为 mydatabase 的数据库。

打开命令行工具。
进入 MySQL 的客户端工具。可以通过以下命令输入密码进入。
```
mysql -u root -p
```
创建一个新的数据库，可以使用以下命令。
```
CREATE DATABASE mydatabase;
```
确认数据库已成功创建，可以使用以下命令。
```
SHOW DATABASES;
```
如果 mydatabase 出现在结果中，那么表示数据库已成功创建。

完成以上步骤后，就成功创建了一个名为 mydatabase 的数据库。

2. 删除数据库

以下是 MySQL 删除数据库的示例：

打开 MySQL 命令行工具。
进入 MySQL 客户端工具。可以使用以下命令输入密码进入。
```
mysql -u root -p
```
删除现有数据库，可以使用以下命令。请注意，这个命令会永久删除数据库和其中的所有表格和数据，所以请谨慎操作。
```
DROP DATABASE mydatabase;
```
这里的 “mydatabase” 是要删除的数据库名称。
确认删除操作已成功完成，可以使用以下命令查看所有数据库列表。
```
SHOW DATABASES;
```
如果 mydatabase 不再出现在结果中，那么表示数据库已成功删除。

请谨慎使用 DROP 命令，确保您要删除的是正确的数据库。该操作是不可逆的。

3. 选择数据库

以下是 MySQL 选择数据库的示例：

打开 MySQL 命令行工具。
进入 MySQL 客户端工具。可以使用以下命令输入密码进入。
```
mysql -u username -p
```
这里的 “username” 是要连接的用户名。在输入该命令后，您需要输入密码来验证身份。
选择要使用的数据库可以使用以下命令：
```
USE mydatabase;
```
这里的 “mydatabase” 是您想要使用的数据库的名称。如果该数据库存在，您现在就可以开始使用它了。如果没有，您需要先创建该数据库（详情请参阅 MySQL 创建数据库的示例）。
确认您已经成功地连接到数据库可以使用以下命令查看当前所连接的数据库名称：
```
SELECT DATABASE();
```
如果该命令返回了您想要使用的数据库名称，那么您就成功连接了该数据库。

请注意，如果您在同一个 MySQL 实例中使用多个数据库，您需要在每次连接到 MySQL 实例后选择要使用的数据库。

4. 创建表格

以下是 MySQL 创建表的示例：

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    ...
    columnN datatype constraint
);

其中，

table_name 是您想要创建的表格的名称。
column1, column2, … columnN 是表格中的列名。
datatype 是列的数据类型。
constraint 是用于限制列值的约束条件，如 NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY 等。

例如，我们要创建一个简单的用户表，该表包含以下列和约束条件：

id：整数类型，自动递增
username：字符串类型，长度不超过 50，不能为空
password：字符串类型，长度不超过 50，不能为空
email：字符串类型，长度不超过 100，不能为空，并且必须是唯一的

可以使用以下 SQL 查询语句创建该表：

CREATE TABLE users (
  id INT(11) NOT NULL AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  password VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL UNIQUE,
  PRIMARY KEY (id)
);

在上面的示例中，我们使用了主键 id，因此我们将其标识为 PRIMARY KEY。如果您在表中使用了多个列作为主键，则需要使用 PRIMARY KEY(column1, column2, ... columnN) 来标识主键。

5. 删除表格

以下是 MySQL 删除表格的示例：

DROP TABLE table_name;

其中，table_name 是您想要删除的表格的名称。

例如，我们要删除名为 users 的用户表格，可以使用以下 SQL 查询语句：

DROP TABLE users;

在执行该查询语句之后，将会永久删除 users 表格及其中的所有数据。因此，在执行此操作之前，请确保您真正希望删除该表格。

6. 插入数据

下面是一个示例，演示如何向 mysql 数据库中的表中插入数据：

假设有一个名为 student 的表，有两个字段：id 和 name。

-- 向 student 表中插入一条数据
INSERT INTO student (id, name) VALUES (1, '张三');

-- 向 student 表中插入多条数据
INSERT INTO student (id, name) VALUES (2, '李四'), (3, '王五'), (4, '赵六');

这个示例演示了如何向 mysql 数据库的 student 表中插入数据。对于每一行数据，需要指定需要插入的字段（如果只插入部分字段，则只需将需要插入的字段列出来），并为它们分别指定值。可以一次插入一行或多行数据。

7. 更新数据

下面是一个示例，演示如何更新 mysql 数据库中的表中的数据：

假设有一个名为 student 的表，有两个字段：id 和 name。

-- 更新 id 为 1 的行的 name 字段值为 '张三三'
UPDATE student SET name='张三三' WHERE id=1;

-- 更新所有行的 name 字段值为 '李四'
UPDATE student SET name='李四';

第一个语句将 id 为 1 的行的 name 字段更新为 '张三三'。

第二个语句将所有行的 name 字段更新为 '李四'。

更新数据时，需要使用 UPDATE 语句，并指定需要更新的表以及需要更新的字段和值。可以使用 WHERE 条件来指定要更新的行，如果不使用 WHERE 条件，则会更新表中的所有行。

8. 删除数据

假设我们有一个名为students的表，其中包含学生的姓名、年龄和分数信息。下面是一些删除数据的示例：

删除表中所有数据

DELETE FROM students;

删除表中特定的一条数据

DELETE FROM students WHERE name='Tom';

以上命令将删除名为'Tom'的学生的所有信息。

删除表中符合特定条件的数据

DELETE FROM students WHERE score < 60;

以上命令将删除分数小于60分的学生的信息。

需要注意的是，删除操作是不可逆的，请谨慎执行。建议先备份数据后再执行删除操作。

9. 选择数据

假设我们有一个名为 “students” 的表，其中包含 id、name 和 age 字段。以下是一些示例：

选择所有数据

SELECT * FROM students;

选择特定列的数据

SELECT name, age FROM students;

按条件选择数据

SELECT * FROM students WHERE age > 18;

按条件选择数据并按照特定列排序

SELECT * FROM students WHERE age > 18 ORDER BY name ASC;

使用聚合函数选择数据

SELECT COUNT(*) FROM students;
SELECT AVG(age) FROM students;

这些都是基本的示例，你可以根据自己的需求进行修改。

10. 排序数据

以下是 MySQL 排序数据的示例：

按照一个或多个字段进行升序排序：

SELECT column1, column2, ... FROM table_name ORDER BY column1 ASC, column2 ASC;

按照一个或多个字段进行降序排序：

SELECT column1, column2, ... FROM table_name ORDER BY column1 DESC, column2 DESC;

按照字符串类型字段进行排序：

SELECT column1, column2, ... FROM table_name ORDER BY column1 ASC;

按照日期类型字段进行排序：

SELECT column1, column2, ... FROM table_name ORDER BY column1 DESC;

按照数值类型字段进行排序：

SELECT column1, column2, ... FROM table_name ORDER BY column1 ASC;

需要注意的是，排序数据时可以指定多个字段，以逗号分隔。在一个字段上进行排序时，可以使用 ASC 或 DESC 关键字。默认情况下，排序按照升序进行。

11. 连接表格

假设有两个表格，一个是学生信息表格（students），另一个是课程表格（courses）。它们之间存在一对多的关系，即一个学生可以选修多门课程，而一门课程只能被一位学生选修。

连接这两个表格，可以通过使用 SQL 语句的 JOIN 命令实现。具体示例代码如下：

SELECT students.name, courses.course_name 
FROM students 
INNER JOIN courses 
ON students.id = courses.student_id;

这条 SQL 语句会将学生信息表格（students）和课程表格（courses）连接起来，并选出学生姓名和所选的课程名。其中，INNER JOIN 指定了连接方式，ON 子句则指定了连接条件，即以学生信息表格（students）中的 id 列与课程表格（courses）中的 student_id 列作为连接条件。

12. 聚合函数

MySQL 中常用的聚合函数包括：

COUNT：计算某个列或所有行数的函数

例如：

SELECT COUNT(*) FROM table_name; – 统计表中所有行数
SELECT COUNT(column_name) FROM table_name; – 统计某个列中非 NULL 的行数

SUM：计算某个列的值的总和的函数

例如：

SELECT SUM(column_name) FROM table_name;

AVG：计算某个列的值的平均值的函数

例如：

SELECT AVG(column_name) FROM table_name;

MAX：找出某个列的最大值的函数

例如：

SELECT MAX(column_name) FROM table_name;

MIN：找出某个列的最小值的函数

例如：

SELECT MIN(column_name) FROM table_name;

以上是 MySQL 中常用的聚合函数示例。

这些是MYSQL的一些基础语句，还有很多高级语句和功能，可以根据具体需要进行学习和使用。

MySQL 的高级查询

子查询：子查询是指在一个查询语句中嵌套另一个查询语句，其中内层查询的结果被用作外层查询的条件。例如：

SELECT * FROM customers WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_date BETWEEN '2020-01-01' AND '2020-12-31');

联结查询：联结查询是指根据两个或多个表之间的关系将它们组合起来查询。MySQL 支持内连接、外连接和交叉连接等多种类型的联结查询。例如：

SELECT customers.customer_name, orders.order_number
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;

分组查询：分组查询是指将查询结果按照指定的列进行分组，并在每个分组上执行聚合函数（例如求和、计数、平均数等）。例如：

SELECT order_date, SUM(order_amount) as total_amount
FROM orders
GROUP BY order_date;

存储过程和函数：存储过程和函数是一组 SQL 语句集合，可以通过单个调用来执行。存储过程用于执行多条 SQL 语句，而函数通常用于执行单个 SQL 语句并返回值。例如：

CREATE PROCEDURE `get_order_summary`(IN `start_date` DATE, IN `end_date` DATE)
BEGIN
    SELECT order_date, SUM(order_amount) as total_amount
    FROM orders
    WHERE order_date BETWEEN start_date AND end_date
    GROUP BY order_date;
END;

CALL `get_order_summary`('2020-01-01', '2020-12-31');

二、 MySQL 的存储引擎

MySQL 的存储引擎是一种可插拔的组件，它负责将数据存储在 MySQL 数据库中。MySQL 支持多种存储引擎，不同的存储引擎有不同的特点和适用场景。下面是 MySQL 常用的存储引擎：

1. InnoDB

InnoDB 存储引擎是 MySQL 默认的事务性存储引擎，它支持事务、行级锁、外键约束等高级特性。与 MyISAM 存储引擎相比，InnoDB 存储引擎更适合于数据写入频繁的场景。

2. MyISAM

MyISAM 存储引擎是 MySQL 最古老的存储引擎之一，它不支持事务，但是支持全文索引、压缩表、表级锁等特性。MyISAM 存储引擎在读操作较多的场景下表现更优秀。

3. MEMORY

MEMORY 存储引擎将数据存储在内存中，因此查询速度非常快，但是当服务器关闭或崩溃时，数据将永久丢失。因此，MEMORY 存储引擎适用于数据量较小、临时性的数据存储。

4. NDB Cluster

NDB Cluster 存储引擎是 MySQL 的集群版存储引擎，它可以实现数据的分布式存储和高可用性。NDB Cluster 存储引擎适用于大型分布式应用场景，如电信、金融等领域。

5. CSV

CSV 存储引擎将数据存储在 CSV 文件中，该文件可以在 Excel 中打开。CSV 存储引擎适用于需要将 MySQL 数据导出为 CSV 文件的场景。

6. ARCHIVE

ARCHIVE 存储引擎支持对大量数据进行快速存储和检索。在 ARCHIVE 存储引擎中，数据按照时间顺序进行存储，并使用压缩算法进行压缩。ARCHIVE 存储引擎适用于大量历史数据的存储和检索。

除了上述常用存储引擎之外，MySQL 还支持其他存储引擎，如 MERGE、BLACKHOLE、FEDERATED 等。在选择存储引擎时，需要根据具体情况进行选择，以达到最优的性能和可靠性。

MySQL 的存储引擎
存储引擎	描述
ARCHIVE	用于数据存档的引擎，数据被插入后就不能在修改了，且不支持索引。
CSV	在存储数据时，会以逗号作为数据项之间的分隔符。
BLACKHOLE	会丢弃写操作，该操作会返回空内容。
FEDERATED	将数据存储在远程数据库中，用来访问远程表的存储引擎。
InnoDB	具备外键支持功能的事务处理引擎
MEMORY	置于内存的表
MERGE	用来管理由多个 MyISAM 表构成的表集合
MyISAM	主要的非事务处理存储引擎
NDB	MySQL 集群专用存储引擎

三、MySQL 数据类型

MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

1.数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 Bytes	(-128，127)	(0，255)	小整数值
SMALLINT	2 Bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 Bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 Bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 Bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 Bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 Bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

2.日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性，。

类型	大小 ( bytes)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	'1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'	YYYY-MM-DD hh:mm:ss	混合日期和时间值
TIMESTAMP	4	'1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日凌晨 03:14:07	YYYY-MM-DD hh:mm:ss	混合日期和时间值，时间戳

3.字符串类型

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

四、MySQL 事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务用来管理 insert,update,delete 语句
一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

1. 原子性

一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

2. 一致性

在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

3. 隔离性

数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

4. 持久性

事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

以下是一个简单的 MySQL 事务的示例：

START TRANSACTION;  -- 开始事务

UPDATE table1 SET column1 = 'value1' WHERE id = 1;  -- 执行第一个 SQL 语句

UPDATE table2 SET column2 = 'value2' WHERE id = 2;  -- 执行第二个 SQL 语句

COMMIT;  -- 提交事务

在上面的示例中，START TRANSACTION 命令表示开始事务，该事务中执行两个 UPDATE 语句，最后通过 COMMIT 命令提交事务。如果在执行事务过程中发生了错误，可以使用 ROLLBACK 命令回滚事务，将数据库恢复到事务开始之前的状态：

START TRANSACTION;

UPDATE table1 SET column1 = 'value1' WHERE id = 1;

UPDATE table2 SET column2 = 'value2' WHERE id = 100;  -- 第二个 SQL 语句错误，此时事务会回滚

ROLLBACK;

在上面的示例中，第二个 UPDATE 语句中的 id 值不存在，因此该 SQL 语句执行失败，如果不进行事务回滚，则第一个 UPDATE 语句中的修改操作仍然生效，与事务的一致性要求相违背。