SQL基础与高级应用

MySQL 数值型函数

MySQL 字符串函数

MySQL 日期和时间函数

MySQL 聚合函数

MySQL 流程控制函数

日期函数

字符串函数

select device_id,gender,age,university from user_profile

字符匹配

一般形式为：列名 [NOT ] LIKE

匹配串中可包含如下四种通配符：
_：匹配任意一个字符；
%：匹配0个或多个字符；
[ ]：匹配[ ]中的任意一个字符(若要比较的字符是连续的，则可以用连字符“-”表 达 )；
[^]：不匹配[ ]中的任意一个字符。

例23．查询学生表中姓‘张’的学生的详细信息。

例24．查询姓“张”且名字是3个字的学生姓名。

如果把姓名列的类型改为nchar(20)，在SQL Server 2012中执行没有结果。原因是姓名列的类型是char(20)，当姓名少于20个汉字时，系统在存储这些数据时自动在后边补空格，空格作为一个字符，也参加LIKE的比较。可以用rtrim()去掉右空格。

例25.查询学生表中姓‘张’、姓‘李’和姓‘刘’的学生的情况。

例26.查询学生表表中名字的第2个字为“小”或“大”的学生的姓名和学号。

例27.查询学生表中所有不姓“刘”的学生。

例28.从学生表表中查询学号的最后一位不是2、3、5的学生信息。

这道题主要考察的是模糊查询 字段名 like ‘匹配内容’

1. _ ：下划线 代表匹配任意一个字符；
2. % ：百分号 代表匹配0个或多个字符；
3. []: 中括号 代表匹配其中的任意一个字符；
4. [^]: ^尖冒号 代表 非，取反的意思；不匹配中的任意一个字符。

tips：面试常问的一个问题：你了解哪些数据库优化技术？ SQL语句优化也属于数据库优化一部分，而我们的like模糊查询会引起全表扫描，速度比较慢，应该尽量避免使用like关键字进行模糊查询。

一、UNIOn–联合查询

例46．查询系号是1和2的班级的班号、班名、系号，系号是1 的记录在前，2在后。

等价于：

例47．查询要求同例46，但将查询结果按系号从大到小排序。

多表联合查询-不去重

题目：现在运营想要分别查看学校为山东大学或者性别为男性的用户的device_id、gender、age和gpa数据，请取出相应结果，结果不去重。

二、字段中多个值–包含查询

三、字符串截取函数

四、窗口函数

一、窗口函数有什么用？

在日常工作中，经常会遇到需要在每组内排名，比如下面的业务需求：

排名问题：每个部门按业绩来排名
topN问题：找出每个部门排名前N的员工进行奖励

面对这类需求，就需要使用sql的高级功能窗口函数了。

什么是窗口函数？

窗口函数，也叫OLAP函数（online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。

窗口函数的基本语法如下：

那么语法中的<窗口函数>都有哪些呢？

<窗口函数>的位置，可以放以下两种函数：

1） 专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。

2） 聚合函数，如sum. avg, count, max, min等

因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。

二、如何使用？

接下来，就结合实例，给大家介绍几种窗口函数的用法。

1.专用窗口函数rank

例如下图，是班级表中的内容

如果我们想在每个班级内按成绩排名，得到下面的结果。

以班级“1”为例，这个班级的成绩“95”排在第1位，这个班级的“83”排在第4位。上面这个结果确实按我们的要求在每个班级内，按成绩排名了。

得到上面结果的sql语句代码如下：

我们来解释下这个sql语句里的select子句。rank是排序的函数。要求是“每个班级内按成绩排名”，这句话可以分为两部分：

1）每个班级内：按班级分组

partition by用来对表分组。在这个例子中，所以我们指定了按“班级”分组（partition by 班级）
2）按成绩排名

order by子句的功能是对分组后的结果进行排序，默认是按照升序（asc）排列。在本例中（order by 成绩 desc）是按成绩这一列排序，加了desc关键词表示降序排列。

通过下图，我们就可以理解partiition by（分组）和order by（在组内排序）的作用了。

窗口函数具备了我们之前学过的group by子句分组的功能和order by子句排序的功能。那么，为什么还要用窗口函数呢？

这是因为，group by分组汇总后改变了表的行数，一行只有一个类别。而partiition by和rank函数不会减少原表中的行数。例如下面统计每个班级的人数。

现在我们说回来，为什么叫“窗口”函数呢？这是因为partition by分组后的结果称为“窗口”，这里的窗口不是我们家里的门窗，而是表示“范围”的意思。

简单来说，窗口函数有以下功能：

1）同时具有分组和排序的功能

2）不减少原表的行数

3）语法如下：

2.其他专业窗口函数

专用窗口函数：rank, dense_rank, row_number有什么区别呢？

它们的区别我举个例子，你们一下就能看懂：

得到结果：

从上面的结果可以看出：

rank函数：这个例子中是5位，5位，5位，8位，也就是如果有并列名次的行，会占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，4。

dense_rank函数：这个例子中是5位，5位，5位，6位，也就是如果有并列名次的行，不占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，2。

row_number函数：这个例子中是5位，6位，7位，8位，也就是不考虑并列名次的情况。比如前3名是并列的名次，排名是正常的1，2，3，4。

这三个函数的区别如下：

最后，需要强调的一点是：在上述的这三个专用窗口函数中，函数后面的括号不需要任何参数，保持()空着就可以。

现在，大家对窗口函数有一个基本了解了吗？

3.聚合函数作为窗口函数

聚和窗口函数和上面提到的专用窗口函数用法完全相同，只需要把聚合函数写在窗口函数的位置即可，但是函数后面括号里面不能为空，需要指定聚合的列名。

我们来看一下窗口函数是聚合函数时，会出来什么结果：

得到结果：

有发现什么吗？我单独用sum举个例子：

如上图，聚合函数sum在窗口函数中，是对自身记录、及位于自身记录以上的数据进行求和的结果。比如0004号，在使用sum窗口函数后的结果，是对0001，0002，0003，0004号的成绩求和，若是0005号，则结果是0001号~0005号成绩的求和，以此类推。

不仅是sum求和，平均、计数、最大最小值，也是同理，都是针对自身记录、以及自身记录之上的所有数据进行计算，现在再结合刚才得到的结果（下图），是不是理解起来容易多了？

比如0005号后面的聚合窗口函数结果是：学号0001~0005五人成绩的总和、平均、计数及最大最小值。

如果想要知道所有人成绩的总和、平均等聚合结果，看最后一行即可。

三、窗口函数作用

这样使用窗口函数有什么用呢？聚合函数作为窗口函数，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等）。同时可以看出每一行数据，对整体统计数据的影响。

四、注意事项

partition子句可以省略，省略就是不指定分组，结果如下，只是按成绩由高到低进行了排序：

得到结果：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Buo6hDyn-1640008700614)(C:Users小王子AppDataRoamingTypora ypora-user-imagesimage-20211204144953248.png)]

但是，这就失去了窗口函数的功能，所以一般不要这么使用。

五、总结

1.窗口函数语法

<窗口函数>的位置，可以放以下两种函数：

1） 专用窗口函数，比如rank, dense_rank, row_number等

2） 聚合函数，如sum. avg, count, max, min等

2.窗口函数有以下功能：

1）同时具有分组（partition by）和排序（order by）的功能

2）不减少原表的行数，所以经常用来在每组内排名

3.注意事项

窗口函数原则上只能写在select子句中

4.窗口函数使用场景

1）业务需求“在每组内排名”，比如：

排名问题：每个部门按业绩来排名
topN问题：找出每个部门排名前N的员工进行奖励

五、MySQL-locate()函数

判断字符串（string）中是否包含另一个字符串（subStr）
locate(subStr,string) ：函数返回subStr在string中出现的位置

功能类似的函数（不做详细介绍）

1. LOCATE(substr,str)
2. LOCATE(substr,str,pos)
3. POSITION(substr IN, str)
4. INSTR(str,substr)

一、分组查询

问题分解：

• 限定条件：平均发贴数低于5或平均回帖数小于20的学校，，聚合函数结果作为筛选条件时，不能用where，而是用having语法，配合重命名即可；
• 按学校输出：需要对每个学校统计其平均发贴数和平均回帖数，因此

细节问题：

• 表头重命名：as
• 用having不用where

完整代码：

二、子查询

题解 | 浙江大学用户题目回答情况

这题有两种解法
第一种：创建一张临时表用，获取浙江大学device_id对用户题目回答明细进行过滤。

第二种：先将两张表关联在一起，然后再筛选出浙江大学的明细数据。

三、链接查询

题意明确：

计算每个学校用户不同难度下的用户平均答题题目数

问题分解：

• 限定条件：无；
• 每个学校：按学校分组
• 不同难度：按难度分组
• 平均答题数：总答题数除以总人数
• 来自上面信息三个表，需要联表，up与qpd用device_id连接，qd与qpd用question_id连接。

细节问题：

• 表头重命名：as
• 平均值精度未明确要求，忽略

完整代码：

四、组合查询

五、条件函数

例题一

题解 | #计算25岁以上和以下的用户数量#

方法一：是case的写法，就是case when 条件1 then 值1 …… end

方法二：就是if的写法，就是if(条件，‘为真则是此值’，‘为假则是此值’)，相对于上边这个少了一个null值的判断，因为这是分为两类，所以就可以这样直接写另一类忽视判断

例题二

题目：现在运营想要将用户划分为20岁以下，20-24岁，25岁及以上三个年龄段，分别查看不同年龄段用户的明细情况，请取出相应数据。（注：若年龄为空请返回其他。）

六、日期函数

例题一

题解 | #计算用户8月每天的练题数量#

记录一下日期函数year(),month(),day()

例题二

题解 | #计算用户的平均次日留存率#

题解：计算用户的平均次日留存率

题目分析

而次日留存率可以这样表示：

次日留存率=去重的数据表中符合次日留存的条目数目÷去重的数据表中所有条目数目

具体而言，使用两个子查询，查询出两个去重的数据表，并使用条件（q2.date应该是q1.date的后一天）进行筛选，如下所示（数据未显示完全，从左至右顺序，列表名为 q1.device_id, q1.date, q2.device_id, q2.date)

因为使用的是q1左级联q2，所以q1的所有信息是显示的；而q2中只显示留存的信息，否则为null。

最后，分别统计q1.device_id 和 q2.device_id 作去重后的所有条目数和去重后的次日留存条目数，即可算出次日留存率。

具体实现

注意，MySQL中 在对列进行计数时不统计值为 null的条目

七、文本函数

例题一

题解 | #统计每种性别的人数#

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UllQbNUU-1640008700617)(C:Users小王子AppDataRoamingTypora ypora-user-imagesimage-20211204135952570.png)]

（1）SUBSTRING_INDEX的写法

（2）LIKE的写法【mark一下，原来有IF(profile LIKE ‘%female’,‘female’,‘male’) 这样的方式】

例题二

题意明确：把用户的个人博客用户名字段提取出来单独记录为一个新的字段

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YQ7CT4bE-1640008700618)(C:Users小王子AppDataRoamingTypora ypora-user-imagesimage-20211204140358802.png)]

问题分解：

• 限定条件：无；

• 提取字段内信息：个人博客字段中的用户名是被字符/分隔的最后一个子串，使用substring_index函数可以按特定字符串截取源字符串 substring_index(FIELD, sep, n)可以将字段FIELD按照sep分隔：

  (1).当n大于0时取第n个分隔符(n从1开始) 左边 的全部内容；

  (2).当n小于0时取 倒数第-n个 分隔符(n从-1开始) 右边 的全部内容； 因此，本题可以用取出用户名.

• substring_index函数解析

细节问题：

• 表头重命名：as

完整代码：

例题三

题解 | #截取出年龄#

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2IJ843p6-1640008700619)(C:Users小王子AppDataRoamingTypora ypora-user-imagesimage-20211204141327475.png)]

题意明确： 统计每个年龄的用户分别有多少参赛者

• 问题分解： 限定条件：无；
• 每个年龄：按年龄分组group by age，但是没有age字段，需要从profile字段截取，按字符,分割后取出即可。可使用substring_index函数可以按特定字符串截取源字符串。substring_index

题解 | #截取出年龄#

substring_index 用法。

先截取至年龄

返回的是
180cm,75kg,27
165cm,45kg,26
178cm,65kg,25
171cm,55kg,23
168cm,45kg,22

再把年龄拿出来

取刚才那段东西，用’,‘分隔的’-1’位，从右往左数，第一次遇到’,'的右边全部内容。

八、窗口函数

题目：现在运营想要找到每个学校gpa最低的同学来做调研，请你取出每个学校的最低gpa。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4xSGI66b-1640008700620)(C:Users小王子AppDataRoamingTypora ypora-user-imagesimage-20211204150139990.png)]

题解 | #找出每个学校GPA最低的同学#

因为学校与学生是一对多的关系，如果仅用min求出gpa最低的学生，查询结果中的id与学生不一定是对应的关系，因此此方法错误。

（1）方法一：将表a的device_id,university,gpa和表b的university,min(gpa)连接起来找。

（2）方法二：窗口函数

复制代码

九、综合练习

例题一：

题目： 现在运营想要了解复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况，请取出相应明细数据，对于在8月份没有练习过的用户，答题数结果返回0

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QPXkCgyt-1640008700620)(C:Users小王子AppDataRoamingTypora ypora-user-imagesimage-20211208091733507.png)]

例题二：

题目：现在运营想要了解浙江大学的用户在不同难度题目下答题的正确率情况，请取出相应数据，并按照准确率升序输出。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-22hKpccD-1640008700621)(C:Users小王子AppDataRoamingTypora ypora-user-imagesimage-20211208094408756.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MJNuNSQH-1640008700622)(C:Users小王子AppDataRoamingTypora ypora-user-imagesimage-20211208094422974.png)]

题解 | #浙大不同难度题目的正确率#

发表于 2021-09-09 09:33:42

浙江大学的用户在不同难度题目下答题的正确率情况，请取出相应数据，并按照准确率升序输出。
输出：题目难度，正确率=正确题数/总题数

筛选：浙江大学

分类：不同难度

排序：按照正确率，升序排列

由于输出中正确率的参数主要来源于gpd因此联结时完整保留gpd信息

用整理查询语句顺序得正确答案

复制代码

例题三：

题目： 现在运营想要了解2021年8月份所有练习过题目的总用户数和练习过题目的总次数，请取出相应结果

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xn6HJDh8-1640008700623)(C:Users小王子AppDataRoamingTypora ypora-user-imagesimage-20211208095531797.png)]

题意明确：2021年8月份所有练习过题目的总用户数和练习过题目的总次数

问题分解：

• 限定条件：2021年8月份，匹配date字段即可，匹配方法主要有三种：
  （1）like语法：
  （2）year、month函数：；
  （3）date_format函数：
• 2：总用户数：count函数计数，因为用户有重复，所以需要distinct去重，即
• 3：总次数：count(question_id)即可

细节问题：

• 表头重命名：as语法

完整代码：

复制代码

增删改查

插入记录

目录

概述

给root用户设置密码

设置开机自启动

目录

索引的创建时机

适合创建索引的情况

1. 主键自动建立唯一索引；
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其它表关联的字段，外键关系建立索引
4. 单键/组合索引的选择问题， 组合索引性价比更高
5. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
6. 查询中统计或者分组字段

不适合创建索引的情况

1. 表记录太少
2. 经常增删改的表或者字段
3. Where 条件里用不到的字段不创建索引
4. 过滤性不好的不适合建索引

目录

目录