union
UNION操作符用于合并两个或者多个select语句的结果集。
使用 UNION 关联的select语句必须拥有相同数量的列，列也必须拥有相似的数据类型。同时，每条select语句中的列的顺序必须相同。
语法:
select 列名[1,2...] from 表
union
select 列名[1,2...] from 表2</pre>
默认地，union操作符选取不同的值，即union是去了重的。如果允许重复的值，可以使用union all。
union all
同样是合并结果集,但是union all不会去重
语法:
select 列名[1,2...] from 表
union all
select 列名[1,2...] from 表2
实战:
查询两张订单表中的orderid,不允许重复：
select order_id
from order_1
union
select order_id
from order_2;
查询两张订单表中的所有orderid
select order_id
from order_1
union all
select order_id
from order_2;
order by
将查询结果 按照某个字段或多个字段 进行升序或者降序 排列
语法:
select 字段1,字段2...
from 表
order by 字段1,字段2... asc/desc;
asc: 代表升序排列,从小到大
desc : 代表降序排列,从大到小
默认是升序排列,asc可以不写
实战:
查询student表,按照生日降序排列
select * from student
order by birthday desc ;
查询student表,按照生日升序排列
select * from student
where birthday is not null
order by birthday ;
group by
GROUP BY 语句根据一个或多个列对结果集进行分组。
在分组的列上可以使用聚合函数查询。
语法:
SELECT 字段, 聚合函数(字段)
FROM 表
WHERE 条件
GROUP BY 字段;
实战
查询每个学生成绩最高的分数是多少?
解析: 每个学生的分数,就是按照学生分组,然后统计每个学生考试科目的最高分
select a.sid,a.sname,max(s.score) from student a inner join sc s on a.sid = s.sid
group by a.sid;
查询各科目成绩最好的分数是多少,展示科目和成绩：
select c.cname,max(s.score)
from sc s
inner join course c on s.cid = c.cid
group by s.cid;
having
having 用于对分组数据的再次过滤,必须紧跟 group by 子句之后,不能单独使用. having的条件必须是聚合函数
语法:
SELECT 字段,聚合函数(字段)
FROM 表
WHERE 字段=xxx
GROUP BY 字段
HAVING 聚合函数(字段) › /‹ /=值
实战:
查出所有学生中只参加了两门考试的学生
select s.sid,s.sname from student s inner join sc s2 on s.sid = s2.sid
group by s.sid
having count(*) =2;
时间函数
MySQL提供了很多用于处理时间的函数,比如获取当前时间,计算两个日期的差值,获取时间的年,月,周等等
制造测试数据
下载sql脚本并执行
https://guoya-data2.oss-cn-hangzhou.aliyuncs.com/guoya-data/wikiAcc/20200721/4rfvx_order1.sql
now() 和 sysdate()
这两个函数都是返回当前时间的
select now(), sysdate();
区别:
now(): 是返回sql语句开始执行时的时间,是一个常量
sysdate(): 是返回 函数执行时的时间
举个例子:
SELECT NOW(), SLEEP(5), NOW();</pre>
SLEEP(5) : 等待5秒
可以看到等待前后的时间 是一样的
SELECT sysdate(), SLEEP(5), sysdate();</pre>
sysdate() 函数是执行时的时间,可以看到两个时间相差5秒
datediff()
计算两个时间之间相差的天数
实战:
查询order_1表中 create_time 与当前时间 相差的天数
select *, datediff(now(),create_time) from order_1;
查询 order_1表中 20天内的订单
select * from order_1 where datediff(now(),create_time) ‹ 20;
dayofweek() 和 weekday()
dayofweek() : 查询 时间 是周几,返回 的是 索引
1 代表周日
2 代表周一
以此类推
7 代表周六
实战:
查询今天是周几
select dayofweek(now());
查询每周日的订单
select * from order_1 where dayofweek(create_time) =1;
weekday() : 查询 时间 是周几,返回 的同样是 索引
不过索引值从 0开始
星期一为0，星期二为1，以此类推,星期日为6
dayname()
返回指定日期 是周几,不以索引展示,而是文字
实战:
查询今天是周几
select dayname(now());
可以看到展示的是英文
修改为中文:
set @@lc_time_names='zh_CN';
再次查询:
查询student表学生的生日 都是周几
select *,dayname(birthday) from student;
day()
返回 日期 是几号.
week()
返回 日期 是 当年的第几周
month()
返回 日期 的 月份
year()
返回 日期 的年份
实战
查询order_1表 每月1号 的订单
select *
from order_1
where day(create_time) = 1;
查询 order_1表 在2019年1月哪一天的订单数量最高?
select day(create_time), count (* )
from order_1
where year(create_time) = 2019
and month(create_time) = 1
group by day(create_time)
having count(* ) = (
select count(* )
from order_1
where year(create_time) = 2019
and month(create_time) = 1
group by day(create_time)
order by count(* ) desc
limit （1）;
查询 order_1表 在2019年 每周的订单数量（*）
select week(create_time), count(*) from order_1
where year(cr8eate_time) = 2019
group by week(create_time);
查询 order_1表 在2019年 每月的订单数量,并将数量按倒叙排列
select month(create_time), count(* ) from order_1
where year(create_time) = 2019
group by month(create_time)
order by count(*) desc;
查询 order_1表 每年的订单数量
select year(create_time), count(*) from order_1
group by year(create_time);
explain
在进行sql语句优化时,可使用explain关键字查看sql执行计划,从而知道MySQL是如何处理sql语句的,可以分析性能瓶颈及优化
举例:
explain select * from sc where sid = (select sid from student where sname = '周梅');
可以看到一共有12列信息
id :
id相同，执行顺序从上往下,id不同，id值越大，优先级越高，越先执行
select_type :
查询类型
1、simple ——简单的select查询，查询中不包含子查询或者UNION
2、primary ——查询中若包含任何复杂的子部分，最外层查询被标记
3、subquery——在select或where列表中包含了子查询
4、derived——在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放到临时表中
5、union——如果第二个select出现在UNION之后，则被标记为UNION，如果union包含在from子句的子查询中，外层select被标记为derived
6、union result:UNION 的结果
table:
引用的表
partitions
分区表的命中情况,不是分区表则为null
type
从最好到最差的连接类型依次为：
system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，ALL
system:
系统表，表中只有一行数据
const:
读常量，最多只会有一条记录匹配，由于是常量，实际上只需要读一次。
eq_ref:
最多只会有一条匹配结果，一般是通过主键或唯一键索引来访问
ref:
对于来自前面表的每一行，在此表的索引中可以匹配到多行。若联接只用到索引的最左前缀或索引不是主键或唯一索引时，使用ref类型（也就是说，此联接能够匹配多行记录）。
fulltext:
使用全文索引的时候是这个类型。要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引
ref_or_null:
跟ref类型类似，只是增加了null值的比较。实际用的不多。
index_merge:
表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取多个索引，性能可能大部分时间都不如range
unique_subquery:
用于where中的in形式子查询，子查询返回不重复值唯一值，可以完全替换子查询，效率更高。
index_subquery:
子查询中的返回结果字段组合是一个索引（或索引组合），但不是一个主键或唯一索引
range:
索引范围查询，常见于使用 =, ‹›, ›, ›=, ‹, ‹=, IS NULL, ‹=›, BETWEEN, IN()或者like等运算符的查询中。
index:
索引全表扫描，把索引从头到尾扫一遍
all:
全表扫描,性能最差。
possible_keys
可能使用到的索引
key
实际使用的索引
key_len
使用索引的字节数,只展示where使用到的,排序和分组使用的索引不计入
ref
如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func
rows
mysql估算的需要扫描的行数（不是精确值）,越小越好
filtered
最终展示的结果条数 占 扫描的条数的百分比, 越大越好
extra
辅助描述信息,常见信息:
using index : 出现这个说明mysql使用了覆盖索引，避免访问了表的数据行，效率不错。
using where ：这说明服务器在存储引擎收到行后讲进行过滤。
using temporary ：这意味着mysql对查询结果进行排序的时候使用了一张临时表
using filesort ：这个说明mysql会对数据使用一个外部的索引排序
注意当出现using temporary 和 using filesort时候说明需要优化操作
总结
sql优化时,先看 type 尽量将连接类型提高,可以通过加索引或者优化查询条件等, 其次再优化 rows 和 filtered 字段。
explain中id越大优先级越高还是越低？ 越高
explain中type字段的字段值：range，index，ALL，ref，const，按照从好到差排列
const ，ref ，range，index，ALL
explain中rows的值 越大越好，还是越小越好？越小越好
explain中filtered的值 越大越好，还是越小越好？越大越好
