连接查询

-- 连接查询可以对多张表进行查询
-- 现在只有一张表， stu
-- 1. 现在给stu添加一个字段，cls_id(班级id)
alter table stu add cls_id int unsigned;
desc stu; -- 查看表结构
-- 2. 创建一个班级
create table class(id int unsigned primary key not null auto_increment, name varchar(20) not null);
-- 3. 班级表中添加数据
insert into class(name) values('py01'),('py02'),('py03'),('py42');
-- 修改 stu 表中的内容
update stu set cls_id=1 where id in (1);
update stu set cls_id=3 where id in (2,4);
update stu set cls_id=2 where id in (3);

-- 基本格式
select 字段，...
from 表1 [inner | left | right] join 表2
on 连接条件
where 条件
group by 字段
having 条件
order by 字段
limit xxx;

-- on 后边跟连接条件，即两种表相同的数据部分
-- 如果没有 on 连接条件

内连接

-- 显示两种表都有的内容
select * from stu inner join class on stu.cls_id = class.id;

select s.name, c.name from stu as s inner join class as c on s.cls_id = c.id

左连接

-- 左表中的数据全部显示
select * from stu left join class on stu.cls_id = class.id;

右连接

select * from stu right join class on stu.cls_id = class.id;

-- 多表查询不使用 join no 连接 等价于内连接查询
select * from stu as s, class as c where s.cls_id = c.id;

自查询

-- 把一张表当多张使用
-- 一张表包含两项数据
-- 省份地区表

-- 创建数据表
create table aresa(id varchar(30) not null primary key,title varchar(30),pid varchar(30));

-- 添加数据
    -- 操作方法一：将要添加的SQL语句复制粘贴到终端，执行
    -- 操作方法二：
        -- 将SQL文件 areas.sql文件拷贝到 ubuntu的桌面中
        -- 在MySQL 命令客户端中，执行source ~/Desktop/areas.sql sql地址

-- 查看所有最顶级的行政单位(即省级信息)
select * from areas where pid is null;

-- 查看非省份地区
select * from areas where pid is not null;

-- 查看北京一共有多少个区
select * from areas as a join areas as p on a.pid = p.id where p.title = '北京市';

子查询

-- 查询显示城市和省份，江西省有哪些地区 areas表
select * from areas where pid = (select id from areas where title = '江西省');

select * from areas where title = '北京市' and pid is null;

-- stu表
-- 查询大于评价年龄的学生
-- 求平均年龄
select avg(age) from stu;
select * from stu where age > (select avg(age) from stu);

-- 查询学生所在的班级名字
-- 通过 stu 表，找到学生对应的班级id
select cls_id from stu;
-- 通过 class表，找到id 对应的名字
select * from class where id in (select cls_id from stu);

-- 查询年龄最大， 身高最高的学生：（两个条件同时满足）
-- 最大年龄，最高身高 max()
select max(age), max(height) from atu;
select * from stu where(age,height) = (select max(age), max(height) from stu);