using
- 概念
- using用来指定连接字段
- using的结果也会对公共字段进行优化，优化的规则和自然连接是一样的
MariaDB [sel]> select * from grades inner join resume using(name);
+-------+---------+------+----+-----------+
| name | chinese | math | id | skill |
+-------+---------+------+----+-----------+
| Sunny | 93 | 96 | 1 | php |
| Jerry | 97 | 91 | 3 | php,mysql |
+-------+---------+------+----+-----------+
# `2 rows in set (0.001 sec)`
MySQL练习题
显示地区及每个地区参加笔试的人数，并按人数降序排列
- 思路分解
- select 查询字段
- from 多表查询 左外连接
- using 指定连接字段
- group by 分组查询结果
- order by 降序排列
-- 第一步： 显示地区及每个地区参加笔试的人数
mysql> select stuaddress,count(writtenexam) from stuinfo left join stumarks using(stuno) group by stuaddress;
+------------+--------------------+
| stuaddress | count(writtenexam) |
+------------+--------------------+
| 上海 | 1 |
| 北京 | 2 |
| 天津 | 2 |
| 河北 | 0 |
| 河南 | 0 |
+------------+--------------------+
# `5 rows in set (0.00 sec)`
-- 第二步：将结果降序排列
mysql> select stuaddress,count(writtenexam) c from stuinfo left join stumarks using(stuno) group by stuaddress order by c desc;
+------------+---+
| stuaddress | c |
+------------+---+
| 北京 | 2 |
| 天津 | 2 |
| 上海 | 1 |
| 河北 | 0 |
| 河南 | 0 |
+------------+---+
# `5 rows in set (0.00 sec)`
显示有学生参加考试的地区
- 思路解析
- select 选择查询字段
- from 多表查询 左外连接
- using 指定连接字段
- group by 分组查询显示
- having 条件筛选
-- having筛选
mysql> select stuaddress,count(writtenexam) c from stuinfo left join stumarks using(stuno) group by stuaddress having c>0;
+------------+---+
| stuaddress | c |
+------------+---+
| 上海 | 1 |
| 北京 | 2 |
| 天津 | 2 |
+------------+---+
# `3 rows in set (0.00 sec)`
- 思路解析
- select 选择查询字段
- from 多表查询 右外连接
- using 指定连接字段
- distinct 去重复
- having 条件筛选
- is not null 去空
-- 表连接实现
-- 第一步：右连接获取有成绩的地区
mysql> select stuaddress from stuinfo right join stumarks using(stuno);
+------------+
| stuaddress |
+------------+
| 北京 |
| 上海 |
| 天津 |
| 北京 |
| 天津 |
| NULL |
+------------+
# `6 rows in set (0.00 sec)`
-- 第二步：去重复
mysql> select distinct stuaddress from stuinfo right join stumarks using(stuno);
+------------+
| stuaddress |
+------------+
| 北京 |
| 上海 |
| 天津 |
| NULL |
+------------+
# `4 rows in set (0.00 sec)`
-- 去除null
mysql> select distinct stuaddress from stuinfo right join stumarks using(stuno) having stuaddress is not null;
+------------+
| stuaddress |
+------------+
| 北京 |
| 上海 |
| 天津 |
+------------+
# `3 rows in set (0.00 sec)`
显示男生和女生的人数
- 方法一:分组查询
- select 查询字段
- from 查询表
- group by 分组查询显示
mysql> select stusex,count(*) from stuinfo group by stusex;
+--------+----------+
| stusex | count(*) |
+--------+----------+
| 女 | 3 |
| 男 | 4 |
+--------+----------+
# `2 rows in set (0.00 sec)`
- 方法二:union
- select 查询字段
- from 查询表
- where 条件筛选
- union 联合查询
mysql> select stusex,count(*) from stuinfo where stusex='男' union select stusex,count(*) from stuinfo where stusex='女';
+--------+----------+
| stusex | count(*) |
+--------+----------+
| 男 | 4 |
| 女 | 3 |
+--------+----------+
# `2 rows in set (0.00 sec)`
mysql> select sum(stusex='男') 男,sum(stusex='女') 女 from stuinfo;
+------+------+
| 男 | 女 |
+------+------+
| 4 | 3 |
+------+------+
# `1 row in set (0.00 sec)`
显示每个地区男生、女生、总人数
- 思路解析
- select 选择字段 聚合函数
- from 选择表
- group by 分组查询显示
mysql> select stuaddress,count(*) 总人数,sum(stusex='男') 男,sum(stusex='女') 女 from stuinfo group by stuaddress;
+------------+--------+------+------+
| stuaddress | 总人数 | 男 | 女 |
+------------+--------+------+------+
| 上海 | 1 | 1 | 0 |
| 北京 | 2 | 1 | 1 |
| 天津 | 2 | 2 | 0 |
| 河北 | 1 | 0 | 1 |
| 河南 | 1 | 0 | 1 |
+------------+--------+------+------+
# `5 rows in set (0.00 sec)`
