mysql c select_常见mysql select语句

最新推荐文章于 2023-01-10 14:49:27 发布

霍冉

最新推荐文章于 2023-01-10 14:49:27 发布

阅读量146

点赞数

文章标签： mysql c select

本文链接：https://blog.csdn.net/weixin_32340879/article/details/113424743

版权

整理一些常见的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，一图胜千言

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

这应该算是一个比较常见又有点棘手的问题。比如某个表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

模拟数据结果如下：

如图，如果要体现父子的缩进关系，最好还是再加一个字段记录层数。但这结果其实看着依然不太对，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

参考链接：