自学数据库-MySQL

MySQL 是一个关系数据库中比较流行的中型数据库。因为是开源免费的（被甲骨文收购后开始收费，但是也有社区版可以免费使用），使用范围很广。MySQL 和大多数关系型数据库一样，使用 SQL 语言。

MySQL官网
MySQL最新版本官方下载

MySQL 的安装和卸载

基于 linux

使用包管理器安装

可以使用 包管理器 来安装，需注意的是，安装包为 mysql-server。

安装过程中会有提示选择时区，可以选择 Etc 然后自己选时区，例如中国是 GTM+8 东八区。

安装完成后，可以查看 mysql 版本

mysql --version

运行服务

安装完成后，可以运行 mysql 服务

service mysql start

如果运行服务或尝试重新运行时报错: warning: cannot change directory to /nonexistent: No such file or directory ，可以修改一下程序的主目录：

service mysql stop
usermod -d /var/lib/mysql/ mysql
service mysql start

初始化配置

首先需要执行初始化配置程序

mysql_secure_installation

如果报错：Error: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' 则是 mysql 服务没有运行成功，需要重新运行。

初始化配置程序执行后，会有提示

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No:

询问是否验证密码插件，它通过检查用户密码的强度来增强 MySQL Server 的安全性，允许用户仅设置强密码。按 Y 接受或按任意键跳过。

再接下来是设置 root 的密码。如果这一部输密码报错：... Failed! Error: SET PASSWORD has no significance for user 'root'@'localhost' as the authentication method used doesn't store authentication data in the MySQL server. Please consider using ALTER USER instead if you want to change authentication parameters. 则需要手动配置密码。（注：程序会卡到这里一直让输入密码，而密码又无法设置。所以这里需要断开连接重新登录）在 ubuntu 的 shell 里输入 mysql 登录到 mysql 数据库中(因为还没配置密码，所以不需要密码)。

# 查看一下 root 使用的密码插件
select host,user,plugin from mysql.user;
# 发现密码插件是 auth_socket，改成 caching_sha2_password，并设置密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY '123456';
# 更新一下
FLUSH PRIVILEGES;
# 再次查看
select host,user,plugin from mysql.user;

更改完成后， \q 退出数据库。重新执行初始化配置程序。这次会提示输入密码，因为刚才手动改了。然后到刚才设置 root 用户密码的步骤，变成了是否变更密码。

接下来就是继续之前的，询问是否删除匿名用户。

然后询问，是否禁止 root 用户从远程登录。

是否删除 test 数据库和对它的访问权限。

最后询问是否刷新授权表，并让初始化后的设定立即生效。

登录 sql

配置完成后，就可以登录了

mysql -uroot -p123456

允许远程访问

如果需要允许远程访问，需要修改配置文件。mysql 的默认配置文件是 /etc/mysql/mysql.conf.d/mysqld.cnf 修改此文件即可。

需要修改的地方有：

• bind-address = 0.0.0.0 允许所有 ip 进行访问
• port = 3306 设置访问端口

然后需要在 mysql 里设置允许远程访问的用户

CREATE USER 'root'@'%' IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

完成后重启服务就可以了。

基于 windows

MySQL 分为安装包安装和压缩包安装两种方式，区别在于压缩包安装的配置在安装包的安装过程中进行设置。

使用压缩包安装

压缩包直接解压到文件夹里即可。解压完成后，在根目录下建立配置文件 my.ini。

[mysqld]
# 设置访问主机和端口，默认127.0.0.1:3306
bind-address=127.0.0.1
port=3306
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 允许最大连接数
max_connections=20
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

然后设置环境变量，新建变量名 MYSQL_HOME ，值为解压目录。然后添加 %MYSQL_HOME%\bin 到 path 环境变量里:。

之后可以对 mysql 进行一些操作：

1. 对 mysql 进行初始化，产生一个临时密码

mysqld --initialize --user=mysql --console

2. 安装 mysql 服务

mysqld --install

3. 启动 mysql 服务

net start mysql

4. 登录 mysql，这里需要使用之前产生的临时密码

mysql -uroot -p

5. 修改 root 用户密码

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY '123456';

初始化后会在 mysql 目录下生成一个 data 文件夹，即之后使用的数据库所在。如果临时密码丢失了可以删除此文件夹重新初始化。

使用安装包进行安装

使用安装包安装比较简单，只是一些选项需要选择：

• Choosing a Setup Type 选择安装类型，使用 developer default 开发者默认
• Select Products and Features 选择产品和功能，将 mysql server / mysql workbench / mysql notiyier 三项添加到右侧

其他的选择 next 和 是 就可以了，接下来会自动进行安装。当安装完成后会进行配置。

• Type and Networking 类型和网络，使用 Development Computer 开发者电脑的默认配置即可
• Authentication Method 身份验证方法使用密码校验身份，然后输入数据库密码
• Windows Service 可以将mysql添加到服务中，设置服务名称并随系统启动

之后一路下路步，就会开始进行配置了。配置完成后会有连接测试，输入用户名和密码，点击 check ，会显示连接成功。之后没有什么需要配置的了，一路 next 就行。

卸载 mysql

1. 停止服务

net stop mysql

2. 卸载服务

SC delete mysql
或使用
mysqld -remove MySQL

3. 执行卸载程序(使用安装包安装)
4. 删除安装文件夹下所有文件
5. 删除隐藏文件夹 C:\ProgramData\MySQL 下所有文件
6. 删除注册表信息

HKEY_LOCAL_MACHINE/SYSTEM/ControlSet001/Services/Eventlog/Applications/MySQL
HKEY_LOCAL_MACHINE/SYSTEM/ControlSet002/Services/Eventlog/Applications/MySQL
HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/Eventlog/Applications/MySQL

7. 删除环境变量

MySQL 的图形管理工具

MySQL 的图形管理工具还是挺多的，推荐使用 Navicat、SQLyog、phpMyAdmin

• Navicat 是一款使用比较广泛的数据库管理工具，支持多种数据库，也有 MySQL 专用版。缺点在于需要收费。
• SQLyog 是一个很简单的轻量化的 MySQL 管理工具，对于中文的支持还是比较好的。SQLyog 也是需要注册收费的。
• phpMyAdmin 是一个基于 web 的 MySQL 管理工具。

MySQL 的基本语法

mysql 的基本语法是

命令 [参数1 [, 参数2…]] 子命令 [参数1 [, 参数2 …]] [子命令 [参数1 [, 参数2]]];

即一个命令附带若干参数，然后接此命令支持的若干子命令。最后会以分号结尾，表示执行此命令。

一条命令是可以分为多行书写的，且不需要连字符，当使用分号 (😉 后立即执行之前的命令。

MySQL 的基本操作 DDL

DDL (Data Definition Language) 是数据定义语言，该语言主要包含对数据库、表结构的常用操作，用来修改表结构。

对数据库的常用操作

SQL命令	功能	举例
show databases	查看所有数据库
create database	创建数据库	create database [if not exists] mydb1 [charset=utf8mb4]
use	切换(选择要操作的)数据库	use mydb1
drop database	删除数据库	drop database[if exists] mydb1
alter database character	修改数据库编码	alter database mydb1 character set utf8mb4
select database()	查询当前数据库
select version()	查询当前数据库软件版本

对表结构的常用操作

• 创建表

create table [if not exists] 表名(
	字段名1	类型[(宽度)]		[约束条件]	[comment '字段说明'],
	字段名2	类型[(宽度)]		[约束条件]	[comment '字段说明'],
	字段名3	类型[(宽度)]		[约束条件]	[comment '字段说明']
) [表的一些设置];

• 查看当前数据库所有表名称

show tables;

• 查看某个表的创建语句

show create table 表名;

• 查看表结构

desc 表名;

• 删除表(表结构)

drop table 表名;

• 删除表(只删除表数据,不删除表结构)

truncate table 表名;

• 添加列(字段)

alter table 表名 add 列名 类型(长度) [约束];

• 修改列名和类型

alter table 表名 change 旧列名 新列名 类型(长度)  [约束];	-- 用此方法必须修改列名
alter table 表名 modify 列名 数据类型 [约束];

• 删除字段

alter table 表名 drop 字段名;

• 修改表名

rename table 表名 to 新表名;

常用数据类型

数据类型大致有三大类：数值类型、日期和时间类型、字符串类型

数值类型

定义数值类型时，默认定义为有符号。如果在数据类型后添加 UNSIGNED 则声明为无符号数据类型。

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 byte	(-128,127)	(0,255)	小整数值
SMALLINT	2 bytes	(-32768,32767)	(0,65535)	大整数值
SMEDIUMINT	3 bytes	(-838608,838607)	(0,16777215)	大整数值
INT或INTEGER	4 bytes	(-2147483648,2147483647)	(0,4294967295)	大整数值
BIGINT	8 bytes	(-9223372036854775808,0223372036854775807)	(0,18446744073709551615)	极大整数值
FLOAT	4 bytes	-	-	单精度浮点数值
DOUBLE	8 bytes	-	-	双精度浮点数值
decimal		-	-	确定整数部分和小数部分位数

字符串类型

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过255个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BOLB	0-65535bytes	二进制形式的长文本数据
TEXT0-65535 bytes	长文本数据
LONGBLOB	0-4294967295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4294967295 bytes	极大文本数据

日期类型

类型	大小	范围	格式	用途
DATE	3	1000-01-01 / 9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’ / ‘838:59:59’	hh:mm:ss	时间值或持续时间
YEAR	1	1901 / 2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00 / 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00 / 2038-01-19 03:14:07	YYYYMMDD HHMMSS	时间戳

其他类型

除了以上常用类型外，还有一些常用的类型：

• enum : 枚举型，例： enum('1', '2', '3', '4')
• bit : 布尔型，值只有 0 和 1，相当于 false 和 true

MySQL 的约束

约束(constraint) 实际上就是表中数据的限制条件，其目的是为了保证表中的记录完整性和有效性，比如某些列不为空或不能重复等。

约束的分类有：

• 主键约束(primary key) PK
  主键约束写在字段定义之后，可以设置多字段联合主键。单主键也可以在定义字段时候定义。

字段名 字段类型 primary key,
或
primary key (字段名1, 字段名2 ...)

主键约束一般只在有需要的时候才设置，例如将 uuid 作为主键。在创建表时，如没有显式执行主键，则会自动添加字段 id 作为主键，并添加自增长约束，初始值为1，增长值为1。
这个主键自增长使用单独索引，例如主键已经使用到40，将主键38的记录删除，再次添加数据的主键就是从41开始。除非显式指定主键为38，否则38将空出来。如果有需要，例如删除了大段数据，希望索引连续，可以更改主键索引值：

alter table django_migrations auto_increment = 40;	# 从40开始进行索引自增长

需要注意的是，40之后如果有数据则会出现问题，需手动解决。

• 自增长约束(auto_increment)
  自增长约束在定义列名时进行声明。默认情况下初始值是1，每新增一条记录增加1。需注意的是一个表只能有一个字段使用自增长约束，且必须有唯一索引。自增长约束字段的类型只能是整数类型。
  在创建表的设置中可以设置自增长的初始值，或者以修改表方式添加自增长初始值。

create table 表名 （
	字段名 数据类型 primary key auto_increment,
	字段名 数据类型
)auto_increment=初始值;
或
alter table 表名 auto_increment=初始值;

• 非空约束(not null)
  非空约束可以在字段定义时定义，或以修改的方式添加。

字段名 数据类型 not null;
或
alter table 表名 modify 字段名 数据类型 not null;

• 唯一性约束(unique)

字段名 数据类型 unique;
或
alter table 表名 add constraint 约束名 unique 列名;

• 默认约束(default)

字段名 数据类型 default 默认值;
或
alter table 表名 modify 列名 类型 default 默认值;

• 零填充约束(zerofill)
  零填充约束使用时，当字段的值长度小于定义长度，则在该值前面补上0，默认为 int(10) unsigned

字段名 数据类型 zerofill

• 外键约束(foreign key) FK
  外键约束是一个特殊字段，经常与主键约束一起使用，用于联系两个相互关联的表。主键所在的表是主表（父表），外键所在的表是从表（子表）。
  定义外键时，必须注意：1.主表必须存在或是正在创建。2.主表必须定义了主键。3.主键不能包含空值，但外键可以。也就是说，只要外键的每个非空值在指定的主键中就行。4.外键中列的数目必须和主键中列的数目相同。5.外键中列的数据类型必须和主键中对应的数据类型相同。

-- 创建表时，在约束部分写入外键约束
[constraint <外键名>] foreign key 字段名 [, 字段名 ...] references <主表名> 主键列 [, 主键列 ...];
-- 通过修改表的方式添加外键约束
alter table 从表名 add constraint 外键名 foreign key 字段名 references 主表名 主键列;

• 无符号约束(unsigned)
  无符号约束指该字段是无负数的数字

MySQL 的基本操作 DML

DML(Data Manipulation Language) 数据操作语言，用来对数据库中表的数据记录进行操作，主要就是增、删、改。

数据插入

数据插入使用 insert ，有两种写法：

// 向某些列插入数据
insert into 表名 (列名1, 列名2, 列名3 ...) values (值1, 值2, 值3 ...);
// 向所有列插入数据
insert into 表名 values (值1, 值2, 值3 ...);

如果需要一次插入多行数据，可以这样使用：

insert into 表名 (列名1, 列名2, 列名3 ...) values (值1, 值2, 值3 ...), (值1, 值2, 值3 ...), (值1, 值2, 值3 ...) ...;

需注意的是，列名和包裹的括号不能省略

另外对于主键也需要输入，一般主键会设置自增长，这样值输入多少无所谓，mysql 会自动计算。

数据删除

数据删除使用 delete

// delete 只删除数据内容
delete from 表名 [where 条件];
// truncate 用于清空表，包含表结构信息
truncate table 表名
truncate 表名

数据修改

数据修改使用 update，如果不加条件，则是修改所有数据

update 表名 set 字段名=值, 字段名=值...;
update 表名 set 字段名=值, 字段名=值... where 条件;

MySQL 的基本操作 DQL

DQL(Data Quary Language) 就是查询操作，其语法格式为

基础语法

select
	[all|distinct]
	<目的列表达式1> [别名],
	<目的列表达式2> [别名]...
from
	<表名或视图名1> [别名],
	<表名或视图名2> [别名]...
[where <条件表达式>]
[group by <列名> [having <条件表达式>]]
[order by <列名> [asc|desc]]
[limit <数字或者列表>];

可以简化为

select *| 列名 from 表名 where 条件;

简单查询

• 查询表内所有信息

select * from 表名;

• 查询目标信息

select 列名1, 列名2 from 表名;

• 别名查询

select * from 表名 as 表别名;
select 列名 as 列别名 from 表名;

• 去重查询

select distinct 列名 from 表名;

• 运算查询(将查询结果进行运算后呈现)

select 列名1, 列名2, 列运算表达式 from 表名;

运算符

算术运算符

运算符	说明
+	加
-	减
*	乘
/ 或 DIV	除
% 或 MOD	模，求余

比较运算符

运算符	说明
=	等于
< 、<= 、>、>=	小于、小于等于、大于、大于等于
<=>	安全等于，两个操作码均为 NULL时，其所得值为1，而当一个操作码为NULL时，其所得值为0
<> 或 !=	不等于
IS NULL 或 ISNULL	判断一个值是否为NULL
IS NOT NULL	判断一个值是否不为NULL
LEAST	当有两个或多个参数时，返回最小值
GREATEST	当有两个或多个参数时，返回最大值
BETWEEN AND	判断一个值是否在两个值之间
IN	判断一个值是否属于列表中的任意一个
NOT IN	判断一个值是否不属于列表
LIKE	通配符匹配
ERGEXP	正则表达式匹配

通配符

通配符	说明
%	匹配任意字符
_	匹配单个字符

逻辑运算符

运算符	说明
NOT 或 !	逻辑非
AND 或 &&	逻辑与
OR 或 ||	逻辑或
XOR	逻辑异或

位运算符

运算符	说明
|	按位或
&	按位与
^	按位异或
<<	按位左移
>>	按位右移
~	按位取反

条件查询

通过运算符，可以将需要查询的条件写在查询语句的 where 后面，以获取各种需要的数据。

排序

可以使用 SQL 语法中的 order by 来设定根据哪个字段进行排序，asc 代表升序，desc 代表降序，默认升序。

select
	字段1, 字段2, ...
from 表
order by 字段1 [asc|desc], 字段2 [asc|desc] ...;

聚合查询

聚合查询是对某一列数据进行处理，返回一个单一的值。需注意的是聚合函数会忽略空值。

聚合函数	说明
count()	统计指定列不为NULL的记录行数，可以使用 * 不指定列
sum()	计算指定列的数值和，如果类型不是数值类型，则结果为0
max()	计算指定列的最大值，如果类型是字符串类型，那么使用字符串排序运算
min()	计算指定列的最小值，如果类型是字符串类型，那么使用字符串排序运算
avg()	计算指定列的平均值，如果类型不是数值类型，则结果为0

select 字段1, 字段2, 聚合函数 from 表名 where 查询条件

分组查询

分组查询是指使用 group by 对查询信息进行分类

select 字段1, 字段2, ... from 表名 group by 分组字段 having 分组条件;
-- having 是分组筛选条件，类似于 where。符合 having 条件的组才返回

需注意的是，分组字段可以是多个字段。如果是多个字段，则必须所有字段的值相同才分到同一组。

分页查询

如果查询结果太多，则可以使用 limit 进行分页

-- 显示前 n 条查询结果
select 字段1, 字段2, ... from 表名 limit n;
-- 分页显示，从第 m 条索引开始，查询 n 条数据，注意 m 是索引号，即从 0 开始计算。
-- 如果按页查询，m 的计算方式为 (页码 - 1) * 每页大小，n 就是每页大小
select 字段1, 字段2, ... from 表名 limit m,n;	

插入查询

有时候需要将查询结果导入到另一张表，可以使用 INSERT INTO SELECT 语句

-- 将表1的查询结果(也可以是整个表)插入到表2
insert into 表2名[(字段1, 字段2, ...)] select 字段1, 字段2, ... from 表1名;

需要注意的是，插入表必须存在，相应字段及字段类型必须一致。

多段查询

如果需要将查询结果作为一张表，进行二次(或多次)查询，有2种方法：

1. 创建临时表

select * from (select 字段1, 字段2, ... from 表名 where 查询条件) as 临时表名;

2. 使用子查询

select 字段1, 字段2, ... from 表名
where 查询条件 in (
	select 字段1, 字段2, ... from (
		select 字段1, 字段2, ... from 表名 where 查询条件
));

select 字段1, 字段2, ... from (
	select 字段1, 字段2, ... from 表名 where 查询条件
) where 查询条件;

MySQL 的基本操作 DCL

DCL (Data Contral Language) 是数据控制语言，主要是 grant(授权) 和 revoke(解除授权) 的相关操作。

DCL 通常用在多用户操作数据库中。例如数据库由多个用户使用，每个用户使用不同的表，相互不能影响。

创建用户

创建用户 @ 后是指定登录方式，例如 localhost，固定IP，或使用 % 表示任意主机。

create user 'hellokitty'@'%' identified by '123123';

删除用户

drop user 'hellokitty'@'%';

授权

-- 将 db_1 数据库下所有表、视图、函数等对象的所有权限授权
grant all privileges on db_1.* to 'hellokitty'@'%';

解除授权

-- 将增、删、改 db_1 数据库中所有对象中的数据的权限收回
revoke insert, delete, update on db_1.* from 'hellokitty'@'%';

访问控制

数据库 mysql 下 user 表中可以对各个账号进行访问控制

# 查看各用户
select host,user from mysql.user;
# 设置 root 用户所有主机均可以使用其进行访问
update user set host = '%' where user = 'root';
# 刷新授权
FLUSH PRIVILEGES;

MySQL 的多表操作

关系数据库的关系，就是指多表之间的关联情况。一般可以通过关系查询和连接查询来跨表查询。

关系查询

关系数据库中，多个表可以通过一些相同的字段或关系进行关联。这些关系主要分为一对一关系、一对多/多对一关系、多对多关系。关系的确定由主键和外键负责。

一对一关系

两个表中，一个表添加唯一外键指向另一方的主键，确保双方数据能够一对一的相互对应，就是一对一关系。实际运用中一对一关系很少见，一般遇到时最好是合并表。

一对多/多对一关系

在两个表中，数据多的一方建立外键，指向一的一方的主键，就是一对多或多对一关系。简单说就是数据少的表中数据都能和另一个表中若干条数据进行对应关联。

多对多关系

多对多的关系需要借助第三张表。通过中间表能够将多对多的关系拆成一对多的关系。中间表至少要有两个外键，分别指向原来两个表的主键。

关系表数据操作

在外键约束下，多个关系表数据操作需要注意的有：

• 从表的外键列值必须依赖主表的主键列，即外键列中的值除了空值必须包含在主键列中。所以通常添加数据时先在主表添加数据，然后再在从表添加数据。
• 还是因为依赖的问题，从表的数据是可以随便删除的，但是主表的数据如果有依赖关系则不能删除。

取消多表关系

可以通过删除外键约束取消多表的关系

alter table <从表名> drop froeign key <外键约束名>;

联合查询(连接查询)

多表查询就是同时查询两个或连个以上的表。因为使用 外键 会影响数据库的速度，很多大项目不允许使用外键，所以查询方式就是使用连接查询。

交叉连接查询

交叉连接查询会产生笛卡尔积（即将两张表的数据相乘,会产生大量冗余），所以一般不使用。

select * from 表A, 表B;

内连接查询

内连接查询结果是两表的交集，即属于表A且属于表B。

select * from A inner join B on 条件;

外连接查询

外连接查询又分为左外连接、右外连接、满外连接三种情况。对于左右外连接查询，如果没有满足查询的条件并不是不返回而是根据左或右表将不满足条件的值全部填为 NULL。

• 左外连接的查询结果是表A加上两表的交集，其实就是表A的全部，在表B中查询不到的数据为 NULL，表B中有表A中没有的不返回

select * from A left outer join B on 条件;

• 右外连接的查询结果是表B加上两表的交集，其实就是表B的全部，在表A中查询不到的数据为 NULL，表A中有表B没有的不返回

select * from A right outer join B on 条件;

• 满外连接查询的结果是两表的并集，即属于表A或属于表B。
  需注意的是，有些数据库对 full join 支持不好，一般会使用 union 来达到满外连接的目的。

-- 使用 full join 满外连接查询
select * form A full outer join B on 条件;
-- 使用 union 联合左外连接和右外连接，达到查询并集的目的
-- union 是将两个查询结果上下拼接并去重，union all 则不进行去重操作
select * from A left outer join B on 条件
union [all]
select * from A right outer join B on 条件;

子查询

子查询就是 select 的嵌套，即在查询结果的基础上再次进行查询。大部分子查询可以使用关联查询替代，而关联查询的效率会高出子查询。所以不推荐使用子查询

-- 查询条件基于另一个查询语句，例如
select * from 表 where 字段 = (select max(字段) from 表);
-- ALL|ANY 关键字格式
select * from 表 where 字段 运算符 all|any (查询条件); 
-- IN 关键字格式
select * from 表 where 字段 in (查询条件);
-- EXISTS 关键字格式
select * from 表 where exists (查询条件);

子查询除了使用像是上例中的等号等运算符外，还可以使用一些关键字，达到更巧妙的查询目的。

关键字	说明	备注
ALL	all 后添加查询条件，表示需要同全部查询子集进行比较并满足条件	where c > all(查询语句) 等价于 where c > 查询结果1 and c > 查询结果2
ANY(SOME)	any 后添加查询条件,表示同全部查询子集进行比较,满足任一即可	ANY 类似于 ALL ,区别在于 ALL 对于结果判定是且的关系,而ANY是或
IN	in 后添加查询条件,表示包含在查询子集之中	在 in 之前加上 not 可以将条件反过来
EXISTS	exists 后添加查询条件,如果查询子集中有数据结果则为 true,执行外层查询,否则相反	exists 关键字的运算效率高于其他关键字

表自关联

将一张表自己和自己关联起来，当成多张表使用就是自关联。注意自关联时必须给表起别名。

select 字段列表 from 表1 as 别名1 join 表1 as 别名2 on 条件;

表自关联常用在树状图的数据库记录上。一般树状图的数据记录有3个字段：编号、节点名称、上级编号。如果通过这种表来查询节点和节点上级的关系，则可以使用自关联。

select * from 表1 as a left join 表1 as b on a.parent_id = b.id

多张表的写法

多张表进行联合查询，在写法上主要就是使用联合关键字，将各表的联合字段关联起来。

SELECT  A1,A2,C1,C2,D1,D2      --展示A表中的A1\A2字段和C表中的C1\C2
FROM  B                         --中间表
INNER JOIN A ON A.A1 = B.B1   --A表中的与B表中相同的字段
INNER JOIN C ON C.C1 = B.B1    --C表中的与B表中相同的字段
INNER JOIN A ON A.A3 = D.D3		-- A表中的与D表中相同的字段
where    xxxxx;     ---按需求加筛选条件

MySQL 的函数

MySQL 的函数主要分为六类：聚合函数、数学函数、字符串函数、日期函数、控制流函数、窗口函数

聚合函数

常用的聚合函数除了聚合查询中的5种(count、sum、max、min、avg)外，还有 group_concat()。

此函数首先根据指定的列进行分组，并且用分隔符分割，将同一分组中的值连接起来，返回一个结果。即将符合条件的分组列转为行返回。

-- 在此示例中，group_concat() 函数返回值组成了一个字段,字段内容是根据函数参数的字段数据得出
-- 根据分组信息，所有的函数结果根据分隔符拼接为字段内的数据信息,默认分隔符是逗号
select 字段名,group_concat([distinct] 字段名 [order by 排序字段 asc|desc] [sepatator '分隔符']) from 表名 group by 字段名;

-- 例如，根据性别将人名聚合分组
select gender, group_concat(name) from person group by gender;
-- 输出内容为：
-- 男    | xxx,xxx,xxx,xxx,xxx
-- 女    | xxx,xxx,xxx

数学函数

常用的数学函数有

函数名	描述	备注
abs(x)	返回x的绝对值	select abs(-1) – 返回1
ceil(x)	返回大于或等于x的最小整数	select ceil(1.5) – 返回2
floor(x)	返回小于或等于x的最大整数	select floor(1.5) – 返回1
greatest(expr1,expr2,…)	返回列表中最大值	类似于 max()
least(expr1,expr2,…)	返回列表中的最小值	类似于 min()
max(expression)	返回字段中的最大值	类似于greatest()
min(expression)	返回字段中的最小值	类似于least()
mod(x,y)	取余数	select mod(5,2) – 返回1
pi()	返回圆周率	select pi() – 返回3.141593
power(x,y)	求x的y次方	select power(2,3) – 返回8
rand()	返回0-1之间的一个随机数	select rand() – 返回随机数
round(x)	返回四舍五入后的整数	select round(1.2345) – 返回1
round(x,y)	返回x四舍五入后保留y位的小数	select round(1.2345,3) --返回1.235
truncate(x,y)	返回x不进行四舍五入保留y位小数的值	select truncate(1.23456,3) – 返回1.234

字符串函数

函数名	描述
lenth(s)	返回字符串的字节长度
char_length(s) / character_length(s)	返回字符串的字符数量
concat(s1,s2,…sn)	将多个字符串合并为一个字符串
concat_ws(x,s1,s2,…sn)	同concat()函数，区别在于使用x分隔，x也可以是分隔符
field(s,s1,s2,…)	返回字符串s在字符串列表(s1,s2,…)中第一次出现的位置
ltrim(s) / rtrim(s) / trim(s)	返回去掉开始/结尾/两端处空格的字符串s
mid(s,n,len) / substring(s,n,len)	从字符串s的n位置处截取长度为len的字符串并返回
position(s1 in s)	从字符串s中获取s1第一次出现的位置
replace(s,s1,s2)	将字符串s2替代字符串s中的字符串s1，并返回s
reverse(s)	将字符串s逆序并返回
right(s,n)	返回字符串s的右数n个字符
strcmp(s1,s2)	比较两个字符串，如果 s1 = s2 则返回0，s1 > s2 返回1，s1 < s2 返回 -1
ucase(s) / upper(s)	字符串转大写
lcase(s) / lower(s)	字符串转小写

日期函数

函数名	描述
unix_timestamp()	返回时间戳
unix_timestamp(date_string)	将指定日期转为时间戳
from_unixtime(unixtime[,string format])	将时间戳转为指定格式日期
curdate() / current_date()	返回当前日期
curtime() / current_time()	返回当前时间
current_timestamp()	返回当前日期和时间
date(expr)	从表达式中提取日期值
datediff(d1,d2)	计算两个日期之间间隔天数
timediff(t1,t2)	计算两个时间之间间隔时间
date_format(d,f)	按表达式f的要求格式化日期d
str_to_date(s,f)	将字符串s按照格式f转化为时间
date_sub(date,expr)	从日期data减去指定的时间间隔
extract(type from d)	从日期d中获取type指定的类型的值
last_day(d)	返回指定日期d的那月中的最后一天
makedate(year,day-of-year)	基于年份year和所在年中的天数序号 day-of-year 返回一个日期
year(d) / month(d) / day(d)	返回日期中的年/月/日
hour(t) / minute(t) / second(t)	返回时间中的时/分/秒
quarter(d)	返回日期d是当年的第几季度
monthname(d)	返回日期d的月份英文名称
dayname(d)	返回日期d的星期英文名称
dayofweek(d)	返回日期d是星期的第几天，以周日为每星期的第一天
dayofyear(d)	返回日期d是当年的第几天
week(d) / weekofyear(d)	计算日期d是当年的第几个星期，范围0-53
weekday(d)	计算日期d是星期几，0为周一
now()	返回当前日期和时间

控制流函数

函数名	描述
if(expr,v1,v2)	如果表达式expr成立返回v1，否则返回v2
ifnull(v1,v2)	如果v1不为空则返回v1，否则返回v2
isnull(expr)	返回判断表达式是否为null
nullif(expr1,expr2)	比较两个表达式，如果相等返回null，否则返回expr1

流控制函数还包括 case when 语句。case 是函数开始，end 表示结束。如果某一条件成立，执行其结果，后面其他的就不执行了。

select case expression
	when condition1 then result1
	when condition2 then result2
	...
	else result
end

窗口函数

窗口函数又称开窗函数，是新版中增加的一大特点。其语法结构如下：

窗口函数名 (expr) over （
	partition by ...
	order by ...
	frame_clause
)

其中参数 expr 有些函数不需要。over子句包含三个选项：

• 分区 (partition by)
  分区选项用于将数据行拆分成多个分区(组)，它的作用类似于 group by 分组。
• 排序(order by)
• 窗口大小(frame_clause)
  此选项用于在当前分区内指定一个计算窗口，也就是一个与当前行相关的数据子集。

MySQL 的视图

视图是一个虚拟的表，其本质是根据 SQL 语句获取动态的数据集，并为其命名。使用时只需使用视图名，可以当作表来用。

创建视图

创建视图的语法为：

create [ or replace]  [algorithm = { undefined | merge | temptable }]
view view_name [(column_list)]
as select_statement
[with [cascaded | local] check option];

参数说明：

• algorithm :可选项，表示视图选择的算法
• view_name :要创建的视图名称
• column_list :可选项，指定视图的字段，默认情况下与 select 语句查询的字段相同
• select_statement :一个完整的查询语句，将查询记录导入到视图中
• [with [cascaded | local] check option] :可选项，表示更新视图时要保证在该视图的权限范围内

修改视图

alter view 视图名 as 查询语句;

更新视图

视图中的行和基表中的行之间如果具有一对一的关系，则可以通过更新视图来更新基表。如果视图包含以下结构中的任何一种，那么它就不可更新：

• 聚合函数
• DISTINCT
• GROUP BY
• HAVING
• UNION 或 UNION ALL
• 位于选择列表中的子查询
• JOIN
• FROM子句中的不可更新视图
• WHERE子句中的子查询，引用FROM子句中的表
• 仅引用文字值(在该种情况下，没有要更新的基本表)

需要注意的是，视图虽然可以更新数据，但是有很多限制。一般情况下，最好将视图作为查询数据的虚拟表，而不要通过视图更新数据。

重命名、删除视图

-- 重命名视图
rename table 视图名 to 新视图名;
-- 删除视图
drop view [if exists] 视图名;

删除视图时，只删除视图的定义，不删除数据。

MySQL 的存储过程

存储过程简单的说就是一组语句集，可以实现一些比较复杂的逻辑功能，类似于函数、方法，是SQL语言层面的代码封装与重用。

创建存储过程

-- 重新定义定界符为 $$ (因为存储过程中间需要使用到分号)
delimiter $$
-- 创建存储过程
create procedure 过程名(参数 类型[,参数 类型][out 参数 类型])	-- out 表示参数是输出参数，因为存储过程没有返回值，要输出时使用输出参数
begin	-- 过程开始
	-- sql 语句，例如: 定义参数(deptno int, out avgsal float)
	select avg(sal) into avgsal from table_1 where don=deptno;
end$$
-- 将定界符还原回 ;
delimiter ;

调用存储过程

call 存储过程名(参数, 参数);

删除存储过程

drop procedure 存储过程名;

因为有输出参数，这个参数一般是一个临时的变量。sql 的临时变量为 @ + 变量名，例如 @a。例如上例，如果使用 @a 作为输出参数，则可以使用 select @a; 来获取输出的数据。

触发器

触发器是一种特殊的存储过程，当执行 DML 操作时能够自动触发触发器的执行，无需手动调用。触发器的这种特性可以协助应用在数据库端确保数据的完整性、日志记录、数据校验等操作。但是使用触发器有可能导致锁表现象，实际开发中应该尽量避免使用触发器。

索引

索引是通过某种算法，构建出一个数据模型，用于快速找出在某个列中有特定值的行，能够节省大量时间和资源。通常会在经常用于查询筛选条件的列上建立索引。因为索引会使用额外的存储空间而且会让增删变得更慢(因为要更新索引)，所以不能够滥用索引。

create index 索引名 on 表名(列名);
drop index 索引名 on 表名;

事务

将多个增删改的操作合成一个不可分割的原子操作，即要么全部完成，要么全都不完成。

begin;
sql 语句;
sql 语句;
sql 语句;
-- 在提交前，数据实际上是没有变动的
-- 此时如果没有出错，则可以提交，确认数据变动
commit;
-- 如果有错误、失败等，可以回滚到之前的状态
rollback;	

注：commit 后无法 rollback。

MySQL 的日志

mysql 的日志除了连接、登录等信息，还可以记录客户端发送的操作、查询指令。

1. 首先，查看是否已经开启实时SQL语句记录。如果 general_log 的值是 ON 是开启了，OFF 是没开启。

SHOW VARIABLES LIKE "general_log%";

2. 临时开启实时日志，并设置文件保存地址。如果需要永久开启，则需要更改设置文件(会产生大量数据，不推荐使用)。

SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/general_log.log';

3. 查看日志文件。