PLSQL数据库select语句Day01

https://www.yuque.com/docs/share/fe42dbdb-7d33-4ea9-9021-48bfcb67411b?# 

一 SQL语句

1.1、基础语句

--注释

/*

多行注释

*/

-- 查询一张表中的所有数据的所有字段 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;
--select 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 from emp where comm is null;

1.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;

1.3、exists存在

--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);

1.4、子查询

--查询所有行记录

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');

1.5、单行函数

-- 当前时间

select sysdate from dual;
select current_date from dual;

-- 日期可以直接+-

-- 2天以后是几号

select sysdate+2 from dual;

-- 所有员工入职的3天后是几号

select ename,hiredate,hiredate+3 from emp;

-- 查询所有员工的试用期期到期（转正的日期） 3个月试用期

select ename,hiredate,hiredate+90 from emp;
select ename,hiredate,add_months(hiredate,-3) from emp;

-- 查询所有员工到目前为止一共工作了几个月

select ename,months_between(sysdate,hiredate) from emp;
-- trunc 取整
select ename,trunc(months_between(sysdate,hiredate)) from emp;

-- 查询当前月的最后一天

select ename,last_day(sysdate) from emp;

-- 下一个星期三是几号

select next_day(sysdate,'星期四') from dual;

--日期对象与日期字符串之间相互转换 ****

--to_char
select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
select sysdate,to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24:mi:ss') from dual;
--to_date
select sysdate,to_date('2022年02月23日','yyyy"年"mm"月"dd"日"') from dual;

--nvl(值1,值2) 对null值判断

--判定函数 decode(判定字段,值1,结果1,值2,结果2....,默认值) 对判定字段的值进行判定,如果值为值1,函数的结果为结果1,与值2相等,函数的结果为结果2...最终如果以上都不相等,最终取默认值

--给每个部门后后面添加一个伪列,如果10部门,伪列显示为十,二十,三十...

select deptno,dname,loc,decode(deptno,10,'十',20,'二十',30,'三十','无') from  dept;

--case when then else end

select deptno,dname,loc,(case deptno when 10 then '十' when 20 then '二十' else '无' end) from  dept;

-- 查询82年入职员工的信息

select * from emp where hiredate between to_date('1982-1-1','yyyy-mm-dd') and to_date('1982-12-31','yyyy-mm-dd');
select * from emp where to_char(hiredate,'yyyy') = '1982';

-- 给20部门的所有员工都涨薪10%，显示出员工的名称， 原来的薪水， 所属部门编号， 涨薪后的薪水

select ename,sal,deptno,decode(deptno,20,sal*1.1,sal) 涨薪后 from emp;

-- 10部门涨薪10%， 20涨薪20%，30降薪1% ， 40部门翻倍3倍

select ename,sal,deptno,decode(deptno,10,sal*1.1,20,sal*1.2,30,sal*0.99,40,sal*3) 涨薪后 from emp;