oracle的学习Ⅳ（create、insert、update、delete、约束、事务、视图、索引、序列号）

た入场券 2022-12-13 07:04 103阅读 0赞

# 一、create（DDL） #

### 方法一： 通过查询建表 ###

**create table tablename as select;**

create table t1 as select * from emp;		// emp查询的结果赋给t1
    create table t2 as select ename,sal from emp where deptno=20;
    create table t3 as select deptno,avg(sal) avgsal from emp group by deptno;
    create table t4 as select * from emp where 1=2;		// 复制emp表结构到t4

### 方法二： ###

**create table tablename  
(columnname datatype \[constraint\],);**  
**表名与列名命名要求**： 字母、下划线 开头 ，知名见意，最长不要超过30个字符。

**常用数据类型**： 数值型 number(a\[,b\])、字符型 char(n) ,varchar2(n)、日期型 date dd-mon-yy

要注意的是这里使用的**varchar2**定义的是一个**动**长字符串，而**char**定义的是一个**固定**的字符串，当然**varchar**和**varchar2**可实现一样的效果但oracle里面对**varchar2**的支持更好，所以更好使用**varchar2**

create table student
    (s_id number(12),
     s_name varchar2(20));

# 二、insert（DML） #

### 一次添加一条记录 ###

**insert into tablename\[(列名1……)\] values(值1,);**

insert into dept(deptno,dname,loc) values(10,'a','linyi');
    
    insert into dept(dname,loc,deptno) values('a','linyi',10);	
    // 对dept插入新值，值对应好其目标字段就可忽略字段顺序
    
    insert into dept values(10,'a','linyi');

### 一次添加多条 ###

**insert into tablename\[(列名1……)\] select;**

insert into dept(deptno,dname) select 12,'c' from dual union select 13,'d' from dual;	
    // 两次查询结果的并，插入到dept表内

# 三、update（DML） #

**update tablename set columname1=新值\[……\]\[where\];**

update emp set sal=sal*1.1;			// 给emp表里面的全部sal乘1.1
    
    update emp set sal=sal*1.1 where deptno=10;  // 给emp表里面部门号为10的全部sal乘1.1

# 四、delete（DML） #

**delete from tablename \[where\];**

delete from emp;		// 删掉emp表内的所有记录
    delete from emp where deptno=10;	// 删掉emp表内deptno为10的记录

# 五、约束 #

**约束：用来保证表中数据的准确性。** 包含有**主键、外键、唯一性、非空、检查**

### 主键约束 ###

**primary key** ： **列值不能重复 不能为空。一个表最多只能有一个主键约束 。**

create table student
    (s_id number(12) primary key,	// 一个表内只能有一个主键，但一个主键可对应多个列
     s_name varchar2(20));

### 唯一性约束 ###

**unique**： **列值不能重复,可以包含多个空值 。**

create table student
    (s_id number(12) primary key,
     s_name varchar2(20) unique );		// 可有多个null，因为null != null

### 非空约束 ###

**not null** ： **列值不能为空。**

create table student
    (s_id number(12) primary key,
     s_name varchar2(20) unique not null );	// 这里的unique和not null就类似于primary key

### 检查约束 ###

**check(条件表达式) ： 列值必须满足指定条件表达式的需求，null不在其检查范围内。**

create table student
    (s_id number(12) ,                      // 列级约束
     s_name varchar2(20) unique not null，
     s_gender char(1) check( s_gender in('f','m'))，	// m为男，f为女，这里的null可以插入
     primary key(s_id)		// 表级约束
     );

### 外键约束 ###

**foreign key(约束列) references 引用表(引用列)** ： **外键约束列列的取值必须在引用表引用列的列值范围内，外键为约束列，约束列所在的表为从表，引用列所在的表为主表**。

create table course
    (c_id varchar2(12) primary key, 
     c_name varchar2(12)
    );
    
    create table grade
    (s_id number(12) references student(s_id), 
     c_id varchar2(12) references course(c_id), 
     score number(4,1),
     primary key(s_id,c_id)                         // 表级约束
    );

# 六、事务 #

### oracle ###

**事务是一个完整的操作**，当操作完成后，数据才会完全呈现。**DML（insert、update、delete）语句不是完整的操作**，需要一个确认（提交commit，回滚rollback）的过程;而**DDL（create、alter、drop）语句，每一个都是一个完整的操作。**（例如：在执行DDL时会在DDL的前后添加autocommit，则会把DDL值前未commit的数据commit）。要注意的是在使用**exit**退出sql命令窗口时**DML语句会autocommit**，而**强制退出则会rollback**

### 与mysql的区别 ###

在**sqlserver**中需要显示的**开启事务**，**gegin tran，commit，rollback**。若**不含有transaction**，其中**insert、update、delete执行完后就自动提**

# 七、视图 #

**视图**本质是一个**虚表，其不包含数据**，所有的数据都来源于创建视图的基表

### 创建视图 ###

*  创建的时候必须要有**相应的权限**，需要遭管理员用户下操作、授权

grant create view to scott;			// 授权，同时对视图的其他操作也授权了

**语法**：`create or replace view viewname as select [with check option|with read only];`

注意这里的**create or replace**是为了**避免重名**；**with check option**的作用是**对**加入视图的**数据进行约束**，也就是说它一定要符合select里面的约束；**with read only**是创建**只读视图**

例如：

// 只能对view2加入deptno为10的数据
    create or replace view2 as select empno,ename,sal,deptno from emp where deptno=10 with check option;

### 删除视图 ###

drop view viewname;

### 视图更名 ###

rename viewname1 to viewname2;

# 八、索引 #

**索引**是为了**提高查询效率**，而列的重复率越低越适合创建索引。**对主键约束列（primary key）和唯一性约束（unique）来说系统会自动创建唯一性索引**

### 创建索引 ###

**语法**：`create [unique] index index_name on tablename(columnname……);`

create index idx_ename on emp(ename);		// 单列的索引
    create index idx3 on emp(lower(ename));  //基于函数的索引.
    create index idx1 on emp(ename,deptno);		// 复合列的索引
    create index idx2 on emp(ename,deptno,sal);		// 复合列的索引

### 索引的使用 ###

使用的原则：要尽量让**索引列出现在where子句里面**，且作为**where子句的第一个的条件**；如果当有**多个索引列**时，那么**各列则在where子句里面按顺序出现**  
例如：

create index idx2 on emp(ename,deptno,sal);		// 复合列的索引
    
    select * from emp;			// 一定不会使用索引
    select * from emp where deptno=10;	// 一定不会使用索引
    select * from emp where ename='SMITH';	// 可能会使用索引
    select * from emp where ename='SMITH' and sal>2000;	// 一定不会使用索引
    select * from emp where deptno=10 and ename='SMITH';	// 一定不会使用索引
    select * from emp where ename='SMITH' and deptno=10;	// 可能会使用索引
    select * from emp where lower(ename)='smith';	// 一定不会使用索引
    select * from emp where ename='SMITH' and deptno=10 and sal>2000;	// 可能会使用索引

### 删除索引 ###

drop index index_name;

### 重建索引 ###

alter index index_name rebuild;

# 九、序列号 #

**序列号**用来**给主键约束列自动生成数值的**

### 序列号的使用 ###

*  语法：`create sequence sequence_name [start with m][increment by n][maxvalue a | nomaxvalue][minvalue b | nominvalue][cycle | nocycle][cache N | nocache]`

> start with m – 序列号从m开始  
> increment by n – 序列号增长的步长  
> maxvalue a – 设置序列的最大值  
> nomaxvalue – 没有序列的最大值，default  
> minvalue b – 设置序列的最小值，这于start with无关  
> nominvalue – 没有序列的最小值，default  
> cycle – 当到序列最大值的时候，循环到最小值  
> nocycle – 不循环，一直增长  
> cache N – 设置缓存的个数，最小为2  
> nocache – 不设置缓存的个数，默认为20

create sequence seq2
    start with 2
    increment by 2
    maxvalue 20
    minvalue 2;
    
    select seq1.nextval from dual;		// 在创建后必须要用nextval进行初始化，nextval表示下一个值
    select seq1.currval from dual;		// currval表示当前值

### 序列号的使用 ###

序列在使用的时候，**必须要用nextval进行初始化**

create sequence seq2
    start with 2
    increment by 2
    maxvalue 20
    minvalue 2;
    
    create table test(id int primary key);	// 创建test表
    insert into test values(seq1.nextval);	// 在表内的主键上使用序列号

### 删除序列号 ###

drop sequence sequence_name;

# 十、其他 #

**“@+绝对地址”** 可以执行sql的文本文件

@d:/createTables.txt		// 文件的执行