MySQL之常用技巧整理

西西先生666

已于 2022-05-11 20:31:50 修改

阅读量308

点赞数

分类专栏： MySQL 文章标签： mysql 数据库

于 2021-06-04 17:03:33 首次发布

本文链接：https://blog.csdn.net/qq_40216188/article/details/117567034

版权

MySQL 专栏收录该内容

5 篇文章 2 订阅

订阅专栏

本文详细介绍了MySQL中常用的SQL执行顺序，以及字符串、时间、窗口函数、正则表达式和数据类型处理技巧。涵盖了LIKE函数、字符串函数（如CONCAT, LOWER, UPPER等）、窗口函数示例、ALTER和CASE-WHEN-ELSE-END函数、时间函数和IFNULL函数的应用。

摘要由CSDN通过智能技术生成

一、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';

西西先生666

关注

0
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
MySQL之常用技巧整理

MySQL之常用技巧整理一、正则表达式批量删除记录一、正则表达式批量删除记录#批量删除tbtt_question表中字段questionid 以Q172.开头的记录，其中^表示以...开头，*是通配符，REGEXP是正则表达式的意思。DELETEFROM tbtt_questionWHERE questionid REGEXP '^Q172.*';...
复制链接

扫一扫