Mysql知识进阶

查询表格详细内容

基础版

desc 表格名;

mysql> desc lanqiao;
+------------+------------+------+-----+---------+-------+
| Field      | Type       | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| candidate  | varchar(3) | YES  |     | NULL    |       |
| age        | int        | YES  |     | NULL    |       |
| grade      | int        | YES  |     | NULL    |       |
| is_promote | tinyint(1) | YES  |     | NULL    |       |
+------------+------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

进阶版

show full columns from 表名;

通过此查询方法，可以查询到与权限相关的更为详细的内容

mysql> show full columns from lanqiao;
+------------+------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field      | Type       | Collation          | Null | Key | Default | Extra | Privileges                      | Comment |
+------------+------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| candidate  | varchar(3) | utf8mb4_0900_ai_ci | YES  |     | NULL    |       | select,insert,update,references |         |
| age        | int        | NULL               | YES  |     | NULL    |       | select,insert,update,references |         |
| grade      | int        | NULL               | YES  |     | NULL    |       | select,insert,update,references |         |
| is_promote | tinyint(1) | NULL               | YES  |     | NULL    |       | select,insert,update,references |         |
+------------+------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
4 rows in set (0.00 sec)

变更key属性

 给某属性增加特征（例如unique）

alter table 表名 add unique;

举个例子

alter table lanqiao add unique(candidate);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

可见，表格属性那里增加了unique限制 

mysql> show full columns from lanqiao;
+------------+------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field      | Type       | Collation          | Null | Key | Default | Extra | Privileges                      | Comment |
+------------+------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| candidate  | varchar(3) | utf8mb4_0900_ai_ci | YES  | UNI | NULL    |       | select,insert,update,references |         |
| age        | int        | NULL               | YES  |     | NULL    |       | select,insert,update,references |         |
| grade      | int        | NULL               | YES  |     | NULL    |       | select,insert,update,references |         |
| is_promote | tinyint(1) | NULL               | YES  |     | NULL    |       | select,insert,update,references |         |
+------------+------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
4 rows in set (0.00 sec)

查看各种约束（主外键引用关系）

desc information_schema.key_column_usage

通过information_schema.key_column_usage表可以来查看外键引用关系

desc information_schema.key_column_usage;
+-------------------------------+--------------+------+-----+---------+-------+
| Field                         | Type         | Null | Key | Default | Extra |
+-------------------------------+--------------+------+-----+---------+-------+
| CONSTRAINT_CATALOG            | varchar(64)  | YES  |     | NULL    |       |
| CONSTRAINT_SCHEMA             | varchar(64)  | YES  |     | NULL    |       |
| CONSTRAINT_NAME               | varchar(64)  | YES  |     | NULL    |       |
| TABLE_CATALOG                 | varchar(64)  | YES  |     | NULL    |       |
| TABLE_SCHEMA                  | varchar(64)  | YES  |     | NULL    |       |
| TABLE_NAME                    | varchar(64)  | YES  |     | NULL    |       |
| COLUMN_NAME                   | varchar(64)  | YES  |     | NULL    |       |
| ORDINAL_POSITION              | int unsigned | NO   |     | 0       |       |
| POSITION_IN_UNIQUE_CONSTRAINT | int unsigned | YES  |     | NULL    |       |
| REFERENCED_TABLE_SCHEMA       | varchar(64)  | YES  |     | NULL    |       |
| REFERENCED_TABLE_NAME         | varchar(64)  | YES  |     | NULL    |       |
| REFERENCED_COLUMN_NAME        | varchar(64)  | YES  |     | NULL    |       |
+-------------------------------+--------------+------+-----+---------+-------+
12 rows in set (0.00 sec)

更改终止标识符

原本默认的语句结束标识为分号“；”

使用delimiter语句，可以更改该默认属性。

delimiter 更正后的标识符

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| aid                |
| heroes_heaven      |
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| student_grades     |
| test               |
+--------------------+
9 rows in set (0.00 sec)
 
mysql> delimiter //
mysql> show databases //
+--------------------+
| Database           |
+--------------------+
| aid                |
| heroes_heaven      |
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| student_grades     |
| test               |
+--------------------+
9 rows in set (0.00 sec)

主码变更

增加主码

alter table 表名 add primary key(需要添加的属性名);

alter table lanqiao add primary key(candidate);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

为选手candidate增加主码后，显示key属性有了pri 

show full columns from lanqiao;
+------------+------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field      | Type       | Collation          | Null | Key | Default | Extra | Privileges                      | Comment |
+------------+------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| candidate  | varchar(3) | utf8mb4_0900_ai_ci | NO   | PRI | NULL    |       | select,insert,update,references |         |
| age        | int        | NULL               | YES  |     | NULL    |       | select,insert,update,references |         |
| grade      | int        | NULL               | YES  |     | NULL    |       | select,insert,update,references |         |
| is_promote | tinyint(1) | NULL               | YES  |     | NULL    |       | select,insert,update,references |         |
+------------+------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
4 rows in set (0.00 sec)

删除主码pri

alter table 表名 drop primary key;

mysql> alter table lanqiao drop primary key;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

show full columns from lanqiao;
+------------+------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field      | Type       | Collation          | Null | Key | Default | Extra | Privileges                      | Comment |
+------------+------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| candidate  | varchar(3) | utf8mb4_0900_ai_ci | NO   | PRI | NULL    |       | select,insert,update,references |         |
| age        | int        | NULL               | YES  |     | NULL    |       | select,insert,update,references |         |
| grade      | int        | NULL               | YES  |     | NULL    |       | select,insert,update,references |         |
| is_promote | tinyint(1) | NULL               | YES  |     | NULL    |       | select,insert,update,references |         |
+------------+------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
4 rows in set (0.00 sec)

 对插入的数据进行分析，插入对应分组的预定结果（good，not good）