前言
该系列是自己学习sql语法的系列部分,记录了为期4天的笔记内容
笔记内容供个人后续查阅资料用(虽然预计会吃灰)
来了就安静看,悄悄来,悄悄走
参考文章:
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;