python按条件查询数据库数据类型_103 . python高级------MySQl数据库的条件查询(5)

103 . python高级------MySQl数据库的条件查询(5)

python修炼第二十四天

2019年 4月 23日 晴

11.子查询

– 标量子查询: 子查询返回的结果是一个数据(一行一列)

– 列子查询: 返回的结果是一列(一列多行)

– 行子查询: 返回的结果是一行(一行多列)python

– 查询出高于平均身高的信息(height)

– 获得平均身高web

select avg(height) from students;

-- 大于平均 身高

select * from students where height > 172.076923;

select * from students where

height > (select avg(height) from students);

– 查询学生的班级号可以对应的 学生名字sql

– 有多少 个班级数据库

select name from classes;

select id from classes;

– 学生对应 的班级信息svg

select * from students where students.cls_id in(1,2);

select * from students where students.cls_id in(select id from classes);

–数据操做前的准备

–建立数据库表code

create table areas(

aid int primary key,

atitle varchar(20),

pid int

);

–从sql文件中导入数据

– source 具体地址/areas.sql;xml

source xxx.sql;

–查询一共有多少个省it

select * from areas where pid is null;

–例1：查询省的名称为“山西省”的全部城市table

– 分两步

– 第一步查到山西 的编号class

select * from areas where atitle = '山西省';

select aid from areas where atitle = '山西省';

– 第二步查到pid是山西 的编号

select * from areas where pid = '140000';

select * from areas where pid = (

select aid from areas where atitle = '山西省'

);

select * from areas where pid = (

select aid from areas where atitle = '杭州市'

);

– 自联

select * from areas as a1 inner join areas as a2

on a1.pid = a2.aid where a2.atitle = '杭州市';

–例2：查询市的名称为“广州市”的全部区县

select * from areas where pid = (

select aid from areas where atitle = '广州市'

);

select * from areas as a1 inner join areas as a2

on a1.pid = a2.aid where a2.atitle = '上海';