暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / openGauss每日一练第2天|《openGauss查询、更新和删除基本使用》学习心得体会和课后练习

openGauss每日一练第2天|《openGauss查询、更新和删除基本使用》学习心得体会和课后练习

原创 闫伟 2021-12-02
788

课程学习

连接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) 
) ;

omm@modb:~$ gsql -r
failed to connect Unknown:5432.
omm@modb:~$ gsql
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=# omm(# omm(# CREATE TABLE customer_t
(  c_customer_sk             integer,   
  c_customer_id             char(5),    
  c_first_name              char(6),    
  c_last_name               char(8) 
) ;omm-# omm(# omm(# 
CREATE TABLE

2.向表中插入记录

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


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

3.查看数据

–获取表中全部记录

select * from customer_t;

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)

–获取表中两条记录

select * from customer_t limit 2;

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输出

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

4.更新数据

–更新所有记录

update customer_t set c_customer_id = c_customer_id + 100;
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=# update customer_t set c_customer_id = c_customer_id + 100;
UPDATE 4

–查看数据

select * from customer_t;
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 < 5000的记录

update customer_t set c_customer_sk = c_customer_sk * 2 where c_customer_sk < 5000;

–查看数据

select * from customer_t;
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)

5.删除数据

–删除c_first_name为Lucy的记录

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

–查看数据

select * from customer_t;
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    
          8642 | 102           | Lily         | Carter  
(3 rows)

–删除所有数据

delete from customer_t;
omm=# delete from customer_t;
DELETE 3

–查看数据

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

6.删除表

drop table customer_t;
omm=# drop table customer_t;
DROP TABLE
omm=# \d
No relations found.

课后作业

1.创建一个表products

字段名 数据类型 含义
product_id INTEGER 产品编号
product_name Char(20) 产品名
category Char(30) 种类 
omm=# omm=# 
CREATE TABLE products
(  product_id             integer,   
  product_name             char(30),    
  category              char(20)   
) ;omm-# omm(# omm(# omm(# 
CREATE TABLE

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

product_id product_name category
1502 olympus camera electrncs
1601 lamaze toys
1700 wait interface Books
1666 harry potter toys 
INSERT into products (product_id, product_name, category) VALUES    
(1502, 'olympus camera', 'electrncs'),
(1601, 'lamaze','toys'),    
(1700, 'wait interface', 'Books'),
(1666, 'harry potter', 'toys');

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

select * from products limit 1;

select * from products limit 3;

select * from products;


select * from products; product_id |          product_name          |       category       
------------+--------------------------------+----------------------
       1502 | olympus camera                 | electrncs           
(1 row)

omm=# omm=#  product_id |          product_name          |       category       
------------+--------------------------------+----------------------
       1502 | olympus camera                 | electrncs           
       1601 | lamaze                         | toys                
       1700 | wait interface                 | Books               
(3 rows)

omm=# omm=# 
 product_id |          product_name          |       category       
------------+--------------------------------+----------------------
       1502 | olympus camera                 | electrncs           
       1601 | lamaze                         | toys                
       1700 | wait interface                 | Books               
       1666 | harry potter                   | toys

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中数据是否删除成功

delete from products where category='toys';omm=# 
DELETE 2
omm=# 
omm=# 
omm=# 
select * from products;omm=# 
omm=#  product_id |          product_name          |       category       
------------+--------------------------------+----------------------
       1502 | olympus camera                 | electrncs           
        700 | wait interface                 | Books               
(2 rows)

6.删除products中所有数据，并查看数据是否删除成功

delete from products;
select * from products;omm=# DELETE 2
omm=# 
 product_id | product_name | category 
------------+--------------+----------
(0 rows)

6.删除表products

drop table products;
opengauss
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

Murkey的PostgreSQL学习之旅（共40篇）
个人PG的学习总结

评论