测试需要掌握的数据库sql知识(四):面试中sql相关必问的问题:连接查询和索引

简介: 测试需要掌握的数据库sql知识(四):面试中sql相关必问的问题:连接查询和索引

一、前言


未看过文章(一)的朋友,需要准备测试数据

测试数据sql如下:

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for class
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class`  (
  `id` int(11) NOT NULL,
  `class_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `created` datetime(6) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES (1, '一班', '2021-07-17 13:40:30.000000');
INSERT INTO `class` VALUES (2, '二班', '2021-07-18 13:40:48.000000');
INSERT INTO `class` VALUES (3, '三班', '2021-07-19 13:40:48.000000');
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(11) NOT NULL,
  `created` datetime(6) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `class_id` int(11) NOT NULL,
  `gender` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `height` int(3) NOT NULL,
  `weight` int(3) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '2021-07-19 13:42:35.000000', '张顺', 1, 'male', 170, 65);
INSERT INTO `student` VALUES (2, '2021-07-19 13:42:35.000000', '张玲', 1, 'female', 170, 65);
INSERT INTO `student` VALUES (3, '2021-07-19 13:42:35.000000', '李广', 2, 'male', 180, 68);
INSERT INTO `student` VALUES (4, '2021-07-19 13:42:35.000000', '李三四', 2, 'female', 170, 65);
INSERT INTO `student` VALUES (5, '2021-07-19 13:42:35.000000', '赵云', 3, 'male', 199, 100);
INSERT INTO `student` VALUES (6, '2021-07-19 13:42:35.000000', '马超', 3, 'female', 171, 66);
INSERT INTO `student` VALUES (7, '2021-07-19 13:42:35.000000', '诸葛亮', 3, 'male', 170, 65);
INSERT INTO `student` VALUES (8, '2021-07-19 13:42:35.000000', '刘备', 3, 'male', 202, 105);
INSERT INTO `student` VALUES (9, '2021-07-19 13:42:35.000000', '曹操', 3, 'male', 181, 80);
INSERT INTO `student` VALUES (10, '2021-07-19 13:42:35.000000', '黄忠', 2, 'female', 166, 50);
SET FOREIGN_KEY_CHECKS = 1;

class表数据


20210720114624730.png

student表数据


20210720114657146.png

二、连接查询


1.左连接——LEFT JOIN(需掌握)

说明:返回左表中的所有记录和右表中与连接字段相等的记录,如果右表没有匹配的记录,那么就以空(Null)代替显示

语法:

select 字段 from 左表表名 左表的变量名(自定义) LEFT JOIN 右表表名 右表的变量名(自定义)on 左表变量名.左表字段=右表变量名.右表字段

举例:

1.查询学生及其所在的班级信息:

SELECT * FROM student a LEFT JOIN class b on a.class_id=b.id

结果:

6af6535e51134cd9897b867fb3e02c45.png


2.如果有学生在四班(class_id=4),但在班级表没有四班(id=4),它的查询结果会是怎样的呢?

现在我们插入一名在四班的学生:

INSERT INTO student VALUES (11, '2021-07-26 13:42:35.000000', '刘德华', 4, 'male', 180, 76);

插入后,student表的数据:


0476552e38d3420a8e780996ee4a330a.png

再执行查询学生及其所在的班级信息的sql:

SELECT * FROM student a LEFT JOIN class b on a.class_id=b.id

结果:


f5b3ef2ad68b48fcb9882120b694b4ca.png


新插入名叫“刘德华”的学生的班级信息显示为空了。

所以,左连接就是:返回左表中的所有记录和右表中与连接字段相等的记录,如果右表没有匹配的记录,那么就以空(Null)代替显示。


2.右连接——RIGHT JOIN

说明:与左连接相反:返回右表中的所有记录和左表中与连接字段相等的记录,如果左表没有匹配的记录,那么就以空(Null)代替显示。**


3.内连接——INNER JOIN(需掌握)

说明:只返回两个表都与连接字段相等的记录

举例:

1.还是以查询学生及其所在的班级信息:

SELECT * FROM student a INNER JOIN class b on a.class_id=b.id;


结果:

9c8f30cbf70e4a97a3309b49dca4a944.png

发现名叫“刘德华”的学生没有显示在查询结果中了

而最开始通过左连接的方式查询,会将“刘德华”查询出来,但它对应的班级信息会显示为空:

f5b3ef2ad68b48fcb9882120b694b4ca.png

所以,内连接就是:只返回两个表都与连接字段相等的记录

4.复合条件连接查询——带where条件的连接查询(了解就行)

说明:连接查询还可以与where条件联用

举例:

1.查询性别为男的学生及其所在的班级信息:

SELECT * FROM student a LEFT JOIN class b on a.class_id=b.id WHERE a.gender='male';


结果:

47cabb4d6a6e497e9071e8a58c2c2015.png


三、索引


说明:实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。

面试会经常问你数据库索引是什么?

答:索引类似于一本书前面的目录,能够提高检索效率从而提升性能。


作为测试我们一般有如此回答就够了,但有一些比较高级测试岗位或面试官问的比较多的时候,还会问索引的优缺点是什么:

答:

优点:

1.大大加快数据的检索速度,这也是创建索引的最主要的原因

2.加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。

3.在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。


缺点:

1.索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大

2.当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,降低了数据的维护速度。


我们还需要掌握一些索引知识:


索引常用哪几种类型:

1.普通索引:仅快速查询b5fa15b7d2634ca18b3964477eb71de6.png

2.唯一索引:加速查询+列值唯一(可以有null)

9baa61f90d5646d4ba7b0f6a465b8011.png

3.主键索引:加速查询+列值唯一(不可以有null)+表中只有一个

6675dc8e39fc4bab9b0254af4091ce19.png

4.全文索引:对本文的内容进行分词,进行搜索

9a0f00338713494f896e23f585a80386.png

目录
相关文章
|
21天前
|
SQL Java 数据库连接
【潜意识Java】MyBatis中的动态SQL灵活、高效的数据库查询以及深度总结
本文详细介绍了MyBatis中的动态SQL功能,涵盖其背景、应用场景及实现方式。
70 6
|
2月前
|
存储 Java easyexcel
招行面试:100万级别数据的Excel,如何秒级导入到数据库?
本文由40岁老架构师尼恩撰写,分享了应对招商银行Java后端面试绝命12题的经验。文章详细介绍了如何通过系统化准备,在面试中展示强大的技术实力。针对百万级数据的Excel导入难题,尼恩推荐使用阿里巴巴开源的EasyExcel框架,并结合高性能分片读取、Disruptor队列缓冲和高并发批量写入的架构方案,实现高效的数据处理。此外,文章还提供了完整的代码示例和配置说明,帮助读者快速掌握相关技能。建议读者参考《尼恩Java面试宝典PDF》进行系统化刷题,提升面试竞争力。关注公众号【技术自由圈】可获取更多技术资源和指导。
|
21天前
|
缓存 关系型数据库 MySQL
【深入了解MySQL】优化查询性能与数据库设计的深度总结
本文详细介绍了MySQL查询优化和数据库设计技巧,涵盖基础优化、高级技巧及性能监控。
161 0
|
3月前
|
架构师 数据库
大厂面试高频:数据库乐观锁的实现原理、以及应用场景
数据库乐观锁是必知必会的技术栈,也是大厂面试高频,十分重要,本文解析数据库乐观锁。关注【mikechen的互联网架构】,10年+BAT架构经验分享。
大厂面试高频:数据库乐观锁的实现原理、以及应用场景
|
3月前
|
SQL 缓存 监控
大厂面试高频:4 大性能优化策略(数据库、SQL、JVM等)
本文详细解析了数据库、缓存、异步处理和Web性能优化四大策略,系统性能优化必知必备,大厂面试高频。关注【mikechen的互联网架构】,10年+BAT架构经验倾囊相授。
大厂面试高频:4 大性能优化策略(数据库、SQL、JVM等)
|
2月前
|
存储 缓存 网络协议
数据库执行查询请求的过程?
客户端发起TCP连接请求,服务端通过连接器验证主机信息、用户名及密码,验证通过后创建专用进程处理交互。服务端进程缓存以减少创建和销毁线程的开销。后续步骤包括缓存查询(8.0版后移除)、语法解析、查询优化及存储引擎调用,最终返回查询结果。
40 6
|
3月前
|
SQL 关系型数据库 MySQL
阿里面试:1000万级大表, 如何 加索引?
45岁老架构师尼恩在其读者交流群中分享了如何在生产环境中给大表加索引的方法。文章详细介绍了两种索引构建方式:在线模式(Online DDL)和离线模式(Offline DDL),并深入探讨了 MySQL 5.6.7 之前的“影子策略”和 pt-online-schema-change 方案,以及 MySQL 5.6.7 之后的内部 Online DDL 特性。通过这些方法,可以有效地减少 DDL 操作对业务的影响,确保数据的一致性和完整性。尼恩还提供了大量面试题和解决方案,帮助读者在面试中充分展示技术实力。
|
2月前
|
SQL JavaScript 程序员
数据库LIKE查询屡试不爽?揭秘大多数人都忽视的秘密操作符!
本文分析了因数据库中的不可见空白字符导致的数据查询问题,探讨了问题的成因与特性,并提出了使用 SQL 语句修复问题的有效方案。同时,总结了避免类似问题的经验和注意事项。
46 0
|
3月前
|
存储 缓存 固态存储
怎么让数据库查询更快
【10月更文挑战第28天】
51 2
|
3月前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
553 1