1. MySQL数据库的特点

MySQL是一个小型的关系型数据库，支持FreeBSD、Linux、MAC、Windows等多种操作系统。MySQL数据库可支持要求最苛刻的Web、电子商务和联机事务处理（OLTP）应用程序。它是一个全面集成、事务安全、符合ACID的数据库，具备全面的提交、回滚、崩溃恢复和行级锁定功能。MySQL凭借其易用性、扩展力和性能，成为全球最受欢迎的开源数据库。
MySQL被设计为一个单进程多线程架构的数据库（通过ps -Lf mysqld pid或pstack mysqld pid命令可以查看多线程结构），这点与SQL Server类似，但与Oracle多进程的架构不同（注意Oracle的Windows版本也是单进程多线程架构）。简而言之，MySQL数据库实例在系统上的表现就是一个进程。MySQL主要有以下优点：
• 可以处理拥有上千万条记录的大型数据。
• 支持常见的SQL语句规范。
• 可移植性高，安装简单小巧。
• 良好的运行环境，有丰富信息的网络支持。
• 调试、管理，优化简单（相对其他大型数据库）。
• 复制全局事务标识可支持自我修复式集群。
• 复制无崩溃从机可提高可用性。
• 复制多线程从机可提交性能。
• 对InnoDB进行NoSQL访问，可快速完成键值操作以及快速提取数据来完成大数据部署。
• 在Linux上的性能提升高达230%。
• 在当今的多核、多CPU硬件上具备更高的扩展力。
• InnoDB性能改进，可更加高效地处理事务和只读负载。
• 更快速地执行查询命令，具备增强的诊断功能。
• Performance Schema可监视各个用户和应用程序的资源占用情况。
• 通过基于策略的密码管理和实施来确保安全性。
• 复制功能支持灵活的拓扑架构，可实现向外扩展和高可用性。
• 分区有助于提高性能和管理超大型数据库环境。
• ACID事务支持构建安全可靠的关键业务应用程序。
• Information Schema有助于方便地访问元数据。
• 插入式存储引擎架构可最大限度发挥灵活性。

2. MySQL服务的运行

本文后面的例子均是在Ubuntu环境下，需要在Ubuntu下安装MySQL的可以参考这篇博客。安装完成之后，通过service mysql status命令来看下MySQL的运行状态，如果是未启动则可以通过service mysql start命令来进行启动，若要停止服务则使用service mysql stop。

root@jack-linux:~# service mysql status
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: inactive (dead) since Sat 2020-11-14 15:02:01 CST; 5s ago
 Main PID: 3993 (code=exited, status=0/SUCCESS)

Nov 14 11:19:40 jack-linux systemd[1]: Starting MySQL Community Server...
Nov 14 11:19:40 jack-linux systemd[1]: Started MySQL Community Server.
Nov 14 15:02:00 jack-linux systemd[1]: Stopping MySQL Community Server...
Nov 14 15:02:01 jack-linux systemd[1]: Stopped MySQL Community Server.
root@jack-linux:~# service mysql start
root@jack-linux:~# service mysql status
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: active (running) since Sat 2020-11-14 15:02:15 CST; 2s ago
  Process: 6982 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid (code=exited, status=0/SUCCESS)
  Process: 6960 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 6984 (mysqld)
    Tasks: 27 (limit: 2318)
   CGroup: /system.slice/mysql.service
           └─6984 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid

Nov 14 15:02:15 jack-linux systemd[1]: Starting MySQL Community Server...
Nov 14 15:02:15 jack-linux systemd[1]: Started MySQL Community Server.

除上述命令外，还可以通过ps -ef | grep mysql或netstat -nlp | grep mysql命令查看MySQL服务的状态。

root@jack-linux:~# ps -ef | grep mysql
mysql     6984     1  0 15:02 ?        00:00:00 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
root      7042  6866  0 15:05 pts/0    00:00:00 grep --color=auto mysql
root@jack-linux:~# netstat -nlp | grep mysql
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      6984/mysqld
unix  2      [ ACC ]     STREAM     LISTENING     3740175  6984/mysqld          /var/run/mysqld/mysqld.sock

3. 创建和删除Database

在创建数据库之前，需要登录，这里以root用户登录为例：执行mysql -u root -p命令后输入设置的密码即可。然后使用SHOW DATABASES;命令查看MySQL中已有的数据库。

root@jack-linux:~# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.32-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>

创建数据库使用CREATE DATABASE DB_NAME命令，删除数据库使用DROP DATABASE DB_NAME。

mysql> CREATE DATABASE TEST;
Query OK, 1 row affected (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| TEST               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> DROP DATABASE TEST;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>

如果创建数据库时要指定字符集，则创建时加上CHARACTER SET关键字即可。然后可以通过SHOW CREATE DATABASE DB_NAME;命令查看数据库的字符集信息。

mysql> CREATE DATABASE TEST CHARACTER SET UTF8;
Query OK, 1 row affected (0.00 sec)

mysql> SHOW CREATE DATABASE TEST;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| TEST     | CREATE DATABASE `TEST` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

4. 创建和删除Table

创建表使用CREATE TABLE TB_NAME命令，删除表使用DROP TABLE TB_NAME。

mysql> USE TEST;
Database changed
mysql> CREATE TABLE TB_TEST(ID INT NOT NULL, NAME VARCHAR(10) NOT NULL);
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW TABLES;
+----------------+
| Tables_in_TEST |
+----------------+
| TB_TEST        |
+----------------+
1 row in set (0.00 sec)

mysql> DROP TABLE TB_TEST;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW TABLES;
Empty set (0.00 sec)

mysql>

同样在创建表时可以指定字符集，加上DEFAULT CHARSET即可。然后可以使用SHOW CREATE TABLE TB_NAME;命令查看表的字符集信息。

mysql> CREATE TABLE TB_TEST(ID INT NOT NULL, NAME VARCHAR(10) NOT NULL) DEFAULT CHARSET=UTF8;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW CREATE TABLE TB_TEST;
+---------+----------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                         |
+---------+----------------------------------------------------------------------------------------------------------------------+
| TB_TEST | CREATE TABLE `TB_TEST` (
  `ID` int(11) NOT NULL,
  `NAME` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

5. 查看Table的列名和类型

查看表的列名和类型使用DESC TB_NAME;命令，或者使用SHOW FULL COLUMNS FROM TB_NAME；命令，而且后面个命令能够查看的列名信息更全。

mysql> DESC TB_TEST;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID    | int(11)     | NO   |     | NULL    |       |
| NAME  | varchar(10) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> SHOW FULL COLUMNS FROM TB_TEST;
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type        | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| ID    | int(11)     | NULL            | NO   |     | NULL    |       | select,insert,update,references |         |
| NAME  | varchar(10) | utf8_general_ci | NO   |     | NULL    |       | select,insert,update,references |         |
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
2 rows in set (0.00 sec)

mysql>

其中各个字段的意义：
• Field：字段的名称。
• Type：字段的类型。
• Collation：描述了如何对查询出来的数据进行比较和排序。
• Null：是否允许为空，YES表示允许，NO表示不允许。
• Key：键，表示该列是否有索引，比如：主键(PRI)、唯一键（UNI）、非唯一键或多列唯一键（MUL）等。优先级：PRI > UNI > MUL。
• Default：字段的默认值。
• Extra：附加信息，如自增主键上的（auto_increment）。
• Privileges：权限，有select、insert、update、reference等。
• Comment：字段的注释。

6. 查看创建的索引及索引类型

在查看索引之前，我们先创建一个学生信息表，列名包括学号、姓名、性别、年龄、班级。如果我们要查看索引的具体信息，使用SHOW INDEX FROM TB_NAME;或者SHOW KEYS FROM TB_NAME;命令。

mysql> CREATE TABLE STUDENT(
    ->     ID  INT  NOT NULL AUTO_INCREMENT,
    ->     NAME  VARCHAR(10) NOT NULL,
    ->     GENDER  VARCHAR(4) NOT NULL,
    ->     AGE  INT,
    ->     CLASS VARCHAR(10),
    ->     PRIMARY KEY(ID),
    ->     KEY(NAME)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> DESC STUDENT;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| ID     | int(11)     | NO   | PRI | NULL    | auto_increment |
| NAME   | varchar(10) | NO   | MUL | NULL    |                |
| GENDER | varchar(4)  | NO   |     | NULL    |                |
| AGE    | int(11)     | YES  |     | NULL    |                |
| CLASS  | varchar(10) | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> SHOW INDEX FROM STUDENT;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| STUDENT |          0 | PRIMARY  |            1 | ID          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| STUDENT |          1 | NAME     |            1 | NAME        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> SHOW KEYS FROM STUDENT;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| STUDENT |          0 | PRIMARY  |            1 | ID          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| STUDENT |          1 | NAME     |            1 | NAME        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql>

7. 查看当前数据库中的用户

当前数据库中的用户信息会存储到名为mysql的数据库中的user表中，因此我们可以切换到mysql数据库中，通过SELECT命令查看user表中的用户信息。

mysql> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

mysql> select host, user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | debian-sys-maint |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
4 rows in set (0.00 sec)

mysql>

8. 查看数据库版本、当前用户和当前数据库

在SELECT操作后加上VERSION()函数可以查看数据库版本，使用USER()函数可以查询当前登录数据库的用户，使用DATABASE()函数可以获取当前连接的数据库名称。

mysql> SELECT VERSION(), USER(), DATABASE();
+-------------------------+----------------+------------+
| VERSION()               | USER()         | DATABASE() |
+-------------------------+----------------+------------+
| 5.7.32-0ubuntu0.18.04.1 | root@localhost | TEST       |
+-------------------------+----------------+------------+
1 row in set (0.00 sec)

mysql>

9. MySQL中的数据类型

MySQL中定义数据字段的类型对数据库的优化是非常重要的。MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串类型。

9.1 数值类型

数值类型又可以分为两类：整数类型和带小数的类型。
首先来看下整数类型，以及其大小、包括的数范围和用途。

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 Byte	(-2⁷, 2⁷-1)	(0, 2⁸-1)	小整数值、微小
SMALLINT	2 Byte	(-2¹⁵, 2¹⁵-1)	(0, 2¹⁶-1)	大整数值、小
MEDIUMINT	3 Byte	(-2²³, 2²³-1)	(0, 2²⁴-1)	大整数值、中等大小
INT或 INTEGER	4 Byte	(-2³¹, 2³¹-1)	(0, 2³²-1)	大整数值、普通大小
BIGINT	8 Byte	(-2⁶³, 2⁶³-1)	(0, 2⁶⁴-1)	极大整数值、大

然后，下面来看下带小数的类型，以及其大小、包括的数范围和用途。

类型	大小	范围（有符号）	范围（无符号）	用途
FLOAT	4 Byte	(-3.04028234663551E+38, 1.175494351E-38),0, (1.175494351E-38, 3.402823466351E+38)	0, (1.175494351E-38, 3.402823466351E+38)	单精度浮点数值
DOUBLE	8 Byte	(-1.7976931348623157E+308, 2.2250738585072014E-308), 0, (2.2250738585072014E-308, 1.7976931348623157E+308)	0, (2.2250738585072014 E-308,1.79769313 48623157E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D)，若M>D则为`M+2`，否则为`D+2`	依赖于M和D的值	依赖于M和D的值	小数值、定点数

9.2 日期/时间类型

表示时间值的日期/时间类型为：DATETIME、DATE、TIMESTAMP、TIME、YEAR。每个时间类型有一个有效值范围和一个“零”值，当指定不合法的MySQL不能表示的值。

类型	大小	范围	格式	用途及注意事项
DATE	3 Byte	1000-01-01 ~ 9999-12-31	YYYY-MM-DD	日期值
TIME	3 Byte	‘-838:59:59’ ~ ‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1 Byte	1901-2155	YYYY	年份值
DATETIME	5+小数位	1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.999999	YYYY-MM-DD HH:MM:SS	混合日期和时间值。DATATIME最大的小数位为6。若小数位为1或2，则总字节数为6(5+1)；若小数位为3或4，则总字节数为7(5+2)；若小数位为5或6，则总字节数为8(5+3)。
TIMESTAMP	8 Byte	1970-01-01 00:00:01.000000 ~ 2038-01-19 03:14:07.999999	YYYYMMDDHHMMSS	混合日期和时间值，时间戳。TIMESTAMP最大小数位是6。

使用日期/时间类型需要注意以下两点：
1）如果要记录年月日时分秒，并且记录的年份比较久远，那么最好使用DATETIME，因为其表示范围更广。
2）如果记录的日期需要让不同时区的用户使用，那么使用TIMESTAMP，因为只有它能够和实际的时区相对应。

9.3 字符串类型

字符串类型包括：CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM、SET。各自的大小及用途如下表所示。

类型	大小	用途
CHAR	0~2⁸-1	定长字符串
VARCHAR	0~2¹⁶-1	变长字符串
TINYBLOB	0~2⁸-1	二进制字符串
TNIYTEXT	0~2⁸-1	短文本字符串
BLOB	0~2¹⁶-1	二进制形式的长文本数据
TEXT	0~2¹⁶-1	长文本数据、VARCHAR的加长增强版
MEDIUMBOLB	0~2²⁴-1	二进制形式的中等长度文本数据
MEDIUMTEXT	0~2²⁴-1	中等长度文本数据
LONGBLOB	0~2³²-1	二进制形式的极大文本数据
LONGTEXT	0~2³²-1	极大文本数据
ENUM	1~2	枚举类型
SET	1~8	类似于枚举类型，但是SET类型一次可以选取多个成员，而ENUM只能选一个

字符串类型需要注意以下几点：
1）CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。当保存CHAR值，在它们的右边填充空格以达到指定的长度。当检索到CHAR值时，尾部空格的空格会被删除掉，而VARCHAR类型会保留尾部空格。
2）BINARY和VARBINARY类似与CHAR和VARCHAR，不同的是它们只包含二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值。
3）BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、MEDIUMBLOB、BLOB、LONGBLOB。它们的区别只是可容纳值的最大长度不同。
4）有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求。

mysql> CREATE TABLE TB_GENDER(gender enum('F', 'M', 'UN'));
Query OK, 0 rows affected (0.03 sec)

mysql> DESC TB_GENDER;
+--------+--------------------+------+-----+---------+-------+
| Field  | Type               | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| gender | enum('F','M','UN') | YES  |     | NULL    |       |
+--------+--------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> CREATE TABLE TB_GENDER2(col set('a', 'b', 'c'), gender enum('F', 'M', 'UN'));
Query OK, 0 rows affected (0.03 sec)

mysql> DESC TB_GENDER2;
+--------+--------------------+------+-----+---------+-------+
| Field  | Type               | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| col    | set('a','b','c')   | YES  |     | NULL    |       |
| gender | enum('F','M','UN') | YES  |     | NULL    |       |
+--------+--------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> INSERT INTO TB_GENDER2 VALUES('d', 'F');
ERROR 1265 (01000): Data truncated for column 'col' at row 1
mysql> INSERT INTO TB_GENDER2 VALUES('a', 'F');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO TB_GENDER2 VALUES('a,c', 'F');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO TB_GENDER2 VALUES('b,c', 'F');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM TB_GENDER2;
+------+--------+
| col  | gender |
+------+--------+
| a    | F      |
| a,c  | F      |
| b,c  | F      |
+------+--------+
3 rows in set (0.00 sec)

mysql>

10. MySQL中数据类型的属性

数据类型的属性包括：auto_increment、binary、default、index、null、not null、primary key、unique、zerofill，分别如下表所示：

属性	说明
auto_increment	auto_increment属性能为新插入的行赋予一个唯一的整数标识符，只能用于整数类型。每个表只允许有一个auto_increment列，MySQL要求auto_increment属性列作为主键，并且从1开始依次自动增长。自动增长列可以手工插入，但插入值是空或者0，那么实际插入的将是自动增长后的值。对于`TRUNCATE`操作，表中的auto_increment属性的值会被置为1，而`DELETE`操作不会置为1。
binary	binary属性只用于char和varchar值。当为列指定了该属性时，将以区分大小写的方式排序和比较。
default	default属性确保在没有任何值可用的情况下，赋予某个`常量值`。注意此属性无法用于BLOB和TEXT列，同时在指定null属性的列上，未指定默认值时默认值将为null。
index	为列指定index属性，则会在该列建立索引，以加速数据库的查询。索引一个列会为该列创建一个有序的键数组，每个键指向其相应的表行。以后针对输入条件可以搜索这个有序的键数组，与搜索整个未索引的表相比，性能将会有极大的提升。
null	为列指定null属性时，该列可以保持为空，注意null精确的说法是“无”，而不是空字符串或0,。
not null	如果将列定义为not null，那么将不允许向该列插入null值。通常会在重要列上添加此属性。
primary key	primary key属性用于确保指定行的唯一性。主键列中的值不能重复，也不能为空。主键列被赋予auto_increment属性是很常见的，因此此列不必与行数据有任何关系，只是作为唯一的标识符。
unique	被赋予unique属性的列将确保所有值都有不同的值，但是null值可以重复。一般会指定主键列为unique。
zerofill	zerofill属性可用于任何数值类型，用0填充所有剩余字段空间。比如，无符号int的默认宽度是10，因此当“零填充”的int值为4时，将表示它为0000000004。

下面针对上述的每种类型进行举例说明。
（1）auto_increment

mysql> CREATE TABLE TB_AUTO_INC(id smallint auto_increment primary key);
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW CREATE TABLE TB_AUTO_INC;
+-------------+------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                       |
+-------------+------------------------------------------------------------------------------------------------------------------------------------+
| TB_AUTO_INC | CREATE TABLE `TB_AUTO_INC` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> DESC TB_AUTO_INC;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | smallint(6) | NO   | PRI | NULL    | auto_increment |
+-------+-------------+------+-----+---------+----------------+
1 row in set (0.00 sec)

mysql> INSERT INTO TB_AUTO_INC VALUES (null), (5), (0);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM TB_AUTO_INC;
+----+
| id |
+----+
|  1 |
|  5 |
|  6 |
+----+
3 rows in set (0.00 sec)

mysql> SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO TB_AUTO_INC VALUES (null);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                7 |
+------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE TB_AUTO_INC1(
    -> id1 smallint not null auto_increment,
    -> id2 smallint not null,
    -> name char(20),
    -> index (id2, id1)
    -> ) ENGINE=MYISAM; # InnoDB表中要求auto_increment列必须设置为key

mysql> DESC TB_AUTO_INC1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id1   | smallint(6) | NO   |     | NULL    | auto_increment |
| id2   | smallint(6) | NO   | MUL | NULL    |                |
| name  | char(20)    | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO TB_AUTO_INC1 (id2, name)  VALUES (2, 'name2'), (3, 'name3'), (4, 'name4'), (2, 'name2'), (3, 'name3'), (4, 'name4');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM TB_AUTO_INC1;
+-----+-----+-------+
| id1 | id2 | name  |
+-----+-----+-------+
|   1 |   2 | name2 |
|   1 |   3 | name3 |
|   1 |   4 | name4 |
|   2 |   2 | name2 |
|   2 |   3 | name3 |
|   2 |   4 | name4 |
+-----+-----+-------+
6 rows in set (0.00 sec)

mysql>

给指定表添加主键列：

mysql> CREATE TABLE TB_AUTO_INC2(id smallint);
Query OK, 0 rows affected (0.05 sec)

mysql> DESC TB_AUTO_INC2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | smallint(6) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> ALTER TABLE TB_AUTO_INC2 ADD id0 int auto_increment primary key first;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC TB_AUTO_INC2;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id0   | int(11)     | NO   | PRI | NULL    | auto_increment |
| id    | smallint(6) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> INSERT INTO TB_AUTO_INC2 (id) VALUES (2), (5);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM TB_AUTO_INC2;
+-----+------+
| id0 | id   |
+-----+------+
|   1 |    2 |
|   2 |    5 |
+-----+------+
2 rows in set (0.00 sec)

mysql> INSERT INTO TB_AUTO_INC2 VALUES (11, 4), (0, 7);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM TB_AUTO_INC2;
+-----+------+
| id0 | id   |
+-----+------+
|   1 |    2 |
|   2 |    5 |
|  11 |    4 |
|  12 |    7 |
+-----+------+
4 rows in set (0.00 sec)

mysql>

修改某个列：

mysql> CREATE TABLE TB_AUTO_INC3(id smallint);
Query OK, 0 rows affected (0.02 sec)

mysql> DESC TB_AUTO_INC3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | smallint(6) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> ALTER TABLE TB_AUTO_INC3 MODIFY id int auto_increment primary key;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC TB_AUTO_INC3;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
+-------+---------+------+-----+---------+----------------+
1 row in set (0.00 sec)

mysql>

（2）binary

mysql> CREATE TABLE TB_BIN(
    -> id int auto_increment primary key,
    -> hostname char(25) binary not null
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> DESC TB_BIN;
+----------+----------+------+-----+---------+----------------+
| Field    | Type     | Null | Key | Default | Extra          |
+----------+----------+------+-----+---------+----------------+
| id       | int(11)  | NO   | PRI | NULL    | auto_increment |
| hostname | char(25) | NO   |     | NULL    |                |
+----------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> INSERT INTO TB_BIN (hostname) VALUES (123), ('zdsA'), ('4456');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM TB_BIN;
+----+----------+
| id | hostname |
+----+----------+
|  1 | 123      |
|  2 | zdsA     |
|  3 | 4456     |
+----+----------+
3 rows in set (0.00 sec)

mysql>

（3）default

mysql> CREATE TABLE TB_DEF(
    -> id int auto_increment primary key,
    -> name varchar(20) default 'test'
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> DESC TB_DEF;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | test    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql>

（4）index

mysql> CREATE TABLE PERSON(
    -> id int auto_increment primary key,
    -> name varchar(20) not null,
    -> age int not null default 0,
    -> phone varchar(20),
    -> index name(name)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> DESC PERSON;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   | MUL | NULL    |                |
| age   | int(11)     | NO   |     | 0       |                |
| phone | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> SHOW INDEX FROM PERSON;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| PERSON |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| PERSON |          1 | name     |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> CREATE INDEX phone_index ON PERSON (phone);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM PERSON;
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| PERSON |          0 | PRIMARY     |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| PERSON |          1 | name        |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| PERSON |          1 | phone_index |            1 | phone       | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql>

（5）zerofill

mysql> CREATE TABLE TB_ZERO_FILL(
    -> col1 int,
    -> col2 int(3),
    -> col3 int zerofill,
    -> col4 int(3) zerofill
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> DESC TB_ZERO_FILL;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type                      | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| col1  | int(11)                   | YES  |     | NULL    |       |
| col2  | int(3)                    | YES  |     | NULL    |       |
| col3  | int(10) unsigned zerofill | YES  |     | NULL    |       |
| col4  | int(3) unsigned zerofill  | YES  |     | NULL    |       |
+-------+---------------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> SELECT * FROM TB_ZERO_FILL;
+------+------+------------+------+
| col1 | col2 | col3       | col4 |
+------+------+------------+------+
|    1 |    1 | 0000000001 |  001 |
+------+------+------------+------+
1 row in set (0.00 sec)

mysql> INSERT INTO TB_ZERO_FILL VALUES (2, 2, 22, 222);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM TB_ZERO_FILL;
+------+------+------------+------+
| col1 | col2 | col3       | col4 |
+------+------+------------+------+
|    1 |    1 | 0000000001 |  001 |
|    2 |    2 | 0000000022 |  222 |
+------+------+------------+------+
2 rows in set (0.00 sec)

mysql>

11. MySQL中的日期和时间函数

MySQL的日期函数有很多，常用的日期或事件函数如下表所示：

函数	功能描述	示例
DAYOFWEEK (DATE)	返回DATE的星期索引（1=Sunday，···，7=Saturday）
DAYOFYEAR (DATE)	返回DATE是一年中的第几天，范围是1~366
HOUR(TIME)/ MINUTE(TIME)/ SECOND(TIME)	返回TIME的小时值/ 分钟值/秒值
DATE_FORMAT (DATE, FORMAT)	依照FORMAT字符串格式化DATE值，修饰符的含义： %M：月的名字（January…December） %W：星期的名字（Sunday…Saturday） %D：有英文后缀的某月的第几天（1st,2nd,…） %Y：4位数字的年份 %y：2位数字的年份 %m：2位数字的月份（00…12） %c：不含前缀0的月份（0…12） %d：2位数字的月份中的天数（00…31） %e：不含前缀0的月份中的天数（0…31） %x：4位数字的年份，其中星期一是星期的第一天，与“%v”一同使用 %a：缩写的星期名（Sun…Sat） %b：缩写的月名（Jan…Dec） %H：2位数字的 24小时（00…23） %k：不含前缀0的 24小时（0…23） %h：2位数字的 12小时（01…12） %I：2位数字的 12小时（01…12） %l：不含前缀0的 12小时（1…12） %i：2位数字的分钟（00…59） %r：12小时的时间格式（hh:mm:ss [AP]M） %T：24小时的时间格式（hh:mm:ss） %S：2位数字的秒（00…59） %s：2位数字的秒（00…59） %p：AM或PM %w：一周中的天数（0=Sunday，···，6=Saturday）
STR_TO_DATE()	将字符串转换为日期类型
CURDATE()/ CURRENT_DATE	以“YYYY-MM-DD”或 “YYYYMMDD”格式返回当前的日期值
CURTIME()/ CURRENT_TIME	以“HH:MM:SS”或 “HHMMSS”格式返回当前的时间值
NOW()/ SYSDATE()/ CURRENT_TIMESTAMP	以“YYYY-MM-DD HH:MM:SS”或 “YYYYMMDDHHMMSS” 格式返回当前的日期和时间值
SEC_TO_TIME (NUMBER)	以“HH:MM:SS”或 “HHMMSS”格式返回整形参数被转换后的时分秒值
TIME_TO_SEC (TIME)	返回参数TIME被转换为秒数后的值

12. MySQL中LIMIT的作用

limit限制返回的结果函数，主要用于查询之后要显示返回的前几条或中间某几行数据，其写法如下所示：
LIMIT 0,100; # 从起始角标为0的位置，往后获取100条记录，可简写为LIMIT 100;。
LIMIT 10,8; # 从起始角标为10的位置，往后获取8条记录。

mysql> DESC TB_PERSON;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  |     | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM TB_PERSON;
+----------+
| COUNT(*) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM TB_PERSON LIMIT 6;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | test1 |   10 |
|  2 | test2 |   20 |
|  3 | test3 |   30 |
|  4 | test4 |   40 |
|  5 | test5 |   50 |
|  6 | test6 |   60 |
+----+-------+------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM TB_PERSON LIMIT 3,6;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  4 | test4 |   40 |
|  5 | test5 |   50 |
|  6 | test6 |   60 |
|  7 | test7 |   70 |
|  8 | test8 |   80 |
|  9 | test9 |   90 |
+----+-------+------+
6 rows in set (0.00 sec)

mysql>

limit还可以用于分页操作，但是在数据量和偏移量（offset）比较大时，会变得很低效。所以对limit的优化，要么限制分页的数量，要么降低偏移量的大小。
MySQL中"limit m,n"的工作原理是先读取前m条记录，然后抛弃前m条，再读取n条想要的记录。所以m越大，性能会越差。优化思路是，在索引（主键）上完成排序分页的操作，最后根据主键关联回原表查询所需要的其他列内容。示例如下：

# 待优化的SQL
SELECT * FROM TB_PERSON ORDER BY NAME LIMIT 100, 20;
# 优化后的SQL
SELECT * FROM TB_PERSON INNER JOIN (
    SELECT id FROM TB_PERSON ORDER BY NAME LIMIT 100, 20
);

二者区别在于，优化前的SQL需要更多的I/O浪费，因为先读索引，再读数据，然后抛弃无用的行，而优化后的SQL只读索引就可以了，然后通过主键读取需要的列。

13. MySQL中对一张表同时查询和更新

MySQL不允许对同一张表同时进行查询和更新，但可以使用临时表的方式来处理。如下的SQL语句不能正常执行：

# 原始的数据
mysql> SELECT * FROM TB_PERSON;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | test1  |   10 |
|  2 | test2  |   20 |
|  3 | test3  |   30 |
|  4 | test4  |   40 |
|  5 | test5  |   50 |
|  6 | test6  |   60 |
|  7 | test7  |   70 |
|  8 | test8  |   80 |
|  9 | test9  |   90 |
| 10 | test10 |  100 |
+----+--------+------+
10 rows in set (0.00 sec)

# 目的：将表中所有的name都变为大写
mysql> UPDATE TB_PERSON p
    -> SET p.name = (SELECT UPPER(q.name) FROM TB_PERSON q WHERE q.age=p.age);
ERROR 1093 (HY000): You can''t specify target table 'p' for update in FROM clause

# 使用临时表
mysql> UPDATE TB_PERSON p INNER JOIN (SELECT UPPER(name) name, age FROM TB_PERSON) q ON q.age=p.age SET p.name = q.name;
Query OK, 10 rows affected (0.00 sec)
Rows matched: 10  Changed: 10  Warnings: 0

mysql> SELECT * FROM TB_PERSON;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | TEST1  |   10 |
|  2 | TEST2  |   20 |
|  3 | TEST3  |   30 |
|  4 | TEST4  |   40 |
|  5 | TEST5  |   50 |
|  6 | TEST6  |   60 |
|  7 | TEST7  |   70 |
|  8 | TEST8  |   80 |
|  9 | TEST9  |   90 |
| 10 | TEST10 |  100 |
+----+--------+------+
10 rows in set (0.00 sec)

mysql>

14. MySQL中在表的指定位置添加列

如果在一个已经建好的表中添加一列，使用ALTER关键字，用下面的语句实现：

1	ALTER TABLE TB_NAME ADD COLUMN NEW_COL_NAME varchar(20) not null;

这条语句会向已有的表中的最后添加新的一列，如果希望添加到指定列的后面，可以使用AFTER关键字，用下面的语句实现：

1	ALTER TABLE TB_NAME ADD COLUMN NEW_COL_NAME varchar(20) not null AFTER TARGET_COL_NAME;

如果想把新列添加到第一列，可以使用FIRST关键字，用下面的语句实现：

1	ALTER TABLE TB_NAME ADD COLUMN NEW_COL_NAME varchar(20) not null FIRST;

可以使用SQL语句ALTER TABLE TB_NAME ADD new_id int auto_increment primary key FIRST;来添加主键，使用SQL语句ALTER TABLE TB_NAME MODIFY id int auto_increment primary key;来修改主键列。

15. MySQL中LENGTH和CHAR_LENGTH的区别

LENGTH和CHAR_LENGTH是MySQL中获取字符串长度的两个函数。LENGTH函数是计算字段的长度，单位为字节，1个汉字算3个字节，1个数字或字母算1个字节。CHAR_LENGTH(str)返回的是字符串str的长度，单位为字符。对于CHAR_LENGTH函数而言，一个多字节字符算作一个单字符。
对于Latin1编码字符，这两个函数的返回结果是相同的，但是对于Unicode和其他编码来说，它们的返回结果是不同的。例如，对于一个包含5个2字节字符集的字符串来说，LENGTH()的返回值为10，而CHAR_LENGTH()的返回值为5。

mysql> SELECT LENGTH(A), LENGTH(B), CHAR_LENGTH(A), CHAR_LENGTH(B) FROM (SELECT _UTF8'你好' A, _GBK'Hi' B) T;
+-----------+-----------+----------------+----------------+
| LENGTH(A) | LENGTH(B) | CHAR_LENGTH(A) | CHAR_LENGTH(B) |
+-----------+-----------+----------------+----------------+
|         6 |         2 |              2 |              2 |
+-----------+-----------+----------------+----------------+
1 row in set (0.00 sec)

mysql>

16. 函数FROM_UNIXTIME和UNIX_TIMESTAMP的作用

函数FROM_UNIXTIME将MySQL中用10位数字存储的时间以日期格式来显示。语法：FROM_UNIXTIME(unix_timestamp, format)，例如：

mysql> SELECT FROM_UNIXTIME(987654321, '%Y/%m/%d %H:%i:%s');
+-----------------------------------------------+
| FROM_UNIXTIME(987654321, '%Y/%m/%d %H:%i:%s') |
+-----------------------------------------------+
| 2001/04/19 12:25:21                           |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT FROM_UNIXTIME(987654321);
+--------------------------+
| FROM_UNIXTIME(987654321) |
+--------------------------+
| 2001-04-19 12:25:21      |
+--------------------------+
1 row in set (0.00 sec)

mysql>

函数UNIX_TIMESTAMP返回指定时间的UNIX格式数字串，即UNIX时间戳（从UTC时间’1970-01-01 00:00:00’开始的秒数），通常为10位数字。语法：UNIX_TIMESTAMP(date)，参数date可以是DATE字符串、DATETIME字符串、TIMESTAMP字符串，或是类似于YYMMDD或YYYYMMDD的数字串。
注意：如果传递1个超出范围的时间，那么UNIX_TIMESTAMP()的返回值是0；如果传入的date是空，那么将返回从UTC时间’1970-01-01 00:00:00’开始到当前时间的秒数。

mysql> SELECT UNIX_TIMESTAMP('2020-08-15');
+------------------------------+
| UNIX_TIMESTAMP('2020-08-15') |
+------------------------------+
|                   1597420800 |
+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP('2020-08-15 10:24:05');
+---------------------------------------+
| UNIX_TIMESTAMP('2020-08-15 10:24:05') |
+---------------------------------------+
|                            1597458245 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP('1234567890');
+------------------------------+
| UNIX_TIMESTAMP('1234567890') |
+------------------------------+
|                     0.000000 |
+------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP();
+------------------+
| UNIX_TIMESTAMP() |
+------------------+
|       1598976033 |
+------------------+
1 row in set (0.00 sec)

mysql>

...

00:00

数据库基本理论知识总结——MySQL基础篇