select *

题目

现在运营想要查看用户信息表中所有的数据，请你取出相应结果

示例：user_profile

id	device_id	gender	age	university	province
1	2138	male	21	北京大学	Beijing
2	3214	male		复旦大学	Shanghai
3	6543	female	20	北京大学	Beijing
4	2315	female	23	浙江大学	ZheJiang
5	5432	male	25	山东大学

根据示例，你的查询应返回以下结果:

id	device_id	gender	age	university	province
1	2138	male	21	北京大学	Beijing
2	3214	male		复旦大学	Shanghai
3	6543	female	20	北京大学	Beijing
4	2315	female	23	浙江大学	Zhejiang
5	5432	male	25	山东大学	Shandong

解题

很简单一题，首先想到的是select *

优化后查询的时间和内存都有减少

原理

两者运行时间相似，是因为数据量小，但是占用空间前一个6624  后一个6528，所以*号更加占用内存空间，主要原因是mysql

1：长度超过 728 字节的时候，会先把超出的数据序列化到另外一个地方，因此读取这条记录会增加一次 io 操作。（MySQL InnoDB）

2：防止索引覆盖和回文回表

索引覆盖

概念

索引覆盖是指查询可以直接利用索引中存储的数据来满足查询的需求，而不需要再通过访问数据或者磁盘来获取额外的信息。索引覆盖可以提高查询的性能，减少IO操作，并减少了额外的资源消耗。

当一个查询可以直接从索引中获取所需的数据列时，数据库可以避免从磁盘中读取数据块。相反，它只需要读取索引的数据页并返回结果。这可以减少磁盘IO的次数，提高查询速度。

为了实现索引覆盖，可以创建一个包含查询需要的列的复合索引。当查询使用复合索引时，数据库可以直接从索引中提取所需列的值，而不需要访问数据表。(复合索引是多个列的联合索引，它可以包含查询中使用的所有列。)

但是索引覆盖并不适合所有类型的查询。对于一些复杂的查询，可能需要访问数据表来执行计算或者其他操作。因此，需要根据具体的查询需求来判断是否适合使用索引覆盖。

demo

假设有一个名为"orders"的表，其中包含以下列：order_id、customer_id、order_date、total_amount。我们需要查询订单日期和订单总金额。

1. 创建复合索引：

CREATE INDEX idx_orders ON orders(order_date, total_amount);

这个复合索引包含了订单的日期和总金额两列。

2. 查询覆盖的例子：

SELECT order_date, total_amount FROM orders WHERE order_date >= '2022-01-01' AND order_date <= '2022-12-31';

这个查询只需要订单日期和总金额的数据，而不需要访问其他的列。因为我们已经在第一步创建了复合索引，数据库可以直接从索引中获取所需的数据，而不需要访问数据表。

这样，查询将会直接从索引中读取数据，并返回结果，而无需访问数据表。这就是索引覆盖的实现方式。

索引覆盖对于有许多重复值或者历史数据的表格可能并不适用。在实际应用中，需要根据具体的数据模型和查询需求来判断是否适合使用索引覆盖。

回文回表

概念

1. 回文（Palindrome）： 在数据库查询优化中，回文通常指的是在查询执行的过程中，查询结果的行顺序与存储的物理顺序相同。具体来说，当查询访问的数据在存储中是按照查询条件的顺序排列时，就称为回文。回文的查询效率比较高，因为数据库可以连续地读取存储中的数据。
2. 回表（Lookup or Index Lookups）： 回表指的是在使用索引的情况下完成查询时，数据库需要在索引节点上进行额外的查找（回到原始表）以获取完整的数据行。当查询条件包含索引列以及其他非索引列时，数据库可能需要通过回表操作来获取非索引列的数据。回表操作可能会增加查询的开销，尤其是在频繁进行回表操作的情况下。

回文是指查询结果的物理顺序与查询顺序一致，

而回表是指在使用索引时需要额外的查找操作。

优化查询性能时，我们通常希望尽可能地实现回文查询，避免频繁的回表操作，以提高查询效率。

demo

1. 回文： 假设有一个名为"orders"的表，其中包含了订单信息，例如订单ID、客户ID和订单日期等。如果要查询特定客户的所有订单，并按照订单日期逆序排列，可以使用以下SQL语句：

SELECT * FROM orders WHERE customer_id = '123' ORDER BY order_date DESC;

如果我们为"customer_id"和"order_date"两列创建了索引，并且索引按照"customer_id"和"order_date"的顺序排列，那么查询的结果将是回文的。这意味着数据在存储中按照查询条件的顺序排列，因此数据库可以连续访问存储中的数据，从而提高查询效率。

2. 回表： 现在假设我们要查询某个客户的所有订单，包括订单的商品信息等详细信息。如果我们使用以下SQL语句：

SELECT * 
    FROM orders o 
    JOIN order_items i 
        ON o.order_id = i.order_id 
    WHERE o.customer_id = '123';

在这个查询中，我们需要关联"orders"和"order_items"两个表，并且查询条件为"customer_id"，如果我们为"order_id"和"customer_id"这两列创建了索引，查询的结果将返回所有匹配的行，但是我们仍然需要通过回表操作来获取订单项的详细信息。

因为"order_items"表中的数据不包含在"orders"表，所以数据库需要在回到"orders"表来获取"order_id"列并在"order_items"表中查找匹配的数据行，这个过程就称为回表。在这种情况下，回表操作会增加查询的开销，通常会降低查询性能。

查询结果去重

描述

题目：现在运营需要查看用户来自于哪些学校，请从用户信息表中取出学校的去重数据。 示例:user_profile

id	device_id	gender	age	university	province
1	2138	male	21	北京大学	Beijing
2	3214	male		复旦大学	Shanghai
3	6543	female	20	北京大学	Beijing
4	2315	female	23	浙江大学	ZheJiang
5	5432	male	25	山东大学	Shandong

根据示例，你的查询应返回以下结果：

university
北京大学
复旦大学
浙江大学
山东大学

示例1

输入：

drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`province` varchar(32)  NOT NULL);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学','Shanghai');
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学','ZheJiang');
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学','Shandong');

输出：

北京大学
复旦大学
浙江大学
山东大学

题解

distinct

group by

小结

为什么使用group by比distinct更有效率

使用group by比distinct更有效率的原因是，group by允许我们同时按照多个列进行聚合（对每组数据进行计算），而distinct只能对单个列进行聚合。在进行多列聚合时，可以减少重复的操作，从而提高效率。

在处理多列时，group by可能会涉及到更多的计算量，但是group by的优势在于group by的聚合方式是在一次扫描数据时完成的，而distinct则需要对数据进行多次扫描。

例如，如果我们需要统计一年中每个月的总销售金额，使用group by可以同时按照年份和月份两个维度进行聚合，而distinct则需要先对年份去重，再对月份去重，这就导致了多次扫描数据，效率反而更低。

distinct应用场景

虽然在多数情况下，使用GROUP BY比DISTINCT更高效，但是在一些特殊情况下，使用distinct可能更为适合，例如：

1. 当需要对一个非常大的数据集去重时，使用DISTINCT可能更高效，因为它不需要为每个分组计算聚合函数。
2. 当需要去重的数据本身就已经排序好时，使用DISTINCT可能更高效，因为它只需要找到不同值的起始位置和结束位置，而不需要计算分组聚合。
3. 当需要去重的数据只有少数几个不同的值时，使用DISTINCT也可能更高效，因为它只需要在查找到不同值后停止扫描，而不需要扫描整个数据集。

总的来说，如果需要对数据进行分组聚合，并且分组条件不止一个，或者需要多个聚合函数计算，那么使用GROUP BY会更为适合。

如果只需要去重，并且数据量极大，或者数据已经排序，或者数据集中只有少数几个不同值，那么使用DISTINCT可能更高效。

查询结果限制返回行数

描述

题目：现在运营只需要查看前2个用户明细设备ID数据，请你从用户信息表 user_profile 中取出相应结果。

示例：

id	device_id	gender	age	university	province
1	2138	male	21	北京大学	Beijing
2	3214	male		复旦大学	Shanghai
3	6543	female	20	北京大学	Beijing
4	2315	female	23	浙江大学	ZheJiang
5	5432	male	25	山东大学	Shandong

根据输入，你的查询应返回以下结果：

device_id
2138
3214

示例1

输入：

drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`province` varchar(32)  NOT NULL);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学','Shanghai');
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学','ZheJiang');
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学','Shandong');

复制

输出：

2138
3214

题解

limit 2

limit 0,2

limit + offset

小结

效率对比

在效率方面，limit 2效率最高，因为它只需要返回前两条数据，不需要扫描整个表。

limit 2 offset 0和limit 2的效率相同，因为它们都只返回前两条数据，不需要扫描整个表。

而limit 0,2需要从头开始扫描整个表，然后返回前两条数据，因此效率是最低的。

查找学校是北大的学生信息

描述

题目：现在运营想要筛选出所有北京大学的学生进行用户调研，请你从用户信息表中取出满足条件的数据，结果返回设备id和学校。

示例：user_profile

id	device_id	gender	age	university	province
1	2138	male	21	北京大学	Beijing
2	3214	male		复旦大学	Shanghai
3	6543	female	20	北京大学	Beijing
4	2315	female	23	浙江大学	ZheJiang
5	5432	male	25	山东大学	Shandong

根据示例，你的查询应返回以下结果：

device_id	university
2138	北京大学
6543	北京大学

示例

输入：

drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`province` varchar(32)  NOT NULL);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学','Shanghai');
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学','ZheJiang');
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学','Shandong');

输出：

2138|北京大学
6543|北京大学

解题

原题

索引覆盖

加入索引后运行时间有所优化，但内存消耗略增加

小结

覆盖索引

原理：就是查询字段在 二级索引中全部找到，不需要回表查询

覆盖索引只是特定于具体select语录而言的联合索引。也就是说一个联合索引对于某个select语句，通过索引可以直接获取查询结果，而不再需要回表查询啦，就称该联合索引覆盖了这条select语句。

题解

添加device_id = user_profile.device_id 的条件，每次查询时会多比较一次。但是这种比较方式可以大幅度缩小数据集的范围，从而提高整个查询的效率。

当使用 where university = "北京大学" 这个条件进行查询时，如果这个列上没有建立索引，那么查询需要对所有的 university 列的值进行比较，来找出所有符合条件的记录。当找到满足 university = "北京大学" 这个条件的记录时，还需要对 device_id 列的值进行比较，才能确定哪些行是我们要查找的数据。

而如果条件改为 where university = "北京大学" and device_id = user_profile.device_id ，那么查询引擎会优先使用建立在 device_id 列上的索引进行查询，只查询符合 university = "北京大学" 条件的那些行，并且这些行的 device_id 字段必须和行本身的设备ID相等。

假设有一个数据集包含1000行，其中只有10行满足 university = "北京大学" 的条件。

如果只使用 university = "北京大学" 这个条件，则在比较完这1000行中所有符合条件的行后，还需根据每个符合条件的行的设备ID来进行一次比较。而通过添加 device_id = user_profile.device_id 这个条件，我们每次只比较满足 university = "北京大学" 的那10行，从而可以减少比较的次数，大幅度提高查询的效率。

sql “<>” 含义

背景

刷题时看到<>这种写法，很奇特特来记录

含义

<>表示不等于的意思，可以将其理解为 != 的同义词。用于判断两个值是否不相等

like 查询

字符匹配

一般形式为：

列名 [NOT ] LIKE

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

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

1	SELECT * ``FROM 学生表 ``WHERE 姓名 ``LIKE ‘张%’

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

1	SELECT * ``FROM 学生表 ``WHERE 姓名 ``LIKE '张__’

如果把姓名列的类型改为nchar(20)，在SQL Server 2012中执行没有结果。

原因是姓名列的类型是char(20)，当姓名少于20个汉字时，系统在存储这些数据时自动在后边补空格，空格作为一个字符，也参加LIKE的比较。可以用rtrim()去掉右空格。

1	SELECT * ``FROM 学生表 ``WHERE rtrim(姓名) ``LIKE '张__'

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

1	SELECT * ``FROM 学生表 ``WHERE 姓名 ``LIKE '[张李刘]%’

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

1	SELECT 姓名,学号 ``FROM 学生表 ``WHERE 姓名 ``LIKE '_[小大]%'

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

1	SELECT 姓名 ``FROM 学生 ``WHERE 姓名 ``NOT LIKE '刘%’

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

1	SELECT * ``FROM 学生表 ``WHERE 学号 ``LIKE '%[^235]'

操作符混合运用

描述

题目：现在运营想要找到gpa在3.5以上(不包括3.5)的山东大学用户 或 gpa在3.8以上(不包括3.8)的复旦大学同学进行用户调研，请你取出相应数据

示例：user_profile

id	device_id	gender	age	university	province	gpa
1	2138	male	21	北京大学	BeiJing	3.4
2	3214	male	NULL	复旦大学	Shanghai	4
3	6543	female	20	北京大学	BeiJing	3.2
4	2315	female	23	浙江大学	ZheJiang	3.6
5	5432	male	25	山东大学	Shandong	3.8

根据输入，你的查询应返回以下结果：(该题对于小数点后面的0不需要计算与统计，后台系统会统一输出小数点后面1位)

device_id	gender	age	university	gpa
3214	male	NULL	复旦大学	4
5432	male	25	山东大学	3.8

示例

输入：

drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`province` varchar(32)  NOT NULL,
`gpa` float);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学','BeiJing',3.4);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学','Shanghai',4.0);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学','BeiJing',3.2);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学','ZheJiang',3.6);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学','Shandong',3.8);

输出：

3214|male|None|复旦大学|4.0
5432|male|25|山东大学|3.8

解题

正常写

select
    device_id,
    gender,
    age,
    university,
    gpa
from
    user_profile
where
    (
        gpa > 3.5
        and university = "山东大学"
    )
    or (
        gpa > 3.8
        and university = "复旦大学"
    )

优化

select
    device_id,
    gender,
    age,
    university,
    gpa
from
    user_profile
where
    device_id in (
        select
            device_id
        from
            user_profile
        where
            gpa > 3.5
            and university = '山东大学'
    )
    or device_id in (
        select
            device_id
        from
            user_profile
        where
            gpa > 3.8
            and university = '复旦大学'
    );

小结

对比

第一种方式使用了逻辑操作符"or"连接两个条件，易于阅读和理解。

而第二种方式使用了子查询来进行两个条件的筛选。在处理大量数据时更有效率，因为子查询使用索引优化了，提高查询速度。 该查询可以进行性能优化的原因是因为它的过滤条件中包含了索引字段。

在这个查询中，过滤条件是通过使用 "gpa" 和 "university" 字段进行的。

如果针对这两个字段创建了索引，那么数据库可以快速定位满足条件的记录，从而提高查询性能。 当创建索引时，数据库会为每个索引字段创建一个索引结构，该结构包含了该字段的值和对应记录的引用。当执行查询时，数据库可以直接在索引中查找匹配的记录，而不需要逐行扫描整个数据表。这可以大大减少查询所需的时间和资源。

此外，由于查询中使用了 "or" 条件，数据库可以通过并行处理这两个条件中的子查询，从而进一步提高查询性能。

如果 "gpa" 和 "university" 字段没有建立索引，那么这个查询将会执行全表扫描（sequential scan），即便在使用了 "or" 条件时也没有重大区别。

为了提高查询性能，可以考虑为 "gpa" 和 "university" 字段建立索引。索引可以显着缩短数据库筛选符合条件的记录所需的时间，减轻数据库负担。如果无法在这些字段上建立索引，可以考虑优化查询语句本身。

例如，可以尝试在不使用 "or" 条件的情况下编写更特定的过滤条件以减少扫描的数据量。还可以尝试使用其他技术，例如分区表或集群，以减少扫描时的负载。

SQL 函数

函数

一、聚合函数 (Aggregate Functions)

SQL 中的聚合函数是一类用于对记录集 (Recordset) 中的数据进行计算和分析的函数。

1. COUNT 函数

COUNT 是 SQL 中最基础的聚合函数，主要用于计算指定列中值的数量。

用法：COUNT(column_name)

示例：计算 user_profile 表中所有记录的数量。

SELECT COUNT(*) FROM user_profile;

2. SUM 函数

SUM 函数用于计算某列中值的总和。

用法：SUM(column_name)

示例：计算 user_profile 表中所有用户的总GPA。

SELECT SUM(gpa) FROM user_profile;

3. AVG 函数

AVG 函数用于计算某列中值的平均值。

用法：AVG(column_name)

示例：计算 user_profile 表中所有用户的平均GPA。

SELECT AVG(gpa) FROM user_profile;

4. MAX 函数

MAX 函数用于计算某列中值的最大值。

用法：MAX(column_name)

示例：找出 user_profile 表中 GPA 值最高的记录。

SELECT * FROM user_profile WHERE gpa = (SELECT MAX(gpa) FROM user_profile);

5. MIN 函数

MIN 函数用于计算某列中值的最小值。

用法：MIN(column_name)

示例：找出 user_profile 表中 GPA 值最低的记录。

SELECT * FROM user_profile WHERE gpa = (SELECT MIN(gpa) FROM user_profile);

二、聚合函数 (String Functions)

SQL 中的字符串函数主要用于对字符串类型的数据进行处理，比如获取子字符串、转换字符串大小写等。

1. SUBSTRING 函数

SUBSTRING 函数用于返回一个字符串的子串。

用法：SUBSTRING(string_expression, start, length)

示例：从 user_profile 表中选出所有名字中包含“张”的用户。

SELECT * FROM user_profile WHERE SUBSTRING(name,1,1) = '张' ;

2. UPPER 函数

UPPER 函数用于将某个字符串转换为大写形式。

用法：UPPER(string_expression)

示例：将 user_profile 表中所有名字都转换为大写字母。

UPDATE user_profile SET name = UPPER(name);

3. LOWER 函数

LOWER 函数用于将某个字符串转换为小写形式。

用法：LOWER(string_expression)

示例：将 user_profile 表中所有名字都转换为小写字母。

UPDATE user_profile SET name = LOWER(name);

三、聚合函数 (Date and Time Functions)

SQL 中的日期和时间函数主要用于对时间戳类型的数据进行处理，比如格式化时间戳、获取时间间隔等。

1. GETDATE 函数

GETDATE 函数用于获取当前的系统时间。

用法：GETDATE()

示例：查看当前系统时间。

SELECT GETDATE();

2. DATEPART 函数

DATEPART 函数用于从一个时间类型的数据中提取某个特定的日期部分。

用法：DATEPART(datepart, date_expression)

示例：查看 user_profile 表中出生日期是哪个月的用户的数量。

SELECT COUNT(*) FROM user_profile WHERE DATEPART(month,birthday) = '6';

3. DATEDIFF 函数

DATEDIFF 函数用于计算时间间隔。

用法：DATEDIFF(interval, startdate, enddate)

示例：查看 user_profile 表中年龄在30岁以上的用户的数量。

SELECT COUNT(*) FROM user_profile WHERE DATEDIFF(year,birthday,GETDATE()) > 30;

四、聚合函数 (Logical Functions)

SQL 中的逻辑函数主要用于判断某个条件是否成立，并返回相应的结果。

1. IF 函数

IF 函数用于根据某个条件成立与否，返回不同的结果。

用法：IF(condition, value if true, value if false)

示例：统计 user_profile 表中女性和男性的数量。

SELECT COUNT(*) AS total, IF(gender = 'F', 'Female', 'Male') AS gender 
FROM user_profile 
GROUP BY gender;

2. CASE 函数

CASE 函数用于根据某个条件成立与否，返回不同的值。

用法：CASE WHEN condition THEN value1 ELSE value2 END

示例：将 user_profile 表中年龄大于等于 18 岁的用户标记为已成年，否则标记为未成年。

SELECT 
    name, 
    CASE WHEN DATEDIFF(YEAR, birthday, GETDATE()) >= 18 
        THEN 'Adult' 
        ELSE 'Minor' 
    END AS age_status 
FROM user_profile;

条件和循环语句

1. IF 语句： 在 SQL 中，可以使用 CASE 表达式来实现类似 if 的条件判断功能。下面是一个示例：

SELECT 
    column1, column2,
    CASE
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ELSE result3
    END AS alias_name
FROM
    table_name;

在以上语句中，根据条件 condition1 和 condition2，选择结果 result1 和 result2，并将之作为别名 alias_name 返回。

2. FOR/WHILE 循环： SQL 并不直接提供传统的 for/while 循环语句，但可以使用 WHILE 循环或者递归来实现类似的循环功能。

使用 WHILE 循环示例：

DECLARE @counter INT;
SET @counter = 1;
WHILE @counter <= 10
BEGIN
    -- 循环体内的语句
    -- 可以使用 BREAK 来跳出循环
    -- 可以使用 CONTINUE 跳过当前循环，进入下一次循环
    SET @counter = @counter + 1;
END

在以上示例中，设置一个计数器变量 @counter，通过判断 @counter 的值决定是否进行循环，循环体内的语句可以执行多次，直到满足循环终止条件为止。

join 和 where

描述

题目：运营想要计算一些参加了答题的不同学校、不同难度的用户平均答题量，请你写SQL取出相应数据

用户信息表：user_profile

id	device_id	gender	age	university	gpa	active_days_within_30	question_cnt	answer_cnt
1	2138	male	21	北京大学	3.4	7	2	12
2	3214	male	NULL	复旦大学	4	15	5	25
3	6543	female	20	北京大学	3.2	12	3	30
4	2315	female	23	浙江大学	3.6	5	1	2
5	5432	male	25	山东大学	3.8	20	15	70
6	2131	male	28	山东大学	3.3	15	7	13
7	4321	male	28	复旦大学	3.6	9	6	52

第一行表示:id为1的用户的常用信息为使用的设备id为2138，性别为男，年龄21岁，北京大学，gpa为3.4，在过去的30天里面活跃了7天，发帖数量为2，回答数量为12

最后一行表示:id为7的用户的常用信息为使用的设备id为4321，性别为男，年龄28岁，复旦大学，gpa为3.6，在过去的30天里面活跃了9天，发帖数量为6，回答数量为52

题库练习明细表：question_practice_detail

id	device_id	question_id	result
1	2138	111	wrong
2	3214	112	wrong
3	3214	113	wrong
4	6534	111	right
5	2315	115	right
6	2315	116	right
7	2315	117	wrong
8	5432	117	wrong
9	5432	112	wrong
10	2131	113	right
11	5432	113	wrong
12	2315	115	right
13	2315	116	right
14	2315	117	wrong
15	5432	117	wrong
16	5432	112	wrong
17	2131	113	right
18	5432	113	wrong
19	2315	117	wrong
20	5432	117	wrong
21	5432	112	wrong
22	2131	113	right
23	5432	113	wrong

第一行表示:id为1的用户的常用信息为使用的设备id为2138，在question_id为111的题目上，回答错误

......

最后一行表示:id为23的用户的常用信息为使用的设备id为5432，在question_id为113的题目上，回答错误

表：question_detail

id	question_id	difficult_level
1	111	hard
2	112	medium
3	113	easy
4	115	easy
5	116	medium
6	117	easy

第一行表示: 题目id为111的难度为hard

....

第一行表示: 题目id为117的难度为easy

请你写一个SQL查询，计算不同学校、不同难度的用户平均答题量，根据示例，你的查询应返回以下结果(结果在小数点位数保留4位，4位之后四舍五入)：

university	difficult_level	avg_answer_cnt
北京大学	hard	1.0000
复旦大学	easy	1.0000
复旦大学	medium	1.0000
山东大学	easy	4.5000
山东大学	medium	3.0000
浙江大学	easy	5.0000
浙江大学	medium	2.0000

解释：

第一行：北京大学有设备id为2138，6543这2个用户，这2个用户在question_practice_detail表下都只有一条答题记录，且答题题目是111，从question_detail可以知道这个题目是hard，故 北京大学的用户答题为hard的题目平均答题为2/2=1.0000

第二行，第三行：复旦大学有设备id为3214，4321这2个用户，但是在question_practice_detail表只有1个用户(device_id=3214有答题，device_id=4321没有答题，不计入后续计算)有2条答题记录，且答题题目是112，113各1个，从question_detail可以知道题目难度分别是medium和easy，故 复旦大学的用户答题为easy, medium的题目平均答题量都为1(easy=1或medium=1) /1 (device_id=3214)=1.0000

第四行，第五行：山东大学有设备id为5432和2131这2个用户，这2个用户总共在question_practice_detail表下有12条答题记录，且答题题目是112，113，117，且数目分别为3，6，3，从question_detail可以知道题目难度分别为medium,easy,easy，所以，easy共有9个，故easy的题目平均答题量= 9(easy=9)/2 (device_id=3214 or device_id=5432) =4.5000，medium共有3个，medium的答题只有device_id=5432的用户，故medium的题目平均答题量= 3(medium=9)/1 ( device_id=5432) =3.0000

.....

示例1

输入：

drop table if exists `user_profile`;
drop table if  exists `question_practice_detail`;
drop table if  exists `question_detail`;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int 
);
CREATE TABLE `question_practice_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL
);
CREATE TABLE `question_detail` (
`id` int NOT NULL,
`question_id`int NOT NULL,
`difficult_level` varchar(32) NOT NULL
);

INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);
INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(8,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(9,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(10,2131,113,'right');
INSERT INTO question_practice_detail VALUES(11,5432,113,'wrong');
INSERT INTO question_practice_detail VALUES(12,2315,115,'right');
INSERT INTO question_practice_detail VALUES(13,2315,116,'right');
INSERT INTO question_practice_detail VALUES(14,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(15,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(16,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(17,2131,113,'right');
INSERT INTO question_practice_detail VALUES(18,5432,113,'wrong');
INSERT INTO question_practice_detail VALUES(19,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(20,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(21,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(22,2131,113,'right');
INSERT INTO question_practice_detail VALUES(23,5432,113,'wrong');
INSERT INTO question_detail VALUES(1,111,'hard');
INSERT INTO question_detail VALUES(2,112,'medium');
INSERT INTO question_detail VALUES(3,113,'easy');
INSERT INTO question_detail VALUES(4,115,'easy');
INSERT INTO question_detail VALUES(5,116,'medium');
INSERT INTO question_detail VALUES(6,117,'easy');

复制

输出：

北京大学|hard|1.0000
复旦大学|easy|1.0000
复旦大学|medium|1.0000
山东大学|easy|4.5000
山东大学|medium|3.0000
浙江大学|easy|5.0000
浙江大学|medium|2.0000

解题

原答题

select 
    a.university,
    c.difficult_level, 
    round(count(a.device_id)/count(distinct a.device_id),4) avg_answer_cnt
from user_profile a,question_practice_detail b,question_detail c
where a.device_id = b.device_id and b.question_id = c.question_id
group by a.university,c.difficult_level;

优化

select 
    a.university,
    c.difficult_level, 
    round(count(a.device_id)/count(distinct a.device_id),4) avg_answer_cnt
from user_profile a
    join question_practice_detail b on a.device_id = b.device_id
    join question_detail c on b.question_id = c.question_id
group by a.university,c.difficult_level;

题解

在连接多个表时，JOIN的效率通常比WHERE子句的效率要高。这是因为JOIN操作会在内部进行表的连接，只返回满足连接条件的行，而WHERE子句则是在连接之后再筛选满足条件的行。以下是JOIN比WHERE高效的几个原因：

1. 数据筛选更早：JOIN操作在连接之前会先进行数据筛选，只将满足连接条件的行进行连接，而WHERE子句是在连接之后再进行筛选。这意味着JOIN可以减少数据量，提前过滤掉不需要的数据，减轻了后续的处理负担。
2. 内部优化：数据库管理系统通常会对JOIN进行内部优化处理，使用了各种优化技术如索引、哈希等来提高JOIN操作的效率。而WHERE子句的筛选则需要进行全表扫描或者索引查找，效率相对会较低。
3. 减少临时表和中间结果：JOIN操作在内部会生成临时表和中间结果，这些结果可以直接参与下一步的JOIN计算，减少了数据的存储和读写操作。而使用WHERE子句进行条件筛选时，需要进行多次查询和存储临时结果，增加了IO和内存的开销。

虽然JOIN通常比WHERE子句高效，但具体效果还会受到数据库的优化配置、表的大小、索引的使用等其他因素的影响。在实际应用中，根据数据的特点和查询的目标，选择合适的连接方式和优化策略是保证查询性能的关键。

union all

描述

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

示例：user_profile

id	device_id	gender	age	university	gpa	active_days_within_30	question_cnt	answer_cnt
1	2138	male	21	北京大学	3.4	7	2	12
2	3214	male		复旦大学	4	15	5	25
3	6543	female	20	北京大学	3.2	12	3	30
4	2315	female	23	浙江大学	3.6	5	1	2
5	5432	male	25	山东大学	3.8	20	15	70
6	2131	male	28	山东大学	3.3	15	7	13
7	4321	male	26	复旦大学	3.6	9	6	52

根据示例，你的查询应返回以下结果（注意输出的顺序，先输出学校为山东大学再输出性别为男生的信息）：

device_id	gender	age	gpa
5432	male	25	3.8
2131	male	28	3.3
2138	male	21	3.4
3214	male	None	4
5432	male	25	3.8
2131	male	28	3.3
4321	male	28	3.6

示例

输入：

drop table if exists `user_profile`;
drop table if  exists `question_practice_detail`;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int 
);
CREATE TABLE `question_practice_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL
);
CREATE TABLE `question_detail` (
`id` int NOT NULL,
`question_id`int NOT NULL,
`difficult_level` varchar(32) NOT NULL
);

INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);
INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(8,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(9,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(10,2131,113,'right');
INSERT INTO question_practice_detail VALUES(11,5432,113,'wrong');
INSERT INTO question_practice_detail VALUES(12,2315,115,'right');
INSERT INTO question_practice_detail VALUES(13,2315,116,'right');
INSERT INTO question_practice_detail VALUES(14,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(15,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(16,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(17,2131,113,'right');
INSERT INTO question_practice_detail VALUES(18,5432,113,'wrong');
INSERT INTO question_practice_detail VALUES(19,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(20,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(21,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(22,2131,113,'right');
INSERT INTO question_practice_detail VALUES(23,5432,113,'wrong');
INSERT INTO question_detail VALUES(1,111,'hard');
INSERT INTO question_detail VALUES(2,112,'medium');
INSERT INTO question_detail VALUES(3,113,'easy');
INSERT INTO question_detail VALUES(4,115,'easy');
INSERT INTO question_detail VALUES(5,116,'medium');
INSERT INTO question_detail VALUES(6,117,'easy');

输出：

5432|male|25|3.8
2131|male|28|3.3
2138|male|21|3.4
3214|male|None|4.0
5432|male|25|3.8
2131|male|28|3.3
4321|male|28|3.6

解题

select 
    device_id, gender, age, gpa
from user_profile
where university='山东大学'

union all

select 
    device_id, gender, age, gpa
from user_profile
where gender='male'

题解

NION和UNION ALL都是用来合并两个或多个SELECT语句的结果集的。

不同之处在于UNION会自动去重，而UNION ALL不会。

以下是使用UNION的示例：

假设有两个表：

Table1
+----+--------+
| ID |  Name  |
+----+--------+
|  1 | Alice  |
|  2 | Bob    |
|  3 | Charlie|
+----+--------+

Table2
+----+------+
| ID | Name |
+----+------+
|  3 | Dave |
|  4 | Eve  |
|  5 | Frank|
+----+------+

我们可以使用以下查询语句来将表1和表2中的数据合并：

SELECT Name FROM Table1
UNION
SELECT Name FROM Table2;

结果如下：

+--------+
|  Name  |
+--------+
| Alice  |
| Bob    |
| Charlie|
| Dave   |
| Eve    |
| Frank  |
+--------+

可以看到，结果中的重复行"Dave"被自动去除了。

下面是使用UNION ALL的示例：

SELECT Name FROM Table1
UNION ALL
SELECT Name FROM Table2;

结果如下：

+--------+
|  Name  |
+--------+
| Alice  |
| Bob    |
| Charlie|
| Dave   |
| Eve    |
| Frank  |
| Charlie|
| Dave   |
| Eve    |
| Frank  |
+--------+

可以看到，结果中出现了重复行"Dav"和"Eve"，因为我们使用了UNION ALL而不是UNION。

a = null 与 a is null 有什么区别

在SQL中，a = null和a is null两者间存在区别：

1. a = null这个表达式其实是不会返回真或假的，这是由NULL的特殊性质决定的，因为NULL表示值未知或者不存在。因此，即使a的值为NULL，执行a = null时结果也不是TRUE或FALSE，而是unknown。
2. a is null表示a的值为NULL，简单明了。

因此，a is null是判断a是否为NULL的首选方式。而对于a = null，则需要用IS NULL或IS NOT NULL代替。例如：

SELECT * FROM table WHERE col IS NULL;

SELECT * FROM table WHERE col IS NOT NULL;

而以下操作在SQL中是无效的：

SELECT * FROM table WHERE col = NULL;

SELECT * FROM table WHERE col != NULL;

CASE

描述

题目：现在运营想要将用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量

本题注意：age为null 也记为 25岁以下

示例：user_profile

id	device_id	gender	age	university	gpa	active_days_within_30	question_cnt	answer_cnt
1	2138	male	21	北京大学	3.4	7	2	12
2	3214	male		复旦大学	4	15	5	25
3	6543	female	20	北京大学	3.2	12	3	30
4	2315	female	23	浙江大学	3.6	5	1	2
5	5432	male	25	山东大学	3.8	20	15	70
6	2131	male	28	山东大学	3.3	15	7	13
7	4321	male	26	复旦大学	3.6	9	6	52

根据示例，你的查询应返回以下结果：

age_cut	number
25岁以下	4
25岁及以上	3

示例1

输入：

drop table if exists `user_profile`;
drop table if  exists `question_practice_detail`;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int 
);
CREATE TABLE `question_practice_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL
);
CREATE TABLE `question_detail` (
`id` int NOT NULL,
`question_id`int NOT NULL,
`difficult_level` varchar(32) NOT NULL
);

INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);
INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(8,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(9,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(10,2131,113,'right');
INSERT INTO question_practice_detail VALUES(11,5432,113,'wrong');
INSERT INTO question_practice_detail VALUES(12,2315,115,'right');
INSERT INTO question_practice_detail VALUES(13,2315,116,'right');
INSERT INTO question_practice_detail VALUES(14,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(15,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(16,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(17,2131,113,'right');
INSERT INTO question_practice_detail VALUES(18,5432,113,'wrong');
INSERT INTO question_practice_detail VALUES(19,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(20,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(21,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(22,2131,113,'right');
INSERT INTO question_practice_detail VALUES(23,5432,113,'wrong');
INSERT INTO question_detail VALUES(1,111,'hard');
INSERT INTO question_detail VALUES(2,112,'medium');
INSERT INTO question_detail VALUES(3,113,'easy');
INSERT INTO question_detail VALUES(4,115,'easy');
INSERT INTO question_detail VALUES(5,116,'medium');
INSERT INTO question_detail VALUES(6,117,'easy');

输出：

25岁以下|4
25岁及以上|3

解题

select 
    case
        when age < 25 or age is null
            then '25岁以下'
        when age >= 25
            then '25岁及以上'
    end age_cut
    ,count(*)
from user_profile
GROUP BY age_cut;

题解

CASE函数

是一种多分支的函数，可以根据条件列表的值返回多个可能的结果表达式中的一个。
可用在任何允许使用表达式的地方，但不能单独作为一个语句执行。
分为：
简单CASE函数
搜索CASE函数

简单 CASE函数

CASE 测试表达式
    WHEN 简单表达式1 THEN 结果表达式1
    WHEN 简单表达式2 THEN 结果表达式2 …
    WHEN 简单表达式n THEN 结果表达式n
    [ ELSE 结果表达式n+1 ]
END

计算测试表达式，按从上到下的书写顺序将测试表达式的值与每个WHEN子句的简单表达式进行比较。
如果某个简单表达式的值与测试表达式的值相等，则返回第一个与之匹配的WHEN子句所对应的结果表达式的值。
如果所有简单表达式的值与测试表达式的值都不相等，
若指定了ELSE子句,则返回ELSE子句中指定的结果表达式的值；
若没有指定ELSE子句，则返回NULL。

例. 查询班级表中的学生的班号、班名、系号和班主任号，并对系号作如下处理：
当系号为1时，显示 “计算机系”；
当系号为2时，显示 “软件工程系”；
当系号为3时，显示 “物联网系”。

SELECT 班号 ,班名,
CASE 系号
    WHEN 1 THEN '软件工程系'
    WHEN 2 THEN '计算机系'
    WHEN 3 THEN '物联网系'
END AS 系号,班主任号
FROM 班级表

搜索CASE函数

CASE
    WHEN 布尔表达式1 THEN 结果表达式1
    WHEN 布尔表达式2 THEN 结果表达式2 …
    WHEN 布尔表达式n THEN 结果表达式n
    [ ELSE 结果表达式n+1 ]
    END

按从上到下的书写顺序计算每个WHEN子句的布尔表达式。
返回第一个取值为TRUE的布尔表达式所对应的结果表达式的值。
如果没有取值为TRUE的布尔表达式，
则当指定了ELSE子句时,返回ELSE子句中指定的结果；
如果没有指定ELSE子句，则返回NULL。

例 用搜索CASE来做：

SELECT 班号 ,班名,
CASE
    WHEN 系号=1 THEN '软件工程系'
    WHEN 系号=2 THEN '计算机系'
    WHEN 系号=3 THEN '物联网系'
END AS 系号,班主任号
FROM 班级表

例.查询“M01F011”号课程的考试情况，列出学号、课程号和成绩，同时将百分制成绩显示为等级。

SELECT 学号,课程号,
CASE
    WHEN 成绩 >= 90 THEN '优'
    WHEN 成绩 BETWEEN 80 AND 89 THEN '良'
    WHEN 成绩 BETWEEN 70 AND 79 THEN '中'
    WHEN 成绩 BETWEEN 60 AND 69 THEN '及格'
    WHEN 成绩 <60 THEN '不及格'
END 成绩
FROM 成绩表
WHERE 课程号 = 'M01F011'

CASE函数(续)

例.统计每个班男生和女生的数量各是多少，统计结果的表头为，班号，男生数量，女生数量。

SELECT 班号,
    COUNT(CASE WHEN 性别=‘男’ THEN ‘男’ END) 男生数,
    COUNT(CASE WHEN 性别=‘女’ THEN ‘女’ END) 女生数
FROM 学生表 GROUP BY 班号

例.判断成绩的等级，85-100为“优”，70-84为“良”，60-69为“及格”，60以下为“不及格”，并统计每一等级的人数。

SELECT
CASE
    WHEN GRADE BETWEEN 85 AND 100 THEN '优'
    WHEN GRADE BETWEEN 70 AND 84 THEN '良'
    WHEN GRADE BETWEEN 60 AND 69 THEN '及格'
ELSE '不及格'
END 等级, COUNT(*) 人数
FROM SC
GROUP BY
CASE
    WHEN GRADE BETWEEN 85 AND 100 THEN '优'
    WHEN GRADE BETWEEN 70 AND 84 THEN '良'
    WHEN GRADE BETWEEN 60 AND 69 THEN '及格'
    ELSE '不及格'
END

date__计算用户8月每天的练题数量

描述

题目：现在运营想要计算出2021年8月每天用户练习题目的数量，请取出相应数据。

示例：question_practice_detail

id	device_id	question_id	result	date
1	2138	111	wrong	2021-05-03
2	3214	112	wrong	2021-05-09
3	3214	113	wrong	2021-06-15
4	6543	111	right	2021-08-13
5	2315	115	right	2021-08-13
6	2315	116	right	2021-08-14
7	2315	117	wrong	2021-08-15
……

根据示例，你的查询应返回以下结果：

day	question_cnt
13	5
14	2
15	3
16	1
18	1

示例1

输入：

drop table if exists `user_profile`;
drop table if  exists `question_practice_detail`;
drop table if  exists `question_detail`;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int 
);
CREATE TABLE `question_practice_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL,
`date` date NOT NULL
);
CREATE TABLE `question_detail` (
`id` int NOT NULL,
`question_id`int NOT NULL,
`difficult_level` varchar(32) NOT NULL
);

INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);
INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong','2021-05-03');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong','2021-06-15');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(8,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(9,3214,113,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(10,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(11,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(12,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(13,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(14,3214,112,'wrong','2021-08-16');
INSERT INTO question_practice_detail VALUES(15,3214,113,'wrong','2021-08-18');
INSERT INTO question_practice_detail VALUES(16,6543,111,'right','2021-08-13');
INSERT INTO question_detail VALUES(1,111,'hard');
INSERT INTO question_detail VALUES(2,112,'medium');
INSERT INTO question_detail VALUES(3,113,'easy');
INSERT INTO question_detail VALUES(4,115,'easy');
INSERT INTO question_detail VALUES(5,116,'medium');
INSERT INTO question_detail VALUES(6,117,'easy');

输出：

13|5
14|2
15|3
16|1
18|1

解题

select 
    day(date) as day,
    count(question_id) as question_cnt
from question_practice_detail
where month(date) = 8 and year(date) = 2021
group by date;

题解

日期函数

DAYOFWEEK(date)

返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)。这些索引值对应于ODBC标准。

mysql> select DAYOFWEEK('1998-02-03')

WEEKDAY(date)

返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)。

mysql> select WEEKDAY('1997-10-04 22:23:00');

DAYOFMONTH(date)

返回date的月份中日期，在1到31范围内。

mysql> select DAYOFMONTH('1998-02-03');

DAYOFYEAR(date)

返回date在一年中的日数, 在1到366范围内。

mysql> select DAYOFYEAR('1998-02-03');

MONTH(date)

返回date的月份，范围1到12。

mysql> select MONTH('1998-02-03');

DAYNAME(date)

返回date的星期名字。

mysql> select DAYNAME("1998-02-05");

MONTHNAME(date)

返回date的月份名字。

mysql> select MONTHNAME("1998-02-05");

QUARTER(date)

返回date一年中的季度，范围1到4。

mysql> select QUARTER('98-04-01');

WEEK(date)

对于星期天是一周的第一天的地方，有一个单个参数，返回date的周数，范围在0到52。

mysql> select WEEK('1998-02-20');

WEEK(date,first)

2个参数形式WEEK()允许你指定星期是否开始于星期天或星期一。
如果第二个参数是0，星期从星期天开始，
如果第二个参数是1，从星期一开始。

mysql> select WEEK('1998-02-20',0);

mysql> select WEEK('1998-02-20',1);

YEAR(date)

返回date的年份，范围在1000到9999。

mysql> select YEAR('98-02-03');

HOUR(time)

返回time的小时，范围是0到23。

mysql> select HOUR('10:05:03');

MINUTE(time)

返回time的分钟，范围是0到59。

mysql> select MINUTE('98-02-03 10:05:03');

SECOND(time)

回来time的秒数，范围是0到59。

mysql> select SECOND('10:05:03');

PERIOD_ADD(P,N)

增加N个月到阶段P(以格式YYMM或YYYYMM)。以格式YYYYMM返回值。注意阶段参数P不是日期值。

mysql> select PERIOD_ADD(9801,2);

PERIOD_DIFF(P1,P2)

返回在时期P1和P2之间月数，P1和P2应该以格式YYMM或YYYYMM。注意，时期参数P1和P2不是日期值。

mysql> select PERIOD_DIFF(9802,199703);

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

描述

题目：现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率。请你取出相应数据。

示例：question_practice_detail

id	device_id	quest_id	result	date
1	2138	111	wrong	2021-05-03
2	3214	112	wrong	2021-05-09
3	3214	113	wrong	2021-06-15
4	6543	111	right	2021-08-13
5	2315	115	right	2021-08-13
6	2315	116	right	2021-08-14
7	2315	117	wrong	2021-08-15
……

根据示例，你的查询应返回以下结果：

avg_ret
0.3000

示例1

输入：

drop table if exists `user_profile`;
drop table if  exists `question_practice_detail`;
drop table if  exists `question_detail`;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int 
);
CREATE TABLE `question_practice_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL,
`date` date NOT NULL
);
CREATE TABLE `question_detail` (
`id` int NOT NULL,
`question_id`int NOT NULL,
`difficult_level` varchar(32) NOT NULL
);

INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);
INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong','2021-05-03');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong','2021-06-15');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(8,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(9,3214,113,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(10,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(11,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(12,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(13,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(14,3214,112,'wrong','2021-08-16');
INSERT INTO question_practice_detail VALUES(15,3214,113,'wrong','2021-08-18');
INSERT INTO question_practice_detail VALUES(16,6543,111,'right','2021-08-13');
INSERT INTO question_detail VALUES(1,111,'hard');
INSERT INTO question_detail VALUES(2,112,'medium');
INSERT INTO question_detail VALUES(3,113,'easy');
INSERT INTO question_detail VALUES(4,115,'easy');
INSERT INTO question_detail VALUES(5,116,'medium');
INSERT INTO question_detail VALUES(6,117,'easy');

输出：

0.3000

解题

select count(q2.device_id) / count(q1.device_id) as avg_ret
from
    (select distinct device_id, date
    from question_practice_detail) as q1
left join
    (select distinct device_id, date
    from question_practice_detail) as q2
on q1.device_id = q2.device_id and q2.date = date_add(q1.date, interval 1 day)

substring_index

描述

题目：现在运营举办了一场比赛，收到了一些参赛申请，表数据记录形式如下所示，现在运营想要统计每个性别的用户分别有多少参赛者，请取出相应结果

示例：user_submit

device_id	profile	blog_url
2138	180cm,75kg,27,male	http:/url/bigboy777
3214	165cm,45kg,26,female	http:/url/kittycc
6543	178cm,65kg,25,male	http:/url/tiger
4321	171cm,55kg,23,female	http:/url/uhksd
2131	168cm,45kg,22,female	http:/urlsydney

根据示例，你的查询应返回以下结果：

gender	number
male	2
female	3

示例1

输入：

drop table if exists user_submit;
CREATE TABLE `user_submit` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`profile` varchar(100) NOT NULL,
`blog_url` varchar(100) NOT NULL
);
INSERT INTO user_submit VALUES(1,2138,'180cm,75kg,27,male','http:/url/bisdgboy777');
INSERT INTO user_submit VALUES(1,3214,'165cm,45kg,26,female','http:/url/dkittycc');
INSERT INTO user_submit VALUES(1,6543,'178cm,65kg,25,male','http:/url/tigaer');
INSERT INTO user_submit VALUES(1,4321,'171cm,55kg,23,female','http:/url/uhsksd');
INSERT INTO user_submit VALUES(1,2131,'168cm,45kg,22,female','http:/url/sysdney');

输出：

male|2
female|3

解题

方法一

select 
    substring_index(profile,',',-1) gender ,
    count(*) number
from user_submit
group by gender;

方法二

SELECT IF(profile LIKE '%female','female','male') gender,COUNT(*) number
FROM user_submit
GROUP BY gender;

题解

substring_index(str,delim,count)

str:要处理的字符串

delim:分隔符

count:计数

例子：str=www.wikidm.cn

      substring_index(str,'.',1)

      结果是：www

      substring_index(str,'.',2)

      结果是：www.wikidm

      也就是说，如果count是正数，那么就是从左往右数，第N个分隔符的左边的全部内容

      相反，如果是负数，那么就是从右边开始数，第N个分隔符右边的所有内容，如：

      substring_index(str,'.',-2)

      结果为：wikidm.cn

     有人会问，如果我要中间的的wikidm怎么办？

     很简单的，两个方向：

      从右数第二个分隔符的右边全部，再从左数的第一个分隔符的左边：

　　substring_index(substring_index(str,'.',-2),'.',1);

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

描述

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

示例： user_profile

id	device_id	gender	age	university	gpa	active_days_within_30	question_cnt	answer_cnt
1	2138	male	21	北京大学	3.4	7	2	12
2	3214	male		复旦大学	4	15	5	25
3	6543	female	20	北京大学	3.2	12	3	30
4	2315	female	23	浙江大学	3.6	5	1	2
5	5432	male	25	山东大学	3.8	20	15	70
6	2131	male	28	山东大学	3.3	15	7	13
7	4321	female	26	复旦大学	3.6	9	6	52

示例： question_practice_detail

id	device_id	question_id	result
1	2138	111	wrong
2	3214	112	wrong
3	3214	113	wrong
4	6543	111	right
5	2315	115	right
6	2315	116	right
7	2315	117	wrong

示例： question_detail

question_id	difficult_level
111	hard
112	medium
113	easy
115	easy
116	medium
117	easy

根据示例，你的查询应返回以下结果：

difficult_level	correct_rate
easy	0.5000
medium	1.0000

示例1

输入：

drop table if exists `user_profile`;
drop table if  exists `question_practice_detail`;
drop table if  exists `question_detail`;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int 
);
CREATE TABLE `question_practice_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL,
`date` date NOT NULL
);
CREATE TABLE `question_detail` (
`id` int NOT NULL,
`question_id`int NOT NULL,
`difficult_level` varchar(32) NOT NULL
);

INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);
INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong','2021-05-03');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong','2021-06-15');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(8,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(9,3214,113,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(10,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(11,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(12,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(13,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(14,3214,112,'wrong','2021-08-16');
INSERT INTO question_practice_detail VALUES(15,3214,113,'wrong','2021-08-18');
INSERT INTO question_practice_detail VALUES(16,6543,111,'right','2021-08-13');
INSERT INTO question_detail VALUES(1,111,'hard');
INSERT INTO question_detail VALUES(2,112,'medium');
INSERT INTO question_detail VALUES(3,113,'easy');
INSERT INTO question_detail VALUES(4,115,'easy');
INSERT INTO question_detail VALUES(5,116,'medium');
INSERT INTO question_detail VALUES(6,117,'easy');

输出：

easy|0.5000
medium|1.0000

解题

select
    qd.difficult_level,
    sum(
        case
            when qpd.result = 'right' then 1
            else 0
        end
    ) / count(1) as correct_rate
    ,
from
    question_practice_detail qpd
    join user_profile up on qpd.device_id = up.device_id
    join question_detail qd on qd.question_id = qpd.question_id
where
    up.university = '浙江大学'
group by
    qd.difficult_level
order by
    correct_rate