Oracle的sql语句3

最新推荐文章于 2020-10-15 19:55:53 发布

墨成诗泪如雨

最新推荐文章于 2020-10-15 19:55:53 发布

阅读量249

点赞数 1

分类专栏：学习阶段(3)Oracle数据库文章标签： oracle中级篇

本文链接：https://blog.csdn.net/weixin_43968510/article/details/100181341

版权

学习阶段(3)Oracle数据库专栏收录该内容

7 篇文章 0 订阅

订阅专栏

1.exists关键字存在

与之对应的: no exists 不存在

看如下案例

-- 和SMITH在同一个岗位,同一个部门的员工
select ENAME, DEPTNO, JOB
from EMP
where (DEPTNO, JOB) in (select DEPTNO, JOB from EMP where ENAME = 'SMITH');
-- 查询和smith在同一个岗位的员工
select m.ENAME, m.JOB
from EMP m
where exists(
        select 'X' from EMP c where c.ENAME = 'SMITH'
                                and c.JOB = m.JOB
          );
``
exists和in的区别:
 in适合外表大而内表小的情况
 exists适合外表小而内表大的情况

2.普通函数

-- lower()小写
select lower('HelloWord')
from dual;
-- upper()大写
select upper('HelloWord')
from dual;
-- length() 字符长度个数
select length('你好啊?')
from dual;
-- substr('',startIndex,length) 从1开始算,从哪开始,要几个
select substr('你好啊?', 1, 2)
from dual; -- 从1开始要,要2个
-- instr()获取字符串位置,找哪个字符,从第几个位置开始找
select instr('helloword', 'o', 6)
from dual;
-- replace()替换文本,将什么替换成什么,全部替换
select replace('helloword', 'o', '我是老虎')
from dual;
-- round()四舍五入,保留几位小数
select round(22.222, 2)
from dual;
-- trunc()截取数字或者日期(去掉时分秒),不改变数据类型
select trunc(sysdate), trunc(123.19344, 1)
from dual;
-- mod()取余数
select mod(12, 3)
from dual;
-- floor() 向下取整
select floor(12.9)
from dual;
-- ceil() 向上取整
select ceil(12.1)
from dual;
-- abs()取绝对值
select abs(-12.1)
from dual;
-- power() x的y次方
select power(2, 4)
from dual;
-- 获取当前日期:sysdate,systimestamp
select sysdate, systimestamp
from dual;
--添加月份
SELECT add_months(SYSDATE, 2)
FROM dual;
--获取月的最后一天的日期
SELECT last_day(SYSDATE)
FROM dual;

练习案例:

-- 查找已经入职8个多月的员工
select *
from EMP
where sysdate >= add_months(HIREDATE, 8); -- 当前时间>入职时间+8个月
-- 显示满10年服务年限的员工的姓名和受雇日期
select ENAME, HIREDATE
from EMP
where sysdate >= add_months(HIREDATE, 12 * 10);
-- 对于每个员工,显示其加入公司的天数
select floor(sysdate - HIREDATE) "入职天数", ENAME
from EMP;
-- 找出各月倒数第三天受雇的所有员工
select HIREDATE, ENAME
from EMP
    -- last_day(hiredate)获取当前日期的最后一个月的最后一天
where last_day(HIREDATE) - 2 = HIREDATE;
-- 查看language(oracle数据库的编码格式)
select sys_context('USERENV', 'language')
from dual;

3.序列

序列sequence:就是一组数字的集合,有序数字,不重复,适合做表的主键的值
创建序列:
create sequence sq_dept
start with 50 --开始值50
increment by 10; – 每次递增10
nextval
– 第一次使用时必须sequence.nextval获取序列化初始值50
– 第二次==>第n次上次值基础上+递增值返回
select sq_dept.nextval
from dual;
currval 查询当前序列的值生成到几了
– sequence.currval 查询序列最后一次生成的值,它只能起查询作用,不会创建值
select sq_dept.currval
from dual;
补充:后续使用UUID 生成随机的32位字符,不重复,用来做主键列的值

4.视图

视图:就是一张虚表,降低sql查询的复杂度
细节:创建视图需要DBA权限
扩展如何让用户获取DBA权限
在这里插入图片描述
优点:
1.将复杂的查询保存视图,可以对最终用户屏蔽一定sql复杂度
2.可以通过视图查询数据的时候,可以对最终用户屏蔽底层表的结构和数据,安全性高
常问的问题:
视图会占用表空间?
不占用,视图不是直接存储数据,数据还是保存在原来的表中
视图可以加快查询吗?
不能,因为每次使用相当于重新执行一次
创建视图:
create view v_result as
select d.DEPTNO, d.DNAME, d.LOC, e.ENAME
from DEPT d,
EMP e
where d.DEPTNO = e.DEPTNO;

5.数据库的索引

<>, != ,is not null , or 都不走索引
oracle3类索引:
唯一索引:效率最高,应用在主键和唯一键上
位图索引:应用在有一定重复规则的数据字段上
b-tree:应用在不规则的数据的普通字段上
作用:
优点是提升查询效率
缺点是降低存储的效率
索引本身也占空间,需要控制数量,一般不超过3-5个人
索引:
– 创建索引
create [BITMAP][UNIQUE] index 索引名称 on 表(字段);
– 删除索引
drop index 索引名称;

6.行转列

有3种方法:现在讲的是静态行转列
通过:case when条件 then 结果1else 结果2 end
准备数据

-- 创建临时成绩表
create table temp_score (
  id    number primary key, -- 设置主键
  sname varchar2(10), --学生名字
  cname varchar2(10), -- 课程名
  score number -- 成绩
);
select *
from temp_score;
insert into temp_score (id, sname, cname, score)
VALUES (1, '赵云', '语文', 90);
insert into temp_score (id, sname, cname, score)
VALUES (2, '赵云', '数学', 80);
insert into temp_score (id, sname, cname, score)
VALUES (3, '赵云', '英语', 70);
insert into temp_score (id, sname, cname, score)
VALUES (4, '刘备', '语文', 90);
insert into temp_score (id, sname, cname, score)
VALUES (5, '刘备', '数学', 90);
insert into temp_score (id, sname, cname, score)
VALUES (6, '刘备', '英语', 90);
insert into temp_score (id, sname, cname, score)
VALUES (7, '诸葛亮', '语文', 100);
insert into temp_score (id, sname, cname, score)
VALUES (8, '诸葛亮', '数学', 100);
insert into temp_score (id, sname, cname, score)
VALUES (9, '诸葛亮', '英语', 100);
insert into temp_score (id, sname, cname, score)
VALUES (10, '张飞', '语文', 60);
insert into temp_score (id, sname, cname, score)
VALUES (11, '张飞', '数学', 50);
insert into temp_score (id, sname, cname, score)
VALUES (12, '张飞', '英语', 40);
insert into temp_score (id, sname, cname, score)
VALUES (13, '关羽', '语文', 70);
insert into temp_score (id, sname, cname, score)
VALUES (14, '关羽', '数学', 80);
insert into temp_score (id, sname, cname, score)
VALUES (15, '关羽', '英语', 50);

在这里插入图片描述
开始测试:

-- 1.查询出每个人的语文,数学,英语成绩
-- case when... then... else... end
select sname,
       max(case when cname = '语文' then score else 0 end) 语文,
       max(case when cname = '数学' then score else 0 end) 数学,
       max(case when cname = '英语' then score else 0 end) 英语
from temp_score
group by sname;

在这里插入图片描述
ps: 实现了行转列
案例扩展,找个单科成绩的最高的那个人

-- 思路1:找到了最高成绩,学科,再去成绩表找该条记录
select *
from temp_score sco,
     (
     -- 找到单科成绩最高,学科名
     select cname, max(score) scoreMax from temp_score group by cname) sco_max
    -- 再去成绩表找该条记录
where sco.cname = sco_max.cname
  and sco.score = sco_max.scoreMax
order by id asc;
-- 思路2:先找到单科的最高分,再查询成绩表,找到最高分数的记录
select *
from temp_score sco
where score = (
              -- 外部的成绩表的名字传入进来,然后内部的成绩表通过该名字找到该学科成绩的最大值
              select max(score) from temp_score scoInner where scoInner.cname = sco.cname);
-- 思路3:成绩表中,当学科一样的时候,不存在一条记录的 分数小于其他记录的分数
select *
from temp_score sco
where
    -- 当学科一样时,不存在外部的成绩表的成绩<内部表的成绩 == 也就是外部表的这个成绩最大(至多它等于内部表最大成绩)
    not exists(select * from temp_score scoInner where sco.cname = scoInner.cname
                                                   and sco.score < scoInner.score)
order by id asc;

继续讲解case when…then… eles… end,可以传入多个when,也就是多个条件

-- 2.创建视图
create VIEW v_temp_score AS
  select sname,
         max(case when cname = '语文' then score else 0 end) 语文,
         max(case when cname = '数学' then score else 0 end) 数学,
         max(case when cname = '英语' then score else 0 end) 英语
  from temp_score
  group by sname;
-- 3.虚拟表测试
select case
         when 100 > 1 then 'pig1'
         when 'b' = 'b1' then 'pig2'
         when 'c3' = 'c' then 'pig3'
         else 'pig4'
           end case
from dual;
-- 3.2
select case 'a'
         when 'a' then 'pig1'
         when 'b' then 'pig2'
         when 'c' then 'pig3'
         else 'pig4' end case
from dual;

todo decode(条件1,值,返回值,…) 函数传入3的倍数个参数

-- todo decode(条件1,值,返回值,...) 函数传入3的倍数个参数
select decode('e', 'b', 'is a', 'd', 'is d', 'e', 'is e', 'not all')
from dual;
select decode('a', 'is a', 'not a')
from dual;

墨成诗泪如雨

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle的sql语句3

1.exists关键字存在与之对应的: no exists 不存在看如下案例-- 和SMITH在同一个岗位,同一个部门的员工select ENAME, DEPTNO, JOBfrom EMPwhere (DEPTNO, JOB) in (select DEPTNO, JOB from EMP where ENAME = 'SMITH');-- 查询和smith在同一个岗位的员工...
复制链接

扫一扫