Oralce（全）个人笔记

• 事务控制语句
  commit 提交数据
  rollback 数据回滚
  savepoint 保存点

---

- 1、Oracle错误:ORA-01843:无效月份的问题

添加月份时，会报错，原因是客户端为中文，需要修改当前这个参数改成AMERICAN。
执行：
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';

- 2、nvl（a，b）

处理空值，a为空就是b。两个参数类型必须一致。

- 3、distinct

关键字（去重）只能在select后面。
如果要查多个列，则显示的是所有列中不同的组合。

- 4、数据类型处理

lower(‘char’)

为将字符数据全部转化为小写。

upper(‘char’)

为将字符数据全部转化为大写。

initcap(A)

为将字符数据的首字母大写

length(A)

返回值为A字符数据的长度

lpad(A，10，‘*’)

返回值为字符类型。将字符数据A的长度设置为10的长度，不足就在左边补足‘*’。

rpad(A，10，‘*’)

返回值为字符类型。将字符数据A的长度设置为10的长度，不足就在右边补足‘*’。

replace(A，B，C)

返回值为字符类型，将A字符中的B字符替换成C

trim(A)

返回值为字符类型，将A字符清除空字符

instr( A , B )

该函数为在A字符当中查找B的起始位置。

instr( A , B ,0)

含义是从第三个参数指定位置开始查找

instr( A , B ,0,2)

含义是从第三个参数指定位置开始查找，且匹配到的第二个位置。

substr( A , 0 ,0)

含义为截断A字符，从第二个参数指定索引开始，包含索引的位置，截断第三个参数的长度
substr(‘Student’,1,2)=tu

- 5、between 200 and 1000

处理数字数据，限定最大值，最小值，包含200，1000。

- 6、name in(‘张三’,’李四’)

in关键字，该句为name等于张三，和name等于李四，相当于or。

- 7、like关键字，模糊匹配

name like ‘_张%’ 匹配第二个字符是张，后面随便是什么字的name
‘_’表示一个字符，’%’为0到多个字符，如果有要匹配这两个特殊字符，则需要在前面加’\’。

- 8、is null关键字

是判断null值的办法

is not null关键字

是判断不是null值的方法

- 9、not关键字

可以添加到between … and … ，in(…)前面，表示不在这些区域内

- 10、round(A,2)

处理数字，四舍五入，用法为：round(0.1567,2)，0.16，保留两位小数点，默认为不保留小数点。

- 11、trunc(A,2)

处理数字，对数字进行截取，截取只是截取小数点后几位
trunc(0.156,2)=0.15，对小数点后面截取2位，不会四舍五入

- 12、mod (A,2)

处理数字，对A进行取余操作，返回余数

- ceil（A）

返回大于等于数值A的最小整数
ceil(1.4)=2

- floor（A）

返回小于等于数值A的最小整数
floor(1.6)=1

- 13、dual 虚表

该为Oracle提供用于操作函数的方式，属于SYS用户，共享给所有用户使用，表中存放一个常量数据’X’
存在的意义是：更方便的操作函数或者查询常量，

- 14、sysdate 日期函数

select sysdate from dual ;//获取系统当前时间

日期相加减所得到的是数字类型的数据。为相加减的多少天。

- 15、months_between(olddate,newdate)

参数列表当中的两个数都为日期类型，返回值为小数，为相差多少个月。

- 16、add_months( newdate ,-12)

对第一个参数的日期进行运算，对月。返回值为date。

- 17、last_day (newdate)

返回第一个参数日期的月最后一天，date。

---

- 18、 to_char（日期数据 , 格式）

将日期数据转化为想要自定义的格式
常用日期格式
yyyy 四位数字年 如：2011
year 全拼的年 如：twenty eleven
month 全拼的月 如：november 或 11 月( 中文 )
mm 两位数字月 如：11
mon 简拼的月 如：nov( 中文没有简拼 )
dd 两位数字日
day 全拼的星期 如：tuesday
dy 简拼的星期 如：tue
am 上午/下午 如：am/pm
sqlplus 中日期的默认格式是：DD-MON-RR

千年虫问题：

假设现在时间为 2011 年 , 05 年和 98 年分别被 Oracle 解释为：
YY RR
05 年 2005 2005
98 年 2098 1998
假设现在时间为 1998 年 , 05 年和 98 年分别被 Oracle 解释为：
YY RR
05 年 1905 2005
95 年 1995 1995

- 19、toDate(字符数据 , 格式)

按照指定格式匹配日期，返回值为Date。
为Oracle数据库独有的。

- to_number(A,B)

按照指定格式B，转换A为数字类型

---

- 20、coalesce(a,b,c)

类似于nvl(A,B)，如果a为空则返回b，如果b为空则返回c，一般c为常量

- 21、case A when B then.C

类似于Java的swich case

case A  when B1 then C1
        when B2  then C2 
        else D
        end

A为要匹配的项，B为满足A的条件，C为满足B的前提下，返回的值。
else为B条件都不满足时则返回D。
end为结束语句关键字

- 22、decode(A,B1,C1,B2,C2,……,D)

为简化版case…end语句。

- 23、order by A

该关键字为排序关键字，A为要排序的数据。null值被认作为最大的。
后面再跟asc时，则是正序排列，则是从小到大，默认可省略。
desc则是倒序排列，则是从大到小，不可省略。
如有两个参数需要排序，两个参数需要用逗号分开，会先排第一个参数，之后排序第二个参数。
排序语句放在查询语句的最后。
有语法是order by 1，改写法为排序第一个列，为省略列名的作用。

- 24、组函数

count(A)

表示为A列中有多少列，忽略空值。

avg(A)

返回A列的平均值，忽略空值，针对数字的操作。

sum(A)

返回A列的总和，忽略空值，针对数字的操作。

max(A)

返回A列的最大值，忽略空值，对所有数据类型都可以操作。

min(A)

返回A列的最小值，忽略空值，对所有数据类型都可以操作。

- 25、group by A

按A列分组查询。
select 后出现的列 , 凡是没有被组函数包围的列 , 必须出现在 group by 短语中。
该关键字的意思为将A列中的值进行分组（合并成一行）。其他查询的列，需要进行组函数处理。

- 26、having A

A为条件，该关键字适用于分组后的数据再次进行查询过滤

- 27、ALL(A)

A为已有行，一般用在子查询内，其返回的是列，返回列中最大值的列

Any(A)

返回的是列中最小值的列

- 28、A in (B)

A为列名，B为返回的多行数据，返回的数据为真假。
使用in关键字的情况为B的数据为多行数据，当要进行A和B的相等比较，就不能使用“=”，需要使用in关键字，返回所有数据，进行相等的比较。
可以在in的前面添加not ，如果使用not in，则B列中不能为null，如果列表中有null值，则列表中没有结果返回。

- 29、exists(A)

A为查询到的列。返回的数据为真假。
该关键字只关心A列中有没有数据返回，并不关心子查询A的结果，在子查询当中select可以随意写什么。
在exists前面可以添加not。

- 30、集合操作

集合操作的前提是两个集合的列结构相同，列的个数，列的顺序，列的数据类型相同。返回一个集合

A　 union　 B

合集，将A、B两个集合进行合并，且去重、排序。

A 　union all　Ｂ

合集，将A、B两个集合进行合并，且不去重、不排序，在满足其功能的情况下，优选该关键字。

Ａ　intersect　Ｂ

交集，返回Ａ、Ｂ两个集合的相同列。

Ａ　minus　Ｂ

差集，返回Ａ中Ｂ没有的

集合 A： {1,2,3,4,5}
集合 B： {1,3,5,7,9}
A 与 B 的合集： {1,2,3,4,5,7,9}
A 与 B 的交集： {1,3,5}
A 与 B 的差集： A-B {2,4}

---

- 31、内连接

A join B on C

A为驱动表，B为匹配表，C为相匹配的数据。
驱动表会被遍历，在匹配表中查找匹配数据。返回条件为真的列

- 32、外连接

如果驱动表在匹配表中找不到匹配记录，则匹配一行空行。
外连接的结果集 = 内连接的结果集 + 驱动表在匹配表中匹配不上的记录和空值。
外连接的本质是驱动表中的数据一个都不能少。
查询时，应该先考虑那张表需要完整的表达出来，不会遗漏信息，则这张表就作为驱动表。

A left outer join B on C

左外连接，A为驱动表，B为匹配表，C为相匹配的数据。

A right outer join B on C

右外连接，B为驱动表，A为匹配表，C为相匹配的数据。

- 33、A full outer join B on C

全外连接，A为驱动表，B为匹配表，C为相匹配的数据。
全外连接可以把两个表中的记录全部查出来。
全外连接的结果集 = 内连接的结果集 +
驱动表中在匹配表中找不到匹配记录的数据和 null 值 +
匹配表中在驱动表中找不到匹配记录的数据和 null 值。
驱动表和匹配表可以互换。
该关键字会把两张表中的数据完整的表述出来，没匹配上的，会匹配到null值。

---

- DML操作

包含插入、更新、删除操作。

- 34、insert into

插入数据关键字。
不用写列名的写法和写列名的写法：

insert into Student values(01,'张三',null);//不写列名
insert into Student(id,name,sex) values(02,'李四',null);//写列名

插入多条信息（从别表中查询）

insert into Student_01
(select * from Student where sex='man');

- 35、rownum

该关键字表示为行号。

select * from emp_xxx where rownum <= 8;//查询行号小于等于8的行。

- 36、update

该关键字为更新数据。

update 表名 set 
列名 = 新的列值,
列名 = 新的列值,......
where 条件;

如果不加条件，则是修改该表全部表记录。
rollback 回退 , commit 确认。

- 37、delete

该关键字为删除数据。

delete [from] 表名 where 条件 ;

如果删除语句中不加 where 条件 , 将删掉表中的全部记录。
rollback 回退 , commit 确认。
drop table 会删除表结构和数据 ;
truncate 删除表数据 , 保留表结构。
Drop 和 truncate 都不可以回退。
delete 仅删除数据 , 可以回退。

- 38、rename

修改表名的关键字

rename Student to Student_01 ; 

- 39、rowid

删除重复数据，该关键字为Oracle独有。
每一条记录在数据库当中的rowid是唯一的。
表示为一个独有的数据。

select rowid rowids from Student ;

---

- DDL 操作

数据定义语言 DDL：create / drop / alter / truncate；

- 40、create table

建表关键字。

1.复制表

create table A  as  B ;

A为重新创建的表名，B为查询语句，代表着查询到的语句，复制到A表中。不复制约束条件。

create table Student_01
as
select * from Student
where 1 <> 1 ;//这个为只复制表结构，不复制表数据

如果B查询语句中，返回的列名中有函数，则需要重新取列名。

2.自定义表列和数据类型

create table 表名(列名 列的数据类型,....);

not null

在数据类型的后面添加，表示该列的数据不能为null值。

unique

列级约束，在数据类型后面添加，限制该列不能为重复的。

- 41、drop table 表名

删除结构和全部表数据

- 42、truncate table 表名

保留表结构，但是删除所有数据
truncate 在功能上等同于：delete + commit
delete 操作将删除数据存储到临时空间中 , 不是直接删除 , 可以回退。
truncate 操作直接删除 , 不占用临时空间 , 不能回退。

- 43、alter table 表名 关键字 …

修改结构。

add(列名，数据类型)

该为添加列的关键字。

rename column 列名1 to列名2

修改列名的关键字。

modify(列名，数据结构);

修改指定列的数据结构。

drop column 列名

删除指定列。

- 44、chr( A )

该函数为返回对应的ACSII码表值，A为ACSII码

- 45、concat( A , B )

该函数为返回A、B拼接起来的字符

---

- 数据表字典

user_tables 用户所有的数据表
user_constraints 用户所有的约束条件
user_objects 用户所有的对象( 表、视图、索引等 )
all_tables 用户能访问的数据表包括自己的和别的用户允许自己访问的
all_constraints 用户能访问的约束条件
all_objects 用户能访问的对象( 表、视图、索引等 )
数据字典的格式如：
User_XXX: 用户自己的对象
All_XXX： 用户能访问的对象
Dba_XXX：数据库所有的对象

- Oracle | 给表和字段添加注释

comment on column 表名.字段名 is ‘注释内容’;

comment on column OPERATOR_INFO.MAIN_OPER_ID is ‘归属操作员’;

comment on table 表名 is ‘注释内容’;

comment on table OPERATOR_INFO is ‘操作员信息表’;