一、日志相关
-- 查看MySQL是否开启了binlog日志
show variables like 'log_bin';
-- 查看binlog日志的格式
show variables like 'binlog_format';
-- 查看所有日志
show binlog events;
-- 查看最新的日志
show master status;
-- 查询指定的binlog日志
show binlog events in 'binlog.000010';
-- 从指定的位置开始,查看指定的Binlog日志
show binlog events in 'binlog.000010' from
156;
-- 从指定的位置开始,查看指定的Binlog日志,限制查询的条数
show binlog events in 'binlog.000010' from
156 limit 2;
--从指定的位置开始，带有偏移，查看指定的Binlog日志,限制查询的条数
show binlog events in 'binlog.000010' from
666 limit 1, 2;
-- 清空所有的 binlog 日志文件
reset master
-- 查看MySQL是否开启了查询日志
show variables like 'general_log';
-- 开启查询日志
set global
general_log=1;
-- 慢日志查询
-- 查看慢查询日志是否开启
show variables like 'slow_query_log%';
-- 开启慢查询日志
set global slow_query_log = 1;
-- 查看慢查询的超时时间
show variables like 'long_query_time%';
select sleep(12);
二、事务
create database if not exists
mydb_transcation;
use mydb_transcation;
-- 创建账户表
create table account(
id
int primary key,
name
varchar(20),
money
double
);
-- 插入数据
insert into account
values(1,'zhangsan',1000);
insert into account values(2,'lisi',1000);
-- 设置MySQL的事务为手动提交(关闭自动提交)
select @@autocommit;
set autocommit = 0;
-- 模拟账户转账
-- 开启事务
begin;
update account set money = money - 200
where name = 'zhangsan';
update account set money = money + 200
where name = 'lisi';
-- 提交事务
commit;
-- 回滚事务
rollback;
select * from account;
-- 查看隔离级别
show variables like '%isolation%';
-- 设置隔离级别
/*
set session transaction isolation level 级别字符串
级别字符串：read uncommitted、read committed、repeatable read、serializable
*/
-- 设置read
uncommitted
set session transaction isolation level
read uncommitted;
-- 这种隔离级别会引起脏读，A事务读取到B事务没有提交的数据
-- 设置read committed
set session transaction isolation level
read committed;
-- 这种隔离级别会引起不可重复读，A事务在没有提交事务之前，可看到数据不一致
-- 设置repeatable
read （MySQ默认的）
set session transaction isolation level
repeatable read;
-- 这种隔离级别会引起幻读，A事务在提交之前和提交之后看到的数据不一致
-- 设置serializable
set session transaction isolation level
serializable;
-- 这种隔离级别比较安全，但是效率低，A事务操作表时，表会被锁起，B事务不能操作。
-- SQL的优化
insert into account
values(3,'wangwu',1000);
-- 查看当前会话SQL执行类型的统计信息
show session status like 'Com_______';
-- 查看全局（自从上次MySQL服务器启动至今）执行类型的统计信息
show global status like 'Com_______';
-- 查看针对InnoDB引擎的统计信息
show status like 'Innodb_rows_%';
-- 查看慢日志配置信息
show variables like '%slow_query_log%';
-- 开启慢日志查询
set global slow_query_log = 1;
-- 查看慢日志记录SQL的最低阈值时间,默认如果SQL的执行时间>=10秒，则算慢查询，则会将该操作记录到慢日志中去
show variables like '%long_query_time%';
select sleep(12);
select sleep(10);
-- 修改慢日志记录SQL的最低阈值时间
set global long_query_time = 5;
-- 通过show
processlist查看当前客户端连接服务器的线程执行状态信息
show processlist;
select sleep(50);
-- ----------------explain执行计划-------------------------
create database mydb13_optimize;
use mydb13_optimize;
-- 1、查询执行计划
explain
select * from user where uid = 1;
-- 2、查询执行计划
explain
select * from user where uname = '张飞';
-- 2.1、id 相同表示加载表的顺序是从上到下
explain select * from user u, user_role ur,
role r where u.uid = ur.uid and ur.rid = r.rid ;
-- 2.2、 id 不同id值越大，优先级越高，越先被执行。
explain select * from role where rid =
(select rid from user_role where uid = (select uid from user where uname = '张飞'))
-- 2.3/
explain select * from role r ,
(select * from user_role ur where ur.uid =
(select uid from user where uname = '张飞')) t where
r.rid = t.rid ;
三、DML操作
略
四、基本查询
略
五、多表操作
-- 多表查询-数据准备
-- 创建部门表
create table if not exists dept3(
deptno varchar(20) primary key ,
-- 部门号
name varchar(20) -- 部门名字
);
-- 创建员工表
create table if not exists emp3(
eid
varchar(20) primary key , -- 员工编号
ename varchar(20), -- 员工名字
age
int, -- 员工年龄
dept_id varchar(20) -- 员工所属部门
);
-- 给dept3表添加数据
insert into dept3 values('1001','研发部');
insert into dept3 values('1002','销售部');
insert into dept3 values('1003','财务部');
insert into dept3 values('1004','人事部');
-- 给emp3表添加数据
insert into emp3 values('1','乔峰',20, '1001');
insert into emp3 values('2','段誉',21, '1001');
insert into emp3 values('3','虚竹',23, '1001');
insert into emp3 values('4','阿紫',18, '1001');
insert into emp3 values('5','扫地僧',85, '1002');
insert into emp3 values('6','李秋水',33, '1002');
insert into emp3 values('7','鸠摩智',50, '1002');
insert into emp3 values('8','天山童姥',60, '1003');
insert into emp3 values('9','慕容博',58, '1003');
insert into emp3 values('10','丁春秋',71, '1005');
-- 1.交叉连接查询
/*
1. 交叉连接查询返回被连接的两个表所有数据行的笛卡尔积
2. 笛卡尔集可以理解为一张表的每一行去和另外一张表的任意一行进行匹配
3. 假如A表有m行数据，B表有n行数据，则返回m*n行数据
4. 笛卡尔积会产生很多冗余的数据，后期的其他查询可以在该集合的基础上进行条件筛选
格式：select * from 表1,表2,表3….;
*/
-- 内连接查询
/*
隐式内连接（SQL92标准）：select * from A,B where 条件;
显示内连接（SQL99标准）：select * from A inner join B on 条件;
*/
-- 查询每个部门的所属员工
-- 隐式内连接
select * from dept3,emp3 where dept3.deptno
= emp3.dept_id;
select * from dept3 a,emp3 b where a.deptno
= b.dept_id;
-- 显式内连接
select * from dept3 inner join emp3 on
dept3.deptno = emp3.dept_id;
select * from dept3 a join emp3 b on
a.deptno = b.dept_id;
-- 查询研发部门的所属员工
-- 隐式内连接
select * from dept3 a ,emp3 b where
a.deptno = b.dept_id and name = '研发部';
-- 显式内连接
select * from dept3 a join emp3 b on
a.deptno = b.dept_id and name = '研发部';
-- 查询研发部和销售部的所属员工
select * from dept3 a join emp3 b on
a.deptno = b.dept_id and (name = '研发部' or name = '销售部') ;
select * from dept3 a join emp3 b on
a.deptno = b.dept_id and name in('研发部' ,'销售部') ;
-- 查询每个部门的员工数,并升序排序
select
a.name,a.deptno,count(1)
from dept3 a
join emp3 b on a.deptno = b.dept_id
group by
a.deptno,name;
-- 查询人数大于等于3的部门，并按照人数降序排序
select
a.deptno,
a.name,
count(1) as total_cnt
from dept3 a
join
emp3 b on a.deptno = b.dept_id
group by
a.deptno,a.name
having
total_cnt >= 3
order by
total_cnt desc;
