mysql useing查询,MySQL数据库之多表查询using优化与案例

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)`

方法三:直接写条件

select 聚合函数查询

from 查询表

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)`

标签：stusex,mysql,查询,stuaddress,MySQL,using,之多表,select

来源： https://www.cnblogs.com/SharkJiao/p/14137851.html