Oracle常用函数

admin
2022-11-24 / 0 评论 / 141 阅读 / 正在检测是否收录...
温馨提示:
本文最后更新于2022年12月07日,已超过736天没有更新,若内容或图片失效,请留言反馈。

一、Oracle 表空间

一个数据库可以有多个表空间,一个表空间里可以有多个表。表空间就是存多个表的物理空间;
可以指定表空间的大小位置等。

  1. 创建表空间

    create tablespace ts1 datafile 'C:\tablespace\ts1.dbf' size 50M;
  2. 自动扩展大小

    create tablespace ts2 datafile 'C:\tablespace\ts2.dbf' size 50M autoextend on next 10M;
  3. 设置最大空间

    create tablespace ts3 datafile 'C:\tablespace\ts3.dbf' size 50M autoextend on next 10M maxsize 1024M;
  4. 更改用户默认表空间

    alter database default tablespace ts1;
  5. 表空间改名

    alter tablespace ts1 rename to tss1;
  6. 删除表空间

    drop tablespace ts2 including contents and datafiles;

二:虚拟表dual 表

Dual 表是sys 用户下的一张虚表;提供一些运算和日期操作时候用到;

例如,查询系统日期时间

select sysdate from dual;

三、序列

序列作为数据库里的对象,主要作用是生成唯一的主键值;

  1. 创建序列

    create sequence 序列名称;
    重要属性: 序列名称.currval 当前值nextval 下一个值
    指定初始值: start with xx
  2. 序列属性

    Minvalue maxvalue 最大值和最小值默认最小值1 最大值10 的27 次方;
    Increment by 属性序列增长步长默认值1
    Cycle 设置循环; (了解即可,不使用);
    Cache 缓存设置; 一次获取序列的多个连续值,默认20 ,放到内存中,方便下次快速获取;

四、CRUD

1、表的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、数据的CRUD

insert

给指定列插入数据: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;

truncate

truncate是一次性删掉所有数据,不删除表结构。注意:如果表中有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、C

To_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%'
2

评论 (0)

取消