MySQL基本语法_range between interval 60 day preceding and curren-CSDN博客

本文链接：https://blog.csdn.net/ks_1998/article/details/122005168

公司表tb_company
在这里插入图片描述
用户表tb_user

一.查询数据

1.设计列的显示内容并执行SELECT

查询所有列（公司表）

SELECT * FROM `tb_company`;

在这里插入图片描述

查询某些列

SELECT name,status FROM tb_company;

在这里插入图片描述

查询列并起别名

SELECT name as a,status as b FROM tb_company;

在这里插入图片描述

2.处理列值后显示列

查询计算后的列值

SELECT name as a,status * 10 as b FROM tb_company;

在这里插入图片描述

其他运算符：a-b,a+b,a/b（不取整）,a DIV b（取整）,a % b（取余）

某列的平均值

SELECT AVG(empnum) FROM tb_company;

在这里插入图片描述

显示某列值的总和

SELECT SUM(empnum) FROM tb_company;

在这里插入图片描述

显示某列个数

SELECT COUNT(empnum) FROM tb_company;

在这里插入图片描述

连接列的内容

SELECT CONCAT(name,'的员工数',empnum) FROM tb_company;

在这里插入图片描述

3.设置条件进行显示

限制显示的数目

SELECT name,status FROM tb_company LIMIT 3;

在这里插入图片描述
LIMIT后面跟两个参数时，第一个参数是指从第几行开始（首位是0），第二个参数是取几条数据

如： select * from test limit 2,3;

这个SQL是指从表的第三行开始向下取3条数据(即取：3，4，5行的三条数据)

WHERE

SELECT name,empnum FROM tb_company WHERE empnum > 10000;

在这里插入图片描述

比较运算符：等于= 不等于<> 大于> 小于<

IN,NOT IN,BETWEEN AND,NOT BETWEEN AND

SELECT * FROM tb_company WHERE empnum IN (20000,22000);

在这里插入图片描述

模糊查询

通配符：%任意 _任意一个

SELECT * FROM tb_company WHERE `name` LIKE '%巴巴';

SELECT * FROM tb_company WHERE `name` LIKE '__巴巴';

SELECT * FROM tb_company WHERE `name` NOT LIKE '__巴巴';

在这里插入图片描述

NULL（用户表）

提取列值为NULL用IS NULL，不为NULL用IS NOT NULL

SELECT * FROM t_user WHERE username IS NULL;

在这里插入图片描述

不重复查询

SELECT DISTINCT company_id FROM t_user;

在这里插入图片描述

4.设置多个条件

SELECT * FROM `tb_company` WHERE empnum > 10000 AND empnum <25000;

SELECT * FROM `tb_company` WHERE `status` = 0 OR empnum > 20000;

AND和OR同时使用，无论顺序如何，会优先处理AND，如果非要先处理OR，要是用()

SELECT name,
   CASE
          WHEN empnum<=10000 THEN '中厂'
          WHEN empnum>10000 AND empnum < 20000 THEN '大厂'
					WHEN empnum>=20000 AND empnum < 30000 THEN '超大厂'
          ELSE '特大厂'
    END AS '规模'
FROM tb_company;

在这里插入图片描述

CASE-WHEN-THEN-ELSE-END会单独显示一列，比如上面的查询语句，会显示col和评价两列

5.排序

排序相关的特别注意点

1.order by 后面也可以直接跟聚合函数

2.默认ASC升序排序，降序要加DESC

使用举例

SELECT * FROM tb_company ORDER BY empnum DESC;

在这里插入图片描述

指定显示范围：

SELECT col FROM tb LIMIT 显示的记录数 OFFSET 位移数；

SELECT * FROM tb_company ORDER BY empnum DESC LIMIT 3 OFFSET 2;

在这里插入图片描述

6.分组

排序相关的特别注意点

GROUP BY经常和集合函数一起使用，例如MAX()、COUNT()等，使用了group by，select后面只能跟聚合函数和group by的字段或者由group by后的字段可唯一确定的字段

使用举例

SELECT status,COUNT(empnum) FROM tb_company GROUP BY `status`;

在这里插入图片描述

提取符合的记录后再分组：

SELECT status,AVG(empnum) FROM tb_company WHERE empnum < 20000 GROUP BY `status`;

在这里插入图片描述

符合empnum小于20000的将会被提取，然后按照status分组，再计算各组的平均值

处理分组后的记录：（HAVING关键字）

SELECT status,AVG(empnum) FROM tb_company WHERE empnum < 20000 GROUP BY `status` HAVING AVG(empnum) > 6300;

在这里插入图片描述

符合empnum小于20000的将会被提取，然后按照status分组，再计算各组的平均值，只有平均值大于6300的才会显示

分组后排序

SELECT status,AVG(empnum) FROM tb_company WHERE empnum < 20000 GROUP BY `status`  HAVING AVG(empnum) > 6300 ORDER BY AVG(empnum);

符合empnum小于20000的将会被提取，然后按照status分组，再计算各组的平均值，只有平均值大于6300的才会进行排序，然后按照升序显示

书写顺序

WHERE => GROUP BY => HAVING => ORDER BY

关于HAVING和WHERE

WHERE 后面只能跟数据库字段，别名也不可以！

SELECT empnum as e FROM tb_company WHERE e > 10000; //报错！！因为这张数据表里面没有e这个字段

1.HAVING后面也可以跟数据库字段，但前提是前面SELECT语句查询了该字段！

SELECT name,empnum FROM tb_company HAVING empnum > 10000;// having使用正确，并且empnum使用别名也可以

SELECT name FROM tb_company HAVING empnum > 10000; // 报错，前面没有empnum查询

2.HAVING后面可以跟聚合函数，WHERE不可以

SELECT AVG(empnum) FROM tb_company GROUP BY `status` HAVING AVG(empnum) > 10000; // AVG(empnum)前面必须有相关查询

在这里插入图片描述

3.having后年可以跟group by后面出现的字段，然后过滤

SELECT exam_id from exam_record GROUP BY exam_id HAVING exam_id > 9001

7.常用函数

7.1 计算函数

MAX(col) 找出某一列的最大值
MIN(col) 找出某一列的最小值
AVG(col) 计算某一列的平均值
ROUND(num,x) 将浮点数num保留x位
COUNT(col) 计算某一列的个数

1.COUNT(条件 or null) 来计算数量
2.COUNT(if(条件,col,null)) 来计算数量，如果条件满足，则将这一行算为一行数据，否则不算一行，这里的col只是一个计数标志而已，可以设置为任意非null的值，甚至可以在if前面加distinct，可参考牛客网的SQL124题

上述count条件计数的写法不同，但效果相同！

SUM(col) 计算某一列的总和

SUM(条件,value,0) 根据大于等于小于0 来判断是否有记录
SUM(if(条件,col,null)) 来求和

例如 sum(if(a > b,10,0)) 意思是a大于b，就加10，否则加0

7.2 日期函数

MONTH(date)

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

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

YEAR(date) 类似的 month(date) day(date)

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

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

HOUR(time)

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

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

MINUTE(time)

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

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

SECOND(time)

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

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

DATEDIFF(recordDate,date) = 1 判断两个日期是否相差一天，datediff只能求相差的天数，且时间大的在前才会返回正值！
date_sub(date,interval 1 day) 将当前日期减一天 Hive的date_sub(date,1) 减一天
date_add(date,interval 1 day) 将当前日期加1天

TIMESTAMPDIFF功能强大
特别注意：该函数是时间小的在前，才会返回正值！

# 求两者之间的天数
select TIMESTAMPDIFF(DAY, '2020-5-1 09:00:00', '2020-5-8 07:00:00')
# 求两者之间的小时
select TIMESTAMPDIFF(HOUR, '2020-5-1 09:00:00', '2020-5-8 07:00:00')
# 求两者之间的分钟
select TIMESTAMPDIFF(MINUTE, '2020-5-1 09:00:00', '2020-5-8 07:00:00')
# 求两者之间的秒
select TIMESTAMPDIFF(SECOND, '2020-5-1 09:00:00', '2020-5-8 07:00:00')

7.3 窗口函数

函数名() over( partition by <要分组的列> order by <要排序的列> rows between <数据范围> )

分组（partition by):窗口按照字段进行分组，窗口函数在不同的分组上分别执行
排序（order by):按照哪些字段进行排序，窗口函数将按照排序后的记录顺序进行编号，可以和partition子句配合使用，也可以单独使用。如果没有partition子句，数据范围则是整个表的数据行
窗口（rows):就是进行函数分析时要处理的数据范围，属于当前分区的一个子集，通常用来作为滑动窗口使用比如要根据每个订单动态计算包括本订单和按时间顺序前后两个订单的移动平均支付金额，则可以设置rows子句来创建滑动窗口（rows)，是物理上的行
- rows between 2 preceding and current row # 取当前行和前面两行
- rows between unbounded preceding and current row #包括本行和之前所有的行
- rows between current row and unbounded following #包括本行和之后所有的行
- rows between 3 preceding and current row #包括本行和前面三行
- rows between 3 preceding and 1 following #从前面三行和下面一行，总共五行
range关键字可以表示数据范围，主要有日期范围和数字范围两种用法，主要将order by后面字段排序后，然后根据每一行数据设定一个范围
- sum(num) over(order by dateTime range between interval 6 day preceding and current row )必须是date类型的数据，这一天和前面6天（如果存在）的数据
- sum(close) over(order by salary range between 100 preceding and 200 following) 通过字段差值来进行选择。如当前行的 close 字段值是 200，那么这个窗口大小的定义就会选择分区中 close 字段值落在 100 至 400 区间的记录（行）

只要加了rows或者range，那就是根据某一个具体的行，然后根据设定的范围，进行操作。在这个范围内sum等

8.0窗口函数排序函数

1.rank() over() 排名相同的两名是并列，但是占两个名次，1 1 3 4 4 6这种

2.dense_rank() over() 排名相同的两名是并列，共占一个名词，1 1 2 3 3 4这种

3.row_number() over() 这个函数不需要考虑是否并列，哪怕根据条件查询出来的数值相同也会进行连续排名 1 2 3 4 5

窗口函数得到的排名均为无符号整形，当两数有一个无符号整形，相减结果也为无符号整形，若结果为负数，在mysql中会报错。此时要将排名转化为有符号整形再相减，利用convert(rk,signed)

8.0窗口函数前后函数

LAG(expr,n,default )over()

LAG(expr,n, default )函数返回当前行的前n行（只有前n行这一行）的expr的值，如果当前行前面没有行，则返回 default

LEAD(expr,n,default )over()

LEAD(expr,n, default)函数返回当前行的后n行的expr的值，如果当前行后面没有行，则返回default

8.0窗口函数求值函数

sum(num) over() 求所有的和
avg(col) over() 求所有的平均值
count(col) over() 求所有的列

关于这三个窗口函数的说明

在这里插入图片描述

over里面只加partition by字段，就是根据分组字段每个组只求一个和

select id,month,sum(Salary) over (partiton by id);

在这里插入图片描述

over里面加partition by，并且加order by 或者只加order by ，会有累计求和的作用

select Id as id,Month as month,sum(Salary) over(partition by Id order by Month) as Salary
from Employee

在这里插入图片描述
累计是以order by 后面的字段为一组的，什么意思呢，看下面的查询语句，只有order by，首先将order by 后面的字段分组，显然，有1、2两组组，组内求和后再累计求和，month为1组内求和40，month为2组内求和为100，但是累计求和，就是40 + 100 = 140了

select Id as id,Month as month,sum(Salary) over(order by Month) as Salary
from Employee

在这里插入图片描述

over里面加partition by、order by、范围，那就是以每一行为单位只求这个范围内的一个和了

7.4 其他函数

if(expr,yes,no)

如果表达式成立，输出第一个yes，否则输出第二个no

if(id % 2 = 0,student,teacher) as person 可充当一列

ifnull

ifnull(x1,x2)意思为：如果x1是null，那么则转换为x2，否则仍旧输出x1

left(str,length)

截取字符串str的左边length长度个字符

如 left(2022-02-18,4) 为2022

substr(‘abcde’,1,3) 结果为abc substr(obj，start，length) Hive相同用法

convert(expr,type) 数据类型转换 Hive：cast(12.14 as bigint) cast(12.14 as string)

expr: 要转换的值
type: 要转换为的数据类型

在这里插入图片描述
concat函数

concat(str1, str2,…) 返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null

8.高阶用法

8.1 临时表

建立临时表t，后续可直接使用临时表t

with t as ( select 语句 )

8.2 递归用法

with recursive 表名 as ( select 语句 )语法

案例1 递归查询1-100

with recursive cte as
(
    select 1 as n
    union all
    select n+1
    from cte
    where n < 100
)

首先执行select 1，此时cte表的内容如下

n
1

递归执行select n+1 from cte where n < 100 得到的是2
此时cte表的内容如下，注意表的内容不是 1 2 而是只有2折以个数据，也就是表的内容是最新查到的数据

n
2

直到100，union连接
变成

n
1
2
...
99
100

案例2

在这里插入图片描述

with recursive t as
(
    select task_id,subtasks_count from Tasks
    union all
    select task_id,subtasks_count - 1 from t where subtasks_count >= 2
)

select * from t

每一行为单位执行递归，最终结果如下

在这里插入图片描述

二.编辑数据

1.修改数据

修改列中的所有值

UPDATE 表名 SET 列名 = 值;

修改符合条件的列

WHERE方法：

UPDATE tb_company SET  nickname = '淘宝' WHERE name = '阿里巴巴';

在这里插入图片描述

非WHERE排序后修改：

UPDATE tb_company SET  status = 3 ORDER BY empnum LIMIT 2;

在这里插入图片描述

2.删除数据

DELETE FROM 表名 WHERE 条件;

DELETE FROM tb_company WHERE sellerid = 11;

排序后删除，删除员工数量最低的2个

DELETE FROM tb_company ORDER BY empnum LIMIT 2;

删除语句不常用用法
在这里插入图片描述

DELETE p1 FROM Person p1,
    Person p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id

从表p1取出3条记录
拿着第1条记录去表p2查找满足WHERE的记录，代入该条件p1.Email = p2.Email AND p1.Id > p2.Id后，发现不满足，所以不用删掉记录1
记录2同理
拿着第3条记录去表p2查找满足WHERE的记录，发现有一条记录满足，所以要从p1删掉记录3
3条记录遍历完，删掉了1条记录，这个DELETE也就结束了

作者：wutengyue
链接：https://leetcode.cn/problems/delete-duplicate-emails/solution/dui-guan-fang-ti-jie-zhong-delete-he-de-jie-shi-by/
来源：力扣（LeetCode）

三.使用多个表

1.UNION

sql1和sql2要想使用union查询必须保证查询的字段数量一致，否则报错，当sql1与sql2的查询字段名称不一致的时候，返回的字段名称为前面的一条sql为标准，但内容却是各自查询的东西

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL|DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

expression1, expression2, … expression_n: 要检索的列
tables: 要检索的数据表
WHERE conditions: 可选，检索条件
DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响
ALL: 可选，返回所有结果集，包含重复数据

SELECT sellerid,name FROM tb_company UNION SELECT username,company_id FROM t_user;

在这里插入图片描述
注意，如果查询的是两列联合，当且仅当两列数据都重复时，UNION DISTINCT才去重

2.内连接

把不同表中相匹配的记录提取出来的连接方式称为内连接。没有条件限制，就是笛卡尔积

用JOIN、ON关键字，INNER JOIN = JOIN ，ON后面的.后面的名字可以不同，内容存在匹配即可，特别的，如果的名字相同，如tb.id=tb1.id，则可以用USING(id) 代替 ON ON tb.id = tb1.id;

全部显示，包括重复列

SELECT * 
FROM tb_company 
INNER JOIN t_user 
ON tb_company.sellerid = t_user.company_id;

在这里插入图片描述

选择列进行显示

SELECT sellerid,name,address,id,company_id 
FROM tb_company 
INNER JOIN t_user 
ON tb_company.sellerid = t_user.company_id;

在这里插入图片描述

给表起别名

别名如果有空格，一定要加 ’ '，否则报错！！！

SELECT c.sellerid,c.name,c.address,u.id,u.company_id 
FROM tb_company as c 
INNER JOIN t_user as u 
ON c.sellerid = u.company_id;

当然查询出来的数据的名字依旧原名

在这里插入图片描述

多表条件提取

SELECT sellerid,name,address,id,company_id 
FROM tb_company 
INNER JOIN t_user 
ON tb_company.sellerid = t_user.company_id
where address = '北京市';

在这里插入图片描述

3.外连接

内连接只会提取与连接键相匹配的内容，但此时两张表中有一部分的内容会被忽略掉，所以我们要用外连接

左外连接
显示相匹配的内容和左表的内容，左表的对应为NULL

SELECT sellerid,name,address,id,company_id 
FROM tb_company 
LEFT JOIN t_user 
ON tb_company.sellerid = t_user.company_id;

在这里插入图片描述

右外连接
显示相匹配的内容和右表的内容，右表的对应为NULL

SELECT sellerid,name,address,id,company_id 
FROM tb_company 
RIGHT JOIN t_user 
ON tb_company.sellerid = t_user.company_id;

在这里插入图片描述

4.自连接

表与自身连接称为自连接，连接必须定义别名

SELECT * FROM t_user as a JOIN t_user AS b;

在这里插入图片描述

5.子查询

可用四种种语法来创建子查询：

带有比较运算符的子查询(sqlstatement)
comparison(>,<,=,!=)
带有ANY(some)或ALL谓词的子查询
comparison [ANY | ALL | SOME] (sqlstatement)
带有谓词IN的子查询
expression [NOT] IN (sqlstatement)，IN后面集合中只有一条记录可以用=
带有EXISTS谓词的子查询
[NOT] EXISTS (sqlstatement)

ALL：

SELECT * FROM t_user WHERE id > 
ALL
(SELECT sellerid FROM `tb_company` WHERE empnum >= 20000);

SELECT sellerid FROM tb_company WHERE empnum >= 20000结果为1,2,3。整个语句查询结果t_user的id都大于3
在这里插入图片描述

ANY（和SOME一样）

SELECT * FROM t_user WHERE id > 
ANY
(SELECT sellerid FROM `tb_company` WHERE empnum >= 20000);

SELECT sellerid FROM tb_company WHERE empnum >= 20000结果为1,2,3。整个语句查询结果t_user的id都大于1，因为id为2的大于1，也会显示
在这里插入图片描述

IN：判断某个记录的值是否在指定的集合中

SELECT * FROM t_user WHERE id 
IN
(SELECT sellerid FROM `tb_company` WHERE empnum >= 20000);

SELECT sellerid FROM tb_company WHERE empnum >= 20000结果为1,2,3。整个语句查询结果t_user的id都是1或者2或者3
在这里插入图片描述

EXISTS

EXISTS关键字后是任意一个子查询，系统对子查询进行运算以判断它是否返回回行，如果至少返回一行，那么EXISTS的结果为true，外层查询执行；如果子查询没有返回行，则外层查询不进行查询

SELECT * FROM t_user WHERE EXISTS (SELECT sellerid FROM `tb_company` WHERE empnum >= 20000);

由于SELECT sellerid FROM tb_company WHERE empnum >= 20000;存在数据，所以外层查询执行
在这里插入图片描述
相关子查询是指引用了外部查询列的子查询，多数情况下是子查询的WHERE子句中引用了外部查询的表，引用了别名

执行的时候先执行外部查询，然后将结果代入子查询，如果子查询符合，那么该行输出

非相关子查询是独立于外部查询的子查询，子查询总共并且先执行一次，执行完毕后将值传递给外部查询

6.笛卡尔积

A = {0,1}，B = {2,3,4}

进行笛卡尔积运算

A×B = {（0，2），（1，2），（0，3），（1，3），（0，4），（1，4）}；

在这里插入图片描述
Students CROSS JOIN Subjects

在这里插入图片描述

MySQL基本语法

MYSQL基本语法

一.查询数据

1.设计列的显示内容并执行SELECT

2.处理列值后显示列

3.设置条件进行显示

4.设置多个条件

5.排序

6.分组

7.常用函数

7.1 计算函数

7.2 日期函数

7.3 窗口函数

8.0窗口函数 排序函数

8.0窗口函数 前后函数

8.0窗口函数 求值函数

7.4 其他函数

8.高阶用法

8.1 临时表

8.2 递归用法

二.编辑数据

1.修改数据

2.删除数据

三.使用多个表

1.UNION

2.内连接

3.外连接

4.自连接

5.子查询

6.笛卡尔积

“相关推荐”对你有帮助么？

8.0窗口函数排序函数

8.0窗口函数前后函数

8.0窗口函数求值函数