GBase 8a 学习笔记 015 —— GBase 8a MPP Cluster SQL综合演示
安全管理
使用数据库超户root连接数据库后，创建数据库，创建新用户组和用户，并给新用户组和用户分配相关权限
-- 连接登录集群数据库（默认管理员为root，密码为空）
[gbase@192 ~]$ gccli -uroot -p
Enter password:
GBase client 9.5.2.39.126761. Copyright (c) 2004-2023, GBase. All Rights Reserved.
-- 给root用户设置密码，保障安全
gbase> set password for root =password('20230304');
Query OK, 0 rows affected (Elapsed: 00:00:00.25)
-- 退出
gbase> exit
Bye
-- 使用原密码登录（空）
[gbase@192 ~]$ gccli -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
-- 使用设置的新密码登录（20230304）
[gbase@192 ~]$ gccli -uroot -p
Enter password:
GBase client 9.5.2.39.126761. Copyright (c) 2004-2023, GBase. All Rights Reserved.
gbase>
gbase> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| performance_schema |
| gbase |
| gctmpdb |
| gclusterdb |
| test |
+--------------------+
6 rows in set (Elapsed: 00:00:00.00)
-- 创建数据库
-- create database if not exists 数据库名;
-- 检查数据库是否创建成功
gbase> create database if not exists mydb;
Query OK, 1 row affected (Elapsed: 00:00:00.02)
gbase> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| performance_schema |
| gbase |
| gctmpdb |
| gclusterdb |
| mydb |
| test |
+--------------------+
7 rows in set (Elapsed: 00:00:00.00)
-- use 数据库名；
gbase> use mydb;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase>
-- 创建用户组
gbase> create role role1;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)
-- 给用户组赋予数据库级权限,首先选择要赋权限的数据库
-- grant all on 数据库名.* to 用户组名;
gbase> grant all on mydb.* to role1;
Query OK, 0 rows affected (Elapsed: 00:00:00.02)
-- 创建用户
-- create user 用户名 identified by ‘密码’;
gbase> create user user1 identified by '123456';
Query OK, 0 rows affected (Elapsed: 00:00:00.02)
-- 给用户授于用户组权限
-- grant 用户组名 to 用户;
gbase> grant role1 to user1;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)
-- 显示该用户和用户组的权限
gbase> show grants for user1;
+--------------------------------------------------------------------------------------------------------+
| Grants for user1@% |
+--------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.*.* TO 'user1'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT "role1" TO "user1" |
+--------------------------------------------------------------------------------------------------------+
2 rows in set (Elapsed: 00:00:00.00)
gbase> show grants for role1;
+--------------------------------------------------------------------------------------------------------+
| Grants for role1@% |
+--------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.*.* TO 'role1'@'%' IDENTIFIED BY PASSWORD '*273A88CB37BFAB343D2A9DB9FAC9E15FE959B38A' |
| GRANT ALL PRIVILEGES ON "vc00001"."mydb".* TO 'role1'@'%' |
+--------------------------------------------------------------------------------------------------------+
2 rows in set (Elapsed: 00:00:00.00)
-- 在系统库中查询用户和用户组对应关系
gbase> select user from gbase.user;
+----------------------------------------------------------------------------------------------------------------------------------+
| user |
+----------------------------------------------------------------------------------------------------------------------------------+
| gbase |
| role1 |
| root |
| user1 |
+----------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (Elapsed: 00:00:00.00)
gbase> select * from gbase.role_edges;
+--------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------+-------------------+
| FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION |
+--------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------+-------------------+
| % | role1 | % | user1 | N |
+--------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------+-------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> select * from gbase.role_edges \G
*************************** 1. row ***************************
FROM_HOST: %
FROM_USER: role1
TO_HOST: %
TO_USER: user1
WITH_ADMIN_OPTION: N
1 row in set (Elapsed: 00:00:00.00)
-- 使用新用户登录8a系统（密码：123456）
[gbase@192 ~]$ gccli -uuser1 -p -D mydb
Enter password:
GBase client 9.5.2.39.126761. Copyright (c) 2004-2023, GBase. All Rights Reserved.
-- 进入有权限数据库
gbase> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| performance_schema |
| mydb |
+--------------------+
3 rows in set (Elapsed: 00:00:00.00)
gbase> use mydb;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
-- 查看当前用户和用户的权限信息
gbase> show grants for current_user();
+--------------------------------------------------------------------------------------------------------+
| Grants for user1@% |
+--------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.*.* TO 'user1'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT "role1" TO "user1" |
+--------------------------------------------------------------------------------------------------------+
2 rows in set (Elapsed: 00:00:00.00)
数据库对象基本操作
创建hash分布表、复制表、随机分布表、视图、索引等
使用user1账户进行操作
-- 创建hash分布表，在库中创建hash分布表
gbase> create table student(
-> sno varchar(20) DEFAULT NULL COMMENT '学号',
-> sname varchar(20) DEFAULT NULL COMMENT '姓名',
-> ssex varchar(3) DEFAULT NULL COMMENT '性别（男、女）',
-> sage int(4) DEFAULT NULL COMMENT '年龄',
-> sdept varchar(20) DEFAULT NULL COMMENT '系')
-> distributed by ('sno');
Query OK, 0 rows affected (Elapsed: 00:00:00.23)
-- 查看列名
gbase> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno | varchar(20) | YES | | NULL | |
| sname | varchar(20) | YES | | NULL | |
| ssex | varchar(3) | YES | | NULL | |
| sage | int(4) | YES | | NULL | |
| sdept | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
5 rows in set (Elapsed: 00:00:00.01)
gbase> show create table student;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE "student" (
"sno" varchar(20) DEFAULT NULL COMMENT '学号',
"sname" varchar(20) DEFAULT NULL COMMENT '姓名',
"ssex" varchar(3) DEFAULT NULL COMMENT '性别（男、女）',
"sage" int(4) DEFAULT NULL COMMENT '年龄',
"sdept" varchar(20) DEFAULT NULL COMMENT '系'
) ENGINE=EXPRESS DISTRIBUTED BY('sno') DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
-- 创建复制表，在库中创建复制表
gbase> create table course(cno int(4) DEFAULT NULL COMMENT '课程号',
-> cname varchar(20) DEFAULT NULL COMMENT '课程名',
-> cpno int(4) DEFAULT NULL COMMENT '选修系',
-> Ccredit int(4) DEFAULT NULL COMMENT '学分')
-> replicated;
Query OK, 0 rows affected (Elapsed: 00:00:00.11)
-- 创建随机分布表
gbase> drop table if exists sc;
Query OK, 0 rows affected, 6 warnings (Elapsed: 00:00:00.28)
gbase> create table sc(
-> sno varchar(20) DEFAULT NULL COMMENT '学号',
-> cno int(4) DEFAULT NULL COMMENT '课程号',
-> grade int(4) DEFAULT NULL COMMENT '成绩');
Query OK, 0 rows affected (Elapsed: 00:00:00.08)
-- 查询建表语句
gbase> show create table course;
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| course | CREATE TABLE "course" (
"cno" int(4) DEFAULT NULL COMMENT '课程号',
"cname" varchar(20) DEFAULT NULL COMMENT '课程名',
"cpno" int(4) DEFAULT NULL COMMENT '选修系',
"Ccredit" int(4) DEFAULT NULL COMMENT '学分'
) ENGINE=EXPRESS REPLICATED DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
-- 插入数据
gbase> insert into student values
-> ('200215121','李勇','男',20,'CS'),('200215122','刘晨','女',19,'CS'),('200215123','王敏','女',18,'MA'), ('200215125','张立','男',19,'IS');
Query OK, 4 rows affected (Elapsed: 00:00:00.27)
Records: 4 Duplicates: 0 Warnings: 0
gbase> insert into course values
-> (1,'数据库',5,4),(2,'数学',null,2),(3,'信息系统',1,4),(4,'操作系统',6,3),(5,'数据结构',7,4),(6,'数据处理',null,2),(7,'PASCAL语言',6,4);
Query OK, 7 rows affected (Elapsed: 00:00:00.08)
Records: 7 Duplicates: 0 Warnings: 0
gbase> insert into sc values
-> ('200215121',1,92),('200215121',2,85),('200215121',3,88),('200215122',2,90),('200215122',3,80),('200215123',3,56),
-> ('200215123',5,80),('200215123',1,66), ('200215126',3,58),('200215126',6,54);
Query OK, 10 rows affected (Elapsed: 00:00:00.04)
Records: 10 Duplicates: 0 Warnings: 0
-- 查询数据
gbase> select count(*) from student;
+----------+
| count(*) |
+----------+
| 4 |
+----------+
1 row in set (Elapsed: 00:00:00.21)
gbase> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno | varchar(20) | YES | | NULL | |
| sname | varchar(20) | YES | | NULL | |
| ssex | varchar(3) | YES | | NULL | |
| sage | int(4) | YES | | NULL | |
| sdept | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
5 rows in set (Elapsed: 00:00:00.00)
gbase> select * from student;
+-----------+--------+------+------+-------+
| sno | sname | ssex | sage | sdept |
+-----------+--------+------+------+-------+
| 200215122 | 刘晨 | 女 | 19 | CS |
| 200215121 | 李勇 | 男 | 20 | CS |
| 200215125 | 张立 | 男 | 19 | IS |
| 200215123 | 王敏 | 女 | 18 | MA |
+-----------+--------+------+------+-------+
4 rows in set (Elapsed: 00:00:00.01)
gbase> select * from course;
+------+--------------+------+---------+
| cno | cname | cpno | Ccredit |
+------+--------------+------+---------+
| 1 | 数据库 | 5 | 4 |
| 2 | 数学 | NULL | 2 |
| 3 | 信息系统 | 1 | 4 |
| 4 | 操作系统 | 6 | 3 |
| 5 | 数据结构 | 7 | 4 |
| 6 | 数据处理 | NULL | 2 |
| 7 | PASCAL语言 | 6 | 4 |
+------+--------------+------+---------+
7 rows in set (Elapsed: 00:00:00.01)
gbase> select * from sc;
+-----------+------+-------+
| sno | cno | grade |
+-----------+------+-------+
| 200215121 | 1 | 92 |
| 200215121 | 2 | 85 |
| 200215121 | 3 | 88 |
| 200215122 | 2 | 90 |
| 200215122 | 3 | 80 |
| 200215123 | 3 | 56 |
| 200215123 | 5 | 80 |
| 200215123 | 1 | 66 |
| 200215126 | 3 | 58 |
| 200215126 | 6 | 54 |
+-----------+------+-------+
10 rows in set (Elapsed: 00:00:00.01)
-- 新建一个视图，显示学生成绩表，字段包括学生姓名、课程、成绩
gbase> CREATE OR REPLACE VIEW v_sc
-> AS SELECT sname, cno, grade FROM student s,sc where s.sno=sc.sno;
Query OK, 0 rows affected (Elapsed: 00:00:00.10)
-- 显示表和视图名字
gbase> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| course |
| sc |
| student |
| v_sc |
+----------------+
4 rows in set (Elapsed: 00:00:00.00)
gbase> show create view v_sc;
+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v_sc | CREATE ALGORITHM=TEMPTABLE DEFINER="user1"@"%" SQL SECURITY DEFINER VIEW "v_sc" AS select "s"."sname" AS "sname","sc"."cno" AS "cno","sc"."grade" AS "grade" from ("student" "s" join "sc") where ("s"."sno" = "sc"."sno") | utf8 | utf8_general_ci |
+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (Elapsed: 00:00:00.00)
-- 修改表sc，在课程号后面增加一列课程名称
gbase> alter table sc add cname varchar(20) DEFAULT NULL after cno;
Query OK, 10 rows affected (Elapsed: 00:00:00.10)
Records: 10 Duplicates: 10 Warnings: 0
gbase> desc sc;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno | varchar(20) | YES | | NULL | |
| cno | int(4) | YES | | NULL | |
| cname | varchar(20) | YES | | NULL | |
| grade | int(4) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (Elapsed: 00:00:00.00)
gbase> show create table sc;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sc | CREATE TABLE "sc" (
"sno" varchar(20) DEFAULT NULL COMMENT '学号',
"cno" int(4) DEFAULT NULL COMMENT '课程号',
"cname" varchar(20) DEFAULT NULL,
"grade" int(4) DEFAULT NULL COMMENT '成绩'
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.01)
-- 新列更新数据
gbase> update sc, course c set sc.cname = c.cname where sc.cno=c.cno;
Query OK, 10 rows affected (Elapsed: 00:00:00.07)
Rows matched: 10 Changed: 10 Warnings: 0
-- 新建hash分布表学生成绩表sc2，显示学生学号、课程名称、成绩，根据sc表进行表复制
gbase> create table sc2 distributed by ('sno') as select sno, c.cname, grade
-> from sc, course c where sc.cno=c.cno;
Query OK, 10 rows affected (Elapsed: 00:00:00.76)
-- 显示建表语句
gbase> show create table sc2;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sc2 | CREATE TABLE "sc2" (
"sno" varchar(20) DEFAULT NULL COMMENT '学号',
"cname" varchar(20) DEFAULT NULL COMMENT '课程名',
"grade" int(4) DEFAULT NULL COMMENT '成绩'
) ENGINE=EXPRESS DISTRIBUTED BY('sno') DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
-- 显示数据
gbase> select * from sc2;
+-----------+--------------+-------+
| sno | cname | grade |
+-----------+--------------+-------+
| 200215126 | 信息系统 | 58 |
| 200215126 | 数据处理 | 54 |
| 200215122 | 数学 | 90 |
| 200215122 | 信息系统 | 80 |
| 200215121 | 数据库 | 92 |
| 200215121 | 数学 | 85 |
| 200215121 | 信息系统 | 88 |
| 200215123 | 信息系统 | 56 |
| 200215123 | 数据结构 | 80 |
| 200215123 | 数据库 | 66 |
+-----------+--------------+-------+
10 rows in set (Elapsed: 00:00:00.01)
-- 表复制，新建跟student表一样结构类型的临时学生表student2 , 不带数据
gbase> create table student2 like student;
Query OK, 0 rows affected (Elapsed: 00:00:00.20)
-- 显示建表语句
gbase> show create table student2;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student2 | CREATE TABLE "student2" (
"sno" varchar(20) DEFAULT NULL COMMENT '学号',
"sname" varchar(20) DEFAULT NULL COMMENT '姓名',
"ssex" varchar(3) DEFAULT NULL COMMENT '性别（男、女）',
"sage" int(4) DEFAULT NULL COMMENT '年龄',
"sdept" varchar(20) DEFAULT NULL COMMENT '系'
) ENGINE=EXPRESS DISTRIBUTED BY('sno') DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
-- 查询数据
gbase> select * from student2;
Empty set (Elapsed: 00:00:00.01)
-- 学生表student2，新建学生学号hash索引
gbase> alter table student2 ADD INDEX ha_index(sno) USING hash global;
Query OK, 0 rows affected (Elapsed: 00:00:00.49)
Records: 0 Duplicates: 0 Warnings: 0
gbase> show index from student2;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+-------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+-------------+---------+
| student2 | 1 | ha_index | 1 | sno | NULL | NULL | NULL | NULL | YES | GLOBAL HASH | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+-------------+---------+
1 row in set (Elapsed: 00:00:00.00)
-- 删除已创建的索引
gbase> alter table student2 drop index ha_index;
Query OK, 0 rows affected (Elapsed: 00:00:00.08)
Records: 0 Duplicates: 0 Warnings: 0
SQL查询
-- 查询所有学生信息
gbase> SELECT * FROM STUDENT ;
+-----------+--------+------+------+-------+
| sno | sname | ssex | sage | sdept |
+-----------+--------+------+------+-------+
| 200215123 | 王敏 | 女 | 18 | MA |
| 200215122 | 刘晨 | 女 | 19 | CS |
| 200215121 | 李勇 | 男 | 20 | CS |
| 200215125 | 张立 | 男 | 19 | IS |
+-----------+--------+------+------+-------+
4 rows in set (Elapsed: 00:00:00.01)
-- 查询年龄19到21岁的学生学号和姓名。
gbase> SELECT Sno, Sname FROM Student WHERE Sage BETWEEN 19 AND 21;
+-----------+--------+
| Sno | Sname |
+-----------+--------+
| 200215122 | 刘晨 |
| 200215121 | 李勇 |
| 200215125 | 张立 |
+-----------+--------+
3 rows in set (Elapsed: 00:00:00.01)
-- 查询年龄最大的前三名学生信息
gbase> SELECT * FROM Student ORDER BY sage DESC LIMIT 3;
+-----------+--------+------+------+-------+
| sno | sname | ssex | sage | sdept |
+-----------+--------+------+------+-------+
| 200215121 | 李勇 | 男 | 20 | CS |
| 200215122 | 刘晨 | 女 | 19 | CS |
| 200215125 | 张立 | 男 | 19 | IS |
+-----------+--------+------+------+-------+
3 rows in set (Elapsed: 00:00:00.89)
-- 查询缺少学生年龄的学生编号。
gbase> SELECT * FROM Student WHERE sage IS NULL;
Empty set (Elapsed: 00:00:00.02)
-- 查询姓刘的学生的信息。
gbase> SELECT * FROM Student WHERE Sname LIKE '刘%';
+-----------+--------+------+------+-------+
| sno | sname | ssex | sage | sdept |
+-----------+--------+------+------+-------+
| 200215122 | 刘晨 | 女 | 19 | CS |
+-----------+--------+------+------+-------+
1 row in set (Elapsed: 00:00:00.02)
-- 按学生号（sno）分组，查询学生选课门数
gbase> SELECT sno,COUNT(cno) FROM SC GROUP BY sno;
+-----------+------------+
| sno | COUNT(cno) |
+-----------+------------+
| 200215123 | 3 |
| 200215122 | 2 |
| 200215126 | 2 |
| 200215121 | 3 |
+-----------+------------+
4 rows in set (Elapsed: 00:00:00.08)
-- 查询选修了2门以上课程的学生学号
gbase> SELECT sno,COUNT(distinct cno) FROM SC GROUP BY sno having count(*)>2 order by sno;
+-----------+---------------------+
| sno | COUNT(distinct cno) |
+-----------+---------------------+
| 200215121 | 3 |
| 200215123 | 3 |
+-----------+---------------------+
2 rows in set (Elapsed: 00:00:00.14)
-- 查询每个学生及其选修课程的情况
gbase> SELECT S.Sname, S.Ssex, SC.Cno, SC.GRADE FROM Student S, SC
-> WHERE S.Sno=SC.Sno;
+--------+------+------+-------+
| Sname | Ssex | Cno | GRADE |
+--------+------+------+-------+
| 李勇 | 男 | 1 | 92 |
| 李勇 | 男 | 2 | 85 |
| 李勇 | 男 | 3 | 88 |
| 刘晨 | 女 | 2 | 90 |
| 刘晨 | 女 | 3 | 80 |
| 王敏 | 女 | 3 | 56 |
| 王敏 | 女 | 5 | 80 |
| 王敏 | 女 | 1 | 66 |
+--------+------+------+-------+
8 rows in set (Elapsed: 00:00:00.38)
-- 查询每个学生及其选修课程的情况（包括未选课学生）
gbase> SELECT S.Sname, S.Ssex, SC.Cno, SC.GRADE
-> FROM Student S LEFT OUTER JOIN SC ON S.Sno=SC.Sno ;
+--------+------+------+-------+
| Sname | Ssex | Cno | GRADE |
+--------+------+------+-------+
| 李勇 | 男 | 1 | 92 |
| 李勇 | 男 | 2 | 85 |
| 李勇 | 男 | 3 | 88 |
| 张立 | 男 | NULL | NULL |
| 王敏 | 女 | 3 | 56 |
| 王敏 | 女 | 5 | 80 |
| 王敏 | 女 | 1 | 66 |
| 刘晨 | 女 | 2 | 90 |
| 刘晨 | 女 | 3 | 80 |
+--------+------+------+-------+
9 rows in set (Elapsed: 00:00:00.50)
-- 查询所有选修课程对应的学生及其选课成绩情况
gbase> SELECT S.Sno, S.Sname, S.Ssex, S.Sage, S.Sdept, SC.Cno, SC.GRADE
-> FROM Student S RIGHT OUTER JOIN SC ON S.Sno=SC.Sno ;
+-----------+--------+------+------+-------+------+-------+
| Sno | Sname | Ssex | Sage | Sdept | Cno | GRADE |
+-----------+--------+------+------+-------+------+-------+
| 200215121 | 李勇 | 男 | 20 | CS | 1 | 92 |
| 200215121 | 李勇 | 男 | 20 | CS | 2 | 85 |
| 200215121 | 李勇 | 男 | 20 | CS | 3 | 88 |
| 200215122 | 刘晨 | 女 | 19 | CS | 2 | 90 |
| 200215122 | 刘晨 | 女 | 19 | CS | 3 | 80 |
| 200215123 | 王敏 | 女 | 18 | MA | 3 | 56 |
| 200215123 | 王敏 | 女 | 18 | MA | 5 | 80 |
| 200215123 | 王敏 | 女 | 18 | MA | 1 | 66 |
| NULL | NULL | NULL | NULL | NULL | 3 | 58 |
| NULL | NULL | NULL | NULL | NULL | 6 | 54 |
+-----------+--------+------+------+-------+------+-------+
10 rows in set (Elapsed: 00:00:00.12)
-- 查询每个学生及其选修课程的情况
gbase> SELECT S.Sno, S.Sname, S.Ssex, S.Sage, S.Sdept, SC.Cno, SC.GRADE
-> FROM Student S FULL OUTER JOIN SC ON S.Sno=SC.Sno ;
+-----------+--------+------+------+-------+------+-------+
| Sno | Sname | Ssex | Sage | Sdept | Cno | GRADE |
+-----------+--------+------+------+-------+------+-------+
| 200215121 | 李勇 | 男 | 20 | CS | 1 | 92 |
| 200215121 | 李勇 | 男 | 20 | CS | 2 | 85 |
| 200215121 | 李勇 | 男 | 20 | CS | 3 | 88 |
| 200215122 | 刘晨 | 女 | 19 | CS | 2 | 90 |
| 200215122 | 刘晨 | 女 | 19 | CS | 3 | 80 |
| 200215123 | 王敏 | 女 | 18 | MA | 3 | 56 |
| 200215123 | 王敏 | 女 | 18 | MA | 5 | 80 |
| 200215123 | 王敏 | 女 | 18 | MA | 1 | 66 |
| 200215125 | 张立 | 男 | 19 | IS | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | 3 | 58 |
| NULL | NULL | NULL | NULL | NULL | 6 | 54 |
+-----------+--------+------+------+-------+------+-------+
11 rows in set (Elapsed: 00:00:00.06)
-- 等价于左外连接 UNION 右外连接
gbase> SELECT S.*, SC.Cno, SC.GRADE FROM Student S LEFT OUTER JOIN SC
-> ON S.Sno=SC.Sno
-> UNION
-> SELECT S.*, SC.Cno, SC.GRADE FROM Student S RIGHT OUTER JOIN SC
-> ON S.Sno=SC.Sno ;
+-----------+--------+------+------+-------+------+-------+
| sno | sname | ssex | sage | sdept | Cno | GRADE |
+-----------+--------+------+------+-------+------+-------+
| 200215121 | 李勇 | 男 | 20 | CS | 1 | 92 |
| 200215121 | 李勇 | 男 | 20 | CS | 2 | 85 |
| 200215121 | 李勇 | 男 | 20 | CS | 3 | 88 |
| 200215125 | 张立 | 男 | 19 | IS | NULL | NULL |
| 200215122 | 刘晨 | 女 | 19 | CS | 2 | 90 |
| 200215122 | 刘晨 | 女 | 19 | CS | 3 | 80 |
| 200215123 | 王敏 | 女 | 18 | MA | 3 | 56 |
| 200215123 | 王敏 | 女 | 18 | MA | 5 | 80 |
| 200215123 | 王敏 | 女 | 18 | MA | 1 | 66 |
| NULL | NULL | NULL | NULL | NULL | 3 | 58 |
| NULL | NULL | NULL | NULL | NULL | 6 | 54 |
+-----------+--------+------+------+-------+------+-------+
11 rows in set (Elapsed: 00:00:04.09)
系统表查询
熟悉information_schema、performance_schema、gbase系统库
-- 使用超级管理员登录集群数据库(或使用企业管理器连接)
-- gccli -u用户名 -p密码 -D连接的数据库 （密码：20230304）
[gbase@192 ~]$ gccli -uroot -p -Dmydb
Enter password:
GBase client 9.5.2.39.126761. Copyright (c) 2004-2023, GBase. All Rights Reserved.
-- 使用系统函数，查看当前系统信息
gbase> select version();
+-----------------+
| version() |
+-----------------+
| 9.5.2.39.126761 |
+-----------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> select vc();
+---------+
| vc() |
+---------+
| vc00001 |
+---------+
1 row in set (Elapsed: 00:00:00.00)
gbase> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (Elapsed: 00:00:00.00)
-- 查看当前实例数据库信息
gbase> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| performance_schema |
| gbase |
| gctmpdb |
| gclusterdb |
| mydb |
| test |
+--------------------+
7 rows in set (Elapsed: 00:00:00.00)
-- 切换当前连接数据库
gbase> use information_schema;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
-- 查看数据库中的tables表的总条数信息
gbase> select count(*) from information_schema.tables ;
+----------+
| count(*) |
+----------+
| 128 |
+----------+
1 row in set (Elapsed: 00:00:00.00)
-- 查看当前集群中mydb数据库中所有表信息
gbase> select TABLE_name from information_schema.tables where TABLE_SCHEMA='mydb';
+------------+
| TABLE_name |
+------------+
| course |
| sc |
| sc2 |
| student |
| student2 |
| v_sc |
+------------+
6 rows in set (Elapsed: 00:00:00.00)
-- 查看当前集群中所有的视图信息
gbase> select TABLE_name from information_schema.tables where TABLE_TYPE like 'VIEW';
+------------+
| TABLE_name |
+------------+
| v_sc |
+------------+
1 row in set (Elapsed: 00:00:00.19)
-- 查询当前节点gcluster层磁盘空间使用信息
gbase> use performance_schema;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> select * from performance_schema.DISK_USAGE_INFO;
+-----------------+------------------+--------------------------------------------+----------+-------------------------+-------------+------------+-------------+-----+
| HOST | DIR_TYPE | PATH | DIR_SIZE | FILESYTEM | SIZE | USED | AVAIL | PCT |
+-----------------+------------------+--------------------------------------------+----------+-------------------------+-------------+------------+-------------+-----+
| 192.168.254.138 | datadir | /opt/gbase/gcluster/userdata/gcluster/ | 7700480 | /dev/mapper/centos-root | 53660876800 | 6207758336 | 47453118464 | 12% |
| 192.168.254.138 | gbase_cache_data | /opt/gbase/gcluster/tmpdata/cache_gcluster | 0 | /dev/mapper/centos-root | 53660876800 | 6207758336 | 47453118464 | 12% |
| 192.168.254.138 | logdir | /opt/gbase/gcluster/log/gcluster/ | 6410240 | /dev/mapper/centos-root | 53660876800 | 6207758336 | 47453118464 | 12% |
+-----------------+------------------+--------------------------------------------+----------+-------------------------+-------------+------------+-------------+-----+
3 rows in set (Elapsed: 00:00:00.01)
-- 查询集群某节点的gcluster层和gnode层磁盘空间使用信息
gbase> select * from performance_schema.CLUSTER_DISK_USAGE_INFO where host='192.168.254.138';
+-----------------+------------------+--------------------------------------------+----------+-------------------------+-------------+------------+-------------+-----+
| HOST | DIR_TYPE | PATH | DIR_SIZE | FILESYTEM | SIZE | USED | AVAIL | PCT |
+-----------------+------------------+--------------------------------------------+----------+-------------------------+-------------+------------+-------------+-----+
| 192.168.254.138 | datadir | /opt/gbase/gnode/userdata/gbase/ | 8712192 | /dev/mapper/centos-root | 53660876800 | 6207758336 | 47453118464 | 12% |
| 192.168.254.138 | gbase_cache_data | /opt/gbase/gnode/tmpdata/cache_gbase | 0 | /dev/mapper/centos-root | 53660876800 | 6207758336 | 47453118464 | 12% |
| 192.168.254.138 | logdir | /opt/gbase/gnode/log/gbase/ | 462848 | /dev/mapper/centos-root | 53660876800 | 6207758336 | 47453118464 | 12% |
| 192.168.254.138 | datadir | /opt/gbase/gcluster/userdata/gcluster/ | 7700480 | /dev/mapper/centos-root | 53660876800 | 6207758336 | 47453118464 | 12% |
| 192.168.254.138 | gbase_cache_data | /opt/gbase/gcluster/tmpdata/cache_gcluster | 0 | /dev/mapper/centos-root | 53660876800 | 6207758336 | 47453118464 | 12% |
| 192.168.254.138 | logdir | /opt/gbase/gcluster/log/gcluster/ | 6410240 | /dev/mapper/centos-root | 53660876800 | 6207758336 | 47453118464 | 12% |
+-----------------+------------------+--------------------------------------------+----------+-------------------------+-------------+------------+-------------+-----+
6 rows in set (Elapsed: 00:00:00.04)
-- 如何查看哪些表是复制表，哪些是hash分布表，分布键是哪列?
gbase> select dbName,tbName,isReplicate,hash_column from gbase.table_distribution where dbname= 'mydb' ;
+--------+----------+-------------+-------------+
| dbName | tbName | isReplicate | hash_column |
+--------+----------+-------------+-------------+
| mydb | student | NO | sno |
| mydb | course | YES | NULL |
| mydb | sc | NO | NULL |
| mydb | sc2 | NO | sno |
| mydb | student2 | NO | sno |
+--------+----------+-------------+-------------+
5 rows in set (Elapsed: 00:00:00.00)
-- 查询数据库中的用户和用户组信息
gbase> select user from gbase.user;
+----------------------------------------------------------------------------------------------------------------------------------+
| user |
+----------------------------------------------------------------------------------------------------------------------------------+
| gbase |
| role1 |
| root |
| user1 |
+----------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (Elapsed: 00:00:00.00)
-- 查询数据库中的用户与用户组role的关联关系
gbase> select * from gbase.role_edges;
+--------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------+-------------------+
| FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION |
+--------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------+-------------------+
| % | role1 | % | user1 | N |
+--------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------+-------------------+
1 row in set (Elapsed: 00:00:00.00)
使用show语句查看自己的语法执行错误、当前节点信息和系统参数
-- 查询当前节点正在运行的线程信息
gbase> SHOW FULL PROCESSLIST;
+-----+------+-----------------+-----------------------+--------------------+---------+-------+------------------------+-------------------------+
| Id | Tid | User | Host | db | Command | Time | State | Info |
+-----+------+-----------------+-----------------------+--------------------+---------+-------+------------------------+-------------------------+
| 1 | 3737 | event_scheduler | localhost | NULL | Daemon | 13060 | Waiting for event lock | NULL |
| 12 | 3758 | gbase | 192.168.254.139:47298 | NULL | Sleep | 1199 | | NULL |
| 13 | 3759 | gbase | 192.168.254.140:44912 | NULL | Sleep | 1199 | | NULL |
| 16 | 3783 | root | 192.168.254.1:2482 | NULL | Sleep | 12986 | | NULL |
| 17 | 3785 | root | 192.168.254.1:2483 | NULL | Sleep | 12100 | | NULL |
| 127 | 6718 | root | localhost | performance_schema | Query | 0 | NULL | SHOW FULL PROCESSLIST |
| 131 | 6930 | root | localhost | information_schema | Sleep | 294 | | NULL |
+-----+------+-----------------+-----------------------+--------------------+---------+-------+------------------------+-------------------------+
7 rows in set (Elapsed: 00:00:00.00)
-- 查询最后一条执行错误信息
gbase> use test;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
-- a为不存在的表
gbase> drop table a;
ERROR 1702 (HY000): gcluster table error: (GBA-02DD-0017) Unknown table 'test.a'
gbase>
gbase> show errors;
+-------+------+--------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------+
| Error | 1702 | gcluster table error: (GBA-02DD-0017) Unknown table 'test.a' |
+-------+------+--------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
-- 查看系统参数（压缩）相关值
gbase> show variables like '%compress%';
+---------------------------------+------------+
| Variable_name | Value |
+---------------------------------+------------+
| _gbase_delete_filter_compressed | OFF |
| _gbase_tx_log_compress | ON |
| gbase_compress_level | 0 |
| gbase_compress_method | No Setting |
| gbase_compression_num_method | 5 |
| gbase_compression_sampling | 1 |
| gbase_compression_str_method | 5 |
| have_compress | YES |
| slave_compressed_protocol | OFF |
+---------------------------------+------------+
9 rows in set (Elapsed: 00:00:00.00)
-- 查看节点信息
-- 如果有SUPER 权限，可以看到所有节点信息。
gbase> SHOW NODES;
+------------+-----------------+-------+--------------+----------------+--------+-----------+
| Id | ip | name | primary part | duplicate part | status | datastate |
+------------+-----------------+-------+--------------+----------------+--------+-----------+
| 2331945152 | 192.168.254.138 | node1 | n1,n4 | n3,n5 | online | 0 |
| 2348722368 | 192.168.254.139 | node2 | n2,n5 | n1,n6 | online | 0 |
| 2365499584 | 192.168.254.140 | node3 | n3,n6 | n2,n4 | online | 0 |
+------------+-----------------+-------+--------------+----------------+--------+-----------+
3 rows in set (Elapsed: 00:00:00.00)
-- 显示集群中客户端正在访问节点的信息。
gbase> SHOW LOCAL NODE;
+------------+-----------------+--------------+--------+
| Id | ip | name | status |
+------------+-----------------+--------------+--------+
| 2331945152 | 192.168.254.138 | coordinator1 | ONLINE |
+------------+-----------------+--------------+--------+
1 row in set (Elapsed: 00:00:00.00)
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
