【oracle】oracle创建表、创建索引、创建自增id

说实话oracle实在用不习惯，虽然已经使用近两年了。
可能是习惯问题吧！毕竟mysql和mssql使用的更频繁

1、oracle创建表

创建表还是比较简单的

-- oracle 创建数据库表
CREATE TABLE test_user 
   (	
	id NUMBER(20,0) NOT NULL ENABLE, 	
	name NVARCHAR2(50), 
	mark NVARCHAR2(200),
	sort NUMBER(20,0)
);
## 向表中插入一条一句
insert into test_user values(1,'罗易','罗易',1);
insert into test_user values(1,'罗易','罗易',1);
insert into test_user values(1,'罗易','罗易',1);
### 切换到命令窗口查看操作过程
### 设置列宽度
SQL> col mark format a10;
SQL> col id format a10;
SQL> col name format a10;
SQL> col sortformat a10;
SQL> select * from test_user;
 
        ID NAME       MARK             SORT
---------- ---------- ---------- ----------
         1 罗易       罗易                1
         1 罗易       罗易                1
         1 罗易       罗易                1
 

我们连续插入几条都是同样的数据居然成功了，为了避免这种情况的发生我们要给数据库表中创建索引

2、oracle创建索引

针对上述问题我们为表创建索引。分别对id创建主键索引和对name创建唯一索引

SQL> alter table test_user add primary key(id);
 
alter table test_user add primary key(id)
 
ORA-02437: cannot validate (AMBASE.SYS_C0020453) - primary key violated
 ### 因为存在主键冲突的数据了 所以先把表中数据清除了
SQL> delete test_user;
 
3 rows deleted
 
SQL> alter table test_user add primary key(id);
 
Table altered 
 
SQL> create UNIQUE index un_name on test_user(name);
 
Index created
### 再连续插入数据就不那么愉快了。。
SQL> insert into test_user values(1,'罗易','罗易',1);
 
1 row inserted
 
SQL> insert into test_user values(1,'罗易','罗易',1);
 
insert into test_user values(1,'罗易','罗易',1)
 
ORA-00001: unique constraint (AMBASE.SYS_C0020454) violated
 
SQL> insert into test_user values(2,'罗易2','罗易2',2);
 
1 row inserted
 
SQL> insert into test_user values(3,'罗易3','罗易2',2);
 
1 row inserted
SQL> select *from test_user;
 
        ID NAME       MARK             SORT
---------- ---------- ---------- ----------
         1 罗易       罗易                1
         2 罗易2      罗易2               2
         3 罗易3      罗易2               2

• 补充一下索引创建语法：

本段内容取自网友 【DSHORE】
方法一、使用add constraint 方法添加主键约束
alter table 表名 add constraint 主键名 primary key (列名1，列名2，…)
方法二、使用索引创建主键
（和方法一没有区别，可以将方法一理解为省略了using index）
alter table 表名 add constraint 主键名 primary key (列名1，列名2，…)
using index [index_name]；
当省略using index后面的index_name时，创建主键的同时创建同名索引；当使用已有索引index_name创建主键时，注意索引列和主键列应该相同才能创建成功。
方法三、直接添加主键
alter table 表名 add primary key (列名1，列名2，…) ;
同样，创建主键的同时创建同名索引。

3、oracle创建自增长id

那么问题又来了，我每次插入数据都要算一下id太费事了，有没有简单点的办法呢？当然有啦！！！
不过有点繁琐，oracle创建自增id需要先创建序列 然后再拿到序列中的值再插入数据库
或者就是在序列的基础上创建一个触发器在插入数据前自动插入自增的id

• 创建序列方式插入自增id

SQL> 
###Oracle 序列（sequence）的创建、删除
###创建序列  自增步长为1 起始值为1
SQL> create sequence seq_test_user
 2  increment by 1
 3  start with 1
 4  nomaxvalue
 5  nocycle
 6  nocache;

Sequence created

SQL> select seq_test_user.nextval from dual;

 NEXTVAL
----------
        1
###创建完成后插入数据方式
SQL> 
###插入错误是因为自增的id与主键id冲突了  多执行几次至自增的序列号大于数据库的最大id即可
SQL> insert into test_user values(seq_test_user.nextval,'罗易5','罗易2',2);

insert into test_user values(seq_test_user.nextval,'罗易5','罗易2',2)

ORA-00001: unique constraint (AMBASE.SYS_C0020454) violated

SQL> insert into test_user values(seq_test_user.nextval,'罗易5','罗易2',2);

1 row inserted

SQL> insert into test_user values(seq_test_user.nextval,'罗易5','罗易2',2);

insert into test_user values(seq_test_user.nextval,'罗易5','罗易2',2)
### 此处报错是因为 name违反了唯一约束
ORA-00001: unique constraint (AMBASE.UN_NAME) violated


SQL> select *From test_user;

       ID NAME       MARK             SORT
---------- ---------- ---------- ----------
        4 罗易5      罗易2               2
        1 罗易       罗易                1
        2 罗易2      罗易2               2
        3 罗易3      罗易2               2

• 完全自动生成自增id
  基于上一步骤的基础上新增一个触发器，具体请看sql过程

 SQL> CREATE or replace TRIGGER test_user_trig
  2      before insert on test_user
  3      referencing old as old new as new for each row
  4  declare
  5  begin
  6      select seq_test_user.nextval into :new.id from dual;
  7  end test_user_trig;
  8  /
 
Trigger created
 
SQL> 
 
SQL> insert into test_user (name,mark,sort) values ('王小宝6','王小宝6',1);
 
1 row inserted
 
SQL> select *from test_user;
 
        ID NAME       MARK             SORT
---------- ---------- ---------- ----------
         4 罗易5      罗易2               2
         1 罗易       罗易                1
         2 罗易2      罗易2               2
         3 罗易3      罗易2               2
         6 王小宝6    王小宝6             1
 
SQL> insert into test_user (name,mark,sort) values ('王小宝7','王小宝7',7);
 
1 row inserted
 
SQL> select *from test_user;
 
        ID NAME       MARK             SORT
---------- ---------- ---------- ----------
         4 罗易5      罗易2               2
         1 罗易       罗易                1
         2 罗易2      罗易2               2
         3 罗易3      罗易2               2
         6 王小宝6    王小宝6             1
         7 王小宝7    王小宝7             7
 
6 rows selected
 
SQL> 
 ###删除序列语句
SQL> drop sequence test_user;
 
drop sequence test_user
 
ORA-02289: sequence does not exist
 
SQL>