三、mysql进阶查询（2）
3.1 GROUP BY
对GROUP BY后面的字段的查询结果进行汇总分组，通常是结合聚合函数一起使用的；
GROUP BY 有一个原则，凡是在 GROUP BY 后面出现的字段，必须在 SELECT 后面出现；
凡是在 SELECT 后面出现的、且未在聚合函数中出现的字段，必须出现在 GROUP BY 后面。
语法：SELECT "字段1", SUM("字段2") FROM "表名" GROUP BY "字段1"; ---对store_name进行分组，并对sales降序排序 mysql> SELECT store_name, SUM(Sales) FROM store_info GROUP BY store_name ORDER BY SUM(Sales) desc; +-------------+------------+ | store_name | SUM(Sales) | +-------------+------------+ | Los Angeles | 1800 | | Boston | 700 | | Houston | 250 | +-------------+------------+ 3 rows in set (0.00 sec)
3.2 HAVING
HAVING 语句的存在弥补了 WHERE 关键字不能与聚合函数联合使用的不足。
语法：SELECT "字段1", SUM("字段2") FROM "表格名" GROUP BY "字段1" HAVING (函数条件); mysql> SELECT store_name, SUM(Sales) FROM store_info GROUP BY store_name having sum(sales)>1000; +-------------+------------+ | store_name | SUM(Sales) | +-------------+------------+ | Los Angeles | 1800 | +-------------+------------+
3.3 别名
语法：SELECT "表格別名"."字段1" [AS] "字段別名" FROM "表格名" [AS] "表格別名"; ---字段设置别名 mysql> SELECT store_name, SUM(Sales) as total FROM store_info GROUP BY store_name having sum(sales)>1000; +-------------+-------+ | store_name | total | +-------------+-------+ | Los Angeles | 1800 | +-------------+-------+ 1 row in set (0.00 sec) ---表别名 mysql> SELECT store_name, SUM(Sales) FROM store_info as a GROUP BY a.store_name ; +-------------+------------+ | store_name | SUM(Sales) | +-------------+------------+ | Boston | 700 | | Houston | 250 | | Los Angeles | 1800 | +-------------+------------+ 3 rows in set (0.00 sec)
3.4 表的自我连接
---通过对分数排名 mysql> select * from student; +------+----------+-------+ | id | name | score | +------+----------+-------+ | 1 | zhangsan | 70 | | 2 | lisi | 100 | | 3 | wangwu | 80 | | 4 | zhaoliu | 90 | +------+----------+-------+ mysql> select A.name,A.score,count(A.score) rank from student as A,student as B where A.score<=B.score group by A.name,A.score order by rank asc; +----------+-------+------+ | name | score | rank | +----------+-------+------+ | lisi | 100 | 1 | | zhaoliu | 90 | 2 | | wangwu | 80 | 3 | | zhangsan | 70 | 4 | +----------+-------+------+ 4 rows in set (0.00 sec)
3.5 子查询
连接表格，在WHERE 子句或 HAVING 子句中插入另一个 SQL 语句 语法：SELECT "字段1" FROM "表格1" WHERE "字段2" [比较运算符] #外查询 (SELECT "字段1" FROM "表格2" WHERE "条件"); #内查询 #可以是符号的运算符，例如 =、>、<、>=、<= ；也可以是文字的运算符，例如 LIKE、IN、BETWEEN
---多表连接 mysql> select * from store_info A, location B where A.store_name=B.store_name; +-------------+-------+------------+--------+-------------+ | Store_Name | Sales | Date | region | store_name | +-------------+-------+------------+--------+-------------+ | Boston | 700 | 2020-12-08 | East | Boston | | Los Angeles | 1500 | 2020-12-05 | West | Los Angeles | | Los Angeles | 300 | 2020-12-08 | West | Los Angeles | +-------------+-------+------------+--------+-------------+ 3 rows in set (0.00 sec)
mysql> select * from store_info; +-------------+-------+------------+ | Store_Name | Sales | Date | +-------------+-------+------------+ | Los Angeles | 1500 | 2020-12-05 | | Houston | 250 | 2020-12-07 | | Los Angeles | 300 | 2020-12-08 | | Boston | 700 | 2020-12-08 | +-------------+-------+------------+ 4 rows in set (0.01 sec) mysql> select store_name from location where region='west'; +-------------+ | store_name | +-------------+ | Los Angeles | | Houstion | +-------------+ 2 rows in set (0.00 sec) mysql> select sum(sales) from store_info where store_name in (select store_name from location where region='west'); +------------+ | sum(sales) | +------------+ | 1800 | +------------+ 1 row in set (0.00 sec)
3.6 EXISTS
用来测试内查询有没有产生任何结果，类似布尔值是否为真
#如果有的话，系统就会执行外查询中的SQL语句。若是没有的话，那整个 SQL 语句就不会产生任何结果。 语法：SELECT "字段1" FROM "表格1" WHERE EXISTS (SELECT * FROM "表格2" WHERE "条件"); mysql> select sum(sales) from store_info where exists(select * from location where region = 'west'); +------------+ | sum(sales) | +------------+ | 2750 | +------------+ 1 row in set (0.00 sec) mysql> select sum(sales) from store_info where exists(select * from location where region = 'westeee'); +------------+ | sum(sales) | +------------+ | NULL | +------------+ 1 row in set (0.00 sec)
3.7 连接查询
1.inner join(内连接):只返回两个表中联结字段相等的行
2.left join(左连接):返回包括左表中的所有记录和石表中联结字段相等的记录
3.right join(右连接):返回包括右表中的所有记录和左表中联结字段相等的记录
3.7.1 inner join(内连接)
#连接两个表中字段记录相等的数据记录 方法一: select * from location A inner join store_info B on A.store_name=B.store_name; 方法二: select * from location A, store_info B where A.store_name=B.store_name; #连接两个表内字段数据记录相等的数据记录对region字段进行汇总分组并求和 select A.region region,sum(B.sales) sales from location A,store_info B where A.store_name = B.store_name group by region;
3.7.2 left join(左连接)
mysql> select * from location A LEFT JOIN store_info B on A.store_name=B.store_name; +--------+-------------+-------------+-------+------------+ | Region | Store_Name | Store_Name | Sales | Date | +--------+-------------+-------------+-------+------------+ | East | Boston | Boston | 700 | 2020-12-08 | | East | New York | NULL | NULL | NULL | | West | Los Angeles | Los Angeles | 1500 | 2020-12-05 | | West | Los Angeles | Los Angeles | 300 | 2020-12-08 | | West | Houston | Houston | 250 | 2020-12-07 | +--------+-------------+-------------+-------+------------+ 5 rows in set (0.01 sec)
3.7.3 right join(右连接)
mysql> select * from location A RIGHT JOIN store_info B on A.store_name=B.store_name; +--------+-------------+-------------+-------+------------+ | Region | Store_Name | Store_Name | Sales | Date | +--------+-------------+-------------+-------+------------+ | West | Los Angeles | Los Angeles | 1500 | 2020-12-05 | | West | Houston | Houston | 250 | 2020-12-07 | | West | Los Angeles | Los Angeles | 300 | 2020-12-08 | | East | Boston | Boston | 700 | 2020-12-08 | | NULL | NULL | lll | 758 | 2020-12-10 | +--------+-------------+-------------+-------+------------+ 5 rows in set (0.00 sec)
