1落剪、count 使用
1.1 查詢employee表中記錄數(shù):
SELECT COUNT(*) FROM employee;
1.2 查詢員工表中有績效的人數(shù):
SELECT COUNT(performance) FROM employee;
1.3 查詢員工表中月薪大于2500的人數(shù):
SELECT COUNT(*) FROM employee WHERE salary > 2500;
1.4 統(tǒng)計(jì)月薪與績效之和大于5000元的人數(shù):
SELECT COUNT(*) FROM employee WHERE IFNULL(salary,0)+IFNULL(performance,0)>5000;
1.5 查詢有績效的人數(shù)，和有管理費(fèi)的人數(shù):
SELECT COUNT(performance),COUNT(manage) FROM employee;
2鄙信、SUM 和MAX和MIN 的使用
1权均、查詢所有雇員月薪和
SELECT SUM(salary) FROM employee;
2简僧、查詢所有雇員月薪和,以及所有雇員績效和
SELECT SUM(salary),SUM(performance) FROM employee;
3澄惊、 查詢所有雇員月薪+績效和
SELECT SUM(salary+IFNULL(performance,0)) FROM employee;
4紊馏、統(tǒng)計(jì)所有員工平均工資
SELECT AVG(salary) FROM employee;
5牺弄、 查詢最高工資和最低工資
SELECT MAX(salary),MIN(salary) FROM employee;
3姻几、分組查詢
1、 將查詢結(jié)果按照1個(gè)或者多個(gè)字段進(jìn)行分組,字段值相同的為1組
SELECT gender FROM employee GROUP BY gender;
2势告、把相同的數(shù)據(jù)去掉
SELECT gender,GROUP_CONCAT(name) FROM employee GROUP BY gender;
4蛇捌、分組查詢-使用
1、在一組里面所有男性名字和女性名字
SELECT gender,GROUP_CONCAT('name') FROM employee GROUP BY gender;
2咱台、 所有男性工資和女性工資
SELECT gender,GROUP_CONCAT(salary) FROM employee GROUP BY gender;
3络拌、對(duì)某一個(gè)部門進(jìn)行分組,查看每個(gè)部門薪資信息都羅列出來
SELECT department,GROUP_CONCAT(salary) FROM employee GROUP BY department;
5、GROUP BY + 聚合函數(shù)
1回溺、查看部門平均薪資
SELECT department,GROUP_CONCAT(salary),SUM(salary) FROM employee GROUP BY department;
2春贸、查看每個(gè)部門的最高薪資
SELECT department,GROUP_CONCAT(salary),MAX(salary) FROM employee GROUP BY department;
3混萝、查看每個(gè)部門的最低薪資
SELECT department,GROUP_CONCAT(salary),MIN(salary) FROM employee GROUP BY department;
4、查詢每個(gè)部門的部門名稱和每個(gè)部門的工資和
SELECT department,GROUP_CONCAT(salary),SUM(salary) FROM employee GROUP BY department;
5萍恕、查詢每個(gè)部門的部門名稱和每個(gè)部門的人數(shù)
SELECT department,GROUP_CONCAT(name),COUNT(*) FROM employee GROUP BY department;
6逸嘀、查詢每個(gè)部門的部門名稱以及每個(gè)部門工資大于1500的人數(shù)
SELECT name,salary FROM employee WHERE salary>1500;
SELECT department,GROUP_CONCAT(salary),COUNT(*) FROM employee WHERE salary>1500 GROUP BY department;
6、GROUP BY + having
1允粤、把所有部門進(jìn)行分組
SELECT department FROM employee GROUP BY department;
2厘熟、每一組有哪些薪資,以及薪資總和
SELECT department,GROUP_CONCAT(salary),SUM(salary) FROM employee GROUP BY department;
3、完成
SELECT department,GROUP_CONCAT(salary),SUM(salary) FROM employee GROUP BY department HAVING SUM(salary)>9000;
7维哈、HAVING和WHERE的區(qū)別
HAVING是在分組后對(duì)數(shù)據(jù)進(jìn)行過濾;
WHERE是在分組前對(duì)數(shù)據(jù)進(jìn)行過濾;
HAVING后面可以使用分組函數(shù)(統(tǒng)計(jì)函數(shù));
WHERE后面不可以使用分組函數(shù);
WHERE是對(duì)分組前記錄的條件,如果某行記錄沒有滿足where子語句的條件,那么這行記錄不參與分組;而HAVING是對(duì)分組后的數(shù)據(jù)約束;
1、查詢工資大于2000的,工資總和大于6000的部門名稱以及工資和;
各部門工資
各部門工資總和
各部門工資總和大于6000
各部門工資總和大于6000降序排序
2.登澜、查詢工資大于2000
SELECT salary FROM employee WHERE salary>2000;
3阔挠、工資總和
SELECT department,GROUP_CONCAT(salary),SUM(salary) FROM employee WHERE salary > 2000 GROUP BY department HAVING SUM(salary)>9000;
ORDER BY SUM(salary) DESC;
8、書寫順序脑蠕，執(zhí)行順序
書寫順序SELECT --> FROM --> WHERE --> GROUP BY --> HAVING --> ORDER BY --> LIMIT
執(zhí)行順序FROM => WHERE => GROUP BY => HAVING => SELECT => ORDER BY => LIMIT
