mysql 函数多行查询 mysql多行子查询

转载

mob6454cc719119 2023-10-12 13:50:32

文章标签 mysql 函数多行查询 mysql 数据库 sql 子查询 文章分类 MySQL 数据库

第九章子查询

#查询员工中工资比Abel多的人
 #子查询
 SELECT e2.last_name,e2.salary
 FROM employees e1,employees e2
 WHERE e1.last_name=‘Abel’ AND e1.salary<e2.salary;#子查询
 SELECT last_name,salary
 FROM employees
 WHERE salary>(
 SELECT salary
 FROM employees
 WHERE last_name=‘Abel’
 );#查询可分为单行子查询和多行子查询，也可以分为相关子查询与不相关子查询
#相关子查询：查询工资大于本部门平均工资的员工信息
 SELECT last_name,department_id,salary
 FROM employees
 WHERE salary>(
 SELECT AVG(salary)
 FROM employees
 )
 GROUP BY department_id;#不相关子查询：查询工资大于本公司平均工资的员工信息
 SELECT last_name,salary
 FROM employees
 WHERE salary>(
 SELECT AVG(salary)
 FROM employees
 )#查询工资大于149号员工工资的员工的信息
 SELECT employee_id,last_name,salary
 FROM employees
 WHERE salary>(
 SELECT salary
 FROM employees
 WHERE employee_id=149
 );#返回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
 );#查询与141号(employee_id)员工的manager_id和department_id相同的其他员工的employee_id，manager_id，department_id
#方式一：不成对比较，单独比较
 SELECT employee_id,manager_id,department_id
 FROM employees
 WHERE manager_id=(
 SELECT manager_id
 FROM employees
 WHERE employee_id=141
 )
 AND department_id=(
 SELECT department_id
 FROM employees
 WHERE employee_id=141
 )
 AND employee_id<>141;#方式二：成对比较
 SELECT employee_id,manager_id,department_id
 FROM employees
 WHERE (manager_id,department_id)=(
 SELECT manager_id,department_id
 FROM employees
 WHERE employee_id=141
 )
 AND employee_id <>141;#查询与141号或174员工的manager_id和department_id相同的其他员工的employee_id，manager_id，department_id
#方式一：不成对比较
 SELECT employee_id,manager_id,department_id
 FROM employees
 WHERE manager_id IN
 (SELECT manager_id
 FROM employees
 WHERE employee_id IN(141,174))
 AND department_id IN
 (SELECT department_id
 FROM employees
 WHERE employee_id IN(141,174))
 AND employee_id NOT IN(141,174);#成对比较
 SELECT employee_id,manager_id,department_id
 FROM employees
 WHERE (manager_id,department_id)IN(
 SELECT manager_id,department_id
 FROM employees
 WHERE employee_id IN(141,174))
 AND employee_id NOT IN(141,174);#having中的子查询
#查询最低工资大于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
 );#case中的子查询
#显示员工的employee_id,last_name和location,其中,若员工department_id与location_id为1800的department_id相同，则lacation为’Canada’,其余为’USA’
 SELECT employee_id,last_name,
 (CASE department_id
 WHEN
 (SELECT department_id FROM departmentsemployees WHERE location_id=1800)
 THEN ‘Canada’ ELSE ‘USA’ END)
 AS location
 FROM employees;#查询书名和类型，其中note值为novel显示小说，law显示法律，
 medicine显示医药，cartoon显示卡通，joke显示笑话
 SELECT name’书名’,note,
 CASE note
 WHEN’novel’ THEN’小说’
 WHEN’law’THEN’法律’
 WHEN’medicine’THEN’医药’
 WHEN’cartoon’THEN’卡通’
 WHEN’joke’THEN’笑话’
 ELSE’其他’
 END
 AS ‘类型’
 FROM books;

查询书名、库存，其中num值超过30本的，显示滞销，大于0并低于10的，

#显示畅销，为0的显示需要无货
 SELECT nameAS’书名’,num AS’库存’,
 CASE WHEN num>30 THEN’滞销’
 WHEN 0<num<10 THEN’畅销’
 WHEN num=0 THEN’无货’
 ELSE ‘正常’
 END
 AS ‘显示状态’
 FROM books;#子查询的空值情况:因为里面的子查询为空，没有叫Haas的人，所有最终没有返回任何行
 SELECT last_name,job_id
 FROM employees
 WHERE job_id=(
 SELECT job_id
 FROM employees
 WHERE last_name=‘Haas’
 )#非法使用子查询:用等于会报错，因为这个子查询会返回多行数据，把等号改为in即可
 SELECT employee_id,last_name
 FROM employees
 WHERE salary =(
 SELECT MIN(salary)
 FROM employees
 GROUP BY department_id
 )#因为有不同的员工领着相同的最低工资
 SELECT employee_id,last_name
 FROM employees
 WHERE salary IN(
 SELECT MIN(salary)
 FROM employees
 GROUP BY department_id
 )#多行子查询
 #多行子查询的操作符：in() any all some(是any的别名，一般用any)
#返回其他job_id中比job_id为’IT_PROG’部门任一员工工资低的员工号、姓名、job_id以及salary
 SELECT employee_id,last_name,job_id,salary
 FROM employees
 WHERE salary<ANY(
 SELECT salary
 FROM employees
 WHERE job_id=‘IT_PROG’)
 AND job_id!=‘IT_PROG’SELECT* FROM employees
 WHERE employee_id!=101;#返回其他job_id中比job_id为’IT_PROG’部门工资都低的员工号、姓名、job_id以及salary
 SELECT employee_id,last_name,job_id,salary
 FROM employees
 WHERE salary<(
 SELECT MIN(salary)
 FROM employees
 WHERE job_id=‘IT_PROG’)
 AND job_id!=‘IT_PROG’#或者
 SELECT employee_id,last_name,job_id,salary
 FROM employees
 WHERE salary<ALL(
 SELECT salary
 FROM employees
 WHERE job_id=‘IT_PROG’)
 AND job_id!=‘IT_PROG’#查询平均工资最低的部门id
#方式一:还是方式一用any,all这种容易理解
 SELECT department_id
 FROM employees
 GROUP BY department_id
 HAVING AVG(salary)<=ALL(
 SELECT AVG(salary)
 FROM employees
 GROUP BY department_id
 )#方式二:三个字段别名一定要有，否则报错,every derived table must have its own itias
#每一个保留下的表都要有自己的别名
 SELECT department_id
 FROM employees
 GROUP BY department_id
 HAVING AVG(salary)=(
 SELECT MIN(avg_sal)
 FROM (SELECT AVG(salary)avg_sal
 FROM employees
 GROUP BY department_id)dept_avg_sal
 )#看下面第一个错，第二个正确 :嵌套函数要以其命名别名的方式
 SELECT MIN(AVG(salary))
 FROM (
 SELECT AVG(salary)
 FROM employees
 GROUP BY department_id
 )SELECT MIN(ll)
 FROM (
 SELECT AVG(salary)ll
 FROM employees
 GROUP BY department_id
 )kkk#相关子查询:比较重要，每次都内查询一次
 #回顾：查询员工中工资大于本部门平均工资的last_name,salary和其department_id
 SELECT last_name,salary,department_id
 FROM employees
 WHERE salary>(
 SELECT AVG(salary)
 FROM employees
 )#查询员工中工资大于60号部门平均工资的last_name,salary和其department_id
 SELECT last_name,salary,department_id
 FROM employees
 WHERE salary>(
 SELECT AVG(salary)
 FROM employees
 WHERE department_id=60
 )#相关子查询：查询员工中工资大于60号部门平均工资的last_name,salary和其department_id 查询员工中工资大于本部门平均工资的last_name,salary和其department_id
 SELECT last_name,salary,department_id
 FROM employees e1
 WHERE salary>(
 SELECT AVG(salary)
 FROM employees
 WHERE department_id=e1.department_id
 )#在from中使用子查询
 SELECT last_name,salary,e1.department_id
 FROM employees e1,(
 SELECT department_id,AVG(salary)dept_avg_sal #作为表的字段出现的而不是函数，必须起别名
 FROM employees
 GROUP BY department_id)e2
 WHERE e1.department_id=e2.department_id
 AND e2.dept_avg_sal<e1.salary;#查询员工的id,salary,按照department_name排序
 SELECT e1.department_id,employee_id,salary
 FROM employees e1
 ORDER BY(
 SELECT department_name
 FROM departments e2
 WHERE e1.department_id=e2.department_id )ASC;#查询员工的id,salary,按照department_name进行排序
 #如果查询的字段的表之中都有并且两表之间join,应指定表的字段
 SELECT employee_id,salary
 FROM employees e1
 ORDER BY(
 SELECT department_id
 FROM departments e2
 WHERE e1.department_id=e2.department_id );#若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同的id的员工employee_id,last_name,job_id
#下面这个是错误的
 SELECT employees.employee_id,last_name,employees.job_id
 FROM employees JOIN job_history
 ON employees.employee_id=job_history.department_id;#正确的如下，count(*)可写为count(字段名)
 SELECT e.employee_id,last_name,e.job_id
 FROM employees e
 WHERE 2<=(
 #select count() //可以count()或者count(字段)
 SELECT COUNT(employee_id)
 FROM job_history
 WHERE employee_id=e.employee_id );#exists与 not exists关键字
 #以下两个题是用的自连接
#查询公司员工的的employee_id,last_name,job_id,department_id信息
 SELECT employee_id,last_name,job_id,department_id
 FROM employees
 WHERE employee_id NOT IN(
 SELECT manager_id
 FROM employees
 WHERE manager_id IS NOT NULL
 );#查询公司管理者的employee_id,last_name,job_id,department_id信息:自连接,要distinct对管理者去重
 SELECT DISTINCT e2.employee_id,e2.last_name,e2.job_id,e2.department_id
 FROM employees e1
 JOIN employees e2
 ON e1.manager_id=e2.employee_id；#查询公司管理者的employee_id,last_name,job_id,department_id信息：用的子查询
 SELECT employee_id,last_name,job_id,department_id
 FROM employees
 WHERE employee_id IN(
 SELECT manager_id
 FROM employees
 );#查询公司管理者的employee_id,last_name,job_id,department_id信息：用exists实现
 SELECT employee_id,last_name,job_id,department_id
 FROM employees e1
 WHERE EXISTS(
 SELECT * FROM employees e2
 WHERE e1.employee_id=e2.manager_id
 )#查询departments表中，不存在与employees表中的部门的department_id和department_name
 #即查出有的部门中没有员工的情况#方式一：
 SELECT d.department_id,d.department_name
 FROM employees e RIGHT JOIN departments d
 ON e.department_id=d.department_id
 WHERE e.department_id IS NULL;#方式二
 SELECT department_id,department_name
 FROM departments d
 WHERE EXISTS(
 SELECT* FROM employees e
 WHERE d.department_id=e.department_id );

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。