ddl操作

库操作

show databases;
create database 库名;
use 库名;
select database();
drop database 库名;

表操作

show tables;
create table 表名(字段 类型,字段 类型);
desc 表名;//查看有哪些字段
show create table 表名;//查看建表命令
alter table 表名 add 字段 类型(长度)[comment 注释]
alter table 表名 modify 字段 新类型//修改字段类型
alter table 表名 change 旧字段 新字段 类型//修改字段名和类型
alter table 表名 drop 字段名
alter table 表名 rename to 新表名
drop table [if exists] 表名 
truncate table 表名//删除重建

dml操作

insert into 表名 (字段1,字段2) values (值1,值2)[,(值1,值2)];
updata 表名 set 字段1=值1,字段2=值2[where];
delete from 表名 [where];

dql操作

select 字段名[as 别名] from 表名 where 条件 group by 字段 having 分组后过滤条件order by 字段 [asc/dsc] limit 起始0,数量;

dcl操作

用户管理

create user 用户名@主机名 identified by 密码;
alter user 用户名@主机名 identified with mysql_native_password by 密码;//修改用户密码;
drop user 用户名@主机名;

权限控制

show grants for 用户名@主机名;
grant 权限列表 on 数据库.表名 to 用户名@主机名;//授予权限all,select,update...;
revoke 权限列表 on 数据库.表名 from 用户名@主机名;//撤销权限all,select,update...;

函数

用户管理

create user 用户名@主机名 identified by 密码;
alter user 用户名@主机名 identified with mysql_native_password by 密码;//修改用户密码;
drop user 用户名@主机名;

字符串

concat(s1,s2,...),lower(s),upper(s),lpad(s,n,p)//用p填充字符串s达到n位
rpad(s,n,p),srim(s)//去除头尾空格
substring(s,start,n)//返回start起的n个字符

数值函数

ceil(i)//向上取整
floor(i)//向下取整
mod(x,y)//取x/y的模
rand()//大于等于0,小于1的随机数
round(x,y)//x四舍五入,保留y个小数

日期函数

curdate(),curtime(),now(),year(date),month(date),day(date),date_add(date,interval 70 month);//加上一个时间计算日期
datediff(date1,date2)//计算日期天数

流程函数

if(value,t,f)//value为true,则返回t,否则f
ifnull(v1,v2)//v1不为null,返回v1,否则v2
case value when s1 then v1 when s2 then v2 else v3 end//value=s1返回v1

外键约束

alter table 表名 add constraint 外键名 foreign key(字段名） references 主表（主表列名）;

多表查询

select * from 表1，表2 where 条件;//内联
select * from 表1 left join 表2 on 条件；//外连接;
select * from biao1 别名1，biao1 别名2 where 条件；//自联结
select * from biao1 where id > any (select id from biao2);//in多用于=,not in,any,all多用于>

事务

start transaction;//开启事务
commit/rollback;//提交回滚
1脏读-事务1读到事务2未提交数据；
2不可重复读-事务先后读取同一条数据结果不同
3幻读-事务读取时无此条数据，写入时存在此条数据
四个隔离级别存在问题：
read uncommited ：123
read commited:23
repeatable read:3（默认）
serializable:无

进阶

储存引擎

show engines;
innodb支持事务、外键、行级锁，mylsam不支持；

性能优化

show global status like 'com_______';//频次
//慢查询默认未开启
show variables like 'slow_query_log';//查询
在/etc/my.cnf添加以下内容开启
slow_query_log=1;
long_query_time=2;//超过2秒记录
日志在/var/lib/mysql/localhost-slow.log,可cat跟踪此日志
//性能分析
select @@have_profiling;
set profiling=1;
show profiles;//查看
//创建索引
create index xxx on xxx(xx);
show index from xx;
drop index xx on xxx;
//大字符串前缀索引
select count（distinct substring(字段，起始1开始，长度）/count(*) from xxx;//测试完整度
create index xx on xxx(字段（长度））；//创建前缀索引

数据操作

//大数据插入
mysql --local-infile -u root -p;
set global local_infile=1;
load data local infile '/root/sql.log' into table 'xxx' field teminated by ',' lines teminated by '\n';

视图

//用于对用户屏蔽部分数据
create or replace view xxx as select id,name from xxx where id>20;//创建视图，可在where后加with cascaded check option,则插入不符合条件的数据时会报错，可将cascaded替换成local，这样只对本视图进行检查，不对关联的其他视图进行检查。

存储过程

//用DELIMITER $$将sql语句结束符改成$$
creat procedure p(in xxx int,out xxx char(10))
begin
sql语句
end；
//调用
call p(50,@result);
show [global|session] variables like 'xx';//查看变量
select @@[global|session]xxx;
set @@[global|session]xxx=x;//设置变量值
declare xxx int default 50;//定义变量
if then else if then end then;//条件
case when  then when then end case;//条件
where n>0 do end where;
//游标类似搜索结果集合
declare xxx cursor for select * from xxx;//定义
open xxx;//打开游标
  while true do
    fetch xxx into 变量1，变量2；
  end while;
close xxx;//关闭游标
//设置异常处理
declare exit handler for sqlstate '02000';
//触发器
create trigger xxx before/after insert/update/delete on xxx for each row//行级触发器
begin end；
show triggers
drop triggers 数据库名 触发器名；

mysql工具

mysql
mysqladmin
mysqlbinlog xxx//日志目录在、var/lib/mysql中，查看二进制日志
mysqlshow -u -p --count/-i dbname tablename xxx;//查看数据库状态信息
mysqldump -u-p dbname >beifenname;//--add-drop-database创建前删除;--add-drop-table默认开，关闭加--skip-add-drop-table;-n无数据库创建-t无表创建-d无数据-T两文件，sql表结构，txt数据空格隔开
mysqlimport -u-p dbname sql.txt//导入mysqldump导出的数据
sourse .sql

日志

show variables like '%log_errors%'//错误日志，默认/var/log/mysqld.log
show variables like '%log_bin%'//二进制日志，记录所有ddl和dml语句，用于灾难回复

分库分表

主从库

//主库配置
修改配置文件/etc/my.cnf,增加
server-id=1//设id
read-only=0//可读写
systemctl restart mysqld//重启服务
create user 'xxx'@'%' identified with mysql_native_password by 'root@123456';//创建用户在任意主机上访问数据库
grant repucation slave on x.x to 'xxx'@'%';//未用户分配主从权限
show master status;//显示二进制日志位置状态
//从库配置
修改配置文件/etc/my.cnf,增加
server-id=2//设id
read-only=1//只读
systemctl restart mysqld//重启服务
change repucation source to source_host='192.168.0.1',source_user='xxx',source_password='xxx',source_log_file='binlog.0004',source_log_pos=663;//设置主从库关系
start replica;//启动从库
show replica status\g;//显示从库状态replica io runing和sql runing为yes则成功

双主双从

//两主库配置
修改配置文件/etc/my.cnf,增加
server-id=1//设id
binlog-do-db=db01 binlog-do-db=db02//设置需要主从的数据库名
log-slave-updates//作为从库写操作也要写入日志
systemctl restart mysqld//重启服务
create user 'xxx'@'%' identified with mysql_native_password by 'root@123456';//创建用户在任意主机上访问数据库
grant repucation slave on x.x to 'xxx'@'%';//为用户分配主从权限
show master status;//显示二进制日志位置状态
//从库配置
修改配置文件/etc/my.cnf,增加
server-id=2//设id
systemctl restart mysqld//重启服务
change repucation source to source_host='192.168.0.1',source_user='xxx',source_password='xxx',source_log_file='binlog.0004',source_log_pos=663;//设置主从库关系
start replica;//启动从库
show replica status\g;//显示从库状态replica io runing和sql runing为yes则成功
//两主库互为复制
source_host='192.168.0.1',source_user='xxx',source_password='xxx',source_log_file='binlog.0004',source_log_pos=663;

分库mycat

//jdk安装,下载包解压
tar -zxvf jdk.tar.gz -c /usr/local/;//安装jdk
vim /etc/profile//编辑环境变量，添加
 java_home=/usr/local/jdk目录
 path=&path:java_home/bin
source /etc/profile;//重新执行配置文件
/mycat安装,下载包解压
tar -zxvf mycat.tar.gz  -c /usr/local/;//安装mycat
//mycat lib文件夹下有mysql-connect-java包，如果版本较低可替换为高版本
rm -rf  mysql-connect-java.jar//删除包
chmod 777 mysql-connect-java.jar//对新包赋权限
//mycat分库配置
//配置文件在mycat/conf目录下schema.xml
<schema>标签的name=数据库名 <table>标签的name=表名
<datanode>配置节点信息datahost为节点名称
<datahost>和datanode一一对应，设置具体链接信息，dbdriver='jdbc',url='jdbc:mysql://192.168.0.1:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8'
//配置server.xml用户权限
<user>标签下的schema用户改成使用的数据库名如db01
//启动mycat服务，切换到mycat安装目录
bin/mycat start//开启，占用端口8066
bin/mycat stop//停止
分片规则是在conf目录下rule.xml
使用mycat访问数据库端口改成8066即可
mysql -h 192.168.0.1 -p8066 -uroot -p 123456

mycat实现主从库的读写分离

<writehost host='master' balance='1'>
 <readhost host='slave' /> 
 </writehost>
 其中balance：
 0不开启读写分离，全发到writehost上
 1全部readhost和备用writehost参与分发读，双主双从
 2所有读写操作全部随即分发
 3读写分离

mycat实现双主双从库的读写分离

<writehost host='master1' balance='1' writetype switchtype>
 <readhost host='slave1' /> 
 </writehost>
 其中writetype：
 0全转到第一台，第一台挂了发第二台
 1两台随机发
 switchtype:
 -1不切换
 1挂了自动切换

mycat监控

//安装zookeeper和mycat-eye
tar -zxvf zookeeper.tar.gz -c /usr/local/;//安装zookeeper
进入安装目录创建data文件夹，mkdir data；pwd获取目录；
进入conf文件夹，修改zoo.sample.cfg为zoo.cfg,mv zoo.sample.cfg zoo.cfg
vim zoo.cfg,修改datadir=“data文件夹目录”
bin/zkServer.sh start//启动服务 status查看状态显示alone则成功
//安装mycat-web
tar -zxvf mycat-web.tar.gz -c /usr/local/;//安装mycat-web
sh start.sh//进入目录启动，如果web和mycat不在同一台服务器上，那么要进入web-inf/classes/mycat.properties修改地址
进入浏览器输入192.168.0.1：8082/mycat