暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / GBase 8a 学习笔记 011 —— GBase 8a MPP Cluster DQL语句

GBase 8a 学习笔记 011 —— GBase 8a MPP Cluster DQL语句

心有阳光 2023-03-06
1017

GBase 8a 学习笔记 011 —— GBase 8a MPP Cluster DQL语句

DQL：Data Query Language 数据库查询语言

SELECT语句语法格式

在SQL中，SELECT语句的语法格式为

SELECT [ ALL | DISTINCT ] <列表达式>, ......
FROM <表名>, ......
[ WHERE <条件表达式> ]
[ GROUP BY <列名>, ...... [ HAVING <条件表达式> ] ]
[ ORDER BY <列名> [ ASC | DESC ], ...... ]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[INTO OUTFILE ‘file_name’ <导出选项> ] ;

编写和执行顺序

  • 查询中用到的关键词主要包含六个，编写顺序依次为
    • select–from–where–group by–having–order by
  • SQL Select语句完整的执行顺序
    • from子句用于组装来自不同数据源的数据；
    • where子句用于根据指定的条件对记录行进行筛选；
    • group by子句用于将数据划分为多个分组；
    • 使用聚集函数进行计算；
    • having子句用于筛选分组；
    • order by子句用于对结果集进行排序。

单表查询示例

-- 数据准备
gbase> CREATE TABLE student (
    ->   Sno varchar(20) ,
    ->   Sname varchar(20) ,
    ->   Ssex varchar(20) ,
    ->   Sage int(11) ,
    ->   Sdept varchar(20)
    -> ) ;
Query OK, 0 rows affected (Elapsed: 00:00:00.05)

gbase> INSERT INTO student (Sno,Sname,Ssex,Sage,Sdept) VALUES
    -> ('200215125','张立','男',19,'IS')
    -> ,('200215123','王敏','女',18,'MA')
    -> ,('200215122','刘晨','女',19,'CS')
    -> ,('200215121','李勇','男',20,'CS');
Query OK, 4 rows affected (Elapsed: 00:00:00.04)
Records: 4  Duplicates: 0  Warnings: 0

gbase> CREATE TABLE SC (
    ->   sno varchar(20) ,
    ->   cno int(11) ,
    ->   grade int(11)) ;
Query OK, 0 rows affected (Elapsed: 00:00:00.03)

gbase> INSERT INTO SC (sno,cno,grade) 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.03)
Records: 10  Duplicates: 0  Warnings: 0

gbase> select * from student;
+-----------+--------+------+------+-------+
| Sno       | Sname  | Ssex | Sage | Sdept |
+-----------+--------+------+------+-------+
| 200215125 | 张立   | 男   |   19 | IS    |
| 200215123 | 王敏   | 女   |   18 | MA    |
| 200215122 | 刘晨   | 女   |   19 | CS    |
| 200215121 | 李勇   | 男   |   20 | CS    |
+-----------+--------+------+------+-------+
4 rows in set (Elapsed: 00:00:00.02)

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>

gbase8a096.png

gbase8a097.png

-- 查询所有学生信息
-- * 代表所有列,慎用
gbase> select * from student;
+-----------+--------+------+------+-------+
| Sno       | Sname  | Ssex | Sage | Sdept |
+-----------+--------+------+------+-------+
| 200215125 | 张立   | 男   |   19 | IS    |
| 200215123 | 王敏   | 女   |   18 | MA    |
| 200215122 | 刘晨   | 女   |   19 | CS    |
| 200215121 | 李勇   | 男   |   20 | CS    |
+-----------+--------+------+------+-------+
4 rows in set (Elapsed: 00:00:00.01)

-- 查询学生学号和姓名信息。
-- 推荐写具体投影列
gbase> SELECT Sno, Sname FROM STUDENT;
+-----------+--------+
| Sno       | Sname  |
+-----------+--------+
| 200215125 | 张立   |
| 200215123 | 王敏   |
| 200215122 | 刘晨   |
| 200215121 | 李勇   |
+-----------+--------+
4 rows in set (Elapsed: 00:00:00.01)

-- 查询偶数年龄的学生学号和姓名。
-- 推荐写where条件过滤数据
gbase> SELECT Sno 学号,Sname 姓名 FROM STUDENT where Sage % 2 = 0;
+-----------+--------+
| 学号      | 姓名   |
+-----------+--------+
| 200215123 | 王敏   |
| 200215121 | 李勇   |
+-----------+--------+
2 rows in set (Elapsed: 00:00:00.01)

-- 查询年龄19到21岁的学生学号和姓名。
-- BETWEEN... AND范围等同于>= and <=
gbase> SELECT Sno, Sname FROM Student WHERE Sage BETWEEN 19 AND 21;
+-----------+--------+
| Sno       | Sname  |
+-----------+--------+
| 200215125 | 张立   |
| 200215122 | 刘晨   |
| 200215121 | 李勇   |
+-----------+--------+
3 rows in set (Elapsed: 00:00:00.01)

-- 查询姓张且全名为2个汉字的学生及姓刘的学生的信息。
-- %代表任意多个字符
-- _代表任意一个字符
gbase> SELECT * FROM Student WHERE Sname LIKE '刘%' OR Sname LIKE '张_';
+-----------+--------+------+------+-------+
| Sno       | Sname  | Ssex | Sage | Sdept |
+-----------+--------+------+------+-------+
| 200215125 | 张立   | 男   |   19 | IS    |
| 200215122 | 刘晨   | 女   |   19 | CS    |
+-----------+--------+------+------+-------+
2 rows in set (Elapsed: 00:00:00.01)

gbase8a098.png

运算符

查询类别 运算符
算术 +, -, *, /, div, %, mod
比较 =, >, >=, <, <=, !=, <>
范围 BETWEEN…AND…, NOT BETWEEN…AND…
集合 IN, NOT IN
空值判断 IS NULL, IS NOT NULL
字符匹配 LIKE, NOT LIKE, REGEXP, RLIKE
逻辑运算 NOT, !, AND, &&, OR, ||, XOR 
-- 查询缺少学生年龄的学生编号。
-- NULL 表示“空值”，没有数据；
-- 不同于数字 0或‘’ 空字符串；
-- 不能用=号，不能与其他值做运算
SELECT * FROM Student WHERE sage IS NULL ;

-- 查询缺少学生年龄的学生编号。
-- NULL 表示“空值”，没有数据；
-- 不同于数字 0或‘’ 空字符串；
-- 不能用=号，不能与其他值做运算
gbase> SELECT * FROM Student WHERE sage IS NULL ;
Empty set (Elapsed: 00:00:00.01)

gbase8a099.png

常用的SQL聚合函数

聚合函数 描 述
COUNT(*) 计算所有行的个数
COUNT(<列名>) 计算一列中非NULL值的个数
SUM(<列名>) 计算一列值的总和（该列必须为数值型）
AVG(<列名>) 计算一列值的平均值（该列必须为数值型）
MAX(<列名>) 计算一列值中的最大值（Maximum）
MIN(<列名>) 计算一列值中的最小值（Minimum） 
-- 按课程号（Cno）分组，查询每门课程的选课人数
-- SELECT 的投影列必须包含在 GROUPBY 中
gbase> SELECT Cno,COUNT(Sno) FROM SC GROUP BY Cno ;
+------+------------+
| Cno  | COUNT(Sno) |
+------+------------+
|    6 |          1 |
|    2 |          2 |
|    1 |          2 |
|    3 |          4 |
|    5 |          1 |
+------+------------+
5 rows in set (Elapsed: 00:00:00.61)

-- 查询选修了2门以上课程的学生学号
-- having是分组聚合运算后的结果上进行筛选
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.23)

gbase8a100.png

where、having 的区别和用法 ：

  • where 后不能跟聚合函数，因为where执行顺序在分组前过滤数据。
  • having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚合函数。
-- 按学生年龄降序排名
gbase> SELECT * FROM Student ORDER BY sage DESC;
+-----------+--------+------+------+-------+
| Sno       | Sname  | Ssex | Sage | Sdept |
+-----------+--------+------+------+-------+
| 200215121 | 李勇   | 男   |   20 | CS    |
| 200215122 | 刘晨   | 女   |   19 | CS    |
| 200215125 | 张立   | 男   |   19 | IS    |
| 200215123 | 王敏   | 女   |   18 | MA    |
+-----------+--------+------+------+-------+
4 rows in set (Elapsed: 00:00:00.07)

-- 查询年龄最大的前三名学生信息
-- LIMIT 3; //检索前 3个记录行
-- LIMIT 3,10; // 检索记录行 4-13
-- LIMIT 10 OFFSET 3; // 检索记录行 4-13
gbase> SELECT * FROM Student ORDER BY sage DESC LIMIT 3;
+-----------+--------+------+------+-------+
| Sno       | Sname  | Ssex | Sage | Sdept |
+-----------+--------+------+------+-------+
| 200215121 | 李勇   | 男   |   20 | CS    |
| 200215125 | 张立   | 男   |   19 | IS    |
| 200215122 | 刘晨   | 女   |   19 | CS    |
+-----------+--------+------+------+-------+
3 rows in set (Elapsed: 00:00:00.10)

-- 使用LIMIT 实现显示11-20条结果集
SELECT * FROM Student ORDER BY sage DESC LIMIT 10,10;

-- 使用LIMIT 实现分页显示
select * from studnet limit (pageNumber-1)*pageSize, pageSize;
select * from student limit pageSize offset (pageNumber-1)*pageSize;

gbase8a101.png

连接查询

  • 若一个查询同时涉及两个及以上的表，则需要连接查询
  • 关系数据库的强大处理能力正是源于各种形式的连接查询，能够将不同表的数据按一定条件连接在一起
  • SQL 提供的链接查询分为内连接（Inner Join）和外连接（Outer Join）两大类型
    • 内连接将两个表连接在一起的条件称为连接谓词（Join Predicate）或连接条件
    • 内连接只返回两个表中与连接谓词匹配的行，不匹配的行不会被输出
    • 外连接可以将左表或右表中不匹配的行输出
    • 外连接分为左外连接（Left Outer Join）、右外连接（Right Outer Join）和完全外连接（Full OuterJoin）
  • INNER JOIN（内连接）
  • LEFT OUTER JOIN（左外连接：左表无对应项的记录填充NULL)
  • RIGHT OUTER JOIN（右外连接：右表无对应项的记录填充NULL）
  • FULL OUTER JOIN（完全外连接：左表或右表无对应项的记录填充NULL）

笛卡尔积

在编写中，避免出现笛卡尔积，会导致性能很差
只要有意义的结果，增加关联条件 WHERE A.A_id = B.A_id;

内连接

SELECT <列名>, ...... 
FROM <左表>，<右表>
WHERE <左表>.<主键> <比较运算符> <右表>.<外键>

-- 或

SELECT <列名>, ...... 
FROM <左表> INNER JOIN <右表>
ON <左表>.<主键> <比较运算符> <右表>.<外键>

-- 查询每个学生及其选修课程的情况
-- 写法一
-- 内连接，结果集取两表中符合条件的记录
SELECT S.Sname, S.Ssex, SC.Cno, SC.GRADE FROM Student S, SC WHERE S.Sno=SC.Sno;

-- 写法二
SELECT S.Sname, S.Ssex, SC.Cno, SC.GRADE FROM Student S INNER JOIN SC ON S.Sno=SC.Sno;

-- 查询每个学生及其选修课程的情况
-- 写法一
-- 内连接，结果集取两表中符合条件的记录
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.06)

-- 写法二
gbase> SELECT S.Sname, S.Ssex, SC.Cno, SC.GRADE FROM Student S INNER JOIN SC ON 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.03)

gbase8a102.png

-- 查询每个学生及其选修课程的情况（包括未选课学生）
-- 左外连接
-- 左外连接，结果集取左表中全部的行，如右表中没有符合条件的对应则填充null
SELECT S.Sno, S.Sname, S.Ssex, S.Sage, S.Sdept, SC.Cno, SC.GRADE FROM Student S LEFT OUTER JOIN SC ON S.Sno=SC.Sno ;

-- 查询每个学生及其选修课程的情况（包括未选课学生）
-- 左外连接
-- 左外连接，结果集取左表中全部的行，如右表中没有符合条件的对应则填充null
gbase> SELECT S.Sno, S.Sname, S.Ssex, S.Sage, S.Sdept, SC.Cno, SC.GRADE FROM Student S LEFT 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 |
+-----------+--------+------+------+-------+------+-------+
9 rows in set (Elapsed: 00:00:00.41)

-- 查询每个学生及其选修课程的情况（包括未选课学生）
-- 右外连接
-- 右外连接，结果集取右表中全部的行，如左表中没有符合条件的对应则填充null
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.07)

-- 查询每个学生及其选修课程的情况
-- 全连接
-- 全连接，结果集取两张表中全部的行，如没有符合条件的对应项则填充null
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.04)

gbase8a103.png

全连接

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 ;

注：实际编写SQL中，尽量少用全连接，避免结果集过大导致性能慢。

自连接

是连接的一种用法, 本质是把一张表当成两张表来使用，一般用别名区分。

-- 查询比’王敏’年龄大的学生的人数（ 在Student表中，’王敏’ 的年龄为18）

-- 分步方式：

SELECT sage FROM student WHERE sname='王敏'; //得出sage查询结果为18

SELECT count(*) 学生人数 FROM student WHERE sage>18;

-- 自连接方式（在一条 SQL 中实现）：
-- a和b是student表的别名

gbase> SELECT count(*) 学生人数
    -> from student as a, student as b
    -> where a.sname='王敏' and a.sage<b.sage;
+--------------+
| 学生人数     |
+--------------+
|            3 |
+--------------+
1 row in set (Elapsed: 00:00:00.09)

gbase8a104.png

多表连接

-- 查询每个学生学号，姓名，选修的课程名及成绩
-- 两个以上的表进行连接，这称为多表连接。

SELECT S.Sno, S.Sname, C.Cname, SC.Grade FROM Student S ,Course C,SC WHERE S.Sno=SC.Sno AND SC.Cno=C.Cno ;

说明：

  • 多表关联时，基于代价进行优化，内关联跟表书写顺序无关；
  • where条件从右向左顺序执行，建议把返回结果集小的表条件写到最后；
  • 使用explain … 在线查询 SELECT 语句执行计划

Union 和 Union All

应用：如果两个或多个 SELECT 语句的结构相似，则可以用“Union”或“Union All”把这些SELECT 语句合并起来

UNION 与 UNION ALL 的区别：

  • UNION ： 对两个结果集进行并集操作，不包括重复行；
  • Union All：对两个结果集进行并集操作，包括重复行，不进行排序；
  • 如果结果集没重复数据，建议使用Union All 代替UNION，性能更好。

注意：保证各个select 集合的结果有相同个数的列，并且每个列的类型是一样的。

  1. 两端都是char类型数据，会隐式转换成varchar类型。
  2. 小的数据类型向大的数据类型转换，如：INT -> BIGINT -> DECIMAL -> DOUBLE

编写高效SQL语句

  • 避免生产环境使用select ，减少投影列
  • 数据量大时，group By 、order by会很费时，建议将hash分布列写在第一个位置
  • 使用where条件提前过滤数据，减少join的运算
  • 使用limit 减少物化的结果集条数
  • 在做多表查询时应注意字段名称的唯一性;如果不唯一，则要明确写明表名。
  • 内连接比外连接效率要高，连接查询所使用的字段最好是hash分布键。
  • 注意笛卡尔积的问题:
    • 关注 /opt/gnode/tmpdata/cache_gbase/HashJoin 空间变化，如果出现2-10G增长，则表示可能出现笛卡尔积
gbase墨力计划
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

GBase 8a 学习笔记（共21篇）
GBase 8a 学习笔记

评论