学习openGauss数据库查询、更新和删除基本使用。
1.连接数据库
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.创建一个表products
omm=# create table products (product_id int,product_name char(20),category char(30));
CREATE TABLE
3.向表中插入数据，采用一次插入一条和多条记录的方式
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
4.获取表中一条记录、三条记录和所有记录
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)
5.将满足product_id > 1600的记录的product_id更新为product_id – 1000，并查看products中所有记录是否更新成功
omm=# update products set product_id=product_id-1000 where product_id>1600;
omm=# 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)
6.删除category为toys的所有记录，并查看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)
7.删除products中所有数据，并查看数据是否删除成功
omm=# delete from products;
DELETE 2
8.删除表products
omm=# drop table products;
DROP TABLE
