MySQL增操作
MySQL增操作
一、添加数据库操作
创建数据库：create database “数据库名称” default charset utf8;
样例：create databases dbtest default charset utf8;
二、添加数据表操作
创建数据表：create table 表名(
列名 类型 not null default 1,
列名 类型 not null auto_increment,
列名 类型 not null auto_increment primary key,
)engine=innodb default charset=utf-8;
创建数据表的参数含义：null 可以为空、not null 不可以为空、default 定义默认值。
auto_increment 表示自增，是这一列的唯一标识。
primary key 是主键。表示约束(不能重复且不能为空); 加速查找。
char和varchar的区别：
char() 如果char为10，但输入的不足10，则char自己补到10.
varchar() 如果varchar为10，但输入的不足10，varchar不补到10.
char和varchar的区别是char查询快，因为是定长，varchar节省空间，因为不补充字节。
建议：数据库优化，定长在前，varchar在后。
***一个表里只能有一个自增列和一个主键。
样例：create table t1(id int not null auto_increment primary key,name char(10)) engine=innodb default charset=utf8;
含义：id是int类型。name是char字符串类型且只能有10个字符，超过10个字符只取前十个。
engine引擎有innodb支持事务。和myisam不支持事件的回滚。
数据表可以定义自增步长：
MySQL的自增步长是基于会话级别，也就是MySQL不能给单独的某张表设置步长。
但是SQLServer可以，因为SQLServer是基于基础表级别的。
会话：每次登录一次数据库，就是一次会话，也就是从mysql -u root -p 进入数据库开始，算一个会话。每个会话之间的步长可以不一样。
三、添加数据行操作
添加数据行：insert into 数据表(字段) values(要添加的数据，按字段对应);
样例：insert into t1(id,name) values(1,'george');
insert into t1(id,name) values(1,'george'),(2,'wang'); 一次添加多个值。
将B表中的数据或指定数据添加到A表中：insert into 数据库表名A(id,name) select id,name from 数据库表名B;
样例：insert into dbtestA(id,name) select id,name from dbtestB;
------ END ------
# 演示auto_increment_increment与auto_increment_offset
**创建演示表，使用auto_increment子句**
create table t1(id int not null auto_increment primary key, col varchar(20));
**插入记录**
insert into t1(col) values('robin'),('fred'),('jack'),('james');
**下面可以看到id列起始值为1，增量为1**
select * from t1;
+----+-------+
| id | col |
+----+-------+
| 1 | robin |
| 2 | fred |
| 3 | jack |
| 4 | james |
+----+-------+
**设置步长为5**
set session auto_increment_increment=5;
show variables like '%auto_incre%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 5 |
| auto_increment_offset | 1 |
+--------------------------+-------+
**再次插入记录**
insert into t1(col) values('robin'),('fred'),('jack'),('james');
**如下查询可以看到步长以5位基数发生变化**
select * from t1;
+----+-------+
| id | col |
+----+-------+
| 1 | robin |
| 6 | fred |
| 11 | jack |
| 16 | james |
+----+-------+
**设置初始值为5**
set session auto_increment_offset=5;
show variables like '%auto_incre%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 5 |
| auto_increment_offset | 5 |
+--------------------------+-------+
truncate table t1;
insert into t1(col) values('robin'),('fred'),('jack'),('james');
**下面是新的结果**
select * from t1;
+----+-------+
| id | col |
+----+-------+
| 5 | robin |
| 10 | fred |
| 15 | jack |
| 20 | james |
+----+-------+
# auto_increment_increment与auto_increment_offset取值范围
**将变量auto_increment_increment设置为0**
set session auto_increment_increment=0;
**实际值变成了1**
show variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 5 |
+--------------------------+-------+
**同样将auto_increment_offset设置为0**
set session auto_increment_offset=0;
**实际值也变成了1**
show variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
**下面尝试将2个变量设置为大于65535**
set session auto_increment_increment=65537;
set session auto_increment_offset=65537;
**其实际的值都变成了65535**
how variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 65535 |
| auto_increment_offset | 65535 |
+--------------------------+-------+
**尝试为2个变量设置为负值**
set session auto_increment_offset=-2;
set session auto_increment_increment=-5;
**下面的查询可以看出全部恢复到缺省值1**
show variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
由上可以看出2个变量只能设置为1至65535之间的整数值。
所有非正整数全部会置为缺省值1，大于65535的值会被自动置为65535。
# 全局与session级别的设置
**查看全局范围这2个变量的值**
show global variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
**下面分别设置会话（session）基本的值**
set session auto_increment_increment=5;
set session auto_increment_offset=10;
**查看会话（session）级别的值**
show session variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 5 |
| auto_increment_offset | 10 |
+--------------------------+-------+
**查看全局（global）级别的值**
show global variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
**设置全局（global）级别的值**
set global auto_increment_increment=2;
set global auto_increment_offset=3;
show global variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 2 |
| auto_increment_offset | 3 |
+--------------------------+-------+
# 已有auto_increment列值任一变量变化的情形
truncate table t1;
show variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
insert into t1(col) values('robin'),('fred'),('jack');
select * from t1;
+----+-------+
| id | col |
+----+-------+
| 1 | robin |
| 2 | fred |
| 3 | jack |
+----+-------+
set session auto_increment_increment=5;
show variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 5 |
| auto_increment_offset | 1 |
+--------------------------+-------+
insert into t1(col) values('david'),('tim'),('jerry');
select * from t1;
+----+-------+
| id | col |
+----+-------+
| 1 | robin |
| 2 | fred |
| 3 | jack |
| 6 | david |
| 11 | tim |
| 16 | jerry |
+----+-------+
New_value = auto_increment_offset+ N * auto_increment_increment
New_value1 = 1 + 1 * 5 = 6
New_value2 = 1 + 2 * 5 = 11
**下面是修改auto_increment_offset后的结果**
set session auto_increment_offset=2;
insert into t1(col) values('lewis'),('ian');
select * from t1;
+----+-------+
| id | col |
+----+-------+
| 1 | robin |
| 2 | fred |
| 3 | jack |
| 6 | david |
| 11 | tim |
| 16 | jerry |
| 22 | lewis |
| 27 | ian |
+----+-------+
这个id为22，应该是这样推算来的：max(id)+(new_offset-old_offset)+increment
也就是说变化auto_increment_offset后的第一个值为max(id)+(new_offset-old_offset)+increment之后再按步长递增。
# SqlServer：自增步长：
**基础表级别：**
CREATE TABLE `t5` (
`nid` int(11) NOT NULL AUTO_INCREMENT,
`pid` int(11) NOT NULL,
`num` int(11) DEFAULT NULL,
PRIMARY KEY (`nid`,`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=4, 步长=2 DEFAULT CHARSET=utf8
CREATE TABLE `t6` (
`nid` int(11) NOT NULL AUTO_INCREMENT,
`pid` int(11) NOT NULL,
`num` int(11) DEFAULT NULL,
PRIMARY KEY (`nid`,`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=4, 步长=20 DEFAULT CHARSET=utf8
