--日期和字符转换函数用法
--(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