*SQL的类型
- DML（Data Manipulation Language 数据操作语言）:select/insert/update/delete
- DDL（Data Definition Language 数据定义语言）:create table/alter table/truncate table/drop table
- create/drop view/sequnece/index/synonym(同义词)
- DCL（Data Control Language 数据控制语言）:grant(授权)/revoke(撤销权限)
DML语句（增/删/改）
INSERT语句
用于插入数据
- 为每一列添加一个新值
- 按列的默认顺序列出各个列的值
- 在INSERT 子句中随意列出列名和他们的值
- 字符和日期型数据应包含在单引号中
语法
使用这种语法一次只能向表中插入一条数据
示例：
插入空值
使用地址符来占位具体值
类似JDBC的PreparedStatement，用占位符"?"来表示以后将要插入的值的位置
在Oracle中可以使用"&"符号来指定列值，位置放在VALUES子句中
通常使用与列名一样的地址符后缀，如：id——&id
地址符几乎可以使用在所有SQL语句中，不仅仅是插入语句
如：
从其他表中拷贝数据
语法是在INSERT语句后加入子查询
- 不需要书写VALUES子句
- 子查询中的值列表应该与INSERT子句中要被插入的列名相对应
- 这种方式可以一次插入多条数据
当需要插入海量数据时，这种方法效率很低，通常使用以下的方法来解决
海量插入数据
- Oracle提供了数据泵（PLSQL程序）：dbms_datapump（程序包）
- SQL*Loader
- 外部表
UPDATE语句
语法
可以一次更新多条数据
一般使用WHERE子句来确定需要更新哪些数据
在UPDATE语句中使用子查询
更新操作中的数据完整性错误
数据的完整性就是指创建表时定义的表与表之间的约束
DELETE语句
语法
可以一次删除多条数据
使用WHERE子句来确定需要更新哪些数据
删除操作中的数据完整性错误
关于DELETE和TRUNCATE的异同
- 都是删除表中的数据
- delete是逐条删除，truncate是先摧毁表再重建
- delete速度更快，truncate速度比delete慢
- delete是DML（数据操作语言）语句，truncate是DDL（数据定义语言）语句
- Delete操作可以回滚rollback
- Delete操作可以闪回（Oracle特有，再事务提交后可以闪回）
- 在Oracle中，大部分操作都可以闪回，即大部分操作都是可逆的
- Delete操作可能产生碎片，并且不释放空间
- 关于碎片
- 碎片使表的数据条目之间不连续，影响查询的速度
- 清理碎片的方法：
注意：插入、更新和删除操作会引起数据的的变化，必须考虑数据的完整性
Oracle中的事务
Oracle中事务的概念
数据库事务的组成
- 一个或多个DML语句
- 一个DDL（Data Definition Language – 数据定义语言）语句
- 一个DCL（Data Control Language – 数据控制语言）语句
数据库事务的执行流程
- 以第一个DML语句的执行作为开始
- 以下面其中一种作为结束
- 显示结束：commit rollback
- 隐式结束（自动提交）：DDL语言、DCL语言、exit(事务正常退出)
- 隐式回滚（系统异常终了）：关闭窗口、死机或掉电等
COMMIT和ROLLBACK语句
使用COMMIT和ROLLBACK语句可以
- 确保数据完整性
- 数据改变被提交之前预览
- 将逻辑上相关的操作分组
通过SAVEPOINT控制事务
- 使用SAVEPOINT语句可以在当前事务中创建保存点。
- 使用ROLLBACK TO SAVEPOINT语句回滚到创建的保存点
- 使用SAVEPOINT来回滚可以避免出现小错误而回滚整个事务
数据库事务的隔离级别
对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题:
- 脏读：对于两个事物T1，T2，T1读取了已经被T2更新但还没有被提交的字段。之后，若T2 回滚，T1读取的内容就是临时且无效的
- 不可重复读：对于两个事物T1，T2，T1读取了一个字段,然后T2更新了该字段。之后，T1再次读取同一个字段，值就不同了
- 幻读：对于两个事物T1，T2，T1从一个表中读取了一个字段，然后T2在该表中插入了一些新的行。之后，如果T1再次读取同一个表，就会多出几行
数据库事务的隔离性
数据库系统必须具有隔离并发运行各个事务的能力，使它们不会相互影响，避免各种并发问题一个事务与其他事务隔离的程度称为隔离级别
数据库规定了多种事务隔离级别，不同隔离级别对应不同的干扰程度，隔离级别越高，数据一致性就越好，但并发性越弱
数据库提供的4种事务隔离级别
Oracle支持的两种事务隔离级别（或三种）
- Oracle支持2种事务隔离级别：READ COMMITED和SERIALIZABLE（或加上READ ONLY只读共三种)
- Oracle默认的事务隔离级别为：READ COMMITED
mysql支持4种事务隔离级别. Mysql 默认的事务隔离级别为: REPEATABLE READ
DDL语句（管理及操作表）
常见的数据库对象
表空间
表空间是Oracle数据库的逻辑单元
数据库——表空间
一个表空间可以于多个数据文件（物理结构）关联一个数据库下可以建立多个表空间，一个表空间可以建立多个用户，一个用户下可以建立多张表
建立表空间的语句
用户
创建用户的语句
为用户赋权限语句
新创建的用户没有任何权限，登陆后会提示
角色
Oracle中存在三个主要角色
分别有以下权限
赋权语法
创建表
CREATE TABLE语句
*前提：
- 用户需要具备CREATE TABLE权限
- 有足够的存储空间
语法
必须指定表名，列名及其数据类型和数据类型的大小
示例：
DEFAULT VALUE-默认值
执行insert操作时，可以为其指定默认值
语法：
- 值、表达式和SQL语句都可以作为默认值
- 其他的列名或者是伪列都是非法的
- 默认值的类型必须和该列的类型一致
利用select语句创建表
select语句查询的结果相当于一张表，我们可以将这个查询结果变成一张表，即创建一张存储select语句查询结果的表
这样就可以很方便的创建新的表了
语法：注意as关键字
示例：
拷贝现有的表结构（创建与现有表的结构相同的表）
利用上述通过select语句创建查询结果对应的表的功能：
create table 新表 as select * from where 1=2;
将会拷贝表但不拷贝表中的数据
同理可以在where条件中加上筛选条件拷贝需要的表数据
列的数据类型
关于ROWID（行地址）：
*表及列名的命名规则
- 必须以字母开头
- 必须在1–30 个字符之间
- 必须只能包含A–Z、a–z、0–9、_、$、和#
- 必须不能和用户定义的其他对象重名
- 必须不能是Oracle的保留字
- Oracle默认存储是都存为大写
- 数据库名只能是1~8位，datalink可以是128位，和其他一些特殊字符
*使用子查询创建表
使用AS subquery选项，将创建表与插入数据结合
语法：
要求：
- 指定的列和子查询中的列要一一对应
- 通过列名和默认值定义列
示例：
修改表
ALTER TABLE语句
使用ALTER TABLE语句可以
语法
列的追加/修改/删除/重命名
追加列的示例：
修改一个列的示例
可以修改列的数据类型，数据类型大小和默认值
注意：对默认值的修改只影响今后对表的修改，此前的数据不影响
删除一个列示例
删除表
DROP TABLE语句
使用DROP TABLE语句
- 数据和结构都被删除
- 所有正在运行的相关事物被提交
- 所有相关索引被删除
- DROP TABLE 语句不能回滚，但是可以闪回
语法
DROP TABLE不是将表彻底删除，只是将表放到RECYCLEBIN（回收站）中
可以通过加双引号的方法查出已删除的表
回收站可以被清空
注意：回收站并不是一定存在的，某些用户没有回收站（如sys-管理员用户），要注意
purge关键字
可以跳过回收站彻底删除
从回收站恢复已删除的表
有多种方式，如闪回删除：
七种方式：
其中闪回数据归档Oracle11G才有，其余六种11G和10G均有
详细自查
重命名对象
RENAME语句
执行RENAME语句将会改变表、视图、序列或同义词的名称
前提：必须是对象的拥有者
语法
清空表
TRUNCATE TABLE语句
使用TRUNCATE TABLE语句将
语法
注意：
- TRUNCATE语句不能回滚
- 可以使用DELETE语句代替TRUNCATE语句删除数据
约束
约束的概念
- 约束是表一级的限制
- 如果存在依赖关系，约束可以防止错误的删除数据
- 约束的类型
- NOT NULL-非空约束
- UNIQUE-唯一约束
- PRIMARY KEY-主键约束
- FOREIGN KEY-外键约束
- CHECK-检查性约束
约束级别
在Oracle中有两种约束级别
- 列级约束：如果没有特别指明，一般都是列级约束
- 表级约束
约束规则
- 用户可以自定义约束，也可以使用OracleServer的sys_cn格式命名约束
- 约束创建的时机：
- 约束可以定义在列一级，或者是表一级
- 通过数据字典查看约束
约束的类型
NOT NULL-非空约束
保证该列的值不为空
UNIQUE-唯一性约束
保证该列的值唯一
PRIMARY KEY-主键约束
通过主键查询数据速度最快，因为主键是一个唯一性的索引，关于原理见下文索引部分
约束列的值：
FOREIGN KEY-外键约束
与主表的主键有约束
- FOREIGN KEY: 在子表中，定义了一个表级的约束
- REFERENCES: 指定表和父表中的列
- ON DELETE CASCADE: 当删除父表时，级联删除子表记录（谨慎使用，比较危险）
- ON DELETE SET NULL: 将子表的相关依赖记录的外键值置为null，此时子表这个记录与父表没有联系了
子表的外键必须是父表的主键
check-检查性约束
- 定义每一行记录所必须满足的条件
- 下面的表达式可以使用在check约束中:
- 引用CURRVAL、NEXTVAL、LEVEL、和ROWNUM
- 调用SYSDATE、UID、USER和USERENV函数
- 另一个表的查询记录
在定义检查性约束的时候最好定义这个检查性约束的名称，当不满足约束时回显这个名称告知约束条件，上图emp_salary_min就是这个约束的名称
小结
建表示例：
常见的数据库对象
视图
- 视图是一种虚表
- 视图建立在已有表的基础上，视图赖以建立的这些表称为基表
- 向视图提供数据内容的语句为SELECT语句，可以将视图理解为存储起来的SELECT语句
- 视图向用户提供基表数据的另一种表现形式
视图的优点
创建视图-基本
权限
一般来讲普通用户没有创建视图的权限，需要被管理员授权
语法
- FORCE：子查询不一定存在
- NOFORCE：子查询存在（默认）
- CHECK OPTION：只操作视图对应的数据，若操作视图不存在的数据则报错
- 如：视图为10号部门的员工视图，就不可以插入部门号为其他的员工条目了
- “只能操作看得到的”
- 不建议通过视图对表进行操作
- WITH READ ONLY：只能做查询操作
子查询可以是复杂的SELECT语句
示例：
显示视图结构：使用
示例2：创建视图时在子查询为列定义别名
之后应该在选择视图中的列时使用别名
查询视图
语法
简单视图和复杂视图
替换（相当于修改）视图
使用CREATE OR REPLACE VIEW子句修改视图
创建或者替换
CREATE VIEW子句中各列的别名应和子查询中各列相对应
创建复杂视图
复杂视图举例：查询各个部门的最低工资，最高工资，平均工资
视图中使用DML的规定
注意：不建议通过视图对表进行操作，原因就是使用DML的规定太多了
可以在简单视图中执行DML操作
当视图定义中包含以下元素之一时不能使用delete
- 组函数
- GROUP BY子句
- DISTINCT（去重）关键字
- ROWNUM伪列
当视图定义中包含以下元素之一时不能使用update
- 组函数
- GROUP BY子句
- DISTINCT关键字
- ROWNUM伪列
- 列的定义为表达式
当视图定义中包含以下元素之一时不能使用insert
- 组函数
- GROUP BY子句
- DISTINCT关键字
- ROWNUM伪列
- 列的定义为表达式
- 表中非空的列在视图定义中未包括
屏蔽DML操作
- 可以使用WITH READ ONLY 选项屏蔽对视图的DML操作
- 任何DML操作都会返回一个Oracle server错误
删除视图
删除视图只是删除视图的定义，并不会删除基表的数据
序列
序列是指可供多个用户用来产生唯一数值的数据库对象
- 自动提供唯一的数值
- 共享对象
- 主要用于提供主键值
- 将序列值装入内存中可以提高访问效率
- *创建序列就相当于创建一个数组
Oracle中的序列从功能上来说相当于MySQL中的auto_increment一样
创建序列
创建序列就相当于创建一个数组，数组长度默认为20
语法
- INCREMENT BY n：定义自增量（步长），默认为1
- START WITH n：定义起始值，默认为1
- MAXVALUE n / MINVALUE n | NOMAXVALUE / NOMINVALUE：最大值/最小值 | 无最大值/无最小值，默认为无最大最小值
- CYCLE | NOCYCLE：是否循环（会产生重复的值），默认为NOCYCLE
- CACHE n | NOCACHE：缓存长度（相当于这个数组的长度），默认为20 | 无缓存，即长度为1
示例：
- 创建序列DEPT_DEPTID_SEQ为表DEPARTMENTS提供主键
- 自增量为10
- 起始值为120
- 默认无最小值
- 最大值为9999
- 不循环
- 不定义缓存
查询序列
示例
查询数据字典视图USER_SEQUENCES获取序列定义信息
如果指定NOCACHE选项，则列LAST_NUMBER显示序列中下一个有效的值
NEXTVAL和CURRVAL伪列（相当于操作数组的指针）
这个指针默认是在第1个元素的前面（即CURRVAL此时无意义）
- NEXTVAL返回序列中下一个有效的值，任何用户都可以引用
- CURRVAL中存放序列的当前值
- NEXTVAL应在CURRVAL之前指定，二者应同时有效
序列的应用示例
序列的特点
- 将序列值装入内存可提高访问效率
- 序列在下列情况下出现裂缝（不连续了）
- 如果不将序列的值装入内存(NOCACHE)，可使用表USER_SEQUENCES查看序列当前的有效值
修改序列
示例
修改序列的增量、最大值、最小值、循环选项或是否装入内存
修改序列的注意事项
- 修改序列的用户必须是序列的拥有者或对序列有ALTER权限
- 只有将来的序列值会被改变
- 改变序列的初始值只能通过删除序列之后重建序列的方法实现
删除序列
- 使用DROP SEQUENCE语句删除序列
- 删除之后，序列不能再次被引用
示例：
索引
索引是用于加速数据存取的数据对象，合理地使用索引可以大大降低I/O次数，从而提高数据访问性能
- 一种独立于表的模式对象，可以存储在与表不同的磁盘或表空间中
- 索引被删除或损坏，不会对表产生影响，其影响的只是查询的速度
- 索引一旦建立，Oracle管理系统会对其进行自动维护，而且由Oracle管理系统决定何时使用索引，用户不用在查询语句中指定使用哪个索引
- 在删除一个表时，所有基于该表的索引会自动被删除
- 通过指针加速Oracle服务器的查询速度
- 通过快速定位数据的方法，减少磁盘I/O
- “类似一本书的目录”
创建索引
索引的类型
- 单列索引：基于单个列所建立的索引
- 如CREATE index 索引名 on 表名（列名）
- 复合索引(多级索引）：基于两个或多个列的索引，在同一张表上可以有多个索引，但是要求列的组合必须不同
- 如：CREATE index emp_idx1 on emp(ename,job);和CREATE index emp_idx2 on emp(job,ename);
索引底层原理类型
自动创建
在定义PRIMARY KEY 或UNIQUE约束后系统自动在相应的列上创建唯一性索引
手动创建
用户可以在其它列上创建非唯一的索引，以加速查询
示例
执行计划（效率对比-不创建索引和创建索引）
Cost（%CPU）越低越好
创建索引的时机
以下情况可以创建索引
- 列中数据值分布范围很广
- 列经常在WHERE子句或连接条件中出现
- 表经常被访问而且数据量很大，访问的数据大概占数据总量的2%到4%
以下情况不应该创建索引
- 表很小
- 列不经常作为连接条件或出现在WHERE子句中
- 查询的数据大于2%到4%
- 表经常更新（经常更新索引对应的列）
查询索引
示例
可以使用数据字典视图USER_INDEXES和USER_IND_COLUMNS查看索引的信息
删除索引
使用DROP INDEX命令删除索引
删除索引UPPER_LAST_NAME_IDX
只有索引的拥有者或拥有DROP ANY INDEX权限的用户才可以删除索引
同义词（别名）
使用同义词访问相同的对象
语法
有否PUBLIC关键字表示公有还是私有同义词（对于当前用户）
创建和删除同义词
需要权限
创建同义词
删除同义词
京公网安备 11010802041100号