`
marb
  • 浏览: 422333 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

PostgreSQL数据表操作手册手册

 
阅读更多

一、表的定义:

    对于任何一种关系型数据库而言,表都是数据存储的最核心、最基础的对象单元。现在就让我们从这里起步吧。
    1. 创建表:
    CREATE TABLE products (
        product_no integer,
        name text,
        price numeric
    );
    
    2. 删除表:
    DROP TABLE products;
    
    3. 创建带有缺省值的表:
    CREATE TABLE products (
        product_no integer,
        name text,
        price numeric DEFAULT 9.99 --DEFAULT是关键字,其后的数值9.99是字段price的默认值。
    );
    
    CREATE TABLE products (
        product_no SERIAL,            --SERIAL类型的字段表示该字段为自增字段,完全等同于Oracle中的Sequence
        name text,
        price numeric DEFAULT 9.99
    );
    输出为:
    NOTICE:  CREATE TABLE will create implicit sequence "products_product_no_seq" for serial column "products.product_no"
    
    4. 约束:
    检查约束是表中最为常见的约束类型,它允许你声明在某个字段里的数值必须满足一个布尔表达式。不仅如此,我们也可以声明表级别的检查约束。
    CREATE TABLE products (
        product_no integer,
        name text,
        --price字段的值必须大于0,否则在插入或修改该字段值是,将引发违规错误。还需要说明的是,该检查约束
        --是匿名约束,即在表定义时没有显示命名该约束,这样PostgreSQL将会根据当前的表名、字段名和约束类型,
        --为该约束自动命名,如:products_price_check。
        price numeric CHECK (price > 0) 
    );
  
    CREATE TABLE products (
        product_no integer,
        name text,
        --该字段的检查约束被显示命名为positive_price。这样做好处在于今后维护该约束时,可以根据该名进行直接操作。
        price numeric CONSTRAINT positive_price CHECK (price > 0) 
    );
    下面的约束是非空约束,即约束的字段不能插入空值,或者是将已有数据更新为空值。
    CREATE TABLE products (
        product_no integer NOT NULL,
        name text NOT NULL,
        price numeric
    );
    如果一个字段中存在多个约束,在定义时可以不用考虑约束的声明顺序。
    CREATE TABLE products (
        product_no integer NOT NULL,
        name text NOT NULL,
        price numeric NOT NULL CHECK (price > 0)
    );
    唯一性约束,即指定的字段不能插入重复值,或者是将某一记录的值更新为当前表中的已有值。
    CREATE TABLE products (
        product_no integer UNIQUE,
        name text,
        price numeric
    );

    CREATE TABLE products (
        product_no integer,
        name text,
        price numeric,
        UNIQUE (product_no)
    );
    为表中的多个字段定义联合唯一性。
    CREATE TABLE example (
        a integer,
        b integer,
        c integer,
        UNIQUE (a, c)
    );
    为唯一性约束命名。
    CREATE TABLE products (
        product_no integer CONSTRAINT must_be_different UNIQUE,
        name text,
        price numeric
    );
    在插入数据时,空值(NULL)之间被视为不相等的数据,因此对于某一唯一性字段,可以多次插入空值。然而需要注意的是,这一规则并不是被所有数据库都遵守,因此在进行数据库移植时可能会造成一定的麻烦。
    
    5. 主键和外键:
    从技术上来讲,主键约束只是唯一约束和非空约束的组合。
    CREATE TABLE products (
        product_no integer PRIMARY KEY,  --字段product_no被定义为该表的唯一主键。
        name text,
        price numeric
    );
    和唯一性约束一样,主键可以同时作用于多个字段,形成联合主键:
    CREATE TABLE example (
        a integer,
        b integer,
        c integer,
        PRIMARY KEY (b, c)
    );
    外键约束声明一个字段(或者一组字段)的数值必须匹配另外一个表中某些行出现的数值。 我们把这个行为称做两个相关表之间的参考完整性。
    CREATE TABLE orders (
        order_id integer PRIMARY KEY--该表也可以有自己的主键。
        --该表的product_no字段为上面products表主键(product_no)的外键。
        product_no integer REFERENCES products(product_no), 
        quantity integer
    );
    
    CREATE TABLE t1 (
        a integer PRIMARY KEY,
        b integer,
        c integer,
        --该外键的字段数量和被引用表中主键的数量必须保持一致。
        FOREIGN KEY (b, c) REFERENCES example (b, c)
    );   
    当多个表之间存在了主外键的参考性约束关系时,如果想删除被应用表(主键表)中的某行记录,由于该行记录的主键字段值可能正在被其引用表(外键表)中某条记录所关联,所以删除操作将会失败。如果想完成此操作,一个显而易见的方法是先删除引用表中和该记录关联的行,之后再删除被引用表中的该行记录。然而需要说明的是,PostgreSQL为我们提供了更为方便的方式完成此类操作。
    CREATE TABLE products (
        product_no integer PRIMARY KEY,
        name text,
        price numeric
    );
    
    CREATE TABLE orders (
        order_id integer PRIMARY KEY,
        shipping_address text
    );
    
    CREATE TABLE order_items (
        product_no integer REFERENCES products ON DELETE RESTRICT--限制选项
        order_id integer REFERENCES orders ON DELETE CASCADE--级联删除选项
        quantity integer,
        PRIMARY KEY (product_no, order_id)
    );
    限制和级联删除是两种最常见的选项。RESTRICT 禁止删除被引用的行。 NO ACTION 的意思是如果在检查约束的时候,如果还存在任何引用行,则抛出错误; 如果你不声明任何东西,那么它就是缺省的行为。(这两个选择的实际区别是,NO ACTION 允许约束检查推迟到事务的晚些时候,而 RESTRICT 不行。) CASCADE声明在删除一个被引用的行的时候,引用它的行也会被自动删除掉。 在外键字段上的动作还有两个选项: SET NULL 和 SET DEFAULT。 这样会导致在被引用行删除的时候,引用它们的字段分别设置为空或者缺省值。 请注意这些选项并不能让你逃脱被观察和约束的境地。比如,如果一个动作声明 SET DEFAULT,但是缺省值并不能满足外键,那么动作就会失败。类似ON DELETE,还有ON UPDATE 选项,它是在被引用字段修改(更新)的时候调用的。可用的动作是一样的。

二、系统字段:

    PostgreSQL的每个数据表中都包含几个隐含定义的系统字段。因此,这些名字不能用于用户定义的字段名。这些系统字段的功能有些类似于Oracle中的rownum和rowid等。
    oid: 行的对象标识符(对象ID)。这个字段只有在创建表的时候使用了WITH OIDS,或者是设置了配置参数default_with_oids时出现。这个字段的类型是oid(和字段同名)。 
    tableoid: 包含本行的表的OID。这个字段对那些从继承层次中选取的查询特别有用,因为如果没有它的话,我们就很难说明一行来自哪个独立的表。tableoid可以和pg_class的oid字段连接起来获取表名字。 
    xmin: 插入该行版本的事务的标识(事务ID)。
    cmin: 在插入事务内部的命令标识(从零开始)。 
    xmax: 删除事务的标识(事务ID),如果不是被删除的行版本,那么是零。
    cmax: 在删除事务内部的命令标识符,或者是零。 
    ctid: 一个行版本在它所处的表内的物理位置。请注意,尽管ctid可以用于非常快速地定位行版本,但每次VACUUM FULL之后,一个行的ctid都会被更新或者移动。因此ctid是不能作为长期的行标识符的。    
    OID是32位的量,是在同一个集群内通用的计数器上赋值的。对于一个大型或者长时间使用的数据库,这个计数器是有可能重叠的。因此,假设OID是唯一的是非常错误的,除非你自己采取了措施来保证它们是唯一的。如果你需要标识表中的行,我们强烈建议使用序列号生成器。     
    
三、表的修改:

    1. 增加字段:
    ALTER TABLE products ADD COLUMN description text;
    新增的字段对于表中已经存在的行而言最初将先填充所给出的缺省值(如果你没有声明DEFAULT子句,那么缺省是空值)。
    在新增字段时,可以同时给该字段指定约束。
    ALTER TABLE products ADD COLUMN description text CHECK(description <> '');
    
    2. 删除字段:
    ALTER TABLE products DROP COLUMN description;
    如果该表为被引用表,该字段为被引用字段,那么上面的删除操作将会失败。如果要想在删除被引用字段的同时级联的删除其所有引用字段,可以采用下面的语法形式。
    ALTER TABLE products DROP COLUMN description CASCADE;
    
    3. 增加约束:
    ALTER TABLE products ADD CHECK(name <> '');  --增加一个表级约束
    ALTER TABLE products ADD CONSTRAINT some_name UNIQUE(product_no);--增加命名的唯一性约束。
    ALTER TABLE products ADD FOREIGN KEY(pdt_grp_id) REFERENCES pdt_grps; --增加外键约束。
    ALTER TABLE products ALTER COLUMN product_no SET NOT NULL--增加一个非空约束。
    
    4. 删除约束:
    ALTER TABLE products DROP CONSTRAINT some_name;
    对于显示命名的约束,可以根据其名称直接删除,对于隐式自动命名的约束,可以通过psql的\d tablename来获取该约束的名字。和删除字段一样,如果你想删除有着被依赖关系地约束,你需要用CASCADE。一个例子是某个外键约束依赖被引用字段上的唯一约束或者主键约束。如:
    MyTest=# \d products
         Table "public.products"
       Column     |  Type   | Modifiers
     ------------+---------+-----------
     product_no | integer |
     name          | text    |
     price           | numeric |
     Check constraints:
        "positive_price" CHECK (price > 0::numeric)
    和其他约束不同的是,非空约束没有名字,因此只能通过下面的方式删除:
    ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;
    
    5. 改变字段的缺省值:
    在为已有字段添加缺省值时,不会影响任何表中现有的数据行, 它只是为将来INSERT命令改变缺省值。
    ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;
    下面为删除缺省值:
    ALTER TABLE products ALTER COLUMN price DROP DEFAULT
    
    6. 修改字段的数据类型:
    只有在字段里现有的每个项都可以用一个隐含的类型转换转换成新的类型时才可能成功。比如当前的数据都是整型,而转换的目标类型为numeric或varchar,这样的转换一般都可以成功。与此同时,PostgreSQL还将试图把字段的缺省值(如果存在)转换成新的类型, 还有涉及该字段的任何约束。但是这些转换可能失败,或者可能生成奇怪的结果。 在修改某字段类型之前,你最好删除那些约束,然后再把自己手工修改过的添加上去。 
    ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);
       
    7. 修改字段名:
    ALTER TABLE products RENAME COLUMN product_no TO product_number;
    
    8. 修改表名:
    ALTER TABLE products RENAME TO items;
    
四、权限:

    只有表的所有者才能修改或者删除表的权限。要赋予一个权限,我们使用GRANT命令,要撤销一个权限,使用REVOKE命令。
    需要指出的是,PUBLIC是特殊"用户"可以用于将权限赋予系统中的每一个用户。在声明权限的位置写ALL则将所有的与该对象类型相关的权限都赋予出去。
    GRANT UPDATE ON table_name TO user;  --将表的更新权限赋予指定的user。
    GRANT SELECT ON table_name TO GROUP group; --将表的select权限赋予指定的组。
    REVOKE ALL ON table_name FROM PUBLIC; --将表的所有权限从Public撤销。
    最初,只有对象所有者(或者超级用户)可以赋予或者撤销对象的权限。但是,我们可以赋予一个"with grant option"权限,这样就给接受权限的人以授予该权限给其它人的权限。如果授予选项后来被撤销,那么所有那些从这个接受者接受了权限的用户(直接或者通过级连的授权)都将失去该权限。

分享到:
评论

相关推荐

    PostgreSQL中文手册9.2

    PostgreSQL PostgreSQL PostgreSQL学习手册 学习手册 学习手册 (数据表 数据表 ) 4 一、表的定义: 一、表的定义: 一、表的定义: . 4 PostgreSQL PostgreSQL PostgreSQL学习手册 学习手册 学习手册 (模式 Schema) ...

    postgresql 中文学习手册

    PostgreSQL学习手册(数据表) PostgreSQL学习手册(模式Schema) PostgreSQL学习手册(表的继承和分区) PostgreSQL学习手册(常用数据类型) PostgreSQL学习手册(函数和操作符&lt;一&gt;) PostgreSQL学习手册(函数和操作符&lt;二&gt;) ...

    PostgreSQL12.2中文手册.chm.7z

    通过深入阅读和理解这份"PostgreSQL12.2中文手册",你不仅可以掌握PostgreSQL的基本操作,还能了解到高级特性及其应用,为你的数据库管理工作打下坚实基础。无论你是新手还是经验丰富的DBA,这份手册都将是你不可或...

    PostgreSQL9.6中文离线手册 pdf版

    随着NoSQL的兴起,PostgreSQL 9.6增强了对JSON数据的支持,包括JSONB类型和JSON操作函数,使得开发者可以在关系数据库中方便地存储和查询JSON数据。 **8. 安全性与权限管理** 手册涵盖了PostgreSQL的安全特性,如...

    PostgreSQL_8.4安装及空间数据导入_安装操作手册

    本操作手册主要涉及PostgreSQL 8.4版本的安装流程以及如何在PostgreSQL 8.4数据库中导入空间数据。 首先,进行PostgreSQL 8.4安装文件的下载。安装文件可以从指定的网站获取,例如***或***。下载完成后,双击运行...

    PostgreSQL 开发规范培训手册

    在数据类型转换方面,手册提到了在增删改操作时的注意事项,例如varchar与text之间的隐式转换,以及不同数值类型之间的隐式转换规则。 在进行类型映射时,手册列出了Oracle字段类型与PostgreSQL字段类型的对应关系...

    postgresql_必备参考手册

    **PostgreSQL 必备参考手册** 在数据库领域,PostgreSQL 是一个强大且开源的关系型数据库管理系统(RDBMS),以其高度稳定性和丰富的功能而受到广大开发者和企业的青睐。本手册针对 PostgreSQL 提供了详尽的指导,...

    postgresql中文手册.zip

    3. **表和数据类型**:理解PostgreSQL支持的各种数据类型,如数值、字符串、日期/时间、布尔值,以及自定义类型。学会如何创建、修改和删除表,以及设置约束(如主键、外键和唯一性约束)。 4. **查询语言**:深入...

    PostgreSQL用户手册10.1版本.rar

    这篇详尽的手册旨在帮助用户更好地理解和操作 PostgreSQL 10.1,特别是对于开发“高斯”这样的项目来说,这将是一个不可多得的参考资料。 1. **新特性与改进** - **并行查询**:PostgreSQL 10 引入了并行查询机制...

    PostgreSQL 10.1中文手册.pdf

    手册中还提到了一些重要的概念,例如SQL语言,它是操作关系数据库的标准化语言,包括了创建表、插入数据、查询、连接表、使用聚集函数、更新和删除数据等操作。高级特性部分则涵盖了视图(允许用户创建一个虚拟表)...

    PostgreSQL9.3.1 官方中文参考手册.pdf

    数据操作章节教用户如何通过SQL语句对数据库进行插入、更新和删除数据的操作。查询章节则介绍了如何使用PostgreSQL进行数据的查询操作,包括表表达式、选择列表、组合查询、行排序以及LIMIT和OFFSET的使用等。这部分...

    PostgreSQL12.2帮助手册v1.0.pdf

    对于PostgreSQL的新手,手册可以帮助理解数据库的基本概念和操作方式,如如何创建数据库、插入和检索数据,以及如何编写复杂的查询语句。而对于有经验的数据库管理员,手册提供了详细的配置选项和系统设置,以帮助...

    中文手册12.2_PostgreSQL.rar

    1. 数据库与表:在PostgreSQL中,数据库是存储数据的逻辑容器,而表则是存储具体数据的结构。理解这两个概念是使用数据库的基础。 2. SQL语言:SQL(Structured Query Language)是用于管理关系数据库的标准语言,...

    PostgreSQL 9.6 中文手册 带书签 高清

    此外,它还支持常见的 DDL(数据定义语言)和 DML(数据操纵语言)操作,如创建表、索引、视图、触发器和存储过程。 **三、性能优化** PostgreSQL 9.6 引入了并行查询功能,允许在多个 CPU 核心之间分发查询执行,...

    PostgreSQL 10手册.pdf

    - 在表之间连接:解释了如何通过JOIN操作连接多个表来获取数据。 - 聚集函数:描述了聚合函数的使用方法,如SUM、AVG、MAX和MIN等,这些函数可以对数据集进行汇总计算。 - 更新:说明了如何使用UPDATE语句修改已...

    postgresql8.3中文手册

    **PostgreSQL 8.3 中文手册:数据库复制与更多** PostgreSQL 8.3 是一个强大、开源的关系型数据库管理系统,它具有丰富的特性和高度的可扩展性。本手册针对的是8.3版本,这一版本引入了许多重要的改进,尤其是...

    postgresql手册 中文版+英文版

    首先,从标题和描述我们可以知道,手册涵盖了PostgreSQL的基本操作和常见命令。对于初学者,理解如何安装和配置PostgreSQL至关重要。这可能包括下载适合的操作系统版本,配置数据库服务器参数,以及创建和管理数据库...

    postgresql-12-A4_postgresql手册_

    本手册全面覆盖了关于PostgreSQL 12的基础知识、高级概念以及实践操作,帮助用户深入理解和充分利用这一强大的开源数据库系统。 一、数据库创建与管理 1. 数据库创建:PostgreSQL 12允许用户通过`createdb`命令或...

    postgresql8.2.3用户手册API

    总的来说,《postgresql8.2.3用户手册API》是开发者理解和操作PostgreSQL数据库不可或缺的参考资料,它涵盖了从基本的数据库连接到复杂的数据操作、安全性和并发控制等所有重要方面。通过深入学习并熟练掌握这些知识...

Global site tag (gtag.js) - Google Analytics