MySQL 常用查询函数 | 时间处理 | 字符串处理 #

1
2
3
4
5
6

-- 1. 注意，如果 unix_timestamp 传入的日期早于 1970, 结果会返回 0.
+--------------------------+------------------+------------------------------+
| from_unixtime(867686400) | unix_timestamp() | unix_timestamp("1997-07-01") |
+--------------------------+------------------+------------------------------+
| 1997-07-01 00:00:00      |       1685622814 |                    867686400 |
+--------------------------+------------------+------------------------------+

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 1. 获取当前日期时间、当前日期、当前时间；裁剪日期部分、裁剪时间部分
+---------------------+------------+-----------+-------------+-------------+
| now()               | curdate()  | curtime() | date(now()) | time(now()) |
+---------------------+------------+-----------+-------------+-------------+
| 2023-06-01 19:59:32 | 2023-06-01 | 19:59:32  | 2023-06-01  | 19:59:32    |
+---------------------+------------+-----------+-------------+-------------+
-- 2. 距离 0 年有多少天、多少秒
+-----------------------+--------------------------+
| to_days('1997-07-01') | to_seconds('1997-07-01') |
+-----------------------+--------------------------+
|                729571 |              63034934400 |
+-----------------------+--------------------------+

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

-- 1. 拼接、带分隔符拼接
+---------------------+----------------------------+
| concat('a','b','c') | concat_ws(' ','a','b','c') |
+---------------------+----------------------------+
| abc                 | a b c                      |
+---------------------+----------------------------+
-- 2. 左右裁剪、自定义裁剪
+-------------------+--------------------+----------------------+
| left('kungfu', 4) | right('kungfu', 2) | substr('kungfu',1,4) |
+-------------------+--------------------+----------------------+
| kung              | fu                 | kung                 |
+-------------------+--------------------+----------------------+

-- 2. 字符串反转、替换、清理空格，同理可以清理左右两侧空格 ltrim, rtrim
+-------------------+-----------------------------+-------------------+
| reverse('kungfu') | replace('kungfu', 'u', '-') | trim('  kungfu ') |
+-------------------+-----------------------------+-------------------+
| ufgnuk            | k-ngf-                      | kungfu            |
+-------------------+-----------------------------+-------------------+

1
2
3
4
5
6

SET @dolphin:='海豚'; SELECT LENGTH(@dolphin), CHAR_LENGTH(@dolphin);
+------------------+-----------------------+
| LENGTH(@dolphin) | CHAR_LENGTH(@dolphin) |
+------------------+-----------------------+
|                6 |                     2 |
+------------------+-----------------------+

1
2
3
4
5
6
7

-- FORMAT函数将数字 N 格式化, 如"#,###,###.##"，舍入到D位小数。它返回一个值作为字符串.
-- N, 要格式化的数字. D, 要舍入的小数位数. locale, 可选, 用于确定分隔符之间的字符.
+---------------------+---------------------+------------------------------+
| FORMAT(14500.10, 1) | FORMAT(14500.10, 0) | FORMAT(14500.10, 0, 'es_HN') |
+---------------------+---------------------+------------------------------+
| 14,500.1            | 14,500              | 14500                        |
+---------------------+---------------------+------------------------------+

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 1、导出數據库為dbname的表结构（其中用戶名為root,密码為dbpasswd,生成的脚本名為db.sql）
mysqldump -uroot -pdbpasswd -d dbname >db.sql;

-- 2、导出數據库為dbname某张表(test)结构
mysqldump -uroot -pdbpasswd -d dbname test>db.sql;

-- 3、导出數據库為dbname所有表结构及表數據（不加-d）
mysqldump -uroot -pdbpasswd  dbname >db.sql;

-- 4、导出數據库為dbname某张表(test)结构及表數據（不加-d）
mysqldump -uroot -pdbpasswd dbname test>db.sql;

1

mysql -uroot -pdbpasswd -d dbname -e 'select * ' > /tmp/somefile.xlsx