MySQL常用操作总结

常用数据表操作：

虽然有DBA的存在，但PD(program designer)仍然需要需要知道SQL常用操作
================================================================================================================================================
查询表中注释信息，该操作可通过工具完成，但没有在控制窗口显示直观
================================================================================================================================================

show full columns from table_name ;

或

show full fields from test ;

================================================================================================================================================
建表
================================================================================================================================================

1.通过工具添加列名，数据类型，数据长度，
 
  是否可空（若空给予默认值），

  主键（bigint类型主键，自增，varchar类型不自增），

  添加注释（一定不要忘记，注释与po类型中的注释、common-util常量值  保持一致，以免误会）

  字符集 ：utf-8

  排序规则：utf8_general_ci

  注意事项：

  列名：

  单词加下划线形式  如：impl_product_data

  不要出现大写字母，查询操作时不方便

举例：
DBA：删除操作不需要添加

DROP TABLE IF EXISTS `fin_biz_exception`;

CREATE TABLE `fin_biz_exception` (
  `fin_ext_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `biz_param1` varchar(50) DEFAULT '' COMMENT '业务参数1',
  `biz_param2` varchar(50) DEFAULT '' COMMENT '业务参数2',
  `biz_type` tinyint(4) NOT NULL COMMENT '业务类型',
  `exception` varchar(200) DEFAULT '' COMMENT '异常信息',
  `remark` varchar(50) DEFAULT '' COMMENT '备注',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`fin_ext_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

comment 注释
若该列为空不用显示声明 为 null 可缺省 ，默认为null，若为空一定要给出默认值
另外，建立表时，若非特殊字段都设为可空

2.建表语句

执行命令：

show create table talbe_name ;

即可获得
================================================================================================================================================
修改
================================================================================================================================================
1.添加列

语法：

alter table table_name add column col_name type null default null commont '' ;

举例：

alter table pet add 'weight' int null default null comment '重量';

2.删除列

语法：

alter table table_name drop column col_name

举例：

alter table student drop column weight ;

3.修改列

语法：

alter table tbl_name modify column col_name type  

若有comment，执行上面的命令会去掉原有的注释

alter table tbl_name change column old_col_name col_name type

该操作可避免去掉comment
建议：
修改列名称时语句都要带有comment ''，这样保险一点

3.1 设置默认值   

举例：

alter table usr_accounts_bak change id id bigint(20) null default null COMMENT '主键' ;

3.2 修改数据类型

举例：

ALTER TABLE ord_order_snapshot_item CHANGE `attr_name` `attr_name`  VARCHAR(1000) NULL COMMENT '属性name'; 

3.3 更改列名称

举例：

alter table pet change weight wei;

3.4 添加字段
（1）说明在哪个字段后
举例：

alter table fin_order_main  add column  order_id    bigint comment '订单ID'  after fin_type ;

（2）DBA要求：
出于后期数据同步考虑，Mysql添加字段请不要手动指定顺序，默认加在最后一列。
错误sql：

ALTER TABLE `account`
ADD COLUMN `nickname` VARCHAR(50) NULL DEFAULT NULL AFTER `username`;

正确sql：

ALTER TABLE `account`
ADD COLUMN `nickname` VARCHAR(50) NULL DEFAULT NULL;

3,5 去掉默认值

举例：

ALTER TABLE product_sku ALTER retail_price DROP DEFAULT;

================================================================================================================================================
插入
================================================================================================================================================

LOCK TABLES `usr_testuser` WRITE;
 
INSERT INTO `usr_testuser` VALUES (457),(459),(460),(461),(462),(463),(464),(465),(466),(467),(479),(485),(486);
 
UNLOCK TABLES;

插入数据 要先将表锁定 避免同时操作

================================================================================================================================================
其他
================================================================================================================================================

1.查看表结构

desc table_name 

2.给表更名

语法：

alter table tbl_name rename new_tbl

举例：

alter table pet rename animal;

3.修改插入操作的ID自增值
设置 插入数据表中的数据的主键id从几开始自增

alter table test AUTO_INCREMENT  = 2 ;

注：若一不小心，在po.setUid(XXX) 一不小心变成了 po.setId(XXX) 即
原本想修改某个ID值却变成了修改主键的值，主键自增啊，万一主键从1自增到那个值怎么办
会不会发生冲突，数据表异常

答：不会，一定主键的值发生变化，那么 AUTO_INCREMENT 就会发生变化，下一个值从该主键+1开始添加，即新添加的数据会从当前最大的ID往下执行插入

4.查询表中存在的重复数据

-- 查询表中重复数据，列名称
select col001,sum(1) as ufo from lhsjb group by col001 having sum(1) > 1