openGauss每日一练第14天 | openGauss逻辑结构：表管理2

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

## 创建表omm=# create table table_1 (
omm(# id integer primary key,  ## 列级约束
omm(# name varchar(10) not null,
omm(# age integer
omm(# );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "table_1_pkey" for table "table_1"
CREATE TABLE## 向表中插入数据omm=# insert into table_1 values (1,'li',10);
INSERT 0 1## 查询表中数据
omm=# select * from table_1 ;
 id | name | age 
----+------+-----
  1 | li   |  10
(1 row)
## 查看表结构
omm=# \d table_1
           Table "public.table_1"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | integer               | not null
 name   | character varying(10) | not null
 age    | integer               | 
Indexes:
    "table_1_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default

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

## 创建表
omm=# create table table_2 (
omm(# id integer,
omm(# name varchar(10) not null,
omm(# age integer,
omm(# primary key (id)   ## 表级约束
omm(# );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "table_2_pkey" for table "table_2"
CREATE TABLE## 向表中插入数据
omm=# insert into table_2 values (1,'li',10);
INSERT 0 1
## 查询表中数据
omm=# select * from table_2;
 id | name | age 
----+------+-----
  1 | li   |  10
(1 row)
## 查看表结构
omm=# \d table_2
           Table "public.table_2"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | integer               | not null
 name   | character varying(10) | not null
 age    | integer               | 
Indexes:
    "table_2_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default

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

## 创建表
omm=# create table table_3 (
omm(# id integer,
omm(# name varchar(10),
omm(# age integer default 10,  ## 定义默认值为10
omm(# primary key (id)omm(# 
omm(# );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "table_3_pkey" for table "table_3"
CREATE TABLE## 向表中插入数据
omm=# insert INTO table_3 (id,name) values (1,'li');
INSERT 0 1## 查询表中数据
omm=# select * from table_3;
 id | name | age 
----+------+-----
  1 | li   |  10
(1 row)
## 查看表结构
omm=# \d table_3
           Table "public.table_3"
 Column |         Type          | Modifiers  
--------+-----------------------+------------
 id     | integer               | not null
 name   | character varying(10) | 
 age    | integer               | default 10
Indexes:
    "table_3_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default

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

## 创建表
omm=# create table table_4 (
omm(# id integer, 
omm(# name varchar(10),
omm(# age integer,
omm(# primary key (id)
omm(# );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "table_4_pkey" for table "table_4"
CREATE TABLE## 向表中插入数据
omm=# insert into table_4 (id,name) values (1,'li');
INSERT 0 1## 查询表中数据
omm=# select * from table_4;
 id | name | age 
----+------+-----
  1 | li   |    
(1 row)
## 查看表结构
omm=# \d table_4
           Table "public.table_4"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | integer               | not null
 name   | character varying(10) | 
 age    | integer               | 
Indexes:
    "table_4_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default

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

omm=# create table table_5 (                              
omm(# invoicen serial not null, 
omm(# name varchar(10),
omm(# age integer
omm(# );
NOTICE:  CREATE TABLE will create implicit sequence "table_5_invoicen_seq" for serial column "table_5.invoicen"
CREATE TABLE## 向表中插入数据
omm=# insert into table_5 (name,age) values ('li',10);
INSERT 0 1
omm=# insert into table_5 (name,age) values ('liu',12);
INSERT 0 1
## 查询表中数据
omm=# select * from table_5;
 invoicen | name | age 
----------+------+-----
        1 | li   |  10
        2 | liu  |  12
(2 rows)

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

## 根据现有的table_5创建table_6，且将旧表的数据拷贝给新表omm=# create table table_6 as select * from table_5;
INSERT 0 2

omm=# select * from table_6;
 invoicen | name | age 
----------+------+-----
        1 | li   |  10
        2 | liu  |  12
(2 rows)## 根据现有的table_5创建table_7，且仅会创建同结构的表，不会将旧表的数据拷贝给新表
omm=# create table table_7 as select * from table_5 where 1=2;
INSERT 0 0
omm=# select * from table_7;
 invoicen | name | age 
----------+------+-----
(0 rows)