【MySQL】SQL高级语句+运行原理

于 2024-06-02 19:16:37 发布
阅读量643 收藏 21
点赞数 15
文章标签： mysql sql android
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/littlefatguy/article/details/139395552
版权

SQL高级语句+运行原理

基于公开网站sqlzoo的MySQL数据库引擎，必须使用英文界面练习代码（不同语言界面不同引擎可能导致数据库数据不同），并切换至Mysql引擎。
网址：https://sqlzoo.net/

  • 所有函数语句均放在SELECT语句后FROM语句前

1.部分常见函数

1.1 四舍五入函数

round(x,y)

  • round函数对x值进行四舍五入，精确到小数点后y位；
  • y为负值时，保留小数点左边相应的位数为0，不进行四舍五入

1.2 字符串函数

1.2.1 coucat函数

连接字符串函数：concat(s1,s2,…)

  • concat函数返回连接参数s1、s2等产生的字符串；
  • 任一参数为null时，则返回null
#语法示例：
SELECT confirmed, deaths, recovered, recovered/confirmed,
concat(round((recovered/confirmed)*100, 2),'%') 治愈率
FROM covid
WHERE recovered/confirmed > 0.3

在这里插入图片描述

#语法示例：查询首都和名称，其中首都需是国家名称的扩展
SELECT name,capital
FROM world
WHERE capital like concat('%', name, '%')
and name != capital

在这里插入图片描述

1.2.2 replace函数

替换函数：replace(s,s1,s2)

  • replace函数使用字符串s2代替s中所有的s1
#语法示例：
SELECT DISTINCT name,
replace(name, 'A', 'a')
FROM covid

在这里插入图片描述

1.2.3 截取函数

截取字符串一部分的函数：
left(s,n)
right(s,n)
substring(s,n,len)

  • left函数返回字符串s最左边n个字符；
  • right函数返回字符串s最右边n个字符；
  • substring函数返回字符串s从第n个字符起取长度为len的子字符串，n也可以为负值，则从倒数第n个字符起取长度为len的子字符串，没有len值则取从第n个字符起到最后一位
#语法示例：
SELECT DISTINCT name,
left(name, 2),
right(name,1),
substring(name,2,3),
substring(name,2)
FROM covid

在这里插入图片描述

#语法示例：查询国家名称及其首都名称都以相同的字母开头的国家名及其首都，且不能包括国家名称和首都名称完全相同的情况
SELECT name,capital
FROM world
WHERE left(name, 1) = left(capital, 1)
and name != capital

在这里插入图片描述

1.3 数据类型转换函数

转换数据类型的函数：cast(x as type)

  • cast函数将一个类型的x值转换为另一个类型的值；
  • type参数可以填写char(n)、date、time、datetime、decimal等转换为对应的数据类型

1.4 日期时间函数

1.4.1 获取年月日的函数：

year(date)
month(date)
day(date)

  • date可以是年月日组成的日期，也可以是年月日时分秒组成的日期时间；
  • year(date)返回日期格式中的年份；
  • month(date)返回日期格式中的月份；
  • day(date)返回年日期格式中的日份
#语法示例：
SELECT whn 更新时间,
year(whn) 年,
month(whn) 月,
day(whn) 日
FROM covid
WHERE recovered > 0

在这里插入图片描述

1.4.2 对指定起始时间进行加减操作：

date_add(date,interval expr type)
date_sub(date,interval expr type)

  • date用来指定起始时间；

  • date可以是年月日组成的日期，也可以是年月日时分秒组成的日期时间；

  • expr用来指定从起始时间添加或减去的时间间隔；

  • type指示expr被解释的方式，type可以是以下值：
    在这里插入图片描述

  • date_add函数对起始时间进行加操作，date_sub函数对起始时间进行减操作

#语法示例：
SELECT whn 更新时间,
date_add(whn, interval 2 day) 加2天
FROM covid
WHERE recovered > 0

在这里插入图片描述

1.4.3 计算两个日期之间间隔的天数

datediff(date1,date2)

  • datediff函数由date1-date2计算出间隔的时间，只有date的日期部分参与计算，时间不参与；
#语法示例：
SELECT DATEDIFF('2024-06-01', '2024-05-01') AS DateDifference;

在这里插入图片描述

1.4.3 将日期和时间格式化

date_format(date,format)

  • date_format函数根据format指定的格式显示date值；
  • 可以替换的格式有：
    在这里插入图片描述

1.5 条件判断函数

if(expr,v1,v2)

  • 如果表达式expr是true返回值v1，否则返回v2
#语法示例：
SELECT recovered 累积治愈人数,
if (recovered > 500, 'GOOD', 'BAD')
FROM covid

在这里插入图片描述

case expr when v1 then r1 when v2 then r2 …else rn end

#语法示例：
SELECT recovered 累积治愈人数,
case recovered>0 when recovered=1 then 'one' else 'more' end
FROM covid

在这里插入图片描述

case when v1 then r1 when v2 then r2…else rn end

#语法示例：
SELECT recovered 累积治愈人数,
case when recovered = 1 then 'one' when recovered > 1 then 'more' else '0' end
FROM covid
WHERE recovered > 0

在这里插入图片描述

2.窗口函数

窗口函数over([partition by 字段名] [order by 字段名 asc|desc])

  • over()中两个子句为可选项，partition by指定分区依据，order by指定排序依据

2.1 排序窗口函数

rank()over()
dense_rank()over()
row_number()over()

#语法示例：查询每一年S14000021选区中所有候选人所在的团体(party)和得票数(votes)，并对每一年中的所有候选人根据选票数的高低赋予名次，选票数最高则为1，第二名则为2，后续以此类推，最后根据团体(party)和年份(yr)排序
SELECT yr, votes, party,
RANK()OVER(PARTITION BY yr ORDER BY votes desc) as posn
FROM ge
WHERE constituency = 'S14000021'
ORDER BY party, yr

在这里插入图片描述

  • 窗口函数只能写在select语句中；
  • 窗口函数中的partition by子句可以指定数据的分区，但其只分区不去重；partition by可以不写，此时默认整个表为一个区；
  • 排序窗口函数中的order by子句是必选项，其在分区内依据指定字段和排序方法对数据进行排序；
  • rank()、dense_rank()、row_number()指定排序赋值方法：
    • rank()：跳跃式排序，同数值同排名，但紧跟的排名跳跃+1.例如：99、99、90、88排序为1、1、3、4；
    • dense_rank()：并列连续排序，同数值同排名，但紧跟的排名不跳跃。例如：99、99、90、88排序为1、1、2、3；
    • row_number()：连续排序，同数值按随机不同排名。例如：99、99、90、88排序为1、2、3、4.

2.2 偏移分析函数

lag(字段名,偏移量[,默认值])over()
lead(字段名,偏移量[,默认值])over()

#语法示例：查询法国和德国1月每天新增确诊人数，最后显示国家名、标准日期（2020-01-27）、当天截至时间累计确诊人数、昨天截至时间累计确诊人数、每天新增确诊人数，按照截至时间排序
SELECT name, date_format(whn, '%Y-%m-%d')date, confirmed 当天截至时间累计确诊人数,
LAG(confirmed, 1)OVER(PARTITION BY name ORDER BY whn) 昨天截至时间累计确诊人数,
(confirmed - LAG(confirmed,1)OVER(PARTITION BY name ORDER BY whn)) 每天新增确诊人数
FROM covid
WHERE name in ('France', 'Germany') and month(whn) = 1
ORDER BY whn

在这里插入图片描述

  • 偏移分析窗口函数中order by子句是必选项；
  • lag表示向上偏移，行向上取数据；lead表示向下偏移，行向下取数据
#语法示例：查询意大利每周新增确诊数（显示每周一的数值 weekday(whn) = 0），最后显示国家名，标准日期（2020-01-27），每周新增人数，按照截至时间排序
SELECT name, date_format(whn, '%Y-%m-%d') date,
(confirmed - LAG(confirmed, 1)OVER(PARTITION BY name ORDER BY whn)) New
FROM covid
WHERE name = 'Italy' AND weekday(whn)=0
ORDER BY whn

在这里插入图片描述

3.表连接

join表连接通过on表名1.字段名=表名2.字段名，将两个表格各自的字段等值连接来匹配连接，无法匹配的填充null值，其逻辑思想如下所示：
在这里插入图片描述

3.1 内连接

SELECT 字段名
FROM 表名1 INNER JOIN 表名2 ON 表名1.字段名 = 表名2.字段名

INNER JOIN表示内连接，连接两个表留下同时互相匹配上的行得到一张新表，操作思想如下所示：

在这里插入图片描述

#语法示例：查询有球员名叫Mario进球的队伍1（team1），队伍2（team2）及球员姓名
SELECT team1, team2, player
FROM game JOIN goal ON game.id = goal.matchid
WHERE player like 'Mario%'

在这里插入图片描述

  • INNER JOIN时INNER可省略， JOIN默认为内连接
#语法示例：查询队伍1（team1）的教练是“Fernando Santos”的球队名称（teamname）、比赛日期（mdate）和赛事编号（id）
SELECT mdate, teamname, ga.id 
FROM game ga JOIN eteam et ON et.id = ga.team1
WHERE coach = 'Fernando Santos'

在这里插入图片描述

  • FROM子句中的表格可以起别名，并在指定连接键和指定字段来源于的表格时，可以用别名代替完整的表格名；
  • 多表连接时，字段名唯一可以不指定表名，但字段名不唯一时一定要指明表名

3.2 左连接

SELECT 字段名
FROM 表名1 LEFT JOIN 表名2 ON 表名1.字段名 = 表名2.字段名

LEFT JOIN表示左连接，左边的表返回所有的行，右边的表只留下匹配上的行，得到一张新表。操作思想如下所示：
在这里插入图片描述

#语法示例：使用合适的连接显示所有教师及其所教授的科目名
SELECT teacher.name, dept.name
FROM teacher LEFT JOIN dept ON teacher.dept = dept.id

在这里插入图片描述

3.3 右连接

SELECT 字段名
FROM 表名1 RIGHT JOIN 表名2 ON 表名1.字段名 = 表名2.字段名

RIGHT JOIN表示右连接，右边的表返回所有的行，左边的表只留下匹配上的行，得到一张新表。操作思想如下所示：
在这里插入图片描述

#语法示例：使用合适的连接显示所有教师及其所教授的科目名
SELECT teacher.name, dept.name
FROM dept RIGHT JOIN teacher ON teacher.dept = dept.id

在这里插入图片描述

  • 左右连接可以相互转换，某种程度上效果一致，只是表的先后顺序不同
#语法示例：查询每场比赛，每个球队的得分情况，最后按照举办时间（mdate）、赛事编号（matchid）、队伍1（team1）和队伍2（team2）排序
SELECT game.mdate, game.team1, 
sum(case when game.team1=goal.teamid then 1 else 0 end) score1,
game.team2,
sum(case when game.team2=goal.teamid then 1 else 0 end) score2
FROM game LEFT JOIN goal ON game.id = goal.matchid
GROUP BY game.mdate, game.team1, game.team2
ORDER BY game.mdate, goal.matchid, game.team1, game.team2

在这里插入图片描述

4.子查询

子查询本身就是一段完整的查询语句，然后用英文括号()包裹嵌套在主查询语句中，子查询可以多层嵌套。最常用的子查询运用在from和where子句中。
子查询是可以自己正常独立运行的一段完整的查询语句，然后将子查询的查询结果作为主查询的一部分，因此子查询优先级高于主查询。

4.1 where基于子查询条件筛选（比较运算符&in关键字）

#语法示例：查询出gdp高于欧洲每个国家的所有国家名，有一些国家gdp值可能为NULL，请排除这些国家
SELECT name
FROM world
WHERE gdp is not null
AND gdp > (
    SELECT max(gdp)
    FROM world
    WHERE continent = 'Europe'
    )

在这里插入图片描述

#语法示例：查询跟阿尔及尼亚（Argentina）和澳大利亚（Australia）在同一大洲的所有国家名及其所属大洲， 并按照国家名进行排序
SELECT name, continent
FROM world
WHERE continent in (
    SELECT continent
    FROM world
    WHERE name in ('Argentina', 'Australia')
    )
ORDER BY name

在这里插入图片描述

  • 对于带有比较运算符的子查询，要求子查询为标量子查询，即子查询结果为一行一列；
  • 对于带有in关键字的子查询，要求子查询为列子查询，即子查询结果为多行一列；
  • where子句中的子查询适用于查询条件无法一步到位，需要先进行一步查询得到结果再基于这个查询结果再进行条件判断的情况

4.2 from基于子查询作为数据表

#语法示例：查询2017年所有在爱丁堡的选区当选议员所在选区（constituency）及其团队（party），已知爱丁堡选区编号为S14000021至S14000026，当选议员即各选区得票数最高的候选人
SELECT constituency, party
FROM (
     SELECT constituency, party, votes,
     RANK()OVER(PARTITION BY constituency ORDER BY votes desc) as posn
     FROM ge
     WHERE yr = 2017 AND constituency between 'S14000021' and 'S14000026') as rk
WHERE rk.posn = 1

在这里插入图片描述

  • from子句中的子查询，本质上是通过一段查询语句得出查询结果作为主查询的数据来源；
  • from子查询结果可以是多行多列；
  • from子查询必须使用别名，同样可以省略as
#语法示例：查询在欧洲(Europe)人均gdp大于英国 (United Kingdom)的国家名
SELECT name
FROM world
WHERE gdp/population > (
     SELECT gdp/population
     FROM world
     WHERE name = 'United Kingdom')
AND continent = 'Europe'

在这里插入图片描述

Faxxtty
关注
  • 15
    点赞
  • 踩
  • 21
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL面试常见题目
ForeverSen的博客
08-12 1890
B树和B+树有什么区别？为什么索引不用B树？ B-树是一种平衡的多路查找(又称排序)树，在文件系统中有所应用。主要用作文件的索引。其中的B就表示平衡(Balance) B-树 ： 定义任意非叶子结点最多只有M个儿子；且M>2； 根结点的儿子数为[2, M]； 除根结点以外的非叶子结点的儿子数为[M/2, M]； 每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（...
一、SQL语句执行过程
IT蜗牛的专栏
03-11 4392
一、MySQL架构图 MySQL逻辑架构图，可以分为 Server 层和存储引擎层两部分。 Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。 存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 In...
sql语句的执行流程
evanYang的博客
10-18 2606
一条sql语句在mysql中的执行流程 Mysql的基本逻辑结构示意图 win下查看mysql数据文件存储位置 show global variables like “%datadir%”; 用户权限 这里注意，当已经建立了连接后，在mysql-user表里面修改了用户的权限后，已经建立的连接权限是不会受影响的。 mysql分层 主要是分三层 客户端 navicat，sqlyog之类的 server层： 连接器、查询缓存、分析器、优化器、执行器 内置函数：日期、时间、数学、加密函数等 跨存储引擎功
SQL执行过程详解
最新发布
u014212540的博客
03-09 3782
SQL执行过程详解
SQL语句的执行
玄霄
05-25 705
SQL语句的执行MySQL的内部组件结构Server层Store层连接器长连接与短连接查询缓存大多数情况查询缓存就是个鸡肋，为什么呢？使用场景分析器词法分析器原理优化器执行器bin-log归档1. 配置my.cnf2. binlog命令3. 查看binlog内容4. 数据归档操作归档测试 MySQL的内部组件结构 大体来说，MySQL 可以分为 Server 层和存储引擎层两部分。 Server层 主要包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函
SQL查询的底层运行原理深入分析
09-08
SQL查询的底层运行原理是数据库管理系统执行SQL语句的关键过程，它涉及到数据的获取、过滤、分组、聚合、排序和返回等步骤。理解这些原理有助于优化查询性能，提高数据库操作的效率。 首先，我们来看一个简单的查询...
Effective MySQL之SQL语句最优化.pdf
01-19
3. SQL语句优化的重要性：在数据库操作中，SQL语句的效率直接影响到应用的运行性能。因为数据库系统是多用户环境，对SQL语句的优化可以减少资源的竞争，提高并发处理能力。 4. SQL语句优化相关原理：优化原理包括但...
《Effective MySQL之SQL语句最优化》手册
01-19
《Effective MySQL之SQL语句最优化》是一本深入探讨如何提升数据库性能的专业手册，它针对SQL语句的优化提供了一系列实用且高效的策略。在数据库管理领域，SQL优化是提升系统性能的关键环节，尤其是在处理大数据量时...
PHP+Mysql 带SQL注入源码 下载
04-25
SQL注入是指攻击者通过输入恶意的SQL语句，来操纵数据库，获取、修改、删除敏感数据，甚至完全控制数据库服务器。例如，一个简单的用户登录表单，如果未对用户输入进行充分的验证和转义，攻击者可以构造特定的输入，...
SQL语句的执行过程
hljjaaa的博客
09-29 851
SQL语句执行过程
sql执行语句
sherly_chen的博客
10-12 4455
1.什么是SQL SQL是结构化查询语言，专门用来访问和处理数据库的编程语言。能够让我们以编程额形式来操作数据库。 那SQL到底能做什么呢，下面我用一张图带你了解SQL执行语句是如何操作数据库的： 语法一：SELECT语句 用于从表中查询数据。执行结果被存储在一个结果表中（称为结果集）。语法格式如下： -- * 号表示查询表格中的所有数据 SELECT * FROM 表名称 -- 如果我们要查询表格中指定字段的数据 SELECT 列名称 FROM 表名称 注意：SQL 语句中的关键字对大小写
SQL逻辑查询语句执行顺序
Ethan Mr.的博客
05-10 1808
  理解sql语句的执行顺序对优化sql非常重要，那么sql语句的执行顺序是怎样的呢，以一条简单的的语句做分析：         这一条语句包含我们经常用到的一些关键字，select，from，where，group by，order by，它的执行顺序如下：先执行from关键字后面的语句，明确数据的来源，它是从哪张表取来的。接着执行where关键字后面的语句，对数据进行筛选。再接着执行group...
SQL语句执行过程详解
皮卡丘的情绪
04-27 1607
文章目录1、sql查询的通用结构：2、SQL语句的执行顺序：2.1、关键字的顺序：2.2、SLEECT语句的执行顺序3、SQL的执行原理 1、sql查询的通用结构： SELECT DISTINCT 字段1,聚合函数(字段2),字段3 “字段3别名” FROM 表1 (LEFT/RIGHT) JOIN 表2 ON 多表的连接条件 (LEFT/RIGHT) JOIN 表3 ON 多表的连接条件 WHERE 不包含聚合函数的过滤条件 GROUP BY 分组条件1,分组条件2 HAVING 包含聚
Sql语句的执行原理-MySQL
qq_32998153的博客
08-16 5599
第一步：应用程序把查询SQL语句发送给服务器端执行。 我们在数据库层执行SQL语句时，应用程序会连接到相应的数据库服务器，把SQL语句发送给服务器处理。 第二步：查询缓存 服务器在解析一个查询语句之前，如果查询缓存是打开的（MySQL默认打开，可以使用have_query_cache查看），在接收到查询请求后，并不会直接去数据库查询，而是在数据库的查询缓存中找是否有相对应的查询数据（某条给定...
一条SQL语句在MySQL中如何执行的
Kirito的博客
04-25 304
MySQL 基本架构概览 请先了解InnoDB引擎 •连接器： 身份认证和权限相关(登录 MySQL 的时候)。 •查询缓存: 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。 •分析器: 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。 •优化器： 按照 MySQL 认为最优的方案去执行。 •执行器: 执行语句，然后从存储引擎返回数据。 MySQL 主要分为 Server
一条SQL语句是如何执行的？
飞翔的博客
06-01 3426
大家六一儿童节好呀！ 接下来的一段时间内，将带领大家一同探索MySQL的奥妙，加油吧！我们。 下面进入正题：一条SQL语句是如何进行的？ 对于这个问题，我想将其分为两个问题来回答，分别是： 一条查询SQL是如何执行的？ 一条更新SQL是如何执行的？ 我们都知道MySQL内部是分为Server层和存储引擎层的；每个层都有各自的职责； 对于一个查询语句，eg：select * f...
sql语句执行步骤详解
imherer的博客
12-05 2063
一、准备工作 先来一段伪代码，首先你能看懂么？ SELECT DISTINCT <select_list> FROM <left_table> <join_type> JOIN <right_table> ON <join_condition> WHERE <where_condition> GROUP BY <group_by_list> HAVING <having_condition>...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值