1、登录openGauss数据库：
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.
omm=#
2、创建数据库，创建表
1、创建数据库
omm=# CREATE DATABASE HR;
CREATE DATABASE
2、切换数据库
omm=# \c hr;
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "hr" as user "omm".
hr=#
3、创建表
hr=# CREATE TABLE customer_t
hr-# ( c_customer_sk integer,
hr(# c_customer_id char(5),
hr(# c_first_name char(6),
hr(# c_last_name char(8)
hr(# ) ;
CREATE TABLE
3、向表中插入数据
hr=# INSERT INTO customer_t VALUES
hr-# (6885, 1, 'Joes', 'Hunter'),
hr-# (4321, 2, 'Lily','Carter'),
hr-# (9527, 3, 'James', 'Cook'),
hr-# (9500, 4, 'Lucy', 'Baker');
INSERT 0 4
4、查询表数据
1、查询表中所有数据
hr=# 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)
2、获取表中两条记录
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)
5、查询表中所有c_first_name的字段，并以别名c_name输出：(列别名)
hr=# select c_first_name as c_name from customer_t;
c_name
--------
Joes
Lily
James
Lucy
(4 rows)
6、更新记录：(更新数据)
1、更新所有记录c_customer_id +100
hr=# update customer_t set c_customer_id = c_customer_id + 100;
UPDATE 4
2、更新c_customer_sk < 5000的记录
hr=# update customer_t set c_customer_sk = c_customer_sk * 2 where c_customer_sk < 5000;
UPDATE 1
3、查询更新后的数据
hr=# 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)
7、删除表中数据
1、删除名为"Lucy"的记录
hr=# delete from customer_t where c_first_name = 'Lucy';
DELETE 1
2、查询验证
hr=# select * from customer_t;
c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
6885 | 101 | Joes | Hunter
9527 | 103 | James | Cook
8642 | 102 | Lily | Carter
(3 rows)
3、删除customer_t表中所有数据
hr=# delete from customer_t;
DELETE 3
4、查询customer_t表中数据（都已经删除）
hr=# select * from customer_t;
c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
(0 rows)
8、删除表
1、删除customer_t表
hr=# drop table customer_t;
DROP TABLE
2、查询当前数据库中customer_t表（已删除）
hr=# \dt customer_t;
No matching relations found.
hr=#
练习：
1.创建一个表products
hr=# CREATE TABLE PRODUCTS (PRODUCT_ID INTEGER,PRODUCT_NAME CHAR(20),CATEGORY CHAR(30));
CREATE TABLE
hr=# \dt
List of relations
Schema | Name | Type | Owner | Storage
--------+----------+-------+-------+----------------------------------
public | products | table | omm | {orientation=row,compression=no}
(1 row)
2.向表中插入数据，采用一次插入一条和多条记录的方式
hr=# insert into products(product_id,product_name,category) values(1502,'olympus camera','electrncs');
INSERT 0 1
hr=# insert into products(product_id,product_name,category) values(1601,'lamaze','toys'),(1700,'wait interface','Books'),(1666,'harry potter','toys');
INSERT 0 3
hr=#
3.获取表中一条记录、三条记录和所有记录
1、查询表中一条数据
hr=# select * from products limit 1;
product_id | product_name | category
------------+----------------------+--------------------------------
1502 | olympus camera | electrncs
(1 row)
2、查询表中三条数据
hr=# select * from products limit 3;
product_id | product_name | category
------------+----------------------+--------------------------------
1502 | olympus camera | electrncs
1601 | lamaze | toys
1700 | wait interface | Books
(3 rows)
3、查询表中所有数据
hr=# 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
1、product_id > 1600的记录的product_id更新为product_id – 1000
hr=# update products set product_id=product_id - 1000 where product_id > 1600;
UPDATE 3
2、查看products更新后的数据
hr=# 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的所有记录
1、删除category = 'toys'
hr=# delete from products where category = 'toys';
DELETE 2
2、查询验证
hr=# select * from products;
product_id | product_name | category
------------+----------------------+--------------------------------
1502 | olympus camera | electrncs
700 | wait interface | Books
(2 rows)
6.删除products中所有数据，并查看数据是否删除成功
1、删除products表中所有数据
hr=# delete from products;
DELETE 2
2、查询验证
hr=# select * from products;
hr=# product_id | product_name | category
------------+--------------+----------
(0 rows)
7、删除表
1、删除products表
hr=# drop table products;
DROP TABLE
2、验证表是否存在
hr=# \dt products;
No matching relations found.
hr=#
最后修改时间：2021-12-02 18:46:33
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
