MySQL基础回顾

前言

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

好了，我们开始！

> mysql -u root -p

数据库基础管理

显示数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

这几个数据库是MySQL安装时系统自动创建的，各自的功能如如下：

创建数据库

通常情况下我们接下来就要创建一个自己业务数据的数据库了：

mysql> create database test1
Query OK, 1 row affected (0.02 sec)

OK,现在使用show databases;就可以看到这个test1数据库了，接下来使用它(类似于git分支，进入它进行操作)

mysql> use test1
Database changed

同样的我们可以删除数据库：

mysql> drop database test1;
Query OK, 0 rows affected (0.01 sec)

操作表

上面我们已经切换到了test1数据库，接下来我们看看里面有什么数据库表：

mysql> show tables;
Empty set (0.00 sec)

嘿嘿，里面没有数据库表，需要我们自己创建业务相关的数据库表：

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)

同样的我们可以删除这张表：

mysql> drop table emp;
Query OK, 0 rows affected (0.01 sec)

也可以修改表的结构（这个操作在实际业务中很常见）：

alter table emp modify ename varchar(20);

上面使用alter table将emp表中的ename的字段长度从10字符更改为了20字符。

上面对数据库表的字段类型做了修改，除此之外还可以对数据库表字段做一些如下操作：

除了对字段的修改操作外，还可以变更表的名字：

mysql> alter table emp rename emp1;
Query OK, 0 rows affected (0.01 sec)

DML语句

DML操作是指针对数据库表中的记录数据的操作，主要包括增删改查四种操作。

插入

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)

更改

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)

事实上查询是一个很复杂的操作，并不是所有查询是上面看到的这样简单，还包括多张表关联查询等操作，后面着重来看查询。

删除

delete from emp where ename='xiaoming';

OK,删除对应条件的行数据是非常方便的，上面已经对数据库最基本的操作进行了认识，接下来我们需要去面对比较复杂的业务场景。

MySQL数据类型

在上面我们已经定义过表的字段，使用了varchar、date、int等数据类型，下面几个表格展示了MySQL中涵盖的数据类型：

数值类型

对于整型类型，按照取值范围分为tinyint、smallint、mediumint、int和bigint这5个类型，另外还支持int类型后指定显示宽度，例如int(5),对于整型都有一个可选属性UNSIGNED(无符号)。

对于小数的表示，MySQL中分为浮点数和定点数两种表示，浮点数有float和double，定点数只有decimal(使用字符串形式表示，比浮点数更精确，适合货币等)

浮点数和定点数后面都可以加(M,D)的方式来表示一共显示M位数字，其中D位位于小数点后面，M和D分别称为精度和标度。decimal在不指定精度时默认整数位时10，默认小数位为0.

日期时间类型

如果要经常插入或者更新日期通常使用TIMESTAMP表示，如果只表示年份，可以用YEAR.

字符串类型

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个成员，根据成员不同存储上也不同。

除了存储之外，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)