1. 各种各样的函数
sql自带了各种各样的函数,极大提高了sql语言的便利性。
所谓函数,类似一个黑盒子,你给它一个输入值,它便按照预设的程序定义给出返回值,输入值称为参数。
函数大致分为如下几类:
- 算术函数 (用来进行数值计算的函数)
- 字符串函数 (用来进行字符串操作的函数)
- 日期函数 (用来进行日期操作的函数)
- 转换函数 (用来转换数据类型和值的函数)
- 聚合函数 (用来进行数据聚合的函数)
函数总个数超过200个,不需要完全记住,常用函数有 30~50 个,其他不常用的函数使用时查阅文档即可。
1.1 算术函数
+ - * /四则运算在之前的章节介绍过,此处不再赘述。
为了演示其他的几个算数函数,在此构造samplemath表
– DDL :创建表
USE shop;
DROP TABLE IF EXISTS samplemath;
CREATE TABLE samplemath
(m float(10,3),
n INT,
p INT);
– DML :插入数据
INSERT INTO samplemath(m, n, p) VALUES (500, 0, NULL);
INSERT INTO samplemath(m, n, p) VALUES (-180, 0, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, NULL, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 7, 3);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 5, 2);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 4, NULL);
INSERT INTO samplemath(m, n, p) VALUES (8, NULL, 3);
INSERT INTO samplemath(m, n, p) VALUES (2.27, 1, NULL);
INSERT INTO samplemath(m, n, p) VALUES (5.555,2, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 1, NULL);
INSERT INTO samplemath(m, n, p) VALUES (8.76, NULL, NULL);
– 查询表内容
SELECT * FROM samplemath;
ABS – 绝对值
语法:ABS( 数值 )
ABS 函数用于计算一个数字的绝对值,表示一个数到原点的距离。
当 ABS 函数的参数为NULL时,返回值也是NULL。
MOD – 求余数
语法:MOD( 被除数,除数 )
MOD 是计算除法余数(求余)的函数,是 modulo 的缩写。小数没有余数的概念,只能对整数列求余数。
注意:主流的 DBMS 都支持 MOD 函数,只有SQL Server 不支持该函数,其使用%符号来计算余数。
ROUND – 四舍五入
语法:ROUND( 对象数值,保留小数的位数 )
ROUND 函数用来进行四舍五入操作。
注意:当参数保留小数的位数为变量时,可能会遇到错误,请谨慎使用变量。
SELECT m,
ABS(m) AS abs_col ,
n, p,
MOD(n, p) AS mod_col,
ROUND(m,1)AS round_colS
FROM samplemath;
1.2 字符串函数
字符串函数也经常被使用,为了学习字符串函数,在此我们构造samplestr表。
– DDL :创建表
USE shop;
DROP TABLE IF EXISTS samplestr;
CREATE TABLE samplestr
(str1 VARCHAR (40),
str2 VARCHAR (40),
str3 VARCHAR (40)
);
– DML:插入数据
INSERT INTO samplestr (str1, str2, str3) VALUES (‘opx’, ‘rt’, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES (‘abc’, ‘def’, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES (‘太阳’, ‘月亮’, ‘火星’);
INSERT INTO samplestr (str1, str2, str3) VALUES (‘aaa’, NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES (NULL, ‘xyz’, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES (‘ABC’, NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES (‘aBC’, NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES (‘abc哈哈’, ‘abc’, ‘ABC’);
INSERT INTO samplestr (str1, str2, str3) VALUES (‘abcdefabc’, ‘abc’, ‘ABC’);
INSERT INTO samplestr (str1, str2, str3) VALUES (‘micmic’, ‘i’, ‘I’);
– 确认表中的内容
SELECT * FROM samplestr;
CONCAT – 拼接
语法:CONCAT(str1, str2, str3)
MySQL中使用 CONCAT 函数进行拼接。
LENGTH – 字符串长度
语法:LENGTH( 字符串 )
LOWER – 小写转换
LOWER 函数只能针对英文字母使用,它会将参数中的字符串全都转换为小写。该函数不适用于英文字母以外的场合,不影响原本就是小写的字符。
类似的, UPPER 函数用于大写转换。
REPLACE – 字符串的替换
语法:REPLACE( 对象字符串,替换前的字符串,替换后的字符串 )
SUBSTRING – 字符串的截取
语法:SUBSTRING (对象字符串 FROM 截取的起始位置 FOR 截取的字符数)
使用 SUBSTRING 函数 可以截取出字符串中的一部分字符串。截取的起始位置从字符串最左侧开始计算,索引值起始为1。
(扩展内容)SUBSTRING_INDEX – 字符串按索引截取
语法:SUBSTRING_INDEX (原始字符串,分隔符,n)
该函数用来获取始字符串按照分隔符分割后,第 n 个分隔符之前(或之后)的子字符串,支持正向和反向索引,索引起始值分别为 1 和 -1。
SELECT SUBSTRING_INDEX(‘www.mysql.com’, ‘.’, 2);