20210915 oracle

20210915 oracle

oracle是关系数据库管理系统

目标：

主要用SQL JBDC Mybatic

连表查询

DQL 查

DML 增删改

SQL

SQL 分

• DDL 数据定义语言
• DML 数据管理语言
  • DQL 查询
  • DML 管理
• DCL 数据库控制语言

DDL： create 创建 drop 删除 alter 修改 rename 重命名 truncate 截断

DML：insert 插入 delete 删除 update 更新 select 查询

DCL：grant 授权 revoke 回收权力 commit 提交表格 rollback 回滚

**多用 记住怎么查 **

表从属用户： 当前用户查询可不写，管理员用户查询 需要加上用户名.。

默认的普通用户 SCOTT 密码TIGER

管理员 sys 密码：yjxxt

DQL 查询

结构：

--select 内容 from 数据源；--分号的作用是给工具看的  实际运行中 不需要也可以
--内容是  * 代表一条数据的所有字段值  
--如果有多个字段 中间用逗号隔开
流程分析
1查询的数据
2数据的来源
3条件
-- 单行注释
/*
这是多行注释
*/
执行流程：  -->from-->select-->结果集

distinct 作用去重 对结果集中的完全相同的数据去重，只显示相同数据中的一个 加在select 后 放在字段前

伪列：不是表结构中真的存在 但是可以查询到

可以作为伪列的有： 数字、字符串、表达式

在SQL中有单引号来表示字符串  '字符串'  双引号""  表示完全保持格式 内容不变的打印
别名：字段或者表后添加别名  用来简化这个字段和表的名字 方便后续引用 区分
字段别名： select 字段名 别名  或者  select 字段名 as 别名  as可以省略
表的别名： select 字段名 from 表名 别名；
字符串拼接用  ||  比如 100||122
null值  一条数据中某个字段没有赋值，默认的是null值
	null值与数据进行运算结果还是null
	null与字符串连接结果还是null
	null 不参加组函数的运算
处理null值的方法  nvl（条件，值2）：如果条件中的值是null，那么结果就是值2，如果条件的值不为null，结果就是条件本身的值

补充

查询当前日期sysdate和current_date 都可以显示系统当前日期

条件查询

格式

select 内容 from数据源 where 行过滤条件；

执行流程是

from–>where–> select–>结果集

逻辑分析

分析：

数据：需要显示什么数据

来源：从哪里查

条件：满足什么样的条件

常用的过滤条件

1、 条件类 < > <= >= = != <> 还有between 值 1 and 值2 相当于 值1<= 数据 <= 值2

2、逻辑类 and or not in(值1，值2，值3…) 相当于or

3、 union 并集 （去重） unionAll （不去重） minus 差集 intersect 交集

like 模糊查找

格式

select * from 数据源 where 字段 like ’%‘

% 表示任意个字符 _ 下划线表示单个任意字符

模糊查找 建议少用 因为效率低

–注意: 在where后面不能使用字段的别名,但是可以使用表的别名,因为执行流程 问题

排序

关键字是 order by 仅对结果集进行排序

流程

from --> where–>select–> order by–> 结果集

select 数据 from 数据源 where 条件 order by 排序字段1、排序字段2… desc（降序）|asc(升序 可不写 默认都是升序)

子查询

前提是当条件字段不是来自于数据源，但是可以间接的建立联系，查询语句嵌套查询语句

exists

存在即合理,存在即合法,存在即保留

where exists (sql语句); 判断 exists()中存在结果集么,如果存在,当前判断的数据就保留,()中不存在结果集,当前判断的数据不能保留

优点相比于in来说 效率高

函数

单行函数

一条记录就返回一个结果

-- 当前时间
select sysdate from dual;
select current_date from dual;
select sysdate 当前时间 from dual;

-- 2天以后是几号
select sysdate+2 from dual;
select '伪列',sysdate+30 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,hiredate,add_months(hiredate,6) 试用期结束日期 from emp;

-- 查询所有员工到目前为止一共工作了几个月
--trunc(数据) 对参数取整
--round(数据,几位) 对数据保留指定小数位数四舍五入
select ename,hiredate,trunc(months_between(sysdate,hiredate)) from emp;
select ename,hiredate,months_between(sysdate,hiredate),round(months_between(sysdate,hiredate),2) from emp;

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

-- 即将要过的下一个星期三是几号
select next_day(sysdate,'星期四') from dual;
select next_day(sysdate+5,'星期一') from dual;


-- 日期与字符串之间转换
-- to_char(date,'模板');
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24:mi:ss') from dual;
select to_char(sysdate,'yyyy"haha"mm"ahha"dd') from dual;

-- to_date(str,'模板');
select to_date('1969-10-12 12:17:16','yyyy-mm-dd hh12:mi:ss') from dual;


--判定函数
--decode(条件,值1,结果1,值2,结果2...,默认值) 根据条件进行判断,如果条件结果为值1,decode函数结果为结果1,为值2,decode为结果2..以上都不满足为默认值
--查询部门信息,以及部门编号的中文表示
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 '二十'  end) from dept;

-- 给20部门的所有员工都涨薪10%，显示出员工的名称， 原来的薪水， 所属部门编号， 涨薪后的薪水
select ename,sal,deptno,sal+(case deptno when 20 then sal*0.2 else 0 end) 涨薪后薪水 from emp;
-- 10部门涨薪10%， 20涨薪20%，30降薪1% ， 40部门翻倍3倍
select ename,sal,deptno,(case deptno when 10 then sal*1.1 when 20 then sal*1.2 when 30 then sal*0.99 else sal*3 end) 涨薪后薪水 from emp;

-- 查询82年入职的员工信息
select * from emp where hiredate between to_date('1982-1-1','yyyy-mm-dd') and to_date('1982-12-31','yyyy-mm-dd');

多行函数

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

-- 组函数 | 多行函数 | 聚合函数 : count()  sum()   avg()   max()   min()
-- 多个已经确定的数据使用组函数 (结果集|分组的每一组)
-- select后面一旦使用组函数,只能与其他组函数或者分组字段一起使用
-- null不参与组函数运算

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

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

-- 统计有员工存在的部门总数 1是伪列?去重变成1
select count(distinct deptno) from emp;
select count(distinct 1) 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) from emp where deptno = 20;


-- 计算出所有员工的奖金总和
select sum(comm) from emp;

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

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

--2)与最高薪资值相等的员工姓名
select * from emp where sal = (最高薪资);
select * from emp where sal = (select max(sal) from emp);

-- 查询工资低于整个公司平均工资的员工编号，姓名及工资
select * from emp where sal < (公司平均工资);
select * from emp where sal < (select avg(sal) from emp);

-- 课后: 查看高于本部门平均薪水员工姓名
--本部门的平均薪水
select avg(sal) 部门平均薪资,deptno from emp group by deptno;
--比本部门平均薪水高的人的姓名
select ename,sal,deptno  from emp e1 where sal>(select avg(sal) from emp e2 where e2.deptno= e1.deptno);


--查询比SMITH工资高,与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');

分组

分组:
–查询: select 数据 from 数据源 where 行过滤条件 group by 分组字段1,分组字段2… having 组过滤信息 order by 排序字段;
–执行流程: from --> where --> group by --> having -->select --> order by
–注意: 分组之后,select后面只能 出现组函数|分组字段

--每一个部门的最高工资
--以部门分组
select max(sal),deptno from emp group by deptno;

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

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

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

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

-- 求出平均工资高于2000的部门编号和平均工资
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
select *  --效率相对较低,不推荐使用
  from (select deptno, avg(sal) avg_sal from emp group by deptno)
 where avg_sal > 2000;

--按 部门岗位(job) 查询 平均工资
select avg(sal) from emp group by job;

--按 岗位查询 平均工资，且平均工资大于2000的岗位
select avg(sal),job from emp group by job having avg(sal)>2000;


--查询最低平均工资的部门编号
--最低平局你工资
select min(avg(sal)) from emp group by deptno;
--每一个部门的平均工资,以及部门编号
select deptno,avg(sal) from emp group by deptno;

--平均工资与最低平均工资相等的部门编号
select deptno,avg(sal) from emp group by deptno having avg(sal) = (最低平均工资);
select deptno, avg(sal)
  from emp
 group by deptno
having avg(sal) = (select min(avg(sal)) from emp group by deptno);

select * from  (select deptno,avg(sal) avg_sal from emp group by deptno) where avg_sal = (最低平均工资); 
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 * from emp where sal>(本部门平均工资);

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

-- 统计每个部门的员工数，和部门编号,按照员工个数升序排序
select deptno 部门编号,count(1) 员工个数 from emp group by deptno order by count(1);

-- 查询每个工种的最高工资以及工种
select job 工种,max(sal) 最高工资 from emp group by job order by max(sal);

-- 查询平均工资在1500到2000之间的部门平均工资和部门编号
select deptno 部门编号,round(avg(sal)) 部门平均工资 from emp group by deptno having avg(sal) between 1500 and 2000 order by avg(sal);
select deptno 部门编号,trunc(avg(sal)) 部门平均工资 from emp group by deptno order by avg(sal);
-- 查询工资高于20部门平均工资的员工

--20部门的平均工资
select trunc(avg(sal)) from emp where deptno=20;
select * from emp where not sal>(select trunc(avg(sal)) from emp where deptno=20);
select * from emp where  sal>(select trunc(avg(sal)) from emp where deptno=20);

行转列

/*
  id name course score

  1 张三  语文  81
  2 张三  数学  75
  3 李四  语文  81
  4 李四  数学  90
  5 王五  语文  81
  6 王五  数学  100
  7 王五  英语  90
  create table tb_student(
    id number(4) ,
    name varchar2(20),
    course varchar2(20),
    score number(5,2)
  );
  insert into tb_student values(1,'张三','语文',81);
  insert into tb_student values(2,'张三','数学',75);
  insert into tb_student values(3,'李四','语文',81);
  insert into tb_student values(4,'李四','数学',90);
  insert into tb_student values(5,'王五','语文',81);
  insert into tb_student values(6,'王五','数学',100);
  insert into tb_student values(7,'王五','英语',90);
  commit;
  drop table tb_student cascade constraints;
*/

select  * from tb_student;

--使用一条sql语句，查询每门课都大于80分的学生姓名
--数据: name
--来源: tb_student
--条件: 1)一共考试完整数目   2)每门课都大于80分(最小分数>80)
select name
  from tb_student
 group by name
having count(course) = (select count(distinct course) from tb_student) and min(score) > 80;


--查询完整的课程数目
select count(distinct course) from tb_student;

select name from tb_student group by name having count(course) = (完整课程数目) and min(score)>80;


--行转列
select name 姓名,decode(course,'语文',score)  语文 from tb_student;
select * from tb_student;
--查看几门
select count(distinct course) from tb_student;
--行专列
select course 科目,decode(name,'张三',score) 张三，decode(name,'李四',score) 李四，decode(name,'王五',score) 王五 from tb_student;

select name 姓名,decode(course,'语文',score) 语文，decode(course,'英语',score) 英语，decode(course,'数学',score) 数学 from tb_student;

select name 姓名,min(decode(course,'语文',score)) 语文，max(decode(course,'英语',score)) 英语，min(decode(course,'数学',score)) 数学 from tb_student group by name;

92语法

-- 连表查询 : 当要查询的数据来自于多张表的时候,需要使用连表查询
-- 92语法 select 数据 from 数据源1,数据源2,数据源3...where 行过滤条件|表连接条件;
-- 99语法


--笛卡尔积: 对乘
-- 查询所有员工的信息以及所在部门的信息
--数据: 员工信息  部门信息
--来源: 员工表   部门表
select * from emp,dept;

--等值连接 : 行过滤条件,表连接之后不满足要求的数据过滤
select empno,dname,e.deptno,dname from emp e,dept d where e.deptno = d.deptno;
--注意: 两张表中的连接的字段要求类型相同
--注意: 表连接存在时候,使用同名字段的时候需要指明出处
select * from emp e,dept d where e.ename = d.dname;

-- 找出30部门的员工名称及部门名称
--先连接后过滤 
select * from emp e,dept d where e.deptno=d.deptno and e.deptno = 30;
--先过滤后连接 ->推荐
--30部门的员工信息
select * from emp where deptno = 30;
--30部门的部门信息
select * from dept where deptno = 30;

select *
  from (select * from emp where deptno = 30),
       (select * from dept where deptno = 30);


--非等值 连接
-- 查询员工姓名，工资等级
select ename,grade from emp e,salgrade s where sal between losal and hisal;

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


-- 10和30部门的员工信息,部门信息以及工资等级
select *
  from emp e, dept d, salgrade s
 where e.deptno = d.deptno
   and e.sal between s.losal and s.hisal;


-- 找出有上级的员工的名称和对应的上级名称
-- 数据: 员工信息  上级信息 
-- 来源: 员工表 emp e1   上级表 emp e2
-- 条件: e1.mgr = e2.empno  因为员工的mgr字段的值记录的就是上级的empno字段

推荐先过滤后连接，同等情况下效率高

where 行过滤条件 |表连接条件

内连接：等值或非等值的，满足连接条件的展示，不满足的不会显示

外连接：一个数据源中的数据无论是否满足连接条件都会显示

• 主表：无论是否满足都显示， 区分方法是 主表对面的表加（+）
• 左外连接 左连接，主表在左边
• 右外连接 右连接，主表在右边