138 张图带你 MySQL 入门

时间 2020-07-23 标签 138 张图带 mysql 入门

SQL 基础使用

MySQL 是一种关系型数据库，说到关系，那么就离不开表与表之间的关系，而最能体现这种关系的其实就是咱们接下来须要介绍的主角 SQL，SQL 的全称是 Structure Query Language ，结构化的查询语言，它是一种针对表关联关系所设计的一门语言，也就是说，学好 MySQL，SQL 是基础和重中之重。SQL 不仅是 MySQL 中特有的一门语言，大多数关系型数据库都支持这门语言。html

下面咱们就来一块儿学习一下这门很是重要的语言。mysql

查询语言分类

在了解 SQL 以前咱们须要知道下面这几个概念web

数据定义语言：简称DDL (Data Definition Language)，用来定义数据库对象:数据库、表、列等；

数据操做语言：简称DML (Data Manipulation Language)，用来对数据库中表的记录进行更新。关键字： insert、update、delete等
数据控制语言：简称DCL(Data Control Language)，用来定义数据库访问权限和安全级别，建立用户等。关键字： grant等
数据查询语言：简称DQL(Data Query Language)，用来查询数据库中表的记录，关键字： select from where等

DDL 语句

建立数据库

下面就开始咱们的 SQL 语句学习之旅，首先你须要启动 MySQL 服务，我这里是 mac 电脑，因此我直接能够启动面试

而后咱们使用命令行的方式链接数据库，打开 iterm，输入下面正则表达式

MacBook:~ mr.l$ mysql -uroot -p

就能够链接到数据库了sql

在上面命令中，mysql 表明客户端命令，- u 表示后面须要链接的用户，-p 表示须要输入此用户的密码。在你输入用户名和密码后，若是成功登录，会显示一个欢迎界面（如上图）和 mysql> 提示符。数据库

欢迎界面主要描述了这些东西安全

每一行的结束符，这里用 ; 或者 \g 来表示每一行的结束
Your MySQL connection id is 4，这个记录了 MySQL 服务到目前为止的链接数，每一个新连接都会自动增长 1 ，上面显示的链接次数是 4 ，说明咱们只链接了四次
而后下面是 MySQL 的版本，咱们使用的是 5.7
经过 help 或者 \h 命令来显示帮助内容，经过 \c 命令来清除命令行 buffer。

而后须要作的事情是什么？咱们最终想要学习 SQL 语句，SQL 语句确定是要查询数据，经过数据来体现出来表的关联关系，因此咱们须要数据，那么数据存在哪里呢？数据存储的位置被称为 表(table)，表存储的位置被称为 数据库(database)，因此咱们须要先建数据库后面再建表而后插入数据，再进行查询。服务器

因此咱们首先要作的就是建立数据库，建立数据库能够直接使用指令网络

CREATE DATABASE dbname;

进行建立，好比咱们建立数据库 cxuandb

create database cxuandb;

注意最后的 ; 结束语法必定不要丢掉，不然 MySQL 会认为你的命令没有输出完，敲 enter 后会直接换行输出

建立完成后，会提示 Query OK, 1 row affected，这段语句什么意思呢？ Query OK 表示的就是查询完成，为何会显示这个？由于全部的 DDL 和 DML 操做执行完成后都会提示这个，也能够理解为操做成功。后面跟着的 **1 row affected ** 表示的是影响的行数，() 内显示的是你执行这条命令所耗费的时间，也就是 0.03 秒。

上图咱们成功建立了一个 cxuandb 的数据库，此时咱们还想建立一个数据库，咱们再执行相同的指令，结果提示

提示咱们不能再建立数据库了，数据库已经存在。这时候我就有疑问了，我怎么知道都有哪些数据库呢？别我再想建立一个数据库又告诉我已经存在，这时候可使用 show databases 命令来查看你的 MySQL 已有的数据库

show databases;

执行完成后的结果以下

由于数据库我以前已经使用过，这里就须要解释一下，除了刚刚新建立成功的 cxuandb 外，informationn_schema 、performannce_schema 和 sys 都是系统自带的数据库，是安装 MySQL 默认建立的数据库。它们各自表示

informationn_schema：主要存储一些数据库对象信息，好比用户表信息、权限信息、分区信息等
performannce_schema： MySQL 5.5 以后新增长的数据库，主要用于收集数据库服务器性能参数。
sys: MySQL 5.7 提供的数据库，sys 数据库里面包含了一系列的存储过程、自定义函数以及视图来帮助咱们快速的了解系统的元数据信息。

其余全部的数据库都是做者本身建立的，能够忽略他们。

在建立完数据库以后，能够用以下命令选择要操做的数据库

use cxuandb

这样就成功切换为了 cxuandb 数据库，咱们能够在此数据库下进行建表、查看基本信息等操做。

好比想要看康康咱们新建的数据库里面有没有其余表

show tables;

果真，咱们新建的数据库下面没有任何表，可是如今，咱们还不进行建表操做，咱们仍是先来认识一下数据库层面的命令，也就是其余 DDL 指令

删除数据库

若是一个数据库咱们不想要了，那么该怎么办呢？直接删掉数据库不就行了吗？删表语句是

drop database dbname;

好比 cxuandb 咱们不想要他了，能够经过使用

drop database cxuandb;

进行删除，这里咱们就不进行演示了，由于 cxuandb 咱们后面还会使用。

可是这里注意一点，你删除数据库成功后会出现 0 rows affected，这个能够不用理会，由于在 MySQL 中，drop 语句操做的结果都是 0 rows affected。

建立表

下面咱们就能够对表进行操做了，咱们刚刚 show tables 发现尚未任何表，因此咱们如今进行建表语句

CREATE TABLE 表名称
(
列名称1 数据类型 约束,
列名称2 数据类型 约束,
列名称3 数据类型 约束,
....
)

这样就很清楚了吧，列名称就是列的名字，紧跟着列名后面就是数据类型，而后是约束，为何要这么设计？举个例子你就清楚了，好比 cxuan 刚被生出来就被打印上了标签

好比咱们建立一个表，里面有 5 个字段，姓名(name)、性别(sex)、年龄(age)、什么时候雇佣(hiredate)、薪资待遇(wage)，建表语句以下

create table job(name varchar(20), sex varchar(2), age int(2), hiredate date, wage decimal(10,2));

事实证实这条建表语句仍是没问题的，建表完成后可使用 DESC tablename 查看表的基本信息

DESC 命令会查看表的定义，可是输出的信息还不够全面，因此，若是想要查看更全的信息，还要经过查看表的建立语句的 SQL 来获得

show create table job \G;

能够看到，除了看到表定义以外，还看到了表的 engine(存储引擎) 为 InnoDB 存储引擎，\G 使得记录可以竖着排列，若是不用 \G 的话，效果以下

删除表

表的删除语句有两种，一种是 drop 语句，SQL 语句以下

drop table job

一种是 truncate 语句，SQL 语句以下

truncate table job

这二者的区别简单理解就是 drop 语句删除表以后，能够经过日志进行回复，而 truncate 删除表以后永远恢复不了，因此，通常不使用 truncate 进行表的删除。‘

修改表

对于已经建立好的表，尤为是有大量数据的表，若是须要对表作结构上的改变，能够将表删除而后从新建立表，可是这种效率会产生一些额外的工做，数据会从新加载近来，若是此时有服务正在访问的话，也会影响服务读取表中数据，因此此时，咱们须要表的修改语句来对已经建立好的表的定义进行修改。

修改表结构通常使用 alter table 语句，下面是经常使用的命令

ALTER TABLE tb MODIFY [COLUMN] column_definition [FIRST | AFTER col_name];

好比咱们想要将 job 表中的 name 由 varchar(20) 改成 varchar(25)，可使用以下语句

alter table job modify name varchar(25);

也能够对表结构进行修改，好比增长一个字段

alter table job add home varchar(30);

将新添加的表的字段进行删除

alter table job drop column home;

能够对表中字段的名称进行修改，好比吧 wage 改成 salary

alter table job change wage salary decimal(10,2);

修改字段的排列顺序，咱们前面介绍过修改语法涉及到一个顺序问题，都有一个可选项 **first | after ** column_name，这个选项能够用来修改表中字段的位置，默认 ADD 是在添加为表中最后一个字段，而 CHANGE/MODIFY 不会改变字段位置。好比

alter table job add birthday after hiredate;

能够对表名进行修改，例如将 job 表改成 worker

alter table job rename worker;

DML 语句

有的地方把 DML 语句（增删改）和 DQL 语句（查询）统称为 DML 语句，有的地方分开，咱们目前使用分开称呼的方式

插入

表建立好以后，咱们就能够向表里插入数据了，插入记录的基本语法以下

INSERT INTO tablename (field1,field2) VALUES(value1,value2);

例如，向中插入如下记录

insert into job(name,sex,age,hiredate,birthday,salary) values("cxuan","男",24,"2020-04-27","1995-08-22",8000);

也能够不用指定要插入的字段，直接插入数据便可

insert into job values("cxuan02","男",25,"2020-06-01","1995-04-23",12000);

这里就有一个问题，若是插入的顺序不一致的话会怎么样呢？

对于含可空字段、非空可是含有默认值的字段、自增字段能够不用在 insert 后的字段列表出现，values 后面只须要写对应字段名称的 value 便可，没有写的字段能够自动的设置为 NULL、默认值或者自增的下一个值，这样能够缩短要插入 SQL 语句的长度和复杂性。

好比咱们设置一下 hiredate、age 能够为 null，来试一下

insert into job(name,sex,birthday,salary) values("cxuan03","男","1992-08-23",15000);

咱们看一下实际插入的数据

咱们能够看到有一行两个字段显示 NULL。在 MySQL 中，insert 语句还有一个很好的特性，就是一次能够插入多条记录

INSERT INTO tablename (field1,field2) VALUES
(value1,value2),
(value1,value2),
(value1,value2),
...;

能够看出，每条记录之间都用逗号进行分割，这个特性可使得 MySQL 在插入大量记录时，节省不少的网络开销，大大提升插入效率。

更新记录

对于表中已经存在的数据，能够经过 update 命令对其进行修改，语法以下

UPDATE tablename SET field1 = value1, field2 = value2 ;

例如，将 job 表中的 cxuan03 中 age 的 NULL 改成 26，SQL 语句以下

update job set age = 26 where name = 'cxuan03';

SQL 语句中出现了一个 where 条件，咱们会在后面说到 where 条件，这里简单理解一下它的概念就是根据哪条记录进行更新，若是不写 where 的话，会对整个表进行更新

删除记录

若是记录再也不须要，可使用 delete 命令进行删除

DELETE FROM tablename [WHERE CONDITION]

例如，在 job 中删除名字是 cxuan03 的记录

delete from job where name = 'cxuan03';

在 MySQL 中，删除语句也能够不指定 where 条件，直接使用

delete from job

这种删除方式至关因而清楚表的操做，表中全部的记录都会被清除。

DQL 语句

下面咱们一块儿来认识一下 DQL 语句，数据被插入到 MySQL 中，就可使用 SELECT 命令进行查询，来获得咱们想要的结果。

SELECT 查询语句能够说是最复杂的语句了，这里咱们只介绍一下基本语法

一种最简单的方式就是从某个表中查询出全部的字段和数据，简单粗暴，直接使用 SELECT *

SELECT * FROM tablename;

例如咱们将 job 表中的全部数据查出来

select * from job;

其中 * 是查询出全部的数据，固然，你也能够查询出指定的数据项

select name,sex,age,hiredate,birthday,salary from job;

上面这条 SQL 语句和 select * from job 表是等价的，可是这种直接查询指定字段的 SQL 语句效率要高。

上面咱们介绍了基本的 SQL 查询语句，可是实际的使用场景会会比简单查询复杂太多，通常都会使用各类 SQL 的函数和查询条件等，下面咱们就来一块儿认识一下。

去重

使用很是普遍的场景之一就是 去重，去重可使用 distinct 关键字来实现

为了演示效果，咱们先向数据库中插入批量数据，插入完成后的表结构以下

下面咱们使用 distinct 来对 age 去重来看一下效果

你会发现只有两个不一样的值，其余和 25 重复的值被过滤掉了，因此咱们使用 distinct 来进行去重

条件查询

咱们以前的全部例子都是查询所有的记录，若是咱们只想查询指定的记录呢？这里就会用到 where条件查询语句，条件查询能够对指定的字段进行查询，好比咱们想查询全部年龄为 24 的记录，以下

select * from job where age = 24;

where 条件语句后面会跟一个判断的运算符 =，除了 = 号比较外，还可使用 >、<、>=、<=、!= 等比较运算符；例如

select * from job where age >= 24;

就会从 job 表中查询出 age 年龄大于或等于 24 的记录

除此以外，在 where 条件查询中还能够有多个并列的查询条件，好比咱们能够查询年龄大于等于 24，而且薪资大雨 8000 的记录

select * from job where age >= 24 and salary > 8000;

多个条件之间还可使用 or、and 等逻辑运算符进行多条件联合查询，运算符会在之后章节中详细讲解。

排序

咱们会常常有这样的需求，按照某个字段进行排序，这就用到了数据库的排序功能，使用关键字 order by 来实现，语法以下

SELECT * FROM tablename [WHERE CONDITION] [ORDER BY field1 [DESC|ASC] ， field2 [DESC|ASC]，……fieldn [DESC|ASC]]

其中 DESC 和 ASC 就是顺序排序的关键字，DESC 会按照字段进行降序排列，ASC 会按照字段进行升序排列，默认会使用升序排列，也就是说，你不写 order by 具体的排序的话，默认会使用升序排列。order by 后面能够跟多个排序字段，而且每一个排序字段能够有不一样的排序顺序。

为了演示功能，咱们先把表中的 salary 工资列进行修改，修改完成后的表记录以下

下面咱们按照工资进行排序，SQL 语句以下

select * from job order by salary desc;

语句执行完成后的结果以下

这是对一个字段进行排序的结果，也能够对多个字段进行排序，可是须要注意一点

根据 order by 后面声名的顺序进行排序，若是有三个排序字段 A、B、C 的话，若是 A 字段排序字段的值同样，则会根据第二个字段进行排序，以此类推。

若是只有一个排序字段，那么这些字段相同的记录将会无序排列。

限制

对于排序后的字段，或者不排序的字段，若是只但愿显示一部分的话，就会使用 LIMIT 关键字来实现，好比咱们只想取前三条记录

select * from job limit 3;

或者咱们对排序后的字段取前三条记录

select * from job order by salary limit 3;

上面这种 limit 是从表记录的第 0 条开始取，若是从指定记录开始取，好比从第二条开始取，取三条记录，SQL 以下

select * from job order by salary desc limit 2,3;

limit 通常常常和 order by 语法一块儿实现分页查询。

注意：limit 是 MySQL 扩展 SQL92 以后的语法，在其余数据库好比 Oracle 上就不通用，我犯过一个白痴的行为就是在 Oracle 中使用 limit 查询语句。。。

聚合

下面咱们来看一下对记录进行汇总的操做，这类操做主要有

汇总函数，好比 sum 求和、count 统计数量、max 最大值、min 最小值等
group by，关键字表示对分类聚合的字段进行分组，好比按照部门统计员工的数量，那么 group by 后面就应该跟上部门
with 是可选的语法，它表示对汇总以后的记录进行再次汇总
having 关键字表示对分类后的结果再进行条件的过滤。

看起来 where 和 having 意思差很少，不过它们用法不同，where 是使用在统计以前，对统计前的记录进行过滤，having 是用在统计以后，是对聚合以后的结果进行过滤。也就是说 where 永远用在 having 以前，咱们应该先对筛选的记录进行过滤，而后再对分组的记录进行过滤。

能够对 job 表中员工薪水进行统计，选出总共的薪水、最大薪水、最小薪水

select sum(salary) from job;

select max(salary),min(salary) from job;

好比咱们要统计 job 表中人员的数量

select count(1) from job;

统计完成后的结果以下

咱们能够按照 job 表中的年龄来进行对应的统计

select age,count(1) from job group by age;

既要统计各年龄段的人数，又要统计总人数

select age,count(1) from job group by age with rollup;

在此基础上进行分组，统计数量大于 1 的记录

select age,count(1) from job group by age with rollup having count(1) > 1;

表链接

表链接一直是笔者比较痛苦的地方，曾经由于一个表链接挂了面试，如今来认真撸一遍。

表链接通常体如今表之间的关系上。当须要同时显示多个表中的字段时，就能够用表链接来实现。

为了演示表链接的功能，咱们为 job 表加一个 type 字段表示工做类型，增长一个 job_type 表表示具体的工做种类，以下所示

下面开始咱们的演示

查询出 job 表中的 type 和 job_type 表中的 type 匹配的姓名和工做类型

select job.name,job_type.name from job,job_type where job.type = job_type.type;

上面这种链接使用的是内链接，除此以外，还有外链接。那么它们之间的区别是啥呢？

内链接：选出两张表中互相匹配的记录；

外链接：不只选出匹配的记录，也会选出不匹配的记录；

外链接分为两种

左外链接：筛选出包含左表的记录而且右表没有和它匹配的记录
右外链接：筛选出包含右表的记录甚至左表没有和它匹配的记录

为了演示效果咱们在 job 表和 job_type 表中分别添加记录，添加完成后的两表以下

下面咱们进行左外链接查询：查询出 job 表中的 type 和 job_type 表中的 type 匹配的姓名和工做类型

select job.name,job_type.name from job left join job_type on job.type = job_type.type;

查询出来的结果以下

能够看出 cxuan06 也被查询出来了，而 cxuan06 他没有具体的工做类型。

使用右外链接查询

select job.name,job_type.name from job right join job_type on job.type = job_type.type;

能够看出，job 表中并无 waiter 和 manager 的角色，可是也被查询出来了。

子查询

有一些状况，咱们须要的查询条件是另外一个 SQL 语句的查询结果，这种查询方式就是子查询，子查询有一些关键字好比 in、not in、=、!=、exists、not exists 等，例如咱们能够经过子查询查询出每一个人的工做类型

select job.* from job where type in (select type from job_type);

若是自查询数量惟一的话，还能够用 = 来替换 in

select * from job where type = (select type from job_type);

意思是自查询不惟一，咱们使用 limit 限制一下返回的记录数

select * from job where type = (select type from job_type limit 1,1);

在某些状况下，子查询能够转换为表链接

联合查询

咱们还常常会遇到这样的场景，将两个表的数据单独查询出来以后，将结果合并到一块儿进行显示，这个时候就须要 UNION 和 UNION ALL 这两个关键字来实现这样的功能，UNION 和 UNION ALL 的主要区别是 UNION ALL 是把结果集直接合并在一块儿，而 UNION 是将 UNION ALL 后的结果进行一次 DISTINCT 去除掉重复数据。

好比

select type from job union all select type from job_type;

它的结果以下

上述结果是查询 job 表中的 type 字段和 job_type 表中的 type 字段，并把它们进行汇总，能够看出 UNION ALL 只是把全部的结果都列出来了

使用 UNION 的 SQL 语句以下

select type from job union select type from job_type;

能够看出 UNION 是对 UNION ALL 使用了 distinct 去重处理。

DCL 语句

DCL 语句主要是管理数据库权限的时候使用，这类操做通常是 DBA 使用的，开发人员不会使用 DCL 语句。

关于帮助文档的使用

咱们通常使用 MySQL 遇到不会的或者有疑问的东西常常要去查阅网上资料，甚至可能须要去查 MySQL 官发文档，这样会耗费大量的时间和精力。

下面教你一下在 MySQL 命令行就能直接查询资料的语句

按照层次查询

可使用 ? contents 来查询全部可供查询的分类，以下所示

? contents;

咱们输入

? Account Management

能够查询具体关于权限管理的命令

好比咱们想了解一下数据类型

? Data Types

而后咱们想了解一下 VARCHAR 的基本定义，能够直接使用

? VARCHAR

能够看到有关于 VARCHAR 数据类型的详细信息，而后在最下面还有 MySQL 的官方文档，方便咱们快速查阅。

快速查阅

在实际应用过程当中，若是要快速查询某个语法时，可使用关键字进行快速查询，好比咱们使用

? show

可以快速列出一些命令

好比咱们想要查阅 database 的信息，使用

SHOW CREATE DATABASE cxuandb;

MySQL 数据类型

MySQL 提供不少种数据类型来对不一样的常量、变量进行区分，MySQL 中的数据类型主要是 数值类型、日期和时间类型、字符串类型 选择合适的数据类型进行数据的存储很是重要，在实际开发过程当中，选择合适的数据类型也可以提升 SQL 性能，因此有必要认识一下这些数据类型。

数值类型

MySQL 支持全部标准的 SQL 数据类型，这些数据类型包括严格数据类型的严格数值类型，这些数据类型有

INTEGER
SMALLINT
DECIMAL
NUMERIC。

近似数值数据类型 并不用严格按照指定的数据类型进行存储，这些有

FLOAT
REAL
DOUBLE PRECISION

还有通过扩展以后的数据类型，它们是

TINYINT
MEDIUMINT
BIGINT
BIT

其中 INT 是 INTEGER 的缩写，DEC 是 DECIMAL 的缩写。

下面是全部数据类型的汇总

整数

在整数类型中，按照取值范围和存储方式的不一样，分为

TINYINT ，占用 1 字节
SMALLINT，占用 2 字节
MEDIUMINT，占用 3 字节
INT、INTEGER，占用 4 字节
BIGINT，占用 8 字节

五个数据类型，若是超出类型范围的操做，会发生错误提示，因此选择合适的数据类型很是重要。

还记得咱们上面的建表语句么

咱们通常会在 SQL 语句的数据类型后面加上指定长度来表示数据类型许可的范围，例如

int(7)

表示 int 类型的数据最大长度为 7，若是填充不满的话会自动填满，若是不指定 int 数据类型的长度的话，默认是 int(11)。

咱们建立一张表来演示一下

create table test1(aId int, bId int(5));

/* 而后咱们查看一下表结构 */
desc test1;

整数类型通常配合 zerofill 来使用，顾名思义，就是用 0 进行填充，也就是数字位数不够的空间使用 0 进行填充。

分别修改 test1 表中的两个字段

alter table test1 modify aId int zerofill;

alter table test1 modify bId int(5) zerofill;

而后插入两条数据，执行查询操做

如上图所示，使用zerofill 能够在数字前面使用 0 来进行填充，那么若是宽度超过指定长度后会如何显示？咱们来试验一下，向 aId 和 bId 分别插入超过字符限制的数字

会发现 aId 已经超出了指定范围，那么咱们对 aId 插入一个在其容许范围以内的数据

会发现，aId 已经插进去了，bId 也插进去了，为何 bId 显示的是 int(5) 却可以插入 7 位长度的数值呢？

全部的整数都有一个可选属性 UNSIGNED(无符号)，若是须要在字段里面保存非负数或者是须要较大上限值时，可使用此选项，它的取值范围是正常值的下限取 0 ，上限取原值的 2 倍。若是一个列为 zerofill ，会自动为该列添加 UNSIGNED 属性。

除此以外，整数还有一个类型就是 AUTO_INCREMENT，在须要产生惟一标识符或者顺序值时，可利用此属性，这个属性只用于整数字符。一个表中最多只有一个 AUTO_INCREMENT 属性，通常用于自增主键，并且 NOT NULL，而且是 PRIMARY KEY 和 UNIQUE 的，主键必须保证惟一性并且不为空。

小数

小数说的是啥？它其实有两种类型；一种是浮点数类型，一种是定点数类型；

浮点数有两种

单精度浮点型 - float 型
双精度浮点型 - double 型

定点数只有一种 decimal。定点数在 MySQL 内部中以字符串的形式存在，比浮点数更为准确，适合用来表示精度特别高的数据。

浮点数和定点数均可以使用 (M,D) 的方式来表示，M 表示的就是 整数位 + 小数位 的数字，D 表示位于 . 后面的小数。M 也被称为精度，D 被称为标度。

下面经过示例来演示一下

首先创建一个 test2 表

CREATE TABLE test2 (aId float(6,2) default NULL, bId double(6,2) default NULL,cId decimal(6,2) default NULL)

而后向表中插入几条数据

insert into test2 values(1234.12,1234.12,1234.12);

这个时候显示的数据就是

而后再向表中插入一些约束以外的数据

insert into test2 values(1234.123,1234.123,1234.123);

发现插入完成后还显示的是 1234.12，小数位第三位的值被舍去了。

如今咱们把 test2 表中的精度所有去掉，再次插入

alter table test2 modify aId float;

alter table test2 modify bId double;

alter table test2 modify cId decimal;

先查询一下，发现 cId 舍去了小数位。

而后再次插入 1.23，SQL 语句以下

insert into test2 values(1.23,1.23,1.23);

结果以下

这个时候能够验证

浮点数若是不写精度和标度，会按照实际的精度值进行显示
定点数若是不写精度和标度，会按照 decimal(10,0) 来进行操做，若是数据超过了精度和标题，MySQL 会报错

位类型

对于位类型，用于存放字段值，BIT(M) 能够用来存放多位二进制数，M 的范围是 1 - 64，若是不写的话默认为 1 位。

下面咱们来掩饰一下位类型

新建一个 test3 表，表中只有一个位类型的字段

create table test3(id bit(1));

而后随意插入一条数据

insert into test3 values(1);

发现没法查询出对应结果。

而后咱们使用 hex() 和 bin() 函数进行查询

发现可以查询出对应结果。

也就是说当数据插入 test3 时，会首先把数据转换成为二进制数，若是位数容许，则将成功插入；若是位数小于实际定义的位数，则插入失败。若是咱们像表中插入数据 2

insert into test3 values(2);

那么会报错

由于 2 的二进制数表示是 10，而表中定义的是 bit(1) ，因此没法插入。

那么咱们将表字段修改一下

而后再进行插入，发现已经可以插入了

日期时间类型

MySQL 中的日期与时间类型，主要包括：YEAR、TIME、DATE、DATETIME、TIMESTAMP，每一个版本可能不一样。下表中列出了这几种类型的属性。

下面分别来介绍一下

YEAR

YEAR 可使用三种方式来表示

用 4 位的数字或者字符串表示，二者效果相同，表示范围 1901 - 2155，插入超出范围的数据会报错。
以 2 位字符串格式表示，范围为 ‘00’_{‘99’。‘00’}‘69’ 表示 2000_{2069，‘70’}‘99’ 表示1970~1999。‘0’ 和 ‘00’ 都会被识别为 2000，超出范围的数据也会被识别为 2000。
以 2 位数字格式表示，范围为 1_99。169 表示 2001~2069, 70~99 表示 1970~1999。但 0 值会被识别为0000，这和 2 位字符串被识别为 2000 有所不一样

下面咱们来演示一下 YEAR 的用法，建立一个 test4 表

create table test4(id year);

而后咱们看一下 test4 的表结构

默认建立的 year 就是 4 位，下面咱们向 test4 中插入数据

insert into test4 values(2020),('2020');

而后进行查询，发现表示形式是同样的

使用两位字符串来表示

delete from test4;

insert into test4 values ('0'),('00'),('11'),('88'),('20'),('21');

使用两位数字来表示

delete from test4;

insert into test4 values (0),(00),(11),(88),(20),(21);

发现只有前两项不同。

TIME

TIME 所表示的范围和咱们预想的不同

咱们把 test4 改成 TIME 类型，下面是 TIME 的示例

alter table test4 modify id TIME;

insert into test4 values ('15:11:23'),('20:13'),('2 11:11'),('3 05'),('33');

结果以下

DATE

DATE 表示的类型有不少种，下面是 DATE 的几个示例

create table test5 (id date);

查看一下 test5 表

而后插入部分数据

insert into test5 values ('2020-06-13'),('20200613'),(20200613);

DATE 的表示通常不少种，以下所示 DATE 的全部形式

‘YYYY-MM-DD’
‘YYYYMMDD’
YYYYMMDD
‘YY-MM-DD’
‘YYMMDD’
YYMMDD

DATETIME

DATETIME 类型，包含日期和时间部分，可使用引用字符串或者数字，年份能够是 4 位也能够是 2 位。

下面是 DATETIME 的示例

create table test6 (id datetime);

insert into test4 values ('2020-06-13 11:11:11'),(20200613111111),('20200613111111'),(20200613080808);

TIMESTAMP

TIMESTAMP 类型和 DATETIME 类型的格式相同，存储 4 个字节（比DATETIME少），取值范围比 DATETIME 小。

下面来讲一下各个时间类型的使用场景

通常表示年月日，一般用 DATE 类型；
用来表示时分秒，一般用 TIME 表示；
年月日时分秒 ，一般用 DATETIME 来表示；
若是须要插入的是当前时间，一般使用 TIMESTAMP 来表示，TIMESTAMP 值返回后显示为 YYYY-MM-DD HH:MM:SS 格式的字符串，
若是只表示年份、则应该使用 YEAR，它比 DATE 类型须要更小的空间。

每种日期类型都有一个范围，若是超出这个范围，在默认的 SQLMode 下，系统会提示错误，并进行零值存储。

下面来解释一下 SQLMode 是什么

MySQL 中有一个环境变量是 sql_mode ，sql_mode 支持了 MySQL 的语法、数据校验，咱们能够经过下面这种方式来查看当前数据库使用的 sql_mode

select @@sql_mode;

一共有下面这几种模式

来源于 https://www.cnblogs.com/Zender/p/8270833.html

字符串类型

MySQL 提供了不少种字符串类型，下面是字符串类型的汇总

下面咱们对这些数据类型作一个详细的介绍

CHAR 和 VARCHAR 类型

CHAR 和 VARCHAR 类型很类似，致使不少同窗都会忽略他们之间的差异，首先他俩都是用来保存字符串的数据类型，他俩的主要区别在于存储方式不一样。CHAR 类型的长度就是你定义多少显示多少。占用 M 字节，好比你声明一个 CHAR(20) 的字符串类型，那么每一个字符串占用 20 字节，M 的取值范围时 0 - 255。VARCHAR 是可变长的字符串，范围是 0 - 65535，在字符串检索的时候，CHAR 会去掉尾部的空格，而 VARCHAR 会保留这些空格。下面是演示例子

create table vctest1 (vc varchar(6),ch char(6));

insert into vctest1 values("abc  ","abc  ");

select length(vc),length(ch) from vctest1;

结果以下

能够看到 vc 的字符串类型是 varchar ，长度是 5，ch 的字符串类型是 char，长度是 3。能够得出结论，varchar 会保留最后的空格，char 会去掉最后的空格。

BINARY 和 VARBINARY 类型

BINARY 和 VARBINARY 与 CHAR 和 VARCHAR 很是相似，不一样的是它们包含二进制字符串而不包含非二进制字符串。BINARY 与 VARBINARY 的最大长度和 CHAR 与 VARCHAR 是同样的，只不过他们是定义字节长度，而 CHAR 和 VARCHAR 对应的是字符长度。

BLOB 类型

BLOB 是一个二进制大对象，能够容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不一样。

TEXT 类型

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不一样，可根据实际状况选择。

ENUM 类型

ENUM 咱们在 Java 中常常会用到，它表示的是枚举类型。它的范围须要在建立表时显示指定，对 1 - 255 的枚举须要 1 个字节存储；对于 255 - 65535 的枚举须要 2 个字节存储。ENUM 会忽略大小写，在存储时都会转换为大写。

SET 类型

SET 类型和 ENUM 类型有两处不一样

存储方式

SET 对于每 0 - 8 个成员，分别占用 1 个字节，最大到 64 ，占用 8 个字节

Set 和 ENUM 除了存储以外，最主要的区别在于 Set 类型一次能够选取多个成员，而 ENUM 则只能选一个。

MySQL 运算符

MySQL 中有多种运算符，下面对 MySQL 运算符进行分类

算术运算符
比较运算符
逻辑运算符
位运算符

下面那咱们对各个运算符进行介绍

算术运算符

MySQL 支持的算术运算符包括加、减、乘、除和取余，这类运算符的使用频率比较高

下面是运算符的分类

运算符	做用
+	加法
-	减法
*	乘法
/, DIV	除法，返回商
%, MOD	除法，返回余数

下面简单描述了这些运算符的使用方法

+ 用于得到一个或多个值的和
- 用于从一个值减去另外一个值
* 用于两数相乘，获得两个或多个值的乘积
/ 用一个值除以另外一个值获得商
% 用于一个值除以另外一个值获得余数

在除法和取余须要注意一点，若是除数是 0 ，将是非法除数，返回结果为 NULL。

比较运算符

熟悉了运算符，下面来聊一聊比较运算符，使用 SELECT 语句进行查询时，MySQL 容许用户对表达式的两侧的操做数进行比较，比较结果为真，返回 1，比较结果为假，返回 0 ，比较结果不肯定返回 NULL。下面是全部的比较运算符

运算符	描述
=	等于
<> 或者是 !=	不等于
<=>	NULL 安全的等于，也就是 NULL-safe
<	小于
<=	小于等于
>	大于
>=	大于等于
BETWEEN	在指定范围内
IS NULL	是否为 NULL
IS NOT NULL	是否为 NULL
IN	存在于指定集合
LIKE	通配符匹配
REGEXP 或 RLIKE	正则表达式匹配

比较运算符能够用来比较数字、字符串或者表达式。数字做为浮点数进行比较，字符串以不区分大小写的方式进行比较。

= 号运算符，用于比较运算符两侧的操做数是否相等，若是相等则返回 1，若是不相等则返回 0 ，下面是具体的示例，NULL 不能用于比较，会直接返回 NULL

<> 号用于表示不等于，和 = 号相反，示例以下

<=> NULL-safe 的等于运算符，与 = 号最大的区别在于能够比较 NULL 值

< 号运算符，当左侧操做数小于右侧操做数时，返回值为 1，不然其返回值为 0。

和上面同理，只不过是知足 <= 的时候返回 1 ，不然 > 返回 0。这里我有个疑问，为何

select 'a' <= 'b';  /* 返回 1 */

/*而*/

select 'a' >= 'b'; /* 返回 0 呢*/

关于 > 和 >= 是同理
BETWEEN 运算符的使用格式是 a BETWEEN min AND max ，当 a 大于等于 min 而且小于等于 max 时，返回 1，不然返回 0 。操做数类型不一样的时候，会转换成相同的数据类型再进行处理。好比

IS NULL 和 IS NOT NULL 表示的是是否为 NULL，ISNULL 为 true 返回 1，不然返回 0 ；IS NOT NULL 同理

IN 这个比较操做符判断某个值是否在一个集合中，使用方式是 xxx in (value1,value2,value3)

LIKE 运算符的格式是 xxx LIKE %123%，好比以下

当 like 后面跟的是 123% 的时候， xxx 若是是 123 则返回 1，若是是 123xxx 也返回 1，若是是 12 或者 1 就返回 0 。123 是一个总体。

REGEX 运算符的格式是 s REGEXP str ，匹配时返回值为 1，不然返回 0 。

后面会详细介绍 regexp 的用法。

逻辑运算符

逻辑运算符指的就是布尔运算符，布尔运算符指返回真和假。MySQL 支持四种逻辑运算符

运算符	做用
NOT 或！	逻辑非
AND 或者是 &&	逻辑与
OR 或者是 \|\|	逻辑或
XOR	逻辑异或

下面分别来介绍一下

NOT 或者是 ! 表示的是逻辑非，当操做数为 0（假），则返回值为 1，不然值为 0。可是有一点除外，那就是 NOT NULL 的返回值为 NULL

AND 和 && 表示的是逻辑与的逻辑，当全部操做数为非零值而且不为 NULL 时，结果为 1，但凡有一个 0 则返回 0，操做数中有一个 null 则返回 null

OR 和 || 表示的是逻辑或，当两个操做数均为非 NULL 值时，若有任意一个操做数为非零值，则结果为 1，不然结果为 0。

XOR 表示逻辑异或，当任意一个操做数为 NULL 时，返回值为 NULL。对于非 NULL 的操做数，若是两个的逻辑真假值相异，则返回结果 1；不然返回 0。

位运算符

一据说位运算，就知道是和二进制有关的运算符了，位运算就是将给定的操做数转换为二进制后，对各个操做数的每一位都进行指定的逻辑运算，获得的二进制结果转换为十进制后就说是位运算的结果，下面是全部的位运算。

运算符	做用
&	位与
\|	位或
^	位异或
～	位取反
>>	位右移
<<	位左移

下面分别来演示一下这些例子

位与 指的就是按位与，把 & 双方转换为二进制再进行 & 操做

按位与是一个数值减少的操做

位或 指的就是按位或，把 | 双方转换为二进制再进行 | 操做

位或是一个数值增大的操做

位异或 指的就是对操做数的二进制位作异或操做

位取反 指的就是对操做数的二进制位作 NOT 操做，这里的操做数只能是一位，下面看一个经典的取反例子：对 1 作位取反，具体以下所示：

为何会有这种现象，由于在 MySQL 中，常量数字默认会以 8 个字节来显示，8 个字节就是 64 位，常量 1 的二进制表示 63 个 0，加 1 个 1 ，位取反后就是 63 个 1 加一个 0 ，转换为二进制后就是 18446744073709551614，咱们可使用 select bin() 查看一下

位右移 是对左操做数向右移动指定位数，例如 50 >> 3，就是对 50 取其二进制而后向右移三位，左边补上 0 ，转换结果以下

位左移 与位右移相反，是对左操做数向左移动指定位数，例如 20 << 2

MySQL 经常使用函数

下面咱们来了解一下 MySQL 函数，MySQL 函数也是咱们平常开发过程当中常用的，选用合适的函数可以提升咱们的开发效率，下面咱们就来一块儿认识一下这些函数

字符串函数

字符串函数是最经常使用的一种函数了，MySQL 也是支持不少种字符串函数，下面是 MySQL 支持的字符串函数表

函数	功能
LOWER	将字符串全部字符变为小写
UPPER	将字符串全部字符变为大写
CONCAT	进行字符串拼接
LEFT	返回字符串最左边的字符
RIGHT	返回字符串最右边的字符
INSERT	字符串替换
LTRIM	去掉字符串左边的空格
RTRIM	去掉字符串右边的空格
REPEAT	返回重复的结果
TRIM	去掉字符串行尾和行头的空格
SUBSTRING	返回指定的字符串
LPAD	用字符串对最左边进行填充
RPAD	用字符串对最右边进行填充
STRCMP	比较字符串 s1 和 s2
REPLACE	进行字符串替换

下面经过具体的示例演示一下每一个函数的用法

LOWER(str) 和 UPPER(str) 函数：用于转换大小写

CONCAT(s1,s2 … sn) ：把传入的参数拼接成一个字符串

上面把 c xu an 拼接成为了一个字符串，另外须要注意一点，任何和 NULL 进行字符串拼接的结果都是 NULL。

LEFT(str,x) 和 RIGHT(str,x) 函数：分别返回字符串最左边的 x 个字符和最右边的 x 个字符。若是第二个参数是 NULL，那么将不会返回任何字符串

INSERT(str,x,y,instr) ：将字符串 str 从指定 x 的位置开始，取 y 个长度的字串替换为 instr。

LTRIM(str) 和 RTRIM(str) 分别表示去掉字符串 str 左侧和右侧的空格

REPEAT(str,x) 函数：返回 str 重复 x 次的结果

TRIM(str) 函数：用于去掉目标字符串的空格

SUBSTRING(str,x,y) 函数：返回从字符串 str 中第 x 位置起 y 个字符长度的字符串

LPAD(str,n,pad) 和 RPAD(str,n,pad) 函数：用字符串 pad 对 str 左边和右边进行填充，直到长度为 n 个字符长度

STRCMP(s1,s2) 用于比较字符串 s1 和 s2 的 ASCII 值大小。若是 s1 < s2，则返回 -1；若是 s1 = s2 ，返回 0 ；若是 s1 > s2 ，返回 1。

REPLACE(str,a,b) : 用字符串 b 替换字符串 str 种全部出现的字符串 a

数值函数

MySQL 支持数值函数，这些函数可以处理不少数值运算。下面咱们一块儿来学习一下 MySQL 中的数值函数，下面是全部的数值函数

函数	功能
ABS	返回绝对值
CEIL	返回大于某个值的最大整数值
MOD	返回模
ROUND	四舍五入
FLOOR	返回小于某个值的最大整数值
TRUNCATE	返回数字截断小数的结果
RAND	返回 0 - 1 的随机值

下面咱们仍是以实践为主来聊一聊这些用法

ABS(x) 函数：返回 x 的绝对值

CEIL(x) 函数：返回大于 x 的整数

MOD(x,y)，对 x 和 y 进行取模操做

ROUND(x,y) 返回 x 四舍五入后保留 y 位小数的值；若是是整数，那么 y 位就是 0 ；若是不指定 y ，那么 y 默认也是 0 。

FLOOR(x) : 返回小于 x 的最大整数，用法与 CEIL 相反

TRUNCATE(x,y): 返回数字 x 截断为 y 位小数的结果， TRUNCATE 知识截断，并非四舍五入。

RAND() ：返回 0 到 1 的随机值

日期和时间函数

日期和时间函数也是 MySQL 中很是重要的一部分，下面咱们就来一块儿认识一下这些函数

函数	功能
NOW	返回当前的日期和时间
WEEK	返回一年中的第几周
YEAR	返回日期的年份
HOUR	返回小时值
MINUTE	返回分钟值
MONTHNAME	返回月份名
CURDATE	返回当前日期
CURTIME	返回当前时间
UNIX_TIMESTAMP	返回日期 UNIX 时间戳
DATE_FORMAT	返回按照字符串格式化的日期
FROM_UNIXTIME	返回 UNIX 时间戳的日期值
DATE_ADD	返回日期时间 + 上一个时间间隔
DATEDIFF	返回起始时间和结束时间之间的天数

下面结合示例来说解一下每一个函数的使用

NOW(): 返回当前的日期和时间

WEEK(DATE) 和 YEAR(DATE) ：前者返回的是一年中的第几周，后者返回的是给定日期的哪一年

HOUR(time) 和 MINUTE(time) : 返回给定时间的小时，后者返回给定时间的分钟

MONTHNAME(date) 函数：返回 date 的英文月份

CURDATE() 函数：返回当前日期，只包含年月日

CURTIME() 函数：返回当前时间，只包含时分秒

UNIX_TIMESTAMP(date) : 返回 UNIX 的时间戳

FROM_UNIXTIME(date) : 返回 UNIXTIME 时间戳的日期值，和 UNIX_TIMESTAMP 相反

DATE_FORMAT(date,fmt) 函数：按照字符串 fmt 对 date 进行格式化，格式化后按照指定日期格式显示

具体的日期格式能够参考这篇文章 https://blog.csdn.net/weixin_38703170/article/details/82177837

咱们演示一下将当前日期显示为年月日的这种形式，使用的日期格式是 %M %D %Y。

DATE_ADD(date, interval, expr type) 函数：返回与所给日期 date 相差 interval 时间段的日期

interval 表示间隔类型的关键字，expr 是表达式，这个表达式对应后面的类型，type 是间隔类型，MySQL 提供了 13 种时间间隔类型

表达式类型	描述	格式
YEAR	年	YY
MONTH	月	MM
DAY	日	DD
HOUR	小时	hh
MINUTE	分	mm
SECOND	秒	ss
YEAR_MONTH	年和月	YY-MM
DAY_HOUR	日和小时	DD hh
DAY_MINUTE	日和分钟	DD hh : mm
DAY_SECOND	日和秒	DD hh ：mm ：ss
HOUR_MINUTE	小时和分	hh:mm
HOUR_SECOND	小时和秒	hh:ss
MINUTE_SECOND	分钟和秒	mm:ss

DATE_DIFF(date1, date2) 用来计算两个日期之间相差的天数

查看离 2021 - 01 - 01 还有多少天

流程函数

流程函数也是很经常使用的一类函数，用户可使用这类函数在 SQL 中实现条件选择。这样作可以提升查询效率。下表列出了这些流程函数

函数	功能
IF(value,t f)	若是 value 是真，返回 t；不然返回 f
IFNULL(value1,value2)	若是 value1 不为 NULL，返回 value1，不然返回 value2。
CASE WHEN[value1] THEN[result1] …ELSE[default] END	若是 value1 是真，返回 result1，不然返回 default
CASE[expr] WHEN[value1] THEN [result1]… ELSE[default] END	若是 expr 等于 value1，返回 result1，不然返回 default

其余函数

除了咱们介绍过的字符串函数、日期和时间函数、流程函数，还有一些函数并不属于上面三类函数，它们是

函数	功能
VERSION	返回当前数据库的版本
DATABASE	返回当前数据库名
USER	返回当前登录用户名
PASSWORD	返回字符串的加密版本
MD5	返回 MD5 值
INET_ATON(IP)	返回 IP 地址的数字表示
INET_NTOA(num)	返回数字表明的 IP 地址

下面来看一下具体的使用

VERSION: 返回当前数据库版本

DATABASE: 返回当前的数据库名

USER : 返回当前登陆用户名

PASSWORD(str) : 返回字符串的加密版本，例如

MD5(str) 函数：返回字符串 str 的 MD5 值

INET_ATON(IP): 返回 IP 的网络字节序列

INET_NTOA(num)函数：返回网络字节序列表明的 IP 地址，与 INET_ATON 相对