表操作

创建表

-- 创建新表
create table 表名(
 字段名 类型(长度),
    ...其他字段...
);
 -- 数据类型
	VARCHAR2(size) 可变长度的字符串，最大长度为size个字节；size的最大值为4000，最小值为1.必须指定
	NVARCHAR2(size)  可变长度的字符串，最大长度为size个字节；size的最大值为4000，必须指定
	NUMBER(p,s)
	LONG 可变长度的字符串，长度可大2G个字节
 DATE 有效日期范围从公元强4712年1月1日到公园后4712年12月31日
	RAW(size)长度为size字节的原始二进制数据，size最大值为2000字节，必须指定
	LONG RAW 可变长度的原始二进制数据，长度可大2G个字节
 CHAR(size)固定长度的字符数据，其长度为size个字节，size最大为2000个字节，而最小值和默认值是1
 NCHAR(size)固定长度，根据Unicode标准定义
 CLOB 一个字符大型对象，可容纳单字节的字符，不支持宽度不等的字符集，最大为4G
 NCLOB 一个字符大型对象，可容纳单字节的字符，不支持宽度不等的字符集，最大为4G
 BLOB 一个二进制大型对象，最大4G字节
	BFILE 包含一个大型二进制文件的定位器，其存储在数据库的外面；使得可以以字符流I/O访问存在数据库服务器上的外部LOB;最大4G字节。
-- 从其他表拷贝结构
create table 表名 as select 字段列表 from 已有表 where 1!=1;

修改表结构

修改表名

rename 原表名 to 新表名

修改列名

alter table 表名 rename column 列名 to 新列名

修改字段类型

alter table 表名 modify(字段 类型)

添加列

alter table 表名 add 字段 类型

删除列

alter table 表名 drop column 字段

删除表

drop table 表名;

约束

-- 五种约束  (约束就是对数据更加精确的控制，更加符合实际，满足我们的需求)
主键约束（PRIMARY KEY）
唯一性约束（UNIQUE）
非空约束（NOT NULL）
外键约束（FOREIGN KEY）
检查约束（CHECK）

主键约束（PRIMARY KEY）

 主键是定位表中单个行的方式，可唯一确定表中的某一行，关系型数据库要求所有表都应该有主键，不过Orcale没有遵守此范例要求，Orcale种的表可以没有主键（这种情况很少）
 1.键列必须具有唯一性，且不能为空。主键约束就相当于 UNIQUE+NOT NULL
 2.一张表只能有一个主键
 3.主键所在列必须具有索引（主键的唯一约束通过索引来实现），如果不存在，将会在索引添加的时候自动创建

唯一性约束（UNIQUE）

 唯一性约束可作用在单列或多列上，对于这些列或列组合，唯一性约束保证每一行的唯一性。
 UNIQUE需要注意：
 1.对于 UNIQUE约束来讲，索引是必须的。如果不存在，就自动创建一个（ UNIQUE的唯一性本质上是通过索引来保证的）
 2.UNIQUE允许null值， UNIQUE约束的列可存在多个null。这是因为， unique 唯一性通过 btree索引来实现，而 btree索引中不包含null。当然，这也造成了在 where 语句中用null值进行过滤会造成全表扫描。

非空约束（NOT NULL）

 非空约束作用的列也叫强制列。顾名思义，强制键列中必须有值，当然键表时候若使用 default 关键字指定了默认值，则不能输入

外键约束（FOREIGN KEY）

 外键约束定义在具有父子关系的子表中，外键约束使得子表中得列对应父表的主键列，用以维护数据库的完整性。不过出于性能和后期的业务系统的扩展的考虑，很多时候，外键约束仅出现在数据库的设计中，实际会放在业务程序中进行处理。
 外键约束需注意一下几点：
 1.外键约束的子表中的列和对应父表中的列数据类型必须相同，列名可以不同。
 2.对应的附表列必须存在主键约束或唯一约束
 3.外键约束列允许 NULL值，对应的行就称了孤行。

检查约束（CHECK）

 检查约束可用来实施一些简单的规则，比如列值必须在某个范围内。检查的规则必须是一个结果为 true或 false的表达式

根据要求创建表及约束

表名：tb_user(用户表)

编号	字段名	字段类型	说明
1	userid	number(5)	用户id，主键
2	username	varchar2(30)	用户名，非空，4~20个字符
3	userpwd	varchar2(20)	密码，非空，4~18个字符
4	age	number(3)	年龄，默认18，值大于等于18
5	gender	char(2)	性别，默认‘男‘，只能是男和女
6	email	varchar2(30)	邮箱，唯一
7	regtime	date	注册日期，默认当前日期

注意：根据系统的编码去更改字符长度比如，utf-8编码中

create table td_user(
	userid number(5) primary key,
    username varchar2(30) check(length(username) between 4 and 20) not null,
    userpwd varchar2(20) not null check(length(userpwd) between 4 and 18),
    age number(3) default(18) check(age>=18),
    gender char(2) default('男') check(gendar in('男','女')),
    email varchar2(30) unique,
    regtime date default(sysdate)
);

表名：tb_txt（文章表）

编号	字段名	字段类型	说明
1	txtid	number(5)	文章编号，主键
2	title	varchar2(32)	文章标题，非空，长度为4~20字符
3	txt	varchar2(1024)	内容，最大长度为1024
4	pubtime	date	发布日期，默认当前日期
5	userid	number(5)	作者，外键，参考用户表的用户id，删除时，自设为null

create table tb_txt(
	txtid number(5) primary key,
 title varchar2(32) not null check(length(title)>=4 and length(title)<=30),
    txt varchar2(1024),
    pubtime date default(sysdate),
    userid number(5) references tb_user(userid) on delete set null
);

还有一种方法创建约束 （了解）
create table tb_txt(
    txtid number(5),
    title varchar2(32),
    txt varchar2(1024),
    pubtime date default(sysdate),
    userid number(5),
 constraint pk_txt_id primary key(txtid),
 constraint ck_txt_title check (length(title)>=4 and length(title)<=30),
 constraint fk_txt_user_id foreign key(userid) references tb_user(userid) on delete set null
);

追加约束

-- 追加主键约束
alter table 表名 add constraint 约束名字 primary key (要加约束的字段);

追加检查约束

alter table 表名 add constraint 约束名字 check(
 条件 -- 比如：length(要加约束的字段) between 4 and 20
)

追加非空约束

alter table 表名 modify(要加约束的字段 constraint 约束名字 not null);

追加唯一约束

alter table 表名 add constraint 约束名字 unique(加约束的字段);

追加默认约束

alter table 表名 modify(字段 default(值));

追加外键约束（强制不让删）

alter table 表名 add constraint 约束名字 foreign key(加约束的字段)references 表名2(字段)

追加外键约束（自动设为null）

alter table 表名 add constraint 约束名字 foreign key(加约束的字段)references 表名2(字段)on delete set null;

追加外键约束（级联删除）

alter table 表名 add constraint 约束名字 foreign key(加约束的字段)references 表名2(字段)on delete cascade;

修改约束

修改约束 其实就去 先删除后修改
先删除：
alter table 表名 drop constraint 约束名字;
然后再添加约束（参考上方追加约束）

约束的启用和禁用

启动/禁用（enable/disable）是否对新变更的数据启用约束验证
验证/非验证（validate/novalidate）是否对表中已客观存在的数据进行约束验证
enable validate -- 默认的约束组合状态，无法添加违反约束的数据行，数据表中也不能存在违反约束的数据行
enable novalidate -- 无法添加违反约束的数据行，但对已存在的违反约束的数据行不做验证
disable validate -- 可以添加违反约束的数据行，但对已存在的违法约束的数据行会做约束验证
disable novalidate -- 可以添加违法约束的数据行，对已存在的违法约束的数据行也不做验证

删除约束

alter table 表名 drop constraint 约束名 ;

DML

DML介绍

-- DML数据操控语言，用于操作数据库对象中包含的数据，也就是说操作的单位是记录
-- Oracle数据库的DML表数据的操作有三种：
insert -- 插入：向数据库表插入一条记录
update -- 更新：删除数据表中的一条或多行的记录
delete -- 删除：用于修改已存在表中的记录的内容
merge -- 合并

DML之insert

语法结构

-- 默认插入：数据必须和表结构里面字段顺序以及个数保持一致;当某个字段存在默认值时，也必须给出值。
insert into 表名 values(1,'值1','值2',null,sysdate);
commit; -- commit 提交事务 如果不些commit的话 那么这条数据不会立马插入到我们的表中，只是放在缓存中。
-- 如果不用默认时间就用 to_date('1990-1-1','yyyy-mm-dd')

-- 在创建表的同时，添加记录
-- 举例：现有一张表，里面存放了字段1，字段2，字段3，字段4.现在我要创建一张新表，发现新表需要存储的记录，在老表中有。
create table 新表明 as select 字段1,字段2,字段3 from 老表名;

-- 从其他数据表中拷贝数据进来（假设我要在我的新表里面插入数据，发现这些数据在老表中就有存储）
insert into 新表 value(select 字段 from 老表 where 条件);


-- 在添加时指定列和顺序
 -- 假设 我表中有 五个字段 字段1，字段2，字段3，字段4，字段5；但是我现在只想给 字段1，字段3，字段5中添加数据
 insert into 表名(字段1,字段3,字段5)values('值','值','值');
 -- 以上的字段的顺序可以随意颠倒，但values中的值就要跟着前面的字段顺序去写入
 -- 如果某些字段，值可以为null或者有默认值，此时我们可以不指定。（例如：上方中我的字段2和字段4就有默认值或者可以为null）
 -- 如果某个字段有默认值，则未指定时使用默认值填充
 -- 如果某个字段没有默认作，但可以为null，未指定时，则使用null填充
 -- 如果某个字段既可以为null，又有默认值，若未指定，则使用默认值填充，想要设为null，必须手动指定。
 
 
 -- 同过指定列的方式，同其他表中查询数据，添加到表中
 insert into 表名1(字段1,字段2,字段3)(select 字段1,字段2,字段3 from 表名2 where 条件)
 -- 此时就不需要哪个字段跟哪个字段对应了，只要字段的数据类型兼容就可以插入
 
 -- 在添加的记录中，存在外键关联时需要注意，可以采取想查询后添加的方式。如下：
 insert into 表名1(字段1,字段2,字段3)values(01,'刘德华',(select 字段3 from 表名2 where 条件));

DML之update

-- 通过update语句可以更新（修改）表中的记录值。
update 表名 set 字段1=值1 where 条件;
-- 以上就是update的语法结构，当需要更新多个字段值时需用逗号隔开。

-- 也可以先列出需要修改的字段，通过查询子句设定。如下：
update 表名1 set(字段1,字段2)=(select '值1','值2' from dual) where 条件;

DML之delete

-- 通过delete语句可以删除表中的记录；语法结构如下：
delete from 表名1 where 条件;

-- 当两张表存在主外键关系时，如果删除主表（被参考的表）中被引用（被参考）的记录时会出现以下三种情况：（参考上面的追加外键约束）
	-- 如果默认外键处理方式，强制不让删
	-- 如果是 on delete cascade 一起删除
	-- 如果是 on delete set null 自动设为null

DML之merge

-- merge 合并数据
--语法：
 merge into  a表
 using  b表
 on 条件
 when matched then
 update set a.列 = b.列
 when not matched then
 insert values(b.列...)

索引

什么是索引？

-- 索引就类似于书的目录，根据目录查询内容会快很多，提高数据查询效率

只有当数据量特别大的时候才会使用索引

创建索引有俩种方式

1、Oracle会自动为主键和唯一键创建索引

2、手动创建约束。
那么表中什么样的字段应该添加索引？
在查询的时候，经常被用来做为查询的字段，应该添加索引

-- 创建索引
create [unique]|[bitmap (位图索引)] index 索引名字 on 表名（字段,[字段,字段。。]）;
create index ename_index on emp(ename);
select * from emp where ename='SMITH';
-- 索引是看不见摸不着的，现在我们的的表数据太少，看不出效果，其实我们已经给ename做了索引，这样查询远比我们之前没做索引的时候要快很多。

普通索引、唯一索引、复合索引都是B树结构。

删除索引

-- 删除索引
drop index 索引名字;
drop index ename_index;
-- 自动创建的约束是无法删除的，但是在删除主键约束和唯一约束的时候才会被删除。

视图

什么是视图？

-- 我们之前做的子查询就是一个视图
-- 视图也就是虚表，实际上是一个命名的查询，用户改变数据的显示形式，简化查询，访问视图与表的访问方式一样。

视图有什么好处？

-- 1、可以限制对数据的访问，让用户通过视图可以看到表中的一部分数据
-- 2、可以使复杂的查询变得简单
-- 3、提供了数据的独立性，用户不知道数据来源处
-- 4、提供了对相同数据的不同显示

-- 视图，就是一个虚表，我们可以从这个表中查询数据
-- 视图，就是一个命名的查询语句
-- 任何有效的查询语句都可以作为视图

创建视图

create view 视图名字 as 查询语句;
create view HR_VIEW as select * from emp;
create view XM_VIEW as select empno,ename,mgr,hirdate,hirdate,deptno from emp;

分配创建视图权限

-- 一般为了方便，我们都是直接赋值dba权限给用户
grant dba to scott;
-- 或者只赋值一个创建视图的权限
grant view to scott;

查看视图表结构

-- 我们可以在命令行窗口模式下查看表的结构，也可以查看视图的结构
desc emp;
desc HR_VIEW;

替换视图

-- or replace:如果视图存在，则替换旧视图。
create or replace view HR_VIEW as select * from emp;

删除视图

drop view 视图名字;
-- 删除视图不会影响原来的数据。

注意事项

-- 我们平常在做表连接的时候，会出现相同的列，但是不影响我们查询。但是在创建视图的时候，不能出现相同列，这个时候我们就不能创建视图了。
-- 比如
create or replace view view_empinfo
as
select e.*,d.* from emp e,dept d where e.deptno = d.deptno;  -- 这条查询语句是没有问题的，但是在创建视图的时候，就会报错。
-- 这个时候，我们就需要用别名，将每个列都查询出来 或者相同的列只显示一个
create or replace view view_empinfo
as
select e.empno,e.ename,e.job,e.mgr,e.sal,d.dname,d.loc from emp e,dept d where e.deptno = d.deptno;

视图有多方便？

-- 假如我们在计算员工年收入的时候，我们每次都需要查询，非常麻烦，那么我们就可以把年收入计算出来做成视图，以后就只要在视图中查询就可以了
create or replace view view_name
as
select e.empno,e.ename,e.job,e.mgr,e.sal,d.dname,d.loc,sal+nvl(comm,0) 年薪 from emp e,dept d where e.deptno = d.deptno;
-- 那么我以后查询年薪的时候就可以从视图中查询了
select 年薪 from view_name;

视图可以操作数据吗，可以做DML操作吗？

-- 视图一般用于查询，不建议DML操作
-- 如果我需要创建一个视图，并且不能进行DML操作，那么我就要用到下面的写法
-- with read only 创建只读视图，不能执行DML操作
create or replace view view_name
as
select * from emp
whih read only;

-- 同样的，如果我们需要对视图进行DML操作时，那么我们可以省略 whit read only 或者 改为with check option
create or replace view view_name
as
select * from emp
[whih check option;]

对视图进行DML的需要注意什么？

-- 1、如果违反基表的约束条件时，则不能更新。
-- 2、如果视图中包含链接操作符、distnct关键字、聚合函数、集合操作符或group by 子查询的时候，不能更新
-- 3、如果视图存在伪劣的时候，则不能更新

视图和表的区别

/*
视图是一个虚表，不占空间。
而表这个对象是真实存在于数据库里面的，占空空间。
键值对视图不能增删改。
视图不能创建索引。
*/

序列

什么是序列？

-- 序列是Oracle自动给我们生成的一个序列的对象

序列什么用？

-- 序列一般用于生成主键或者达到主键自增的效果

创建序列

-- 语法
create sequence 序列名字(
 start whit 1 -- 表示从1开始
    increment by 1 -- 表示每次执行的时候，增加多少。一般用1
    minvalue 1 -- 序列最小值，一般用了 start whit 不会用minvalue（可以省略）
    mixvalue 10 -- 序列最大值，表示序列到最大值是停止（可省略）
    cache|nocache -- 缓存，可省略，默认为 20 , 省略不写就是 nocache
 cycle|nocycle -- 是否生成循环，一边和mixvalue使用，表示到mixvalue后从start whit 使用，可省略，省略不写就代表 nocycle
)

序列的俩个属性

-- nextval:取序列的值，并且序列的值增加。
-- currval:取序列当前的值。不能直接去当前值，需要执行nextval后才可以

修改序列

alter sequence 序列名字 maxvalue 5000;
-- 注意，不能修改序列的 start whit 值，但是能修改maxvalue、cache、cycle。

删除序列

drop sequence 序列名字;

如何利用序列创建主键自增？

-- 创建一个序列
create sequence mysqe
start whit 1
increment by 1
insert into 表名 values(myseq.nextval,'值','值'...)

如何查看序列的值？

-- Oracle为我们提供了一个虚表 dual,序列的值就保存到dual中
select myseq.nextval from dual;
select myseq.currval from dual;

注意：如果序列达不到顺序自增的话怎么办？

-- 关闭延迟技术
ALTER SYSTEM SET deferred_segment_creation=FALSE;

同义词

什么都是同义词？

-- 同义词，就是数据库对象的一个别名，可以简化访问其他用户的数据库对象。缩短了对象名称的长度，同时屏蔽了对象的名称，使用户不知道最终的数据来源与那个对象。

创建同义词的语法

create [or replace] synonym 同义词名字 for 表（对象）; 发音：思来内幕
create [or replace] synonym stu for student;
-- 那么以后查询的时候，我就可以这么使用
select * from stu;

赋值创建同义词的权限

grant create any synonym to 用户名;

删除同义词

drop synonym 同义词;

事务

什么是事务？

-- 一组相关的数据改变SQL语句，这组SQL语句对数据的改变要么同时成功，要么同时失败。
-- 自我理解：如果理解事务这个词呢？在数据库中，事务指的是可以实现数据库中的存储的数据的改变。也就是说insert、update、dalete语句。一组sql语句，可以实现数据的改变。

/*
事务必须具备以下四个属性，简称ACID 属性：
原子性（Atomicity）：事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行
一致性（Consistency）：当事务完成时，数据必须处于一致状态
隔离性（Isolation）：对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务
永久性（Durability）：事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务的永久性

例如银行转账。
孙昊账户有1000元，大哥0元，所谓原子性就是孙昊给大哥转钱，孙昊转钱和大哥收钱，这两步必须是同时发生的，如果某一步出错或者不发生，业务就出现问题，那么这个事务肯定出错。所谓一致性就是，孙昊和大哥一共1000元，转完钱后还是1000元。 
*/

查看事务

show autocommit;

开启或关闭事务

set autocommit on|off;

显式事务

set autocommit off; -- 关闭事务的自动提交
-- 手动调用
commit; -- 提交
rollback; -- 回滚 类似于撤销

隐式事务

set autocommit on; -- 打开事务的自动提交
-- 打开后 create、drop、grant等操作事务是自动提交的
-- 每一个insert、update、delete执行后都会提交到数据库

事务保存点

-- 语法 
savepoint 名字;
-- 用法
insert into stu values(1,'aa');
savepoint s1;
insert into stu values(2,'aa');
savepoint s2;
insert into stu values(3,'aa');
savepoint s3;
insert into stu values(4,'aa');
savepoint s4;
-- 假如在第三条语句出错了，我单纯的用回滚的话，那么我之前的语句就需要再次添加了。那么我就可以这么回滚
rollback to s3;

锁

锁的概念

-- 锁是用来对数据库的共享资源并发机制的控制。也就是说锁是用来保护正在修改的数据，知道这些数据提价或者回滚事务之后，其他用户才可以进行修改。如果没有锁，那特么就乱了套了。假如十八和艳子同时对一张表进行操作，再都没进行事务提交或回滚的情况下，此时我数据库该听谁的命令？总不能谁漂亮听谁的吧？听十八的艳子不服气，听艳子的十八又不服气。总不能让俩人打一架吧，谁赢听谁的？所以只有艳子提交了事务之后，十八才能更新数据。

锁的类型

-- 分为两种，行级锁和表级锁。

/* 行级锁：是一种排他锁，防止其他事务修改此行
 在使用 insert、update、delete、select .... for update[WAIT n （等几秒）| NOWAIT（不等待）|skip locked(跳过)]语句的时候，Oracle会自动启动行级锁。
 当使用commit或者rollback的时候才会释放锁
 假如一个用户占据锁的时候过长怎么办? 利用管理杀掉该session
	select * from v$lock; 查看锁 拿到sid
	select sid,serial# from v$session where sid='141'; 查看sid和 serial
 然后执行
	alter system kill session 'sid,serial';
*/
/*
表级锁:锁定整个表
lock table 表名 in mode(mode就是锁的模式) mode;
共享锁：只允许用户select，不能做insert、update、delete操作，多个用户可以同时对同一张表进行表级锁
行共享（row share） -- 禁止排他锁定表
行排他（row exclusive） -- 禁止使用排他锁和共享锁
共享行排他(SHARE ROW EXCLUSIVE) – 比共享锁更多的限制，禁止使用共享锁及更高的锁
排他(EXCLUSIVE) – 限制最强的表锁，仅允许其他用户查询该表的行。禁止修改和锁定表
*/

更多推荐

【Oracle 数据库】奶妈式教程day15 DDL、DML、索引、视图、序列、死锁这一篇就够了