在进一步完成了Leetcode中的数据库练习题后(题目很少,50道题中只有20道左右是免费的),开始整理一下在Leetcode与sqlzoo所遇到的基本句式以及经典思路。
很神奇的是,leetcode的平台对于sql运行不太完善。有些在navicat上运行无误在leetcode上会报错。不过很少,瑕不掩瑜吧。
较长,多图,慎读(Wifi下服用更佳)。
一、特征查找。
这里主要包括四种。
1.精准查找 句式:where XX=XX
2.范围查找 句式:where XX>/< XX where XX between XX and XX
思路:比较容易啦,这里只是需要注意between句式是包含两端的值的。
3.枚举查找 句式:where XX in (XX,XX,XX)
思路:从orders中的订单信息通过distinct获取买过产品的客户集合,再通过not in判断出不在该集合的客户。
4.模糊查找 句式: where XX like 'XX%' %类似网站查询中*的作用,代替未知个数字符;_则代表一个字符。
二、汇总分析
入门级:这里一般是伴随某种特征筛选后汇总的,故而经常将count/sum/max/min/avg与group by 结合使用,有时还会结合having(进一步筛选)、limit(返回指定行数)使用。
思路:先通过class分组出不同课堂,然后通过having与distinct判断各组中学生数是否满足要求。
进阶级:有时候会要求返回某类的前几名。这时候需要注意,这个名次到底是并列算一名还是算多名,这时对应使用rank()、dense_rank()。句式:rank() over(order by XX)
思路:本题中由于要求没有间隔,使用dense_rank()以排名即可。
拓展:如果某时刻附近有连续三个时刻(包括它自身)的车流值为100以上,则称之为高峰期。那么如何判断一个给出各时刻车流值的表格中哪些时刻为高峰期呢?
思路:结合rank的一个较笨的方法,先筛出100以上车流值的时刻,随后根据时刻对其rank排序(由于都是不同时刻,与dense_rank并没有区别)。再计算rank与行号的差值称之为rank1,则rank1相同的行代表相邻连续的时刻,group分组筛出3行以上的数值即可。
三、复杂查询(关联子查询)
从这里开始,创建子表以提供跳板进一步分析开始常见了起来。子表中需要保留什么信息,group by与order by关键字显得愈加重要。经常会出现每组前几名等题型。注意说明的是,由于子表的出现,经常会出现几张表之间重复特证名,因此及时为新生成的子表起别名显得很重要。
思路:先建立个子查询,然后根据原表与子表中对应部门查询,保证大于关系的元素数少于指定数目,借此以筛选出前几名的信息。注意的是,本体中工资前三高,代表的是dense_rank(),即某一工资并列两人,这两个人也只占用同一工资level,而不是普通大众意义上的占用两个名额。
四、联结
这里涉及了内连接inner join(与join几乎没有差别)、左联结left join、右联结 right join。区别则在于以哪个主键为准。
同时涉及on XXX 句式。在join后增加附加条件。
这本质上与from 并列的两个表+where+判断条件逻辑类似。
思路:本体看起来复杂,实际看清特征字段后思路则比较容易。首先需要将两表中的client_id、users_id对应联结,筛选出满足时间要求的行、非禁止的用户,并分组统计状态不为completed的数目、总数目,随后再计算出用户的取消率。
补充:这里还涉及了一个交换的题,由于思路比较新奇,故而也摆放在这里。
思路:根据是否为奇数(与1进行按位与运算),选择更换后的id并更新,联结后重新排序。对于最后一个为奇数时,使用ifnull函数单一赋值。
五、一些不在学习主线上,但是比较有趣的东西
1.函数书写。
整体相差不大,前面增加了个function,与java类似,但将returns类型写在了行末。
思路:主要运用的还是dense_rank(),前面有介绍。放在这里主要是介绍下函数的写法。
需要说明的是,mysql初始默认用户是没有改动函数的权限的,set global一句可以获取这个权限。