MySQL-高级查询

嵌套查询(子查询)

把内层的查询结果作为外层的查询条件

select ... from 表名 where 条件(select ....);

示例

# 把攻击值小于平均攻击值的英雄名字和攻击值显示出来
select name,gongji from MOSHOU.sanguo
where 
gongji<(select avg(gongji) from MOSHOU.sanguo);
# 找出每个国家攻击力最高的英雄的名字和攻击值
select name,gongji from moshou.sanguo
where 
(country,gongji) in
(select country,max(gongji) from moshou.sanguo group by country);

多表查询

多个表之间联合查询

// 方式一
select 字段名列表 from 表名列表; (笛卡尔积)
/*
t1 : name -> "A1"  "A2"  "A3"
t2 : name -> "B1"  "B2"
select * from t1,t2;
+------+-------+
| name | name2 |
+------+-------+
| A1   | B1    |
| A1   | B2    |
| A2   | B1    |
| A2   | B2    |
| A3   | B1    |
| A3   | B2    |
+------+-------+
*/

// 方式二
... where 条件
// 显示省和市的详细信息
select sheng.s_name,city.c_name from sheng,city
where
sheng.s_id=city.cfather_id;
// 显示省市县详细信息
select sheng.s_name,city.c_name,xian.x_name from sheng,city,xian
where
sheng.s_id=city.cfather_id and city.c_id = xian.xfather_id;

连接查询

内连接

# 语法格式
select 字段名 from 表1 inner join 表2 on 条件 inner join 表3 on 条件;

# 显示省市详细信息
select sheng.s_name,city.c_name from sheng 
inner join city on sheng.s_id = city.cfather_id;
# 显示省市县详情信息
select sheng.s_name,city.c_name,xian.x_name from sheng 
inner join city on sheng.s_id = city.cfather_id
inner join xian on city.c_id = xian.xfather_id;

外连接

以左(右)表为主显示查询结果，左(右)边的记录全部显示

# 语法格式
select 字段名 from 表1 
left(right) join 表2 on 条件 
left(right) join 表3 on 条件;

# 显示省市详细信息
select sheng.s_name,city.c_name from sheng
left join city on sheng.s_id = city.cfather_id;
# 显示省市县详情信息
select sheng.s_name,city.c_name,xian.x_name from sheng
left join city on sheng.s_id = city.cfather_id
left join xian on city.c_id - xian.xfather_id;