mysql 常用sql脚本汇总

mysql 常用sql脚本汇总

@[toc]

mysql的常用脚本有哪些呢?我们来做个总结

建表语句

  1. sql语句最后指定主键
DROP TABLE IF EXISTS `user_info1`;
CREATE TABLE `user_info1` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '姓名',
  `password` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '密码',
  `deleted` int(1) DEFAULT '0' COMMENT '0未删除  1删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
  1. 建表主键后指定主键
DROP TABLE IF EXISTS `user_info2`;
CREATE TABLE `user_info2` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键' PRIMARY KEY,
  `name` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '姓名',
  `password` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '密码',
  `deleted` int(1) DEFAULT '0' COMMENT '0未删除  1删除'
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
  1. 建表之后指定主键
drop table if exists `user_info3`;
create table `user_info3` (
  `id` int(11) NOT NULL COMMENT '主键',
  `name` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '姓名',
  `password` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '密码',
  `deleted` int(1) DEFAULT '0' COMMENT '0未删除  1删除'
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
alter table user_info3 add primary key(id);

注⚠️:主键才可以自增,这里需要先去掉AUTO_INCREMENT,不然此处sql 从上向下执行会报错!

如果需要设置可以执行以下脚本

/*设置自增*/
alter table `user_info3` modify id int(11) auto_increment;
/*修改初始自增值*/
alter table `user_info3` auto_increment=3;

复制表

/* 复制表结构 */
create table `user_info1_back` like `user_info1`;
/* 备份表数据  生产上线前对于改动的表都需要执行此备份操作*/
insert into `user_info1_back` select * from `user_info1`;

新增

  1. 新增单条数据:
INSERT INTO `user_info1`
(name,password)
VALUES
('张三','123456');
  1. 新增多条数据:
INSERT INTO `user_info1`
(name,password)
VALUES
('A','123456'),
('B','123456')
('C','123456');

3.新增表字段

/* 增加字段 在指定表字段后新增,建议所有的新增脚本都这样精确标明位置*/
alter table `user_info1` add sex int(1) NOT NULL DEFAULT "1" comment "0女,1男" after password;

删除

  • 删除表
  1. 删除表和数据,不可逆
drop table `user_info1` if exists `user_info1`;
  1. 删除整个表数据,不可逆
TRUNCATE table `user_info1`;
/* TRUNCATE 返回 0 或 -1  删除成功返回0  删除失败返回-1*/
  1. 删除数据 可加where 条件删除部分数据,可逆
delete from `user_info1` where  id = 1 ;
/* delete 返回删除了多少条数据*/
  • 删除字段
alter table `user_info1` drop password;

修改

  • 修改表名
alter table `user_info1` rename to `user_info_1`;
-- 或者
alter table `user_info_1` rename AS `user_info1`;
/* 修改表注释*/
alter table `user_info1` comment '用户信息 表1';
  • 修改表字段
/* 修改字段 名称*/
alter table `user_info1` change column name user_name varchar(20) DEFAULT null COMMENT '姓名';
/* 修改字段 长度*/
alter table `user_info1` modify sex int(2) DEFAULT "1" comment '0男,1女';
/* 修改字段 默认值 (若本身存在默认值,则先删除;若本身不存在则可以直接设定)*/
alter table `user_info1` alter column sex drop DEFAULT;
alter table `user_info1` alter column sex set DEFAULT "0";
/* 修改字段 备注*/
alter table `user_info1` modify column sex int(1) DEFAULT "1" comment '0男,1女';
/* 修改字段 位置*/
alter table `user_info1` modify column sex int(1) DEFAULT "1" COMMENT '0男,1女' after user_name;

注⚠️:modify 操作字段时 需要带上原有的字段属性(长度,类型,默认值,注释),不然可能会造成不必要的丢失!

小结:mysql 的基本增删改就总结到这里(查就没太多好说的了),后面如果还有需要增加的会继续追加进来!