进阶级-高级SQL查询

最新推荐文章于 2024-07-29 22:50:36 发布

m18779186977

最新推荐文章于 2024-07-29 22:50:36 发布

阅读量334

点赞数

分类专栏：编程-MySQL / 缓存文章标签： php MySQL

本文链接：https://blog.csdn.net/m18779186977/article/details/50833944

版权

编程-MySQL / 缓存专栏收录该内容

3 篇文章 0 订阅

订阅专栏

概念篇
- 关系
- 数据库结构的缓存
代码篇
结语

概念篇：

关系：

一对一关系（one-to-one relationship）：仅需一个表。譬如每个作者都有自己的昵称，每个昵称都对应一个作者，所以放于一表即可1。
多对一关系（many-to-one relationship）：两个表才能完成包容。譬如，在文章表中，每个文章都有一位作者，如果作者修改了自己的信息（修改了作者表），将导致数据版本差异；故而通过ID连接两个表。
一对多关系（one-to-many relationship）：与上类似，但有额外的问题，譬如作者可能有两个邮箱，新手往往会选择将邮箱的字节数扩大，造成代码冗余和空间浪费。
多对多关系（many-to-many relationship）：举栗：每个人都有多种情绪，如果“情绪列”存储可存储大量的情绪词，则导致了一对多关系中提到的问题。可以通过使用查找表（lookup table）来联系两个表。

数据库结构的缓存

数据库的结构被缓存在information_schema，当管理者需要去查找未知表内的字段，或者其他需求，都可以通过该表来完成初步查询。

代码篇：

不同

（DISTINCT）：防止输出重复结果行。

SELECT DISTINCT col1_name, /* etc. */ FROM table_name

连接

（INNER JOIN）：处理多个表中相关联的数据，将其形成为一个表。

SELECT columns
FROM table1 INNER JOIN table2
    ON condition(s) for data to be related

　　实例：

SELECT joke.id, LEFT(joketext,20), name
FROM joke INNER JOIN author
    ON authorid = author.id

SELECT joketext
FROM joke INNER JOIN author
    ON authorid = author.id
WHERE name = "Joan Smith"

SELECT author.name
FROM joke INNER JOIN author
    ON authorid = author.id
INNER JOIN jokecategory
    ON joke.id = jokeid
INNER JOIN category
    ON categoryid = category.id
WHERE category.name = "Knock-knock"

别名

（AS），避免多次连接同样的表，并可以修正显示的列名称。

SELECT flight.number, orgin.name, destination.name
FROM flight INNER JOIN city AS origin
    ON flight.organizationid= origin.id
INNER JOIN city AS destination
    ON destination.organizationid= destination.id

简单优化：

SELECT flight.number, orgin.name AS origin, destination.name AS destination
FROM flight AS f INNER JOIN city AS o
    ON f.organizationid= o.id
INNER JOIN city AS d
    ON f.organizationid= d.id

分组

（GROUP BY），“列函数对于GROUP BY子句定义的每个组，各返回一个结果”2，这样大大增强了查询的高效性。

SELECT author.name, COUNT(*) AS numjokers
FROM joke INNER JOIN author
    ON authorid = author.id
GROUP BY authorid
# authorid 在这里等同于author.id

左连接

（LEFT JOIN），当我们试图索引一个 作者名|名下笑话数量 格式的列表时，在常规情况下，没有发布笑话的作者（未满足ON的条件），不会出现在列表中。左连接可以恰到好处的解决这个问题。

SELECT author.name, COUNT(joke.id) AS numjokers
FROM author LEFT JOIN joke
    ON authorid = author.id
GROUP BY author.id

重点Tips：
1. LEFT JOIN只强制左边表中的所有行出现在结果集中，故而必须是author LEFT JOIN joke
2. GROUP BY author.id是必须的，当程序意识到n号作者未发布任何笑话（joke表中，没有他的笑话），将强制创建一条joke，并全部赋值为NULL——包括authorid——结果是所有未发布笑话的作者，都在一个分组里。

限制

（HAVING）,如果我们需要一个只显示 未发布笑话的作者列表 ，该怎样做呢？
We try：

SELECT author.name, COUNT(joke.id) AS numjokers
FROM author LEFT JOIN joke
    ON authorid = author.id
WHERE numjokes = 0
GROUP BY author.id

但仔细思考这段代码，你会发现执行WHERE需要GROUP BY的结果，而GROUP BY又需要WHERE的结果才能完成分组。陷入了一个死循环。

SELECT author.name, COUNT(joke.id) AS numjokers
FROM author LEFT JOIN joke
    ON authorid = author.id
GROUP BY author.id
HAVING numjokes = 0

数据库的结构缓存

SELECT TABLE_NAME FROM information_schema.`COLUMNS` WHERE COLUMN_NAME = 'address'

结语

　　这篇笔记写了很久，一方面是自己忙于生活中的繁杂琐事，另一方面则是对于逻辑跳转的茫然与模糊。
　　HAVING是最难写的，不是因为它复杂，而是因为这次我选了篇幅最小、最为陌生的HAVING开始，然而，HAVING的代码里用到了LEFT JOIN，从LEFT JOIN又追溯到了GROUP BY和AS，往上追索前行，这种感觉就像是——本以为只是带漂亮妹妹压个马路，却顺手牵走了隔壁老王的孩子。
　　这样的过程成功的摧残了我的耐心和专注力，让我不禁对于哲学产生了奇怪的兴趣（共同知识、理性社会、逻辑环，我指这样的哲学）。