15.mysql面试题

最新推荐文章于 2024-02-28 06:45:00 发布

信行合一

最新推荐文章于 2024-02-28 06:45:00 发布

阅读量2.1w

点赞数

分类专栏： 20_mysql 文章标签： mysql

本文链接：https://blog.csdn.net/tengdazhang770960436/article/details/51017713

版权

20_mysql 专栏收录该内容

23 篇文章 2 订阅

订阅专栏

文章目录

1.同一张表相关
2.合并结果型
- 1.表结构
3.case when

1.同一张表相关

1.1.非级联型元素比较

学生表：student
id stuName
1  s1
2  s2
3  s3


课程表：course
id couName
1  chinese
2  math
3  english


成绩表：socre
id stuid couid score
1  1     1     50
2  1     2     78
3  1     3     89
4  2     1     67
5  2     2     88
6  2     3     76
7  3     1     90
8  3     3     99



问题：

1.数学比语文高

SELECT * from student where id in (
SELECT math.stuid from
(SELECT * from score where couid=2) math,
(SELECT * FROM score where couid=1) chinese
where
math.stuid = chinese.stuid and math.score>chinese.score
);


2.平均分大于60

SELECT * from student
where id in
(SELECT avgTest.stuid from
(SELECT stuid,AVG(score) as avgScore
from score GROUP BY stuid HAVING avgScore>60) avgTest
);

1.2.级联型表比较

1.选择要移动的节点

在这里插入图片描述

2.选择要移动到的节点

在这里插入图片描述 ### 3.表结构：

CREATE TABLE `sys_organization` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `parent_id` bigint(20) DEFAULT NULL,
  `parent_ids` varchar(100) DEFAULT NULL,
  `available` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_sys_organization_parent_id` (`parent_id`),
  KEY `idx_sys_organization_parent_ids` (`parent_ids`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

4.删除逻辑

public void deleteOrganization(Long organizationId) {
        // 1.查找到当前的组织
        Organization organization = findOne(organizationId);
        // 2.删除当前的组织
        final String deleteSelfSql = "delete from sys_organization where id=?";
        jdbcTemplate.update(deleteSelfSql, organizationId);
        // 3.删除以当前组织开头的组织
        final String deleteDescendantsSql = "delete from sys_organization where parent_ids like ?";
        jdbcTemplate.update(deleteDescendantsSql, organization.makeSelfAsParentIds() + "%");
    }

5.移动组织

@Override
    public void move(Organization source, Organization target) {

        // 1.把 target 当做 source 的父元素
        // 1.1.设置 source 的 parent_id 为 target 的 id
        // 1.2.设置 source  parent_ids 为 target.makeSelfAsParentIds() 的值
        // 2.修改 source 子元素的 parent_ids
        // 3.因为 source 子元素的直接父元素是不变的，所以在修改的时候，不能修改它 parent_ids 最后的那个元素，所以替换的时候也是需要注意的
        // 比如：source 子元素的 a 原来的 parent_ids 是 0/1/2/4/
        // 那么在修改的时候，最后的 4/ 是不用动的，这个时候只需要替换的是 0/1/2/ 这一部分

        String moveSourceSql = "update sys_organization set parent_id=?,parent_ids=? where id=?";
        jdbcTemplate.update(moveSourceSql, target.getId(), target.makeSelfAsParentIds(), source.getId());
        String moveSourceDescendantsSql = "update sys_organization set parent_ids=concat(?, substring(parent_ids, length(?))) where parent_ids like ?";
        jdbcTemplate.update(moveSourceDescendantsSql, target.makeSelfAsParentIds(), source.makeSelfAsParentIds().substring(1), source.makeSelfAsParentIds() + "%");
    }

2.合并结果型

1.表结构

1.图片表（存放图片）

CREATE TABLE `tb_pic` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增长 id',
  `url` varchar(255) DEFAULT NULL COMMENT '图片 url 路径',
  `description` varchar(255) DEFAULT NULL COMMENT '图片描述',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='图片表';

2.标签表（存放标签）

CREATE TABLE `tb_flag` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增长 id',
  `name` varchar(255) DEFAULT NULL COMMENT '标签名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='标签表';

3.图片标签链接表（图片、标签中间表）

CREATE TABLE `tb_pic_flag_link` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增长 id',
  `pic_id` int(11) DEFAULT NULL COMMENT '图片 id',
  `flag_id` int(11) DEFAULT NULL COMMENT '标签 id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='图片标签链接表';

4.实现的效果

1.查询单张图片的描述

效果：
单张表描述
查询语句：

#查询单张图片，及其描述
SELECT p.id, p.url, p.description, GROUP_CONCAT(f.`name`)
from tb_pic p, tb_pic_flag_link l, tb_flag f
where p.id = l.pic_id and l.flag_id=f.id and p.id=1;

2.查询所有图片的描述

效果：
单张表描述
查询语句：

#查询多张图片，及其描述
SELECT p.id, p.url, p.description, GROUP_CONCAT(f.`name`)
from tb_pic p, tb_pic_flag_link l, tb_flag f
where p.id = l.pic_id and l.flag_id=f.id GROUP BY p.id;

3.case when

根据不同的情况去查询不同关联表内容
1.有三张表如下
1.1.学生表 student

CREATE TABLE `student` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `test`.`student` (`id`, `name`, `class_id`) VALUES ('1', 's1', '1');
INSERT INTO `test`.`student` (`id`, `name`, `class_id`) VALUES ('2', 's2', '2');

1.2.班级表1 class1

CREATE TABLE `class1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `test`.`class1` (`id`, `name`) VALUES ('1', '1班');

1.3.班级表2 class2

CREATE TABLE `class2` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `test`.`class2` (`id`, `name`) VALUES ('2', '2班');

1.4.班级表3 class3

CREATE TABLE `class3` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `test`.`class3` (`id`, `name`) VALUES ('3', '3班');

2.实现效果：根据学生的 class_id 不同去不同的表取他们的班级信息：比如学生的 class_id 为 1 则去 class1 取学生的班级信息，class_id 为 2 则去 class2 取班级信息

SELECT 
s.id,s.`name`,
case
s.class_id 
when 1 THEN c1.name
when 2 THEN c2.`name`
ELSE c3.name
END as className
FROM 
student s
LEFT JOIN class1 c1 on s.class_id=c1.id
LEFT JOIN class2 c2 on s.class_id=c2.id
LEFT JOIN class3 c3 on s.class_id=c3.id

查询效果：