基本使用MySQL
SQL语句执行顺序 摘自《MySQL技术内幕:SQL编程》
FROM - ON - JOIN - WHERE - GROUP BY - WITH - HAVING - SELECT - DISTINCT - ORDER BY - LIMIT
有三类后面可以加聚合函数
1、SELECT
2、ORDER BY
3、HAVING
底层原理:对于执行顺序来说 group by 确定了 聚合函数的作用范围 where 语句先执行 不知道聚合函数的作用范围 所以 where 语句并不能跟聚合函数 Having 在groupby之后执行 使用聚合语句可以知道其作用范围 故可以使用
COUNT()
函数
在MySQL中,COUNT()
函数用于计算指定列中的行数。它通常与 SELECT
语句一起使用,用于获取满足条件的行数。
基本语法如下:
SELECT COUNT(column_name) FROM table_name WHERE condition;
其中:
column_name
是要计数的列名,可以是具体的列名,也可以是*
代表所有列。table_name
是要查询的表名。condition
是可选的筛选条件,用于过滤要计数的行。
在SQL中,DISTINCT
关键字用于去除查询结果中重复的行,仅保留唯一的行。它通常与 SELECT
语句一起使用,用于对查询结果进行去重。
基本语法如下:
SELECT DISTINCT column_name1, column_name2, ...
FROM table_name
WHERE condition;
其中:
column_name1, column_name2, ...
是要选择的列名,可以是一个或多个列。table_name
是要查询的表名。condition
是可选的筛选条件,用于过滤结果集。
ROUND()
函数
在 SQL 中,ROUND()
函数用于将数值进行四舍五入。它可以将数字舍入到指定的小数位数。
基本语法如下:
ROUND(number, decimals)
number
是要进行四舍五入的数字。decimals
是要保留的小数位数,可选参数,默认为 0。
CROSS JOIN
CROSS JOIN
是 SQL 中的一种连接操作,它返回两个表的笛卡尔积,即将第一个表中的每一行与第二个表中的每一行进行组合。在进行 CROSS JOIN
操作时,不需要任何条件来匹配两个表中的行,因此它会返回所有可能的组合。
基本语法如下:
SELECT *
FROM table1
CROSS JOIN table2;
其中:
table1
和table2
是要进行连接的两个表。
在实际应用中,要谨慎使用 CROSS JOIN
,因为它会生成非常庞大的结果集。通常情况下,可以使用 INNER JOIN
、LEFT JOIN
等其他类型的连接来更精确地匹配两个表中的行。
HAVING
HAVING
是 SQL 中用于筛选分组数据的条件。与 WHERE
关键字不同的是,HAVING
关键字用于在对数据进行分组之后对分组结果进行筛选,而 WHERE
关键字用于在对数据进行分组之前对原始数据进行筛选。
示例用法感受差异:
基本语法
SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name
HAVING condition;
其中:
column_name
是要查询的列名。aggregate_function(column_name)
是对列进行聚合操作的函数,如COUNT()
、SUM()
、AVG()
等。table_name
是要查询的表名。GROUP BY
子句用于对结果集进行分组。condition
是筛选分组结果的条件,类似于WHERE
子句,但是在分组之后执行。
示例用法:
假设有一个学生表 students
,包含 name
和 score
两列,我们想要计算每个班级的平均分,并筛选出平均分大于 80 的班级。
SELECT class, AVG(score) AS avg_score
FROM students
GROUP BY class
HAVING AVG(score) > 80;
在这个示例中,GROUP BY class
将数据按班级分组,然后 AVG(score)
计算了每个班级的平均分。HAVING AVG(score) > 80
这一条件筛选出平均分大于 80 的班级。
LIMIT
关键字
在 SQL 中,LIMIT
关键字用于限制 SELECT
查询返回的行数。它通常用于在查询结果中获取指定数量的行,或者用于分页查询。
基本语法如下:
SELECT column_name1, column_name2, ...
FROM table_name
LIMIT number;
其中:
column_name1, column_name2, ...
是要选择的列名。table_name
是要查询的表名。number
是要返回的行数。
实现分页查询,获取指定页数的记录:
SELECT * FROM table_name
LIMIT offset, N;
其中 offset
是要跳过的行数,N
是要返回的行数。
INNER JOIN
和 LEFT JOIN
是 SQL 中常用的两种连接(JOIN)操作,它们用于将两个或多个表中的数据组合在一起。
INNER JOIN
:
INNER JOIN
又称为内连接,它返回两个表中满足连接条件的行。如果两个表中的行在连接条件上没有匹配,则不会包含在结果集中。- 基本语法:
SELECT table1.column1, table2.column2... FROM table1 INNER JOIN table2 ON table1.column = table2.column;
- 示例:
SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
-
LEFT JOIN
:LEFT JOIN
又称为左连接,它返回左表中的所有行,以及与右表中满足连接条件的行。如果右表中没有与左表匹配的行,则将返回 NULL 值。- 基本语法:
SELECT table1.column1, table2.column2... FROM table1 LEFT JOIN table2 ON table1.column = table2.column;
- 示例:
SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
- 当我们只需要返回两个表中满足连接条件的行时,使用
INNER JOIN
。 - 当我们需要返回左表中的所有行,以及与右表中满足连接条件的行时,使用
LEFT JOIN
。
UNION
UNION
是 SQL 中用于合并两个或多个 SELECT
查询结果集的操作符。它用于将两个查询的结果集合并成一个结果集,同时去除重复的行。
基本语法如下:
SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;
其中:
每个 SELECT
子句都必须具有相同数量的列。 列的数据类型必须兼容,或者可以进行隐式类型转换。 结果集中的列名由第一个 SELECT
子句中的列名决定。 默认情况下,UNION
操作会自动去除重复的行。如果需要包含重复的行,可以使用 UNION ALL
。
窗口函数!!!
OVER(PARTITION BY)
是 SQL 中用于在窗口函数中进行分区操作的语法。它允许你将结果集分成若干个逻辑分区,并在每个分区内进行聚合、排序等操作。
基本语法如下:
aggregate_function() OVER (PARTITION BY partition_column ORDER BY order_column)
其中:
aggregate_function()
是要应用的聚合函数,如SUM()
、AVG()
、ROW_NUMBER()
等。OVER
关键字指定窗口函数的范围。PARTITION BY partition_column
是可选的,用于定义窗口函数的分区方式。ORDER BY order_column
是可选的,用于定义排序顺序。
示例用法:
假设有一个订单表 orders
,包含 customer_id
、order_date
和 total_amount
列,我们想要计算每个客户的订单总额,并按客户进行分区。
SELECT
student_id,
subject,
score,
RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS ranking
FROM grades;
在这个示例中,RANK() OVER (PARTITION BY subject ORDER BY score DESC)
将结果集按照 subject
列进行分区,并在每个分区内按照 score
列降序排序,然后为每个学生的成绩计算排名。
CASE
表达式
在 SQL 中,通常使用 CASE
表达式来实现条件判断,而不是 IF
。CASE
表达式允许根据条件执行不同的逻辑,并返回不同的结果。
基本语法如下:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE default_result
END
其中:
condition1
,condition2
等是条件表达式,可以是任意逻辑表达式。result1
,result2
等是条件满足时返回的结果。default_result
是当所有条件都不满足时返回的默认结果。
IF
函数
在MySQL中,也可以使用 IF
函数来实现条件判断。IF
函数接受三个参数:条件表达式、满足条件时返回的值、不满足条件时返回的值。
IF(condition, value_if_true, value_if_false)
其中:
condition
是条件表达式,可以是任意逻辑表达式。value_if_true
是条件满足时返回的值。value_if_false
是条件不满足时返回的值。
逻辑表达式
逻辑表达式是指在计算机编程和数学中用于表示逻辑关系的表达式。在 SQL 中,逻辑表达式通常用于条件判断,例如 WHERE
子句中的条件、IF
函数中的条件等等。常见的逻辑运算符包括:
- 等于:
=
(或==
) - 不等于:
<>
(或!=
) - 大于:
>
- 小于:
<
- 大于等于:
>=
- 小于等于:
<=
- 逻辑与:
AND
- 逻辑或:
OR
- 逻辑非:
NOT
另外,SQL 还支持 BETWEEN
、IN
、LIKE
等运算符,它们也常用于构建逻辑表达式,用于更复杂的条件判断。
RANK()
函数
在 SQL 中,RANK()
函数用于为结果集中的行分配排名。它按照指定的排序条件对行进行排序,并为每行分配一个排名,如果有多行具有相同的排序条件,则它们将共享同一个排名,接下来的排名将被跳过,因此可能会出现不连续的排名。
基本语法如下:
RANK() OVER (PARTITION BY partition_column ORDER BY order_column)
其中:
PARTITION BY partition_column
是可选的,用于指定分组条件,如果省略,则对整个结果集进行排名。ORDER BY order_column
是必需的,用于指定排序条件。
REGEXP
在 SQL 中,REGEXP
是用于在字符串中执行正则表达式匹配的操作符。它通常与 SELECT
语句的 WHERE
子句一起使用,用于筛选出符合特定模式的字符串。
SELECT column_name
FROM table_name
WHERE column_name REGEXP pattern;
其中:
column_name
是要进行正则表达式匹配的列名。table_name
是要查询的表名。pattern
是要匹配的正则表达式模式
示例
SELECT employee_name
FROM employees
WHERE employee_name REGEXP 'John';
在 SQL 中使用 REGEXP
进行正则表达式匹配时,你可以在 REGEXP
后面提供一个符合正则表达式语法的模式。这个模式将用于匹配相应的列数据。
正则表达式模式可以根据你的具体需求而定,它可以包含各种元字符、字符类和量词,用于定义匹配的规则。以下是一些常见的正则表达式元字符和示例:
.
: 匹配任意字符。^
: 匹配字符串的开头。$
: 匹配字符串的结尾。*
: 匹配前面的元素零次或多次。+
: 匹配前面的元素一次或多次。?
: 匹配前面的元素零次或一次。[]
: 字符类,匹配方括号中任意一个字符。|
: 逻辑或,匹配两个模式中的任意一个。\
: 转义字符,用于转义元字符。
示例:
- 匹配以 "John" 开头的字符串:
^John
- 匹配以 "John" 结尾的字符串:
John$
- 匹配包含 "John" 的字符串:
John
- 匹配以 "John" 开头且以 "Doe" 结尾的字符串:
^John.*Doe$
- 匹配任意包含 "John" 或 "Jane" 的字符串:
(John|Jane)
根据你的具体需求和数据情况,可以编写相应的正则表达式模式来进行匹配。需要注意的是,正则表达式的语法和规则因数据库系统而异,因此在使用时需要查阅相应数据库系统的文档以了解支持的正则表达式语法。
LIKE
在 SQL 中,LIKE
是用于在字符串中执行模式匹配的操作符。它通常与 SELECT
语句的 WHERE
子句一起使用,用于筛选出符合特定模式的字符串。
基本语法如下:
SELECT column_name
FROM table_name
WHERE column_name LIKE pattern;
其中:
column_name
是要进行模式匹配的列名。table_name
是要查询的表名。pattern
是要匹配的模式,可以包含特殊的通配符。
LIKE
操作符支持以下通配符:
%
:表示任意字符序列(包括零个字符)。_
:表示单个字符。
GROUP_CONCAT()
函数
在 SQL 中,GROUP_CONCAT()
函数用于将多行数据按照指定的分隔符连接成单个字符串。通常用于聚合查询结果中某一列的值。
基本语法如下:
GROUP_CONCAT(column_name SEPARATOR separator)
其中:
column_name
是要连接的列名。separator
是可选的,用于指定连接后的分隔符,默认为逗号,
。
子查询、窗口函数和表连接选择
子查询、窗口函数和表连接是 SQL 中常用的操作,它们在不同的情况下有不同的用途。
-
子查询(Subquery):
- 当你需要在查询中使用另一个查询的结果作为条件时,可以使用子查询。子查询可以嵌套在主查询的
SELECT
、FROM
、WHERE
、HAVING
子句中,用于过滤、计算或检索数据。 - 例如,当你需要查找某个表中满足某个条件的行,但该条件需要从另一个表中获取,或者需要基于子查询的结果进行进一步的过滤和计算时,可以使用子查询。
- 当你需要在查询中使用另一个查询的结果作为条件时,可以使用子查询。子查询可以嵌套在主查询的
-
窗口函数(Window Function):
- 当你需要对查询结果集中的行执行聚合、排序或分析操作,并保留原始行的完整性时,可以使用窗口函数。窗口函数可以在结果集的行上执行计算,并返回额外的列,而不改变查询结果集的行数。
- 例如,当你需要为每个分组计算聚合值,同时保留每个分组内原始行的信息时,可以使用窗口函数。
-
表连接(Table Join):
- 当你需要将多个表中的数据合并在一起时,可以使用表连接。表连接允许你通过共享的列将多个表中的行合并在一起,并且可以根据连接条件过滤结果。
- 例如,当你需要从多个表中检索相关联的数据,或者需要将数据从多个表中组合以执行复杂的查询时,可以使用表连接。
总之,使用子查询时主要是为了获取条件数据,使用窗口函数时是为了对结果集中的行进行聚合和分析,而使用表连接时是为了合并来自不同表的相关数据。根据具体的查询需求和数据结构,选择合适的方法来实现查询操作。
连续刷题
题目:现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率。请你取出相应数据。
select count(t2.date)/count(t1.date) from
(select distinct device_id,date from question_practice_detail) t1
left join
(select distinct device_id,date from question_practice_detail) t2
on t1.device_id = t2.device_id
and t2.date = date_add(t1.date,interval 1 day)
从此题了解了distinct可以做双重去重。
字符串截取函数
1、LOCATE(substr , str ):返回子串 substr 在字符串 str 中第一次出现的位置,如果字符substr在字符串str中不存在,则返回0;
2、POSITION(substr IN str ):返回子串 substr 在字符串 str 中第一次出现的位置,如果字符substr在字符串str中不存在,与LOCATE函数作用相同;
3、LEFT(str, length):从左边开始截取str,length是截取的长度;
4、RIGHT(str, length):从右边开始截取str,length是截取的长度;
5、SUBSTRING_INDEX(str ,substr ,n):返回字符substr在str中第n次出现位置之前的字符串;
6、SUBSTRING(str ,n ,m):返回字符串str从第n个字符截取到第m个字符;
7、REPLACE(str, n, m):将字符串str中的n字符替换成m字符;
8、LENGTH(str):计算字符串str的长度。
由截取profile中的性别学会了应用5
profile | |
180cm,75kg,27,male |
substring_index(profile,',',-1) as gender
进一步地:选择博客地址中的用户id,例如 ‘http:/ur/bisdgboy777’ 👉🏻'bisdgboy777'
熟悉文本操作函数
1.replace
2.substr
3.substring_index
4.trim
代码
-- 方法1 替换
-- select device_id, replace(blog_url,'http:/url/','') as user_name
-- from user_submit;
-- 方法2 截取
-- select device_id, substr(blog_url,11,length(blog_url)-10) as user_name
-- from user_submit;
-- 方法3 删除
-- select device_id, trim('http:/url/' from blog_url) as user_name
-- from user_submit;
-- 方法4 字段切割
select device_id, substring_index(blog_url,'/',-1) as user_name
from user_submit;
小心截取中间时不要忘记切断开头或者屁股
题目: 现在运营想要了解复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况,请取出相应明细数据,对于在8月份没有练习过的用户,答题数结果返回0.
示例:用户信息表user_profile
id | device_id | gender | age | university | gpa | active_days_within_30 |
1 | 2138 | male | 21 | 北京大学 | 3.4 | 7 |
2 | 3214 | male | 复旦大学 | 4.0 | 15 | |
3 | 6543 | female | 20 | 北京大学 | 3.2 | 12 |
4 | 2315 | female | 23 | 浙江大学 | 3.6 | 5 |
5 | 5432 | male | 25 | 山东大学 | 3.8 | 20 |
6 | 2131 | male | 28 | 山东大学 | 3.3 | 15 |
7 | 4321 | female | 26 | 复旦大学 | 3.6 | 9 |
示例: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 |
思路:
1、用左连接把user和question连接起来,那么我们得到的结果首先就是所有的user的基本信息,如果答题了就会有答题的相关数据,如果没答题那么答题的相关数据为null。
2、COUNT(q.question_id)聚合函数是不计空值的,所以遇到空值默认为0了; 利用SUM() 和case when... then去计算答对的题数。
3、筛选条件时要注意月份,只敲MONTH(date) = 8是不够的,这样会遗漏那些没有答题的同学,所以要加上MONTH(date) IS NULL。
4、最后GROUP BY device_id求出所有复旦大学的同学的情况
SELECT
u.device_id,
u.university,
COUNT(q.question_id) AS question_cnt,
SUM(CASE WHEN result = 'right' THEN 1
ELSE 0 END) AS right_question_cnt
FROM user_profile u
LEFT JOIN question_practice_detail q
ON u.device_id = q.device_id
WHERE university = '复旦大学' AND (MONTH(date) = 8 OR MONTH(date) IS NULL)
GROUP BY device_id;