openGauss每日一练第 2天|openGauss查询、更新和删除的基本使用

本次课讲的内容是openGauss数据库查询、更新和删除的基本使用。

一、学习内容

1、课程学习前还是先进入实训环境，输入omm口令和密码连接openGauss。

root@modb:~# 
root@modb:~# su - omm
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.

2、连接openGauss后，建立数据库

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

3、向表插入记录

INSERT INTO customer_t VALUES    
omm-# omm-# (6885, 1, 'Joes', 'Hunter'),    
(4321, 2, 'Lily','Carter'),    
omm-# (9527, 3, 'James', 'Cook'),
omm-# (9500, 4, 'Lucy', 'Baker');
INSERT 0 4

4、查看数据

（1）、查看表中记录数

omm=# select * from customer_t;
 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   
(4 rows)

（2）、获取表中两条记录

omm=# select * from customer_t limit 2;
 c_customer_sk | c_customer_id | c_first_name | c_last_name 
---------------+---------------+--------------+-------------
          6885 | 1             | Joes         | Hunter  
          4321 | 2             | Lily         | Carter  
(2 rows)

（3）、查询表中所有c_first_name的字段，并以别名c_name输出

omm=# select c_first_name as c_name from customer_t;
 c_name 
--------
 Joes  
 Lily  
 James 
 Lucy  
(4 rows)

5、更新数据

（1）、更新所有记录

omm=# update customer_t set c_customer_id = c_customer_id + 100;
UPDATE 4
omm=# select * from customer_t;
 c_customer_sk | c_customer_id | c_first_name | c_last_name 
---------------+---------------+--------------+-------------
          6885 | 101           | Joes         | Hunter  
          4321 | 102           | Lily         | Carter  
          9527 | 103           | James        | Cook    
          9500 | 104           | Lucy         | Baker   
(4 rows)

（2）、更新c_customer_sk < 5000的记录

omm=# update customer_t set c_customer_sk = c_customer_sk * 2 where c_customer_sk < 5000;
UPDATE 1
omm=# select * from customer_t;
 c_customer_sk | c_customer_id | c_first_name | c_last_name 
---------------+---------------+--------------+-------------
          6885 | 101           | Joes         | Hunter  
          9527 | 103           | James        | Cook    
          9500 | 104           | Lucy         | Baker   
          8642 | 102           | Lily         | Carter  
(4 rows)

6、删除数据

（1）、删除c_first_name为Lucy的记录

omm=# delete from customer_t where c_first_name = 'Lucy';
DELETE 1
omm=# select * from customer_t;
 c_customer_sk | c_customer_id | c_first_name | c_last_name 
---------------+---------------+--------------+-------------
          6885 | 101           | Joes         | Hunter  
          9527 | 103           | James        | Cook    
          8642 | 102           | Lily         | Carter  
(3 rows)

（2）、删除所有数据

omm=# delete from customer_t;
DELETE 3
omm=# select * from customer_t;
 c_customer_sk | c_customer_id | c_first_name | c_last_name 
---------------+---------------+--------------+-------------
(0 rows)

7、删除表

omm=# drop table customer_t;
DROP TABLE

二、课后作业

1、创建一个表products

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

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

omm=# INSERT INTO products VALUES (1502,'olmpus camera','electrncs');
INSERT 0 1

omm=# INSERT INTO products VALUES (1601,'lamaze','toys'),
omm-# (1700,'wait interface','Books'),(1666,'harry potter','toys');
INSERT 0 3

3、获取表中一条记录、三条记录和所有记录

omm=# select * from products limit 1;
 product_id |     product_name     |            category            
------------+----------------------+--------------------------------
       1502 | olmpus camera        | electrncs                     
(1 row)

omm=# select * from products limit 3;
 product_id |     product_name     |            category            
------------+----------------------+--------------------------------
       1502 | olmpus camera        | electrncs                     
       1601 | lamaze               | toys                          
       1700 | wait interface       | Books                         
(3 rows)

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

4、将满足product_id > 1600的记录的product_id更新为product_id – 1000，并查看products中所有记录是否更新成功

omm=# update products set product_id=product_id-1000 where product_id>1600;
UPDATE 3

omm=# select * from products;
 product_id |     product_name     |            category            
------------+----------------------+--------------------------------
       1502 | olmpus camera        | electrncs                     
        601 | lamaze               | toys                          
        700 | wait interface       | Books                         
        666 | harry potter         | toys                          
(4 rows)

5、删除category为toys的所有记录，并查看products中数据是否删除成功

omm=# delete from products where category='toys';
DELETE 2

omm=# select * from products;
 product_id |     product_name     |            category            
------------+----------------------+--------------------------------
       1502 | olmpus camera        | electrncs                     
        700 | wait interface       | Books                         
(2 rows)

6、删除products中所有数据，并查看数据是否删除成功

omm=# delete from products;
DELETE 2

omm=# select * from products;
 product_id | product_name | category 
------------+--------------+----------
(0 rows)

7、删除表products

omm=# drop table products;
DROP TABLE