SQL 语法③

[ ] SQL中的函数

函数也叫：聚合函数，分组函数，聚集函数，内建函数，合计函数.....

SQL语言中定义了部分的函数，可以帮助我们完成对查询结果的计算操作：

count()函数：统计个数

sum()函数：一般用于数值类型的字段求和

avg()函数：一般用于数值类型的字段求平均值

max()、min() 函数求字段的最大值和最小值

---

① count函数

语法

select count(*) from tableName where 条件;# 统计某一列的数据，包含null
select count(字段) from tableName where 条件;# 统计指定列的数据，不包含null
select count( distinct 字段) from tableName where 条件;# 排重统计

---

练习

1) 统计一个班级共有多少学生？

select count(*) from student1;
select count(age) from student1;
select count(distinct age) from student1;

---

2) 统计成绩大于80的学生有多少个？

select * from student1 where score > 80;
select count(*) from student1 where score > 80;
select count(score) from student1 where score > 80;

② sum函数

语法

select sum(字段) from tableName where 条件; 

---

练习

1) 统计一个班级成绩和

select sum(score) from student;

2) 分别统计年龄和成绩的和

select sum(age) 年龄 , sum(score) 成绩 from student;

3) 统计年龄和成绩和值

select sum(age) + sum(score) 和值 from student;
select sum(age+score) 和值 from student;

使用上述第二种方式计算时，发现统计的和值结果是不正确的，原因是如果使用sum() 多列进行求和的时候，如果某一列中的值有null，这一列所在的行中的数据结果为0，null和任何数据相加都等于0。

可以使用mysql 数据库提供的函数： ifnull(列名,用于计算的临时值) 

select sum(ifnull(age,0) + ifnull(score,0)) 和值 from student;

---

③ avg函数

语法

select avg(字段) from tableName where 条件;

---

④ max,min函数

语法

select max(字段) from tableName where 条件;
select min(字段) from tableName where 条件;

---

⑤ group by分组查询

语法

select * from tableName group by 字段;

分组查询一般结合聚合函数一起使用，当某个字段被分组之后，聚合函数则根据分组的结果进行聚合函数的运算。

准备数据

create table orders(
    id int primary key auto_increment,
    product varchar(20),
    price double
);
insert into orders values (NULL,'电视机',2999);
insert into orders values (NULL,'电视机',2999);
insert into orders values (NULL,'洗衣机',1000);
insert into orders values (NULL,'洗衣机',1000);
insert into orders values (NULL,'洗衣机',1000);
insert into orders values (NULL,'冰箱',3999);
insert into orders values (NULL,'冰箱',3999);
insert into orders values (NULL,'空调',1999);

查询每种商品的总价格

select product,sum(price) from orders group by product;

查询商品总价格超过3000的商品

select product , sum(price) from orders group by product having sum(price) > 150;

如果使用group by 对数据进行分组之后还要过滤。这时一般不能使用where，因为where关键字的后面不能跟上面讲解的这些函数。如果需要在过滤的条件中加上述的函数，只能使用having关键字。

---

练习题

#按商品名称统计，每类商品所购买的个数：
select product,count(*) from orders group by product;
#按商品名称统计，统计每类商品花费的总金额在5000元以上的商品，并且按照总金额升序排序
select product,sum(price) sum from orders group by product having sum(price) > 5000 order by sum asc;
#按商品名称统计，统计每类商品购买数量大于1的商品，并且按照购买数量降序排序,只展示商品和购买数量
select product,count(*) num from orders group by product having num > 1 order by num desc;

---

[ ] 查询练习

① 准备数据

create table exam( 
    id int primary key auto_increment,
    name varchar(20),
    english int,
    chinese int,
    math    int
);
insert into exam values (NULL,'张三',85,74,91);
insert into exam values (NULL,'李四',95,90,83);
insert into exam values (NULL,'王五',85,84,59);
insert into exam values (NULL,'赵六',75,79,76);
insert into exam values (NULL,'田七',69,63,98);
insert into exam values (NULL,'李老八',89,90,83);

---

练习

1) 查询所有学生考试成绩信息

select * from exam;

2) 查询所有学生的姓名和英语成绩

select name,english from exam;

3) 查询英语成绩信息（不显示重复的值）

select distinct english from exam;

4) 查看学生姓名和学生的总成绩

select name,sum(english + chinese + math) from exam group by name;

5) 查询学生的姓名和平均分,平均分用avg别名展示

select name,(english+chinese+math) / 3 as avg from exam group by name;

6) 查询李四学生的成绩：

select * from exam where name = '李四';

7) 查询名称叫李四学生并且英文大于90分

select name,english from exam where english > 90 and name = '李四';

8) 查询姓李的学生的信息

select * from exam where name like '李%’；

9) 查询英语成绩是69,75,89学生的信息

select * from exam where english in(69,75,89);

10) 查询数学成绩在80-90之间的学生信息

select * from exam where math between 80 and 90;        

select * from exam where math >= 80 and math <= 90;

11) 只要有一门不及格,就找出来

select * from exam where english < 60 or chinese < 60 or math < 60;

12) 查询学生信息，并且按照语文成绩进行排序

select * from exam order by chinese;

13) 查询学生信息，并且按照语文成绩倒序排序：

select * from exam order by chinese desc;

14) 查询学生信息，先按照语文成绩进行倒序排序，如果成绩相同再按照英语成绩升序排序

select * from exam order by chinese desc,english asc;

15) 查询姓李的学生的信息，按照英语成绩降序排序

select * from exam where name like '李%' order by english desc;

16) 查询学生信息,按照总成绩排序,只展示学生的姓名和总分(SUM)

select name , sum(english + chinese + math) sum from exam group by name order by sum;

17) 获取所有学生的英语成绩的总和:

select sum(english) from exam;

18) 获取所有学生的英语成绩和数学成绩总和：

select sum(ifnull(english,0) + ifnull(math,0)) from exam;

select sum(english) + sum(math) from exam;

19) 查询姓李的学生的英语成绩的总和

select sum(english) from exam where name like '李%';

20) 查询所有学生各科的总成绩：

select sum(english),sum(chinese),sum(math) from exam;

21) 获得姓李的学生的个数

select count(*) from exam where name like '李%';

22) 获得数学成绩的最高分：

select max(math) from exam;

23) 获得语文成绩的最低分

select min(chinese) from exam;

24) 获取语文成绩的平均值

select avg(chinese) from exam;