一、创建表

1.1 建表语句

语法

1
CREATE TABLE table_name (column_name column_type);

示例

1
2
3
4
5
6
7
CREATE TABLE IF NOT EXISTS `wno704_tbl`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `title` VARCHAR(100) NOT NULL,
   `author` VARCHAR(40) NOT NULL,
   `date` DATE,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='人员';

实例解析：

1.如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。
2.AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
3.PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。
4.ENGINE 设置存储引擎，CHARSET 设置编码。

二、查看操作

2.1 查看表名称

1
show tables;

2.2 查看表结构

1
desc tablename;

2.3 查看表详细结构语句

1
show create table tablename;

三、表字段操作

3.1 添加字段

语法

1
ALTER TABLE tb_name ADD [ COLUMN ] 新字段名数据类型 [约束条件] [FIRST | AFTER 已有字段名];

示例

1
ALTER TABLE `wno704_tbl` ADD COLUMN  `status` INT FIRST;

3.2 修改字段

语法

1
2
3
ALTER TABLE tb_name CHANGE [COLUMN] 原字段名新字段名数据类型 [约束条件];
ALTER TABLE tb_name ALTER [COLUMN] 字段名 {SET | DROP} DEFAULT;
ALTER TABLE tb_name MODIFY [COLUMN] 字段名数据类型 [约束条件] [FIRST | AFTER 已有字段名];

示例

1
2
3
ALTER TABLE `wno704_tbl` CHANGE COLUMN `status` `flag` INT;
ALTER TABLE `wno704_tbl` ALTER COLUMN `status` SET DEFAULT 0;
ALTER TABLE `wno704_tbl` MODIFY COLUMN `status` INT  AFTER `date`;

3.3 删除字段

语法

1
ALTER TABLE tb_name DROP [COLUMN] 字段名;

示例

1
ALTER TABLE `wno704_tbl` DROP COLUMN `flag`;

四、表操作

4.1 重命名表

语法

1
2
ALTER TABLE 原表名 RENAME [TO] 新表名;
RENAME TABLE 原表名1 TO 新表名1 [, 原表名2 TO 新表名2] ... ...;

示例

1
2
ALTER TABLE `wno704_tbl` RENAME TO `wno704_tb`;
RENAME TABLE `wno704_tb` TO `wno704_tbl`;

4.2 删除表

语法

1
DROP TABLE [IF EXISTS] 表1 [, 表2]...;

示例

1
DROP TABLE IF EXISTS `wno704_tbl`;

4.3 insert into select

语法

1
insert into TABLE  表1 ([COLUMN]) select [COLUMN] from 表2;

示例

1
2
3
insert into total_index_td(date,orgid,type,num,update_time)
select date_format(now(), '%Y-%m') date,p.orgid,15 type,0 num,now() update_time from entry_pianquzb_report  p where p.attr = 2 and p.delflag = 0
and not exists (select 1 from total_index_td q where q.orgid = p.orgid and q.type = 15);

4.4 注解分析

1
2
3
4
5
SELECT TABLE_NAME,COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE , COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'kdptdb'   and  TABLE_NAME  =  'entry_pianquzb_report';

ALTER TABLE kdptdb.entry_pianquzb_report MODIFY COLUMN latn int COMMENT '地市';

4.5 批量注解增加

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE , COLUMN_COMMENT,
FLOOR(RAND() * 100) + 1  as rand,
CONCAT('ALTER TABLE ',TABLE_NAME,' MODIFY COLUMN ',COLUMN_NAME,'  ', (case when CHARACTER_MAXIMUM_LENGTH is null then  DATA_TYPE else concat(DATA_TYPE,'(',CHARACTER_MAXIMUM_LENGTH,')') end),' COMMENT ''',
(
	 case when column_name = 'id' then '主键'
				when column_name like '%ids%' then '序列'
				when column_name like '%id%' then '编码'
				when column_name like '%name%' then '名称'
				when column_name like '%create_by%' then '创建人'
				when column_name like '%create_date%' then '创建时间'
				when column_name like '%update_by%' then '更新人'
				when column_name like '%update_date%' then '更新时间'
				when column_name like '%del_flag%' then '删除标识'
				when column_name like '%email%' then '邮件'
				when column_name like '%phone%' then '电话'
				when column_name like '%remark%' then '备注'
				when column_name like '%extend%' then '扩展'
				when column_name like '%time%' then '时间'
				else '1-其他字段' end
	),
''';')  sqll
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA in ('ofm_main_db')   and  COLUMN_COMMENT = ''