SQL 杂项知识点回顾

采自《SQL 基础教程》
涉及表

shohin

shohin2

tenposhohin

---

书写顺序：**SELECT** - FROM - WHERE - GROUP BY - HAVING - ORDER BY

执行顺序：FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY

---

只能使用 字母、数字、下划线 ( _ ) 作为数据库、表和列的名称，且 必须以字母开头

---

char / varchar

• char ：定长字符串，当列中字符串长度不达到最大长度时，用空格补充
• varchar ：可变长字符串，不会进行空格补充（对空间更友好）

---

给表新添加列

alter table <表名> add column <列定义>;

alter table shohin add column shohin_mei_kana varchar(100);

在表中删除列

alter table <表名> drop column <列名>;

alter table shohin drop column shohin_mei_kana;

---

变更表名

rename table <表名> to <新表名> 

---

去重 （DISTINCT）

DISTINCT只能用用在第一个列名之前

select distinct <列名> from <表名>

select distinct * from shohin

*如果有多条重复null值会保留一条null值

---

SQL 中语句书写是固定的，不能随意更改！（例如 where 必须紧跟在 from 后面）

---

SQL 中的运算符

• 算术运算符 （ + - * / ）
• 比较运算符 （ = < > >= <= != / <> ）
• 逻辑运算符 （ NOT AND OR )

---

SQL 语句中可以使用计算表达式（ + - * / ）

select shohin_mei, hanbai_tanka, hanbai_tanka * 2 as hanbai_tanka_x2
from shohin

需要注意的是，如果是包含 NULL 值的运算，结果肯定都为 NULL

5 + NULL -> NULL;	  10 - NULL -> NULL;	  15 * NULL -> NULL;	  20 / NULL -> NULL;

---

不能对 NULL 使用比较运算符

例 ：查询表中 shiire_tanka 为空的记录

-- X 这是错的！虽然不会报错，但查询不出任何结果！
select *
from shohin
where shiire_tanka = null

对 NULL值判断需要使用NULL专用的运算符 IS NULL / IS NOT NULL

-- ✔ 正确用法~
select *
from shohin
where shiire_tanka is null

---

使用逻辑运算符时也要特殊对待 NULL 值

叉子和圆珠笔的进货价格都为 NULL，那么当我们使用查询条件 shiire_tanka = 2800 会得到什么真值？

真值既不是真，也不是假。而是 SQL 中特有的一种真值 —— UNKNOWN （不确定）

与通常的逻辑运算被称为二值逻辑（TRUE or FALSE）相对，SQL 的逻辑运算被称为三值逻辑（TRUE or FALSE or UNKNOWN）

其部分真值表：

P	Q	P AND Q
真	不确定	不确定
假	不确定	假
不确定	不确定	不确定

P	Q	P OR Q
真	不确定	真
假	不确定	不确定
不确定	不确定	不确定

在逻辑运算符中，AND 优先级高于 OR

---

聚合函数

聚合函数会将 NULL 值排除在外。但 COUNT(*) 例外，不会排除 NULL 值
只有 SELECT 和 HAVING 子句、 ORDER BY 子句 中能够适合用聚合函数

5 个常用聚合函数

1. COUNT 计算表中的记录数

2. SUM 计算表中数值列的总和

3. AVG 计算表中数值列的平均值

4. MAX 求出表中任意列的中数据最大值

5. MIN 求出表中任意列的中数据最小值

​ COUNT 函数结果会根据参数的不同而不同。COUNT(*) 会得到包含 NULL 的数据行数，而 COUNT(<列名>) 会得到 NULL 之外的数据行数

---

​ AVG 函数会事先删除 NULL 再进行计算。也就是说值为 NULL 的行并没有权重

... avg(shiire_tanka)

(500 + 32 + 2800 + 2800 + 5000 + 790) / 6

---

分组 （GROUP BY）

​ GROUP BY 子句中指定的键称为 聚合键 或 分组列

​ 当聚合键中包含 NULL 时，再结果中会以 不确定(空行) 的形式表现出来

select shiire_tanka, count(*)
from shohin
group by shiire_tanka

使用 GROUP BY 子句时，SELECT 子句中不能出现聚合键之外的列名。原因很简单，聚合键和非聚合键之间的关系并不一定时一对一的

例如，在上诉查询中增加一列 shohin_mei

select shohin_mei,shiire_tanka, count(*)
from shohin
group by shiire_tanka

此时，shiire_tanka 为 2800 的商品有2个，那么 shohin_mei 应该为其中的哪一个呢？并不知道

虽然 MySQL 中这样的语法也得以执行，不会报错，但其他的DBMS支持这样的语法。总的来说，不能这样做

在 GROUP BY 子句中写别的列的别名，虽然在MySQL中支持这种语法，但不推荐这样做

select shiire_tanka as st, count(*)
from shohin
group by st

GROUP BY 分组的的结果时无序的

HAVING 子句用来指定分组条件，紧跟在 GROUP BY 子句后面

SELECT <列名1>，<列名2> ...
FROM <表名>
GROUP BY <列名1>，<列名2> ...
HAVING <分组结果响应条件>

HAVING子句能够使用的三要素

• 常数
• 聚合函数
• GROUP BY 中指定的列名（即聚合键）

​ 有些条件我们既可以写在 WHERE 子句中，也可以写在 HAVING 子句中，这些条件就是聚合键所对应的条件。推荐写在 WHERE 子句中，原因之一就是这样可以使性能更高

---

排序 （ ORDER BY )

• 使用 ORDER BY 子句对查询结果进行排序

• 在 ORDER BY 子句中类名后面可以使用关键字 ASC 指定升序排序，使用 DESC 指定降序排序（不写默认 ASC）

• ORDER BY 子句中可以指定多个排序键

• 排序键包含 NULL 时，会在结果的开头或末尾汇总（MySQL 在开头汇总）
  

• ORDER BY 子句中可以使用 SELECT 子句中定义的列别名

---

数据插入 ( INSERT )

INSERT INTO <表名> (列名1, 列名2, 列名3 ...) VALUES (值1, 值2, 值3 ...);

如果值清单包含了所有列，则可以省略列清单

-- 值清单包含所有列
INSERT INTO <表名> VALUES (值1, 值2, 值3 ...);

还可以使用其配合 SELECT 来完成表复制功能

-- 将 shohin 表内容拷贝到 shohinCopy 表
INSERT INTO shohinCopy (shohin_id, shohin_mei, shohin_hunrui, hanbai_tanka, shiire_tanka, torokubi)
SELECT shohin_id, shohin_mei, shohin_hunrui, hanbai_tanka, shiire_tanka, torokubi
FROM  shohin;

---

数据删除 （ DELETE ）

数据删除答题可分两种

• DROP TABLE 语句，将表完全删除
• DELETE 语句，将表数据删除，会留下表

DELETE 基本使用

DELETE FROM <表名>
WHERE ...

---

#### 数据更新 （ UPDATE ）

UPDATE 基本使用

UPDATE <表名>
SET <列名> = <表达式>,
	<列名> = <表达式>,
	<列名> = <表达式>，
	...
WHERE ...

---

MySQL 中开启/提交/回滚事务

START TRANSACTION

COMMIT

ROLLBACK

---

视图

​ 表是真实存在的数据，占用计算机物理空间，是存储在我们计算机存储设备（硬盘）中的

​ 视图是虚拟表，不是真正存在的物理表。其本质是已编译好的 SELECT 语句

​

创建视图的语句

CREATE VIEW 视图名 (<视图列名1>, <视图列名2>, <视图列名3> ...)
AS
<SELECT 语句>

例：

CREATE VIEW  shohinSum (shohin_bunrui, cnt_shoin)
AS
SELECT shohin_bunrui, count(*)
FROM shohin
GROUP BY shohin_bunrui

使用该视图查询数据

select * from shohinsum

---

子查询

子查询作为内层查询会先执行

案例

-- 查询出售价单价高于平均售价单价的商品
SELECT *
FROM shohin
WHERE hanbai_tanka > (SELECT AVG(hanbai_tanka)
											FROM shohin);
											

我们能够在几乎所有地方使用标量子查询（标量子查询：只能返回一行或一列的子查询）

---

关联子查询

• 关联子查询会在细分的组内进行比较时使用
• 关联子查询和 GROUP BY 子句一样，也可以对表中数据进行切分
• 关联子查询的结合条件如果未出现在子查询中就会发送错误

例：我们需要查询各商品分类中高于该分类平均销售单价的商品

SELECT *
FROM shohin AS s1
WHERE hanbai_tanka > (SELECT AVG(hanbai_tanka)
					  FROM shohin AS s2
					  WHERE s1.shohin_bunrui = s2.shohin_bunrui -- 结合条件
					  GROUP BY shohin_bunrui);

---

EXIST

EXISTS 运算符用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False。

• 通常指定关联子查询为 EXIST 的参数
• EXIST 只关心记录是否存在，所以返回那哪列无所谓

案例：

-- 查询大阪店(000C)在售商品(shohin_id)的销售单价(hanbai_tanka)
SELECT shohin_mei, hanbai_tanka
FROM shohin AS s
WHERE EXISTS (SELECT *
			  FROM tenposhohin AS ts
			  WHERE ts.tenpo_id = '000C'
		      AND ts.shohin_id = s.shohin_id);

---

CASE 表达式

CASE WHEN <判断表达式> THEN <表达式>
	 WHEN <判断表达式> THEN <表达式>
	 WHEN <判断表达式> THEN <表达式>
	 .
	 .
	 ELSE <表达式>
END

-- 类似与 Java 中的 if(..){...} else if (..){...} else {...}

• ELSE 语句可以省略不写，会自动默认 ELSE NULL

---

UNION 并集

• 集合运算符会除去重复的记录
• 在集合运算中使用 ALL 可以保留重复记录（ UNION ALL ）
• 作为集合运算对象的记录的 列数 和 类型 必须相同
• 可以使用任何 SELECT 语句，但 ORDER BY 子句只能使用一次

SELECT shohin_id, shohin_mei
FROM shohin
UNION
SELECT shohin_id, shohin_mei
FROM shohin2

---

联结 / 连接 （ JOIN ）

• 连接就是将其他表中的列添加过来，进行 “列添加” 的集合运算

• UNION 以 行 为单位进行连接，JOIN 以 列 为单位进行连接

  

INNER JOIN ( 内连接 )

• SELECT 子句中的列需要按照 <表名/表别名>.<列名> 的格式书写 （ 如果列是表独有则不用 ）
• 连接条件 ON 子句要在 FORM 和 WHERE 之间

SELECT ts.tenpo_id, ts.tenpo_mei, ts.shohin_id, s.shohin_mei, s.hanbai_tanka
FROM tenposhohin AS ts INNER JOIN shohin as s
ON ts.shohin_id = s.shohin_id

OUTER JOIN （ 外连接 ）

SELECT ts.tenpo_id, ts.tenpo_mei, ts.shohin_id, s.shohin_mei, s.hanbai_tanka
FROM tenposhohin AS ts RIGHT OUTER JOIN shohin as s
ON ts.shohin_id = s.shohin_id