Oracle数据库中创建自增主键的实例教程
分类:计算机教程

在设计数据库表的时候发现Oracle没有自增主键的设置,Google了解到Oracle本身并不支持自增主键,需要通过序列和触发器实现。创建表Student

一、Sql * plus 常用命令

Create Table Student( id number(12) primary key, --通过序列和触发器实现id的自增 name varchar2(20) , age number(3) , sex number(1) ) 

1.关于登录,连接的几个命令

创建序列Sequence

1) conn[nect] //例  conn system/manager

Create Sequence SEQ_STUDENT minvalue 1 maxvalue 99999999999999999999 start with 1 --从1开始 increment by 1 --增量为1 cache 0 order; 

用法 conn 用户名/密码 @网络服务名 (as sysdba/sysoper)

创建触发器Trigger

当特权用户登录的时候,必须带上 as sysdba/sysope

Create or Replace Trigger STUDENT_AUTOINCREMENT Before Insert on Student For Each Row When (NEW.ID IS NULL) Begin Select SEQ_STUDENT.NEXTVAL INTO :NEW.ID FROM DUAL; End; 

比如 sys用户登录,就必须这么写 conn  sys/aaaaaaa as sysdba

注意点:

2) show user //当显示当前登录的用户是哪一个

1:一个sequence可以被多个表共享。

3) disc[onnect] 断开连接

2:被多个表共享的sequence生成的数字序列始终连续,不会重新开始。

4) exit 断开,退出

3:如果不再使用的sequence请删除。

5) clear scr

SELECT * FROM DAYSBFJ.DAYS_CARD_UPDATE3 order by id asc--alter table DAYSBFJ.DAYS_CARD_UPDATE3 add source_Flag varchar2(2);--create sequence DAYS_CARD_UPDATE2_SEQ_ID minvalue 1 maxvalue 999999999 start with 1;--Update DAYSBFJ.DAYS_CARD_UPDATE2 set id = DAYS_CARD_UPDATE2_SEQ_ID.nextval;--update DAYSBFJ.DAYS_CARD_UPDATE3 set SOURCE_FLAG = '2'

6) alter user identified by 新密码

另一个例子:

2.关于文件操作的命令

新建一个缺少主键的表

1) start 和 @

create table test1(name1 varchar2(40),city varchar2(40));

运行文件中的 sql脚本

--插入数据

@ c:sql; 或 start c:sql;

insert into test1 values('name1','nanjing');insert into test1 values('name1','nanjing');insert into test1 values('name2','nanjing1');insert into test1 values('name3','nanjing2');insert into test1 values('name4','nanjing3');insert into test1 values('name5','nanjing4');insert into test1 values('name6','nanjing5');insert into test1 values('name7','nanjing6');insert into test1 values('name8','nanjing7');insert into test1 values('name9','nanjing8');insert into test1 values('name10','nanjing9');insert into test1 values('name10','nanjing9');insert into test1 values('name12','nanjing11');insert into test1 values('name13','nanjing12');insert into test1 values('name14','nanjing13');commit;

2) spool

--增加主键ID

可以将sqlplus 屏幕上的内容存到文本文件中

alter table TEST1 add id number(10);

spool c:b.sql;

--设置sequence使ID自增

运行sql脚本

create sequence SEQ_ID minvalue 1 maxvalue 999999999 start with 1;

....

--将id的值设置为sequence

select * from table1

Update test1 set id=seq_id.nextval;commit;

spool off;

--设置id为主键

3.交互式命令

alter table TEST1 add constraint PK_TEST1 primary key (ID); select ID,Name1,CITY from TEST1;

1 & 可以替代变量,该变量在执行时,要由用户输入 

select * from userinfo where userId='&AAA' //在 sql developer 中也好用

4. 显示和设置环境变量

可以用来控制各种格式,如果希望永久保存格式,可以去修改 glogin.sql角本

1)linesize 设置显示行的宽度,默认是80字符

show linesize
set linesize 120

2)pagesize 设置每页显示行的数目 默认40 行

二、Oracle 中表的管理(重要内容)

1) 命名规则

oracle中表和列的命名规则

-- 必须以字母开头(数字不行)    ,下划线 _ 也不行

-- 长度不能超过30个字符

-- 不能使和Oracle 保留字 //比如用 level 做字段名

-- 只能使用如下字符 'A-Z','a-z',0-9,$,# 等               

2) 数据类型

-- char 定长字符串,最大2000个字符

-- varchar2 变长字符串,最大4000个字符

-- clob(character large object ) 字符型大对象 最大4G

-- bolb 二进制数据,可以存放图片,声音等 最大 4G

-- number 范围 -10 的38 次方,到10 的 38 次方

-- number (8,2) 可以表示带小数点的数

-- data 日期类型,包 含年月日,时分秒

-- timestamp 时间戳

3)关于日期类型的说明

//创建一个表
create table STUDENT
(
    ID       NUMBER(4),
    STUNAME  VARCHAR2(20),
    SEX      CHAR(2),
     BIRTHDAY DATE,
     SAL      NUMBER(5,2)
)

添加一条数据

insert into student values (1,'陈鹏飞','男','1999-09-09',9999.99)
//会报错,文字与格式字符串不匹配
//因为这里的  '1999-09-09' 格式不正确 oracle 默认的日期格式是 DD-MON-YY
//修改日期格式的语句
alter session set nls_date_format ='yyyy-MM-dd'

3) 删除数据

delete from student  //删除所有记录,表格构还在,写日志,可以恢复,速度慢

drop tablue student //连表结构一起删除

truncate table student //删除表中的数据,表结构在,不写日志,速度快,无法恢复,在sqlserver 或mysql中,它会重置自增主键

savepoint aaa;
delete from student ;
rollback to aaa;

4) 关于查询

查看执行sql语句用的时候

set timing on //显示一条语句的执行时间

//例如 :
set timing on
select * from student 

 结果:

 ID STUNAME              SEX BIRTHDAY        SAL
 ----- -------------------- --- ----------- -------
1 陈鹏飞               男  1999-9-9     999.99
Executed in 0.016 seconds

// 注意:严格区分大小写

select * from student where stuName='AA'; 

//大写的AA和小写的aa 在oracle中是不同的

 mysql :默认查询的时候是不区分大小写的 如果就想区分大小写  SELECT * FROM userInfo where userName= binary ('aaa')

sqlservler :默认也不区分 如果想区分, select * from userInfo where userName= 'aaa' collate Chinese_PRC_CS_AI

或 alter  column 字段名 nvarchar(20) collate Chinese_PRC_CS_AI

//关于 null 值

-- 查看姓名,工资,工资 奖金 , 奖金

select ename,sal,sal comm, comm  from scott.emp;

可以发现 , 数值和null 相加,结果也是 null

输出结果:                  
ENAME            SAL   SAL COMM      COMM


SMITH         800.00            
ALLEN        1600.00       1900    300.00
WARD         1250.00       1750    500.00
JONES        2975.00            
MARTIN       1250.00       2650   1400.00
BLAKE        2850.00           

如何处理? 使用 nvl

select ename,sal,nve(sal,0) nvl(comm,0), comm  from scott.emp;

查年薪 : select ename,sal,sal*12 nvl(comm,0)*12 as 年薪, comm  from scott.emp;

查工资最高的人的姓名和工资 SQL> select ename ,sal from scott.emp where sal =(select max(sal) from scott.emp);

三、Oracle 中的序列(重要内容)

序列( sequence ) 是一个计数器,它不会与特定的表关联

通过创建 sequence 和触发器实现表的自增主键,序列一般用来添充主键或计数

//创建序列

create sequence seq_id    //seq_id 是可以任意指定的序列的名称
minvalue 1
start with 1
increment by 1
cache 20;   //定义存放序列的内存块的大小,默认是20,实例异常关闭的时候,可能会造成数据丢失

//查看序列

select * from USER_SEQUENCES  //只能查看用户自己的
select * from ALL_SEQUENCES  
select * from DBA_SEQUENCES 

//删除序列
drop sequence  seq_id 

思考:如何使用

当要使用序列的值的时候, 直接调用它的 nextval

比如:

seq_id.nextval //注意:后面没有() ,它是一个属性值

select   seq_id.nextval from dual;

使用的方式

方式一 ,不使用触发器,在插入数据的时候,使用序列生成主键

insert into student (id,stuName,sex,sal) values ( seq_id.nextval,'马苗','女',8888.88) 

说明: seq_id是序列的名称

方式二 建立触发器,当有数据添加的时候由触发器使用序列生成主键           

create trigger TRG_TEST before insert on student
for each row
begin
select seq_id.nextval into :new.id from dual;
end ; //后面的分号不能少

创建触发器以后,再对这个表进行inser 的时候,主键就会自动增长

//需要注意: 对于两个表的自增主键,用同一个序列,它产生的序号会被分着用

附 hibernate  和  sequnce

根据hibernate的文档,有两种方式实现实体对象的主键自动增长。

第一种:设置ID的增长策略是sequence,同时指定sequence的名字,最好每个表建一个sequence,此种做法就如同MS-SQL,MY-SQL中的自动增长一样,不需要创建触发器,具体的oracle数据库脚本及hibernate配置文件: 略 */

可以看到 在oracle 使用序列还是很麻烦的,所以建议对oracl中的主键,尽量不要使用自增,可以使用varchar2 ,添加的时候,用uuid java.util.UUID.randomUUID();

四、Oracle 中的用户管理 

1、创建用户

create user  //一般具有 dba(数据库管理员) 的权限才能使用

create user nicecat identified by nicecat      建立一个名为nicecat 的用户,密码也是nicecat

注意: 密码必须以 字母开头

2.删除用户

一般情况下,要用dab的身份去删除用户, 如果是别的用户进行用户删除操作,要有 drop user 的权限,自己不能删除自已 drop user 用户名 [cascade] 

注意:cascade 是级联的意思,如果被删除的用户已经创建了表,则连他的表一起删除

3.授权

grant connect to nicecat ; //把 connect 这个角色授给 nicecat

4.口令管理

使用 profile 管理用户口令

男篮世界杯赔率,profile 是口令限制,资源限制的集合,当建立数据库时,oracle 会自动建立名为 default 的profile。当建立用户的时候,如果我们没指定 profile,那么oracle 就会将 default分配给用户。

思考:如何实现用户锁定

1.可以指定用户登录时最多可以输入密码的次数

2.可以指定用户锁定的时间(天)

本文由美洲杯赔率发布于计算机教程,转载请注明出处:Oracle数据库中创建自增主键的实例教程

上一篇:Oracle 中XML处理函数介绍 下一篇:没有了
猜你喜欢
热门排行
精彩图文
  • MySQL两种临时表的用法详解
    MySQL两种临时表的用法详解
    外部临时表 通过CREATE TEMPORARY TABLE创建的临时表,这种临时表称为外部临时表。这种临时表只对当前用户可见,当前会话结束的时候,该临时表会自动关闭
  • MySQL笔记之数据类型详解
    MySQL笔记之数据类型详解
    数值型数据可以定义为unsigned,对整数而已可以改变其范围,对浮点数而言可以限定其不能为负数 char定长型,字节长度固定,最大长度为255字节,可以使用
  • MySQL 主主同步配置步骤
    MySQL 主主同步配置步骤
    MySQL 主主同步配置 服务器名IP系统MySQLodd.example.com192.168.1.116rhel-5.85.5.16even.example.com192.168.1.115rhel-5.85.5.16假设要同步的库是db_rocky ㈠ 创建同步用户 在 ODD上
  • MYSQL常见出错代码对照
    MYSQL常见出错代码对照
    常见错误!1016错误:文件无法打开,使用后台修复或者使用phpmyadmin进行修复。1044错误:数据库用户权限不足,请联系空间商解决1045错误:数据库服务器
  • 酷派巨亏75亿港元后复牌 5G与海外业务能否助其翻
    酷派巨亏75亿港元后复牌 5G与海外业务能否助其翻
    此外,酷派也一直在没有放弃在5G技术领域的探索。早在2013年,酷派便开始参与5G终端的研发及测试,直至2017年,酷派成立了人工智能科技中心。 半年后,