进入墨天轮1分钟提供的环境。熟悉一下OpenGauss的gsql环境。和PG差不多，感谢恩墨的平台，对于开源有了新的理解

 学习心得体会和课后练习

1、连接openGauss

root@modb:~# 
root@modb:~# 
root@modb:~# su - omm
omm@modb:~$

2、创建数据表

omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=# CREATE TABLE customer_t
omm-# (  c_customer_sk             integer,   
omm(#   c_customer_id             char(5),    
omm(#   c_first_name              char(6),    
omm(#   c_last_name               char(8) 
omm(# ) ;
omm=# CREATE TABLE

3、向表中插入记录

–列出表中各列字段

omm=# \dt customer_t 
--------+------------+-------+-------+----------------------------------
 public | customer_t | table | omm   | {orientation=row,compression=no}
(1 row)

omm=#                            List of relations
 Schema |    Name    | Type  | Owner |             Storage              

omm=# \d customer_t 
        Table "public.customer_t"
    Column     |     Type     | Modifiers 
---------------+--------------+-----------
 c_customer_sk | integer      | 
 c_customer_id | character(5) | 
 c_first_name  | character(6) | 
 c_last_name   | character(8) | 

omm=#

omm=# INSERT INTO customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES (3769, 5, 'Grace','White');
INSERT 0 1

–如果已经知道表中字段的顺序，也可不列出表中的字段，和上条语法等效

omm=# INSERT INTO customer_t VALUES (3769, 5, 'Grace','White');
INSERT 0 1
omm=# 
omm=# 
omm=# select * from customer_t ;
 c_customer_sk | c_customer_id | c_first_name | c_last_name 
---------------+---------------+--------------+-------------
          3769 | 5             | Grace        | White   
          3769 | 5             | Grace        | White   
          3769 | 5             | Grace        | White   
omm=#           3769 | 5             | Grace        | White 

-插入多条

INSERT INTO customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES    
(6885, 1, 'Joes', 'Hunter'),    
(4321, 2, 'Lily','Carter'),    
(9527, 3, 'James', 'Cook'),
(9500, 4, 'Lucy', 'Baker');

4、查看表

–查看表中记录数

select count(*) from customer_t;
omm=# select count(*) from customer_t;
 count 
-------
     8
(1 row)

–查看所有记录

select * from customer_t;
omm=# select * from customer_t;
 c_customer_sk | c_customer_id | c_first_name | c_last_name 
---------------+---------------+--------------+-------------
          3769 | 5             | Grace        | White   
          3769 | 5             | Grace        | White   
          3769 | 5             | Grace        | White   
          3769 | 5             | Grace        | White   
          6885 | 1             | Joes         | Hunter  
          4321 | 2             | Lily         | Carter  
          9527 | 3             | James        | Cook    
          9500 | 4             | Lucy         | Baker   
(8 rows)

–查询表中所有c_first_name记录**

select c_first_name from customer_t;
omm=# select c_first_name from customer_t;
 c_first_name 
--------------
 Grace 
 Grace 
 Grace 
 Grace 
 Joes  
 Lily  
 James 
 Lucy  
(8 rows)

–查询表中所有c_first_name记录，并去掉重复数据

SELECT DISTINCT(c_first_name) from customer_t;
omm=# SELECT DISTINCT(c_first_name) from customer_t;
 c_first_name 
--------------
 James 
 Grace 
 Lucy  
 Joes  
 Lily  
(5 rows)

omm=# 

–查询所有记录，且按c_customer_id升序排列

select * from customer_t order by c_customer_id;
omm=# select * from customer_t order by c_customer_id;
 c_customer_sk | c_customer_id | c_first_name | c_last_name 
---------------+---------------+--------------+-------------
          6885 | 1             | Joes         | Hunter  
          4321 | 2             | Lily         | Carter  
          9527 | 3             | James        | Cook    
          9500 | 4             | Lucy         | Baker   
          3769 | 5             | Grace        | White   
          3769 | 5             | Grace        | White   
          3769 | 5             | Grace        | White   
          3769 | 5             | Grace        | White   
(8 rows)

omm=# 

–查询c_first_name为Lily的记录

select * from customer_t where c_first_name = 'Lily';
omm=# select * from customer_t where c_first_name = 'Lily';
 c_customer_sk | c_customer_id | c_first_name | c_last_name 
---------------+---------------+--------------+-------------
          4321 | 2             | Lily         | Carter  
(1 row)

omm=# 

5.删除表

drop table customer_t;

6、课后作业

1.创建一个表products

omm=# CREATE TABLE products
omm-# (  product_id             integer,   
omm(#   product_name             char(30),    
omm(#   category              char(20)   
omm(# ) ;
CREATE TABLE

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

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

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

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

omm=# select count(*) from products ;
 count 
-------
     4
(1 row)

omm=# 

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

omm=# select category from products order by 1;
(4 rows)

omm=#        category       
----------------------
 Books               
 electrncs           
 toys                
 toys   

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

omm=# select category from products where category='toys';
(2 rows)

omm=#        category       
----------------------
 toys                
 toys                

6.删除表products

omm=# drop table products ;
DROP TABLE
omm=# \d    
                           List of relations
 Schema |    Name    | Type  | Owner |             Storage              
--------+------------+-------+-------+----------------------------------
 public | customer_t | table | omm   | {orientation=row,compression=no}
(1 row)