mysql

配置文件

安装目录下的mysql.ini文件

#客户端配置
[client]
#mysql配置
[mysql]
#mysql服务器配置
[mysqld]
-----------------------------
default-storage-engine=INNODB //默认存储引擎

目录结构

bin 可执行文件
data 数据存储文件
docs 文档
include 包含头文件
lib 存储库文件
share 错误消息和字符集文件

修改提示符

--prompt //修改登录后的提示符
prompt //登录后修改提示符
可用配置参数 \B 完整日期 \d 当前db \h 主机名 \u当前用户

分类

ddl 数据定义语言，create、alter、...
tpl 事务处理语言，commit，rollback、...
dcl 数据控制语言，grent、...
dml 数据操作语言，insert、update、...

常用命令

select version(); 当前服务器版本
select now();  当前日期
select user();  当前用户
set names gbk;  使用gbk编码显示查询到的记录

语句规范

关键字与函数名称全部大写（推荐） 数据库名称，表名称，字段名称全部小写 sql语句必须以分号结尾

语法

创建数据库

create database [if not exists] db_name [default] character set [=] charset;

查看数据库信息

//显示所有的数据库
show databases [like 'pattern' | where expr];
//显示所有的表
show tables [from table];
//显示所有的列
show columns from table;
desc table;

查看错误信息

show warnings;

查看创建数据库的命令

show create database db_name;

修改数据库编码

alter database db_name [default] character set charset;

删除数据库

drop database [if exists] db_name;

数据类型

整型

类型    数据范围
tinyint  2^8
smallint  2^16
mediumint  2^24
int  2^32
bigint 2^64

浮点型

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

日期类型

year   年
time   时间
date   日期
datetime   日期时间
timestamp  时间戳

字符类型

char(m) 最大255个
varchar(m) 最大65535
tinytext 2^8
text  2^16
mediumtext  2^24
longtext  2^32
enum('v1','v2') 最多65535
set('v1','v2')  最多64个

数据表操作

打开数据库

use db_name;

创建数据表

create table [if not exists] table_name(
    column_name data_type [null | not null],
);
//使用无符号需要在类型后面使用unsigned
//使用primary key设置主键
//使用自增auto_increment为主键，默认为1，增量为1
// (id int unsigned primary key auto_increment);
//唯一约束：unique key
// (username varchar(10) not null unique key);
//默认值：default 
// (dft varchar(10) not null default 'dft');

查看数据表

show tables [from db_name] [like 'pattern'|where expr];
//查看数据表

查看表列(结构)

show columns from tb_name;

插入记录

insert [into] tb_name [(column1,column2,...)] values(value1,value2,...);

查询数据

select expr,... from tb_name;

约束

保证数据的完整性和一致性 表级约束，列级约束

not null 非空约束
primary key 主键约束
unique key 唯一约束
default 默认约束
foreign key 外键约束

外键约束

//只能使用innoDB存储引擎
foreign key (volumn) references tb_name (column);
外键约束参照：
caseade 级联更新
set null 设置为null
restrict 拒绝操作
no action 无操作
//ON DELETE RESTRICT ON UPDATE CASCADE

修改数据表

//添加字段
alter table tb_name add [column] col_name col_def [first | after column];
//添加多个
alter table tb_name add [column]
(col_name col_type [not null|null],...);
//删除列
alter table tb_name drop [column] col_name,drop col_name,...;
//添加主键约束
alter table tb_name add [constraint [name]] primary key [index_type] (column);
//添加唯一约束
alter table tb_name add [constraint [name]] unique key [index_type] (col1,col2,...);
//添加外键约束
alter table tb_name add [constraint [name]] foreign key (column) reference table (column) [on delete|on update] [foreign_ref];
//修改默认约束
alter table tb_name alter [column] col_name set default def_value;
//删除默认约束
alter table tb_name alter [column] col_name drop default;
//删除主键约束
alter table tb_name drop primary key;
//删除唯一约束
alter table tb_name drop [key|index] key_name;
//删除外键约束
alter table tb_name drop foreign key key_name;

//修改列定义
alter table tb_name modify [column] col_name col_def [first|after col_name];
//修改列名
alter table tb_name change [column] 'old' 'new' col_def;
//修改表名
alter table tb_name rename new_tb_name;
rename table tb_name to new_tb_name,tb to ntb,...;

查看约束名称

show indexes from test;

增删改查

//添加记录
insert [into] tb_name [(col1,col2,...)] values (val1,val2,...),(...),...;
insert [into] tb_name set col1=val1,col2=val2,...;
insert [into] tb_name [(col1,col2,...)] select ...;

//更新记录
update tb_name set col1=val1,col2=val2,... [where expr]

//删除记录
delete from tb_name [where expr]

//查询记录
select col1 [[as] o_name],col2,... [from tb_name where expr]

//分组查询
select expr from tb_name group by col [asc|desc] [having where_expr]

//排序
select expr from tb_name [order by col [asc|desc]]

//分页查询,记录从0开始
select expr from tb_name [limit [offset,]size]
select expr from tb_name [limit size offset offset_num]

子查询

select *,[(subquery)] from (subquery) where (subquery) = val
select * from db_name where val = [any|some|all] (sunquery)

连接

inner join 内连接,显示符合条件的记录，交集
left [outer] join 左外连接，显示左表全部和其他表符合条件的记录
right [outer] join 右外连接，显示右表全部和其他表符合条件的记录
full [outer] join 全外连接，mysql暂不支持，可以使用左连接和右连接查询，之后在使用union all将两个结果联合
cross join  交叉连接，笛卡尔积查询

//连接查询
select * from tb1 join_type tb2 on join_cond

//多表更新
update tb_name join_type other_tb on join_cond set col1 = val1,... where expr
//多表删除
delete [alias] from tb_name [alias] join_type other_tb on join_cond where expr

字符函数

concat(char1,char2,...)  //字符连接
concat_ws(split,char1,char2,...)  //字符连接，指定分隔符
format(num,pl) //数字格式化，返回字符,pl为小数点后保留位数
lower(chars)  //字符串小写
upper(chars)  //字符串大写
left(chars,size) //获取字符前size位
right(chars,size) //获取字符最后size位
length(chars)  //字符串长度
ltrim(chars),rtrim(chars),trim([{leading|trailing|both} split from] chars)  //删除左，右，全部空格，{leading|trailing|both}为左，右，两侧的指定字符(split)去除
replace(chars,old,new)  //将chars中的old替换为new
substring(chars,f[,t])  //字符串截取,从f位开始截取t个，如果没有t表示到chars结尾，f可以为负数，指从最后开始
like expr //模糊查询，(%:任意字符，_:任意一个字符)，%1%% escape '1'为指定1后面的%为字符串而不是通配符

数值运算

ceil()  //向上取整
floor()  //向下取整
div  //整数除法，不计算小数
mod  //取模运算，或者使用%
power()  //幂运算
round()  //四舍五入
truncate()  //截断数字

比较运算

[not] between ... and ...   范围查询
[not] in (1,2,...)   列表范围
is [not] null  是否为空

日期运算

now()   //当前日期时间
curdate()  //当前日期
curtime()  //当前时间
date_add(date,interval num dtype)  //日期增加,dtype为日期单位(year|week|day|month...)
datediff(d1,d2)  //日期差值
date_format(date,format)  //日期格式化,(%Y年|%m月|%d日|%H时|%i分|%s秒)

信息函数

select connection_id()  //连接id
select datebase()   //连接的数据库
select last_insert_id()  //最后插入的自增id
select user()  //登录用户
select version()  //数据库版本

聚合函数

avg() 平均数
count() 个数
max() 最大值
min() 最小值
sum() 和

加密函数

md5()  //md5值加密
password() //计算mysql密码，5.7之前可以使用set password = password('pwd');修改mysql密码。

自定义函数

//函数参数最大值为1024，参数和返回值没有必然联系
create [or replace] function function_name([p1 type,p2 type,...]) returns {string|integer|real|...} 
begin
fun_body
[return rtn]
end;
//如果有多个sql语句要执行，则需要修改结束符，使用delimiter expr 修改，创建成功之后要重新修改为默认结束符
//删除函数
drop function fun_name;

存储过程

//增强sql功能，加快速度，减少网络流量
//in，入参
//out，出参，使用 into 为出参赋值
//inout，出入参
create 
[definer = user]
procedure sp_name([in|out|inout] param_name type,...) 
begin
body
end;
//调用存储过程，变量传递使用@param_name
call sp_name();
//删除存储过程
drop procedure sp_name;

存储引擎

//锁：行锁和表锁
//事务特征：原子性，隔离性，一致性，持久性
//创建数据表是使用engine=e_name指定存储引擎,或者使用以下语句修改存储引擎
alter table tb_name engine = e_name

5.7特点

//初始化 
mysqld --initialize --user=mysql --basedir=path --datadir=path
//增加计算列
//引入json类型

技巧

join 技巧
//更新的表在from从句中的，不允许更新，可以使用*多表更新*处理
//使用join代替在查询列中的子查询，即将select col,(sunquery) from t 替换为 select col,col2 from t1 join t2
//避免子查询，将在条件中的子查询使用join来代替
行列转换
//行转列：将查询到的每个数据横向显示，如日期
//使用分别筛选查询之后，再使用笛卡尔积进行关联进行行转列，要进行多个子查询，比较复杂
//使用case when cond then expr end 进行行列转换,需要将所有的列名进行穷举
//列转行：将以多个数据以分隔符进行分隔的时候转换为多行
//创建一个只有一个自增数据的序列表，计算每个数据中单独数据的个数，通过连接序列表并指定序列id小于等于个数，然后截取数据中的每个数据
//列转行：将同一个人的多个数据列，转换为多行显示
//多次查询特定的列，然后使用union all连接
//使用原表与序列化表笛卡尔积的方式关联将一行变成多行，然后使用case进行判断，使用coalesce()函数将结果中为null的列去除
唯一序列号
//使用自增主键auto_increment
//使用序列表生成序列号
删除重复数据
//使用group by having查询重复数据
//查询id最大或最小的数据，使用*多表删除*处理重复数据
多列过滤
//where可以使用多列过滤，where (c1,c2,...) in (select c1,c2,... from tb)
多属性查询
//查询一个数据是否具有某几个属性，使用多次关联，每次关联一种属性，条件中将不为null的值使用case输出为1，否则为0，然后相加大于需要的个数
//使用关联之后分组查询，取出结果数量大于需要的数量的结果
阶段计算税额
//使用join连接查询得到跨越税率区间数的数据行，使用least函数计算区间中的值，然后再进行计算税额之后统计即可

优化

慢查询日志：

show variables like '%slow_query%';//查看慢查询日志是否开启和存储位置
set globle log_queries_not_using_indexes=on;//设置将未使用索引的慢查询加入日志
show variables like 'long_query_time';//慢查询的限制时间
set long_query_time=3.00;//设置慢查询的限制时间

慢查询日志格式：

执行时间
# Time: 190103 13:59:24
执行sql的主机信息
# User@Host: root[root] @ localhost [127.0.0.1]
sql的执行信息，发送行数，扫描行数
# Query_time: 0.000997  Lock_time: 0.000997 Rows_sent: 2  Rows_examined: 2
sql的执行时间
SET timestamp=1546495164;
sql的内容
select * from store limit 10;

慢查询分析工具：

mysqldumpslow //慢查日志分析，mysql自带的工具
//mysqldumpslow -t 10 path | more
pt-query-digest  //需要下载安装的工具
//pt-query-digest path | more

执行计划：

explain sql_string//显示sql的执行计划
返回列说明：
select_type 查询类型，https://www.cnblogs.com/danhuangpai/p/8475458.html
table 数据表
type 连接类型，从好到差为const，eq_reg，ref，range，index，all
possible_keys 显示可能应用在这张表的索引，为空表示没有可能的索引
key 实际使用的索引，空表示没有使用
key_len 使用索引的长度，在不损失精度的情况下，长度越短越好
ref 显示索引的哪一列被使用，可能为一个常数
rows 表扫描的行数
Extra using filesort 使用文件排序，可能需要优化sql,using temporary 使用临时表存储结果，可能需要优化sql

sql优化：

max() //查询可以使用索引优化
count(指定列) //执行不包含为null的值
子查询 //如果子查询中有多条数据，转换为join时需要去重
group by //子查询和连接方式灵活修改
limit //尽量使用主键进行排序操作，可以添加where id > lastpage条件（id为连续增长），然后再分页

索引优化：

在sql语句中条件判断的列上建立索引，索引的字段越小越好，离散度大的列放在联合索引的前面
//离散度表示当前列值的范围 
pt-duplicate-key-checker -uuser -p 'pwd' -h host //索引检测
pt-index-usage -uuser -p '' slow.log //检测不使用的索引

数据库结构优化：

选择合适的数据类型 //选择可以存下数据的最小的类型
选择简单的数据类型 //int比varchar处理简单
尽可能使用not null并指定默认值
尽量少用text等数据类型
//使用int存储IP地址，使用int存储时间等

范式化：第三范式要求，存在传递性依赖的不符合
反范式化：添加冗余字段增加查询效率

垂直拆分：将多列拆分为多表，不常用，大字段单独存放，常用字段放在一起
水平拆分：按照id的hash进行拆分表，统计查询与前台查询分开，统计使用汇总表

系统优化：

网络配置：/etc/sysctl.conf
#增加tcp支持的队列数
net.ipv4.tcp_max_syn_backlog=65535
#减少断开连接，加快回收连接
net.ipv4.tcp_max_tw_buckets=8000
net.ipv4.tcp_tw_reuse=1
net.ipv4.tcp_recycle=1
net.ipv4.tcp_timeout=10
打开文件数限制：/etc/security/limits.conf
* sort nofile 65535
* hard nofile 65535
mysql配置优化：/etc/my.cnf  /etc/mysql/my.cnf  my.ini,查找配置文件的顺序(后面的会覆盖前面的配置) mysqld -verbose --help | grep -A 1 'Defaut options'
Innodb_buffer_pool_size //innodb缓存池大小
Innodb_buffer_pool_instance //缓存池的个数，默认为1，5.5新增
Innodb_log_buffer_size  //缓存日志大小
Innodb_flush_log_at_trx_commit //数据刷新到磁盘的配置，0（每秒刷新一次），1（默认，每次提交都刷新到磁盘），2（每次刷新提交到缓冲区，每秒刷新到磁盘，建议值）
Innodb_read_io_threads //读线程数，默认4
Innodb_write_io_threads //写线程数，默认4
Innodb_file_per_table //控制每一个表都使用独立的表空间，默认为off，即所有表都在共享表空间，建议设为on
Innodb_stats_on_metadata  //mysql统计信息刷新配置

//建议配置工具
https://tools.percona.com