python进阶-数据库-连接查询

连接查询

• 内连接查询 : 查询的结果为两个表匹配到的数据
• 右连接查询 : 查询的结果为两个表匹配到的数据, 右表特有的数据, 对于左表中不存在的数据使用null填充
• 左连接查询 : 查询的结果为两个表匹配到的数据, 左表特有的数据, 对于右表中不存在的数据使用null填充

语法

select * from 表1 inner或left或right join 表2 on 表1.列 = 表2.列

• 查询有对应班级的学生以及班级信息

select * from students inner join classes on students.cls_id=classes.id;

• 按照要求显示姓名, 班级

select s.name, c.name from students as s inner join classes as c on s.cls_id=c.id

• 当是同一个班级的时候, 按照学生的id进行从小到大的排序

select s.*, c.name from students as a inner join classes as c on s.cls_id=c.id order by c.name,s.id;

• left join

select * from students as s left join classes as c on s.cls_id=c.id;

• having : 从结果里面取数据

select * from students as s left join classes as c on s.cls_id=c.id having c.id is null;

自关联

-- 查询省对应的市
select * from areas as province inner join areas as city on city.pid=province.aid having province.atitle="山东省";