单行和多行子查询
单行和多行子查询
--子查询(单行子查询,多行子查询)
--查询比"CLARK"工资高的员工的信息?
--[1]'CLARK'的工资是多少?
SELECT SAL FROM EMP WHERE ENAME ='CLARK'; --2450
--[2]比2450高的员工信息
SELECT * FROM EMP WHERE SAL >2450;
----子查询:
--将一个查询的结果作为另外一个查询的条件来使用
SELECT * FROM EMP WHERE SAL >(SELECT SAL FROM EMP WHERE ENAME ='CLARK');
语法:
SELECT 字段列名 FROM 表名
WHERE 条件 比较符 (SELECT * FROM 表名);
-- 特点:
--子查询在主查询前执行一次
--主查询使用子查询的结果
--【1】子查询可以作为另外的一个查询的条件来使用
--查询工资高于平均工资的雇员的名字和工资
--[1]平均工资
SELECT AVG (SAL) FROM EMP;
SELECT ENAME ,SAL
FROM EMP
WHERE SAL >( SELECT AVG (SAL) FROM EMP)
ORDER BY SAL;
--查询和SCOTT同一部门且比他工资低的雇员的名字和工资
--'SCOTT'在哪个部门?
SELECT DEPTNO FROM EMP WHERE ENAME ='SCOTT'; --20
--'SCOTT'的工资?
SELECT SAL FROM EMP WHERE ENAME ='SCOTT'; --3000
SELECT ENAME,SAL
FROM EMP
WHERE DEPTNO = ( SELECT DEPTNO FROM EMP WHERE ENAME ='SCOTT')
AND SAL<(SELECT SAL FROM EMP WHERE ENAME ='SCOTT');
--注意:子查询的字段不能多于一个,只能有一个
--【2】子查询可以作为insert语句的值来使用
--今天新入职一个员工,与'SCOTT'同一个部门
INSERT INTO EMP (EMPNO,ENAME,DEPTNO,HIREDATE)
VALUES
(1001,'码上成功',
(SELECT DEPTNO FROM EMP WHERE ENAME ='SCOTT'),SYSDATE);
SELECT * FROM emp;
--【3】子查询可以作为update的条件或修改的值来使用
--将 ‘新员工’ 的工作修改为与‘SMITH’的工作相同
--[1]smith的工作
SELECT job FROM EMP WHERE ENAME = 'SMITH';
UPDATE EMP
SET JOB =( SELECT job FROM EMP WHERE ENAME = 'SMITH')
WHERE ENAME = '成功';
--将比FORD工资低的员工都加1000块钱
--[1]'FORD'的工资是
SELECT SAL FROM EMP WHERE ENAME = 'FORD';
UPDATE EMP SET SAL =SAL+1000
WHERE SAL<(SELECT SAL FROM EMP WHERE ENAME = 'FORD');
SELECT * FROM EMP;
--【4】子查询可以作为delete的条件使用
--比FORD工资高的都删除
DELETE EMP
WHERE SAL >(SELECT SAL FROM EMP WHERE ENAME = 'FORD');
--【5】子查询的结果可以作为一个表来使用
SELECT EMPNO AS 经理编号,ENAME AS 经理姓名 FROM EMP;
--查询员工的编号，姓名，经理姓名
SELECT E.EMPNO 员工编号,E.ENAME 员工姓名 ,M.经理姓名
FROM EMP E,( SELECT EMPNO AS 经理编号,ENAME AS 经理姓名 FROM EMP) M
WHERE E.MGR = M.经理编号(+); --92标准
SELECT E.EMPNO 员工编号,E.ENAME 员工姓名 ,M.经理姓名
FROM EMP E LEFT JOIN
( SELECT EMPNO AS 经理编号,ENAME AS 经理姓名 FROM EMP) M
ON E.MGR = M.经理编号; --99
--多行子查询
--子查询的结果返回是 多行数据
--all：和子查询返回的所有值比较
--any：和子查询返回的任意一个值比较
--in ：等于列表中的任何一个
--查询工资低于任何一个“CLERK”岗位的工资的雇员信息。
--查询CLERK这个职位的工资是多少? 1300
SELECT SAL FROM EMP WHERE JOB = 'CLERK';
SELECT MAX(SAL) FROM EMP WHERE JOB = 'CLERK';
SELECT * FROM EMP
WHERE SAL <(SELECT MAX(SAL) FROM EMP WHERE JOB = 'CLERK');
SELECT * FROM emp;
SELECT * FROM EMP
WHERE SAL < ANY (SELECT SAL FROM EMP WHERE JOB = 'CLERK');
--查询工资比所有的'SALESMAN'职位都高的雇员的编号,名字和工资
--[1]'SALESMAN'工资都是多少?
SELECT SAL FROM EMP WHERE JOB = 'SALESMAN';
SELECT EMPNO,ENAME,SAL
FROM EMP
WHERE SAL >ALL (SELECT SAL FROM EMP WHERE JOB = 'SALESMAN') ;
--查询部门20中的职务同部门10的雇员一样的雇员信息.
--[2]部门10是什么职务
SELECT JOB FROM EMP WHERE DEPTNO = 10;
SELECT * FROM EMP
WHERE JOB IN(SELECT JOB FROM EMP WHERE DEPTNO = 10)
AND DEPTNO = 20;
--查询在雇员中那些是经理人
--查询经理人的编号
--查询每个部门平均薪水的等级
--小结:
--[1]多表连接查询 --92标准
--等值连接查询 -->两个表之间,存在主外键关系
--非等值连接 -->两个表之间没有直接关系 >= ,<= .....
--以上两个查询,参与查询的表是平级关系
--左外连接,右外连接 --参与查询的两个表之间有主次之分
--[2]多表连接查询--99标准
--交叉连接 croos join -->笛卡尔积
--自然连接 natural join
-->相当于等值连接(前提是两个参与表中有同名列,要求同名列类型完全一致)
--using连接
-->参与查询的两个表中多个同名列,使用using制定使用特定的列进行连接
--inner join ....on...where --等值连接
--左外链接 left join
--右外连接 right join
--全连接 full join
--[3] 单行子查询
--子查询的结果是单行单列
--使用>=,<=,=,<,>,<> 进行where后的条件判断
--[4]多行子查询
--子查询的结果是多行单列的
--使用 any :任意一个 , all(全部), in(等值)