DDL&DML

一、表设计（DDL）
1、设计表：
遵循三范式
确定表名
确定字段名 类型+约束（主键、外键、非空、默认、检查自定义规则、唯一）
注：主键： 唯一标识一条记录（唯一且非空）
唯一： 唯一
非空： 不能为空
默认： 当没给值是设定默认值
外键： 参考其他表(自己)的某个（些）字段
检查： 自定义规则
2、创建表

• 创建新表（不加约束）

--创建表
--create table 表名（
--字段名 类型(长度) 
--...其他字段....
--);
create table t_user (
       --序号
       ssid number(5) ,
       --姓名
       ssname varchar2(30),
       --密码
       spwd varchar2(10),
       --性别
       gender char(3)
);

2、创建表

• 创建表（同时创建约束+默认名称）字段后直接给约束
  简单，但是后期不方便排错

--表+约束 字段后直接给约束 
--创建学生表
create table s_student(
       --学号 主键 唯一非空
       sid number(8) primary key,
       --学生名字不能为空
       sname varchar2(20) not null,
       --年龄不超出范围【18,48】
       sage number(3) check(sage between 18 and 48),
       --男或女
       sgender varchar2(3) check(sgender in('男','女')),
       --邮箱
       mail varchar2(30) unique      
)
--注意为小括号,中间用逗号隔开，最后一个不用逗号

• 创建表（同时创建约束+指定名称） 在字段后指定约束名字添加约束，如：
  后期方便排错，推荐使用

--学号 主键 唯一非空
sid number(8) constraints pk_sid primary key,
--学生名字不能为空
sname varchar2(20) constraints sname_notnull not null,

• 创建表（同时创建约束+指定名称） 在结构的最后指定约束
  推荐， 便于后期排错

--表结构最后定义约束
--创建班级表
create table classes(
       classid number(3) primary key,
       cname varchar2(5) not null
)
--创建学生表
create table s_student(
       --学号 主键 唯一非空
       sid number(8) ,
       --学生名字不能为空
       sname varchar2(20) ,
       --年龄不超出范围【18,48】
       sage number(3) check(sage between 18 and 48),
       --男或女
       sgender varchar2(3) check(sgender in('男','女')),
       --邮箱 
       mail varchar2(30),
       --班级
       classid number(3),
       --定义约束  指定名字,指定约束 ,指定字段
       constraints pk_sid primary key(sid),
       constraints sname_notnull check(sname is not null),
       --外键约束
       constraints fk_classid foreign key(classid) references classes(classid) on delete set null      
)

• 创建表（追加创建约束+指定名称） 表结构之后追加约束

--表结构后面追加约束
alter table s_student add constraints class_unique unique(classid);
--删除约束
alter table s_student drop constraints class_unique ;

• 加入注释

--加入注释
comment on table t_user is '学生表';
comment on column t_user.ssid is '序号';
comment on column t_user.spwd is '密码';
comment on column t_user.gender is'性别';

• 从已有表中拷贝

--已有表中拷贝
--create table 表名 as select 字段列表 from 已有表 where 1!=1;  
--只拷贝结构不拷贝数据
create table copy_t as select * from t_user where 1!=1;
--结构和数据全部拷贝
create table copy_t as select * from t_user ;

• 删除表

--不存在主外键约束：
--drop table 表名 删除表
drop table copy_t;
--存在主外键约束：
--1.先删除从表,再删除主表
drop table s_student;
drop table classes;
--2.直接删除主表,级联删除主从表之间约束     
--若表中存在主外键约束，则不法直接删除，加上cascade contraints可以删除关联的外键约束并且不影响表的数据
drop table s_student cascade contraints;

• 删除表中数据

--不存在主外键关系：
--指定对应数据，直接删除
delete from s_student where sid=2;
--存在主外键关系:
--(1.)先删除从表中引用主表中当前这条数据的那些数据,然后再删除主表中的数据  
-默认则从表中关联主外键关系的数据为空
delete from classes where classid=2;
--(2.)直接删除主表中的数据,一起把从表中引用了当前要删除的主表中的数据的这些子记录删除
--前提是创建主外键关系时生了级联删除on delete cascade--外键
constraints fk_classid foreign key(classid) references classes(classid) on delete cascade;
--(3.)在删除主表中数据的时候,从表中引用了这个数据的子记录设置为null 
 --外键
constraints fk_classid foreign key(classid) references classes(classid) on delete set null ;   

• delete与数据截断的区别

--删除数据  delete会开启事务 
delete from tb_student;
delete from clazz;
--数据截断  truncate  不会开启事务
--如果存在主从表关系,检查整个表结构是否有被引用,如果有结构上的引用就不能删除  
truncate table tb_student;
truncate table clazz;

• 修改表的结构

--修改表名 
rename tb_txt to tb_txt_new; 
--修改列名 
alter table tb_txt_new  rename column  txtid to tid; 
--修改类型 
alter table tb_txt_new modify(tid varchar2(20)); 
--添加列 
alter table tb_txt_new add col varchar2(30); 
--删除列 
alter table tb_txt_new drop column col;
--查看结果
select * from tb_txt_new; 

3、序列
定义
数据库表中的主键值有的时候我们会用数字类型的，并且自增。Sequence 是oracle提供的用于产生一系列唯一数字的数据库对象。
mysql、sql server创建表的时候很容易实现。由于oracle中没有设置自增列的方法，所以我们在oracle数据库中主要用序列来实现主键自增的功能。
使用工具|程序管理流水号，序列在创建时 没有与表关联 ,在操作数据时 与表关联

• 创建序列

--create sequence 序列名 start with 起始值 increment by 步进;
create sequence seq_classid start with 100 increment by 2;

• 使用

--1)、currval :当前值
--2)、nextval:下个值
select seq_classid.nextval from dual;
select seq_classid.currval from dual;
insert into clazz values(seq_classid.nextval,'27期','java');
--删除序列
--drop sequence 序列名

二、DML
DML（Data Manipulation Language 数据操控语言）用于操作数据库对象中包 含的数据，也就是说操作的单位是记录，主要有insert、delete、update语句。
1、事务
事务的定义和特点
事务是指作为单个逻辑工作单元执行的一组相关操作。这些操作要求全部完成或者全部不完成。使用事务是为了保证数据的安全有效。
Oracle 默认的隔离级别是 read committed。（读已提交）
事务有一下四个特点：（ACID）
1）、原子性（Atomic）：事务中所有数据的修改，要么全部执行，要么全部不执行。
2）、一致性（Consistence）：事务完成时，要使所有的数据都保持一致的状态，换言之：通过事务进行的所有数据修改，必须在所有相关的表中得到反映。
3）、隔离性（Isolation）：事务应该在另一个事务对数据的修改前或者修改后进行访问。
4）、持久性（Durability）：保证事务对数据库的修改是持久有效的，即使发生系统故障，也不应该丢失。
事务的开启:
还行DML语句时候 insert update delete
事务的结束:
提交：1）.ddl语句 2）.正常执行完 DCL 语句 C 3）.正常退出客户端 4）.commit
失败: 1）.rollback 2）.非法退出客户端
注意：rollback 只能对未提交的数据撤销，已经 Commit 的数据是无法撤销的，因为 commit之后已经持久化到数据库中。
2、insert

--insert into 表名 values(和表结构顺序和个数和类型一致的数据，可以手写也可以从别的表中获取的);
insert into  tb_student values(4,'hah',18,'男',21324324,null);                   
insert into tb_student(sid,sname) values(seq_classid.nextval,(select ename from emp where sal=800));              
--insert into 表名 select 查询列 from 源表 where 过滤数据;
insert into copy_emp select * from emp;
select * from copy_emp;

3、update

--update 表名 set 字段=值 [,....] where 过滤行记录;
update clazz set subject='大数据';                       
update clazz set subject='py' where classid=106; 

4、删除

--delete [from] 表名 where 过滤行记录
delete from clazz;
delete tb_student where sid=3;