【MySQL】视图，15道常见面试题---含考核思路详细讲解

目录

一 视图

1.1视图是什么 

1.2 创建视图

1.3 查看视图(两种)

1.4 修改视图(两种)

1.5 删除视图

二 外连接&内连接&子查询介绍

2.1 外连接

2.2 内连接

2.3 子查询

三 外连接&内连接&子查询案例

3.1 了解表结构与数据

3.2 15道常见面试题

四 思维导图 

一 视图

1.1视图是什么 

视图是在数据库中定义的虚拟表。它是一个基于一个或多个实际表的查询结果集，可以像实际表一样被查询和操作，视图本身并不存储数据，它只是通过定义一个查询。视图可以看作是一个动态生成的数据表，其内容是从其他表中选择、过滤和计算得到的。

视图通过使用SQL查询语句来定义，这些查询语句可以包括与一个或多个表的连接、条件过滤、列计算、聚合函数等操作。在视图定义中，我们可以指定要在视图中包含的列和行，以及对这些列进行何种计算和处理

1.2 创建视图

语句

create view 视图名
as
查询语句

案例

① 创建视图

create view V_stu_sc
as 
select 
stu.*,sc.cid,sc.score
from t_mysql_student stu,t_mysql_score sc
where stu.sid=sc.sid

1.3 查看视图(两种)

语句：

① desc  视图名;
② show create view 视图名;

1.4 修改视图(两种)

① 

create or replace view 视图名

as

查询语句;

② 

alter view 视图名

as

查询语句;

1.5 删除视图

语句：

drop view 视图名

二 外连接&内连接&子查询介绍

2.1 外连接

    外连接分为左外连接（Left Outer Join）和右外连接（Right Outer Join）。左外连接会返回左表中的所有记录以及右表中满足连接条件的记录。如果右表中没有匹配的记录，则结果集中对应的字段将为NULL。右外连接与左外连接相反，会返回右表中的所有记录以及左表中满足连接条件的记录

左外连接（LEFT JOIN）：

      返回左表中的所有记录以及右表中满足连接条件的记录。如果右表中没有匹配的记录，则结果集中对应的字段将为NULL

右外连接（RIGHT JOIN）：

          返回右表中的所有记录以及左表中满足连接条件的记录。如果左表中没有匹配的记录，则结果集中对应的字段将为NULL

语句：

-- 左外连接  
SELECT 列名  
FROM 表1  
LEFT OUTER JOIN 表2  
ON 表1.列名 = 表2.列名;  
  
-- 右外连接  
SELECT 列名  
FROM 表1  
RIGHT OUTER JOIN 表2  
ON 表1.列名 = 表2.列名;

2.2 内连接

      内连接是最常见的连接类型，它会返回两个表中满足连接条件的记录。只有当两个表中的指定字段具有匹配的值时，记录才会被包含在结果集中

语句：

SELECT 列名  
FROM 表1  
INNER JOIN 表2  
ON 表1.列名 = 表2.列名;

2.3 子查询

      子查询可以在一个查询中嵌套另一个查询，通常用于生成另一个查询的派生数据。子查询可以出现在SELECT、FROM或WHERE子句中，根据其位置和用途，子查询可以有多种形式。子查询可以在查询的列名、条件或排序中使用

-- 子查询在SELECT子句中  
SELECT 列名, (子查询) AS 别名  
FROM 表名;  
  
-- 子查询在FROM子句中作为派生表  
SELECT 派生表.列名  
FROM (子查询) AS 派生表;  
  
-- 子查询在WHERE子句中作为条件  
SELECT 列名  
FROM 表名  
WHERE 列名 = (子查询);

三 外连接&内连接&子查询案例

3.1 了解表结构与数据

首先先了解表结构，有利于我们后续查询做题

①学生表-t_mysql_student 
   sid 学生编号,sname 学生姓名,sage 学生年龄,ssex 学生性别

②教师表-t_mysql_teacher
   tid 教师编号,tname 教师名称

③ 课程表-t_mysql_course
   cid 课程编号,cname 课程名称,tid 教师名称

④ 成绩表-t_mysql_score
    sid 学生编号,cid 课程编号,score 成绩

所有表数据：

-- 学生表
insert into t_mysql_student values('01' , '赵雷' , '1990-01-01' , '男');
insert into t_mysql_student values('02' , '钱电' , '1990-12-21' , '男');
insert into t_mysql_student values('03' , '孙风' , '1990-12-20' , '男');
insert into t_mysql_student values('04' , '李云' , '1990-12-06' , '男');
insert into t_mysql_student values('05' , '周梅' , '1991-12-01' , '女');
insert into t_mysql_student values('06' , '吴兰' , '1992-01-01' , '女');
insert into t_mysql_student values('07' , '郑竹' , '1989-01-01' , '女');
insert into t_mysql_student values('09' , '张三' , '2017-12-20' , '女');
insert into t_mysql_student values('10' , '李四' , '2017-12-25' , '女');
insert into t_mysql_student values('11' , '李四' , '2012-06-06' , '女');
insert into t_mysql_student values('12' , '赵六' , '2013-06-13' , '女');
insert into t_mysql_student values('13' , '孙七' , '2014-06-01' , '女');

-- 教师表
insert into t_mysql_teacher values('01' , '张三');
insert into t_mysql_teacher values('02' , '李四');
insert into t_mysql_teacher values('03' , '王五');

-- 课程表
insert into t_mysql_course values('01' , '语文' , '02');
insert into t_mysql_course values('02' , '数学' , '01');
insert into t_mysql_course values('03' , '英语' , '03');

-- 成绩表
insert into t_mysql_score values('01' , '01' , 80);
insert into t_mysql_score values('01' , '02' , 90);
insert into t_mysql_score values('01' , '03' , 99);
insert into t_mysql_score values('02' , '01' , 70);
insert into t_mysql_score values('02' , '02' , 60);
insert into t_mysql_score values('02' , '03' , 80);
insert into t_mysql_score values('03' , '01' , 80);
insert into t_mysql_score values('03' , '02' , 80);
insert into t_mysql_score values('03' , '03' , 80);
insert into t_mysql_score values('04' , '01' , 50);
insert into t_mysql_score values('04' , '02' , 30);
insert into t_mysql_score values('04' , '03' , 20);
insert into t_mysql_score values('05' , '01' , 76);
insert into t_mysql_score values('05' , '02' , 87);
insert into t_mysql_score values('06' , '01' , 31);
insert into t_mysql_score values('06' , '03' , 34);
insert into t_mysql_score values('07' , '02' , 89);
insert into t_mysql_score values('07' , '03' , 98);

3.2 15道常见面试题

 01）查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数   

考核：内连接
涉及表：t_mysql_course，t_mysql_score

语句：

SELECT
    s.*,
    ( CASE WHEN t1.cid = '01' THEN t1.score END ) 语文,
    ( CASE WHEN t2.cid = '02' THEN t2.score END ) 数学 
FROM
    t_mysql_student s,
    ( SELECT * FROM t_mysql_score WHERE cid = '01' ) t1,
    ( SELECT * FROM t_mysql_score WHERE cid = '02' ) t2 
WHERE
    s.sid = t1.sid 
    AND t1.sid = t2.sid 
    AND t1.score > t2.score

02）查询同时存在" 01 "课程和" 02 "课程的情况

考核：内连接

涉及表：t_mysql_score   

为了让数据更加直观加上了优化表

优化表：t_mysql_student

语句：

SELECT
    s.*,
    ( CASE WHEN t1.cid = '01' THEN t1.score END ) 语文,
    ( CASE WHEN t2.cid = '02' THEN t2.score END ) 数学 
FROM
    t_mysql_student s,
    ( SELECT * FROM t_mysql_score WHERE cid = '01' ) t1,
    ( SELECT * FROM t_mysql_score WHERE cid = '02' ) t2 
WHERE
    s.sid = t1.sid 
    AND t1.sid = t2.sid

03）查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

考核：外连接中的左外连接

涉及表：t_mysql_scor    t_mysql_student

语句：

SELECT
    s.*,
    ( CASE WHEN t1.cid = '01' THEN t1.score END ) 语文,
    ( CASE WHEN t2.cid = '02' THEN t2.score END ) 数学 
FROM
    t_mysql_student s
    INNER JOIN ( SELECT * FROM t_mysql_score WHERE cid = '01' ) t1 ON s.sid = t1.sid
    LEFT JOIN ( SELECT * FROM t_mysql_score WHERE cid = '02' ) t2 ON t1.sid = t2.sid


04）查询不存在" 01 "课程但存在" 02 "课程的情况

考核：外连接中的右外连接

涉及表：t_mysql_scor    t_mysql_student

语句：

SELECT
    s.*,
    ( CASE WHEN sc.cid = '01' THEN sc.score END ) 语文,
    ( CASE WHEN sc.cid = '02' THEN sc.score END ) 数学 
FROM
    t_mysql_student s,
    t_mysql_score sc 
WHERE
    s.sid = sc.sid 
    AND s.sid NOT IN ( SELECT sid FROM t_mysql_score WHERE cid = '01' ) 
    AND sc.cid = '02'

05）查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

考核：聚合函数=》 分组，筛选  外连接中的左外连接

涉及表：t_mysql_student    t_mysql_score

语句：

SELECT
    s.sid,
    s.sname,
    round( avg( sc.score ), 2 ) 平均数 
FROM
    t_mysql_student s
    LEFT JOIN t_mysql_score sc ON s.sid = sc.sid 
GROUP BY
    s.sid,
    s.sname 
HAVING
    平均数 >= 60


    
    
06）查询在t_mysql_score表存在成绩的学生信息

考核：聚合函数》分组，外连接的左外连接

语句：

SELECT
    s.sid,
    s.sname 
FROM
    t_mysql_student s
    LEFT JOIN t_mysql_score sc ON s.sid = sc.sid 
GROUP BY
    s.sid,
    s.sname


 

07）查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

考核：聚合函数》分组，求和，总数。外连接中的左外连接

语句：

SELECT
    s.sid,
    s.sname,
    count( sc.score ) 选课总数,
    sum( sc.score ) 总成绩 
FROM
    t_mysql_student s
    LEFT JOIN t_mysql_score sc ON s.sid = sc.sid 
GROUP BY
    s.sid,
    s.sname

08）查询「李」姓老师的数量

考核：聚合函数》总数。like的使用

语句：

select count(*) from t_mysql_teacher where tname like '李%'

09）查询学过「张三」老师授课的同学的信息

sql语句:

SELECT
    s.*,
    c.cname,
    t.tname,
    sc.score 
FROM
    t_mysql_course c,
    t_mysql_student s,
    t_mysql_teacher t,
    t_mysql_score sc 
WHERE
    t.tid = c.tid 
    AND c.cid = sc.cid 
    AND sc.sid = s.sid 
    AND t.tname = '张三'

10）查询没有学全所有课程的同学的信息
sql语句:

SELECT
	s.sid,
	s.sname,
	count( sc.score ) n 
FROM
	t_mysql_student s
	LEFT JOIN t_mysql_score sc ON s.sid = sc.sid 
GROUP BY
	s.sid,
	s.sname 
HAVING
	n < (
	SELECT
		count(*) 
	FROM
	t_mysql_course)

11）查询没学过"张三"老师讲授的任一门课程的学生姓名

sql语句:

SELECT
    s.sid,
    s.sname 
FROM
    t_mysql_score sc,
    t_mysql_student s 
WHERE
    s.sid = sc.sid 
    AND sc.cid NOT IN ( SELECT cid FROM t_mysql_course c, t_mysql_teacher t WHERE c.tid = t.tid AND t.tname = '张三' ) 
GROUP BY
    s.sid,
    s.sname

12）查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

sql语句:

SELECT s.sid,
s.sname,
 
avg(sc.score) n
from
t_mysql_student s,
t_mysql_score sc
where s.sid=sc.sid
and sc.score<60
GROUP BY s.sid,
s.sname

13）检索" 01 "课程分数小于 60，按分数降序排列的学生信息

sql语句:

SELECT
	s.sid,
	s.*,
	sc.score 
FROM
	t_mysql_student s,
	t_mysql_score sc 
WHERE
	s.sid = sc.sid 
	AND sc.cid = '01' 
	AND sc.score < 60 
ORDER BY
	sc.score desc

14）按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 

① case when 

② if

sql语句:

① case语法：
SELECT
    s.sid,
    s.sname ,
    sum((case when sc.cid='01' then sc.score end))语文,
    sum(    (case when sc.cid='02' then sc.score end))数学,
    sum((case when sc.cid='03' then sc.score end))英语,
   ROUND(avg(sc.score),2) 
FROM
    t_mysql_score sc
    RIGHT JOIN t_mysql_student s ON sc.sid = s.sid 
GROUP BY
    s.sid,
    s.sname


② if语法：
 SELECT
    s.sid,
    s.sname ,
    sum(if(sc.cid='01',sc.score,0))语文,
    sum(if(sc.cid='02',sc.score,0))数学,
    sum(if(sc.cid='03',sc.score,0))英语,
   ROUND(avg(sc.score),2) 
FROM
    t_mysql_score sc
    RIGHT JOIN t_mysql_student s ON sc.sid = s.sid 
GROUP BY
    s.sid,
    s.sname

15）查询各科成绩最高分、最低分和平均分：
以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

sql语句:

SELECT
        c.cid,
        c.cname,
        count(sc.sid) 人数,
        max(sc.score) 最高分,
        min(sc.score) 最低分,
        ROUND(avg(sc.score),2) 平均分 ,
        CONCAT(ROUND(sum(if(sc.score>=90,1,0))/(SELECT count(1) 
        from t_mysql_student)*100,2),'%')  优秀率,
        CONCAT(ROUND(sum(if(sc.score>=80 and sc.score<90,1,0))/(SELECT count(1) 
        from t_mysql_student)*100,2),'%')  优良率,
        CONCAT(ROUND(sum(if(sc.score>=70 and sc.score<80,1,0))/(SELECT count(1) 
        from t_mysql_student)*100,2),'%')  中等率,
        CONCAT(ROUND(sum(if(sc.score>=60,1,0))/(SELECT count(1) 
        from t_mysql_student)*100,2),'%') 及格率
        
    FROM
        t_mysql_score sc
        LEFT JOIN t_mysql_course c ON sc.cid = c.cid 
    GROUP BY
        c.cid,
        c.cname

四 思维导图 

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/308988.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

道路拆除的题解

道路拆除的题解

目录 原题描述&#xff1a; 题目描述 输入格式 输出格式 样例 #1 样例输入 #1 样例输出 #1 样例 #2 样例输入 #2 样例输出 #2 提示 题目大意&#xff1a; 主要思路&#xff1a; 至于dis怎么求&#xff1f; 代码code&#xff1a; 原题描述&#xff1a; 题目描述 …
阅读更多...
盖子的c++小课堂——第二十四讲：差分数组

盖子的c++小课堂——第二十四讲：差分数组

前言 嗨嗨嗨&#xff0c;这里是盖子的小课堂哟&#xff0c;这次更新主要是因为快放假了&#xff0c;时间多了&#xff0c;好嘞&#xff0c;废话不多说&#xff0c;点赞评论拿来吧你~ 差分数组 一维差分数组 假设给你一个数组 nums &#xff0c;先对区间 [a,b] 中每个元素加…
阅读更多...
Android Canvas图层saveLayer剪切clipPath原图addCircle绘制对应圆形区域并放大，Kotlin（3）

Android Canvas图层saveLayer剪切clipPath原图addCircle绘制对应圆形区域并放大，Kotlin（3）

Android Canvas图层saveLayer剪切clipPath原图addCircle绘制对应圆形区域并放大&#xff0c;Kotlin&#xff08;3&#xff09; 在文章2 Android Canvas图层saveLayer剪切clipPath原图addCircle绘制对应圆形区域&#xff0c;Kotlin&#xff08;2&#xff09;-CSDN博客 的基础上&…
阅读更多...
LightGBM原理和调参

LightGBM原理和调参

背景知识 LightGBM(Light Gradient Boosting Machine)是一个实现GBDT算法的框架&#xff0c;具有支持高效率的并行训练、更快的训练速度、更低的内存消耗、更好的准确率、支持分布式可以处理海量数据等优点。 普通的GBDT算法不支持用mini-batch的方式训练&#xff0c;在每一次…
阅读更多...
【博士每天一篇文-算法】Graph Structure of Neural Networks

【博士每天一篇文-算法】Graph Structure of Neural Networks

阅读时间&#xff1a;2023-11-12 1 介绍 年份&#xff1a;2020 作者&#xff1a;尤家轩 斯坦福大学 期刊&#xff1a; International Conference on Machine Learning. 引用量&#xff1a;130 论文探讨了神经网络的图结构与其预测性能之间的关系。作者提出了一种新的基于图的…
阅读更多...
如何在simulink中怎么获取足端轨迹代码解释？

如何在simulink中怎么获取足端轨迹代码解释？

在使用Java代码框架统计用户获取足端轨迹时&#xff0c;我们可以使用Simulink的外部接口功能和Java的网络编程来实现。 我们需要在Simulink中配置外部接口以便与Java进行通信。可以使用Simulink中的TCP/IP或UDP模块来实现网络通信。假设我们选择TCP/IP模块。 足端轨迹是机器人运…
阅读更多...
kubernetes(k8s)集群常用指令

kubernetes(k8s)集群常用指令

基础控制指令 # 查看对应资源: 状态 $ kubectl get <SOURCE_NAME> -n <NAMESPACE> -o wide 查看默认命名空间的pod [rootk8s-master ~]# kubectl get pod NAME READY STATUS RESTARTS AGE nginx 1/1 Running 0 3h53m查看所有pod [roo…
阅读更多...
超过80%大厂都在用，Jetpack Compose现代Android界面开发的未来

超过80%大厂都在用，Jetpack Compose现代Android界面开发的未来

超过80%大厂都在用&#xff0c;Jetpack Compose现代Android界面开发的未来 1. 引言 Jetpack Compose是一款用于构建Android界面的现代化工具包。目前该框架已经相对成熟&#xff0c;大厂包括Google、字节、阿里等大厂都在使用。根据反馈&#xff0c;普遍认为开发效率提高了很…
阅读更多...
Linux最常用的几个系统管理命令

Linux最常用的几个系统管理命令

文章目录 Linux最常用的几个系统管理命令查看网络信息的原初 ifconfig默认无参数使用-s显示短列表配置IP地址修改MTU启动关闭网卡 显示进程状态 ps语法几个实例默认情况显示所有进程查找特定进程信息 任务管理器的 top常规使用显示完整命令设置信息更新次数设置信息更新时间显示…
阅读更多...
智谱AI大模型ChatGLM3-6B更新，快來部署体验

智谱AI大模型ChatGLM3-6B更新，快來部署体验

ChatGLM3 是智谱AI和清华大学 KEG 实验室联合发布的新一代对话预训练模型。ChatGLM3-6B 是 ChatGLM3 系列中的开源模型&#xff0c;在保留了前两代模型对话流畅、部署门槛低等众多优秀特性的基础上&#xff0c;ChatGLM3-6B 引入了如下特性&#xff1a; 1.更强大的基础模型&…
阅读更多...
FlinkAPI开发之数据合流

FlinkAPI开发之数据合流

案例用到的测试数据请参考文章&#xff1a; Flink自定义Source模拟数据流 原文链接&#xff1a;https://blog.csdn.net/m0_52606060/article/details/135436048 概述 在实际应用中&#xff0c;我们经常会遇到来源不同的多条流&#xff0c;需要将它们的数据进行联合处理。所以…
阅读更多...
JMeter 批量接口测试

JMeter 批量接口测试

一、背景 最近在进行某中台的接口测试准备&#xff0c;发现接口数量非常多&#xff0c;有6、70个&#xff0c;而且每个接口都有大量的参数并且需要进行各种参数验证来测试接口是否能够正确返回响应值。想了几种方案后&#xff0c;决定尝试使用JMeter的csv读取来实现批量的接口…
阅读更多...
【Docker项目实战】使用Docker部署nullboard任务管理工具

【Docker项目实战】使用Docker部署nullboard任务管理工具

【Docker项目实战】使用Docker部署nullboard任务管理工具 一、nullboard介绍1.1 nullboard简介1.2 任务看板工具介绍 二、本地环境介绍2.1 本地环境规划2.2 本次实践介绍2.3 注意事项 三、本地环境检查3.1 检查Docker服务状态3.2 检查Docker版本3.3 检查docker compose 版本 四…
阅读更多...
export default 和exprot

export default 和exprot

1.默认导入和默认导出 语法: export default {需要输出的内容} 接收: import 成员变量的名字 from 文件夹的路径 案例&#xff1a; a.mjs文件夹下默认导出 export default{a:10,b:20,show(){console.log(123);} } 在b.mjs文件中用成员变量进行接收 import AA from &q…
阅读更多...
【昕宝爸爸定制】如何将集合变成线程安全的?

【昕宝爸爸定制】如何将集合变成线程安全的?

如何将集合变成线程安全的? ✅典型解析&#x1f7e2;拓展知识仓☑️Java中都有哪些线程安全的集合&#xff1f;&#x1f7e0;线程安全集合类的优缺点是什么&#x1f7e1;如何选择合适的线程安全集合类☑️如何解决线程安全集合类并发冲突问题✔️乐观锁实现方式 (具体步骤)。✅…
阅读更多...
城堡世界源码

城堡世界源码

随着数字技术的飞速发展和人们对于娱乐需求的不断提升&#xff0c;城堡世界源码开发逐渐成为了新的热门话题。城堡世界是一个集潮流、艺术、科技于一体的数字娱乐新领域&#xff0c;通过将虚拟现实、增强现实等技术融入传统玩具设计中&#xff0c;为玩家们带来了全新的互动体验…
阅读更多...
建站为什么需要服务器？（Web服务器与计算机对比）

建站为什么需要服务器？（Web服务器与计算机对比）

​　　在部署网站时&#xff0c;底层基础设施在确保最佳性能、可靠性和可扩展性方面发挥着至关重要的作用。虽然大多数人都熟悉个人计算机 (PC) 作为日常工作和个人任务的设备&#xff0c;但 PC 和 Web 服务器之间存在显著差异。在这篇文章中&#xff0c;我们将讨论这些差异是什…
阅读更多...
拼多多API的未来：无限可能性和创新空间

拼多多API的未来：无限可能性和创新空间

拼多多&#xff0c;作为中国电商市场的巨头之一&#xff0c;自成立以来一直保持着高速的发展态势。其API的开放为开发者提供了无限的可能性和创新空间&#xff0c;使得更多的商业逻辑和功能得以实现。本文将深入探讨拼多多API的未来发展&#xff0c;以及它所具备的无限可能性和…
阅读更多...
Python基础学习（一）

Python基础学习（一）

Python基础语法学习记录 输出 将结果或内容呈现给用户 print("休对故人思故国&#xff0c;且将新火试新茶&#xff0c;诗酒趁年华") # 输出不换行&#xff0c;并且可以指定以什么字符结尾 print("青山依旧在",end ",") print("几度夕阳红…
阅读更多...
2024-01-03 无重叠区间

2024-01-03 无重叠区间

435. 无重叠区间 思路&#xff1a;和最少数量引爆气球的箭的思路基本都是一致了&#xff01;贪心就是比较左边的值是否大于下一个右边的值 class Solution:def eraseOverlapIntervals(self, points: List[List[int]]) -> int:points.sort(keylambda x: (x[0], x[1]))# 比较…
阅读更多...
最新文章

Copyright @ 2022~2023