MySQL之常用技巧整理
- 一、SQL执行顺序
- 二、常用函数整理
- 2.1 LIKE函数-模糊匹配
- 2.2 字符串函数
- 2.2.1 CONCAT函数-字符串拼接
- 2.2.2 LOWER函数-将字符串转换为小写
- 2.2.3 UPPER函数-将字符串转换为大写
- 2.2.4 LENGTH函数-返回字符串的字节长度
- 2.2.5 CHAR_LENGTH函数-返回字符串字符长度
- 2.2.6 BIT_LENGTH函数-返回字符串的比特长度
- 2.2.7 SUBSTRING函数-返回字符串子串
- 2.2.8 TRIM函数-返回删除子串后的字符串
- 2.2.9 LPAD/RPAD函数-字符串填充
- 2.2.10 INSTR函数-返回子串在字符串中第一次出现的索引位置
- 2.2.11 LOCATE函数-返回子串在字符串中第一次出现的索引位置
- 2.2.12 FIELD函数-返回字符串在后续字符串中出现的位置
- 2.2.13 FIND_IN_SET函数-返回字符串在列表字符串中出现的位置
- 2.2.14 REPLACE函数-字符串替换
- 2.2.15 INSERT函数-字符串插入替换
- 2.2.16 REVERSE函数-字符串反转
- 2.3 窗口函数-窗口函数在Hive或者MySQL8.X才有
- 2.4 ALTER函数
- 2.5 CHANGE和MODIFY函数
- 2.6 case when then else end函数用法
- 2.7 MySQL时间函数
- 2.8 IFNULL()函数
- 2.9 对于字符型和时间型数据求最大值
- 三、正则表达式批量删除记录
- 四、查看MySQL版本
一、SQL执行顺序
(8)SELECT
(9)DISTINCT
(11)<Top Num> <select list>
(1)FROM [left_table]
(3)<join_type> JOIN <right_table>
(2) ON <join_condition>
(4)WHERE <where_condition>
(5)GROUP BY <group_by_list>
(6)WITH <CUBE | RollUP>
(7)HAVING <having_condition>
(10)ORDER BY <order_by_list>
(12)LIMIT <output number>
二、常用函数整理
2.1 LIKE函数-模糊匹配
like语句
%xxx%:查询username字段中包含xxx的记录。
SELECT
*
FROM
table_name
WHERE
username LIKE '%xxx%';
%xxx:查询username字段中以xxx结尾的记录。
SELECT
*
FROM
table_name
WHERE
username LIKE '%xxx';
xxx%:查询username字段中以xxx开头的记录。
SELECT
*
FROM
table_name
WHERE
username LIKE 'xxx%';
2.2 字符串函数
2.2.1 CONCAT函数-字符串拼接
- CONCAT(str1,str2,…)函数用于将多个字符串连接之后返回;
SELECT
CONCAT('MySQL', '字符串', '函数') AS str;
#结果
str |
--------------+
MySQL字符串函数|
#如果该函数中的任何参数为 NULL,返回结果为 NULL
SELECT
CONCAT('MySQL', NULL, '函数') AS str;
#结果
str|
---+
|
- 以上方式只能用于连接字符串常量,不能用于连接字段的值。
2.2.2 LOWER函数-将字符串转换为小写
- LOWER(str)和LCASE(str)函数用于将字符串转换为小写形式。
SELECT
LOWER('MySQL字符串函数') AS str1,
LCASE('MySQL字符串函数') AS str2;
#结果
str1 |str2 |
--------------+--------------+
mysql字符串函数|mysql字符串函数|
2.2.3 UPPER函数-将字符串转换为大写
- UPPER(str)和UCASE(str)函数用于将字符串转换为大写形式.
SELECT
UPPER('MySQL字符串函数') AS str1,
UCASE('MySQL字符串函数') AS str2;
#结果
str1 |str2 |
--------------+--------------+
MYSQL字符串函数|MYSQL字符串函数|
2.2.4 LENGTH函数-返回字符串的字节长度
- LENGTH(str)和OCTET_LENGTH(str)函数用于返回字符串的字节长度,一个汉字3个字节,一个英文字母2个字节。
SELECT
LENGTH('MySQL字符串函数') AS len1,
OCTET_LENGTH('MySQL字符串函数') AS len2;
#结果:一个汉字3个字节
len1|len2|
----+----+
20| 20|
2.2.5 CHAR_LENGTH函数-返回字符串字符长度
SELECT
CHAR_LENGTH('MySQL字符串函数') AS len1,
CHARACTER_LENGTH('MySQL字符串函数') AS len2;
#结果
len1|len2|
----+----+
10| 10|
2.2.6 BIT_LENGTH函数-返回字符串的比特长度
SELECT
BIT_LENGTH('MySQL字符串函数') AS len;
#结果-一个字节8个比特。
len|
---+
160|
2.2.7 SUBSTRING函数-返回字符串子串
- SUBSTRING(str,pos)、SUBSTRING(str FROM pos)、SUBSTRING(str,pos,len)以及SUBSTRING(str FROM pos FOR len)函数都可以用于返回从指定位置 pos 开始的子串,len 表示返回子串的长度;pos 为 0 表示返回空字符串。
SELECT
SUBSTRING('MySQL字符串函数', 6) AS str1,
SUBSTRING(
'MySQL字符串函数'
FROM
6
) AS str2,
SUBSTRING('MySQL字符串函数', 6, 3) AS str3,
SUBSTRING(
'MySQL字符串函数'
FROM
6 FOR 3
) AS str4,
SUBSTRING('MySQL字符串函数', 0) AS str5;
#结果
str1 | str2 |str3 |str4 |str5|
---------+---------+-------+------+----+
字符串函数|字符串函数|字符串 |字符串 | |
- 位置参数 pos 可以为负数,此时返回的子串从字符串右侧第 pos 个字符开始
SELECT
SUBSTRING('MySQL字符串函数', - 2) AS str1,
SUBSTRING(
'MySQL字符串函数',
- 5,
3
) AS str2;
#结果
str1 |str2 |
------+------+
函数 |字符串 |
- 另外,SUBSTR()和MID()函数都是 SUBSTRING() 函数的同义词,也支持以上 4 种形式。
- LEFT(str,len)函数返回字符串 str 左侧的 len 个字符,RIGHT(str,len)函数返回字符串 str 右侧的 len 个字符。
SELECT
LEFT ('MySQL字符串函数', 5) AS str1,
RIGHT ('MySQL字符串函数', 5) AS str2;
#结果
str1 |str2 |
-----+---------+
MySQL|字符串函数|
- SUBSTRING_INDEX(str,delim,count)函数返回第 count 个分隔符 delim 之前的子串。如果 count 为正数,从左侧开始计数并返回左侧的所有字符;如果 count 为负数,从右侧开始计数并返回右侧的所有字符。
SELECT
SUBSTRING_INDEX(
'张三;李四;王五',
';',
2
) AS str1,
SUBSTRING_INDEX(
'张三;李四;王五',
';',
- 2
) AS str2;
#结果
str1 |str2 |
--------+--------+
张三;李四|李四;王五|
2.2.8 TRIM函数-返回删除子串后的字符串
- TRIM([remstr FROM] str)函数用于返回删除字符串 str 两侧所有 remstr 字符串之后的子串,remstr 默认为空格。
SELECT
TRIM(' MySQL字符串函数 ') AS str1,
TRIM(
'-'
FROM
'--MySQL字符串函数--'
) AS str2;
#结果
str1 |str2 |
--------------+--------------+
MySQL字符串函数|MySQL字符串函数|
- TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)函数用于返回删除字符串 str 两侧/左侧/右侧所有 remstr 字符串之后的子串,默认删除两侧字符串(BOTH),remstr 默认为空格。
SELECT
TRIM(
LEADING ' '
FROM
' MySQL字符串函数 '
) AS str1,
TRIM(
TRAILING '-'
FROM
'--MySQL字符串函数--'
) AS str2;
#结果
str1 |str2 |
----------------+----------------+
MySQL字符串函数 |--MySQL字符串函数|
2.2.9 LPAD/RPAD函数-字符串填充
- LPAD(str,len,padstr)函数表示字符串 str 的左侧使用 padstr 进行填充,直到长度为 len;RPAD(str,len,padstr)函数表示在字符串 str 的右侧使用 padstr 进行填充,直到长度为 len。
SELECT
LPAD(123, 6, '0') AS str1,
LPAD(123, 2, '0') AS str2,
RPAD(123, 6, '0') AS str1,
RPAD(123, 2, '0') AS str1;
#结果
str1 |str2|str1 |str1|
------+----+------+----+
000123|12 |123000|12 |
- 当字符串 str 的长度大于 len 时,相当于从右侧截断字符串。
- REPEAT(str,count)函数用于将字符串 str 复制 count 次并返回结果。
SELECT
REPEAT
('A', 5) AS str;
#结果
str |
----------+
AAAAA|
2.2.10 INSTR函数-返回子串在字符串中第一次出现的索引位置
- INSTR(str,substr)函数用于返回子串 substr 在字符串 str 中第一次出现的索引位置,没有找到子串时返回 0。
SELECT
INSTR(
'MySQL字符串函数',
'字符串'
) AS index1,
INSTR(
'MySQL字符串函数',
'日期'
) AS index2,
INSTR('MySQL字符串函数', '') AS index3,
INSTR(
'MySQL字符串函数',
NULL
) AS index4;
#结果
index1|index2|index3|index4|
------+------+------+------+
6| 0| 1| |
2.2.11 LOCATE函数-返回子串在字符串中第一次出现的索引位置
- LOCATE(substr,str)函数也可以用于返回子串 substr 在字符串 str 中第一次出现的索引位置,和 INSTR(str,substr) 函数唯一的不同就是参数的顺序相反。
- LOCATE(substr,str,pos)函数返回子串 substr 在字符串 str 中从位置 pos 开始第一次出现的索引位置。
SELECT
LOCATE('S', 'MySQL Server', 5) AS ind;
#结果
ind|
---+
7|
2.2.12 FIELD函数-返回字符串在后续字符串中出现的位置
- FIELD(str,str1,str2,str3,…) 函数返回字符串 str 在后续字符串列表中出现的位置,没有找到时返回 0。
SELECT
FIELD(
'李四',
'张三',
'李四',
'王五'
) AS ind;
#结果
ind|
---+
2|
2.2.13 FIND_IN_SET函数-返回字符串在列表字符串中出现的位置
- FIND_IN_SET(str,strlist) 函数返回字符串 str 在列表字符串 strlist 中出现的位置,strlist 由 N 个子串使用逗号分隔组成。
SELECT
FIND_IN_SET(
'李四',
'张三,李四,王五'
) AS ind;
#结果
ind|
---+
2|
2.2.14 REPLACE函数-字符串替换
- REPLACE(str,from_str,to_str)函数用于将字符串 str 中所有的 from_str 替换为 to_str,返回替换后的字符串。
SELECT
REPLACE (
'MySQL字符串函数',
'字符串',
'日期'
) AS str1,
REPLACE (
'MySQL字符串函数',
'字符串',
''
) AS str2;
#结果
str1 |str2 |
------------+---------+
MySQL日期函数|MySQL函数|
2.2.15 INSERT函数-字符串插入替换
- INSERT(str,pos,len,newstr)函数用于在字符串 str 的指定位置 pos 之后插入子串 newstr,替换随后的 len 个字符。
SELECT
INSERT (
'MySQL字符串函数',
6,
3,
'日期'
) AS str;
#结果
str |
------------+
MySQL日期函数|
2.2.16 REVERSE函数-字符串反转
- REVERSE(str)函数用于将字符串 str 中的字符顺序进行反转。
SELECT
REVERSE(
'上海自来水来自海上'
) = '上海自来水来自海上' AS "回文";
#结果
回文|
----+
1|
2.3 窗口函数-窗口函数在Hive或者MySQL8.X才有
2.3.1 基本原理
- 窗口函数:也叫OLAP函数(Online Anallytical Processing,联机分析处理),可以对数据库数据进行实时分析处理。
- 基本语法:
<窗口函数> over (partition by <用于分组的字段名> -- partition子句可省略,不指定分组
order by <用于排序的列名>)
-
<窗口函数>位置可以放两种函数:
1)专用窗口函数:rank, dense_rank, row_number;
2)聚合函数:如sum, avg, cont, max, min。 -
因为窗口函数是对where或者group by子句处理后的结果进行操作,所以「窗口函数原则上只能写在select子句中」。
-
功能:
1)同时具备分组和排序的功能;
2)不改变原有表的行数;
3)窗口函数原则上只能写在select子句中。
2.3.2 示例1
2.3.2.1 创建数据表
CREATE TABLE
IF NOT EXISTS `products` (
`id` INT UNSIGNED AUTO_INCREMENT,
`name` VARCHAR (100) NOT NULL,
`price` BIGINT NOT NULL,
PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;
- 1)如果不想字段为 NULL 可以设置字段的属性为 NOT NULL, 在操作数据库时如果输入该字段的数据为NULL ,就会报错。
- 2)UNSIGNED属性就是将数字类型无符号化,INT的类型范围是-2 147 483 648 ~ 2 147 483 647, INT UNSIGNED的范围类型就是0 ~ 4 294 967 295。
- 3)AUTO_INCREMENT定义列为自增的属性,一般用于主键,数值会自动加1。
- 4)PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键,列间以逗号分隔。
- 5)ENGINE 设置存储引擎,CHARSET 设置编码。
2.3.2.2 批量插入数据
INSERT INTO products (NAME, price)
VALUES
('苹果', 50),
('橘子', 100),
('葡萄', 50),
('西瓜', 80),
('柠檬', 30),
('香蕉', 50);
SELECT
*
FROM
products;
id | name | price |
---|---|---|
1 | 苹果 | 50 |
2 | 橘子 | 100 |
3 | 葡萄 | 50 |
4 | 西瓜 | 80 |
5 | 柠檬 | 30 |
6 | 香蕉 | 50 |
2.3.2.3 使用窗口函数
SELECT
name,
price,
rank () over (ORDER BY price DESC) AS rank_1,
dense_rank ()(ORDER BY price DESC) AS rank_2,
row_number ()(ORDER BY price DESC) AS rank_3
FROM
products;
#结果
name price rank_1 rank_2 rank_3
橘子 100 1 1 1
西瓜 80 2 2 2
苹果 50 3 3 3
香蕉 50 3 3 4
葡萄 50 3 3 5
柠檬 30 6 4 6
- rank()在出现了相同位次之后,跳过了相同的位次;
- dense_rank()则没有跳过相同的位次;
- row_number()按照自然数的顺序进行排列;
- 在上述的这三个专用窗口函数中,函数后面的括号不需要任何参数,保持()空着就可以。
2.3.2 示例2
- 按照班级进行分组,在每个班级中按照成绩进行排名。
SELECT
*, rank () over (
PARTITION BY 班级 -- 先分组
ORDER BY
成绩 DESC
) AS ranking -- 再排序
FROM
班级表
2.3.3 对于MySQL中没有rank函数时,进行排名的情况
- 需要定义变量自己实现rank排名。
select
a.s_id -- 学号
,@i:=@i+1 as '不保留空缺排名' -- 直接i的自加,顺序一直变大,声明变量需加@
,@k:=(case when @avg_score=a.avg_s then @k else @i+1 end) as '保留空缺排名' -- 只有在前后二次排序值不同时才会使顺序号加1
,@avg_score:=avg_s as '平均分' -- 表a中的值
from (select
s_id
,round(avg(s_score), 2) as avg_s
from Score
group by s_id
order by 2 desc)a -- 表a:平均成绩的排序和学号
,(select @avg_score:=0, @i:=0, @k:=0)b -- 表b:进行变量初始化,固定写法。
2.4 ALTER函数
- 需要修改数据表名或者修改数据表字段时,就需要使用到ALTER命令,例子如2.5所示。
- 修改数据表字符集
ALTER TABLE Teacher CHARACTER
SET utf8;
- 修改表字段的字符集
ALTER TABLE Teacher CHANGE t_id t_id VARCHAR (20) CHARACTER
SET utf8;
2.5 CHANGE和MODIFY函数
- 修改表:修改表student的name字段的数据类型,将varchar(10)修改为varchar(20);
- 1)MODIFY函数
ALTER TABLE student MODIFY NAME VARCHAR (20);
2)CHANGE函数
ALTER TABLE student CHANGE NAME NAME VARCHAR (20);
- 修改列名
1)修改列名只能使用change修改:将列名name修改为username;
ALTER TABLE Student CHANGE name username VARCHAR (20);
DESC Student;-- 显示数据表字段的详情
2.6 case when then else end函数用法
- case when then else end 语句用于查询满足多种条件的情况,还有就是用于进行行转列的查询,这个是放在select 子句后面的,充当的是字段的作用。
- 具体用法分为两种,一种是简单的函数形式,另一种就是表达式的形式。
1)简单的函数形式:case 字段 when 值 then 结果 else 其他情况 end;
2)表达式的形式:case when 字段=值(这里写表达式,例如 score=80) then 结果 else 其他情况 end;
-- 简单的函数形式
select (case score when 'a' then '优秀' else '良好' end) as score from student;
-- 表达式形式
select (case when score between 85 and 90 then '优秀' else '良好' end) as score from student;
- THEN后边的值与ELSE后边的值类型应一致,否则会报错;
- 在case里面还可以嵌套子查询等复杂用法。
- (case when then end)就是select后面需要查询出来的一个字段,在使用时可以用小括号括起来可读性更高,对其可以使用聚合函数,别名,去重,等操作。
1)场景1: 有分数score,score<60返回不及格,score>=60返回及格,score>=80返回优秀
SELECT
STUDENT_NAME,
(CASE WHEN score < 60 THEN '不及格'
WHEN score >= 60 AND score < 80 THEN '及格'
WHEN score >= 80 THEN '优秀'
ELSE '异常' END) AS REMARK
FROM
TABLE
- 注意:如果你想判断score是否null的情况,WHEN score = null THEN ‘缺席考试’,这是一种错误的写法,正确的写法应为:
CASE WHEN score IS NULL THEN '缺席考试' ELSE '正常' END
2)场景2: 现老师要统计班中,有多少男同学,多少女同学,并统计男同学中有几人及格,女同学中有几人及格。
SELECT
SUM (CASE WHEN STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_COUNT,
SUM (CASE WHEN STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_COUNT,
SUM (CASE WHEN STU_SCORE >= 60 AND STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_PASS,
SUM (CASE WHEN STU_SCORE >= 60 AND STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_PASS
FROM
THTF_STUDENTS
3)场景3: 经典行转列,并配合聚合函数做统计
:现要统计各个城市,总共使用了多少水耗、电耗、热耗
SELECT
E_CODE,
SUM(CASE WHEN E_TYPE = 0 THEN E_VALUE ELSE 0 END) AS WATER_ENERGY,--水耗
SUM(CASE WHEN E_TYPE = 1 THEN E_VALUE ELSE 0 END) AS ELE_ENERGY,--电耗
SUM(CASE WHEN E_TYPE = 2 THEN E_VALUE ELSE 0 END) AS HEAT_ENERGY--热耗
FROM
THTF_ENERGY_TEST
GROUP BY
E_CODE
4)场景4: CASE WHEN中使用子查询:
根据城市用电量多少,计算用电成本。假设电能耗单价分为三档,根据不同的能耗值,使用相应价格计算成本。
CASE WHEN energy <= (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 0) THEN (SELECT P_PRICE FROM TABLE_PRICE WHERE P_LEVEL = 0)
WHEN energy > (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 0) AND energy <= (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 1) THEN (SELECT P_PRICE FROM TABLE_PRICE WHERE P_LEVEL = 1)
WHEN energy > (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 1) AND energy <= (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 2) THEN (SELECT P_PRICE FROM TABLE_PRICE WHERE P_LEVEL = 2)
2.7 MySQL时间函数
2.7.1 一些时间函数
- year():返回年份
- date_format(now(), ‘%Y%m%d’):返回%Y%m%d形式的年月日
- dayofyear() :一年中的第几天
- weekofyear()和yearweek():一年中的第几周
- week():一年中的第几周
- month():返回月份
- dayofweek():星期索引,(1=星期天,2=星期一, ……7=星期六),符合国内标准
- weekday():星期索引,(0=星期一,1=星期二, ……6= 星期天),国外标准
2.7.2 获得当前日期时间 函数
2.7.3 日期转换函数、时间转换函数
- MySQL Date/Time to Str(日期/时间转换为字符串)函数:date_format(date,format), time_format(time,format)
MySQL 日期、时间转换函数:date_format(date,format), time_format(time,format) 能够把一个日期/时间转换成各种各样的字符串格式。它是 str_to_date(str,format) 函数的 一个逆转换。
- MySQL Str to Date (字符串转换为日期)函数:str_to_date(str, format)
select str_to_date('08/09/2008', '%m/%d/%Y'); -- 2008-08-09
select str_to_date('08/09/08' , '%m/%d/%y'); -- 2008-08-09
select str_to_date('08.09.2008', '%m.%d.%Y'); -- 2008-08-09
select str_to_date('08:09:30', '%h:%i:%s'); -- 08:09:30
select str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'); -- 2008-08-09 08:09:30
可以看到,str_to_date(str,format) 转换函数,可以把一些杂乱无章的字符串转换为日期格式。
- MySQL (日期、天数)转换函数:to_days(date), from_days(days)
select to_days('2008-08-08'); -- 733627
select from_days(733627); -- 2008-08-08
- MySQL (时间、秒)转换函数:time_to_sec(time), sec_to_time(seconds)
select time_to_sec('01:00:05'); -- 3605
select sec_to_time(3605); -- '01:00:05'
- MySQL 拼凑日期、时间函数:makdedate(year,dayofyear), maketime(hour,minute,second)
select makedate(2001,31); -- '2001-01-31'
select makedate(2001,32); -- '2001-02-01'
select maketime(12,15,30); -- '12:15:30'
- MySQL (Unix 时间戳、日期)转换函数
select unix_timestamp(); -- 1218290027
select unix_timestamp('2008-08-08'); -- 1218124800
select unix_timestamp('2008-08-08 12:30:00'); -- 1218169800
select from_unixtime(1218290027); -- '2008-08-09 21:53:47'
select from_unixtime(1218124800); -- '2008-08-08 00:00:00'
select from_unixtime(1218169800); -- '2008-08-08 12:30:00'
select from_unixtime(unix_timestamp('2021-08-09 15:08:08'), '%Y-%m-%d %h:%i:%s');-- 2021-08-09 03:08:08
2.7.4 MySQL 日期时间计算函数
- MySQL 为日期增加一个时间间隔:date_add()
set @dt = now();
select date_add(@dt, interval 1 day); -- add 1 day
select date_add(@dt, interval 1 hour); -- add 1 hour
select date_add(@dt, interval 1 minute); -- add 1 minute
select date_add(@dt, interval 1 second); -- add 1 second
select date_add(@dt, interval 1 microsecond); -- add 1 microsecond
select date_add(@dt, interval 1 week); -- add 1 week
select date_add(@dt, interval 1 month); -- add 1 month
select date_add(@dt, interval 1 quarter);-- add 1 quarter
select date_add(@dt, interval 1 year); -- add 1 year
select date_add(@dt, interval -1 day); -- sub 1 day
- MySQL 日期、时间相减函数:datediff(date1,date2), timediff(time1,time2)
- MySQL datediff(date1,date2):两个日期相减 date1 - date2,返回天数。
select datediff('2008-08-08', '2008-08-01'); -- 7
select datediff('2008-08-01', '2008-08-08'); -- -7
- MySQL timediff(time1,time2):两个日期相减 time1 - time2,返回 time 差值。
select timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00'); -- 08:08:08
select timediff('08:08:08', '00:00:00'); -- 08:08:08
注意:timediff(time1,time2) 函数的两个参数类型必须相同。
- MySQL 时间戳(timestamp)转换、增、减函数:
timestamp(date) -- date to timestamp
timestamp(dt,time) -- dt + time
timestampadd(unit,interval,datetime_expr) --
timestampdiff(unit,datetime_expr1,datetime_expr2) --
请看示例部分:
select timestamp('2008-08-08'); -- 2008-08-08 00:00:00
select timestamp('2008-08-08 08:00:00', '01:01:01'); -- 2008-08-08 09:01:01
select timestamp('2008-08-08 08:00:00', '10 01:01:01'); -- 2008-08-18 09:01:01
select timestampadd(day, 1, '2008-08-08 08:00:00'); -- 2008-08-09 08:00:00
select date_add('2008-08-08 08:00:00', interval 1 day); -- 2008-08-09 08:00:00
MySQL timestampadd() 函数类似于 date_add()。
select timestampdiff(year,'2002-05-01','2001-01-01'); -- -1
select timestampdiff(day ,'2002-05-01','2001-01-01'); -- -485
select timestampdiff(hour,'2008-08-08 12:00:00','2008-08-08 00:00:00'); -- -12
select datediff('2008-08-08 12:00:00', '2008-08-01 00:00:00'); -- 7
MySQL timestampdiff() 函数就比 datediff() 功能强多了,datediff() 只能计算两个日期(date)之间相差的天数。
2.7.5 MySQL 时区(timezone)转换函数
- convert_tz(dt,from_tz,to_tz)
select convert_tz('2008-08-08 12:00:00', '+08:00', '+00:00'); -- 2008-08-08 04:00:00
- 时区转换也可以通过 date_add, date_sub, timestampadd 来实现。
select date_add('2008-08-08 12:00:00', interval -8 hour); -- 2008-08-08 04:00:00
select date_sub('2008-08-08 12:00:00', interval 8 hour); -- 2008-08-08 04:00:00
select timestampadd(hour, -8, '2008-08-08 12:00:00'); -- 2008-08-08 04:00:00
2.8 IFNULL()函数
- IFNULL() 函数用于判断第一个表达式是否为 NULL,如果为 NULL 则返回第二个参数的值,如果不为 NULL 则返回第一个参数的值。
- IFNULL() 函数语法格式为:
IFNULL(expression, alt_value)
如果第一个参数的表达式 expression 为 NULL,则返回第二个参数的备用值。
-- 第一个参数为 NULL:
SELECT IFNULL(NULL, "RUNOOB");
-- 以上实例输出结果为:
RUNOOB
-- 第一个参数不为 NULL:
SELECT IFNULL("Hello", "RUNOOB");
-- 以上实例输出结果为:
Hello
2.9 对于字符型和时间型数据求最大值
- 对字符型数据的最大值,是按照首字母由A~Z的顺序排列,越往后,其值越大。当然,对于汉字则是按照其全拼拼音排列的,若首字符相同,则比较下一个字符,以此类推。
- 对与日期时间类型的数据也可以求其最大/最小值,其大小排列就是日期时间的早晚,越早认为其值越小
三、正则表达式批量删除记录
#批量删除tbtt_question表中字段questionid 以Q172.开头的记录,其中^表示以...开头,*是通配符,REGEXP是正则表达式的意思。
DELETE
FROM
tbtt_question
WHERE
questionid REGEXP '^Q172.*';
四、查看MySQL版本
- 查看MySQL版本
SHOW VARIABLES LIKE 'version';