Oracle数据库约束数据类型和序列

varchar是标准sql里的，varchar2是oracle所提供的独有的数据类型，前者对于汉字占两个字节，对于数字和英文字符占一个字节，占的内存小，后者一般情况下把所有字符都占两个字节处理

数据约束的目的

数据约束的根本目的在于保持数据的完整性。数据完整性，是指数据的精确性和可靠性。即数据库中的数据都是符合某种预定义规则。当用户输入的数据不符合这些规则时，将无法实现对数据库的更改

主键约束

主键约束是数据库中最常见的约束。主键约束可以保证数据完整性。即防止数据库表中的两条记录完全相同，通过将主键纳入查询条件，可以达到查询结果最多返回一条记录的目的。

主键约束最终的目的是确保数据库表中的记录是唯一的，在更多的情况下称之主键为行的唯一索引。这就是要求表中主键列所填充的数据值不允许重复，主键约束属于唯一性约束。

主键

数据表中用一列或多列来标识当前记录行在表中是唯一记录的列称之为主键列，建立主键即建立主键约束。

主键是实体（行）完整性约束的一种重要标志

数据库表由行和列构成，每行被称为数据实体，每列被称为实体的属性

主键的特点及要求：

主键值在表中不允许重复；
主键值不允许为空；
主键针对实体通常不具备实际意义，只作为行唯一标识使用；
主键不建议经常修改，或最好不修改；
考虑性能，不建议设置联合主键。

建立主键约束

使用GUI工具快速建立主键约束（推荐）
使用SQL语句建立主键约束

(1) 建立表时同时建立主键约束

(2) 建立表后添加主键约束

外键约束

外键约束是引用完整性约束
外键是表与表之间关联引用的一种唯一方式

外键的特点：

外键值通常不允许为空；
外键值必需来源于所引用主表的主键值；
外键列数据类型通常必需与主键列数据类型相同；
不能在删除主表数据时导致从表的外键孤立。

给表新增非空的列时，如果表里已有数据是无法新增列的，必需清空表数据才能新增

唯一性约束

唯一性约束限制记录行中的某个列字段值在所有行中是唯一，通常主键约束就隶属唯一性约束，但是不能认为唯一性约束就一定是主键

默认约束

默认值约束是设定表中的某个列在未填充数据时，系统将使用设置的预定义默认值来填充列值，如果显示设置了默认值则默认值失效

检查约束

检查约束是限制表中的某个列字段值的一种强大规则，只有符合检查约束条件的值才能作为这个列字段的值。

如限定年龄必需在18-50之间，地址必需在北京，上海，广州这个范围，邮件地址必须以.com为结尾

--ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效

/* 1，错误分析：

这个错误是对表的操作短时间内太过快速和频繁而导致的数据库锁表。即由于其他Session已经对目标表做了操作，并且已经有排他锁在表上了，所以新的Session无法再对表进行DDL操作。

2，解决方法： */

-- 1），查询被锁会话ID：146

SELECT SESSION_ID FROM V$LOCKED_OBJECT;

-- 2），根据会话ID-146查询详细信息

SELECT SID, SERIAL#, USERNAME, OSUSER FROM V$SESSION WHERE SID = 146;

删除约束

alter table 表名 drop constraint 约束名称

Oracle常用数据类型

Oracle中的常用数据类型主要应用场景为建立数据表时定义列的类型、函数、存储过程的变量定义。这些数据类型大致可以分为4类：

字符型（character）
数值型（number）
日期型（date）
大对象类型（LOB）

Char(长度)固定长度最大限制(2000字节)

Varchar(长度)可变长度最大限制(4000字节)

Varchar2(长度)等同Varchar 最大限制(4000字节)

String(长度)编程类型不能定义表的列类型

Date 日期类型

Timestamp 时间戳类型，精确到毫秒

Sysdate函数获取系统日期类型

Systimestamp 函数获取系统日期时间毫秒

提示：使用to_date和to_char对日期时间和字符串之间实现转换

日期格式表示符号

年(yy,yyy,yyyy)

月(mm,MM,mon,Month)

天(dd(月份天),ddd(年份天))

周(dy(简写),day(全写))

时(hh,hh24);分(mi);秒(ss);毫秒(ff(1-9)) //hh24是24小时制，hh12是12小时制

Lob类型

Clob 用于存储大型文本数据

Blob 用于存储二进制数据

Bfile 用于存储大文件数据

值类型

Number最常用的类型，可应用列和编程

Int 整型，可应用列和编程

Integer 整型，可应用列和编程

Float 浮点类型，可应用列和编程

Dec 类似于浮点类型，可应用列和编程

伪列类型

Rowid记录行ID，由18个英文字母组成

Rownum查询数据行生成的行号码(从1开始)

提示：rowid和rownum只有在查询过程中生成

序列

Sequence序列是Oracle中自动生成不重复的数据的对象

序列主要具备以下特制和应用

自动提供唯一的数值
共享对象
主要用于提供主键值
将序列值装入内存可以提高访问效率

CREATE SEQUENCE sequence --创建序列名称

[INCREMENT BY n] --递增值是n，n是正数就递增，如果是负数就递减，默认是1

[START WITH n] --开始的值，递增默认是minvalue递减是maxvalue

[{MAXVALUE n | NOMAXVALUE}] --最大值

[{MINVALUE n| NOMINVALUE}] --最小值

[{CYCLE | NOCYCLE}] --循环/不循环

[{CACHE n| NOCACHE}]; --分配并存入到内存中

应用序列

获取序列下一个值：序列名称nextval

获取序列当前值：序列名称currval

Drop sequence序列名称；--删除序列对象

--建立主键
select * from dep_table;
ALTER TABLE dep_table ADD constraint PK_id primary key(ID);

CREATE TABLE emp_tab
(
 ID VARCHAR2(64) NOT NULL PRIMARY KEY,
 NAME varchar2(32) NOT NULL
)

DROP TABLE emp_tab;
commit;
SELECT * FROM emp_tab;
INSERT INTO emp_tab VALUES('NO001','张无忌');
INSERT INTO emp_tab VALUES('NO002','张无忌2');

ALTER TABLE emp_tab ADD dep_id varchar2(64) NOT NULL;
DELETE FROM emp_tab;

--建立外键约束
alter TABLE emp_tab ADD constraint FK_emp_tab_dep_id foreign key(dep_id)
references dep_table(ID);
commit;

INSERT INTO emp_tab VALUES('NO002','任盈盈','01');

ALTER TABLE emp_tab ADD Identity varchar(21) NULL;
--建立唯一性约束
ALTER TABLE emp_tab ADD constraint uq_emp_tab_identity unique(identity);

ALTER TABLE emp_tab ADD address VARCHAR2(128);

INSERT INTO emp_tab VALUES('NO003','任我行','01','1321321967999');

DELETE FROM emp_tab WHERE NAME='任我行';

--建表时设置列的默认值
CREATE TABLE users
(
 address VARCHAR2(128) DEFAULT '本市'
)

SELECT * FROM users;
INSERT INTO users VALUES(default);

--表建立后添加默认约束
ALTER TABLE emp_tab MODIFY address DEFAULT '长春市';

INSERT INTO emp_tab VALUES('NO004','张无忌4','02','25643215848',default);

INSERT INTO aaa VALUES(DEFAULT);

SELECT * FROM aaa;

DROP TABLE aaa;

SELECT * FROM dba_tab_columns dtc WHERE dtc.TABLE_NAME='EMP_TAB';
SELECT * FROM emp_tab;
--使用SQL语句建立检查约束
ALTER TABLE emp_tab ADD age NUMBER NULL;
UPDATE emp_tab SET age = 23;

ALTER TABLE emp_tab ADD constraint ck_emp_tab_age check(age >=18 AND age <=60);

ALTER TABLE emp_tab ADD constraint ck_emp_tab_address 
CHECK(address IN ('北京市','大连市','长春市','石家庄'));

--ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效
/* 1，错误分析：

这个错误是对表的操作短时间内太过快速和频繁而导致的数据库锁表。即由于其他Session已经对目标表做了操作，并且已经有排他锁在表上了，所以新的Session无法再对表进行DDL操作。

2，解决方法： */
-- 1），查询被锁会话ID：146
 SELECT SESSION_ID FROM V$LOCKED_OBJECT;
 
-- 2），根据会话ID-146查询详细信息
 SELECT SID, SERIAL#, USERNAME, OSUSER FROM V$SESSION WHERE SID = 146;
 
-- 3），杀掉会话：
 ALTER SYSTEM KILL SESSION '146,268';
 
--新增邮箱约束
ALTER TABLE emp_tab ADD CONSTRAINT ck_emp_tab_email
CHECK(email LIKE '%163.com');


ALTER TABLE emp_tab DROP CONSTRAINT ck_emp_tab_email;

--数据类型
DROP TABLE test_table;

CREATE TABLE test_table
(
 NAME VARCHAR(45),
 birth DATE
)

INSERT INTO test_table VALUES('空少爷',to_date('1998/6/25','yyyy/MM/dd'));
SELECT * FROM test_table;

SELECT * FROM dual;
SELECT to_char(systimestamp,'yyyy-mm-dd dy ddd hh24:mi:ss.ff') FROM dual;

CREATE TABLE person
(
 age INT,
 height FLOAT,
 money NUMBER(38,5)
)
INSERT INTO person VALUES(23.6,180,5000.99);
INSERT INTO person VALUES(23.6,180,5000.991236);

SELECT * FROM person;

--伪列rowid和rownum

SELECT rowid,ROWNUM,NAME FROM emp_tab;

CREATE TABLE test_seq_tab
(
 ID NUMBER NOT NULL PRIMARY KEY,
 NAME VARCHAR2(64)
)

INSERT INTO test_seq_tab VALUES(seq_emp.nextval,'罗军');

INSERT INTO test_seq_tab VALUES(seq_emp.nextval,'罗军2');

INSERT INTO test_seq_tab VALUES(seq_emp.nextval,'罗军3');

INSERT INTO test_seq_tab VALUES(seq_emp.nextval,'罗军4');

INSERT INTO test_seq_tab VALUES(seq_emp.nextval,'罗军5');

SELECT * FROM test_seq_tab;

SELECT seq_emp.CURRVAL FROM dual;

/*任务一
1使用SQL语句修改dept表，设置ID列为表的主键。
2使用SQL语句修改dept表，设置name列和code列唯一性约束。
3使用SQL语句修改dept表，建立检查约束，设置ID列值必需以“NO”开头。*/

select * from dept;

--设置ID列为主键
ALTER TABLE dept ADD constraint pk_dept_id primary key(ID);
--注意，每个主键的名字都不可重复，所以最好命名为pk_表名_字段名，避免和其他主键重名，pk是primary key的缩写

--设置name列和code列唯一性约束
ALTER TABLE dept ADD constraint uq_dept_name unique(name);
ALTER TABLE dept ADD constraint uq_dept_code unique(code);

--新增ID约束，设置ID列值必需以“NO”开头。
ALTER TABLE dept ADD CONSTRAINT ck_dept_ID
CHECK(ID LIKE 'NO%');

/*任务三
1 使用GUI工具创建名称为emp(职员信息表)的表，结构如下；

Number  ColumnName  Type  Null  Primary Key Foreign key Default value Constraint
  Description
1 ID  Varcahr2(64)  N Y N     主键
2 Name  Varcahr2(32)  N N N     员工姓名
3 Gender  Number  Y N N 1 1或0 性别(1=男，0=女)
4 Birth Date  Y N N     生日日期
5 Address Varchar2(128) Y N N     家庭住址
6 Phone Varchar2(16)  Y N N     手机号码
7 EntryDate Date  Y N N     入职日期
8 Email Varchar2(32)  Y N N     邮箱号码
9 DEPID Varcahr2(64)  N N Y     外键，引用dept表ID列
10  Descs Varchar2(1000)  Y N N     员工备注
                

2使用SQL语句设置emp表depid列为外键列，引用dept表ID主键列值；建立检查约束email列必需以”.com”为结尾。

3 使用GUI工具向emp表中添加至少12条记录，每个部门都应有员工分布，性别应有男及有女，部分记录email列应有空值，应有重复的姓氏存储在。

4 使用GUI工具或编写SQL语句创建一个名称为seq_ Sales的序列，最小值为100，每次递增10。
*/

--使用SQL语句给性别设定默认值为1
ALTER TABLE emp MODIFY Gender default '1';

--使用SQL语句限制性别值只能是1或0
ALTER TABLE emp ADD constraint ck_emp_gender check(gender = 0 or gender = 1);

--使用SQL语句设置emp表depid列为外键列，引用dept表ID主键列值
alter table emp ADD constraint FK_emp_depid foreign key(depid) references dept(id);

--建立检查约束email列必需以”.com”为结尾
ALTER TABLE emp ADD CONSTRAINT ck_emp_email
CHECK(email LIKE '%.com');

--使用GUI工具向emp表中添加至少12条记录，每个部门都应有员工分布，性别应有男及有女，部分记录email列应有空值，应有重复的姓氏存储在。
INSERT INTO emp VALUES('NO001','罗军','1',to_date('1998/6/25','yyyy/MM/dd'),'长春市','13750018710',to_date('2020/6/25','yyyy/MM/dd'),'luojun@163.com','NO100',seq_emp.nextval);

select * from emp;

posted @ 2021-08-29 22:12 伊万阅读(195) 评论(0) 编辑收藏举报

刷新页面返回顶部

伊万

Oracle数据库约束 数据类型和序列

公告

Oracle数据库约束数据类型和序列