使用命令和SQL操作MySQL

显示数据库的版本


1
mysql> select version(), current_date;
2
+-----------+--------------+
3
| version() | current_date |
4
+-----------+--------------+
5
| 8.0.31    | 2023-02-15   |
6
+-----------+--------------+
7
1 row in set (0.04 sec)

创建一个数据库


xxxxxxxxxx
13
1
mysql> show databases;
2
+--------------------+
3
| Database           |
4
+--------------------+
5
| connecttest        |
6
| information_schema |
7
| mycloud            |
8
| mysql              |
9
| performance_schema |
10
| sys                |
11
+--------------------+
12
6 rows in set (1.48 sec)
13
#mysql数据库的内容是用来管理各个用户的权限的


xxxxxxxxxx
2
1
mysql> use mysql
2
Database changed


xxxxxxxxxx
2
1
mysql> create database test;
2
Query OK, 1 row affected (0.03 sec)


xxxxxxxxxx
2
1
mysql> use test
2
Database changed


xxxxxxxxxx
7
1
mysql> select database();
2
+------------+
3
| database() |
4
+------------+
5
| test       |
6
+------------+
7
1 row in set (0.00 sec)


xxxxxxxxxx
1
1
 drop database test;

创建一张表


xxxxxxxxxx
2
1
mysql> show tables;
2
Empty set (0.00 sec)


xxxxxxxxxx
3
1
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
2
    ->  species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
3
Query OK, 0 rows affected (0.11 sec)

常见的数据类型

分类	数据类型	说明
数值类型	BIT(M)	位类型。M指定位数，默认值1，范围1-64。
	BOOL，BOOLEAN	使用0或1表示假或真
	TINYINT [UNSIGNED] [ZEROFILL]	带符号的范围是-128到127。无符号0到255。
	SMALLINT [UNSIGNED] [ZEROFILL]	2的16次方
	INT [UNSIGNED] [ZEROFILL]	2的32次方
	BIGINT [UNSIGNED] [ZEROFILL]	2的64次方
	FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]	单精度浮点数，M指定显示长度，D指定小数位数
	DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]	双精度浮点数
文本、二进制类型	CHAR(size)	定长字符串，size是字符数
	VARCHAR(size)	变长字符串，size是上限
	BLOB	二进制数据比如图片、音乐等
	LONGBLOB	比较大的二进制数据
	TEXT(clob)	文本数据
	LONGTEXT(longclob)	比较大的文本数据
时间和日期	DATE	日期
	TIME	（一天之中的）时间
	DATETIME	时间和日期的组合
	TIMESTAMP	时间和日期的组合时间戳（其显示受时区的影响）


xxxxxxxxxx
12
1
mysql> describe pet; # 用desc也可以
2
+---------+-------------+------+-----+---------+-------+
3
| Field   | Type        | Null | Key | Default | Extra |
4
+---------+-------------+------+-----+---------+-------+
5
| name    | varchar(20) | YES  |     | NULL    |       |
6
| owner   | varchar(20) | YES  |     | NULL    |       |
7
| species | varchar(20) | YES  |     | NULL    |       |
8
| sex     | char(1)     | YES  |     | NULL    |       |
9
| birth   | date        | YES  |     | NULL    |       |
10
| death   | date        | YES  |     | NULL    |       |
11
+---------+-------------+------+-----+---------+-------+
12
6 rows in set (0.00 sec)

增加表内容：插入数据


xxxxxxxxxx
2
1
mysql> INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
2
Query OK, 1 row affected (0.03 sec)


xxxxxxxxxx
2
1
mysql> INSERT INTO pet (name,owner,species,birth) VALUES ('Whistler','Gwen','bird','1997-12-09');
2
Query OK, 1 row affected (0.01 sec)


xxxxxxxxxx
8
1
INSERT INTO pet VALUES('Fluffy','Harold','cat','f','1993-02-04' ,NULL);
2
INSERT INTO pet VALUES('Claws','Gwen','cat','m','1994-03-17'    ,NULL);
3
INSERT INTO pet VALUES('Buffy','Harold','dog','f','1989-05-13'  ,NULL);
4
INSERT INTO pet VALUES('Fang','Benny','dog','m','1990-08-27'    ,NULL);
5
INSERT INTO pet VALUES('Bowser','Diane','dog','m','1979-08-31','1995-07-29');
6
INSERT INTO pet VALUES('Chirpy','Gwen','bird','f','1998-09-11' ,NULL);
7
INSERT INTO pet VALUES('Whistler','Gwen','bird',NULL,'1997-12-09' ,NULL);
8
INSERT INTO pet VALUES('Slim','Benny','snake','m','1996-04-29',NULL);

查询表内容

展示表中的数据


xxxxxxxxxx
16
1
mysql> select * from pet;
2
+----------+--------+---------+------+------------+------------+
3
| name     | owner  | species | sex  | birth      | death      |
4
+----------+--------+---------+------+------------+------------+
5
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
6
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
7
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
8
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
9
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
10
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
11
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
12
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
13
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
14
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
15
+----------+--------+---------+------+------------+------------+
16
10 rows in set (0.00 sec)


xxxxxxxxxx
7
1
mysql> SELECT * FROM pet WHERE name = 'Bowser';
2
+--------+-------+---------+------+------------+------------+
3
| name   | owner | species | sex  | birth      | death      |
4
+--------+-------+---------+------+------------+------------+
5
| Bowser | Diane | dog     | m    | 1979-08-31 | 1995-07-29 |
6
+--------+-------+---------+------+------------+------------+
7
1 row in set (0.00 sec)


xxxxxxxxxx
8
1
mysql> SELECT * FROM pet WHERE birth >= '1998-1-1';
2
+----------+-------+---------+------+------------+-------+
3
| name     | owner | species | sex  | birth      | death |
4
+----------+-------+---------+------+------------+-------+
5
| Puffball | Diane | hamster | f    | 1999-03-30 | NULL  |
6
| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | NULL  |
7
+----------+-------+---------+------+------------+-------+
8
2 rows in set (0.00 sec)


x
1
mysql>  SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
2
+-------+--------+---------+------+------------+-------+
3
| name  | owner  | species | sex  | birth      | death |
4
+-------+--------+---------+------+------------+-------+
5
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
6
+-------+--------+---------+------+------------+-------+
7
1 row in set (0.00 sec)
8

9
mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
10
+----------+-------+---------+------+------------+-------+
11
| name     | owner | species | sex  | birth      | death |
12
+----------+-------+---------+------+------------+-------+
13
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL  |
14
| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | NULL  |
15
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL  |
16
| Slim     | Benny | snake   | m    | 1996-04-29 | NULL  |
17
+----------+-------+---------+------+------------+-------+
18
4 rows in set (0.00 sec)


xxxxxxxxxx
9
1
mysql>  SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')
2
    ->  OR (species = 'dog' AND sex = 'f');
3
+-------+--------+---------+------+------------+-------+
4
| name  | owner  | species | sex  | birth      | death |
5
+-------+--------+---------+------+------------+-------+
6
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
7
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
8
+-------+--------+---------+------+------------+-------+
9
2 rows in set (0.00 sec)


xxxxxxxxxx
16
1
mysql>  SELECT name, birth FROM pet;
2
+----------+------------+
3
| name     | birth      |
4
+----------+------------+
5
| Puffball | 1999-03-30 |
6
| Whistler | 1997-12-09 |
7
| Fluffy   | 1993-02-04 |
8
| Claws    | 1994-03-17 |
9
| Buffy    | 1989-05-13 |
10
| Fang     | 1990-08-27 |
11
| Bowser   | 1979-08-31 |
12
| Chirpy   | 1998-09-11 |
13
| Whistler | 1997-12-09 |
14
| Slim     | 1996-04-29 |
15
+----------+------------+
16
10 rows in set (0.01 sec)


xxxxxxxxxx
27
1
mysql> SELECT owner FROM pet;
2
+--------+
3
| owner  |
4
+--------+
5
| Diane  |
6
| Gwen   |
7
| Harold |
8
| Gwen   |
9
| Harold |
10
| Benny  |
11
| Diane  |
12
| Gwen   |
13
| Gwen   |
14
| Benny  |
15
+--------+
16
10 rows in set (0.00 sec)
17

18
mysql>  SELECT DISTINCT owner FROM pet;
19
+--------+
20
| owner  |
21
+--------+
22
| Diane  |
23
| Gwen   |
24
| Harold |
25
| Benny  |
26
+--------+
27
4 rows in set (0.00 sec)

select的参数和where子句可以配合使用：


xxxxxxxxxx
12
1
mysql> SELECT name, species, birth FROM pet
2
    ->  WHERE species = 'dog' OR species = 'cat';
3
+--------+---------+------------+
4
| name   | species | birth      |
5
+--------+---------+------------+
6
| Fluffy | cat     | 1993-02-04 |
7
| Claws  | cat     | 1994-03-17 |
8
| Buffy  | dog     | 1989-05-13 |
9
| Fang   | dog     | 1990-08-27 |
10
| Bowser | dog     | 1979-08-31 |
11
+--------+---------+------------+
12
5 rows in set (0.00 sec)

使用select语句的结果默认没有明确顺序，有些情况下，用户需要对结果进行排序以更快地得到想要的内容，此时，就可以使用order by子句：


xxxxxxxxxx
16
1
mysql>  SELECT name, birth FROM pet ORDER BY birth;
2
+----------+------------+
3
| name     | birth      |
4
+----------+------------+
5
| Bowser   | 1979-08-31 |
6
| Buffy    | 1989-05-13 |
7
| Fang     | 1990-08-27 |
8
| Fluffy   | 1993-02-04 |
9
| Claws    | 1994-03-17 |
10
| Slim     | 1996-04-29 |
11
| Whistler | 1997-12-09 |
12
| Whistler | 1997-12-09 |
13
| Chirpy   | 1998-09-11 |
14
| Puffball | 1999-03-30 |
15
+----------+------------+
16
10 rows in set (0.00 sec)

增加desc修饰可以按降序排序：


xxxxxxxxxx
16
1
mysql>  SELECT name, birth FROM pet ORDER BY birth DESC;
2
+----------+------------+
3
| name     | birth      |
4
+----------+------------+
5
| Puffball | 1999-03-30 |
6
| Chirpy   | 1998-09-11 |
7
| Whistler | 1997-12-09 |
8
| Whistler | 1997-12-09 |
9
| Slim     | 1996-04-29 |
10
| Claws    | 1994-03-17 |
11
| Fluffy   | 1993-02-04 |
12
| Fang     | 1990-08-27 |
13
| Buffy    | 1989-05-13 |
14
| Bowser   | 1979-08-31 |
15
+----------+------------+
16
10 rows in set (0.00 sec)

也可以根据多列情况依次排序：


xxxxxxxxxx
17
1
mysql> SELECT name, species, birth FROM pet
2
    ->  ORDER BY species, birth DESC;
3
+----------+---------+------------+
4
| name     | species | birth      |
5
+----------+---------+------------+
6
| Chirpy   | bird    | 1998-09-11 |
7
| Whistler | bird    | 1997-12-09 |
8
| Whistler | bird    | 1997-12-09 |
9
| Claws    | cat     | 1994-03-17 |
10
| Fluffy   | cat     | 1993-02-04 |
11
| Fang     | dog     | 1990-08-27 |
12
| Buffy    | dog     | 1989-05-13 |
13
| Bowser   | dog     | 1979-08-31 |
14
| Puffball | hamster | 1999-03-30 |
15
| Slim     | snake   | 1996-04-29 |
16
+----------+---------+------------+
17
10 rows in set (0.00 sec)

空值NULL相关的操作

使用is null和is not null运算符可以判断某个值是否为NULL：


xxxxxxxxxx
7
1
mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
2
+-----------+---------------+
3
| 1 IS NULL | 1 IS NOT NULL |
4
+-----------+---------------+
5
|         0 |             1 |
6
+-----------+---------------+
7
1 row in set (0.00 sec)

不能对NULL使用比较运算符，比如<、<>、=之类的：


xxxxxxxxxx
7
1
mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
2
+----------+-----------+----------+----------+
3
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
4
+----------+-----------+----------+----------+
5
|     NULL |      NULL |     NULL |     NULL |
6
+----------+-----------+----------+----------+
7
1 row in set (0.00 sec)

简单的模式匹配

使用like和not like可以启用通配符匹配：

_匹配任意单个字符；
%匹配任意字符串；


xxxxxxxxxx
28
1
mysql> SELECT * FROM pet WHERE name LIKE 'b%';
2
+--------+--------+---------+------+------------+------------+
3
| name   | owner  | species | sex  | birth      | death      |
4
+--------+--------+---------+------+------------+------------+
5
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
6
| Bowser | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
7
+--------+--------+---------+------+------------+------------+
8
2 rows in set (0.00 sec)
9

10
mysql> SELECT * FROM pet WHERE name LIKE '%fy';
11
+--------+--------+---------+------+------------+-------+
12
| name   | owner  | species | sex  | birth      | death |
13
+--------+--------+---------+------+------------+-------+
14
| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |
15
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |
16
+--------+--------+---------+------+------------+-------+
17
2 rows in set (0.00 sec)
18

19
mysql> SELECT * FROM pet WHERE name LIKE '%w%';
20
+----------+-------+---------+------+------------+------------+
21
| name     | owner | species | sex  | birth      | death      |
22
+----------+-------+---------+------+------------+------------+
23
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |
24
| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |
25
| Bowser   | Diane | dog     | m    | 1979-08-31 | 1995-07-29 |
26
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |
27
+----------+-------+---------+------+------------+------------+
28
4 rows in set (0.00 sec)

聚集函数和统计行数


xxxxxxxxxx
7
1
mysql>  SELECT COUNT(*) FROM pet;
2
+----------+
3
| COUNT(*) |
4
+----------+
5
|       10 |
6
+----------+
7
1 row in set (0.07 sec)


xxxxxxxxxx
22
1
mysql>  SELECT owner, COUNT(*) FROM pet GROUP BY owner;
2
+--------+----------+
3
| owner  | COUNT(*) |
4
+--------+----------+
5
| Diane  |        2 |
6
| Gwen   |        4 |
7
| Harold |        2 |
8
| Benny  |        2 |
9
+--------+----------+
10
4 rows in set (0.00 sec)
11

12
mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;
13
+---------+----------+
14
| species | COUNT(*) |
15
+---------+----------+
16
| hamster |        1 |
17
| bird    |        3 |
18
| cat     |        2 |
19
| dog     |        3 |
20
| snake   |        1 |
21
+---------+----------+
22
5 rows in set (0.01 sec)


xxxxxxxxxx
9
1
mysql> SELECT species,COUNT(species) FROM pet GROUP BY species HAving COUNT(species) >= 2;
2
+---------+----------------+
3
| species | count(species) |
4
+---------+----------------+
5
| bird    |              3 |
6
| cat     |              2 |
7
| dog     |              3 |
8
+---------+----------------+
9
3 rows in set (0.00 sec)

多表连接操作


xxxxxxxxxx
11
1
CREATE TABLE event (name VARCHAR(20), date DATE,type VARCHAR(15), remark VARCHAR(255));
2
INSERT INTO event VALUES('Fluffy','1995-05-15','litter','4 kittens, 3 female, 1 male');
3
INSERT INTO event VALUES('Buffy','1993-06-23','litter','5 puppies, 2 female, 3 male');
4
INSERT INTO event VALUES('Buffy','1994-06-19','litter','3 puppies, 3 female');
5
INSERT INTO event VALUES('Chirpy','1999-03-21','vet','needed beak straightened');
6
INSERT INTO event VALUES('Slim','1997-08-03','vet','broken rib');
7
INSERT INTO event VALUES('Bowser','1991-10-12','kennel',NULL);
8
INSERT INTO event VALUES('Fang','1991-10-12','kennel',NULL);
9
INSERT INTO event VALUES('Fang','1998-08-28','birthday','Gave him a new chew toy');
10
INSERT INTO event VALUES('Claws','1998-03-17','birthday','Gave him a new flea collar');
11
INSERT INTO event VALUES('Whistler','1998-12-09','birthday','First birthday');

下面，我们来执行一个多表内连接的例子：


xxxxxxxxxx
11
1
mysql> SELECT pet.name,remark
2
    ->  FROM pet INNER JOIN event
3
    ->  ON pet.name = event.name
4
    ->  WHERE event.type = 'litter';
5
+--------+-----------------------------+
6
| name   | remark                      |
7
+--------+-----------------------------+
8
| Fluffy | 4 kittens, 3 female, 1 male |
9
| Buffy  | 3 puppies, 3 female         |
10
| Buffy  | 5 puppies, 2 female, 3 male |
11
+--------+-----------------------------+

在from语句当中可以对多表进行连接操作以生成一个新表。

inner join表示内连接：pet表和event表的内连接会创建一个新表：新表当中的每一行，都是从由pet的某一行和event中某一行组合而成，并且必须两行都满足ON子句的条件。

需要注意的是，如果两个表当中存在同名字的列，需要使用.运算符来指明该列所归属的表。


xxxxxxxxxx
17
1
mysql> SELECT * FROM pet INNER JOIN event ON pet.name = event.name;
2
+----------+--------+---------+------+------------+------------+----------+------------+----------+-----------------------------+
3
| name     | owner  | species | sex  | birth      | death      | name     | date       | type     | remark                      |
4
+----------+--------+---------+------+------------+------------+----------+------------+----------+-----------------------------+
5
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       | Fluffy   | 1995-05-15 | litter   | 4 kittens, 3 female, 1 male |
6
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       | Buffy    | 1993-06-23 | litter   | 5 puppies, 2 female, 3 male |
7
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       | Buffy    | 1994-06-19 | litter   | 3 puppies, 3 female         |
8
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       | Chirpy   | 1999-03-21 | vet      | needed beak straightened    |
9
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       | Slim     | 1997-08-03 | vet      | broken rib                  |
10
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 | Bowser   | 1991-10-12 | kennel   | NULL                        |
11
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       | Fang     | 1991-10-12 | kennel   | NULL                        |
12
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       | Fang     | 1998-08-28 | birthday | Gave him a new chew toy     |
13
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       | Claws    | 1998-03-17 | birthday | Gave him a new flea collar  |
14
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       | Whistler | 1998-12-09 | birthday | First birthday              |
15
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       | Whistler | 1998-12-09 | birthday | First birthday              |
16
+----------+--------+---------+------+------------+------------+----------+------------+----------+-----------------------------+
17
11 rows in set (0.00 sec)

通过给表起别名可以实现自己和自己内连接，下面是示例：


xxxxxxxxxx
12
1
mysql>  SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
2
    ->  FROM pet AS p1 INNER JOIN pet AS p2
3
    ->  ON p1.species = p2.species
4
    ->  AND p1.sex = 'f' AND p1.death IS NULL
5
    ->  AND p2.sex = 'm' AND p2.death IS NULL;
6
+--------+------+-------+------+---------+
7
| name   | sex  | name  | sex  | species |
8
+--------+------+-------+------+---------+
9
| Fluffy | f    | Claws | m    | cat     |
10
| Buffy  | f    | Fang  | m    | dog     |
11
+--------+------+-------+------+---------+
12
2 rows in set (0.01 sec)

使用内连接得到的新表存在一个特点，那就是左表的行和右表的行都必须满足条件才会组合成新行出现在结果当中。在有些情况下，用户除了需要知道左表和右表当中有哪里公共对象以外，还需要知道哪些左表中的对象从来没有在右表当中出现过。这种情况下就需要使用左外连接：


xxxxxxxxxx
18
1
mysql> SELECT * FROM pet LEFT OUTER JOIN event ON pet.name = event.name;
2
+----------+--------+---------+------+------------+------------+----------+------------+----------+-----------------------------+
3
| name     | owner  | species | sex  | birth      | death      | name     | date       | type     | remark                      |
4
+----------+--------+---------+------+------------+------------+----------+------------+----------+-----------------------------+
5
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       | NULL     | NULL       | NULL     | NULL                        |
6
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       | Whistler | 1998-12-09 | birthday | First birthday              |
7
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       | Fluffy   | 1995-05-15 | litter   | 4 kittens, 3 female, 1 male |
8
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       | Claws    | 1998-03-17 | birthday | Gave him a new flea collar  |
9
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       | Buffy    | 1994-06-19 | litter   | 3 puppies, 3 female         |
10
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       | Buffy    | 1993-06-23 | litter   | 5 puppies, 2 female, 3 male |
11
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       | Fang     | 1998-08-28 | birthday | Gave him a new chew toy     |
12
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       | Fang     | 1991-10-12 | kennel   | NULL                        |
13
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 | Bowser   | 1991-10-12 | kennel   | NULL                        |
14
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       | Chirpy   | 1999-03-21 | vet      | needed beak straightened    |
15
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       | Whistler | 1998-12-09 | birthday | First birthday              |
16
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       | Slim     | 1997-08-03 | vet      | broken rib                  |
17
+----------+--------+---------+------+------------+------------+----------+------------+----------+-----------------------------+
18
12 rows in set (0.00 sec)

右外连接和左外连接类型，不同之处在于会把右表当中存在，而在左表中未出现的对象也放入新表当中。


xxxxxxxxxx
18
1
delete from pet where name = 'Chirpy'; # 先删除左表当中的一行
2
mysql> SELECT * FROM pet LEFT OUTER JOIN event ON pet.name = event.name;
3
+----------+--------+---------+------+------------+------------+----------+------------+----------+-----------------------------+
4
| name     | owner  | species | sex  | birth      | death      | name     | date       | type     | remark                      |
5
+----------+--------+---------+------+------------+------------+----------+------------+----------+-----------------------------+
6
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       | NULL     | NULL       | NULL     | NULL                        |
7
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       | Whistler | 1998-12-09 | birthday | First birthday              |
8
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       | Fluffy   | 1995-05-15 | litter   | 4 kittens, 3 female, 1 male |
9
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       | Claws    | 1998-03-17 | birthday | Gave him a new flea collar  |
10
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       | Buffy    | 1994-06-19 | litter   | 3 puppies, 3 female         |
11
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       | Buffy    | 1993-06-23 | litter   | 5 puppies, 2 female, 3 male |
12
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       | Fang     | 1998-08-28 | birthday | Gave him a new chew toy     |
13
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       | Fang     | 1991-10-12 | kennel   | NULL                        |
14
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 | Bowser   | 1991-10-12 | kennel   | NULL                        |
15
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       | Whistler | 1998-12-09 | birthday | First birthday              |
16
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       | Slim     | 1997-08-03 | vet      | broken rib                  |
17
+----------+--------+---------+------+------------+------------+----------+------------+----------+-----------------------------+
18
11 rows in set (0.00 sec)

全外连接是左外连接和右外连接的并集，但是MySQL不支持。

修改表内容

更新


xxxxxxxxxx
20
1
mysql> UPDATE event SET remark = 'test' WHERE remark IS NULL;
2
Query OK, 2 rows affected (0.04 sec)
3
Rows matched: 2  Changed: 2  Warnings: 0
4

5
mysql> select * from event;
6
+----------+------------+----------+-----------------------------+
7
| name     | date       | type     | remark                      |
8
+----------+------------+----------+-----------------------------+
9
| Fluffy   | 1995-05-15 | litter   | 4 kittens, 3 female, 1 male |
10
| Buffy    | 1993-06-23 | litter   | 5 puppies, 2 female, 3 male |
11
| Buffy    | 1994-06-19 | litter   | 3 puppies, 3 female         |
12
| Chirpy   | 1999-03-21 | vet      | needed beak straightened    |
13
| Slim     | 1997-08-03 | vet      | broken rib                  |
14
| Bowser   | 1991-10-12 | kennel   | test                        |
15
| Fang     | 1991-10-12 | kennel   | test                        |
16
| Fang     | 1998-08-28 | birthday | Gave him a new chew toy     |
17
| Claws    | 1998-03-17 | birthday | Gave him a new flea collar  |
18
| Whistler | 1998-12-09 | birthday | First birthday              |
19
+----------+------------+----------+-----------------------------+
20
10 rows in set (0.00 sec)

删除


xxxxxxxxxx
17
1
mysql> DELETE FROM event WHERE name = 'Fang';
2
Query OK, 2 rows affected (0.03 sec)
3

4
mysql> SELECT * FROM event;
5
+----------+------------+----------+-----------------------------+
6
| name     | date       | type     | remark                      |
7
+----------+------------+----------+-----------------------------+
8
| Fluffy   | 1995-05-15 | litter   | 4 kittens, 3 female, 1 male |
9
| Buffy    | 1993-06-23 | litter   | 5 puppies, 2 female, 3 male |
10
| Buffy    | 1994-06-19 | litter   | 3 puppies, 3 female         |
11
| Chirpy   | 1999-03-21 | vet      | needed beak straightened    |
12
| Slim     | 1997-08-03 | vet      | broken rib                  |
13
| Bowser   | 1991-10-12 | kennel   | test                        |
14
| Claws    | 1998-03-17 | birthday | Gave him a new flea collar  |
15
| Whistler | 1998-12-09 | birthday | First birthday              |
16
+----------+------------+----------+-----------------------------+
17
8 rows in set (0.00 sec)

操作表结构

获取表的创建信息


xxxxxxxxxx
12
1
mysql> SHOW CREATE TABLE event;
2
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3
| Table | Create Table                                                                                                                                                                                                                     |
4
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5
| event | CREATE TABLE `event` (
6
  `name` varchar(20) DEFAULT NULL,
7
  `date` date DEFAULT NULL,
8
  `type` varchar(15) DEFAULT NULL,
9
  `remark` varchar(255) DEFAULT NULL
10
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
11
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
12
1 row in set (0.05 sec)

修改表的结构


xxxxxxxxxx
46
1
mysql> ALTER TABLE event ADD (info varchar(100));
2
Query OK, 0 rows affected (0.57 sec)
3
Records: 0  Duplicates: 0  Warnings: 0
4

5
mysql> DESC event;
6
+--------+--------------+------+-----+---------+-------+
7
| Field  | Type         | Null | Key | Default | Extra |
8
+--------+--------------+------+-----+---------+-------+
9
| name   | varchar(20)  | YES  |     | NULL    |       |
10
| date   | date         | YES  |     | NULL    |       |
11
| type   | varchar(15)  | YES  |     | NULL    |       |
12
| remark | varchar(255) | YES  |     | NULL    |       |
13
| info   | varchar(100) | YES  |     | NULL    |       |
14
+--------+--------------+------+-----+---------+-------+
15
5 rows in set (0.13 sec)
16

17
mysql> ALTER TABLE event DROP info;
18
Query OK, 0 rows affected (0.05 sec)
19
Records: 0  Duplicates: 0  Warnings: 0
20

21
mysql> DESC event;
22
+--------+--------------+------+-----+---------+-------+
23
| Field  | Type         | Null | Key | Default | Extra |
24
+--------+--------------+------+-----+---------+-------+
25
| name   | varchar(20)  | YES  |     | NULL    |       |
26
| date   | date         | YES  |     | NULL    |       |
27
| type   | varchar(15)  | YES  |     | NULL    |       |
28
| remark | varchar(255) | YES  |     | NULL    |       |
29
+--------+--------------+------+-----+---------+-------+
30
4 rows in set (0.00 sec)
31

32

33
mysql> ALTER TABLE event CHANGE remark remark1 varchar(254);
34
Query OK, 8 rows affected (0.22 sec)
35
Records: 8  Duplicates: 0  Warnings: 0
36

37
mysql> DESC event;
38
+---------+--------------+------+-----+---------+-------+
39
| Field   | Type         | Null | Key | Default | Extra |
40
+---------+--------------+------+-----+---------+-------+
41
| name    | varchar(20)  | YES  |     | NULL    |       |
42
| date    | date         | YES  |     | NULL    |       |
43
| type    | varchar(15)  | YES  |     | NULL    |       |
44
| remark1 | varchar(254) | YES  |     | NULL    |       |
45
+---------+--------------+------+-----+---------+-------+
46
4 rows in set (0.00 sec)

修改表的名字


xxxxxxxxxx
11
1
mysql> RENAME TABLE event TO events;
2
Query OK, 0 rows affected (0.01 sec)
3

4
mysql> show tables;
5
+----------------+
6
| Tables_in_test |
7
+----------------+
8
| events         |
9
| pet            |
10
+----------------+
11
2 rows in set (0.00 sec)

表的约束

为了保证数据的完整性，数据库当中所有存储数据的值都必须满足一些特定的一致性约束。这些一致性约束是为了保证插入到数据库中的数据是正确的，它防止了用户可能的输入错误。主要分为以下三类：

实体完整性：规定表的一行（即每一条记录）在表中是唯一的实体。实体完整性通过表的主键来实现。
域完整性：指数据库表的列（即字段）必须符合某种特定的数据类型或约束。比如NOT NULL。
参照完整性：保证一个表的外键和另一个表的主键对应。参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

可以在创建表的时候设置好约束，下面是创建表的时候设置约束的语法。


xxxxxxxxxx
4
1
 col_name datatype
2
 [NOT NULL | NULL] [DEFAULT default_value]
3
 [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
4
 [reference_definition]

参照约束的语法如下：


xxxxxxxxxx
1
1
REFERENCES tbl_name (key_part,...)

使用primary key指明主键约束，即不允许为空也不允许重复，从而可以区分两条记录的唯一性。


xxxxxxxxxx
11
1
mysql> create table owner (name varchar(80) primary key,sex char(1));
2
Query OK, 0 rows affected (0.01 sec)
3

4
mysql> desc owner;
5
+-------+-------------+------+-----+---------+-------+
6
| Field | Type        | Null | Key | Default | Extra |
7
+-------+-------------+------+-----+---------+-------+
8
| name  | varchar(80) | NO   | PRI | NULL    |       |
9
| sex   | char(1)     | YES  |     | NULL    |       |
10
+-------+-------------+------+-----+---------+-------+
11
2 rows in set (0.00 sec)

也可以在表创建完成之后，使用alter table语句再增加或者删除主键约束：


xxxxxxxxxx
30
1
mysql> alter table owner drop primary key;
2
Query OK, 0 rows affected (0.02 sec)
3
Records: 0  Duplicates: 0  Warnings: 0
4

5
mysql> desc owner;
6
+-------+-------------+------+-----+---------+-------+
7
| Field | Type        | Null | Key | Default | Extra |
8
+-------+-------------+------+-----+---------+-------+
9
| name  | varchar(80) | NO   |     | NULL    |       |
10
| sex   | char(1)     | YES  |     | NULL    |       |
11
+-------+-------------+------+-----+---------+-------+
12
2 rows in set (0.01 sec)
13

14
mysql> alter table owner add(id int);
15
Query OK, 0 rows affected (0.02 sec)
16
Records: 0  Duplicates: 0  Warnings: 0
17

18
mysql> alter table owner add constraint primary key(id);
19
Query OK, 0 rows affected (0.04 sec)
20
Records: 0  Duplicates: 0  Warnings: 0
21

22
mysql> desc owner;
23
+-------+-------------+------+-----+---------+-------+
24
| Field | Type        | Null | Key | Default | Extra |
25
+-------+-------------+------+-----+---------+-------+
26
| name  | varchar(80) | NO   |     | NULL    |       |
27
| sex   | char(1)     | YES  |     | NULL    |       |
28
| id    | int         | NO   | PRI | NULL    |       |
29
+-------+-------------+------+-----+---------+-------+
30
3 rows in set (0.00 sec)

下面的示例中可以设置自动增长的主键：


xxxxxxxxxx
26
1
mysql> alter table owner add id int primary key auto_increment;
2
Query OK, 0 rows affected (0.05 sec)
3
Records: 0  Duplicates: 0  Warnings: 0
4

5
mysql> insert into owner (name,sex) VALUES ('Diane','f');
6
Query OK, 1 row affected (0.01 sec)
7

8
mysql> insert into owner (name,sex) VALUES ('Gwen','m');
9
Query OK, 1 row affected (0.00 sec)
10

11
mysql> insert into owner (name,sex) VALUES ('Harold','m');
12
Query OK, 1 row affected (0.00 sec)
13

14
mysql> insert into owner (name,sex) VALUES ('Benny','f');
15
Query OK, 1 row affected (0.00 sec)
16

17
mysql> select * from owner;
18
+--------+------+----+
19
| name   | sex  | id |
20
+--------+------+----+
21
| Diane  | f    |  1 |
22
| Gwen   | m    |  2 |
23
| Harold | m    |  3 |
24
| Benny  | f    |  4 |
25
+--------+------+----+
26
4 rows in set (0.00 sec)

MySQL C API


xxxxxxxxxx
32
1
MYSQL * mysql_init(MYSQL *mysql);//为MySQL连接分配资源，参数一般填NULL
2
//数据结构MYSQL是操作资源的句柄
3

4
void mysql_close(MYSQL *mysql);
5
//关闭MYSQL连接
6

7
MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char *user,
8
const char *passwd, const char *db, unsigned int port, const char *unix_socket,
9
unsigned long client_flag);//连接到MySQL服务端
10
//一般的参数
11
// host -> "localhost" user -> "root" passwd-> 密码 db->数据库名
12
// 其余参数选择默认 port -> 0 unix_socket -> NULL client_flag -> 0
13
// 如果出现报错，返回值为NULL，使用mysql_error函数可以获取报错原因
14

15

16
int mysql_query(MYSQL *mysql, const char *stmt_str);
17
// 执行SQL语句，stmt_str -> SQL语句的内容 不需要加分号
18
    
19
MYSQL_RES *mysql_store_result(MYSQL *mysql);
20
// 在mysql_query之后调用，假如执行的SQL语句会得到结果，需要使用该函数将结果存入数据结构MYSQL_RES当中
21
    
22
void mysql_free_result(MYSQL_RES *result);
23
// 释放数据结构MYSQL_RES占据的内存空间
24

25
my_ulonglong mysql_num_rows(MYSQL_RES *result);
26
// SQL语句结果的行数
27

28
unsigned int mysql_num_fields(MYSQL_RES *result);
29
// SQL语句结果的列数
30

31
MYSQL_ROW mysql_fetch_row(MYSQL_RES *result);
32
// 从结果当中取出一行


xxxxxxxxxx
4
1
//MYSQL 代表了一个MYSQL连接
2
//MYSQL_RES SQL指令的执行结果，可以理解成一个二维数组，或者是若干行的集合
3
//MYSQL_ROW 一行，实际上底层是一个字符串数组，每一个字符串值代表了表中一个具体值，无论其在数据库当中是什么数据类型。
4
//my_ulonglong 用来描述行数的无符号整数


xxxxxxxxxx
30
1
#include<func.h>
2
#include<mysql/mysql.h>
3
int main(){
4
    MYSQL *db = mysql_init(NULL);
5
    char *host = "localhost";
6
    char *user = "root";
7
    char *password = "123";
8
    char *database = "test";
9
    MYSQL *ret = mysql_real_connect(db,host,user,password,database,0,NULL,0);
10
    if(ret == NULL){
11
        printf("Error: %s\n", mysql_error(db));
12
        return -1;
13
    }
14
    char *sql = "select * from pet";
15
    int qret = mysql_query(db,sql);
16
    if(qret != 0){
17
        printf("Error: %s\n", mysql_error(db));
18
        return -1;
19
    }
20
    MYSQL_RES *result = mysql_store_result(db);
21
    printf("total rows: %lu\n",mysql_num_rows(result));
22
    MYSQL_ROW row;
23
    while((row = mysql_fetch_row(result)) != NULL){
24
        for(int i = 0; i < mysql_num_fields(result); ++i){
25
            printf("%s\t", row[i]);
26
        }
27
        printf("\n");
28
    }
29
    mysql_close(db);
30
}

事务和索引

事务和隔离级别

事务的基本概念

MySQL中的事务是一系列SQL操作的集合，这些操作要么全部成功执行，要么全部失败回滚。事务是数据库管理系统提供的一种机制，用于确保数据库的完整性、一致性和持久性。事务具有以下特性：

原子性（Atomicity）：事务是一个原子操作单元，不可再分割。这意味着事务中的所有操作要么全部成功执行，要么全部失败回滚，即使在系统发生故障时也是如此。
一致性（Consistency）：事务的执行使数据库从一个一致性状态转移到另一个一致性状态。在事务开始之前和事务结束之后，数据库都必须保持一致性。
隔离性（Isolation）：事务的执行不受其他事务的干扰。即使在同一时间有多个事务同时运行，每个事务也必须与其他事务隔离，以防止数据损坏或不一致。
持久性（Durability）：一旦事务提交成功，对数据库的改变将是永久性的，即使系统发生故障也不会丢失。

上面的四个性质一般可以缩写为ACID。值得注意的是，一致性和其他三个性质并不在同一个层次上，具体来说就是：一致性是上层业务追求的目标，而原子性、隔离性和持久性是事务本身的属性。原子性、一致性和隔离性一起合作从而实现一致性。

下面我们通过一个例子来说明上述性质：

当涉及到银行转账时，事务的性质尤为重要，因为涉及到资金的安全和准确性。假设有一个银行系统，其中有两个账户：账户A和账户B。现在，用户要进行一笔转账操作，从账户A向账户B转移一定金额的资金。

首先，业务目标是一致性：在转账之前和之后，系统必须保持一致性。也就是说，无论转账是否成功，银行系统都必须确保账户A和账户B的总余额保持不变。如果转账成功，账户A的余额减少了100元，而账户B的余额增加了100元，使得总资金仍然保持不变。

那么事务是如何保证执行过程中总资金不变的呢？

原子性：假设用户要转移100元。在一个事务中，银行系统会首先从账户A中扣除100元，然后将这笔资金存入账户B。在这个过程中，要么这两个操作都成功执行，要么都失败。如果任何一个操作失败，例如因为网络故障或系统崩溃，资金将不会丢失，因为整个操作将被回滚，账户A和账户B的余额将保持不变。
隔离性：当一个用户正在进行转账操作时，其他用户可能也同时进行转账操作或查询账户余额。隔离性要求这些并发操作之间不会相互干扰。在事务中，转账操作应该独立于其他操作，直到事务完成。这意味着在转账过程中，其他用户不应该看到账户余额的不一致或不正确的状态。
持久性：一旦转账操作成功提交，银行系统必须保证这笔资金转移是永久性的。即使系统发生故障或重新启动，转账后的余额变化也必须得到保留，不能丢失。

通过使用事务，银行系统可以确保转账操作的安全性和准确性，从而保护用户的资金和数据。

在MySQL当中使用事务

下面是在MySQL当中和事务相关的几个命令。

命令名称	描述
START TRANSACTION/BEGIN	用于启动一个新的事务。在启动事务之后，后续的SQL操作将被视为一个原子操作序列，直到显式地提交或回滚事务为止。
COMMIT	用于提交事务。当事务中的所有操作都成功完成并且需要将更改永久保存到数据库时，应使用COMMIT命令。提交事务会使得事务中的所有操作生效。
ROLLBACK	用于回滚事务。当在事务中发生错误或需要取消之前的更改时，可以使用ROLLBACK命令。回滚将撤销事务中的所有操作，并将数据库恢复到事务开始之前的状态。
SAVEPOINT	用于创建一个保存点（Savepoint），使得可以在事务中的任何位置进行部分回滚。SAVEPOINT命令允许将事务分割成更小的逻辑单元，并在需要时回滚到指定的保存点。
RELEASE SAVEPOINT	用于释放一个保存点，表示不再需要回滚到该保存点。RELEASE SAVEPOINT命令允许释放事务中已经创建的保存点，以节省资源。
ROLLBACK TO SAVEPOINT	用于回滚到指定的保存点。当需要在事务中的某个位置进行部分回滚时，可以使用ROLLBACK TO SAVEPOINT命令将事务回滚到指定的保存点。


xxxxxxxxxx
35
1
# 构建表格并插入数据
2
CREATE TABLE bank_accounts (
3
    id INT AUTO_INCREMENT PRIMARY KEY,
4
    account_number VARCHAR(20) UNIQUE,
5
    balance DECIMAL(10, 2)
6
);
7

8
INSERT INTO bank_accounts (account_number, balance) VALUES
9
('1001', 1000.00),
10
('1002', 2000.00);
11

12
# 开始第一个事务
13
BEGIN; # 或者START TRANSACTION
14
# 随便执行几个DML
15
select * from bank_accounts;
16
update bank_accounts set balance = 1100 where id = 1;
17
# 在update之后，在本窗口和另一个窗口看到的表内容是不一样的
18
# 撤销修改并终止
19
ROLLBACK;
20

21
# 开始第二个事务
22
BEGIN; # 或者START TRANSACTION
23
# 执行几个DML
24
update bank_accounts set balance = 1100 where id = 1;
25
# 创建一个保存点
26
SAVEPOINT point1;
27
# 执行一个错误的语句
28
update bank_accounts set balance = 1900 where id = 1;
29
# 回到保存点
30
ROLLBACK TO point1;
31
# 执行一个正确的语句
32
update bank_accounts set balance = 1900 where id = 2;
33
# 提交事务
34
COMMIT;
35
# 之后本窗口和其他窗口看到的内容就一致了

如果系统变量autocommit的数值为1，那么每一句DML默认就是一个事务。如果系统变量autocommit的数值为0，那么执行DML默认在一个事务当中，需要commit才能结束事务。


xxxxxxxxxx
10
1
mysql> set autocommit = 0;
2
Query OK, 0 rows affected (0.00 sec)
3

4
mysql> show variables like 'autocommit';
5
+---------------+-------+
6
| Variable_name | Value |
7
+---------------+-------+
8
| autocommit    | OFF   |
9
+---------------+-------+
10
1 row in set (0.00 sec)

并发带来的问题

在之前的例子当中，我们了解到事务具有隔离性：在一个连接的事务执行过程中，另一个连接当中是无法立刻感知到表内容的数据变化的。隔离性的实现需要数据库底层能够处理多个用户并发访问带来的问题。那接下来我们就要解决这两个问题：

并发会带来什么样的问题？所有的并发异常都是不可接受的吗？
隔离程度会存在不同的级别吗？隔离级别越高越好吗？

脏写（Dirty Write）：脏写是指在并发环境下，一个事务修改了另一个事务尚未提交的数据，然后另一个事务又修改了相同的数据并提交，导致数据变得不一致或无效。
脏读（Dirty Read）：当一个事务读取了另一个事务尚未提交的数据时，就发生了脏读。如果该事务后来回滚，则读取的数据实际上是无效的或不一致的。这可能会导致系统中出现不正确的结果。
不可重复读（Non-repeatable Read）：不可重复读是指在一个事务内多次读取同一行数据，但由于其他事务的更新操作，每次读取的数据都不一致。这可能会导致在同一事务内对相同数据进行多次读取时出现不一致的结果。
幻读（Phantom Read）：幻读则是同一事务中，两次查询同一条件返回的行数不同，因为其他事务插入了新数据。

隔离级别

为了在临界区的持续时长和不同程度的并发问题之间取权衡，数据库提出了隔离级别的概念：

读未提交（Read Uncommitted）：
- 在这个隔离级别下，一个事务可以读取到其他事务尚未提交的数据。这是最低的隔离级别，可能导致脏读、不可重复读和幻读等并发问题。
读已提交（Read Committed）：
- 在这个隔离级别下，一个事务只能读取到其他事务已经提交的数据。这可以避免脏读，但是仍然可能发生不可重复读和幻读的问题。
可重复读（Repeatable Read）：
- 在这个隔离级别下，一个事务在同一个事务中多次读取相同的数据，得到的结果是一致的。这可以避免脏读和不可重复读，但是仍然可能发生幻读的问题。
串行化（Serializable）：
- 在这个隔离级别下，事务是串行执行的，每个事务都像是在独立的系统中执行一样。这可以避免脏读、不可重复读和幻读等所有并发问题，但是会降低系统的并发性能。

从上往下的隔离级别等级越来越高，等级越高，隔离效果就越好，但是消耗的系统资源也越多，用户可以根据实际情况进行设定。这些隔离级别可以不同程度地解决之前并发带来的问题。如下表所示：

	脏写	脏读	不可重复读	幻读
读未提交 READ UNCOMMITTED	×	√	√	√
读已提交 READ COMMITTED	×	×	√	√
可重复读 REPEATABLE READ	×	×	×	√
串行化 SERIALIZABLE	×	×	×	×

索引

想象一下，你有一本很厚的书，想要快速找到某个特定的章节或内容。如果没有目录（索引），你可能需要从头到尾一页一页地翻阅，这会非常耗时。书的目录就是一种索引，它列出了章节名称和对应的页码，让你能够迅速定位到目标内容。

在 MySQL 中，索引（Index） 也是类似的概念。它是数据库表中一列或多列值的排序结构，用于帮助 MySQL 高效地获取数据。数据库索引允许数据库服务器比在没有索引的情况下更快地查找和检索特定的行。

简单来说，索引是数据库管理系统中一个排序的数据结构，用于提高数据检索操作的速度。

索引的优点

使用索引的主要目的是提高数据库的查询性能。具体来说，有以下几个优点：

提高查询速度：这是索引最核心的价值。通过索引，MySQL 可以不必扫描整个表（全表扫描），而是直接定位到符合条件的记录，大大减少了查询所需的时间，尤其是在数据量巨大的表中。
保证数据的唯一性：通过创建唯一索引（Unique Index），可以确保表中的某一列（或几列组合）的值是唯一的，防止插入重复数据。主键索引本身就是一种唯一索引。
加速表连接（JOIN 操作）：如果连接条件中的列建立了索引，MySQL 可以更快地找到匹配的行，从而加速 JOIN 操作。
加速排序（ORDER BY）和分组（GROUP BY）：如果排序或分组的列上有索引，MySQL 可以利用索引的有序性来避免额外的排序操作，或者更快地进行分组。
优化 WHERE 子句的查询：索引使得数据库引擎能够快速定位到满足 WHERE 子句条件的行。

索引的基本原理

MySQL 中的索引通常使用特定的数据结构来实现，最常见的是 B-Tree（或其变种，如 B+Tree）。

B+Tree 结构：
- B+Tree 是一种平衡多路搜索树。它的特点是所有数据都存储在叶子节点上，并且叶子节点之间通过指针连接，形成一个有序链表。
- 非叶子节点只存储键值（索引列的值）和指向下一层节点的指针，用于快速导航。
- 当你在某个列上创建索引时，MySQL 会为该列（或多列组合）的值构建一个 B+Tree。树的节点中存储了索引列的值和指向表中实际数据行位置的指针（例如，主键值或行物理地址）。
查询过程：
- 当执行一个带有 WHERE 子句的查询时，如果 WHERE 子句中引用的列上有索引，MySQL 会利用这个 B+Tree 结构。
- 它会从 B+Tree 的根节点开始，根据查询条件中的值逐层向下查找，直到找到叶子节点中对应的索引条目。
- 这个索引条目包含了指向表中实际数据行的指针，MySQL 随后通过这个指针快速读取所需的数据行。

这种方式避免了逐行扫描整个表的低效操作。

索引类型

MySQL 支持多种类型的索引，以适应不同的查询需求：

主键索引 (PRIMARY KEY)：
- 一种特殊的唯一索引，用于唯一标识表中的每一行。
- 每个表只能有一个主键。
- 主键列的值不能为空（NOT NULL）且必须唯一。
- 在 InnoDB 存储引擎中，表数据本身就是按照主键索引（聚簇索引）的顺序存储的。
唯一索引 (UNIQUE INDEX)：
- 确保索引列中的所有值都是唯一的，但允许有单个 NULL 值（如果列允许 NULL）。
- 一个表可以有多个唯一索引。
普通索引/二级索引 (NORMAL INDEX / SECONDARY INDEX)：
- 最基本的索引类型，没有任何唯一性或主键的限制。
- 其唯一任务就是加快对数据的访问速度。
- 也称为非唯一索引。
全文索引 (FULLTEXT INDEX)：
- 主要用于在文本数据（如 CHAR, VARCHAR, TEXT 类型的列）中进行关键词搜索。
- 它允许你执行更复杂的文本搜索，比如查找包含特定单词或短语的行，而不是简单的精确匹配或范围匹配。
- 主要在 MyISAM 和 InnoDB (MySQL 5.6+) 存储引擎中支持。
空间索引 (SPATIAL INDEX)：
- 用于地理空间数据类型（如 GEOMETRY 类型）。
- 允许对地理空间数据进行高效查询，例如查找某个点附近的地点。
- 主要在 MyISAM 存储引擎中支持。
组合索引 (Composite Index / Multi-column Index)：
- 在表的多个列上创建的索引。
- 当查询条件涉及到这些列的组合时，组合索引会非常有效。
- 遵循“最左前缀原则”：查询条件必须从索引的最左边的列开始使用，才能有效地利用该索引。例如，如果在 (col1, col2, col3) 上创建了组合索引，那么 WHERE col1 = ?、WHERE col1 = ? AND col2 = ?、WHERE col1 = ? AND col2 = ? AND col3 = ? 都可以有效利用该索引。但 WHERE col2 = ? 或 WHERE col3 = ? 则不能。
覆盖索引 (Covering Index)：
- 这不是一种特定的索引类型，而是一种索引的使用方式。
- 当一个查询需要的所有数据都可以直接从索引中获取，而无需回表（即无需访问表中的实际数据行）时，这个索引就被称为覆盖索引。
- 覆盖索引可以极大地提高查询性能，因为它减少了磁盘 I/O。

索引的缺点

虽然索引能带来巨大的性能提升，但它们并非没有代价：

占用存储空间：索引本身也是数据，需要存储在磁盘上，有时甚至可能比数据本身占用的空间还要大。
降低写操作（INSERT, UPDATE, DELETE）的速度：当对表中的数据进行增加、删除或修改时，不仅需要修改数据本身，还需要更新相关的索引结构，以保持索引的正确性和有序性。这会增加写操作的开销。索引越多，写操作的开销就越大。
创建和维护索引需要时间：创建索引可能是一个耗时的操作，尤其是在大表上。索引的维护（如重建、优化）也需要时间和资源。

何时创建索引

经常用于 WHERE 子句的列：这是最常见的创建索引的场景。
经常用于 JOIN 操作的连接条件的列：例如外键列。
经常用于 ORDER BY 子句的列：可以利用索引的有序性避免额外的排序操作。
经常用于 GROUP BY 子句的列：可以帮助更快地进行分组。
列中值的区分度高（高基数性）：如果一个列有很多不同的值（例如用户ID），那么索引的效果会比较好。如果一个列的值大部分都相同（例如性别，只有男/女/未知），那么索引的效果可能不佳，甚至会降低性能。
查询中需要覆盖索引的场景。

何时不宜创建索引？

表记录太少：如果表中的数据量非常小（比如几百行），那么全表扫描可能比使用索引更快，因为索引查找本身也有开销。
数据重复度高、选择性低的列：例如“性别”列，索引带来的性能提升可能微乎其微，甚至可能因为维护索引而降低整体性能。
经常进行写操作（INSERT, UPDATE, DELETE）且读操作不频繁的列：索引会增加写操作的负担。需要权衡读写性能。
不再使用或者很少使用的索引：它们只会占用空间并拖慢写操作，应该及时删除。

创建索引的基本语法示例


xxxxxxxxxx
14
1
-- 创建普通索引
2
CREATE INDEX idx_lastname ON employees (last_name);
3

4
-- 创建唯一索引
5
CREATE UNIQUE INDEX idx_email ON employees (email);
6

7
-- 创建组合索引
8
CREATE INDEX idx_name_dept ON employees (last_name, department_id);
9

10
-- 创建主键索引 (通常在创建表时定义)
11
ALTER TABLE employees ADD PRIMARY KEY (employee_id);
12

13
-- 创建全文索引
14
CREATE FULLTEXT INDEX idx_article_content ON articles (content);

索引优化的一些提示

选择合适的列创建索引：不是越多越好。
利用最左前缀原则：对于组合索引，确保查询条件能利用到索引的前导列。
避免在索引列上使用函数或进行运算：这会导致索引失效。例如 WHERE YEAR(date_col) = 2023 不会使用 date_col 上的索引，应该改为 WHERE date_col >= '2023-01-01' AND date_col < '2024-01-01'。
使用 EXPLAIN 分析查询计划：查看 MySQL 如何执行你的查询，是否有效利用了索引。
定期维护索引：例如使用 OPTIMIZE TABLE（对 MyISAM）或监控索引碎片情况。
考虑覆盖索引：尽量让查询只通过索引就能返回结果。