1.截取字符串：concat(substr('201504',0,4),'01')||'转23'
2.获取指定日期上月及上年的日期：
获取指定日期“201504”上月 “201503”日期：to_char(add_months(to_date('201504','yyyyMM'), -1),'yyyyMM')
获取指定日期“201504”下月 “201505”日期：to_char(add_months(to_date('201504','yyyyMM'), 1),'yyyyMM')
获取指定日期“201504”上年 “201404”日期：to_char(add_months(to_date('201504','yyyyMM'), -12),'yyyyMM')
获取指定日期“201504”下年 “201404”日期：to_char(add_months(to_date('201504','yyyyMM'), 12),'yyyyMM')
3.获取第二天日期：
select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss'),to_char((sysdate)+1,'yyyy-MM-dd HH24:mi:ss') from dual;
4.获取本月当前时间的最后一天日期：
select last_day(sysdate) from dual;
5.获取两个日期的月份差：
select months_between('19-12月-1999','19-3月-1999') Mon_between from dual;
select months_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.20','yyyy.mm.dd')) mon_betw from dual;
6.给出日期date和星期x之后计算星期X对应的日期:
select next_day('08-6月-2015','星期五') next_day from dual;
7.获取系统当前日期 加 星期 ：
select to_char(sysdate,'yyyy-MM-dd day') from dual;
8.按照给出的要求将日期进行截断：
select to_char(trunc(sysdate,'hh'),'yyyy-MM-dd HH24:mi:ss') hh,
to_char(trunc(sysdate,'mi'),'yyyy-MM-dd HH24:mi:ss'),to_char(trunc(sysdate,'mi'),'mi') hhmm from dual;
9.对日期进行格式化：
select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
10.将给出的字符转化为数字number：
select to_number('1999') year from dual;
11.返回与指定的字符对应的十进制数;
select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual;
12.给出整数,返回对应的字符：
select chr(54740) zhao,chr(65) chr65 from dual;
13.返回字符串并将字符串的第一个字母变为大写:
select initcap('smith') upp from dual;
14.获取字符串的长度：length(name) ;将所有的字符小写：lower('AaBbCcDd');将所有的字符大写：upper('AaBbCcDd')
15.删除左边及右边出现的空字符串：
select ltrim(rtrim(' gao qian jing ',' '),' ') from dual;
16.替换的字符或变量:
select replace('he love you','he','i') from dual;
17.返回指定值的绝对值：
select abs(100),abs(-100) from dual;
18.返回大于或等于给出数的最小整数：
select ceil(3.1415927) from dual;
19.对给定的数取整：
select floor(2345.67) from dual;
20.对指定的精度进行四舍五入：
select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;
21.取数字n的符号,大于0返回1,小于0返回-1,等于0返回0:
select sign(123),sign(-100),sign(0) from dual;
22.查询前N条记录：
select a.*,rownum from (select * from scott.emp order by empno ) a where rownum < 10;
select a.*,rownum from scott.emp a where rownum < 10 order by a.empno;
23.如何快速创建一个和原表一样的备份表：
create table new_table as (select * from old_table);
24.怎样用Sql语句实现查找一列中第N大值：
select * from (select t.*,dense_rank() over (order by sal) rank from scott.emp t) where rank = 5;
--查询所有的用户
select * from dba_users;
select * from scott.emp;
--增加字段
alter table scott.emp add(
empname varchar2(20) default '无名氏' not null,
empage integer default 22 not null);
alter table scott.emp add(
empname varchar2(20) default '无名氏' not null);
--修改字段类型
alter table scott.emp modify(empname varchar2(30) default'xushuyi');
--修改字段名
alter table scott.emp rename column empname to empnames;
--修改列名称
alter table scott.emp rename column empnames to empname;
--删除字段
alter table scott.emp drop(empname);
alter table scott.emp drop column empage;
--修改表名
alter table scott.emps rename to emp;
--为表添加主键
alter table scott.emp add constraint pk_emp primary key(empno);
--删除表中的主键
alter table scott.emp drop constraint pk_emp;
Oracle表连接
SQL/Oracle使用表连接从多个表中查询数据
语法格式:
select 字段列表
from table1,table2
where table1.column1=table2.column2;
说明:
在where子句中指定连接条件
当被连接的多个表中存在同名字段时,必须在该字段前加上"表名"作为前缀.
连接的类型
Oracle8i之前的表连接：
等值连接(Equijoin)
非等值连接(Non-Equijoin)
外连接(Outer join):-->左外连接-->右外连接
自连接(Self join)
Oracle9之后新引入的连接形式(支持SQL99规范)
交叉连接(Cross join)
自然连接(Natural join)
使用Using子句建立连接
使用on子句建立连接
外连接(Outer join):-->左外连接-->右外连接-->全外连接
等值连接(Equijoin)
select empno,ename,sal,emp.deptno,dname from emp,dept
where emp.deptno = dept.deptno;
多表连接中：
可使用AND操作符增加查询条件
使用表别名可以简化查询
使用表名(表别名)前缀可提高查询效率
为了连接n个表,至少需要n-1个连接条件
非等值连接(Non-Equijoin)
外连接(Outer join)
使用外连接可以看到参与连接的某一方不满足连接条件的记录
外连接运算符为(+)
传统的外连接分为左外连接和右外连接两种
语法格式:
select 字段列表
from table1,table2
where table1.column1(+)=table2.column2;
select 字段列表
from table1,table2
where table1.column1=table2.column2(+);
自连接(Self join)
select a.enpno,a.ename,a.ngr,b.ename
from emp a,emp b
where a.ngr = b.enpno;
SQL99连接语法
SQL1999规范中规定的连接查询语法
复制代码
select 字段列表
from table1
[cross join table2] |
[natural join table2] |
[join table2 using(字段名)] |
[join table2 on(table.column_name=table2.column_name)] |
[(left | right | full out ) join table2
on(table1.column_name=table2.column_name)];
复制代码
交叉连接(Cross join)
Cross join 产生了一个笛卡尔集，其效果等同于再两个表进行连接时未使用where子句限定连接条件;
select empno,ename,sal,emp.deptno,dname
from emp cross join dept;
自然连接(Natural join)
Natural join基于两个表中的全部同名列建立连接
从两个表中选出同名列的值均对应相等的所有行
如果两个表中的同名列的所有数据类型不同,则出错
不允许在参照列上使用表名或者别名作为前缀
select empno,ename,sal,emp.deptno,dname
from emp natural join dept;
Using子句
如果不希望参照被连接表的所有同名列进行等值连接,自然连接将无法满足要求，可以在连接时使用USING子句来设置用于等值连接的列(参照列)名.
select empno,ename,sal,emp.deptno,dname
from emp join dept
using(deptno);
不允许在参照列上使用表名或者别名作为前缀
On子句
如果要参照非同名的列进行等值连接,或想设置任意的连接条件,可以使用On子句
select empno,ename,sal,emp.deptno,dname
from emp join dept
on(emp.deptno=dept.deptno);
多表连接
使用SQL99连接语法,两个以上的表进行连接时应依次/分别指定相临的两个表之间的连接条件.
复制代码
select 字段列表
from table1
[cross join table2] |
[natural join table2] |
[join table2 using(字段名)] |
[join table2 on(table.column_name=table2.column_name)] |
[(left | right | full out ) join table2
on(table1.column_name=table2.column_name)]
[cross join table3] |
[natural join table3] |
[join table3 using(字段名)] |
[join table3 on(table.column_name=table3.column_name)] |
[(left | right | full out ) join table3
on(table2.column_name=table3.column_name)]...;
复制代码
内连接和外连接
内连接(Inner join)
在SQL99规范中,内连接只返回满足连接条件的数据.
外连接(Outer join)
左外连接(Left Outer Join)
两个表在连接过程中除返回满足连接条件的行为外,还返回左表中不满足条件的行为，这种连接称为左外连接.
右外连接(Right Outer Join)
两个表在连接过程中除返回满足连接条件的行为外,还返回右表中不满足条件的行为,这种连接称为右外连接.
满外连接(Full Outer Join)
Oracle9开始新增功能,两个表在连接过程中除返回满足连接条件的行为外，还返回两个表中不满足条件的所有行为，这种连接称为满外连接.
子查询(Sub Query)
子查询子查询在主查询前执行一次
主查询使用子查询的结果
select 字段列表
from table
where 表达式 operator (select 字段列表 from table);
使用子查询注意事项
在查询时基于未知时应考虑使用子查询
子查询必须包含在括号内
将子查询放在比较运算符的右侧,以增强可读性.
除非进行Top-N分析,否则不要再子查询中使用Order by子句
对单行子查询使用单行运算符
对多行子查询使用多行运算符
单行子查询
单行子查询只返回一行记录
对单行子查询可使用单行记录比较运算符
=--------------等于
>--------------大于
>=------------大于等于
<--------------小于
<=--------------小于等于
<>--------------不等于
select * from emp
where sal>(select sal from emp where empno=7000);
子查询空值/多值问题
如果子查询未返回任何行,则主查询页不会返回任何结果
如果子查询返回单行结果,则为单行子查询,可以在主查询中对其使用相应的单行记录比较运算符
如果子查询返回多行结果,则为多行子查询,此时不允许对其使用单行记录比较运算符
多行子查询
多行子查询返回多行记录
对多行子查询只能使用多行记录比较运算符
in--------------等于列表中的任何一个
any--------------和子查询返回的任意一个值比较
all--------------和子查询返回的所有值比较
select * from emp
where sal>any(select avg(sal) from emp group by deptno);
select * from emp
where sal>all(select avg(sal) from emp group by deptno);
select * from emp
where job in(select job from emp where ename='martin' or ename='ssss');
TopN查询
在oracle中通常采用子查询的方式来实现Top n查询
复制代码
select 字段列表
from(select 字段列表 from table order by 排序字段)
where rownum <=n;
------------------------------------------------------------
select *
from(select * from emp order by sal desc)
where rownum <=5;
复制代码
oracle 截取字符(substr)，检索字符位置(instr) case when then else end语句使用 收藏
常用函数：substr和instr
1.SUBSTR（string,start_position,[length]） 求子字符串，返回字符串
解释：string 元字符串
start_position 开始位置（从0开始）
length 可选项，子字符串的个数
For example:
substr("ABCDEFG", 0); //返回：ABCDEFG，截取所有字符
substr("ABCDEFG", 2); //返回：CDEFG，截取从C开始之后所有字符
substr("ABCDEFG", 0, 3); //返回：ABC，截取从A开始3个字符
substr("ABCDEFG", 0, 100); //返回：ABCDEFG，100虽然超出预处理的字符串最长度，但不会影响返回结果，系统按预处理字符串最大数量返回。
substr("ABCDEFG", -3); //返回：EFG，注意参数-3，为负值时表示从尾部开始算起，字符串排列位置不变。
2.INSTR（string,subString,position,ocurrence）查找字符串位置
解释：string：源字符串
subString：要查找的子字符串
position：查找的开始位置
ocurrence：源字符串中第几次出现的子字符串
For example:
INSTR('CORPORATE FLOOR','OR', 3, 2)中，源字符串为'CORPORATE FLOOR', 目标字符串为'OR'，起始位置为3，取第2个匹配项的位置；返回结果为 14 '
浙公网安备 33010602011771号