Oracle 数据库的对象定义操作(DDL语句)

原创 2016年05月31日 16:52:18

常用数据类型

  1. varchar:表示可变字符串类型;<=4000
  2. char:表示定长的字符串;<=2000
  3. number :表示整数和浮点数 –

    number(6)表示6位数字的 整数

    number(6,2) 表示6位数字的小数,小数点后面两位,前面4位

  4. date :日期格式,oracle 默认格式为:”dd-mm-yy”

  5. Timestamp:时间戳,精确度比较高的日期类型
  6. Time:时间格式
  7. Blob:存储大的二进制多媒体文件。<=4GB
  8. Clob:存储大的二进制文本文件。 <=4GB

建立表

学生管理系统

学员(student):学号(sno),姓名(sname),年龄(sage),性别(sgender),地址(address),电话(telphone)

班级(class):班号(cid) 班级名称(cname),所在教室(classroom)….

课程(course):课程编号(courseId),课程名称(cousename),学分(coursescore),课时(coursetime)
教师(teacher):教师编号(tid) ,教师姓名(tname)……

转化为E-R图如下所示:

1.student表

学员(student):学号(sno),姓名(sname),年龄(sage),性别(sgender),地址(address),电话(telphone)

–创建表student

create table student(
  sno number(2),
  sname varchar(9) DEFAULT 'xxx',
  sage number(3),
  sgender varchar(3),
  address varchar(100),
  telephone number(11)
);

–查看表

select * FROM student;

–插入 insert into

insert into student values(1,'汪筱筱',20,'女','北京','29048701921');
insert into student values(2,'吴龙',22,'男','上海','10974673921');
insert into student values(3,'牟思邈',21,'男','天津','40280018271');
insert into student values(2,'宇辉',19,'男','深圳','29148993021');

–修改表alter add
–添加 QQ 字段

alter table student add(QQ number(10));

–添加email字段

alter table student add(email varchar(10));

–添加生日字段

alter table student add(birthday varchar(20));

–使用update 插入各个同学的生日

update student set birthday = to_date('1996-8-4','yyyy-mm-dd') where sno =1;
update student set birthday = to_date('1994-5-9','yyyy-mm-dd') where sno = 2;
update student set birthday = to_date('1996-10-1','yyyy-mm-dd') where sno = 3;
update student set birthday = to_date('1999-1-28','yyyy-mm-dd') where sno = 4;

–使用update 插入各个同学的 QQ 号;

update student set QQ = 1234 where sno =1;
update student set QQ = 1893308 where sno = 2 ;
update student set QQ = 1092387 WHERE SNO = 3;
UPDATE STUDENT SET QQ = 1378249 where sno = 4;

删除表

–删除表中的列例如删除email字段

alter table student drop(email);

修改表

–修改数据类型

alter table student modify(sname varchar(30));

–查看表的结构

desc student;

–修改表名称:

alter table student rename to imit_student;
rename student to imit_student;

截断表

语法:truncate table 表名

删除表有三种不同的方式:

1. drop table student 
2. delete from student 
3. truncate table student 

* DELETE DROP TRUNCATE的比较 *

相同点:
三个关键字都能删除表的所记录
不同点:
1. truncate与delete都只是删除数据,不删除表的结构,而drop会删除表的结构,以及依赖它的约 束,触发器,索引,但是依赖它的存储过程与函数会保留。

  1. delete是DML语句,它的操作会放到回滚段中,可以执行回滚操作,能够触发触发器。而drop,truncate都是DDL语句,它的操作不会放到回滚段中,无法回滚,不会触发触发器。

  2. delete删除数据不会回收该空间。drop会将表所占用的所有空间都释放。truncate默认情况下是要释放空间,但可以设置不释放空间。

  3. 速度不一样,一般情况:drop>truncate>delete

  4. 安全性 ,小心使用drop和truncate

总结:

 想删除部分数据建议使用delete
 想删除表只能drop
 想删除记录但保留表的结构时,如果操作与事务无关,不需要触发触发器可以使用truncate,如果操作与事务有关,需要触发器可以使用delete
 如果想整理数据内部碎片,可以使用truncate跟上reuse stroage.然后重新导入数据。

约束(CONSTRAINT)

约束可以比较好控制数据的完整性。

约束的分类

 主键约束:主键表示唯一标识,本身不能空且不能重复。(PK)
 唯一约束:只表此列的值是唯一,但是可以为空。(UK)
 检查约束:表示一个列的值是否是合法数据。(CK)
 非空约束:表示此列不能出现空值。
 主-外键约束:表示两张表之间约束。(FK)

主键约束

给某字段加主键约束可以使用关键字PRIMARY KEY;

CREATE TABLE student(
    Sid number(5) PRIMARY KEY,
    Sname varchar2(20),
    Sage number(3),
    Sgender char(2) DEFAULT '男',
    Address varchar2(100),
    Telpone varchar2(12),
    email varchar2(30)
)

 主键约束本身就有非空的约束和唯一约束,而且一个表只能有一个主键。
 将约束直接定义在相应的字段之后的约束称为列级约束。
 将约束定义所有字段之后的约束我们称为表级约束。
 给约束定义名称最好使用表级约束。

如:

CREATE TABLE student(
    Sid number(5),--学员编号
    Sname varchar2(20),
    Sage number(3),
    Sgender char(2) DEFAULT '男',
    Address varchar2(100),
    Telpone varchar2(12),
    email varchar2(30),
    CONSTRAINT student_sid_pk PRIMARY KEY(sid)
)

 通常一张表的主键会设置成与实体本身无关一个字段。
也就是说通常会虚构出一个无业务无关ID字段作为表的主键。

CREATE TABLE student(
 Id number(8) ,--虚构一个与业务无关的字来作为表的主键
    Sid number(5),--学员编号
    Sname varchar2(20),
    Sage number(3),
    Sgender char(2) DEFAULT '男',
    Address varchar2(100),
    Telpone varchar2(12),
    email varchar2(30),
    CONSTRAINT student_sid_pk PRIMARY KEY(id)
)

eg:

ALTER TABLE student ADD CONSTRAINT student_sid_pk PRIMARY KEY(sid);

Eg:向表student中添加记录

insert into student values(1,'华华',12,'女','澳门','243254657');
insert into student values(2,'郭凯',22,'男','上海','354657568');

 当使用了主键约束后,则不允许插入两个完全相同的记录。并且主键所在列不允许为空值。

-主键约束(PRIMARY KEY)

  • 一张表只能有一个主键字段,主键可以任意类型,一般设置为number
  • 主键自带,唯一,非空约束
  • 直接在字段声明后面添加的约束,叫做列级约束
  • 在所有的字段后,通过关键字 CONSTRAINT 声明的约束叫做 表级约束

–列级约束

CREATE TABLE student(
    Sid number(5) PRIMARY KEY,
  Sname varchar2(20),
    Sage number(3),
    Sgender char(2) DEFAULT '男',
    Address varchar2(100),
    Telpone varchar2(12),
    email varchar2(30)
)

–表级约束

CREATE TABLE student(
    Sid number(5),--学员编号
    Sname varchar2(20),
    Sage number(3),
    Sgender char(2) DEFAULT '男',
    Address varchar2(100),
    Telpone varchar2(12),
    email varchar2(30),
    CONSTRAINT student_sid_pk PRIMARY KEY(sid)
)

唯一约束

保证数据的某项内容不重复。可以使用唯一约束,关键字是UNIQUE;

eg:

CREATE TABLE student(
    id number(5),--主键
 sno varchar2(8) UNIQUE,--代表学员编号
    Sname varchar2(20) UNIQUE,
    Sage number(3),
    Sgender char(2) DEFAULT '男',
    Address varchar2(100),
    Telpone varchar2(12),
    email varchar2(30),
    CONSTRAINT student_sid_pk PRIMARY KEY(id)
)

可以使用表级约束使用constraint 关键字来定义唯一性约束。

CREATE TABLE student(
    id number(5),--主键
 sno varchar2(8) UNIQUE,--代表学员编号
    Sname varchar2(20),
    Sage number(3),
    Sgender char(2) DEFAULT '男',
    Address varchar2(100),
    Telpone varchar2(12),
    email varchar2(30),
    CONSTRAINT student_sid_pk PRIMARY KEY(id),
 CONSTRAINT student_sname_uk UNIQUE(sname)
)

可查看当前用户的所有约束,查看Oracle 数据字典,user_constraint

select constraint_name from user_constraints where owner='SCOTT'

检查约束

检查数据的合法性的约束,可以使用check 关键字;

将表中student 中的年龄范围定义在 0-150,性别必须是‘男’或‘女’

CREATE TABLE student(
    id number(5),--主键
 sno varchar2(8) UNIQUE,--代表学员编号
    Sname varchar2(20),
    Sage number(3) CHECK(sage BETWEEN 0 AND 150),
    Sgender char(2) DEFAULT '男' CHECK(sgender IN(‘男’,’女’)),
    Address varchar2(100),
    Telpone varchar2(12),
    email varchar2(30),
    CONSTRAINT student_sid_pk PRIMARY KEY(id),
 CONSTRAINT student_sname_uk UNIQUE(sname)
)

同样也可以用表级约束来定义检查约束

CREATE TABLE student(
    id number(5),--主键
 sno varchar2(8) UNIQUE,--代表学员编号
    Sname varchar2(20),
    Sage number(3),
    Sgender char(2) DEFAULT '男' CHECK(sgender IN('男','女')),
    Address varchar2(100),
    Telpone varchar2(12),
    email varchar2(30),
    CONSTRAINT student_sid_pk PRIMARY KEY(id),
CONSTRAINT student_sname_uk UNIQUE(sname),
CONSTRAINT student_sage_ck CHECK(sage BETWEEN 0 AND 150)
)

非空约束
对那些必填的字段进行约束,使用关键字 not null;

CREATE TABLE student(
    id number(5),--主键
 sno varchar2(8) UNIQUE NOT NULL,--代表学员编号
    Sname varchar2(20),
        Sage number(3),
    Sgender char(2) DEFAULT '男' CHECK(sgender IN('男','女')),
    Address varchar2(100),
    Telpone varchar2(12),
    email varchar2(30),
    CONSTRAINT student_sid_pk PRIMARY KEY(id),
CONSTRAINT student_sname_uk UNIQUE(sname),
CONSTRAINT student_sage_ck CHECK(sage BETWEEN 0 AND 150)
)

–如果修改表时,要添加一个非空约束,而表中的记录已经为空,则设置为默认;

alter table student add constraint sno NOT NULL (sno default 12345 );

主-外键约束(FOREIGN key)

  • 外键约束表明的是 表与表之间的关系,一般是多张表
  • 外键一般是从其他表中的字段引用来的
  • 外键主要用来维护两实体之间的联系。
  • 对于1:N的关系建立在在多一方。
  • 对于1:1的关系建立在从表的一方。
  • 对于多对多的关系是需要中间表来维护,而且中间表使用的是联合主键。

说明:
 外键约束可以保证数据的完整性,不会出现不合理的数据,它可以为空可以重复,但数据都是来源于关联表的主键值。

 外键可以进行列级定义或或者表级定义

CONSTRAINT student_class_classid_fk FOREIGN KEY(classid)            REFERENCES class(id)(表级定义)
classid number(3) references class(id)(列级定义)

 主外键约束引用的必须是父表的主键。
 在添加数据的时候必须先添加父表的数据。

 删除的时候必须先删除子表中数据再删除父表中的数据实际也可以进级级联操作,要进行级联操作的时候需要在建立外键的关系的时候指定此级联属性。
A. ON DELETE CASCADE:删除父表中的数据时,会级联删除子表中关联的数据。

ALTER TABLE student 
ADD CONSTRAINT student_class_classid_fk 
FOREIGN KEY(classid) REFERENCES class(id)
ON DELETE CASCADE;

B. ON DELETE SET NULL:删除父表中数据时,会将子表的关联列的数据全部置为NULL

      ALTER TABLE student ADD CONSTRAINT student_class_classid_fk 
FOREIGN KEY(classid) REFERENCES class(id)
ON DELETE SET NULL;

C. ON DELETE NO ACTION:不做任何级联,默认设置(不需要加该语句)

 删除表的时候也需要先删除子表再删除父表。但是可以加上CASCADE CONSTRAINT来进行级联删除(意义不大)。

drop table class CASCADE CONSTRAINT;

约束的修改

  • 添加约束

其语法是:
ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束类型(约束的字段)
Eg:将class表的classroom字段加上唯一性约束

 ALTER TABLE class ADD 
CONSTRAINT class_classroom_uk UNIQUE(classroom)

可以使用上面语法添加的约束类型有:
PRIMARY KEY、UNIQUE、 CHECK 、FOREIGN KEY

对于NOT NULL类型的约束可以使用下面的语法:

ALTER TABLE class MODIFY(classroom varchar(30) NOT NULL);
  • 删除约束
    其约束的语法:
    ALTER TABLE 表名 DROP CONSTRAINT 约束名

    Eg:去掉STUDENT表的sname字段的唯一约束

 ALTER TABLE student DROP CONSTRAINT student_sname_uk;
  • 约束的禁用和启用
    其语法:

ALTER TABLE 表名 DISABLE CONSTRAINT 约束名
ALTER TABLE 表名 ENABLE CONSTRAINT 约束名

--classes表建立
create table classes(
  cno number(2),
  cname varchar(10),
  classroom varchar(30)
);
select * from classes;

--插入 insert into
insert into student values(1,'汪筱筱',20,'女','北京','29048701921');


--course 表建立
create table course(
  courseNo number(10),
  coursename varchar(20),
  coursescore number(20),
  coursetime number(20)
);
select * from course;

--插入 insert into
insert into course values(1,'语文',100,'48');
insert into course values(2,'数学',100,'48');
insert into course values(3,'英语',100,'48');
insert into course values(4,'物理',100,'48');
insert into course values(5,'化学',100,'48');
insert into course values(6,'生物',100,'48');

--teacher表的建立
create table teacher(
  tno number(3),
  tname varchar(10)
);
select * from teacher;

--插入 insert into
insert into teacher values(101,'张宇');
insert into teacher values(102,'黄媛');
insert into teacher values(103,'陆云');
insert into teacher values(104,'吴汉涵');
insert into teacher values(105,'梁新慧');
insert into teacher values(106,'胡延海');

--删除表
 drop table teacher;

 --新建teacher表并设置主键
 create table teacher(
  tno number(3) primary key,
  tname varchar(10)
);

--查看表
select * from teacher;
--插入数据
insert into teacher values(101,'张宇');
--再插入一个编号为101 的teacher
insert into teacher values(101,'黄媛');

这里写图片描述

insert into teacher values(102,'黄媛');
insert into teacher values(103,'陆云');
insert into teacher values(104,'吴汉涵');
insert into teacher values(105,'梁新慧');
insert into teacher values(106,'胡延海');

这里写图片描述

**主外键约束还存在问题**
--删除原有表
drop table student;

--新建表student
create table student(
  id number  primary key,
  sno number(2) unique not null,
  sname varchar(9) DEFAULT 'xxx',
  sage number(3),
  sgender varchar(3) DEFAULT '男'check(sgender in ('男','女')),
  address varchar(100),
  telephone number(11)
  classesid number (3) references classes(id),
  CONSTRAINT student_sid_pk PRIMARY KEY(id),
    CONSTRAINT student_sname_uk UNIQUE(sname),
    CONSTRAINT student_sage_ck CHECK(sage BETWEEN 0 AND 150)
    CONSTRAINT student_classes_classesid_fk 
 FOREIGN KEY(classes_id) REFERENCES classes(id)
);
drop table classes;
--classes表建立
create table classes(
  id number(3) primary key,
  cno number(2) not null unique,
  cname varchar(10) not null unique,
  classroom varchar(30)
);
--主外键约束
--删除原有表
drop table student;

--新建表student
create table student(
  id number  primary key,
  sno number(2) unique not null,
  sname varchar(9) DEFAULT 'xxx',
  sage number(3),
  sgender varchar(3) DEFAULT '男'check(sgender in ('男','女')),
  address varchar(100),
  telephone number(11)
  classesid number (3) references classes(id),
  CONSTRAINT student_sid_pk PRIMARY KEY(id),
    CONSTRAINT student_sname_uk UNIQUE(sname),
    CONSTRAINT student_sage_ck CHECK(sage BETWEEN 0 AND 150)
    CONSTRAINT student_classes_classesid_fk 
 FOREIGN KEY(classes_id) REFERENCES classes(id)
);
drop table classes;
--classes表建立
create table classes(
  id number(3) primary key,
  cno number(2) not null unique,
  cname varchar(10) not null unique,
  classroom varchar(30)
);

对于表的创建及增删查改,其约束性等内容中,查找操作和约束性是难点,同时也是我了解的不够透彻的地方,需要继续巩固学习的内容。

版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

Oracle中的DDL语句

 Oracle中的DDL语句DDL语言是创建与管理表的语句。1、创建表  创建表的语法:Create table 表名(列名 数据类型(尺寸))例如:SQL> Create table tablena...

Oracle数据库对象简介

Oracle数据库数据对象中最基本的是表和视图,其他还有约束、序列、函数、存储过程、包、触发器等。对数据库的操作可以基本归结为对数据对象的操作,理解和掌握Oracle数据库对象是学习Oracle的捷径...

Oracle442个应用场景-----------角色管理

--------------------------------角色管理------------------------------------ 一、角色的概念和特性      1.什么是角色? ...

算法导论之贪心算法(Huffman编码和拟阵)

贪心算法,在解决最优化问题上,通过得到子问题的局部最优解来合成问题的一个解,以局部最优选择来输出一个全局最优解。 问题要用贪心算法来求解,需满足和动态规划一样的最优子结构特征,同时还需要再每个子问题最...

oracle 数据库操作语句大全

  • 2013年08月01日 15:17
  • 127KB
  • 下载

oracle 如何查看创建表等数据库对象时的DDL语句

利用dbms_metadata.get_ddl查看DDL语句(原创) 当我们想要查看某个表或者是表空间的DDL的时候,可以利用dbms_metadata.get_ddl这个包来查看。...
  • haiross
  • haiross
  • 2014年05月12日 11:32
  • 16706

[Oracle]利用DBMS_METADATA.GET_DDL函数获取数据库对象的DDL语句

如果我们想看某个表或者表空间等数据库对象的DDL语句的话,可以使用Oracle函数DBMS_METADATA.GET_DDL()函数获取,该函数有如下几个参数:-- OBJECT_TYPE ---需要...

oracle 数据库语句简记

ALTER SESSION SET CURRENT_SCHEMA=p2p; select ym,nvl(counth,0) zc,nvl(countt,0) tz from ( SELECT...
  • zxc8471
  • zxc8471
  • 2016年10月31日 09:32
  • 93

Oracle 数据库SQL语句优化方法

在提高SQL语句效率上,可以考虑以下几个方面: 第一:重新构造语句 第二:修改或禁止触发器 第三:重新构造数据 第四:及时统计CBO所用信息 1 重新构造语句 在重构索引之后,你可以试着重构语句,及重...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle 数据库的对象定义操作(DDL语句)
举报原因:
原因补充:

(最多只允许输入30个字)