Oracle常用函数和表空间,序列

EMP表:

--字符函数:
select SUBSTR('Springboot Spring SpringMVC',2,9)from dual;

select length('张利辉')from dual;

select lengthb('张利辉')from dual;

select userenv('language')from dual;

select instr('Springboot Springcloud SpringMvc','ing')from dual;

select instr('Springboot Springcloud SpringMvc','ing',3,2)from dual;

select instr('Springboot Springcloud hehe','ing',-3,2) from dual; --4

select rpad('Happy',10,'*')from dual;

select lpad('Happy',10,'*')from dual;

select trim(' A B C')from dual;

select trim('a' from 'aaahappyaaaaaa')from dual;

----日期函数
select floor("MONTHS_BETWEEN"(SYSDATE,"TO_DATE"('1999-09-29', 'yyyy-MM-dd')))from dual;

select floor(sysdate-"TO_DATE"('1999-09-29', 'yyyy-MM-dd'))from dual;

select "ADD_MONTHS"(sysdate, 1)from dual;

select sysdate from dual;

select to_char(sysdate,'yyyy-MM-dd hh24:mi:ss')from dual;

---nvl nvl2虑空函数

select to_char(sal,'L9,999.99') from emp;

select sal*12+nvl(comm,0) from emp;

select sal*12+nvl2(comm,comm+2000,0) from emp;

----decode 函数

select ename,decode(deptno,10,'开发部',20,'测试部',30,'财务部','保洁部') from EMP

表空间:

创建文件:

     create tablespace y2165tabspace
          datafile 'E:\app\zhanglihui\y2165tabspace_1.dbf' size 10M,
          'E:\app\zhanglihui\y2165tabspace_2.dbf' size 10M autoextend on next 32M maxsize unlimited Oracle常用函数和表空间,序列

查询当前用户下的表空间的文件:

select tablespace_name from user_tablespaces;

删除表空间的同时清除物理文件:

drop tablespace y2165tabspace including contents and datafiles

--Oracle没有自增列，用的就是序列
create table dept
(
   deptno number primary key not null,
   deptname nvarchar2(32)
) tablespace y2165tabspace

create table emp
(
   empno number primary key not null,
   empname nvarchar2(32)
) tablespace y2165tabspace

insert into dept values(1,'开发部')
commit
select * from dept

   insert into emp values(1,'微冷的雨')
   insert into emp values(seq_num.nextval,'微冷的雨')
commit
select * from emp

insert into dept values(seq_num.nextval,'开发部')

select seq_num.nextval from dual;

Oracle常用函数和表空间,序列

EMP表:

--字符函数: select SUBSTR('Springboot Spring SpringMVC',2,9)from dual;

----decode 函数

select ename,decode(deptno,10,'开发部',20,'测试部',30,'财务部','保洁部') from EMP

相关推荐

--字符函数:
select SUBSTR('Springboot Spring SpringMVC',2,9)from dual;