多表查询
- 先创建一个学生表和班级表,然后加入一些数据:
create table stu(
id int primary key auto_increment ,
name varchar(20),
gender varchar(20),
math double);
insert into stu values(null,"zhangsan","male",89.5),
(null,"lisi","male",90.5),
(null,"wangwu","male",79.5),
(null,"rose","female",81.5);
create table class(id int primary key auto_increment,
cla int);
insert into class values(null,1),
(null ,1),
(null ,2),
(null,2);
1.内连接查询
1.1.隐式内连接
- 查询学生信息和班级信息:
select * from stu,class where stu.id=class.id; - 查询学生id,姓名和班级:
select s.id, s.name ,c.cla from stu s,class c where s.id=c.id;
1.2.显示内连接
-
查询学生信息和班级信息:
select * from stu inner join class on stu.id=class.id; -
内连接查询注意:从哪些表中查询数据 ? 条件什么?查询哪些字段?
2.外连接查询
先插入一条数据:
insert into stu values(null,"jack","male",99.5);
2.1.左外连接
- 查询所有学生信息,有班级就查询,没有就算了:
select * from stu left join class on stu.id=class.id;
2.2.右外连接
- 查询班级的学生:
select * from stu right join class on stu.id=class.id;
3.子查询
- 概念:查询中嵌套查询,称嵌套查询为子查询
1.子查询的结果是单行单列的
- 查询数学成绩最高同学的信息:
select * from stu where math=(select max(math) from stu);
2.子查询的结果是多行单列的
- 查询班级是1班和2班的同学信息:
select * from stu where id in (select id from class where cla=1 or cla=2);
3.子查询的结果是多行多列的
- 查询成绩在80分以上的学生信息和班级信息:
select * from stu where math>80(子查询)看成为一张虚拟表
select * from class t1,(select * from stu where math>80) t2 where t1.id=t2.id;