mysql 相减的聚合函数 mysql相加函数

转载

新新人类 2023-08-02 20:51:07

文章标签 mysql 相减的聚合函数 mysql 数据库 sql 表名 文章分类 MySQL 数据库

文章目录

新增 (Create)

全列插入
指定列插入

查询 (Retrieve)

全列查询
指定列查询
条件查询

关系元素运算符
模糊查询

分页查询
去重：DISTINCT
别名：AS
升序 or 降序

更新 (Update)
删除 (Delete)
分组（GROUP BY）
联合查询

内连接（inner join）
自连接
外连接

左连接（left join）
右连接（left join）

子查询（嵌套查询）
合并查询

UNION
UNION ALL

聚合函数（复合函数）

最大最小
总数、总和、平均值、保留小数

条件函数

IF
CASE

日期函数

时间戳和日期的转换
年月日截取
日期差计算

文本函数

新增 (Create)

全列插入

语法

INSERT INTO [表名] VALUES(参数1， 参数2， 参数3......);

示例

INSERT INTO book VALUES(4, "C++Primer", 99.9, "2000-08-02", 103);

mysql> SELECT * FROM book
    -> ;
+------+-----------+-------+------------+------+
| id   | name      | price | publish    | num  |
+------+-----------+-------+------------+------+
|    4 | C++Primer |   100 | 2000-08-02 |  103 |
+------+-----------+-------+------------+------+
1 rows in set (0.00 sec)

这里 price 的值为 100 而不是 99.9 是因为 默认decimal为（10，0） ，也就是 0 位小数，保存的时候将小数点后面的值四舍五入。

mysql 相减的聚合函数 mysql相加函数_表名

指定列插入

即只插入部分列内容

语法

INSERT INTO [表名](列1，列2，列3.......) values(参数1， 参数2， 参数3......);

示例

mysql> INSERT INTO book(id,name,price)
    -> VALUES(8,"红楼梦",94.4);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> SELECT * FROM book;
+------+-----------+-------+------------+------+
| id   | name      | price | publish    | num  |
+------+-----------+-------+------------+------+
|    4 | C++Primer |   100 | 2000-08-02 |  103 |
|    8 | 红楼梦    |    94 | NULL       | NULL |
+------+-----------+-------+------------+------+
2 rows in set (0.00 sec)

查询 (Retrieve)

全列查询

语法

SELECT * FROM [表名];

示例

mysql> SELECT * FROM book;
+------+-----------+-------+------------+------+
| id   | name      | price | publish    | num  |
+------+-----------+-------+------------+------+
|    4 | C++Primer |   100 | 2000-08-02 |  103 |
|    8 | 红楼梦    |    94 | NULL       | NULL |
+------+-----------+-------+------------+------+
2 rows in set (0.00 sec)

指定列查询

语法

SELECT 列1，列2，列3..... FROM [表名];

示例

mysql> SELECT name, price FROM book;
+-----------+-------+
| name      | price |
+-----------+-------+
| C++Primer |   100 |
| 红楼梦    |    94 |
+-----------+-------+
2 rows in set (0.00 sec)

条件查询

关系元素运算符

mysql 相减的聚合函数 mysql相加函数_mysql 相减的聚合函数_02

语法

SELECT * FROM [表名] WHERE [条件];

示例

mysql> SELECT * FROM book WHERE id=8;
+------+-----------+-------+---------+------+
| id   | name      | price | publish | num  |
+------+-----------+-------+---------+------+
|    8 | 红楼梦    |    94 | NULL    | NULL |
+------+-----------+-------+---------+------+
1 row in set (0.00 sec)

模糊查询

语法

SELECT * FROM [表名] WHERE [列名] LIKE ”%XX%“
// 查询名字中带XX的数据
SELECT * FROM [表名] WHERE [列名] LIKE ”XX%“
// 查询名字中以XX开头的数据
SELECT * FROM [表名] WHERE [列名] LIKE ”%XX“
// 查询名字中以XX结尾的数据

分页查询

语法

SELECT * FROM [表名] LIMIT [每页条数] OFFSET [偏移条数];

示例

mysql> SELECT * FROM book;
+------+-----------+-------+------------+------+
| id   | name      | price | publish    | num  |
+------+-----------+-------+------------+------+
|    4 | C++Primer |   100 | NULL       |  103 |
|    4 | C++Primer |   100 | 2000-08-02 |  103 |
|    8 | 红楼梦    |    94 | NULL       | NULL |
+------+-----------+-------+------------+------+
3 rows in set (0.00 sec)

// 限制显示条数
mysql> SELECT * FROM book LIMIT 1;
+------+-----------+-------+---------+------+
| id   | name      | price | publish | num  |
+------+-----------+-------+---------+------+
|    4 | C++Primer |   100 | NULL    |  103 |
+------+-----------+-------+---------+------+
1 row in set (0.00 sec)

// 限制显示条数、规定偏移量
mysql> SELECT * FROM book LIMIT 1 OFFSET 1;
+------+-----------+-------+------------+------+
| id   | name      | price | publish    | num  |
+------+-----------+-------+------------+------+
|    4 | C++Primer |   100 | 2000-08-02 |  103 |
+------+-----------+-------+------------+------+
1 row in set (0.00 sec)

// 剩余数据不足LIMIT限制时，不会报错，而是输出所有剩余的
mysql> SELECT * FROM book LIMIT 2 OFFSET 2;
+------+-----------+-------+---------+------+
| id   | name      | price | publish | num  |
+------+-----------+-------+---------+------+
|    8 | 红楼梦    |    94 | NULL    | NULL |
+------+-----------+-------+---------+------+
1 row in set (0.00 sec)

去重：DISTINCT

语法

SELECT DISTINCT * FROM [表名];

示例

mysql> SELECT * FROM book;
+------+-----------+-------+------------+------+
| id   | name      | price | publish    | num  |
+------+-----------+-------+------------+------+
|    4 | C++Primer |   100 | NULL       |  103 |
|    4 | C++Primer |   100 | 2000-08-02 |  103 |
|    8 | 红楼梦    |    94 | NULL       | NULL |
|    4 | C++Primer |   100 | 2000-08-02 |  103 |
+------+-----------+-------+------------+------+
4 rows in set (0.00 sec)

mysql> SELECT DISTINCT * FROM book;
+------+-----------+-------+------------+------+
| id   | name      | price | publish    | num  |
+------+-----------+-------+------------+------+
|    4 | C++Primer |   100 | NULL       |  103 |
|    4 | C++Primer |   100 | 2000-08-02 |  103 |
|    8 | 红楼梦    |    94 | NULL       | NULL |
+------+-----------+-------+------------+------+
3 rows in set (0.00 sec)

别名：AS

语法

SELECT 列a,列b...... AS [别名] FROM [表名];

示例

mysql> SELECT name, price * num AS total FROM book;
+-----------+-------+
| name      | total |
+-----------+-------+
| C++Primer | 10300 |
| C++Primer | 10300 |
| 红楼梦    |  NULL |
| C++Primer | 10300 |
+-----------+-------+
4 rows in set (0.00 sec)

–

升序 or 降序

语法

SELECT * FROM [表名] ORDER BY [列名] DESC
// DESC为降序排序，ASC为升序排序，默认为ASC。

示例

mysql> SELECT name, price * num AS total FROM book ORDER BY total;
+-----------+-------+
| name      | total |
+-----------+-------+
| 红楼梦    |  NULL |
| C++Primer | 10300 |
| C++Primer | 10300 |
| C++Primer | 10300 |
+-----------+-------+
4 rows in set (0.00 sec)

更新 (Update)

语法

UPDATE [表名] SET [修改项] = [修改结果]

示例

mysql> SELECT * FROM book;
+------+-----------+-------+------------+------+
| id   | name      | price | publish    | num  |
+------+-----------+-------+------------+------+
|    4 | C++Primer |   100 | NULL       |  103 |
|    4 | C++Primer |   100 | 2000-08-02 |  103 |
|    8 | 红楼梦    |    94 | NULL       | NULL |
|    4 | C++Primer |   100 | 2000-08-02 |  103 |
+------+-----------+-------+------------+------+
4 rows in set (0.00 sec)

mysql> UPDATE book SET num=55 WHERE id=8;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM book;
+------+-----------+-------+------------+------+
| id   | name      | price | publish    | num  |
+------+-----------+-------+------------+------+
|    4 | C++Primer |   100 | NULL       |  103 |
|    4 | C++Primer |   100 | 2000-08-02 |  103 |
|    8 | 红楼梦    |    94 | NULL       |   55 |
|    4 | C++Primer |   100 | 2000-08-02 |  103 |
+------+-----------+-------+------------+------+
4 rows in set (0.00 sec)

删除 (Delete)

语法

DELETE FROM [表名];

示例

mysql> DELETE FROM book WHERE id=4;
Query OK, 3 rows affected (0.00 sec)

mysql> SELECT * FROM book;
+------+-----------+-------+---------+------+
| id   | name      | price | publish | num  |
+------+-----------+-------+---------+------+
|    8 | 红楼梦    |    94 | NULL    |   55 |
+------+-----------+-------+---------+------+
1 row in set (0.00 sec)

分组（GROUP BY）

分组的意思是根据所选 列名 对数据进行分组，可以理解为作为分组依据的 列 不会变动，而对其余列按照要求进行相关操作，最后对照着 列中的项 展示对应的结果。

语法

SELECT * FROM [表名] WHERE [条件] GROUP BY [列名]
SELECT * FROM [表名] GROUP BY [列名] HAVING [过滤条件]
// WHERE 后跟的条件里不允许使用聚合函数，但 HAVING 后面的过滤条件可以。

示例

mysql 相减的聚合函数 mysql相加函数_mysql 相减的聚合函数_03

SELECT gender, university, count(device_id) AS user_num,
avg(active_days_within_30) AS avg_active_days,
avg(question_cnt) as avg_question_cnt
FROM user_profile GROUP BY university, gender;

mysql 相减的聚合函数 mysql相加函数_mysql_04

联合查询

内连接（inner join）

内连接即查找两个表中的交集，找到两个表中同时符合条件的数据，进行连接。

语法

select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件; 
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

自连接

自连接是特殊的内连接，即与本表进行连接，需要对表名进行 别名显示 。

语法

select 字段 from 表名1 别名1 [inner] join 表名2 别名2 on 连接条件 and 其他条件;

外连接

外连接又分左外连接和右外连接。简单来说就是，以左表的数据为基准就是左连接，以右表的数据为基准就是右连接。

左连接（left join）

对于左连接，以左表的数据为基准，在右表中查找符合条件的数据，找不到的以 NULL 展示。

语法

select 字段名 from 表名1 left join 表名2 on 连接条件; 
// 以 表1 为左基准，查询 表2 中的符合数据

右连接（left join）

对于右连接，以右表的数据为基准，在左表中查找符合条件的数据，找不到的以 NULL 展示。

语法

select 字段 from 表名1 right join 表名2 on 连接条件; 
//  以 表1 为右基准，查询 表2 中的符合数据

子查询（嵌套查询）

子查询又叫做嵌套查询（窗口查询），其实就是嵌入 其他sql语句 中的 select语句 ，一般用于 查询的条件是另一条语句的结果 这一情况。

语法

select 字段 from 表名 where 查询条件=(select 列名 from 表名 where 查询条件);

示例

找到每个学校gpa最低的同学来做调研，请你取出相应数据。

mysql 相减的聚合函数 mysql相加函数_mysql_05

mysql 相减的聚合函数 mysql相加函数_mysql 相减的聚合函数_06

SELECT device_id, university, gpa
FROM user_profile
WHERE gpa IN( # IN 可替换为 = ANY
    SELECT min(gpa)
    FROM user_profile
    GROUP BY university
)
GROUP BY university # 保证学校名不重复
ORDER BY university; # 保证与题目要求输出顺序一致

合并查询

UNION

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

语法

select 字段 from 表名 where 查询条件
UNION
select 列名 from 表名 where 查询条件;

同样的结果也可以通过 or语句 来得到

select 字段 from 表名 where 查询条件1 or 查询条件2;

但是OR这个逻辑运算符会忽略索引、扫描全表，所以在海量数据查询中性能会下降很多。

UNION ALL

该操作符用于取得两个结果集的并集。当使用该操作符时，不自动去掉结果集中的重复行。

语法

select 字段 from 表名 where 查询条件
UNION ALL
select 列名 from 表名 where 查询条件;

聚合函数（复合函数）

最大最小

MAX()： 返回查询到的数据的最大值
MIN()： 返回查询到的数据的最小值

语法

SELECT max([列名]) FROM [表名]
// 查询对应列中最大的数据
SELECT min([列名]) FROM [表名]
// 查询对应列中最小的数据

示例

mysql> SELECT * FROM class;
+------+------+-------+
| id   | num  | name  |
+------+------+-------+
| NULL | NULL | li    |
| NULL | NULL | chen  |
| NULL | NULL | zhang |
+------+------+-------+
3 rows in set (0.00 sec)

mysql> SELECT max(name) FROM class
    -> ;
+-----------+
| max(name) |
+-----------+
| zhang     |
+-----------+
1 row in set (0.00 sec)

总数、总和、平均值、保留小数

COUNT()： 返回查询到的数据的数量
SUM()： 返回查询到的数据的总和
AVG()： 返回查询到的数据的平均值
ROUND()： 返回查询数据的保留小数结果，常与 sum、avg 搭配。

语法

SELECT count([列名]) FROM [表名]
// 查询对应列中数据的总数
SELECT sum([列名]) FROM [表名]
// 查询对应列中数据的和
SELECT avg([列名]) FROM [表名]
// 查询对应列中数据的平均值
SELECT round([数据], n) FROM [表名]
// 查询对应数据保留n位小数的结果，数据可以为count、avg的结果

条件函数

IF

条件函数 if（x,a,b）表示如果 x 成立、则返回 a；否则返回 b 。常用来划分查询结果的输出情况。

语法

SELECT IF(X, A, B) AS [列名] FROM [表名]

示例

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

mysql 相减的聚合函数 mysql相加函数_sql_07

mysql 相减的聚合函数 mysql相加函数_mysql_08

SELECT if(age>=25, '25岁以及上', '25岁以下') AS age_cut,
COUNT(device_id) AS Number FROM user_profile
GROUP BY age_cut;

CASE

数据库中的 case运算符 类似于 C语言中的 switch语句 ，WHEN 用来罗列情况，THEN 将情况与输出结果相对应，ELSE 总结未罗列的情况，END 标识语句结束。

语法

SELECT
CASE
	WHEN [条件] THEN [输出结果]
	WHEN [条件] THEN [输出结果]
	ELSE [输出结果]
END
AS [列名] FROM [表名]

示例

现在运营想要将用户划分为20岁以下，20-24岁，25岁及以上三个年龄段，分别查看不同年龄段用户的明细情况，请取出相应数据。

mysql 相减的聚合函数 mysql相加函数_数据库_09

mysql 相减的聚合函数 mysql相加函数_表名_10

SELECT device_id, gender,
CASE
    WHEN age>=25 THEN '25岁以上'
    WHEN age BETWEEN 20 AND 24 THEN "20-24岁"
    WHEN age<20  THEN '20岁以下'
    ELSE '其他'
END
AS age_cut FROM user_profile

日期函数

时间戳和日期的转换

时间戳是数据库中自动生成的唯一二进制数字，表明数据库中数据修改发生的相对顺序，其记录形式类似：1627963699 ，在实际工作环境中，对于用户行为发生的时间通常都是用时间戳进行记录。

from_unixtime 可以将时间戳转换成日期，其使用语法如下：

# 时间戳所在列转换
SELECT
from_unixtime([时间戳所在列], 'yyyy-MM-dd’) AS [列名] # 日期格式有’yyyy-MM-dd’ 和 ‘yyyyMMdd’，这里选用前者
From [表名]

# 单个时间戳转换
SELECT from_unixtime([时间戳], [日期格式]) AS [列名]

unix_timestamp 可以将日期转换成时间戳，其使用语法如下：

如果日期值格式满足yyyy-MM-dd HH:mm:ss，则无需指定日期格式：

SELECT unix_timestamp([日期值]) AS [列名]

如果日期值格式不满足yyyy-MM-dd HH:mm:ss，则必须指定日期格式：

SELECT unix_timestamp(‘2021-09-02','yyyy-MM-dd’) AS [列名]

年月日截取

可以从完整日期格式中提取出年月日：

语法

SELECT year([日期值]), month([日期值]), day([日期值]) FROM [表名]
# 提取一列的年月日则可以将 日期值 改为 列名

示例

计算出2021年8月每天用户练习题目的数量，请取出相应数据。

mysql 相减的聚合函数 mysql相加函数_mysql_11

mysql 相减的聚合函数 mysql相加函数_数据库_12

SELECT DAY(date) AS day, COUNT(question_id) AS question_cnt
FROM question_practice_detail
WHERE YEAR(date)='2021' AND MONTH(date)='08'
GROUP BY day;

日期差计算

datedff

datediff 的作用为计算两个日期之间的天数间隔。

语法

datediff(date1, date2)

返回起始时间 date1 和结束时间 date2 之间的天数，date1 大于 date2 的情况下，返回的天数为正数；date1 小于 date2 的情况下，返回的天数为负数。

date_sub

语法

date_sub (string startdate, interval int day)

返回开始日期 startdate 减少 day 天后的日期。

date_add

语法

date_add(string startdate, interval int day)

返回开始日期 startdate 增加 day 天后的日期。

示例

查看用户在某天刷题后第二天还会再来刷题的平均概率。请取出相应数据。

mysql 相减的聚合函数 mysql相加函数_mysql 相减的聚合函数_13

mysql 相减的聚合函数 mysql相加函数_数据库_14

SELECT COUNT(q2.device_id)/COUNT(q1.device_id) AS avg_ret
FROM(
    SELECT DISTINCT device_id, date
    FROM question_practice_detail
) q1
LEFT JOIN(
    SELECT DISTINCT device_id,
    date_sub(date, INTERVAL 1 DAY) AS date
    FROM question_practice_detail
) q2
USING(date, device_id);
# USING 等同于 ON q1.date=q2.date AND q1.device_id=q2.device_id;

文本函数

LOCATE(substr, str) ：返回子串 substr 在字符串 str 中第一次出现的位置，如不存在，则返回0；
POSITION(substr IN str) ：LOCATE函数 作用相同；
LEFT(str, length) ：从左边开始截取 str，length 是截取的长度；
RIGHT(str, length) ：从右边开始截取 str，length 是截取的长度；
SUBSTRING_INDEX(str, substr, n) ：返回字符 substr 在 str 中第 n 次出现位置之前的字符串，n 若为负数，则表倒数；
SUBSTRING(str , n, m) ：返回字符串 str 从第 n 个字符截取长度为 m 个字符；
REPLACE(str, n, m) ：将字符串 str 中的 n 字符替换成 m 字符；
LENGTH(str) ：计算字符串 str 的长度。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：Java 反向dns解析 dns的反向解析

下一篇：mysql 索引时间戳 mysql 时间加索引

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯