Oracle常用函数 一、Oracle 表空间一个数据库可以有多个表空间，一个表空间里可以有多个表。表空间就是存多个表的物理空间；可以指定表空间的大小位置等。创建表空间create tablespace ts1 datafile 'C:\tablespace\ts1.dbf' size 50M;自动扩展大小create tablespace ts2 datafile 'C:\tablespace\ts2.dbf' size 50M autoextend on next 10M;设置最大空间create tablespace ts3 datafile 'C:\tablespace\ts3.dbf' size 50M autoextend on next 10M maxsize 1024M;更改用户默认表空间alter database default tablespace ts1;表空间改名alter tablespace ts1 rename to tss1;删除表空间drop tablespace ts2 including contents and datafiles;二：虚拟表dual 表Dual 表是sys 用户下的一张虚表；提供一些运算和日期操作时候用到；例如，查询系统日期时间select sysdate from dual;三、序列序列作为数据库里的对象，主要作用是生成唯一的主键值；创建序列create sequence 序列名称； 重要属性： 序列名称.currval 当前值nextval 下一个值 指定初始值： start with xx序列属性Minvalue maxvalue 最大值和最小值默认最小值1 最大值10 的27 次方； Increment by 属性序列增长步长默认值1 Cycle 设置循环； （了解即可，不使用）； Cache 缓存设置； 一次获取序列的多个连续值，默认20 ，放到内存中，方便下次快速获取；四、CRUD1、表的CRUD创建表create table 表名 ( 字段名1 字段类型 默认值 是否为空 ， 字段名2 字段类型 默认值 是否为空， 字段名3 字段类型 默认值 是否为空， … );示例：create table T_BOOK_LOG ( operusers VARCHAR2(10) not null, optertype VARCHAR2(10), optertime DATE )修改表名rename 旧表名 to 新表名；示例：rename t_book to t_books;删除表删除所有数据，会删除表结构drop table 表名；示例：drop table T_BOOKs;修改表添加新字段alter table 表名 add(字段名 字段类型 默认值 是否为空);示例：alter table t_book add(booknum number(6) default 0 null);字段添加注释comment on column 表明.字段 is '注释内容';示例：comment on column t_book.booknum is '书本数量';修改字段类型属性alter table 表名 modify (字段名 字段类型 默认值 是否为空);示例：alter table t_book modify (booknum varchar2(20) default 'hello');修改字段名alter table 表名 rename column 列名 to 新列名;示例：alter table t_book rename column booknum to remark;删除字段alter table 表名 drop column 字段名；示例：alter table t_book drop column remark;2、数据的CRUDinsert给指定列插入数据：insert into dept(deptno,dname) values(50,'xx'); 插入全部列数据：insert into dept(deptno,dname,loc) values(60,'xx','lll'); 简写insert into dept values(70,'xxx','llll');update更新指定数据：update dept set dname='司法部' where deptno=50; update dept set dname='司法部' ,loc='china' where deptno=50;delete删除指定数据：delete from dept where deptno=70; 删除指定条件的数据：delete from dept where deptno>40;truncatetruncate是一次性删掉所有数据，不删除表结构。注意：如果表中有identity产生的自增id列，truncate后，会恢复初始值。truncate table 表名；查询所有select * from emp;指定字段查询select ename,sal from emp;where 条件查询select * from emp where sal>=800; select * from emp where sal>=1500 and job='SALESMAN';Distinct去重复记录Group by分组查询select job,count(ename) as num from EMP t group by job;Having 过滤分组select job,count(ename) as num from EMP t group by job having count(ename)>=2;Order by 排序select * from emp order by sal desc;子查询查询出基本工资大于平均工资的员工select * from emp where sal>(select avg(sal) from emp)union并集（去重复）select * from t_user1 union select * from t_user2;union all并集（不去重）select * from t_user1 union all select * from t_user2;intersect交集select * from t_user1 intersect select * from t_user2;minus差集select * from t_user1 minus select * from t_user2;inner内连接select * from emp t,dept d where t.deptno=d.deptno;和这个一样，inner join效率更高select * from emp e inner join dept d on e.deptno=d.deptno;left join左外连接select * from emp e left join dept d on e.deptno=d.deptno;right join右外连接select * from emp e right join dept d on e.deptno=d.deptno;五、数据类型1、字符串类型及函数字符类型分3 种，char(n) 、varchar(n)、varchar2(n) ；char(n)：固定长度字符串，假如长度不足n，右边空格补齐；varchar(n)：可变长度字符串，假如长度不足n，右边不会补齐；varchar2(n)：可变长度字符串，Oracle 官方推荐使用，向后兼容性好；char(n) VS varchar2(n)： char(n)查询效率相对较高，varchar2(n)存储空间相对较小；字符串常用方法lpad() 向左补全字符串：select lpad(stuno,6,'0') from t_user3; rpad() 向右补全字符串：select rpad(stuno,6,'0') from t_user3; lower() 返回字符串小写：select lower(userName) from t_user3; upper() 返回字符串大写：select upper(userName) from t_user3; initcap() 单词首字符大写：select initcap(userName) from t_user3; length() 返回字符串长度：select length(password) from t_user3; substr() 截取字符串：select substr(userName,1,2) from t_user3; instr() 获取字符串出现的位置：select instr(password,'23',2,2) from t_user3; ltrim() 删除左侧空格:select ltrim(userName) from t_user3; rtrim() 删除右侧空格:select rtrim(userName) from t_user3; trim() 删除两侧空格:select trim(userName) from t_user3; concat() 串联字符串：select concat(userName,password) from t_user3; reverse() 反转字符串：select reverse(userName) from t_user3;2、数值类型number：是oracle 中的数据类型；number(precision,scale)；Precision，scale 均可选；Precision 代表精度，sacle 代表小数位的位数；Precision 范围[1,38] scale 范围[-84,127]举例： 12345.678 Precision 是8 scale 是3；数值类型常用方法abs() 求绝对值；select abs(n1) from t_number where id=1; round() 四舍五入：select round(n1,2) from t_number where id=1; ceil() 向上取整：select ceil(n1) from t_number where id=2; floor 向下取整：select floor(n1) from t_number where id=2; Mod()取模：select mod(5,3) from dual; Sign()正负性：select sign(n1) from t_number where id=1; Sqrt() 求平方根：select sqrt(9) from dual; Power()求乘方：select power(2,3) from dual; Trunc()截取：select trunc(123.456,2) from dual; To_char() 格式化数值：常见的字符匹配有0、9、，、$、FM、L、CTo_char() 格式化数值To_char() 格式化数值：常见的字符匹配有0、9、，、$、FM、L、C select to_char(123.45,'0000.000') from dual; select to_char(123.45,'9999.999') from dual; select to_char(123123,'99,999,999.99') from dual; select to_char(123123.3,'FM99,999,999.99') from dual; select to_char(123123.3,'$99,999,999.99') from dual; select to_char(123123.3,'L99,999,999.99') from dual; select to_char(123123.3,'99,999,999.99C') from dual;3、日期类型Date 和timestamp（时间戳）Date ：包含信息century（世纪信息） year 年month 月day 日hour 小时minute 分钟second 秒Timestamp ：一般用于日期时间要求非常精确的情况，精确到毫秒级；insert into t_date values(1,sysdate,systimestamp);日期类型常用方法select sysdate from dual; select systimestamp from dual; Add_months 添加月份select add_months(d1,2) from t_date where id=1; Last_day 返回指定日期月份的最后一天select last_day(d1) from t_date where id=1; update t_date set d3=to_date('2016-12-20','YYYY-MM-DD') where id=1; update t_date set d3=to_date('2016-12-20 18:31:34','YYYY-MM-DD HH24:MI:SS') where id=1; Months_between 返回两个日期的相差月数select months_between(d1,d3) from t_date where id=1; next_day 返回特定日期之后的一周内的日期：select next_day(d1,2) from t_date where id=1;Trunc 截取日期select trunc(d1,'YYYY') from t_date where id=1; select trunc(d1,'MM') from t_date where id=1; select trunc(d1,'DD') from t_date where id=1; select trunc(d1,'HH') from t_date where id=1; select trunc(d1,'MI') from t_date where id=1;Extract 返回日期的某个域select extract(year from sysdate) from dual; select extract(month from sysdate) from dual; select extract(day from sysdate) from dual; select extract(Hour from systimestamp) from dual; select extract(minute from systimestamp) from dual; select extract(second from systimestamp) from dual;To_char 将日期转换成字符串select to_char(d1,'YYYY-MM-DD') from t_date where id=1; select to_char(d1,'YYYY-MM-DD HH24:MI:SS') from t_date where id=1;4、其他常用处理函数常用的聚合函数Max 求最大值select max(sal) from emp ;Min 求最小值select min(sal) from emp ;Avg 求平均值select avg(sal) from emp ;Sum 求和select sum(sal) from emp ;Count 统计记录数select count(ename) from emp ;Nvl 空值处理select ename,nvl(sal,0) from emp;rownum 分页select * from (select a.*,rownum rn from (select * from emp) A where rownum<=10) where rn>5;Oracle 中的运算select 2+1 from dual; select 2-1 from dual; select 2*1 from dual; select 2/1 from dual;Between and条件判断式Between and 范围查询：select * from emp where sal between 900 and 1500; select * from emp where sal>=900 and sal<=1500;In 集合范围select ename,hiredate from emp where ename in (select distinct ename from bonus)Like 模糊查询select * from emp where ename like '%M%' select * from emp where ename like 'M%' select * from emp where ename like '_M%'