OREACLE-01

1、基础知识归纳

oreacle中null代表特殊字符，不可以字段=null判断，一定是a is null 或者a is not null　　
oreacle中any some all使用的判断
between and 关键字
in（list）和not in（list）的使用
and 和or的语句出现在一个sql中，and的优先级高于or的优先级。所以or用括号包起来，提高优先级。

/*exists（sub－query）,当exists中的子查询语句能查到对应结果的时候，就可以查询出所有的数据　

相当于双层for循环
--现在要查询部门编号为10和20的员工，要求使用exists实现
*/
select * from emp where deptno = 10 or deptno = 20;
--通过外层循环来规范内层循环
select *
  from emp e
 where exists (select deptno
          from dept d
         where (d.deptno = 10 or d.deptno = 20)
           and e.deptno = d.deptno)

模糊查询最好%不要放在开头，这样检索起来是最慢的。

模糊查询：
like  _ ,%,escape ‘\‘   _\% escape ‘\’

在like的语句中，需要使用占位符或者通配符
_,某个字符或者数字仅出现一次
%，任意字符出现任意次数
escape,使用转义字符,可以自己规定转义字符

使用like的时候要慎重，因为like的效率比较低
使用like可以参考使用索引，但是要求不能以%开头
涉及到大文本的检索的时候，可以使用某些框架 luence，solr，elastic search
*/
--查询名字以S开头的用户
select * from emp where ename like('S%')
--查询名字以S开头且倒数第二个字符为T的用户
select * from emp where ename like('S%T_');
select * from emp where ename like('S%T%');
--查询名字中带%的用户
select * from emp where ename like('%\%%') escape('\')
/*

order by 默认按照升序方式排列。也可以支持多个字段order by sal desc,ename asc 如果是数字会排序，如果是字符川会按照字典序（asic码排列）每次在执行order by的时候相当于是做了全排序，思考全排序的效率会比较耗费系统的资源，因此选择在业务不太繁忙的时候进行。与mysql中limit by比价相似。只是msql限制了输出。

字符串连接符

1 --使用计算字段
2 --字符串连接符
3 select 'my name is '||ename name from emp;
4 select concat('my name is ',ename) from emp;

计算所有员工的年薪 NVL函数

select ename,(e.sal+e.comm)*12 from emp e;
null是比较特殊的存在，null做任何运算都还是为null，因此要将空进行转换
--引入函数nvl，nvl(arg1,arg2),如果arg1是空，那么返回arg2，如果不是空，则返回原来的值
select ename,(e.sal+nvl(e.comm,0))*12  from emp e

交集全集并集重集链接：https://blog.csdn.net/limingchuan123456789/article/details/11997103

select 100+null from dual;
--A
select * from emp where deptno =30;
--B
select * from emp where sal >1000; 
--并集，将两个集合中的所有数据都进行显示，但是不包含重复的数据
select * from emp where deptno =30 union
select * from emp where sal >1000;
--全集，将两个集合的数据全部显示，不会完成去重的操作
select * from emp where deptno =30 union all
select * from emp where sal >1000;
--交集，两个集合中交叉的数据集，只显示一次
select * from emp where deptno =30 intersect 
select * from emp where sal >1000;
--差集,包含在A集合而不包含在B集合中的数据，跟A和B的集合顺序相关

2、oreacle函数

　
　
having可以用于虚拟的聚合函数查询，但where是不具备的。

--字符函数
--concat：表示字符串的连接  等同于||
select concat('my name is ', ename) from emp;
--将字符串的首字母大写
select initcap(ename) from emp;
--将字符串全部转换为大写
select upper(ename) from emp;
--将字符串全部转换为小写
select lower(ename) from emp;
--填充字符串
select lpad(ename,10,'*') from emp;
select rpad(ename,10,'*') from emp;
--去除空格
select trim(ename) from emp;
select ltrim(ename) from emp;
select rtrim(ename) from emp;
--查找指定字符串的位置
select instr('ABABCDEF','A') from emp;
--查看字符串的长度
select length(ename) from emp;
--截取字符串的操作
select substr(ename,0,2) from emp;
--替换操作
select replace('ababefg','ab','hehe') from emp;

数值的函数

--数值函数
--给小数进行四舍五入操作，可以指定小数部分的位数
select round(123.123,2) from dual;
select round(123.128,2) from dual;
select round(-123.128,2) from dual;

--截断数据,按照位数去进行截取，但是不会进行四舍五入的操作
select trunc(123.128,2) from dual;
--取模操作
select mod(10,4) from dual;
select mod(-10,4) from dual;
--向上取整
select ceil(12.12) from dual;
--向下取整
select floor(13.99) from dual;
--取绝对值
select abs(-100) from dual;
--获取正负值
select sign(-100) from dual;
--x的y次幂
select power(2,3) from dual;

获取当前时间：(select sysdata from dual ) between startTime and endTIme

mysql中获取日期和时间

▪ select current_time() from dual;---- mysql:时间。
▪ select current_date() form dual; ---mysql；日期
▪ select current_timestamp() from dual;---mysql：日期时间

3、日期函数

--日期函数
select sysdate from dual;
select current_date from dual;
--add_months,添加指定的月份 在hiredate所在的月份加两月
select add_months(hiredate,2),hiredate from emp;
--返回输入日期所在月份的最后一天
select last_day(sysdate) from dual;
--两个日期相间隔的月份
select months_between(sysdate,hiredate) from emp;
--返回四舍五入的第一天
select sysdate 当时日期,
round(sysdate) 最近0点日期,
round(sysdate,'day') 最近星期日,
round(sysdate,'month') 最近月初,
round(sysdate,'q') 最近季初日期, 
round(sysdate,'year') 最近年初日期 from dual;
--返回下周的星期几
select next_day(sysdate,'星期一') from dual;
--提取日期中的时间
select 
extract(hour from timestamp '2001-2-16 2:38:40 ' ) 小时,
extract(minute from timestamp '2001-2-16 2:38:40 ' ) 分钟,
extract(second from timestamp '2001-2-16 2:38:40 ' ) 秒,
extract(DAY from timestamp '2001-2-16 2:38:40 ' ) 日,
extract(MONTH from timestamp '2001-2-16 2:38:40 ' ) 月,
extract(YEAR from timestamp '2001-2-16 2:38:40 ' ) 年
 from dual;
--返回日期的时间戳
select localtimestamp from dual;
select current_date from dual;
select current_timestamp from dual;
--给指定的时间单位增加数值
select
trunc(sysdate)+(interval '1' second), --加1秒(1/24/60/60)
trunc(sysdate)+(interval '1' minute), --加1分钟(1/24/60)
trunc(sysdate)+(interval '1' hour), --加1小时(1/24)
trunc(sysdate)+(INTERVAL '1' DAY),  --加1天(1)
trunc(sysdate)+(INTERVAL '1' MONTH), --加1月
trunc(sysdate)+(INTERVAL '1' YEAR), --加1年
trunc(sysdate)+(interval '01:02:03' hour to second), --加指定小时到秒
trunc(sysdate)+(interval '01:02' minute to second), --加指定分钟到秒
trunc(sysdate)+(interval '01:02' hour to minute), --加指定小时到分钟
trunc(sysdate)+(interval '2 01:02' day to minute) --加指定天数到分钟
from dual;

4、转换函数

/*

转换函数
     在oracle中存在数值的隐式转换和显式转换
     隐式转换指的是字符串可以转换为数值或者日期
显式转换：
    to_char: 当由数值或者日期转成字符串的时候，必须要规定格式
*/
select '999'+10 from dual;
--date ：to_char
select to_char(sysdate,'YYYY-MI-SS HH24:MI:SS') from dual;
-- number : to_char
select to_char(123.456789,'9999') from dual;
select to_char(123.456789,'0000.00') from dual;
select to_char(123.456789,'$0000.00') from dual;
select to_char(123.456789,'L0000.00') from dual;
select to_char(123456789,'999,999,999,999') from dual;
--to_date:转换之后都是固定的格式
select to_date('2019/10/10 10:10:10','YYYY-MM-DD HH24:MI:SS') from dual;
--to_number:转成数字
select to_number('123,456,789','999,999,999') from dual;

5、sql的说明

--显示没有上级管理的公司首脑
select ename,nvl(to_char(mgr),'boss') from emp where mgr is null; 由于mgr是数值，那么转换为varchar 所以就要用转换函数
--显示员工雇佣期满6个月后下一个星期五的日期
select hiredate,next_day(add_months(hiredate,6),'星期五') from emp; 先查询6个月以后的日期，在根据日期查询时星期五的日期。

6、条件函数

可以通过case when 条件判断 then decode（参数，if，结果）

--条件函数
--decode,case when

--给不同部门的人员涨薪，10部门涨10%，20部门涨20%，30部门涨30%
select ename,sal,deptno,decode(deptno,10,sal*1.1,20,sal*1.2,30,sal*1.3) from emp;
select ename,
       sal,
       deptno,
       case deptno
         when 10 then
          sal * 1.1
         when 20 then
          sal * 1.2
         when 30 then
          sal * 1.3
       end
  from emp;

7、行转列实现

　　如何实现行转列呢

select decode(type, 1, value) 姓名,
       decode(type, 2, value) 性别,
       decode(type, 3, value) 年龄
  from test;
select min(decode(type, 1, value)) 姓名,
       min(decode(type, 2, value)) 性别,
       min(decode(type, 3, value)) 年龄
  from test group by t_id;

分析如下：

首先可以根据判断条件取值，然后group by分组，然后取一行的最大值。因为group by查询的语句是需要有条件的。

8、oreacle中rank函数：可以实现排名的序列号：https://www.jb51.net/article/51627.htm

　　mysql中实现排序通过关联表实现

按各科成绩进行排序，并显示排名(实现不完全)
-- mysql没有rank函数
    select a.s_id,a.c_id,
        @i:=@i +1 as i保留排名,
        @k:=(case when @score=a.s_score then @k else @i end) as rank不保留排名,
        @score:=a.s_score as score
    from (
        select s_id,c_id,s_score from score WHERE c_id='01' GROUP BY s_id,c_id,s_score ORDER BY s_score DESC
)a,(select @k:=0,@i:=0,@score:=0)s
    union
    select a.s_id,a.c_id,
        @i:=@i +1 as i,
        @k:=(case when @score=a.s_score then @k else @i end) as rank,
        @score:=a.s_score as score
    from (
        select s_id,c_id,s_score from score WHERE c_id='02' GROUP BY s_id,c_id,s_score ORDER BY s_score DESC
)a,(select @k:=0,@i:=0,@score:=0)s
    union
    select a.s_id,a.c_id,
        @i:=@i +1 as i,
        @k:=(case when @score=a.s_score then @k else @i end) as rank,
        @score:=a.s_score as score
    from (
        select s_id,c_id,s_score from score WHERE c_id='03' GROUP BY s_id,c_id,s_score ORDER BY s_score DESC
)a,(select @k:=0,@i:=0,@score:=0)s

posted @ 2022-04-07 23:48 Jerry&Ming 阅读(62) 评论(0) 收藏举报

刷新页面返回顶部

Jerry&Ming

OREACLE-01

1、基础知识归纳

2、oreacle函数

公告