MySQL表操作

1.查看当前数据库下有那些表

-- 查看schema下表清单show tables;show full tables from ${schema名};-- 查看表详细信息select * from information_schema.tables where table_schema = '${Schema名}' and table_name = '${表名}';

2.创建表

MySQL_V8.0建表官网参考文档

create table ${表名} { ${字段名1} ${字段类型及长度} ${null/not null} default ${默认值} common '${字段注释}', ${字段名2} ${字段类型及长度} ${null/not null} default ${默认值} common '${字段注释}', ... ${字段名n} ${字段类型及长度} ${null/not null} default ${默认值} common '${字段注释}' primary key(${主键字段列表})} ENGINE=${引擎类型} DEFAULT CHARSET=${表编码};-- For example:create table zhoujl_test1{ id int not null common 'ID', name varchar(600) not null common '姓名', birthday date not null common '出生日期', sex varchar(1) not null common '性别', salary double(19,2) null default 0.0 common '收入', primary key(id)} ENGINE=InnoDB DEFAULT CHARSET=utf8;

3.修改表名

rename table ${原表名} to ${新表名};ALTER TABLE t1 RENAME t2;

4.增加字段

alter table ${表名} add column ${字段1表达式}, add column ${字段2表达式}, add column ${字段n表达式};-- For example:CREATE TABLE t1 (c1 INT);ALTER TABLE t1 ADD COLUMN c2 INT;

5.修改字段

alter table ${表名} modify column ${字段1表达式}, modify column ${字段2表达式}, modify column ${字段n表达式};-- For example:alter table t2 modify column c4 varchar(20), modify column c3 int;

6.字段重命名

alter table ${表名} change ${原字段名} ${新字段表达式};-- For example:alter table t2 change c3 c5 int;

7.删除字段

alter table ${表名} drop column ${字名1}, drop column ${字段2};-- For example:alter table t2 drop c5;

8.字段信息查看

show full columns from ${表名};select * from information_schema.columns where table_schema = '${schema名}' and table_name = '${表名}';

9.查看没有主键的表

select table_schema, table_name from information_schema.tables where table_name not in (select distinct table_name       from information_schema.columns       where column_key = "PRI") AND table_schema not in  ('mysql', 'information_schema', 'sys', 'performation_schema') and table_schema = '${schema名}';

MySQL表操作Amazon数据化运营锻造精品、 慧聪商务网、 heap、 2020 上半年热门关键词盘点、 亚马逊同行恶搞愈发猖狂！单身宝妈被逼走投无路……、 Wish FBS项目全解读，不懂的看这里！、 厦深铁路有多长？途径几个站？、 厦深铁路是高铁吗？、 2017年情人节和元宵节是同一天吗？、