Home » Code » 常见mysql select语句

常见mysql select语句

整理一些常见的mysql select语句,方便查找。

父子同表,通过一个parentid关联(父亲parentid为0儿子的parentid为父亲id)是比较简便常见的做法,先列一些这里边的例子。

列出所有儿子并加上其父亲的某项值

如某回复表(id,content,parentid)列出所有子回复及其父亲的内容:

select a.*,b.content as parentContent from reply a,reply b where a.parentid=b.id

对于上边这个语句,我见识少一时半会儿不懂这是什么写法。这是一个简单的select语句还是有连接(join)或者有联合(union)或者有子查询?这句话的意思看着是以a中的parentid与b中的id相等作为筛选条件得出结果,那么它应该跟内连接INNER JOIN是一样的了。说到这儿先说说SQL都有哪些连接先。

基于 ANSI 标准的 SQL 列出了五种 JOIN 方式: 内连接(INNER), 全外连接(FULL OUTER), 左外连接(LEFT OUTER), 右外连接(RIGHT OUTER)和交叉连接(CROSS)。 在特定的情况下, 一张表(基本表, 视图, 或连接表)可以和自身进行连接, 成为自连接(self-join)。

SQL 定义了两种不同语法方式去表示”连接”。首先是”显式连接符号”,它显式地使用关键字 JOIN,其次是”隐式连接符号”。隐式连接符号把需要连接的表放到 SELECT 语句的 FROM 部分,并用逗号隔开。

内连接INNER JOIN是普遍常用的连接方式,假设有A表和B表,它会将A表中的每一行跟B中的每一行做比较,产生迪卡尔积,然后返回符合条件的记录。实际上数据库会以其他更高效的方式去实现连接,迪卡尔积是非常没效率的做法,尤其在两表数据量大的时候。因此,上边的写法跟下面这个是一样的:

SELECT a.*,b.content as parentContent FROM reply a INNER JOIN reply b ON a.pid=b.id

FROM部分写“,”跟INNER JOIN语义上是等同的,这是所谓的隐式连接!

交叉连接CROSS JOIN就是返回迪卡尔积了,它等价于连接条件永为真或没有连接条件。交叉连接不应该使用ON过滤任何结果,如果用了跟INNER JOIN没有区别(交叉连接是内连接的基础),可以使用where条件进行过滤。显式写法:

SELECT * FROM reply a CROSS JOIN reply b

隐式写法:

SELECT * FROM reply a,reply b

左外连接LEFT OUTER JOIN(OUTER可以省略不写)的原理是,从左表读出一条,选出所有与on匹配的右表纪录(n条)进行连接,形成n条纪录(包括重复的行),如果右边没有与on条件匹配的表,那连接的字段都是NULL,然后继续读下一条。

右外连接RIGHT OUTER JOIN(OUTER可以省略不写)跟左外连接原理完全一致,只是交换一下两表的位置,因此很少用。

全连接FULL OUTER JOIN是左右外连接的并集,结果集包含所有被连接表的所有记录,一方没有的以NULL补充。一些数据库如Mysql并不支持全连接,但可以通过左外连接并上(UNION)右外连接来模拟实现。

各种JOIN,一图胜千言

各种JOIN,一图胜千言

查询所有父代及其所有后代

这应该算是一个比较常见又有点棘手的问题。比如某个表pc(id,name,pid)有爷爷父亲儿子孙子4层,要求将所有数据查询出来就是排列好的树状。Mysql没有递归查询的函数,在层数不确定又比较多的时候很麻烦,这点跟Oracle之类的差别就明显了,听说人家那是很容易就搞定的。现在知道了层数最大为4,按网上大神说的使用INNER JOIN可以实现:

SELECT distinct d.*
FROM pc a
INNER JOIN pc b ON b.pid = a.id
OR b.id = a.id
INNER JOIN pc c ON c.pid = b.id
OR c.id = b.id
INNER JOIN pc d ON d.pid=c.id 
OR d.id=c.id
WHERE a.pid =0
ORDER BY a.id ASC , b.id ASC , c.id ASC ,d.id asc

模拟数据结果如下:

select_parent_child
如图,如果要体现父子的缩进关系,最好还是再加一个字段记录层数。但这结果其实看着依然不太对,id为8的孙子其pid是5应该排在id为5后面才对,这里却是将拥有相同pid的儿子排完了才出来孙子,依然不是想要的结果。但如果层数只有两层,这种方法是绝对没问题的。这里一堆JOIN,虽然是INNER类型的,但如果数据稍大,估计也不好。

查询一定数量父代及其所有后代

只考虑两层的情况了:

SELECT * FROM reply child
INNER JOIN
(SELECT id FROM reply WHERE parentid=0 ORDER BY id DESC limit 2) pids #找出一定数量的父代#
ON child.parentid=pids.id OR child.id=pids.id
ORDER BY pids.id ASC,child.id ASC

对于这个问题:找出一定数量的父代及其一定数量的后代,暂时不懂。

优先显示某些记录

对于“优先显示”的记录与“剩下其他”记录其内部还可以有其他排序方案,就是要保证某些得先显示,可以使用union。例如从reply表中取出所有优先显示id为2和5的两条记录:

select a.* FROM
(SELECT * FROM reply where id IN (2,5) ORDER BY id desc) as a
UNION
select b.* FROM
(SELECT * FROM reply WHERE id NOT IN (2,5) ORDER BY id asc) as b

union要对子句进行排序,必须用括号包起来,且需要作为一个独立结果集进行union才有效。在union之后可以使用order by对整个结果集进行排序,这样会破坏原子句中的排序。

使用子查询作为表达式

以论坛里边的应用为例,要查id为1的用户名以及他发表过的的主题数(thread表),回复数(reply表),后面两表都是通过user_id跟user表关联,这个sql语句如何写?

经验不足的我当时就想到这横向加字段就就是JOIN了,很自然的上个LEFT JOIN:

SELECT a.name, count(b.id) AS threadCount, count(c.id) AS replyCount
FROM user a
LEFT JOIN forum_thread b ON b.user_id = a.id
LEFT JOIN forum_reply c ON c.user_id = a.id
WHERE a.id =1

自己想想都要笑出声来了,去掉WHERE就一清二楚了,随着JOIN的表越多结果就越大,count()是对最终的结果集进行统计,JOIN一次的时候是准确的,两次以上就出问题了,整个结果集通过WHERE筛选后的结果并不是我们需要的。

要使用LEFT JOIN这样子可以:

select user.name,t.tc as threadCount,r.rc as replyCount
from user 
left join 
(select user_id,count(user_id) as tc from forum_thread where user_id=1) 
as t 
on t.user_id=user.id 
left join 
(select user_id,count(user_id) as rc from forum_reply where user_id=1)
as r 
on r.user_id=user.id 
where user.id=1

仔细观察这写法好像就是将子查询作为另一个表与user表进行JOIN,在JOIN之前就已经用条件将结果查询出来了。

其实将子查询用作表达式可能更好看一些:

SELECT name,
(SELECT count(*) FROM forum_thread WHERE user_id=1) AS tCount,
(SELECT count(*) FROM forum_reply WHERE user_id=1) AS rCount 
FROM user 
WHERE id=1

原来是如此简单,还可以这么写,将一个子查询作为SELECT表达式中的一个字段也可以,哎,这东西你不说我真是不懂了,什么都得多学多练,否则你就是一个无知!

2019-03-30更新

返回拥有某字段最大值的记录(The Row Holding the Maximum of a Certain Column),如返回最贵的文章。最容易想到,使用max。

SELECT * from items where price = (select max(price) from items)

另一个方案是使用 left join:

SELECT * from items s1 left join items s2 on s1.price < s2.price

原理一下子不太明白,先看通过条件 s1.price < s2.price 产生的迪卡尔积结果集:

结果集如上,当 s1 为最大值时,s2 中结果是 null 的,因此加上条件 s2.scores is null 就是拥有 scores 最大值的结果了。

另外还有一个文案就是直接排序,不过这时候只能找到一条记录,当有多条记录 price 并列最大时,只返回一条。

SELECT * from items order by price desc limit 1

返回某字段在某组内最大值的记录(The Rows Holding the Group-wise Maximum of a Certain Column),如对于每篇文章,返回最贵的经销商。子查询轻松解决:

SELECT * from items s1 where price = (select max(s2.price) from items s2 where s2.article = s1.article) ORDER BY article

但这可能效率不是太好,可以考虑使用不相关的子查询:

SELECT s1.* from items s1 inner join (select article,max(price) as price from items group by article) s2 on s1.article = s2.article and s1.price = s2.price order by s1.article

或者依据上边的原理,使用 left join:

SELECT s1.* from items s1 left JOIN items s2 on s1.article = s2.article and s1.price < s2.price where s2.price is null

参考链接:

http://zh.wikipedia.org/wiki/%E8%BF%9E%E6%8E%A5_(SQL)
http://blog.sina.com.cn/s/blog_6ad62438010168lg.html
http://blog.csdn.net/hguisu/article/details/5731880
http://blog.csdn.net/ACMAIN_CHM/article/details/4142971

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Time limit is exhausted. Please reload CAPTCHA.