MySQL 常用命令

— 链接mysql -h 127.0.0.1 -P 33066 -u root -proot– 创建表CREATE TABLE `table_name` (`id` bigint unsigned AUTO_INCREMENT COMMENT ‘主键ID’,`c1` bigint NOT NULL DEFAULT ‘0’ COMMENT ‘bigint’,`c2` int NOT NULL DEFAULT ‘0’ COMMENT ‘int’,`c3` decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘decimal’,`c4` varchar(100) DEFAULT ” COMMENT ‘varchar’,`c5` text COMMENT ‘text’,`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,`modify_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘修改时间’,`deleted` tinyint(4) NOT NULL DEFAULT ‘0’ COMMENT ‘是否删除-0不删除，1软删除’,PRIMARY KEY (`id`),UNIQUE KEY `uk_c1` (`c1`),KEY `idx_create_time` (`create_time`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT=’COMMENT’;– 查看表结构show create table table_name;desc table_name;– 新增行ALTER table xx_xx add column int11 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘int11’;– int(11) 和 int 效果一样，11是显示宽度；超过11位时，该限制失效；ALTER table xx_xx add column time1 datetime not null default CURRENT_TIMESTAMPCOMMENT ‘创建时间’;ALTER table xx_xx add column varchar5 varchar(5) NOT NULL DEFAULT ” COMMENT ‘varchar5’;– 删除行ALTER TABLE xx_xx DROP COLUMN varchar10;– 新增唯一索引ALTER TABLE `xx_xx` ADD UNIQUE INDEX `uk_column_name` (`column_name`);ALTER TABLE `xx_xx` ADD CONSTRAINT `uk_column_name` UNIQUE(`column_name`);ALTER table `xx_xx` ADD UNIQUE idx_column_name (`column_name`);– 新增普通索引ALTER TABLE `xx_xx` ADD INDEX index_name ( `column_name` );ALTER TABLE `xx_xx` ADD INDEX index_name ( `c1`, `c2`, `c3` );– 删除索引ALTER TABLE xx_xx DROP INDEX ddd;– 新增数据INSERT INTO table_name ( field1, field2,…fieldN )VALUES(value1, value2,…valueN),(value1, value2,…valueN);– 事务BEGIN 开始一个事务ROLLBACK 事务回滚COMMIT 事务确认SET AUTOCOMMIT=0 禁止自动提交SET AUTOCOMMIT=1 开启自动提交