[MySQL]视图&索引以及连接查询案列

已于 2024-01-09 11:38:39 修改
阅读量1.3k 收藏 16
点赞数 20
文章标签： mysql 数据库
于 2024-01-06 17:10:09 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_74934282/article/details/135425435
版权

目录

1.视图

1.1视图是什么

 1.2视图的作用

1.3操作

1.3.1创建视图

1.3.2视图的修改

 1.3.3删除视图

 1.3.4查看视图

 2.索引

2.1什么是索引

2.2为什么要使用索引

 2.3索引的优缺点

2.3.1优点

2.3.2缺点

 2.4索引的分类

 3.连接查询案列

 4.思维导图

1.视图

1.1视图是什么

视图（View）是数据库中的虚拟表，是由一个或多个基本表（或其他视图）的行或列组成的。视图并不在数据库中占用存储空间，并且可以提供一种逻辑上的数据组织方式，将数据按照某种条件过滤、划分或组合，以方便用户查询和使用数据。

含义：

虚拟表，和普通表一样使用

 1.2视图的作用

  1. 数据安全性：视图可以限制用户对数据的访问权限。通过只向用户开放特定的视图，可以隐藏实际存储数据的表和列，保护敏感信息的安全性。视图还可以根据用户的角色和权限，提供不同级别的数据访问控制。

  2. 数据的逻辑组织和定制：视图可以对基本表进行逻辑上的组织和定制，以满足用户的特定需求。通过视图，可以对数据进行过滤、排序、聚合和连接等操作，将数据以一种用户友好的方式展示出来，减少了用户对底层数据结构的了解和操作。

  3. 数据的简化和抽象：视图可以简化复杂的查询操作，将一系列的数据操作组合在一起提供给用户，使用户能够更方便地进行数据的查询和分析。通过视图，用户可以从多个表中获取所需的信息，而无需编写复杂的JOIN和WHERE子句。

  4. 逻辑数据独立性：视图可以提供逻辑数据独立性，使应用程序和数据库之间的关联变得松耦合。如果底层表结构发生变化，只需调整视图的定义即可，而不需要修改应用程序中使用视图的相关代码。

  5. 性能提升：视图可以提供对常用查询的优化，通过将查询的结果存储在视图中，可以减少复杂的计算和数据访问操作，提高查询的性能。

1.3操作

1.3.1创建视图

创建视图语法：

carete view 视图名

as

查询语句

1.3.2视图的修改

方式一：

create or replace view 视图名

as

查询语句

方式二：

alter view 视图名

as

查询语句

 1.3.3删除视图

删除视图的语法:

drop view 视图名，视图名........

 1.3.4查看视图

查看视图相关字段

DESC 视图名

查看视图相关语句

SHOW CREATE VIEW 视图名 

 2.索引

2.1什么是索引

索引是数据库中用于加快数据检索速度的一种数据结构。它类似于书籍的目录，通过在关键字和对应数据位置之间建立的映射关系，可以帮助数据库系统更快地定位和访问特定的数据记录。索引通常是在数据库表的一列或多列上创建的，以提高对这些列的查询速度。

2.2为什么要使用索引

索引是一种特殊的数据库结构，由数据表中的一列或多列组合而成，可以用来快速查询数据表中某一特定值的记录。

 2.3索引的优缺点

2.3.1优点

  1. 提高查询速度：索引可以大大加快数据的检索速度，特别是在大型数据表中。
  2. 加速排序：使用索引可以加速ORDER BY子句的查询，使数据按照特定的列进行快速排序。
  3. 加速连接：在进行表的连接操作时，如果连接的列上有索引，可以提高连接的速度。
  4. 提高唯一性约束：可以创建唯一索引来强制单个列或多列的唯一性约束。

2.3.2缺点

  1. 占用存储空间：索引需要额外的存储空间，这会增加数据库的存储成本。
  2. 维护成本：在数据插入、更新和删除时，索引的维护也会增加相应的开销，可能会影响性能。
  3. 复杂查询的代价：在执行查询时，如果使用了多个索引，可能需要对多个索引进行查找和合并，这可能会导致查询性能下降。
  4. 索引失效：当数据库中的数据量较小时，使用索引可能会导致优化器不选择索引，反而影响查询性能。
  5. 索引选择错误：选择不当的索引可能导致查询性能降低，需要权衡查询需求与索引选择。

 2.4索引的分类

  1. 单列索引（Single-Column Index）：针对单个列创建的索引。最为常见的索引类型，可用于加快单列查询速度。

  2. 复合索引（Composite Index）：针对多个列组合创建的索引。通过在多个列上创建索引可以提高复合条件的查询速度，同时也提供了对这些列的单列查询的优化。

  3. 唯一索引（Unique Index）：确保索引列的值全部唯一的索引。用于实施唯一性约束，防止表中出现重复的值。

  4. 主键索引（Primary Key Index）：主键在数据库表中起到唯一标识记录的作用，数据库系统通常会对主键列自动创建主键索引，以确保主键的快速检索。

  5. 外键索引（Foreign Key Index）：在包含外键的列上创建的索引，用于加快外键关联表之间的连接和查询操作。

  6. 全文索引（Full-Text Index）：针对文本类型的列进行的特殊索引，用于支持全文搜索和匹配。

  7. 聚集索引（Clustered Index）：数据行的物理顺序与索引的逻辑顺序一致的索引。在聚集索引中，索引本身就是数据的排列顺序，因此表中的数据行实际上就是按照这个顺序进行存储的。

  8. 非聚集索引（Non-Clustered Index）：索引中的顺序与数据行的物理存储不同。在非聚集索引中，索引和数据行是分开存储的，索引的叶子节点中存储的是指向数据行的指针或引用。

 3.连接查询案列

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

SELECT
    * 
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 "课程的情况

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 )

SELECT
    * 
FROM
    ( SELECT * FROM t_mysql_score WHERE cid = '01' ) t1
    LEFT JOIN ( SELECT * FROM t_mysql_score WHERE cid = '02' ) t2 ON t1.sid = t2.sid;
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 "课程的情况

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 分的同学的学生编号和学生姓名和平均成绩

SELECT
    s.sid,
    s.sname,
    ROUND( avg( sc.score ), 2 ) 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 >= 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）查询「李」姓老师的数量 

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

 

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

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）查询没有学全所有课程的同学的信息 

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）查询没学过"张三"老师讲授的任一门课程的学生姓名

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）查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

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，按分数降序排列的学生信息

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语法：
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
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

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

 

 4.思维导图

懒大王o
关注
  • 20
    点赞
  • 踩
  • 16
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
视图与索引&连表查询（内/外联）和子查询
m0_74276368的博客
01-06 1302
目录一、视图 1.1、概念：1.2、场景：1.3、用视图的意义1.2、创建(增加)视图1.3、修改视图1.4、删除视图1.5、查看视图​编辑二、索引2.1、概念2.2、优缺点优点：缺点：2.3、应用场景2.4、会失效2.5、分类：2.6、语法创建索引的语法三、复杂SQL语句3.1、用处3.2、内联（inner join/ join）概念：应用场景：3.3、外联(left join\right join或full outer join)概念：分类：应用场景：3.3、子查询概念：面试题3.4、案例(运用两种方式
MySQL的视图和索引用法与区别详解
09-08
MySQL的视图和索引是数据库管理中两个重要的概念，它们各自有着独特的功能和作用。 首先，让我们深入了解MySQL的视图。视图可以看作是SQL SELECT语句的保存版本，它提供了一种抽象和简化数据的方式。创建视图时，...
mysql之视图&mysql连接案例&索引
2302_76705099的博客
01-06 573
方法①：create or replace view 视图名 as 查询语句;SHOW CERATE VIEW 视图名;方法②：alter view 视图名 as 查询语句;drop view 视图名,视图名,…create view 视图名。虚拟表，和普通表一样使用。
【成长笔记】MySQL多表关联查询以及索引和视图的操作
最新发布
Jerry_Gao921的博客
05-24 298
为SC表建立按学号（sno）和课程号（cno）组合的升序的主键索引，索引名为SC_INDEX。修改student 表中年龄（age）字段属性，数据类型由int 改变为smallint。4.创建一视图 stu_info,查询全体学生的姓名，性别，课程名，成绩。创建课程表 t_course，并插入课程数据。2、向表test1里插入175651条数据。在 t_sdudnet 表中插入学生数据。创建 t_student 学生表。创建学生选课表 t_SC。
MySQL查看视图
天高任鸟飞
07-29 4万+
查看视图 是指查看数据库中，已经存在的视图的定义 查看视图，必须要有SHOW VIEW的权限查看视图有三种方式使用DESCRIBE语句使用DESCRIBE语句，查看视图 MySQL中，使用DESCRIBE可以查看视图的字段信息，其中，包括字段名、字段类型等信息语法格式 DESCRIBE 视图名; 或者简写 DESC 视图名;使用describe语句，查看stu_class视图可以看出，显
MySQL之视图、子查询和关联子查询
柳小葱的博客
04-20 2043
前面的章节介绍了数据更新操作，今天介绍MySQL的复杂查询。复杂查询主要包括 1.视图 先介绍一下视图： 从SQL的角度来看，视图和表是相同的，两者的区别在于表中存储的是实际的数据，而视图中保存的是SELECT语句（视图本身并不存储数据）。 使用视图可以轻松完成跨多表查询数据等复杂操作。 视图中不能包含ORDER BY和对视图的更新操作（INSERT、DELETE、UPDATE） 1.1创建视图的方法 CREATE VIEW 视图名称(<视图列名1><视图列名2><视图列名
视图的查询
weixin_51992178的博客
05-21 2267
视图定义以后用户可以像对基本表一样对视图进行查询视图消解：从数据字典里取出视图的定义，把定义的子查询与用户的查询结合并转换成等价的对基本表的查询，然后执行修正了的查询 select * from is_student where sage<20; 转换成 select * from student where sdept='is' and sage<20; 小提示： 目前多数关系数据库对行列子集视图的查询均能进行正确的转换。但其他的就不一定了，在此情况下建议直接对基本表进行查询 ...
MySQL 视图
weixin_65685029的博客
08-22 1561
视图是数据库中的一个虚拟表，同真实表一样，视图包含一系列带有名称的行和列数据。 视图是从一个或者多个表中导出来的，视图的行为与表非常相似，在视图中，用户可以使用SELECT、INSERT、UPDATE、DELETE等语句。从MySQL5.0开始就可以使用视图，视图可以使用户操作方便，而且可以保障数据库系统的安全性。视图其实没有存储任何数据，它只是对表的一个查询，视图的定义保存在数据字典内，创建视图所基于的表叫做‘基表’。 二、视图的作用和优点 作用：控制安全、保存查询数据 优点：提供了灵活一致级别安全
MySQL中索引与视图的用法与区别详解
09-09
在MySQL数据库中，索引和视图是两个重要的概念，它们在数据库管理和查询优化中扮演着关键角色。本文将深入探讨这两个概念的用法及它们之间的区别。 首先，我们来看索引。索引是数据库为了加快数据检索速度而创建的...
MySQL数据库应用 实验训练4：视图和索引的构建与使用
06-25
MySQL数据库中的视图和索引是两个非常重要的概念，它们对于数据管理、查询效率和数据安全性都有着显著的影响。本实验训练旨在让学生通过实际操作来深入理解这两个概念。 首先，我们来看视图。视图可以被看作是...
mysql三张表连接建立视图
12-16
三张表连接·· A表的a字段 对应 B表的b字段 ，B表的b1字段对应C 表的c字段 现在 建立 一个视图，可以同时 看到三张表的 所有信息·~ create or replace view v_name as select t1.*,t2.*,t3.* from A表 t1, B表 ...
Mysql数据库高级用法之视图、事务、索引、自连接、用户管理实例分析
09-08
MySQL数据库在高级应用中涉及了多个关键概念，包括视图、事务、索引、自连接以及用户管理。这些概念在数据库管理和数据操作中都扮演着至关重要的角色。 首先，让我们来详细了解一下视图。视图是数据库中的一个虚拟...
Oracle 查询视图
21号先锋者
03-18 7542
视图的查询：视图创建成果后，可以从视图中检索数据，这点和从表中检索数据一样。 还可以查询视图的全部信息和指定的数据行和列。 SELECT * FROM salvu50; desc 视图名称 查询表结构与视图结构写法一致，如下所示： ...
SQL对视图的查询
qq_42762824的博客
11-05 9124
title : 每日深耕，勤练不缀之sql子查询 本文应用sql数据均从github上下载 需要的小伙伴自取https://github.com/cystanford/sql_nba_data.git 虚拟表，本身是不具备数据的 只是为了体现安全性，封装性，以及权限性 下面是一个图，很好的诠释了这个关系 帮助我们使用表的一部分，另一方面也可以针对不同的用户制定不同的查询视图。 视图作为一张虚拟表，帮我们封装了底层和数据表的接口。它相当于一张表或者多张表的数据结果集，编写视图后，我们可以不断重用他，这个.
视图
栀子花的专栏
07-10 1412
1.什么是视图视图是基于一个表或多个表或视图的逻辑表，本身不包含数据，通过它可以对表里面的数据进行查询和修改。视图基于的表称为基表。视图是存储在数据字典里的一条select语句。 通过创建视图可以提取数据的逻辑上的集合或组合，不会存储数据。      视图包含简单视图和复杂视图，两者区别如下： 1.简单视图只从单表里获取数据，复杂视图从多表获取数据； 2.简单视图不包含函数和数据组，复杂视图包含；...
数据库 SQL Server 视图 创建视图 查询视图 修改视图 删除视图
qq_740785701的博客
12-06 1万+
文章目录1 视图概述2 创建视图3 修改视图4 删除视图 1 视图概述 视图就是将一个或多个表中的目标字段抽取出来形成的一个虚拟表。这个虚拟表和真实的表具有相同的功能。 2 创建视图 语法格式： create view view_name as select 字段 from tb1 where 条件 create view v_id_name as select departid,name from files 注： files表的建表语句，如下： create table files( userid
【SQL查询】视图_view
aaa321110的博客
11-30 313
转自：http://database.e800.com.cn/articles/2009/719/1248015564465_1.html 　　视图是从一个或几个基本表（或视图）导出的表。它与基本表不同，是一个虚表。数据库只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。所以基本表中的数据发生变化，从视图中查询出的数据也就随之改变了。从这个意义上讲，视图就像一个...
MongoDB创建和查询视图(一)
威赞的专栏
12-25 1296
本文整理mongodb的官方文档，介绍mongodb的视图创建和查询。Mongodb中，允许使用两种方式来创建视图。
SQL语句（四）视图定义、查询、更新和删除
热门推荐
学习笔记
07-16 12万+
一、视图定义 CREATE VIEW 视图名 AS 子查询 WITH CHECK OPTION //可以省略 二、视图查询、更新 和基本表的查询语句类似，只是把表名的位置换成视图名就可以 三、视图删除 DROP VIEW 视图名 【CASCADE】//CASCADE为级联删除，可以省略 四、实例 1.创建一个“上海晓莉贸易商行”供应商供应的零件视图V_DLMU_PartSupp...
mysql视图使用索引
07-28
MySQL视图本身并不支持索引的创建，因为视图只是一个虚拟表，它是根据查询语句动态生成的结果集。然而，当使用视图时，MySQL仍然可以使用底层表的索引。 当查询引用了视图时，MySQL优化器会尝试使用底层表的索引来...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值