Oracle常用命令

一、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;

7、Oracle 虚拟表dual表

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

select sysdate from dual;

二、Oracle序列

1、创建序列

create sequence 序列名称；

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

2、序列属性

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

三、Oracle表操作

1、插入insert语句

（1）给指定列插入数据

insert into dept(deptno,dname) values(50,'xx');

（2）插入全部列数据

insert into dept(deptno,dname,loc) values(60,'xx','lll');  
简写 insert into dept values(70,'xxx','llll');

2、更新update语句

（1）更新指定数据

update dept set dname='司法部' where deptno=50;
update dept set dname='司法部' ,loc='china' where deptno=50;

3、删除delete语句

（1）删除指定数据

delete from dept where deptno=70;

（2）删除指定条件的数据

delete from dept where deptno>40;

4、查询select语句

（1）查询所有

select * from emp;

（2）指定字段查询

select ename,sal from emp;

（3）加where条件

select * from emp where sal>=800;  
select * from emp where sal>=1500 and job='SALESMAN';

（4）Distinct去重复记录；

（5）Group by分组查询

select job,count(ename) as num from EMP t group by job; 

（6）Having过滤分组

select job,count(ename) as num from EMP t group by job having count(ename)>=2;

（7）Order by 排序

select * from emp order by sal desc;

（8）子查询：查询出基本工资大于平均工资的员工

select * from emp where sal>(select avg(sal) from emp)

（9）联合查询：

①并集（去重复）：

select * from t_user1
union
select * from t_user2;

②并集：

select * from t_user1
union all
select * from t_user2;

③交集：

select * from t_user1
intersect
select * from t_user2;

④差集：

select * from t_user1
minus
select * from t_user2;

⑤内连接：

select * from emp t,dept d where t.deptno=d.deptno;

类似

select * from emp e inner join dept d on e.deptno=d.deptno; 

inner可以省略；

⑥外连接：

Ⅰ.左外连接

select * from emp e left join dept d on e.deptno=d.deptno;

Ⅱ.右外连接

select * from emp e right join dept d on e.deptno=d.deptno;

四、Oracle数据类型及函数

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)存储空间相对较小；

select length(userName) as charlength,length(password) as varchar2length from t_user3;

（1）lpad() 向左补全字符串

select lpad(stuno,6,'0') from t_user3;

（2）rpad() 向右补全字符串

select rpad(stuno,6,'0') from t_user3;

（3）lower() 返回字符串小写

select lower(userName) from t_user3;

（4）upper() 返回字符串大写

select upper(userName) from t_user3;

（5）initcap() 单词首字符大写

select initcap(userName) from t_user3;

（6）length() 返回字符串长度

select length(password) from t_user3;

（7）substr() 截取字符串

select substr(userName,1,2) from t_user3;

（8）instr() 获取字符串出现的位置

select instr(password,'23',2,2) from t_user3;

（9）ltrim() 删除左侧空格

select ltrim(userName) from t_user3;

（10）rtrim() 删除右侧空格

select rtrim(userName) from t_user3;

（11）trim() 删除两侧空格

select trim(userName) from t_user3;

（12）concat() 串联字符串

select concat(userName,password) from t_user3;

（13）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；

常用方法

(1)abs() 求绝对值

select abs(n1) from t_number where id=1;

(2)round() 四舍五入

select round(n1,2) from t_number where id=1;

(3)ceil() 向上取整

select ceil(n1) from t_number where id=2;

(4)floor 向下取整

select floor(n1) from t_number where id=2;

(5)Mod()取模

select mod(5,3) from dual;

(6)Sign()正负性

select sign(n1) from t_number where id=1;

(7)Sqrt() 求平方根

select sqrt(9) from dual;

(8)Power()求乘方

select power(2,3) from dual;

(9)Trunc()截取

select trunc(123.456,2) from dual;

(10)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);

下面重点讲date类型的常用函数：

select sysdate from dual;
select systimestamp from dual;

(1)Add_months 添加月份

select add_months(d1,2) from t_date where id=1;

(2)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;

(3)Months_between 返回两个日期的相差月数

 select months_between(d1,d3) from t_date where id=1;

(4)next_day 返回特定日期之后的一周内的日期

select next_day(d1,2) from t_date where id=1;

(5)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;

(6)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;

(7)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;