openGauss数据库查询、更新和删除基本使用 练习

数据库连接：

root@modb:~# su - omm
omm@modb:~$ gsql -r

创建数据表

CREATE TABLE customer_t
( c_customer_sk integer,

omm=# CREATE TABLE customer_t
omm-# ( c_customer_sk integer,
omm(# c_customer_id char(5),
omm(# c_first_name char(6),
omm(# c_last_name char(8)
omm(# ) ;
CREATE TABLE

向表中插入记录

omm=# INSERT INTO customer_t VALUES
omm-# (6885, 1, 'Joes', 'Hunter'),
omm-# (4321, 2, 'Lily','Carter'),
omm-# (9527, 3, 'James', 'Cook'),
omm-# (9500, 4, 'Lucy', 'Baker');
INSERT 0 4

查看数据

omm=# select * from customer_t;
c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
6885 | 1 | Joes | Hunter
4321 | 2 | Lily | Carter
9527 | 3 | James | Cook
9500 | 4 | Lucy | Baker
(4 rows)

–获取表中两条记录

omm=# select * from customer_t limit 2;
c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
6885 | 1 | Joes | Hunter
4321 | 2 | Lily | Carter
(2 rows)

–查询表中所有c_first_name的字段，并以别名c_name输出

omm=# select c_first_name as c_name from customer_t;
c_name
--------
Joes
Lily
James
Lucy
(4 rows)

更新数据

omm=# update customer_t set c_customer_id = c_customer_id + 100;
UPDATE 4

–查看数据

 omm=# select * from customer_t;
c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
6885 | 101 | Joes | Hunter
4321 | 102 | Lily | Carter
9527 | 103 | James | Cook
9500 | 104 | Lucy | Baker
(4 rows)

–更新c_customer_sk*2 < 5000的记录

omm=# update customer_t set c_customer_sk = c_customer_sk * 2 where c_customer_sk < 5000;
UPDATE 1

–查看数据

omm=# select * from customer_t;
c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
6885 | 101 | Joes | Hunter
9527 | 103 | James | Cook
9500 | 104 | Lucy | Baker
8642 | 102 | Lily | Carter
(4 rows)

删除数据

omm=# delete from customer_t where c_first_name = 'Lucy';
DELETE 1

–查看数据

omm=# select * from customer_t;
(3 rows)

omm=# c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
6885 | 101 | Joes | Hunter
9527 | 103 | James | Cook
8642 | 102 | Lily | Carter  

–删除所有数据

omm=# delete from customer_t;
DELETE 3

–查看数据

omm=# select * from customer_t;
c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
(0 rows)

删除表

omm=# drop table customer_t;
DROP TABLE

操作截图：

课后作业

1.创建一个表products

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

omm(#);
CREATE TABLE

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

omm=# INSERT INTO products values
omm-# (1502,'olympus camera','electrncs'),
omm-# (1601,'lamaze','toys'),
omm-# (1700,'wait interface','Books'),
omm-# (1666,'harry potter','toys');
INSERT 0 4

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

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 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 products;
DELETE 2
omm=# select * from products;
product_id | product_name | category
------------+--------------+----------
(0 rows)

7.删除表products

omm=# drop table products;
DROP TABLE

操作截图：