Oracle数据库 —— 数据库对象

一、DDL语句：数据库定义语言，用来定义数据库对象

1.创建表

语法：

CREATE TABLE 表名(
列名1  数据类型1,
列名2  数据类型2,

……
列名n  数据类型n
)

CREATE TABLE CITY(
ID NUMBER(10),
NAME VARCHAR2(20),
ADRESS VARCHAR2(20)
)

 

2.新增字段

ALTER TABLE CITY ADD(phone VARCHAR2(20))

3.变更字段

ALTER TABLE CITY MODIFY(ID NUMBER(20))

4.删除字段

ALTER TABLE CITY DROP(NAME)

5.删除表

DROP TABLE EMP_LIST

6.清除表中数据

DELETE FROM CITY;

TRUNCATE TABLE EMP_LIST

注意：delete涉及事务操作，效率较低；truncate不涉及事务操作，效率高，适合数据量大的表

7.注释

COMMENT ON TABLE CITY_1 IS '行转列'；--给表添加注释

comment ON COLUMN CITY_2.NATION IS '城市'；--给表的字段添加注释

二、索引

1.主键索引：特殊的唯一索引，即使索引也是约束

CREATE TABLE EMP_LIST(
ID NUMBER(10) PRIMARY KEY,--为ID字段添加主键
EMPNO NUMBER(4),
ENAME VARCHAR2(20)
)

alter TABLE EMP_LIST ADD CONSTRAINT PK_EMP_LIST_ID PRIMARY KEY(ID);

2.普通索引

CREATE TABLE EMP1_LIST(
ID NUMBER(10),
EMPNO NUMBER(4),
ENAME VARCHAR2(20)
)

CREATE INDEX IDX_EMP1_LIST_ID ON EMP1_LIST(ID)

3.唯一索引：值唯一，可为空

CREATE TABLE EMP2_LIST(
ID NUMBER(10),
EMPNO NUMBER(4),
ENAME VARCHAR2(20)
)

CREATE UNIQUE INDEX UIDX_EMP2_LIST_ID ON EMP2_LIST(ID)

4.组合索引：将多个字段作为索引

CREATE TABLE EMP3_LIST(
ID NUMBER(10),
EMPNO NUMBER(4),
ENAME VARCHAR2(20)
)

CREATE INDEX IDX_EMP3_LIST_ID_EMPNO ON EMP3_LIST(ID,EMPNO)

三、视图：虚表，本身不含任何数据，创建视图时会封装一条SQL语句

创建视图前先赋权

grant CREATE VIEW TO SCOTT;

1.创建视图，一张基表
CREATE VIEW V_EMP AS SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP

SELECT * FROM V_EMP

2.创建视图，多张基表
CREATE VIEW V_EMP_DEPT AS SELECT E.EMPNO,E.ENAME,E.SAL,D.DNAME FROM EMP E,DEPT D WHERE E.DEPTNO(+)=D.DEPTNO

SELECT * FROM V_EMP_DEPT

3.使用DML操作视图

--DML操作只能使用在一张基表的视图，无法修改与非键值保存对应的列
INSERT INTO V_EMP_DEPT VALUES(1256,'SKLI',2569,50)

4.可以创建基于视图的视图
CREATE VIEW V_DEPT AS SELECT EMPNO,DEPTNO FROM V_EMP_DEPT V,DEPT D WHERE V.DNAME=D.DNAME

SELECT * FROM V_DEPT

5.创建的视图若存在函数时，则函数必须起别名
CREATE VIEW VV_EMP AS SELECT DEPTNO,AVG(SAL) A FROM EMP GROUP BY DEPTNO

SELECT * FROM VV_EMP

6.删除视图
drop VIEW V_DEPT

注意：视图的数据来源于基表，若在基表中插入数据，则视图中的数据也会随之增加

四、序列：序号生成器，sequence，生成一个或一组间隔相等的整数序号，常用于增加数据

1.创建序列
CREATE SEQUENCE SER_EMP --序列名

START WITH 1 --从几开始

MINVALUE 1 --最小值

MAXVALUE 999999 --最大值

INCREMENT BY 1 --每次间隔，默认为1

cycle /nocycle --达到最大值，从start with重新自增

cache n/nocache --缓存n个序列号，默认为20个，可以提高生成序列的效率

order/noorder --是否按照请求顺序获取序列号

2.获取序列的下一个值
SELECT SEQ_EMP.NEXTVAL FROM DUAL;

3.获取序列的当前值
SELECT SEQ_EMP.CURRVAL FROM DUAL

五、约束

1.创建唯一约束：字段中不能出现重复的非空数据

ALTER TABLE TEXT1 ADD CONSTRAINT UN_TEXT1_ID UNIQUE(ID)

行级约束：在字段上加约束

CREATE TABLE TEXT1(
ID number(4) CONSTRAINT UN_TEXT1_ID UNIQUE,--行级约束
NAME VARCHAR2(20)
)

 表级约束：在所有字段最后添加约束

CREATE TABLE TEXT1(
ID number(4),
NAME VARCHAR2(20),
CONSTRAINT UN_TEXT1_ID UNIQUE(id)--表级约束
)

2.非空约束：不能做表级约束

alter TABLE TEXT2 MODIFY ID NOT NULL

3.检查约束：对某列的取值范围，格式进行限制

alter TABLE TEXT3 ADD CONSTRAINT CK_TEXT3_ID CHECK(NAME='LJH')

4.外键：foreign key
DROP TABLE TEXT5
--子表

CREATE TABLE TEXT4(
ID number(4) PRIMARY KEY,
ENAME VARCHAR2(20)
)

--父表

CREATE TABLE TEXT5(
ID number(4) UNIQUE,
NAME VARCHAR2(20)
)

--创建外键
alter TABLE TEXT4 ADD CONSTRAINT FK_TEXT4_ID FOREIGN KEY(ID) REFERENCES TEXT5(ID)

--插入数据，必须先在父表中插入数据，子表中的数据才能插入,并且两个表中插入的主键相同
insert into TEXT4 VALUES(1,'LKIUJ')

insert into TEXT5 VALUES(1,'LKIUJ')

--删除数据,必须先清除子表中的数据再清除父表中的数据

delete FROM TEXT4

delete FROM TEXT5

注意：唯一索引和唯一约束不是一回事，创建唯一约束的同时会自动创建唯一索引，但创建唯一索引的同时不会自动创建唯一约束。