前言
最近想做一个小项目,具有三端(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)
|