-- 创建带解释的表
CREATE TABLE groups(
gid INT PRIMARY KEY AUTO_INCREMENT COMMENT '设置主键自增ID',
gname VARCHAR(200) COMMENT '名称'
) COMMENT='群表';
SHOW CREATE TABLE city;
#查看表的注释
SELECT table_name,table_comment FROM information_schema.tables WHERE table_schema = 'sakila' AND table_name ='groups';
# 查看列的注释 涂聚文 Geovin Du
SHOW FULL COLUMNS FROM city;
SELECT column_name, column_comment FROM information_schema.columns WHERE table_schema ='sakila' AND table_name = 'groups';
-- 表注释，列注释city_id
use sakila;
-- 表描述
ALTER TABLE city COMMENT='城市表';
-- 更改注释
alter table city modify column city varchar(50) comment '城市名称';
ALTER TABLE city MODIFY COLUMN `city_id` smallint(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '城市ID';
ALTER TABLE city MODIFY COLUMN `country_id` smallint(5) unsigned NOT NULL COMMENT '国家代码';
ALTER TABLE city MODIFY COLUMN city_id smallint(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '城市ID';
ALTER TABLE city MODIFY COLUMN country_id smallint(5) unsigned NOT NULL COMMENT '国家代码';
alter table city modify column last_update timestamp comment '更新时间';
SHOW CREATE TABLE city;
#查看表的注释
SELECT table_name as '表名',table_comment as '表注释' FROM information_schema.tables WHERE table_schema = 'sakila' AND table_name ='city';
# 查看列的注释
SHOW FULL COLUMNS FROM city;
SELECT column_name as '列名', column_comment as '列注释' FROM information_schema.columns WHERE table_schema ='sakila' AND table_name = 'city';
SELECT * FROM information_schema.columns WHERE table_schema ='sakila' AND table_name = 'city';
-- 数据库中所有表的主键和外键约束信息的Sql语句
-- 1方法
SELECT C.TABLE_SCHEMA, -- 拥有者,
C.REFERENCED_TABLE_NAME, -- 父表名称 ,
C.REFERENCED_COLUMN_NAME, -- 父表字段 ,
C.TABLE_NAME, -- 子表名称,
C.COLUMN_NAME, -- 子表字段,
C.CONSTRAINT_NAME, -- 约束名,
T.TABLE_COMMENT, -- 表注释,
R.UPDATE_RULE, -- 约束更新规则,
R.DELETE_RULE -- 约束删除规则
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE C
JOIN INFORMATION_SCHEMA. TABLES T
ON T.TABLE_NAME = C.TABLE_NAME
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
ON R.TABLE_NAME = C.TABLE_NAME
AND R.CONSTRAINT_NAME = C.CONSTRAINT_NAME
AND R.REFERENCED_TABLE_NAME = C.REFERENCED_TABLE_NAME
WHERE C.REFERENCED_TABLE_NAME IS NOT NULL ;
SELECT C.TABLE_SCHEMA 拥有者,
C.REFERENCED_TABLE_NAME 父表名称 ,
C.