SQL语法学习---第2天

前言

该系列是自己学习sql语法的系列部分，记录了为期4天的笔记内容
笔记内容供个人后续查阅资料用（虽然预计会吃灰）
来了就安静看，悄悄来，悄悄走
参考文章：

• 菜鸟教程-SQL教程

2022-04-21 日报总结

目标

• 完成SQL “改”练习，即UPDATE语句
• 完成SQL “删”练习，即DELETE语句

关于SQL

初始化的表：

用于后文做对比

SQL “改”

UPDATE 语句

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

• update [表名]：“改”某个表
• set column_name=value：设置该列的值为value
• where：满足某种条件的记录

Tips:

注意 SQL UPDATE 语句中的 WHERE 子句！

WHERE 子句规定哪条记录或者哪些记录需要更新。

如果您省略了 WHERE 子句，所有的记录都将被更新！

实践

UPDATE websites 
SET alexa='5000', country='USA' 
WHERE name='菜鸟教程';

把 “菜鸟教程” 的 alexa 排名更新为 5000，country 改为 USA。

结果

其他：

	- **alexa**：英文为“排名”的意思
	- 注意sql语句中的中英文问题：如：逗号，分号等
	- 注意sql语句中的字段类型问题：如：int，string等
	- 对于没有where子句的sql语句要谨慎运行，因为他会更改所有记录项

---

SQL “删”

DELETE 语句

DELETE 语句用于删除表中的记录(即行)。

DELETE FROM table_name
WHERE some_column=some_value;

• delete from [表名]：“删”某个表中的记录，而不是该表；
• drop关键字为删除表整体
• 增加where子句是为了避免删除所有记录

Tips:

注意 SQL DELETE 语句中的 WHERE 子句！
WHERE 子句规定哪条记录或者哪些记录需要删除。

如果省略了 WHERE 子句，所有的记录都将被删除！

实践

DELETE FROM websites
WHERE name='Facebook' AND country='USA';

从 “websites” 表中删除网站名为 “Facebook” 且国家为 USA 的网站。

结果

运行前：

运行后：

其他：

清空表所有可使用如下sql语句：

DELETE FROM table_name;

Tips:删除动作需谨慎，确认是否有删除的需要，因为无法撤销

---

SQL高级查询

sql最重要的高级查询，至少对于我来说，它的一些高级查询是很重要的（也是个人的知识盲点部分qaq），因为复杂的业务场景往往存在各种数据的关联查询，满足某种复杂查询条件的需求；

高级查询简单的理解为复杂查询，已知的情况有如下

• 多个表的关联查询
• 多个查询条件的组合查询

SELECT TOP, LIMIT, ROWNUM 子句

SELECT LIMIT

解答疑惑

重点

注意:

• 并非所有的数据库系统都支持 SELECT TOP 语句。

• MySQL 支持 LIMIT 语句来选取指定的条数数据，

• Oracle 可以使用 ROWNUM 来选取。

SQL Server / MS Access 语法

SELECT TOP number|percent column_name(s)
FROM table_name;

• percent 表示百分比，是个关键字

• 从 websites 表中选取前面百分之 50 的记录：

  SELECT TOP 50 PERCENT * FROM Websites;
  

MySQL 语法(常用数据库系统，重点学这个)

SELECT column_name(s)
FROM table_name
LIMIT number;

Oracle 语法

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;

实践

SELECT * FROM Websites LIMIT 2;

从 “Websites” 表中选取头两条记录：

结果

---

LIKE 操作符

LIKE 操作符

LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。

语法

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

模式描述如下：

Select * from emp where ename like 'M%';

​ 查询 EMP 表中 Ename 列中有 M 的值，M 为要查询内容中的模糊信息。

• % 表示多个字值，_ 下划线表示一个字符；
• M% : 为能配符，正则表达式，表示的意思为模糊查询信息为 M 开头的。
• %M% : 表示查询包含M的所有内容。
• %M_ : 表示查询以M在倒数第二位的所有内容。

实践

SELECT * FROM Websites
WHERE name LIKE 'G%';

选取 name 以字母 “G” 开始的所有客户：

结果

其他

通过使用 NOT 关键字，您可以选取不匹配模式的记录。

SELECT * FROM Websites
WHERE name NOT LIKE '%oo%';

选取 name 不包含模式 “oo” 的所有客户：

小笔记：

• 以M开始：M%
• 以M结束：%M
• 包含M：%M%
• Not 不匹配某种模式

---

SQL 通配符

SQL 通配符

• 通配符可用于替代字符串中的任何其他字符。
• 在 SQL 中，通配符与 SQL LIKE 操作符一起使用。
• SQL 通配符用于搜索表中的数据。

在 SQL 中，可使用以下通配符：

通配符	描述
%	替代 0 个或多个字符
_	替代一个字符
[charlist]	字符列中的任何单一字符
[^charlist] 或 [!charlist]	不在字符列中的任何单一字符

• 后面2个可以理解为元素是否在数组中，而改数组特殊之处在于，各个元素之间没有逗号隔开。
• 后2个都是对单一一个字符进行匹配。
• 后2个属于属于正则表达式的范畴，所以在mysql中需配合REGEXP，NOT REGEXP 关键字，而不是LIke关键字。

因为前面“%”符号已经了解过，这里重点了解剩余3种通配符

---

使用 SQL _ 通配符

实践

SELECT * FROM Websites
WHERE name LIKE 'G_o_le';

选取 name 以 “G”、“F” 或 “s” 开始的所有网站：

结果

---

使用 SQL [charlist] 通配符

MySQL 中使用 REGEXP 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE) 来操作正则表达式。

实践1

SELECT * FROM Websites
WHERE name REGEXP '^[GFS]';

选取 name 以 “G”、“F” 或 “s” 开始的所有网站：

• 该例子中表达式为：^[GFC]而不是[^GFC]
• 前者^表示以[]中的某个字符为起始字符

结果1

实践2

SELECT * FROM Websites
WHERE name REGEXP '^[^GFS]';

选取 name 不以 “G”、“F” 或 “s” 开始的所有网站：

• 第1个^符号，以x开头
• 第2个^表示非逻辑，即不为G，不为F，不为S

结果2

实践3

SELECT * FROM Websites
WHERE name REGEXP '[^GFS]';

选取 name 不包含 “G”、而包含"F" 或 "s"或“其他” 开始的所有网站：

• 第1个^符号，即不为G

结果3

---

IN 操作符

IN 操作符

IN 操作符允许您在 WHERE 子句中规定多个值。

语法

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);

• IN 后面用圆括号来包裹需要比较的元素

实践

SELECT * FROM Websites
WHERE name IN ('Google','菜鸟教程');

选取 name 为 “Google” 或 “菜鸟教程” 的所有网站：

结果

---

BETWEEN 操作符

BETWEEN 操作符

BETWEEN 操作符用于选取介于两个值之间的数据范围内的值。

注意：

	- 包含上下限的值
	- 与and配合，如：`between min and max`

语法

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

实践

SELECT * FROM Websites
WHERE alexa BETWEEN 1 AND 20;

选取 alexa 介于 1 和 20 之间的所有网站：

结果

带NOT关键字

不在数据范围之间的值

实践

SELECT * FROM Websites
WHERE alexa NOT BETWEEN 1 AND 20;

选取 alexa 介于 1 和 20 之外的所有网站：

结果

与IN 操作符 配合使用

实践

SELECT * FROM Websites
WHERE (alexa BETWEEN 1 AND 20)
AND country NOT IN ('USA', 'IND');

选取 alexa 介于 1 和 20 之间但 country 不为 USA 和 IND 的所有网站：

• 表达式前面的条件使用圆括号包裹起来了

结果

带有文本值的 between 操作符实例

between min and max 中：

• min，max 不一定是数值，也可以是文本
• 如果min=‘A’,max=“H”

实践

SELECT * FROM Websites
WHERE name BETWEEN 'A' AND 'H';

选取 name 以介于 ‘A’ 和 ‘H’ 之间字母开始的所有网站：

结果

举一反三

• between 关键字 还可以比较比较日期，从而筛选出满足某个日期范围内的所有数据，在日志分析中很常见

• 不仅仅是数值可以用来比较，文本值也是可以用来比较的

---

SQL 别名

SQL 别名

• 通过使用 SQL，可以为表名称或列名称指定别名。

• 基本上，创建别名是为了让列名称的可读性更强。

• AS 关键字

• 常见的场景为：多个表联合查询时，2个表中存在相同的列名，因此需要使用别名区分列名

列的 SQL 别名语法

AS关键字紧跟在列名后

SELECT column_name AS alias_name
FROM table_name;

表的 SQL 别名语法

AS关键字紧跟在表名后

SELECT column_name(s)
FROM table_name AS alias_name;

列的别名实例

**提示：**如果列名称包含空格，要求使用双引号或方括号：

实践

SELECT name AS n, country AS c
FROM Websites;

该SQL 语句指定了两个别名，一个是 name 列的别名，一个是 country 列的别名

结果

实践

关键字：concat

SELECT name, CONCAT(url, ', ', alexa, ', ', country) AS site_info
FROM Websites;

把三个列（url、alexa 和 country）结合在一起，并创建一个名为 “site_info” 的别名：

结果

表的别名实例

现在涉及到2个表了

“Websites” 和 “access_log” 表

实践

SELECT w.name, w.url, a.count, a.date
FROM Websites AS w, access_log AS a
WHERE a.site_id=w.id and w.name="菜鸟教程";

语句选取 “菜鸟教程” 的所有访问记录

结果

其他

在下面的情况下，使用别名很有用：

• 在查询中涉及超过一个表
• 在查询中使用了函数
• 列名称很长或者可读性差
• 需要把两个列或者多个列结合在一起

---

连接(JOIN)

• JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。
• 最常见的 JOIN 类型：SQL INNER JOIN（简单的 JOIN）。 SQL INNER JOIN 从多个表中返回满足 JOIN 条件的所有行。
• 配合关键字 ON

下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法。

已知“websites”表和“access_log”表，“Websites” 表中的 “id” 列指向 “access_log” 表中的字段 “site_id”。上面这两个表是通过 “site_id” 列联系起来的。

实践

SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites
INNER JOIN access_log
ON Websites.id=access_log.site_id;

结果

不同的 SQL JOIN

在我们继续讲解实例之前，我们先列出您可以使用的不同的 SQL JOIN 类型：

• INNER JOIN：如果表中有至少一个匹配，则返回行
• LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
• RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
• FULL JOIN：只要其中一个表中存在匹配，则返回行

小结：

• inner join 为一种连接方式，总共有7种
• on 关键字为2个表的衔接点，即纽带
• 带方向（left right）的连接，可以简单的记为哪方要就需返回哪方的行
• inner 表示2个表中都有
• full 表示其中一个表有

---

INNER JOIN 关键字

INNER JOIN 关键字在表中存在至少一个匹配时返回行。

**注：**INNER JOIN 与 JOIN 是相同的。

语法

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

或：

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;

实践

SELECT Websites.name, access_log.count, access_log.date
FROM Websites
INNER JOIN access_log
ON Websites.id=access_log.site_id
ORDER BY access_log.count;

返回所有网站的访问记录,并通过访问次数排序

结果

注：

• INNER JOIN 关键字在表中存在至少一个匹配时返回行。

• 如果 “Websites” 表中的行在 “access_log” 中没有匹配，则不会列出这些行。

• inner join 等价于 join

---

LEFT JOIN 关键字

LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。

如果右表中没有匹配，则结果为 NULL。

**注释：**在某些数据库中，LEFT JOIN 称为 LEFT OUTER JOIN。

语法

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

或：

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

实践

SELECT Websites.name, access_log.count, access_log.date
FROM Websites
LEFT JOIN access_log
ON Websites.id=access_log.site_id
ORDER BY access_log.count DESC;

将返回所有网站及他们的访问量（如果有的话）。

结果

小结

到这里对 join，与 left join 有一些新的理解

• join 仅会返回2个表中都存在的记录项
• left join 解释：
  • 左表与右表当id=site_id时的数据项A
  • 左表中在右表中未对应出的数据项B
  • 数据项综合为A+B
• 数据项总数举例：
  • 比如右表中数据项有9条，其中与左表对应的有7条
  • 左表数据共有8条，未匹配4条
  • 合计7+4=11条
  • 右匹配的所有+左未匹配
• 右表没有对应的记录也会显示

右匹配所有+左未匹配

小结

• 增删改查对应

  • insert into
  • delete from
  • upadte
  • select

• 其中 “增”和“删” 都先接了介词（into ，from），“改”和“查”直接接了名词（表名）

• sql语句在sql编辑器中书写时可以分行写，这样结构更直观，逻辑更清晰

• # 查询 site_id 列中的不同值
  SELECT DISTINCT site_id
  FROM access_log;