mysql 标量_mysql 标量子查询和非法子查询

mysql 标量子查询和非法子查询

发布时间：2020-03-02 09:09:38

来源：51CTO

阅读：261

作者：wx5d21d5e6e5ab1

#where或having后面:

#标量子查询(单行子查询)

#列子查询(多行子查询)

#行子查询(多行多列)

特点:子查询放在小括号内，一般放在条件的右侧，标量子查询一般配备单行操作符使用

单行操作符：<> >= <= < >

列子查询：一般搭配着多行操作符使用

多行操作符：in、any、some、all

#标量子查询

#案例：谁的工资比ABEL高的员工信息

SELECT *

FROM employees

WHERE salary>(

SELECT salary

FROM employees

WHERE last_name='Abel'

);

#案例：返回job_id与141号员工相同，salary比143号员工多的员工的姓名、job_id和工资

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

);

#案例：返回工资最少的员工的last_name、job_id和salary

SELECT last_name,job_id,salary

FROM employees

WHERE salary=(

SELECT MIN(salary)

FROM employees

);

#案例：查询最低工资大于50号部门最低工资的部门id和其最低工资

SELECT department_id,MIN(salary)

FROM employees

GROUP BY department_id

HAVING MIN(salary)>(

SELECT MIN(salary)

FROM employees

WHERE department_id=50

);

#非法使用标量子查询

SELECT department_id,MIN(salary)

FROM employees

GROUP BY department_id

HAVING MIN(salary)>(

SELECT salary #单行操作符只能搭配标量子查询，而这是列子查询

FROM employees

WHERE department_id=50

);