前言
该系列是自己学习sql语法的系列部分,记录了为期4天的笔记内容
笔记内容供个人后续查阅资料用(虽然预计会吃灰)
来了就安静看,悄悄来,悄悄走
参考文章:
2022-04-24 日报总结
学习SQL VIEW(视图)及余下部分
目标
- RIGH
- FUL
SQL 视图(Views)
视图是可视化的表。
作用:
-
视图隐藏了底层的表结构,简化了数据访问操作,客户端不再需要知道底层表的结构及其之间的关系。
-
视图提供了一个统一访问数据的接口。(即可以允许用户通过视图访问数据的安全机制,而不授予用户直接访问底层表的权限)
-
从而加强了安全性,使用户只能看到视图所显示的数据。
-
视图还可以被嵌套,一个视图中可以嵌套另一个视图。
本节内容
- 创建: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?
- 不利于代码的可读性和可维护性,特别是强类型语言,查询
INT
值,结果得到一个NULL
,程序可能会奔溃…如果要兼容这些情况程序往往需要多做很多操作来兜底 - 若所在列存在
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 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等关键字不会感到陌生了;
也许还会忘记,但是脑海里有个印象也是好的;
继续加油,向下一个盲区前进!