openGauss每日一练第 1 天 | 创建表、插入、查询等基本操作

openGauss创建表、插入、查询基本操作

登录数据库

su -omm
gsql -r

root@modb:~# su - omm
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.

创建表

创建一个表products
字段名 数据类型 含义
product_id INTEGER 产品编号
product_name Char(10) 产品名
category Char(10) 种类
根据表定义编写建表语句
create table products
(product_id integer,
product_name char(30),
category char(10)
);

omm=# create table products
omm-# (product_id integer,
omm(# product_name char(30),
omm(# category char(10)
omm(# );
CREATE TABLE

插入数据

向表中插入数据，采用一次插入一条和多条记录的方式
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’);

omm=# insert into products (product_id,product_name,category) values(1502,'olympus camera','electrncs');
INSERT 0 1

一次插入多条方式

insert into products (product_id,product_name,category) values(1502,‘olympus camera’,‘electrncs’),(1601,‘lamaze’,‘toys’),(1700,‘wait interface’,‘Books’),(1666,‘harry potter’,‘toys’);

omm=# insert into products (product_id,product_name,category) values(1502,'olympus camera','electrncs'),(1601,'lamaze','toys'),(1700,'wait interface','Books'),(1666,'harry potter','toys');
INSERT 0 4

查看数据

查询表中所有记录

select * from products;

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

查询表中记录数

select count(*) from products;

omm=# select count(*) from products;
 count 
-------
     5
(1 row)

按需查询

查询表中所有category记录，并将查询结果按升序排序
select category from products order by category;

omm=# select category from products order by category;
  category  
------------
 Books     
 electrncs 
 electrncs 
 toys      
 toys      
(5 rows)

特地条件查询

查询表中category为toys的记录
select * from products where category=‘toys’;

omm=# select * from products where category='toys';
omm=#  product_id |          product_name          |  category  
------------+--------------------------------+------------
       1601 | lamaze                         | toys      
       1666 | harry potter                   | toys      
(2 rows)

对表进行删除

删除表products
drop table products

omm=# drop table products;
DROP TABLE   #这个提示就是删除表，也不加个successful
-- 再次查询提示表products不存在了
omm=# select count(*) from products;
ERROR:  relation "products" does not exist on gaussdb
LINE 1: select count(*) from products;
                             ^