Halo数据库SQL语句的基本用法续篇

冷狼 2024-11-04

107

前言：

写这些文章的初心就是希望能对社区和数据库生态做出一点点微不足道的贡献。也非常感谢墨天轮社区为我们提供的这个平台。再次，本人衷心的感谢各位的支持。

如果有对我们的产品感兴趣的朋友可以通过主页的联系方式与我取得联系，获取license来安装体验，当然您如果有好的建议也可以提给我们。

一、case表达式：

什么是CASE表达式：

CASE表达式我认为是SQL-92标准语法里加入的最有用的特性。通常情况下用于区分在编程过程中的条件分支场景。并且CASE作为SQL语法，不依赖于具体数据库的技术，所以可以提高SQL代码的可移植性。

通常情况下，CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种，下面用示例简单介绍下用法：

示例1：

testzz=#  SELECT job, sal,case job
testzz-# when 'ANALYST' then SAL*1.10
testzz-# when 'CLERK' then SAL*1.15
testzz-# when 'MANAGER' then SAL*1.20
testzz-# else sal end SALARY
testzz-# FROM emp;
    job    | sal  | salary  
-----------+------+---------
 SALESMAN  | 1600 |    1600
 SALESMAN  | 1250 |    1250
 MANAGER   | 2975 | 3570.00
 SALESMAN  | 1250 |    1250
 MANAGER   | 2850 | 3420.00
 MANAGER   | 2450 | 2940.00
 PRESIDENT | 5000 |    5000
 SALESMAN  | 1500 |    1500
 CLERK     |  950 | 1092.50
 ANALYST   | 3000 | 3300.00
 CLERK     | 1300 | 1495.00
 CLERK     |  800 |  920.00
(12 rows)

换一种写法：

testzz=# SELECT job, sal,case
testzz-# when job='ANALYST' then SAL*1.1
testzz-# when job='CLERK'   then SAL*1.15
testzz-# when job='MANAGER' then SAL*1.20
testzz-# else sal end SALARY
testzz-# FROM emp;
    job    | sal  | salary  
-----------+------+---------
 SALESMAN  | 1600 |    1600
 SALESMAN  | 1250 |    1250
 MANAGER   | 2975 | 3570.00
 SALESMAN  | 1250 |    1250
 MANAGER   | 2850 | 3420.00
 MANAGER   | 2450 | 2940.00
 PRESIDENT | 5000 |    5000
 SALESMAN  | 1500 |    1500
 CLERK     |  950 | 1092.50
 ANALYST   | 3000 |  3300.0
 CLERK     | 1300 | 1495.00
 CLERK     |  800 |  920.00
(12 rows)

两种方式的查询结果是一样的。其实我们还可以增加一些搜索功能，上面的例子我使用了when谓词后跟定值，然而还可以使用表达式和比较符。

示例2：

testzz=#  SELECT ename,sal,case  when sal>=3000 then '高级' when sal>=2000 then '中级' else '低级' end 级别 FROM emp;
 ename  | sal  | 级别 
--------+------+------
 ALLEN  | 1600 | 低级
 WARD   | 1250 | 低级
 JONES  | 2975 | 中级
 MARTIN | 1250 | 低级
 BLAKE  | 2850 | 中级
 CLARK  | 2450 | 中级
 KING   | 5000 | 高级
 TURNER | 1500 | 低级
 JAMES  |  950 | 低级
 FORD   | 3000 | 高级
 MILLER | 1300 | 低级
 HALOZZ |  800 | 低级
(12 rows)

二、GROUP子句和分组函数：

1、常见的五个分组函数：

sum(); avg(); count(); max(); min().

Group By 从字面意义上理解就是根据“By”指定的规则对数据进行分组，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。即Group By子句的分组可以理解为去重的效果。这里需要注意，select字句后面的选项只能是聚合函数，和分组特性（分组列）。下面我针对这几个聚合函数结合group by的使用给大家提供一些例子参考。

示例3：

testzz=# select deptno,sum(sal),avg(sal),count(sal),max(sal),min(sal) from emp group by deptno;
 deptno | sum  |          avg          | count | max  | min  
--------+------+-----------------------+-------+------+------
     30 | 9400 | 1566.6666666666666667 |     6 | 2850 |  950
     10 | 8750 | 2916.6666666666666667 |     3 | 5000 | 1300
     20 | 6775 | 2258.3333333333333333 |     3 | 3000 |  800
(3 rows)

我们还可以在where的基础上再分组进行聚合操作

示例4：

testzz=# select deptno,sum(sal),count(sal) from emp where sal>2000 group by deptno;
 deptno | sum  | count 
--------+------+-------
     30 | 2850 |     1
     10 | 7450 |     2
     20 | 5975 |     2
(3 rows)

在我们HaloDB中，数值类型可以使用所有组函数，MIN(),MAX(),count()可以作用于日期类型和字符类型

testzz=#  select min(hiredate), max(hiredate),min(ename),max(ename),count(hiredate) from emp;    min     |    max     |  min  | max  | count 
------------+------------+-------+------+-------
 1980-12-17 | 1982-01-23 | ALLEN | WARD |    12
(1 row)

注意：COUNT(*)函数返回表中行的总数，包括重复行与数据列中含有空值的行，而其他分组函数的统计都不包括空值的行。而COUNT(comm)返回该列所含非空行的数量。

testzz=# select count(*),count(comm) from emp;
 count | count 
-------+-------
    12 |     4
(1 row)

三、Having子句和分组函数的过滤

Having在SQL语句中用于对分组后的数据进行进一步的条件筛选。一般来说，它经常与group by一起使用，首先按照指定字段进行分组（GROUP BY），然后在这些分组的基础上应用聚合函数或者进行其他计算（SELECT, WHERE, HAVING, ORDER BY 等）。我们可以通过 WHERE 子句对每组的记录进行过滤，而 HAVING 则用来对每个分组的结果集进行额外的筛选条件，示例如下

示例5：

testzz=# select deptno,sum(sal),count(sal) from emp where sal>2000 group by deptno having sum(sal)>5000; deptno | sum  | count 
--------+------+-------
     10 | 7450 |     2
     20 | 5975 |     2
(2 rows)

本篇最后，给各位朋友出一个简单的小题目，要求查询出每门课都大于80分的学生姓名，有感兴趣的朋友可以在评论区留言。答案我们下一期揭晓。

create table s1(name char(6),subject char(8),score int);
insert into s1 values('张三','语文',79);
insert into s1 values('张三','数学',75);
insert into s1 values('李四','语文',76);
insert into s1 values('李四','数学',90);
insert into s1 values('王五','语文',90);
insert into s1 values('王五','数学',100);
insert into s1 values('王五','英语',81);

四、ORDER子句

ORDER BY 语句用于根据指定的列对结果集进行排序。ORDER BY 语句默认按照升序对记录进行排序。如果我们希望按照降序对记录进行排序，可以使用 DESC 关键字。

示例6：

testzz=# select deptno,sum(sal),count(sal) from emp where sal>2000 group by deptno having sum(sal)>5000 order by sum(sal) desc;
 deptno | sum  | count 
--------+------+-------
     10 | 7450 |     2
     20 | 5975 |     2
(2 rows)

这里需要注意以下三点：

1）排序可以使用列名，列表达式，列函数，列别名，列位置编号等都没有限制，select的投影列可不包括排序列，除指定的列位置标号外。
2）升序和降序，升序ASC（默认), 降序DESC。有空值的列的排序，缺省（ASC升序）时 null排在最后面。
3）混合排序，使用多个列进行排序，多列使用逗号隔开，可以分别在各列后面加升降序

我们再看两个例子：

示例7：

testzz=# select ename,sal from emp order by sal;
 ename  | sal  
--------+------
 HALOZZ |  800
 JAMES  |  950
 WARD   | 1250
 MARTIN | 1250
 MILLER | 1300
 TURNER | 1500
 ALLEN  | 1600
 CLARK  | 2450
 BLAKE  | 2850
 JONES  | 2975
 FORD   | 3000
 KING   | 5000
(12 rows)

还可以结合我们上面介绍的聚合函数使用，示例如下：

示例8：

testzz=# select deptno,avg(sal) from emp group by deptno order by avg(sal) desc;
 deptno |          avg          
--------+-----------------------
     10 | 2916.6666666666666667
     20 | 2258.3333333333333333
     30 | 1566.6666666666666667
(3 rows)

示例9：

testzz=# select ename,job,sal+comm from emp order by 3 nulls first;
 ename  |    job    | ?column? 
--------+-----------+----------
 HALOZZ | CLERK     |         
 KING   | PRESIDENT |         
 JAMES  | CLERK     |         
 FORD   | ANALYST   |         
 MILLER | CLERK     |         
 JONES  | MANAGER   |         
 BLAKE  | MANAGER   |         
 CLARK  | MANAGER   |         
 TURNER | SALESMAN  |     1500
 WARD   | SALESMAN  |     1750
 ALLEN  | SALESMAN  |     1900
 MARTIN | SALESMAN  |     2650
(12 rows)

结尾：

感谢朋友们关注，我们下期见。

halo 数据库国产数据库

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者