mysql索引、查询连接、文本函数、窗口函数、时间函数

本文链接：https://blog.csdn.net/weixin_48620718/article/details/125974036

本文介绍了MySQL中索引的创建与删除，查询连接的使用，包括UNION ALL和UNION的区别，以及常用函数的应用，如文本、时间及窗口函数。详细讲解了如何使用CREATE INDEX创建索引，使用ALTER TABLE删除索引，以及UNION和UNION ALL的合并操作。还探讨了文本函数如SUBSTRING_INDEX和时间函数如YEAR、MONTH等的用法，并举例说明了窗口函数ROW_NUMBER() OVER()的应用场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、索引

1.1 创建索引

1.2 删除索引

2、查询连接：Union all和Union

6.3.1 row_number() over()窗口函数

7 数学函数

8 数字类型转换函数

9 存储过程

1、索引

1.1 创建索引

CREATE
| UNIQUE -- 唯一索引
| FULLTEXT -- 全文索引
| INDEX index_name ON table_name -- 不指定唯一或全文时默认普通索引
(column1[(length) [DESC|ASC]] [,column2,...]) -- 可以对多列建立组合索引

create index idx_duration ON examination_info(duration);  --创建普通索引
create unique index uniq_idx_exam_id ON examination_info(exam_id);  --创建唯一索引
create fulltext index full_idx_tag ON examination_info(tag);   --创建全文索引

1.2 删除索引

alter table table_name drop index 索引名

alter table examination_info drop index uniq_idx_exam_id;
alter table examination_info drop index full_idx_tag;

2、查询连接：Union all和Union

Union all对两个数据集（两张表）合并，不去重，保留所有，合并时字段类型要保持一致

Union对两个数据集合并，并自动去重

select id,name
from OrderItems1
union 
select id,name
from OrderItems2;

在用 UNION 组合查询，结果要排序时,最后一个select后面接order by排序

select id,name
from OrderItems1
union 
select id,name
from OrderItems2 order by id;

3、表结构操作

基本语法：alter table table_name 操作名 column 列名[列名2] 类型和长度 [after|first 列名];

如：添加一列在某一列的后面

alter table user_info add column school varchar(15) after level;

改名并修改数据类型和长度：对job进行改名，并更改数据类型和长度

alter table user_info change column job profession varchar(10);

修改默认值

alter table user_info modify column achievement int(11) default 0;

4、排序

order by 字段名 desc;  --结果降序（从大到小）
order by 字段名 asc;   --结果升序（不写asc,系统默认为升序），（从小到大）

5、判断语句

5.1 if

if(表达式,值1，值2)，如果表达式满足，就把值1赋值给age，不满足就值2赋给age

select 
if(age<25 or age is null,'25岁以下','25岁及以上') age_cut,
count(device_id) number from user_profile
group by age_cut;

5.2 case when ……then

适合多条件下的判断

# 当age小于20以下时，就把这个数据赋为字符串“20岁以下”，其他条件以此规律
select device_id,gender,
case 
     when age<20 then '20岁以下' 
     WHEN age between 20 and 24  then '20-24岁' 
     WHEN age>=25 then '25岁及以上'
     else'其他'  
     end age_cut
from user_profile;

6 常用函数

6.1 文本函数

SUBSTRING_INDEX(str ,delim分隔符 ,n个数)：用分隔符分隔字符串str，返回str字符串中第n次出现位置之前的字符串;

例如： substring_index(profile,',',-1)，可以返回male和female。 -1改为2就返回180cm和75cm

SUBSTRING(str ,n ,m)：截取字符串str从第n个字符到第m个字符；

REPLACE(str, n, m)：将字符串str中的n字符串替换成m字符串；

LEFT(str, length)：从左边开始截取str，length是截取的长度；

RIGHT(str, length)：从右边开始截取str，length是截取的长度；

LENGTH(str)：计算字符串str长度

count(字段名)：统计个数，一般与group by 一起用

trim(str from 字段名); 从字段名中删除str字符串，

concat(str1,str2,……)对两个字符串拼接

6.2 时间函数

year()提取年; year('2021-05-03')结果为2021

day()提取天数；day('2021-05-03'）结果为3

month()提取月份； month('2021-05-03')结果为5

date_format(字段名，转换格式)，

例如：date_format('2022-07-31','%Y-%m')可返回2022-07

date_format('2022-07-31','%Y%m')可返回202207

date_format('2022-07-31','%Y')可返回2022

date_add(date,interval expr type)和date_sub(date,interval expr type)对指定起始时间进行加减操作。

date为起始时间，expr为起始时间加上或减去的时间间隔，type为expr的时间间隔的类型(类型有：second秒、day、hour、day、week、month、year)

date_add('2021-08-03 23:59:59',interval 1 second)返回2021-08-04 24:00:00
date_sub('2021-08-03 23:59:59',interval 2 month)返回2021-06-03 23:59:59

datediff(date1,date2)——计算两个日期之间间隔的天数

6.3 窗口函数

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

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

面对这类问题，就需要sql的高级窗口函数了，

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

基本语法：

<窗口函数> over (partition by <用于分组的列名>
                order by <用于排序的列名>)

<窗口函数>可以放以下两种函数：

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

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

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

6.3.1 row_number() over()窗口函数

row_number() over(partition by 字段1 order by 字段2)

按照字段1分组，对字段2排序（默认升序）

例如:取出每个学校的最小gpa

# 对每个学校分组，按照gpa排序，默认为升序，取出第一条就是题目所说的每个学校gpa的最小值
select university,gpa
From (
       select university,gpa,
       row_number() over(partition by university order by gpa) as rk
       From user_profile) a
    where rk = 1

7 数学函数

round(num,y) ,四舍五入，y为保留的位数

8 数字类型转换函数

cast(x as type)

9 存储过程

定义：

先编译好的一段sql语句，调用时可以简化开发工作，减少数据传输，顾名思义就是sql语句封装和调用，也可以说是写一个函数。

需要先定义，后调用，定义参数有in（接收调用时传入的值）、out（向调用者返回的值）、inout（既可以接收传入的值，也可以向调用者返回数据）

语法如下：

DELIMITER $$
CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE 数据库名.存储过程名([in变量名 类型,out 参数 2，...])
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
	BEGIN
		[DECLARE 变量名 类型 [DEFAULT 值];]
		存储过程的语句块;      
	END$$
DELIMITER ;

#  存储过程的语句块，也就是sql 逻辑

# 调用过程
call 过程名(输入参数传值,@输出参数)

CREATE OR REPLACE FUNCTION 将要么创建一个新函数，要么替换现有的定义。

例如：在school数据库中创建一个名为stu_score的过程，在begin语句中声明一个score变量，默认值为60分，然后又把他设置90分，调用过程时返回值为90分