mysql基础-8.连表查询和子查询

连表查询

重点讲解一下连表查询，这个也是面试重点

直接连，错误示范

select * from student,class;

这种连接又被称为笛卡儿积，就是两两向交

后面要加上条件证明两表有相同的字段

select * from student,class where student.class_id = class.id;

有三种连接模式

• inner join
• left join
• right join

如果数据都能一一对应上的话，那么他们三个的输出结果没有任何区别

但是，数据有对不上的情况，那么他们三个就有区别了

例如

班级表

id	班级名称
1	三年一班
2	三年二班
3	三年三班

学生表

id	name	class_id
1	wangwu	1
2	lisi	null
3	fengfeng	2
4	fang	2

select * from student inner join class  on student.class_id = class.id;
select * from class inner join student  on student.class_id = class.id;

select * from student left join class  on student.class_id = class.id;
select * from class left join student on student.class_id = class.id;

select * from student right join class  on student.class_id = class.id;
select * from class right join student  on student.class_id = class.id;

第一种情况，内连接，通过学生连班级，就只有1 3 4的学生和班级记录

第二种情况，内连接，通过班级连学生，和第一种情况是一样的

第三种情况，左连接，通过学生连班级，学生信息是完整显示出来了，学生2对应的班级信息全是null

第四种情况，左连接，通过班级连学生，班级信息完整显示出来了，但是班级3对应的学生信息是null

第五种情况，右连接，通过学生连班级，以班级表为基表，班级信息是完整显示出来了，但是班级3对应的学生信息是null

第六种情况，右连接，通过班级连学生，以学生表为基表，学生信息完整显示出来了，但是学生2对应的班级信息是null

不同的连接关系

他们的区别是：

1. 返回不同

   inner join会返回两表中一样的行

   left join会返回左表的所有记录，哪怕它没有匹配到右表

2. 数量不同

   inner join小于等于左表和右表的记录数量

   left join：left join的数量以左表中的记录数量相同。

3. 记录属性不同

   inner join不足的记录属性会被直接舍弃

   left join不足的记录属性用NULL填充

left和right就是基表不同，操作不同

子查询

子查询是另一个语句中的 SELECT 语句。

子查询也称为内查询（Inner Query），必须位于括号之中。包含子查询的查询称为外查询（Outer Query）。子查询支持多层嵌套，也就是子查询可以包含其他子查询。

小试牛刀

# 查询是男生的数据
select * from user where sex = 1;
select * from user where id in (select id from user where sex = 1);

虽然这样写是脱裤子放屁，但是意思就是这么个意思

先在子查询里面查出数据，再带入上层进行查询

需要考虑子查询的结果集是单个还是多个

1. 子查询在where里面
2. 子查询在form
3. 子查询在select里面

create table users
(
    id   int(11) primary key auto_increment,
    name varchar(8) not null,
    age  int(4),
    sex bool default 0
);

# 查询超过男生平均年龄的男生
# 查询超过男生平均年龄的男生，并且把平均年龄显示出来
# 查询用户的年龄，平均年龄
# 查询用户的年龄，性别，对应性别的平均年龄

# 查询超过男生平均年龄的男生
select * from users where sex = 1 and age > (select avg(age) from users where sex = 1 group by sex);

# 查询超过男生平均年龄的男生，并且把平均年龄显示出来
select name, age, u1.ag from (select avg(age) as ag from users where sex = 1 group by sex) u1, users where users.age > u1.ag;

# 查询用户的年龄，平均年龄
select name, age, (select avg(age) from users)  as ag from users;

# 查询用户的年龄，性别，对应性别的平均年龄
select name, age, sex, (select avg(age) from users u2 where u1.sex = u2.sex)  as sex_ag from users u1;