–lower把大写转小写 upper把小写转大写,–lower把大写转小写 upper把小写转大写

select * from scott.dept;
select * from scott.emp;

select * from scott.dept;
select * from scott.emp;

–dual 是 Oracle提供的贰个虚表
select length(‘hello’) from dual;

–dual 是 Oracle提供的3个虚表
select length(‘hello’) from dual;

–常用函数
–lower把大写转小写 upper把小写转大写
select * from scott.emp where ename=’smith’;
–搜索字母转为小写后的结果
select * from scott.emp where lower(ename)=’smith’;

–常用函数
–lower把大写转小写 upper把小写转大写
select * from scott.emp where ename=’smith’;
–搜索字母转为小写后的结果
select * from scott.emp where lower(ename)=’smith’;

select upper(‘helloworld’) from dual;
select lower(‘HELLOWORLD’) from dual;

select upper(‘helloworld’) from dual;
select lower(‘HELLOWORLD’) from dual;

–INITCAP使串中的全数单词的首字母变为题写
select initcap(‘sql course’) from dual;

–INITCAP使串中的全数单词的首字母变为题写
select initcap(‘sql course’) from dual;

–CONCAT 连接五个字符串
select concat(‘Hello’,’World’) from dual;

–CONCAT 连接七个字符串
select concat(‘Hello’,’World’) from dual;

–substr 取子字符串,从start起头,取count个
select substr(‘HelloWorld’,1,5) from dual;

–substr 取子字符串,从start初叶,取count个
select substr(‘HelloWorld’,1,5) from dual;

–substr 取子字符串,从4开头取到末尾
select substr(‘HelloWorld’,-4) from dual;

–substr 取子字符串,从4开端取到末尾
select substr(‘HelloWorld’,-4) from dual;

–LENGTH 重临字符串的长短
select length(‘HelloWorld’) from dual;

–LENGTH 重临字符串的长度
select length(‘HelloWorld’) from dual;

–INSTLAND(string,char)
在1个字符串中找寻钦命的字符,重临发现内定的字符的职位,从1早先
select instr(‘HelloWorld’,’o’) from dual;

–INSTPRADO(string,char)
在二个字符串中追寻钦点的字符,重返发现钦点的字符的地方,从1发端
select instr(‘HelloWorld’,’o’) from dual;

–QashqaiPAD在列的左边粘贴字符,LPAD在列的右边粘贴字符
select rpad(sal,8,’*’) from scott.emp;

–卡宴PAD在列的左侧粘贴字符,LPAD在列的左边粘贴字符
select rpad(sal,8,’*’) from scott.emp;

select lpad(sal,8,’*’) from scott.emp;

select lpad(sal,8,’*’) from scott.emp;

–T奇骏IM删除首尾的空字符串
select trim(‘ HelloWorld ‘) from dual;
select length(‘ HelloWorld ‘) from dual;
select length(trim(‘ HelloWorld ‘)) from dual;

–T大切诺基IM删除首尾的空字符串
select trim(‘ HelloWorld ‘) from dual;
select length(‘ HelloWorld ‘) from dual;
select length(trim(‘ HelloWorld ‘)) from dual;

–TEscortIM删除首尾的H(对大小写敏感)
select trim(‘H’ from ‘HelloWorldH’) from dual;

–T卡宴IM删除首尾的H(对大小写敏感)
select trim(‘H’ from ‘HelloWorldH’) from dual;

–TLX570IM删除首的H
select trim(leading ‘H’ from ‘HelloWorldH’) from dual;

–T奇骏IM删除首的H
select trim(leading ‘H’ from ‘HelloWorldH’) from dual;

–T汉兰达IM删除尾的H
select trim(trailing ‘H’ from ‘HelloWorldH’) from dual;

–TRIM删除尾的H
select trim(trailing ‘H’ from ‘HelloWorldH’) from dual;

–T锐界IM删除首尾的H
select trim(both ‘H’ from ‘HelloWorldH’) from dual;

–T福睿斯IM删除首尾的H
select trim(both ‘H’ from ‘HelloWorldH’) from dual;

–REPLACE(‘string’,’s1′,’s2′)
–string 希望被轮换的字符或变量
–s1 必要被调换的字符串 s2 替换的字符串
select replace(‘HelloWorldH’,’ll’,’FF’) from dual;

–REPLACE(‘string’,’s1′,’s2′)
–string 希望被沟通的字符或变量
–s1 须要被替换的字符串 s2 替换的字符串
select replace(‘HelloWorldH’,’ll’,’FF’) from dual;

–数值函数 Round四舍五入
select Round(3.14159,3) from dual;

–数值函数 Round四舍五入
select Round(3.14159,3) from dual;

–TRUNC截断
select TRUNC(3.14159,3) from dual;

–TRUNC截断
select TRUNC(3.14159,3) from dual;

–取模
select MOD(1600,300) from dual;

–取模
select MOD(1600,300) from dual;

–日期函数

–日期函数

create table tb_test(
currdate date
);

create table tb_test(
currdate date
);

select * from tb_test;

select * from tb_test;

–查询系统时间
select sysdate from dual;

–查询系统时间
select sysdate from dual;

–插入sysdate
insert into tb_test(currdate)values(sysdate);
–to_date将自定义日期格式字符串插入date类型的多寡
insert into tb_test(currdate)values(to_date(‘2018/1/14
02:03:42′,’yyyy/mm/dd hh:mi:ss’));
insert into
tb_test(currdate)values(to_date(‘2018/1/14′,’yyyy/mm/dd’));
–日期函数
–oracle的日期类型时date 暗中同意是yyyy/mm/dd hh:mi:ss
select sysdate from dual;

–插入sysdate
insert into tb_test(currdate)values(sysdate);
–to_date将自定义日期格式字符串插入date类型的多寡
insert into tb_test(currdate)values(to_date(‘2018/1/14
02:03:42′,’yyyy/mm/dd hh:mi:ss’));
insert into
tb_test(currdate)values(to_date(‘2018/1/14′,’yyyy/mm/dd’));
–日期函数
–oracle的日子类型时date 私下认可是yyyy/mm/dd hh:mi:ss
select sysdate from dual;

–在日期上丰裕或减去一个数字结果仍为日期
select sysdate+84 from dual;

–在日期上添加或减去四个数字结果仍为日期
select sysdate+84 from dual;

–两个转移函数 to_date to_char to_number
–to_date(日期字符串,格式)
select to_date(‘2018/1/14 02:03:42′,’yyyy/mm/dd hh:mi:ss’) from dual;
select to_date(‘2018/1/14′,’yyyy/mm/dd’) from dual;

–八个转移函数 to_date to_char to_number
–to_date(日期字符串,格式)
select to_date(‘2018/1/14 02:03:42′,’yyyy/mm/dd hh:mi:ss’) from dual;
select to_date(‘2018/1/14′,’yyyy/mm/dd’) from dual;

–to_char(日期,格式) 将日期转成字符
select to_char(sysdate,’yyyy/mm/dd’) from dual;
select to_char(sysdate,’yyyy/mm/dd hh:mi:ss’) from dual;
select to_char(sysdate,’mm’) from dual;
–将数字转成字符
select to_char(3.14159) from dual;

–to_char(日期,格式) 将日期转成字符
select to_char(sysdate,’yyyy/mm/dd’) from dual;
select to_char(sysdate,’yyyy/mm/dd hh:mi:ss’) from dual;
select to_char(sysdate,’mm’) from dual;
–将数字转成字符
select to_char(3.14159) from dual;

–to_number(字符串) 将四个字符串的数字转成number类型
select to_number(‘123.123’) from dual;
–无法将非数字的字符串转成number类型
select tb_number(‘abc’) from dual;

–to_number(字符串) 将二个字符串的数字转成number类型
select to_number(‘123.123’) from dual;
–不能够将非数字的字符串转成number类型
select tb_number(‘abc’) from dual;

–几个日子相减再次回到日期之间相差的命局
select to_date(‘2018/04/08′,’yyyy/mm/dd’)-sysdate from dual;
select TRUNC(to_date(‘2018/04/08′,’yyyy/mm/dd’)-sysdate,0) from dual;
select sysdate-to_date(‘2018/04/08′,’yyyy/mm/dd’) from dual;

–四个日子相减重临日期之间离开的天命
select to_date(‘2018/04/08′,’yyyy/mm/dd’)-sysdate from dual;
select TRUNC(to_date(‘2018/04/08′,’yyyy/mm/dd’)-sysdate,0) from dual;
select sysdate-to_date(‘2018/04/08′,’yyyy/mm/dd’) from dual;

–能够用数字除24来向日期中加上或减去时辰
select sysdate+2/24 from dual;
–能够用数字除24再除60向日期中加上分钟
select sysdate+2/24/60 from dual;

–能够用数字除24来向日期中丰硕或减去小时
select sysdate+2/24 from dual;
–能够用数字除24再除60向日期中拉长分钟
select sysdate+2/24/60 from dual;

–再次回到d1,d2之间月的数据,d1,d2可为字符串
select months_between(
to_date(‘2018-04-08′,’yyyy-mm-dd’),
to_date(‘2017-11-20′,’yyyy-mm-dd’)
)as months from dual;

–重临d1,d2之间月的数码,d1,d2可为字符串
select months_between(
to_date(‘2018-04-08′,’yyyy-mm-dd’),
to_date(‘2017-11-20′,’yyyy-mm-dd’)
)as months from dual;

–再次回到日期d加上四个月后的日子,3是整数
select add_months(sysdate,3) from dual;

–重返日期d加上3个月后的日期,3是整数
select add_months(sysdate,3) from dual;

–再次来到日期d后第①个星期几的日期
select next_day(sysdate,’星期二’) from dual;
–海外周四是1 , 所以3指礼拜五
select next_day(sysdate,3) from dual;

–重临日期d后先是个星期几的日子
select next_day(sysdate,’星期二’) from dual;
–海外周一是1 , 所以3指周天
select next_day(sysdate,3) from dual;

–再次回到d所在月最终一天的日期
select last_day(sysdate) from dual;

–重临d所在月最后一天的日期
select last_day(sysdate) from dual;

–获得下近日辰 0分0秒
select trunc(sysdate+1/24,’hh’) from dual;

–获得下一小时 0分0秒
select trunc(sysdate+1/24,’hh’) from dual;

–获得下一天 0分0秒
select trunc(sysdate+1,’dd’) from dual;

–获得下一天 0分0秒
select trunc(sysdate+1,’dd’) from dual;

–得到下八月 1号0分0秒
select last_day(sysdate) from dual;
select trunc(last_day(sysdate)+1) from dual;

–获得下一月 1号0分0秒
select last_day(sysdate) from dual;
select trunc(last_day(sysdate)+1) from dual;

–获得下一年 10月1号0分0秒
select trunc(sysdate,’yyyy’) from dual;
select add_months(trunc(sysdate,’yyyy’),12) from dual;

–获得下一年 5月1号0分0秒
select trunc(sysdate,’yyyy’) from dual;
select add_months(trunc(sysdate,’yyyy’),12) from dual;

–Round四舍五入函数 暗中同意依据 dd进行 4舍5入 抢先中午 12点就进来下一天
select Round(sysdate) from dual;

–Round四舍五入函数 暗中同意依照 dd举行 4舍5入 超过深夜 12点就进去下一天
select Round(sysdate) from dual;

–当先15号重返下四个月
select Round(sysdate,’mm’) from dual;
–超越八月就重返下一年三月1号
select Round(sysdate,’yyyy’) from dual;

–超过15号重回下三个月
select Round(sysdate,’mm’) from dual;
–超过7月就重回下一年五月1号
select Round(sysdate,’yyyy’) from dual;

–截断日期
select trunc(sysdate,’mm’) from dual;
select trunc(sysdate,’dd’) from dual;
select trunc(sysdate,’hh’) from dual;

–截断日期
select trunc(sysdate,’mm’) from dual;
select trunc(sysdate,’dd’) from dual;
select trunc(sysdate,’hh’) from dual;

–定时器
–每分钟实施
select trunc(sysdate,’mi’)+1/24/60 from dual;

–定时器
–每分钟实施
select trunc(sysdate,’mi’)+1/24/60 from dual;

–每一日定时执行3
–例如:天天的黎明(Liu Wei)2点施行
select trunc(sysdate)+1+2/24 from dual;

–每日定时执行3
–例如:每日的黎明先生2点推行
select trunc(sysdate)+1+2/24 from dual;

–每一周定时进行
–例如:每星期四黎明(英文名:lí míng)2点施行
select trunc(next_day(sysdate,2))+2/24 from dual;

–每一周定时进行
–例如:每星期三黎明先生2点推行
select trunc(next_day(sysdate,2))+2/24 from dual;

–每月定时进行
–例如:每月1日凌晨2点执行
select trunc(last_day(sysdate))+1+2/24 from dual;

–每月定时实施
–例如:每月1日凌晨2点执行
select trunc(last_day(sysdate))+1+2/24 from dual;

–每季度定时执行
–例如每季度的率后天凌晨2点推行
select trunc(add_months(sysdate,3),’Q’)+2/24 from dual;

–每季度定时执行
–例如每季度的第贰天凌晨2点实施
select trunc(add_months(sysdate,3),’Q’)+2/24 from dual;

–每半年定时实施
–例如:每年7月1日和1月1日凌晨2点
select add_months(trunc(sysdate,’yyyy’),6)+2/24 from dual;

–每3个月定时实施
–例如:每年7月1日和1月1日凌晨2点
select add_months(trunc(sysdate,’yyyy’),6)+2/24 from dual;

–每年定时执行
–例如:每年10月十13日和凌晨2点实践
select add_months(trunc(sysdate,’yyyy’),12)+2/24 from dual;

–每年定时执行
–例如:每年7月15日和凌晨2点执行
select add_months(trunc(sysdate,’yyyy’),12)+2/24 from dual;

–通用函数完毕都部队分作用
–nvl函数 假使第三个参数为null,则取第3个参数
select comm from scott.emp;
select nvl(comm,0) from scott.emp;

–通用函数完结都部队分功效
–nvl函数 若是第③个参数为null,则取第③个参数
select comm from scott.emp;
select nvl(comm,0) from scott.emp;

–使用0替换null的comm,统计年收入
select ename,sal,comm,(sal+comm)*12 年收入 from scott.emp;
select ename,sal,nvl(comm,0),(sal+nvl(comm,0))*12 from scott.emp;

–使用0替换null的comm,总括年收入
select ename,sal,comm,(sal+comm)*12 年收入 from scott.emp;
select ename,sal,nvl(comm,0),(sal+nvl(comm,0))*12 from scott.emp;

–nvl2函数 假设首个参数为null,则取第多少个参数,不然取第一个参数
select nvl2(comm,comm,0) from scott.emp;

–nvl2函数 即使第三个参数为null,则取第伍个参数,不然取第三个参数
select nvl2(comm,comm,0) from scott.emp;

–nullif 相等回到null,不等再次回到expr1
select nullif(1,3) from dual;

–nullif 相等回到null,不等重临expr1
select nullif(1,3) from dual;

–多行函数
–case
select * from scott.emp;

–多行函数
–case
select * from scott.emp;

select ename,job,sal 基本薪水,
case job
when ‘SALESMAN’ THEN sal+sal*0.9
when ‘MANAGER’ THEN sal+sal*0.85
when ‘ANALYST’ THEN sal+100
when ‘CLERK’ THEN sal+200
else sal
end
as 实发工钱 –作为小名
from scott.emp;

select ename,job,sal 基本报酬,
case job
when ‘SALESMAN’ THEN sal+sal*0.9
when ‘MANAGER’ THEN sal+sal*0.85
when ‘ANALYST’ THEN sal+100
when ‘CLERK’ THEN sal+200
else sal
end
as 实发工钱 –作为别称
from scott.emp;

–decode函数
select ename,job,sal 基本报酬,
decode(job,
‘SALESMAN’, sal+sal*0.39,
‘MANAGER’ , sal+sal*0.85,
‘ANALYST’ , sal+100,
‘CLERK’ , sal+200,
‘PRESIDENT’,sal
)as 实发工钱 –小名
from scott.emp;

–decode函数
select ename,job,sal 基本工资,
decode(job,
‘SALESMAN’, sal+sal*0.39,
‘MANAGER’ , sal+sal*0.85,
‘ANALYST’ , sal+100,
‘CLERK’ , sal+200,
‘PRESIDENT’,sal
)as 实发工钱 –别名
from scott.emp;

–多表连接
create table tb_course(
name varchar2(18),
course varchar2(18),
grade number
);

–多表连接
create table tb_course(
name varchar2(18),
course varchar2(18),
grade number
);

INSERT INTO tb_course(NAME,course,grade) VALUES(‘tom’,’JDBC’,20);
INSERT INTO tb_course(NAME,course,grade)
VALUES(‘tom’,’Hibernate’,50);
INSERT INTO tb_course(NAME,course,grade) VALUES(‘tom’,’Spring’,80);
INSERT INTO tb_course(NAME,course,grade) VALUES(‘mary’,’JDBC’,30);
INSERT INTO tb_course(NAME,course,grade)
VALUES(‘mary’,’Hibernate’,60);
INSERT INTO tb_course(NAME,course,grade) VALUES(‘mary’,’Spring’,70);

INSERT INTO tb_course(NAME,course,grade) VALUES(‘tom’,’JDBC’,20);
INSERT INTO tb_course(NAME,course,grade)
VALUES(‘tom’,’Hibernate’,50);
INSERT INTO tb_course(NAME,course,grade) VALUES(‘tom’,’Spring’,80);
INSERT INTO tb_course(NAME,course,grade) VALUES(‘mary’,’JDBC’,30);
INSERT INTO tb_course(NAME,course,grade)
VALUES(‘mary’,’Hibernate’,60);
INSERT INTO tb_course(NAME,course,grade) VALUES(‘mary’,’Spring’,70);

–多表连接1.
select * from tb_course;
select name,grade as JDBC from tb_course where course=’JDBC’;
select name,grade as Hibernate from tb_course where
course=’Hibernate’;
select name,grade as Spring from tb_course where course=’Spring’;

–多表连接1.
select * from tb_course;
select name,grade as JDBC from tb_course where course=’JDBC’;
select name,grade as Hibernate from tb_course where
course=’Hibernate’;
select name,grade as Spring from tb_course where course=’Spring’;

select T4.name,T1.JDBC,T2.Hibernate,T3.Spring
from
(select name,grade as JDBC from tb_course where course=’JDBC’) T1,
(select name,grade as Hibernate from tb_course where
course=’Hibernate’) T2,
(select name,grade as Spring from tb_course where course=’Spring’)
T3,
(select distinct name from tb_course) T4
where T4.name = T1.name and T4.name = T2.name and T4.name = T3.name;

select T4.name,T1.JDBC,T2.Hibernate,T3.Spring
from
(select name,grade as JDBC from tb_course where course=’JDBC’) T1,
(select name,grade as Hibernate from tb_course where
course=’Hibernate’) T2,
(select name,grade as Spring from tb_course where course=’Spring’)
T3,
(select distinct name from tb_course) T4
where T4.name = T1.name and T4.name = T2.name and T4.name = T3.name;

–多表连接2.
select
name,
decode(course,’JDBC’,grade,0) AS
JDBC,–若是’course’列的值是’jdbc’,则彰显’grade’的值,不然显示0
decode(course,’Hibernate’,grade,0) AS Hibernate,
decode(course,’Spring’,grade,0) AS Spring
from
tb_course;

–多表连接2.
select
name,
decode(course,’JDBC’,grade,0) AS
JDBC,–若是’course’列的值是’jdbc’,则显示’grade’的值,不然彰显0
decode(course,’Hibernate’,grade,0) AS Hibernate,
decode(course,’Spring’,grade,0) AS Spring
from
tb_course;

–decode(column,value,cloumnvalue,default)
–分组,组函数,decode
select
name,
sum(decode(course,’JDBC’,grade,0)) JDBC,
sum(decode(course,’Hibernate’,grade,0)) Hibernate,
sum(decode(course,’Spring’,grade,0)) Spring
from
tb_course t
GROUP BY t.name;

–decode(column,value,cloumnvalue,default)
–分组,组函数,decode
select
name,
sum(decode(course,’JDBC’,grade,0)) JDBC,
sum(decode(course,’Hibernate’,grade,0)) Hibernate,
sum(decode(course,’Spring’,grade,0)) Spring
from
tb_course t
GROUP BY t.name;