1 安全管理

1.1 使用root登录

使用数据库超户root连接数据库后，创建数据库，创建新用户组和用户，并给新用户组和用户分配相关权限
连接登录集群数据库（默认管理员为root，密码为空）

[gbase@gbase02 ~]$ gccli -uroot -p
Enter password:
GBase client 9.5.2.39.126761. Copyright (c) 2004-2023, GBase. All Rights Reserved.

1.2 给root用户设置密码

gbase> set password for root=password('123456');
Query OK, 0 rows affected (Elapsed: 00:00:00.05)

1.3 新建数据库

gbase> create database if not exists mydb;
gbase> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| performance_schema |
| gbase |
| gctmpdb |
| gclusterdb |
| mydb |
| obs |
| test |
| test2 |
+--------------------+
9 rows in set (Elapsed: 00:00:00.01)

1.4 创建用户组和用户并赋权

gbase> create role role1; 
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

gbase> use mydb;

给用户组赋予数据库级权限,首先选择要赋权限的数据库
gbase> grant all on mydb.* to role1;
Query OK, 0 rows affected (Elapsed: 00:00:00.02)

创建用户
gbase> create user user1 identified by '123456';
Query OK, 0 rows affected (Elapsed: 00:00:00.03)
给用户授于用户组权限
gbase> grant role1 to user1;
Query OK, 0 rows affected (Elapsed: 00:00:00.02)

显示该用户组和用户的权限
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> 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> 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\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)

1.5 使用新账号登录

[gbase@gbase02 ~]$ gccli -uuser1 -p -D mydb
Enter password: 
gbase> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| performance_schema |
| mydb |
+--------------------+
3 rows in set (Elapsed: 00:00:00.00)

gbase> use mydb;

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)

2 数据库对象基本操作

2.1 创建hash分布表

创建hash分布表、复制表、随机分布表、视图、索引等，继续使用user1账户进行操作即可
在库中创建hash分布表(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 '系')
distributed by ('sno');

查看列名

desc student;
show create table student;

2.2 创建复制表

在库中创建复制表(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 '学分')
replicated ;

2.3  创建随机分布表

在库中创建随机分布表(sc 学生成绩表)

drop table if exists sc;
create table sc(
sno varchar(20) DEFAULT NULL COMMENT '学号',
cno int(4) DEFAULT NULL COMMENT '课程号',
grade int(4) DEFAULT NULL COMMENT '成绩');

2.4 查询建表语句

show create table course;

2.5 插入数据

insert into student values 
('200215121','李勇','男',20,'CS'),('200215122','刘晨','女',19,'CS'),('200215123','王敏','女',18,'MA'), ('200215125','张立','男',19,'IS');
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);

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);

2.6 查询数据

select count(*) from student;
desc student;
select * from student;
select * from course;
select * from sc;

2.7 新建一个视图

显示学生成绩表，字段包括学生姓名、课程、成绩

CREATE OR REPLACE VIEW v_sc 
AS SELECT sname, cno, grade FROM student s,sc where s.sno=sc.sno; 

显示表和视图名字

show tables;
show create view v_sc;

2.8 修改表sc

在课程号后面增加一列课程名称

alter table sc add cname varchar(20) DEFAULT NULL after cno;
desc sc;
show create table sc;

新列更新数据

update sc, course c set sc.cname = c.cname where sc.cno=c.cno;
select * from sc;

新建hash分布表学生成绩表sc2，显示学生学号、课程名称、成绩，根据sc表进行表复制，

create table sc2 distributed by ('sno') as select sno, c.cname, grade from sc, course c where sc.cno=c.cno;
show create table sc2; 
select * from sc2;

表复制，新建跟student表一样结构类型的临时学生表student2 , 不带数据

create table student2 like student;
show create table student2; 
select * from student2;

学生表student2，新建学生学号hash索引

alter table student2 ADD INDEX ha_index(sno)  USING hash global;
show index from student2;

删除已创建的索引

alter table student2 drop index ha_index;
show index from student2;

3 SQL查询

3.1 查询所有学生信息

SELECT * FROM STUDENT ;  

3.2 查询年龄19到21岁的学生学号和姓名

SELECT Sno, Sname FROM Student WHERE Sage BETWEEN 19 AND 21;  

3.3 查询年龄最大的前三名学生信息

SELECT  *  FROM Student ORDER BY sage DESC LIMIT 3;

3.4 查询缺少学生年龄的学生编号

SELECT  * FROM Student WHERE sage IS NULL ;

3.5 查询姓刘的学生的信息

SELECT * FROM Student WHERE Sname LIKE '刘%';

3.6 按学生号（sno）分组，查询学生选课门数

SELECT sno,COUNT(cno) FROM SC GROUP BY sno ;

3.7 查询选修了2门以上课程的学生学号

SELECT sno,COUNT(distinct cno) FROM SC GROUP BY sno having count(*)>2 order by sno;

3.8 查询每个学生及其选修课程的情况

SELECT  S.Sname, S.Ssex,  SC.Cno, SC.GRADE   FROM Student S, SC 
WHERE S.Sno=SC.Sno;

3.9 查询每个学生及其选修课程的情况（包括未选课学生）

SELECT  S.Sname, S.Ssex,  SC.Cno, SC.GRADE  
        FROM Student S LEFT OUTER JOIN SC  ON S.Sno=SC.Sno  ;

3.10 查询所有选修课程对应的学生及其选课成绩情况

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 ;

3.11 查询每个学生及其选修课程的情况

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  ;
-- 等价于左外连接 UNION 右外连接
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  ;

4 系统表查询

4.1 目标

熟悉information_schema、performance_schema.、gbase系统库

4.1 使用超级管理员登录集群数据库(或使用企业管理器连接)

gccli -u用户名 -p密码 -D 连接的数据库

4.2 使用系统函数，查看当前系统信息

select version();
select vc();
select user();

4.3 查看当前实例数据库信息

show databases;

4.4 切换当前连接数据库

use information_schema;

4.5 查看数据库中的tables表的总条数信息

select count(*) from information_schema.tables ;

4.6 查看当前集群中mydb数据库中所有表信息

select TABLE_name from information_schema.tables where TABLE_SCHEMA='mydb';

4.7 查看当前集群中所有的视图信息

select TABLE_name from information_schema.tables where TABLE_TYPE like 'VIEW';

4.8 查询当前节点gcluster层磁盘空间使用信息

use performance_schema;
select * from performance_schema.DISK_USAGE_INFO;

4.9 查询集群某节点的gcluster层和gnode层磁盘空间使用信息

select * from performance_schema.CLUSTER_DISK_USAGE_INFO where host='vm1';

4.10 如何查看哪些表是复制表，哪些是hash分布表，分布键是哪列?

select dbName,tbName,isReplicate,hash_column from gbase.table_distribution where dbname= 'mydb' ;

4.11 查询数据库中的用户和用户组信息

select user from gbase.user;

4.12 查询数据库中的用户与用户组role的关联关系

select * from gbase.role_edges;

4.13 使用show语句查看自己的语法执行错误、当前节点信息和系统参数

查询当前节点正在运行的线程信息：

SHOW FULL PROCESSLIST;

查询最后一条执行错误信息：

use test;
drop table a; -- a为不存在的表
show errors;

查看系统参数（压缩）相关值

show variables like '%compress%';

查看节点信息

SHOW NODES; --如果有SUPER 权限，可以看到所有节点信息。
SHOW LOCAL NODE; --显示集群中客户端正在访问节点的信息。