学习目标
学习openGauss数据库查询、更新和删除基本使用。
课程学习
连接openGauss
#第一次进入等待15秒
#数据库启动中…
su - omm
gsql -r
1.创建数据库
CREATE TABLE customer_t
( c_customer_sk integer,
c_customer_id char(5),
c_first_name char(6),
c_last_name char(8)
) ;
2.向表中插入记录
INSERT INTO customer_t VALUES
(6885, 1, ‘Joes’, ‘Hunter’),
(4321, 2, ‘Lily’,‘Carter’),
(9527, 3, ‘James’, ‘Cook’),
(9500, 4, ‘Lucy’, ‘Baker’);
3.查看数据
–获取表中全部记录
select * from customer_t;
–获取表中两条记录
select * from customer_t limit 2;
–查询表中所有c_first_name的字段，并以别名c_name输出
select c_first_name as c_name from customer_t;
4.更新数据
–更新所有记录
update customer_t set c_customer_id = c_customer_id + 100;
–查看数据
select * from customer_t;
–更新c_customer_sk < 5000的记录
update customer_t set c_customer_sk = c_customer_sk * 2 where c_customer_sk < 5000;
–查看数据
select * from customer_t;
5.删除数据
–删除c_first_name为Lucy的记录
delete from customer_t where c_first_name = ‘Lucy’;
–查看数据
select * from customer_t;
–删除所有数据
delete from customer_t;
–查看数据
select * from customer_t;
6.删除表
drop table customer_t;
课后作业
1.创建一个表products
字段名 数据类型 含义
product_id INTEGER 产品编号
product_name Char(20) 产品名
category Char(30) 种类
CREATE TABLE products ( product_id integer, product_name Char(20), category Char(30) ) ;
2.向表中插入数据，采用一次插入一条和多条记录的方式
product_id product_name category
1502 olympus camera electrncs
1601 lamaze toys
1700 wait interface Books
1666 harry potter toys
INSERT INTO products VALUES (1502, ‘olympus camera’, ‘electrncs’), (1601, ‘lamaze’, ‘toys’), (1700, ‘owait interface’, ‘Books’), (1666, ‘harry potter’, ‘toys’);
3.获取表中一条记录、三条记录和所有记录
select * from products limit 1;
select * from products limit 3;
select * from products;
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;
5.删除category为toys的所有记录，并查看products中数据是否删除成功
delete from products where category = ‘toys’;
select * from products;
6.删除products中所有数据，并查看数据是否删除成功
delete from products;
select * from products;
6.删除表products
drop table products;
