Menu

Subscribe

SQL必知必会

SQL

这是<<SQL必知必会>>的读书笔记，虽然这是一本用MySQL作为主要工具的书，但是用SQL Server其实也没有问题，电子书来源于Linux公社。
 过程中，使用了 DataGrip 作为SQL编辑执行工具，数据库使用了SQL Server 2017 Express。
 前文中数据类型，基本的查询操作之前看了很多，这里主要关注函数和存储过程。

使用函数

大多数SQL支持实现以下类型的函数
 - 用于处理文本字符串
 - 用于在数值数据上进行算数操作
 - 用于处理日期和时间
 - 返回DBMS正使用的特殊信息

文本处理函数

文本处理函数RTRIM()可以去除列值右边的空格，UPPER()函数可将文本转为大写 

SELECT
  UPPER(FIRST_NAME)                                    FIRST_NAME,
  UPPER(REPLACE(SUBSTRING(LAST_NAME, 1, 5), 'a', '_')) LAST_NAME
FROM PEOPLE;

常用文本处理函数如下图，其中需要注意的是SOUNDEX()。
 常用日期处理函数

SOUNDEX()函数将任何文本字符串转换为描述其语音表示的字母数字模式的算法，SOUNDEX()考虑了类似的发音字符和音节
 使得能对字符串进行发音比较而不是字母比较，虽然SOUNDEX()不是SQL概念，但是大多数DBMS都提供SOUNDEX()的支持 

SELECT
  FIRST_NAME FIRST_NAME,
  LAST_NAME  LAST_NAME
FROM PEOPLE
WHERE SOUNDEX(FIRST_NAME) = SOUNDEX('JACK');

时间与日期处理

应用程序一般不使用日期和时间的存储格式，因此日期和时间函数总是用来读取、统计和处理这些值。由于这个原因，日期和时间函数在SQL中具有重要的作用。遗憾的是，它们很不一致，可移植性最差。 

SELECT
  SNO,
  SNAME,
  SBIRTHDAY
FROM STUDENT
WHERE DATEPART(YY, SBIRTHDAY) = 1976;

查询结果如下:

SNO SNAME SBIRTHDAY
101 李军 1976-02-20 00:00:00.000
107 王丽 1976-01-23 00:00:00.000

数值处理函数

数值处理函数仅处理数值数据。这些函数一般主要用于代数、三角或几何运算，因此不像字符串或日期时间处理函数使用那么频繁。具有讽刺意味的是，在主要 DBMS 的函数中，数值函数是最一致、最统一的函数。

常用的数值处理函数如下图所示:
 常用数值处理函数

聚集函数

我们经常需要汇总数据而不用把它们实际检索出来，为此 SQL 提供了专门的函数。使用这些函数， SQL 查询可用于检索数据，以便分析和报表生成。这种类型的检索例子有:

  • 确定表中行数（或者满足某个条件或包含某个特定值的行数）;
  • 获得表中某些行的和;
  • 找出表列（或所有行或某些特定的行）的最大值、最小值、平均值.

聚集函数（Aggregate Function）对某些行运行的函数，计算并返回一个值。

常用的聚集函数如下图所示:

以上 5 个聚集函数都可以如下使用：

  • 对所有行执行计算，指定ALL参数或不指定参数（因为ALL是默认行为）
  • 只包含不同的值，指定DISTINCT参数

Noticed:
 ALL参数不需要指定，因为它是默认行为。如果不指定DISTINCT，则假定为ALL

通过如下代码可以看出: 

SELECT COUNT(SSEX)
FROM STUDENT;
SELECT COUNT(DISTINCT SSEX)
FROM STUDENT;

第一段的执行结果为6，第二段的执行结果为2，显然DISTINCT()作用在于将相同的键值进行合并。

Warning: DISTINCT不能用于 不能用于COUNT(*)
 DISTINCT必须使用列名，不能用于计算或表达式。

组合聚集函数

目前为止的所有聚集函数例子都只涉及单个函数。但实际上，SELECT语句可根据需要包含多个聚集函数。 

SELECT COUNT(*) AS NUM_ITEMS,
       MIN(PROD_PRICE) AS PRICE_MIN,
       MAX(PROD_PRICE) AS PRICE_MAX,
       AVG(PROD_PRICE) AS PRICE_AVG
FROM PRODUCTS;

返回结果如下:

NUM_ITEMS PRICE_MIN PRICE_MAX P RICE_AVG
9 3.4900 11.9900 6.823333

分组数据

使用分组可以将数据分为多个逻辑组，对每个组进行聚集计算。

创建分组

在使用GROUP BY子句前，需要知道一些重要的规定。

  • GROUP BY子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致地进行数据分组；
  • 如果在GROUP BY子句中嵌套了分组，数据将在最后指定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）；
  • GROUP BY子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名；
  • 大多数 SQL 实现不允许GROUP BY列带有长度可变的数据类型（如文本或备注型字段）。
  • 除聚集计算语句外，SELECT语句中的每一列都必须在GROUP BY子句中给出；
  • 如果分组列中包含具有NULL值的行，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。

这是之前做的一个案例，需要查询SCORE表中选学一门以上课程的同学中分数为非最高分成绩的记录，非常绕人，下面使用GROUP BY语句可以拿到选学一门课程以上并且分数为最高分的记录。 

SELECT
  SNO,
  max(DEGREE) AS MD
FROM SCORE
GROUP BY SNO
HAVING count(SNO) > 1

HAVING支持所有的WHERE操作符

未完待续...

Comments
Write a Comment