目录
一 视图
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语句:文章来源:https://www.toymoban.com/news/detail-789228.html
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
四 思维导图
文章来源地址https://www.toymoban.com/news/detail-789228.html
到了这里,关于【MySQL】视图,15道常见面试题---含考核思路详细讲解的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!