学习目标
学习openGauss数据库查询、更新和删除基本使用。
第二天作业如下：
连接openGauss
root@modb:~# su - ommomm@modb:~$ gsql -rgsql ((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
字段名 | 数据类型 | 含义 |
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 TABLE2.向表中插入数据，采用一次插入一条和多条记录的方式
omm=#
insert into products values
(1502,'olympus camera','electrncs'),
(1601,'lamaze','toys'),(1700,'wait interface','Books'),(1666,'harry potter','toys');INSERT 0 4
3.获取表中一条记录、三条记录和所有记录
omm=# select * from products limit 1;omm=# product_id | product_name | category
------------+----------------------+--------------------------------
1502 | olympus camera | electrncs
(1 row)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 3omm=# 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 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中所有数据，并查看数据是否删除成功
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
