Oracle知识点梳理

数据操作语言DML
插入：INSERT 更新：UPDATE 删除：DELETE 查询：SELECT
数据库定义语言 DDL
创建： CREATE 修改：ALTER 删除：DROP 截断表：TRUNCATE
数据库控制语言 DCL
授权： GRANT 回收权限：REVOKE
事务控制语句： 提交： COMMIT 回滚： ROLLBACK 保存点 ：SAVE POINT

事务：一组逻辑操作单元 使数据从一种状态变换成另一种状态

一个数据库（database）下面可以分多个表空间（tablespace）；一个表空间下面又可以分多个段（segment）；一个数据表要占一个段（segment），一个索引也要占一个段（segment ）。 一个段（segment）由多个 区间（extent）组成，那么一个区间又由一组连续的数据块（data block）组成。这连续的数据块是在逻辑上是连续的，有可能在物理磁盘上是分散。

mysql属于关系型数据库，oracle也属于关系型数据库(对象型数据库)
Oracle：没有自增长(序列)、截取(伪列)
方法与函数 面向对象编程中叫方法 面向过程编程叫函数
管理员账户
SYS 超级管理员即事最高权限管理员
系统用户，数据字典所有者，此用户不能被删除。
System 数据库默认管理用户，拥有DBA角色权限，此用户不能被删除
Scott 测试账户

免账户登录：只能本机登陆：sqlplus / as sysdba 登陆为：sys用户

oracle时具有权限角色的概念的
每个用户的所有操作都是需要权限的，并且每个用户的表空间都是独立的，用户的权限事系统管理员给用户设置的，或A用户给B用户访问自己的权限
角色：一组权限的集合
管理员权限操作
select * from all_users; 查询数据库所有用户
DROP USER 要删除的用户名 CASCADE; 使用system身份登陆数据库，删除用户；
Create user 用户名 identified by 密码； 创建用户
Alter user 用户名 identified by 需要修改的密码； // 修改用户名密码
创建完用户需要给用户授权
DCL 语句 数据库控制语句

1. grant 授权
2. revoke收回权限

grant create session to 用户名; 分配链接权
Grant create table to 用户名; 分配建表权
Grant unlimited tablespace to 用户名; 分配表空间无限权(CURD)
Grant Create sequence to 用户名; 分配创建序列权限 oracle没有自增长
用户拿到权限后 才可以进行建表 增删改查 等操作
注意事项：
用户是相互独立的 只有授权给别的用户后才能被别的用户查看 没有自增长与枚举 时间格式 和 字符型数据需要用单引号括起来
dual 表 Oracle中的一个实际存在的表，任何用户均可读取，常用在没有目标表的Select语句块中 用途详情链接 https://blog.csdn.net/tianlesoftware/article/details/4764326
当前用户给别的用户授权：
Grant select on 表名 to 别的用户 //查询
Grant insert on 表名 to 别的用户 //增加
Grant delete on 表名 to 别的 //删除
Grant update(sname) on 表名 to 别的用户 //修改
管理员可以锁定用户 用户也可以给自己授权的用户进行锁定
alter user 被锁用户 account unlock; 解锁（锁定）用户

Show user; 显示当前登陆的用户名
select * from tab; 查询当前用户拥有的表

Oracle 中支持的数据类型 :

char和varchar的区别？

1. Char类型内容的实际长度为声明时的最大长度，不够用空格代替！
2. varchar类型内容的实际长度为插入数据的长度！
3. 2个类型都是以字节为单位，2个字节存储一个汉字！

nchar和nvarchar的区别？

1. nchar类型内容的实际长度为声明时的最大长度，不够用空格代替！
2. nvarchar类型内容的实际长度为插入数据的长度！
3. 2个类型都是以字符为单位，1个字符存储一个汉字！

DDL：数据结构定义语言： 对表的元数据信息操作
创建表：
Create table 表名(字段名 字段类型,字段名 字段类型，…);
Create table a as select * from b where…;

删除表：
Drop table 表名;
修改表名：
Alter table 现在使用的表名 rename to 修改后的表名;
添加列：
ALTER TABLE 表名 add(需要添加的字段 字段类型);
修改列的字段数据类型：
ALTER TABLE 表名 modify（字段名 修改后的数据类型);
修改列的字段名：
ALTER TABLE 表名 RENAME COLUMN 现在的字段名 TO 修改后的字段名；
删除列：
ALTER TABLE 表名 DROP COLUMN 字段名；
删除所有数据（截断数据）
Truncate table stu

约束：

1. 主键：primary key
   添加主键约束
   ALTER TABLE 表名 ADD CONSTRAINT 需要添加主键的字段名 PRIMARY KEY (需要添加主键的字段名);
   删除主键：
   ALTER TABLE 表名 DROP primary key;
2. 唯一性约束 ：unique
   添加唯一性约束
   ALTER TABLE 表名 ADD CONSTRAINT 需要添加主键的字段名 UNIQUE (需要添加主键的字段名);
   删除唯一性约束
   ALTER TABLE 表名 DROP UNIQUE(需要删除唯一性约束字段名称);
   ALTER TABLE 表名 DROP CONSTRAINT 需要删除唯一性约束字段名称;
3. 检查约束 check
   添加须要检查的字段
   ALTER TABLE 表名 ADD CONSTRAINT 需要检查的字段名称 CHECK（检查条件); //检查条件可以看成 where
   eg 年龄（age）这个字段需要在大于0小于100，则
   ALTER TABLE 表名 ADD CONSTRAINT age CHECK(age>0 AND age<100);
   删除需要检查的字段
   ALTER TABLE 表名 DROP CONSTRAINT 需要删除检查约束的字段名称;
4. 默认约束 default
   ALTER TABLE 表名 MODIFY 字段名 DETAULT 默认的内容;
5. 不为空 或为空约束 not null null
   ALTER TABLE 表名 MONDIFY 字段名 NOT NULL;
   ALTER TABLE 表名 MONDIFY 字段名 NULL;
6. 外键约束 foreign key 子表中外键列所关联的主表的列必须是主键约束
   添加外键约束
   ALTER TABLE 表名 ADD CONSTRAINT 子表外键字段名 FOREIGN KEY(子表外键字段名) REFERENCES 父表主键字段名
   删除外键：
   ALTER TABLE 表名 DROP CONSTRAINT 外键字段名称

约束综合运用建表：
CREATE table pop(
p_id NUMBER(10) CONSTRAINT P_id PRIMARY KEY, //主键
p_name VARCHAR(20) CONSTRAINT p_name UNIQUE NOT null, //唯一不为空
p_age NUMBER(10) NOT NULL, //不为空约束
p_sex VARCHAR(20) CONSTRAINT p_sex CHECK(p_sex in(‘男’,‘女’)), //检查
p_gj varchar(10) default ‘中国’ //默认
);

序列：
给某个字段创建一个序列
Create sequence 字段名
查询当前序列
select 字段名.NEXTVAL FROM dual;
查询下一个序列
select 字段名.CURRVAL FROM dual;
从指定数字开始创建序列
CREATE SEQUENCE 字段名 start width 数字；
创建一个序列从指定数字开始，步长为多少 步长 每次插入一个id自动增长多少 eg 步长为 5 第一个id 添加 添加完成后 序列为6 当第二id 添加进来时 序列为 6+5，一次类推
CREATE TABLE 字段名 start width 数字 increment by 步长大小；

创建一个具有缓存的序列 缓存含义 若为5 先创建5个序列 当序列被用完在创建5个 第一次缓存1-5 第二次 6-10 以此类推
CREATE SEQUENCE 字段名 CACHE 缓存数字；
创建一个具大值的序列
CREATE TABLE 字段名 MAXVALUE 数字；
创建一个最大值不循环序列
CREATE TABLE 字段名 maxvalue 数字 nocycle；
创建一个最大值循环不缓存序列
Create sequence 字段名 maxvalue 数字 cycle nocache；

查看表中列信息
Desc 表名;

MDL：数据操纵语言 对表中行进行操作

1. 向表中添加数据 全字段添加
   INSERT INTO 表名 value(字段内容，字段内容，。。。。)；

2. 向表中部分字段添加数据
   INSERT INTO 表名 （字段名称，字段名称，。。。。） value(字段内 容，字段内容，。。。。)；

3. 查询表全部内容
   select * from 表名；

4. 查询部分内容
   select 要查询的字段名称 from 表名 where 过滤条件；

5. 修改表内容
   UPDATA 表名 set 字段名=修改的内容，字段名=修改的内容 where 过滤定位条件；

6. 删除某些行
   DELETE FROM 表名 where 过滤条件；

7. 清空表数据
   DELETE FROM 表名 ；

where 条件后面的关键字：

1. and 并且 相当于 与

2. or 或

3. in（内容1，内容2） 或者1 或者2

4. between x and y (x到y之间)

5. 占位符：
   %：代表任意位任意内容 0或多个
   _：代表一位任意内容

6. like 模糊查询 一般和占位符连用 eg 数据库中 有个李%元 但现在只知道这个人名含% 其余都不知
   where like’%%%’ ESCAPE’’ ; 则可以查到类似人名 其中 \标识转移字符
   转义字符 让字符不具有关键字或字符含义只是普通字符 ESCAPE’’ 告诉编译器这个是转义字符

7. Not in：
   select * from stu where s_age not in(10,50)

8. Not between x and y：
   select * from stu where s_age not between 20 and 30

9. Not like：Select * from stu where s_name not like ‘张%’;

10. Some： 表示满足其中一个的意义，是用or串起来的比较从句
    Select * from stu where s_age>some(20,50,30); >20

11. any：表示满足其中一个的意义，是用or串起来的比较从句
    Select * from stu where s_age>any(20,50,30); >20

12. all：表示满足其其中所有的查询结果的含义
    Select * from stu where s_age>all(20,50,30); >50
    分组：
    group by 字段名，字段名，。。。；
    注意分组不能出现在where 子句中 即where 后不能有 group by
    查询语句中要查询的字段 在分组中必须出现 但分组中有的字段 查询显示字段可以没有
    分组条件：
    having group by可以在having 后使用
    having 条件 group by 字段名
    排序：
    order by desc:降序 asc：升序 （默认）
    伪列： Rownum：表中物理上是不存在，但是可以查询。仅仅代表数据的行号！与表中数据一定一一对应，不能对当前查询语句使用大于号。 伪列 默认是先编号 后排序 所以要 先进行排序，把排完序的表在进行编号
    Select stu.，rownum from stu;
    Select * from stu where rownum<5;
    Select * from stu where rownum<5 order by s_id desc;
    Select * from (select xx.,rownum yy from stu xx) where yy>=5 and yy<=10;
    Selet * from (select *,rownum xx from stu where rownum<=10) yy where yy.xx>=5

Oracle 分页
Select 需要显示字段名from
(select xx.需要显示字段名,rownum rr from
(select 需要显示字段名 from 表名order by 排序字段名称 desc) xx
)yy where yy.rr>=Nn-N+1 and yy.rr<=3n；

N:每页显示条数
n：当前页码

mysql 分页
Select 需要显示字段名 from 表名 order by 排序字段名称 desc limit N*(n-1),N

N:每页显示条数
n：当前页码

Union：并集，去掉重复项！排序！ Select * from a union Select * from b
Union all：合集，不去掉重复项！不排序！Select * from a union all Select * from b
Intersect：交集，只有重复项，排序！ Select * from a Intersect Select * from b
Minus：差集，前表数据-后表数据，去掉重复项！排序！Select * from a Minus Select * from b

事务控制语句

1. commit 提交
2. rollback 回滚
3. save point 保存点

Oracle默认是自己处理事务，事务是非自动提交，增删改数据后，必须要提交或者回滚。
DML 删除的数据写入回滚段，数据是可以回滚回来，由于会写入回滚段，所以删除速度较慢！

DDL 删除的数据不写入回滚段，数据是不可以回滚回来，由于不会写入回滚段，所以删除速度较快！
Oracle 函数学习
Oracle函数大全下载地址：
https://www.cnblogs.com/gaofei-1/p/7445903.html

流只能对本地文件处理
DBMS 数据库管理系统，中有一个数据字典（系统目录）用来储存元数据 （metadata）
主键 ：不为空 不重复
外键： 外键的内容不能超出主键内容
实体间的关系

1. 一对一
2. 一对多
3. 多对多
   父子表 父唯一 子多表 父表为主键 子表创建外键

一个方案名对应一个用户 方案名=用户名
去除列重复数据 DISTINCT 字段名，字段名，。。。
改变当前会话中日期格式 方法：
alter session set nls_date_format = ‘yyyy-mm-dd hh24:mi:ss’;
2003-01-28 15:23:38
where 中比较运算符不能连用 eg a<b<c 类型
比较运算符 大于> 小于< 大于等于>= 小于等于<= 不等于!= 不等于<>
空值比较 where 字段名 IS NULL //字段等于空 // IS NOT NULL
WHERE 子句中运算符 优先级 NOT > AND > OR

单行函数： 每次取一条记录，作为 函数的参数，得到这条记录对应的单个结果 eg length(字段名) 字段内容的长度
多行函数： eg max(age) 最大年龄

等值查询 N各表 需要 N-1个等值条件
内连接：

1. 等值连接 form 表1 join 表2 on 表1主键=表2外键 join 表三 表1主键=表3外键
2. 非等值连接 表之间没有主外键关系 但需要加入 where
3. 自连接 自己查自己

外连接：

1. 左外连接 左表内容都展示 from 表1 left outer 表2 on 主键=外键
2. 右外连接 右表内容都展示 from 表1 right outer 表2 on 主键=外键
3. 满外连接 两张表都显示 from 表1 full outer 表二 on 主键=外键
   Select count() from表名; 查询将返回表中记录的条数
   Select count() from表名 where ； 查询将返回表中满足条件记录的条数
   Select count(字段名) from 表名； 这列内容不为空的条数
   Select count(distint 字段名) from 表名；这列内容不为空且不重复的条数
   在使用left jion时，on和where条件的区别如下：

1、 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

下面每组的两个语句都是完全等效的:

A LEFT JOIN B
A LEFT OUTER JOIN B

A RIGHT JOIN B
A RIGHT OUTER JOIN B

A FULL JOIN B
A FULL OUTER JOIN B

A INNER JOIN B
A JOIN B

REPLACE( string1, string_to_replace [, replacement_string] )
參數或參數
字符串1
用另一組字符替換字符序列的字符串。
string_to_replace
將在string1中搜索的字符串。
replace_string
可選的。所有出現的string_to_replace都將替換為string1中的replace_string。如果replacement_string省略參數，REPLACE函數簡單地刪除所有出現string_to_replace，並返回生成的字符串。
例如：

REPLACE(‘123123tech’, ‘123’);
Result: ‘tech’

REPLACE(‘123tech123’, ‘123’);
Result:‘tech’

REPLACE(‘222tech’, ‘2’, ‘3’);
Result: ‘333tech’

REPLACE(‘0000123’, ‘0’);
Result: ‘123’

REPLACE(‘0000123’, ‘0’, ’ ‘);
Result: ’ 123’

oracle的索引分为5种:唯一索引，组合索引，反向键索引，位图索引，基于函数的索引
创建索引的标准语法:
CREATE INDEX 索引名 ON 表名 (列名) TABLESPACE 表空间名;

1. 创建唯一索引:
   CREATE unique INDEX 索引名 ON 表名 (列名) TABLESPACE 表空间名;

2. 创建组合索引:
   CREATE INDEX 索引名 ON 表名 (列名1,列名2) TABLESPACE 表空间名;

3. 创建反向键索引:
   CREATE INDEX 索引名 ON 表名 (列名) reverse TABLESPACE 表空间名;

4. 创建基于函数的索引:
   基于函数的索引就是存储预先计算好的函数或表达式值的索引。这些表达式可以是算术运算表达式、SQL或PL/SQL函数、C调用等
   CREATE INDEX idx ON emp ( UPPER(ename)) TABLESPACE 表空间名;

注意点：
1.reenyi Oracle 语句及登陆用户名均不区分大小写，但数据字典中都是存放的是大写，如果对大小写敏感则利用双引号引起来
2.关键字大写方便阅读 结尾必须；
4. 查询过程中，对数值类型数据可以使用 + - * /
5. 给字段起别名 可用双引号 也可以不用双引号 可用AS 也可以不用
6. 在算术表达式中出现null 则结果为null null不等于0
7. 用 || 可把两列或多列查询结果合并在一起 || 为链接表达式 链接表达式中的字符型必须’ ’ 引起来 若链接表达式中有null 展示页面显示为空
8. 任意数据类型 都支持为null
9. 日期和字符型必须使用单引号引起来
10. 对于日期类型数据可以进行 + - 天数运算 两个日期只能相减
11. 那日期作为条件进行查询 注意 where Date=’ 日-月-年’
12. 多表查询时 不加入等值条件，会产生笛卡尔积
13. 任何数据类型都可以使用 max() min()
14. avg（）求平均 sum() 求和 只能number 类型使用
15. count（）会带来全表扫描（效率低）
16. count（）不会过滤掉一整行值为null的行
17. count（1）会过滤掉一整行为null的行