GaussDB SQL及常规操作

本实验主要介绍GaussDB 数据库中的SQL语法，包括数据查询、数据更新、数据定义以及常用函数和操作符。

一 购买 GaussDB 实例 #

步骤1 进入云数据库GaussDB服务，点击左侧的【服务列表】，选择其中【数据库】下的【云数据库GaussDB】。

进入云数据库GaussDB，点击页面右上角的【购买数据库实例】按钮。

步骤2 进入购买页后，根据以下参数信息购买数据库。

主要参数如下，其他请保持默认。

• 计费模式：选择【按需计费】

• 区域：默认的【华北-北京四】

• 实例名称：可以设置为【gauss-hccda】

• 产品类型：基础版

• 数据库版本：选择最新版即可

• 实例类型：集中式

• 部署形态：1主2备

• 性能规格、存储类型、存储空间、磁盘加密保持默认

• 虚拟私有云：选择【vpc-hce】

• 内网安全组：选择【sg-hce】

• 管理员密码：设置符合安全要求的root用户密码

步骤3 确认信息无误后，点击【提交】。

注意：GaussDB实例创建需要20分钟，请耐心等待

二 通过Xfce终端登录ECS服务器，下载 gsql 客户端并解压 #

步骤1 通过桌面上的Xfce终端登录ECS服务器

输入以下命令，并将命令中的IP地址替换为ECS弹性公网IP

ssh root@xxx.xxx.xx.x

注意：IP地址可进入华为云ECS服务查看：

ECS服务器密码如下图所示：

步骤2 下载gsql客户端，并解压

wget https://dbs-download.obs.cn-north-1.myhuaweicloud.com/GaussDB/1642684986086/GaussDB_opengauss_client_tools.zip
unzip GaussDB_opengauss_client_tools.zip
cd /root/GaussDB_opengauss_client_tools/Euler2.5_X86_64
cp GaussDB-Kernel-V500R001C20-EULER-64bit-gsql.tar.gz /opt
cd /opt/
tar -zxvf GaussDB-Kernel-V500R001C20-EULER-64bit-gsql.tar.gz
source gsql_env.sh

步骤3 使用gsql客户端连接数据库，并创建数据库及对应用户

以下命令的连接数据库，xxx.xx.xx.x是GaussDB主节点的IP，yourpassword是设置的数据库密码，请根据实际情况替换。

gsql -h xxx.xx.xx.x -d postgres -p 8000 -U root -W yourpassword -r

注意：GaussDB主节点的IP可通过点击GaussDB实例名称，进入信息页面查看：

将鼠标放在主实例上，会显示主实例所在可用区：

查看该页面下方，主实例可用区的内网地址：

注意：命令中的IP需替换为GaussDB的主节点IP

三 数据类型 #

步骤1 TINYINT

1. 创建具有 TINYINT 类型数据的表

CREATE TABLE int_type_t1
(
IT_COL1 TINYINT
) ;

1. 插入数据

INSERT INTO int_type_t1 VALUES(10);

1. 查看数据

SELECT * FROM int_type_t1;

步骤2 DECIMAL

1. 创建表

CREATE TABLE decimal_type_t1
(
DT_COL1 DECIMAL(10,4)
);

1. 插入数据

INSERT INTO decimal_type_t1 VALUES(123456.122331);

1. 查询表中的数据

SELECT * FROM decimal_type_t1;

步骤3 SMALLSERIAL

1. 创建表

CREATE TABLE smallserial_type_tab(a SMALLSERIAL);

1. 插入数据

INSERT INTO smallserial_type_tab VALUES(default);
INSERT INTO smallserial_type_tab VALUES(default);

1. 查询表中的数据

SELECT * FROM smallserial_type_tab;

步骤4 FLOAT

1. 创建表

CREATE TABLE float_type_t2
(
FT_COL1 INTEGER,
FT_COL2 FLOAT4,
FT_COL3 FLOAT8,
FT_COL4 FLOAT(3),
FT_COL5 BINARY_DOUBLE,
FT_COL6 DECIMAL(10,4),
FT_COL7 INTEGER(6,3)
) ;

1. 插入数据

INSERT INTO float_type_t2 VALUES(10,10.365456,123456.1234,10.3214, 321.321, 123.123654,123.123654);

1. 查询表中的数据

SELECT * FROM float_type_t2 ;

步骤5 字符类型

1. 创建表

CREATE TABLE char_type_t1
(
CT_COL1 CHARACTER(4)
) ;

1. 插入数据

INSERT INTO char_type_t1 VALUES ('ok');

1. 查询表中的数据

SELECT ct_col1, char_length(ct_col1) FROM char_type_t1;

步骤6 日期/时间类型

1. 创建表

CREATE TABLE date_type_tab(coll date);

1. 插入数据

INSERT INTO date_type_tab VALUES (date '12-10-2010');

1. 查询表中的数据

SELECT * FROM date_type_tab;

步骤7 二进制类型

1. 创建表

   1. CREATE TABLE blob_type_t1
      (
      BT_COL1 INTEGER,
      BT_COL2 BLOB,
      BT_COL3 RAW,
      BT_COL4 BYTEA
      ) ;
      

2. 插入数据

   1. INSERT INTO blob_type_t1 VALUES(10,empty_blob(), HEXTORAW('DEADBEEF'), E'\\xDEADBEEF');
      

3. 查询表中的数据

   1. SELECT * FROM blob_type_t1;
      

四 数据库对象 #

步骤1 表

1. 创建表 1.

(1) 创建普通表bank_card

CREATE TABLE bank_card( b_number NCHAR(30) PRIMARY KEY, b_type NCHAR(20),b_c_id INT NOT NULL);

(2) 创建临时表bank_card2

CREATE TEMPORARY TABLE bank_card2(b_number NCHAR(30) PRIMARY KEY, b_type NCHAR(20),b_c_id INT NOT NULL);

1. 修改表属性 1.

在创建表后如果因为业务场景变动，使用ALTER TABLE命令可以更改表的定义

(1) 将bank_card表重命名为bank_card1

ALTER TABLE bank_card RENAME TO bank_card1;

(2) 在bank_card1表中增加列，数据类型为Integer

ALTER TABLE bank_card1 ADD full_masks INTEGER;

(3) 添加约束

ALTER TABLE bank_card1 ADD CONSTRAINT ck_bank_card CHECK(b_c_id>0);
ALTER TABLE bank_card1 ADD CONSTRAINT uk_bank_card UNIQUE(full_masks);

(4) 删除列

ALTER TABLE bank_card1 DROP full_masks;

(5) 向表中插入数据若不满足约束会报错

INSERT INTO bank_card1(b_number, b_type, b_c_id) VALUES ('6222021302020000001','Credit Card', 0);

1. 删除表 1.

当不再需要表数据及表定义时，可以使用DROP TABLE命令删除此表

DROP TABLE IF EXISTS bank_card1;

步骤2 视图

视图是一个虚拟表，是SQL的查询结果，其内容由查询定义。对于来自多张关联表的复杂查询，就不得不使用十分复杂的SQL语句进行查询，造成极差的体验感。使用视图之后，可以极大的简化操作，使用视图不需要关心相应表的结构、关联条件等

1. 创建视图

   1. --创建表
      CREATE TABLE bank_card( b_number NCHAR(30) PRIMARY KEY, b_type NCHAR(20),b_c_id INT NOT NULL);
      --基于表创建视图
      CREATE VIEW v_bank_card as select b_number, b_c_id from bank_card;
      

2. 使用视图进行查询

   1. SELECT * FROM v_bank_card;
      

1. 修改视图

   1. ALTER VIEW v_bank_card RENAME TO v_bank_card_new;
      

2. 删除视图

   1. DROP VIEW v_bank_card_new;
      

步骤3 索引

1. 创建索引

   1. CREATE INDEX idx_b_type ON bank_card (b_type);
      

2. 使用元命令查看新建的索引

   1. \di idx_b_type
      

1. 重命名索引

   1. ALTER INDEX idx_b_type RENAME TO idx_b_type_new;
      

2. 删除索引

   1. DROP INDEX idx_b_type_new;
      

五 函数与操作符 #

介绍数据库常用函数和操作符，例如字符处理函数和操作符，数字操作函数和操作符，时间和日期处理函数和操作符等

步骤1 字符处理函数

GaussDB提供的字符处理函数和操作符主要用于字符串与字符串、字符串与非字符串之间的连接，以及字符串的模式匹配操作

1. instr(string1,string2,int1,int2)：返回在string1中从int1位置开始匹配到第int2次string2的位置，第一个int表示开始匹配起始位置，第二个int表示匹配的次数

   1. SELECT instr( 'abcdabcdabcd', 'bcd', 2, 2 );
      

1. overlay(string placing string FROM int [for int])替换子字符串。FROM int表示从第一个string的第几个字符开始替换，for int表示替换第一个string的字符数目

   1. SELECT overlay('hello' placing 'world' FROM 2 for 3 );
      

1. position(substring in string)指定子字符串的位置。字符串区分大小写

   1. SELECT position('ing' in 'string');
      

1. substring_inner(string [FROM int] [for int]) 截取子字符串，FROM int表示从第几个字符开始截取，for int表示截取几个字节

   1. SELECT substring_inner('adcde', 2,3);
      

1. replace(string text, FROM text, to text)把字符串string里出现地所有子字符串FROM的内容替换成子字符串to的内容

   1. SELECT replace('abcdefabcdef', 'cd', 'XXX');
      

1. substring(string [FROM int] [for int])截取子字符串，FROM int表示从第几个字符开始截取，for int表示截取几个字节

   1. SELECT substring('Thomas' FROM 2 for 3);
      

步骤2 数字操作函数和操作符

1. 数字操作符

2. SELECT 2+3,2*3, @ -5.0, 2.0^3.0, |/ 25.0, 91&15, 17#5,1<<4 AS RESULT;
   

1. abs(exp)，cos(exp)，sin(exp)，acos(exp)，asin(exp)：返回表达式的绝对值，余弦值，正弦值，反余弦值和反正弦值

   1. SELECT abs(-10),cos(0),sin(0),acos(1),asin(0);
      

1. bitand(exp1,exp2)计算两个数字与运算(&)的结果

   1. SELECT bitand(29,15);
      

1. round(number[,decimals]) 将number类数值按照decimals指定的向小数点前后截断

   1. SELECT round(1234.5678,-2),round(1234.5678,2);
      

步骤3 日期和时间处理函数和操作符

1. 时间和日期操作符”+”

   1. SELECT date '2021-5-28' + integer '7' AS RESULT;
      

SELECT date '2021-05-28' + interval '1 hour' AS RESULT;

SELECT date '2021-05-28' + time '03:00' AS RESULT;

SELECT interval '1 day' + interval '1 hour' AS RESULT;

1. 时间和日期操作符”-”

   1. SELECT date '2021-05-01' - date '2021-04-28' AS RESULT;
      

SELECT date '2021-05-01' - integer '7' AS RESULT;

SELECT date '2021-05-28' - interval '1 hour' AS RESULT;

SELECT time '05:00' - interval '2 hours' AS RESULT;

1. age(timestamp, timestamp)将两个参数相减，并以年、月、日作为返回值。若相减值为负，则函数返回亦为负，入参可以都带timezone或都不带timezone

   1. SELECT age(timestamp '2001-04-10', timestamp '1957-06-13');
      

1. current_time当前时间

   1. SELECT current_time;
      

步骤4 类型转换函数

1. to_char(int, text)，to_clob(str)，to_date(exp[,fmt])，to_number(n[,fmt])将指定入参转换为char，clob，date，number类型

   1. SELECT to_char(125,'999'),to_clob('hello111'::CHAR(15)),to_date('05 Dec 2000', 'DD Mon YYYY'), to_number('12,454.8-', '99G999D9S');
      

1. cast(x as y)将x转换成y指定的类型。

   1. SELECT cast('22-oct-1997' as timestamp);
      

1. hextoraw(string) 将一个十六进制构成的字符串转换为RAW

   1. SELECT hextoraw('7D');
      

六 数据的增删改查 #

步骤1 数据准备

1. 创建客户信息表。

   1. --删除表client
      DROP TABLE IF EXISTS client;
      
      --创建表client
      CREATE TABLE client
      (
              c_id INT PRIMARY KEY,
              c_name NVARCHAR2(100) NOT NULL,
              c_mail NCHAR(30) UNIQUE,
              c_id_card NCHAR(20) UNIQUE NOT NULL,
              c_phone NCHAR(20) UNIQUE NOT NULL,
              c_password NCHAR(20) NOT NULL
      );
      

2. 创建银行卡信息表。

   1. --删除表bank_card
      DROP TABLE IF EXISTS bank_card;
      
      
      --创建表bank_card
      CREATE TABLE bank_card
      (
              b_number NCHAR(30) PRIMARY KEY,
              b_type NCHAR(20),
              b_c_id INT NOT NULL
      );
      

3. 创建保险信息表。

   1. --删除表insurance
      DROP TABLE IF EXISTS insurance;
      
      
      --创建表insurance
      CREATE TABLE insurance
      (
              i_name NVARCHAR2(100) NOT NULL,
              i_id INT PRIMARY KEY,
              i_amount INT,
              i_person NCHAR(20),
              i_year INT,
              i_project NVARCHAR2(200)
      );
      

4. 退出gsql会话连接。

   1. \q
      

5.编辑client.sql

vi client.sql

按i进入INSERT模式，client.sql脚本如下：

INSERT INTO client(c_id, c_name, c_mail, c_id_card, c_phone, c_password) VALUES(1,'Zhang Yi','zhangyi@huawei.com', '340211199301010001', '18815650001','gaussdb_001');
INSERT INTO client(c_id, c_name, c_mail, c_id_card, c_phone, c_password) VALUES(2,'Zhang Er','zhanger@huawei.com', '340211199301010002', '18815650002','gaussdb_002');
INSERT INTO client(c_id, c_name, c_mail, c_id_card, c_phone, c_password) VALUES (3,'Zhang San', 'zhangsan@huawei.com', '340211199301010003', '18815650003', 'gaussdb_003');
INSERT INTO client(c_id, c_name, c_mail, c_id_card, c_phone, c_password) VALUES (4,'Zhang Si', 'zhangsi@huawei.com', '340211199301010004', '18815650004', 'gaussdb_004');

保存退出：先按【Esc】再输入【:wq】

1. 编辑bank_card.sql

   1. vi bank_card.sql
      

按i进入INSERT模式，bank_card.sql脚本为：

INSERT INTO bank_card(b_number, b_type, b_c_id) VALUES ('6222021302020000001','Credit Card', 1);
INSERT INTO bank_card(b_number, b_type, b_c_id) VALUES ('622220213020200000002','Credit Card', 3);
INSERT INTO bank_card(b_number, b_type, b_c_id) VALUES ('6222021302020000003','Credit Card',5);
INSERT INTO bank_card(b_number, b_type, b_c_id) VALUES ('6222021302020000004','Credit Card', 7);
INSERT INTO bank_card(b_number, b_type, b_c_id) VALUES ('6222021302020000005','Credit Card', 9);

保存退出：先按【Esc】再输入【:wq】

1. 编辑insurance.sql

   1. vi insurance.sql
      

按i进入INSERT模式，insurance.sql脚本为：

INSERT INTO insurance(i_name, i_id, i_amount, i_person, i_year, i_project) VALUES ('Health Insurance', 1, 2000,'Old People', 30,'Ping An Insurance');
INSERT INTO insurance(i_name, i_id, i_amount, i_person, i_year, i_project) VALUES ('Life Insurance', 2,3000, 'Seniors', 30,'Ping An Insurance');
INSERT INTO insurance(i_name, i_id, i_amount, i_person, i_year, i_project) VALUES ('Accident Insurance', 3,5000, 'All', 30,'Ping An Insurance');
INSERT INTO insurance(i_name, i_id, i_amount, i_person, i_year, i_project) VALUES ('Medical Insurance', 4, 2000,'All', 30,'Ping An Insurance');
INSERT INTO insurance(i_name, i_id, i_amount, i_person, i_year, i_project) VALUES ('Loss of Property Insurance', 5, 1500, 'Middle-aged', 30,'Ping An Insurance');

保存退出：先按【Esc】再输入【:wq】

1. 在SQL界面执行脚本

   1. --启动数据库后执行
      gsql -h xxx.xxx.xxx.xxx -U root -d postgres -p 8000 -r
      

\i client.sql
\i bank_card.sql
\i insurance.sql

步骤2 数据查询

通过DQL语言来演示如何从表中查询数据，包括简单查询、带条件查询、连接查询、排序和限制等。

1. 简单查询 1.

日常查询中，最常用的是通过FROM子句实现的查询。

(1) SELECT后面使用*号查询bank_card表中的所有列。

SELECT * FROM bank_card;

(2) 查看bank_card表中的银行卡号和卡类型

SELECT b_number,b_type FROM bank_card;

(3) 查看客户为1的客户编号，客户名称，客户邮箱和银行卡号信息

SELECT a.c_id,a.c_name, a.c_mail, b.b_number FROM client a, bank_card b where a.c_id= 1 and b.b_c_id = 1;

(4) 别名使用

SELECT b_c_id AS CardID, b_type CardType FROM bank_card;

SELECT a.c_id CID ,a.c_name Name, a.c_mail Email, b.b_number CardNumber FROM client a, bank_card b where a.c_id= 1 and b.b_c_id = 1;

1. 带条件查询 1.

在SELECT语句中，可以通过设置条件以达到更精确的查询，使用比较操作符“>、 <、 >=、 <=、 !=、 <>、 =”来指定查询条件。

(1) 在银行卡表中查询卡类型是信用卡的银行卡信息。

SELECT * FROM bank_card WHERE b_type= 'Credit Card';

(2) 从bank_card中查询客户ID为1且银行卡类型为信用卡的银行卡信息

SELECT * FROM bank_card where b_c_id= 1and b_type='Credit Card';

1. 连接查询 1.

实际应用中所需要的数据，经常会需要查询两个或两个以上的表。这种查询两个或两个以上数据表或视图的查询叫做连接查询。连接查询通常建立在存在相互关系的父子表之间。

(1) 内连接

内连接的关键字为inner join，其中inner可以省略。使用内连接，连接执行顺序必然遵循语句中所写的表的顺序。

使用内连接联合查询表。查询客户ID、银行卡卡号和银行卡类型。使用client和bank_card两个相关的列(c_id)做查询操作。

SELECT c.c_id, b.b_number, b.b_type FROM client c JOIN bank_card b ON (b.b_c_id = c.c_id);

(2) 外连接

使用左外连接查询客户的编号，用户姓名，银行卡号和银行卡类型

SELECT c.c_id,c.c_name, b.b_number,b.b_type FROM client c left join bank_card b on c.c_id=b.b_c_id;

使用右外连接查询表client和表bank_card中的数据

SELECT c.c_id,c.c_name, b.b_number,b.b_type FROM client c right join bank_card b on c.c_id =b.b_c_id;

通过全连接获取表client和表bank_card的完全连接数据

SELECT c.c_id,c.c_name, b.b_number,b.b_type FROM client c FULL JOIN bank_card b ON (b.b_c_id = c.c_id);

1. 数据排序和数据限制 1.

(1) 使用ORDER BY子句对查询语句返回的行根据指定的列进行排序。

按照保额降序查询保险编号大于2的保险名称，保额和适用人群。

SELECT i_name,i_amount,i_person FROM insurance WHERE i_id>2 ORDER BY i_amount DESC;

（2）LIMIT子句允许限制查询返回的行。可以指定偏移量，以及要返回的行数或行百分比。 可以使用此子句实现top-N报表。要获得一致的结果，请指定ORDER BY子句以确保确定性排序顺序。

查询下表 insurance中的保险信息。通过增加LIMIT 2 OFFSET 1限定查询时跳过前1行后，查询总共2行数据。

SELECT i_name, i_id, i_amount, i_person FROM insurance LIMIT 2 OFFSET 1;

步骤3 数据更新

本节将介绍数据操作语言DML（Data Manipulation Language），演示如何通过DML语言来对数据库表中的数据进行更新操作。主要包括：数据插入、数据修改和数据删除。

（1）数据插入

INSERT语句用于在表中插入新的数据，有三种形式：

第一种是值插入，即构造一行记录并插入到表中。

第二种形式是查询插入，它通过SELECT子句返回的结果集构造一行或多行记录插入到表中。

第三种是先插入记录，如果报主键冲突错误则执行UPDATE操作，更新指定字段值。

向表bank_card中值插入数据。

INSERT INTO bank_card(b_number, b_type, b_c_id) VALUES ('6222021302020000030','Savings Card', 30);
INSERT INTO bank_card(b_number, b_type, b_c_id) VALUES ('6222021302020000031','Savings Card', 31);
INSERT INTO bank_card(b_number, b_type, b_c_id) VALUES ('6222021302020000032','Savings Card', 32);

通过子查询向表bank_card1表中插入bank_card表的所有数据

CREATE TABLE bank_card1(b_number NCHAR(30) PRIMARY KEY, b_type NCHAR(20), b_c_id INT NOT NULL);
INSERT INTO bank_card1 SELECT * FROM bank_card;

主键冲突错误，执行UPDATE操作。

INSERT INTO bank_card VALUES ('6222021302020000001', 'Credit Card', 1) ON DUPLICATE KEY UPDATE b_type = 'Savings Card';

（2）数据修改

查询表bank_card记录。

SELECT * FROM bank_card;

更新表bank_card中客户编码为1的b_type为信用卡。

UPDATE bank_card SET bank_card.b_type = 'Credit Card' where b_c_id=1;

（3）数据删除

查询表bank_card记录。

SELECT * FROM bank_card;

删除表bank_card中同时匹配b_type=‘Credit Card’和b_c_id=1的记录。

DELETE FROM bank_card WHERE b_type='Credit Card' AND b_c_id=1;

查询表bank_card记录

SELECT * FROM bank_card;

删除表bank_card的全部数据。

DELETE FROM bank_card;

查询表bank_card记录

SELECT * FROM bank_card;