52_2MySQL【数据类型、约束、索引、视图】

day52下

MySQL

数据类型

新建查询数据类型：常用

注意sql规范，不然会报错

# 数据类型

# 整数类型 -------------------------------------------------------------------------------------------
# tinyint - 1字节 - -128~127
# int - 4字节
# 经验：存储状态码一般使用tinyint，其他的需求一般使用int就足够了

# int(3) 其中3表示数据不满3位使用0填充，实际上该字段上的数据的取值范围：-2147483648~2147483647
# unsigned -- 无符号
# zerofill -- 0填充
CREATE TABLE a (
  id int(3) unsigned zerofill NOT NULL PRIMARY KEY
) 
insert into a(id) values(1);#001
insert into a(id) values(1234);#1234

# 浮点类型 ---------------------------------------------------------------------------------------------
# float(8,2) -- 单精度浮点型
# double(8,2) -- 双精度浮点型
# decimal(8,2) -- 以字符串形式存储的小数
# (8,2)表示8位数，其中2位为小数位
# 经验：因为float和double都有可能损失精度，有精度要求的字段一般使用decimal

# 字符串类型 -------------------------------------------------------------------------------------------
# char(32) -- 定长字符串，如果存储"abc"，底层会开辟32个字符长度的空间
# varchar(32) -- 变长字符串，如果存储"abc"，底层会根据具体数据开辟空间
# (32) -- MySQL5.5之前为32字节，之后32个字符
# 注意：char和varchar取值范围为0~255字符

# BLOB -- 可以存储二进制数据的字符串类型，意味着该类型可以存储文件
# TEXT -- 可以存储长文本数据的字符串类型，意味着该类型可以存储纯文本数据
# LONGBLOB -- 可以存储极长的二进制数据
# LONGTEXT -- 可以存储极长的纯文本数据
# 注意：存储二进制文件和纯文本文件的技术叫做CBLOB -> CLOB指的是存储纯文本文件，BLOB指的是存储二进制文件
# 经验：MySQL虽然允许存储文件，但是实际开发中不会存储文件，如果要存储文件，在数据库中存储的是该文件的路径，是为了减少数据库的压力

# 日期时间串类型 -------------------------------------------------------------------------------------------
# date -- 日期类型 -- 2024-06-01
# time -- 时间类型 -- 14:32:30
# datetime -- 日期时间类型 -- 2024-06-01 14:32:30
# timestamp -- 时间戳 -- 2024-06-01 14:32:30
# year -- 年份 -- 2024

# 时间戳可以设置更新数据就更新时间
CREATE TABLE a (
  id int(3) unsigned zerofill NOT NULL PRIMARY KEY,
  str varchar(3) COLLATE utf8mb4_general_ci DEFAULT NULL,
  xxx timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

注意

无符号和零填充

时间戳；注意时间戳必须在表里添加时勾选更新日期时间，才会自动更新

详解–参考学习

在MySQL里面我们将数据类型分为了以下一些类型：

1. 数值类型（整型、浮点）

2. 字符串类型

3. 日期时间类型

4. 复合类型

整型

UNSIGNED（无符号） 主要用于整型和浮点类型，使用无符号。即，没有前面面的-（负号）。
存储位数更长。tinyint整型的取值区间为，-128~127。而使用无符号后可存储0-255个长度。

创建时在整型或浮点字段语句后接上：Unsigned

浮点类型

字符类型

时间类型

注意：

1. 时间类型在web系统中用的比较少，很多时候很多人喜欢使用int来存储时间。插入时插入的是unix时间戳，因为这种方式更方便计算。在前端业务中用date类型的函数，再将unix时间戳转成人们可识别的时间。

2. 上面的类型你可以根据实际情况实际进行选择

有些人为了在数据库管理中方便查看，也有人使用datetime

约束

新建查询约束：

# 约束：对于该字段上数据的限制

# 主键约束 -----------------------------------------------------------
# 理解：该字段上的数据不允许重复且不能为null
# 注意：一张表必须有主键，且只能有一个！！！

# 添加主键约束 -- 方式一
CREATE TABLE user(
	username VARCHAR(32) PRIMARY KEY,
	password VARCHAR(32),
	name VARCHAR(32),
	nickName VARCHAR(32),
	phone VARCHAR(32),
	sex VARCHAR(32),
	age INT(3)
)


# 添加主键约束 -- 方式二
# PRIMARY KEY(username,password)->联合主键，意味着主键是username+password加在一起的数据
CREATE TABLE user(
	username VARCHAR(32),
	password VARCHAR(32),
	name VARCHAR(32),
	nickName VARCHAR(32),
	phone VARCHAR(32),
	sex VARCHAR(32),
	age INT(3),
	PRIMARY KEY(username,password)
)

# 添加主键约束 -- 方式三
# 经验：创建表时就添加主键，方式三很少使用
CREATE TABLE user(
	username VARCHAR(32),
	password VARCHAR(32),
	name VARCHAR(32),
	nickName VARCHAR(32),
	phone VARCHAR(32),
	sex VARCHAR(32),
	age INT(3)
)
ALTER TABLE user ADD PRIMARY KEY(username);

# 删除主键约束
ALTER TABLE user DROP PRIMARY KEY;

# 唯一约束 -----------------------------------------------------------
# 理解：该字段上的数据不允许重复，但可以为null

# 添加唯一约束
ALTER TABLE user ADD UNIQUE(phone);

# 删除唯一约束
ALTER TABLE user DROP index phone;


# 非空约束 -----------------------------------------------------------
# 理解：该字段上的数据不允许为null，但可以重复

# 添加非空约束
ALTER TABLE user MODIFY password VARCHAR(32) NOT NULL; 

# 删除非空约束
ALTER TABLE user MODIFY password VARCHAR(32) NULL; 

# 添加默认值
ALTER TABLE user MODIFY password VARCHAR(32) DEFAULT '000000'; 


# 外键约束 -----------------------------------------------------------
# 理解：保证两个字段之间参照完整性

# 创建学科表,并插入数据
CREATE TABLE course(
	id INT(3) PRIMARY KEY auto_increment,
	name VARCHAR(32)
)
INSERT into course(name) VALUES('Java');
INSERT into course(name) VALUES('Python');
INSERT into course(name) VALUES('HTML');

# 创建学生表
# 创建学科表,并插入数据
CREATE TABLE student(
	id INT(3) PRIMARY KEY auto_increment,
	name VARCHAR(32),
	c_id INT(3)
)

# 添加外键约束
alter table student add foreign key(c_id) references course(id);

# 删除外键约束
#需要外键名
alter table student drop foreign key student_ibfk_1;

#通过获取创建表信息查看外键名
show create table student;
CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `c_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c_id` (`c_id`),
  CONSTRAINT `student_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `course` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

# 经验：外键已被淘汰，效率太低了，因为添加、修改数据时，MySQL都会去外键关联的表中查询是否有脏数据
# 外键的优点：不会出现脏数据
# 外键的缺点：效率低

# 如果不适用外键，如何避免脏数据呢？
# 在页面选择数据，不会乱填的！
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114

注意

联合主键

默认值，非空约束常用

外键；注意有外键情况下选择有限制，乱输入会报错；有脏数据的情况下加不了外键

使用数据类型要考虑实际情况，如手机号可以定长11，也可以变长32，因为外国手机号长不一定为11
注意联合主键，是联合在一起为主键，不是分别为主键；因此其中一个数据可以出现重复

后加主键比较少

添加外键约束，避免出现脏数据，效率低；实际来看，限制情况对于乱存的脏数据不会出现

索引

新建查询索引：

# 索引
# 理解：索引相当于一本书的目录，让查询更快
# 注意：索引上的数据作为查询条件，速度会更快


# 主键索引 ---------------------------------------------------------------------------
# 注意：主键作为约束，该字段上的数据不能重复也不能为null，作为索引，查询会更快

# 添加主键索引 -- 方式一
CREATE TABLE user(
	username VARCHAR(32) PRIMARY KEY,
	password VARCHAR(32),
	name VARCHAR(32),
	nickName VARCHAR(32),
	phone VARCHAR(32),
	sex VARCHAR(32),
	age INT(3)
)


# 添加主键索引 -- 方式二
# PRIMARY KEY(username,password)->联合主键，意味着主键是username+password加在一起的数据
CREATE TABLE user(
	username VARCHAR(32),
	password VARCHAR(32),
	name VARCHAR(32),
	nickName VARCHAR(32),
	phone VARCHAR(32),
	sex VARCHAR(32),
	age INT(3),
	PRIMARY KEY(username,password)
)

# 添加主键索引 -- 方式三
# 经验：创建表时就添加主键，方式三很少使用
CREATE TABLE user(
	username VARCHAR(32),
	password VARCHAR(32),
	name VARCHAR(32),
	nickName VARCHAR(32),
	phone VARCHAR(32),
	sex VARCHAR(32),
	age INT(3)
)
ALTER TABLE user ADD PRIMARY KEY(username);

# 删除主键索引
ALTER TABLE user DROP PRIMARY KEY;

# 唯一索引 ---------------------------------------------------------------------------
# 注意：唯一作为约束，该字段上的数据不能重复，作为索引，查询会更快

# 添加唯一索引
ALTER TABLE user ADD UNIQUE(phone);

# 删除唯一索引
ALTER TABLE user DROP index phone;

# 普通索引 ---------------------------------------------------------------------------
# 注意：普通索引没有任何数据上的约束，作为索引，查询会更快

# 添加普通索引
ALTER TABLE user ADD index(nickName);

# 删除普通索引
drop index nickName on user;

# 全文索引 ---------------------------------------------------------------------------
# 注意：项目中不同，如果要做全文索引会使用ES(ElasticSearch)框架

# 添加全文索引
# ngram 中文、日文、韩文的全文解析器，如果不加则只能解析英文
ALTER TABLE news ADD FULLTEXT(info) WITH PARSER ngram;

# 全文搜索
SELECT * FROM news WHERE MATCH(info) against('胡歌很帅');

# 删除全文索引
drop index info on news;

# 面试题：索引为什么作为条件会更快？
# 因为索引底层使用B+Tree的数据结构，会让查询效率更高

# 面试题：索引会让该字段作为条件查询更快，为什么创建表时不会每个字段都加上索引呢？
# 考点：索引的优缺点
# 优点：该字段作为条件查询更快
# 缺点：添加、删除数据时，索引的数据结构发生改变
# 经验1：会把经常作为查询条件的字段设置为索引
# 经验2：偶尔添加或删除索引字段上的数据直接无视，批量添加或删除索引字段上的数据，可以先把索引删除后，等批量操作完再添加上索引

# 面试题：索引的分类？
# 聚簇索引(聚集索引)和非聚簇索引(非聚集索引)

# 面试题：聚簇索引和非聚簇索引的区别
# 聚簇索引(主键索引)：叶子结点上存放的是数据行的数据，效率更高
# 非聚簇索引(唯一、普通、全文索引)：叶子节点上存放的是数据行的地址
# 经验：select 字段 FROM 表名 WHERE 主键字段=xxx;

# 面试题：索引失效的情况
# 1.最短路径算法（是走索引查询更快，还是顺序查找更快）
# 2.最左匹配原则
# 3.使用模糊查询
# 4.使用函数
# 5.使用OR
# .....

# 提升作业：B+Tree和BTree的区别


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109

注意

全文索引

B+Tree和BTree的区别

【博客搜索相关内容：B+Tree 索引；MySQL高级——MySQL中BTree索引结构与检索原理；B+Tree和BTree的区别…】
对于

结构: 数据 向下的指针 指向数据的指针
结构: 数据 向下的指针
从结构上看,B+Tree 相较于 B-Tree 而言 缺少了指向数据的指针 也就红色小方块

在BTree中每个节点保留了关键字（表的主键）、数据信息（表数据）、子节点内存地址信息。

而B+Tree中非叶子节点只保留了关键字以及子节点的内存地址信息，而叶子节点中还保留了数据信息，并且叶子节点之间以链表形式组织（如mysql的innodb以双向链表组织叶子节点）。

对于mysql

1. 保存同样数据B树的高度通常比B+树高，那么查询效率低
2. B+叶子节点双向链表范围查询效率更高
3. B树数据和索引等在一块，索引遍历时，意味着更多的磁盘IO

视图

新建查询索引：

# 视图：虚拟表

# 创建移动公司的用户表
CREATE TABLE user(
	username VARCHAR(32) PRIMARY KEY,
	password VARCHAR(32),
	name VARCHAR(32),
	type INT(3),
	province VARCHAR(32),
	city VARCHAR(32),
	phone VARCHAR(32),
	age INT(3)
)

# 创建视图
#语法：create [or replace] view 视图名 as select 字段名 from 表名 where 条件 [with check option]
# or replace 如果视图名相同就替换
# with check option修改数据时如果违反条件就不允许修改
create or replace view view01 as select phone,province,city,age from user where age < 40 with check option;

# 查询视图数据
#语法：select * from 视图名
SELECT * FROM view01;

# 修改视图数据
#语法：update 视图名 set 字段=”值”
UPDATE view01 SET age=41 WHERE phone='13993300090';

# 删除视图数据
DELETE FROM view01 WHERE phone='13993300090';

# 删除视图
#语法：drop view 视图名
DROP view view01;

# 注意：修改虚拟表，原表也会发生改变
# 解决：创建子账号，子账号只有查询权限

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

视图含义：虚拟表，和普通表一样使用，mysql5.1版本出现的新特性，是通过表动态生成的数据

理解：对于一个公司不会把原用户表数据直接给其他公司，但也会有做给个部分数据虚拟表给需求公司

详解–参考学习

什么是视图
在计算机科学和数据库领域，视图（View）是一种虚拟表，它基于一个或多个实际表（或其他视图）的查询结果。视图并不在数据库中存储实际的数据，而是根据需要动态生成。视图允许用户以一种特定的方式查看数据库中的数据，同时隐藏了底层表的复杂性。

视图的主要作用包括：
1.数据安全性： 视图可以限制用户对数据库中数据的访问权限。通过只允许用户访问视图而不是直接访问表，可以确保用户只能看到他们被授权查看的数据。
2.简化复杂性： 数据库中的表可能包含大量的列和行，而有时用户只关心表中的某一部分数据。通过创建视图，可以将复杂的查询和过滤操作封装在一个视图中，用户只需关注视图的结构而不必处理底层表的复杂性。
3.数据独立性： 视图提供了一种逻辑数据独立性，允许数据库管理员更改底层表的结构而不影响用户对视图的访问。这使得数据库的维护更加灵活，而不会破坏已有的查询和报表。
4.简化查询： 视图可以用于封装复杂的查询，使得用户可以通过简单的查询访问所需的数据。这样的封装也有助于提高查询的可维护性和可重用性。
5.聚焦业务逻辑： 通过创建特定的视图，可以使数据库中的数据更符合业务需求。视图允许将数据呈现为业务实体，而不仅仅是底层表中的原始数据。

创建视图的语法通常包括对一个或多个表进行查询，然后将查询结果保存为一个虚拟表。视图可以被其他查询使用，就好像它是一个实际存在的表一样。