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

openGauss查询、更新和删除基本使用

登录数据库

su -omm
gsql -r

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

根据表定义编写建表语句

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

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

插入数据

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

一次插入一条方式
insert into products (product_id,product_name,category) values(1502,'olympus camera','electrncs');

一次插入多条方式
insert into products (product_id,product_name,category) values(1601,'lamaze','toys'),(1700,'wait interface','Books'),(1666,'harry potter','toys');

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

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

获取表中一条记录

select * from products limit 1;

获取表中三条记录

select * from products limit 3;

获取表中所有记录

select * from products;

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                          
       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中所有记录是否更新成功

update products set product_id=product_id-1000 where product_id>1600;

select * from products;

omm=# update products set product_id=product_id-1000 where product_id>1600;
UPDATE 3- 可以看到如下三条记录product_id已经变为原值 -1000 ，说明更新成功omm=# select * from products;
        601 | lamaze               | toys                          
        700 | wait interface       | Books                         
        666 | harry potter         | toys                          
(4 rows)

omm=#  product_id |     product_name     |            category            
------------+----------------------+--------------------------------
       1502 | olympus camera       | electrncs              

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

delete from products where category='toys';

select * from products where category='toys';

-如下删除前有category为toys的两条记录omm=# select * from products where category='toys';
 product_id |     product_name     |            category            
------------+----------------------+--------------------------------
        601 | lamaze               | toys                          
        666 | harry potter         | toys                          
(2 rows)

omm=# 
omm=# delete from products where category='toys';
DELETE 2
omm=# -如下删除后有category为toys记录数为0了，说明删除成功
omm=# select * from products where category='toys';
 product_id | product_name | category 
------------+--------------+----------
(0 rows)

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

select * from products;

delete from products;

select * from products;

- 删除前记录数为2omm=# select * from products;
 product_id |     product_name     |            category            
------------+----------------------+--------------------------------
       1502 | olympus camera       | electrncs                     
        700 | wait interface       | Books                         
(2 rows)

omm=# 
omm=# delete from products;
DELETE 2
omm=# - 删除后记录数为0，说明删除成功
omm=# select * from products;
 product_id | product_name | category 
------------+--------------+----------
(0 rows)

7.删除表products

drop table products;

omm=# drop table products;
DROP TABLE