家居网站建设营销推广,网站搭建网站设置,最新项目加盟代理,山东东营市是几线城市1. 函数的介绍
1.1 函数简介
在编程中, 函数是一种组织代码的方式, 用于执行特定任务.
它是一段可以被重复使用的代码块, 通常接受一些输入(参数)然后返回一个输出.
函数可以帮助开发者将大型程序分解为更小的, 更易于管理的部分, 提高代码的可读性和可维护性.函数在编程语言…
1. 函数的介绍
1.1 函数简介
在编程中, 函数是一种组织代码的方式, 用于执行特定任务.
它是一段可以被重复使用的代码块, 通常接受一些输入(参数)然后返回一个输出.
函数可以帮助开发者将大型程序分解为更小的, 更易于管理的部分, 提高代码的可读性和可维护性.函数在编程语言和SQL中都扮演着非常重要的角色.
以下是对函数的一些详细解释:
* 1. 代码重用和模块化: 函数允许我们将一段常用的代码块封装起来, 并在需要时多次调用.这避免了代码的重复, 并使得代码结构更清晰, 模块化, 更易于阅读和维护.* 2. 提高开发效率: 使用函数, 我们可以更快地开发应用程序.由于函数是预先编写和测试过的代码块, 因此在使用它们时, 我们不必从零开始编写代码, 只需调用现有的函数即可.* 3. 提高代码可维护性: 如果某个功能需要修改或更新, 我们只需要修改相应的函数, 而无需查找并修改代码中所有相关的部分.这使得代码更易于维护, 减少了出错的可能性.在SQL中, 函数的作用同样强大:
* 1. 数据转换和处理: SQL函数可以用来转换和处理检索到的数据.例如, 我们可以用函数来处理字符串, 数值, 或者执行一些复杂的计算.* 2. 提高查询效率: 通过使用SQL函数, 我们可以在数据库查询中包含复杂的逻辑和操作, 而无需在应用程序代码中执行这些操作.这可以大大提高查询效率, 并减少网络传输的数据量.* 3. 增强数据管理能力: SQL函数的使用可以让数据库管理员更有效地管理数据库.例如, 他们可以编写函数来执行常规任务, 如备份, 恢复或清理数据, 从而提高管理效率.从函数定义的角度来看, 函数可以分为内置函数和自定义函数两类:
* 1. 内置函数: 是由编程语言或数据库系统预先定义好的函数.它们通常是通用功能, 例如字符串处理, 数学计算, 日期和时间操作等.对于编程语言来说, 内置函数通常作为标准库的一部分提供.对于SQL来说, 数据库系统(如MySQL, SQL Server, Oracle等)也提供了一系列内置函数, 用于数据处理和转换.* 2. 自定义函数: 是开发人员根据自己的需求编写的函数.它们通常用于实现特定业务逻辑或特定任务.自定义函数的编写需要遵循编程语言或数据库系统的函数编写规范.通过自定义函数, 开发人员可以扩展语言或数据库系统的功能, 使其更加符合特定的应用需求.无论是内置函数还是自定义函数, 它们的目的都是为了提高代码的可重用性, 可读性和维护性;
正确, 合理地使用函数, 可以使代码结构更加清晰, 提高开发效率, 减少错误, 并方便后续的修改和扩展.1.2 不同DBMS函数的差异
不同的数据库管理系统(DBMS)在函数支持方面存在很大的差异性.
尽管SQL语言本身是标准化的, 但各个DBMS厂商在实现时, 为了满足特定的需求或者提供更高的性能, 都会增加一些特定的函数和功能.这些差异可能包括以下几个方面:
* 1. 函数名称和语法: 不同的DBMS可能使用不同的函数名称和语法来完成相同的操作.例如, 字符串拼接操作, 一些DBMS使用(||)或者()作为拼接符, 而MySQL使用concat()函数.* 2. 函数参数和返回值: 即使不同DBMS中存在相同名称的函数, 它们的参数和返回值也可能有所不同.一些函数可能在某些DBMS中接受更多的参数, 或者在返回结果方面有所不同.* 3. 函数性能和优化: 不同的DBMS可能对相同的函数有不同的实现方式, 导致性能有所不同.一些DBMS可能针对特定函数进行了优化, 以提供更好的性能.由于这些差异的存在, 开发人员在使用SQL函数时需要特别注意. 为了提高代码的可移植性, 建议遵循以下几点最佳实践:
* 1. 尽量使用通用的函数和语法: 尽量使用那些被多个DBMS支持的通用函数和语法, 以减少差异.
* 2. 编写DBMS特定的代码: 如果需要使用某个DBMS特定的函数或功能, 可以在代码中明确标注, 并确保该代码仅在目标DBMS上运行.
* 3. 进行充分的测试: 在不同的DBMS上对代码进行充分的测试, 确保函数的行为和性能符合预期.总之, 了解并应对不同DBMS之间在函数支持方面的差异, 对于开发人员来说是非常重要的.
这有助于编写出更具可移植性和适应性的代码, 提高应用程序的稳定性和性能.1.3 MySQL的内置函数及分类
MySQL内置函数按功能可划分为以下几大类(某些函数可能跨越多个类别, 需要根据您的实际需求和场景来决定):
* 1. 数值函数: 用于数学运算和数值处理, 比如求和, 求平均值, 求绝对值等.
* 2. 字符串函数: 用于处理字符串数据, 包括字符串的合并, 截取, 查找, 替换等.
* 3. 日期和时间函数: 用于处理日期和时间数据, 例如获取当前日期, 计算日期差, 格式化日期等.
* 4. 流程控制函数: 用于实现条件判断和流程控制, 如IF函数, CASE函数等.
* 5. 加密和解密函数: 提供数据的加密和解密功能, 保证数据的安全性. 如AES加密函数等.
* 6. 信息函数: 用于获取MySQL数据库或服务器的信息, 如数据库版本, 当前连接数等.
* 7. 聚合(或分组)函数: 这类函数主要用于对多行数据进行聚合计算, 常常与GROUP BY子句一起使用, 如COUNT, SUM, AVG, MAX, MIN等.根据函数的操作和特性, 可以将其区分为单行函数和多行函数,
这种区分帮助我们更好地理解和应用这些函数:
* 1. 单行函数: 通常针对单个数据行进行操作, 它们接受一个或多个参数, 然后返回一个结果.这些函数通常执行简单的计算或转换任务, 处理单一的数据行.它们一般较简单, 方便在查询语句中使用, 并且能够快速返回结果.* 2. 多行函数(或称为聚合函数)则针对多个数据行进行操作, 并对这些行进行聚合计算.它们通常用于对数据进行统计, 汇总或计算平均值等操作.多行函数会对一组数据行进行处理, 并返回一个综合的结果.它们通常用于处理包含多个数据行的数据集, 并在这些数据行上进行计算和分析.需要注意的是, 这种区分是基于函数的操作和特性进行的逻辑划分, 而不是根据函数实际占用的行数来划分的.
无论是单行函数还是多行函数, 它们的具体实现可能会占用一行或多行代码, 这取决于函数的复杂性, 语法要求以及编程风格等因素.2. 数值类型函数
2.1 基本函数
函数功能示例ABS(x)返回数值x的绝对值.SELECT ABS(-10); 返回10.SIGN(X)返回X的符号. 正数返回1, 负数返回-1, 0返回0.SELECT SIGN(-10); 返回-1.PI()返回圆周率的值.SELECT PI(); 返回3.14159… .CEIL(x), CEILING(x)返回大于或等于x的最小整数(向上取整).SELECT CEIL(3.14); 返回4.FLOOR(x)返回小于或等于x的最大整数(向下取整.SELECT FLOOR(3.14); 返回3.LEAST(e1, e2,e3…)返回参数列表中的最小值.SELECT LEAST(10, 20, 30); 返回10.GREATEST(e1,e2,e3…)返回参数列表中的最大值.SELECT GREATEST(10, 20, 30); 返回30.MOD(x,y)返回x除以y的余数.SELECT MOD(10, 3); 返回1.RAND()返回0~1之间的随机浮点数(包含0但是不包含1).SELECT RAND(); 返回类似于0.12345的随机数.RAND(x)使用x作为种子值, 返回一个0~1之间的随机数.相同的x会产生相同的随机数.SELECT RAND(10); 对于相同的种子值, 总是返回相同的随机数, 这可以用于生成可重复的随机序列.ROUND(x)对x进行四舍五入, 返回最接近的整数.SELECT ROUND(3.5); 返回4.ROUND(x,y)对x进行四舍五入, 并保留小数点后y位.SELECT ROUND(3.567, 2); 返回3.57.TRUNCATE(x,y)截断数字x到y位小数.SELECT TRUNCATE(3.567, 2); 返回3.56.SQRT(x)返回x的平方根. 如果x为负数, 则返回NULL.SELECT SQRT(9); 返回3.
mysql SELECT ABS(-123), ABS(32), SIGN(-23), SIGN(43), PI() FROM DUAL;
---------------------------------------------------
| ABS(-123) | ABS(32) | SIGN(-23) | SIGN(43) | PI() |
---------------------------------------------------
| 123 | 32 | -1 | 1 | 3.141593 |
---------------------------------------------------
1 row in set (0.00 sec)mysql SELECT CEIL(32.32), CEILING(-43.23), FLOOR(32.32), FLOOR(-43.23), MOD(12,5) FROM DUAL;
----------------------------------------------------------------------
| CEIL(32.32) | CEILING(-43.23) | FLOOR(32.32) | FLOOR(-43.23) | MOD(12,5) |
----------------------------------------------------------------------
| 33 | -43 | 32 | -44 | 2 |
----------------------------------------------------------------------
1 row in set (0.00 sec)-- 随机数:
mysql SELECT RAND(), RAND(), RAND(10), RAND(10), RAND(-1), RAND(-1) FROM DUAL;
------------------------------------------------------------------------------------------------------------------------
| RAND() | RAND() | RAND(10) | RAND(10) | RAND(-1) | RAND(-1) |
------------------------------------------------------------------------------------------------------------------------
| 0.7014638667008503 | 0.6726362497290934 | 0.6570515219653505 | 0.6570515219653505 | 0.9050373219931845 | 0.9050373219931845 |
------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)-- 四舍五入:
mysql SELECT ROUND(12.33), ROUND(12.343,2), ROUND(12.324,-1), TRUNCATE(12.66,1), TRUNCATE(12.66,-1) FROM DUAL;
----------------------------------------------------------------------------------------
| ROUND(12.33) | ROUND(12.343,2) | ROUND(12.324,-1) | TRUNCATE(12.66,1) | TRUNCATE(12.66,-1) |
----------------------------------------------------------------------------------------
| 12 | 12.34 | 10 | 12.6 | 10 |
----------------------------------------------------------------------------------------
1 row in set (0.00 sec)2.2 角度与弧度互换函数
函数函数RADIANS(x) 将角度转化为弧度. 其中, 参数x为角度值.DEGREES(x) 将弧度转化为角度, 其中, 参数x为弧度值.
mysql SELECT RADIANS(30), RADIANS(60), RADIANS(90), DEGREES(2*PI()), DEGREES(RADIANS(90)) FROM DUAL;
---------------------------------------------------------------------------------------------------
| RADIANS(30) | RADIANS(60) | RADIANS(90) | DEGREES(2*PI()) | DEGREES(RADIANS(90)) |
---------------------------------------------------------------------------------------------------
| 0.5235987755982988 | 1.0471975511965976 | 1.5707963267948966 | 360 | 90 |
---------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)2.3 三角函数
函数用法描述SIN(x)返回x的正弦值.参数x为弧度值.ASIN(x)返回x的反正弦值.获取正弦为x的值. 如果x的值不在-1到1之间, 则返回NULL.COS(x)返回x的余弦值.参数x为弧度值.ACOS(x)返回x的反余弦值.获取余弦为x的值. 如果x的值不在-1到1之间, 则返回NULL.TAN(x)返回x的正切值.参数x为弧度值.ATAN(x)返回x的反正切值.返回正切值为x的值.ATAN2(m,n)返回两个参数的反正切值.返回m和n的反正切值.COT(x)返回x的余切值.X为弧度值.
ATAN2(M,N)函数返回两个参数的反正切值.
与ATAN(X)函数相比, ATAN2(M,N)需要两个参数, 例如有两个点point(x1,y1)和point(x2,y2),
使用ATAN(X)函数计算反正切值为ATAN((y2-y1)/(x2-x1)), 使用ATAN2(M,N)计算反正切值则为ATAN2(y2-y1,x2-x1).
由使用方式可以看出, 当x2-x1等于0时, ATAN(X)函数会报错, 而ATAN2(M,N)函数则仍然可以计算.mysql SELECT SIN(RADIANS(30)), DEGREES(ASIN(1)), TAN(RADIANS(45)), DEGREES(ATAN(1)), DEGREES(ATAN2(1,1)) FROM DUAL;
--------------------------------------------------------------------------------------------------
| SIN(RADIANS(30)) | DEGREES(ASIN(1)) | TAN(RADIANS(45)) | DEGREES(ATAN(1)) | DEGREES(ATAN2(1,1)) |
--------------------------------------------------------------------------------------------------
| 0.49999999999999994 | 90 | 0.9999999999999999 | 45 | 45 |
--------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)
2.4 指数与对数
函数用法描述POW(x,y), POWER(X,Y)返回x的y次方.接受两个参数x和y, 返回x的y次方.EXP(X)返回e的X次方.接受一个参数X, 返回以常数e (2.718281828459045) 为底的X次方.LN(X), LOG(X)返回以e为底的X的对数.接受一个参数X, 返回以e为底的X的对数.当X 0 时, 返回的结果为NULL.LOG10(X)返回以10为底的X的对数.接受一个参数X, 返回以10为底的X的对数.当X 0 时, 返回的结果为NULL.LOG2(X)返回以2为底的X的对数.接受一个参数X, 返回以2为底的X的对数.当X 0 时, 返回NULL.
mysql SELECT POW(2,5), POWER(2,4), EXP(2), LN(10), LOG10(10), LOG2(4) FROM DUAL;
-------------------------------------------------------------------------------
| POW(2,5) | POWER(2,4) | EXP(2) | LN(10) | LOG10(10) | LOG2(4) |
-------------------------------------------------------------------------------
| 32 | 16 | 7.38905609893065 | 2.302585092994046 | 1 | 2 |
-------------------------------------------------------------------------------
1 row in set (0.01 sec)2.5 进制转换
函数用法示例输入示例输出BIN(x)返回x的二进制编码BIN(10)1010OCT(x)返回x的八进制编码OCT(64)100HEX(x)返回x的十六进制编码HEX(255)FFCONV(x, f1, f2)返回f1进制数变成f2进制数CONV(1010, 2, 16)A
mysql SELECT BIN(10), HEX(10), OCT(10), CONV(10,2,8) FROM DUAL;
-----------------------------------------
| BIN(10) | HEX(10) | OCT(10) | CONV(10,2,8) |
-----------------------------------------
| 1010 | A | 12 | 2 |
-----------------------------------------
1 row in set (0.00 sec)3. 字符串函数
* MySQL中, 字符串的索引是从1开始的.函数用法ASCII(S)返回字符串S中的第一个字符的ASCII码值.CHAR_LENGTH(s)返回字符串s的字符数(与CHARACTER_LENGTH(s)相同).LENGTH(s)返回字符串s的字节数(和字符集有关).CONCAT(s1, s2, ..., sn)连接s1, s2, ..., sn为一个字符串.CONCAT_WS(x, s1, s2, ..., sn)同CONCAT(s1, s2, ...)函数, 但是每个字符串之间要加上x.INSERT(str, idx, len, replacestr)将字符串str从第idx位置开始, len个字符长的子串替换为字符串replacestr.REPLACE(str, a, b)用字符串b替换字符串str中所有出现的字符串a.UPPER(s) 或 UCASE(s)将字符串s的所有字母转成大写字母.LOWER(s) 或 LCASE(s)将字符串s的所有字母转成小写字母.LEFT(str, n)返回字符串str最左边的n个字符.RIGHT(str, n)返回字符串str最右边的n个字符.LPAD(str, len, pad)用字符串pad对str最左边进行填充, 直到str的长度为len个字符.RPAD(str, len, pad)用字符串pad对str最右边进行填充, 直到str的长度为len个字符.LTRIM(s)去掉字符串s左侧的空格.RTRIM(s)去掉字符串s右侧的空格.TRIM(s)去掉字符串s开始与结尾的空格.TRIM(s1 FROM s)去掉字符串s开始与结尾的s1.TRIM(LEADING s1 FROM s)去掉字符串s开始处的s1.TRIM(TRAILING s1 FROM s)去掉字符串s结尾处的s1.REPEAT(str, n)返回str重复n次的结果.SPACE(n)返回n个空格.STRCMP(s1, s2)依次比较字符串s1和s2的ASCII码值的大小.SUBSTR(s, index, len)返回从字符串s的index位置开始的len个字符(与SUBSTRING(s, n, len), MID(s, n, len)相同).LOCATE(substr, str)返回字符串substr在字符串str中首次出现的位置(与POSITION(substr IN str), INSTR(str, substr)相同). 未找到则返回0.ELT(m, s1, s2, …, sn)返回指定位置的字符串, 如果m1, 则返回s1; 如果m2, 则返回s2; 以此类推.FIELD(s, s1, s2, …,sn)返回字符串s在字符串列表中第一次出现的位置.FIND_IN_SET(s1, s2)返回字符串s1在字符串s2中出现的位置. 其中, 字符串s2是一个以逗号分隔的字符串.REVERSE(s)返回反转后的字符串.NULLIF(value1, value2)比较两个值, 如果value1与value2相等, 则返回NULL, 否则返回value1.
-- 查询字符串mm在字符序列中的位置; 查询字符串mm在字符集合的位置:
mysql SELECT FIELD(mm, hello, msm, amma), FIND_IN_SET(mm, hello, mm, amma) FROM DUAL;
---------------------------------------------------------------------------
| FIELD(mm, hello, msm, amma) | FIND_IN_SET(mm, hello, mm, amma) |
---------------------------------------------------------------------------
| 0 | 0 |
---------------------------------------------------------------------------
1 row in set (0.01 sec)-- 比较两个值是否相等:
mysql SELECT NULLIF(mysql, mysql), NULLIF(mysql, );
-----------------------------------------------
| NULLIF(mysql, mysql) | NULLIF(mysql, ) |
-----------------------------------------------
| NULL | mysql |
-----------------------------------------------
1 row in set (0.00 sec)4. 日期和时间函数
4.1 获取日期, 时间
函数用法CURDATE(), CURRENT_DATE()返回当前日期, 只包含年, 月, 日.CURTIME() , CURRENT_TIME()返回当前时间, 只包含时, 分, 秒.NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()返回当前系统日期和时间, 包含年, 月, 日, 时, 分, 秒.UTC_DATE()返回UTC(世界标准时间)日期, 只包含年, 月, 日.UTC_TIME()返回UTC(世界标准时间)时间, 只包含时, 分, 秒.
上类函数返回的是一个日期时间类型的值, 在这个值后面加上0, 将日期时间值转换为纯数字表示方式.-- 当前日期; 当前时间; 当前系统时间; 纯数字系统时间:
mysql SELECT CURDATE(), CURTIME(), NOW(), SYSDATE()0 FROM DUAL;
------------------------------------------------------------
| CURDATE() | CURTIME() | NOW() | SYSDATE()0 |
------------------------------------------------------------
| 2023-11-12 | 11:00:24 | 2023-11-12 11:00:24 | 20231112110024 |
------------------------------------------------------------
1 row in set (0.00 sec)-- 世界标准日期; 纯数字世界标准日期; 世界标准时间; 纯数字世界标准时间:
mysql SELECT UTC_DATE(), UTC_DATE()0, UTC_TIME(), UTC_TIME()0 FROM DUAL;
----------------------------------------------------
| UTC_DATE() | UTC_DATE()0 | UTC_TIME() | UTC_TIME()0 |
----------------------------------------------------
| 2023-11-12 | 20231112 | 03:00:37 | 30037 |
----------------------------------------------------
1 row in set (0.00 sec)4.2 日期与时间戳的转换
函数用法示例UNIX_TIMESTAMP()以UNIX时间戳的形式返回当前时间.SELECT UNIX_TIMESTAMP(); - 1634348884UNIX_TIMESTAMP(date)将时间date以UNIX时间戳的形式返回.SELECT UNIX_TIMESTAMP(2023-09-25 12:00:00); - 对应的UNIX时间戳.FROM_UNIXTIME(timestamp)将UNIX时间戳的时间转换为普通格式的时间.SELECT FROM_UNIXTIME(1634348884); - 2021-10-16 08:28:04
-- 将当前时间转为时间戳:
mysql SELECT UNIX_TIMESTAMP(now()) FROM DUAL;
-----------------------
| UNIX_TIMESTAMP(now()) |
-----------------------
| 1699758866 |
-----------------------
1 row in set (0.00 sec)-- 将当前时间转为时间戳:
mysql SELECT UNIX_TIMESTAMP() FROM DUAL;
------------------
| UNIX_TIMESTAMP() |
------------------
| 1699758639 |
------------------
1 row in set (0.00 sec)-- 将指定时间转为时间戳:
mysql SELECT UNIX_TIMESTAMP(2023-09-25 12:00:00) FROM DUAL;
---------------------------------------
| UNIX_TIMESTAMP(2023-09-25 12:00:00) |
---------------------------------------
| 1695614400 |
---------------------------------------
1 row in set (0.00 sec)-- 将时间戳转为时间格式:
mysql SELECT FROM_UNIXTIME(1699758639) FROM DUAL;
---------------------------
| FROM_UNIXTIME(1699758639) |
---------------------------
| 2023-11-12 11:10:39 |
---------------------------
1 row in set (0.01 sec)4.3 获取月份, 星期, 星期数, 天数等函数
函数用法示例YEAR(date)返回日期中的年份.SELECT YEAR(2023-09-25); 返回2023.MONTH(date)返回日期中的月份.SELECT MONTH(2023-09-25); 返回9.DAY(date)返回日期中的天数.SELECT DAY(2023-09-25); 返回25.HOUR(time)返回时间中的小时数.SELECT HOUR(14:30:00); 返回14.MINUTE(time)返回时间中的分钟数.SELECT MINUTE(14:30:00); 返回30.SECOND(time)返回时间中的秒数.SELECT SECOND(14:30:45); 返回45.MONTHNAME(date)返回日期的月份名称.SELECT MONTHNAME(2023-09-25); 返回September.DAYNAME(date)返回日期是星期几的单词名称.SELECT DAYNAME(2023-09-25); 返回Monday.WEEKDAY(date)返回日期是星期几, 周一是0, 周日是6.SELECT WEEKDAY(2023-09-25); 返回对应的数字.QUARTER(date)返回日期所在的季度.SELECT QUARTER(2023-09-25); 返回3.WEEK(date), WEEKOFYEAR(date)返回日期是一年中的第几周.SELECT WEEK(2023-09-25); 返回对应的周数.DAYOFYEAR(date)返回日期是一年中的第几天.SELECT DAYOFYEAR(2023-09-25); 返回268.DAYOFMONTH(date)返回日期是所在月份的第几天.SELECT DAYOFMONTH(2023-09-25); 返回25.DAYOFWEEK(date)返回日期是星期几, 周日是1, 周六是7.SELECT DAYOFWEEK(2023-09-25); 返回对应的数字.
-- 获取年; 月; 日; 时; 分; 秒:
mysql SELECT YEAR(CURDATE()), MONTH(CURDATE()), DAY(CURDATE()), HOUR(CURTIME()), MINUTE(NOW()), SECOND(SYSDATE())
FROM DUAL;
------------------------------------------------------------------------------------------------------
| YEAR(CURDATE()) | MONTH(CURDATE()) | DAY(CURDATE()) | HOUR(CURTIME()) | MINUTE(NOW()) | SECOND(SYSDATE()) |
------------------------------------------------------------------------------------------------------
| 2023 | 11 | 12 | 11 | 43 | 56 |
------------------------------------------------------------------------------------------------------
1 row in set (0.01 sec)-- 月份名称; 星期名称; 周几:
mysql SELECT MONTHNAME(2021-10-26), DAYNAME(2021-10-26), WEEKDAY(2021-10-26) FROM DUAL;
-----------------------------------------------------------------------
| MONTHNAME(2021-10-26) | DAYNAME(2021-10-26) | WEEKDAY(2021-10-26) |
-----------------------------------------------------------------------
| October | Tuesday | 1 |
-----------------------------------------------------------------------
1 row in set (0.00 sec)-- 第几季度; 今年的第几周; 今年的第几天; 这个月的第几天; 星期几的对应数字:
mysql SELECT QUARTER(CURDATE()), WEEK(CURDATE()), DAYOFYEAR(NOW()), DAYOFMONTH(NOW()), DAYOFWEEK(NOW()) FROM DUAL;
--------------------------------------------------------------------------------------------
| QUARTER(CURDATE()) | WEEK(CURDATE()) | DAYOFYEAR(NOW()) | DAYOFMONTH(NOW()) | DAYOFWEEK(NOW()) |
--------------------------------------------------------------------------------------------
| 4 | 46 | 316 | 12 | 1 |
--------------------------------------------------------------------------------------------
1 row in set (0.00 sec)4.4 日期的操作函数
函数用法EXTRACT(type FROM date)返回指定日期中特定的部分, type指定返回的值.
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年和月
-- 分钟数; 一年的第几周:
mysql SELECT EXTRACT(MINUTE FROM NOW()), EXTRACT( WEEK FROM NOW()) FROM DUAL;
-------------------------------------------------------
| EXTRACT(MINUTE FROM NOW()) | EXTRACT( WEEK FROM NOW()) |
-------------------------------------------------------
| 56 | 46 |
-------------------------------------------------------
1 row in set (0.00 sec)-- 第几个季度; 分秒:
mysql SELECT EXTRACT( QUARTER FROM NOW()),EXTRACT( MINUTE_SECOND FROM NOW()) FROM DUAL;
------------------------------------------------------------------
| EXTRACT( QUARTER FROM NOW()) | EXTRACT( MINUTE_SECOND FROM NOW()) |
------------------------------------------------------------------
| 4 | 5656 |
------------------------------------------------------------------
1 row in set (0.00 sec)4.5 时间和秒钟转换的函数
函数用法示例TIME_TO_SEC(time)将时间time转化为秒, 并返回结果值. 转化公式为: 小时*3600 分钟*60 秒.SELECT TIME_TO_SEC(02:30:45); 返回结果为9045, 即2小时30分钟45秒转换为9045秒.SEC_TO_TIME(seconds)将秒数 seconds 转化为包含小时, 分钟和秒的时间格式, 并返回结果.SELECT SEC_TO_TIME(9045); 返回结果为 02:30:45, 即9045秒转换为2小时30分钟45秒的时间格式.
-- 时间转为秒:
mysql SELECT TIME_TO_SEC(NOW()) FROM DUAL;
--------------------
| TIME_TO_SEC(NOW()) |
--------------------
| 43347 |
--------------------
1 row in set (0.00 sec)-- 秒转换为时分秒:
mysql SELECT SEC_TO_TIME(78774) FROM DUAL;
--------------------
| SEC_TO_TIME(78774) |
--------------------
| 21:52:54 |
--------------------
1 row in set (0.01 sec)4.6 计算日期和时间的函数
4.6.1 方式1
函数用法示例DATE_ADD(datetime, INTERVAL expr type)在给定的日期时间datetime 上添加指定的时间间隔 INTERVAL expr type, 并返回结果日期时间.SELECT DATE_ADD(2023-09-25 10:30:00, INTERVAL 5 DAY); 返回2023-10-01 10:30:00ADDDATE(date, INTERVAL expr type)与 DATE_ADD 函数相同, 可以在日期date上添加指定的时间间隔.SELECT ADDDATE(2023-09-25, INTERVAL 1 MONTH); 返回2023-10-25DATE_SUB(date, INTERVAL expr type)在给定的日期时间date上减去指定的时间间隔 INTERVAL expr type, 并返回结果日期时间.SELECT DATE_SUB(2023-09-25, INTERVAL 3 YEAR); 返回2020-09-25SUBDATE(date, INTERVAL expr type)与DATE_SUB函数相同, 可以在日期date上减去指定的时间间隔.SELECT SUBDATE(2023-09-25, INTERVAL 1 WEEK); 返回2023-09-18
间隔类型含义HOUR小时MINUTE分钟SECOND秒YEAR年MONTH月DAY日YEAR_MONTH年和月DAY_HOUR日和小时DAY_MINUTE日和分钟DAY_SECOND日和秒HOUR_MINUTE小时和分钟HOUR_SECOND小时和秒MINUTE_SECOND分钟和秒
-- 加1天:
mysql SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS col1 FROM DUAL;
---------------------
| col1 |
---------------------
| 2023-11-13 12:39:41 |
---------------------
1 row in set (0.00 sec)-- 加1秒:
mysql SELECT DATE_ADD(2021-10-21 23:32:12, INTERVAL 1 SECOND) AS col2 FROM DUAL;
---------------------
| col2 |
---------------------
| 2021-10-21 23:32:13 |
---------------------
1 row in set (0.00 sec)-- 加一秒:
mysql SELECT ADDDATE(2021-10-21 23:32:12, INTERVAL 1 SECOND) AS col3 FROM DUAL;
---------------------
| col3 |
---------------------
| 2021-10-21 23:32:13 |
---------------------
1 row in set (0.00 sec)-- 加1年1分, 可以使用空格或下划线间隔, 使用单引号包裹:
mysql SELECT DATE_ADD(2021-10-21 23:32:12,INTERVAL 1_1 MINUTE_SECOND) AS col4 FROM DUAL;
---------------------
| col4 |
---------------------
| 2021-10-21 23:33:13 |
---------------------
1 row in set (0.00 sec)-- 使用负数减去n年:
mysql SELECT DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5 FROM DUAL;
---------------------
| col5 |
---------------------
| 2022-11-12 12:41:00 |
---------------------
1 row in set (0.00 sec)-- 加1年一月:
mysql SELECT DATE_ADD(NOW(), INTERVAL 1 1 YEAR_MONTH) AS col6 FROM DUAL;
---------------------
| col6 |
---------------------
| 2024-12-12 12:50:02 |
---------------------
1 row in set (0.00 sec)-- 减31天:
mysql SELECT DATE_SUB(2021-01-21, INTERVAL 31 DAY) AS col1 FROM DUAL;
------------
| col1 |
------------
| 2020-12-21 |
------------
1 row in set (0.00 sec)-- 减31天:
mysql SELECT SUBDATE(2021-01-21, INTERVAL 31 DAY) AS col2 FROM DUAL;
------------
| col2 |
------------
| 2020-12-21 |
------------
1 row in set (0.00 sec)-- 减1天1小时:
mysql SELECT DATE_SUB(2021-01-21 02:01:01, INTERVAL 1 1 DAY_HOUR) AS col3 FROM DUAL;
---------------------
| col3 |
---------------------
| 2021-01-20 01:01:01 |
---------------------
1 row in set (0.00 sec)4.6.2 方式2
函数名用法描述ADDTIME(time1, time2)返回time1加上time2的时间.当time2为一个数字时, 代表的是秒, 可以为负数.SUBTIME(time1, time2)返回time1减去time2后的时间.当time2为一个数字时, 代表的是秒, 可以为负数.DATEDIFF(date1, date2)返回date1 - date2的日期间隔天数.返回两个日期之间的天数差.TIMEDIFF(time1, time2)返回time1 - time2的时间间隔.返回两个时间之间的时间差.FROM_DAYS(N)返回从0000年1月1日起, N天以后的日期.根据给定的天数返回相应的日期.TO_DAYS(date)返回日期date距离0000年1月1日的天数.返回给定日期与0000年1月1日之间的天数.LAST_DAY(date)返回date所在月份的最后一天的日期.返回给定日期所在月份的最后一天.MAKEDATE(year, n)针对给定年份与所在年份中的天数返回一个日期.根据给定的年份和天数返回一个日期.MAKETIME(hour, minute, second)将给定的小时, 分钟和秒组合成时间并返回.将给定的小时, 分钟和秒组合成一个时间.PERIOD_ADD(time, n)返回time加上n后的时间.在给定的时间上添加指定的时间间隔.
-- 加, 减20秒:
mysql SELECT ADDTIME(NOW(), 20), SUBTIME(NOW(), 30) FROM DUAL;
------------------------------------------
| ADDTIME(NOW(), 20) | SUBTIME(NOW(), 30) |
------------------------------------------
| 2023-11-12 13:14:10 | 2023-11-12 13:13:20 |
------------------------------------------
1 row in set (0.00 sec)-- 减去1小时1分钟1秒; 计算天数差:
mysql SELECT SUBTIME(NOW(), 1:1:3), DATEDIFF(NOW(), 2021-10-01) FROM DUAL;
--------------------------------------------------------
| SUBTIME(NOW(), 1:1:3) | DATEDIFF(NOW(), 2021-10-01) |
--------------------------------------------------------
| 2023-11-12 12:14:01 | 772 |
--------------------------------------------------------
1 row in set (0.00 sec)-- 计算时间差, 天:时:分 :
mysql SELECT TIMEDIFF(NOW(), 2021-10-25 22:10:10) FROM DUAL;
----------------------------------------
| TIMEDIFF(NOW(), 2021-10-25 22:10:10) |
----------------------------------------
| 838:59:59 |
----------------------------------------
1 row in set, 1 warning (0.00 sec)-- 0000年加366的日期; 0000年到0000-12-25的天数; 当前时间月份的最后一天:
mysql SELECT FROM_DAYS(366), TO_DAYS(0000-12-25), LAST_DAY(NOW()) FROM DUAL;
--------------------------------------------------------
| FROM_DAYS(366) | TO_DAYS(0000-12-25) | LAST_DAY(NOW()) |
--------------------------------------------------------
| 0001-01-01 | 359 | 2023-11-30 |
--------------------------------------------------------
1 row in set (0.00 sec)-- 制作时间: 今年的第一个月份 12天:
mysql SELECT MAKEDATE(YEAR(NOW()), 12) FROM DUAL;
---------------------------
| MAKEDATE(YEAR(NOW()), 12) |
---------------------------
| 2023-01-12 |
---------------------------
1 row in set (0.00 sec)-- 制作时间; 增加10秒:
mysql SELECT MAKETIME(10, 21, 23), PERIOD_ADD(20200101010101, 10) FROM DUAL;
------------------------------------------------------
| MAKETIME(10, 21, 23) | PERIOD_ADD(20200101010101, 10) |
------------------------------------------------------
| 10:21:23 | 20200101010111 |
------------------------------------------------------
1 row in set (0.00 sec)-- 现在的时间50秒:
mysql SELECT ADDTIME(NOW(), 50) FROM DUAL;
---------------------
| ADDTIME(NOW(), 50) |
---------------------
| 2023-11-12 20:34:10 |
---------------------
1 row in set (0.00 sec)-- 现在的时间 1小时1分钟1秒:
mysql SELECT ADDTIME(NOW(), 1:1:1) FROM DUAL;
-------------------------
| ADDTIME(NOW(), 1:1:1) |
-------------------------
| 2023-11-12 21:35:15 |
-------------------------
1 row in set (0.00 sec)-- 现在的时间 - 1小时1分钟1秒:
mysql SELECT SUBTIME(NOW(), 1:1:1) FROM DUAL;
-------------------------
| SUBTIME(NOW(), 1:1:1) |
-------------------------
| 2023-11-12 19:34:13 |
-------------------------
1 row in set (0.00 sec)-- 负数在这里不会负负得正, 而是保持不变:
mysql SELECT SUBTIME(NOW(), -1:-1:-1) FROM DUAL;
----------------------------
| SUBTIME(NOW(), -1:-1:-1) |
----------------------------
| 2023-11-12 20:35:54 |
----------------------------
1 row in set, 1 warning (0.00 sec) -- 一个警告-- 0000年366天的时间:
mysql SELECT FROM_DAYS(366) FROM DUAL;
----------------
| FROM_DAYS(366) |
----------------
| 0001-01-01 |
----------------
1 row in set (0.00 sec)-- 制作日期, 提供的参数为年, 天:
mysql SELECT MAKEDATE(2020, 1) FROM DUAL;
------------------
| MAKEDATE(2020,1) |
------------------
| 2020-01-01 |
------------------
1 row in set (0.00 sec)-- 32天转为2月1日:
mysql SELECT MAKEDATE(2020, 32) FROM DUAL;
-------------------
| MAKEDATE(2020,32) |
-------------------
| 2020-02-01 |
-------------------
1 row in set (0.00 sec)-- 制作时间:
mysql SELECT MAKETIME(1, 1, 1) FROM DUAL;
-----------------
| MAKETIME(1,1,1) |
-----------------
| 01:01:01 |
-----------------
1 row in set (0.01 sec)-- 增加1秒时间:
mysql SELECT PERIOD_ADD(20200101010101, 1) FROM DUAL;
-------------------------------
| PERIOD_ADD(20200101010101, 1) |
-------------------------------
| 20200101010102 |
-------------------------------
1 row in set (0.00 sec)-- 与0000年1月1日到现在的天数:
mysql SELECT TO_DAYS(NOW()) FROM DUAL;
----------------
| TO_DAYS(NOW()) |
----------------
| 739202 |
----------------
1 row in set (0.00 sec)-- 时间的使用场景举例, 查询7天内的新增用户数有多少?
SELECT COUNT(*) as num FROM new_user WHERE TO_DAYS(NOW()) - TO_DAYS(regist_time) 7;4.7 日期的格式化与解析
函数用法DATE_FORMAT(date, fmt)按照字符串fmt格式化日期date值. 返回格式化后的日期字符串.TIME_FORMAT(time, fmt)按照字符串fmt格式化时间time值. 返回格式化后的时间字符串.GET_FORMAT(date_type, format_type)返回日期字符串的显示格式. date_type指定日期类型(ISO, EUR或其他), format_type指定格式类型(DATE, TIME或DATETIME). 返回指定日期类型和格式类型的日期字符串显示格式.STR_TO_DATE(str, fmt)按照字符串fmt对str进行解析, 解析为一个日期. 返回解析后的日期值. 如果解析失败, 则返回NULL.
非GET_FORMAT函数中fmt参数常用的格式符:格式符说明%Y4位数字表示年份%y两位数字表示年份%M月名表示月份(January, February, ...)%m两位数字表示月份(01, 02, 03, ...)%b缩写的月名(Jan., Feb., ...)%c数字表示月份(1, 2, 3, ...)%D英文后缀表示月中的天数(1st, 2nd, 3rd, ...)%d两位数字表示月中的天数(01, 02, ...)%e数字形式表示月中的天数(1, 2, 3, 4, 5, ...)%H两位数字表示小时, 24小时制(01, 02, ...)%h 和 %I两位数字表示小时, 12小时制(01, 02, ...)%k数字形式的小时, 24小时制(1, 2, 3, ...)%l数字形式表示小时, 12小时制(1, 2, 3, 4, ...)%i两位数字表示分钟(00, 01, 02, ...)%S 和 %s两位数字表示秒(00, 01, 02, ...)%W一周中的星期名称(Sunday, Monday, ...)%a一周中的星期缩写(Sun., Mon., Tues., ...)%w以数字表示周中的天数(0Sunday, 1Monday, ...)%j以3位数字表示年中的天数(001, 002, ...)%U以数字表示年中的第几周, (1, 2, 3, ...), 其中Sunday为周中第一天%u以数字表示年中的第几周, (1, 2, 3, ...), 其中Monday为周中第一天%T24小时制的时间格式(%H:%i:%s)%r12小时制的时间格式(%I:%i:%s %p)%pAM或PM%%表示%字符本身
GET_FORMAT函数中date_type和format_type参数取值如下:日期类型格式化类型返回的格式化字符串DATEUSA%m.%d.%YDATEJIS%Y-%m-%dDATEISO%Y-%m-%dDATEEUR%d.%m.%YDATEINTERNAL%Y%m%dTIMEUSA%h:%i:%s %pTIMEJIS%H:%i:%sTIMEISO%H:%i:%sTIMEEUR%H.%i.%sTIMEINTERNAL%H%i%sDATETIMEUSA%Y-%m-%d %h:%i:%s %pDATETIMEJIS%Y-%m-%d%H:%i:%sDATETIMEISO%Y-%m-%dT%H:%i:%sDATETIMEEUR%d.%m.%Y %H.%i.%sDATETIMEINTERNAL%Y%m%d%H%i%s
-- 显示时分秒:
mysql SELECT DATE_FORMAT(NOW(), %H:%i:%s) FROM DUAL;
--------------------------------
| DATE_FORMAT(NOW(), %H:%i:%s) |
--------------------------------
| 17:51:20 |
--------------------------------
1 row in set (0.01 sec)-- 显示年月日:
mysql SELECT STR_TO_DATE(09/01/2009,%m/%d/%Y) FROM DUAL;
--------------------------------------
| STR_TO_DATE(09/01/2009,%m/%d/%Y) |
--------------------------------------
| 2009-09-01 |
--------------------------------------
1 row in set (0.00 sec)-- 转为时间格式:
mysql SELECT STR_TO_DATE(20140422154706,%Y%m%d%H%i%s) FROM DUAL;
----------------------------------------------
| STR_TO_DATE(20140422154706,%Y%m%d%H%i%s) |
----------------------------------------------
| 2014-04-22 15:47:06 |
----------------------------------------------
1 row in set (0.00 sec)-- 格式符之间可以设置连接符:
mysql SELECT STR_TO_DATE(2014-04-22 15:47:06,%Y-%m-%d %H:%i:%s) FROM DUAL;
--------------------------------------------------------
| STR_TO_DATE(2014-04-22 15:47:06,%Y-%m-%d %H:%i:%s) |
--------------------------------------------------------
| 2014-04-22 15:47:06 |
--------------------------------------------------------
1 row in set (0.00 sec)-- 返回格式化字符串:
mysql SELECT GET_FORMAT(DATE, USA);
-------------------------
| GET_FORMAT(DATE, USA) |
-------------------------
| %m.%d.%Y |
-------------------------
1 row in set (0.00 sec)-- 按照%m.%d.%Y 格式显示时间:
mysql SELECT DATE_FORMAT(NOW(), GET_FORMAT(DATE,USA)) FROM DUAL;
--------------------------------------------
| DATE_FORMAT(NOW(), GET_FORMAT(DATE,USA)) |
--------------------------------------------
| 11.13.2023 |
--------------------------------------------
1 row in set (0.00 sec)-- 指定显示年月日:
mysql SELECT STR_TO_DATE(2020-01-01 00:00:00,%Y-%m-%d) FROM DUAL;
-----------------------------------------------
| STR_TO_DATE(2020-01-01 00:00:00,%Y-%m-%d) |
-----------------------------------------------
| 2020-01-01 |
-----------------------------------------------
1 row in set, 1 warning (0.01 sec)5. 流程控制函数
流程处理函数可以根据不同的条件, 执行不同的处理流程, 可以在SQL语句中实现不同的条件选择.
MySQL中的流程处理函数主要包括IF(), IFNULL()和CASE()函数.函数用法示例IF(condition, value1, value2);condition为评估条件, 值为TRUE, 返回value1, 否则返回value2.IF(5 3, True, False) 返回 TrueIFNULL(value1, value2);如果value1不为NULL, 返回value1, 否则返回value2.IFNULL(NULL, Fallback) 返回 FallbackCASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 ... [ELSE resultn] END;相当于C语言的if...else if...else...CASE WHEN score 90 THEN A WHEN score 80 THEN B ELSE C ENDCASE expr WHEN 常量值1 THEN 值1 WHEN 常量值2 THEN 值2 ... [ELSE 值n] END;相当于C语言的switch...case...CASE grade WHEN A THEN 优秀 WHEN B THEN 良好 ELSE 一般 END
-- IF判断:
mysql SELECT IF(1 0,正确, 错误) FROM DUAL;
--------------------------
| IF(1 0,正确, 错误) |
--------------------------
| 正确 |
--------------------------
1 row in set (0.00 sec)-- IFNULL语句:
mysql SELECT IFNULL(null, Hello World) FROM DUAL;
-----------------------------
| IFNULL(null, Hello World) |
-----------------------------
| Hello World |
-----------------------------
1 row in set (0.00 sec)-- 设置一个别名, 否则字段明就太长了(字段名就是查询条件).
-- 从上往下遇到符合条件表达式执行子句并结束CASE语句.
SELECTCASE WHEN 1 0 THEN 1 0 WHEN 2 0 THEN 2 0 ELSE 3 0 END AS result;
--------
| result |
--------
| 1 0 |
--------
1 row in set (0.00 sec)-- 设置常量值, 依据常量值选择:
SELECT CASE 1 WHEN 1 THEN 我是1 WHEN 2 THEN 我是2 ELSE 你是谁 END AS result;
--------
| result |
--------
| 我是1 |
--------
1 row in set (0.00 sec)SELECT CASEWHEN 1 0 THEN yes WHEN 1 0THEN no ELSE unknown END AS 结果;
------
| 结果 |
------
| yes |
------
1 row in set (0.00 sec)SELECTCASEWHEN 1 0THEN yesWHEN 1 0 THEN no ELSE unknownEND AS 结果;
---------
| 结果 |
---------
| unknown |
---------
1 row in set (0.00 sec)SELECTCASE 1WHEN 0 THEN 0WHEN 1 THEN 1ELSE -1 END AS 结果;
------
| 结果 |
------
| 1 |
------
1 row in set (0.01 sec)SELECTCASE -1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END AS 结果;
------
| 结果 |
------
| -1 |
------
1 row in set (0.00 sec)-- 安装薪资打标签(结尾字段需要带,):
SELECT employee_id, salary,CASE WHEN salary15000THEN 高薪WHEN salary10000THEN 潜力股 WHEN salary8000THEN 屌丝ELSE 草根END AS 描述
FROM employees;
-------------------------------
| employee_id | salary | 描述 |
-------------------------------
| 100 | 24000.00 | 高薪 |
| 101 | 17000.00 | 高薪 |
| 102 | 17000.00 | 高薪 |
| 103 | 9000.00 | 屌丝 |
| 104 | 6000.00 | 草根 |
| ... | ... | ... |
| 203 | 6500.00 | 草根 |
| 204 | 10000.00 | 潜力股 |
| 205 | 12000.00 | 潜力股 |
| 206 | 8300.00 | 屌丝 |
-------------------------------
107 rows in set (0.00 sec)-- status字段作为判断的依据.
SELECT oid, status,CASE status WHEN 1THEN 未付款WHEN 2 THEN 已付款WHEN 3 THEN 已发货 WHEN 4 THEN 确认收货 ELSE 无效订单END AS 订单状态
FROM t_order;-- 计算年薪带佣金提成, 其中有些人有提成有些人没有提成, 可以使用IFNULL语句区分:
mysql SELECT employee_id, 12 * salary * (1 IFNULL(commission_pct, 0)) AS 年薪 FROM employees;
------------------------
| employee_id | 年薪 |
------------------------
| 100 | 288000.00 |
| 101 | 204000.00 |
| 102 | 204000.00 |
| 103 | 108000.00 |
| ... | ... |
| 202 | 72000.00 |
| 203 | 78000.00 |
| 204 | 120000.00 |
| 205 | 144000.00 |
| 206 | 99600.00 |
------------------------
107 rows in set (0.00 sec)-- 为IT_PROG, ST_CLERK, SA_REP三个部门的员工加薪:
SELECT last_name, job_id, salary,CASE job_id WHEN IT_PROG -- 信息技术程序员THEN 1.10 * salaryWHEN ST_CLERK -- 库房职员THEN 1.15 * salaryWHEN SA_REP -- 销售代表THEN 1.20 * salaryELSE salary END AS REVISED_SALARY
FROM employees;
---------------------------------------------------
| last_name | job_id | salary | REVISED_SALARY |
---------------------------------------------------
| King | AD_PRES | 24000.00 | 24000.00 |
| Kochhar | AD_VP | 17000.00 | 17000.00 |
| De Haan | AD_VP | 17000.00 | 17000.00 |
| Hunold | IT_PROG | 9000.00 | 9900.00 |
| Ernst | IT_PROG | 6000.00 | 6600.00 |
| Austin | IT_PROG | 4800.00 | 5280.00 |
| ... | ... | ... | ... | -- 省略
| Whalen | AD_ASST | 4400.00 | 4400.00 |
| Hartstein | MK_MAN | 13000.00 | 13000.00 |
| Fay | MK_REP | 6000.00 | 6000.00 |
| Mavris | HR_REP | 6500.00 | 6500.00 |
| Baer | PR_REP | 10000.00 | 10000.00 |
| Higgins | AC_MGR | 12000.00 | 12000.00 |
| Gietz | AC_ACCOUNT | 8300.00 | 8300.00 |
---------------------------------------------------
107 rows in set (0.00 sec)-- 练习: 查询部门号为10, 20, 30的员工信息,
-- 若部门号为10, 则打印其工资的1.1倍, 20号部门, 则打印其工资的1.2倍, 30号部门打印其工资的1.3倍数.SELECT department_id, first_name, salary,CASE department_idWHEN 10THEN salary * 1.1WHEN 20THEN salary * 1.2WHEN 30THEN salary * 1.3ELSE salaryEND AS REVISED_SALARY
FROM employees
WHERE department_id IN (10, 20, 30);
-----------------------------------------------------
| department_id | first_name | salary | REVISED_SALARY |
-----------------------------------------------------
| 10 | Jennifer | 4400.00 | 4840.00 |
| 20 | Michael | 13000.00 | 15600.00 |
| 20 | Pat | 6000.00 | 7200.00 |
| 30 | Den | 11000.00 | 14300.00 |
| 30 | Alexander | 3100.00 | 4030.00 |
| 30 | Shelli | 2900.00 | 3770.00 |
| 30 | Sigal | 2800.00 | 3640.00 |
| 30 | Guy | 2600.00 | 3380.00 |
| 30 | Karen | 2500.00 | 3250.00 |
-----------------------------------------------------
9 rows in set (0.00 sec)6. 加密与解密函数
加密与解密函数主要用于对数据库中的数据进行加密和解密处理, 以防止数据被他人窃取.
这些函数在保证数据库安全时非常有用.函数名参数返回值描述PASSWORD(str)str: 要加密的字符串加密后的字符串, 41位长返回字符串str的加密版本, 常用于用户密码加密. 加密结果不可逆. 在MySQL 8.0中, PASSWORD函数已被弃用.MD5(str)str: 要加密的字符串加密后的字符串返回字符串str的MD5加密后的值. 若参数为NULL, 返回NULL. MD5严格来说并不能算是加密.SHA(str)str: 要加密的字符串加密后的字符串从原明文密码str计算并返回加密后的字符串. 当参数为NULL时, 返回NULL. SHA加密算法比MD5更安全.AES_ENCRYPT(value, password_seed)value: 要加密的值password_seed: 加密密码加密后的字符串返回使用password_seed作为加密密码加密value后的结果.AES_DECRYPT(value, password_seed)value: 要解密的值password_seed: 加密密码解密后的字符串返回使用password_seed作为加密密码解密value的十六进制结果.
-- MD5:
mysql SELECT md5(123) FROM DUAL;;
----------------------------------
| md5(123) |
----------------------------------
| 202cb962ac59075b964b07152d234b70 |
----------------------------------
1 row in set (0.01 sec)-- sha加密:
mysql SELECT SHA(123) FROM DUAL;;
------------------------------------------
| SHA(123) |
------------------------------------------
| 40bd001563085fc35165329ea1ff5c5ecbdbbeef |
------------------------------------------
1 row in set (0.01 sec)-- 加密:
mysql SELECT AES_ENCRYPT(mysql, sql) AS Value;
------------------------------------
| Value |
------------------------------------
| 0x526D32028DF8A1721BE85D33F1FABD8C |
------------------------------------
1 row in set (0.00 sec)-- 解密(要确保加密后的值被正确保存到数据库后, 才能进行解密):
mysql SELECT AES_DECRYPT(0x526D32028DF8A1721BE85D33F1FABD8C, sql) AS password;
--------------------
| password |
--------------------
| NULL |
--------------------
1 row in set (0.00 sec)-- 创建表格:
CREATE TABLE secure_data ( id INT PRIMARY KEY AUTO_INCREMENT, encrypted_data VARBINARY(255)
); -- 写入数据
INSERT INTO secure_data (encrypted_data)
VALUES (AES_ENCRYPT(mysql, sql));-- 查看数据:
mysql SELECT * FROM secure_data;
-----------------------------------------
| id | encrypted_data |
-----------------------------------------
| 1 | 0x526D32028DF8A1721BE85D33F1FABD8C |
-----------------------------------------
1 row in set (0.00 sec)-- 解密得到其字符串的16进制:
mysql SELECT AES_DECRYPT(encrypted_data, sql) AS decrypted_data FROM secure_data WHERE id 1;
--------------------------------
| decrypted_data |
--------------------------------
| 0x6D7973716C |
--------------------------------
1 row in set (0.00 sec)-- 手动去除0x前缀:
mysql SELECT CAST(UNHEX(0x6D7973716C) AS CHAR);
-----------------------------------
| CAST(UNHEX(6D7973716C) AS CHAR) |
-----------------------------------
| mysql |
-----------------------------------
1 row in set (0.00 sec)-- 舍弃前缀:
mysql SELECT CAST(UNHEX(SUBSTRING(0x6D7973716C, 3)) AS CHAR);
---------------------------------------------------
| CAST(UNHEX(SUBSTRING(0x6D7973716C, 3)) AS CHAR) |
---------------------------------------------------
| mysql |
---------------------------------------------------
1 row in set (0.00 sec)7. 信息函数
MySQL中内置了一些可以查询MySQL信息的函数, 这些函数主要用于帮助数据库开发或运维人员更好地对数据库进行维护工作.函数用法描述VERSION()SELECT VERSION();返回当前MySQL的版本号.CONNECTION_ID()SELECT CONNECTION_ID();返回当前MySQL服务器的连接数.DATABASE()SELECT DATABASE();返回当前MySQL命令行所在的数据库名.SCHEMA()SELECT SCHEMA();与DATABASE()函数功能相同, 返回当前数据库名.USER()SELECT USER();返回当前连接MySQL的用户名, 格式为用户名主机名.CURRENT_USER()SELECT CURRENT_USER();返回当前连接MySQL的用户名和主机名, 与USER()函数相同.SYSTEM_USER()SELECT SYSTEM_USER();返回当前连接MySQL的用户名, 与USER()函数相同.SESSION_USER()SELECT SESSION_USER();返回当前会话连接MySQL的用户名, 与USER()函数相同.CHARSET(value)SELECT CHARSET(value);返回字符串value的字符集.COLLATION(value)SELECT COLLATION(value);返回字符串value的比较规则.
-- 当前MySQL命令行所在的数据库名:
mysql SELECT DATABASE() FROM DUAL;
------------
| DATABASE() |
------------
| atguigudb |
------------
1 row in set (0.00 sec)-- 当前连接MySQL的用户名:
mysql SELECT USER(), CURRENT_USER(), SYSTEM_USER(), SESSION_USER() FROM DUAL;
----------------------------------------------------------------
| USER() | CURRENT_USER() | SYSTEM_USER() | SESSION_USER() |
----------------------------------------------------------------
| rootlocalhost | rootlocalhost | rootlocalhost | rootlocalhost |
----------------------------------------------------------------
1 row in set (0.01 sec)-- 字符串使用的字符集:
mysql SELECT CHARSET(ABC);
----------------
| CHARSET(ABC) |
----------------
| gbk |
----------------
1 row in set (0.00 sec)-- 字符串比较规则:
mysql SELECT COLLATION(ABC);
------------------
| COLLATION(ABC) |
------------------
| gbk_chinese_ci |
------------------
1 row in set (0.00 sec)8. 其他函数
MySQL中有些函数无法对其进行具体的分类, 但是这些函数在MySQL的开发和运维过程中也是不容忽视的.函数用法描述FORMAT(value, n)返回格式化后的数字将数字value进行格式化, 保留小数点后n位, 并四舍五入.CONV(value, from, to)返回进制转换后的值将value从from进制转换为to进制.INET_ATON(ipvalue)返回IP地址的数字表示将以点分隔的IP地址ipvalue转化为一个数字.INET_NTOA(value)返回数字形式的IP地址将数字形式的IP地址value转化为以点分隔的IP地址.BENCHMARK(n, expr)返回执行表达式的结果将表达式expr重复执行n次, 用于测试MySQL处理expr表达式所耗费的时间.CONVERT(value USING char_code)返回字符编码转换后的值将value所使用的字符编码修改为char_code.
# 如果n的值小于或者等于0, 则只保留整数部分:
mysql SELECT FORMAT(123.123, 2), FORMAT(123.523, 0), FORMAT(123.123, -2);
-------------------------------------------------------------
| FORMAT(123.123, 2) | FORMAT(123.523, 0) | FORMAT(123.123, -2) |
-------------------------------------------------------------
| 123.12 | 124 | 123 |
-------------------------------------------------------------
1 row in set (0.00 sec)-- 进制转换:
mysql SELECT CONV(16, 10, 2), CONV(8888, 10, 16), CONV(NULL, 10, 2);
--------------------------------------------------------
| CONV(16, 10, 2) | CONV(8888, 10, 16) | CONV(NULL, 10, 2) |
--------------------------------------------------------
| 10000 | 22B8 | NULL |
--------------------------------------------------------
1 row in set (0.00 sec)-- 计算方式为: 192乘以256的3次方, 加上168乘以256的2次方, 加上1乘以256, 再加上100.
mysql SELECT INET_ATON(192.168.1.100);
----------------------------
| INET_ATON(192.168.1.100) |
----------------------------
| 3232235876 |
----------------------------
1 row in set (0.01 sec)-- 数字转ip地址:
mysql SELECT INET_NTOA(3232235876);
-----------------------
| INET_NTOA(3232235876) |
-----------------------
| 192.168.1.100 |
-----------------------
1 row in set (0.01 sec)-- 检测程序运行时间:
mysql SELECT BENCHMARK(1, MD5(mysql));
----------------------------
| BENCHMARK(1, MD5(mysql)) |
----------------------------
| 0 |
----------------------------
1 row in set (0.01 sec)-- 检测程序运行时间(100w次):
mysql SELECT BENCHMARK(1000000, MD5(mysql));
----------------------------------
| BENCHMARK(1000000, MD5(mysql)) |
----------------------------------
| 0 |
----------------------------------
1 row in set (2.82 sec)-- CONVERT函数使用USING关键字来指定目标字符集为utf8. 它将字符串mysql转换为utf8字符集;
-- 然后CHARSET函数查询转换后的字符集。
mysql SELECT CHARSET(mysql), CHARSET(CONVERT(mysql USING utf8));
----------------------------------------------------------
| CHARSET(mysql) | CHARSET(CONVERT(mysql USING utf8)) |
----------------------------------------------------------
| gbk | utf8mb3 |
----------------------------------------------------------
1 row in set, 1 warning (0.00 sec)
