MYSQL大数据量下的，SQL练习，SQL常用高级关键字讲解

如果你没有数据练习，请看我这篇博客

https://blog.csdn.net/weixin_44012722/article/details/107974662

现在开始练习(以下练习都是以上面的数据集作为练习数据)

1.JOIN

1. left join

返回左表拥有的emp_no，

select e.emp_no,birth_date,first_name,last_name,gender,salary from employees e left join salaries s on e.emp_no = s.emp_no 

//返回左表有的emp_no，且右表没有的emp_no
select e.emp_no,birth_date,first_name,last_name,gender,salary from employees e left join salaries s on e.emp_no = s.emp_no where s.emp_no is null 

2. right join

返回右表拥有的emp_no

select e.emp_no,birth_date,first_name,last_name,gender,salary from employees e right join salaries s on e.emp_no = s.emp_no 

//返回右表有的emp_no，且左表没有的emp_no
select e.emp_no,birth_date,first_name,last_name,gender,salary from employees e right join salaries s on e.emp_no = s.emp_no where s.emp_no is null 

3. inner join

返回左右表都有的emp_no

select e.emp_no,birth_date,first_name,last_name,gender,salary from employees e inner join salaries s on e.emp_no = s.emp_no 

4. full join / full outer join (Oracle数据库支持full join，mysql是不支持full join的，但仍然可以同过左外连接+ union+右外连接实现)

返回左表所有有的emp_no和右表所有的emp_no

//Oracle数据库支持full join
select e.emp_no,m.emp_no,first_name,last_name,motto from employees e full join motto m on e.emp_no = m.emp_no 

2.UNION

select e.emp_no,birth_date,first_name,last_name,gender,salary from employees e left join salaries s on e.emp_no = s.emp_no where s.emp_no is null 
union
select e.emp_no,birth_date,first_name,last_name,gender,salary from employees e right join salaries s on e.emp_no = s.emp_no 

3.EXPLAIN

在日常工作中，我们会有时会开慢查询去记录一些执行时间比较久的SQL语句，找出这些SQL语句并不意味着完事了，些时我们常常用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。所以我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。

执行EXPLAIN可以查询 到以下字段

– id

//id相同  id如果相同，可以认为是一组，从上往下顺序执行；
explain select t2.* from t1,t2,t3 where t1.id = t2.id and t1.id = t3.id and t1.col_1 = ''

//id不同 d值越大，优先级越高，越先执行
explain select t2.* from t2 where id = (select t1.id from t1 where id = (select t3.id from t3 where t3.col_1 = ''))

– select_type

查询类型只要区别，普通查询，联合查询，子查询的复杂查询

– type

全

部

访

问

类

型

\color{#FF0000}{全部访问类型}







全


部


访


问


类


型

system > const > eq_ref > ref > fulltext > ref_or_null > index-merge > unique_subquery > index_subquery > range > index > ALL

常

用

访

问

类

型

\color{#FF0000}{常用访问类型}







常


用


访


问


类


型

system > const > eq_ref > ref > range > index > ALL

一

般

来

说

至

少

要

达

到

r

a

n

g

e

级

别

为

好

，

最

好

到

达

r

e

f

级

别

\color{#FF0000}{一般来说至少要达到range级别为好，最好到达ref级别}







一


般


来


说


至


少


要


达


到


r


a


n


g


e


级


别


为


好


，


最


好


到


达


r


e


f


级


别

const和eq_ref的区别 (刚开始我也分不清，后面知道了，分享一下)

两者相同都是用于比较primary和unique索引，不同地方是，const是通过一次索引比较，找出一条记录，而eq_ref是对于每个索引键，表中都只有 一条记录与之匹配，找出1条记录，看以下sql，品一下

const

explain select * from t1 where id = 1

eq_ref

explain select * from t1,t2  where t1.id = t2.id

– extra

以上就是EXPLAIN关键字的介绍，想进行优化SQL练习的请看我这篇博客

4.EXISTS / NOT EXISTS

EXISTS(包括 NOT EXISTS )子句的返回值是一个BOOL值。 EXISTS内部有一个子查询语句(SELECT … FROM…)， 我将其称为EXIST的内查询语句。其内查询语句返回一个结果集。 EXISTS子句根据其内查询语句的结果集空或者非空，返回一个布尔值。将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果取非空值，则EXISTS子句返回TRUE，这一行行可作为外查询的结果行，否则不能作为结果,反而反之

例子

select * from salaries s where emp_no in ( select emp_no from employees )

select * from salaries where exists ( select emp_no from employees )

可以看到查询结果都一样，但是执行时间不一样，一个用in嵌套子查询，一个用exists内查询，因为salaries是数据比employees的数据要多，所以in查询相当于大表驱动小表，相当于他要去salaries表循环N次，每次要去employees表查找是否有相同emp_n，而exists是小表驱动大表，遍历employees表中数据，如果满足salaries表中emp_no数据相同则返回emp_no作为外查询作为结果集。小表驱动大表是推荐，效率高

5.VARIABLES

mysqld服务器维护两种变量。——GLOBAL变量 / SESSION变量

当mysql启动他就会按照 my.ini 里的配置信息 设置到 GLOBAL变量中， 当有一个客户端来连接，就会将GLOBAL的配置信息变量 复制到 新创建的这个SESSION变量中维护。所以当MYSQL重启就会重新把my.ini中配置信息放入GLOBAL中。

1.想要永久该配置信息就要改 my.ini文件

2.想要临时改变配置信息，选择可以改变GLOBAL或SESSION

SET GLOBAL var_name = var_val
SET SESSION var_name = var_val
#默认改变SESSION中的变量
SET var_name = var_val

GLOBAL 和 SESSION 的区别就是，改变GLOBAL变量，后续连接的客户端也会改变，但不影响已连接的客户端，如果SESSION变量改变了，只影响执行该SQL的当前客户端，不影响其他 ，当客户端断开连接，重新连接他又是从GLOBAL中初始化配置信息

如果想知道配置参数变量的意思可以看这篇博客

https://blog.csdn.net/qq_30450439/article/details/80089892

（变量值1为ON,0为OFF）

6.PROFILES / PROFILE

用于分析当前连接客户端会话中语句执行的资源消耗情况，可用于SQL调优测

默认参数处于关闭状态，开启后 保存最近15次的运行结果

SHOW VARIABLES like 'profiling'
# 如果关闭了请开启
SET profiling=on
#随便执行一条查询SQL
#查看当前会话所有执行的SQL的消耗信息
SHOW PROFILES

#展示指定 查询ID 查看此SQL的消耗性能的过程情况
SHOW PROFILE FOR QUERY 查询ID

SHOW PROFILE 还可以显示其他详细的性能消耗信息

#使用例子
SHOW PROFILE CPU,SOURCE FOR QUERY 查询ID

当你的SQL性能消耗信息STATUS中出现了以下，你要注意

因为 SHOW PROFILES 是显示15条，不能显示全部，如果要查询全局就要开启 全局日志查询（他会记录所有查询SQL）

此功能最好在测试环境中开启，因为生产环境中会占用性能，使性能下降

#开启全局日志
SET general_log = 1
#显示当前会话配置参数 general_log_file全局日志查询文件路径 log_output日志文件格式
SHOW VARIABLES LIKE 'general_log_file'
SHOW VARIABLES LIKE 'log_output'
# 如果改变log_output日志文件格式为TABLE,他就会记录在mysql库中general_log表中
SET GLOBAL log_output = 'TABLE'
#客户端重新连接

7.FUNCTION

mysql的自定义函数，用于封装自定义方法

7.1. 创建函数

DELIMITER $$  #定界符（函数定义开始标志）
DROP FUNCTION IF EXISTS 方法名$$
CREATE FUNCTION 方法名(形参变量名 形参数据类型) RETURN 返回参数类型
BEGIN
  .....;
  .....;
  RETURN 返回参数;
END $$ #函数定义结束标志

8.PROCEDURE

存储过程，用于批量执行sql语句逻辑，与 函数 区别是没有返回值

8.1. 创建存储过程

DELIMITER $$  #定界符（存储过程定义开始标志）
DROP FUNCTION IF EXISTS 存储过程名$$
CREATE FUNCTION 存储过程名(形参变量名 形参数据类型)
BEGIN
  .....;
  .....;
END $$ #存储过程定义结束标志

8.2. 调用存储过程

DELIMITER $  #定义定界符
CALL 存储过程名(参数变量..)$

9.更多关键字正在持续的更新中。。。

一键查询淘宝/拼多多内部优惠券，每日大额外卖红包，购物省钱的宝藏工具