1 分页查询
数据伪列:表中不存在但却可以使用的列
在 Oracle 中除sysdate 外还有ROWNUM,ROWID 与记录相关的伪列
2.1 ROWNUM
可以为查询结果动态的生成记录编号
获取记录时,不支持大于操作
格式:
SELECT ROWNUM FROM 表名 -- 获取全部
SELECT ROWNUM FROM 表名 WHERE ROWNUM =/</<= -- 获取特定记录
2.2 分页查询
格式:
-- 表达一
SELECT 列名 FROM
(SELECT 列名,ROWNUM 别名 FROM 表名 WHERE ROWNUM < x) 表别名
WHERE 表别名.别名 > y; -- 查询某一范围的记录
-- 表达二
SELECT 列名 FROM (SELECT 列名,ROWNUM 别名 FROM 表名)表别名
WHERE 表别名.别名 > x AND 表别名.别名 < y;
3 数据处理
DML 语言 数据操纵语言,可在表中执行插入、修改、删除 操作
事务 由若干项 DML 操作组成
3.1 插入数据 INSERT
格式:
INSERT INTO 表名(列名,列名,……)values(值,值,……)
INSERT INTO 表名 values(值,值,……) -- 简化写法,不推荐
事项:
- 一次只能向表中插入一条数据
- 值会添加到对应的列中,在简化写法中的列以默认形式排列
- 值中的字符型和日期型数据要在单引号中
- 对于没有值添加的列,默认用null填充,而简化写法中要声明插入的值为null
oracle 语法 从其他表拷贝数据
insert into emp2 select * from emp where deptno=30;
3.2 更新数据 UPDATE
格式:
update 表名
set 列名 = 值,列名 = 值,……
where
更新操作一定不要忘记 WHERE 条件,否则将更新所有记录
3.3 删除数据 DELETE
格式:
detele from 表名 where
实际开发中,删除可分为逻辑删除和物理删除
物理删除:直接将数据从数据库中删除
逻辑删除:用一个字段记录数据是否有效,一般由0,1进行记录。当逻辑删除时,使记录无效化,但数据在物理上还存在
逻辑删除本质上执行的是 update 语句
4 数据库的事务
事务是一组逻辑操作单元,使数据从一种状态变换到另一种状态
事务的组成:
- 一个或多个DML语句
- DDL语句和DCL语句没有事务
事务的操作:
提交(COMMIT)和回滚(RollBack)
事务的作用:
- 保证数据的完整性
- 数据发生改变后提交前可进行预览
示例:delete语句和update语句
执行时 将数据库中的元数据拷贝到缓冲区,写入新数据
如果执行 rollback 则将元数据在还原到数据库,取代新数据
如果执行 commit 则将缓冲区的元数据删除,保存新数据
4.1事务的特性
ACID 四大特性
- 原子性:事务的完成操作不可分割,一个事务中有多条DML语句要么都成功,要么都失败
- 一致性:一个查询的结果必须和数据库在查询开始的状态一致
- 隔离性:对于不同会话,未完成的事务必须不可见
- 持久性:事务一旦提交,数据的更改不可逆
5 约束
5种约束:
- not null 非空约束
- unique 唯一约束
- primary key 主键约束 = 非空+唯一
- foreign key 外键约束
- check 检查约束
5.1 表级约束和列级约束
作用范围:
- 表级约束作用在多个列上
- 列级约束作用在一个列上
定义方式:
- 列级约束必须跟在列的后面进行定义
- 表级约束不与列一起 单独定义
- 非空约束只能定义在列上
5.2 约束定义
5.2.1 非空约束
保证列值不为空,只能定义在列上
非空约束属于检查约束的一种
可以对约束自定义名称,否则系统会随机生成名称 SYS_字母+数字
格式:
列名 数据类型 not null,
列名 数据类型 constraint 自定义名 not null
5.2.2 唯一约束
保证该字段的值不重复
允许出现null,且多个null 不违反唯一约束
列名 数据类型 unique
列名 数据类型 constraint 自定义名 unique
5.2.3 主键约束
设计表时,会选取一个无意义列或唯一列作为主键,也可选取多个列作为联合主键(不推荐)
可以定义在列上,也可定义在表上
格式:
列名 数据类型 primary key
constraint 自定义名(一般以pk_开头)primary key(列名)
5.2.4 外键约束
主要作用:给多张表添加关联
关键字: FOREIGN KEY
外联关键字段:REFERENCES KEY
constraint 自定义名(一般以fk_开头)foreign key(关联列名)
references 关联表名(关联列名)
关联表名又称父表,使用外键约束的表又称子表,创建表时,要先创建父表才能创建子表
删除和修改
如果删父表的数据,该记录没有关联的子表,就可直接删除
如果有子表关联数据,则不能删除
设置父表记录删除时,关联子表的修改方式:
-- 子表
constraint 自定义名(一般以fk_开头)foreign key(关联列名)
references 关联表名(关联列名)on delete cascade
或
constraint 自定义名(一般以fk_开头)foreign key(关联列名)
references 关联表名(关联列名)on delete set null
-- 父表
列名 数据类型 primary key
on delete cascade 表示级联删除 删除父表的时候 将关联到它的子表的记录全部删除
on delete set null 表示级联更新 删除父表的时候 子表关联的数据记录 设置为null 或者为一个新值
5.2.5 检查约束
每一行必须满足的条件
列名 数据类型 check (检查条件)
constraint 自定义名(一般以ck_开头)check (检查条件)
5.3 修改约束
添加和删除约束(但不能修改已有约束)
ALTER TABLE 表名 add constraint 自定义名 约束格式 -- 添加
ALTER TABLE 表名 DROP constraint 约束名 --删除
有效或无效化约束
ALTER TABLE 表名 DISABLE constraint 约束名 -- 无效化
ALTER TABLE 表名 ENABLE constraint 约束名 -- 激活
添加 not null 约束(添加时使用 modify)
ALTER TABLE 表名 MODIFY (列名 数据类型 not null)
6 试图
试图是一种虚拟表
建立在已有的表的基础上,建立试图的表称为基表
6.1 创建试图
格式:
CREATE [OR REPLACE] VIEW viewname as (子查询)
6.2 修改试图
格式与创建试图相同,直接在子查询中进行修改,然后创建新试图
6.3删除试图
删除试图不会影响基表的数据
格式:
DROP VIEW viewname
7 数据库的其他对象
7.1 常见数据库对象
表 数据集
试图 从表中抽出的有逻辑相关的数据集
序列 连续的数字
索引 提高查询效率
同义词 给表和试图起的别名
7.2 序列
可供多个用户用来产生唯一数据的数据库对象
作用:
- 自动提供唯一数值
- 共享对象
- 为主要用户提供主键值
- 将序列值装入内存提高访问的效率
7.2.1 创建序列
-- 创建序列
CREATE SEQUENCE stu_seq
increment by 1 -- 步长 就是每次增加的值
start with 1 -- 开始值
maxvalue 99999999 -- 最大值
minvalue 0 -- 最小值
nocycle --是否循环 nocycle|cycle
nocache; --是否缓存 nocache|cache
-- 序列方法
-- nextval 和currval 均为伪列
-- nextval 返回序列中下一个有效值 任何用户都可以引用
-- currval 存放序列中的当前值
7.2.2 修改序列
做法:将创建序列的关键字换成 ALTER
可以修改序列的增量,最大值,最小值,循环,缓存
初始值不能修改,要改变初始值只能重新创建序列‘
7.3 索引
索引可以加速 Oracle 服务器的查询速度
7.3.1 创建索引
自动创建:
定义表时,使用主键约束(primary key)和唯一约束(unique)时都会创建一个唯一性索引
手动创建:
用户可以在其他列上创建非唯一索引,从而加快查询速度
CREATE INDEX 索引名 on 表名( 列名 )
7.3.2 什么时候创建索引
- 列中的值分布范围很广
- 列经常在where子句或链接条件中出现
- 表经常被访问而且数据量很大(访问数据大概为栈总数据的2%-4%)
7.3.3 删除索引
DROP INDEX 索引名
7.4 同义词、表
当用户需要访问其他用户的对象时,为了简化书写,可以给对象创建同义词
序列也可以创建同义词,但没有意义
格式:
CREATE SYNONYM 同义词名 FOR 用户名.表名; -- 创建
DROP SYNONYM 同义词名 -- 删除