MySQL入门（7）—— 基于datagrip的SQL函数

最新推荐文章于 2024-04-14 16:58:54 发布

小白乔学技术

最新推荐文章于 2024-04-14 16:58:54 发布

阅读量2.9k

点赞数 1

分类专栏：数据库文章标签： mysql sql 数据库 datagrip

本文链接：https://blog.csdn.net/qyt_722/article/details/117736054

版权

数据库专栏收录该内容

17 篇文章 22 订阅

订阅专栏

一、字段拼接函数

CONCAT（）函数用于将多个字符串连接成一个字符串格式：

 select CONCAT(str1,str2,…) from table_name;

例如查询商品表，返回一列：商品名称（价格）

SELECT concat(prod_name,'(',prod_price,')') FROM products;
SELECT concat(prod_name,'(',prod_price,'元)') FROM products;  #加单位

在这里插入图片描述

二、列的计算和别名

在列上可以进行加减乘除操作
例如查询 orderitems 表中，订单编号为 20005的产品id（prod_id），物品单价(item_price)，物品数量(quantity)。并计算每个产品的总价(total_price)

SELECT prod_id,item_price,quantity,item_price*quantity AS total_price FROM orderitems WHERE order_num=20005;

在这里插入图片描述

三、文本处理函数

1、查询产品表，将产品名称中左右字母转换为大写、小写

（1）upper(string)：将字符串转换为大写

SELECT upper(prod_name) FROM products;

在这里插入图片描述

（2）lower(string)：将字符串转换为小写

SELECT lower(prod_name) FROM products;

在这里插入图片描述

2、查询产品表，返回产品名称的前三个字符/后三个字符

（1）left(string,n)：返回字符串左边的n个字符

SELECT left(prod_name,3) FROM products;

在这里插入图片描述

（2）right(string,n)：返回字符串右边的n个字符

SELECT right(prod_name,3) FROM products;

在这里插入图片描述

3、substring()：返回子串的字符

（1）substring(字符串，n)

从第n个索引位置开始截取字符串

SELECT substring(prod_name,2)FROM products;  #索引从1开始

在这里插入图片描述

（2）substring(string from n)

SELECT substring(prod_name FROM 2) FROM products;

在这里插入图片描述

（3）substring(字符串，索引位置，n)

从索引位置开始截取字符串，向后截取n个字符

SELECT substring('qytverygood',2,3);
SELECT substring('qytverygood' FROM 2 FOR 3) ;

在这里插入图片描述

（4）substring_index(string，关键字，n)

从头开始截取字符串，向后截取到关键字出现的第n次

SELECT substring_index('www.qytCSDN.com','.',2) as abstract;

在这里插入图片描述

4、其他内容

（1）查询商品表，返回一列：商品名称（价格）。

SELECT concat(prod_name,'(',prod_price,')') FROM products;

在这里插入图片描述

（2）查询商品表，返回一列：商品名称（价格），并讲该列命名为prod_name_price。

SELECT concat(prod_name,' (',prod_price,')') AS prod_name_price FROM products;

在这里插入图片描述

（3）查询 orderitems 表中，订单编号为 20006的产品id（prod_id），物品单价(item_price)，物品数量(quantity)。并计算每个产品的总价(total_price)

SELECT prod_id,item_price,quantity,item_price*quantity AS total_price FROM orderitems WHERE order_num=20005;

在这里插入图片描述

5、其他函数

length(string)：返回字符串的长度
ltrim(string)：去掉字符串左边的空格
rtrim(string)：去掉字符串右边的空格
trim(string)：去掉字符串两边的空格

四、日期函数

首先需要注意的是MySQL使用的日期格式。指定一个日期时，不管是插入或更新表值还是用where子句进行过滤，日期格式推荐设置为：yyyy-mm-dd。这是首选的日期格式，因为他排除了多义性。
例如：2020年9月1日，给出为2020-09-01。

1、DATE_ADD() 增加日期

DATE_ADD() 函数向日期添加指定的时间间隔。格式：

DATE_ADD(date,INTERVAL expr type)

date 参数是合法的日期表达式
expr 参数是希望添加的时间间隔
type 参数可以是下列值

MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

2、DATE_FORMAT() 格式化时间

DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。
格式：

DATE_FORMAT(date,format)

date 参数是合法的日期。
format 规定日期/时间的输出格式。
可以使用的格式有：

格式	描述
%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值（00-31）
%e	月的天，数值（0-31）
%f	微秒
%H	小时（00-23）
%h	小时（01-12）
%I	小时（01-12）
%i	分钟，数值（00-59）
%j	年的天（001-366）
%k	小时（0-23）
%l	小时（1-12）
%M	月名
%m	月，数值（00-12）
%p	AM 或 PM
%r	时间，12-小时（hh:mm:ss AM 或 PM）
%S	秒（00-59）
%s	秒（00-59）
%T	时间, 24-小时（hh:mm:ss）
%U	周（00-53）星期日是一周的第一天
%u	周（00-53）星期一是一周的第一天
%V	周（01-53）星期日是一周的第一天，与 %X 使用
%v	周（01-53）星期一是一周的第一天，与 %x 使用
%W	星期名
%w	周的天（0=星期日, 6=星期六）
%X	年，其中的星期日是周的第一天，4 位，与 %V 使用
%x	年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y	年，4 位
%y	年，2 位

3、DATEDIFF() 函数返回两个日期之间的天数

格式：

DATEDIFF(date1,date2)   #只有日期部分参与运算

SELECT datediff('2020-09-15','2020-09-11') FROM orders;

在这里插入图片描述

4、常见日期函数

AddDate()：增加一个日期（天、周等）
AddTime()：增加一个时间（时、分等）
CurDate()：返回当前日期
CurTime()：返回当前时间
Date()：返回日期时间的日期部分
DateDiff()：计算两个日期之差
Date_Add()：高度灵活的日期运算函数
Date_Format()：返回一个格式化的日期或时间串
Day()：返回一个日期的天数部分
DayOfWeek()：对于一个日期，返回对应的星期几
Hour()：返回一个时间的小时部分
Minute()：返回一个时间的分钟部分
Month()：返回一个日期的月份部分
Now()：返回当前日期和时间
Second()：返回一个时间的秒部分
Time()：返回一个日期时间的时间部分
Year()：返回一个日期的年份部分

5、日期函数的应用

# 增加日期 语法格式：DATE_ADD(date,INTERVAL expr type)
SELECT adddate(order_date,INTERVAL 1 DAY ) FROM orders WHERE order_num=20005;

# 查询当前日期
SELECT curdate();

# 查询当前时间
SELECT curtime();

# 查询当前日期和时间
SELECT now();

# 查询订单列表，返回下单时间的日期部分
SELECT date(order_date) FROM orders;

# 查询订单表中的下单时间，将下单时间格式化为：年/月/日。格式
SELECT date_format(order_date,'%Y/%m/%d') FROM orders;

# 查询订单表的下单时间，只返回年份
SELECT year(order_date) FROM orders;

# 查询今天是一周中的第几天（西方周日为第一天）
SELECT dayofweek(now());

# 查询今天是周几
SELECT dayname(now());

五、数学函数

# 查询商品表，并对商品价格进行四舍五入
SELECT round(prod_price) FROM products;

round()：对数值进行四舍五入
ceil()：向上取整 返回大于等于该参数的最小整数
floor()：向下取整 返回小于该参数的最大整数值。
truncate()：截断 取小数点多少位**
abs()：返回一个数的绝对值
cos()：返回一个角度的余弦
exp()：返回一个数的指数值
mod()：返回除操作的余数
pi()：返回圆周率
rand()：返回一个随机数
sin()：返回一个角度的正弦 例如SIN(x) 返回 x 的正弦值，其中 x 为弧度值。
sqrt()：返回一个数的平方根
tan()：返回一个角度的正切

六、聚合函数

我们经常需要汇总数据而不用把它们实际检索出来，为此MySQL提供了专门的函数。
聚集函数（aggregate function）运行在行组上，计算和返回单个值的函数。
常见的聚合函数有一下几种：

1、COUNT()函数

COUNT() 函数返回匹配指定条件的行数。

（1）COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）格式：

SELECT COUNT(column_name) FROM table_name;

（2）COUNT( * ) 函数返回表中的记录数（null也计入）格式：

SELECT COUNT(*) FROM table_name;

（3）COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目，格式：

SELECT COUNT(DISTINCT column_name) FROM table_name;

（4）练习

# 查询商品表中一共有多少条数据 -- 注意和count（1）的区别，阿里手册推荐用count（*）
SELECT count(*) FROM products;
# 查询商品表中商品描述列，不为null 的数据有多少条
SELECT count(prod_desc) FROM products;
# 查询商品表中供应商有几家？
SELECT count(DISTINCT vend_id) FROM products;

在这里插入图片描述

2、MAX()函数

MAX() 函数返回指定列的最大值。格式：

SELECT MAX(column_name) FROM table_name;

练习：

# 查询商品表中单价最高的商品
SELECT max(prod_price) FROM products;

3、MIN()函数

MIN() 函数返回指定列的最小值。格式：

SELECT MIN(column_name) FROM table_name;

练习：

# 查询商品表中单价最低的商品
SELECT min(prod_price) FROM products;

4、SUM()函数

SUM() 函数返回数值列的总数。格式：

SELECT SUM(column_name) FROM table_name;

练习：

# 查询订单明细表中，商品数量（quantity）的总和
SELECT sum(quantity) FROM orderitems;
# 查询订单明细表中，订单编号为20005的订单的总订单金额
SELECT sum(quantity*item_price) FROM orderitems WHERE order_num=20005;

5、AVG()函数

AVG() 函数返回数值列的平均值。AVG()函数忽略列值为NULL的行。格式：

SELECT AVG(column_name) FROM table_name;

练习：

# 查询商品表中所有商品单价的平均值
SELECT avg(prod_price) FROM products;
# 查询商品表中，一共多少条商品、最低价格、最高价格、评价价格；
SELECT count(*),min(prod_price),max(prod_price),avg(prod_price) FROM products;