openGauss每日一练第 1天|表的基本使用

登录数据库

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=# 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=# INSERT INTO customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES (3769, 5, ‘Grace’,‘White’);  
INSERT 0 1  

omm=# INSERT INTO customer_t VALUES (3769, 5, ‘Grace’,‘White’);  
INSERT 0 1  

omm=# select * from customer_t;  
c_customer_sk | c_customer_id | c_first_name | c_last_name  
---------------±--------------±-------------±------------  
3769 | 5 | Grace | White  
3769 | 5 | Grace | White  
(2 rows)

注：插入后自动提交
复制

插入多行数据

omm=# INSERT INTO customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES (6885, 1, ‘Joes’, ‘Hunter’),(4321, 2, ‘Lily’,‘Carter’),(9527, 3, ‘James’, ‘Cook’),(9500, 4, ‘Lucy’, ‘Baker’);  
INSERT 0 4  
omm=# select * from customer_t;  
c_customer_sk | c_customer_id | c_first_name | c_last_name  
--------------±--------------±-------------±------------  
3769 | 5 | Grace | White  
3769 | 5 | Grace | White  
6885 | 1 | Joes | Hunter  
4321 | 2 | Lily | Carter  
9527 | 3 | James | Cook  
9500 | 4 | Lucy | Baker  
(6 rows)
复制

基本查询

select count(*) from customer_t;  
select c_first_name from customer_t;  
select distinct(c_first_name) from customer_t;  
select * from customer_t order by c_customer_ id;  
select * from customer_t where c_first_name=‘Lily’;  

注：去重不加（）也能查询
select distinct c_first_name from customer_t;  
复制

openGauss每日一练第 1 天作业

创建表

omm=# CREATE TABLE products  
omm-# ( product_id integer,  
omm(# product_name char(10),  
omm(# category char(10)  
omm(# ) ;
复制

查看表定义

omm=# \d products  
Table “public.products”  
Column | Type | Modifiers  
--------------±--------------±----------  
product_id | integer |  
product_name | character(10) |  
category | character(10) |
复制

插入数据

omm=# INSERT INTO products (product_id, product_name, category) VALUES (1502, ‘olympus camera’,‘electrncs’);  
ERROR: value too long for type character(10)  
CONTEXT: referenced column: product_name  

注：长度不够，修改字段长度
omm=# alter table products MODIFY product_name char(20);  

omm=# \d products  
Table “public.products”  
Column | Type | Modifiers  
--------------±--------------±----------  
product_id | integer |  
product_name | character(20) |  
category | character(10) |

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 count(*) from products;  
count
---------------
 4
    
omm=# select * from products;  
product_id | product_name | category  
------------±---------------------±-----------  
1502 | olympus camera | electrncs  
1601 | lamaze | toys  
1700 | wait interface | Books  
1666 | harry potter | toys  
omm=# (4 rows)
复制

查询表中所有category记录，并将查询结果按升序排序

omm=# select * from products order by category;  
product_id | product_name | category  
------------±---------------------±-----------  
1700 | wait interface | Books  
1502 | olympus camera | electrncs  
1601 | lamaze | toys  
1666 | harry potter | toys  
(4 rows)
复制

查询表中category为toys的记录

omm=# select * from products where category=‘toys’;  
product_id | product_name | category  
------------±---------------------±-----------  
1601 | lamaze | toys  
1666 | harry potter | toys  
(2 rows)
复制

删除表products

omm=# drop table products;  
DROP TABLE
复制

第一天简单的了解了一下表相关操作，基本操作差不多跟别的数据库使用相似，其中的不同之处待以后慢慢学习使用发掘。