MYSQL函数大全

双速17！

已于 2022-09-13 14:18:49 修改

阅读量679

点赞数 2

文章标签： mysql 数据库 sql

于 2021-07-16 18:14:06 首次发布

本文链接：https://blog.csdn.net/qq_45199095/article/details/118764947

版权

检索数据

选择数据库

use 数据库名字

mysql> use one
Database changed

列出所有数据库
show databases;
列出所有表
show tables;

SELECT 语句

select 查询规范
select [查询字段，数学函数] from [表名，子查询语句] 最后面加条件（并不是所有的检索都要加where关键字，select 和 from 才是查询表中固定所有的参数，大部分select语句都是这样构成的）

查询表中某一列或者多列的数据

select 列名 from 表名；

select pwd from test;
+------+
| pwd  |
+------+
| 1000 |
| 998  |
| 888  |
| 777  |
| 666  |
| gao  |
| 555  |
+------+
7 rows in set (0.01 sec)

多列中间用逗号隔开

select name,pwd from test;
+----------+------+
| name     | pwd  |
+----------+------+
| zhangsan | 1000 |
| lisi     | 998  |
| wangwu   | 888  |
| kuaile   | 777  |
| lei      | 666  |
| gaoxing  | gao  |
| kaixin   | 555  |
+----------+------+
7 rows in set (0.00 sec)

查询所有列
select * from 表名;

select * from test;
+----+----------+------+
| id | name     | pwd  |
+----+----------+------+
|  1 | zhangsan | 1000 |
|  2 | lisi     | 998  |
|  3 | wangwu   | 888  |
|  4 | kuaile   | 777  |
|  5 | lei      | 666  |
|  6 | gaoxing  | gao  |
|  7 | kaixin   | 555  |
+----+----------+------+
7 rows in set (0.01 sec)

去重 distinct

select distinct(需要去重的列) from 表名；
注意：distinct应用的是你查询的所有列。

select distinct pwd from test;
+------+
| pwd  |
+------+
| 1000 |
| 998  |
| 888  |
| 777  |
| 666  |
| 444  |
| 555  |
+------+
7 rows in set (0.00 sec)

限制结果 limit

select 查询列名 from 表名 limit 限制的数据条数；

select name from test limit 3;
+----------+
| name     |
+----------+
| zhangsan |
| lisi     |
| wangwu   |
+----------+
3 rows in set (0.00 sec)

限制从第几条开始查询
select 查询列名 from 表名 limit 开始行数，数据条数。如从第二行开始展示3条数据.(limit是从0开始计数的所以第二行在limit中要写成1)

select name from test limit 1,3;
+--------+
| name   |
+--------+
| wangwu |
| kuaile |
| lei    |
+--------+
3 rows in set (0.00 sec)

排序 order by

select 查询的列名 from 表名 order 需要排序的列名

select id from test order by pwd;
+----+
| id |
+----+
|  1 |
|  8 |
|  6 |
|  7 |
|  5 |
|  4 |
|  3 |
|  2 |

如示例所示你不用一定要排序的列查出来。

多个列排序，在排序列之间加上逗号即可（如果两列之间排序出现冲突，Mysql会优先进行写在前面的排序）

select id,pwd from test order by pwd,id;
+----+------+
| id | pwd  |
+----+------+
|  1 | 1000 |
|  8 | 1000 |
|  6 | 444  |
|  7 | 555  |
|  5 | 666  |
|  4 | 777  |
|  3 | 888  |
|  2 | 998  |
+----+------+
8 rows in set (0.00 sec)

升序在查的列之后加上desc

select id,pwd from test order by pwd desc;
+----+------+
| id | pwd  |
+----+------+
|  2 | 998  |
|  3 | 888  |
|  4 | 777  |
|  5 | 666  |
|  7 | 555  |
|  6 | 444  |
|  1 | 1000 |
|  8 | 1000 |
+----+------+
8 rows in set (0.00 sec)

降序 order by默认就是降序关键字为asc用法与desc一致（注：如果多列排序，并且之间有排序冲突，无论你使用desc还是asc都是降序排序）

排序与限制数据组合使用

select 查询的列 from 表名 order by 排序的列 limit 限制的条数
(limit必须要在order by 字句之后。这是mysql的执行死规则。)

select name from test order by pwd desc limit 1,3;
+--------+
| name   |
+--------+
| wangwu |
| kuaile |
| lei    |
+--------+
3 rows in set (0.00 sec)

where 条件语句

where 之后的参数有很多，它也是执行条件时优先级比较高的。

 WHERE子句操作符（这些操作符不可以判断null）
操作符通用示例
select 查询的列名 from 表名 where 条件;
比如查询pwd大于五百的人名字
select name from test where pwd >500;
+----------+
| name     |
+----------+
| zhangsan |
| lisi     |
| wangwu   |
| kuaile   |
| lei      |
| kaixin   |
| xin      |
+----------+
7 rows in set (0.00 sec)
操 作 符 说 明
= 等于
<> 不等于
!= 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于

范围查询 between and

select 查询的列名 from 表名 where 取范围的列名 between 最小值 and 最大值；（最小值必须在and前面不可以颠倒顺序）

select name from test where pwd between 100 and 500;
+---------+
| name    |
+---------+
| gaoxing |
+---------+
1 row in set (0.00 sec)

and、or、in、not操作符

and 意思就是和，就是所有列出的条件都要符合才行

select 查找的列名 from 表名 where 筛选条件列1 and 筛选条件列2;
select name from test where id >1 and pwd <800;
+---------+
| name    |
+---------+
| kuaile  |
| lei     |
| gaoxing |
| kaixin  |
+---------+
4 rows in set (0.00 sec)

or 就是或者的意思,就是查询出的数据只要符合列出条件其中的一个就可以了。

select 查找的列名 from 表名 where 筛选条件列1 and 筛选条件列2;
select name from test where id = 8 or pwd > 800;
+----------+
| name     |
+----------+
| zhangsan |
| lisi     |
| wangwu   |
| xin      |
+----------+
4 rows in set (0.00 sec)

and和or的优先级。如果你查询语句中既有and还有or，Mysql会优先进行and条件的满足。所以这时我们就需要将or的条件用（）扩起来就可以了。

in 的意思就是“是”的意思。多个条件在()里按逗号隔开。

select * from 表名 where 选择的列名 in (条件)

select * from test where id in(1,2,3,4,5,6);
+----+----------+------+
| id | name     | pwd  |
+----+----------+------+
|  1 | zhangsan | 1000 |
|  2 | lisi     | 998  |
|  3 | wangwu   | 888  |
|  4 | kuaile   | 777  |
|  5 | lei      | 666  |
|  6 | gaoxing  | 444  |
+----+----------+------+
6 rows in set (0.00 sec)

not 的意思就是不的意思（一般和in一起使用）

select 查询的列名 from 表名 where 判断的列名 not in (判断的列名);
select * from test where pwd not in (1000);
+----+---------+------+
| id | name    | pwd  |
+----+---------+------+
|  2 | lisi    | 998  |
|  3 | wangwu  | 888  |
|  4 | kuaile  | 777  |
|  5 | lei     | 666  |
|  6 | gaoxing | 444  |
|  7 | kaixin  | 555  |
+----+---------+------+
6 rows in set (0.01 sec)

like模糊查询

其中%表示零个或多个字符，_表示一个字符。查询时要用单引号括起来。

slect 查询的列名 from 表名 where 条件列名 like ’条件‘
select * from test where name like 'l%';
+----+------+------+
| id | name | pwd  |
+----+------+------+
|  2 | lisi | 998  |
|  5 | lei  | 666  |
+----+------+------+
2 rows in set (0.01 sec)

_线示例
select * from test where name like 'l__';
+----+------+------+
| id | name | pwd  |
+----+------+------+
|  5 | lei  | 666  |
+----+------+------+
1 row in set (0.00 sec)

正则表达式关键字：regexp

正则表达式的作用是匹配文本，将一个模式（正则表达式）与一个文本串进行比较。MySQL WHERE子句对正则表达式提供了初步的支持，允许你指定正则表达式，过滤SELECT检索出的数据。

比如你要查询nam中带有l字母的字段

select * from test where name regexp 'l';

正则表达式中的or
也可以匹配两个以上的or条件,只需要一直加 | 号就行了。

select * from test where id regexp '2|15';
+----+------+------+
| id | name | pwd  |
+----+------+------+
|  2 | lisi | 998  |
+----+------+------+
1 row in set (0.00 sec)

正则表达式遍历数组开头查询

可以看出regexp可以用[]号在里面写一个数组，用来进行第一个字段的检索
select * from test where pwd regexp '[12]000' ;
+----+----------+------+
| id | name     | pwd  |
+----+----------+------+
|  1 | zhangsan | 1000 |
|  8 | xin      | 1000 |
+----+----------+------+
2 rows in set (0.00 sec)

当然一个一个写数的话很麻烦，正则表达式提供了另一种解决方式用-代表范围
由此可见在最大数最小数之间加一个 - 符号就可以划定一个范围查询

select * from test where pwd regexp '[1-5]000' ;
+----+----------+------+
| id | name     | pwd  |
+----+----------+------+
|  1 | zhangsan | 1000 |
|  8 | xin      | 1000 |
|  9 | lei      | 2000 |
+----+----------+------+
3 rows in set (0.00 sec)

拼接字符串concat

拼接串，即把多个串连接起来形成一个较长的串。需要一个或多个指定的串，各个串之间用逗号分隔。不是表中字段的用‘’扩起来。
示例

#select concat(需要拼接的字段1，需要拼接的字段2) from 表名;
select concat(id,'(',name,')') from test;
+-------------------------+
| concat(id,'(',name,')') |
+-------------------------+
| 1(zhangsan)             |
| 2(lisi)                 |
| 3(wangwu)               |
| 4(kuaile)               |
| 5(lei)                  |
| 6(gaoxing)              |
| 7(kaixin)               |
| 8(xin)                  |
| 9(lei)                  |
+-------------------------+

LTrim()祛除查询出来字段左边的空格

示例

select * from test where id in(9,10);
+----+------+---------+
| id | name | pwd     |
+----+------+---------+
|  9 | lei  | 2000    |
| 10 |  qu  | 1232422 |
+----+------+---------+
2 rows in set (0.00 sec)

可以看出我第二条数据name字段前面有一个空格
加上LTrim之后空格就消失了，消除右边空格的是RTrim().

select LTrim(name) from test;
+-------------+
| LTrim(name) |
+-------------+
| zhangsan    |
| lisi        |
| wangwu      |
| kuaile      |
| lei         |
| gaoxing     |
| kaixin      |
| xin         |
| lei         |
| qu          |
+-------------+
10 rows in set (0.00 sec)

拼接和祛除空格组合使用

select concat(id,' name 是(',LTrim(name),')') from test;
+-----------------------------------------+
| concat(id,' name 是(',LTrim(name),')')  |
+-----------------------------------------+
| 1 name 是(zhangsan)                     |
| 2 name 是(lisi)                         |
| 3 name 是(wangwu)                       |
| 4 name 是(kuaile)                       |
| 5 name 是(lei)                          |
| 6 name 是(gaoxing)                      |
| 7 name 是(kaixin)                       |
| 8 name 是(xin)                          |
| 9 name 是(lei)                          |
| 10 name 是(qu)                          |
+-----------------------------------------+
10 rows in set (0.00 sec)

字段之间的计算

例子
操作符： + 加 - 减 * 乘 / 除

#select 计算字段1 算数操作符 计算字段2 from 表名;
select gongzi*yuefen as zonggongzi from gongzi;
+------------+
| zonggongzi |
+------------+
|      84000 |
|      80000 |
|      67500 |
|      25800 |
|      54000 |
|      65100 |
+------------+
6 rows in set (0.00 sec)

文本处理函数

示例表数据

 select * from gongzi;
+----+------+--------+--------+
| id | name | gongzi | yuefen |
+----+------+--------+--------+
|  1 | zhao |   7000 |     12 |
|  2 | qian |  10000 |      8 |
|  3 | sun  |   7500 |      9 |
|  4 | li   |   8600 |      3 |
|  5 | zhou |   9000 |      6 |
|  6 | wu   |   9300 |      7 |
+----+------+--------+--------+
6 rows in set (0.00 sec)

left 返回字符串左边的字符

示例

#select left(截取的字段名,截取的长度) from 表名;
select left(name,1) from gongzi;
+--------------+
| left(name,1) |
+--------------+
| z            |
| q            |
| s            |
| l            |
| z            |
| w            |
+--------------+
6 rows in set (0.00 sec)

length返回字符串长度

示例

#select length（求长度的字段名） from 表名;
select length(name) from gongzi;
+--------------+
| length(name) |
+--------------+
|            4 |
|            4 |
|            3 |
|            2 |
|            4 |
|            2 |
+--------------+
6 rows in set (0.00 sec)

locate() 找出字符串的一个子串

示例
返回1代表有，0代表没有

#select locate(name,'查找的字符串') from 表名
select locate(name,'zhao') from gongzi;
+---------------------+
| locate(name,'zhao') |
+---------------------+
|                   1 |
|                   0 |
|                   0 |
|                   0 |
|                   0 |
|                   0 |
+---------------------+
6 rows in set (0.00 sec)

lower将字符串转换为小写

示例

#select lower（转换的字段名） from 表名;
select lower(name) from gongzi;
+-------------+
| lower(name) |
+-------------+
| zhao        |
| qian        |
| sun         |
| li          |
| zhou        |
| wu          |
| zheng       |
+-------------+
7 rows in set (0.00 sec)

Upper 将字符串转换成大写

示例

select upper(name) from gongzi;
+-------------+
| upper(name) |
+-------------+
| ZHAO        |
| QIAN        |
| SUN         |
| LI          |
| ZHOU        |
| WU          |
| ZHENG       |
+-------------+
7 rows in set (0.00 sec)

数值处理函数

abs()返回一个数的绝对值

示例

#select abs(数值或字段名) from 表名;
select abs(-3);
+---------+
| abs(-3) |
+---------+
|       3 |
+---------+
1 row in set (0.00 sec)

cos()返回一个角度的余弦

示例

select cos(yuefen) from gongzi;
+----------------------+
| cos(yuefen)          |
+----------------------+
|   0.8438539587324921 |
| -0.14550003380861354 |
|  -0.9111302618846769 |
|  -0.9899924966004454 |
|    0.960170286650366 |
|   0.7539022543433046 |
|  0.28366218546322625 |
+----------------------+
7 rows in set (0.00 sec)

数值函数使用方法大致相同我就不一一举例了，下面是数值函数大全

Abs() 返回一个数的绝对值
Cos() 返回一个角度的余弦
Exp() 返回一个数的指数值
Mod() 返回除操作的余数
Pi() 返回圆周率
Rand() 返回一个随机数
Sin() 返回一个角度的正弦
Sqrt() 返回一个数的平方根
Tan() 返回一个角度的正切

汇总函数

示例表

 select * from gongzi;
+----+-------+--------+--------+
| id | name  | gongzi | yuefen |
+----+-------+--------+--------+
|  1 | zhao  |   7000 |     12 |
|  2 | qian  |  10000 |      8 |
|  3 | sun   |   7500 |      9 |
|  4 | li    |   8600 |      3 |
|  5 | zhou  |   9000 |      6 |
|  6 | wu    |   9300 |      7 |
|  7 | ZHENG |   6000 |      5 |
+----+-------+--------+--------+
7 rows in set (0.00 sec)

avg()平均数

示例

select avg(gongzi) from gongzi;
+-------------+
| avg(gongzi) |
+-------------+
|   8200.0000 |
+-------------+
1 row in set (0.00 sec)

count() 返回行数

示例

select count(name) from gongzi;
+-------------+
| count(name) |
+-------------+
|           7 |
+-------------+
1 row in set (0.00 sec)

count（）括号中可以加放字段，可以放星号，也可以放1.其中放1和星号进去计算的是全部行数，不会忽略null值，放字段进去计算的是这个字段有多少行，会忽略null值。

max()最大值

示例

select max(gongzi) from gongzi;
+-------------+
| max(gongzi) |
+-------------+
|       10000 |
+-------------+
1 row in set (0.00 sec)

min()最小值

示例

select min(gongzi) from gongzi;
+-------------+
| min(gongzi) |
+-------------+
|        6000 |
+-------------+
1 row in set (0.00 sec)

sum()求和

示例

select sum(gongzi) from gongzi;
+-------------+
| sum(gongzi) |
+-------------+
|       57400 |
+-------------+
1 row in set (0.00 sec)

分组和排序group by

group by用法

#select 查询的字段 from 表名 group by 需要进行分组的字段 having 分组之后的条件

select 字句顺序

字句          说明           是否必须使用
select    要返回的列或者表达式    是
from      从行中检索数据的表      仅在从表选择数据时使用
where     行级过滤              否
group by  分组说明             仅在分组时使用
having    组级过滤              否
order by  输出排序顺序           否
limit     要检索的行数           否