#行子查询
#查询员工编号最小并且工资最高的员工信息
#方法一
#第一步
SELECT
MIN(employee_id)
FROM
employees
#第二部
SELECT
MAX(salary)
FROM
employees

#结合起来
SELECT
*
FROM
employees
WHERE
employee_id =(SELECT
MIN(employee_id)
FROM
employees
)AND
salary=(SELECT
MAX(salary)
FROM
employees
);
#方法二
SELECT
*
FROM
employees
WHERE
(employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary)
FROM
employees);
#查询每个部门的员工个数
SELECT
d.*,(SELECT
COUNT(*)
FROM
employees e
WHERE
e.department_id=d.`department_id`) 个数
FROM
departments d

#查询员工号=102的部门名

SELECT
department_name
FROM
departments d
INNER JOIN
employees e
ON
d.`department_id` =e.department_id
WHERE
e.employee_id='102';

#from后面表查询
#把from的子查询 看成是一张表
#查询每个部门的平均工资的工资等级

SELECT
aug.*,j.`grade_level`
FROM
(SELECT
AVG(salary) ag,department_id
FROM
employees
GROUP BY
department_id) aug
INNER JOIN
job_grades j
ON
aug.ag BETWEEN j.`lowest_sal`AND j.`highest_sal`;

 

#exists后面放表查询
#查询没有女朋友的男神信息
#方法一
SELECT
b.*
FROM
beauty t
RIGHT OUTER JOIN
boys b
ON
t.`boyfriend_id`=b.`id`
WHERE
t.`boyfriend_id` IS NULL;

#方法二
SELECT
b.*
FROM
boys b
WHERE b.`id` NOT IN(SELECT
boyfriend_id
FROM
beauty);

#方法三
SELECT
boys.*
FROM
boys
WHERE
NOT EXISTS(
SELECT
boyfriend_id
FROM
beauty
WHERE
beauty.`boyfriend_id`=boys.`id`);