数据库的用法

1.查询每个部门人数大于3的

SELECT staff_department 部门,COUNT(*) 部门人数 
from staff_table 
GROUP BY staff_department
HAVING COUNT(*)>3

2.查询员工工资的最大值，最小值，总和,以及平均值

SELECT MAX(staff_salary) 最高工资,MIN(staff_salary) 最低工资,
SUM(staff_salary) 总和,AVG(staff_salary) 平均工资
FROM staff_table

3.查询部门编号为3的员工个数

SELECT department_id,staff_name 姓名
from staff_table
WHERE department_id=3

4.查询员工表中最大入职时间和最小入职时间的相差天数

SELECT DATEDIFF(MAX(staff_date),MIN(staff_date)),MAX(staff_date)最晚入职时间,MIN(staff_date)最早入职时间
from staff_table

5.查询出不同部门门的担任“钳工”的职工平均工资

SELECT AVG(staff_salary),staff_department
FROM staff_table
WHERE staff_type='钳工'
GROUP BY staff_department

6.查询不同部门的担任“钳工”的职工平均工资高于2000的部门

SELECT AVG(staff_salary),staff_department
FROM staff_table
WHERE staff_type='钳工'
GROUP BY staff_department
HAVING AVG(staff_salary)>2000

7.查询每个部门工资大于4000的编号和年龄

SELECT staff_salary 工资,staff_department 部门,staff_name 姓名,staff_id 编号
from staff_table
where staff_salary>4000
GROUP BY staff_department

8.查询每个部门门手机号以188开头的有多少人

SELECT COUNT(*) staff_phone,staff_department
from staff_table
WHERE staff_phone LIKE'188%'
GROUP BY staff_department

9.按员工姓名的长度分组，查询每一-组的人数大于2的

SELECT COUNT(*),LENGTH(staff_name)
FROM staff_table
GROUP BY staff_department
HAVING COUNT(*)>2