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

2024-01-06 17:44
文章标签 mysql 查询 连接 索引 视图 database 案列

本文主要是介绍[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.思维导图

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

这篇关于[MySQL]视图索引以及连接查询案列的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/577104。

相关文章

oracle数据库索引失效的问题及解决

oracle数据库索引失效的问题及解决

《oracle数据库索引失效的问题及解决》本文总结了在Oracle数据库中索引失效的一些常见场景,包括使用isnull、isnotnull、!=、、、函数处理、like前置%查询以及范围索引和等值索引... 目录oracle数据库索引失效问题场景环境索引失效情况及验证结论一结论二结论三结论四结论五总结ora
阅读更多...
Mysql 中的多表连接和连接类型详解

Mysql 中的多表连接和连接类型详解

《Mysql中的多表连接和连接类型详解》这篇文章详细介绍了MySQL中的多表连接及其各种类型,包括内连接、左连接、右连接、全外连接、自连接和交叉连接,通过这些连接方式,可以将分散在不同表中的相关数据... 目录什么是多表连接？1. 内连接（INNER JOIN）2. 左连接（LEFT JOIN 或 LEFT
阅读更多...
mysql重置root密码的完整步骤(适用于5.7和8.0)

mysql重置root密码的完整步骤(适用于5.7和8.0)

《mysql重置root密码的完整步骤(适用于5.7和8.0)》：本文主要介绍mysql重置root密码的完整步骤,文中描述了如何停止MySQL服务、以管理员身份打开命令行、替换配置文件路径、修改... 目录第一步：先停止mysql服务，一定要停止！方式一：通过命令行关闭mysql服务方式二：通过服务项关闭
阅读更多...
SQL Server数据库磁盘满了的解决办法

SQL Server数据库磁盘满了的解决办法

《SQLServer数据库磁盘满了的解决办法》系统再正常运行,我还在操作中,突然发现接口报错,后续所有接口都报错了,一查日志发现说是数据库磁盘满了,所以本文记录了SQLServer数据库磁盘满了的解... 目录问题解决方法删除数据库日志设置数据库日志大小问题今http://www.chinasem.cn天发
阅读更多...
mysql主从及遇到的问题解决

mysql主从及遇到的问题解决

《mysql主从及遇到的问题解决》本文详细介绍了如何使用Docker配置MySQL主从复制,首先创建了两个文件夹并分别配置了`my.cnf`文件,通过执行脚本启动容器并配置好主从关系,文中还提到了一些... 目录mysql主从及遇到问题解决遇到的问题说明总结mysql主从及遇到问题解决1.基于mysql
阅读更多...
Python中列表的高级索引技巧分享

Python中列表的高级索引技巧分享

《Python中列表的高级索引技巧分享》列表是Python中最常用的数据结构之一,它允许你存储多个元素,并且可以通过索引来访问这些元素,本文将带你深入了解Python列表的高级索引技巧,希望对... 目录1.基本索引2.切片3.负数索引切片4.步长5.多维列表6.列表解析7.切片赋值8.删除元素9.反转列表
阅读更多...
Spring Boot实现多数据源连接和切换的解决方案

Spring Boot实现多数据源连接和切换的解决方案

《SpringBoot实现多数据源连接和切换的解决方案》文章介绍了在SpringBoot中实现多数据源连接和切换的几种方案,并详细描述了一个使用AbstractRoutingDataSource的实... 目录前言一、多数据源配置与切换方案二、实现步骤总结前言在 Spring Boot 中实现多数据源连接
阅读更多...
MySQL的索引失效的原因实例及解决方案

MySQL的索引失效的原因实例及解决方案

《MySQL的索引失效的原因实例及解决方案》这篇文章主要讨论了MySQL索引失效的常见原因及其解决方案,它涵盖了数据类型不匹配、隐式转换、函数或表达式、范围查询、LIKE查询、OR条件、全表扫描、索引... 目录1. 数据类型不匹配2. 隐式转换3. 函数或表达式4. 范围查询之后的列5. like 查询6
阅读更多...
Redis KEYS查询大批量数据替代方案

Redis KEYS查询大批量数据替代方案

《RedisKEYS查询大批量数据替代方案》在使用Redis时,KEYS命令虽然简单直接,但其全表扫描的特性在处理大规模数据时会导致性能问题,甚至可能阻塞Redis服务,本文将介绍SCAN命令、有序... 目录前言KEYS命令问题背景替代方案1.使用 SCAN 命令2. 使用有序集合（Sorted Set）
阅读更多...
Linux下MySQL8.0.26安装教程

Linux下MySQL8.0.26安装教程

《Linux下MySQL8.0.26安装教程》文章详细介绍了如何在Linux系统上安装和配置MySQL,包括下载、解压、安装依赖、启动服务、获取默认密码、设置密码、支持远程登录以及创建表,感兴趣的朋友... 目录1.找到官网下载位置1.访问mysql存档2.下载社区版3.百度网盘中2.linux安装配置1.
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2