SQL查询

以opengauss数据库(postgreSQL)为测试环境。

去重

1
2
SELECT DISTINCT xh 
FROM xk;

部分选择

使用 LIMIT OFFSET 子句。

1
2
3
SELECT * 
FROM xk
LIMIT 5 OFFSET 5;

排序

用 ORDER BY 子句进行排序。

排序的依据不必要来自查询的列。

可以有多个排序依据， 优先级按顺序来。

默认为升序， 可以用 DESC 改为降序。

1
2
3
SELECT *
FROM xk
ORDER BY cj DESC, xh;

LIMIT 子句要写在 ORDER BY 后面。

过滤数据

使用 WHERE 子句。

注意操作符

  • BETWEEN AND
  • IS NULL

可以用 AND 与 OR 连接多个条件。 AND 优先级高于 OR, 用 () 明确条件。

IN 和 NOT.

1
2
3
SELECT *
FROM xk
WHERE kcbh NOT IN (1238, 7398);

使用 LIKE 谓词与通配符过滤字符串。

通配符：

  • %. 匹配任意字符， 可以为0个， 不可以匹配 NULL.
  • _. 匹配一个字符。 (DB2不支持)
  • []. 指定字符集， 匹配字符集中的一个字符。 可以在里面加前缀字符^来否定。 如 [^ABC]， 或用NOT。
1
2
3
SELECT *
FROM XK
WHERE xh LIKE '2020%';

计算字段

使用 || 拼接。

AS 关键字赋予别名。

1
2
3
4
SELECT xh||' '||kcbh
AS a
FROM xk
LIMIT 5;

函数

文本处理函数：

  • UPPER()
  • LOWER()
  • TRIM

聚集函数：

  • AVG()
  • COUNT()
  • MAX()
  • MIN()
  • SUM()

数据分组

用GROUP BY 子句进行数据分组， 按组计算聚集。

分组可以嵌套， 在最后的分组上汇总数据。

NULL值为一组。

GROUP BY 子句要在 WHERE 之后， ORDER BY 之后。

过滤分组

使用HAVING 子句过滤分组。

1
2
3
4
5
SELECT xh, SUM(cj) AS scores
FROM xk
GROUP BY xh
HAVING xh LIKE '2020%'
ORDER BY scores DESC;

子查询

查询结果可以作为列返回。 因此可以嵌套 SELECT 语句。 用 IN 连接。

子查询只能查询单个列。

1
2
3
4
5
6
7
SELECT sum(xf)
FROM kc
WHERE kcbh IN (SELECT kcbh
               FROM xk
               WHERE xh IN (SELECT xh
                            FROM xs
                            WHERE xm='张三'));

很多子查询可以用联结代替， 联结写起来更简洁， 但运行效率较低。

如上面的查询可以替换为：

1
2
3
4
5
SELECT sum(xf)
FROM xs, xk, kc
WHERE kc.kcbh=xk.kcbh AND xk.xh=xs.xh
GROUP BY xm
HAVING xm='张三';

联结

内联结

等值联结

使用关联的数据来过滤笛卡尔积。

1
2
3
4
5
6
SELECT xm, xs.xh, SUM(cj) AS scores
FROM xs, xk
WHERE xs.xh=xk.xh
GROUP BY xs.xh
HAVING xs.xh LIKE '2020%'
ORDER BY scores DESC;

还可以用 INNER JOIN ... ON 语法， 更加标准。

根据笛卡尔积的理解不难推想出多个表的内联结处理方法。

内联结好写， 但效率不高。

自联结

通过别名的方法将一张表拓展成两张相同的表， 然后用内联结方法处理。

自然联结

笛卡尔积使得相同的列会多次出现， 自然联结便是人工去重， 明确重复的列来自哪张表。

外联结

外联结包括两张表中没有相同值的部分， 值为NULL。

LEFT/RIGHT/FULL OUTER JOIN ... ON 。 

1
2
3
SELECT xm, sum(cj)
FROM xs
FULL OUTER JOIN xk ON xs.xh=xk.xh;

组合查询

用 UNION 将多个 SELECT 语句查询结果合并， 这些 SELECT 语句的查询必须包含相同列、表达式或聚集函数且数据类型兼容。

合并时重复的查询结果会自动去重。

1
2
3
4
5
6
7
SELECT xm, xh
FROM xs
WHERE ydh='01'
UNION
SELECT xm, xh
FROM xs
WHERE xh LIKE '2020%';

使用 UNION ALL 将取消自动去重。

只能用一条 ORDER BY 语句排序， 且位于最后。

交叉表

动态交叉表需要查阅相关数据库的文档。

静态交叉表实现思路大致一致， 语句需要查阅相关数据库文档。

postgresql的静态交叉表：

1
2
3
4
5
6
7
SELECT xm,
sum(CASE WHEN xk.kcbh=(SELECT kcbh FROM kc WHERE kc.kc='数学分析') THEN cj ELSE 0 END) AS "数学分析",
sum(CASE WHEN xk.kcbh=(SELECT kcbh FROM kc WHERE kc.kc='数据库设计') THEN cj ELSE 0 END) AS "数据库设计",
sum(CASE WHEN xk.kcbh=(SELECT kcbh FROM kc WHERE kc.kc='大学物理') THEN cj ELSE 0 END) AS "大学物理"
FROM xs, xk
WHERE xs.xh=xk.xh
GROUP BY xm;

AS 后面的字符串需要用双引号扩起。