2019-04-30日

14 分钟读完 (大约 2033 个字)

MySQL基础回顾

前言

最近想做一个小项目，具有三端(Android,Web,后端)，所以复习一下数据库相关知识，就以这一篇博文为开端复习整理一下数据库相关知识点。本文开始之前我使用的是Mac OS安装的MySQL 5.7版本，你也可以在你的Windows或者Linux系统上安装MySQL社区版，建议使用Linux环境。

好了，我们开始！

1	> mysql -u root -p

数据库基础管理

显示数据库

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来查看一下表结构：

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);

这样就给数据库表中插入了一行数据，查看插入的数据：

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语句就是一条查询语句，其中*是通配符，表示所有字段，当然我们也可以选择性的查询部分字段内容：

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保留空格，例如：

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类型也有些类似的不同：

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个成员。

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只能选一个。

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)