mysql加强
学习目标
- 字符串处理函数
- 日期处理函数
- 综合练习
第1章 字符串的函数
上课讲常用
Name | 描述 |
---|---|
ASCII() | 返回最左侧字符的数值 SELECT ASCII(‘a’) |
BIN() | 返回包含数字的二进制表示的字符串 SELECT BIN(‘11’) |
BIT_LENGTH() | 以位为单位返回参数长度 SELECT BIT_LENGTH(‘11’) |
CHAR() | 返回传递的每个整数的字符 SELECT CHAR(‘123’) |
CHAR_LENGTH() | 返回参数中的字符数 SELECT CHAR_LENGTH(1234) |
CHARACTER_LENGTH() | CHAR_LENGTH()的同义词 |
CONCAT() | 返回连接字符串 SELECT CONCAT(“a” , “b” , “c”); |
CONCAT_WS() | 返回与分隔符连接 SELECT CONCAT_WS(’-’,‘a’,‘c’) |
ELT() | 返回索引号处的字符串 SELECT ELT(2,‘a’,‘a-c’,‘abcd’) |
FIELD() | 返回后续参数中第一个参数的索引(位置) SELECT FIELD(‘ej’, ‘Hej’, ‘ej’, ‘Heja’, ‘hej’, ‘foo’); |
FIND_IN_SET() | 返回第二个参数中第一个参数的索引位置 SELECT FIND_IN_SET(‘b’,‘a,b,c,d’); |
FORMAT() | 返回格式化为指定小数位数的数字 SELECT FORMAT(“123.123”,2) |
INSERT() | 在指定位置插入一个子字符串,直到指定的字符数 SELECT INSERT(‘Quadratic’, 3, 1, ‘What’); |
INSTR() | 返回第一次出现的子串的位置 SELECT INSTR(‘foobarbar’, ‘bar’); |
LCASE() | LOWER()的同义词 SELECT LCASE(‘FOOT’); |
LENGTH() | 以字节为单位返回字符串的长度 SELECT LENGTH(‘ab’) |
LIKE | 简单的模式匹配 |
LOAD_FILE() | 加载指定的文件 |
LOCATE() | 返回第一次出现的子串的位置 SELECT LOCATE(‘a’ , ‘xxxxabbbbaa’) |
LOWER() | 以小写形式返回参数 |
LPAD() | 返回字符串参数,使用指定的字符串进行左填充 SELECT LPAD(‘hi’,4,’?!’); |
LTRIM() | 删除前导空格 SELECT LTRIM(’ barbar’); |
MAKE_SET() | 返回一组以逗号分隔的字符串,这些字符串具有相应的位设置位 SELECT MAKE_SET(2,‘a’,‘b’,‘c’); |
MATCH | 执行全文搜索 |
MID() | 返回从指定位置开始的子字符串 MID(str,pos,len) 是 SUBSTRING(str,pos,len) SELECT MID(‘abcdef’ , 1 , 3 ) |
NOT LIKE | 简单模式匹配的否定 |
OCT() | 返回包含数字的八进制表示的字符串 SELECT OCT(123) |
OCTET_LENGTH() | LENGTH()的同义词 SELECT OCTET_LENGTH(‘abc’) |
ORD() | 返回参数最左侧字符的字符代码 SELECT ORD(‘abc’); |
QUOTE() | 转义参数以在SQL语句中使用 SELECT QUOTE(‘Don’t!’) |
REGEXP | 使用正则表达式匹配模式 SELECT ‘abc’ REGEXP ‘a’ |
REPEAT() | 重复指定次数的字符串 SELECT REPEAT(‘MySQL’, 3); |
REPLACE() | 替换指定字符串的出现次数 SELECT REPLACE(‘www.mysql.com’, ‘w’, ‘Ww’); |
REVERSE() | 反转字符串中的字符 SELECT REVERSE(‘abc’) |
RIGHT() | 返回指定的最右边的字符数 SELECT RIGHT(‘foobarbar’, 4); |
RLIKE | REGEXP的同义词 |
RPAD() | 追加指定次数的字符串 SELECT RPAD(‘hi’,5,’?!’); |
RTRIM() | 删除尾随空格 SELECT RTRIM('barbar '); |
SPACE() | 返回指定数量的空格的字符串 SELECT SPACE(6); |
STRCMP() | 比较两个字符串 SELECT STRCMP(‘text2’, ‘text’); 若所有的字符串均相同,则返回STRCMP(),若根据当前分类次序,第一个参数小于第二个,则返回 -1,其它情况返回 1 。 |
SUBSTR() | 返回指定的子字符串 SELECT SUBSTR(‘abcd’,2); |
SUBSTRING() | 返回指定的子字符串 SELECT SUBSTRING(‘abcd’,2,2); |
SUBSTRING_INDEX() | 在指定的分隔符出现次数之前从字符串返回子字符串 SELECT SUBSTRING_INDEX(‘www.mysql.com’, ‘.’, 2); |
TRIM() | 删除前导和尾随空格 SELECT TRIM(’ abc ') |
UCASE() | UPPER()的同义词 SELECT UCASE(‘abc’) |
UPPER() | 转换为大写 SELECT UPPER(‘abc’) |
convert() | 转换字符串 SELECT CONVERT(NOW(),CHAR) FROM DUAL; SELECT CAST(NOW() AS CHAR) FROM DUAL; |
常用函数:
- 合并字符串函数:concat(str1,str2,str3…) concat_ws(‘分隔符’ , str1 , str2 ,str3)
- 获取字符串字节数函数:length(str)
- 获取字符串字符数函数:char_length(str)
- 字母大小写转换函数:大写:upper(x),ucase(x);小写lower(x),lcase(x)
- 字符串查找函数 find_in_set( findStr , str)
- 获取指定位置的子串 locate( findStr, str)
- 字符串去空格函数 trim( str ) ltrim( str ) rtrim( str )
- 字符串替换函数 : replace(未被替换前的字符 , 指定需要被替换的字符 , 如果匹配成功替换的字符 );
- 字符串截取函数 : substr(完整字符 , 截取的长度) substring(完整字符 , 从哪开始截取, 截取的长度 )
第2章 日期函数
名称 | 描述 |
---|---|
ADDDATE() | 将时间值(间隔)添加到日期值 SELECT ADDDATE(NOW() ,2 ) |
ADDTIME() | 添加时间 SELECT ADDTIME(NOW() ,60*2) |
CURDATE() | 返回当前日期不携带时分秒 SELECT CURDATE() |
CURRENT_DATE() , CURRENT_DATE | CURDATE()的同义词 |
CURRENT_TIME() , CURRENT_TIME | CURTIME()的同义词 |
CURRENT_TIMESTAMP() , CURRENT_TIMESTAMP | 同义词NOW() |
CURTIME() | 返回当前时间 只返回时间 SELECT CURTIME() |
DATE() | 提取日期或日期时间表达式的日期部分 SELECT DATE( NOW() ) |
DATE_ADD() | 将时间值(间隔)添加到日期值 select DATE_ADD(CURDATE(), INTERVAL 24 HOUR) |
DATE_FORMAT() | 格式化日期指定 SELECT DATE_FORMAT(NOW() , “%Y”) 表达式查阅课外文档 |
DATE_SUB() | 从日期中减去时间值(间隔) select DATE_SUB(CURDATE(), INTERVAL 2 HOUR) |
DATEDIFF() | 减去两个日期 SELECT DATEDIFF(“2019-08-01”,CURDATE()) |
DAY() | DAYOFMONTH()的同义词 SELECT DAY( NOW() ) 时间减去两天 |
DAYNAME() | 返回工作日的名称 SELECT DAYNAME(NOW()) |
DAYOFMONTH() | SELECT DAYOFMONTH(NOW()) 时间减去两天 |
DAYOFWEEK() | 返回参数的工作日索引 SELECT DAYOFWEEK(NOW()) |
DAYOFYEAR() | 返回一年中的某一天(1-366) SELECT DAYOFYEAR(NOW()) |
EXTRACT() | 提取部分日期 SELECT EXTRACT(MONTH FROM ‘1999-07-02’) |
FROM_DAYS() | 给定一个天数 N, 返回一个DATE值。 SELECT FROM_DAYS(‘729669’) |
HOUR() | 提取小时 SELECT HOUR(NOW()) |
LAST_DAY | 返回参数的月份的最后一天 SELECT LAST_DAY(NOW()) |
LOCALTIME() , LOCALTIME | NOW()的同义词 |
LOCALTIMESTAMP , LOCALTIMESTAMP() | NOW()的同义词 |
MINUTE() | 从论证中返回分钟 SELECT MINUTE(NOW()) |
MONTH() | 从过去的日期返回月份 SELECT MONTH(NOW()) |
MONTHNAME() | 返回月份名称 SELECT MONTHNAME(NOW()) |
NOW() | 返回当前日期和时间 |
QUARTER() | 从日期参数返回季度 SELECT QUARTER(NOW()) |
SEC_TO_TIME() | 将秒转换为’hh:mm:ss’格式 SELECT SEC_TO_TIME(111) |
SECOND() | 返回秒(0-59) SELECT SECOND(NOW()) |
STR_TO_DATE() | 将字符串转换为日期 SELECT STR_TO_DATE(‘04/31/2004’, ‘%m/%d/%Y’); |
SUBDATE() | 使用三个参数调用时DATE_SUB()的同义词 SELECT SUBDATE(NOW(),INTERVAL 22 HOUR) |
TIME() | 提取传递的表达式的时间部分 SELECT TIME(NOW()) |
TO_DAYS() | 返回转换为days的日期参数 SELECT TO_DAYS(NOW()) |
TO_SECONDS() | 返回自0年以来转换为秒的日期或日期时间参数 |
WEEK() | 返回周数 SELECT WEEK(‘2018-01-08’) |
WEEKDAY() | 返回工作日索引 SELECT WEEKDAY(NOW()) |
WEEKOFYEAR() | 返回日期的日历周(1-53) SELECT WEEKOFYEAR(NOW()) |
YEAR() | 返回年份 SELECT YEAR(NOW()) |
YEARWEEK() | 返回年份和星期 SELECT YEARWEEK(NOW()) |
SYSDATE() | 当前时间 等效now() |
常用函数
- 当前日期函数: now()
- 返回参数的月份的最后一天 : last_day( date )
- 从日期参数返回季度 : quarter( date )
- 返回两个日期之间的天数 : datediff(date1 ,date2)
- 根据传入的日期格式返回需要的日期: date_format( date , 日期格式 ) 参考扩展文档
第3章 数字函数(了解)
- 绝对值函数:abs(x)
- 向上取整函数:ceil(x)
- 向下取整函数:floor(x)
- 取模函数:mod( x,y)
- 随机数函数:rand()
- 四舍五入函数:round(x,y)
- 数值截取函数:truncate(x,y) : 返回数字X,截断到D小数位。 如果D为0,结果没有小数点或小数部分。 D是负数,导致值X的小数点左边的D数字变为零。
第4章 特殊函数
5.1 流程判断函数
5.1.1 case when then
在查询代码的过程中,可能我们需要对查询的结果进行判断,例如java中的if判断,此时我们可以使用case when ten else的条件判断
语法:
SELECT
CASE [字段,值] WHEN 判断条件 THEN 希望的到的值
WHEN 判断条件 THEN 希望的到的值
ELSE 前面条件都没有满足情况下得到的值 END;
SELECT
CASE 11 WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
ELSE 'more' END;
SELECT
CASE WHEN 1>0 THEN 'true'
ELSE 'false' END;
5.1.2 if
如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL),则 IF()的返回值为expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值,具体情况视其所在语境而定。SELECT IF(1<2,'yes ',‘no’);
函数:
IF(条件 ,条件成立的值,条件不成立的值)
语法:
select IF(条件 , 条件成立的值 , 条件不成立的值)
SELECT IF(1>2,2,3);
5.1.3 ifnull
我们在数据库中难免碰到为null的字段,此时我们需要进行特定的转换而不是直接修改数据库的值,那么可以采用
ifnull函数进行转换。其主要目的就是将null值转换成我们自己想要的值
函数:
ifnull( 需要判断的值或字段 , 如果为null转换后的值 )
使用:
SELECT IFNULL(NULL , '这是将null转换后的值')
5.2 加密函数
5.2.1 password(str)
加密函数是MySQL中用来对数据进行加密的函数。因为数据库中有些很敏感的信息不希望被其他人看到,就应该通过加密方式来使这些数据变成看似乱码的数据。例如用户的密码,就应该经过加密。
PASSWORD(str)函数可以对字符串str进行加密。一般情况下,PASSWORD(str)函数主要是用来给用户的密码加密的。下面使用PASSWORD(str)函数为字符串“123456”加密。
语法 :
password(str)
select password('123456')
5.2.2 MD5(str)
MD5(str)函数可以对字符串str进行加密。MD5(str)函数主要对普通的数据进行加密。下面使用MD5(str)函数为字符串“123456”加密
语法:
MD5(str)
select MD5('123456')