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

这篇具有很好参考价值的文章主要介绍了【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

【MySQL】视图,15道常见面试题---含考核思路详细讲解,mysql,数据库

1.3 查看视图(两种)

语句:

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

【MySQL】视图,15道常见面试题---含考核思路详细讲解,mysql,数据库

1.4 修改视图(两种)

① 

create or replace view 视图名

as

查询语句;

② 

alter view 视图名

as

查询语句;

1.5 删除视图

语句:

drop view 视图名

【MySQL】视图,15道常见面试题---含考核思路详细讲解,mysql,数据库

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

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

【MySQL】视图,15道常见面试题---含考核思路详细讲解,mysql,数据库

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

【MySQL】视图,15道常见面试题---含考核思路详细讲解,mysql,数据库

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

【MySQL】视图,15道常见面试题---含考核思路详细讲解,mysql,数据库
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'

【MySQL】视图,15道常见面试题---含考核思路详细讲解,mysql,数据库

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

【MySQL】视图,15道常见面试题---含考核思路详细讲解,mysql,数据库
    
    
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

【MySQL】视图,15道常见面试题---含考核思路详细讲解,mysql,数据库
 

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

【MySQL】视图,15道常见面试题---含考核思路详细讲解,mysql,数据库

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

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

语句:

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

【MySQL】视图,15道常见面试题---含考核思路详细讲解,mysql,数据库

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 = '张三'

【MySQL】视图,15道常见面试题---含考核思路详细讲解,mysql,数据库

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)

【MySQL】视图,15道常见面试题---含考核思路详细讲解,mysql,数据库

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

【MySQL】视图,15道常见面试题---含考核思路详细讲解,mysql,数据库

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
 

【MySQL】视图,15道常见面试题---含考核思路详细讲解,mysql,数据库

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

【MySQL】视图,15道常见面试题---含考核思路详细讲解,mysql,数据库

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

【MySQL】视图,15道常见面试题---含考核思路详细讲解,mysql,数据库

【MySQL】视图,15道常见面试题---含考核思路详细讲解,mysql,数据库

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

【MySQL】视图,15道常见面试题---含考核思路详细讲解,mysql,数据库

四 思维导图 

【MySQL】视图,15道常见面试题---含考核思路详细讲解,mysql,数据库文章来源地址https://www.toymoban.com/news/detail-789228.html

到了这里,关于【MySQL】视图,15道常见面试题---含考核思路详细讲解的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • uniApp常见面试题-附详细答案

    uniApp中如何进行页面跳转? 答案:可以使用uni.navigateTo、uni.redirectTo和uni.reLaunch等方法进行页面跳转。其中,uni.navigateTo可以实现页面的普通跳转,uni.redirectTo可以实现页面的重定向跳转,uni.reLaunch可以实现关闭所有页面,打开到应用内的某个页面。 示例代码: uniApp中如何进

    2024年02月09日
    浏览(56)
  • Spring常见面试题汇总(超详细回答)

    Spring框架是一个开源的Java应用程序开发框架,它提供了很多工具和功能,可以帮助开发者更快地构建企业级应用程序。通过使用Spring框架,开发者可以更加轻松地开发Java应用程序,并且可以更加灵活地组织和管理应用程序中的对象和组件。 Spring框架的核心思想是依赖注入(

    2024年02月15日
    浏览(39)
  • SpringSecurity常见面试题汇总(超详细回答)

    Spring Security是一个基于Spring框架的安全框架,提供了完整的安全解决方案,包括认证、授权、攻击防护等功能。 其核心功能包括: 认证:提供了多种认证方式,如表单认证、HTTP Basic认证、OAuth2认证等,可以与多种身份验证机制集成。 授权:提供了多种授权方式,如角色授权

    2024年04月16日
    浏览(32)
  • SpringBoot常见面试题汇总(超详细回答)

    Spring Boot 是一个基于 Spring 框架的开源框架,用于快速创建独立的、生产级别的、可运行的 Spring 应用程序。它采用了约定优于配置的理念,使开发者可以不需要手动配置大量的 Spring 配置文件,而快速搭建出符合生产要求的、可运行的应用程序。 Spring Boot 通过自动配置,可以

    2024年01月18日
    浏览(43)
  • 35个MySQL常见面试题+答案

    今天给大家总结了35 个 Mysql 常见的小问题 1.说一说三大范式 2.MyISAM 与 InnoDB 的区别是什么? 3.为什么推荐使用自增 id 作为主键? 4.一条查询语句是怎么执行的? 5.使用 Innodb 的情况下,一条更新语句是怎么执行的? 6.Innodb 事务为什么要两阶段提交? 7.什么是索引? 8.索引失效的场

    2024年02月16日
    浏览(37)
  • MySQL第五战:常见面试题(下)

    在当今的IT世界,数据库是任何应用程序的核心。而MySQL,作为最流行的开源关系数据库管理系统,已经成为许多开发者和企业的首选。无论是初创公司还是大型企业,都依赖于MySQL来存储、管理和检索数据。 随着技术的不断发展,MySQL的复杂性和功能也在持续增长。为了更好

    2024年01月19日
    浏览(48)
  • 数据结构常见面试题及详细java示例

    下面是一些常见的数据结构面试题以及使用Java实现的解决方案。 1、如何在给定数组中找到重复的元素?         使用HashSet是一个常见的解决方法。 2、链表反转         反转链表是最常见的数据结构问题之一。以下是用Java实现的一个例子。 3、二叉树的深度     

    2024年04月12日
    浏览(37)
  • MySQL之CRUD及常见面试题讲解

    目录 一、CRUD是什么 二、什么是SQL注入 三、行转列的使用 四、CRUD中常用 : GROUP BY HAVING  ORDER BY  五、聚合函数和连表查询 聚合函数 连表查询 六、DELETE、TRUNCATE、DROP的区别 七、MySQL常见面试题讲解 CRUD是一个常用的缩写词,用于描述四种基本的数据库操作,即

    2024年02月13日
    浏览(37)
  • 玩转Mysql系列 - 第15篇:详解视图

    这是Mysql系列第15篇。 环境:mysql5.7.25,cmd命令中进行演示。 需求背景 电商公司领导说:给我统计一下:当月订单总金额、订单量、男女订单占比等信息,我们啪啦啪啦写了一堆很复杂的sql,然后发给领导。 这样一大片sql,发给领导,你们觉得好么? 如果领导只想看其中某

    2024年02月09日
    浏览(37)
  • 15天学习MySQL计划-SQL优化/视图(进阶篇)-第八天

    1.插入数据(insert) 1.批量插入 2.手动提交事务 3.主键顺序插入 4.大批量插入数据 如果一次性需要插入大批量数据,使用insert语句插入性能较低,此时可以使用MySQL数据库提供的load指令来进插入 方法如下。 2.主键优化 1.数据组织方式 2.页分裂 页可以为空,也可以填充一半,

    2023年04月26日
    浏览(52)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包