目录
嵌套查询(子查询)
定义:
将内层查询结果做为外层查询条件
语法:
select ...... from 表名 where 条件(select ......)
1、把攻击值小于平均攻击值的英雄名字和攻击值显示出来 1、先计算平均值 select avg(gongji) from MOSHOU.sanguo; select avg(gongji) from MOSHOU.sanguo; 2、找到 < 平均值 select name,gongji from MOSHOU.sanguo 子查询: select name,gongji from MOSHOU.sanguo where gongji<(select avg(gongji) from MOSHOU.sanguo); 2、找出每个国家攻击力最高的英雄的名字和攻击值 select name,gongji from sanguo where gongji in(select max(gongji) from sanguo group by country);
多表查询
查询有两种方式:
1)select 字段名列表 from 表名列表:(笛卡尔积---->前面表的记录与后面表的每一条记录逐条进行匹配)
2)......where 条件;
例如:
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 |
+------+-------+示例:
1.显示省和市的详细信息 select sheng.s_name,city.c_name from sheng,city where sheng.s_id = city.cfather_id; 2.显示省、市和县的详细信息 select sheng.s_name,city.c_name,xian.x_name fron 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;
外连接
左连接:
1)以左表为主显示结果
2)语法结构:
select 字段名 from 表1
left join 表2 on 条件
left 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;
右连接:
1)以右表为主显示结果
2)语法格式:
select 字段名 from 表1
right join 表2 on 条件
right join 表3 on 条件