MySQL的select(极客时间学习笔记)

查询语句

首先, 准备数据, 地址是: , 除了id以外, 24个字段的含义如下:

查询

查询分为单列查询, 多列查询, 全部查询等等:

select name from heros; // 单列查询
select name, hp_max, mp_max, attack_max, defense_max from heros; // 多列查询
select * from heros; // 全部查询

学习阶段可以使用select *, 但是在生产环境不要用, 因为效率会非常低.

起别名

起别名是一种技巧, 可以对原有名称进行简化, 让sql语句看起来更加精简, 这个在多表连接查询的时候非常有用:

select name as n, hp_max as hm, mp_max as mm, attack_max as am, defense_max as dm from heros; // 起别名查询

查询常数

select 查询可以对常数进行查询, 简单说就是在select查询结果中增加一列固定的常数列. 一般用于整合不同的数据源, 用常数作为这个表的标记.

select '王者荣耀' as platform, name from heros; // 查询常数

虚构了一个platform字段, 注意的是, 字符串常数要是用单引引起来, 否则会被当做列名来进行查询, 会报错, 如果是数字的话不需要添加单引号.

去除重复行

去除重复行是个非常少实用的操作, 关键字是distinct:

select distinct attack_range from heros; 
select distinct attack_range, name from heros

需要注意的是:

1. distinct需要放在所有的列名之前, 也就是select 之后.
2. distinct其实是对后面所有列名的组合进行去除, 并不是对某一列进行去重. 这也是为什么distinct要放在所有的列名之前的原因.

排序检索数据

检索数据的时候, 可以按照某种顺序来进行结果的返回, 比如说查询所有的英雄, 按照最大生命从高到低的顺序进行排列, 这个时候排序就要使用到 order by 子句了, 注意点如下:

1. 排序的列名: order by后面可以有一个或多个列名, 如果是多个列名进行排序, 会按照后面第一个列先进行排序, 当第一个列的值相同的时候, 再按照第二个列进行排序, 以此类推.
2. 排序的顺序: order by后面可以注明排序规则, asc代表递增排序, desc代表递减排序, 没有注明规则, 默认是递增排序. 还有对数值类型字段的排序很容易理解, 那如果是文本数据呢? 需要参考数据库的设置方式, 才可以进行判断.
3. 非选择列排序: order by 可以使用非选择列进行排序, 也就是说select后面没有这个列名, 同样可以放到order by后面进行排序.
4. order by的位置: order by通常位于select语句的最后一条子句, 否则会报错.

select name, hp_max from heros order by hp_max desc ; // 最大生命排序
sql：select name, hp_max from heros order by mp_max, hp_max desc; // 最大法力升序, 法力相同, 生命值降序排序

约束返回结果的数量

约束返回结果的数量, 使用limit关键字.

select name, hp_max from heros order by hp_max desc limit 5; // 最大生命值从高到低排序并返回前五条数据

---

注意的是在不同的dbms中, 使用的关键字可能不同. 在 mysql、postgresql、mariadb 和 sqlite 中使用 limit 关键字，而且需要放到 select 语句的最后面。如果是 sql server 和 access，需要使用 top 关键字

select top 5 name, hp_max from heros order by hp_max desc;

---

如果是 db2，使用fetch first 5 rows only这样的关键字

select name, hp_max from heros order by hp_max desc fetch first 5 rows only; 

---

如果是 oracle，你需要基于 rownum 来统计行数：

sql：select name, hp_max from heros where rownum <=5 order by hp_max desc;

约束返回结果数量可以减少数据表的网络传输量, 可以提升查询效率.

select的执行顺序

这里要说的是两个顺序, 一个是关键字的顺序, 一个是select语句的执行顺序:

1. 关键字的顺序:

select ... from ... where ... group by ... having ... order by ...

1. select语句的执行顺序

from > where > group by > having > select 的字段 > distinct > order by > limit

---

一条sql语句, 关键字顺序和执行顺序如下:

select distinct player_id, player_name, count(*) as num # 顺序 5
from player join team on player.team_id = team.team_id # 顺序 1
where height > 1.80 # 顺序 2
group by player.team_id # 顺序 3
having num > 2 # 顺序 4
order by num desc # 顺序 6
limit 2 # 顺序 7

在select语句执行这些步骤的时候, 每个步骤都会生成一张虚拟表, 然后将这个虚拟表传入下一个步骤作为输入. 但是这些步骤是隐含在sql的执行过程中, 对我们是不可见的.

sql的执行原理

select是先执行from这一步的, 这个阶段, 如果是多张表联查, 会经历下面的步骤:

1. 首先通过笛卡尔积, 得到虚拟表vt(virtual table)1-1.
2. 通过on进行筛选, 在虚拟表vt1-1的基础上进行筛选, 得到虚拟表vt1-2.
3. 添加外部行. 如果使用的是左连接、右连接或者是全连接, 就会涉及外部行, 也就是虚拟表v1-2的基础上增加外部行, 得到虚拟表vt1-3.

两张以上的表, 会重复上面的步骤, 直到所有的表都被处理完成, 这个过程得到的最后结论就是现在的原始数据.

拿到原始数据之后, 就可以在这个基础上再进行where阶段了. 这个过程会再次得到一个虚拟表,假设为vt2.

之后进入第三步和第四步, 也就是group by和having阶段, 在vt2上进行分组和分组过滤, 得到中间的虚拟表vt3.

上面筛序就完成了, 接下来进入到select阶段, 当然, 是先查询出所需要的列(字段), 之后就会进入到distinct阶段, 这个也是两个阶段, 也会产生虚拟表.

字段选择并过滤重复之后就会进入到order by阶段进行排序, 再次得到虚拟表.

最后进入limit阶段, 得到最终的结果.

在一条sql中, 不存在的关键字, 中间的那部分阶段就会省略, 这些就是底层的原理.(疑惑是查询字段会使用到别名这些, 这些又是如何识别的呢?)

count(*)的优化

1. 一般情况下：count(*) = count(1) > count(字段), 所以尽量使用count(*)，当然如果你要统计的是就是某个字段的非空数据行数，那另当别论。毕竟执行效率比较的前提是要结果一样才行。
2. 如果要统计count(*)，尽量在数据表上建立二级索引，系统会自动采用key_len小的二级索引进行扫描，这样当我们使用select count(*)的时候效率就会提升，有时候提升几倍甚至更高都是有可能的。