Mysql初学习(2)

本文最后更新于:6 个月前

Mysql管理以及事务管理


MYSQL

在安装完mysql之后,除了MySQL Server,即真正的MySQL服务器外,还附赠一个MySQL Client程序。MySQL Client是一个命令行客户端,可以通过MySQL Client登录MySQL,然后,输入SQL语句并执行。

MySQL Client和MySQL Server的关系如下:

1
2
3
┌──────────────┐  SQL   ┌──────────────┐
│ MySQL Client │───────>│ MySQL Server │
└──────────────┘ TCP └──────────────┘

在MySQL Client中输入的SQL语句通过TCP连接发送到MySQL Server。默认端口号是3306,即如果发送到本机MySQL Server,地址就是127.0.0.1:3306

也可以只安装MySQL Client,然后连接到远程MySQL Server。假设远程MySQL Server的IP地址是10.0.1.99,那么就使用-h指定IP或域名:

1
mysql -h 10.0.1.99 -u root -p

管理MySQL

数据库

在一个运行MySQL的服务器上,实际上可以创建多个数据库(Database)。要列出所有数据库,使用命令:

1
2
3
4
5
6
7
8
9
10
11
12
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| shici |
| sys |
| test |
| school |
+--------------------+

其中,information_schemamysqlperformance_schemasys是系统库,不要去改动它们。其他的是用户创建的数据库。

要创建一个新数据库,使用命令:

1
2
mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.01 sec)

要删除一个数据库,使用命令:

1
2
mysql> DROP DATABASE test;
Query OK, 0 rows affected (0.01 sec)

注意:删除一个数据库将导致该数据库的所有表全部被删除。

对一个数据库进行操作时,要首先将其切换为当前数据库:

1
2
mysql> USE test;
Database changed

列出当前数据库的所有表,使用命令:

1
2
3
4
5
6
7
8
9
mysql> SHOW TABLES;
+---------------------+
| Tables_in_test |
+---------------------+
| classes |
| statistics |
| students |
| students_of_class1 |
+---------------------+

要查看一个表的结构,使用命令:

1
2
3
4
5
6
7
8
9
10
11
mysql> DESC students;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| class_id | bigint(20) | NO | | NULL | |
| name | varchar(100) | NO | | NULL | |
| gender | varchar(1) | NO | | NULL | |
| score | int(11) | NO | | NULL | |
+----------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

还可以使用以下命令查看创建表的SQL语句:

1
2
3
4
5
6
7
8
9
10
11
12
mysql> SHOW CREATE TABLE students;
+----------+-------------------------------------------------------+
| students | CREATE TABLE `students` ( |
| | `id` bigint(20) NOT NULL AUTO_INCREMENT, |
| | `class_id` bigint(20) NOT NULL, |
| | `name` varchar(100) NOT NULL, |
| | `gender` varchar(1) NOT NULL, |
| | `score` int(11) NOT NULL, |
| | PRIMARY KEY (`id`) |
| | ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 |
+----------+-------------------------------------------------------+
1 row in set (0.00 sec)

创建表使用CREATE TABLE语句,而删除表使用DROP TABLE语句:

1
2
mysql> DROP TABLE students;
Query OK, 0 rows affected (0.01 sec)

修改表就比较复杂。如果要给students表新增一列birth,使用:

1
ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;

要修改birth列,例如把列名改为birthday,类型改为VARCHAR(20)

1
ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;

要删除列,使用:

1
ALTER TABLE students DROP COLUMN birthday;

实用mysql语句

插入或替换

如果我们希望插入一条新记录(INSERT),但如果记录已经存在,就先删除原记录,再插入新记录。此时,可以使用REPLACE语句,这样就不必先查询,再决定是否先删除再插入:

1
REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

id=1的记录不存在,REPLACE语句将插入新记录,否则,当前id=1的记录将被删除,然后再插入新记录。

插入或更新

如果我们希望插入一条新记录(INSERT),但如果记录已经存在,就更新该记录,此时,可以使用INSERT INTO ... ON DUPLICATE KEY UPDATE ...语句:

1
INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;

id=1的记录不存在,INSERT语句将插入新记录,否则,当前id=1的记录将被更新,更新的字段由UPDATE指定。

插入或忽略

如果我们希望插入一条新记录(INSERT),但如果记录已经存在,就啥事也不干直接忽略,此时,可以使用INSERT IGNORE INTO ...语句:

1
INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

id=1的记录不存在,INSERT语句将插入新记录,否则,不执行任何操作。

快照

如果想要对一个表进行快照,即复制一份当前表的数据到一个新表,可以结合CREATE TABLESELECT

1
2
-- 对class_id=1的记录进行快照,并存储为新表students_of_class1:
CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;

新创建的表结构和SELECT使用的表结构完全一致。

写入查询结果集

如果查询结果集需要写入到表中,可以结合INSERTSELECT,将SELECT语句的结果集直接插入到指定表中。

例如,创建一个统计成绩的表statistics,记录各班的平均成绩:

1
2
3
4
5
6
CREATE TABLE statistics (
id BIGINT NOT NULL AUTO_INCREMENT,
class_id BIGINT NOT NULL,
average DOUBLE NOT NULL,
PRIMARY KEY (id)
);

然后,我们就可以用一条语句写入各班的平均成绩:

1
INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;

确保INSERT语句的列和SELECT语句的列能一一对应,就可以在statistics表中直接保存查询的结果:

1
2
3
4
5
6
7
8
9
> SELECT * FROM statistics;
+----+----------+--------------+
| id | class_id | average |
+----+----------+--------------+
| 1 | 1 | 86.5 |
| 2 | 2 | 73.666666666 |
| 3 | 3 | 88.333333333 |
+----+----------+--------------+
3 rows in set (0.00 sec)

强制使用指定索引

在查询的时候,数据库系统会自动分析查询语句,并选择一个最合适的索引。但是很多时候,数据库系统的查询优化器并不一定总是能使用最优索引。如果我们知道如何选择索引,可以使用FORCE INDEX强制查询使用指定的索引。例如:

1
> SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;

指定索引的前提是索引idx_class_id必须存在。

事务

  • A:Atomic,原子性,将所有SQL作为原子工作单元执行,要么全部执行,要么全部不执行;
  • C:Consistent,一致性,事务完成后,所有数据的状态都是一致的,即A账户只要减去了100,B账户则必定加上了100;
  • I:Isolation,隔离性,如果有多个事务并发执行,每个事务作出的修改必须与其他事务隔离;
  • D:Duration,持久性,即事务完成后,对数据库数据的修改被持久化存储。

隐式事务:单条mysql语句,数据库自动将其当作一个事务执行

显式事务:手动把多条SQL语句作为一个事务执行,使用BEGIN开启一个事务,使用COMMIT提交一个事务

1
2
3
4
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

COMMIT是指提交事务,即试图把事务内的所有SQL所做的修改永久保存。如果COMMIT语句执行失败了,整个事务也会失败。

有些时候,我们希望主动让事务失败,这时,可以用ROLLBACK回滚事务,整个事务会失败:

1
2
3
4
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK;

隔离级别

对于两个并发执行的事务,如果涉及到操作同一条记录的时候,可能会发生问题。因为并发操作会带来数据的不一致性,包括脏读、不可重复读、幻读等。数据库系统提供了隔离级别来让我们有针对性地选择事务的隔离级别,避免数据不一致的问题。

SQL标准定义了4种隔离级别,分别对应可能出现的数据不一致的情况:

Isolation Level 脏读(Dirty Read) 不可重复读(Non Repeatable Read) 幻读(Phantom Read)
Read Uncommitted Yes Yes Yes
Read Committed - Yes Yes
Repeatable Read - - Yes
Serializable - - -

Read Uncommitted

读取到的数据为上一个事务修改数据并且该数据回滚,数据失效,也就是脏数据

1
2
3
4
5
6
7
mysql> select * from students;
+----+-------+
| id | name |
+----+-------+
| 1 | Alice |
+----+-------+
1 row in set (0.00 sec)
时刻 事务A 事务B
1 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
2 BEGIN; BEGIN;
3 UPDATE students SET name = ‘Bob’ WHERE id = 1;
4 SELECT * FROM students WHERE id = 1;
5 ROLLBACK;
6 SELECT * FROM students WHERE id = 1;
7 COMMIT;

当事务A执行完第3步时,它更新了id=1的记录,但并未提交,而事务B在第4步读取到的数据就是未提交的数据。

随后,事务A在第5步进行了回滚,事务B再次读取id=1的记录,发现和上一次读取到的数据不一致,这就是脏读。

可见,在Read Uncommitted隔离级别下,一个事务可能读取到另一个事务更新但未提交的数据,这个数据有可能是脏数据。

Read Committed

1
2
3
4
5
6
7
mysql> select * from students;
+----+-------+
| id | name |
+----+-------+
| 1 | Alice |
+----+-------+
1 row in set (0.00 sec)
时刻 事务A 事务B
1 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
2 BEGIN; BEGIN;
3 SELECT * FROM students WHERE id = 1;
4 UPDATE students SET name = ‘Bob’ WHERE id = 1;
5 COMMIT;
6 SELECT * FROM students WHERE id = 1;
7 COMMIT;

当事务B第一次执行第3步的查询时,得到的结果是Alice,随后,由于事务A在第4步更新了这条记录并提交,所以,事务B在第6步再次执行同样的查询时,得到的结果就变成了Bob,因此,在Read Committed隔离级别下,事务不可重复读同一条记录,因为很可能读到的结果不一致。

Repeatable Read

1
2
3
4
5
6
7
mysql> select * from students;
+----+-------+
| id | name |
+----+-------+
| 1 | Alice |
+----+-------+
1 row in set (0.00 sec)

事务B在第3步第一次读取id=99的记录时,读到的记录为空,说明不存在id=99的记录。随后,事务A在第4步插入了一条id=99的记录并提交。事务B在第6步再次读取id=99的记录时,读到的记录仍然为空,但是,事务B在第7步试图更新这条不存在的记录时,竟然成功了,并且,事务B在第8步再次读取id=99的记录时,记录出现了。

可见,幻读就是没有读到的记录,以为不存在,但其实是可以更新成功的,并且,更新成功后,再次读取,就出现了。

Serializable

Serializable是最严格的隔离级别。在Serializable隔离级别下,所有事务按照次序依次执行,因此,脏读、不可重复读、幻读都不会出现。

虽然Serializable隔离级别下的事务具有最高的安全性,但是,由于事务是串行执行,所以效率会大大下降,应用程序的性能会急剧降低。如果没有特别重要的情景,一般都不会使用Serializable隔离级别。

默认隔离级别

如果没有指定隔离级别,数据库就会使用默认的隔离级别。在MySQL中,如果使用InnoDB,默认的隔离级别是Repeatable Read。


本博客所有文章除特别声明外,均采用 CC BY-SA 4.0 协议 ,转载先请联系作者且注明出处!