为什么mysql按id快

文章目录

• DDL

• 一、库的管理

• 1. 库的创建
• 2. 库的修改
• 3. 库的删除

• 二、表的管理

• 1. 表的创建
• 2. 表的修改
• 3. 表的删除
• 4. 表的复制

• 三、常见的数据类型

• 1. 整型
• 2. 浮点型(float和double)
• 3. 定点数
• 4. 字符串(char, varchar, text)
• 5. 二进制数据(_Blob)
• 6. 日期时间类型
• 7. 数据类型的属性

• 四、常见约束

• 1. 六大约束
• 2. 添加时机

• 五、标识列 // 自增长列

DDL

数据定义语言 （Data Definition Language）
常用于：库和表的管理

• 创建 - CREATE
• 修改 - ALTER
• 删除 - DROP

一、库的管理

1. 库的创建

语法： CREATE DATABASE [ IF NOT EXISTS ] 库名；

2. 库的修改

语法：🈚️ 一般来说不修改，容易丢失数据
更改库的字符集：ALTER DATABASE 库名 CHARACTER SET gbk；
默认是UTF-8。
RENAME语句：5.1.7到5.1.23版本可以用的，但是官方不推荐，会有丢失数据的危险。现已移除。

3. 库的删除

语法：DROP DATABASE 库名；

二、表的管理

1. 表的创建

语法： CREATE TABLE [ IF NOT EXISTS ] 表名（
    列名 列的类型  [（长度）约束 ]
    列名 列的类型  [（长度）约束 ]
    …
    列名 列的类型  [（长度）约束 ]
）；

2. 表的修改

语法：ALTER TABLE 表名 ADD/DROP/MODIFY/CHANGE 列名 [ 列类型 约束]

修改列名
ALTER TABLE 表名 CHANGE COLUMN   旧列名 新列名 [ 类型 ];

修改列的类型或约束
ALTER TABLE 表名 MODIFY COLUMN 列名 新类型；;

添加新列
ALTER TABLE 表名 ADD COLUMN 列名 [ 类型 ];

删除列
ALTER TABLE 表名 DROP COLUMN 列名;

修改表名
ALTER TABLE 旧表名 RENAME TO 新表名;

3. 表的删除

语法：DROP TABLE 表名;

4. 表的复制

只复制表的结构：
CREATE TABLE 表2 LIKE 表1;

复制全部结构+数据
CREAT TABLE 表3 SELECT  *   FROM 表1;

只复制部分数据
CREAT TABLE 表4
SELECT  列1，列2，...
FROM 表1
WHERE 条件1，...;

只复制部分结构
CREAT TABLE 表5
SELECT  列1，列2，...
FROM 表1
WHERE 0 [ false ];

---

三、常见的数据类型

主要有以下五大类：
整数类型： TINY INT、SMALL INT、NEDIUM INT、INT/INTEGER、BIG INT
浮点数类型： FLOAT、DOUBLE、DECIMAL
字符串类型： CHAR、VARCHAR、TINY INT、TEXT、MEDIUM TEXT、 LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB
日期类型： Date、DateTime、TimeStamp、Time、Year
其他数据类型： BINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等等

---

binary和varbinary用于保存较短的二进制
enum用于保存枚举
set用于保存集合

---

***SET类型：和Enum类型类似，可以保存0-64个成员。和Enum类型的最大区别是：set类型一次可以选取多个成员，而Enum只能选一个
根据成员个数不同，存储所占字节也不同

成员数	字节数
1～8	1
9~16	2
17~24	3
25~32	4
33~64	8

---

1. 整型

MySQL数据类型	含义（符号）
tinyint(m)	1个字节 范围(-128~127)
smallint(m)	2个字节 范围(-32768~32767)
mediumint(m)	3个字节 范围(-8388608~8388607)
int(m)	4个字节 范围(-2147483648~2147483647)
bigint(m)	8个字节 范围(±9.22*10的18次方)

取值范围如果加了unsigned，则最大值翻倍，如tinyint unsigned的取值范围为(0~256)。

int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，没有影响到显示的宽度。

---

2. 浮点型(float和double)

MySQL数据类型	含义
float(m,d)	单精度浮点型 8位精度(4字节) m总个数，d小数位
double(m,d)	双精度浮点型 16位精度(8字节) m总个数，d小数位

设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位。整数部分最大是3位，如果插入数12.123456，存储的是12.1234，如果插入12.12，存储的是12.1200.

---

3. 定点数

MySQL数据类型	含义
DEC(m,d)	M+2字节
DECIMAL(m,d)	M+2字节

最大取值范围与double相同，给定decimal的有效取值范围由M和D决定

特点：

• m：整数部分+小数部分
• d：小数部分
• 如果超过范围，则插入临界值
• m、d可以省略
• 如果是float和double，则回根据插入的数值的进度来决定精度
• 定点型的精准度较高，如果要求插入数值的精度较高如货币运算等则考虑使用

原则：所选择的类型越简单越好，能保存数值的类型越小越好

---

4. 字符串(char, varchar, text)

MySQL数据类型	含义
char(n)	固定长度，最多255个字符
varchar(n)	固定长度，最多65535个字符
tinytext	可变长度，最多255个字符
text	可变长度，最多65535个字符
mediumtext	可变长度，最多2的24次方-1个字符
longtext	可变长度，最多2的32次方-1个字符

	char	varchar
(m)	可以省略，默认为1	不可省略
特点	固定长度的字符	可变长度的字符
空间	比较耗费	比较节省
效率	高	底

---

5. 二进制数据(_Blob)

1._BLOB和_text存储方式不同，_TEXT以文本方式存储，英文存储区分大小写，而_Blob是以二进制方式存储，不分大小写。

2._BLOB存储的数据只能整体读出。

3._TEXT可以指定字符集，_BLO不用指定字符集。

---

6. 日期时间类型

MySQL数据类型	含义
date	日期 ‘2008-12-2’
time	时间 ‘12:25:36’
datetime	日期时间 ‘2008-12-2 22:06:44’
timestamp	自动存储记录修改时间

特点

1. Timestamp支持的时间范围较小，取值范围：19700101080001 ～ 2038年某个时间
   Datetime的取值范围：1000-1-1 ～ 999-12-31
2. timestamp和实际时区油有关，更能反应实际的日期。而datatime只能反映出插入时的当地时区
3. timestamp的属性受Mysql版本和SQLMode的影响很大
4. timestamp这个字段里的时间数据会随其他字段修改的时候自动刷新，所以这个数据类型的字段可以存放这条记录最后被修改的时间。

---

7. 数据类型的属性

MySQL数据类型	含义
NULL	数据列可包含NULL值
NOT NULL	数据列不允许包含NULL值
DEFAULT	默认值
PRIMARY KEY	主键
AUTO_INCREMENT	自动递增，适用于整数类型
UNSIGNED	无符号
CHARACTER SET name	指定一个字符集

---

四、常见约束

含义：一种限制，用于限制表中的数据，为了保证表中的数据等准确和可靠性

1. 六大约束

约束	含义
NOT NULL	非空
DEFAULT	默认值
PRIMARY KEY	主键，保证该字段段值具有唯一性，且非空
FOREIGN KEY	外键，限制俩个表的关系，值必须来自主表
UNIQUE	唯一，保证唯一性，可以为空
CHECK	检查约束[ MySQL不支持 ]

2. 添加时机

1. 创建表时
2. 修改表时

约束的添加分类：
1. 列级约束：都支持，但外键约束没有效果，外键不可起名
2. 表级约束：处了非空、默认，其他都支持，外键可以起名

一、创建表时添加约束：
	语法：直接在字段名和类型后面追加 约束类型即可
		 只支持：默认、非空、主键、唯一

CREATE TABLE 表名1(
	id INT PRIMARY KEY,			
	Name VARCHAR(20) NOT NULL,	
	gender CHAR(1)，
	seat INT UNIQUE,			
	majorID INT FOREIGN KEY REFERENCES 表名2(id) 
);

CREATE TABLE 表名2(
	id INT PRIMARY KEY,
	majorName VARCHAR(20)
);

添加表级约束
	语法：在各个字段最下面
	[ constraint ] 约束名 约束类型（字段名）
CREATE TABLE 表名1(
	id INT,	
	Name VARCHAR(20),
	gender CHAR(1),
	seat INT UNIQUE,			
	majorID INT,
	[ constraint ] pk PRIMARY KEY(id),
	[ constraint ] uq UNIQUE(seat),
);

CREATE TABLE 表名2(
	id INT PRIMARY KEY,
	majorName VARCHAR(20),
	[ constraint ] fk_从表名_主表名 FOREIGN KEY(从表列名) REFERENCES 主表(id)
);

一般写法：
CREATE TABLE 表名1(
	id INT PRIMARY KEY,			
	Name VARCHAR(20) NOT NULL,	
	gender CHAR(1)，
	seat INT UNIQUE,			
	majorID INT，
	[ constraint ] fk_从表名_主表名 FOREIGN KEY(从表列名) REFERENCES 主表(id) 
);

主键和唯一的对比：

	唯一性	是否为空	数量	组合
主键	Y	N	ONLY	Y 但不推荐
唯一	Y	Y	Many	Y 但不推荐

外键的特点：

1. 要求从表中设置外键关系
2. 从表的外键列的类型和主标的关联列数据类型要求一致或兼容，名称无所谓
3. 主表中的关联列必须是个key( 一般是主键或唯一 )
4. 插入数据时，先插入主表，再插入从表
5. 删除数据时，先删除从表，再删除主表

二、修改表时添加约束
语法:
1.添加列级约束：alter table 表名 modify column 字段名 字段类型 新约束；
2.添加表级约束：alter table 表名 add [constraint 约束名] 约束类型(字段名) [外键的引用]
CREATE TABLE 表名1(
	id INT,			
	Name VARCHAR(20),	
	gender CHAR(1)，
	seat INT UNIQUE,			
	majorID INT
);
1.添加非空约束
ALTER TABLE 表名1 MODIFY COLUMN Name VARCHAR(20) NOT NULL;

2.添加默认约束
ALTER TABLE 表名1 MODIFY COLUMN age INT DEFAULT;

3.添加主键
列级：ALTER TABLE 表名1 MODIFY COLUMN id INT PRIMARY KEY;
表级：ALTER TABLE pk_表名 ADD PRIMARY KEY(列名);

4.添加唯一
ALTER TABLE 表名1 MODIFY COLUMN seat INT UNIQUE;
ALTER TABLE 表名1 ADD UNIQUE(seat);

5.添加外键
ALTER TABLE 表名1 ADD CONSTRAINT fk_从表名_主表名 FOREIGN KEY(从表列名) REFERENCES 主表(列名);

---

三、修改表时删除约束
1.删除非空约束
ALTER TABLE 表名1 MODIFY COLUMN s tuname VARCHAR(20) NULL;

2.删除默认约束
ALTER TABLE 表名1 MODIFY COLUMN age INT ;

3.删除主键
ALTER TABLE 表名1 DROP PRIMARY REY;

4.删除唯一
ALTER TABLE 表名1 DROP INDEX seat；

5.删除外键
ALTER TABLE 表名1 DEOP FOREIGN KEY fk_stuinfo_major；

---

五、标识列 // 自增长列

含义：可以不用手动的插入值，系统提供默认的序列值
特点：

1. 必须和key搭配
2. 一个表至多一个
3. 只能是数值型
4. 可以通过auto_increment_increment = 3； 设置步长
   可以通过手动插值来设置起始值

一、建表时设置标识列
CREATE TABLE 表1(
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20)
);

INSERT INTO 表1 VALUES(NULL,'JOJO');
Insert into 表1(name) values ('MUDA');

设置变量：
SET auto_increment_increment = 3;

二、修改表时设置标识列
ALTER TABLE 表1 MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

三、删除标识列
ALTER TABLE 表1 MODIFY COLUMN id INT;