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

今天进入第二天的学习了，通过昨天首日的查询和增数据的学习，今天循序渐进，学习更新和删除数据的使用。

学习数据库是一个循序渐进的过程，由易到难，在这21天内，我们要做到一步一个脚印，不要一口吃成大胖子。

以下是我今天的作业内容并打卡：

1.创建一个表products

create table products (product_id  INTEGER, product_name  Char(20), category Char(30));

COMMENT ON COLUMN products.product_id is '产品编号';

comment on column products.product_name is '产品名';

comment on column products.category is '种类';

omm=# create table products (product_id INTEGER, product_name Char(20), category Char(30));
omm=# CREATE TABLE

omm=# COMMENT ON COLUMN products.product_id is '产品编号';
COMMENT
omm=# comment on column products.product_name is '产品名';
COMMENT
omm=# comment on column products.category is '种类';
COMMENT

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

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

insert into products values (1601,'lamaze','toys'),(1700,'wait interface','Books'),(1666,'harry potter','toys');

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
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 * from products;
product_id | product_name | category
------------+----------------------+--------------------------------
1502 | olympus 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';
omm=# DELETE 2

omm=# select * from products;
product_id | product_name | category
------------+----------------------+--------------------------------
1502 | olympus 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
omm=# select * from products;
ERROR: relation "products" does not exist on gaussdb
LINE 1: select * from products;

以上就是我今天第二日的作业打卡情况，请老师阅览。