怎样写优雅的SQL原生语句
前言:
上一篇讲Mysql根本架构时,以"sql查询语句在MySql架构中详细是如何施行的" 停止了全面的讲解。知道了sql查询语句在MySql架构中的详细施行流程,但是为了能够更好更快的写出sql语句,我觉得非常有必要知道sql语句中各子句的施行次序。看过上一篇文章的小伙伴应当都知道,sql语句最后各子句的施行应当是在施行器中完成的,储备引擎对施行器供给的数据读写接口。此刻开端我们的学习
语句中各子句完全施行次序概括(依照次序号施行)
from (注:这里也包罗from中的子语句)
join
on
where
group by(开端使用select中的别号,后面的语句中都可以使用)
avg,sum.... 等聚合函数
having
select
distinct
order by
limit
每个子句施行次序剖析
所有的 查询语句都是从from开端施行的,在施行历程中,每个步骤都会为下一个步骤生成一个虚拟表,这个虚拟表将作为下一个施行步骤的输入。
1. from
form是一次查询语句的开端。
假如是一张表,会直接操纵这张表;
假如这个from后面是一个子查询,会先施行子查询中的内容,子查询的结果也就是第一个虚拟表T1。(留意:子查询中的施行流程也是依照本篇文章讲的次序哦)。
假如需要关联表,使用join,请看2,3
2. join
假如from后面是多张表,join关联,会第一对前两个表施行一个笛卡尔乘积,这时候就会生成第一个虚拟表T1(留意:这里会选中相对小的表作为根基表);
3. on
对虚表T1停止ON挑选,只要那些相符的行才会被记载在虚表T2中。(留意,这里的这里假如还有第三个表与之关联,会用T2与第三个表停止笛卡尔乘积生产T3表,连续反复3. on步骤生成T4表,不外下面的次序讲解临时不针对这里的T3和T4,只是从一个表关联查询T2连续说)
4. where
对虚拟表T2停止WHERE前提过滤。只要相符的记载才会被插入到虚拟表T3中。
5.group by
group by 子句将中的独一的值组合成为一组,得到虚拟表T4。假如利用了group by,那么后面的所有步骤都只能操纵T4的列或者是施行6.聚合函数(count、sum、avg等)。(留意:缘由在于分组后终究的结果集中只包括每个组中的一行。谨记,不然这里会显现许多问题,下面的代码误区会特殊说。)
6. avg,sum.... 等聚合函数
聚合函数只是对分组的结果停止一些处置,拿到某些想要的聚合值,例如求和,统计数目等,并不生成虚拟表。
7. having
利用having挑选器,生成T5。HAVING子句主要和GROUP BY子句配合使用,having挑选器是第一个也是为独一一个利用到已分组数据的挑选器。
8. select
施行select操纵,选中指定的列,插入到虚拟表T6中。
9. distinct
对T6中的记载停止去重。移除雷同的行,发生虚拟表T7.(留意:事实上假如利用了group by子句那么distinct是余外的,缘由一样在于,分组的时候是将列中独一的值分成一组,同时只为每一组返回一行记载,那么所以的记载都将是不雷同的。 )
10. order by
利用order by子句。依照order_by_condition排序T7,此时返回的一个游标,而不是虚拟表。sql是基于汇合的理论的,汇合不会预先对他的行排序,它只是成员的逻辑汇合,成员的次序是可有可无的。对表停止排序的查询可以返回一个对象,这个对象包括特定的物理次序的逻辑组织。这个对象就叫游标。
oder by的几点说明
由于order by返回值是游标,那么使用order by 子句查询不克不及利用于表表达式。
order by排序是很需要成本的,除非你必需要排序,不然最好不要指定order by,
order by的两个参数 asc(升序摆列) desc(落序摆列)
11. limit
取出指定行的记载,发生虚拟表T9, 并将结果返回。
limit后面的参数可以是 一个limit m ,也可以是limit m n,表示从第m条到第n条数据。
(留意:许多开发人员喜爱使用该语句来解决分页问题。关于小数据,使用LIMIT子句没有任何问题,当数据量非常大的时候,使用LIMIT n, m是非常低效的。由于LIMIT的机制是每次都是从头开端扫描,假如需要从第60万行开端,读取3条数据,就需要先扫描定位到60万行,然后再停止读取,而扫描的历程是一个非常低效的历程。所以,关于大数据处置时,是非常有必要在利用层创立必然的缓存机制)
开发某需求写的一段sql
SELECT `userspk`.`avatar` AS `user_avatar`, `a`.`user_id`, `a`.`answer_record`, MAX(`score`) AS `score`FROM (select * from pkrecord order by score desc) as a INNER JOIN `userspk` AS `userspk` ON `a`.`user_id` = `userspk`.`user_id`WHERE `a`.`status` = 1 AND `a`.`user_id` != 'm_6da5d9e0-4629-11e9-b5f7-694ced396953' GROUP BY `user_id`ORDER BY `a`.`score` DESC LIMIT 9;
查询结果:
先简要说一下我要查询的内容:
想要查询pk记载表中分数最高的9个会员记载和他们的头像。
通过这段sql实际想一遍sql各字句的施行次序
pk记载表的数据构造设计,每个会员每天每个馆下大概会有多笔记录,所以需要停止分组,并且查询结果只想拿到每个分组内最高的那笔记录。
这段sql的一些说明:
大概有些同学会认为子查询没有必要 直接查询pk记载表就可以,但是并不克不及拿到预测的结果,由于分组后的每个组结果是不停止排序的,并且max拿到的最高分数必定是对应的该分组下最高分数,但是其它记载大概就不是最高分数对应的那笔记录。所以子查询非常有必要,它能够对原始的数据第一停止排序,分数最高的那条就是第一条对应的第一笔记录。
看一下代码和施行结果与带有子查询的停止比力,就能懂得我上面说的一段话:
//不使用子查询SELECT `userspk`.`avatar` AS `user_avatar`, `pkrecord`.`user_id`, `pkrecord`.`answer_record`, `pkrecord`.`id`, MAX(`score`) AS `score`FROM pkrecordINNER JOIN `userspk` AS `userspk` ON `pkrecord`.`user_id` = `userspk`.`user_id`WHERE `pkrecord`.`status` = 1 AND `pkrecord`.`user_id` != 'm_6da5d9e0-4629-11e9-b5f7-694ced396953' GROUP BY `user_id`ORDER BY `pkrecord`.`score` DESC LIMIT 9;
查询结果
2. 在子查询中对数据已经停止排序后,外层排序方式假如和子查询排序分数雷同,都是分数倒序,外层的排序可以去除,没有必要写两遍。
sql语句中的别号
别号在哪些状况使用
在 SQL 语句中,可认为表名称及字段(列)名称指定别号
表名称指定别号
同时查询两张表的数据的时候: 未设定别号前:
SELECT article.title,article.content,user.username FROM article, userWHERE article.aid=1 AND article.uid=user.uid
设定别号后:
SELECT a.title,a.content,u.username FROM article AS a, user AS u where a.aid=1 and a.uid=u.uid
好处:使用表别号查询,可以使 SQL 变得简约而更易书写和阅读,特别在 SQL 比力复杂的状况下
查询字段指定别号
查询一张表,直接对查询字段设定别号
SELECT username AS name,email FROM user
查询两张表
好处:字段别号一个明显的结果是可以自定义查询数据返回的字段名;当两张表有雷同的字段需要都被查询出,使用别号可以完善的停止区分,幸免冲突
SELECT a.title AS atitle,u.username,u.title AS utitle FROM article AS a, user AS u where a.uid=u.uid
关联查询时候,关联表本身的时候,一些分类表,必需使用别号。
别号也可以在group by与having的时候都可使用
别号可以在order by排序的时候被使用
查看上面一段sql
delete , update MySQL都可以使用别号,别号在多表(级联)删除尤其有用
delete t1,t2 from t_a t1 , t_b t2 where t1.id = t2.id
子查询结果需要使用别号
查看上面一段sql
别号使用留意事项
虽然定义字段别号的 AS 关键字可以省略,但是在使用别号时候,倡议不要省略 AS 关键字
书写sql语句的留意事项
书写标准上的留意
字符串类型的要加单引号
select后面的每个字段要用逗号分隔,但是最后连着from的字段不要加逗号
使用子查询创立暂时表的时候要使用别号,不然会报错。
为了增强机能的留意
不要使用“select * from ……”返回所有列,只检索需要的列,可幸免后续因表构造转变致使的不必要的程序修改,还可落低额外耗损的资源
不要检索已知的列
select user_id,name from User where user_id = ‘10000050’
使用可参数化的搜索前提,如=, >, >=, <, <=, between, in, is null乃至like ‘%’;尽量不要使用非参数化的负向查询,这将致使没法使用索引,如<>, !=, !>, !<, not in, not like, not exists, not between, is not null, like ‘%’
当需要验证可否有相符前提的记载时,使用exists,不要使用count(*),前者在第一个匹配记载处返回,后者需要遍历所有匹配记载
Where子句中列的次序与需使用的索引次序保持一致,不是所有数据库的优化器都能对此次序停止优化,保持良好编程习惯(索引相关)
不要在where子句中对字段停止运算或函数(索引相关)
如where amount / 2 > 100,即便amount字段有索引,也没法使用,改成where amount > 100 * 2就可使用amount列上的索引
如where substring( Lastname, 1, 1) = ‘F’就没法使用Lastname列上的索引,而where Lastname like ‘F%’或者where Lastname >= ‘F’ and Lastname < ‘G’就可以
在有min、max、distinct、order by、group by操纵的列上建索引,幸免额外的排序开销(索引相关)
当心使用or操纵,and操纵中任何一个子句可使用索引都会提高查询机能,但是or前提中任何一个不克不及使用索引,都将致使查询机能下落,如where member_no = 1 or provider_no = 1,在member_no或provider_no任何一个字段上没有索引,都将致使表扫描或聚簇索引扫描(索引相关)
Between一样比in/or高效得多,假如能在between和in/or前提中选中,那么始终选中between前提,并用>=和<=前提组合替换between子句,由于不是所有数据库的优化器都能把between子句改写为>=和<=前提组合,假如不克不及改写将致使没法使用索引(索引相关)
调整join操纵次序以使机能最优,join操纵是自顶向下的,尽量把结果集小的两个表关联放在前面,可提高机能。(join相关) 留意:索引和关联我会独自拿出来两篇文章停止具体讲解,在这个留意事项中只是简便提一下。
更多MySQL相关技术文章,请拜访MySQL教程栏目停止学习!
以上就是怎样写文雅的SQL原生语句的具体内容,更多请关注百分百源码网其它相关文章!