openGauss每日一练第2天 课程笔记和作业

数据库环境

openGauss:2.0.0 - 数据库实训平台

学习目标

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

学习笔记

update、where、as

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

select c_first_name as c_name from customer_t;

课后作业

1.创建一个表products

omm=# create table products(
omm(# product_id integer,
omm(# product_name char(30),
omm(# category char(20));
CREATE TABLE
omm=# \d
                          List of relations
 Schema |   Name   | Type  | Owner |             Storage              
--------+----------+-------+-------+----------------------------------
 public | products | table | omm   | {orientation=row,compression=no}
(1 row)

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

omm=# insert into products(product_id,product_name,category) values(1502,'olympus camera','electrncs');
INSERT 0 1
omm=# select * from products;
 product_id |          product_name          |       category       
------------+--------------------------------+----------------------
       1502 | olympus camera                 | electrncs           
(1 row)

omm=# insert into products(product_id,product_name,category) values(1601,'lamaze','toys'),(1700,'wait interface','Books'),(1666,'harry potter','toys');
INSERT 0 3
omm=# 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)

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

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

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

omm=# 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，并查看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 from products where category = 'toys';
DELETE 2
omm=# select * from products;


omm=#  product_id |          product_name          |       category       
------------+--------------------------------+----------------------
       1502 | olympus camera                 | electrncs           
        700 | wait interface                 | Books               
(2 rows)

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

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

7.删除表products

omm=# \d          
                          List of relations
 Schema |   Name   | Type  | Owner |             Storage              
--------+----------+-------+-------+----------------------------------
 public | products | table | omm   | {orientation=row,compression=no}
(1 row)
omm=# drop table products;
DROP TABLE
omm=# \d
No relations found.
omm=# 

学习体会

“一天不练，手生脚慢，两天不练，功夫减半，三天不练，成了门外汉”，有时间还是要多写写sql的。

学习资源

• openGauss SQL学习参考资料
• 每日一练：openGauss数据库在线实训课程
• openGauss每日一练 | 21期养成好习惯，提升技术能力！
• 墨天轮Markdown编辑器使用介绍
• 墨天轮数据库在线实训平台V1.0操作手册
• 墨天轮数据社区

欢迎各位同学一起来交流学习心得！