标量子查询

#一、where或having后面
#1.标量子查询(单行子查询)
#2、列子查询(多行子查询)
#3.行子查询(多行多列)
/*
特点：
（1）子查询放在小括号内
（2）子查询一般放在条件的右侧
（3）标量子查询，一般搭配着单行操作符使用
> < >= <= = <>
列子查询，一般搭配着多行操作符使用
IN、ANY/SOME、ALL 
(4)子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果。
*/
#1.标量子查询

#案例1：谁的工资比 Abel高？
#（1）查询Abel的工资
SELECT `salary`
FROM `employees`
WHERE `last_name`='Abel';
#(2)查询员工的信息，满足salary>(1)结果
SELECT 
  `last_name`,
  salary 
FROM
  `employees` 
WHERE salary > 
  (SELECT 
    `salary` 
  FROM
    `employees` 
  WHERE `last_name` = 'Abel') ;
#案例2：返回job_id与141号员工相同，`salary`比143号员工多的员工姓名，job_id和工资
#(1)查询141号员工的job_id,工资
SELECT  job_id
FROM  employees
WHERE  `employee_id`=141;
#(2)查询143号员工的job_id,工资
SELECT  `salary`
FROM  employees
WHERE  `employee_id`=143;
#（3）查询员工的姓名，job_id和工资，要求job_id=(1)并且salary>(2)
SELECT `last_name`,`job_id`,`salary`
FROM `employees`
WHERE job_id=
(
SELECT  job_id
FROM  employees
WHERE  `employee_id`=141
)
AND salary>(
SELECT  `salary`
FROM  employees
WHERE  `employee_id`=143
);

#案例3：返回公司工资最少的员工的`last_name`，`job_id`和`salary`
#(1)查询公司的最低工资
SELECT MIN(`salary`)
FROM `employees`;
#(2)查询`last_name`，`job_id`和`salary`，要求`salary`=（1）
SELECT `last_name`,`job_id`,`salary`
FROM  `employees`
WHERE `salary`=(
SELECT MIN(`salary`)
FROM `employees`
);

#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
#查询50号部门的最低工资
SELECT MIN(`salary`)
FROM `employees`
WHERE  `department_id`=50;
#查询每个部门的最低工资
SELECT MIN(`salary`),`department_id`
FROM `employees`
GROUP BY `department_id`;
#(3)在（2）基础上筛选，满足min(`salary`)>(1)
SELECT  MIN(`salary`),`department_id`
FROM `employees`
GROUP BY `department_id`
HAVING MIN(`salary`)>(
SELECT MIN(`salary`)
FROM `employees`
WHERE  `department_id`=50
);

/*
非法使用标量子查询
*/
SELECT  MIN(`salary`),`department_id`
FROM `employees`
GROUP BY `department_id`
HAVING MIN(`salary`)>(
SELECT `salary`
FROM `employees`
WHERE  `department_id`=50
);
#用成了列子查询，应该用标量子查询

SELECT  MIN(`salary`),`department_id`
FROM `employees`
GROUP BY `department_id`
HAVING MIN(`salary`)>(
SELECT `salary`
FROM `employees`
WHERE  `department_id`=150
);
#查询不存在的值