Oracle-DDL语句、DML语句、事务、约束

一。几个概念

1.数据库对象：

①表: 存储的基本单元，有行列组成
②视图 :一个逻辑的数据集，数据来自一个或多个表
③序列: 产生主键值
④索引: 对数据库表中一个或多个列的值进行排序的结构，目的是提高查询的性能
⑤同义词: 给出对象的别名.

2.查询数据字典：

①描述用户拥有的表：
select * from user_tables;
②查看用户所拥有的数据类型：
select distinct object_table from user_objects;
③查看用户拥有的表、视图、同义词、序列：
select * from user_catalog;

3.数据类型：

类型	解释
varchar2(size)	变长字符数据，size为最大长度
char(size)	定长字符数据，size为长度
number(7,2)	7个字符长度，保留小数点后2位
date	日期和时间
long	可以保存最大长度为2GB的字符数据
clob	保存最大长度为4GB的单字符字节数据
RAW and LONG RAW	存储二进制数据
blob	最大为4GB的二进制数据类型
bfile	在外部文件中储存的二进制数据，最大为4GB

二。DDL语句

DDL（data definition language）数据库定义语言
包括CREATE、ALTER、DROP等。DDL主要是用在定义或改变表的结构，数据类型，表之间的链接和约束等初始化工作上。

1.create

创建表：

第一种：
create table emp(
ename number(4),
sal bumber(5,2),
job varchar2(20)
)

第二种：
create table test select * from emp;

第三种：用子查询建表
create table test
as
slect ename,empno,sal*12 salary
from emp
where empno=10;

2.alter

①增加新列：
alter table test
add (job varchar2(10));
会增加到最后一列。

②更改现存的列：
alter table test
modify (ename varchar2(10) );
如果现存的列有数据，不能更改；没有数据，可以更改.

③删除列
alter table test
drop column comm;

3.drop

drop table test;

表中所有数据和结构都被删除。
只有所有未决的事务都被提交，才能drop成功。
此表上所有的索引全部被删除。
此操作不饿能回滚，但是可以从回收站回收回来。

补充：回收drop后的表
第一步：show recyclebin; 查看回收站的表
第二步：flashback table " BIN$oxq89J+9TQWUDMAD4bCpQw==$0" to before drop;

4.truncate

truncate table test;

移除表中所有行的数据，释放表空间（delete不释放表空间）。
执行truncate后，不能回滚。

三。DML语句

1.insert

insert into emp values(6666,‘李四’,‘项目总监’,7839,to_date(‘2018-1-1’,‘yyyy-mm-dd’),2000,null ,20);

insert into test select * from emp;

2.update

①更新指定行
update test set deptno=10 where empno=7369;

②更新所有
update test set deptno=10

3.delete

①删除指定行
delete from test where empno=6666;

②删除所有行
delete from test;

四。rename、comment

1.rename：

更改表、视图、同义词、序列等对象的名称。

rename dept to department;
注意:操作用户必须是对象的拥有者或DBA。

2.comment

为一个表或视图添加注释。

comment on table test
is ‘这是测试表’;

查看表注释：
select * from user_tab_comments;

补充：
查看所有列的注释：select * from all_col_comments;
查看用户列的注释：select * from user_col_comments;
查看所有表的注释：select * from all_tab_comments;

五。事务

1.定义：

以第一个可执行的sql语句开始，
以下列任何一个sql结束：
①执行commit或者rollback
②执行ddl或者dcl
③用户退出
④系统崩溃

2.保存点：

①使用rollback在当前事务中做一个标记：rollback aa
②使用rollback to savepoint回滚到那个标记：rollback to aa

3.锁定：

①在并发事务之间，阻止可能产生的破坏性影响。
②不需要用户进行干预，自动使用。
③在事务期间使用，事务结束时释放。
④有两种模式：排他、共享

六。约束

①not mull 非空约束
②unique 唯一约束
③primary key 主键
④foreign key 外键
⑤check 定义一般性约束

建表的时候顺便建立约束：

create table student(
	stuno number(2),
	name varchar2(14),
	constraint stu_stuno_pk primary key(stuno),
	constraint emp_dept_fk foreign key(stuno) references dept2 (deptno),
	CONSTRAINT check_student_no CHECK (stuno BETWEEN 100 and 9999)
);

1.主键：

①.查询表的主键：(表名要大写，否则为空)

select cu.* from user_cons_columns cu, user_constraints au 
	where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' 
		and au.table_name = 'EMP';	

②.删除主键：

alter table emp drop constraint emp_no_pk;

③.加主键

alter table emp add constraint emp_no_pk primary key(empno);

2.外键：

①.查询外键：（表名必须大写，否则为空）

select constraint_name "外键名",  table_name "表名",R_constraint_name "参考约束名" 
	from user_constraints c 
		where c.constraint_type= 'R' and c.table_name='EMP2';

②.删除外键：

alter table emp2 drop constraint emp_dept_fk;

③.增加外键：
(父表的列必须是主键，否则会报异常ORA-02270: no matching unique or primary key for this column-list)
alter table emp2 add constraints emp_dept_fk foreign key(DEPTNO) references dept2(DEPTNO);
或者
alter table emp2 add constraints emp_dept_fk foreign key(DEPTNO) references dept2(DEPTNO) on delete cascade；
注意：列名一定大写，否则操作的结果和你想的根本不一样。

补充on delete cascade：

①对于alter table emp2 add constraints emp_dept_fk foreign key(DEPTNO)
references dept2(DEPTNO)语句， 如果删除父表（部门表dept）数据，根本删除不了（会报错，因为有子表的外键牵连）。
delete from dept where depto=40; 会报错。

②对于alter table emp2 add constraints emp_dept_fk foreign key(DEPTNO)
references dept2(DEPTNO) on delete cascade语句，
如果删除父表（dept表）数据，不但会删除dept表的数据，还会删除子表关联的数据。 delete from dept where
deptno=40; 会删除dept表中的部门为40的记录，也会删除emp表中与部门号为40相关的记录。

3.检查约束：

①.查询表有哪些约束：

select constraint_name "约束名",  table_name "表名",search_condition "约束条件"
	from user_constraints c 
		where c.constraint_type= 'C' and c.table_name='EMP2';

②.删除检查约束：

alter table emp2 drop constraint CK_EMP2_EMPNO;

③.增加检查约束：
建表时加check约束：

create table tb_supplier
(
  supplier_id       number,
  supplier_name     varchar2(50),
  contact_name      varchar2(60),
  /*定义CHECK约束，该约束在字段supplier_id被插入或者更新时验证，当条件不满足时触发。*/
  CONSTRAINT check_tb_supplier_id CHECK (supplier_id BETWEEN 100 and 9999)
);

已经有数据的情况下给表加check约束：

alter table emp2 add constraint ck_emp2_empno CHECK (EMPNO between 100 and 8000);

注意：必须保证表中数据符合即将添加的check条件，否则创建check约束失败。
④.启用约束：
ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;
⑤.禁用约束：
ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;