PostgreSQL：四. 数据表的基本操作

ゞ浴缸里的玫瑰 2023-05-21 07:16 26阅读 0赞

### 四. 数据表的基本操作 ###

#### 4.1 创建数据表 ####

下面的案例是如何添加主键约束，外键约束，唯一约束，非空约束，默认约束等；

1.使用主键约束：有单字段主键，多字段联合主键；  
【例1】定义数据表tb\_emp1，假设表中间没有主键id，为了唯一确定一个员工，可以把name、deptId联合起来做为主键，SQL语句如下：

CREATE TABLE tb_emp1
     (
    name VARCHAR(25),
    deptId INT,
    salary FLOAT,
    PRIMARY KEY(name,deptId)
    );

2.使用外键约束：  
【例2】创建一个部门表tb\_dept1，名称不能为空，SQL语句如下：

CREATE TABLE tb_dept2
    (
    id       int PRIMARY KEY,
    name    VARCHAR(22)  NOT NULL,
    location  VARCHAR(50)
    );

定义数据表tb\_emp2，让它的键deptId作为外键关联到tb\_dept2的主键id，SQL语句为：

CREATE TABLE tb_emp2 
    (
    id      INT PRIMARY KEY,
    name   VARCHAR(25),
    deptId  INT, 
    salary   FLOAT,
    CONSTRAINT fk_emp_dept2 FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
    );

3.使用唯一约束  
【例3】定义数据表tb\_dept2，指定部门的名称唯一，SQL语句如下：

CREATE TABLE tb_dept2 
    (
    id      INT PRIMARY KEY,
    name    VARCHAR(22) UNIQUE,
    location  VARCHAR(50)
    );

在定义完所有列之后指定唯一约束，语法规则如下：  
\[CONSTRAINT <约束名>\] UNIQUE(<字段名>)  
【例4】定义数据表tb\_dept3，指定部门的名称唯一，SQL语句如下：

CREATE TABLE tb_dept3 
    (
    id      INT PRIMARY KEY,
    name    VARCHAR(22),
    location  VARCHAR(50),
    CONSTRAINT STH UNIQUE(name)
    );

4.非空约束和默认约束  
【例5】定义数据表tb\_emp7，指定员工的部门编号默认为1111，SQL语句如下：  
CREATE TABLE tb\_emp7  
(  
id INT PRIMARY KEY,  
name VARCHAR(25) NOT NULL,  
deptId INT DEFAULT 1111,  
salary FLOAT,  
CONSTRAINT fk\_emp\_dept3 FOREIGN KEY (deptId) REFERENCES tb\_dept1(id)  
);

#### 4.2 修改数据表 ####

【例4.6】将数据表tb\_dept3改名为tb\_department3。

ALTER TABLE tb_dept3 RENAME TO tb_department3;

【例4.7】将数据表tb\_dept1中name字段的数据类型由VARCHAR(22)的修改成VARCHAR(30)。

ALTER TABLE tb_dept1 ALTER COLUMN name TYPE VARCHAR(30);

【例4.8】将数据表tb\_dept1中的location字段名称改为loc，数据类型保持不变，SQL语句如下：

ALTER TABLE tb_dept1 RENAME location TO loc;

【例4.9】在数据表tb\_dept1中添加一个没有完整性约束的INT类型的字段managerId（部门经理编号），SQL语句如下：

ALTER TABLE tb_dept1 ADD COLUMN managerId INT;

【例4.10】在数据表tb\_dept1中添加一个不能为空的VARCHAR(12)类型的字段column1，SQL语句如下：

ALTER TABLE tb_dept1 ADD COLUMN column1 VARCHAR(12) not  null;

#### 4.3 删除数据表 ####

删除字段的语法格式如下：  
ALTER TABLE <表名> DROP <字段名>;  
“字段名”指需要从表中删除的字段的名称。  
【例1】删除数据表tb\_dept1表中的managerid字段。

ALTER TABLE tb_dept1 DROP managerid;

【例2】删除数据表tb\_emp9中的外键约束。  
首先创建表tb\_emp9，创建外键deptId关联tb\_dept1表的主键id，SQL语句如下：

CREATE TABLE tb_emp9 
    (
    id      INT PRIMARY KEY,
    name   VARCHAR(25),
    deptId  INT,
    salary   FLOAT,
    CONSTRAINT fk_emp_dept  FOREIGN KEY (deptId) REFERENCES tb_dept1(id)
    );
    ALTER TABLE tb_emp9 DROP CONSTRAINT fk_emp_dept;

【例3】删除数据表tb\_dept2，SQL语句如下：

DROP TABLE IF EXISTS tb_dept2;

语句执行完毕之后，数据表列表中已经不存在名称为tb\_dept2的表，删除操作成功。  
在数据库中创建两个关联表，首先，创建表tb\_dept2，SQL语句如下：

CREATE TABLE tb_dept2 
    (
    id       INT PRIMARY KEY,
    name    VARCHAR(22),
    location  VARCHAR(50)
    );

接下来创建表tb\_emp，SQL语句如下：

CREATE TABLE tb_emp 
    (
    id       INT PRIMARY KEY,
    name    VARCHAR(25),
    deptId   INT, 
    salary   FLOAT,
    CONSTRAINT fk_emp_dept  FOREIGN KEY (deptId) REFERENCES tb_dept2(id)
    );

首先直接删除父表tb\_dept2，输入删除语句如下：

DROP TABLE tb_dept2;

接下来，解除关联子表tb\_emp的外键约束，SQL语句如下：

ALTER TABLE tb_emp DROP CONSTRAINT fk_emp_dept;

语句成功执行后，将取消表tb\_enp和表tb\_dept2之间的关联关系，此时，可以输入删除语句，将原来的父表tb\_dept2删除，SQL语句如下：

DROP TABLE tb_dept2;

#### 4.4 PostgreSQL 11新特性-新增带默认值的字段不在重写数据表 ####

步骤1：新增测试数据表tt，语句如下：

CREATE TABLE tt(id int, name text);

步骤2：插入500万行测试数据，语句如下：

INSERT INTO t1 (id,name ) SELECT n, n || '_ALTER TABLE TEST ' FROM generate_series (1,5000000) n;

步骤3：查看数据表tt的relfilenode和relpages信息，语句如下：

SELECT relname,relfilenode, relpages FROM pg_class WHERE relname='tt';

步骤4：新增带默认值的字段ffname，语句如下：

ALTER TABLE tt ADD COLUMN ffname text DEFAULT '默认值';

步骤5：再次查看数据表tt的relfilenode和relpages信息，语句如下：

SELECT relname,relfilenode, relpages FROM pg_class WHERE relname='tt';

从结果可以看出，relfilenode和relpages的数据并没有发生变化

#### 4.5 综合案例-数据表的基本操作 ####

步骤1：登录数据库。  
启动pgAdmin 4，输入密码连接服务器。  
步骤2：创建数据库company，执行过程如下。

CREATE DATABASE company;

步骤3：创建表offices。  
创建表offices的语句如下：

CREATE TABLE offices 
    (
    officeCode  INT NOT NULL UNIQUE,
    city        VARCHAR(50) NOT NULL,
    address     VARCHAR(50) NOT NULL,
    country     VARCHAR(50) NOT NULL,
    postalCode  VARCHAR(15) NOT NULL,
    PRIMARY KEY  (officeCode)
    );

步骤4 创建表employees。  
创建表employees的语句如下：

CREATE TABLE employees 
    (
    employeeNumber  INT NOT NULL PRIMARY KEY,
    lastName         VARCHAR(50) NOT NULL,
    firstName        VARCHAR(50) NOT NULL,
    mobile           VARCHAR(25) NOT NULL,
    officeCode       INT NOT NULL,
    jobTitle         VARCHAR(50) NOT NULL,
    birth            DATE,
    note            VARCHAR(255),
    sex             VARCHAR(5),
    CONSTRAINT office_fk FOREIGN KEY(officeCode)  REFERENCES offices(officeCode)
    );

步骤5：将表employees的birth字段改名为employee\_birth。  
修改字段名，需要用到ALTER TABLE语句，输入语句如下：

ALTER TABLE employees RENAME birth TO employee_birth;

步骤6：修改sex字段，数据类型为CHAR(1)，非空约束。  
修改字段数据类型，需要用到ALTER TABLE语句，输入语句如下：

ALTER TABLE employees ALTER COLUMN sex TYPE CHAR(1);

步骤7：修改sex字段为非空约束。

ALTER TABLE employees ALTER COLUMN sex SET NOT NULL;

步骤8：删除字段note。  
删除字段，需要用到ALTER TABLE语句，输入语句如下：

ALTER TABLE employees DROP note;

步骤9：增加字段名favoriate\_activity，数据类型为VARCHAR(100)。  
增加字段，需要用到ALTER TABLE语句，输入语句如下：

ALTER TABLE employees ADD COLUMN favoriate_activity VARCHAR(100);

步骤10：删除表offices。  
在创建表employees表时候，设置了表的外键，该表关联了其父表的officeCode主键，如前面所述，删除关联表时，要先删除子表employees的外键约束，才能删除父表，因此，必须先删除employees表的外键约束。  
1）删除employees表的外键约束，输入如下语句：

ALTER TABLE employees DROP CONSTRAINT office_fk;

其中office\_fk为employees表的外键约束的名称，语句执行成功后，即可删除offices父表。  
2）删除表offices，输入如下语句：

DROP TABLE offices;

步骤11：将表employees名称修改为employees\_info。  
修改数据表名，需要用到ALTER TABLE语句，输入语句如下：

ALTER TABLE employees RENAME TO employees_info;

#### 4.6 常见问题及解答 ####