openGauss每日一练第 2 天 |表数据增删查改

学习openGauss的第二天。

主要内容是连接数据库，创建表，对表中数据进行增删查改操作

登录到到omm用户

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.

 

1、创建表products

omm=# create table products (product_id integer,product_name char(20),category char(30));

CREATE TABLE

 

2、对表中插入数据，单行插入方式以及多行插入方式

omm=# insert into products values(1502,'olympus camera','electrncs') ;

INSERT 0 1

omm=# insert into products values(1601,'lamaze','toys'),(1700,'wait interface','books'),(1666,'harry potter','toys');

INSERT 0 3

 

3、查询表中一条记录，三条记录和所有记录

omm=# select * from products limit 1 ;

 product_id |     product_name     |            category   

       

------------+----------------------+--------------------------------

       1502 | olympus camera       | electrncs             

      

(1 row)

 

omm=# select * from products limit 3 ;

 product_id |     product_name     |            category   

       

------------+----------------------+--------------------------------

       1502 | olympus camera       | electrncs             

      

       1601 | lamaze               | toys                  

      

omm=#        1700 | wait interface       | books                 

      

(3 rows)

 

 

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.将满足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 product_id from products ;

        601

        700

        666

(4 rows)

 

omm=#  product_id

------------

       1502

 

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

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

DELETE 2

omm=#

omm=# select * from products where category='toys';

 product_id | product_name | category

------------+--------------+----------

(0 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