MySQL select语句的七大子句

/*

select的七大子句：

1、from：从哪些表中查询数据

2、on：主要用在关联查询中，后面跟关联条件

3、where：主要用于数据筛选，后面可以以某个字段为条件，但是一定不能以分组函数为条件

4、group by：可以根据某个或某几个字段进行分组，会将该字段之一样的数据分为一组

group by单独使用没有任何意义，必须和分组函数结合使用

但是分组函数可以单独使用，此时是将所有数据作为一组

5、having：是在查询的结果中再次进行筛选，因此后面的条件必须是select后面出现的字段或分组函数

注意：on后主要跟关联条件，where后跟字段作为条件，having后跟分组函数作为条件

6、order by：排序，根据某个或某几个字段进行排序，asc表示升序（默认），desc表示降序

7、limit，分页

limit index,pageSize

index：当前页的起始索引

pageSize：每页显示的条数

pageNum：当前页的页码

index = (pageNum-1)*pageSize

pageNum=1，pageSize=4，limit 0,4

pageNum=4，pageSize=4，limit 12,4

pageNum=8，pageSize=4，limit 28,4

pageNum=4，pageSize=6，limit 18,6

limit 4-->limit 0,4

*/

#测试on

SELECT * FROM t_employee ON eid > 10; -- X

SELECT * FROM t_employee emp LEFT JOIN t_department dept ON emp.eid = 10; -- √

#测试where

SELECT * FROM t_employee WHERE eid > 10; -- √

SELECT AVG(salary) FROM t_employee WHERE AVG(salary)>15000 GROUP BY did; -- X

#测试group by

#查询每个部门的平均薪资，最高薪资，最低薪资，薪资综合，人数

SELECT did,AVG(salary),MAX(salary),MIN(salary),SUM(salary),COUNT(0) FROM t_employee WHERE did IS NOT NULL GROUP BY did;

#查询男女的平均薪资

SELECT sex,AVG(salary) FROM t_employee WHERE did IS NOT NULL GROUP BY sex;

#查询每个部门男女的平均薪资

SELECT did,sex,AVG(salary) FROM t_employee WHERE did IS NOT NULL GROUP BY did,sex;

SELECT did,sex,AVG(salary) FROM t_employee WHERE did IS NOT NULL GROUP BY sex,did;

#测试having

SELECT * FROM t_employee HAVING eid > 10; -- √

SELECT AVG(salary) FROM t_employee WHERE did IS NOT NULL GROUP BY did HAVING AVG(salary) > 15000;

SELECT did,AVG(salary) FROM t_employee WHERE did IS NOT NULL GROUP BY did HAVING eid > 10; -- X

SELECT did,AVG(salary) FROM t_employee WHERE did IS NOT NULL GROUP BY did HAVING did > 1; -- √

SELECT did,AVG(salary) FROM t_employee WHERE did IS NOT NULL AND did > 1 GROUP BY did; -- √

#测试order by

SELECT * FROM t_employee ORDER BY salary DESC,kpi ASC;

#测试limit

SELECT * FROM t_employee LIMIT 4;