网站开发 视频存储,网站建设 目的,建一个多用户团购网站需要多少钱,代理建设网站原文#xff1a;http://www.cnblogs.com/stephen-liu74/archive/2011/12/16/2290803.html 一、表的定义#xff1a; 对于任何一种关系型数据库而言#xff0c;表都是数据存储的最核心、最基础的对象单元。现在就让我们从这里起步吧。 1. 创建表#xff1a; CREATE… 原文http://www.cnblogs.com/stephen-liu74/archive/2011/12/16/2290803.html 一、表的定义 对于任何一种关系型数据库而言表都是数据存储的最核心、最基础的对象单元。现在就让我们从这里起步吧。 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中文文档如转载本系列博客请同样注明该出处。 转载于:https://www.cnblogs.com/Leo-Forest/archive/2012/11/07/2758719.html
