数据查询语言DQL

1. 数据查询select

mysql官方提供了一个world库，来供学习使用，world库中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       |                |
+-------------+----------+------+-----+---------+----------------+

1.1 单独使用

1.1.1 select @@xxx 查看系统参数

SELECT @@port;
SELECT @@basedir;
SELECT @@datadir;
SELECT @@socket;
SELECT @@server_id;

1.1.2 select 函数;

-- 查看当前时间
SELECT NOW();
-- 查看当前USE的数据库
SELECT DATABASE();
-- 查看当前用户
SELECT USER();
SELECT CONCAT("hello world");
-- 拼接
SELECT CONCAT(USER,"@",HOST) FROM mysql.user;
-- 将上面的结果输出到一行，以逗号分隔
SELECT GROUP_CONCAT(USER,"@",HOST) FROM mysql.user;

更多详细可以参考官方文档: https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html

1.2 单表子句-from

SELECT * FROM 表;
SELECT 列1, 列2, ... FROM 表;

1.3 单表子句-where

SELECT col1, col2 ... FROM 表名 WHERE 条件

1.3.1 等值查询

SELECT col1, col2 FROM 表名 WHERE 字段=值

1.3.2 比较操作符

SELECT col1, col2... FROM 表名 WHERE 字段 < 值
SELECT col1, col2... FROM 表名 WHERE 字段 > 值
SELECT col1, col2... FROM 表名 WHERE 字段 <= 值
SELECT col1, col2... FROM 表名 WHERE 字段 >= 值
SELECT col1, col2... FROM 表名 WHERE 字段 != 值
SELECT col1, col2... FROM 表名 WHERE 字段 <> 值

1.3.3 逻辑运算符

SELECT col1, col2... FROM 表名 WHERE 条件1 AND 条件2
SELECT col1, col2... FROM 表名 WHERE 条件1 AND 条件2

1.3.4 模糊查询

SELECT col1, col2... FROM 表名 WHERE 字段 LIKE 'xxx%';

注意：%不能放在前面，因为不走索引。

1.3.5 where配合in语句

SELECT col1, col2... FROM 表名 WHERE 字段 IN ('xxx', 'xxx', 'xxx', ...);

1.3.6 where配合between and

SELECT col, col2... FROM 表名 WHERE 字段 BETWEEN 值1 AND 值2

1.4 单表子句-group by

1.4.1 作用

根据by后面的条件进行分组，方便统计，by后面跟一个列或多个列

1.4.2 常用聚合函数

max() -- 最大值
min() -- 最小值
avg() -- 平均值
sum() -- 总和
count() -- 个数
group_concat -- 列转行

1.4.3 示例

1. 统计世界上每个国家的总人口数

SELECT countrycode, SUM(Population) FROM city GROUP BY countrycode;

2. 统计中国各个省的总人口数

SELECT district,SUM(population) FROM city WHERE countrycode = 'CHN' GROUP BY district;

3. 统计世界上每个国家的城市数量

SELECT countrycode, COUNT(id) FROM city GROUP BY countrycode;

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

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

5. 统计中国每个省的城市平均人口

SELECT district,AVG(population) FROM city WHERE countrycode = 'CHN' GROUP BY district;

1.5 单表子句-having

1. 统计中国每个省的总人数，只打印总人口数小于100w的信息

SELECT
  district,
  SUM(population)
FROM
  city
WHERE countrycode = 'CHN'
GROUP BY district
HAVING SUM(population) < 1000000;

注意：having条件是不走索引的，一般可以用临时表解决。

1.6 单表子句-order by

1. 查询中国的城市信息，并按人口数量从大到小排序

SELECT * FROM city WHERE countrycode = 'CHN' ORDER BY population DESC;

2. 统计中国，每个省的总人口，找出大于500w的，并按总人口从大到小排序

SELECT
  district,
  SUM(population)
FROM
  city
WHERE countrycode = 'CHN'
GROUP BY district
HAVING SUM(population) > 5000000
ORDER BY SUM(population) DESC;

1.7 单表子句-limit

1.7.1 limit的使用

1. 显示前M个
LIMIT M
2. 跳过前N行，再显示M行
LIMIT N, M
3. 跳过N行，再显示M行
LIMIT M OFFSET N

1.7.2 示例

1. 统计中国，每个省的总人口，找出大于500w的，并按总人口从大到小排序，只显示前三名

SELECT
  district,
  SUM(population)
FROM
  city
WHERE countrycode = 'CHN'
GROUP BY district
HAVING SUM(population) > 5000000
ORDER BY SUM(population) DESC
LIMIT 3;

1.8 联合查询-unique

1.8.1 用法

将多个查询的结果集合并到一起。

1. UNION—去重
2. UNION ALL—不去重

1.8.2 示例

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

1. 使用IN或者OR的方式
SELECT * FROM city WHERE countrycode IN ('CHN', 'USA');
SELECT * FROM city WHERE countrycode = 'CHN' OR countrycode = 'USA';

2. 使用联合查询的方式
SELECT * FROM city WHERE countrycode = 'CHN'
UNION ALL
SELECT * FROM city WHERE countrycode = 'USA';

说明：一般情况下，会将IN或者OR语句，改写成UNION ALL，来提高性能。

1.9 多表连接

1.9.1 left join

select * from a LEFT JOIN b ON a.x=b.o

表b是为了迎合表a，a为主表，必须显示全;a中有b中没有的，显示为NULL，a中没有b中也没有的，不予显示。

19.2 inner join

相当于对left join进行过滤，如果有NULL则自动清除

select * from a INNER JOIN b ON a.x=b.o