做阿里巴巴网站应怎样定位,品牌建设交流问题有哪些,招聘网最新招聘,wordpress树结构Day52
1.DML-多表联合查询(重要)
1.1 一对一情况 略 1.2 一对多情况 当需要查询多个表中的字段时#xff0c;就可以使用表连接来实现。表联接分为内连接和外连接 内连接#xff1a;将两个表中存在联结关系的字段符合联结关系的那些记录形成记录集的联结 外连接#xff1a;…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_idt.id学生表的老师id等于老师表的id SELECT s.name,t.name FROM student s,teacher t WHERE s.t_idt.id;方式二 SELECT s.name AS 学生姓名,t.name AS 老师姓名 FROM student s INNER JOIN teacher t ON s.t_idt.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_idt.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_idt.id;1.2.3 全连接 UNION 概念多张表联合查询多张表没有联系的数据都查询出来 注意MySQL没有全连接的SQL语句实现全连接的思想就是合并查询结果 去重 合并查询结果 SELECT s.name,t.name FROM student s LEFT JOIN teacher t ON s.t_idt.id
UNION ALL
SELECT s.name,t.name FROM student s RIGHT JOIN teacher t ON s.t_idt.id;合并查询结果 去重 SELECT s.name,t.name FROM student s LEFT JOIN teacher t ON s.t_idt.id
UNION
SELECT s.name,t.name FROM student s RIGHT JOIN teacher t ON s.t_idt.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_idstu.id AND sco.c_idcou.id;提升题查询出每个学生的平均成绩学生姓名平均分 SELECT stu.name,AVG(sco.score) FROM scores sco INNER JOIN student stu ON sco.s_idstu.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_idxx.s_id AND sco.s_idstu.id AND sco.c_id cou.id;2.DCL 给子账号开权限 参数说明 符号说明grant all在grant后接all说明给予所有权限revoke all在revoke后接all说明删除所有权限权限 on .. 所明给予所有库所有表的操作权限‘用户’‘主机’主机里面若为%。任意来源的主机均可以使用这个用户来访问 开子账号 create user hhylocalhost identified by 123123;添加权限 grant select,insert on fy2401javaee.* to hhylocalhost;删除权限 revoke insert on fy2401javaee.* from hhylocalhost;删除子账号 drop user hhylocalhost;设置临时编码格式关闭命令行后就无效了 SET CHARSET gbk;3.数据类型 在MySQL里面我们将数据类型分为了以下一些类型 1.数值类型整型、浮点 2.字符串类型 3.日期时间类型 4.复合类型 3.1 整数类型
MySQL数据类型所占字节值范围tinyint1字节-128~127smallint2字节-32768~32767mediumint3字节-8388608~8388607int4字节范围-2147483648~2147483647bigint8字节±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);#12343.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数据类型所占字节值范围CHAR0-255字节定长字符串VARCHAR0-255字节变长字符串TINYBLOB0-255字节不超过255个字符的二进制字符串TINYTEXT0-255字节短文本字符串BLOB0-65535字节二进制形式的长文本数据TEXT0-65535字节长文本数据MEDIUMBLOB0-16 777 215字节二进制形式的中等长度文本数据MEDIUMTEXT0-16 777 215字节中等长度文本数据LONGBLOB0-4 294 967 295字节二进制形式的极大文本数据LONGTEXT0-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数据类型所占字节值范围date3字节日期格式2024-06-01time3字节时间格式14:32:30datetime8字节日期时间格式2024-06-01 14:32:30timestamp4字节自动存储记录修改的时间 2024-06-01 14:32:30year1字节年份 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)-联合主键意味着主键是usernamepassword加在一起的数据 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)
) ENGINEInnoDB AUTO_INCREMENT4 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_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)-联合主键意味着主键是usernamepassword加在一起的数据 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;面试题索引为什么作为条件会更快 因为索引底层使用BTree的数据结构会让查询效率更高 面试题索引会让该字段作为条件查询更快为什么创建表时不会每个字段都加上索引呢 考点索引的优缺点 优点该字段作为条件查询更快 缺点添加、删除数据时索引的数据结构发生改变 经验1会把经常作为查询条件的字段设置为索引 经验2偶尔添加或删除索引字段上的数据直接无视批量添加或删除索引字段上的数据可以先把索引删除后等批量操作完再添加上索引 面试题索引的分类 聚簇索引(聚集索引)和非聚簇索引(非聚集索引) 面试题聚簇索引和非聚簇索引的区别 聚簇索引(主键索引)叶子结点上存放的是数据行的数据效率更高 非聚簇索引(唯一、普通、全文索引)叶子节点上存放的是数据行的地址 经验select 字段 FROM 表名 WHERE 主键字段xxx; 面试题索引失效的情况 1.最短路径算法是走索引查询更快还是顺序查找更快 2.最左匹配原则 3.使用模糊查询 4.使用函数 5.使用OR … 提升BTree和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 age41 WHERE phone13993300090;删除视图数据 DELETE FROM view01 WHERE phone13993300090;删除视图 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 age41 WHERE phone13993300090;删除视图数据 DELETE FROM view01 WHERE phone13993300090;删除视图 DROP view view01;注意修改虚拟表原表也会发生改变 解决创建子账号子账号只有查询权限 总结 1.多表联合查询 – 重要 一对多 多对多 2.DCL 3.数据类型整数、浮点、字符串、日期时间 4.约束主键、唯一、非空、外键 5.索引主键、唯一、普通、全文面试题 – 重要 6.视图
