day04-MySQL常用函数01

5.MySQL常用函数

5.1合计/统计函数

5.1.1合计函数-count

count 返回行的总数

1. Select count(*)|count (列名) from table_name
2.         [WHERE where_definition]

复制代码

练习

1. -- 统计一个班级共有几个学生
2. SELECT COUNT(*) FROM student
4. -- 统计数学成绩大于90的学生有多少个
5. SELECT COUNT(*) FROM student
6.         WHERE math > 90
7.        
8. -- 统计总分大于250的人数有多少个
9. SELECT COUNT(*) FROM student
10.         WHERE chinese+english+math> 250

复制代码

count(*) 和 count(列)的区别:
count(*)返回满足条件的记录的行数
count(列)统计满足条件的某列有多少个，但是会排除为null的情况
5.1.2合计函数-sum

sum函数返回满足where条件的行的和，一般使用在数值列

1. Select sum(列名) {,sum(列名)...} from tablename
2.         [WHERE where_definition]

复制代码

练习

1. -- sum函数
2. -- 统计一个班的数学总成绩
3. SELECT SUM(math) FROM student
5. -- 统计一个班语文、英语、数学各科的总成绩
6. SELECT SUM(math),SUM(english),SUM(chinese) FROM student
8. -- 统计一个班语文、英语、数学的成绩总和
9. SELECT SUM(math+english+chinese) FROM student
11. -- 统计一个班级语文成绩平均分
12. SELECT SUM(chinese)/COUNT(*) FROM student

复制代码

注意：
sum仅对数值起作用，否则没有意义
对多列求和，“  ,  ” 不能少
5.1.3合计函数-avg

avg函数返回满足where条件的一列的平均值

1. Select avg (列名) {,avg(列名)...} from tablename
2.         [WHERE where_definition]

复制代码

练习

1. -- avg 函数
2. -- 求一个班级数学平均分
3. SELECT AVG(math) FROM student
4. -- 求一个班级总分平均分
5. SELECT AVG(chinese+english+math) FROM student

复制代码

5.1.4 合计函数-Max/min

Max/min函数返回满足where条件的一列的最大/最小值

1. Select max (列名) {,avg(列名)...} from tablename
2.         [WHERE where_definition]

复制代码

练习

1. -- max和min 函数
2. -- 求班级最高分和最低分
3. SELECT MAX(chinese+english+math),MIN(chinese+english+math)FROM student
5. -- 求班级数学最高分和最低分
6. SELECT MAX(math),MIN(math)FROM student

复制代码

5.1.5分组统计-group by

• 使用group by子句对列进行分组
  

1. SELECT column1,column2,column3... FROM table
2.         group by column

复制代码

• 使用having子句对分组后的结果进行过滤
  

1. SELECT column1,column2,column3...
2.         FROM table
3.         group by column having ...

复制代码

练习：

[code]-- 先创建测试表CREATE TABLE dept( /*部门表*/deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0, dname VARCHAR(20)  NOT NULL  DEFAULT "",loc VARCHAR(13) NOT NULL DEFAULT "");INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK'), (20, 'RESEARCH', 'DALLAS'), (30, 'SALES', 'CHICAGO'), (40, 'OPERATIONS', 'BOSTON');SELECT * FROM dept;-- 员工表CREATE TABLE emp(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*编号*/ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/mgr MEDIUMINT UNSIGNED ,/*上级编号*/hiredate DATE NOT NULL,/*入职时间*/sal DECIMAL(7,2)  NOT NULL,/*薪水*/comm DECIMAL(7,2) ,/*红利 奖金*/deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/);-- 添加测试数据 INSERT INTO emp VALUES(7369, 'SMITH', 'CLERK', 7902, '1990-12-17', 800.00,NULL , 20), (7499, 'ALLEN', 'SALESMAN', 7698, '1991-2-20', 1600.00, 300.00, 30),  (7521, 'WARD', 'SALESMAN', 7698, '1991-2-22', 1250.00, 500.00, 30),  (7566, 'JONES', 'MANAGER', 7839, '1991-4-2', 2975.00,NULL,20),  (7654, 'MARTIN', 'SALESMAN', 7698, '1991-9-28',1250.00,1400.00,30),  (7698, 'BLAKE','MANAGER', 7839,'1991-5-1', 2850.00,NULL,30),  (7782, 'CLARK','MANAGER', 7839, '1991-6-9',2450.00,NULL,10),  (7788, 'SCOTT','ANALYST',7566, '1997-4-19',3000.00,NULL,20),  (7839, 'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),  (7844, 'TURNER', 'SALESMAN',7698, '1991-9-8', 1500.00, NULL,30),  (7900, 'JAMES','CLERK',7698, '1991-12-3',950.00,NULL,30),  (7902, 'FORD', 'ANALYST',7566,'1991-12-3',3000.00, NULL,20),  (7934,'MILLER','CLERK',7782,'1992-1-23', 1300.00, NULL,10);SELECT * FROM emp;-- 工资级别#工资级别表CREATE TABLE salgrade(grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*工资级别*/ losal DECIMAL(17,2)  NOT NULL, /* 该级别的最低工资 */hisal DECIMAL(17,2)  NOT NULL /* 该级别的最高工资*/);INSERT INTO salgrade VALUES (1,700,1200);INSERT INTO salgrade VALUES (2,1201,1400);INSERT INTO salgrade VALUES (3,1401,2000);INSERT INTO salgrade VALUES (4,2001,3000);INSERT INTO salgrade VALUES (5,3001,9999);SELECT * FROM salgrade;SELECT * FROM dept;SELECT * FROM emp;# 演示group by + having# GROUP by用于对查询的结果分组统计, having子句用于限制分组显示结果.-- 如何显示每个部门的平均工资和最高工资-- 按照部门来分组查询SELECT AVG(sal),MAX(sal) ,deptno        FROM emp         GROUP BY deptno;-- 显示每个部门的每种岗位的平均工资和最低工资# 1.先显示每个部门的平均工资和最低工资# 2.再显示每个部门的每种岗位的平均工资和最低工资SELECT AVG(sal),MIN(sal) ,deptno,job        FROM emp         GROUP BY deptno,job; -- 先按照部门分组，再按照岗位分组-- 显示平均工资低于2000的部门号和它的平均工资# 1.先显示各个部门的平均工资和部门号# 2.再显示平均工资低于2000的（having过滤）SELECT AVG(sal),deptno        FROM emp         GROUP BY deptno        HAVING AVG(sal)