Mysql 常用 SQL

发表于 更新于 上层目录 阅读次数：

环境信息

  • Mysql 5.7

常用 sql

导出数据

导出到文件

SELECT * FROM [TABLE] 
    INTO OUTFILE '[FILE]' 
    FIELDS TERMINATED BY ',' 
    OPTIONALLY ENCLOSED BY '"' 
    LINES TERMINATED BY '\n'；

参数说明：

  • into outfile ‘导出的目录和文件名’
  • fields terminated by ‘字段间分隔符’
  • optionally enclosed by ‘字段包围符’

  • optionally enclosed by 对数值型字段无效

  • lines terminated by ‘行间分隔符’

产生报错可参考 Mysql 常见错误

插入数据

批量插入

insert into tb(c1,c2) values(1,2),(3,4),(5,6);

备份数据

mysqldump 常见用法

MySQL 备份及主从恢复

mysqldump 备份时排除某些表

  • --ignore-table=database.table 导出时排除指定的表
mysqldump -uroot -p database --ignore-table=database.table1 --ignore-table=database.table2 > backup.sql

mysqldump 导出表结构

  • -d 选项，只导出表结构，不导出数据

导出整个数据库表结构（不包含数据）

mysqldump -uroot -p -d DB > db.sql

导出单个数据表结构（不包含数据）

mysqldump -uroot -p -d DB TABLE > table.sql

触发器

为了创建一个新的触发器，可以使用 CREATE TRIGGER 语句。 下面说明了 CREATE TRIGGER 语句的语法： [1]

CREATE TRIGGER [trigger_name] [trigger_time] [trigger_event]
 ON [table_name]
 FOR EACH ROW
 BEGIN
 ...
 END;

参数说明：

  • trigger_name : 触发器名称, 一般遵循命名约定 [trigger time]_[table name]_[trigger event]，例如 before_employees_update
  • trigger_time : 触发激活时间，必须指定定义触发器的激活时间。如果要在更改之前处理操作，则使用 BEFORE 关键字，如果在更改后需要处理操作，则使用 AFTER 关键字
  • trigger_event : 触发事件，可以是 INSERT，UPDATE 或 DELETE。此事件导致触发器被调用。 触发器只能由一个事件调用。要定义由多个事件调用的触发器，必须定义多个触发器，每个事件一个触发器。
  • table_name : 触发器管理的表。触发器必须与特定表关联。没有表触发器将不存在，所以必须在 ON 关键字之后指定表名
  • BEGIN … END : 将 SQL 语句放在 BEGIN 和 END 块之间。这是定义触发器逻辑的位置。

以下示例定义一个触发器，在表中的字段被更改后，改回旧的之，实现禁止更改表的效果

DELIMITER // 

CREATE TRIGGER `before_update_images` 
    BEFORE UPDATE ON `images` 
    FOR EACH ROW
BEGIN
    set new.name=old.name;
    set new.url=old.url;
END //
DELIMITER;

常用配置

mysql 命令提示符下翻页查看输出

pager less -S

之后的输出可以使用和less命令一样的参数

gegeral_log 开启

查询 general_log 配置，并启用

mysql> show global variables like '%general%';
+------------------+----------------------------------------------------+
| Variable_name    | Value                                              |
+------------------+----------------------------------------------------+
| general_log      | OFF                                                |
| general_log_file | /var/lib/mysql/data/localhost.log                  |
+------------------+----------------------------------------------------+

mysql> set global general_log_file='/tmp/mysql.log';

mysql> set global general_log=on;
    
mysql> show global variables like '%general%';
+------------------+----------------------+
| Variable_name    | Value                |
+------------------+----------------------+
| general_log      | ON                   |
| general_log_file | /tmp/mysql.log       |
+------------------+----------------------+
2 rows in set (0.00 sec)

    
mysql> set global general_log=off;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like '%general%';
+------------------+----------------------+
| Variable_name    | Value                |
+------------------+----------------------+
| general_log      | OFF                  |
| general_log_file | /tmp/mysql.log       |
+------------------+----------------------+
2 rows in set (0.00 sec)

在 general_log 中可用通过搜索 event id 来查看登陆连接对数据库的所有 SQL 。

脚注