如何在云服务器上的PostgreSQL中创建,删除和管理表

PostgreSQL是一个数据库管理系统,它使用SQL查询语言来操作数据。许多应用程序和网站都具有PostgreSQL可以满足的数据存储需求。本指南将演示如何创建和管理表

什么是PostgreSQL?

PostgreSQL是一个使用SQL查询语言的数据库管理系统。 它是一个非常稳定和功能丰富的数据库系统,可用于存储来自您的VPS上其他应用程序的数据。

在本文中,我们将讨论如何在PostgreSQL界面中创建和管理表。 您将学习如何正确配置表,并使用它们来存储您的信息。

如何在Ubuntu上安装和登录PostgreSQL

在本指南中,我们将在Ubuntu 12.04上安装PostgreSQL,但它应该在大多数其他发行版的默认存储库中可用。

键入以下命令以安装:

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

安装后,创建一个新用户来管理我们将要创建的数据库:

sudo adduser postgres_user

登录到默认的PostgreSQL用户(称为“postgres”)以创建数据库并将其分配给新用户:

sudo su - postgres
psql

您将被放入PostgreSQL命令提示符。

创建与您创建的系统用户匹配的新用户。 然后创建一个由该用户管理的数据库:

CREATE USER postgres_user WITH PASSWORD 'password';
CREATE DATABASE my_postgres_db OWNER postgres_user;

使用以下命令退出界面:

\q

退出默认的“postgres”用户帐户,并使用以下命令登录到您创建的用户:

exit
sudo su - postgres_user

使用以下命令登录到您创建的数据库:

psql my_postgres_db

我们现在准备好了解表管理。

PostgreSQL中的表创建语法

我们的数据库还没有任何表格。 我们可以通过要求PostgreSQL使用这个命令给我们列出可用的表来验证:

\d
No relations found.

我们可以通过遵循以下语法创建一个新表:

CREATE TABLE new_table_name (
	table_column_title TYPE_OF_DATA column_constraints,
	next_column_title TYPE_OF_DATA column_constraints,
	table_constraint
	table_constraint
) INHERITS existing_table_to_inherit_from;

在右括号之后直到分号的部分是一个可选结构,用于继承先前定义中列出的列之外的现有表中的所有列。

括号内的部分分为两部分:列定义和表约束。

PostgreSQL列和表定义

列定义遵循此语法模式:

column_name data_type (optional_data_length_restriction) column_constraints

列名称应该是自解释的。

PostgreSQL数据类型

数据类型可以是以下任何一种:

  • 布尔 :使用“布尔”或“布尔”申报true或false值。
  • 字符值
    • 焦炭 :包含一个字符
    • CHAR(#):持有的字符#编号。 将插入空格以填充任何额外的空间。
    • VARCHAR(#):最多字符数#持有。 可以少含。
  • 整数值
    • SMALLINT:-32768到32767之间的整数。
    • INT:-214783648与214783647之间的整数。
    • 串行 :自动填充整数。
  • 浮点值
    • 浮动(#):浮点数有精度至少为#点。
    • :8个字节的浮点数
    • 数字(#,after_dec):用数字#号实数,小数点后的数字after_dec
  • 日期和时间值
    • 日期 :存储日期值
    • 时间 :存储时间值
    • 时间戳 :存储日期和时间值
    • timestamptz:存储时间戳,其中包括时区数据
    • 间隔 :存储两个时间戳值之间的差
  • 几何数据
    • :存储在一对限定一个点的坐标的
    • :存储一组点,映射出了一条
    • LSEG:一个定义一个线段存储数据
    • :定义一个矩形存储数据
    • 多边形 :定义的任何封闭的空间存储数据
  • 设备规格
    • INET:存储IP地址
    • MACADDR:存储设备的MAC地址

PostreSQL列和表约束

列定义还可以具有为列中找到的数据类型提供规则的约束。 以下可以用作数据类型后面的空格分隔值:

  • NOT NULL:列不能为空值
  • UNIQUE:列的值不能成为任何记录是相同的。 Null始终被认为是唯一的值
  • PRIMARY KEY:上述两个约束的组合。 每个表只能使用一次
  • 检查 :确保条件为真为值在列
  • 参考文献 :值必须存在于一个列中的另一个表

在定义列之后,可以声明表宽约束。 表宽约束可以是UNIQUE,PRIMARY KEY,CHECK或REFERENCES。

如何在PostgreSQL中创建表

让我们创建一个测试表来练习。 我们将创建一个名为“pg_equipment”的表,定义各种游乐设备。 键入以下表定义:

CREATE TABLE pg_equipment (
	equip_id serial PRIMARY KEY,
	type varchar (50) NOT NULL,
	color varchar (25) NOT NULL,
	location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
	install_date date
	);
NOTICE:  CREATE TABLE will create implicit sequence "pg_equipment_equip_id_seq" for serial column "pg_equipment.equip_id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pg_equipment_pkey" for table "pg_equipment"
CREATE TABLE

我们可以看到我们的新表,在提示符中输入“\ d”:

\d
                       List of relations
 Schema |           Name            |   Type   |     Owner     
--------+---------------------------+----------+---------------
 public | pg_equipment              | table    | postgres_user
 public | pg_equipment_equip_id_seq | sequence | postgres_user
(2 rows)

列出该表以及由“equip_id”串行数据类型声明创建的序列。

如何在PostgreSQL中改变表数据

我们可以使用以下一般语法更改表的定义:

ALTER TABLE table_name Action_TO_Take;

例如,我们可以通过输入以下命令为我们的“pg_equipment”表添加一列:

ALTER TABLE pg_equipment ADD COLUMN functioning bool;
ALTER TABLE

我们可以通过键入以下内容查看额外的列:

\d pg_equipment
    Column    |         Type          |                            Modifiers                            
--------------+-----------------------+-----------------------------------------------------------------
 equip_id     | integer               | not null default nextval('pg_equipment_equip_id_seq'::regclass)
 type         | character varying(50) | not null
 color        | character varying(25) | not null
 location     | character varying(25) | 
 install_date | date                  | 
 functioning  | boolean               |
 . . .

要添加指定“除非另有说明,设备应被视为工作”的默认值,请提供以下命令:

ALTER TABLE pg_equipment ALTER COLUMN functioning SET DEFAULT 'true';

如果我们想要确保该值也不为null,我们可以这样做:

ALTER TABLE pg_equipment ALTER COLUMN functioning SET NOT NULL;

要重命名列,请使用以下语法:

ALTER TABLE pg_equipment RENAME COLUMN functioning TO working_order;

要删除刚创建的列,请输入以下命令:

ALTER TABLE pg_equipment DROP COLUMN working_order;

我们可以用这个命令重命名整个表:

ALTER TABLE pg_equipment RENAME TO playground_equip;

删除PostgreSQL中的表

我们可以通过键入以下内容删除我们创建的表:

DROP TABLE playground_equip;
DROP TABLE

如果我们将该命令提供给不存在的表,我们将收到以下错误:

ERROR: table "playground_equip" does not exist

为了避免这个错误,我们可以告诉postgreSQL删除表,如果它存在并成功返回成功。 我们通过发出以下命令来做到这一点:

DROP TABLE IF EXISTS playground_equip;
NOTICE:  table "playground_equip" does not exist, skipping
DROP TABLE

这一次,它告诉我们,没有找到表,但是继续,而不是抛出一个错误。

结论

您现在应该知道在PostgreSQL中创建和管理简单表。 如果您正在从另一个应用程序管理数据,或者从命令行学习如何控制PostgreSQL,这些技能将非常有用。

作者:Justin Ellingwood