【软件测试】Oracle基础_DDL篇

 DDL（data definition language -数据库定义语言

            思维导图详见：https://blog.csdn.net/Y1013768371/article/details/89280745）

          范围：DDL主要是用在定义或改变表的结构，数据类型，表之间的链接和约束等初始化工作上，

                     比如说我们在创建表的时候用到的一些create、alter、drop等sql语句。

           语法：create/alter/drop table 表名 [...];

CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
....
)

ALTER TABLE table_name [操作]

DROP TABLE 表名称
DROP DATABASE 数据库名称

Oracle三级模式结构

基本表的创建

---

1.没有参照表直接创建

    注：属于三级模式中的模式

    语法：
    create table 表名(
              列名 数据类型 [constraint 约束名] 约束,
              ....
     );

--学生信息表stu
create table stu(
  sid varchar(100),--varchar,可变长字符串，最大长度2000bytes 
  sname varchar2(32),--varchar2,可变长字符串，最大长度4000bytes 
  sex char(8),--char,固定长度字符串，最大长度2000bytes 
  age integer,--integer,整数类型，小的整数 
  score number(5,2),--number(5,2),数字类型，5为总数位,2为小数位,5-2整数位
  admission_date date, --date，日期(日-月-年 时:分:秒),DD-MM-YY HH:MI:SS
  habby clob --clob，字符数据，最大长度4G
);

--课程表(关联学生信息表)course
create table course(
cid varchar(100),
cname varchar(50) not null, --非空约束，后面详说
sid varchar(200)
);

2.使用子查询根据参照表创建(desc 表名 错误解决详见：https://blog.csdn.net/Y1013768371/article/details/89142920)

--表course插入数据,方便验证,不详细介绍插入语句
insert into course values('201','@cyy','20');
insert into course values('202','@zzt','30');
select * from course;

CID	CNAME	SID
--------------------
201     @cyy    20
202	@zzt	30

SQL> desc course
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 CID						    VARCHAR2(100)
 CNAME					   NOT NULL VARCHAR2(50)
 SID						    VARCHAR2(200)

--复制表结构(包括约束)和数据
create table course_1 as select * from course;
insert into course_1 values('203','@zly','10');
select * from course_1;

CID	CNAME	SID
-------------------
201	@cyy	20
202	@zzt	30
203	@zly	10

SQL> desc course_1
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 CID						    VARCHAR2(100)
 CNAME					   NOT NULL VARCHAR2(50)
 SID						    VARCHAR2(200)

--复制表结构
create table course_2 as select * from course_1 where 1= 2;
select * from course_2;

CID	CNAME	SID

SQL> desc course_2
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 CID						    VARCHAR2(100)
 CNAME					   NOT NULL VARCHAR2(50)
 SID						    VARCHAR2(200)

--相同结构复制全部数据
insert into course_2 select * from course_1;
select * from course_2;

CID	CNAME	SID
-------------------
201	@cyy	20
202	@zzt	30
203	@zly	10

--相同结构只复制指定列数据
insert into course_1 values('204','ljj',40);
select * from course_1;

CID	CNAME	SID
-------------------
201	@cyy	20
202	@zzt	30
203	@zly	10
204	ljj	40

select * from course_2;

CID	CNAME	SID
-------------------
201	@cyy	20
202	@zzt	30
203	@zly	10

insert into course_2(cid,cname) select cid,cname from course_1 where cid = '204';
select * from course_2;

CID	CNAME	SID
-------------------
201	@cyy	20
202	@zzt	30
203	@zly	10
204	ljj	

表的修改

1.add(添加列)

--单列添加
alter table course_1 add cinfo varchar2(4000);
--多列添加
alter table course_1 add(tid varchar2(120),tname char(100));

select * from course_1;

CID	CNAME	SID	CINFO	TID	TNAME
-----------------------------------------------
201	@cyy	20			
202	@zzt	30			
203	@zly	10			
204	ljj	40	

SQL> desc course_1
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 CID						    VARCHAR2(100)
 CNAME					   NOT NULL VARCHAR2(50)
 SID						    VARCHAR2(200)
 CINFO						    VARCHAR2(4000)
 TID						    VARCHAR2(120)
 TNAME						    CHAR(100)

2.modify(修改列类型)

注：空数据时，long能转为clob，clob不能直接modify转为其他类型，需转详见：https://blog.csdn.net/Y1013768371/article/details/89218639；非空数据类型转换只能短类型转长类型，如char(100) 转char(200) 或varchar(100)

--单列修改列类型
alter table course_1 modify cinfo varchar(2000);
--多列修改列类型
alter table course_1 modify(tid integer,tname varchar(128));

SQL> desc course_1
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 CID						    VARCHAR2(100)
 CNAME					   NOT NULL VARCHAR2(50)
 SID						    VARCHAR2(200)
 CINFO						    VARCHAR2(2000)
 TID						    NUMBER(38)
 TNAME						    VARCHAR2(128)

3.rename column(修改列名)

注：不能同时修改多个列名

alter table course_1 rename column cinfo to cinfo_temp;

SQL> desc course_1
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 CID						    VARCHAR2(100)
 CNAME					   NOT NULL VARCHAR2(50)
 SID						    VARCHAR2(200)
 CINFO_TEMP					    VARCHAR2(2000)
 TID						    NUMBER(38)
 TNAME						    VARCHAR2(128)

4.drop column(删除列)

--单列删除
alter table course_1 drop column cinfo_temp;
--多列删除
alter table course_1 drop(tid,tname);

SQL> desc course_1
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 CID						    VARCHAR2(100)
 CNAME					   NOT NULL VARCHAR2(50)
 SID						    VARCHAR2(200)

约束的创建：primary key(主键约束)/foreign key(外键约束)/not null(非空约束)/check(检查约束)/unique(唯一约束)

语法：

--添加约束
    --列级约束
    列名 列类型 [constraint 约束名] 约束类型(primary key/foreign key/not null/check/unique)
    --表级约束
        --建表时
        [constraint 约束名] 约束类型(primary key/foreign key/not null/check/unique)(列1,列2...) [references 表名(列1,列2...)];
        --建表后
        alter table 表名 add[modify] [constraint 约束名] 约束类型(primary key/foreign key/not null/check/unique)(列1,列2...) [references 表名(列1,列2...)]; 

1.列级约束

--删除stu表再新建
drop table stu; 

--学生信息表stu
--新建基本表及添加列级约束
create table stu(
  --主键约束,不能跟unique约束一起用,[constraint 约束名] -定义约束名方便管理,可以不加
  sid varchar(100) constraint stu_sid primary key, --约束名为stu_sid
 
  sname varchar2(32) unique not null,
  --设置默认值
  sex char(8) default '男',
  --检查约束
  age integer check(age > 0),
  score number(5,2) check(score between 0 and 100),
  admission_date date, 
 --唯一、非空约束同时存在,约束名为stu_sname
  habby clob constraint stu_sname not null
);

SQL> desc stu
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 SID					   NOT NULL VARCHAR2(100)
 SNAME					   NOT NULL VARCHAR2(32)
 SEX						    CHAR(8)
 AGE						    NUMBER(38)
 SCORE						    NUMBER(5,2)
 ADMISSION_DATE 				    DATE
 HABBY					   NOT NULL CLOB

--删除约束,stu_sname为约束名
alter table stu drop constraint stu_sname; 

SQL> desc stu
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 SID					   NOT NULL VARCHAR2(100)
 SNAME					   NOT NULL VARCHAR2(32)
 SEX						    CHAR(8)
 AGE						    NUMBER(38)
 SCORE						    NUMBER(5,2)
 ADMISSION_DATE 				    DATE
 HABBY						    CLOB

 2.表级约束

    1)建表时

--删除course表再新建
drop table course;
--课程表course
--新建基本表时添加表级约束
create table course(
cid varchar(100),
cname varchar(50),
sid varchar(200),
primary key(cid),
check(cname like '%@%'),
constraint course_cname unique(cname),
constraint course_sid foreign key(sid) references stu(sid)
);

SQL> desc course
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 CID					   NOT NULL VARCHAR2(100)
 CNAME						    VARCHAR2(50)
 SID						    VARCHAR2(200)

    2)建表后

--删除course_1表再新建
drop table course_1;
--课程表course
--新建基本表course
create table course_1(
cid varchar(100),
cname varchar(50),
sid varchar(200)
);

SQL> desc course_1
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 CID						    VARCHAR2(100)
 CNAME						    VARCHAR2(50)
 SID						    VARCHAR2(200)

--建表后添加表级非空约束,特殊，添加使用关键字modify
alter table course_1 modify(cname constraint course_1_cname not null,cid not null);
--添加唯一约束,约束名为course_ch1
alter table course_1 add constraint course_1_ch1 unique(cname);
--添加检查约束,约束名由系统分配
alter table course_1 add check(cname like '%@%');
--添加主键约束
alter table course_1 add primary key(cid);
--添加外键约束,约束名为course_1_ch2,特殊，需要关联主表stu的主键sid
alter table course_1 add constraint course_1_ch2 foreign key(sid) references stu(sid);

SQL> desc course_1
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 CID					   NOT NULL VARCHAR2(100)
 CNAME					   NOT NULL VARCHAR2(50)
 SID						    VARCHAR2(200)

 约束的删除

 语法：alter table 表名 drop constraint 约束名;

--删除cname的非空唯一约束,sid的外键约束
alter table course_1 drop constraint course_1_cname;
alter table course_1 drop constraint course_1_ch1;
alter table course_1 drop constraint course_1_ch2;

SQL> desc course_1
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 CID					   NOT NULL VARCHAR2(100)
 CNAME						    VARCHAR2(50)
 SID						    VARCHAR2(200)

 基本表的删除/清空数据

1.truncate(DDL语言，清空表数据，清空表效率高，删除表中数据所占用的磁盘空间的)

truncate table course;

2.delete(DML语言，具有事务功能，删除数据)

delete from  course_1;

3.drop(DDL语言，删除表)  

drop table course_2;

命令	是否删除内容	是否删除表结构	是否释放空间	是否删除相关视图
drop	是	是	是	是
delete	是	否	否	否
truncate	是	否	是	否

视图

 注：

    1)视图在三级模式中属于外模式，可对一列或多列的值进行的排序
    2)视图存有查询语句,是虚拟的表,不以基本表存在，引用基本表，不能被修改更新

1.视图的创建

     语法：create view 视图名 as select [...] from 表名 [...];

--dept不是system用户下的表，调用需直接scott.dept
create view v_dept as select * from scott.dept;
--查看视图v_dept
select * from v_dept;

DEPTNO DNAME        LOC
-----------------------------
10    ACCOUNTING    NEW YORK
20    RESEARCH      DALLAS
30    SALES         CHICAGO
40    OPERATIONS    BOSTON

--查看基本表dept
select * from scott.dept;

DEPTNO DNAME        LOC
-----------------------------
10    ACCOUNTING    NEW YORK
20    RESEARCH      DALLAS
30    SALES         CHICAGO
40    OPERATIONS    BOSTON

2.视图的删除

drop view v_dept;

索引

  注：

    1)索引在三级模式中属于内模式
    2)索引相当于一本书的目录,主要作用提高数据库检索速度

1.索引的创建

语法：create index 索引名 on 表名(列名);

--emp不是system用户下的表，调用需直接scott.emp

select * from scott.emp; --查询时间：0.033s

create index index_emp on SCOTT.emp(sal); 

select * from scott.emp; --查询时间：0.004s

2.索引的删除

drop index index_emp;

参考原文：https://www.cnblogs.com/dato/p/7049343.html