Oracle学习笔记

1、创建表空间

create tablespace duJian
datafile 'F:\JSJSJSJSJSJSJS\5《Oracle数据库》\duJian.dbf'
size 10M
autoextend on next 5M maxsize unlimited

2、创建用户

create user duJian
identified by orcl
default tablespace duJian;

3、用户授权

GRANT connect to duJian;
GRANT resource,dba to duJian;

导入dmp文件（在cmd初始视图下写）：

imp root/orcl  file=F:\杜健\activity-1.4.dmp full=y ignore=y

4、根据序列进行设置主键自动增长

insert into student (id,name,classid)values(SQE_STUID.NEXTVAL,'嘟嘟',3);

4、Date操作
select sysdate from dual：当前系统时间，dual为虚表，sysdate为当前系统时间
select Last_day(sysdate) from dual：获取当前月最后一天
select to_date(‘2017-10-01’,’yyyy-mm-dd’) from dual：将字符串转化成Date类型时间:12小时制
select to_date(‘1990-12-12 12:13:14’,’yyyy-mm-dd hh24:mi:ss’) from dual：将字符串转化成Date类型时间:24小时制
select to_char(t.hiredate,’yyyy-mm-dd’) from teacher t：将日期转换成字符串

select months_between(sysdate,to_date(‘2017-02-01’,’yyyy-mm-dd’)) from dual：计算相隔月份
select sysdate-to_date(‘2017-02-01’,’yyyy-mm-dd’) from dual：计算相隔天数，直接进行减法运算

select next_day(sysdate,3) from dual :得到本星期或者当前星期之后的第一个星期的第三天

5、ROUND函数与TRUNC函数
select round(123.456,2) from dual：计算四舍五入保留两位小数的值，参数“2”为保留的小数位数
select round(sysdate,’month’) from dual：计算月份的四舍五入，如当前天数大于15则月份进一，否则回到当前月一号。

select trunc(59.897,2) from dual：截断小数前两位，得到59.89

6、求最大值

二、字符函数
（1） select substr(‘abcde’,2,2) from dual：得到‘bc’，即截取两个字符。
（2）select instr(‘abcded’,’d’,2) from dual：从第二个字符找‘d’，得到4，即为第一个‘d’的索引
（3）select length (‘abcded’) from dual：长度

CHR/ASCII转换
（1）chr(97)–得到‘a’
（2）ascii(‘a’)–得到97

Initcap：将首字母转换成大写
select Initcap (‘abcded’) from dual：得到‘Abcded’

pad函数：填充
左填充：select lpad(‘9001’,10,’0’) from dual：填充后一共10个字符，除原本的‘9001’外，其他在左侧用0填充；
结果：0000009001

Trim函数：去掉左右空格
select ‘wawawa’||trim(’ abcde ‘) from dual：||在oracle中表示连接字符串
结果：wawawaabcde

translate函数：逐个替换
select translate(‘abcde’,’ace’,’135’) from dual
结果：1b3d5
decode函数：
select u_id,u_name, decode(u_gender,’0’,’男’,’1’,’女’)from t_user;

concat函数：
select Concat(‘abc’,’123’) from dual
结果：abc123
大小写转换：
Upper函数——将字符串s全部转换为大写
select Upper(‘JKkdjfdsklf’) from dual
Lower函数——将字符串s全部转换为小写
select Lower(‘JKkdjfdsklf’) from dual

三、数学函数
mod函数：求余数
select mod(5,2) from dual
结果：1
Round函数——求四舍五入
select round(256.45,1)from dual
结果：256.5
Trunc函数——截取,不五入,
select Trunc(256.45,1)from dual
结果：256.4
Abs函数——取绝对值
select abs(-256.45)from dual
结果：256.45
Ceil函数——取比其大的最小的一个整数
select ceil(256.33)from dual
结果：257
Floor函数——取比其小的最大的一个整数
select Floor(256.33)from dual

四、转换函数
to_char函数：
将当前时间转换成字符串（12小时制）
select to_char(sysdate,’yyyy-mm-dd hh:mi:ss’) from dual
将当前时间转换成字符串（24小时制）
select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’) from dual

To_date函数——字符串转成日期
To_number函数——转数字，如果有英文则会报错
select To_number(‘12345’) from dual

–常规函数
NVL函数——如果 第一个字段值为NULL，则返回第二个字段，否则返回其本身的值
select nvl(score,’缺考’) from 成绩表
NVL2函数——如果第一个字段值为NULL,则返回第三个字段，否则返回第二个字段
select nvl(score,’缺考’,’正常’) from 成绩表

五、分析函数

分析函数是什么？
分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数，它可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。
分析函数和聚合函数的不同之处是什么？
普通的聚合函数用group by分组，每个分组返回一个统计值，而分析函数采用partition by分组，并且每组每行都可以返回一个统计值。
分析函数的形式
分析函数带有一个开窗函数over()，包含三个分析子句:分组(partition by), 排序(order by), 窗口(rows) ，他们的使用形式如下：over(partition by xxx order by yyy rows between zzz)。
例：
1、分组(partition by)
select t.tname 姓名,sum(t.sal) over(partition by t.deptno ) 薪酬,t.deptno from teacher t

结果：
此时得到的sum(t.sal) 薪酬为分组后的累加

2、排序(order by)
select t.tname 姓名,sum(t.sal) over(partition by t.deptno order by t.sal) 薪酬,t.deptno
from teacher t
结果：此时薪酬为依次累加，可能是对统计范围规定了个统计顺序，一步一步的统计。

3、DENSE_RANK()函数：具有相等值的行排位相同，序号是连续的
select t.tname 姓名,DENSE_RANK() over(partition by t.deptno order by t.sal) 排列,t.deptno from teacher t

4、RANK()函数：具有相等值的行排位相同，序号随后跳跃
select t.tname 姓名,RANK() over(partition by t.deptno order by t.sal) 排列,t.deptno from teacher t

5、row_number()函数：返回连续的排位，不论值是否相等
select t.tname 姓名,t.sal+nvl2(t.comm,t.comm+t.sal,t.sal) 薪水,t.deptno,row_number()
over(partition by t.deptno order by t.sal desc) 排名 from teacher t