SQL 日常记录

1. 查看数据库中触发器信息

SHOW TRIGGERS

2. 修改外键

     2.1.删除约束

ALTER TABLE ‘表名1’ DROP FOREIGN KEY  ‘约束名’

     2.2.增加约束

ALTER TABLE ‘表名1’ ADD CONSTRAINT '约束名' FOREIGN KEY （‘字段名’） REFERENCES '表名2' （‘字段名’）

3. 更新指定记录

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

3. 删除指定记录

DELETE FROM 表名称 WHERE 列名称 = 值

4. MySQL函数

Mysql常用函数大全(分类汇总讲解)
Mysql GROUP_CONCAT函数讲得不错

5. MySQL的inner join, left join, right join

从https://blog.csdn.net/stpeace/article/details/91507571精简。

     5.1 数据展示

mysql> select * from a;
+----+-----+
| id | age |
+----+-----+
|  1 |  10 |
|  2 |  20 |
|  3 |  30 |
|  4 |  40 |
+----+-----+
mysql> select * from b;
+----+-------+
| id | score |
+----+-------+
|  1 |   100 |
|  2 |   200 |
|  3 |   300 |
|  5 |   500 |
+----+-------+

     5.2 inner join

mysql> select * from a inner join b where a.id = b.id;
+----+-----+----+-------+
| id | age | id | score |
+----+-----+----+-------+
|  1 |  10 |  1 |   100 |
|  2 |  20 |  2 |   200 |
|  3 |  30 |  3 |   300 |
+----+-----+----+-------+

     5.3 left join

mysql> select * from a left join b on a.id = b.id;
+----+-----+------+-------+
| id | age | id   | score |
+----+-----+------+-------+
|  1 |  10 |    1 |   100 |
|  2 |  20 |    2 |   200 |
|  3 |  30 |    3 |   300 |
|  4 |  40 | NULL |  NULL |
+----+-----+------+-------+

     5.4 right join

mysql> select * from a right join b on a.id = b.id;
+------+------+----+-------+
| id   | age  | id | score |
+------+------+----+-------+
|    1 |   10 |  1 |   100 |
|    2 |   20 |  2 |   200 |
|    3 |   30 |  3 |   300 |
| NULL | NULL |  5 |   500 |
+------+------+----+-------+

6. Binlog

MySQL Binlog 介绍