select 通用语法(单表)

1. DQL 介绍
   select
   show

2. select 语句的应用
   2.1 select单独使用的情况***
   mysql> select @@basedir;
   mysql> select @@port;
   mysql> select @@innodb_flush_log_at_trx_commit;
   mysql> show variables like ‘innodb%’;
   mysql> select database();
   mysql> select now();

2.2 select 通用语法(单表) *****
select 列
from 表
where 条件
group by 条件
having 条件 having
order by 条件
limit

2.3 学习环境的说明
world数据库
city 城市表
country 国家表
countrylanguage 国家的语言

city表结构
mysql> desc city;
±------------±---------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±------------±---------±-----±----±--------±---------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | | 0 | |
±------------±---------±-----±----±--------±---------------+

5 rows in set (0.00 sec)
mysql>

ID : 城市序号(1-…)
name : 城市名字
countrycode : 国家代码,例如:CHN,USA
district : 区域: 中国 省 美国 洲
population : 人口数

工作中快速熟悉业务

1. 快速和研发人员打好关系
2. 找到领导要ER图
3. DESC 表名,show create table
4. select * from city limit 5;

2.4 SELECT 配合 FROM 子句使用
– select 列,列,列 from 表
— 例子:

1. 查询表中所有的信息(生产中几乎是没有这种需求的)
   USE world ;
   SELECT id,NAME ,countrycode ,district,population FROM city;
   或者:
   SELECT * FROM city;

2. 查询表中 name和population的值
   SELECT NAME ,population FROM city;

2.5 SELECT 配合 WHERE 子句使用
– select 列,列,列 from 表 where 过滤条件

– where等值条件查询 *****
例子:

1. 查询中国所有的城市名和人口数
   SELECT NAME,population FROM city
   WHERE countrycode=‘CHN’;

– where 配合比较判断查询(> < >= <=) *****
例子:

1. 世界上小于100人的城市名和人口数
   SELECT NAME,population FROM city
   WHERE population<100;

– where 配合 逻辑连接符(and or)
例子:

1. 查询中国人口数量大于1000w的城市名和人口
   SELECT NAME,population FROM city
   WHERE countrycode=‘CHN’ AND population>8000000;

2. 查询中国或美国的城市名和人口数
   SELECT NAME,population FROM city
   WHERE countrycode=‘CHN’ OR countrycode=‘USA’;

3. 查询人口数量在500w到600w之间的城市名和人口数
   SELECT NAME,population FROM city
   WHERE population>5000000 AND population<6000000;
   或者:
   SELECT NAME,population FROM city
   WHERE population BETWEEN 5000000 AND 6000000;

– where 配合 like 子句 模糊查询 *****
例子:

1. 查询一下contrycode中带有CH开头,城市信息

SELECT * FROM city
WHERE countrycode LIKE ‘CH%’;

注意:不要出现类似于 %CH%,前后都有百分号的语句,因为不走索引,性能极差
如果业务中有大量需求,我们用"ES"来替代

– where 配合 in 语句

例子:

1. 查询中国或美国的城市信息.
   SELECT NAME,population FROM city
   WHERE countrycode=‘CHN’ OR countrycode=‘USA’;
   或者:
   SELECT NAME,population FROM city
   WHERE countrycode IN (‘CHN’ ,‘USA’);

2.5.2 GROUP BY
将某列中有共同条件的数据行,分成一组,然后在进行聚合函数操作.
例子:

1. 统计每个国家,城市的个数
   SELECT countrycode ,COUNT(id) 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;

4. 统计中国 每个省的总人口数
   SELECT district, SUM(population) FROM city
   WHERE countrycode=‘CHN’
   GROUP BY district ;

5. 统计中国 每个省城市的个数
   SELECT district, COUNT(NAME) FROM city
   WHERE countrycode=‘CHN’
   GROUP BY district ;

6. 统计中国 每个省城市的名字列表GROUP_CONCAT()
   guangdong guangzhou,shenzhen,foshan…
   GROUP_CONCAT() 把统计后的数据写入一行,列转成行

SELECT district, GROUP_CONCAT(NAME) FROM city
WHERE countrycode=‘CHN’
GROUP BY district ;

7. 小扩展
   anhui : hefei,huaian …
   SELECT CONCAT(district,":" ,GROUP_CONCAT(NAME)) FROM city
   WHERE countrycode=‘CHN’
   GROUP BY district ;
   CONCAT() 拼接语句,可以自定义分隔符

2.7 SELECT 配合 ORDER BY 子句
ORDER BY 排序功能 desc倒序 asc正序(默认)
例子:

1. 统计所有国家的总人口数量,
   将总人口数大于5000w的过滤出来,
   并且按照从大到小顺序排列
   SELECT countrycode,SUM(population) FROM city
   GROUP BY countrycode
   HAVING SUM(population)>50000000
   ORDER BY SUM(population) DESC ;

2.8 SELECT 配合 LIMIT 子句
limit 显示多少行

例子:

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

SELECT countrycode,SUM(population) FROM city
GROUP BY countrycode
HAVING SUM(population)>50000000
ORDER BY SUM(population) DESC
LIMIT 3 OFFSET 0;

SELECT countrycode,SUM(population) FROM city
GROUP BY countrycode
HAVING SUM(population)>50000000
ORDER BY SUM(population) DESC
LIMIT 3 OFFSET 3

LIMIT M,N :跳过M行,显示一共N行
LIMIT Y OFFSET X: 跳过X行,显示一共Y行

2.9 练习题:

1. 统计中国每个省的总人口数，只打印总人口数小于100w的
   SELECT district ,SUM(population) FROM city
   WHERE countrycode=‘CHN’
   GROUP BY district
   HAVING SUM(population)<1000000;

2. 查看中国所有的城市，并按人口数进行排序(从大到小)
   SELECT * FROM city WHERE countrycode=‘CHN’
   ORDER BY population DESC;

3. 统计中国各个省的总人口数量，按照总人口从大到小排序
   SELECT district ,SUM(population) FROM city
   WHERE countrycode=‘CHN’
   GROUP BY district
   ORDER BY SUM(population) DESC ;

4. 统计中国,每个省的总人口,找出总人口大于500w的,
   并按总人口从大到小排序,只显示前三名
   SELECT district ,SUM(population) FROM city
   WHERE countrycode=‘CHN’
   GROUP BY district
   HAVING SUM(population)>5000000
   ORDER BY SUM(population) DESC
   LIMIT 3;

2.10 小结
select disctrict , count(name) from city
where countrycode=‘CHN’
group by district
having count(name) >10
order by count(name) desc
limit 3;

2.11 union 和 union all
作用: 多个结果集合并查询的功能

需求: 查询中或者美国的城市信息
SELECT * FROM city WHERE countrycode=‘CHN’ OR countrycode=‘USA’;

改写为:
SELECT * FROM city WHERE countrycode=‘CHN’
UNION ALL
SELECT * FROM city WHERE countrycode=‘USA’;

面试题: union 和 union all 的区别 ?
union all 不做去重复
union 会做去重操作