MySQL数据库（2）- 库的操作、表的操作、数据的操作、存储引擎的介绍
一、库的操作
1、系统数据库
执行如下命令，查看系统数据库：
mysql> show databases;
参数解释：
information_schema： 虚拟库，不占用磁盘空间，存储的是数据库启动后的一些参数，如用户表信息、列信息、权限信息、字符信息等；
performance_schema： MySQL 5.5开始新增一个数据库：主要用于收集数据库服务器性能参数，记录处理查询请求时发生的各种事件、锁等现象；
mysql： 授权库，主要存储系统用户的权限信息；
test： MySQL数据库系统自动创建的测试数据库；
2、库的相关操作命令
1）求救（创建数据库的官方）语法，命令如下：
mysql> help create database;
2）创建数据库的语法如下：
mysql> CREATE DATABASE db_name charset utf8;
3）查看所有数据库，命令如下：
mysql> show databases;
4）查看当前库（结果包含库名和编码方式），命令如下：
mysql> show create database db1;
5）查看所在的库（结果只包含库名），命令如下：
mysql> select database();
6）选择数据库名，命令如下：
mysql> use 数据库名;
7）删除数据库，命令如下：
mysql> drop database 数据库名;
8）修改数据库，命令如下：
mysql> alter database db1 charset utf8; # 将数据库的字符编码改为utf8
3、数据库的命名规则
a、可以由字母、数字、下划线、@、#、$组成；
b、区分大小写；
c、唯一性；
d、不能使用关键字，如：create、select；
e、不能单独使用数字；
f、最长128位；
PS：基本上跟python或者js的命名规则一样。
4、SQL语言类型（了解）
SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统，SQL语言由IBM开发。SQL语言分为3种类型：
1）DDL语句 数据库定义语言： 数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER；
2）DML语句 数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT；
3）DCL语句 数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE；
二、表的操作
表相当于文件，表中的一条记录就相当于文件的一行内容，不同的是，表中的一条记录有对应的标题，称为表的字段。
1、创建表，语法如下：
create table 表名(
字段名1：类型[(宽度) 约束条件],
字段名2：类型[(宽度) 约束条件],
字段名3：类型[(宽度) 约束条件]
);
注意：
1）在同一张表中，字段名不能相同；
2）宽度和约束条件可选；
3）字段名和类型是必须的；
4）一定在要库中创建表，即先use db_name;
示例：
create table a1(
id int,
name varchar(50),
age int(3)
);
2、插入表的记录，命令如下：
insert into a1 values
(1,’alex’,18),
(2,’wusir’,20);
3、查询表的数据和结构
a、查询表中存储的数据，命令和结果如下：
mysql> select * from a1; +------+-------+------+ | id | name | age | +------+-------+------+ | 1 | alex | 18 | | 2 | wusir | 20 | +------+-------+------+ 2 rows in set (0.02 sec)
b、查看a1表的结构，命令和结果如下：
mysql> desc a1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | age | int(3) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
c、查看表的详细结构，命令和结果如下：
mysql> show create table a1\G; *************************** 1. row *************************** Table: a1 Create Table: CREATE TABLE `a1` ( `id` int(11) DEFAULT NULL, `name` varchar(50) DEFAULT NULL, `age` int(3) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
4、复制表
a、既复制表结构，又复制记录，如下命令：
mysql> create database db3 charset utf8; # 新建一个数据库db3 mysql> use db3; # 使用数据库db3 mysql> select * from db2.a1; # 查看db2中表a1的数据 mysql> create table b1 select * from db2.a1; # 这就是复制表的操作 mysql> select * from db3.b1; # 查看结果，跟a1表数据一样
b、只复制表结构，不要记录，如下命令：
# 在db2数据库下创建一个a2表，给一个where条件，条件要求不成立，则只复制了表结构 mysql> use db2; mysql> create table a2 select * from a1 where 1>5; # 这是复制表结构的操作 mysql> desc a2; # 查看表结构，跟a1表一样 mysql> select * from a2; # 查看a2表结构中的数据，发现是空数据
c、使用like，只复制表结构，不要记录，如下命令：
mysql> use db2; mysql> create table a3 like a1; # 这是复制表结构的操作 mysql> desc a3; mysql> select * from a3;
5、删除表，命令如下：
drop table 表名;
三、数据的操作
现在，我们已经对插入数据、更新数据、删除数据有了全面的认识，那么，在MySQL中其实最重要的不是这三大操作，而是查数据（下篇介绍）。
在MySQL管理软件中，可以通过SQL语句中的DML语言来实现数据的操作，包括：
1）使用INSERT实现数据的插入；
2）使用DELETE实现数据的删除；
3）UPDATE实现数据的更新；
4）使用SELECT查询数据以及。；
1、插入数据
a、插入完整数据（顺序插入）
# 语法一： insert into 表名(字段1,字段2,字段3,…,字段n) values (值1,值2,值3,…,值n); # 语法二： insert into 表名 values (值1,值2,值3,…,值n);
b、指定字段插入数据，语法如下：
insert into 表名(字段1,字段2,字段3,…) values (值1,值2,值3,…);
c、插入多条记录，语法如下：
insert into 表名 values (值1,值2,值3,…,值n), (值1,值2,值3,…,值n), (值1,值2,值3,…,值n);
d、插入查询结果，语法如下：
insert into 表名(字段1,字段2,字段3,…,字段n) select (字段1,字段2,字段3,…,字段n) from 表2 where condition;
2、删除数据
语法：
delete from 表名
where condition;
3、更新数据
语法：
update 表名 set
字段1=值1,
字段2=值2,
where condition;
四、数据类型
存储引擎决定了表的类型，而表内存放的数据也有不同的类型，且每种数据类型都有自己的宽度，但宽度是可选参数。
MySQL的数据类型大致可以分为：数值、日期/时间、字符串(字符)、枚举、集合类型。
1、数值类型（整数和浮点数）
|
类型 |
范围（有符号） |
无符号 |
|
TINYINT |
(-128 , 127) |
(0，255) |
|
SMALLINT |
(-32 768，32 767) |
(0，65 535) |
|
MEDIUMINT |
(-8 388 608，8 388 607) |
(0，16 777 215) |
|
INT |
(-2 147 483 648，2 147 483 647) |
(0，4 294 967 295) |
|
BIGINT |
(-9 233 372 036 854 775 808，9 223 372 036 854 775 807) |
(0，18 446 744 073 709 551 615) |
|
FLOAT |
(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) |
0，(1.175 494 351 E-38，3.402 823 466 E+38) |
|
DOUBLE |
(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) |
0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) |
|
DECIMAL |
依赖于M和D的值 |
依赖于M和D的值 |
a、整数（TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT）
作用：存储年龄，等级，id，各种号码等。
示例一：验证默认有符号类型（以tinyint为例）
mysql> create database db4 charset utf8; mysql> use db4; mysql> create table t1(x tinyint); mysql> insert into t1 values(-1); # 向表t1中成功插入-1，证明默认有符号类型 mysql> select * from t1; +-------+ | x | +-------+ | -1 | +-------+ 1 row in set (0.00 sec) mysql> insert into t1 values(-128),(127); # 成功插入，证明包含范围两端的值 mysql> insert into t1 values(-129); # 超出范围，报错 mysql> insert into t1 values(128); # 超出范围，报错
示例二：创建表时如何定义数值为无符号类型（以tinyint为例）
mysql> create table t2(x tinyint unsigned); # 使用unsigned定义为无符号类型 mysql> insert into t2 values(-1); # 插入失败，说明定义成功 mysql> insert into t2 values(255); # 插入成功，说明定义成功
示例三：整数类型后面的参数是显示宽度，而不是存储宽度（以int为例）
mysql> create table t3(id int(1) unsigned); mysql> insert into t3 values(255555); # 插入255555也是可以的 mysql> select * from t3; mysql> select * from t3; +--------+ | id | +--------+ | 255555 | +--------+ 1 row in set (0.00 sec) # 以上操作还不能够完全验证，再建一个表验证 mysql> create table t4(id int(5) unsigned zerofill); # zerofill用0填充 mysql> insert into t4 values(1); mysql> select * from t4; # 插入的记录是1，但是显示的是00001，宽度为5 mysql> select * from t4; +-------+ | id | +-------+ | 00001 | +-------+ 1 row in set (0.00 sec)
注意：为整数类型指定宽度时，仅仅只是指定查询结果的显示宽度，与存储范围无关，存储范围参见上面表格。其实我们完全没必要为整数类型指定显示宽度，使用默认的就可以了。默认的显示宽度，都是在最大值的基础上加1。
补充：MySQL中无布尔类型，使用tinyint(1)构造（可以用boolean关键字创建，但是desc查看表结构则Type显示为tinyint(1)）。
b、浮点数（FLOAT、DOUBLE、DECIMAL）
作用：存储薪资，身高，体重，体质参数等。
示例一：FLOAT[(M,D)] [unsigned] [zerofill]
# 单精度浮点数（非准确小数值）：M是全长，D是小数点后个数。M最大值255，D最大值30 mysql> create table t5(x float(255,30)); # 插入一个数据x，小数点后31个1 mysql> insert into t5 values(1.1111111111111111111111111111111); mysql> select * from t5; # 随着小数的增多，精度开始不准确 +----------------------------------+ | x | +----------------------------------+ | 1.111111164093017600000000000000 | # 小数点后显示30位 +----------------------------------+
示例二：DOUBLE[(M,D)] [unsigned] [zerofill]
# 双精度浮点数（非准确小数值）：M是全长，D是小数点后个数。M最大值255，D最大值30 mysql> create table t6(x double(255,30)); # 插入一个数据x，小数点后31个1 mysql> insert into t6 values(1.1111111111111111111111111111111); mysql> select * from t6; # 精度比float要准确点，但随着小数增多，同样变得不准确 +----------------------------------+ | x | +----------------------------------+ | 1.111111111111111200000000000000 | # 小数点后显示30位 +----------------------------------+ 1 row in set (0.00 sec)
示例三：DECIMAL[(M,D)] [unsigned] [zerofill]
# 准确的小数值，M是整数部分总个数（负号不算），D是小数点后个数。M最大65，D最大30 mysql> create table t7(x decimal(65,30)); # # 插入一个数据x，小数点后31个1 mysql> insert into t7 values(1.1111111111111111111111111111111); mysql> select * from t7; # 精度始终准确 +----------------------------------+ | x | +----------------------------------+ | 1.111111111111111111111111111111 | # 这里D为30，于是只显示小数点后30位 +----------------------------------+ 1 row in set (0.00 sec)
2、日期/时间类型（DATE、TIME、YEAR、DATETIME、TIMESTAMP）
作用：存储用户注册时间，文章发布时间，员工入职时间，出生时间，过期时间等。
|
类型 |
格式 |
范围 |
|
DATE |
YYYY-MM-DD |
1000-01-01/9999-12-31 |
|
TIME |
HH:MM:SS |
'-838:59:59'/'838:59:59' |
|
YEAR |
YYYY |
1901/2155 |
|
DATETIME |
YYYY-MM-DD HH:MM:SS |
1000-01-01 00:00:00/9999-12-31 23:59:59 |
|
TIMESTAMP |
YYYYMMDD HHMMSS |
1970-01-01 00:00:00/2038（第2147483647 秒） |
示例如下：
a、YEAR
mysql> create table t8(born_year year); # 无论指定何种宽度，都默认是year(4) mysql> insert into t8 values(1905),(2018); # 插入记录成功 mysql> insert into t8 values(1900); # 插入失败，超出范围
b、DATE、TIME、DATETIME
mysql> create table t9(d date,t time,dt datetime); # 调用MySQL自带的now()函数，获取当前类型指定的事件，如下 mysql> insert into t9 values(now(),now(),now()); mysql> select * from t9; mysql> select * from t9; +------------+----------+---------------------+ | d | t | dt | +------------+----------+---------------------+ | 2018-10-15 | 21:20:13 | 2018-10-15 21:20:13 | +------------+----------+---------------------+ 1 row in set (0.00 sec)
c、TIMESTAMP（了解即可）
mysql> create table t10(ts timestamp); mysql> insert into t10 values(); mysql> insert into t10 values(null); mysql> insert into t10 values(now()); mysql> select * from t10; mysql> select * from t10 +---------------------+ | ts | +---------------------+ | 2018-10-15 21:24:51 | | 2018-10-15 21:24:57 | | 2018-10-15 21:25:05 | +---------------------+ 3 rows in set (0.00 sec)
d、datetime与timestamp的区别
在实际应用的很多场景中，MySQL的这两种日期类型都能够满足我们的需要，存储精度都为秒，但在某些情况下，会展现出他们各自的优劣。
下面就来总结一下两种日期类型的区别：
1）DATETIME的日期范围是1001——9999年，TIMESTAMP的时间范围是1970——2038年。
2）DATETIME存储时间与时区无关，TIMESTAMP存储时间与时区有关，显示的值也依赖于时区。在mysql服务器，操作系统以及客户端连接都有时区的设置。
3）DATETIME使用8字节的存储空间，TIMESTAMP的存储空间为4字节。因此，TIMESTAMP比DATETIME的空间利用率更高。
4）DATETIME的默认值为null；TIMESTAMP的字段默认不为空（not null）,默认值为当前时间（CURRENT_TIMESTAMP），如果不做特殊处理，并且update语句中没有指定该列的更新值，则默认更新为当前时间。
3、字符串（字符）类型（CHAR，VARCHAR，TEXT）
介绍字符类型之前先了解如下两个MySQL的内置函数：
char_length() # 查看字符数
length() # 查看字节数
示例一：char填充空格来满足固定长度，但是在查询时却会很不要脸地删除尾部的空格（装作自己好像没有浪费过空间一样），然后修改sql_mode让其现出原形。
# 创建t11表，分别指明字段x为char类型，y为varchar类型 mysql> create table t11(x char(5),y varchar(4)); # x，y都存放3个字加一个空格 mysql> insert into t11 values(‘你瞅啥 ’,’你瞅啥 ’); # 在检索时，char类型很不要脸地将自己浪费的2个字符给删掉了，装的好像没有浪费过空间一样 mysql> select x,char_length(x),y,char_length(y) from t11; +-----------+----------------+------------+----------------+ | x | char_length(x) | y | char_length(y) | +-----------+----------------+------------+----------------+ | 你瞅啥 | 3 | 你瞅啥 | 4 | +-----------+----------------+------------+----------------+ 1 row in set (0.02 sec) # 设置sql_mode，让char现原形 mysql> set sql_mode = ‘PAD_CHAR_TO_FULL_LENGTH’; # 查看当前sql_mode模式 mysql> select @@sql_mode; # 再次检索，char现出原形，即占用了5个字符空间 mysql> select x,char_length(x),y,char_length(y) from t11; +-------------+----------------+------------+----------------+ | x | char_length(x) | y | char_length(y) | +-------------+----------------+------------+----------------+ | 你瞅啥 | 5 | 你瞅啥 | 4 | +-------------+----------------+------------+----------------+ 1 row in set (0.00 sec) # 查看字节数（utf8的一个中文是3个字节，空格是2个字节） mysql> select x,length(x),y,length(y) from t11; +-------------+-----------+------------+-----------+ | x | length(x) | y | length(y) | +-------------+-----------+------------+-----------+ | 你瞅啥 | 11 | 你瞅啥 | 10 | +-------------+-----------+------------+-----------+ 1 row in set (0.00 sec)
总结：
1）虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%，因此，用户在设计数据库时应当综合考虑各个方面的因素，以求达到最佳的平衡。
2）text数据类型用于保存变长的大字符串，可以多到65535（2**16-1）个字符。
4、枚举类型和集合类型（enum、set）
作用：字段的值只能在给定的范围中选择，如，单选框，多选框。
enum：枚举 - 单选，只能在给定的范围内选一个值，如，性别（gender）= 男（male）/女（female）；
set：集合 – 多选，在给定的范围内可以选择一个或多个以上的值，如，爱好=（爱好1，爱好2，…）；
示例：
mysql> create table consumer( -> id int, -> name varchar(50), -> gender enum(‘male’,’female’), -> level enum(‘vip1’,‘vip2’,’vip3’,’vip4’), # 在指定范围内多选一 -> fav set(‘play’,’music’,’read’,’study’) # 在指定范围内多选多 -> ); Query OK, 0 rows affected (0.03 sec) mysql> insert into consumer values -> (1,'赵云','male','vip2','read,study'), -> (2,'马丽','female','vip4','play'); Query OK, 2 rows affected (0.00 sec) mysql> select * from consumer; +------+---------+-------+-------+------------+ | id | name | gender| level | fav | +------+---------+-------+-------+------------+ | 1 | 赵云 | male | vip2 | read,study | | 2 | 马丽 |female | vip4 | play | +------+---------+-------+-------+------------+ 2 rows in set (0.00 sec)
五、存储引擎的介绍（了解）
1、什么是存储引擎
上一篇我们了解到MySQL中建立的库相当于文件夹，库中的表相当于文件。
现实生活中我们用来存储数据的文件有不同的类型，每种文件类型对应各自不同的处理机制，比如，处理文本用txt类型，处理表格用excel，处理图片用png等。
数据库中的表也应该有不同的类型，表的类型不同，会对应mysql不同的存取机制，表类型又称为存储引擎。
PS：存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中，数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）。
在Oracle和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。而MySQL数据库提供了多种储存引擎，用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。
如上图，SQL解析器、SQL优化器、缓冲池、存储引擎等组件在每个数据中都存在，但不是每个数据库都有这么多存储引擎。MySQL的插件式存储引擎可以让存储引擎层的开发人员设计他们希望的存储层，例如，有的应用需要满足事务的要求，有的应用则不需要对事务有这么强的要求；有的希望数据能持久存储，而有的只希望放在内存中，临时并快速地提供对数据的查询。
2、MySQL支持的存储引擎
a、如何查看
mysql> show engines\G; # 查看所有支持的引擎 mysql> show variables like '%storage_engine%'; # 查看正在使用的存储引擎
b、InnoDB存储引擎
支持事务，其设计目标主要面向联机事务处理(OLTP)的应用。
其特点是行锁设计、支持外键，并支持类似 Oracle 的非锁定读，即默认读取操作不会产生锁。从 MySQL 5.5.8 版本开始InnoDB是默认的存储引擎。
InnoDB存储引擎将数据放在一个逻辑的表空间中，这个表空间就像黑盒一样由 InnoDB 存储引擎自身来管理。从MySQL 4.1(包括4.1)版本开始，可以将每个 InnoDB 存储引擎的 表 单独存放到一个独立的ibd 文件中。此外，InnoDB 存储引擎支持将裸设备(row disk)用于建立其表空间。
InnoDB通过使用多版本并发控制(MVCC)来获得高并发性，并且实现了SQL标准的4种隔离级别，默认为REPEATABLE级别，同时使用一种称为netx-key locking的策略来避免幻读(phantom)现象的产生。除此之外，InnoDB存储引擎还提供了插入缓冲(insert buffer)、二次写(double write)、自适应哈希索引(adaptive hash index)、预读(read ahead) 等高性能和高可用的功能。
对于表中数据的存储，InnoDB 存储引擎采用了聚集(clustered)的方式，每张表都是按主键的顺序进行存储的，如果没有显式地在表定义时指定主键，InnoDB存储引擎会为每一行生成一个6字节的ROWID，并以此作为主键。
InnoDB 存储引擎是 MySQL 数据库最为常用的一种引擎，Facebook、Google、Yahoo等公司的成功应用已经证明了InnoDB存储引擎具备高可用性、高性能以及高可扩展性。对其底层实现的掌握和理解也需要时间和技术的积累。如果想深入了解InnoDB存储引擎的工作原理、实现和应用，可以参考《MySQL 技术内幕：InnoDB 存储引擎》一书。
c、MyISAM存储引擎
不支持事务、表锁设计、支持全文索引，主要面向一些OLAP数据库应用，在MySQL 5.5.8 版本之前是默认的存储引擎(除 Windows 版本外)。数据库系统与文件系统一个很大的不同在于对事务的支持，MyISAM 存储引擎是不支持事务的。究其根本，这也并不难理解。用户在所有的应用中是否都需要事务呢？在数据仓库中，如果没有ETL这些操作，只是简单地通过报表查询还需要事务的支持吗？此外，MyISAM 存储引擎的另一个与众不同的地方是，它的缓冲池只缓存(cache)索引文件，而不缓存数据文件，这与大多数的数据库都不相同。
d、MEMORY存储引擎
正如其名，Memory 存储引擎中的数据都存放在内存中，数据库重启或发生崩溃，表中的数据都将消失。它非常适合于存储OLTP数据库应用中临时数据的临时表，也可以作为OLAP数据库应用中数据仓库的维度表。Memory 存储引擎默认使用哈希索引，而不是通常熟悉的B+树索引。
e、BLACKHOLE存储引擎
黑洞存储引擎，可以应用于主备复制中的分发主库。
MySQL 数据库还有很多其他存储引擎，上述只是列举了最为常用的一些引擎。如果你喜欢，完全可以编写专属于自己的引擎，这就是开源赋予我们的能力，也是开源的魅力所在。
3、指定表类型/存储引擎，命令如下：
mysql> create table t1(id int)engine=innodb; # 默认不写就是innodb类型
练习：创建四张表，分别使用innodb、myisam、memory、blackhole存储引擎，进行插入数据测试：
create table t1(id int)engine=innodb; # 生成t1.frm和t1.ibd create table t2(id int)engine=myisam; # 生成t2.frm t2.MYD t2.MYI create table t3(id int)engine=memory; # 生成t3.frm create table t4(id int)engine=blackhole; # 生成t4.frm
总结：
1）.frm是存储数据表的框架结构；
2）.ibd是mysql数据文件；
3）.MYD是MyISAM表的数据文件的扩展名；
4）.MYI是MyISAM表的索引的扩展名；
5）发现后两种存储引擎只有表结构，无数据；
6）memory，在重启mysql或者重启机器后，表内数据清空；
7）blackhole，往表内插入任何数据，都相当于丢入黑洞，表内永远不存记录；
