学习java的日子 Day52 多表联合查询，DCL，数据类型，约束，索引，视图

Day52

1.DML-多表联合查询(重要)

1.1 一对一情况

略

1.2 一对多情况

当需要查询多个表中的字段时，就可以使用表连接来实现。表联接分为内连接和外连接

内连接：将两个表中存在联结关系的字段符合联结关系的那些记录形成记录集的联结

外连接：会选出其他不匹配的记录，分为左连接和右连接

创建场景

创建老师表，并插入数据

CREATE TABLE teacher(id INT(3) PRIMARY KEY auto_increment,name VARCHAR(32)
)

INSERT INTO teacher(name) VALUES('何老师');
INSERT INTO teacher(name) VALUES('苍老师');
INSERT INTO teacher(name) VALUES('波多老师');

创建学生表，并插入数据

CREATE TABLE student(id INT(3) PRIMARY KEY auto_increment,name VARCHAR(32),t_id INT(3)
)

INSERT INTO student(name,t_id) VALUES('喻平',1);
INSERT INTO student(name,t_id) VALUES('马智威',1);
INSERT INTO student(name,t_id) VALUES('牛西燕',1);
INSERT INTO student(name,t_id) VALUES('李林俊',2);
INSERT INTO student(name,t_id) VALUES('陈宽朗',2);
INSERT INTO student(name) VALUES('陈俊豪');

1.2.1 内链接

select 表1.字段 [as 别名],表n.字段 from 表1 [别名],表n where 条件;

select 表1.字段 [as 别名],表n.字段 from 表1 INNER JOIN 表n on 条件;

方式一

student s：学生表用s代替

s.t_id=t.id：学生表的老师id等于老师表的id

SELECT s.name,t.name FROM student s,teacher t WHERE s.t_id=t.id;

方式二

SELECT s.name AS '学生姓名',t.name AS '老师姓名' FROM student s INNER JOIN teacher t ON s.t_id=t.id;

1.2.2 外连接

左连接 LEFT JOIN：针对于左边表去查询，会展示左边表中的所有数据

select 表1.字段 [as 别名],表n.字段 from 表1 LEFT JOIN 表n on 条件;

SELECT s.name,t.name FROM student s LEFT JOIN teacher t ON s.t_id=t.id;

右链接 RIGHT JOIN：针对于右边表去查询，会展示右边表中的所有数据

select 表1.字段 [as 别名],表n.字段 from 表1 right JOIN 表n on 条件;

SELECT s.name,t.name FROM student s RIGHT JOIN teacher t ON s.t_id=t.id;

1.2.3 全连接 UNION

概念：多张表联合查询，多张表没有联系的数据都查询出来

注意：MySQL没有全连接的SQL语句，实现全连接的思想就是合并查询结果 + 去重

合并查询结果

SELECT s.name,t.name FROM student s LEFT JOIN teacher t ON s.t_id=t.id
UNION ALL
SELECT s.name,t.name FROM student s RIGHT JOIN teacher t ON s.t_id=t.id;

合并查询结果 + 去重

SELECT s.name,t.name FROM student s LEFT JOIN teacher t ON s.t_id=t.id
UNION
SELECT s.name,t.name FROM student s RIGHT JOIN teacher t ON s.t_id=t.id;

1.3 多对多情况

多对多，会有一张中间表作为媒介

创建场景

创建学生表，并插入数据

CREATE TABLE student(id INT(3) PRIMARY KEY auto_increment,name VARCHAR(32)
)

INSERT INTO student(name) VALUES('喻平');
INSERT INTO student(name) VALUES('马智威');
INSERT INTO student(name) VALUES('牛西燕');

创建学科表，并插入数据

CREATE TABLE course(id INT(3) PRIMARY KEY auto_increment,name VARCHAR(32)
)

INSERT INTO course(name) VALUES('语文');
INSERT INTO course(name) VALUES('数学');
INSERT INTO course(name) VALUES('英语');

创建成绩表，并插入数据

CREATE TABLE scores(id INT(3) PRIMARY KEY auto_increment,s_id INT(3),c_id INT(3),score FLOAT(4,1)
)

INSERT INTO scores(s_id,c_id,score) VALUES(1,1,90);
INSERT INTO scores(s_id,c_id,score) VALUES(1,2,91);
INSERT INTO scores(s_id,c_id,score) VALUES(1,3,92);
INSERT INTO scores(s_id,c_id,score) VALUES(2,1,93);
INSERT INTO scores(s_id,c_id,score) VALUES(2,2,94);
INSERT INTO scores(s_id,c_id,score) VALUES(2,3,95);
INSERT INTO scores(s_id,c_id,score) VALUES(3,1,85);
INSERT INTO scores(s_id,c_id,score) VALUES(3,2,87);
INSERT INTO scores(s_id,c_id,score) VALUES(3,3,86);

需求：查询学生对应的学科成绩(学生姓名，学科名，成绩)

SELECT stu.name,cou.name,sco.score FROM student stu INNER JOIN course cou INNER JOIN scores sco WHERE sco.s_id=stu.id AND sco.c_id=cou.id;

提升题：查询出每个学生的平均成绩（学生姓名，平均分）

SELECT stu.name,AVG(sco.score) FROM scores sco INNER JOIN student stu ON sco.s_id=stu.id GROUP BY sco.s_id;

提升题：查询出每个学生的最好成绩的学科（学生姓名，学科目，成绩）

SELECT stu.name,cou.name,sco.score FROM scores sco INNER JOIN (SELECT sco.s_id,max(sco.score) AS 'maxScore' FROM scores sco GROUP BY sco.s_id) xx INNER JOIN student stu INNER JOIN course cou ON sco.score =xx.maxScore AND sco.s_id=xx.s_id AND sco.s_id=stu.id AND sco.c_id = cou.id;

2.DCL

给子账号开权限

参数说明

符号	说明
grant all	在grant后接all说明给予所有权限
revoke all	在revoke后接all说明删除所有权限
权限 on .	. 所明给予所有库所有表的操作权限
‘用户’@‘主机’	主机里面若为%。任意来源的主机均可以使用这个用户来访问

开子账号

create user 'hhy'@'localhost' identified by '123123';

添加权限

grant select,insert on fy2401javaee.* to 'hhy'@'localhost';

删除权限

revoke insert on fy2401javaee.* from 'hhy'@'localhost';

删除子账号

drop user 'hhy'@'localhost';

设置临时编码格式（关闭命令行后就无效了）

SET CHARSET gbk;

3.数据类型

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

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

2.字符串类型

3.日期时间类型

4.复合类型

3.1 整数类型

MySQL数据类型	所占字节	值范围
tinyint	1字节	-128~127
smallint	2字节	-32768~32767
mediumint	3字节	-8388608~8388607
int	4字节	范围-2147483648~2147483647
bigint	8字节	±9.22*10的18次方

经验：存储状态码一般使用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

3.2 浮点类型

MySQL数据类型	所占字节	值范围
float(m, d)	4字节	单精度浮点型，m总个数，d小数位
double(m, d)	8字节	双精度浮点型，m总个数，d小数位
decimal(m, d)		decimal是存储为字符串的浮点数

float(8,2) – 单精度浮点型

double(8,2) – 双精度浮点型

decimal(8,2) – 以字符串形式存储的小数

(8,2)表示8位数，其中2位为小数位

经验：因为float和double都有可能损失精度，有精度要求的字段一般使用decimal

3.3 字符串类型

MySQL数据类型	所占字节	值范围
CHAR	0-255字节	定长字符串
VARCHAR	0-255字节	变长字符串
TINYBLOB	0-255字节	不超过255个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65535字节	二进制形式的长文本数据
TEXT	0-65535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据
VARBINARY(M)	允许长度0-M个字节的定长字节符串	值的长度+1个字节
BINARY(M)	M	允许长度0-M个字节的定长字节符串

char(32) – 定长字符串，如果存储"abc"，底层会开辟32个字符长度的空间

varchar(32) – 变长字符串，如果存储"abc"，底层会根据具体数据开辟空间

(32) – MySQL5.5之前为32字节，之后32个字符

注意：char和varchar取值范围为0~255字符

BLOB – 可以存储二进制数据的字符串类型，意味着该类型可以存储文件

TEXT – 可以存储长文本数据的字符串类型，意味着该类型可以存储纯文本数据

LONGBLOB – 可以存储极长的二进制数据

LONGTEXT – 可以存储极长的纯文本数据

注意：存储二进制文件和纯文本文件的技术叫做CBLOB -> CLOB指的是存储纯文本文件，BLOB指的是存储二进制文件

经验：MySQL虽然允许存储文件，但是实际开发中不会存储文件，如果要存储文件，在数据库中存储的是该文件的路径，是为了减少数据库的压力

3.4 日期时间串类型

MySQL数据类型	所占字节	值范围
date	3字节	日期，格式：2024-06-01
time	3字节	时间，格式：14:32:30
datetime	8字节	日期时间，格式：2024-06-01 14:32:30
timestamp	4字节	自动存储记录修改的时间 2024-06-01 14:32:30
year	1字节	年份 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,
) 

4.约束

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

4.1 主键约束

理解：该字段上的数据不允许重复且不能为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;

4.2 唯一约束

理解：该字段上的数据不允许重复，但可以为null

添加唯一约束

ALTER TABLE user ADD UNIQUE(phone);

删除唯一约束

ALTER TABLE user DROP index phone;

4.3 非空约束

理解：该字段上的数据不允许为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'; 

4.4 外键约束

理解：保证两个字段之间参照完整性,外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系

创建学科表,并插入数据

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都会去外键关联的表中查询是否有脏数据

外键的优点：不会出现脏数据

外键的缺点：效率低

如果不适用外键，如何避免脏数据呢？

在页面选择数据，不会乱填的！

5.索引(重要)

理解：索引相当于一本书的目录，让查询更快

注意：索引上的数据作为查询条件，速度会更快

5.1 主键索引

注意：主键作为约束，该字段上的数据不能重复也不能为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)
)

添加主键索引 – 方式三

alter table 表 add PRIMARY KEY(字段)

经验：创建表时就添加主键，方式三很少使用

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;

5.2 唯一索引

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

添加唯一索引

alter table 表 add UNIQUE(字段)

ALTER TABLE user ADD UNIQUE(phone);

删除唯一索引

ALTER TABLE user DROP index phone;

5.3 普通索引

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

添加普通索引

alter table 表 add index(字段)

ALTER TABLE user ADD index(nickName);

删除普通索引

drop index nickName on user;

5.4 全文索引

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

添加全文索引

alter table 表 add FULLTEXT(字段)

ngram 中文、日文、韩文的全文解析器，如果不加则只能解析英文

ALTER TABLE news ADD FULLTEXT(info) WITH PARSER ngram;

全文搜索 info

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的区别

6.视图

视图：虚拟表,和普通表一样使用，是通过表动态生成的数据

创建移动公司的用户表

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)
)

创建视图

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 view01;

修改视图数据

UPDATE view01 SET age=41 WHERE phone='13993300090';

删除视图数据

DELETE FROM view01 WHERE phone='13993300090';

删除视图

DROP view view01;

注意：修改虚拟表，原表也会发生改变

解决：创建子账号，子账号只有查询权限

总结

1.多表联合查询 – 重要
一对多
多对多
2.DCL
3.数据类型（整数、浮点、字符串、日期时间）
4.约束（主键、唯一、非空、外键）
5.索引（主键、唯一、普通、全文，面试题） – 重要
RIMARY KEY,
password VARCHAR(32),
name VARCHAR(32),
type INT(3),
province VARCHAR(32),
city VARCHAR(32),
phone VARCHAR(32),
age INT(3)
)

> 创建视图> or replace 如果视图名相同就替换
>
> with check option修改数据时如果违反条件就不允许修改```mysql
create or replace view view01 as select phone,province,city,age from user where age < 40 with check option;

查询视图数据

SELECT * FROM view01;

修改视图数据

UPDATE view01 SET age=41 WHERE phone='13993300090';

删除视图数据

DELETE FROM view01 WHERE phone='13993300090';

删除视图

DROP view view01;

注意：修改虚拟表，原表也会发生改变

解决：创建子账号，子账号只有查询权限

总结

1.多表联合查询 – 重要
一对多
多对多
2.DCL
3.数据类型（整数、浮点、字符串、日期时间）
4.约束（主键、唯一、非空、外键）
5.索引（主键、唯一、普通、全文，面试题） – 重要
6.视图