SQL学习笔记（2）

1. 数据处理函数

对于数据处理函数，每一个DBMS都有特定的函数，因此用到数据处理函数的SQL语句不易于移植。

1.1 常用的文本处理函数

• LEFT截取字符串左边部分

  LEFT(要截取的字段，截取长度)

  SELECT LEFT(username,3) FROM user;
  

• RIGHT截取字符串右边部分

  RIGHT(要截取的字段，截取长度)

  SELECT RIGHT(username,3) FROM user;
  

• LENGTH返回字符串长度

  LENGTH(字符串)

  SELECT LENGTH(username) FROM user;
  

• LOWER将字符串转换为小写

  LOWER(字符串)

  SELECT LOWER(username) FROM user;
  

• UPPER将字符串转换为大写

  UPPER(字符串)

  SELECT UPPER(username) FROM user;
  

• TRIM LTRIM RTRIM去除字符串左右、左边、右边多余空格

• SUBSTRING截取字符串

  SUBSTRING(被截取字段，从第几位开始截取)

  SUBSTRING（被截取字段，从第几位开始截取，截取长度）

  SELECT SUBSTRING(username,3),SUBSTRING(username,3,2) FROM user;
  

1.2 日期和时间数据处理函数

• CURDATE获取当前日期

  SELECT CURDATE();
  +------------+
  | CURDATE()  |
  +------------+
  | 2021-10-15 |
  +------------+
  

• CURTIME获取当前时间

  SELECT CURTIME();
  +-----------+
  | CURTIME() |
  +-----------+
  | 10:05:22  |
  +-----------+
  

• NOW获取当前时间日期：

  SELECT NOW();
  +---------------------+
  | NOW()               |
  +---------------------+
  | 2021-10-15 10:06:17 |
  +---------------------+
  

• DATE获取日期和时间数据中的日期

  SELECT DATE(NOW());
  

• TIME获取日期和时间数据中的时间

  SELECT TIME(NOW());
  

• YEAR获取日期和时间数据中的年份

  SELECT YEAR(NOW());
  

• MONTH获取日期和时间数据中的月份

  SELECT MONTH(NOW());
  

• DAY获取日期和时间数据中的天数

  SELECT DAY(NOW());
  

• HOUR获取日期和时间数据中的小时

  SELECT HOUR(NOW());
  

• MINUTE获取日期和时间数据中的分钟

  SELECT MINUTE(NOW());
  

• SECOND获取日期和时间数据中的秒钟

  SELECT SECOND(NOW());
  

• MICROSECOND获取日期和时间数据中的毫秒

  SELECT MICROSECOND(NOW());
  

1.3 数值处理函数

在主要的DBMS中，数值处理函数是最统一的

函数	说明
ABS()	返回一个数的绝对值
COS()	返回一个角度的余弦
EXP()	返回一个数的指数值
PI()	返回圆周率π的值
SIN()	返回一个角度的正切
SQRT()	返回一个数的平方根
TAN()	返回一个角度的正切

2. 汇总数据

**聚集函数：**可以对检索结果列进行汇总统计

不同DBMS中的聚集函数基本保持一致

函数	说明
AVG()	返回某列平均值
COUNT()	返回某列的行数
MAX()	返回某列最大值
MIN()	返回某列最小值
SUM()	返回某列值之和

• AVG计算列值之和

  AVG只能用于单个列，且忽略NULL值

  SELECT AVG(price) FROM product WHERE id = '01'; -- 计算id是'01'的price列平均值
  

• COUNT计算某列的行数

  SELECT COUNT(*) FROM user; -- 对行的数目进行统计，无论是否是NULL
  SELECT COUNT(username) FROM user; -- 对特定列进行行数统计，忽略NULL
  

• MAX返回某列最大值

  不光对数值类型，对文本类型也可以进行MAX

  忽略NULL值

  SELECT MAX(price) FROM  product; -- 返回price列中最大值
  

• MIN返回指定某列最小值，与MAX相对。忽略NULL值

• SUM返回指定列值得和

  忽略NULL

  SELECT SUM(price) FROM product; -- 返回price列值得和
  

聚集不同值

使用DISTINCT可以使得聚集函数只处理不同的值

DISTINCT只能用于列名，不能用于计算或表达式，也不能用于*

SELECT AVG(DISTINCT price) FROM product; -- 求不同的price值的平均值

3. 分组数据GROUP BY / HAVING

可以通过分组将数据分为多个逻辑组，然后对分组进行聚集计算从而完成汇总

3.1 创建分组

使用GROUP BY子句进行分组建立。

GROUP BY可以包含多个列，因此可以对分组进行嵌套，更细致的进行数据分组。

GROUP BY子句列出的每一列都必须是检索列或者有效的表达式（不能是聚集函数），如果在SELECT中使用表达式，就必须在GROUP BY子句中指定相同的表达式，不能使用别名

如果分组列中有NULL值，那么这些NULL值所在的行将被分为一个组

GROUP BY必须在WHERE之后，ORDER BY之前

SELECT id,COUNT(*) AS num_prods
FROM product
GROUP BY id;
-- 根据id进行分组，然后统计每个分组的行数
-- 对每个逻辑分组进行COUNT计算，而不是对整体进行COUNT计算。

SELECT id,name FROM product
GROUP BY id,name; -- 先按id列分组，再按name列分组

3.2 过滤分组 HAVING

HAVING类似于WHERE，即HAVING子句支持所有WHERE操作符（包括通配符和多个操作符的子句等），但是HAVING过滤的是分组而不是每一行数据。

即WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。WHERE过滤掉的行不会出现在分组这种。

如果不指定GROUP BY（即不分组），那么DBMS处理HAVING和处理WHERE一致

SELECT id,COUNT(*) AS num_prods
FROM product
GROUP BY id
HAVING COUNT(*) >= 2;
-- 过滤出COUNT(*) >= 2的分组

3.3 SELECT子句顺序

顺序	子句	说明
1	SELECT	要返回的列或表达式
2	FROM	从中检索数据的表
3	WHERE	行级过滤
4	GROUP BY	分组
5	HAVING	组级过滤
6	ORDER BY	排序顺序

4. 子查询

嵌套在其他查询中的查询。即为嵌套在子句中的SELECT语句

子查询只能用于查询单个列，企图检索多个列将返回错误

4.1 使用子查询进行过滤

在WHERE子句中使用子查询，从而灵活的设置过滤条件（通常配合WHERE IN使用）

SELECT cust_id FROM orders
WHERE order_num IN (SELECT order_num FROM orderitems
                    WHERE prod_id = '001');
-- 先执行WHERE子句中的子查询，然后以此结果作为过滤条件进行外层查询
-- 先检索orderitems表中prod_id为'001'的order_num列，然后将该结果作为过滤条件，检索orders表中order_num列满足过滤条件的cust_id列

4.2 作为计算字段使用子查询

SELECT name,
	   (SELECT COUNT(*) FROM orders
        WHERE orders.id = customers.id) AS orders
FROM customers;
-- 该语句返回两列：name和orders。其中orders是一个计算字段，由子查询建立。
-- 该子查询对每个检索出来的结果（行）都执行一次。即先进行外层查询，然后对于查询结果的每一行都进行子查询。

注意在这里没有直接使用列名，而是使用了完全限定列名：表名.列名

这样可以避免混淆，如上面的子查询如果不用完全限定列名：

SELECT COUNT(*) FROM orders WHERE id = id; -- DBMS认为是orders表中的id列自己比较，因此输出结果是错误的。

5. 联结表

可以通过联结将多个表的信息联系起来进行检索

通过where子句进行联结表

当使用where子句联结时，需要在from子句中列出要联结的所有表名，这一点与join有区别

SELECT vend_name,prob_name,prob_price	-- 要检索的列
FROM venders,products                  	-- 要联结的表名
where venders.id = products.id;			-- 联结条件

这个例子是等值联结，也成为内联结，基于两个表之间的相等测试。

上面的例子中，vend_name列在venders表中，而prob_name和prob_price列在products表中。这也是与普通select语句最大的区别，即要检索的列分别属于不同的表，要通过联结条件将它们联系起来检索出来。

在联结两个表时，实际做的是将第一个表中的每一行与第二个表中的每一行进行匹配（是否满足联结条件）。因此如果没有联结条件（没有where子句），那么检索出的行数就是笛卡尔积（如第一张表有m行，第二张表有n行，那么就返回m*n个结果，相当于两张表检索结果的排列组合）

可以通过INNER JOIN完成相同功能

格式：

FROM 表名
INNER JOIN 要联结的表名 ON 联结条件

如：

SELECT vend_name,prob_name,prob_price			-- 要检索的列
FROM venders
INNER JOIN products ON venders.id = products.id;-- 联结条件

可以联结多个表，而不止是2个表

SELECT prob_name,vend_name,prob_price,quantity
FROM orderitems,products,venders
WHERE products.vend_id = venders.vend_id
AND orderitems.prob_id = products.prob_id
AND order_num = 20007;

需要注意不要联结不必要的表，联结的表越多，性能下降越厉害

6. 高级联结

6.1 使用表别名：

也可以通过AS关键字对表起别名。

注意给表起的别名可用于在select子句、where子句、order by子句及其他部分

SELECT U.username 
FROM user as U 				-- 给表user起别名U
WHERE U.password > 'aaa';

6.2 自联结

自己跟自己联结

比如这样一个例子：在一张表中找出与TOM同一家公司的所有职员信息

SELECT c1.cust_id,c1.cust_name,c1.cust_contact
FROM customers AS c1,customers AS c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'TOM';

6.3 自然联结 natural join

自然连接(Natural join)是一种特殊的等值连接，它要求两个关系中进行比较的分量必须是相同的属性组，并且在结果中把重复的属性列去掉。而等值连接并不去掉重复的属性列。

“ 自然联结 ”不需要使用where 或者是 on 限定条件，它会自动根据字段的值加以匹配，这就是“自然”两个字的含义，即自发的匹配。

但是，自然联结这种自发的匹配并不是随意的。它必须有一个严格的限制条件，那就是两个表中必须要具有公共字段，而且这两个公共字段的名称必须一样，值得类型也必须一样，才能让其“ 自发 ”的匹配。即productinfo和vendors都有一个相同的字段vendname，且他们的类型一样才可以自发的联结。

内联结：

select p.*,v.*
from productinfo as p inner join vendors as v
on p.vendname=v.vendname;

自然联结：可以看出没有加on联结条件，而是根据列的字段值进行自发的匹配

select p.*,v.*
from productinfo as p natural join vendors as v

6.4 外联结 OUTER JOIN

许多联结将一个表中的行与另一个表中的行联结，但有时需要包含没有关联行的那些行，这时就需要用到外联结。

通过LEFT OUTER JOIN 或RIGHT OUTER JOIN完成外联结

如检索包括没有订单顾客在内的所有顾客

SELECT customer.cust_id,orders.order_num
FROM customers
LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;

通过LEFT和RIGHT决定外联结以哪个表为基础。LEFT是左边的表，RIGHT是右边的表。上面的例子中以customers表为基础，检索出它的所有行，即使在orders中没有检索到匹配的行，也会作为结果返回，不过orders.order_num列值为NULL。

6.5 使用带聚集函数的联结

SELECT customers.cust_id,COUNT(orders.order_num) AS num_ord
FROM customers
INNER JOIN orders ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;

使用INNER JOIN将customers表与orders表联结，GROUP BY子句按cust_id列分组数据，函数调用COUNT根据分组进行汇总计算并作为num_ord列返回。

7. 组合查询 UNION

使用UNION操作符将多个seletc语句检索结果组合成一个结果集

使用where的多过滤条件：

SELECT cust_name,cust_contact,cust_email
FROM customers
WHERE cust_state IN ('IN','IL','MI') OR cust_name = 'FUN4ALL';

使用UNION组合：

SELECT cust_name,cust_contact,cust_email
FROM customers
WHERE cust_state IN ('IN','IL','MI')
UNION
SELECT cust_name,cust_contact,cust_email
FROM customers
WHERE cust_name = 'FUN4ALL';

上面两种方式结果相同。UNION只是DBMS执行这两条SELECT语句，并将输出组合成一个查询结果集

UNION规则：

• UNION必须由两条或两条以上的SELECT语句组成
• UNION中的每个查询必须有相同的列、表达式或聚集函数（不过，不同的列不需要以相同的次序列出）。因为UNION会将多个SELECT语句的结果组合在一起，因此必须保证结果的形式（有哪些列）要一致。组合相当于扩展检索结果的行数
• 列数据类型必须兼容，即类型不必完全相同，但是DBMS可以隐式转换。
• 当UNION的不同SELECT子句中指定列有不同的列名，返回结果会使用第一个SELECT中的那个列名（由于只使用第一个列名，因此ORDER BY也只能使用第一个列名）
• UNION会从结果集中去除重复的行（比如第一个SELECT中的某个结果行与第二个SELECT中的某个结果行一致，那么只会保留一个）。如果想要返回所有的结果行（不取消重复的行），那么使用UNION ALL而不是UNION。
• 对组合结果排序，ORDER BY子句只能用于最后一个SELECT语句最后。但是注意，ORDER BY是对组合结果集进行排序，而不是对最后一个SELECT语句结果进行排序。