MySQL函数及正则表达式
目录
一、准备样例数据
1、创建数据库 create database study;
2、创建实例表
create table student1 (
id int PRIMARY KEY, -- 主键
name varchar(11), -- 名称
age int , -- 年龄
address varchar(32), -- 住址
phone varchar(20), -- 电话
bir_date datetime, -- 出生日期
sex char(3) -- 性别
) CHARACTER SET utf8; -- 指定字符集
直接创建一个与已有表同格式的表(复制表) create table student2 select * from student1;也可只需要一部分字段，*改为具体字段名就行
修改表字段类型 ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型;
3、插入样例数据使用
insert into 表(字段1,字段2…) value
(值1,值2…),
(值1,值2…);
可以多行同时插入，值用英文逗号隔开，每一行的数据均放在同一个括号内，单行导入就一个括号。
insert into student1 (id,name,age,address,phone,bir_date,sex) values
(1,'张山',18,'长沙市芙蓉区','13488845608','2020-04-22','男'),
(2,'李四',19,'长沙市天心区','13489999901','2021-03-12','女'),
(3,'王五',20,'长沙市雨花区','15866663306','2018-10-01','女'),
(4,'李明',17,'长沙市芙蓉区','15877774407','2019-09-01','男'),
(5,'王小明',18,'长沙市雨花区','13999900099','2020-03-12','女'),
(6,'李世明',20,'长沙市岳麓区','13939393399','2022-11-15','男'),
(7,'刘老根',19,'长沙市长沙县','15115151555','2011-08-09','男'),
(8,'刘宝山',17,'长沙市长沙县','15100500550','2015-11-15','女'),
(9,'杨五六',18,'长沙市芙蓉区','','2016-12-12','男'),
(10,'李隆基',17,'长沙市岳麓区',15868680909,'2021-01-11','男');
二、MySQL查询
1、直接查询
语法：select 字段 from 表名；
举例：select name,age,phone from student1;
解析：从 student1 表中查询 name 、age，phone数据。使用*号时查询所有字段数据
2、条件查询(where)
语法：select 字段 from 表名 where 条件；
举例：select name，age from student1 where age > 19;
解析：从 student1 表中查询 age大于19 的 name和age
3、模糊查询(like)
语法：select 字段 from 表名 where 字段 like '%数据%';
举例：select * from student1 where name like '%山%';
解析：从 student1 表中查询 name 中含有 '山' 的所有记录
注意：%表示任意字符，可为空，跟换为_，则表示单个字符，几个’_'，就表示几个字符。
举例：select * from student1 where name like '李_';
解析：从 student1 表中查询 name 中含以'李'开通的包含2个字符的的所有记录
4、in与not in运算符
语法：select 字段 from 表名 where 字段 in(列表)//或 not in(列表);
举例：select * from student1 where age in(20, 17, 18) ;
解析：从 student1 表中查询 年龄为17、18、20三个年龄的所有记录
举例：select * from student1 where age not in(20, 17);
解析：从 student1 表中查询 年龄不为17、20三个年龄所有记录
5、逻辑运算符(and 、or 、not)
语法：and（且）, or（或）, not（非）一般与in搭配
举例：select * from student1 where age > 17 and name like '%五%';
解析：从 student1 表中查询年龄大于17并且姓名中包含“五”的记录
举例：select * from student1 where age > 17 and name like '%五%';
解析：从 student1 表中查询年龄大于17或者姓名中包含“五”的记录
6、 排序查询
语法：select 字段 from 表名 order by 字段 排序方式(升序 asc, 降序 desc);
举例：select * from student1 where id <6 order by bar_date asc; ---默认升序，asc可不写
解析：从 student1 表中查询id小于6的所有记录并按照 bar_date升序排序
举例：select * from student1 where id <6 order by bar_date desc; ---默认升序，asc可不写
解析：从 student1 表中查询id小于6的所有记录并按照 bar_date降序排序
7、范围运算
语法：用来替换算术运算符
select 字段 from 表名 where 字段 between 范围1 and 范围2;
举例：select * from student1 where age between 17 and 19;
解析：从 student 表中查询 年龄17到19岁之间的所有记录
它等价于 select * from student1 where age >= 17 and age <= 19;
8、嵌套查询
说明：在查询语句中包含有子查询语句，所以叫嵌套查询，没有单独的语法，嵌套子查询通常位于查询语句的条件之后;
举例：select name, age from student1 where name in (select name from student2 where phone= '15115151555' or phone='';)
解析：获取student2表phone为'15115151555' 或者为空的姓名，已此姓名为条件查询student1满足条件的数据，in可以使用=。两个一样的意思。
9、多表连查
语法：与嵌套查询一样，都需要一个共同字段，然后将多个表连接在一起查询，将符合条件的记录组成一个合集
三种常用连接：
9-1、内连接 inner join（AB共有部分）
举例：select a.id,a.name,b.interest from student1 a inner join student3 b on a.id=b.id and b.interest ='电竞' ;
解析：查询a,b两个表中重复数据，输出a表的id和name，b表的interest数据。
其他书写方式(直连接)：select a.id,a.name,b.interest from student1 a,student3 b where a.id=b.id and b.interest ='电竞' ;
9-2、左连接 left join（A表独有+共有部分）
举例1：select a.id,a.name,b.interest from student1 a left join student3 b on a.id=b.id and b.interest ='电竞' ;
解析：查询2个表重复项，输出a表的id和name全部数据,同步输出b表满足与a表id相同的interest数据
举例2：select a.id,b.name,b.interest from student1 a left join student3 b on a.id=b.id and b.interest ='电竞' ;
解析：查询2个表重复项，输出a表的id全部数据,同步输出b表满足与a表id相同的name,interest数据
9-3、右连接 right join（B表独有+共有部分）
举例1：select a.id,a.name,b.interest from student1 a left join student3 b on a.id=b.id and b.interest ='电竞' ;
解析：输出b表的interest全部数据,同步输出a表满足与b表id相同的id,a.name数据
举例2：select a.id,b.name,b.interest from student1 a left join student3 b on a.id=b.id and b.interest ='电竞' ;
解析：输出b表的name,interest全部数据,同步输出a表满足与b表id相同的name数据
总结：
inner join 取重复项；
left join 以左边表数据为主，同时输出满足条件的右边数据；
Right join 以右边表数据为主，同时输出满足条件的左边数据；
三、MySQL函数
1、字符串函数
主要用于处理字符串。其中包括字符串连接函数、字符串比较函数、将字符串的字母都变成小写或大写字母的函数和获取子串的函数等
1.1、length(str)函数：获取参数值的字节个数
对于utf-8字符集来说，一个英文占1个字节；一个中文占3个字节；
对于gbk字符集来说，一个英文占1个字节；一个中文占2个字节。
1.2、concat(str1,str2,str3…)函数：字符串拼接函数
举例：select concat(name,'，爱好：',interest) from student3;
延申：
Oracle中 concat只能连接2个字符串，不能多，可以使用||连接多个字符串。
1.3、upper(str)和Lower(str)
upper(str):将字符中的英文字母都变成大写
举例：select upper('你好，iPone');
Lower(str):将字符中的英文字母都变成小写
举例：select lower('你好，iPone');
1.4、substr(str,start,len)或substring(str,start,len)
格式：substr(str,start,len)或substring(str,start,len)
说明：截取字符串函数，start 开始的位置，第一位为1，len 截取的字符长度
select substr('鲸落万物生,鲸生万物灭,万物皆在生,生者灭万物',2,8) as out_put;
select substring('鲸落万物生,鲸生万物灭,万物皆在生,生者灭万物',1,10) as out_put;
1.5、instr(str,str1)
说明：str被查的字符串，str1表示需要找到的字符串，返回为str1在str中第一次出现的位置索引，从1开始计数，如果没有，则返回0
举例：select instr('鲸落万物生,鲸生万物灭,万物皆在生,生者灭万物','万物生') as put;
解析：查找语句中第一次出现'万物生'的位置，语句应该返回3
1.6、trim(str)
说明：去掉str字符串前后的空格，字符中间的空格不会去掉
举例：select trim(' 天不生 我 李淳罡，万古剑道 如 长夜 ') as mm;
1.7、lpad(str,len,str1),rpad(str,len,str1)
说明:填充指定字符串，str被填充的字符串，len填充后整个字符串的长度，str1填充进入的字符，从左到右循环插入至满足长度，lpad对str左边填充，rpad对str右边填充
举例： select lpad('魑魅魍魉',9,'abc') as pad;
select rpad('魑魅魍魉',9,'abc') as pad;
1.8、replace(str,str1,str2)
说明：将str中的str1字符修改为str2
举例：select replace('道道道，非常道','道','叨') as mm;
注： replace()方法只能查找指定列，而不能使用全局查找,想要全局查找替换，可用循环语句。
2、数学函数
主要用于处理数字。这类函数包括绝对值函数、正弦函数、余弦函数和获得随机数的函数等。
2.1、round(x,m)
说明：四舍五入。x表示目标数，m为保留的小数位
当对正数进行四舍五入：按照正常的计算方式，四舍五入即可。
当对负数进行四舍五入：先把符号丢到一边，对去掉负号后的正数进行四舍五入，完成以后，再添加负号。
举例：select round(3.1415279,3) as a,round(-3.1415279,3) as b;
2.2、ceil(x)、floor(x)
说明：
Ceile、Ceiling向上取整，返回>=该参数的最小整数。求的是大于等于这个数字的最小整数。
floot向下取整，返回<=该参数的最大整数，求的是小于等于这个数字的最大整数。
举例：select ceil(1.9) as a ,floor(1.9) as b,ceil(1.1) as c,floor(1.1) as d，ceiling(1.1) f;
2.3、truncate(x,D)
说明：截取函数，截取不要的部分，然后删掉（断掉）它。在小数点的D位置处，截取数字直接删去数字，若在左边就是位置取整不使用任何法则。
x是要截取的数字。D为正数时是小数点的右侧部分，D为0时则不要小数部分，D为负数时是小数点左边部分，从第D为开始改为0，具体使用看例子演示。
Select truncate(12345.12345,3) a ,truncate(12345.12345,0) b,truncate(12345.12345,-3) c;
2.4、mod(x,y)
说明：取余函数。x为被除数，y为除数，当被除数为正数，结果就是正数。当被除数为负数，结果就是负数。
举例：select mod(10,3) a ,mod(-10,4) b;
2.5、pow(x,D)
说明：指数运算，x为底数，D为指数
举例：select pow(2,5) a,pow(3,2) b;
2.6、sqrt(x)
说明：取x的平方根
举例：select sqrt(4) ;
2.7、ABS(x)
说明：绝对值函数
举例：select ABS(-1.2) A, ABS(1.2) B;
2.8、rand()
说明：取0-1之间的随机数
举例：select rand();
3、日期与时间函数
3.1、时间与日期函数含义
日期的含义：指的是我们常说的年、月、日。
时间的含义：指的是我们常说的时、分、秒。
3.2、now()
说明：获取当前系统日期时间
举例：select now();
3.3、curdate()
说明：获取系统当前日期，不包含时间
举例：select curdate() ;
3.4、curtime()
说明：获取系统当前时间，不包含日期
举例：select curtime();
3.5、获取日期和时间中的年、月、日、时、分、秒
说明：获取年份：year();获取月份:month();获取日:day();获取小时:hour();获取分钟:minute();获取秒数:second()
举例：select year(now()) y ,month(now()) m,day(now()) d,hour(now()) h,minute(now()) mi,second(now()) s;
3.6、weekofyear()
说明：获取时间当前的周次
举例：select weekofyear(now());
3.7、quarter()
说明：获取时间所在的季度
举例：select quarter(now());
3.8、str_to_date()函数
说明：将日期格式转换为字符串，转换成指定格式的日期
举例：
select str_to_date('2022,01,15','%Y,%m,%d') a ,str_to_date('2022,01,15','%y,%c,%d') b;
3.9、date_format()函数
说明：将日期转换成日期字符串
举例：
select date_format(now(),'%Y年%m月%d日%h时%i分%s秒') a;
select date_format(now(),'%y年%c月%d日%H时%i分%s秒') a;
3.10、date_add(日期,interval num 时间)函数
说明：向前、向后偏移日期和时间，正号为向后，负号为向前，除此之外还有hour（小时），minute（分钟），second（秒）
举例：
select curdate() as当前时间,
date_add(curdate(),interval 1 year) as 一年后,
date_add(curdate(),interval -1 year) as 一年前;
Select now() as 当前时间,
Date_add(curdate(),interval 1 month) as 一月后,
Date_add(curdate(),interval 1 day) as 一月天后,
Date_add(curtime(),interval 1 hour) as 一小时后;
3.11、last_day()函数
说明：提取某个月最后一天的日期
举例：select last_day(now()) as 当前月最后一天,last_day('2022-10-15') as a;
3.12、datediff(end_date,start_date)函数
说明：计算两个时间相差的天数
举例：select datediff('2023-10-15','2022-10-15');
3.13、timestampdiff(unit,start_date,end_date)函数
说明：计算两个时间返回的年/月/天数；
unit参数是确定(start_date,end_date)结果的单位，表示为整数，以下是有效单位：
year：年份、month：月份、day：天、hour：小时、minute 分钟、second：秒、microsecond：微秒、week：周数、quarter：季度
举例：
select timestampdiff(year,'2013-10-15 17:25:36',now()) as 出生年数,
timestampdiff(month,'2013-10-15 17:25:36',now()) as 出生月数,
timestampdiff(day,'2013-10-15 17:25:36',now()) as 出生天数,
timestampdiff(hour,'2013-10-15 17:25:36',now()) as 出生小时数,
timestampdiff(week,'2013-10-15 17:25:36',now()) as 出生周数;
4、流程控制函数
4.1、if(expr,v1,v2)函数
说明：实现if-else的效果，如果expr是true，返回v1。如果expr是false，返回v2
举例：select name,age,if(age>=18,'成年','未成年') aa from student1;
4.2、ifnull(v1,v2)函数
说明：判断v1是否为null，是null则返回v2；如果v1不为NULL，返回v1
举例：select ifnull(null,'老八') a ,ifnull(2,'生肖') b;
4.3、case…when函数的三种用法
1）、等值判断：可以实现多条件的查询值筛选
格式：case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1
when 常量2 then 要显示的值2或语句2
…
else 要显示的值n或语句n
End
举例 ：
Select name,age,case age
When 17 then '少年'
When 18 then '青年'
When 19 then '中年'
When 20 then '老年'
End as '年龄段'
From student1 order by age;
2）、区间判断
Case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
…
else 要显示的值n或语句n
End
举例：Select name,age,case
When age in (17,18) then '少年'
When age = 19 then '青年'
Else '老年'
End as '年龄段'
From student1 order by age;
5、系统信息函数
5.1、version()函数
说明：查看MySQL系统版本信息号
举例：select version();
5.2、connection_id()函数
说明：查看当前登入用户的连接次数，返回可能随时间变化
举例：select connection_id();
5.3、processlist
说明：查看用户的连接信息
举例：show processlist();
列说明：
Id列：登录MySQL的用户标识，是系统自动分配的CONNECTION ID；
User列：显示当前的“用户名”；
Host列：显示执行这个语句的IP，用来追踪出现问题语句的用户；
db列：显示这个进程目前连接的是哪个数据库；
Command列：显示当前连接执行的命令，一般是休眠（Sleep）、查询（Query）、连接（Connect）；
Time列：显示这个状态持续的时间，单位是秒；
State列：显示使用当前连接的SQL语句的状态，包含有：Copying to tmptable、Sorting result、Sending data等状态；
Info列：显示当前SQL的内容，如果语句过长可能无法显示完全。
5.4、database(),schema()函数
说明：查看当前使用的数据库,查询所有的数据库：show databases;
举例：select database(),schema();
5.5、user(),current_user(),system_user()函数
说明：获取当前用户
举例：select user(),current_user(),system_user();
5.6、charset(x)函数
说明：查询x字符串使用的字符集
举例：select charset('2022-11-15'),charset(123);
5.7、collation(x)函数
说明：查询x字符串排列方式
举例：select collation(123),collation('123');
6、其他函数
6.1、FORMAT(x,y)函数
说明：把x格式化为以逗号隔开的数字序列，y是结果的保留的小数位数。
举例：seelct format(12345678.12345678,3);
6.2、INET_ATON(ip)函数
说明：返回IP地址的数字表示
举例：select inet_aton('192.168.160.111');
6.3、INET_NTOA(NUM)函数
说明：返回数字所代表的IP地址。
举例：select inet_ntoa(3232276591);
6.4、password(str)函数
说明：密码加密函数，str为NULL，返回NULL。
password在MySQL服务器鉴定系统中使用。不应该用在个人的应用程序中。
加密是单向的（不可逆），加密后的密码保存到用户权限表中。
执行密码加密与UNIX中密码加密方式不同。
举例：select password('123456');
6.5、md5(str)函数
说明：加密函数;参数为字符串，该函数为字符串算出一个MD5 128比特校验和
返回值以32位16进制数字的二进制字符串形式返回
str为NULL，返回NULL
举例：select md5('123456');
6.6、encode(str，pswd_str)、decode(加密的字符串,pswd_str)函数
加密：encode(被加密的密码，密码);
解密：decode(encode(被加密的密码，密码),密码); //也可以用上面返回的二进制字符串
7、聚合函数
7.1、功能与分类
功能：用作统计使用，又称为聚合函数或统计函数或组函数
分类：sum 求和avg 平均值max 最大值min 最小值count 计算个数
7.2、聚合函数的传入参数，数据类型
1、sum()函数和avg()函数：传入整型/小数类型才有意义；
2、sum()函数和avg()函数对于字符串类型、日期/时间类型的计算都没有太大意义。因此， sum()函数和avg()函数，我们只用来对小数类型和整型进行求和。跳过空值行。
3、max()函数和min()函数：传入整型/小数类型、日期/时间类型意义较大
4、可以传入任何数据类型，但是碰到null要注意，空值跳过，不计数。
注;sum()/count()方法计算平均值时，有时候得到的结果和AVG()函数不一定一样。当存在某计算列空值但其他列不是空值的时候就会出现不一样的结果，因为分母count()并没有跳过空值列。
四、MySQL正则表达式
MySQL 中，使用 REGEXP 关键字指定正则表达式的字符匹配模式，其基本语法格式如下：
属性名 REGEXP ‘匹配方式’;
其中，“属性名”表示需要查询的字段名称；“匹配方式”表示以哪种方式来匹配查询。“匹配方式”中有很多的模式匹配字符，它们分别表示不同的意思。下表为常用的匹配。
正则表达式匹配方法是查找条件中的数据，包含或等于条件，类似like
样例表数据
1、查询以特定字符或字符串开头的记录
正则表达式：字符^用来匹配以特定字符或字符串开头的记录
举例：查询姓李的所有用户 select * from student1 where name REGEXP '^李';
2、查询以特定字符或字符串结尾的记录
正则表达式：字符$用来匹配以特定字符或字符串结尾的记录
举例：查询名字最后一个为明的所有用户 select * from student1 where name REGEXP '明$';
3、匹配字符串中的任意一个字符
正则表达式：字符 . 用来代替任意一个字符
举例：查询名字中包含“小”或者号码最后一位为7的所有用户
select * from student1 where name REGEXP '.小.' or phone REGEXP'.7';
4、匹配多个字符
正则表达式：[…]字符集合，* ,+ 。匹配所包含的任意一个字符
字符和+都可以匹配多个该符号之前的字符。均不能作为条件的第一个字符。
举例1：查询号码中包含5到7数字的所有用户
select * from student1 where phone REGEXP '[5-7]';
举例2：查询号码中包含888三个连续数字的所有用户
select * from student1 where phone REGEXP '1*888*';
举例3：查询号码中包含560三个连续数字的所有用户
select * from student1 where phone REGEXP '560+';
5、匹配指定字符串
正则表达式:直接在条件引号中输入指定字符串即可，如要查多个字符串，用|连接
举例1：查询名字中包含“小”的所有用户
select * from student1 where name REGEXP '小' ;
举例2：查询名字中包含“李”或“王”的所有用户
select * from student1 where name REGEXP '李|王' ;
6、匹配指定字符以外的字符
正则表达式：[^5-7]，可以是数字、字母
举例：查询号码中包含不是1-9字符的所有用户，即包含0字符的用户
select * from student1 where phone REGEXP '[^1-9]';
7、使用{m}或者{m,n}来指定字符串连续出现的次数
说明：{m}：m是一个非负整数。匹配确定的 m 次。
{m,n}：m 和 n 均为非负整数，其中m <= n。
举例1：查询号码中数值5连续出现最少2次,最多7次的所有用户
select * from student1 where phone REGEXP '5{2,7}';
举例2：查询号码中数值5连续出现2次的所有用户
select * from student1 where phone REGEXP '5{2}';
456
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
