首页 > 代码库 > 表的创建、修改及约束

表的创建、修改及约束

1、创建表

CREATE TABLE EMP2 AS SELECT * FROM EMP WHERE 1=2;--只复制表结构CREATE TABLE TEST1 AS SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE DEPTNO=10;--只复制另一个表中的某几个字段CREATE TABLE EMP2 AS SELECT * FROM EMP; --复制整张表,包括数据CREATE TABLE EMP2 AS SELECT * FROM EMP WHERE rownum<=10;--只复制前10行数据

2、插入数据

INSERT INTO DEPT VALUES(50,DBA,HK);--所有字段都插入值INSERT INTO DEPT(DEPTNO,DNAME) VALUES(60,TEST);--只插入其中几个字段值INSERT INTO EMP2 (SELECT * FROM EMP);--使用子查询插入数据时,不要加values或asINSERT INTO EMP2 (SELECT * FROM EMP WHERE ROWNUM<=10);

3、更新表中的数据

update emp2 set empno=2222,ename=rusky where empno=1111;--如果不加where条件,则更新表中的所有行。update emp2 set (empno,ename)=(2222,rusky) where empno=1111;--ORA-01767: UPDATE ...SET 表达式必须是子查询 update emp2 set (ename,job,hiredate)=(select ename,job,hiredate from emp where empno=7369) where empno=1111;--使用子查询修改表DELETE FROM TEST1 WHERE EMPNO=7782;--删除表中的某一行记录DELETE FROM TEST;--不加WHERE条件,清空表中所有记录DELETE FROM EMP2 WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME=SALES); --使用子查询删除记录

4、修改表结构

ALTER TABLE DEPT2 ADD (MANAGER NUMBER(4)); --增加列ALTER TABLE DEPT2 MODIFY(MANAGER NUMBER(4));--修改列属性ALTER TABLE DEPT2 DROP COLUMN MANAGER;--删除表中的字段DROP TABLE DEPT2;--直接删除表,包括表数据和表结构。TRUNCATE TABLE DEPT2;--只清空表中的数据RENAME DEPT2 TO DEPT3;--修改表名COMMENT ON TABLE DEPT3 IS THIS IS A TEST TABLE; --给表增加注释COMMENT ON COLUMN DEPT3.MANAGER IS THE MANAGER OF THIS DEPARTMENT;--给某个字段增加注释

5、相关查询及其它

SELECT * FROM USER_TABLES; --查看用户所有表SELECT DISTINCT OBJECT_TYPE FROM USER_OBJECTS; --查看用户拥有的数据类型SELECT * FROM USER_CATALOG; --查看用户拥有的表、视图、同义词、序列查看表、字段、视图、同义词等的注释:ALL_COL_COMMENTSUSER_COL_COMMENTSALL_TAB_COMMENTSUSER_TAB_COMMENTS

====
几种不常见的数据类型
LONG:可保存最大长度为2GB的字符数据
CLOB:可保存最大长度为4GB的字符数据
BLOG:可保存最大长度为4GB的二进制数据
BFILE:在外部文件中存储的二进制数据,最大为4GB
RAW and LONG RAW:存储二进行数据

6、约束
--约束用于确保数据库数据满足特定的商业规则。在Oracle中,约束包括:not null、unique、primary key, foreign key和check五种。

CREATE TABLE Goods(GoodsId char(8) primary key, --主键GoodName varchar2(50),UnitPrice number(10,2) check(UnitPrice>0),--单价必须大于0Category varchar2(30),Provider varchar2(100));--添加注释COMMENT ON TABLE Goods is  商品表Goods(商品号GoodsId,商品名GoodName,单价UnitPrice,商品类别Categroy,供应商Provider)CREATE TABLE Customers(CustomerId char(8) primary key, --主键Name varchar2(30) not null,--姓名允许为空Address varchar2(150),Email varchar2(100) unique, --Email唯一Gender char(2) default() check(Gender in(,)), --性别只能是男或女CardId char(18));--添加注释COMMENT ON TABLE Customers is 客户表Customers(客户号CustomerId,姓名Name,住址Address,电邮Email,性别Gender,身份证CardId)CREATE TABLE Purchases(CustomerId char(8) references Customers(Customerid), --外键GoodId char(8) references Goods(GoodsId),Num number(10) check(Num between 1 and 30));--添加注释COMMENT ON TABLE Purchases is 销售表Purchases(客户号CustomerId,商品号GoodsId,购买数量Num)

A、表级定义
表级定义是指在定义了所有列后,再定义约束,这里需要注意,not null约束只能在列级上定义。
案例:

sql> create table Goods(GoodsId char(8),GoodsName varchar2(50), Category varchar(30),constraint PK_GoodsId primary key(GoodsId));

B、列级定义
列级定义是在定义列的同时定义约束。
案例:

sql>create table Goods(GoodsId char(8) constraint PK_GoodsId primary key ,--主键GoodName varchar2(50),UnitPrice number(10,2) check(UnitPrice>0),--单价必须大于0Category varchar(30),Provider varchar(100));

--添加、修改约束
--如果在建表时忘记建立必要的约束,则可以在建表后使用alter table命令为表增加约束,但是要注意:增加not null约束时,需要使用modify选项,而增加其他四种约束使用add选项。

ALTER TABLE Goods modify GoodsId not null;ALTER TABLE Customers add constraint UQ_CardId unique(Cardid);

--删除约束

sql>alter table 表名 drop constraint 约束名称;ALTER TABLE Customers drop constraints UQ_CardId;

--修改约束名

ALTER TABLE EMPLOYEE RENAME CONSTRAINT PK_EMPLOYEE TO PK_EMPLOYEE1;

特别说明:
在删除主键约束的时候,可能有错误。比如
alter table 表名 drop primary key;
这是因为如果在两表存在主从关系,那么在删除主表主键约束时,必须带上cascade选项。
sql> alter table 表名 drop primary key cascade;
--查看约束:
SELECT * FROM USER_CONSTRAINTS;
SELECT * FROM USER_CONS_COLUMNS;
================
其它示例:

列级定义约束:CREATE TABLE DEPARTMENT(DEPTID NUMBER(2) PRIMARY KEY,--或者DEPTID NUMBER(2) CONSTRAINT PK_DEPARTMENT PRIMARY KEY,NAME VARCHAR2(12),LOC VARCHAR2(12));表级定义约束:CREATE TABLE EMPLOYEE(EMPID NUMBER(4),NAME VARCHAR2(10),DEPTID NUMBER(2),CONSTRAINT PK_EMPLOYEE PRIMARY KEY(EMPID),CONSTRAINT FK_DEPARTMENT FOREIGN KEY(DEPTID) REFERENCES DEPARTMENT(DEPTID));

====
定义多列的复合约束

CREATE TABLE DEPARTMENT2(DEPTID NUMBER(2),--或者DEPTID NUMBER(2) CONSTRAINT PK_DEPARTMENT PRIMARY KEY,DNAME VARCHAR2(12),LOC VARCHAR2(12),PRIMARY KEY(DEPTID,DNAME));CREATE TABLE EMPLOYEE2(EMPID NUMBER(4) PRIMARY KEY,NAME VARCHAR2(10),DEPTID NUMBER(2),DNAME VARCHAR2(12),CONSTRAINT FK_DEPARTMENT1 FOREIGN KEY(DEPTID,DNAME) REFERENCES DEPARTMENT2(DEPTID,DNAME) ON DELETE CASCADE--当删除部门表中的某个部门信息时,级联删除员工表中的 DEPTNO,DNAME.或者这样:ON DELETE SET NULL,设为null);

 

表的创建、修改及约束