SQL语法学习---第4天

前言

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

• 菜鸟教程-SQL教程

2022-04-24 日报总结

学习SQL VIEW（视图）及余下部分

目标

• RIGH
• FUL

SQL 视图（Views）

视图是可视化的表。

作用：

1. 视图隐藏了底层的表结构，简化了数据访问操作，客户端不再需要知道底层表的结构及其之间的关系。

2. 视图提供了一个统一访问数据的接口。（即可以允许用户通过视图访问数据的安全机制，而不授予用户直接访问底层表的权限）

3. 从而加强了安全性，使用户只能看到视图所显示的数据。

4. 视图还可以被嵌套，一个视图中可以嵌套另一个视图。

本节内容

• 创建：CREATE VIEW
• 更新：CREATE OR REPLACE VIEW
• 删除：DROP VIEW

SQL CREATE VIEW 语句

语法：

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

• 视图总是显示最新的数据！每当用户查询视图时，数据库引擎通过使用视图的 SQL 语句重建数据。

实践：

CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No

视图 “Current Product List” 会从 “Products” 表列出所有正在使用的产品（未停产的产品）。

结果：

由于没有样本数据库，因此在工具中无法复现该结果；

• create view views_name as ：创建一个视图。
• 视图的名字使用 英文的方括号包裹起来了。
• 后面常规的查询语句则将满足条件的数据查询出来，作为视图的内容。

其他：

上一步的操作仅仅是创建一个视图，如果要查询该视图，那么使用如下语句

SELECT * FROM [Current Product List]

视图嵌套的效果：

CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName

• 视图名为：“Category Sales For 1997”的数据来源于视图名为“Product Sales for 1997”的视图

查询该嵌套视图数据：

SELECT * FROM [Category Sales For 1997]

也可以基于视图再追加条件：

SELECT * FROM [Category Sales For 1997]
WHERE CategoryName='Beverages'

SQL 更新视图

SQL CREATE OR REPLACE VIEW 语法

语法：

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

实践：

CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No

向 “Current Product List” 视图添加 “Category” 列

• 对于原有的视图名称没有改变
• 而视图的数据来源的增加了新的列

结果：

略

SQL 撤销(删除)视图

SQL DROP VIEW 语法

语法：

DROP VIEW view_name

---

SQL Date 函数

• 只要您的数据包含的只是日期部分，运行查询就不会出问题。
• 但是，如果涉及时间部分，情况就有点复杂了。

重点：数据中包含时间的部分，查询起来就会变得复杂

MySQL Date 函数

下面的表格列出了 MySQL 中最重要的内建日期函数：

函数	描述
NOW()	返回当前的日期和时间
CURDATE()	返回当前的日期
CURTIME()	返回当前的时间
DATE()	提取日期或日期/时间表达式的日期部分
EXTRACT()	返回日期/时间的单独部分
DATE_ADD()	向日期添加指定的时间间隔（间隔的年月日）
DATE_SUB()	从日期减去指定的时间间隔（间隔的年月日）
DATEDIFF()	返回两个日期之间的天数
DATE_FORMAT()	用不同的格式显示日期/时间

SQL Date 数据类型

MySQL 使用下列数据类型在数据库中存储日期或日期/时间值：

• DATE - 格式：YYYY-MM-DD
• DATETIME - 格式：YYYY-MM-DD HH:MM:SS
• TIMESTAMP - 格式：YYYY-MM-DD HH:MM:SS
• YEAR - 格式：YYYY 或 YY

**注：**当您在数据库中创建一个新表时，需要为列选择数据类型！

结果：

创建一个表，在创建该表时注意：

• Order 是个关键字，因此不能直接作为表名
• 使用工具写sql语句时，关键字会被高亮显示

准备实践所用数据

insert into Orders
values
(1,"Geitost","2008-11-11 13:23:44"),
(2,"Camembert Pierrot","2008-11-09 15:45:21"),
(3,"Mozzarella di Giovanni","2008-11-11 11:12:01"),
(4,"Mascarpone Fabioli","2008-10-29 14:56:59");

该语句中有一次性插入多条数据的用法

数据结果：

注意：

这个步骤中的诡异的部分来了，因为在创建表的时候设置的字段类型是：DATE，所以插入的数据中，带时间的部分直接被砍掉了

复制该表，并改变字段类型为 DATETIME,插入测试数据：

-- 复制表orders  ->  orders_fade
create table Orders_fade 
select * from Orders;

-- 改变orders_fade 的字段类型
alter table Orders_fade
modify OrderDate DATETIME;

-- 向 Orders_fade表中插入练习用的数据
insert into Orders_fade
values
(1,"Geitost","2008-11-11 13:23:44"),
(2,"Camembert Pierrot","2008-11-09 15:45:21"),
(3,"Mozzarella di Giovanni","2008-11-11 11:12:01"),
(4,"Mascarpone Fabioli","2008-10-29 14:56:59");

（给自己点个赞，这些全程自己手写，说明前天的还没忘干净，继续加油加油！）

验证数据中带时间部分的复杂性

SELECT * FROM Orders WHERE OrderDate='2008-11-11'

测试第2种

综上：

​ 关于文档中所说的带时间部分的复杂性，我的理解如下：

到目前为止，个人觉得还谈不上复杂，需要注意以下几点

• 创建表的时候，要清楚的知道这个字段到底存的是日期，还是日期时间

• 查询该表的时候如果是日期时间，那么查询条件一定要写全，必须包含时间部分，不能忽略

• 借用文档忠告：

  ​ 如果您希望使查询简单且更易维护，那么请不要在日期中使用时间部分！

---

SQL NULL 值

NULL 值

NULL 值代表遗漏的未知数据。

默认地，表的列可以存放 NULL 值。

本章讲解 IS NULL 和 IS NOT NULL 操作符。

重点：

• 无法使用比较运算符来测试 NULL 值，比如 =、< 或 <>。
• 必须使用 IS NULL 和 IS NOT NULL 操作符。
• 无法比较 NULL 和 0；它们是不等价的。

示例：

断言空

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL

断言非空

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NOT NULL

其他：

数据库建表的时候默认是 NULL，但在工作中一般建表的时候都会禁止使用 NULL 的！

为什么工作中不使用 NULL？

1. 不利于代码的可读性和可维护性，特别是强类型语言，查询 INT 值，结果得到一个 NULL，程序可能会奔溃…如果要兼容这些情况程序往往需要多做很多操作来兜底
2. 若所在列存在 NULL 值，会影响 count()、 <col> != <value>、 NULL + 1 等查询、统计、运算情景的结果

---

SQL ISNULL()、NVL()、IFNULL() 和 COALESCE() 函数

微软的 ISNULL() 函数用于规定如何处理 NULL 值。

NVL()、IFNULL() 和 COALESCE() 函数也可以达到相同的结果。

在这里，我们希望 NULL 值为 0。

下面，如果 “UnitsOnOrder” 是 NULL，则不会影响计算，因为如果值是 NULL 则 ISNULL() 返回 0：

这些函数共同的作用：

用于处理当数据中出现NULL值时，而做的处理

针对MySQL

SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
FROM Products

或：

SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))
FROM Products

即：当出现NULL时，该值被当作0来处理

---

SQL 通用数据类型

和其他编程语言一样，数据对字段也有不同的数据类型声明

详情直接参考菜鸟该章节，

• SQL 通用数据类型
• 用于各种数据库的数据类型

---

SQL 函数

SQL 拥有很多可用于计数和计算的内建函数。

SQL Aggregate 函数

SQL Aggregate 函数计算从列中取得的值，返回一个单一的值。

• 是一个通用的说法，比如人类中有具体的男人，女人，下方的Scalar是一样的
• Aggregate：总数的;总计的
• scalar： 纯量的;标量的;无向量的（adj），数量，标量（n）

有用的 Aggregate 函数：

• AVG() - 返回平均值
• COUNT() - 返回行数
• FIRST() - 返回第一个记录的值
• LAST() - 返回最后一个记录的值
• MAX() - 返回最大值
• MIN() - 返回最小值
• SUM() - 返回总和

SQL Scalar 函数

SQL Scalar 函数基于输入值，返回一个单一的值。

意思同上

有用的 Scalar 函数：

• UCASE() - 将某个字段转换为大写
• LCASE() - 将某个字段转换为小写
• MID() - 从某个文本字段提取字符，MySql 中使用
• SubString(字段，1，end) - 从某个文本字段提取字符
• LEN() - 返回某个文本字段的长度
• ROUND() - 对某个数值字段进行指定小数位数的四舍五入
• NOW() - 返回当前的系统日期和时间
• FORMAT() - 格式化某个字段的显示方式

**提示：**在下面的章节，我们会详细讲解 Aggregate 函数和 Scalar 函数。

---

SQL Aggregate 函数

AVG() 函数

AVG() 函数返回数值列的平均值。

将某列下的所有值相加，计算其平均值

语法：

SELECT AVG(column_name) FROM table_name

实践：

SELECT AVG(count) AS CountAverage FROM access_log;

从 “access_log” 表的 “count” 列获取平均值：

结果：

COUNT() 函数

COUNT() 函数返回匹配指定条件的行数。

语法：

SELECT COUNT(column_name) FROM table_name;

实践：

以下返回所有记录和，包含了重复记录项

SELECT COUNT(site_id) AS nums FROM access_log;

以下返回所有记录和，不包含重复记录项

SELECT COUNT(DISTINCT site_id) AS nums 
FROM access_log;

结果：

包含重复

不包含重复

---

FIRST() 函数

FIRST() 函数返回指定的列中第一个记录的值。

语法：

SELECT FIRST(column_name) FROM table_name;

注意：

只有 MS Access 支持 FIRST() 函数。mysql并不支持，因此替代性方案为：

使用limit关键字,配合ASC

SELECT column_name FROM table_name
ORDER BY column_name ASC
LIMIT 1;

实践：

SELECT name FROM websites
ORDER BY id ASC
LIMIT 1;

从 “websites” 表中通过id升序，取得第一项

结果：

---

LAST() 函数

LAST() 函数返回指定的列中最后一个记录的值。（返回一个数值）

语法：

SELECT LAST(column_name) FROM table_name;

注意：

只有 MS Access 支持 LAST() 函数。mysql并不支持，因此替代性方案为：

使用limit关键字,配合DESC

SELECT column_name FROM table_name
ORDER BY column_name DESC
LIMIT 1;

实践：

SELECT name FROM Websites
ORDER BY id DESC
LIMIT 1;

从 “websites” 表中通过id倒序，取得第一项，即（升序的最后一项）

结果：

略

---

MAX() 函数

MAX() 函数返回指定列的最大值。

语法：

SELECT MAX(column_name) FROM table_name;

实践：

SELECT MAX(alexa) AS max_alexa FROM websites;

从 “Websites” 表的 “alexa” 列获取最大值：

结果：

---

MIN() 函数

MIN() 函数返回指定列的最小值。（返回一个数值）

语法：

SELECT MIN(column_name) FROM table_name;

实践：

SELECT MIN(alexa) AS min_alexa FROM websites;

从 “Websites” 表的 “alexa” 列获取最小值：

结果：

---

GROUP BY 语句

这是个语句，而不是函数

GROUP BY 语句可结合一些聚合函数来使用

GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。

语法：

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

实践：

SELECT site_id, SUM(access_log.count) AS nums
FROM access_log GROUP BY site_id;

统计 access_log 各个 site_id 的访问量：

原本的表只是记录了每次访问的记录（即：单条访问记录）

前一个site_id 不写的话，结果不会报错，只是不会显示site_id列，但都正确地进行了分组

自己的理解：

• 如果没有进行分组的情况：那么只返回一个nums列，并且只有一个记录项
• 分组之后，根据不同的site_id,统计了不同site_id下的访问总数
• 所以：group by 所接的 字段，即为要被分组的字段，是个参照字段，参照该字段进行分组。

结果：

---

SUM() 函数

SUM() 函数返回数值列的总数。

• sum()：统计数据值的总和
• count()：统计记录项个数总和

语法：

SELECT SUM(column_name) FROM table_name;

实践：

SELECT SUM(count) AS nums FROM access_log;

查找 “access_log” 表的 “count” 字段值的总数：

结果：

---

GROUP BY 语句

这是个语句，而不是函数

GROUP BY 语句可结合一些聚合函数来使用

GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。

语法：

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

实践：

SELECT site_id, SUM(access_log.count) AS nums
FROM access_log GROUP BY site_id;

统计 access_log 各个 site_id 的访问量：

原本的表只是记录了每次访问的记录（即：单条访问记录）

前一个site_id 不写的话，结果不会报错，只是不会显示site_id列，但都正确地进行了分组

自己的理解：

• 如果没有进行分组的情况：那么只返回一个nums列，并且只有一个记录项
• 分组之后，根据不同的site_id,统计了不同site_id下的访问总数
• 所以：group by 所接的 字段，即为要被分组的字段，是个参照字段，参照该字段进行分组。

结果：

其他：

• 不仅仅是搭配SUM函数

• 像COUNT等其他函数也是可以的

  

多表连接的情况

SELECT websites.name,COUNT(access_log.aid) AS nums 
FROM access_log
LEFT JOIN websites
ON access_log.site_id=websites.id
GROUP BY websites.name;

• 左连接：左未匹配数+右匹配所有 Ln+Rm
• 连接只是基于2表中不同字段的相同值，做了数据项的集合运算

统计有记录的网站的记录数量：

---

SQL HAVING 子句

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。

HAVING 子句可以让我们筛选分组后的各组数据。

个人理解：

• 对原有的数据基础之上再做一次数据筛选
• Having 之后能接聚合函数，且写在group by 之后

语法：

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

实践：

SELECT Websites.name, SUM(access_log.count) AS nums FROM websites
INNER JOIN access_log
ON websites.id=access_log.site_id
WHERE websites.alexa < 200 
GROUP BY websites.name
HAVING SUM(access_log.count) > 200;

查找总访问量大于 200 的网站。

结果：

其他：

where 和having之后都是筛选条件，但是有区别的：

1.where在group by前， having在group by 之后

2.聚合函数（avg、sum、max、min、count），不能作为条件放在where之后，但可以放在having之后

---

SQL EXISTS 运算符

EXISTS 运算符用于判断查询子句是否有记录，

如果有一条或多条记录存在返回 True，否则返回 False。

最终全部语句会返回记录项

语法：

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

实践：

SELECT w.name, w.url 
FROM websites AS w
WHERE EXISTS (
  SELECT count FROM access_log AS a 
  WHERE w.id = a.site_id AND count > 200);

查找总访问量(count 字段)大于 200 的网站是否存在。

结果：

---

SQL Scalar 函数

SQL Scalar 函数基于输入值，返回一个单一的值。

SQL UCASE() 和 LCASE() 函数

UCASE() 函数把字段的值转换为大写。

LCASE() 函数把字段的值转换为小写。

upper：大写

lower：小写

语法：

UCASE():

SELECT UCASE(column_name) FROM table_name;

LCASE():

SELECT LCASE(column_name) FROM table_name;

实践：

# UCASE() 用法
SELECT UCASE(name) AS site_title, url
FROM websites;

# LCASE() 用法
SELECT LCASE(name) AS site_title, url
FROM Websites;

从 “Websites” 表中选取 “name” 和 “url” 列，并把 “name” 列的值转换为大/小写：

结果:

---

SQL MID() 函数

MID() 函数用于从文本字段中提取字符。

可以理解为截取字符串，给定起始下标，加截取的长度

语法：

SELECT MID(column_name,start[,length]) FROM table_name;

参数	描述
column_name	必需。要提取字符的字段。
start	必需。规定开始位置（起始值是 1）。
length	可选。要返回的字符数。如果省略，则 MID() 函数返回剩余文本。

实践：

SELECT MID(name,1,4) AS ShortTitle
FROM websites;

从 “Websites” 表的 “name” 列中提取前 4 个字符：

结果:

---

SQL LEN() 函数

LEN() 函数返回文本字段中值的长度。

注意：

• mysql中支持的是LENGTH(),而非LEN（）

语法：

SELECT LENGTH(column_name) FROM table_name;

实践：

SELECT name, LENGTH(url) as LengthOfURL
FROM websites;

从 “Websites” 表中选取 “name” 和 “url” 列中值的长度：

结果:

---

SQL ROUND() 函数

ROUND() 函数用于把数值字段舍入为指定的小数位数。

语法：

SELECT ROUND(column_name,decimals) FROM TABLE_NAME;

参数	描述
column_name	必需。要舍入的字段。
decimals	可选。规定要返回的小数位数。

实践：

SELECT name, ROUND(alexa,2)  as alexaRound
FROM websites
order by alexa asc;

从 “Websites” 表中选取 “alexa” 和 “name” 使用round函数

结果:

（因为原本是个整数，所以也看不出个所以然，期待有机会能看）

---

SQL NOW() 函数

NOW() 函数返回当前系统的日期和时间。

• 可用来构造新的数据列
• 可用来初始化某些数据项目，填充数据用

语法：

SELECT NOW() FROM table_name;

实践：

SELECT name, url, Now() AS date
FROM websites;

从 “Websites” 表中选取 name，url，及当天日期：

结果:

---

SQL FORMAT() 函数

FORMAT() 函数用于对字段的显示进行格式化。

语法：

SELECT FORMAT(column_name,format) FROM table_name;

参数	描述
column_name	必需。要格式化的字段。
format	必需。规定格式。

（format可填值，还未找到参考手册）

实践：

SELECT name, url, DATE_FORMAT(Now(),'%Y-%m-%d') AS date
FROM websites;

从 “Websites” 表中选取 name, url 以及格式化为 YYYY-MM-DD 的日期

结果:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-f8yWc75T-1650784062482)(https://s2.loli.net/2022/04/24/zSyATZ8431qb6ua.png)]

---

完结

又到了完结撒花的时候了，对于视图，索引，连接，联合，GROUP BY等关键字不会感到陌生了；

也许还会忘记，但是脑海里有个印象也是好的；

继续加油，向下一个盲区前进！