Mysql知识归纳整理

最新推荐文章于 2024-07-25 16:06:56 发布

20220310004

最新推荐文章于 2024-07-25 16:06:56 发布

阅读量51

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/2301_77163313/article/details/131215931

版权

一、数据类型：

- 整数类型：包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别表示不同范围的整数值。例如，INT类型可以存储范围在-2147483648到2147483647之间的整数。
- 浮点类型：包括FLOAT和DOUBLE，用于存储带有小数部分的数字。FLOAT适用于单精度浮点数，而DOUBLE适用于双精度浮点数。
字符串类型：
- CHAR：固定长度字符串，最多255个字符。
- VARCHAR：可变长度字符串，最大长度为65535个字符。
- TEXT：用于存储长文本数据，最大长度为65535个字符。
- ENUM：枚举类型，用于存储从给定集合中选择的一个值。
- SET：集合类型，用于存储从给定集合中选择的多个值。
日期和时间类型：
- DATE：存储日期，格式为'YYYY-MM-DD'。
- TIME：存储时间，格式为'HH:MM:SS'。
- DATETIME：存储日期和时间，格式为'YYYY-MM-DD HH:MM:SS'。
- TIMESTAMP：存储日期和时间，并以UNIX时间戳格式表示（1970年1月1日以来的秒数）。
二进制类型：
- BINARY：固定长度的二进制字符串，最多255个字节。
- VARBINARY：可变长度的二进制字符串，最大长度为65535个字节。
- BLOB：用于存储二进制大对象数据，最大长度为65535个字节。

其他一些特殊的数据类型，例如JSON、GEOMETRY等，用于存储特定格式的数据。

JSON类型： JSON（JavaScript Object Notation）是一种轻量级的数据交换格式，在MySQL中被用作一种特殊的数据类型，用于存储和操作结构化的JSON文档数据。

JSON类型在MySQL中具有以下特点：
- 支持存储和索引结构化的JSON数据。
- 可以通过各种内置函数进行查询、修改和操作JSON数据。
- 提供了大量用于处理JSON数据的函数，如JSON_EXTRACT、JSON_ARRAY_APPEND，以及JSON_OBJECT等。
- JSON类型的字段可以使用索引加速查询，提高性能。
在使用JSON类型时，您可以将复杂的结构化数据存储在单个字段中，而无需拆分成多个表和字段。这对于存储可变结构或半结构化数据非常有用，例如日志、配置文件、API响应等。
GEOMETRY类型： GEOMETRY类型是MySQL中支持空间数据的数据类型，用于存储点、线、多边形等几何对象的信息。它基于OGC（Open Geospatial Consortium）的规范，允许进行空间数据的存储和查询。

GEOMETRY类型在MySQL中的应用如下：
- 存储和处理地理和几何数据，例如地图坐标、地理区域、路径等。
- 支持空间索引，以加快空间数据的查询速度。
- 提供了多个函数用于处理和分析几何对象，如ST_Distance、ST_Contains和ST_Intersects等。
使用GEOMETRY类型，您可以在MySQL数据库中存储和操作具有地理或几何关系的数据，并进行空间查询和分析。这对于地理信息系统（GIS）、位置相关的应用和数据分析非常有用。

二、数据库操作

1、创建数据库：使用CREATE DATABASE语句可以创建一个新的数据库。示例：

CREATE DATABASE database_name;

2、删除数据库：使用DROP DATABASE语句可以删除一个现有的数据库。注意，这将永久删除数据库及其所有相关的表和数据。示例：

DROP DATABASE database_name;

3、

修改数据库： MySQL并没有提供直接修改数据库名的语句。如果需要修改数据库名，通常的做法是先创建一个新的数据库，将原始数据库中的数据导出并导入到新数据库中，然后删除原始数据库。

4、

创建表：使用CREATE TABLE语句可以创建一个新的表。在创建表时，您需要指定表的名称以及每个字段的名称、数据类型和约束。示例：

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

5、删除表：使用DROP TABLE语句可以删除一个现有的表。注意，这将永久删除表及其所有相关的数据。示例：

DROP TABLE table_name;

6、

修改表：使用ALTER TABLE语句可以修改现有的表结构。您可以添加、修改或删除表的列，添加或删除表的约束等。示例如下

添加列：

ALTER TABLE table_name ADD column_name datatype constraint;

修改列：

ALTER TABLE table_name MODIFY column_name datatype constraint;

删除列：

ALTER TABLE table_name DROP column_name;

三、数据查询

1、基本查询：使用SELECT语句可以选择需要查询的列和表。示例：

SELECT column1, column2 FROM table_name;

2、条件查询：可以使用WHERE子句添加条件来过滤查询结果。示例：

SELECT column1, column2 FROM table_name WHERE condition;

3、排序查询：可以使用ORDER BY子句对查询结果进行排序，默认为升序。示例：

SELECT column1, column2 FROM table_name ORDER BY column1 ASC;

如果降序则为：

在ORDER BY子句中指定列名，并使用关键字DESC（降序）进行排序

SELECT column1, column2 FROM table_name ORDER BY column1 DESC;

4、分组查询：可以使用GROUP BY子句对查询结果进行分组，并使用聚合函数计算每个组的汇总值。示例：

SELECT column1, SUM(column2) FROM table_name GROUP BY column1;

5、聚合函数： MySQL提供了很多聚合函数，如SUM、AVG、COUNT等，用于计算列或行的汇总值。示例：

SELECT COUNT(*) FROM table_name;

6、子查询：可以在SELECT语句中嵌套查询，将内部查询的结果作为外部查询的条件或数据来源。示例：

SELECT column1 FROM table_name WHERE column2 IN (SELECT column2 FROM another_table);

7、连接查询：可以使用JOIN子句将多个表进行连接查询，根据指定的条件关联表中的数据。示例：

SELECT column1, column2 FROM table1 JOIN table2 ON table1.id = table2.id;

四、数据插入、更新、删除

1、数据插入（INSERT）：使用INSERT语句可以将新的数据行插入到表中。您需指定要插入的表名和要插入的列及其对应的值。示例：

INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2');

2、数据更新（UPDATE）：使用UPDATE语句可以更新表中已存在的数据行。您需要指定要更新的表名、设置新的列值以及更新条件。示例：

UPDATE table_name SET column1 = 'new_value1', column2 = 'new_value2' WHERE condition;

3、数据删除（DELETE）：使用DELETE语句可以删除表中的数据行。您需要指定要删除的表名以及删除条件。注意：如果不指定任何条件，则将删除整表中的所有数据行。示例：

DELETE FROM table_name WHERE condition;

注：在执行这些操作之前，请确保有适当的访问权限，并仔细考虑数据的备份和安全性。

五、索引

MySQL支持多种索引类型，用于提高查询性能和加快数据检索速度。以下是一些常见的索引类型：

1、B-tree索引： B-tree（平衡树）索引是最常用的索引类型，适用于各种查询条件。它将索引数据按照一定的顺序组织成一棵树，方便进行范围查询和排序操作。

CREATE INDEX index_name ON table_name (column_name);

其中，index_name是索引的名称，table_name是要创建索引的表名，column_name是要创建索引的列名。

2、哈希索引：哈希索引适用于等值查询，在内存中使用哈希表来存储索引数据。它具有高效的查找速度，但不支持范围查询和排序。

CREATE INDEX index_name ON table_name (column_name) USING HASH;

同样，index_name是索引的名称，table_name是要创建索引的表名，column_name是要创建索引的列名。

3、全文索引：全文索引适用于对文本内容进行搜索，可以实现更复杂的关键词匹配和模糊搜索。MySQL提供了全文索引功能，可以针对文本列创建全文索引，并使用MATCH AGAINST语句进行全文搜索。

CREATE FULLTEXT INDEX index_name ON table_name (column_name);

同样，index_name是索引的名称，table_name是要创建索引的表名，column_name是要创建索引的列名。

4、空间索引：空间索引适用于地理位置数据，可以进行距离计算和空间查询。MySQL提供了空间索引功能，可以创建空间索引和执行空间查询语句。

其他索引：

创建空间索引：空间索引适用于地理位置数据，可以通过以下方式创建空间索引：
```
CREATE SPATIAL INDEX index_name ON table_name (column_name);
```
在上面的语句中，index_name是索引的名称，table_name是要创建索引的表名，column_name是要创建索引的空间列名。
创建前缀索引：前缀索引是对列值的前缀进行索引，适用于对较长的列进行索引优化。可以通过以下方式创建前缀索引：
CREATE INDEX index_name ON table_name (column_name(length));
在上面的语句中，index_name是索引的名称，table_name是要创建索引的表名，column_name是要创建索引的列名，length是指定需要索引的前缀长度。
创建唯一索引：唯一索引是确保索引列的值在整个表中是唯一的。可以通过以下方式创建唯一索引：
CREATE UNIQUE INDEX index_name ON table_name (column_name);
在上面的语句中，index_name是索引的名称，table_name是要创建索引的表名，column_name是要创建索引的列名。

唯一索引的特点是索引列的值不能重复，确保数据的唯一性。

六、事务

MySQL支持ACID事务，其中ACID代表原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability），这些特性确保了数据库操作的可靠性和数据的完整性。

具体来说，MySQL中的事务具有以下特点：

原子性（Atomicity）：事务被视为一个不可分割的单元，要么全部执行成功，要么全部回滚到初始状态。如果在事务过程中发生了错误，所有对数据库的修改都将被撤销，数据库恢复到原始状态，保证数据的一致性。
一致性（Consistency）：事务开始之前和结束之后，数据库必须处于一致的状态。这意味着事务的执行不会破坏数据库中的约束和规则，例如主键、外键、唯一性约束等。
隔离性（Isolation）：事务的隔离性确保在并发环境下多个事务可以独立地执行，互不干扰。每个事务的更改对其他事务是不可见的，直到事务提交。这样可以防止数据丢失、脏读、不可重复读和幻读等并发问题。
持久性（Durability）：一旦事务提交，其所做的修改将永久保存在数据库中，即使发生系统故障或重启，修改的数据也不会丢失。

为了保证事务的 ACID 特性，MySQL提供了以下机制：

事务控制语句：MySQL提供了START TRANSACTION用于显式地开始一个事务，并通过COMMIT进行提交或者使用ROLLBACK进行回滚。
锁机制：MySQL使用锁来实现事务的隔离性，确保并发事务之间的数据操作不会相互干扰。包括行级锁、表级锁等。
日志：MySQL将事务的操作记录在日志中，事务的修改操作包括对数据库表的插入、更新和删除操作。

创建事务是通过在MySQL中使用BEGIN、COMMIT和ROLLBACK语句来实现的。以下是一个基本的事务示例：

BEGIN; -- 开始事务

-- 执行一系列的SQL操作，例如插入、更新或删除数据
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
UPDATE table_name SET column = new_value WHERE condition;
DELETE FROM table_name WHERE condition;

COMMIT; -- 提交事务，将操作结果永久保存到数据库

-- 如果在事务过程中发生错误或需要取消之前的操作，可以使用ROLLBACK语句回滚事务
ROLLBACK; -- 回滚事务，撤销之前的操作

以上代码中，BEGIN语句表示开始一个事务，后续的SQL操作将被视为一个整体进行处理。如果所有的操作都执行成功，并且希望将结果永久保存到数据库中，则使用COMMIT语句提交事务。如果在事务执行过程中出现错误或需要取消之前的操作，则可以使用ROLLBACK语句回滚事务，将所有已执行的操作撤销。

通过使用事务，可以确保一组相关的数据库操作要么全部成功执行并提交，要么全部失败并回滚到初始状态，从而保证了数据的一致性、完整性和持久性。

请注意，在默认情况下，MySQL的自动提交模式是开启的，即每个SQL语句都被视为一个单独的事务并立即提交。如果要使用显式的事务管理，需要将自动提交模式禁用，可以使用以下语句实现：

SET autocommit = 0; -- 禁用自动提交模式

七、存储引擎

常见的MySQL存储引擎及其特点：

InnoDB：InnoDB是MySQL默认的事务型存储引擎。它提供了ACID（原子性、一致性、隔离性和持久性）事务支持，具有良好的并发性能和数据完整性。InnoDB支持行级锁定和外键约束，适用于大规模应用和高并发环境。
MyISAM：MyISAM是MySQL较早期的存储引擎，不支持事务和行级锁定。它在处理大量读操作的情况下表现较优，具有快速插入和查询速度的特点。但它不适合频繁更新和并发写入的场景，且容易出现数据损坏。
MEMORY：MEMORY存储引擎将数据存储在内存中，提供了非常高的读写性能，适用于对速度要求非常高、但数据可丢失、重启后需要重新加载的场景。由于数据存储在内存中，所以存储容量受限，同时也没有持久化的能力。

八、用户管理

创建用户：使用CREATE USER语句创建一个新用户。例如：
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
其中，'username'为用户名，'host'表示允许连接数据库的主机，'password'为用户密码。
删除用户：使用DROP USER语句删除现有用户。例如：
DROP USER 'username'@'host';
将会删除名为'username'的用户在指定的主机'host'上的访问权限。
修改用户密码：使用ALTER USER语句修改用户密码。例如：
ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';
将会修改名为'username'的用户在指定的主机'host'上的密码为'new_password'。
授权：使用GRANT语句给用户赋予不同的权限。例如：
GRANT SELECT, INSERT, UPDATE ON database.table TO 'username'@'host';
以上命令将授予名为'username'的用户在指定的主机'host'上对数据库中'table'表进行SELECT、INSERT和UPDATE操作的权限。
撤销权限：使用REVOKE语句取消用户的特定权限。例如：
REVOKE INSERT ON database.table FROM 'username'@'host';
以上命令将撤销名为'username'的用户在指定的主机'host'上对数据库中'table'表进行INSERT操作的权限。

20220310004

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql知识归纳整理

MySQL支持ACID事务，其中ACID代表原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability），这些特性确保了数据库操作的可靠性和数据的完整性。如果需要修改数据库名，通常的做法是先创建一个新的数据库，将原始数据库中的数据导出并导入到新数据库中，然后删除原始数据库。您需要指定要删除的表名以及删除条件。通过使用事务，可以确保一组相关的数据库操作要么全部成功执行并提交，要么全部失败并回滚到初始状态，从而保证了数据的一致性、完整性和持久性。
复制链接

扫一扫