MySql数据库多表操作
一、连接查询[连表查询、多表查询]
当查询结果的列来源于多张表时,需要将多张表连接成一个大的数据集,再选择合适的列返回
mysql支持三种类型的连接查询,分别为:
查询的结果为两个表匹配到的数据
使用内连接,必须保证两个表都会对应id的数据才会被查询出来。
格式:select 字段1,字段2... from 主表A inner join 从表B on 主表A.主键=从表B.外键
例如:查询学生的信息[ 成绩、名字、班级 ] 我们给学生表添加一个学生信息,然后使用该学生的主键id来连表查询成绩、名字和班级。
insert into student (name,sex,age,class,description) values ('刘德华',1,17,406,''); select achievement,name,class from student as a inner join achievement as b on a.id=b.sid where id=101; # 上面语句因位该学生只在学生表student中有数据,而成绩表中没有数据,所以使用内连接,连表查询的结果是 Empty set (0.00 sec)
同样,如果从表有数据,而主表没有数据,则使用内连接查询一样无法查询到结果。
#例如,添加一个成绩记录,是不存在学生 insert into achievement (sid,cid,achievement) values (102,10,85); select achievement,name,class from student as a inner join achievement as b on a.id=b.sid where id=102;
右连接查询(right join)
a.id=b.sid,只要b表的sid存在就可以 ; a.id不存在不要紧 左表中不存在的数据使用null填充
查询的结果为两个表匹配到的数据,右表特有的数据,对于左表中不存在的数据使用null填充
格式:select 字段1,字段2... from 主表 right join 从表 on 主表.主键=从表.外键
select achievement,name,class from student as a right join achievement as b on a.id=b.sid where b.sid=102;
左连接查询(left join)
只要主表有数据,不管从表是否有数据都会被查询出来。
查询的结果为两个表匹配到的数据,左表特有的数据,对于右表中不存在的数据使用null填充
格式:select * from 表1 left join 表2 on 表1.列 = 表2.列
例如,使用左连接查询学生表与成绩表,查询学生姓名及分数
select achievement,name,class from student as a left join achievement as b on a.id=b.sid; 等同于 select achievement,name,class from achievement as b right join student as a on a.id=b.sid;
总结:三种连表查询,最常用的是 left join,然后inner join保证数据的一致性。右连接基本上都是使用左连接代替。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select 表.字段1,表.字段2,表.字段3..... from 主表 left join 从表1 on 主表.主键=从表1.外键 left join 从表2 on 主表.主键=从表2.外键 # 这里和从表2连接的on条件看实际情况,也会出现从表1.主键=从表2.外键的情况 left join 从表3 on 主表.主键=从表3.外键 # 这里可以是(从表1或从表2).主键=从表2.外键的情况 left join ...
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
多表查询的效率,性能比单表要差。 多表查询以后,还会带来字段多了会引起字段覆盖的情况、 主表student 从表1 achievement 从表2 course name xxx name 上面三张表如果连表,则出现主表的name覆盖从表2的name这种情况。 上面两个问题: 1. 把多表查询语句可以替换成单表查询语句【需要优化的情况】 2. 把重复的字段名,分别使用as来设置成别的名称。
练习:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select sum(b.achievement) sum # 有时候as可以不写 from student as a left join achievement as b on a.id=b.sid where a.id=20;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1. 先查305的学生信息 2. 再查305的学生成绩 3. 再查305的学生成绩对应的课程 4. 最后查305的学生成绩对应的课程的老师 select a.name,b.achievement,c.course,d.name from student as a left join achievement as b on a.id=b.sid left join course as c on b.cid=c.id left join lecturer as d on d.id=c.lecturer_id where a.class=305;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
上面代码的效果: +--------+-------------+----------------+--------+ | name | achievement | course | name | +--------+-------------+----------------+--------+ | 谭季同 | 100.0 | Photoshop | 唐老师 | | 谭季同 | 79.0 | 负载均衡 | 杜老师 | | 谭季同 | 78.5 | Flask项目 | 白老师 | | 白瀚文 | 73.0 | go | 陈老师 | | 白瀚文 | 65.0 | webpy | 林老师 | | 白瀚文 | 86.0 | 数据分析 | 郑老师 | | 白瀚文 | 60.0 | API接口 | 宋老师 | | 晁然 | 0.0 | Flask | 陈老师 | | 晁然 | 78.0 | Python网络编程 | 江老师 | | 晁然 | 78.0 | HTML5 | 丘老师 | | 白素欣 | 81.0 | Django项目 | 易老师 | | 白素欣 | 90.0 | Python | 黄老师 | | 白素欣 | 39.0 | Nginx | 曹老师 | | 庄晓敏 | 82.5 | Nginx | 曹老师 | | 庄晓敏 | 68.0 | Python | 黄老师 | | 庄晓敏 | 100.0 | API接口 | 宋老师 | +--------+-------------+----------------+--------+
二、单表的连表查询[自关联查询]
# 建表: create table area( id smallint not null auto_increment comment '主键ID', name char(30) not null comment '地区名称', pid smallint not null default 0 comment '父级地区ID', primary key (id) ) engine=innodb charset=utf8; insert into area (name,pid) values ('广东',0),('深圳',1),('龙岗',2),('福田',2),('宝安',2);
格式:select 字段1,字段2...from 主表(当前表) as a left join 从表(当前表) as b on a.主键=b.外键
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
# 主表看成保存深圳的表, # 从表看成保存深圳子地区的表 select b.id,b.name from area as a left join area as b on a.id=b.pid where a.name='深圳';
在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句
格式:select 字段 from 表名 where 条件(另一条查询语句)
主查询
主要查询的对象,第一条 select 语句
主查询和子查询的关系
-
子查询是嵌入到主查询中
-
子查询是辅助主查询的,要么充当条件,要么充当数据源
-
子查询是可以独立存在的语句,是一条完整的 select 语句
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
##### 例如:查询406班上大于平均年龄的学生 使用 子查询: 1. 查询406班学生平均年龄 2. 查询大于平均年龄的学生 查询406班级学生的平均身高 select name,age from student where age > (select avg(age) as avg from student where class=406) and class=406;
having
group by 字段 having 条件;
过滤筛选,主要作用类似于where关键字,用于在SQL语句中进行条件判断,过滤结果的。
但是与where不同的地方在于having只能跟在group by 之后使用。
练习:查询301班级里的学生的平均成绩大于班上平均成绩的学生成绩信息(name,平均分,班级)。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
# 先求301班的平均成绩 select avg(achievement) as achi from student as a left join achievement as b on a.id=b.sid where class=301; # 判断301中的每个人平均成绩大于上面的到的平均成绩 select name,avg(achievement) from student as a left join achievement as b on a.id=b.sid where class=301 group by name having avg(achievement) > (select avg(achievement) as achi from student as a left join achievement as b on a.id=b.sid where class=301);
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> select avg(achievement) as achi from student as a left join achievement as b on a.id=b.sid where class=301; +----------+ | achi | +----------+ | 68.96875 | +----------+ 1 row in set mysql> select name, achievement from student as a left join achievement as b on a.id=b.sid where class=301; +--------+-------------+ | name | achievement | +--------+-------------+ | 程星云 | 63.5 | | 程星云 | 72 | | 程星云 | 40.5 | | 娄镇明 | 86.5 | | 娄镇明 | 85.5 | | 柳宗仁 | 99 | | 柳宗仁 | 60 | | 曾嘉慧 | 90 | | 王紫伊 | 73 | | 王紫伊 | 0 | | 王紫伊 | 55 | | 黄威 | 98 | | 黄威 | 79 | | 黄威 | 62.5 | | 庄信杰 | 60 | | 庄信杰 | 79 | +--------+-------------+ 16 rows in set mysql> select sum(achievement) from student as a left join achievement as b on a.id=b.sid where class=301; +------------------+ | sum(achievement) | +------------------+ | 1103.5 | +------------------+ 1 row in set mysql> select count (achievement) from student as a left join achievement as b on a.id=b.sid where class=301; +--------------------+ | count(achievement) | +--------------------+ | 16 | +--------------------+ 1 row in set mysql> select name,avg(achievement) from student as a left join achievement as b on a.id=b.sid where class=301 group by name having avg(achievement) > (select avg(achievement) as achi from student as a left join achievement as b on a.id=b.sid where class=301); +--------+------------------+ | name | avg(achievement) | +--------+------------------+ | 娄镇明 | 86 | | 庄信杰 | 69.5 | | 曾嘉慧 | 90 | | 柳宗仁 | 79.5 | | 黄威 | 79.83333 | +--------+------------------+ 5 rows in set mysql>
select查询语句的完整格式
select distinct 字段1,字段2....
from 表名 as 表别名
left join 从表1 on 表名.主键=从表1.外键
left join ....
where ....
group by ... having ...
order by ...
limit start,count
-
执行顺序为:
-
from 表名[包括连表]
-
where ....
-
group by ...
-
select distinct *
-
having ...
-
order by ...
-
limit start,count
-
-
实际使用中,只是语句中某些部分的组合,而不是全部