Mysql核心之数据查询 有更新!

数据查询是数据库最常用到的功能,熟练掌握查询的知识点是非常必要的。下面主要深入了解Mysql数据库的各种花式查询,如何跟据问题写出一条完美的Sql.

查询语句的执行顺序

一、一条查询语句的分四个部分:
1、从哪个表。
2、查什么?对于查到的数据是mysql的基本数据类型,对于这些数据,我们能够可以进行运算【使用[算数/位]运算符】,使用函数做统计,或者数据处理。。常用聚合函数:Count,SUM ,MIN,MAX…等等。。。
3、过滤条件,数据满足什么条件的情况下获取它,这其中也会用到运算和函数,最常用的是比较运算,查找时间范围,数据范围,判断。。。
4、排序,topn,当我们过滤了数据之后,通常是分组,排序。。或者是获取其中一部分。

sql语句写错可能是遇到最多的问题,主要还是由于没有搞清楚sql的解析过程,执行顺序。
二、sql语句的正确写法

SELECT DISTINCT
    < select_list >
FROM
    < left_table > < join_type >
JOIN < right_table > ON < join_condition >
WHERE
    < where_condition >
GROUP BY
    < group_by_list >
HAVING
    < having_condition >
ORDER BY
    < order_by_condition > 
LIMIT 
	< limit_number >
OFFSET
	< offset_number >

三、sql语句的执行顺序


 1 FROM  <left_table>
 2 ON  <join_condition>
 3 <join_type> JOIN <right_table>
 4 WHERE <where_condition>
 5 GROUP BY <group_by_list>
 6 HAVING <having_condition>
 7 SELECT 
 8 DISTINCT <select_list>
 9 ORDER BY <order_by_condition>
10 LIMIT <limit_number> OFFSET <offset_number > 

在这里有几个比较容易错的点:
1、where/having的异同:
’Group by’作为where与having的分界点,他们都是条件过滤,但是’having在group by之后,什么意思呢?就是说Having是对分组之后的数据聚合。而’where’必须在group by之前。在查询过程中聚合语句(sum,min,max,avg,count)比having子句优先执行.而where子句在查询过程中执行优先级别高于聚合语句(sum,min,max,avg,count)所以呢,要对聚合函数的结果做过滤,只能用having
2、在聚合函数使用和多表联合查询的时候,经常会使用别名,别名是为了方便其他地方用到,但是经常会出现提示别名不存在,那是因为在这个别名还未创建的时候我们就去使用它,例如:

#假设我查询用户表user 的性别sex人数count(sex)>20的数量
select count(sex) as ss from user group by sex having ss>20

这条语句能执行吗?貌似很完美,其实是错误的,ss这个别名根本找不到,回到上面的’执行顺序’我们可以看到,having在select之前执行,而count在select之后,我们只有首先获取到了原数据,才能聚合。所以having执行的时候这个count还没有执行呢,更不能定义ss这个别名了。所以会报错。

四、执行顺序中的核心点
第一部分:1,2,3表示数据从哪里查,这里面最重要的就是多表联合,而核心就是笛卡儿积。如果我们连几张该怎么联合程我郁闷要的虚拟表都不清楚,那么更别说去查询这张表了。

第二部分:4,5,6 表示过滤条件,这部分只要搞清楚where/having使用的场景,以及条件中的运算和函数。就没什么问题。

第三部分:7,8没什么说的,对查询出的数据使用函数如;count,distinct,sum,max.min….

第四部分:9,10固定模式,我们取到最终的数据之后可以排序啊,取topN.

表的并集,笛卡儿积

一、表的并集UNION:并集指得是两组数据的总和并去掉重复项

# UNION使用语法
SELECT expression1, expression2,  ... expression_n FROM tables [WHERE conditions]
UNION [ALL | DISTINCT] 
SELECT expression1, expression2,  ... expression_n FROM tables [WHERE conditions];
# 参数解释
expression1, expression2, ... expression_n 要检索的列。
tables: 要检索的数据表。
WHERE conditions: 可选, 检索条件。
DISTINCT: 可选,删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据,所以 DISTINCT 修饰符对结果没啥影响。
ALL: 可选,返回所有结果集,包含重复数据。
# 示例
select cl1,cl2 cl3 from table1
UNION
select dl1,dl2 dl3 from table2

UNION使用时要保证数据格式相同,数据格式指的是列数相同,类型相同。并集之后相当于同一张表,一张表的数据必须有相同的列,相同的数据类型。
二、笛卡儿积:
笛卡儿积在sql中实现的是交叉连接,它是关系代数中的一个概念,表示的是任意两个数据进行组合形成一条新的数据。它本身的意义不大,在sql加上限制条件才据有意义。
00c7bb6182af4e14bad9312ea8caf5ff-image.png

单表查询VS多表查询

数据库中的查询分为两个部分,单表和多表查询,他们各自的特点和联系是什么?单表查询就是一次查询只操作一张表,取某几行数据进行,分组,统计,去重,过滤,排序,top,等函数操作。而多表查询只是同时操作多张表获取想要的数据,进行一系列的函数操作。多表查询首先会将多个表做笛卡儿积形成一张表,然后通过连接条件删除无用的数据,此时就得到了一张我们所需要的数据表,最后同单表查询一样,在这张数据表中查询(通过条件)我们所需要的数据。

内联接(INNER JOIN或JOIN),外联接(OUTER JOIN),全联接

以stuinfo和score表作为测试数据(stuinfo表示学生信息,score表示成绩,通过stu_id学号关联)数据如下:

mysql> select * from stuinfo;
+--------+----------+---------+
| stu_id | stu_name | stu_sex |
+--------+----------+---------+
| 123401 | Amin     | 1       |
| 123402 | DSin     | 0       |
| 123403 | Wande    | 1       |
| 123404 | Ximi     | 0       |
+--------+----------+---------+
mysql> select * from score;
+----------+---------+--------+
| score_id | stu_id  |  score |
+----------+---------+--------+
|        1 | 123403  |   80   |
|        2 | 123405  |   90   |
|        3 | 123401  |   88   |
|        4 | 123402  |   79   |
+----------+---------+--------+

一、内联接:INNER JOIN ON 和JOIN ON

INNER JOIN == JOIN,他们是完全等同的,JOIN可以看作是INNER JOIN的缩写,内连接的重点是根据连接条件ON,只获取满足条件的部分数据因此对以上两表做内链接结果如下:

mysql> SELECT a.stu_id a_stu_id,b.stu_id b_stu_id,a.stu_name,a.stu_sex,b.score FROM stuinfo a JOIN score b ON a.stu_id=b.stu_id;
+----------+----------+----------+---------+-------+
| a_stu_id | b_stu_id | stu_name | stu_sex | score |
+----------+----------+----------+---------+-------+
|   123401 |   123401 | Amin     | 1       |    88 |
|   123402 |   123402 | DSin     | 0       |    79 |
|   123403 |   123403 | Wande    | 1       |    80 |
+----------+----------+----------+---------+-------+

二、外联接:OUTER JOIN

1、左外连接:LEFT OUTER JOIN ON == LEFT JOIN ON

mysql> SELECT a.stu_id a_stu_id,b.stu_id b_stu_id,a.stu_name,a.stu_sex,b.score FROM stuinfo AS a LEFT OUTER JOIN score AS b ON a.stu_id=b.stu_id;
+----------+----------+----------+---------+-------+
| a_stu_id | b_stu_id | stu_name | stu_sex | score |
+----------+----------+----------+---------+-------+
|   123403 |   123403 | Wande    | 1       |    80 |
|   123401 |   123401 | Amin     | 1       |    88 |
|   123402 |   123402 | DSin     | 0       |    79 |
|   123404 |     NULL | Ximi     | 0       |  NULL |
+----------+----------+----------+---------+-------+

2、右外连接:RIGHT OUTER JOIN ON == RIGHT JOIN ON

mysql> SELECT a.stu_id a_stu_id,b.stu_id b_stu_id,a.stu_name,a.stu_sex,b.score FROM stuinfo AS a RIGHT OUTER JOIN score AS b ON a.stu_id=b.stu_id;
+----------+----------+----------+---------+-------+
| a_stu_id | b_stu_id | stu_name | stu_sex | score |
+----------+----------+----------+---------+-------+
|   123401 |   123401 | Amin     | 1       |    88 |
|   123402 |   123402 | DSin     | 0       |    79 |
|   123403 |   123403 | Wande    | 1       |    80 |
|     NULL |   123405 | NULL     | NULL    |    90 |
+----------+----------+----------+---------+-------+

这两条语句很清楚的展示了左外连接右外连接的差异,简单一句话来说左外连接保存左表所有数据,保存右表中满足条件的数据。右外联接保存右表所有数据,保存左表满足条件的数据。[ A inner join B,A表成为左表,B表称为右表]
惊不惊喜,意不意外,我**,谁TM告诉我MYsql有108种连接的,【连接JOIN,内联接INNER JOIN,左联接Left JOIN,左外连接Left OUTER JOIN,右连接RIGHT JOIN ,右外联接RIGHT OUTER JOIN。】

三、全联接:FULL JOIN

FULL JOIN == FULL OUTER JOIN全链接==全外连接这句话是对的(适用于oracle),但是在这里不适用,因为MYSQL没有FULL OUTER JOIN,并且Mysql中的FULL JOIN 与Oracle中的FULL JOIN 完全不是一个东西,下面我们来看看Mysql的FULL JOIN。

///\\先我们来猜一猜stuinfo与score Full JOIN 的结果是什么?我猜如下:

+----------+----------+----------+---------+-------+
| a_stu_id | b_stu_id | stu_name | stu_sex | score |
+----------+----------+----------+---------+-------+
|   123401 |   123401 | Amin     | 1       |    88 |
|   123402 |   123402 | DSin     | 0       |    79 |
|   123403 |   123403 | Wande    | 1       |    80 |
|     NULL |   123405 | NULL     | NULL    |    90 |
|   123404 |     NULL | Ximi     | 0       |  NULL |
+----------+----------+----------+---------+-------+

按前面的套路,FULL JOIN 不应该就是保留所有表的数据吗? SQl验证我们的结果:

mysql> SELECT * FROM stuinfo  FULL  JOIN score ;
+--------+----------+---------+----------+--------+-------+
| stu_id | stu_name | stu_sex | score_id | stu_id | score |
+--------+----------+---------+----------+--------+-------+
| 123401 | Amin     | 1       |        1 | 123403 |    80 |
| 123402 | DSin     | 0       |        1 | 123403 |    80 |
| 123403 | Wande    | 1       |        1 | 123403 |    80 |
| 123404 | Ximi     | 0       |        1 | 123403 |    80 |
| 123401 | Amin     | 1       |        2 | 123405 |    90 |
| 123402 | DSin     | 0       |        2 | 123405 |    90 |
| 123403 | Wande    | 1       |        2 | 123405 |    90 |
| 123404 | Ximi     | 0       |        2 | 123405 |    90 |
| 123401 | Amin     | 1       |        3 | 123401 |    88 |
| 123402 | DSin     | 0       |        3 | 123401 |    88 |
| 123403 | Wande    | 1       |        3 | 123401 |    88 |
| 123404 | Ximi     | 0       |        3 | 123401 |    88 |
| 123401 | Amin     | 1       |        4 | 123402 |    79 |
| 123402 | DSin     | 0       |        4 | 123402 |    79 |
| 123403 | Wande    | 1       |        4 | 123402 |    79 |
| 123404 | Ximi     | 0       |        4 | 123402 |    79 |
+--------+----------+---------+----------+--------+-------+
16 rows in set (0.00 sec)

*^( ̄(oo) ̄)^惊不惊喜,意不意外????,没错,–MYSQL中的FULL JOIN全链接完全就是=笛卡儿积=


如何实现我们需要的全联接,即我预想的结果,在MySQL中可以如下实现:【对左连接和右连接的结果做并集】

mysql> SELECT a.stu_id a_stu_id,b.stu_id b_stu_id,a.stu_name,a.stu_sex,b.score FROM stuinfo AS a LEFT OUTER JOIN score AS b ON a.stu_id=b.stu_id
    -> UNION
    -> SELECT a.stu_id a_stu_id,b.stu_id b_stu_id,a.stu_name,a.stu_sex,b.score FROM stuinfo AS a RIGHT OUTER JOIN score AS b ON a.stu_id=b.stu_id ;
+----------+----------+----------+---------+-------+
| a_stu_id | b_stu_id | stu_name | stu_sex | score |
+----------+----------+----------+---------+-------+
|   123403 |   123403 | Wande    | 1       |    80 |
|   123401 |   123401 | Amin     | 1       |    88 |
|   123402 |   123402 | DSin     | 0       |    79 |
|   123404 |     NULL | Ximi     | 0       |  NULL |
|     NULL |   123405 | NULL     | NULL    |    90 |
+----------+----------+----------+---------+-------+

总结:

**//\\sql标准的联合VN图:

数据的联接,交并,只是第一步,复杂的业务往往是对某中数据的获取,和操作上。聚合函数,比较运算...这些很基础的东西最终才是制胜的关键。

补充LIMIET,OFFSET

mysql>SELECT stu_id FROM stuinfo LIMIT 3 OFFSET 0 --offset 0,偏移量表示从0开始,LIMIT 3表示取3条数据。 

mysql>SELECT stu_id FROM stuinfo LIMIT 2,3        --LIMIT 2,3表示从2开始取3条 

mysql>SELECT stu_id FROM stuinfo LIMIT 3          --LIMIT 3表示从头开始取3条 == LIMIT 0,3

MySQL里对LIMIT OFFSET的处理方式是,取出OFFSET+LIMIT的所有数据,然后去掉OFFSET,返回底部的LIMIT。所以,在OFFSET数值较大时,MySQL的查询性能会非常低。
…待续

. - - —— ————THE END——— —— - - .

⚠求而不得,往往不求而得!
⚠此文章为原创作品,转载务必保留本文地址及原作者。

评论

发表评论

validate