官网：https://www.mysql.com/
文档：https://dev.mysql.com/doc/refman/8.0/en/
历史：芬兰人Michael Monty Widenius，1996年创始MySQL。之后合伙成立了MySQL AB公司，2008年被Sun公司收购
MySQL社区版：免费，官方不提供技术支持
版本号：MySQL 5.7 (GA)，5.7是发行序列号，GA（Current Generally Available Realease）表示稳定版
Windows 安装
先安装.NET Framework
在线安装版：mysql-installer-web-community.msi
离线安装版：mysql-installer-community.msi
官方客户端
命令行客户端：MySQL Command Line Client，安装时默认带有
图形化客户端：MySQL Workbench，安装时选上安装；
Workbench的连接方法选Standard TCP/IP Over SSH，可以在mysql端口不对外开放的情况下连接mysql；
Workbench的通讯密钥保存于.ssh/known_hosts，服务器端有变更时，需要清理.ssh/known_hosts
第三方客户端：Navicat，可以对比不同库的表结构，并进行表结构同步
Linux 安装
yum源下载地址：https://dev.mysql.com/downloads/repo/yum/ 选择好操作系统版本
yum源：mysql-community-release.noarch.rpm
添加yum源：上传到服务器，rpm -Uvh mysql-community-release.noarch.rpm，如果要添加的源比现有源版本低，需要加--oldpackage
查看源列表：yum repolist enabled
安装客户端：yum -y install mysql-community-client，-y表示忽略询问
安装服务端：yum -y install mysql-community-server
解决安装失败
问题：依赖mariadb-libs、缺失libsasl2.so.2
分析：libsasl2.so.2是一个动态链接库，查看类似文件 ls /usr/lib64 | grep libsasl，发现存在 libsasl2.so.3，不同的mysql-server.rpm依赖不同的libsasl.so
解决：1、尝试别的版本的yum源；2、修改yum配置文件
yum配置文件：/etc/yum.repos.d/mysql-community.repo
修改rpm下载地址：baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/6/basearch/
mysqld
启动mysql：service mysqld start，另有stop\restart\status
查看初始密码：grep 'temporary password' /var/log/mysqld.log
本机客户端登录：mysql -u root -p
修改自己的密码：set password = 'pass';
远程客户端登录：mysql -h 目标IP -P 目标端口 -u 用户名 -p
修改密码：mysqladmin -u root -p password
层次结构：database - table
查看当前配置：show variables;
修改和查看时区：set global time_zone = '+8:00';set time_zone = '+8:00';flush privileges;show variables like "%time_zone%"; # 已有连接得重连才能生效
临时更改配置：set sql_log_bin=0; -- 立即生效，重启后失效
配置文件：/etc/my.cnf，配置文件里只有少量配置，这里的配置可以覆盖show variables里显示的配置，重启生效
仓库 database（类似于数据库理论里的schema）
查看database：show databases
创建database：create database db1
查看创建信息：show create database db1
删除database：drop database db1
切换当前database：use db1
查看存储引擎：show engines，默认采用InnoDB存储引擎
InnoDB引擎优点：支持事务 和 外键
格式化显示：语句后加 \G
表
查看表：show tables
查看表结构：describe table1 或者 desc table1 或 show columns from table1;
查看表创建信息：show create table table1
修改表名：alter table table1 rename to table2
预判删除表：drop table if exist table1
创建表时指定存储引擎：create table table1() engine=InnoDB
创建表时指定字符集：create table table1() default chartset=utf8
表定义
联合主键：primary key(id, name)
默认值：default 111
自动加1：auto_increment，从1开始
0填充：int(4) zerofill，不够4位以0补齐
修改表
修改字段类型：modify column1 int
修改字段名：change column1 column2 int
修改字段位次：modify column1 int first（第一） 或 modify column1 int after column2（之后）
删除外键：drop foreign key column1
数据类型
整数：tinyint（1字节，正负127）、smallint（2字节，正负3200）、mediumint（3字节，正负830万）、int（4字节，正负21亿）、bigint（8字节，正负92亿亿）
显示长度：int(11) 表示查询时，不满11位的数字，数字前用0补齐到11位
无符号：int unsigned，使用无符号类型，正数取值范围可翻倍
浮点数：float（4字节，正负3.410的38次方）、double（8字节，正负2.210的308次方）
定点数：decimal(m,n)，用字符串来存储数字，m为有效位数，n为小数位数，m+2字节
日期：year（1字节，YYYY，1901~2155），date（3字节，YYYY-MM-DD）
时间：time（3字节，HH:MM:SS）、datetime（8字节，YYYY-MM-DD HH:MM:SS）、timestamp（4字节，YYYY-MM-DD HH:MM:SS,1970~2038）
定长字符串：char(m)，m字节，m<=255
变长字符串varchar(m)：存储时会加上结束字符，m<=65535；5.0版本以上，varchar(100)，无论是数字、字母还是汉字，都可以存放100个
变长字符串text：tinytext（255个字符），text（6500个字符），mediumtext（一千六百万个字符），longtext（42亿个字符）
枚举：enum('value1','value2')，用一个数字表示一个值，(可取值个数 / 256)个字节
枚举集合：set('value1','value2','value3')，用一位表示一个值是否被选取，(可取值个数 / 8)个字节
位数组：bit(m)
字节数组binary：binary(m)（定长）、varbinary(m)（不定长）
字节数组blob：tinyblob(m)（m<=255）、blob(m)（m<=6500）、mediumblob(m)（m<=一千六百万）、longblob(m)（m<=42亿）
mySql函数
select、values、where子句中可以使用函数
数学函数：pow，sin等
字符串函数：length，lower等
日期函数：current_date、utc_date得到 YYYY-MM-DD，另有季度、月份、星期函数
时间函数：localtime、now、sysdate、current_timestamp、utc_time都得到 YYYY-MM-DD HH:MM:SS
时间戳函数：unix_timestamp、from_unixtime
条件判断函数：if(1<3, 1, 0)、ifnull、case
系统信息函数：版本 version()、连接数 connection_id()、当前数据库 database()、当前用户user()、字符串所使用的字符集charset(str)
查看连接信息：show full processlist
单向加密函数：password(str)、md5(str)
双向加密函数：加密 encode(str, 密钥)，解密decode(str, 密钥)
数据转换：字符集转换convert(str, using latin1)，类型转换convert(x, type)
查询数据
限制查询结果数量：limit 0,10、limit 10 -- 从0开始查10条
like通配符：% 匹配任意长度字符串，包括0长度、- 匹配一个字符
组查询 获取非组共有字段的值：select group_concat(Sname) as names
别名：select字句、from字句中 用 as 来取别名
-- 内连接（非外连接的连接）的两种写法
select t1.*,t2.* from t1,t2 where t1.id=t2.id;
select t1.*,t2.* from t1 inner join t2 on t1.id=t2.id; -- 性能好
-- 连接更新
update t1 inner join t2 on t1.id=t2.id set t1.name=t2.name;
-- 结合连接查询、子查询、分组查询，删除重复项
delete from t inner join (select id,name from t group by name having count(name) >=2) as multi on t.name = multi.name where t.id != multi.id; --count(name)不包含name为null的行，count(*)则包括
-- 交叉连接，即笛卡尔积
select * from table1 cross join table2;
并集不去重：union all -- 效率比union高
全连接：即左外连接 和 右外连接的合集，左外连接 union 右外连接
正则表达式查询：where Sname regexp '正则表达式'，匹配中文 where hex(name) regexp 'e[4-9][0-9a-f]{4}'
插入与更新
插入：INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN );
更新：UPDATE table_name SET field1=new-value1, field2=new-value2
索引
InnoDB存储引擎支持B+树索引
创建表时指定索引：
create table table1 (
index index1(column1)
)
查看查询语句所使用的索引：explain select * from table1;
字符串字段前缀（局部）索引： index index1(name(10)) -- 取name的前10（索引长度）个字节作为索引值
字符串字段全文索引：
create table table1 (
fulltext key index1(info) -- key 与 index 同意
) engine=MyISAM -- 只有 MyISAM 存储引擎支持全文索引，但不支持事务
查看表的索引：show index from table1
删除索引：alter table1 drop index index1 或 drop index index1 on table1
查询有索引字段 但索引不起作用的情况：
1、like '%xx' ，%在最前面
2、查询中使用的字段 是 多字段索引的中的字段，但不是多字段索引的第一个字段
3、or 前后的字段 不全是索引字段
存储过程和存储函数
定义存储过程
create procedure pro1 (in param1 int, out param2 int) -- param1为入参，类型是int，param2为出参
begin
declare age int default 30; -- 定义变量，指明类型和初始值
set age = param1; -- 给变量赋值
select count(*) into param2 from table1; -- 用单列单行给出参（变量）赋值
end;
call pro1(22, @count); -- 调用存储过程
select @count; -- 查看存储过程的出参
show procedure status like 'pattern'; -- 查看存储过程
show create procedure pro1; -- 查看存储过程
select * from information_schema.routines where routine_name=pro1; -- 存储过程存在这个表中
drop procedure pro1; -- 删除存储过程
定义存储函数
create function fun1 (in param1 int, out param2 int)
returns int -- 函数返回类型
return (select Sage from table1); -- 返回单列单行
end;
select fun1(); -- 调用存储函数的方法 与 调用内置函数的方法相同
show function status like 'pattern'; -- 查看存储函数
show create function fun1 ; -- 查看存储函数
select * from information_schema.routines where routine_name=pro1; -- 存储函数存在这个表中
drop function fun1; -- 删除存储函数
存储过程内的sql状态处理程序
declare continue handler for sqlstate '42S02' set @info='error'; -- 捕获状态码42S02，并给出参@info赋值
declare exit handler for 1146 set @info='error'; -- 捕获错误码1146，exit表示处理后退出，另有continue表示处理后继续执行
declare con1 condition for 1146; -- 定义一个条件，相当于状态码 或 错误码的别名
-- 内置条件：sqlwarning，01开头的状态码；not found，02开头的状态码；sqlexception，01和02以外开头的状态码
declare continue handler for con1 set @info='error'; -- 使用条件
存储函数内的流程控制
if age = 10
then select '10';
else select '0';
end if;
case age
when 10 then select '10';
when 20 then select '20';
else select '0';
end case;
loop1: loop -- loop1是循环的名称
set id = id + 1;
if id >= 10 then leave loop1; -- 离开循环
elseif id<5 then iterate loop1; -- 回到循环体开头
end if;
end loop loop1;
repeat
set id = id + 1;
until id >= 10
end repeat;
while id < 10 do
set id = id + 1;
end while;
视图
查看视图：describe view1; 或 show table status like 'view1'; 或 show create view view1;
视图信息：存储在 information_schema.views中
修改视图：create or replace view view1 ··· 或 alter view view1 ···
触发器
查看触发器：show triggers;
触发器信息：存储在 information_schema.triggers中
用户和权限相关表
权限规则：以下表对同一对象设置的权限，只要有一个表的设置是有权限，那么就有权限
mysql.user表定义用户对所有仓库的权限，列分类：
用户列：用户名user，允许的远程客户端主机host
权限列
安全列
资源列：一小时查询次数max_questions，一小时更新次数max_updates，一小时连接次数max_connections，同时连接数max_user_connnections
user 加上 host 才唯一确定一个用户
mysql.db表定义用户对单个仓库的权限，列分类：
用户列：user，host
对象列：仓库名 db
权限列
要设置某个用户只对单个仓库有权限，需要在user表中对应权限列设为N，db表中设为Y
mysql.tables_priv表定义用户对单个表的权限，列分类：
用户列：user，host
对象列：db，table_name
权限列：表权限table_priv（是个set）、列权限column_priv（是个set）
mysql.columns_priv表定义用户对单个列的权限，列分类：
用户列：user，host
对象列：db，table_name，column_name
权限列：column_priv
mysql.procs_priv表定义用户对存储过程和存储函数的权限
用户管理与授权
可以通过编辑mysql仓库下的表 或 使用grant语句 来实现
select currrent_user(); -- 查看当前用户
create user 'user1'@'ip' identified by 'pass'; -- 创建用户，指定允许访问的IP 和 密码，%表示任何IP
grant select,update on db1.table1 to 'user1'@'ip'; -- 授权，指定操作，表，用户
grant all privileges on *.* to 'user1'@'ip'; -- 通配授权
drop user 'user1'@'ip'; -- 删除用户
flush privileges; -- 生效权限设置
show grants for 'user1'@'ip'; -- 查看权限
身份验证插件 与 密码修改
查看插件：user表的plugin列
创建用户时指定插件：create user 'user1'@'%' identified with mysql_native_password by 'pass'；用户创建后，插件不能改动
默认插件：mysql8的默认插件为caching_sha2_password，之前为mysql_native_password
修改默认插件：/etc/my.cnf 里设置 default-authentication-plugin=mysql_native_password，后重启mysqld
修改别人的密码：alter user 'user1'@'%' identified by 'pass';
set password for 'user1'@'%' = 'pass';
修改自己的密码：set password = 'pass';
忘记root密码：https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html
1、在/etc/my.cnf 中加上 skip-grant-tables
2、重启 mysqld
3、无密码登录，mysql -u root
4、重置密码 set password = 'pass';
远程客户端访问
1、确认端口是否可达
（1）本地登录mysql并查看配置的端口号：show global variables like 'port';
（2）查看本机监听端口：netstat -tunlp | grep 3306
（3）检查云主机安全组入向规则，没有则创建3306端口入向规则
2、确认 用户是否存在，客户端IP是否被允许
3、确认用户对某仓库是否有访问权限
数据库备份 、恢复、迁移、导出、导入
mysqldump -u root -p db1 > ~/dump.sql; -- 备份仓库
mysqldump -u root -p db1 table1 > ~/dump.sql; -- 备份表
mysqldump -u root -p --databases db1 db2 > ~/dump.sql; -- 备份多仓库
mysqldump -u root -p --all-databases > ~/dump.sql; -- 备份所有仓库
mysql -u root -p db1 < ~/dump.sql; -- 恢复一个仓库
mysql -u root -p < ~/dump.sql; -- 恢复所有仓库
-- 登录数据库后
use db1;
source ~/dump.sql; -- 导入备份（批量执行）
-- 从ip_1迁移到 ip_2，即ip_1备份，ip_2恢复
mysqldump -h ip_1 -uroot -pPass db_1 | mysql -h ip_2 -uroot -pPass; -- 单仓库迁移
mysqldump -h ip_1 -uroot -pPass --all-databases | mysql -h ip_2 -uroot -pPass; -- 所有仓库迁移
/etc/my.cnf 中设置可导出到的目录 secure-file-priv=/tmp
-- 登录数据库后
select * from db1.table1 into outfile '/tmp/file.txt'; -- 导出数据
load data infile '/tmp/file.txt' into table db1.table1; -- 导入数据
MySQL日志
日志配置
查看日志相关配置：show variables like 'log_%';
二进制日志 log_bin
记录备份后进行的所有更新，每启动一次mysqld会新增一个文件，超过max_binlog_size也会新建一个文件
配置：日志文件最大体积 max_binlog_size，日志过期时间 expire_logs_days
查看有哪些日志文件：show binary logs;
查看单个日志文件：mysqlbinlog /var/lib/mysql/binlog.000001
情况日志文件：reset master;
回退数据到某时刻：mysqlbinlog --stop-date='2016-01-10 12:12:12' /var/lib/mysql/binlog.000001 | mysql -uroot -ppass
临时关闭日志功能：set sql_log_bin=0;
错误日志 log_error
查看配置：show variables like 'log_error%';
阿里云 云数据库 RDS版 - 选MySQL
准备工作
1、创建数据库实例，要选择与自己的ECS相同的网络类型
2、设置白名单，即来访IP白名单
3、创建root账号
图形化功能
1、重启
2、仓库、表、索引、视图、触发器、用户管理
3、参数设置
4、备份、恢复、迁移
阿里云
云数据库POLARDB、云数据库RDS MySQL 版、数据管理DMS、数据库审计
