(openGauss每日一练第 1 天)：创建数据库、创建表、插入记录、查询记录和删除表基本使用

1、登录openGauss数据库：

切换到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.

omm=# 

2、创建数据库

create database hr;
CREATE DATABASE
omm=# 

3、切换数据库

#先查看都有哪些数据库输入命令\l
omm=# \l
                              List of databases
   Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges 
-----------+-------+----------+-------------+-------------+-------------------
 hr        | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 omm       | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
           |       |          |             |             | omm=CTc/omm
(5 rows)

 template0 | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/omm           +
           |       |          |             |             | omm=CTc/omm
 template1 | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/omm           +

#切换到刚刚创建的hr数据库\c hr
omm=# \c hr
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "hr" as user "omm".
hr=#

4、创建对象（表）

hr=# CREATE TABLE customer_t
hr-# (  c_customer_sk             integer,   
hr(#   c_customer_id             char(5),    
hr(#   c_first_name              char(6),    
hr(#   c_last_name               char(8) 
hr(# ) ;
CREATE TABLE

5、查询当前数据库有哪些对象（表）

hr=# \dt
                            List of relations
 Schema |    Name     | Type  | Owner |             Storage              
--------+-------------+-------+-------+----------------------------------
 public | countries   | table | omm   | {orientation=row,compression=no}
 public | customer_t  | table | omm   | {orientation=row,compression=no}
 public | departments | table | omm   | {orientation=row,compression=no}
 public | employees   | table | omm   | {orientation=row,compression=no}
 public | job_history | table | omm   | {orientation=row,compression=no}
 public | jobs        | table | omm   | {orientation=row,compression=no}
 public | locations   | table | omm   | {orientation=row,compression=no}
 public | regions     | table | omm   | {orientation=row,compression=no}
(8 rows)

6、向表中插入数据

1、标准写法：列对应数值
hr=# INSERT INTO customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES (3769, 5, 'Grace','White');
INSERT 0 1

2、知道列的顺序可以直接插入列顺序对应的值
hr=# INSERT INTO customer_t VALUES (3769, 5, 'Grace','White');
INSERT 0 1

3、批量插入数据
hr=# INSERT INTO customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES    
hr-# (6885, 1, 'Joes', 'Hunter'),    
hr-# (4321, 2, 'Lily','Carter'),    
hr-# (9527, 3, 'James', 'Cook'),
hr-# (9500, 4, 'Lucy', 'Baker');
INSERT 0 4

7、查询表中的数据

1、数据总量
hr=# select count(*) from customer_t;
 count 
-------
     6
(1 row)

2、列出所有数据
hr=# 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)

hr=#

具体SQL语法请参阅：[OpenGaussSQL语言](openGauss SQL学习参考资料)

8、删除某张表

drop table table_name;

练习：

1、创建一个表products

hr=# CREATE TABLE PRODUCTS ( PRODUCT_ID INTEGER,PRODUCT_NAME CHAR(30),CATEGORY CHAR(20));
CREATE TABLE

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

1、单条插入：
hr=# INSERT INTO PRODUCTS VALUES(1502, 'OLYMPUS CAMERA','ELECTRNCS');
INSERT 0 1

2、多条插入：
hr=# INSERT INTO PRODUCTS  VALUES
hr-# (1601, 'LAMAZE', 'TOYS'),
hr-# (1700, 'WAIT INTERFACE','BOOKS'),
hr-# (1666, 'HARRY POTTER', 'TOYS');
INSERT 0 3

3.查询表中所有记录及记录数

1、查询数据量：
hr=# SELECT COUNT(1) FROM PRODUCTS;
 count 
-------
     4
(1 row)

2、查询所有数据：
hr=# 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.查询表中所有category记录，并将查询结果按降序排序（order by默认升序）

hr=# SELECT CATEGORY FROM PRODUCTS ORDER BY CATEGORY DESC;
       category       
----------------------
 TOYS                
 TOYS                
 ELECTRNCS           
 BOOKS               
(4 rows)

5.查询表中category为toys的记录

hr=# SELECT * FROM PRODUCTS WHERE CATEGORY = 'TOYS';
 product_id |          product_name          |       category       
------------+--------------------------------+----------------------
       1601 | LAMAZE                         | TOYS                
       1666 | HARRY POTTER                   | TOYS                
(2 rows)

6.删除表products

1、查看表（删除前再次确认）：
hr=# \dt products
                          List of relations
 Schema |   Name   | Type  | Owner |             Storage              
--------+----------+-------+-------+----------------------------------
 public | products | table | omm   | {orientation=row,compression=no}
(1 row)

2、删除表products：
hr=# DROP TABLE PRODUCTS;
DROP TABLE

3、再次查看表已删除：
hr-# \dt products
hr-# No matching relations found.