【MySQL】二级MySQL考试 救场帮助表
周六去考二级，应用第一题就是添加外键约束
草，写了半天老说语法不对，然后急中生智，觉得默认的库里应该有文档说明表
以下是SQL查询过程：
-- 猜测是在mysql库里面 mysql> USE mysql; Database changed -- 查看这个库下的表 mysql> SHOW TABLES; +------------------------------------------------------+ | Tables_in_mysql | +------------------------------------------------------+ | columns_priv | | component | | db | | default_roles | | engine_cost | | func | | general_log | | global_grants | | gtid_executed | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | password_history | | plugin | | procs_priv | | proxies_priv | | replication_asynchronous_connection_failover | | replication_asynchronous_connection_failover_managed | | role_edges | | server_cost | | servers | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +------------------------------------------------------+ 35 rows in set (0.46 sec)
然后发现这四张Help表，考试的时候我没关注过分类表
展开查看发现，里面已经说明了MySQL的内容：
mysql> SELECT * FROM help_category; +------------------+---------------------------------------+--------------------+-----+ | help_category_id | name | parent_category_id | url | +------------------+---------------------------------------+--------------------+-----+ | 0 | Contents | 0 | | | 1 | Help Metadata | 0 | | | 2 | Data Types | 0 | | | 3 | Administration | 0 | | | 4 | Functions | 0 | | | 5 | Enterprise Encryption Functions | 4 | | | 6 | Language Structure | 0 | | | 7 | Geographic Features | 0 | | | 8 | MBR | 7 | | | 9 | WKT | 7 | | | 10 | Comparison Operators | 4 | | | 11 | Logical Operators | 4 | | | 12 | Flow Control Functions | 4 | | | 13 | Numeric Functions | 4 | | | 14 | Date and Time Functions | 4 | | | 15 | String Functions | 4 | | | 16 | Cast Functions and Operators | 4 | | | 17 | XML | 4 | | | 18 | Bit Functions | 4 | | | 19 | Encryption Functions | 4 | | | 20 | Locking Functions | 4 | | | 21 | Information Functions | 4 | | | 22 | Spatial Functions | 4 | | | 23 | WKT Functions | 22 | | | 24 | WKB Functions | 22 | | | 25 | Geometry Constructors | 22 | | | 26 | Geometry Property Functions | 22 | | | 27 | Point Property Functions | 22 | | | 28 | LineString Property Functions | 22 | | | 29 | Polygon Property Functions | 22 | | | 30 | GeometryCollection Property Functions | 22 | | | 31 | Geometry Relation Functions | 22 | | | 32 | MBR Functions | 22 | | | 33 | GTID | 4 | | | 34 | Aggregate Functions and Modifiers | 4 | | | 35 | GROUP BY Functions and Modifiers | 4 | | | 36 | Window Functions | 4 | | | 37 | Performance Schema Functions | 4 | | | 38 | Internal Functions | 4 | | | 39 | Miscellaneous Functions | 4 | | | 40 | Data Definition | 0 | | | 41 | Data Manipulation | 0 | | | 42 | Transactions | 0 | | | 43 | Compound Statements | 0 | | | 44 | Account Management | 0 | | | 45 | Table Maintenance | 0 | | | 46 | User-Defined Functions | 0 | | | 47 | Components | 0 | | | 48 | Plugins | 0 | | | 49 | Utility | 0 | | | 50 | Storage Engines | 0 | | +------------------+---------------------------------------+--------------------+-----+ 51 rows in set (0.05 sec)
因为是关键字或者其他问题，尝试翻查这个关键字帮助表
当时写外键语句是这样：
ALTER TABLE xxx ADD CONSTRAINT FOREIGN KEY xxx(xxx) REFERENCE xx(xx);
报错提醒发生在 reference这里
所以我当时就搜素两个关键字，外键和引用这两个
mysql> SELECT * FROM help_keyword WHERE `name` LIKE '%reference%'; +-----------------+------------+ | help_keyword_id | name | +-----------------+------------+ | 653 | REFERENCE | | 673 | REFERENCES | +-----------------+------------+ 2 rows in set (0.03 sec) mysql> SELECT * FROM help_keyword WHERE `name` LIKE '%FOREIGN%'; +-----------------+---------+ | help_keyword_id | name | +-----------------+---------+ | 599 | FOREIGN | +-----------------+---------+ 1 row in set (0.02 sec)
然后官方给这个文档帮助表设定的说明关系是，关键字和主题是一个多对多的关系
用一个关系表维护，这里要先看【帮助主题】绑定的【关键字ID】
mysql> SELECT * FROM help_relation WHERE help_keyword_id = 599; +---------------+-----------------+ | help_topic_id | help_keyword_id | +---------------+-----------------+ | 508 | 599 | | 518 | 599 | | 520 | 599 | | 521 | 599 | +---------------+-----------------+ 4 rows in set (0.12 sec)
我们还可以再看看之前reference关键字关联的
mysql> SELECT * FROM help_relation WHERE help_keyword_id = 653; +---------------+-----------------+ | help_topic_id | help_keyword_id | +---------------+-----------------+ | 519 | 653 | | 532 | 653 | +---------------+-----------------+ 2 rows in set (0.02 sec) mysql> SELECT * FROM help_relation WHERE help_keyword_id = 673; +---------------+-----------------+ | help_topic_id | help_keyword_id | +---------------+-----------------+ | 520 | 673 | | 521 | 673 | | 610 | 673 | +---------------+-----------------+ 3 rows in set (0.02 sec)
可以发现599ID 和673ID都有共同两个帮助主题
所以确定是References关键字，然后看看520ID的主题是什么：
由于主题表输出的内容太多，格式完全混乱了，当时考试那个命令终端就是一直刷，还没办法停下
然后先查看主题表的字段：
mysql> DESC help_topic; +------------------+-------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+-------------------+------+-----+---------+-------+ | help_topic_id | int unsigned | NO | PRI | NULL | | | name | char(64) | NO | UNI | NULL | | | help_category_id | smallint unsigned | NO | | NULL | | | description | text | NO | | NULL | | | example | text | NO | | NULL | | | url | text | NO | | NULL | | +------------------+-------------------+------+-----+---------+-------+ 6 rows in set (0.08 sec)
最主要的是example样例和decription描述,但是example查询为空，只能看描述了
mysql> SELECT example FROM help_topic WHERE help_topic_id = 520; +---------+ | example | +---------+ | | +---------+
520ID没找到，就找521，结果一看正好就是这个：
就是少写了S导致的
MySQL supports foreign keys, which permit cross-referencing related
data across tables, and foreign key constraints, which help keep the
related data consistent.
A foreign key relationship involves a parent table that holds the
initial column values, and a child table with column values that
reference the parent column values. A foreign key constraint is defined
on the child table.
The essential syntax for a defining a foreign key constraint in a
CREATE TABLE or ALTER TABLE statement includes the following:
[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (col_name, ...)
REFERENCES tbl_name (col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
URL: https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html
