SQL基础应用-DQL（select）

使用的是mysql官网的world.sql练习

DQL介绍

select
show

select语句的应用

select单独使用的情况

select @@port;   --查看端口
select @@basedir; --查看程序所在目录
select @@server_id; --查看server_id

mysql> select database();  --查看当前所在的库
+------------+
| database() |
+------------+
| world      |
+------------+
1 row in set (0.00 sec)

mysql> select now();    --查看当前时间
+---------------------+
| now()               |
+---------------------+
| 2021-12-03 07:43:11 |
+---------------------+
1 row in set (0.00 sec)

select通用语法(单表)

select 列
from 表
where 条件
group by 条件
having 条件
order by 条件
limit

select 配合 from 子句使用

select 列,列,列 from 表

例子：

1. 查询表中所有的信息

use world;
select * from city;

2. 查询表中的name和Population的值

select `Name`,Population from city;

select配合where子句使用

select 列,列,列 from 表 where 过滤条件

例子：

• where等值条件查询

1. 查询中国所有的城市名和人口数

select `Name`,Population from city where CountryCode='CHN';

• where配合不等值查询(> < >= <=)

2. 世界上小于100人是城市，城市名和人口数

select `Name`,Population from city where Population<100;

• where配合逻辑连接符（and or）

3. 查询中国人口数量大于800w的城市名和人口数

select `Name`,Population from city where CountryCode='CHN' AND Population>8000000; 

4. 查询中国或美国的城市名和人口数

select `Name`,Population from city where CountryCode='CHN' OR CountryCode='USA'; 

5. 查询人口数量在500w到600w之间的城市名和人口数

select `Name`,Population from city where Population BETWEEN 5000000 AND 6000000;

• where配合like子句实现模糊查询

6. 查询一下CountryCode中有C开头的城市信息

select * from city where CountryCode like 'C%';
--%不要出现在前面，类似'%C%',影响查询性能，因为不走索引，如果业务中有大量需求，用ES来代替

• where配合in语句

7. 查询中国或美国的城市信息

select `Name`,Population from city where CountryCode in ('CHN','USA');

select配合group by + 聚合函数应用

常用聚合函数介绍

MAX(),MIN(),AVG(),COUNT(),SUM()：计算类最大值，最小值，平均值等
GROUP_CONCAT()：见实例6

GROUP BY

将某列中有共同条件的数据行，分成一组，然后再进行聚合函数操作

例子：

1. 统计每个国家，城市的个数

select CountryCode,COUNT(`Name`) from city GROUP BY CountryCode;

2. 统计每个国家的总人口数

select CountryCode,SUM(Population) from city GROUP BY CountryCode;

3. 统计每个国家省的个数

select CountryCode,COUNT(DISTINCT District) from city GROUP BY CountryCode;
--DISTINCT去重

4. 统计中国每个省的总人口数

select District,SUM(Population) from city where CountryCode='CHN' GROUP BY District;

5. 统计中国每个省城市的个数

select District,COUNT(id) from city where CountryCode='CHN' GROUP BY District;

6. 统计中国每个省城市的名字列表

select District,GROUP_CONCAT(`Name`) from city where CountryCode='CHN' GROUP BY District;

select配合having应用

作用是在GROUP BY后做过滤

例子：

1. 统计所有国家的总人口数量将总人口数大于1亿的过滤出来

select CountryCode,SUM(Population) from city GROUP BY CountryCode HAVING SUM(Population) > 100000000;

select配合ORDER BY子句

给最后的结果进行排序，可以单独使用

例子：

1. 统计所有国家的总人口数量，将总人口数大于5000w的过滤出来，并且按照从大到小顺序排列

select CountryCode,SUM(Population) from city GROUP BY CountryCode HAVING SUM(Population) > 50000000 ORDER BY SUM(Population) DESC;

select配合limit子句

1. 统计所有国家的总人口数量，将总人口数大于5000w的过滤出来，并且按照从大到小顺序排列，只显示前三名

select CountryCode,SUM(Population) from city GROUP BY CountryCode HAVING SUM(Population) > 50000000 ORDER BY SUM(Population) DESC limit 3;

2. 统计所有国家的总人口数量，将总人口数大于5000w的过滤出来，并且按照从大到小顺序排列，只显示4-6行

select CountryCode,SUM(Population) from city GROUP BY CountryCode HAVING SUM(Population) > 50000000 ORDER BY SUM(Population) DESC limit 3,3;
--3,3：前面那个三是过滤前三行，后三行是显示三行

union和union all

多个结果集合并查询的功能

例子：

1. 查询中国或者美国的城市信息

select `Name`,Population from city where CountryCode='CHN'
UNION ALL
select `Name`,Population from city where CountryCode='USA';

union和union all的区别
union all：不做去重复
union：会做去重复

多表连接查询（内连接）

作用：
单表数据不能满足查询需求时

例子：查询世界上小于100人的城市所在的国家名，国土面积，城市名，人口数

select CountryCode,`Name`,Population from city WHERE Population<100;
--找出国家名时PCN再去country表中查询
select `Name`,SurfaceArea from country where `Code`='PCN';

多表连接查询的基本语法

最核心的是，找到多张表之间的关联条件列
列书写时，必须是：表名.列
所有涉及到的查询列，都放在select后
将所有过滤，分组，排序等条件按顺序写在on后

例子：查询世界上小于100人的城市所在的国家名，国土面积，城市名，人口数

SELECT country.`Name`,country.SurfaceArea,city.`Name`,city.Population
from city
JOIN country  --city表关联country
ON city.CountryCode = country.`Code` --两个表的关联条件
WHERE city.Population<100;

多表关联

from A表
JOIN B表
ON A表.a=B表.a
JION C表
ON B表.b=C表.b

别名应用

• 表别名

SELECT cut.`Name`,cut.SurfaceArea,ct.`Name`,ct.Population
from city AS ct
JOIN country AS cut
ON ct.CountryCode = cut.`Code`
WHERE ct.Population<100;

全局调用

• 列别名

SELECT cut.`Name`,cut.SurfaceArea AS 面积,ct.`Name`,ct.Population
from city AS ct
JOIN country AS cut
ON ct.CountryCode = cut.`Code`
WHERE ct.Population<100;
--显示的时候cut.SurfaceArea就会显示面积

可以备having和order调用