098-

SELECT          7
FROM            1
JOIN ON         2
WHERE           3
GROUP           4
HAVING          6
ORDER BY        8
LIMIT OFFSET,size;     8

OFFSET 要显示条目的其实索引
size 要显示的条目个数;
#特点：放在语句最后，要显示的页数page，每页显示条数size

#查询天5条员工信息
SELECT * FROM `employees` LIMIT 0,5;#0表示起始位置，一般为0，可以将0省略

#查询第11~25条记录
SELECT * FROM `employees` LIMIT 10,15;

#有奖金的员工信息，并且工资较高的前10名显示
SELECT * 
FROM `employees` 
WHERE `commission_pct` IS NOT NULL 
ORDER BY `salary` DESC 
LIMIT 10 ;

/*
select *
from 
limit ,(page-1)*size,size ;
size=10
page
1 0
2 20
3 30
*/


SELECT SUBSTR(`email`,1,INSTR(`email`,'@')-1)
FROM ;


SELECT last_name,salary
FROM `employees`
WHERE salary=(
	SELECT MIN(salary)
	FROM `employees`
);



SELECT AVG(salary),`department_id`
FROM `employees`
GROUP BY `department_id`
HAVING AVG(salary)=(
	SELECT MIN(ag)
	FROM (
		SELECT AVG(salary) ag,`department_id`
		FROM `employees`
		GROUP BY `department_id`
	) ag_dep
);


SELECT AVG(`salary`),`job_id`
FROM `employees`
GROUP BY `job_id`
ORDER BY AVG(salary) DESC
LIMIT 1;



#查询平均工资最低的部门信息和该部门的平均工资
#1
SELECT AVG(salary),`department_id`
FROM `employees`
GROUP BY `department_id`

#2
SELECT `department_id`
FROM `employees`
GROUP BY `department_id`
ORDER BY AVG(salary)
LIMIT 1

#3
SELECT *
FROM `departments`
WHERE `department_id`=(
	SELECT `department_id`
	FROM `employees`
	GROUP BY `department_id`
	ORDER BY AVG(salary)
	LIMIT 1
);





SELECT *
FROM `jobs`
WHERE `job_id`=(	
	SELECT job_id
	FROM `employees` e
	GROUP BY `job_id`
	ORDER BY AVG(`salary`) DESC
	LIMIT 1
);



SELECT AVG(`salary`),`department_id`
FROM `employees`
GROUP BY `department_id`
HAVING AVG(salary)>(
	SELECT AVG(salary)
	FROM `employees`
)



SELECT MAX(salary),`department_id`
FROM `employees`
GROUP BY `department_id`
ORDER BY MAX(salary) ASC
LIMIT 1


SELECT MAX(salary),`department_id`
FROM `employees`
GROUP BY `department_id`
HAVING `department_id`=(
	SELECT `department_id`
	FROM `employees`
	GROUP BY `department_id`
	ORDER BY MAX(salary) ASC
	LIMIT 1
)




#1
SELECT `department_id`
FROM `employees`
GROUP BY `department_id`
ORDER BY AVG(`salary`) DESC
LIMIT 1;


SELECT `manager_id`
FROM `employees`
WHERE `department_id`=(
	SELECT `department_id`
	FROM `employees`
	GROUP BY `department_id`
	ORDER BY AVG(`salary`) DESC
	LIMIT 1
)


SELECT `last_name`,`department_id`,`email`,`salary`
FROM `employees`
WHERE `employee_id` IN(
	SELECT `manager_id`
	FROM `employees`
	WHERE `department_id`=(
		SELECT `department_id`
		FROM `employees`
		GROUP BY `department_id`
		ORDER BY AVG(`salary`) DESC
		LIMIT 1
	)
);