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

发布时间:2024年01月06日

目录

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

查询结果

?4.思维导图

文章来源:https://blog.csdn.net/m0_74934282/article/details/135425435
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。