5.MySQL常用函數(shù)5.1合計/統(tǒng)計函數(shù)5.1.1合計函數(shù)-countcount 返回行的總數(shù)
Select count(*)|count (列名) from table_name [WHERE where_definition]
練習
-- 統(tǒng)計一個班級共有幾個學生SELECT COUNT(*) FROM student-- 統(tǒng)計數(shù)學成績大于90的學生有多少個SELECT COUNT(*) FROM student WHERE math > 90-- 統(tǒng)計總分大于250的人數(shù)有多少個SELECT COUNT(*) FROM student WHERE chinese+english+math> 250count(*) 和 count(列)的區(qū)別:count(*)返回滿足條件的記錄的行數(shù)count(列)統(tǒng)計滿足條件的某列有多少個，但是會排除為null的情況
5.1.2合計函數(shù)-sumsum函數(shù)返回滿足where條件的行的和，一般使用在數(shù)值列
Select sum(列名) {,sum(列名)...} from tablename [WHERE where_definition]練習
-- sum函數(shù)-- 統(tǒng)計一個班的數(shù)學總成績SELECT SUM(math) FROM student-- 統(tǒng)計一個班語文、英語、數(shù)學各科的總成績SELECT SUM(math),SUM(english),SUM(chinese) FROM student-- 統(tǒng)計一個班語文、英語、數(shù)學的成績總和SELECT SUM(math+english+chinese) FROM student-- 統(tǒng)計一個班級語文成績平均分SELECT SUM(chinese)/COUNT(*) FROM student注意：sum僅對數(shù)值起作用，否則沒有意義
對多列求和，“,” 不能少
5.1.3合計函數(shù)-avgavg函數(shù)返回滿足where條件的一列的平均值
Select avg (列名) {,avg(列名)...} from tablename [WHERE where_definition]【day04-MySQL常用函數(shù)01】練習
-- avg 函數(shù)-- 求一個班級數(shù)學平均分SELECT AVG(math) FROM student-- 求一個班級總分平均分SELECT AVG(chinese+english+math) FROM student5.1.4 合計函數(shù)-Max/minMax/min函數(shù)返回滿足where條件的一列的最大/最小值Select max (列名) {,avg(列名)...} from tablename [WHERE where_definition]練習
-- max和min 函數(shù)-- 求班級最高分和最低分SELECT MAX(chinese+english+math),MIN(chinese+english+math)FROM student-- 求班級數(shù)學最高分和最低分SELECT MAX(math),MIN(math)FROM student5.1.5分組統(tǒng)計-group by- 使用group by子句對列進行分組
SELECT column1,column2,column3... FROM table group by column- 使用having子句對分組后的結果進行過濾
SELECT column1,column2,column3... FROM table group by column having ...練習：
-- 先創(chuàng)建測試表CREATE TABLE dept( /*部門表*/deptno MEDIUMINTUNSIGNEDNOT NULLDEFAULT 0,dname VARCHAR(20)NOT NULLDEFAULT "",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(empnoMEDIUMINT UNSIGNEDNOT NULLDEFAULT 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 /*部門編號*/);-- 添加測試數(shù)據(jù) 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用于對查詢的結果分組統(tǒng)計, 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)<2000-- 也可以使用別名進行過濾，如下：SELECT AVG(sal) AS avg_sal,deptno FROM emp GROUP BY deptno HAVING avg_sal<2000
經(jīng)驗總結擴展閱讀
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- cmd常用命令介紹
- 質(zhì)點運動方程是時間的函數(shù)嗎
- 餐具常用消毒的方法
- 番茄醬怎么用
- Python-函數(shù)-字符串函數(shù)
- SpringBoot 常用讀取配置文件的 3 種方法！
- 建筑勞務公司常用的會計分錄
- 浴霸燈泡型號哪種更常用?
- 餐廳吊燈高度多高好?
- python-函數(shù)-統(tǒng)計函數(shù)
