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

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

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;