openGauss每日一练第1天|课后练习

切换用户至omm用户（gaussdb的所有者）

su - omm

gsql连接数据库,并检查当前会话连接信息:
gsql -r
\conninfo

1.创建一个表products

字段名 数据类型 含义
product_id INTEGER 产品编号
product_name Char(30) 产品名
category Char(20) 种类

omm=# CREATE TABLE products
omm-# (product_id INTEGER,
omm(# product_name char(30),
omm(# category Char(20)
omm(# );
CREATE TABLE
omm=# COMMENT ON TABLE products is '产品';
COMMENT
omm=# COMMENT ON COLUMN products.product_id is '产品编号';
COMMENT
omm=# COMMENT ON COLUMN products.product_name is '产品名称';
omm=# COMMENT
COMMENT ON COLUMN products.category is '产品种类';
COMMENT
omm=#
omm=# \d+
                                      List of relations

 Schema |   Name   | Type  | Owner |  Size   |             Storage              | Description
--------+----------+-------+-------+---------+----------------------------------+-------------
 public | products | table | omm   | 0 bytes | {orientation=row,compression=no} | 产品
(1 row)

omm=# \d+ products
                              Table "public.products"
    Column    |     Type      | Modifiers | Storage  | Stats target | Description  
--------------+---------------+-----------+----------+--------------+--------------
 product_id   | integer       |           | plain    |              | 产品编号
 product_name | character(30) |           | extended |              | 产品名称
 category     | character(20) |           | extended |              | 产品种类
Has OIDs: no
Options: orientation=row, compression=no
omm=#

2.向表中插入数据，采用一次插入一条和多条记录的方式

omm=# insert into products values (1502,'olympus camera','electrncs');
INSERT 0 1
omm=# INSERT INTO products VALUES    
omm-# (1601,'lamaze','toys'),    
omm-# (1700,'wait interface','Books'),    
omm-# (1666,'harry potter','toys');
INSERT 0 3
omm=# 

3.查询表中所有记录及记录数

omm=# select * from products;
 product_id |          product_name          |       category       
------------+--------------------------------+----------------------
       1502 | olympus camera                 | electrncs           
       1601 | lamaze                         | toys                
       1700 | wait interface                 | Books               
       1666 | harry potter                   | toys                
(4 rows)

4.查询表中所有category记录，并将查询结果按升序排序

omm=# select distinct(category) from products order by category ;
       category       
----------------------
 Books               
 electrncs           
 toys                
(3 rows)

5.查询表中category为toys的记录

omm=# select * from products where category = 'toys';
 product_id |          product_name          |       category       
------------+--------------------------------+----------------------
       1601 | lamaze                         | toys                
       1666 | harry potter                   | toys                
(2 rows)

6.删除表products

omm=# drop table products;
DROP TABLE