前言
最近想做一个小项目，具有三端(Android,Web,后端)，所以复习一下数据库相关知识，就以这一篇博文为开端复习整理一下数据库相关知识点。本文开始之前我使用的是Mac OS安装的MySQL 5.7版本，你也可以在你的Windows或者Linux系统上安装MySQL社区版，建议使用Linux环境。
好了，我们开始！
数据库基础管理
显示数据库
1
2
3
4
5
6
7
8
9
| mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
|
这几个数据库是MySQL安装时系统自动创建的，各自的功能如如下：
| 数据库名 | 功能描述 |
|---|
| information_schema | 主要存储系统中的一些数据库对象信息，比如用户表信息、列信息、权限信息、字符集信息、分区信息等。 |
| mysql | 存储了系统的用户权限信息。 |
| performance_schema | 主要用于收集数据库服务器性能参数(5.5版本) |
| sys | 通过这个库可以快速的了解系统的元数据信息(5.7版本新特性) |
创建数据库
通常情况下我们接下来就要创建一个自己业务数据的数据库了：
1
2
| mysql> create database test1
Query OK, 1 row affected (0.02 sec)
|
OK,现在使用show databases;就可以看到这个test1数据库了，接下来使用它(类似于git分支，进入它进行操作)
1
2
| mysql> use test1
Database changed
|
同样的我们可以删除数据库：
1
2
| mysql> drop database test1;
Query OK, 0 rows affected (0.01 sec)
|
操作表
上面我们已经切换到了test1数据库，接下来我们看看里面有什么数据库表：
1
2
| mysql> show tables;
Empty set (0.00 sec)
|
嘿嘿，里面没有数据库表，需要我们自己创建业务相关的数据库表：
1
| create table emp(ename varchar(10), hiredate date, sal decimal(10, 2), deptno int(2));
|
执行完创建数据库表语句后使用desc来查看一下表结构：
1
2
3
4
5
6
7
8
9
10
| mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(10) | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | int(2) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
|
同样的我们可以删除这张表：
1
2
| mysql> drop table emp;
Query OK, 0 rows affected (0.01 sec)
|
也可以修改表的结构（这个操作在实际业务中很常见）：
1
| alter table emp modify ename varchar(20);
|
上面使用alter table将emp表中的ename的字段长度从10字符更改为了20字符。
上面对数据库表的字段类型做了修改，除此之外还可以对数据库表字段做一些如下操作：
| 操作命令 | 操作实例 | 操作说明 |
|---|
增加表字段add column | alert table emp add column age int(3); | 在emp表中增加了一个字段age |
删除表字段drop column | alert table emp drop column age; | 删除emp表中的age字段 |
修改字段名change | alert table emp change age age1 int(4); | 修改字段age为age1,字段长度修改为int(4) |
修改字段排列顺序modify | alert table emp modify age int(3) first; | 修改age字段，将它放在最前面 |
除了对字段的修改操作外，还可以变更表的名字：
1
2
| mysql> alter table emp rename emp1;
Query OK, 0 rows affected (0.01 sec)
|
DML语句
DML操作是指针对数据库表中的记录数据的操作，主要包括增删改查四种操作。
插入
1
| insert into emp(ename, hiredate, sal, deptno) values('xiaoming', '2019-03-01', '4500', 2);
|
这样就给数据库表中插入了一行数据，查看插入的数据：
1
2
3
4
5
6
7
| mysql> select * from emp;
+----------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+----------+------------+---------+--------+
| xiaoming | 2019-03-01 | 4500.00 | 2 |
+----------+------------+---------+--------+
1 row in set (0.00 sec)
|
更改
1
| update emp set sal=6000 where ename='xiaoming';
|
上面将emp表中的对应ename是xiaoming的数据行的sal字段内容更改为了6000.
查询
实际上上面的select * from emp语句就是一条查询语句，其中*是通配符，表示所有字段，当然我们也可以选择性的查询部分字段内容：
1
2
3
4
5
6
7
| mysql> select ename, sal from emp;
+----------+---------+
| ename | sal |
+----------+---------+
| xiaoming | 6000.00 |
+----------+---------+
1 row in set (0.00 sec)
|
事实上查询是一个很复杂的操作，并不是所有查询是上面看到的这样简单，还包括多张表关联查询等操作，后面着重来看查询。
删除
1
| delete from emp where ename='xiaoming';
|
OK,删除对应条件的行数据是非常方便的，上面已经对数据库最基本的操作进行了认识，接下来我们需要去面对比较复杂的业务场景。
MySQL数据类型
在上面我们已经定义过表的字段，使用了varchar、date、int等数据类型，下面几个表格展示了MySQL中涵盖的数据类型：
数值类型
| 数值类型 | 字节数 |
|---|
| TINYINT | 1 |
| SMALLINT | 2 |
| MEDIUMINT | 3 |
| INT、INTEGER | 4 |
| BIGINT | 8 |
| FLOAT | 4 |
| DOUBLE | 8 |
| DEC(M, D) | M+2 |
| DECIMAL(M,D) | M+2 |
对于整型类型，按照取值范围分为tinyint、smallint、mediumint、int和bigint这5个类型，另外还支持int类型后指定显示宽度，例如int(5),对于整型都有一个可选属性UNSIGNED(无符号)。
对于小数的表示，MySQL中分为浮点数和定点数两种表示，浮点数有float和double，定点数只有decimal(使用字符串形式表示，比浮点数更精确，适合货币等)
浮点数和定点数后面都可以加(M,D)的方式来表示一共显示M位数字，其中D位位于小数点后面，M和D分别称为精度和标度。decimal在不指定精度时默认整数位时10，默认小数位为0.
日期时间类型
| 日期和时间类型 | 字节 |
|---|
| DATE | 4 |
| DATETIME | 8 |
| TIMESTAMP | 4 |
| TIME | 3 |
| YEAR | 1 |
如果要经常插入或者更新日期通常使用TIMESTAMP表示，如果只表示年份，可以用YEAR.
字符串类型
| 字符串类型 | 字节 |
|---|
| CHAR(M) | M |
| VARCHAR(M) | M |
| TINYBLOB | 允许长度0~255字节 |
| BLOB | 允许长度0~65535字节 |
| MEDIUMBLOB | 允许长度0~167772150字节 |
| LONGBLOG | 允许长度0~4294967295字节 |
| TINYTEXT | 允许长度0~255字节 |
| TEXT | 允许长度0~65535字节 |
| MEDIUMTEXT | 允许长度0~167772150字节 |
| LONGTEXT | 允许长度0~4294967295字节 |
| VARBINARY(M) | 允许长度0~M字节 |
| BINARY(M) | 允许长度0~M字节 |
blob存储的是二进制数据，而text存储的是非二进制数据，需要指定字符集，text只能存储纯文本字符串。
char和varchar类型很类似，都用来保存MySQL中较短的字符串，区别就是varchar保留空格，例如：
1
2
3
4
5
6
7
8
9
10
11
12
| mysql> create table vc (v varchar(4), c char(4));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into vc values ('ab ', 'ab ');
Query OK, 1 row affected (0.00 sec)
mysql> select concat(v, '+'), concat(c, '+') from vc;
+----------------+----------------+
| concat(v, '+') | concat(c, '+') |
+----------------+----------------+
| ab + | ab+ |
+----------------+----------------+
|
binary和varbinary类型也有些类似的不同：
1
2
3
4
5
6
7
8
9
10
11
12
13
| mysql> create table t (c binary(3));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t set c='a';
Query OK, 1 row affected (0.00 sec)
mysql> select *, hex(c), c='a', c='a\0', c='a\0\0' from t;
+------+--------+-------+---------+-----------+
| c | hex(c) | c='a' | c='a\0' | c='a\0\0' |
+------+--------+-------+---------+-----------+
| a | 610000 | 0 | 0 | 1 |
+------+--------+-------+---------+-----------+
1 row in set (0.00 sec)
|
hex()函数可以将一个字符串转化成十六进制格式，可以看到上面使用binary时在值的最后填充了0x00.
枚举类型
enum类型是一种数据库中的枚举类型，对于1~255个成员使用1个字节存储，对于255~65535个成员使用2个字节存储，最多允许有65535个成员。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| mysql> create table t (gender enum('M', 'F'));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t values ('M'), ('1'), ('f'), (NULL);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from t;
+--------+
| gender |
+--------+
| M |
| M |
| F |
| NULL |
+--------+
4 rows in set (0.00 sec)
|
SET类型
set类型和enum类型非常类似，也是一个字符串对象，里面可以包含0~64个成员，根据成员不同存储上也不同。
| 成员数 | 字节数 |
|---|
| 1~8 | 1 |
| 9~16 | 2 |
| 17~24 | 3 |
| 25~32 | 4 |
| 33~64 | 8 |
除了存储之外，set和enum最主要区别是set类型可以一次选取多个成员(可以理解为集合），而enum只能选一个。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| mysql> create table t (col set ('a', 'b', 'c', 'd'));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t values ('a,b'), ('a,d,a'), ('a,c'), ('a');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from t;
+------+
| col |
+------+
| a,b |
| a,d |
| a,c |
| a |
+------+
4 rows in set (0.00 sec)
|
