学习目标

学习表的约束、表的默认值、自增类型等技术

课程作业

1.创建表的时候定义列级约束

--创建带列级约束的表，并测试约束的有效性
newdb1=# create table test(id bigint primary key, name varchar(50) not null,age  int);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
newdb1=#  insert into test values(1,'user1',50);
INSERT 0 1
newdb1=#  insert into test values(1,'',50);
ERROR:  null value in column "name" violates not-null constraint
DETAIL:  Failing row contains (1, null, 50).
newdb1=#  select * from test;
 id | name  | age
----+-------+-----
  1 | user1 |  50
(1 row)

--查看表定义也能看到约束
newdb1=# \d test
            Table "public.test"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | bigint                | not null
 name   | character varying(50) | not null
 age    | integer               |
Indexes:
    "test_pkey" PRIMARY KEY, btree (id) TABLESPACE newtbs1
复制

2.创建表的时候定义表级约束

--查看表定义也能看到约束,其中主键就是表级约束
newdb1=# \d test
            Table "public.test"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | bigint                | not null
 name   | character varying(50) | not null
 age    | integer               |
Indexes:
    "test_pkey" PRIMARY KEY, btree (id) TABLESPACE newtbs1
复制

3.为表的属性定义默认值

--创建了字段age默认值是20
newdb1=#  create table test002(id bigint,name varchar(28) not null,age  int default 20,primary key(id));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test002_pkey" for table "test002"
CREATE TABLE
--验证
newdb1=# insert into test002 values(1,'tx');
INSERT 0 1
newdb1=# select * from test002;
 id | name | age
----+------+-----
  1 | tx   |  20
(1 row)


复制

4.如果在创建表的时候，没有为某列定义默认值，缺省的默认值是空值null

--age地段没有定义默认值，缺省值为空
newdb1=#  create table test003(id bigint,name varchar(28) not null,age  int ,primary key(id));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test003_pkey" for table "test003"
CREATE TABLE
newdb1=# insert into test003 values(1,'tx');
INSERT 0 1
newdb1=# select * from test003;
 id | name | age
----+------+-----
  1 | tx   |
(1 row)


复制

5.创建表时使用自增数据类型

newdb1=# create table invoice(invoicenum serial NOT NULL,name varchar(20));
NOTICE:  CREATE TABLE will create implicit sequence "invoice_invoicenum_seq" for serial column "invoice.invoicenum"
CREATE TABLE
newdb1=#  insert into invoice(name) values('user1');
INSERT 0 1
newdb1=# insert into invoice(name) values('user2');
INSERT 0 1
newdb1=# select * from invoice;
 invoicenum | name
------------+-------
          1 | user1
          2 | user2
(2 rows)
复制

6.使用现有的表创建新表

特别注意 使用ctas方式创建新表的时候，老表的约束、主键等都不会带到新表，只有数据会过去

newdb1=# create table invoice2 as select * from invoice;
INSERT 0 2
newdb1=#
newdb1=# \d invoice2
            Table "public.invoice2"
   Column   |         Type          | Modifiers
------------+-----------------------+-----------
 invoicenum | integer               |
 name       | character varying(20) |

newdb1=# select * from invoice2;
 invoicenum | name
------------+-------
          1 | user1
          2 | user2
(2 rows)


newdb1=# insert into invoice2(name) values('user3');
INSERT 0 1
newdb1=# select * from invoice2;
 invoicenum | name
------------+-------
          1 | user1
          2 | user2
            | user3
(3 rows)

newdb1=# \d test002
           Table "public.test002"
 Column |         Type          | Modifiers
--------+-----------------------+------------
 id     | bigint                | not null
 name   | character varying(28) | not null
 age    | integer               | default 20
Indexes:
    "test002_pkey" PRIMARY KEY, btree (id) TABLESPACE newtbs1

newdb1=# create table test0022 as select * from test002;
INSERT 0 1
newdb1=# \d test0022
          Table "public.test0022"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | bigint                |
 name   | character varying(28) |
 age    | integer               |
复制

使用ctas where 1=2方式只能带过去表结构，数据和索引约束这些都没有的

newdb1=# CREATE TABLE test00222 AS SELECT * FROM test002 WHERE 1=2;
INSERT 0 0
newdb1=# \d test00222
          Table "public.test00222"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | bigint                |
 name   | character varying(28) |
 age    | integer               |

newdb1=#
newdb1=#
newdb1=# \d invoice
                                      Table "public.invoice"
   Column   |         Type          |                          Modifiers
------------+-----------------------+--------------------------------------------------------------
 invoicenum | integer               | not null default nextval('invoice_invoicenum_seq'::regclass)
 name       | character varying(20) |

newdb1=# CREATE TABLE invoice222 AS SELECT * FROM invoice  WHERE 1=2;
INSERT 0 0
newdb1=# \d invoice222
           Table "public.invoice222"
   Column   |         Type          | Modifiers
------------+-----------------------+-----------
 invoicenum | integer               |
 name       | character varying(20) |

newdb1=#
复制