网站首页 > 博客 > 正文

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

深海暗蓝博客 2024-01-29 1 0

一视图

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 视图名

查询语句

案例

① 创建视图

create view V_stu_sc

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 视图名

查询语句;

②

alter view 视图名

查询语句;

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

四思维导图

好文推荐

评论可见，请评论后查看内容，谢谢！！！评论后请刷新页面。

本文由用户于 2024-01-29 发布在夸智网，如有疑问，请联系我们。
本文链接：https://www.kuazhi.com/post/712893162.html

夸智网

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

数据库 java Mysql——SUM()函数

遥控智能拼接机器人怎么用

发表评论取消回复

夸智网

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

数据库 java Mysql——SUM()函数

遥控智能拼接机器人怎么用

相关文章

发表评论取消回复

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