Python 高级：11 MySQL 数据库的条件查询

一、聚合函数

1. 聚合函数的介绍

聚合函数又叫组函数，通常是对表中的数据进行统计和计算，一般结合分组（group by）来使用，用于统计和计算分组数据。

常用的聚合函数：
● count(col)：表示求指定列的总行数
● max(col)：表示求指定列的最大值
● min(col)：表示求指定列的最小值
● sum(col)：表示求指定列的和
● avg(col)：表示求指定列的平均值

2. 总行数

（1）返回总行数，包括 null 值：
select count(*) from job;
（2）返回总行数，不包括 null 值：
select count(salary) from job;

3. 最大值

根据条件，返回最大值：
select max(salary) from job where position="深圳";

4. 最小值

根据条件，返回最小值：
select min(salary) from job where position="深圳";

5. 和

通过 sum 求平均：
select sum(salary)/count(salary) from job;

6. 平均

（1）不统计 null：
select avg(salary) from job;
（2）统计 null，并将 null 置为 0 统计：
select avg(ifnull(salary,0)) from job;

7. 聚合函数的特点

聚合函数默认忽略字段为 null 的记录，若想将列值为 null 的记录也参与计算，必须使用 ifnull 函数对 null 值做替换。

二、分组查询

1. 分组查询介绍

将查询结果按照指定字段进行分组，字段中数据相等的分为一组。

GROUP BY 列名 [HAVING 条件表达式] [WITH ROLLUP]
● 列名：按照指定字段的值进行分组
● HAVING 条件表达式：用来过滤分组后的数据
● WITH ROLLUP：在所有记录的最后加上一条记录，显示 select 查询时聚合函数的统计和计算结果

2. group by 的使用

（1）根据单个字段分组：
select position from job group by position;
（2）根据多个字段分组：
select position,salary from job group by position,salary;

3. group by + group_concat() 的使用

group_concat(字段名)：统计每个分组指定字段的信息集合，每个信息之间使用逗号进行分割
分组之后集合：
select position,group_concat(salary) from job group by position;

4. group by + 聚合函数的使用

（1）分组平均：
select position,avg(salary) from job group by position;
（2）分组总数：
select position,count(*) from job group by position;

5. group by + having 的使用

having 的作用和 where 类似，都是过滤数据的，但 having 是过滤分组数据的，只能用于 group by
分组过滤：select position,count(*) from job group by position having count(*) > 1;

6. group by + with rollup 的使用

with rollup 的作用是在最后的记录后面加上一行，显示 select 查询时聚合函数的统计和计算结果
分组统计：
select position,group_concat(quantity) from job group by position with rollup;

三、连接查询

1. 内连接

查询两个表中符合条件的共有记录
select * from job as j inner join address as a on j.position=a.position;
● inner join 是内连接查询关键字
● on 是连接查询条件

2. 左连接

以左表为主根据条件条件查询右表数据，如果根据条件查询右表数据不存在使用 null 值填充
select * from job as j left join address as a on j.position=a.position;
● left join 是内连接查询关键字
● on 是连接查询条件

3. 右连接

以右表为主根据条件查询左表数据，如果根据条件查询右表数据不存在使用 null 值填充
select * from job as j right join address as a on j.position=a.position;
● right join 是内连接查询关键字
● on 是连接查询条件

4. 自连接

左表和右表是同一个表，根据连接查询条件查询两个表中的数据
select c.id, c.name, c.parent_id, p.name from areas as c inner join areas as p on c.parent_id=p.id
select c.id, c.name, c.parent_id, p.name from areas as c inner join areas as p on c.parent_id=p.id where p.name="广东省";
● 自连接查询必须对表起别名
● 自连接查询就是把一张表模拟成左右两张表，然后进行连表查询
● 自连接就是一种特殊的连接方式，连接的表还是本身这张表

四、子查询

在一个 select 语句中，嵌入另外一个 select 语句，那么被嵌入的 select 语句称之为子查询语句，外部那个 select 语句则称为主查询。

主查询和子查询的关系：
● 子查询是嵌入到主查询中的
● 子查询是辅助主查询的，要么充当条件，要么充当数据源
● 子查询是可以独立存在的语句，是一条完整的 select 语句
（1）大于平均：select * from job where salary > (select avg(salary) from job);
（2）最大：select job from job where salary = (select max(salary) from job);
● 子查询要被嵌入到一对小括号里

五、数据库设计之三大范式

1. 数据库设计之三大范式的介绍
● 第一范式（1NF）：强调的是列的原子性，即列不能够再分成其他几列。
● 第二范式（2NF）：满足 1NF，另外包含两部分内容。一是表必须有一个主键；二是非主键字段必须完全依赖于主键，而不能只依赖于主键的一部分。
● 第三范式（3NF）：满足 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。

2. 第一范式介绍

● 列拆分

3. 第二范式介绍

● Discount、Quantity 完全依赖于主键，而 UnitPrice、ProductName 只依赖于 ProductID

4. 第三范式介绍

● OrderDate、CustomerID、CustomerName、CustomerAddr、CustomerCity 等非主键列都完全依赖于主键（OrderID），符合 2NF。但是CustomerName、CustomerAddr、CustomerCity 直接依赖的是 CustomerID，而不是直接依赖于主键，是通过传递才依赖于主键。

五、外键 SQL 语句的编写

1. 外键约束的作用

外键约束：对外键字段的值进行更新和插入时会和引用表中字段的数据进行验证，数据如果不合法，更新和插入会失败，保证数据的有效性。

2. 添加外键约束

（1）对于已经存在的字段添加外键约束：
alter table job add foreign key(a_id) references address(id);
（2）在创建数据表时设置外键约束：
(1)create table school(id int not null primary key auto_increment, name varchar(10));
(2)create table teacher(id int not null primary key auto_increment, name varchar(10), s_id int not null, constraint ss_id foreign key(s_id) references school(id));

3. 删除外键约束

（1）先获取外键约束名称：show create table 表名;
（2）删除对应的外键名：alter table 表名 drop foreign key 外键别名;

六、分组和聚合的组合使用

数据：
(1)create database jing_dong charset=utf8;
(2)use jing_dong;
(3)create table goods( id int unsigned primary key auto_increment not null, name varchar(150) not null, cate_name varchar(40) not null, brand_name varchar(40) not null, price decimal(10,3) not null default 0, is_show bit not null default 1, is_saleoff bit not null default 0 );
(4)insert into goods values(0,'r510vc 15.6英寸笔记本','笔记本','华硕','3399',default,default);
insert into goods values(0,'y400n 14.0英寸笔记本电脑','笔记本','联想','4999',default,default);
insert into goods values(0,'g150th 15.6英寸游戏本','游戏本','雷神','8499',default,default);
insert into goods values(0,'x550cc 15.6英寸笔记本','笔记本','华硕','2799',default,default);
insert into goods values(0,'x240 超极本','超级本','联想','4880',default,default);
insert into goods values(0,'u330p 13.3英寸超极本','超级本','联想','4299',default,default);
insert into goods values(0,'svp13226scb 触控超极本','超级本','索尼','7999',default,default);
insert into goods values(0,'ipad mini 7.9英寸平板电脑','平板电脑','苹果','1998',default,default);
insert into goods values(0,'ipad air 9.7英寸平板电脑','平板电脑','苹果','3388',default,default);
insert into goods values(0,'ipad mini 配备 retina 显示屏','平板电脑','苹果','2788',default,default);
insert into goods values(0,'ideacentre c340 20英寸一体电脑 ','台式机','联想','3499',default,default);
insert into goods values(0,'vostro 3800-r1206 台式电脑','台式机','戴尔','2899',default,default);
insert into goods values(0,'imac me086ch/a 21.5英寸一体电脑','台式机','苹果','9188',default,default);
insert into goods values(0,'at7-7414lp 台式电脑 linux ）','台式机','宏碁','3699',default,default);
insert into goods values(0,'z220sff f4f06pa工作站','服务器/工作站','惠普','4288',default,default);
insert into goods values(0,'poweredge ii服务器','服务器/工作站','戴尔','5388',default,default);
insert into goods values(0,'mac pro专业级台式电脑','服务器/工作站','苹果','28888',default,default);
insert into goods values(0,'hmz-t3w 头戴显示设备','笔记本配件','索尼','6999',default,default);
insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default);
insert into goods values(0,'x3250 m4机架式服务器','服务器/工作站','ibm','6888',default,default);
insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default);