**JAVA学习笔记Day15（数据库一）

常用操作指令

--sql中的注释
--DQL 查询语句
--select *|字段名1,字段名2... from 表名;

--查询某个表中的所有数据
--select * from 表名
select * from emp;

--查询所有员工的姓名
--select 字段名 from 表名;
select ename from emp;

--查询所有员工的姓名,员工编号,上级编号
--查询的数据: ename,empno,mgr
--数据来源: emp雇员表
--查询的条件:
select ename, empno, mgr from emp;

--查询所有员工的部门编号
--查询的数据: deptno
--来源: dept|emp
select deptno from dept;
select deptno from emp;

--所有有员工存在的部门的部门编号
--查询的数据: deptno
--来源: emp
--去重  distinct
select distinct deptno from emp;

select distinct ename,deptno from emp;

--表达式  结果字段的名字为表达式的名字   值为表达式计算的结果-->计算器
select 1+1 from emp;
select distinct 123*456 from emp;

--别名  select 字段1 as 别名,字段2 as 别名, 字段3 别名.. from 表名 别名;
--别名如果是英文默认大写,如果想要原样使用|中文带空格,在别名的前后""
select distinct 123+456 as "get Sum" from dept;
select distinct 123+456  "求 和" from dept d;

--''代表字符串  ""原样输出 结果字段名和值原样字符串输出
select '哈哈' "123" from emp;

--表达式|字符串都是伪列:不是表中真是存在的字段

--字符串的拼接  ||
select 'a-'||'-b' from dual;
--查询所有员工的姓名添加同一前缀 尚学堂
select '尚学堂'||ename "学生姓名" from emp;

--虚表: oracle中不是真是存在的表,其中没有任何数据  dual
select sysdate from dual;
--null
--查询所有员工的员工姓名和奖金
select ename,comm from emp;
--null与数字进行运算,结果为null
--在所有员工原奖金基础上+1
select ename, comm, comm+1 from emp;
--null与字符串运算结果不会改变原串
select ename, comm, comm||'1' from emp;
--处理null值   nvl(字段,值2) 如果字段值1位null,给你值2,如果值1不为null,给你值1
select ename 姓名, comm 奖金, nvl(comm,0)+1 "+1后的奖金" from emp;

--一节结尾小练习
--查询所有员工的名字, 工种, 年薪(不带奖金)
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 *|字段1,字段2.. from 表名 where 行过滤条件;
--求20部门所有员工信息
select * from emp where deptno = 20;

--求30部门的所有员工的员工名称,和部门编号
select ename, deptno from emp where deptno =30;

--执行顺序  from---where---select

-- = 、 >、 <、 >=、 <=、 !=、 <>、
--所有员工中薪资>800
select * from emp where sal>800; 
--SMITH查询斯密斯的信息
select * from emp where ename='SMITH';

--求除了SMITH以外的所有员工信息
select * from emp where ename != 'SMITH';
select * from emp where ename <> 'SMITH';
select * from emp where not ename = 'SMITH';

--and 、or、 not , between and
--求薪资在1500~2500之间的员工信息
select * from emp where sal >=1500 and sal<=2500;
select * from emp where sal  between 1500 and 2500; --是>=<=

--求薪资>1500并且是30部门的员工名称,部门标号,薪资
select ename,deptno,sal from emp where sal>=1500 and deptno=30;
--求薪资>1500或者30部门的员工名称,部门标号,薪资
select ename,deptno,sal from emp where sal>=1500 or deptno=30;

--求除了薪资>1500和30部门的员工名称,部门标号,薪资
select ename,deptno,sal from emp where not sal>=1500 and not deptno=30;

--select ename,deptno,sal from emp where not(sal>=1500 and deptno=30);
select ename,deptno,sal from emp where not(sal>=1500 or deptno=30);

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

--求奖金为null的人
select * from emp where comm is null;

--求有奖金的人
select * from emp where comm is not null;
select * from emp where not comm is null;
--select * from emp where comm not is null;  错误事例

--集合操作
--求薪资>1500或者30部门的员工名称,部门标号,薪资
select * from emp where sal>=1500
Union
select * from emp where deptno=30;

-- Union All，全集(不去重)
select * from emp where sal>=1500
Union All
select * from emp where deptno=30;

--求没有员工在的部门标号
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 'JONES';

--姓名中第二个字符为A的所有员工姓名
select ename from emp where ename like '_A%';
--姓名以H结尾的所有员工信息
select ename from emp where ename like '%H';
--测试数据
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%';
select * from emp where ename like '%C%%' escape('C'); --任意指定转义字符,需要使用escape() 中说明

--事务
commit;
rollback;

--in 匹配后面结果集中的任意一个数据,无法做区间判断
--查询薪资为 1500 2000 2500 5000的员工信息
select * from emp where sal=1500 or sal = 2000 or sal=2500 or sal=5000;
select * from emp where sal in (1500,2000,2500,5000);
--查找 KING 和 SMITH 两个员工的信息
select * from emp where ename = 'KING' or ename = 'SMITH';
select * from emp where ename in ('KING','SMITH');

--查询 在SALES he  ACCOUNTING 的所有员工信息
--查询的数据:*
--来源:emp
--条件: 1)查询SALES he  ACCOUNTING的部门编号  2)根据上一步查询的部门标号在emp中查询满足条件的数据
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
select losal from salgrade where grade =2;  --1201
select hisal from salgrade where grade =2;  --1400

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

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

--查询工资比SMITH高的同一部门的员工信息
--查询的数据:*
--来源:emp
--条件: deptno是 'SMITH'的部门   sal>'SMITH'的薪资
select *
  from emp
 where deptno in (select deptno from emp where ename = 'SMITH')
   and sal > (select sal from emp where ename = 'SMITH');
   
select ename,deptno,sal from (select *
  from emp
 where deptno in (select deptno from emp where ename = 'SMITH')
   and sal > (select sal from emp where ename = 'SMITH'));
   
--exists 存在即保留  存在即合法
select * from emp where exists (select * from dept where dname='SALES');
select *
  from emp
 where exists (select deptno, dname
          from dept
         where dname in ('SALES', 'ACCOUNTING'));

--销售部和财务部所有员工信息 
select *
  from emp 
 where exists (select deptno, dname
          from dept 
         where dname in ('SALES', 'ACCOUNTING')
           and emp.deptno = dept.deptno);
           
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 *
  from emp e
 where not exists (select deptno, dname
          from dept d
         where dname in ('SALES', 'ACCOUNTING')
           and e.deptno = d.deptno);
           
--获取所有行记录
select * from emp;
select * from emp where 1=1;   


--排序  order by  desc 降序  asc升序
--select * from 表名 where 行过滤条件 order by 排序字段;  --默认升序
--from--where--select--order by
select * from emp order by sal asc;   

--根据薪资进行升序排序,薪资相同的根据员工编号进行降序排序
select * from emp order by sal asc,empno desc;

--根据员工的奖金进行降序排序       nulls first    nulls last  
select * from emp where deptno=30 order by comm desc nulls first;

   

--null
--查询所有员工的员工姓名和奖金
select ename,comm from emp;
--null与数字进行运算,结果为null
--在所有员工原奖金基础上+1
select ename, comm, comm+1 from emp;
--null与字符串运算结果不会改变原串
select ename, comm, comm||'1' from emp;
--处理null值   nvl(字段,值2) 如果字段值1位null,给你值2,如果值1不为null,给你值1
select ename 姓名, comm 奖金, nvl(comm,0)+1 "+1后的奖金" from emp;

--一节结尾小练习
--查询所有员工的名字, 工种, 年薪(不带奖金)
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 *|字段1,字段2.. from 表名 where 行过滤条件;
--求20部门所有员工信息
select * from emp where deptno = 20;

--求30部门的所有员工的员工名称,和部门编号
select ename, deptno from emp where deptno =30;

--执行顺序  from---where---select

-- = 、 >、 <、 >=、 <=、 !=、 <>、
--所有员工中薪资>800
select * from emp where sal>800; 
--SMITH查询斯密斯的信息
select * from emp where ename='SMITH';

--求除了SMITH以外的所有员工信息
select * from emp where ename != 'SMITH';
select * from emp where ename <> 'SMITH';
select * from emp where not ename = 'SMITH';

--and 、or、 not , between and
--求薪资在1500~2500之间的员工信息
select * from emp where sal >=1500 and sal<=2500;
select * from emp where sal  between 1500 and 2500; --是>=<=

--求薪资>1500并且是30部门的员工名称,部门标号,薪资
select ename,deptno,sal from emp where sal>=1500 and deptno=30;
--求薪资>1500或者30部门的员工名称,部门标号,薪资
select ename,deptno,sal from emp where sal>=1500 or deptno=30;

--求除了薪资>1500和30部门的员工名称,部门标号,薪资
select ename,deptno,sal from emp where not sal>=1500 and not deptno=30;

--select ename,deptno,sal from emp where not(sal>=1500 and deptno=30);
select ename,deptno,sal from emp where not(sal>=1500 or deptno=30);

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

--求奖金为null的人
select * from emp where comm is null;

--求有奖金的人
select * from emp where comm is not null;
select * from emp where not comm is null;
--select * from emp where comm not is null;  错误事例

--集合操作
--求薪资>1500或者30部门的员工名称,部门标号,薪资
select * from emp where sal>=1500
Union
select * from emp where deptno=30;

-- Union All，全集(不去重)
select * from emp where sal>=1500
Union All
select * from emp where deptno=30;

--求没有员工在的部门标号
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 'JONES';

--姓名中第二个字符为A的所有员工姓名
select ename from emp where ename like '_A%';
--姓名以H结尾的所有员工信息
select ename from emp where ename like '%H';
--测试数据
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%';
select * from emp where ename like '%C%%' escape('C'); --任意指定转义字符,需要使用escape() 中说明

--事务
commit;
rollback;

--in 匹配后面结果集中的任意一个数据,无法做区间判断
--查询薪资为 1500 2000 2500 5000的员工信息
select * from emp where sal=1500 or sal = 2000 or sal=2500 or sal=5000;
select * from emp where sal in (1500,2000,2500,5000);
--查找 KING 和 SMITH 两个员工的信息
select * from emp where ename = 'KING' or ename = 'SMITH';
select * from emp where ename in ('KING','SMITH');

--查询 在SALES he  ACCOUNTING 的所有员工信息
--查询的数据:*
--来源:emp
--条件: 1)查询SALES he  ACCOUNTING的部门编号  2)根据上一步查询的部门标号在emp中查询满足条件的数据
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
select losal from salgrade where grade =2;  --1201
select hisal from salgrade where grade =2;  --1400

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

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

--查询工资比SMITH高的同一部门的员工信息
--查询的数据:*
--来源:emp
--条件: deptno是 'SMITH'的部门   sal>'SMITH'的薪资
select *
  from emp
 where deptno in (select deptno from emp where ename = 'SMITH')
   and sal > (select sal from emp where ename = 'SMITH');
   
select ename,deptno,sal from (select *
  from emp
 where deptno in (select deptno from emp where ename = 'SMITH')
   and sal > (select sal from emp where ename = 'SMITH'));
   
--exists 存在即保留  存在即合法
select * from emp where exists (select * from dept where dname='SALES');
select *
  from emp
 where exists (select deptno, dname
          from dept
         where dname in ('SALES', 'ACCOUNTING'));

--销售部和财务部所有员工信息 
select *
  from emp 
 where exists (select deptno, dname
          from dept 
         where dname in ('SALES', 'ACCOUNTING')
           and emp.deptno = dept.deptno);
           
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 *
  from emp e
 where not exists (select deptno, dname
          from dept d
         where dname in ('SALES', 'ACCOUNTING')
           and e.deptno = d.deptno);
           
--获取所有行记录
select * from emp;
select * from emp where 1=1;   


--排序  order by  desc 降序  asc升序
--select * from 表名 where 行过滤条件 order by 排序字段;  --默认升序
--from--where--select--order by
select * from emp order by sal asc;   

--根据薪资进行升序排序,薪资相同的根据员工编号进行降序排序
select * from emp order by sal asc,empno desc;

--根据员工的奖金进行降序排序       nulls first    nulls last  
select * from emp where deptno=30 order by comm desc nulls first;