关于外键
子表：
CREATE TABLE req(
icon VARCHAR(128) PRIMARY KEY,
series VARCHAR(128),
CONSTRAINT fk_series FOREIGN KEY(series) REFERENCES series(name) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE = InnoDB;
父表：
CREATE TABLE series(
name VARCHAR(128) PRIMARY KEY,
sys TINYINT(1) DEFAULT 0
) ENGINE = InnoDB;
外键
支持外键只能使用 InnoDB 存储引擎。
级联操作问题
ON UPDATE CASCADE 指定级联更新；ON DELETE SET NULL 指定父表删除记录时子表相应字段设为 NULL。如果级联删除则是 ON DELETE CASCADE。
如果不使用这些语句，那么在父表更新或删除有子表依赖的记录时将不被允许。
为已有表增加外键
ALTER TABLE req ADD series VARCHAR(128);
ALTER TABLE req ADD CONSTRAINT fk_series FOREIGN KEY(series) REFERENCES series(name) ON UPDATE CASCADE ON DELETE SET NULL;
关于 SELECT 合并
SQL：
SELECT COUNT(*) AS sum FROM t1 WHERE p1 = 'x';
SELECT COUNT(*) AS sum FROM t1 WHERE p1 = 'x' AND p2 = 'y';
结果：
+-----+
| sum |
+-----+
| 4 |
+-----+
+-----+
| sum |
+-----+
| 1 |
+-----+
合并SQL：
SELECT COUNT(*) FROM t1 WHERE p1 = 'x' GROUP BY p2 = 'y';
结果：
+-----+
| sum |
+-----+
| 3 |
| 1 |
+-----+
通过 GROUP BY p2 = 'y' 将结果分成了两组
了解表结构
desc t1;
show create table t1;
GROUP BY 后查总数
select count(*) from (select pkg from req group by pkg);
这样是不行的。[Err] 1248 - Every derived table must have its own alias
正确语句如下：
select count(*) from (select pkg from req group by pkg) as t;
连接数
SHOW VARIABLES LIKE '%max_connections%';
SHOW GLOBAL STATUS LIKE '%max_used_connections%';
SET GLOBAL max_connections = 256;
SHOW PROCESSLIST;
关于 TIMESTAMP
time TIMESTAMP NOT NULL
等同于
time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
或许我们的需求是这样：
time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
