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名}';