添加注释
--给表添加注释
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;