Java——数据库（Oracle）： SELECT

1、查询语句

   select *|数据,数据... from 数据源

select * from emp;
select * from dept;

（1）主外键约束关系:
       主表|父表 : 具有主键字段
       从表|子表 : 添加一个外键字段
          外键字段关联主表的主键字段
          外键字段中能够出现的值,必须在主表的主键字段中已有的值

DML  DQL数据查询语言
select *(全部列) from 数据源

select empno,ename,sal from emp;  --查询emp表中所有数据的指定列empno,ename,sal

SELECT EMPNO FROM EMP; --命令不区分大小写,字符串内容区分大小写

伪列 (整数,表达式,字符串): 表中不存在的字段,但是可查询的内容

--整数
select empno,ename,sal,1 from emp;
--表达式
select empno,ename,sal,sal+100,123*456 from emp;
--字符串 '字符'
select '哈哈' from emp;
select empno,ename,deptno,'娃哈哈公司' from emp;

--字符串连接符 ||
select 'yjxxt-'||ename from emp;

     字段别名: 为查询的结果集中的列起别名(名字默认为字段名或者伪列的值)
     select 数据 (as) 别名,数据 别名,数据 别名... from 数据源

select ename as 员工姓名, sal 员工薪资, 202 "教室    编号" from emp;
--当别名名字内容比较特别,存在特殊符号,空格,小写名称...  可以在名字的前后添加一对""--->""中的内容原封不动显示
select ename "name" from emp;

--表的别名: select 数据 from 表名 别名  
--表的别名不能添加as
select e.ename,e.deptno from emp e;


--查询公司所有的部门编号
select deptno from dept;

--查询有员工存在的部门编号
select deptno from emp;

--去重: distinct 对结果集中完全相同的数据进行去重
select distinct deptno from emp;

--查询语句中查询数据的位置: *与字段名不能同时存在
select e.*,e.ename,e.sal from emp e;

--虚表 dual
select distinct 123*456 from emp;
select sysdate from dual;

--null 一个字段没有赋值默认null
--为员工奖金涨薪100
--null与数字运算结果为null
select empno,ename,comm,comm+100 from emp;
--null值与字符串拼接,结果字符串
select empno,ename,comm,comm||100 from emp;

--nvl(值1,值2) 当值1为null,结果为值2,当 值1不为null,结果为值1
select empno,ename,comm,nvl(comm,0)+100 from emp;

2、查询语句:

       select 数据 from 数据源  查询一张表中的所有数据的指定字段值
       条件查询: select 数据字段 from 数据源 where 行过滤条件    --> 满足行过滤条件where的数据被保留在结果集中,只保留指定字段的值select
       执行过程: from -->where-->select

a)、= 、 >、 <、 >=、 <=、 !=、 <>、 between and
b)、and 、or、 not 
c)、集合函数 union、  union all、    intersect 、minus
c)、null  :is null、  is not null、  --not is null
d)、like :模糊查询 % _ escape('单个字符')

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

-- 查询工资大于1000的员工的姓名 工作岗位  工资   所属部门编号
--查询的数据 : ename, job,sal,deptno  
--数据的来源 : 员工表 emp
--条件 : 工资大于1000 sal>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 empno,ename,job,sal,sal*12 from emp where sal*12>20000;
--select后的别名在where中不能使用,因为执行流程问题,先执行from->where->select
--select empno,ename,job,sal,sal*12 result from emp where result>20000;

--先查询后判断
--1)查询出所有员工的信息,包含年薪
select empno,ename,job,sal,sal*12 result from emp;
--2)对已经查询出来(1)保留的结果集)当做数据源判断年薪>20000
select * from (select empno,ename,job,sal,sal*12 result from emp) where result>20000;

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

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

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

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

-- -检索 工资 2000, 3000员工名称 岗位 工资
select * from emp where sal=1500 or sal=3000;
-- 工资在2000到3000之间的员工信息
select * from emp where sal>=2000 and sal<=3000;
--between and 相当于>= <=
select * from emp where sal between 1500 and 3000;  

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

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

select * from emp where not(job='CLERK' and deptno=20);


-- 存在奖金的员工名称
select ename,sal,comm from emp where comm is not null;
select ename,sal,comm from emp where not comm  is null;
-- 不存在奖金的员工名称
select ename,sal,comm from emp where  comm is null;

-- 查询工资大于1500 或 含有佣金的人员姓名
select * from emp where sal>1500 or comm is not null;

--集合函数 union(去重)、 union all、	intersect 、minus
select * from emp where sal>1500
union
select * from emp where comm is not null;

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

--like :模糊查询 % _ escape('单个字符')
-- % 任意个任意字符
-- _ 一个任意字符
-- 查询员工姓名中包含字符A的员工信息
select * from emp where ename like '%A%';
-- 查询员工姓名中第二个字母为A的员工信息
select * from emp where ename like '_A%';


-- 查询名字 中包含%的员工信息
--插入2条数据,员工名称中带有%
insert into emp(empno,ename,sal) values(1000,'t_%test',8989); 
insert into emp(empno,ename,sal) values(1200,'t_tes%t',8000);
--在对一张表中的数据做增删改操作的时候会默认开启事务,commit提交  rollback回滚

select * from emp where ename like '%S%%' escape('S');

--删除表中的数据
delete from emp where ename like '%S%%' escape('S');

-- 查询工资为 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);

-- 部门名称为 SALES 或 ACCOUNTING 的雇员信息
--1)查询到部门名称为 SALES 或 ACCOUNTING的部门编号
select deptno from dept where dname='SALES' or dname='ACCOUNTING';
select deptno from dept where dname in('SALES','ACCOUNTING');
--2)在以上两个部门编号的员工信息
select * from emp where deptno in(10,30);
--子查询 : 查询语句嵌套查询语句
select *
  from emp
 where deptno in
       (select deptno from dept where dname in ('SALES', 'ACCOUNTING'));

-- 查询工资等级为 2的员工信息
--查询2等级的最低薪
select losal from salgrade where grade = 2;
--查询2等级的最高薪
select hisal from salgrade where grade = 2;

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

--查询1500薪资的等级
select grade from salgrade where 1500 between losal and hisal;

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


-- 查询工资比SMITH高的与SMITH同一部门的员工信息
--SMITH的工资
select sal from emp where ename='SMITH';
--SMITH的部门编号
select deptno from emp where ename='SMITH';

select * from emp where sal>(SMITH的薪资) and deptno = (SMITH的部门编号);
select * from emp where sal>(select sal from emp where ename='SMITH') and deptno = (select deptno from emp where ename='SMITH');

3，exists(结果集) 存在即合理,存在即合法 

       select 数据 from 数据源 where exists(结果集) 
       从from的数据源中拿出一条数据,执行where后的判断,判断是否满足条件,看到exists,就观察后面()中的结果集中是否存在数据,存在数据当前数据就满足条件,被保留,不存在不合法。

select * from emp where exists(select deptno from dept);

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

--销售部的员工信息
select * from emp where exists(select deptno from dept where dname in('SALES') and dept.deptno = emp.deptno);
--别名
select * from emp e where exists(select deptno from dept d where dname in('SALES') and d.deptno = e.deptno);
--'SALES','ACCOUNTING'员工信息
select * from emp e where exists(select * from dept d where dname in('SALES','ACCOUNTING') and d.deptno = e.deptno);

--查询出所有信息
select * from emp e where exists(select * from dept d where dname in('SALES','ACCOUNTING') and d.deptno != e.deptno);

--除了 'SALES','ACCOUNTING'员工信息
select * from emp e where not exists(select * from dept d where dname in('SALES','ACCOUNTING') and d.deptno = e.deptno);

    （2）select 数据 from 数据源 where 行过滤条件 order by 排序字段1 desc|asc,排序字段2..;
     排序    order by 排序字段  默认升序排序

select * from emp where deptno in(10,30) order by sal,empno desc;

--根据奖金做升序排序 nulls first|last
select * from emp order by comm nulls last;

--获取所有行的记录
select * from emp;
select * from emp where  1=1  ;
select * from emp where ename like '%';

--查询所有字段的值,推荐字段1,字段2,字段3... 优于 *
select * from dept;
select deptno,dname,loc from dept;

4、函数

        单行函数 : 一行记录返回一个结果
        多行函数|组函数|聚合函数 : 多条记录返回一个结果  

--单行函数
-- 当前时间
select distinct sysdate from emp;
select sysdate from dual;
select current_date from dual;

-- 2天以后日期
-- 日期可以进行+-
select sysdate,sysdate+2 from dual;

-- 所有员工入职的3天后是几号
select empno,ename,sal,hiredate,hiredate-3 from emp;

-- 查询所有员工的试用期期到期（转正的日期） 3个月试用期
select empno,ename,sal,hiredate,hiredate+90 from emp;
--add_months(日期,月数)
select empno,ename,sal,hiredate,add_months(hiredate,3) from emp;

-- 查询所有员工到目前为止一共工作了几个月
select empno,ename,hiredate,months_between(sysdate,hiredate) from emp;

-- 查询当前月的最后一天
select last_day(sysdate) from emp;

-- 下一个星期三是几号
select next_day(sysdate,'星期四') from dual;

-- 日期对象 与 字符串之间转换问题
--to_char(日期对象,'模板')
select to_char(sysdate,'yyyy"年"mm"月"dd"日"') from dual;
--to_date(日期字符串,'模板')
select to_date('2021-7-29 15:37:47','yyyy-mm-dd hh24:mi:ss') from dual;

5、聚合函数|组函数|多行函数

    （1）count(*|字段|伪列)  sum()  max()   min()  avg()
    （2）注意:如果select后面出现组函数 的使用,只能和组函数或者分组字段一起使用

-- 统计一下一共有多少个员工
select count(*) from emp;
select count(empno) from emp;
select count(deptno) from emp;
select count(1) from emp;

-- 统计一共有几个部门 
select count(deptno) from dept;

-- 统计有员工存在的部门总数
select count(distinct deptno) from emp;

-- 统计20部门一共有多少人
select count(1) from emp where deptno  = 20;

-- 计算本公司每个月一共要在工资上花费多少钱
select sum(sal) from emp;

-- 计算20部门每个月的工资花销
select sum(sal) from emp where deptno = 20;

-- 查询本公司的最高工资和最低工资
select max(sal),min(sal) from emp;

--查看30部门的最高工资和最低工资
select max(sal),min(sal) from emp where deptno = 30;

-- 请查询出 20部门的平均工资， 部门编号
select avg(sal),deptno from emp where deptno = 20 group by deptno;

-- 计算出所有员工的奖金总和
select sum(comm) from emp;
--null值不参与组函数的计算

-- 统计有奖金的员工有几个
select count(1) from emp where  comm is not null;
select count(comm) from emp;

-- 查询 最高薪水的员工姓名,及薪水
select max(sal) from emp;

select ename,sal from emp where sal = (最高薪资);
select ename,sal from emp where sal = (select max(sal) from emp);

-- 查询工资低于平均工资的员工编号，姓名及工资
select empno,ename,sal from emp where sal <(select avg(sal) from emp);

6、分组

group by 分组字段1,分组字段2
（1）查询: select 数据 from 数据源 where 行过滤条件 group by 分组字段 having 组过滤信息 order by 排序字段;
（2）流程: from-->where --> group by --> having --> select --> order by 

（3）注意 : 
         ①一旦分组,分组之后的行为操作都是以组为单位,只能看到有多少组,每组的标签(分组字段的值),但是看不到组中的数据
         ②一旦分组,select后面只能查询分组字段或者组函数
         ③where中不能使用组函数
 

-- 查出比本部门平均工资高的员工信息
select * from emp where sal>(本部门平均工资);

select * from emp e1 where sal>(select avg(sal) from emp e2 where e2.deptno = e1.deptno);

select avg(sal) from emp where deptno = 20;

-- 找出每个部门的最高工资 
select deptno,max(sal),min(sal),sum(sal),avg(sal),count(1) from emp group by deptno;


-- 找出20部门和30部门的每个部门最高工资 
--1)先过滤后分组
select max(sal),deptno from emp where deptno in(20,30) group by deptno order by max(sal) desc;

--2)先分组后过滤
select max(sal),deptno from emp group by deptno having deptno in(20,30);

-- 求出每个部门员工工资高于1000的的平均工资
select avg(sal),deptno from emp where sal>1000 group by deptno;

-- 求出10和20部门部门的哪些工资高于1000的员工的平均工资
select avg(sal),deptno from emp where sal>1000 and deptno in(10,20) group by deptno;
select avg(sal),deptno from emp where sal>1000  group by deptno having deptno in(10,20);

-- 求出平均工资高于2000的部门编号和平均工资
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

--查询 最低平均工资的部门编号
select deptno from emp group by deptno having avg(sal) = (最低平均工资);
--1)
select deptno
  from emp
 group by deptno
having avg(sal) = (select min(avg(sal)) from emp group by deptno);

--2)
select *
  from (select deptno, avg(sal) avg_sal from emp group by deptno)
 where avg_sal = (select min(avg(sal)) from emp group by deptno);

--最低平局工资
select min(avg(sal)) from emp group by deptno;

7、连表查询

    （1）当要查询的数据来自与不同的表|数据源,就可以使用连表查询，语法: 92  99

①92

笛卡尔积 : 对乘
select 数据 from 数据源1,数据源2...;

（2）注意:
     ①查询同名字段需要指明出处②查询所有员工信息,这个员工所在的部门信息
     ③查询的数据: 员工信息,部门信息
     ④数据的来源: emp dept，select * from emp,dept;
 

select * from emp,dept;

--表连接条件
--等值连接
select e.*,d.* from emp e,dept d where e.deptno=d.deptno;
select e.empno,d.dname,e.deptno from emp e,dept d where e.deptno=d.deptno;

--查询10,30部门的员工 信息以及所在部门信息
select e.*,d.* from emp e,dept d where e.deptno=d.deptno and e.deptno in(10,30) order by sal;

--等值连接条件的字段一般为主外键关联关系或者同名字段,但是主要类型相同就可
select * from emp e,dept d where e.ename=d.dname;

--非等值连接

--查询员工的信息以及工资等级信息
select grade from salgrade where 1500 between losal and hisal;

select * from emp e,salgrade s where e.sal between s.losal and s.hisal;

（3）自连接
    一张表作为2个身份,自己与自己连接

--查询由上级存在的员工信息以及上级经理人信息
--数据: 员工信息,经理人信息
--来源: 员工表emp e1, 经理人表 emp e2
select * from emp e1,emp e2 where e1.mgr=e2.empno;

（4）内连接: 满足连接条件才保留,不满足连接条件不保留

           ①所有员工的员工信息以及上级经理人信息
           ②需求: 员工表中数据无论是否满足连接条件都要显示

（5）外连接 : 主表中的数据无论是否满足连接条件都显示
         ①左外连接: 左连接    表连接的位置,主表 在左边
         ②右外连接: 右连接    表连接的位置,主表 在右边

--员工表作为主表
--在连接条件的位置,主表的对面添加(+)
select * from emp e1,emp e2 where e1.mgr=e2.empno(+); -->  左连接
select * from emp e2,emp e1 where e1.mgr=e2.empno(+); -->  右连接