Oracle的一些sql语句

添加注释

--给表添加注释
comment on table emp is '雇员表';
--给列添加注释
comment on column emp.ename is '雇员姓名';

查看表里的字段

desc emp;

计算

--字符串连接符
select 'my name is '||ename name from emp;
select concat('ny name is ',ename) from emp;
--计算所有员工的年薪
select ename,(e.sal+e.comm)*12 from emp e;
--null特殊，引入函数nvl，nvl(arg1,arg2),如果arg1是空，那么返回arg2，如果不是空，则返回原来的值
select ename 姓名,(e.sal+nvl(comm,0))*12 as 年薪 from emp e;

交并差集

--A
select * from emp where deptno =30;
--B
select * from emp where sal >1500;
--并集，不包含重复的数     union、union all
select * from emp where deptno =30 union select * from emp where sal>1500;
--全集，包含重复的数
select * from emp where deptno =30 union all select * from emp where sal>1500;
--交集，无重复    intersect
select * from emp where deptno =30 intersect select * from emp where sal>1500;
--差集，包含在A集合而不包含在B集合中的数据，跟A和B的集合顺序相关  minus
select * from emp where deptno =30 minus select * from emp where sal>1500;
select * from emp where sal>1500 minus select * from emp where deptno =30 

单行函数

输入一个参数，输出一个参数.单行函数可以嵌套。

字符函数

--concat 连接字符串
select concat('my name is ',ename) from emp;
--首字符大写
select initcap(ename) from emp;
--全部大写
select upper(ename) from emp;
--全部小写
select lower(ename) from emp;
--填充字符,在前面补齐10位
select lpad(ename,10,'*') from emp;
--填充字符,在右面补齐10位
select rpad(ename,10,'*') from emp;
--取出空格
select trim(ename) from emp;
select ltrim(ename) from emp;
select rtrim(ename) from emp;  
-- 查询‘A’的下标
select instr(ename,'A') from emp;
-- 字符串长度
select length(ename) from emp;
-- 截取字符串
select substr(ename,0,2) from emp;
--替换
select replace(ename,'A','a') from emp;

数字类型

--数值函数
--给小数四舍五入
select round(123.136,2) from dual;
--截断
select trunc(123.123,2) from  dual;
--向上取整
select ceil(12.123) from dual;
--向下
select floor(-12.8) from dual;
--取模
select mod(10,4) from dual;
--绝对值
select abs(-100) from dual;

日期类型

--当前系统时间
select sysdate from dual;
--添加指定月份
select add_months(hiredate,3),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 extract (YEAR from date '2001-2-16' ) 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;

转换函数

隐式转换：字符串可以转换成日期或数值

显示转换：to_char to_date to_number

--转换成字符串
select to_char(sysdate,'YYYY-MM-dd HH24:MI:SS') from dual;
select to_char(123.11,'0000.0') from dual;
--转换成日期
select to_date('2019/02/25 10:11:30','YYYY-MM-dd HH24:MI:SS') from dual;

其他函数

条件函数 decode( a, b, c ，d，e) 表示 if(a == b){ return c }else if(a == d){return e }

--为所有人涨工资，10涨10%  20涨15%    30涨18%
select ename 姓名,sal,decode(deptno,10,sal*1.1,20,sal*1.15,30,sal*1.18) 涨薪 from emp ;

case ……when……then……

select ename 姓名,sal,
   case deptno
     when 10 then sal*1.1
     when 20 then sal*1.2
     when 30 then sal*1.3
     end
 涨薪 from emp ;

组函数

组函数又称为聚合函数，出入多个值，只输出一个结果。

组函数,一般情况下，组函数都要和groupby组合使用,组函数一般用于选择列表或者having条件判断

/*常用的组函数有5个
avg()  平均值,只用于数值类型的数据
min()  最小值，适用于任何类型
max()  最大值，适用于任何类型
count() 记录数,处理的时候会跳过空值而处理非空值,count一般用来获取表中的记录条数，获取条数的时候可以使用*或者某一个具体的列,甚至可以使用纯数字来代替，但是从运行效率的角度考虑，建议使用数字或者某一个具体的列,而不要使用*
sum()   求和，只适合数值类型的数据
*/
select avg(sal) from emp;
select max(sal) from emp;
select min(sal) from emp;
select count(sal) from emp;

group by,按照某些相同的值去进行分组操作，group进行分组操作的时候，可以指定一个列或者多个列，但是当使用了groupby 之后，选择列表中只能包含组函数的值或者group by 的普通字段

--group by
--求每个部门的平均薪水
select avg(sal) from emp group by deptno;
--求平均新书大于2000的部门
select avg(sal) from emp group by deptno having avg(sal) >2000;
--部门下雇员的工资>2000 人数
select deptno,count(sal) from emp where sal>2000 group by deptno

限制输出、分页

在oracle中，如果需要使用限制输出和分页的功能的话，必须要使用rownum，但是select * from emp ORDER BY sal desc limit 5;不能直接使用，需要嵌套使用。

--4、求薪水最高的前5名雇员
select * from (select * from emp e order by e.sal desc) t1 where rownum<=5;
--错误 
select * from emp e  where rownum <=5 order by e.sal desc ;--取5行再排序
--5、求薪水最高的第6到10名雇员
--使用rownum的时候必须要再外层添加嵌套，此时才能将rownum作为其中的一个列，然后再进行限制输出
select * from
(select t1.* ,rownum rn from
       (select * from emp e order by e.sal desc)t1
       where rownum<=10)t2
       where t2.rn >5;

练习题

行转列

--建表语句
create table test(
   id number(10) primary key,
   type number(10) ,
   t_id number(10),
   value varchar2(5)
);
insert into test values(100,1,1,'张三');
insert into test values(200,2,1,'男');
insert into test values(300,3,1,'50');
insert into test values(101,1,2,'刘二');
insert into test values(201,2,2,'男');
insert into test values(301,3,2,'30');
insert into test values(102,1,3,'刘三');
insert into test values(202,2,3,'女');
insert into test values(302,3,3,'10');

sql语句

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