Oracle 基操笔记（一）建表, 约束

秒速五厘米 2022-04-12 03:59 226阅读 0赞

### 本文包含大量实际操作，功能可通过索引找到 ###

### 文后是PL/SQL Developer的初始配置代码 ###

1 主键

1.1建表

1.2添加单字段主键 (建表时)

1.3 DDL完成后添加主键约束（DML）

2 使用外键约束

2.1添加外键约束

2.2修改数据表时添加外键约束（DML）

2.3 移除外键约束（DML）

3.1非空约束

3.2 移除非空

3.3 指定某字段唯一

3.4 移除唯一约束

4.1 指定默认值

4.2  使用检查约束

4.3 自动递增

5 查看表结构

6 删除表

6.1 孤儿表的删除

6.2 有依赖关系的表删除

7.PL/SQL Developer 的初始配置

--------------------

**序列+触发器实现字段自增[https://blog.csdn.net/adidas74891496/article/details/86518469][https_blog.csdn.net_adidas74891496_article_details_86518469]**

# 1 主键 #

## 1.1建表 ##

员工表为例（DDL）

CREATE TABLE tb_emp
    (
    id NUMBER(11),
    name VARCHAR2(25),
    deptId NUMBER(11),
    salary NUMBER(9,2)
    );

## 1.2添加单字段主键 (建表时) ##

CREATE TABLE tb_emp
    (
    id NUMBER(11) PRIMARY KEY,
    name VARCHAR2(25),
    deptId NUMBER(11),
    salary NUMBER(9,2)
    );

或定义完所有列后添加

CREATE TABLE tb_emp
    (
    id NUMBER(11),
    name VARCHAR2(25),
    deptId NUMBER(11),
    salary NUMBER(9,2),
    PRIMARY KEY (id)
    );

也可多字段联合主键

CREATE TABLE tb_emp
    (
    id NUMBER(11),
    name VARCHAR2(25),
    deptId NUMBER(11),
    salary NUMBER(9,2),
    PRIMARY KEY (id,name)
    );

## 1.3 DDL完成后添加主键约束（DML） ##

在创建表时未定义，可通过修改添加

ALTER TABLE <表名>
    ADD CONSTRAINS <约束名> PRIMARY KEY (字段名) ;

删除主键约束

ALTER TABLE <表名>
    DROP CONSTRAINS <约束名> ;

# #

# 2 使用外键约束 #

## 2.1添加外键约束 ##

[CONSTRAINT <外键名>] FOREIGN KEY 字段1 [,字段2,...]
    REFERENCES <主表名> 主键列1 [,主键列2,...]

/\*

"\[ \]" 表示可选代码

字段1, 字段2 表示可联合主键/外键

\*/

**下面是外键添加示范**

创建部门表 tb\_dept1

CREATE TABLE tb_dept1
    (
    id NUMBER(11) PRIMARY KEY,
    name VARCHAR2(22) NOT NULL,
    location VARCHAR2(50)
    )

定义表tb\_emp5

在表tb\_emp5上添加了名为fk\_emp5\_dept1 的外键约束, 外键名为depId，fk代表Foreign Key

依赖于tb\_dept1的主键id

CREATE TABLE tb_emp5
    (
    id NUMBER(11) PRIMARY KEY,
    name VARCHAR2(25) NOT NULL,
    deptID NUMBER(11),
    salary NUMBER(9,2),
    CONSTRAINT fk_emp5_dept1 FOREIGN KEY (deptId)
    REFERENCES  dept1 (id)
    )

【注意】主外键类型要匹配

## 2.2修改数据表时添加外键约束（DML） ##

ALTER TABLE <表名>
    ADD CONSTRAINS 约束名 FOREIGN KEY (外键约束字段名)
    REFERENCES 数据表名 (字段名)
    ON DELETE CASDADE;

【注】最后一行on delete casdade 是数据库外键定义的一个可选项，

用来设置当主键表中的被参考列的数据发生变化时，外键表中响应字段的变换规则的。

on delete 指在主键表中删除一条记录。

cascade 表示级联操作，就是说，如果主键表中被参考字段更新，外键表中也更新，

主键表中的记录被删除，外键表中改行也相应删除。

## 2.3 移除外键约束（DML） ##

移除数据表tb\_emp5的外键约束fk\_emp\_dept1

ALTER TABLE tb_emp5
    DROP CONSTRAINTS fk_emp_dept1;

## 3.1非空约束 ##

将tb\_emp5表上的字段name指定为非空（DML）

ALTER TABLE tb_emp5
    MODIFY name NOT NULL;

创建表时，就设定好非空：

定义数据表tb\_emp6，指定员工的名称不能为空

CREATE TABLE tb_emp6 
    (
    id     NUMBER(11) PRIMARY KEY,
    name   VARCHAR2(25) NOT NULL,
    deptId  NUMBER(11), 
    salary  NUMBER(9,2)
    );

## 3.2 移除非空 ##

【注意】与上数第二个代码段对比

ALTER TABLE tb_emp5
    MODIFY name  NULL;

## 3.3 指定某字段唯一 ##

定义数据表tb\_dept3，指定部门的名称唯一

CREATE TABLE tb_dept3 
    (
    id      NUMBER(11) PRIMARY KEY,
    name    VARCHAR2(22),
    location  VARCHAR2(50),
    CONSTRAINT STH UNIQUE(name)
    );

添加唯一性约束（DML）

ALTER TABLE tb_emp5
    ADD CONSTRAINT unq_name UNIQUE（name）;

unq\_name 是约束名 ，指定name字段唯一

## 3.4 移除唯一约束 ##

可参考2.3 ，套公式就行

ALTER TABLE tb_emp5
    DROP CONSTRAINTS unq_name;

## 4.1 指定默认值 ##

CREATE TABLE tb_emp7 
    (
    id      NUMBER(11) PRIMARY KEY,
    name   VARCHAR2(25) NOT NULL,
    deptId  NUMBER(11) DEFAULT 1111, 
    salary  NUMBER(9,2)
    );

## 4.2  使用检查约束 ##

语法

CONSTRAINT 检查约束名 CHECK(检查条件)

定义数据表tb\_emp8，指定员工的性别只能输入“男”或者“女”

CREATE TABLE tb_emp8 
    (
    id      NUMBER(11) PRIMARY KEY,
    name   VARCHAR2(25) NOT NULL,
    gender  VARCHAR2(2), 
    age    NUMBER(2),
    CONSTRAINT CHK_GENDER  CHECK (GENDER=’男’ or GENDER=’女’)
    );

定义好后添加约束（DML）

将tb\_emp8表上的字段age添加检查约束，规定年龄输入值在15~25之间。

ALTER TABLE tb_emp8
    ADD CONSTRAINT chk_age  CHECK (age>=15 and age<=25);

删除约束/（DML）

ALTER TABLE tb_emp8
    DROP CONSTRAINTS chk_age;

## 4.3 自动递增 ##

定义数据表tb\_emp9，指定员工的编号自动递增

CREATE TABLE tb_emp9 
    (
    id      NUMBER(11)  GENERATED BY DEFAULT AS IDENTITY
    name   VARCHAR2(25) NOT NULL,
    deptId  NUMBER(11), 
    salary  NUMBER(9,2)
    );

# 5 查看表结构 #

SQL> DESC / DESCRIBE + TableName;

# 6 删除表 #

## 6.1 孤儿表的删除 ##

DROP TABLE TableName

语句执行完毕之后，使用DESC命令查看当前数据库中所有的表

SQL> DESC tb_dept3;

ERROR:                
\-------------------   
错误: 对象 TB\_DEPT3 不存在  
执行结果可以看到，数据表列表中已经不存在名称为tb\_dept2的表，删除操作成功。

## 6.2 有依赖关系的表删除 ##

在数据库中创建两个关联表，首先，创建表tb\_dept2，

CREATE TABLE tb_dept2 
    (
    id       NUMBER(11) PRIMARY KEY,
    name    VARCHAR2(22),
    location  VARCHAR2(50)
    );

接下来创建表tb\_emp，

CREATE TABLE tb_emp 
    (
    id       NUMBER(11) PRIMARY KEY,
    name    VARCHAR2(25),
    deptId   NUMBER(11), 
    salary   NUMBER(9,2),
    CONSTRAINT fk_emp_dept  FOREIGN KEY (deptId) REFERENCES tb_dept2(id)
    );

可以看到，以上执行结果创建了两个关联表tb\_dept2和表tb\_emp，

其中tb\_emp表为子表，具有名称为fk\_emp\_dept的外键约束，

tb\_dept2为父表，其主键id被子表tb\_emp所关联。

**正确操作：**

先删除外键约束

ALTER TABLE tb_emp DROP CONSTRAINTS fk_emp_dept;

语句成功执行后，将取消表tb\_emp和表tb\_dept2之间的关联关系，

此时，可以输入删除语句，将原来的父表tb\_dept2删除，

DROP TABLE tb_dept2;

# 7.PL/SQL Developer 的初始配置 #

**--首先使用USER SYSTEM  
\--设置表空间，指定一个实际位置并设置大小**  
CREATE TABLESPACE TEST  
LOGGING DATAFILE 'D:\\OraData\\OraTblSpace\\TEST01.DBF'【实际为准】  
SIZE 32M  
AUTOEXTEND ON  
NEXT 32M  
EXTENT MANAGEMENT LOCAL;

**--创建用户 leo01 密码也是 leo01**  
CREATE USER leo01 IDENTIFIED BY leo01  
DEFAULT TABLESPACE TEST;

**--赋予用户权限**  
GRANT "CONNECT" TO leo01;  
GRANT RESOURCE TO leo01;  
GRANT DBA TO leo01;  
GRANT "JAVASYSPRIV" TO leo01;  
GRANT "JAVAUSERPRIV" TO leo01;  
GRANT CREATE VIEW TO leo01;  
GRANT CREATE TRIGGER TO leo01;  
GRANT EXECUTE any PROCEDURE to leo01;  
GRANT ALTER any trigger to leo01;

SELECT \* FROM all\_users;  
SELECT \* FROM dba\_users;  
SELECT username,password FROM dba\_users;--并不能查询出来  
SELECT DISTINCT owner FROM all\_objects; --选出不重复的owner字段  
ALTER USER SYS IDENTIFIED BY 123456;    --SYS用户密码改掉  
ALTER USER SYSTEM IDENTIFIED BY 123456;

**--查看各种权限、角色**  
SELECT \* FROM dba\_sys\_privs;  
SELECT \* FROM user\_sys\_privs;  
SELECT \* FROM dba\_roles;  
SELECT \* FROM all\_tables WHERE owner='LEO01';

**1.【SYS用户】具有DBA权限，并具有SYS模式。**

只能通过SYSDBA登录数据库，  
    是Oracle数据库中权限最高的帐号。 sys用户具有“SYSDBA”和“SYSOPER”权限，  
    登陆em时也只能用这两个身份，不能用normal。而system登录em时只能用normal模式登录。  
    sys拥有数据字典(dictionary),或者说dictionary属于sys schema。

**2.【SYSTEM用户】具有DBA权限，但是没有SYSDBA权限。**  
    平常一般用该帐号管理数据库。登录em时只能使用normal登录。

**3.【SYSDBA权限】和【SYSOPER权限】区别**

**【SYSOPER】权限，即数据库操作员权限，**  
    sysoper主要用来启动、关闭数据库，  
    sysoper 登陆后用户是 public。  
    权限包括：

1.                打开数据库(STARTUP, ALTER DATABASE OPEN/MOUNT/OPEN),服务器(CREATE SPFILE,etc)
2.                关闭数据库服务器 
3.                备份数据库 
4.                恢复数据库RECOVERY 
5.                日志归档ARCHIVELOG 
6.                会话限制RESTRICTED SESSION

**【SYSDBA】权限，即数据库管理员权限，最高的系统权限。**  
    任何具有sysdba登录后用户是“SYS”。  
    权限包括：管理功能, 创建数据库(CREATE DATABASE)以及 “SYSOPER”的所有权限  
                        其他用户需要手动grant权限，show user为该用户的名称。

[https_blog.csdn.net_adidas74891496_article_details_86518469]: https://blog.csdn.net/adidas74891496/article/details/86518469