MySQL基础回顾

前言

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

好了,我们开始!

1
> mysql -u root -p

数据库基础管理

显示数据库

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 columnalert table emp add column age int(3);在emp表中增加了一个字段age
删除表字段drop columnalert table emp drop column age;删除emp表中的age字段
修改字段名changealert table emp change age age1 int(4);修改字段age为age1,字段长度修改为int(4)
修改字段排列顺序modifyalert 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数据类型

在上面我们已经定义过表的字段,使用了varchardateint等数据类型,下面几个表格展示了MySQL中涵盖的数据类型:

数值类型

数值类型字节数
TINYINT1
SMALLINT2
MEDIUMINT3
INT、INTEGER4
BIGINT8
FLOAT4
DOUBLE8
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.

日期时间类型

日期和时间类型字节
DATE4
DATETIME8
TIMESTAMP4
TIME3
YEAR1

如果要经常插入或者更新日期通常使用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~81
9~162
17~243
25~324
33~648

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