数据服务面试题

数据服务面试题

1. 有一个录取学生人数表，记录的是每年录取学生人数和入学学生的学制
   以下是表结构：
   CREATE TABLE admit (
   id int(11) NOT NULL AUTO_INCREMENT,
   year int(255) DEFAULT NULL COMMENT ‘入学年度’,
   num int(255) DEFAULT NULL COMMENT ‘录取学生人数’,
   stu_len varchar(255) DEFAULT NULL COMMENT ‘学生学制’,
   PRIMARY KEY (id)
   ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COMMENT=‘录取人数’;
   year表示学生入学年度
   num表示对应年度录取学生人数
   stu_len表示录取学生的学制
   说明：例如录取年度2018学制3，表示该批学生在校年份为2018_2019、20192020、2020~2021
   以下是示例数据：

id year num stu_len
1 2018 2000 3
2 2019 2000 3
3 2020 1000 4
4 2020 2000 3

根据以上示例计算出每年在校人数,写出SQL语句：

-- 先查出每年入学人数，再查出毕业当年的人数合并结果集，
-- 然后开窗求总和，整表为分区，
-- 最后开窗序号为1的进行去重
SELECT 
			t2.year,
			t2.res
FROM(
		SELECT 
			t1.`year`,
			SUM(t1.num) over(ORDER BY t1.`year`) as res,
			ROW_NUMBER() over(PARTITION BY t1.`year` ORDER BY t1.`year`) as ranking
		FROM(
				SELECT year,num FROM admit
				UNION ALL
				SELECT year+stu_len as year,-num FROM admit
				) as t1
		ORDER BY t1.`year`
		) as t2
WHERE t2.ranking = 1;

计算结果截图：
在这里插入图片描述

2. 简述一下Mysql的索引
   主键索引（Primary Key Index）：用于唯一标识每一行数据的索引。每张表只能有一个主键索引，它可以加速通过主键进行的查询。
   唯一索引（Unique Index）：确保索引列中的值是唯一的。与主键索引不同，一张表可以有多个唯一索引。
   普通索引（Normal Index）：最常见的索引类型。它可以加速按照索引列进行的查询，但允许重复值。

3. Mysql插入数据的方式有多少种，分别的哪些：
   Mysql插入数据支持：

4. Insert into 表名(字段，…) values(字段,…)
   可以多行插入，如果不写字段的话要按照默认顺序写values里的数据

5. insert into 表名select xxx from xxx
   直接将查询结果集插入到表中，不够mysql不支持overwire的形式，只支持replace和isnert into

6. replace into 表名(字段，…) values(字段,…)
   当插入的数据与已有数据的主键或唯一索引发生冲突时，REPLACE INTO 将执行替换操作。

7. load data local infile 数据 into table 表名
   fields terminated by 分隔符
   lines terminated by 换行符
   将数据文件直接导入mysql，需要指定好数据位置，并且提前创建好表，和指定分隔符和换行符

5.insert ignore into表名(字段，…) values(字段,…)
用于在插入数据时遇到主键或唯一索引冲突时，忽略该行数据而不报错。
6．isnert INTO表名(字段，…) values(字段,…) ON DUPLICATE KEY UPDATE
字段= VALUES(字段);
在插入数据时遇到主键或唯一索引冲突时，执行更新操作而不是报错
4. 内连接、自连接、外连接（左、右、全）、交叉连接的区别

1. 内连接inner join，也叫等值连接，返回关联外键相同的，成功关联上的记录
2. 自连接，连接自身，用于找层级关系，像员工表里面找管理员这种
3. 左外连接left join，以左表为基表，返回左表中的所有记录，以及右表关联上左表字段的记录，如果左边有记录没关联上右表也会返回左表记录，但是右表的相关字段就会成空值
4. 右外连接right join，以右表为基表，返回右表中的所有记录，以及左表关联上右表字段的记录，如果右边有记录没关联上左表也会返回右表记录，但是左表的相关字段就会成空值
5. 全外连接full join,全外连接会返回所有左右表的记录数，其中没有关联上的字段会成空值
6. 交叉连接，就是在关联表的时候不加关联条件，会导致笛卡尔集，导致数据指数级增长，两个表之间的每一行数据会和另外一个表的所有行匹配一次，最后数量是两个表中的记录数的乘积，一般需要避免这个情况
7. 说一下你对数仓分层的理解
   略
8. 对高校的业务系统了解多少，有没有处理高校数据的经验
   略