Oracle 总结_oracle初学者技术总结-CSDN博客

本文链接：https://blog.csdn.net/weixin_36024609/article/details/78018915

--日期和字符转换函数用法
--(to_date,to_char)
--日期转换为字符串
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual; 
--获取时间的年
select to_char(sysdate,'yyyy') as nowYear   from dual;  
--获取时间的月       
select to_char(sysdate,'mm')as nowMonth from dual; 
--获取时间的日  
select to_char(sysdate,'dd')as nowDay    from dual; 
--获取时间的时      
select to_char(sysdate,'hh24')as nowHour   from dual;     
--获取时间的分 
select to_char(sysdate,'mi')as nowMinute from dual;    
--获取时间的秒
select to_char(sysdate,'ss')as nowSecond from dual;  、

--求某天是星期几
 select to_char(to_date('1998-04-27','yyyy-mm-dd'),'day') from dual; 
--设置日期语言      
   ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';   
--两个日期间的天数 
  select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;  
--时间为null的用法-------------
   select id, active_date from table1      
   UNION      
   select 1, TO_DATE(null) from dual; 
--在当前月份加一个月
select ADD_MONTHS(SYSDATE, 1) from dual;

--月份差
--当前
select months_between(SYSDATE,to_date('1998-4-27','yyyy-MM-dd')) from dual;
--计算时间差
select months_between(to_date('1998-4-27','yyyy-MM-dd'),to_date('1995-7-30','yyyy-MM-dd')) from dual;
--一年的第一天
select TO_CHAR(SYSDATE,'DDD'),sysdate from dual
--返回日期列表中最晚日期
 select greatest('01-1月-04','04-1月-04','10-2月-04') from dual
--计算时间差
--时间差-年
select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))/365) as spanYears from dual
--时间差-月
select ceil(months_between(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanMonths from dual     
--时间差-天   
select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanDays from dual       
--时间差-时      
select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24) as spanHours from dual      
--时间差-分   
select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60) as spanMinutes from dual    
--时间差-秒
select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60*60) as spanSeconds from dual 
--查找月的第一天,最后一天
SELECT Trunc(Trunc(SYSDATE, 'MONTH') - 1, 'MONTH') First_Day_Last_Month,
       Trunc(SYSDATE, 'MONTH') - 1 / 86400 Last_Day_Last_Month,
       Trunc(SYSDATE, 'MONTH') First_Day_Cur_Month,
       LAST_DAY(Trunc(SYSDATE, 'MONTH')) + 1 - 1 / 86400 Last_Day_Cur_Month
   FROM dual;



--字符函数
--1.字符串截取
select substr('abcdef',1,3) from dual
--2.查找子串位置
select instr('abcfdgfdhd','fd') from dual
select instr('Springboot springclound' ,'ing',-3,2) from dual
--3.字符串连接
select 'HELLO'||'hello world' from dual;
--4.去掉字符串中的空格
    select ltrim(' abc') s1,
    rtrim('zhang ') s2,
    trim(' zhang ') s3 from dual

select trim('a' from 'ahappy') from dual
--5.去掉前导和后缀
    select trim(leading 9 from 9998767999) s1,
    trim(trailing 9 from 9998767999) s2,
    trim(9 from 9998767999) s3 from dual;
--6.返回字符串首字母的Ascii值
   select ascii('a') from dual
--7.返回ascii值对应的字母
   select chr(97) from dual
--8.计算字符串长度 
   select length('abcdef') from dual;
--字节
select lengthb('小仙女') from dual;
--9.initcap（首字母变大写） ,lower(变小写),upper(变大写)
   select lower('ABC') s1,  
       upper('def') s2, 
       initcap('efg') s3
   from dual; 

--数字函数
--1.取整函数（ceil 向上取整,floor 向下取整）
   select ceil(66.6) N1,floor(66.6) N2 from dual;
--2. 取幂(power) 和 求平方根(sqrt)
   select power(3,2) N1,sqrt(9) N2 from dual;
--3.求余
   select mod(9,5) from dual;
--4.返回固定小数位数 (round:四舍五入，trunc:直接截断)
   select round(66.667,2) N1,trunc(66.667,2) N2 from dual; 
--5.返回值的符号(正数返回为1,负数为-1)
   select sign(-32),sign(293) from dual;

--转换函数
--to_char()[将日期和数字类型转换成字符类型
--1.
select to_char(sysdate) s1,
        to_char(sysdate,'yyyy-mm-dd') s2,
        to_char(sysdate,'yyyy') s3,
        to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') s4,
        to_char(sysdate, 'hh24:mi:ss') s5,
        to_char(sysdate,'DAY') s6 
    from dual;
--2.
select sal,to_char(sal,'＄99999') n1,to_char(sal,'＄99,999') n2 from emp



--3.to_date()[将字符类型转换为日期类型] 
insert into emp(empno,hiredate) values(8000,to_date('2004-10-10','yyyy-mm-dd'));
--隐式转换

  
--4. to_number() 转换为数字类型   //以数字显示的小时数
    select to_number(to_char(sysdate,'hh12')) from dual; 



--其它函数
--1.user: 返回登录的用户名称 
    select user from dual;

--2.vsize: 返回表达式所需的字节数
    select vsize('HELLO') from dual;

--3.3.nvl(ex1,ex2): 　
--ex1值为空则返回ex2,否则返回该值本身ex1（常用） 
    select comm,nvl(comm,0) from emp;
--nvl2
select comm,nvl(comm,0) from emp;

--4.nullif(ex1,ex2): 
    --值相等返空，否则返回第一个值
    select nullif(sal,comm),sal,comm from emp;

--5.coalesce: 　
    --返回列表中第一个非空表达式
    select comm,sal,coalesce(comm,sal,sal*10) from emp;

--1.创建自动增长表空间
CREATE tablespace worktbs
datafile 'worktbs.DBF'
size 10M autoextend on;

--删除表空间
drop tablespace worktbs

--创建序列
--从序,10开始，每次增加1，最大为20000，不循环，在增加会报错，缓存30个序列号
CREATE sequence sequence_name
START WITH 10
INCREMENT BY 1
maxvalue 20000
nocycle 
cache 30;

--删除序列
drop sequence sequence_name
drop sequence sequence_name

--2017年9月15日10:56:40
--授权给用户来创建表空间
GRANT create tablespace to LJL
--授权给用户来删除表空间
GRANT drop tablespace to LJL


--创建私有同义词
create or replace SYNONYM emp for employee
--创建公有同义词
CREATE PUBLIC SYNONYM EMPLOYEE for A_hr.employee

GRANT select ON EMPLOYEE TO LJL;
--以ljl用户登录，
select * from EMPLOYEE
--删除同义词
drop SYNONYM emp;
DROP PUBLIC SYNONYM EMPLOYEE;

--其他用户可以访问ljl用户下的employee表
GRANT select on LJL.employee to PUBLIC
CREATE PUBLIC SYNONYM EMPLOYEE for LJL.employee


select * from EMPLOYEE

--创建表空间
create tablespace y2164tablespace
datafile 'F:\oracle11g\oradata\orcl\y2164tablespace.DBF' size 10M
autoextend on next 32M maxsize unlimited

--查看所有表空间
select tablespace_name from user_tablespaces

--创建表空间（基础）
create  tablespace y2164tablespace2
DATAFILE 'F:\oracle11g\oradata\orcl\y2164tablespace2.DBF' size 10m

--同时创建多个表空间
create  tablespace y2164tabspace3
DATAFILE 'F:\oracle11g\oradata\orcl\y2164tablespace3_1.DBF'  size 10m,
'F:\oracle11g\oradata\orcl\y2164tablespace3_2.DBF'
size 10m

--删除表空间
drop tablespace y2164tablespace3_1.DBF
--删除表空间同时清楚物理文件
drop tablespace y2164tablespace3_1.DBF including contents and datafiles
--在表空间内创建表
CREATE table role
(rid NUMBER primary key not null, rname nvarchar2(32))tablespace y2164tablespace

--序列
--创建序列
CREATE sequence seq_num


--删除序列
drop sequence seq_num

--索引
--创建唯一索引
--薪水级别表中，为级别编号列创建唯一索引
create UNIQUE INDEX index_unique_grade on salgrade(grade)
select * from employee

--创建反向键索引
--在员工表中，为员工编号列创建反向键索引
CREATE INDEX index_reverse_empno on employee(empno) reverse

--创建位图索引
--在员工表中，为工种列创建位图索引
create bitmap INDEX index_bit_job on employee(job)

--删除索引
drop INDEX index_unique_grade

--重建索引
--将反向键索引更改为B树索引
alter INDEX index_reverse_empno rebuild noreverse

--分区表
--创建范围分区
drop table sales1
create table sales1
(
sales_id number,
product_id varchar2(5),
sales_date date not null
)
--select * from sales1;
insert into sales1 values(1,'哈',to_date('2013-05-5','yyyy-MM-dd'));
insert into sales1 values(2,'哈1',to_date('2013-08-5','yyyy-MM-dd'));
insert into sales1 values(3,'哈2',to_date('2013-02-5','yyyy-MM-dd'));
insert into sales1 values(4,'哈3',to_date('2013-12-5','yyyy-MM-dd'));
partition by range(sales_date)
(
partition p1 values less than(to_date('2013-04-1','yyyy-MM-dd')),
partition p2 values less than(to_date('2013-07-1','yyyy-MM-dd')),
partition p3 values less than(to_date('2013-10-1','yyyy-MM-dd')),
partition p4 values less than(to_date('2014-01-1','yyyy-MM-dd')),
partition p5 values less than(maxvalue)
);
--查看第三条数据
select * from sales1 partition(p3)

--删除第三条数据
delete from sales1 partition(p3)

select * from sales1

--间隔分区
select * from sales2
create table sales2
(
sales_id number,
product_id varchar2(5),
sales_date date not null
)

partition by range(sales_date)
interval(numtoyminterval(3,'Month'))
(partition p1 values less than (to_date('2013-04-1','yyyy-MM-dd')));
--插入数据
INSERT into sales2 values(1,'a',to_date('2013-01-01'),10,'1')
--获得分区情况
select table_name,partition_name
from user_tab_partitions
where table_name=upper('sales2')
--查看输出结果，系统自动根据输入情况创建新分区（sys_P82）

--查询分区数据
select * from sales2 partition(LJL)

select * from SALGRADE
--索引
--创建唯一索引
--薪水级别表中，为级别编号列创建唯一索引
create UNIQUE INDEX index_unique_grade on salgrade(grade)

--创建反向键索引
--在员工表中，为员工编号列创建反向键索引
CREATE INDEX index_reverse_empno on employee(empno) REVOKE