MySQL中函数的使用

luxinfeng666

已于 2022-03-20 15:04:02 修改

阅读量1k

点赞数

分类专栏：学习笔记文章标签： mysql sql

于 2022-03-20 15:03:48 首次发布

本文链接：https://blog.csdn.net/luxinfeng666/article/details/123613296

版权

学习笔记专栏收录该内容

21 篇文章 4 订阅

订阅专栏

本文详细介绍了如何创建数据库表`products`，包括字段定义、约束及外键设置，并填充了相关商品数据。同时，列举并解释了常见的文本处理和日期时间处理函数，如`LEFT`、`RIGHT`、`LENGTH`、`CURDATE`和`DATEDIFF`等，以及在实际查询中的应用示例。此外，还提到了一些基本数值处理函数，如`RAND`和`ABS`等。

摘要由CSDN通过智能技术生成

建表语句

create table products
(
    prod_id         char(10)      not null
        primary key,
    vend_id         int           not null,
    prod_name       char(255)     not null,
    prod_price      decimal(8, 2) not null,
    prod_desc       text          null,
    production_time datetime      null comment '产品生产日期',
    constraint fk_products_vendors
        foreign key (vend_id) references vendors (vend_id)
);

填充数据

INSERT INTO db_test.products (prod_id, vend_id, prod_name, prod_price, prod_desc, production_time) VALUES ('ANV01', 1001, '.5 ton anvil', 5.99, '.5 ton anvil, black, complete with handy hook', null);
INSERT INTO db_test.products (prod_id, vend_id, prod_name, prod_price, prod_desc, production_time) VALUES ('ANV02', 1001, '1 ton anvil', 9.99, '1 ton anvil, black, complete with handy hook and carrying case', null);
INSERT INTO db_test.products (prod_id, vend_id, prod_name, prod_price, prod_desc, production_time) VALUES ('ANV03', 1001, '2 ton anvil', 14.99, '2 ton anvil, black, complete with handy hook and carrying case', null);
INSERT INTO db_test.products (prod_id, vend_id, prod_name, prod_price, prod_desc, production_time) VALUES ('DTNTR', 1003, 'Detonator', 13.00, 'Detonator (plunger powered), fuses not included', null);
INSERT INTO db_test.products (prod_id, vend_id, prod_name, prod_price, prod_desc, production_time) VALUES ('FB', 1003, 'Bird seed', 10.00, 'Large bag (suitable for road runners)', null);
INSERT INTO db_test.products (prod_id, vend_id, prod_name, prod_price, prod_desc, production_time) VALUES ('FC', 1003, 'Carrots', 2.50, 'Carrots (rabbit hunting season only)', null);
INSERT INTO db_test.products (prod_id, vend_id, prod_name, prod_price, prod_desc, production_time) VALUES ('FU1', 1002, 'Fuses', 3.42, '1 dozen, extra long', null);
INSERT INTO db_test.products (prod_id, vend_id, prod_name, prod_price, prod_desc, production_time) VALUES ('JP1000', 1005, 'JetPack 1000', 35.00, 'JetPack 1000, intended for single use', '2021-03-20 14:29:28');
INSERT INTO db_test.products (prod_id, vend_id, prod_name, prod_price, prod_desc, production_time) VALUES ('JP2000', 1005, 'JetPack 2000', 55.00, 'JetPack 2000, multi-use', '2022-01-20 14:29:20');
INSERT INTO db_test.products (prod_id, vend_id, prod_name, prod_price, prod_desc, production_time) VALUES ('OL1', 1002, 'Oil can', 8.99, 'Oil can, red', '2022-02-20 14:29:35');
INSERT INTO db_test.products (prod_id, vend_id, prod_name, prod_price, prod_desc, production_time) VALUES ('SAFE', 1003, 'Safe', 50.00, 'Safe with combination lock', '2021-06-20 14:29:42');
INSERT INTO db_test.products (prod_id, vend_id, prod_name, prod_price, prod_desc, production_time) VALUES ('SLING', 1003, 'Sling', 4.49, 'Sling, one size fits all', '2021-08-08 14:30:06');
INSERT INTO db_test.products (prod_id, vend_id, prod_name, prod_price, prod_desc, production_time) VALUES ('TNT1', 1003, 'TNT (1 stick)', 2.50, 'TNT, red, single stick', '2022-02-23 14:30:24');
INSERT INTO db_test.products (prod_id, vend_id, prod_name, prod_price, prod_desc, production_time) VALUES ('TNT2', 1003, 'TNT (5 sticks)', 10.00, 'TNT, red, pack of 10 sticks', '2020-10-20 14:30:32');
INSERT INTO db_test.products (prod_id, vend_id, prod_name, prod_price, prod_desc, production_time) VALUES ('food', 1003, 'bamboo', 100.00, 'panda foods', curtime());

文本处理函数

常见的文本处理函数

● Left(str, n)：截取str左侧的n个字符
● Length(str)：统计某一字段的长度
● Locate(subStr, str)：找出串的一个子串
● Lower(str)：将串转换为小写
● LTrim(str)：去掉串左边的空格
● Right(str, n)：截取str右侧的n个字符
● Rtrim(str)：去掉串右边的空格
● Soundex(str)：比较字符串的发音，返回发音相同的记录
● SubString(str, pos)：返回子串的字符
● Upper(str)：将串转换为大写

select left(prod_name, 2), right(prod_name, 2), length(prod_name) from products;
select locate(prod_name, 'ab'), lower(prod_name), upper(prod_name) from products;
select LTRIM(RTRIM(prod_name)) as real_name, substring(prod_name, 2) from products;

日期和时间处理函数

常见的日期和时间处理函数

● CurDate()：返回当前日期
● CurTime()：返回当时时间
● Date()：返回日期/时间的日期部分
● DateDiff()：计算两个日期的差
● Day()：返回一个日期的天数部分
● DayOfWeek()：对于一个日期，返回对应的星期几
● Hour()：返回一个时间的小时（24小时制）
● Minute()：返回对应的分钟
● Month()：返回对应的月份
● Second()：返回对应的秒数
● Time()：返回一个日期时间的时间部分
● Year()：返回对应的年份

select MINUTE('2022-03-20 14:46:00');
select DAYOFWEEK('2022-03-20 14:46:00');
select DATEDIFF('2022-03-20 00:00:01', '2022-03-25 00:59:59');
select * from products where production_time between '2020-10-20 14:30:32' and CURTIME();
``

## 基本数值处理函数

**常见的数值处理函数**

● Abs()：返回一个数的绝对值
● Exp()：返回一个数的指数值
● Mod()：返回除操作的余数
● Pi()：返回圆周率
● Rand()：返回一个随机数
● Sqrt()：返回一个数的平方根

```sql
select Rand();