openGauss

课2作业-openGauss查询、更新和删除基本使用

学习内容

limit

select * from table1 limit n;

解释：获取table1里面有限条(n条)数据，*指获取所有列

update与set

update ---- 更新（某个表）
set ------- 设定（某个列）

update table1 set column1=column1+100,column2=值2 where 条件

对表中满足条件的行（一条数据成为一行）设定某一列值变换，上述语句对表table1种满足条件的行，设定column1上的值加100，column2的值统一设置为值2

delete与truncate

delete----清除数据
truncate----清空表数据，且设置计数器归零

delete from products where 条件
delete from products;	/* 注意delete后面没有*号 */
truncate products;	/* truncate后直接加表名 */

此次作业中清空表格时用delete和truncate都可以

作业内容

作业要求

1.创建一个表products

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

1、2点是课1的内容，不再赘述

CREATE TABLE IF NOT EXISTS products(
product_id INTEGER,
product_name CHAR(20),
category CHAR(30)
);

INSERT INTO products (product_id, product_name, category) VALUES
(1502, 'olympus camera', 'electrncs'),
(1601, 'lamaze', 'toys'),
(1700, 'wait interface', 'Books'),
(1666, 'harry potter', 'toys');

效果：

omm=# CREATE TABLE IF NOT EXISTS products(
omm(# product_id INTEGER,
omm(# product_name CHAR(20),
omm(# category CHAR(30)
omm(# );
CREATE TABLE
omm=# INSERT INTO products (product_id, product_name, category) VALUES
omm-# (1502, 'olympus camera', 'electrncs'),
omm-# (1601, 'lamaze', 'toys'),
omm-# (1700, 'wait interface', 'Books'),
omm-# (1666, 'harry potter', 'toys');
INSERT 0 4
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)

3.获取表中一条记录、三条记录和所有记录
查询所有记录已由上文展示。
下面获取有限条记录：

select * from products limit 1; 	/*获取一条记录*/
select * from products limit 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=# 

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
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)

omm=# 

可以看到原本大于1600的product_id都减去了1000；

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

delete from products where category = 'toys';
select * from products;

效果

omm=# delete from products where category = 'toys';
DELETE 2
omm=# SELECT * FROM products;
 product_id |     product_name     |            category            
------------+----------------------+--------------------------------
       1502 | olympus camera       | electrncs                     
        700 | wait interface       | Books                         
(2 rows)

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

select * from products;
delete from products;		/* delete清空 */
select * from products;
//这里重新注入数据测试
select * from products;
truncate products;		/* truncate清空 */
select * from products;

效果

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

omm=# INSERT INTO products (product_id, product_name, category) VALUES
omm-# (1601, 'lamaze', 'toys'),
omm-# (1502, 'olympus camera', 'electrncs'),
omm-# (1700, 'wait interface', 'Books'),
omm-# (1666, 'harry potter', 'toys');
INSERT 0 4
omm=# truncate products;
TRUNCATE TABLE
omm=# select * from products;
 product_id | product_name | category 
------------+--------------+----------
(0 rows)

7.删除表products

drop table if exists products;