对于 MySQL 常用的 SQL 语句比如 select,create,insert,update,delete,join,order by,group by 等等相信大家都不陌生; 但对于一些不是很常用却又十分实用的语句在要使用的时候却经常记不起来如何写, 所以小编特地梳理了一下这些语句, 希望对大家有所帮助.
导出数据库
说到导出数据库, 大家肯定能 mysqldump, 由于 mysqldump 使用时涉及到比较多的参数, 所以在使用 mysqldump 时对这些参数基本记不全, 那么我们先来熟悉一下常用的参数, 如下表:
(1) 导出整个数据库
mysqldump -uroot -p111111 -h172.30.14.242 dbname>mydb.sql
(2) 导出存储过程及函数
mysqldump -uroot -p111111 -h172.30.14.242 -ntd-R dbname > prorfunc.sql
(3) 导出数据库表
mysqldump -uroot -p111111 -h172.30.14.242 dbnametablename>mydb.sql
若只需要导出表结构, 可以增加参数 - d 或者 - no-data
若只需要导出表数据, 可以增加参数 - t 或者 --no-create-info
导入数据库
(1) 未连接数据库时导入
MySQL -uroot -p111111 -h172.30.14.242 dbname< mydb.sql
(2) 连接数据库时导入
连接数据库
MySQL -uroot -p111111 -h172.30.14.242
指定客户端和服务器之间传递字符的编码规则为 utf8
set names utf8
使用数据库
use dbname
导入数据库
source /tmp/mydb.sql
其他数据导出 / 导入方式
SELECT INTO...OUTFILE 语句把表数据导出到一个文本文件中, 并用 LOAD DATA ...INFILE 语句恢复数据. 但是这种方法只能导出或导入数据的内容, 不包括表的结构.
(1) SELECT INTO ... OUTFILE 导出数据
SELECT * FROM mytable INTO OUTFILE '/tmp/mytable.txt';
可以通过命令选项来设置数据输出的指定格式, 如下语句为导出 CSV 格式:
- SELECT * FROM mytable INTO OUTFILE'/tmp/mytable.txt' FIELDS TERMINATED BY ','
- ENCLOSED BY '"'LINESTERMINATED BY'\r\n';
FIELDS 子句: 在 FIELDS 子句中有三个亚子句: TERMINATED BY, [OPTIONALLY] ENCLOSED BY 和 ESCAPED BY. 如果指定了 FIELDS 子句, 则这三个亚子句中至少要指定一个.
TERMINATED BY 用来指定字段值之间的符号;
ENCLOSED BY 子句用来指定包裹文件中字符值的符号;
ESCAPED BY 子句用来指定转义字符.
LINES 子句: 在 LINES 子句中使用 TERMINATED BY 指定一行结束的标志.
(2) LOAD DATA ... INFILE 导入数据
- LOAD DATA LOCAL INFILE 'dump.txt' INTOTABLE mytable FIELDS TERMINATED BY ':'
- LINES TERMINATED BY '\r\n';
LOAD DATA 默认情况下是按照数据文件中列的顺序插入数据的, 如果数据文件中的列与插入表中的列不一致, 则需要指定列的顺序. 如, 在数据文件中的列顺序是 a,b,c, 但在插入表的列顺序为 b,c,a, 则数据导入语法如下:
LOAD DATA LOCAL INFILE 'mytable.txt' INTO TABLE mytable (b, c, a)
字符串操作
(1) 字符串拼接
1)CONCAT(s1,s2,...) 函数
将所有字符串拼接在一起
语句: SELECT CONCAT('2019','06','18);
执行结果: 20190618
2)CONCAT_WS(s,s1,s2,...) 函数
通过第一个字符串 s 将后面的所有字符串拼接在一起
语句: SELECT CONCAT_WS('-','2019','06','18');
执行结果: 2019-06-18
(2) 字符串替换
REPLACE(s,s1,s2) 函数
将字符串 s 中的 s1 字符串替换为 s2 字符串
语句: SELECT REPLACE('helloname','name','world');
执行结果: hello world
时间函数
(1) 时间转字符串
DATE_FORMAT 函数用于以不同的格式显示日期 / 时间数据
语法: DATE_FORMAT(date,format)
format 规定日期 / 时间的输出格式.
示例:
语句: select DATE_FORMAT(now(),'%Y-%m-%d %T');
执行结果: 2019-07-09 15:35:19
语句: select DATE_FORMAT(now(),'%Y-%m-%d %H:%i:%s');
执行结果: 2019-07-09 15:36:02
(2) 字符串转时间
STR_TO_DATE 用于将字符转化为日期 / 时间格式
语法: STR_TO_DATE(string, format)
语句: SELECT STR_TO_DATE("2019 07 07","%Y %m %d");
执行结果: 2019-07-07
(3) 时间运算
函数从日期加上指定的时间间隔
DATE_ADD(date,INTERVAL expr type)
函数从日期减去指定的时间间隔
DATE_SUB(date,INTERVAL expr type)
type 可取值为:
SECOND,MINUTE,HOUR,DAY,WEEK,MONTH,QUARTER,EAR
示例:
语句: SELECT DATE_ADD(now(),INTERVAL 2 DAY);
执行结果: 2019-07-11 15:48:36
语句: SELECT DATE_SUB(now(),INTERVAL 2 DAY);
执行结果: 2019-07-07 15:48:36
浮点数运算后的精度控制
(1) 转化指定精度的字符串
FORMAT 函数在 MySQL 中是数据内容格式化, 可以格式化数据为整数或者浮点数, 整数部分超过三位的时候以逗号分割, 并且返回的结果是 string 类型的.
语句: SELECT FORMAT(1012.232,2);
执行结果: 1,012.23
(2) 转化指定精度的浮点数
FORMAT 函数返回的是字符串形式数据, 若要返回浮点数, 我们使用 CONVERT 函数.
语句: SELECT CONVERT(1012.232, decimal(12,2));
执行结果: 1012.23
来源: http://database.51cto.com/art/201907/599667.htm