MySQL常用SQL整理

MySQL常用SQL整理

添加字段

alter table `user` add `is_super` tinyint(1) unsigned not null default '0' after `is_sys` ;
alter table `article` add `url` varchar(200) not null default '' after `source` ;

修改字段名称及类型

alter table `article` change `isshow` `is_show` tinyint(1) unsigned not null default '0' ;

修改字段类型

alter table `article` modify `title` varchar(200) not null default '' ;

删除字段

alter table `article` drop column isshow,drop column gid,drop column pid ;

创建索引

CREATE UNIQUE INDEX category_code USING BTREE ON `article` (`categoryid`,`code`);
alter table `article` add unique category_code (`categoryid`,`code`) USING BTREE;

创建索引

alter table table_name add index index_name (column_list) ;
alter table table_name add unique index_name (column_list) ;
alter table table_name add primary key (id) ;

删除索引

drop index index_name on table_name ;
alter table table_name drop index index_name ;
alter table table_name drop primary key ;

删表

drop table `user` ;

清空表

truncate table `user` ;

mysql重命名表

alter table `article` rename `article1` ;
alter table `article` rename as `article1` ;
alter table `article` rename to `article1` ;
rename table `article` to `article1` ;

创建表

CREATE TABLE `uc_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `citycode` varchar(20) NOT NULL DEFAULT '' COMMENT '城市',
  `username` varchar(100) NOT NULL DEFAULT '',
  `password` char(32) NOT NULL DEFAULT '',
  `status` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `nickname` varchar(100) NOT NULL DEFAULT '',
  `wcaid` varchar(20) NOT NULL DEFAULT '',
  `lastlogintime` bigint(10) unsigned NOT NULL DEFAULT '0',
  `lastloginip` varchar(20) NOT NULL DEFAULT '',
  `createtime` bigint(10) unsigned NOT NULL DEFAULT '0',
  `updatetime` bigint(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uni_username` (`username`) USING BTREE,
  UNIQUE KEY `uni_citycode_wcaid` (`citycode`,`wcaid`) USING BTREE,
  KEY `idx_username_password_status` (`username`,`password`,`status`),
  KEY `status` (`status`)
) ENGINE=MyISAM AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 COMMENT='用户表' ;

CREATE TABLE `adslots` (
  `id` varchar(50) NOT NULL,
  `alias` varchar(50) NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` varchar(1000) NOT NULL,
  `price` bigint(20) NOT NULL,
  `width` bigint(20) NOT NULL,
  `height` bigint(20) NOT NULL,
  `num_slots` bigint(20) NOT NULL,
  `num_auto_fill` bigint(20) NOT NULL,
  `auto_fill` text NOT NULL,
  `created_at` bigint(20) NOT NULL,
  `updated_at` bigint(20) NOT NULL,
  `version` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uni_adslot_alias` (`alias`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;

插入

insert into user (username,status) value ('u1',1) ;

批量插入

insert into user (username,status) values ('u1',1),('u2',1),('u3',1) ;

insert into stat_ip (source,citycode,tid,ip) values (2,'bj','10086','192.168.101.4'),(2,'bj','10086','192.168.101.2'),(2,'bj','10086','192.168.101.5') on duplicate key update source=values(source),citycode=values(citycode),tid=values(tid),ip=values(ip);

insert into stat_ip (source,citycode,tid,ip,ctime,num) values (2,'bj','10086','192.168.101.7',123456789,1),(2,'bj','10086','192.168.101.7',123456789,1),(2,'bj','10086','192.168.101.7',123456789,1) on duplicate key update num=num+1;