Python MySQL数据库2：数据库查询（下）

显示分组中的 name, age, id

select gender,group_concat(name, age, id) from animalTable where gender=1 group by gender

由于上面的 name, age, id 混在了一起，我们需要分开他们，看的更直观一些

select gender,group_concat(name,"_",age, "_",id) from animalTable where gender=1 group by

(5)、group by + having

• having 条件表达式：用来分组查询后指定一些条件来输出查询结果
• having作用和where一样，但having只能用于group by

查询平均年龄超过7岁的性别，以及姓名

select gender, group_concat(name),avg(age) from animalTable group by gender having avg(a

• 查询每种性别中的数量多于2个的信息

select gender, group_concat(name) from animalTable group by gender having count(*)>2;

• (6)、group by + with rollup
  with rollup 的作用是：在最后新增一行，来记录当前列里所有记录的总和

select gender,count(*) from animalTable group by gender with rollup;

五、分页

• 5.1、当数据量过大时，在一页中查看数据是一件非常麻烦的事情
  语法：select * from 表名 limit start,count,解释：start 是页码，count是一页显示的数量
• 5.2、查询前2行男生信息

select * from personTable where gender = "男" limit 0,2;

• 5.3、求第n页的数据，每页 m 条数据（不足m条，有多少显示多少条）

select * from personTable limit (n-1)*m,m;

• 举例： 如果展示：第三页的数据,每页2个数据：n=3,m=2

select * from personTable limit 4,2;

• 错误写法：

select * from personTable limit (3-2)*2,2;

提示：limit 要放在其他约束之后，最后面

六、连接查询

(多表的时候有用，单表的时候几乎没有用)，当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回，mysql支持三种类型的连接查询，分别为：内连接查询、左连接查询、右连接查询(有了左连接，一般不使用右连接)

语法：inner join ... on...

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

• 6.1、准备

• 给 personTable  添加  class_id 字段，并赋值，效果如下
  alter table personTable add class_id int unsigned;

创建一个班级的数据表，并赋值

mysql> create table classTable(
    ->  id int unsigned primary key auto_increment not null,
    -> class_name varchar(20) default ''
);
mysql> insert into classTable values(0,"一班"),(0,"二班"),(0,"三班")

6.2、内连接查询：inner join ... on (取交集)：查询的结果为两个表匹配到的数据

• select ... from 表A inner join 表B;

select * from personTable inner join classTable;

• (1)、查询 有能够对应班级的学生以及班级信息

select * from personTable inner join classTable on personTable.class_id=classTable.id;

• (2)、在上面查询的基础上 按照要求显示姓名、班级:不再使用 *,展示什么: 表名.字段

select personTable.*,classTable.class_name from personTable inner join classTable on personTable.class_id=classTable.id;
// 只显示两个组的名字
select personTable.name,classTable.class_name from personTable inner join classTable on personTable.class_id=classTable.id;

• (3)、给数据表 起别名（取表名的首字母）

select p.name,c.class_name from personTable as p inner join classTable as c on p.class_id=c

• (4)、在以上的查询中，将班级姓名显示在第1列（将上面的p.name,c.class_name调换一下顺序）

select c.class_name,p.name from personTable as p inner join classTable as c on p.class_id=c.id;

• (5)、查询 有能够对应班级的学生以及班级信息, 按照班级降序进行排序

select c.class_name,p.name from personTable as p inner join classTable as c on p.class_id=c.id order by c.class_name desc;

• (6)、当时同一个班级的时候，按照学生的id进行从大到小排序

select p.name,p.id,c.class_name from personTable as p inner join classTable as c on p

6.3、左连接查询:left join (取左边)：查询的结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据使用null填充

(1)、查询每位学生对应的班级信息

select * from personTable left join classTable on personTable.class_id= classTable.id;

(2)、查询没有对应班级信息的学生(用 having 就好，对结果进行处理)

select ... from xxx as s left join xxx as c on..... where .....或者select ... from xxx as s left join xxx as c on..... having .....

select * from personTable left join classTable on personTable.class_id= classTable.id having classTable.class_name is null; 
或者 where 来替换 having
select * from personTable left join classTable on personTable.class_id= classTable.id wher

6.4、右连接查询(取右边)：查询的结果为两个表匹配到的数据，右表特有的数据，对于左表中不存在的数据使用null填充

提示： 将数据表名字互换位置，用left join完成  right join...on...

七、自关联

• 7.1、自关联的引用

• 设计省信息的表结构provinces

id
ptitle

• 设计市信息的表结构citys

id
ctitle
proid

• citys表的proid表示城市所属的省，对应着provinces表的id值
  
• 问题：能不能将两个表合成一张表呢？
  
• 思考：观察两张表发现，citys表比provinces表多一个列proid，其它列的类型都是一样的
  
• 意义：存储的都是地区信息，而且每种信息的数据量有限，没必要增加一个新表，或者将来还要存储区、乡镇信息，都增加新表的开销太大
  
• 答案：定义表areas，结构如下

id
atitle
pid

• 说明:

• 因为省没有所属的省份，所以可以填写为null
• 城市所属的省份pid，填写省所对应的编号id
• 这就是自关联，表中的某一列，关联了这个表中的另外一列，但是它们的业务逻辑含义是不一样的，城市信息的pid引用的是省信息的id
• 在这个表中，结构不变，可以添加区县、乡镇街道、村社区等信息

• 7.1、准备工作
  把本地的 city.sql(地区文件内容) 拷贝到 服务器root家目录下，下面代码：前面的city.sql  是本地的路径，后面的是服务器的路径，我是直接放到了 root的家目下

scp -P 22 city.sql root@47.93.240.8: city.sql

• 在 pythonTestDataBase 库下建立表 city

创建city表的语句如下：

create table city(
      id int unsigned primary key auto_increment not null,
      pid int unsigned default 0,
      cityname varchar(20) default ' ',
      type int unsigned default null
);

• 导入数据

source  city;

• 7.2、查询出山东省有哪些市

select * from city as province inner join city as citys on citys.pid=province.aid having province.atitle="山东省";
select province.atitle, citys.atitle from city as province inner join city as citys on citys.pid=province.aid having province.atitle="山东省";

• 7.3、  查询出青岛市有哪些县城

select province.atitle, citys.atitle from city as province inner join city as citys on citys.pid=province.aid having province.atitle="青岛市";
select * from city where pid=(select aid from city where atitle="青岛市")

八、子查询

• 8.1、 子查询 与 主查询

• 子查询语句：在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句。
• 主查询：主要查询的对象,第一条 select 语句。
• 主查询 和 子查询 的关系

• 子查询是嵌入到主查询中
• 子查询是辅助主查询的,要么充当条件,要么充当数据源
• 子查询是可以独立存在的语句,是一条完整的 select 语句

• 8.2、子查询分类

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

• 8.3、标量子查询
• 
  
• 
  查询动物的平均年龄

select avg(age) from animalTable;

• 查询大于平均年龄的动物

select * from animalTable where age > (select avg(age) from animalTable);

8.4、列级子查询

查询还有学生在班的所有班级名字

• 找出学生表中所有的班级 id
  
• 找出班级表中对应的名字
  select class_name from classTable where id in (select class_id from personTable);
  

• 8.5、行级子查询

• 需求: 查找班级年龄最大,身高最高的学生
  
• 行元素: 将多个字段合成一个行元素,在行级子查询中会使用到行元素

select * from students where (height,age) = (select max(height),max(age) from students);

• 8.6、子查询中特定关键字使用

• in 范围
  格式: 主查询 where 条件 in (列子查询)

九、总结

• 9.1、查询的完整格式

SELECT select_expr [,select_expr,...] [      
   FROM tb_name
   [WHERE 条件判断]
   [GROUP BY {col_name | postion} [ASC | DESC], ...] 
   [HAVING WHERE 条件判断]
   [ORDER BY {col_name|expr|postion} [ASC | DESC], ...]
   [ LIMIT {[offset,]rowcount | row_count OFFSET offset}]
]

• 9.2、完整的select语句

select distinct *
from 表名
where ....
group by ... having ...
order by ...
limit start,count

• 9.3、执行顺序为：

• from 表名
• where ....
• group by ...
• select distinct *
• having ...
• order by ...
• limit start,count

• 9.4、实际使用中，只是语句中某些部分的组合，而不是全部