一：聚合函数
1. 聚合函数的介绍
聚合函数又叫组函数，通常是对表中的数据进行统计和计算，一般结合分组(group by)来使用，用于统计和计算分组数据。
常用的聚合函数:
count(col): 表示求指定列的总行数
max(col): 表示求指定列的最大值
min(col): 表示求指定列的最小值
sum(col): 表示求指定列的和
avg(col): 表示求指定列的平均值
2. 求总行数
-- 返回非NULL数据的总行数.
select count(height) from students;
-- 返回总行数，包含null值记录;
select count(*) from students;
3. 求最大值
-- 查询女生的编号最大值
select max(id) from students where gender = 2;
4. 求最小值
-- 查询未删除的学生最小编号
select min(id) from students where is_delete = 0;
5. 求和
-- 查询男生的总身高
select sum(height) from students where gender = 1;
-- 平均身高
select sum(height) / count(*) from students where gender = 1;
6. 求平均值
-- 求男生的平均身高, 聚合函数不统计null值，平均身高有误
select avg(height) from students where gender = 1;
-- 求男生的平均身高, 包含身高是null的
select avg(ifnull(height,0)) from students where gender = 1;
说明
ifnull函数: 表示判断指定字段的值是否为null，如果为空使用自己提供的值。
7. 聚合函数的特点
聚合函数默认忽略字段为null的记录 要想列值为null的记录也参与计算，必须使用ifnull函数对null值做替换。
二：分组函数
1. 分组查询介绍
分组查询就是将查询结果按照指定字段进行分组，字段中数据相等的分为一组。
分组查询基本的语法格式如下：
GROUP BY 列名 [HAVING 条件表达式] [WITH ROLLUP]
说明:
列名: 是指按照指定字段的值进行分组。
HAVING 条件表达式: 用来过滤分组后的数据。
WITH ROLLUP：在所有记录的最后加上一条记录，显示select查询时聚合函数的统计和计算结果
2. group by的使用
group by可用于单个字段分组，也可用于多个字段分组
mysql> select gender from students group bygender;+--------+
| gender |
+--------+
| NULL |
| ? |
| ? |
+--------+
3 rows in set (0.00sec)
mysql> select name, gender from students group byname, gender;+----------+--------+
| name | gender |
+----------+--------+
| lisi | ? |
| peiqi | ? |
| waang | NULL |
| zhangsan | ? |
| 平二 | ? |
+----------+--------+
5 rows in set (0.00sec)
mysql>
3. group by + group_concat()的使用
group_concat(字段名): 统计每个分组指定字段的信息集合，每个信息之间使用逗号进行分割
--根据gender字段进行分组， 查询gender字段和分组的name字段信息
mysql> select gender,group_concat(name) from students group bygender;+--------+--------------------+
| gender | group_concat(name) |
+--------+--------------------+
| NULL | waang |
| ? | peiqi,zhangsan |
| ? | lisi,平二 |
+--------+--------------------+
3 rows in set (0.00 sec)
4. group by + 聚合函数的使用
--统计不同性别的人的平均年龄
mysql> select gender,avg(age) from students group bygender;+--------+----------+
| gender | avg(age) |
+--------+----------+
| NULL | 15.0000 |
| ? | 9.0000 |
| ? | 18.0000 |
+--------+----------+
3 rows in set (0.00sec)--统计不同性别的人的个数
mysql> select gender,count(*) from students group bygender;+--------+----------+
| gender | count(*) |
+--------+----------+
| NULL | 1 |
| ? | 2 |
| ? | 2 |
+--------+----------+
3 rows in set (0.00sec)
mysql>
5. group by + having的使用
having作用和where类似都是过滤数据的，但having是过滤分组数据的，只能用于group by
--根据gender字段进行分组，统计分组条数大于2的
mysql> select gender,count(*) from students group by gender having count(*)>=2;+--------+----------+
| gender | count(*) |
+--------+----------+
| ? | 2 |
| ? | 2 |
+--------+----------+
2 rows in set (0.00 sec)
6. group by + with rollup的使用
with rollup的作用是：在最后记录后面新增一行，显示select查询时聚合函数的统计和计算结果
--根据gender字段进行分组，汇总总人数
mysql> select gender,count(*) from students group by gender withrollup;+--------+----------+
| gender | count(*) |
+--------+----------+
| NULL | 1 |
| ? | 2 |
| ? | 2 |
| NULL | 5 |
+--------+----------+
4 rows in set (0.00sec)--根据gender字段进行分组，汇总所有人的年龄
mysql> select gender,group_concat(age) from students group by gender withrollup;+--------+-------------------+
| gender | group_concat(age) |
+--------+-------------------+
| NULL | 15 |
| ? | 0,18 |
| ? | 18,18 |
| NULL | 15,0,18,18,18 |
+--------+-------------------+
4 rows in set (0.00sec)
mysql>
三：连接查询
1. 连接查询的介绍
连接查询可以实现多个表的查询，当查询的字段数据来自不同的表就可以使用连接查询来完成。
连接查询可以分为:
内连接查询
左连接查询
右连接查询
自连接查询
2. 内连接查询
查询两个表中符合条件的共有记录
内连接查询效果图:
内连接查询语法格式:
select 字段 from 表1 inner join 表2 on 表1.字段1 = 表2.字段2
说明:
inner join 就是内连接查询关键字
on 就是连接查询条件
例1：使用内连接查询学生表与班级表:
mysql> select * fromclass;+----+-------+---------------+
| id | title | student_count |
+----+-------+---------------+
| 0 | 1班 | 20 |
| 1 | 2班 | 30 |
| 2 | 3班 | 32 |
+----+-------+---------------+
3 rows in set (0.00sec)
mysql> select * fromstudents;+----+----------+------+--------+--------+
| id | name | age | height | gender |
+----+----------+------+--------+--------+
| 1 | peiqi | 0 | NULL | ? |
| 2 | waang | 15 | NULL | NULL |
| 3 | zhangsan | 18 | 180.00 | ? |
| 4 | lisi | 18 | 160.00 | ? |
| 5 | 平二 | 18 | 160.00 | ? |
+----+----------+------+--------+--------+
5 rows in set (0.00sec)
mysql> select * from students as s inner join class as c on s.id=c.id;+----+-------+------+--------+--------+----+-------+---------------+
| id | name | age | height | gender | id | title | student_count |
+----+-------+------+--------+--------+----+-------+---------------+
| 1 | peiqi | 0 | NULL | ? | 1 | 2班 | 30 |
| 2 | waang | 15 | NULL | NULL | 2 | 3班 | 32 |
+----+-------+------+--------+--------+----+-------+---------------+
2 rows in set (0.00sec)
mysql>
View Code
3. 左连接查询
以左表为主根据条件查询右表数据，如果根据条件查询右表数据不存在使用null值填充
左连接查询效果图:
左连接查询语法格式:
select 字段 from 表1 left join 表2 on 表1.字段1 = 表2.字段2
说明:
left join 就是左连接查询关键字
on 就是连接查询条件
表1 是左表
表2 是右表
例1：使用左连接查询学生表与班级表:
mysql> select * from students as s left join class as c on s.id=c.id;+----+----------+------+--------+--------+------+-------+---------------+
| id | name | age | height | gender | id | title | student_count |
+----+----------+------+--------+--------+------+-------+---------------+
| 1 | peiqi | 0 | NULL | ? | 1 | 2班 | 30 |
| 2 | waang | 15 | NULL | NULL | 2 | 3班 | 32 |
| 3 | zhangsan | 18 | 180.00 | ? | NULL | NULL | NULL |
| 4 | lisi | 18 | 160.00 | ? | NULL | NULL | NULL |
| 5 | 平二 | 18 | 160.00 | ? | NULL | NULL | NULL |
+----+----------+------+--------+--------+------+-------+---------------+
5 rows in set (0.00sec)
mysql>
View Code
4. 右连接查询
以右表为主根据条件查询左表数据，如果根据条件查询左表数据不存在使用null值填充
右连接查询效果图:
右连接查询语法格式:
select 字段 from 表1 right join 表2 on 表1.字段1 = 表2.字段2
说明:
right join 就是右连接查询关键字
on 就是连接查询条件
表1 是左表
表2 是右表
例1：使用右连接查询学生表与班级表:
mysql> select * from students as s right join class as c on s.id=c.id;+------+-------+------+--------+--------+----+-------+---------------+
| id | name | age | height | gender | id | title | student_count |
+------+-------+------+--------+--------+----+-------+---------------+
| NULL | NULL | NULL | NULL | NULL | 0 | 1班 | 20 |
| 1 | peiqi | 0 | NULL | ? | 1 | 2班 | 30 |
| 2 | waang | 15 | NULL | NULL | 2 | 3班 | 32 |
+------+-------+------+--------+--------+----+-------+---------------+
3 rows in set (0.00sec)
mysql>
View Code
6. 自连接查询
左表和右表是同一个表，根据连接查询条件查询两个表中的数据。
自连接查询的用法:
select c.id, c.title, c.pid, p.title from areas as c inner join areas as p on c.pid = p.id where p.title = '山西省';
说明:
自连接查询必须对表起别名
7. 子查询的介绍
在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句，外部那个select语句则称为主查询.
主查询和子查询的关系:
子查询是嵌入到主查询中
子查询是辅助主查询的,要么充当条件,要么充当数据源
子查询是可以独立存在的语句,是一条完整的 select 语句
7.2. 子查询的使用
例1. 查询大于平均年龄的学生:
select * from students where age > (select avg(age) from students);
例2. 查询学生在班的所有班级名字:
select name from classes where id in (select cls_id from students where cls_id is not null);
例3. 查找年龄最大,身高最高的学生:
select * from students where (age, height) = (select max(age), max(height) from students);
四：外键约束查询
1. 外键约束作用
外键约束:对外键字段的值进行更新和插入时会和引用表中字段的数据进行验证，数据如果不合法则更新和插入会失败，保证数据的有效性
2. 对于已经存在的字段添加外键约束
--为cls_id字段添加外键约束
alter table students add foreign key(cls_id) references classes(id);
3. 在创建数据表时设置外键约束
--创建学校表
create tableschool(
idint not null primary keyauto_increment,
namevarchar(10)
);--创建老师表
create tableteacher(
idint not null primary keyauto_increment,
namevarchar(10),
s_idint not null,foreign key(s_id) referencesschool(id)
);
4. 删除外键约束
--需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称
show create tableteacher;--获取名称之后就可以根据名称来删除外键约束
alter table teacher drop foreign key 外键名;
分组和聚合函数的使用
1. 数据准备
--创建 "京东" 数据库
create database jing_dong charset=utf8;--使用 "京东" 数据库
usejing_dong;--创建一个商品goods数据表
create tablegoods(
idint unsigned primary key auto_increment not null,
namevarchar(150) not null,
cate_namevarchar(40) not null,
brand_namevarchar(40) not null,
pricedecimal(10,3) not null default 0,
is_showbit not null default 1,
is_saleoffbit not null default 0);--向goods表中插入数据
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);
表结构说明:
id 表示主键 自增
name 表示商品名称
cate_name 表示分类名称
brand_name 表示品牌名称
price 表示价格
is_show 表示是否显示
is_saleoff 表示是否售完
2. SQL语句演练
--查询类型cate_name为 '超极本' 的商品名称、价格
select name,price from goods where cate_name = '超级本';--显示商品的分类
select cate_name from goods group bycate_name;--求所有电脑产品的平均价格,并且保留两位小数
select round(avg(price),2) as avg_price fromgoods;--显示每种商品的平均价格
select cate_name,avg(price) from goods group bycate_name;--查询每种类型的商品中 最贵、最便宜、平均价、数量
select cate_name,max(price),min(price),avg(price),count(*)from goods group bycate_name;--查询所有价格大于平均价格的商品，并且按价格降序排序
select id,name,price fromgoodswhere price > (select round(avg(price),2) as avg_price fromgoods)order by price desc;
扫一扫
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
