openGauss每日一练第14天|表管理2

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

-- 创建表的时候为表定义列级约束：在列级定义了primary key约束（id列）和not null约束（name列）。
drop table if exists mytest;
create table mytest(
       id bigint primary key, 
       name varchar(50) not null,
       age  int
       );
--插入数据
  insert into mytest values(1,'user1',50);
--查看数据
 select * from mytest;
--查看约束
\d mytest 

root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:00 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=# drop table if exists mytest;
NOTICE:  table "mytest" does not exist, skipping
omm=# DROP TABLE
create table mytest(id bigint primary key,name varchar(50) not null,age int);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "mytest_pkey" for table "mytest"
CREATE TABLE
omm=# insert into mytest values(1,'myuser1',50);
INSERT 0 1
omm=# select * from mytest;
omm=#  id |  name   | age 
----+---------+-----
  1 | myuser1 |  50
(1 row)


omm=# \d mytest
           Table "public.mytest"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | bigint                | not null
 name   | character varying(50) | not null
 age    | integer               | 
Indexes:
    "mytest_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default

omm=# 

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

--在创建表的时候为表定义表级约束：在表列级定义了primary key约束（id列），在列级定义了not null约束（name列）。
drop table if exists mytest001;
create table mytest001(
      id bigint,
	  name varchar(50) not null,  -- 创建列级not null约束
      age  int,
      primary key(id) -- 创建表级约束
     );
insert into mytest001 values(1,'myuser1',50);
select * from mytest001;
 \d mytest001

omm=# drop table if exists mytest001;
NOTICE:  table "mytest001" does not exist, skipping
DROP TABLE
omm=# create table mytest001(
omm(#       id bigint,
omm(#   name varchar(50) not null,  -- 创建列级not null约束
omm(#       age  int,
omm(#       primary key(id) -- 创建表级约束
omm(#      );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "mytest001_pkey" for table "mytest001"
CREATE TABLE
omm=# insert into mytest001 values(1,'myuser1',50);
INSERT 0 1
omm=# select * from mytest001;
 id |  name   | age 
----+---------+-----
  1 | myuser1 |  50
(1 row)

omm=# \d mytest001
          Table "public.mytest001"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | bigint                | not null
 name   | character varying(50) | not null
 age    | integer               | 
Indexes:
    "mytest001_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default

omm=# 

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

--在创建表的时候为表的某个列定义默认值：
drop table if exists mytest002;
create table mytest002(
      id bigint,
      name varchar(28) not null,
      age  int default 20,  --  为该列定义默认值为20
      primary key(id)
     );
--在向表mytest002插入数据时，没有提供age列的值：
insert into mytest002(id,name) values(1,'myuser1');
insert into mytest002(id,name) values(2,'user2');
select * from mytest002;

omm=# drop table if exists mytest002;
NOTICE:  table "mytest002" does not exist, skipping
DROP TABLE
omm=# create table mytest002(
omm(#       id bigint,
omm(#       name varchar(28) not null,
omm(# omm(#       age  int default 20,  --  为该列定义默认值为20
      primary key(id)
omm(#      );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "mytest002_pkey" for table "mytest002"
CREATE TABLE
omm=# insert into mytest002(id,name) values(1,'myuser1');
INSERT 0 1
omm=# insert into mytest002(id,name) values(2,'user2');
omm=# INSERT 0 1
select * from mytest002;
 id |  name   | age 
----+---------+-----
  1 | myuser1 |  20
  2 | user2   |  20
(2 rows)

omm=# 

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

--未定义age列的默认值，如果插入数据时未提供该列的值，则将默认插入空值null
drop table if exists mytest;
create table mytest(
        id bigint,
        name varchar(50) not null,
        age  int,                                
       primary key(id)
      );
insert into mytest(id,name) values(1,'myuser1');
select * from mytest;

omm=# drop table if exists mytest;
DROP TABLE
omm=# create table mytest(
omm(#         id bigint,
omm(#         name varchar(50) not null,
omm(#         age  int,                                
omm(#        primary key(id)
omm(#       );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "mytest_pkey" for table "mytest"
CREATE TABLE
omm=# insert into mytest(id,name) values(1,'myuser1');
INSERT 0 1
omm=# select * from mytest;
 id |  name   | age 
----+---------+-----
  1 | myuser1 |    
(1 row)

omm=# 

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

--创建一个带有serial数据类型的测试表myvoice：
drop table if exists myvoice;
create table myvoice(myvoicenum serial NOT NULL,name varchar(20));
--为表myvoice插入3条记录，并查看插入数据后的表的数据：
insert into myvoice(name) values('myuser1');
insert into myvoice(name) values('user2');
insert into myvoice(name) values('user3');
--可以看到每插入一条记录到表myvoice后，列myvoicenum的值会自增1。
select * from myvoice;

omm=# drop table if exists myvoice;
NOTICE:  table "myvoice" does not exist, skipping
omm=# DROP TABLE
create table myvoice(myvoicenum serial NOT NULL,name varchar(20));
NOTICE:  CREATE TABLE will create implicit sequence "myvoice_myvoicenum_seq" for serial column "myvoice.myvoicenum"
CREATE TABLE
omm=# insert into myvoice(name) values('myuser1');
INSERT 0 1
omm=# insert into myvoice(name) values('user2');
INSERT 0 1
omm=# insert into myvoice(name) values('user3');
INSERT 0 1
omm=# select * from myvoice;
 myvoicenum |  name   
------------+---------
          1 | myuser1
          2 | user2
          3 | user3
(3 rows)

omm=# 

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

--将创建新表，并且会将旧表的数据拷贝给新表：
DROP TABLE if exists newmytestwithdata;
CREATE TABLE newmytestwithdata AS SELECT * FROM myvoice;
SELECT * FROM newmytestwithdata;
--创建和旧表的表结构相同的新表，但是不会将旧表的数据拷贝给新表：
DROP TABLE if exists mytestnewwithoutdata;
CREATE TABLE mytestnewwithoutdata AS SELECT * FROM myvoice WHERE 1=2;
SELECT * FROM mytestnewwithoutdata;

omm=# DROP TABLE if exists newmytestwithdata;
NOTICE:  table "newmytestwithdata" does not exist, skipping
DROP TABLE
omm=# CREATE TABLE newmytestwithdata AS SELECT * FROM myvoice;
omm=# INSERT 0 3
SELECT * FROM newmytestwithdata;
 myvoicenum |  name   
------------+---------
          1 | myuser1
          2 | user2
          3 | user3
(3 rows)

omm=# DROP TABLE if exists mytestnewwithoutdata;
NOTICE:  table "mytestnewwithoutdata" does not exist, skipping
DROP TABLE
omm=# CREATE TABLE mytestnewwithoutdata AS SELECT * FROM myvoice WHERE 1=2;
INSERT 0 0
omm=# SELECT * FROM mytestnewwithoutdata;
omm=#  myvoicenum | name 
------------+------
(0 rows)


omm=#