openGauss每日一练第2天 | 学习心得体会

学习目标

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

课程学习

1.创建数据表

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=# \d customer_t
Table “public.customer_t”
Column | Type | Modifiers
---------------±-------------±----------
c_customer_sk | integer |
c_customer_id | character(5) |
c_first_name | character(6) |
c_last_name | character(8) |

omm=#

2.向表中插入记录

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=#

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=# 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*2 < 5000的记录

update customer_t set c_customer_sk = c_customer_sk * 2 where c_customer_sk < 5000;
omm=# update customer_t set c_customer_sk = c_customer_sk * 2 where c_customer_sk < 5000;
UPDATE 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    
          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
omm=# 
复制

–查看数据

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
omm=# 
复制

–查看数据

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=# 
复制

课后作业

1.创建一个表products

CREATE TABLE products
( product_id integer,
product_name char(20),
category char(30)
);

COMMENT ON COLUMN products.product_id IS '产品编号';
COMMENT ON COLUMN products.product_name IS '产品名';
COMMENT ON COLUMN products.category IS '种类';

 omm=# CREATE TABLE products
omm-# ( product_id integer,
omm(#   product_name char(20),
omm(#   category     char(30)
omm(#   );
CREATE TABLE
omm=#   
omm=# COMMENT ON COLUMN products.product_id IS '产品编号';
COMMENT
omm=# COMMENT ON COLUMN products.product_name IS '产品名';
COMMENT
omm=# COMMENT ON COLUMN products.category IS '种类';
COMMENT
omm=# omm=# \d+ products
                              Table "public.products"
    Column    |     Type      | Modifiers | Storage  | Stats target | Description  
--------------+---------------+-----------+----------+--------------+--------------
 product_id   | integer       |           | plain    |              | 产品编号
 product_name | character(20) |           | extended |              | 产品名
 category     | character(30) |           | extended |              | 种类
Has OIDs: no
Options: orientation=row, compression=no

复制

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

--一次插入一条
INSERT INTO products (product_id,product_name,category) VALUES (1502,'olympus camera','electrncs');
INSERT INTO products (product_id,product_name,category) VALUES (1601,'lamaze','toys');
INSERT INTO products (product_id,product_name,category) VALUES (1700,'wait interface','Books');
INSERT INTO products (product_id,product_name,category) VALUES (1666,'harry potter','toys');

--一次插入多条
INSERT INTO products (product_id,product_name,category) VALUES (1502,'olympus camera','electrncs'),(1601,'lamaze','toys'),(1700,'owait interface','Books'),(1666,'harry potter','toys');

domm=# --一次插入一条
omm=# INSERT INTO products (product_id,product_name,category) VALUES (1502,'olympus camera','electrncs');
INSERT 0 1
omm=# INSERT INTO products (product_id,product_name,category) VALUES (1601,'lamaze','toys');
INSERT 0 1
omm=# INSERT INTO products (product_id,product_name,category) VALUES (1700,'wait interface','Books');
INSERT 0 1
omm=# INSERT INTO products (product_id,product_name,category) VALUES (1666,'harry potter','toys');
INSERT 0 1omm=# truncate table products ;
TRUNCATE TABLE
omm=# --一次插入多条
omm=# INSERT INTO products (product_id,product_name,category) VALUES (1502,'olympus camera','electrncs'),(1601,'lamaze','toys'),(1700,'owait interface','Books'),(1666,'harry potter','toys');
INSERT 0 4
omm=# 

复制

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

--获取一条记录

SELECT * FROM products limit 1;

--获取三条记录

SELECT * FROM products limit 3;

--获取所有记录

SELECT * FROM products;

omm=# --获取一条记录
omm=# 
omm=# SELECT * FROM products limit 1;
 product_id |     product_name     |            category            
------------+----------------------+--------------------------------
       1502 | olympus camera       | electrncs                     
(1 row)
omm=# --获取三条记录
omm=# 
omm=# SELECT * FROM products limit 3;
 product_id |     product_name     |            category            
------------+----------------------+--------------------------------
       1502 | olympus camera       | electrncs                     
       1601 | lamaze               | toys                          
       1700 | owait interface      | Books                         
(3 rows)
omm=# --获取所有记录
omm=# 
omm=# SELECT * FROM products;
 product_id |     product_name     |            category            
------------+----------------------+--------------------------------
       1502 | olympus camera       | electrncs                     
       1601 | lamaze               | toys                          
       1700 | owait interface      | Books                         
       1666 | harry potter         | toys                          
(4 rows)
复制

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;

omm=# select * from products;
 product_id |     product_name     |            category            
------------+----------------------+--------------------------------
       1502 | olympus camera       | electrncs                     
       1601 | lamaze               | toys                          
       1700 | owait interface      | Books                         
       1666 | harry potter         | toys                          
(4 rows)

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 | owait interface      | Books                         
        666 | harry potter         | toys                          
(4 rows)
复制

5.删除category为toys的所有记录，并查看products中数据是否删除成功

delete products where category='toys';

select * from products;

omm=# select * from products;
 product_id |     product_name     |            category            
------------+----------------------+--------------------------------
       1502 | olympus camera       | electrncs                     
        601 | lamaze               | toys                          
        700 | owait interface      | Books                         
        666 | harry potter         | toys                          
(4 rows)

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

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

delete from products;

omm=# select * from products;
 product_id |     product_name     |            category            
------------+----------------------+--------------------------------
       1502 | olympus camera       | electrncs                     
        700 | owait interface      | Books                         
(2 rows)

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

7.删除表products

drop table products;

omm=# drop table products;
DROP TABLE
omm=# 
复制

 学习总结

通过本课的学习，我学会了数据记录的查询，更新和删除的基本操作