第二十三天笔记

PLSQL查询用法；

基础查询：

-- 查询一张表中的所有数据的所有字段  select * from 数据源;
-- 查询语法 :  select 查询的数据(*|字段名|字段1,字段 2...|伪列) from 数据源 别名;
--* 一条数据的所有字段值
--执行流程: where->from --> select 

--注释
/*
  多行注释
*/
-- 查询一张表中的所有数据的所有字段  select * from 数据源;
-- 查询语法 :  select 查询的数据(*|字段名|字段1,字段 2...|伪列) from 数据源 别名;
--* 一条数据的所有字段值
--执行流程: from --> select 

select * from dept;
SELECT * FROM EMP;

--1)
--查询|检索|获取 所有员工的所有信息
--查询的数据 : 员工的所有信息 *
--数据的来源 : 员工表 emp
--条件 :  无 
select * from emp;

--2)
--查询所有的员工名字
--查询的数据 : 员工的姓名 ename
--数据的来源 : 员工表 emp
--条件 :  无 
SELECT ename FROM EMP;

--3)
--查询所有的员工编号和员工名称,员工上级的编号
select empno, ename, mgr from emp;

--4)
--查询所有的部门编号
select deptno from dept;

--查询所有员工所在的部门编号
--查询的数据 : 部门编号 deptno
--数据的来源 : emp,dept
--条件 :  无 

--去重 distinct : 对结果集中完全相同的多条数据只显示一条
select distinct deptno from emp;

--5)
--查询出所有有员工存在的部门编号,每个部门编号只出现一次
select distinct deptno from emp;

--6)
--查询出所有存在员工的部门的部门编号,以及员工名称
select distinct ename, deptno from emp;

--7)
--伪列 : 表达式,整数,字符串
--查询表达式 2*3
select distinct 123*456 from emp;
select ename,sal,sal*12 from emp;

--8)
--取别名 
-- select 数据 (as) 别名,数据 别名.. from 数据源 别名;
--""  中的内容原封不动显示 
--如果别名为英文,默认大写,如果想要原封不动显示,前后添加一对""
select ename 员工姓名,sal "empSal",sal*12 as "年 薪" from emp e;
select e.* from emp e;

--9)
--字符串 ''
--作为伪列存在时,默认字段名字段值都为当前字符串
select deptno,dname,'xixihaha' "xixihaha" from dept;
select deptno,dname,'yjxxt' 公司名称 from dept;

--10)
--字符串拼接 ||
select 'xixi'||'haha' from emp;
select 'YJX-'||ename 员工姓名 from emp;

--11)
--伪列 ： 数据源中不存在的字段，可以在select后面查询，假设存在
--伪列 : 表达式,整数,字符串

--12)
--虚表 ： dual
select 789*654  from dual;
select distinct 789*654 from emp;

--13)
--给每一个员工在原来的基础上+1块钱奖金
--null值与数字运算，结果为null
select ename,sal,comm,comm+1 from emp;
--null值与字符串拼接，结果为原字符串
select ename,sal,comm,comm||'1' from emp;

--处理null值 : nvl(值1,值2) 当值1为null,nvl函数的结果为值2,当值1不为null,nvl函数的结果为值1
select ename,sal,comm,nvl(comm,0)+1 from emp;

--13)
--一节结尾小练习
--查询所有员工的名字, 工种, 年薪(不带奖金)
select ename,job,sal*12 from emp;

--查询所有员工的名字,工种,年薪(带12月奖金的)
select ename,job,(sal+nvl(comm,0))*12 from emp;

--查询所有员工的名字, 工种, 年薪(带一次奖金的)
select ename,job,sal*12+nvl(comm,0) from emp;
 

-- 条件查询 : select 要查询的数据 from 数据源 where 行过滤条件 order by 排序字段1,排序字段2...；
-- 执行流程 ：  from --> where --> select ->  order  by
-- 条件判断运算符 ： = < >  <=  >= !=  <>
-- 条件连接符 ： and  or  not 
-- 区间判断 ： 条件1 and 条件2 |  between 值1 and 值2(相当于>=与<=)

-- 条件查询 : select 要查询的数据 from 数据源 where 行过滤条件 order by 排序字段1,排序字段2...；
-- 执行流程 ：  from --> where --> select ->  order  by
-- 条件判断运算符 ： = < >  <=  >= !=  <>
-- 条件连接符 ： and  or  not 
-- 区间判断 ： 条件1 and 条件2 |  between 值1 and 值2(相当于>=与<=)

-- 查询20部门的员工信息
-- 数据 ： 员工信息 *
-- 来源 : 员工表 emp
-- 条件 ： deptno = 20
select * from emp where deptno = 20;

-- 查询工资大于1000的员工的姓名，工作岗位，工资，所属部门编号
select ename, job, sal, deptno from emp where sal>1000;

-- 查询不在20部门工作的员工信息
select * from emp where deptno != 20;
select * from emp where deptno <> 20;
select * from emp where not deptno = 20;

--先过滤后查询
-- 查询员工的年薪大于20000的 员工名称、岗位 年薪
select ename,job,sal*12 from emp  where sal*12 > 20000;
-- 注意： 在where不能使用字段别名，因为执行流程问题
--select ename,job,sal*12 n from emp  where n > 20000; --报错

--先查询后过滤
select ename,job,sal*12 r from emp;
--数据源可以为表也可以为结果集
select * from (select ename,job,sal*12 r from emp) where r>20000;

-- 查询  any(任意一个)   some(任意一个)  all(所有)

-- 查询工资比我们三个人都高的那些员工的信息 900,1000,1100
select * from emp where sal> all(900,1000,1100);

-- 查询工资比我们三个人都低的那些员工的信息
select * from emp where sal< all(900,1000,1100);

-- 查询比我们中随便一个工资高的员工信息
select * from emp where sal> some(900,1000,1100);

-- 查询 工种为’SALESMAN’的员工信息 （注意 内容区分大小写）
select * from emp where job = 'SALESMAN';

-- 检索 工资 2000, 3000员工名称 岗位 工资
select ename,sal from emp where sal=2000 or sal=3000;

-- 工资在2000到3000之间的员工信息
select * from emp where sal>=2000 and sal<=3000;
select * from emp where sal between 2000 and 3000;

---查询 岗位 为 CLERK 且部门编号为 20的员工名称 部门编号，工资
select * from emp where job='CLERK' and deptno=20;

-- 查询 岗位 为 CLERK 或部门编号为 20的员工名称 部门编号，工资
select * from emp where job='CLERK' or deptno=20;

--查询 岗位 不是 CLERK 员工名称 部门编号，工资
select * from emp where job!='CLERK';
select * from emp where job<>'CLERK';
select * from emp where not job='CLERK';

-- 查询 岗位 不为 CLERK 并且部门编号不为 20的员工名称 部门编号，工资
select * from emp where job!='CLERK' and deptno!=20;
select * from emp where not (job='CLERK' or deptno=20);

--存在佣奖金的员工名称
-- 判断null值需要使用is
select * from emp where comm is not null;
select * from emp where not comm is null;

--不存在奖金的员工名称
select * from emp where comm is null;

--集合函数 :  对结果集进一步操作
--union并集去重、  union all并集不去重、    intersect交集 、minus差集
--查询工资大于1500 或 含有佣金的人员姓名
select * from emp where sal>1500 or comm is not null;
select * from emp where sal>1500
union all
select * from emp where comm is not null;

--查询显示不存在雇员的所有部门号。
select deptno  from dept
minus
select distinct deptno from emp;

--like 模糊匹配查询
-- 需要配合 %任意个任意字符   _一个任意字符 
--查询员工姓名中包含字符A的员工信息
select * from emp where ename like 'A';--定值查询,比使用=效率低
select * from emp where ename like '%A%';

--查询员工姓名中第二个字母为A的员工信息
select * from emp where ename like '_A%';

-- 测试数据
insert into emp(empno,ename,sal) values(1000,'t_%test',8989);
insert into emp(empno,ename,sal) values(1200,'t_tes%t',8000);

--查询员工姓名中包含字符%的员工信息
select * from emp where ename like '%A%%' escape('A');

-- 查询工资为1500， 2000， 2500， 5000的员工的信息
SELECT * FROM EMP WHERE SAL=1500 OR SAL=2000 OR SAL=2500 OR SAL=5000;
--in (值列表) 判断值是否在in后面值列表中，如果在就满足条件，不在不满足
SELECT * FROM EMP WHERE SAL IN (1500, 2000, 2500, 5000);

--排序 order by 排序字段1 desc降序|asc升序默认,排序字段2...
--对结果集中的数据做排序
-- 查询30部门的员工信息， 并且按工资降序排序
select * from emp where deptno = 30 order by sal desc;

-- 查询30部门的员工信息， 并且按工资降序排序如果工资相同，则按照员工编号降序排序
select * from emp where deptno = 30 order by sal desc, empno desc;

-- 查询所有员工信息， 按照奖金升序排序  null
--nulls first 所有的null值在最前,nulls last所有null值在最后
select *  from emp order by comm asc nulls first;
 

--exists (结果集)  存在即保留,存在即合理
-- 从 from后数据源中拿出每一条数据,判断是否满足where后的条件,如果是exists就判断exists()中的结果集中是否存在数据,存在当前判断的这条数据就满足跳进,不存在就过滤
--执行流程 : from -->  where --> select --> order by

--exists (结果集)  存在即保留,存在即合理
-- 从 from后数据源中拿出每一条数据,判断是否满足where后的条件,如果是exists就判断exists()中的结果集中是否存在数据,存在当前判断的这条数据就满足跳进,不存在就过滤
--执行流程 : from -->  where --> select --> order by

-- 查询所有员工数据
select * from emp where exists (select * from dept);
-- 查询所有员工数据
select * from emp where exists (select * from emp where comm is not null);
-- 查询10,30部门 的员工信息
select * from emp where exists (select * from dept where deptno in (10,30) and dept.deptno = emp.deptno);
-- 别名
select *
  from emp e
 where exists (select *
          from dept d
         where deptno in (10, 30)
           and d.deptno = e.deptno);
-- 查询'SALES', 'ACCOUNTING'部门 的员工信息
select *
  from emp e
 where exists (select deptno, dname
          from dept d
         where dname in ('SALES', 'ACCOUNTING')
           and e.deptno = d.deptno);
-- 查询所有的员工信息       
select *
  from emp e
 where exists (select deptno, dname
          from dept d
         where dname in ('SALES', 'ACCOUNTING')
           and e.deptno != d.deptno);

--与有奖金的员工同一部门的员工信息
select empno, ename, sal,deptno
  from emp e1
 where exists (select empno, ename, sal, comm,deptno
          from emp e2
         where comm is not null
           and e1.deptno = e2.deptno);
 

 

--子查询 : 查询语句嵌套查询语句
--当条件与要查询的数据在不同的数据源,而两个数据源之间存在一定的关联方式,可以子查询中转查询

--查询所有行记录
select * from emp;
select * from emp where 1=1;

--子查询 : 查询语句嵌套查询语句
--当条件与要查询的数据在不同的数据源,而两个数据源之间存在一定的关联方式,可以子查询中转查询

--部门名称为 SALES 或 ACCOUNTING 的雇员信息
--数据 : 员工信息 *
--来源 : 员工表 emp
--条件 : dname in ('SALES','ACCOUNTING')
--查询 SALES 或 ACCOUNTING的部门编号
select deptno from dept where dname in ('SALES','ACCOUNTING');
--在10,30部门的员工信息
select * from emp where deptno in (10,30) ;

select *
  from emp
 where deptno in
       (select deptno from dept where dname in ('SALES', 'ACCOUNTING'));

-- 查询工资等级为 2的员工信息
--数据 : 员工信息 *
--来源 : 员工表 emp
--条件 : 工资等级为 2
--查询2等级的最低薪与最高薪
select losal from salgrade where grade = 2;
select hisal from salgrade where grade = 2;
--判断员工薪资在这个范围内的员工信息
select * from emp where sal between 1201 and 1400;

select *
  from emp
 where sal between (select losal from salgrade where grade = 2) and
       (select hisal from salgrade where grade = 2);

-- 查询 销售部（SALES） 中 工资大于1500的员工信息
select *
  from emp
 where deptno = (select deptno from dept where dname = 'SALES')
   and sal > 1500;

-- 查询工资比SMITH高的同一部门的员工信息
select sal from emp where ename='SMITH';
select deptno from emp where ename='SMITH';
select *
  from emp
 where sal > (select sal from emp where ename = 'SMITH')
   and deptno = (select deptno from emp where ename = 'SMITH');