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

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

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

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

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

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

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优点

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

2.3.2缺点

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

2.4索引的分类

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

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

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

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

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

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

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

非聚集索引（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

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

上一篇：jvm垃圾回收相关的算法
下一篇：2024前端炫酷源码分享（附效果图及在线演示）

特权帐户安全 CyberArk
它隔离、控制和监控整个数据中心的所有特权活动，补充了CyberArk在市场领先的特权身份管理套件‌。‌CyberArk PTA（Privileged Threat Analytic）‌：特权威胁分析，
阅读更多2024-11-16
消息中间件分类
1. JMS（Java Message Service）：这是 Java 平台上的一套消息服务 API 标准，它定义了消息系统的基本功能，如消息的创建、发送、接收和读取等。5. 其他消息中间件：除了上
阅读更多2024-11-16
企业知识中台：构建智慧企业的核心
知识中台是企业数字化转型的重要一环，它能够帮助企业更有效地管理和利用知识资产，提升企业的竞争力。在选择搭建知识中台的工具时，HelpLook是一个值得考虑的选择。HelpLook以其直观易用的内容管理
阅读更多2024-11-16
如何恢復電腦IP地址的手動設置？
如何恢復電腦IP地址的手動設置。
阅读更多2024-11-16
OSPF动态路由配置(Cisco Packet Tracer)
OSPF路由协议通过向全网扩散本设备的链路状态信息，使网络中每台设备最终同步一个具有全网链路状态的数据库，然后路由器采用OSPF算法，以自己为根，计算到达其他网络的最短路径，最终形成全网路由信息。（4
阅读更多2024-11-16
HarmonyOS ArkTs 解决流式传输编码问题
使用 HarmonyOS ArkTs 解决流式传输中的编码和 JSON 解析问题。
阅读更多2024-11-16
每日论文20-24RFIC四核三模带自动模式跟踪输出缓冲器的VCO
每日论文20-24RFIC四核三模带自动模式跟踪输出缓冲器的VCO
阅读更多2024-11-16
PG数据库数据库时间字段开始时间和结束时间，判断和查询条件的开始和截止时间存在交集，SQL如何编写
假设你的表名是 events，开始时间字段为 start_time，结束时间字段为 end_time，而你要检查的时间段是 query_start_time 和 query_end_time。在 Po
阅读更多2024-11-16
cls(c基础)
作用清屏格式#include
阅读更多2024-11-16
see的本质是什么？
如果你喜欢看【龙虎斗】，看【猫蛇大战】相关的视频，你会发现，蛇的头会跟随性地转动，这不仅是因为蛇很紧张，更重要的，是因为蛇的眼睛的问题，蛇的眼睛对稍远一点的东西，其实是看不太清楚的，而且蛇的眼睛是没有
阅读更多2024-11-16

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

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.思维导图

相关文章