加密解密,MySQL单行函数,数学函数字符串日期时间,流程控制,完整详细可收藏查询SQL

MySQL数据库 专栏收录该内容
23 篇文章 1 订阅


1、函数的理解

1.1 什么是函数
函数在计算机语言的使用中贯穿始终,函数的作用是什么呢?它可以把我们经常使用的代码封装起来,需要的时候直接调用即可。这样既 提高了代码效率 ,又 提高了可维护性 。在 SQL 中我们也可以使用函数对检索出来的数据进行函数操作。使用这些函数,可以极大地 提高用户对数据库的管理效率 。
在这里插入图片描述
从函数定义的角度出发,我们可以将函数分成 内置函数 和 自定义函数 。在 SQL 语言中,同样也包括了内置函数和自定义函数。内置函数是系统内置的通用函数,而自定义函数是我们根据自己的需要编写的。

1.2 不同DBMS函数的差异
我们在使用 SQL 语言的时候,不是直接和这门语言打交道,而是通过它使用不同的数据库软件,即DBMS。DBMS 之间的差异性很大,远大于同一个语言不同版本之间的差异。实际上,只有很少的函数是被 DBMS 同时支持的。比如,大多数 DBMS 使用(||)或者(+)来做拼接符,而在 MySQL 中的字符串拼接函数为concat()。大部分 DBMS 会有自己特定的函数,这就意味着采用 SQL 函数的代码可移植性是很差的,因此在使用函数的时候需要特别注意。

1.3 MySQL的内置函数及分类
MySQL提供了丰富的内置函数,这些函数使得数据的维护与管理更加方便,能够更好地提供数据的分析与统计功能,在一定程度上提高了开发人员进行数据分析与统计的效率。MySQL提供的内置函数从 实现的功能角度 可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取MySQL信息函数、聚合函数等。这些丰富的内置函数可分为两大类: 单行函数 、 聚合函数(分组函数)。
在这里插入图片描述
单行函数:操作数据对象,接受参数返回一个结果,只对一行进行变换,每行返回一个结果,可以嵌套,参数可以是一列或一个值。


2、数值函数

2.1 基本函数

函数用法
ABS(x)返回x的绝对值
SIGN(x)返回x的符号,整数返回1,负数返回-1,0返回0
PI()返回圆周率的值
CEIL(x),CEILING(x)返回大于或等于x的最小整数
FLOOR(x)返回小于或等于x的最大整数
LEAST(e1,e2,e3…)返回列表中最小值
GREATEST(e1,e2,e3…)返回列表中最大值
MOD(x,y)返回x除以y后的余数
RAND返回0~1的随机数
RAND(x)返回0~1的随机数,其中x用作种子值,相同的x会产生相同的随机数
ROUND(x)返回x四舍五入后的整数
ROUND(x,y)返回x四舍五入后的保留小数点后y位的数
TRUNCATE(x,y)返回x截断为y位小数的结果
SQRT(x)返回x的平方根,若x为负,则返回NULL

2.2 角度与弧度

函数用法
RADIANS(x)将角度转化为弧度,参数x为角度值
DEGREES(x)将弧度转化为角度,参数x为弧度值
SELECT RADIANS(180),DEGREES(PI())
FROM DUAL;

在这里插入图片描述

2.3 三角函数

函数用法
SIN(x)返回x的正弦值,参数x为弧度值
ASIN(x)返回x的反正弦值,若x不在-1到1之间,则返回NULL
COS(x)返回x的余弦值,参数x为弧度值
ACOS(x)返回x的反余弦值,若x不在-1到1之间,则返回NULL
TAN(x)返回x的正切值,参数x为弧度值
ATAN(x)返回x的反正切值,即返回正切值为x的值
ATAN2(m,x)返回两个参数的反正切值
COT(x)返回x的余切值,参数x为弧度值

2.4 指数与对数

函数用法
POW(x,y),POWER(x,y)返回x的y次方
EXP(x)返回e的x次方,其中e=2.718281828259045
LN(x),LOG(x)返回以e为底的x的对数,当x<=0时,返回NULL
LOGx(y)返回以x为底的y的对数,当x<=0时,返回NULL

2.5 进制转换

函数用法
BIN(x)返回x的二进制编码
HEX(x)返回x的十六进制编码
OCT(x)返回x的八进制编码
CONV(x,f1,f2)将以f1进制编码的x转换为以f2进制编码

3、字符串函数

注意:在MySQL中,字符串的位置是从1开始的,而不是0。

函数用法
ASCII(s)返回字符串s中第一个字符的ASCII码值
CHAR_LENGTH(s)返回字符串s的字符数
LENGTH(s)返回字符串s的字节数,和字符集有关
CONCAT(s1,s2,s3…)拼接s1s2s3…为一个字符串
CONCAT_WS(x,s1,s2,s3…)以x为间隔拼接s1s2s3…为一个字符串
INSERT(str,idx,len,replacestr)用replacstr替换str从第idx个位置起的len个字串
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进行左填充,直到长度为len个字符
RPAD(str,len,pad)用字符串pad对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个字符
LOCATE(substr,str)返回字符串substr在字符串str中首次出现的位置,未找到返回0
ELT(m,s1,s2,s3…)返回列表中第m个字符串,若m=n,则返回sn
FIELD(s,s1,s2,s3…)返回字符串s在列表中第一次出现的位置
FIND_IN_SET(s1,s2)返回字符串s1在字符串s2中出现的位置,其中s2是一个以逗号分隔的字符串
REVERSE(s)返回字符串反转后的字符串
NULLIF(value1,value2)若value1=value2,则返回NULL,否则返回value1

4. 日期和时间函数

4.1 获取日期时间

函数用法
CURDATE(),CURRENT_DATE()返回当前日期,只包含年月日
CURTIME(),CURRENT_TIME()返回当前时间,只包含时分秒
NOW(),SYSDATE(),CURRENT_TIMESTAMP(),LOCALTIME(),LOCALTIMESTAMP()返回当前系统的日期和时间
UTC_DATE()返回UTC(世界标准时间)日期
UTC_TIME()返回UTC(世界标准时间)时间

4.2 日期与时间戳

函数用法
UNIX_TIMESTAMP()以UNIX时间戳的形式返回当前时间
UNIX_TIMESTAMP(date)将时间date以UNIX时间戳的形式返回
FROM_UNIXTIME(timestamp)将UNIX时间戳的时间转换为普通格式的时间

4.3 月份星期天数等

函数用法
YEAR(date),MONTH(date),DAY(date)返回具体的日期值
HOUR(time),MINUTE(time),SECOND(time)返回具体的时间值
MONTHNAME(date)返回月份英文名
DAYNAME(date)返回星期英文名
WEEKDAY(date)返回date是周几,周一是0,周二是1…
QUARTER(date)返回date是第几季度,范围为1~4
WEEK(date),WEEKOFYEAR(date)返回date是一年中的第几周
DAYOFYEAR(date)返回date是一年中的第几天
DAYOFMONTH(date)返回date是该月的第几天
DAYOFWEEK(date)返回date是该周的第几天,周日是1,周一是2…

4.4 日期的操作

函数用法
EXTRACT(type FROM date)返回指定日期中特定的部分,type指定返回的值

在这里插入图片描述

4.5 时间和秒钟

函数用法
TIME_TO_SEC(time)将time转化为秒并返回,公式:小时*3600+分钟*60+秒
SEC_TO_TIME(seconds)将seconds转化为包含小时分钟秒的时间

4.6 计算日期和时间

第1组:

函数用法
DATE_ADD(date INTERVAL expr type),ADDDATE(date INTERVAL expr type)返回与date相差INTERVAL时间间隔的日期时间
DATE_SUB(date INTERVAL expr type),SUBDATE(date INTERVAL expr type)返回与date相差INTERVAL时间间隔的日期时间

在这里插入图片描述

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS col1,DATE_ADD('2021-10-21 23:32:12',INTERVAL
1 SECOND) AS col2,
ADDDATE('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col3,
DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS col4,
DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5, #可以是负数
DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) AS col6 #需要单引号
FROM DUAL;

第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_DATE(date)返回date距离0000年1月1日的天数
LAST_DAY(date)返回date所在月份最后一天的日期
MAKEDATE(year,n)针对给定年份year与天数返回一个日期
MAKETIME(hour,minute,second)将给定的时分秒组合成时间返回
PERIOD_ADD(time,n)返回time加上n后的时间

4.7 格式化与解析

函数用法
DATE_FORMAT(date,fmt)按照字符串fmt格式化日期date值
TIME_FORMAT(time,fmt)按照字符串fmt格式化时间time值
GET_FORMAT(date_type,format_type)返回日期字符串的显示格式
STR_TO_DATE(str,fmt)按照字符串fmt对str进行解析,解析为一个日期

上述 非GET_FORMAT 函数中fmt参数常用的格式符:
在这里插入图片描述
GET_FORMAT函数中date_type和format_type参数取值如下:
在这里插入图片描述


5、流程控制函数

流程处理函数可以根据不同的条件,执行不同的处理流程,可以在SQL语句中实现不同的条件选择。MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。

函数用法
IF(value,value1,value2)若value为TRUE,则返回value1,否则返回value2
IFNULL(value1,value2)若value1不为NULL,则返回value1,否则返回value2
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 … [ELSE result] END相当于Java中的if…else if…else
CASE expr WHEN 常量1 THEN 值1 WHEN 常量2 THEN 结果2 … [ELSE result] END相当于Java中的switch…case…
SELECT oid,`status`, CASE `status` WHEN 1 THEN '未付款'
WHEN 2 THEN '已付款'
WHEN 3 THEN '已发货'
WHEN 4 THEN '确认收货'
ELSE '无效订单' END
FROM t_order;

6、加密与解密函数

加密与解密函数主要用于对数据库中的数据进行加密和解密处理,以防止数据被他人窃取。这些函数在保证数据库安全时非常有用。

函数用法
PASSWORD(str)返回字符串str的加密版本,41位长,加密结果不可逆
MD5(str)返回字符串str的md5加密后的值
SHA(str)加密,比md5安全
ENCODE(value,password_seed)返回使用password_seed作为加密密码加密value
DECODE(value,password_seed)返回使用password_seed作为解密密码解密value

7、MySQL信息函数

MySQL中内置了一些可以查询MySQL信息的函数,这些函数主要用于帮助数据库开发或运维人员更好地对数据库进行维护工作。

函数用法
VERSION()返回当前MySQL的版本号
CONNECTION_ID()返回当前MySQL服务器的连接数
DATABASE(),SCHEMA()返回MySQL命令行当前所在的数据库
USER(),CURRENT_USER(),SYSTEM_USER(),SESSION_USER()返回当前连接MySQL的用户名,返回结果格式为“主机名@用户名”
CHARSET(value)返回字符串value的字符集
COLLATION(value)返回字符串value的比较规则

8、其他函数

MySQL中有些函数无法对其进行具体的分类,但是这些函数在MySQL的开发和运维过程中也是不容忽视的。

函数用法
FORMAT(value,n)返回对数字value进行格式化后的结果数据,n表示四舍五入后保留到小数点后n位
CONV(value,from,to)将value的值进行不同进制之间的转换
INET_ATON(ipvalue)将以点分隔的ip地址转化为一个数字
INET_NTOA(value)将数字形式的ip地址转化为以点分隔的ip地址
BENCHMARK(n,expr)将expr重复执行n次,用于测试处理所耗费的时间
CONVERT(value USING char_code)将value所使用的字符编码修改为char_code
  • 15
    点赞
  • 16
    评论
  • 6
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

Ø 常用查询 MySQL结束符是“;”结束。 1、 显示所有数据库 show databases; 2、 删除数据库 drop database dbName; 3、 创建数据库 create database [if not exists] dbName; 中括号部分选的,判断该数据不存在就创建 4、 切换、使用指定数据库 use dbName; 5、 显示当前使用数据库所有的表对象 show tables; 6、 显示表结构describe(desc) desc tableName; 7、 创建一张表 create table user ( --int 整型 uId int, --小数 uPrice decimal, --普通长度文本,default设置默认值 uName varchar(255) default ‘zhangsan’, --超长文本 uRemark text, --图片 uPhoto blob, --日期 uBirthday datetime ); 8、 子查询建表方法 部分列名匹配模式: create table userInfo ( name varchar(20), sex char ) as select name, sex from user; 上面的列名和子查询的列名以及类型要对应 全部列名模式: create table userInfo as select * from user; 直接将整个表的类型和数据备份到新表userInfo中 9、 添加表字段 添加单列 alter table user add tel varchar(11) default ‘02012345678’; 添加多列 alter table user add ( photo blob, birthday date ); 上面就同时增加了多列字段 10、 修改表字段 修改tel列 alter table user modify tel varchar(15) default ‘02087654321’; 修改tel列的位置,在第一列显示 alter table user modify tel varchar(15) default '02087654321' first; 修改tel列的位置,在指定列之后显示 alter table user modify tel varchar(15) default '02087654321' after age; 注意:alter modify不支持一次修改多个列,但是Oracle支持多列修改 但是MySQL以通过多个modify的方式完成: alter table user modify tel varchar(15) default '02087654321' first, modify name varchar(20) after tel; 11、 删除指定字段 alter table user drop photo; 12、 重命名表数据 表重命名 alter table user rename to users; 字段重命名 alter table users change name u_name varchar(10); alter table users change sex u_sex varchar(10) after u_name; 如果需要改变列名建议使用change,如果需要改变数据类型和显示位置以使用modify 13、 删除表 drop table users; drop删除表会删除表结构,表对象将不存在数据中;数据也不会存在;表内的对象也不存在,如:索引、视图、约束; truncate删除表 truncate都被当成DDL出来,truncate的作用就是删除该表里的全部数据,保留表结构。相当于DDL中的delete语句, 但是truncate比delete语句的速度要快得多。但是truncate不能带条件删除指定数据,只会删除所有的数据。如果删除的表有外键, 删除的速度类似于delete。但新版本的MySQL中truncate的速度比delete速度快。 Ø 约束 MySQL中约束保存在information_schema数据库的table_constraints中,以通过该表查询约束信息; 约束主要完成对数据的检验,保证数据库数据完整性;如果有相互依赖数据,保证该数据不被删除。 常用五类约束: not null:非空约束,指定某列不为空 unique: 唯一约束,指定某列和几列组合的数据不能重复 primary key:主键约束,指定某列的数据不能重复、唯一 foreign key:外键,指定该列记录
©️2021 CSDN 皮肤主题: 像素格子 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值