MySQL 学习笔记（二）

2020-11-24  本文已影响0人  Whyn

别名

当表名或字段名比较长且需要重复使用时，可以为它们取一个别名，简化调用。其中：

数据操作

创建完数据表后，就可以往表里面添加具体的实体数据。下面介绍对实体数据的一些基本操作。

最后，一个SELECT语句中，其子句结构具备严格的固定顺序，如下所示：

SELECT DISTINCT <select_list>
FROM <left_table> <join_type> JOIN <right_table>
ON <join_condition>
WHERE <where_condition>                          # 行级过滤
GROUP BY <group_by_list>                         # 分组
WITH { CUBE | ROLLUP }                           # 分组汇总
HAVING <having_condtion>                         # 分组过滤
ORDER BY <order_by_list>
LIMIT <limit_number>

存储引擎

数据库对数据进行增删改查底层是通过存储引擎进行的，不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。

MYSQL 支持多种不同的存储引擎，可以通过命令SHOW ENGINES查看系统支持的所有存储引擎：

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.07 sec)

可以看到，MySQL 支持的存储引擎有：FEDERATED、MEMORY、InnoDB、PERFORMANCE_SCHEMA、MyISAM、MRG_MYISAM、BLACKHOLE、CSV和ARCHIVE。其中：各种存储引擎最明显的区别是是否支持事务，MySQL 8.0 中默认的存储引擎为InnoDB，且其是 MySQL 唯一一个支持事务的内置存储引擎。

下面介绍下 MySQL 中最常用的两种存储引擎：

其他存储引擎也拥有各自的特性，比如MEMORY引擎支持将表中数据存储到内存中，使得其具备高效快速的查询能力...
不同的存储引擎有不同的特点，实际使用中应结合业务场景，选择使用合适的存储引擎。

以下是几种常用存储引擎的横向比较，方便查看各引擎的特点：

特性 InnoDB MyISAM MEMORY MERGE BerkeleyDB
存储限制 64TB 有 有 有
事务支持 支持
锁机制 行锁 表锁 表锁 表锁 行锁
B树索引 支持 支持 支持 支持 支持
哈希索引 支持
全文索引 支持 支持
集群索引 支持
数据缓存 支持 支持 支持
索引缓存 支持 支持 支持 支持 支持
数据压缩 支持
空间使用 高 低 N/A 低 低
内存使用 高 低 中等 低 高
批量插入速度 低 高 高 高 高
外键支持 支持

事务

关系型数据库中，可以使用 事务（transaction） 来维护数据库完整性，它能保证事务范围内的成批操作要么全部执行，要么全部不执行（除非明确指示）。

简单来说，事务的作用就是使得多条 SQL 语句的操作具备原子性。
在事务的语义下，执行多条 SQL 语句就相当于执行一条 SQL 语句。而当事务中的某条 SQL 语句操作失败时，它会回滚之前成功的操作，使得数据库与未执行该事务前的状态保持一致，维护了数据的完整性。

保证事务可靠性的理论基础是事务具备的四大特性：ACID，具体内容如下：

下面介绍下数据库的事务隔离级别，在介绍之前，先创建一个示例数据表account，表示一个银行账户：

mysql> CREATE TABLE account (
    -> id BIGINT PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(20) NOT NULL DEFAULT 'anonymous',
    -> money DECIMAL(10,5) NOT NULL DEFAULT 0
    -> )ENGINE=InnoDB;
Query OK, 0 rows affected (2.18 sec)

注：MySQL 中只有InnoDB支持事务。

当多个事务同时操作同一条记录时，由于并发操作可能会造成数据的不一致性，包括脏读、不可重复读、幻读等。也因此，为了在最大可能上保证数据的一致性，数据库提供了以下几种隔离级别：

总结一下，数据库隔离级别之间的关系如下表所示：

隔离级别 读数据一致性 脏读 不可重复读 幻读
Read Uncommitted 最低级别 是 是 是
Read Committed 语句级 否 是 是
Repeatable Read 事务级 否 否 是
Serializable 最高级别，事务级 否 否 否

数据库中，对事务的操作主要包含如下几方面：

举个例子：现在假设表account中用户lisi转账 100 元给zhangsan，由于银行转账过程涉及不同用户间金额计算，这个操作过程要求具备原子性，因此使用事务进行处理，确保数据的一致性：

# 初始数据
mysql> SELECT * FROM account;
+----+----------+-----------+
| id | name     | money     | 
+----+----------+-----------+
| 1  | lisi     | 200.00000 |
| 5  | zhangsan | 200.00000 |
+----+----------+-----------+
2 rows in set (0.00 sec)

# 开启事务
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

# lisi - 100
mysql> UPDATE account SET money = money - 100 WHERE name = 'lisi';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# zhangsan + 100
mysql> UPDATE account SET money = money + 100 WHERE name = 'zhangsan';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 提交事务
mysql> COMMIT;
Query OK, 0 rows affected (0.14 sec)

# 查看事务执行结果
mysql> SELECT * FROM account;
+----+----------+-----------+
| id | name     | money     |
+----+----------+-----------+
|  1 | lisi     | 100.00000 |
|  5 | zhangsan | 300.00000 |
+----+----------+-----------+
2 rows in set (0.01 sec)
上一篇 下一篇

猜你喜欢

热点阅读