Oracle中 表空间，数字函数，日期函数，转换函数，同义词，反向键索引

grant create tablespace to sll
------------------------
create tablespace y2164tabspace
DATAFILE 'C:\app\linlin\oradata\orcl\y2164tabspace.dbf' size 10m
AUTOEXTEND on next 32m maxsize unlimited
select tablespace_name from user_tablespaces


create  tablespace y2164tabspace2
DATAFILE 'C:\app\linlin\oradata\orcl\y2164tabspace2.dbf' size 10m

create  tablespace y2164tabspace3
DATAFILE 'C:\app\linlin\oradata\orcl\y2164tabspace3.dbf'  size 10m,
'C:\app\linlin\oradata\orcl\y2164tabspace3-1.dbf'
 size 10m
==========================================================
----------字符函数
select Lower('Abc') from dual;
select upper('Abc') from dual;
select initcap('abc') from dual;

select concat('abc','end') from dual;


select substr('Spring Boot实战',3)from dual;
select substr('Spring Boot实战',3,8)from dual;

select length('abc') from dual;
select lengthb('中国人')from dual;

select instr('Srpingboot springcloud',3,2) from dual;

select instr('Srpingboot springcloud springmvc','ing',-3,2) from dual;

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

select trim('A      bv   ') from dual;
select trim('a' from 'ahappya') from dual;

--------日期函数
select Months_Between(sysdate,"TO_DATE"('1998-09-24','yyyy-MM-dd'))from dual;
select add_months(sysdate,1) from dual;

---------------
select userenv('language') from dual;

select floor(sysdate-to_date('1998-09-24','yyyy-MM-dd')) from dual;
select sysdate from dual;

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

select round(12.45,3) from dual;
select trunc(12.49,1) from dual;

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

select * from emp;

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

select ename,decode(deptno,10,'sales',20,'money',30,'yanfa','baojiebu') from emp;

--sys_guid函数
select sys_guid()  from dual;
-----------创建私有同义词
create or replace synonym emp for employee;
-----创建共有同义词
create public synonym employee for scott.employee

grant select on employee to sll

--以sll登录
select * from employee
----删除同义词（共有，私有）
drop synonym emp;
drop public synonym employee;
---------------------------------------------
---在订单表中国年，只允许当前员工查看自己的订单记录
--当前用户 scott
--获取create view权限
create or replace view v_myorders
as 
select * from orders
where sales req id=(
select empno from employee
where ename=(
select user from dual
)
);

select  * from  v_myorders
select  * from  dept;
--------------------------------
--在员工表中，普通职员只允许看姓名和部门列
-----------

create or replace view v_employee
as 
select empno,ename,e.deptno,dname from employee e
inner join dept d on e.deptno=d.deptno

-----------------------------------------------------------
--索引
------------------------------------

---反向键索引
create unique index idx_empno on employee(empno) reverse

alter table employee add constraint PK_empno
primary key(empno)
using index idx_empno

select * from employee where empno=7900