视图与索引连表查询内/外联和子查询

1.视图

先介绍一下视图：

从SQL的角度来看，视图和表是相同的，两者的区别在于表中存储的是实际的数据，而视图中保存的是SELECT语句（视图本身并不存储数据）。
使用视图可以轻松完成跨多表查询数据等复杂操作。
视图中不能包含ORDER BY和对视图的更新操作（INSERT、DELETE、UPDATE）

创建视图的方法

CREATE VIEW 视图名称(<视图列名1><视图列名2><视图列名3>......)
AS
<SELECT语句>

创建一个视图


CREATE VIEW productSum(product_type,cnt_product)
AS
SELECT product_type,COUNT(*)
  FROM product
 GROUP BY product_type;

使用一个视图

SELECT product_type,cnt_product
  FROM productSum;

我们这里解释一下，我们首先通过创建了一个视图Productsum，然后通过SELECT语句获取视图中的数据，这样我们就不用了每次需要数据时都SELECT创建新的数据表，需要注意的是，原表product的数据产生变化后，视图数据也会随着变化，非常方便。
之所以能够实现上述功能，是因为视图就是保存好的SELECT语句定义视图时可以使用任何SELECT语句，既可以使用WHERE,GROUP BY,HAVING,也可以通过SELECT*来指定全部列。

使用视图的查询

在FROM子句中使用视图的查询，通常有如下两个步骤：

  1. 首先执行定义视图的SELECT语句。（创建视图）
  2. 根据得到的结果，再执行在FROM子句中使用视图的SELECT语句。（从视图中取数据）
  3. 也就是说视图的查询通常需要执行2条以上的SELECT语句。这里没有用两条，而用了两条以上，是因为还可能出现以视图为基础创建视图的多重视图。例如：我们可以以productsun视图创建productsumjim视图。
--基于productsum创建新的视图
CREATE VIEW productsumjim(product_type,cnt_product)
AS
SELECT product_type,cnt_product
  FROM productsum
 WHERE product_type="办公用品";

--查看视图
SELECT product_type,cnt_product
  FROM productsumjim;

虽然语法上没有错误，但是我们还是尽量少使用在视图上创建视图，这是因为对于多数DBMS来说，多重视图会降低SQL的性能。因此还是建议大家使用单一视图。

视图的限制 ——定义时不能使用ORDER BY

虽然我们前面说过定义视图时我们能使用任何SELECT语句，但是有一点例外就是ORDER BY，因此下面的语法是错误的。

CREATE VIEW productsum(product_type,cnt_product)
AS
SELECT product_type,COUNT(*)
  FROM product
 GROUP BY product_type
 OEDER BY product_type --这里是错误的，不能使用ORDER BY语句。
1.4视图的限制——对视图的更新

对视图的更新有着很严格的限制：

  • SELECT子句中未使用DISTINCT
  • FROM子句中只有一张表
  • 未使用GROUP BY子句
  • 未使用HAVING子句
    接下来，我们就对视图进行更新。
--创建一个视图
CREATE productjim(product_id,product_name,product_type,sale_price,purchase_price,regist_date)
AS
SELECT *
  FROM product
 WHERE product_type="办公用品";

--向视图中添加数据行
INSERT INTO productjim VALUES('0009','铅笔','办公用品',95,10,'2009-11-30')
删除视图
DROP VIEW 视图名称(<视图列名1>，<视图列名2>,......)
DROP VIEW productsum;
 子查询

我们先来说一下子查询和视图：子查询是将用来定义视图的SELECT语句直接用于FROM语句中。
我们使用子查询来实现一个视图

--在from子句中直接书写定义视图的SELECT语句
SELECT product_type,cnt_product
  FROM(SELECT product_type,COUNT(*) AS cnt_product
          FROM product
         GROUP BY product_type) AS productsum;

可以看出结果一模一样。但是有一个不同点：子查询是一张一次性视图，在数据库中并不会创建productsum,下次使用时需要重新定义。

这里我们使用了一个两层的子查询，原则上来说，子查询的层数没有明确的限制。

--一个三层的子查询
SELECT product_type, cnt_product
  FROM (SELECT *
          FROM (SELECT product_type, COUNT(*) AS cnt_product
                  FROM Product
                 GROUP BY product_type) AS ProductSum
         WHERE cnt_product = 4) AS ProductSum2;

关联子查询

介绍一下关联子查询：关联子查询会在细分的组内进行比较时使用。

  • 关联子查询和GROUP BY子句一样，也可以对表中的数据进行切分。
  • 关联子查询的结合条件如果未出现在子查询中就会发生错误。
    例如：我们查询各种商品种类中高于该种类的平均销售单价的商品。--发生错误的语句 SELECT product_id,product_name,sale_price FROM product WHERE sale_price > (SELECT AVG(sale_price) FROM product GROUP BY product_type);--这里会报错，因为不是唯一值
--发生错误的语句
SELECT product_id,product_name,sale_price
  FROM product
 WHERE sale_price > (SELECT AVG(sale_price) FROM product GROUP BY product_type);--这里会报错，因为不是唯一值

正确的方式应该是：

SELECT product_type,product_name,sale_price
  FROM product AS P1
 WHERE sale_price >(SELECT AVG(sale_price) FROM product AS P2
 WHERE P1.product_type=P2.product_type
 GROUP BY product_type);

结果如下：

数据库常见的面试题

设计到的表有：

  • t_student：学生信息表，包含学生编号（student_id）、学生姓名（student_name）等字段。
  • t_course：课程信息表，包含课程编号（course_id）、课程名称（course_name）等字段。
  • t_teacher：教师信息表，包含教师编号（teacher_id）、教师姓名（teacher_name）等字段。
  • t_score：成绩信息表，包含学生编号（student_id）、课程编号（course_id）、成绩（score）等字段。
面试题1/7

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

SELECT s.student_id, s.student_name, sc1.course_score AS score1, sc2.course_score AS score2
FROM student s
JOIN score sc1 ON s.student_id = sc1.student_id AND sc1.course_id = '01'
JOIN score sc2 ON s.student_id = sc2.student_id AND sc2.course_id = '02'
WHERE sc1.course_score > sc2.course_score;

2.查询同时存在"01"课程和"02"课程的情况：

SELECT DISTINCT s.student_id, s.student_name
FROM student s
JOIN score sc1 ON s.student_id = sc1.student_id AND sc1.course_id = '01'
JOIN score sc2 ON s.student_id = sc2.student_id AND sc2.course_id = '02';

3.查询存在"01"课程但可能不存在"02"课程的情况(不存在时显示为 null ) 

SELECT s.student_id, s.student_name, COALESCE(sc2.course_score, NULL) AS score2
FROM student s
LEFT JOIN score sc2 ON s.student_id = sc2.student_id AND sc2.course_id = '02';

4.查询不存在"01"课程但存在"02"课程的情况：

SELECT s.student_id, s.student_name
FROM student s
LEFT JOIN score sc1 ON s.student_id = sc1.student_id AND sc1.course_id = '01'
WHERE sc1.course_score IS NULL;

5.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩：

SELECT student_id, student_name, AVG(course_score) AS average_score
FROM student s
JOIN score sc ON s.student_id = sc.student_id
GROUP BY student_id, student_name
HAVING average_score >= 60;

6.查询在t_mysql_score表存在成绩的学生信息：

SELECT DISTINCT s.student_id, s.student_name
FROM student s
JOIN score sc ON s.student_id = sc.student_id;

7.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )：

SELECT s.student_id, s.student_name, COUNT(DISTINCT sc.course_id) AS course_count, SUM(COALESCE(sc.course_score, 0)) AS total_score
FROM student s
LEFT JOIN score sc ON s.student_id = sc.student_id
GROUP BY s.student_id, s.student_name;

面试题8/15

8.查询「李」姓老师的数量：

SELECT COUNT(*) AS count
FROM teacher t
WHERE t.teacher_name LIKE '李%';

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

SELECT DISTINCT s.student_id, s.student_name
FROM student s
JOIN score sc ON s.student_id = sc.student_id
JOIN course c ON sc.course_id = c.course_id
JOIN teacher t ON c.teacher_id = t.teacher_id
WHERE t.teacher_name = '张三';

 10.查询没有学全所有课程的同学的信息

SELECT s.student_id, s.student_name
FROM student s
WHERE NOT EXISTS (
    SELECT * FROM course c
    WHERE NOT EXISTS (
        SELECT * FROM score sc WHERE sc.student_id = s.student_id AND sc.course_id = c.course_id
    )
);

11.查询没学过"张三"老师讲授的任一门课程的学生姓名：

SELECT s.student_name
FROM student s
WHERE NOT EXISTS (
    SELECT * FROM course c
    JOIN score sc ON c.course_id = sc.course_id
    JOIN teacher t ON c.teacher_id = t.teacher_id
    WHERE t.teacher_name = '张三' AND sc.student_id = s.student_id
);

12.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩：

SELECT s.student_id, s.student_name, AVG(sc.course_score) AS average_score
FROM student s
JOIN score sc ON s.student_id = sc.student_id
WHERE sc.course_score < 60
GROUP BY s.student_id, s.student_name
HAVING COUNT(sc.course_id) >= 2;

13.检索"01"课程分数小于 60，按分数降序排列的学生信息：

SELECT s.student_id, s.student_name, sc.course_score
FROM student s
JOIN score sc ON s.student_id = sc.student_id AND sc.course_id = '01'
WHERE sc.course_score < 60
ORDER BY sc.course_score DESC;

14.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩：

SELECT s.student_id, s.student_name, sc.course_id, sc.course_score, (SELECT AVG(course_score) FROM score WHERE student_id = s.student_id) AS average_score
FROM student s
JOIN score sc ON s.student_id = sc.student_id
ORDER BY average_score DESC;

15.查询各科成绩最高分、最低分和平均分：

SELECT course_id, MAX(course_score) AS highest_score, MIN(course_score) AS lowest_score, AVG(course_score) AS average_score,
       SUM(CASE WHEN course_score >= 60 THEN 1 ELSE 0 END) / COUNT(*) AS pass_rate,
       SUM(CASE WHEN course_score >= 70 AND course_score < 80 THEN 1 ELSE 0 END) / COUNT(*) AS medium_rate,
       SUM(CASE WHEN course_score >= 80 AND course_score < 90 THEN 1 ELSE 0 END) / COUNT(*) AS good_rate,
       SUM(CASE WHEN course_score >= 90 THEN 1 ELSE 0 END) / COUNT(*) AS excellent_rate
FROM score
GROUP BY course_id;

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.mfbz.cn/a/304154.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

案例分享：当前高端低延迟视频类产品方案分享（内窥镜、记录仪、车载记录仪、车载环拼、车载后视镜等产品）

案例分享：当前高端低延迟视频类产品方案分享（内窥镜、记录仪、车载记录仪、车载环拼、车载后视镜等产品）

若该文为原创文章&#xff0c;转载请注明出处 本文章博客地址&#xff1a;https://hpzwl.blog.csdn.net/article/details/135439369 红胖子(红模仿)的博文大全&#xff1a;开发技术集合&#xff08;包含Qt实用技术、树莓派、三维、OpenCV、OpenGL、ffmpeg、OSG、单片机、软硬结…
阅读更多...
Qt QWidget窗口基类

Qt QWidget窗口基类

文章目录 1 QWidget介绍2 如何显示 QWidget窗口2.1 新建基于QWidget的窗口类2.2 再添加一个QWidget窗口类2.3 显示新添加的 QWidget窗口 3 常用的属性和方法3.1 窗口位置3.2 窗口大小3.3 窗口标题3.4 窗口图标3.5 资源文件 4 实例 1 QWidget介绍 Qt 中的常用控件&#xff0c;比…
阅读更多...
flex弹性盒子常用的布局属性详解

flex弹性盒子常用的布局属性详解

想必大家在开发中经常会用到flex布局。而且还会经常用到 justify-content 属性实现分栏等等 接下来给大家分别讲一下 justify-content 的属性值。 以下是我敲的效果图大家可以清晰看出区别 space-between 属性值可以就是说两端对齐 space-evenly 属性值是每个盒子之间的…
阅读更多...
觉得伺服方案比较难开发的可以看过来

觉得伺服方案比较难开发的可以看过来

参数 TMCM-1690是单轴FOC伺服电机控制模块集成预驱适合三相BLDC/PMSM和DC有刷伺服电机&#xff0c;带有高达1.5A栅极驱动电流和60V(48 V nominal)供电提供了UART(RS232-/RS485ready),CAN 和 EtherCAT 通讯接口支持TML,CANopen,或 CANopen-over-EtherCAT通讯协议TMCM-1690 支持增…
阅读更多...
[足式机器人]Part2 Dr. CAN学习笔记 - Ch03 傅里叶级数与变换

[足式机器人]Part2 Dr. CAN学习笔记 - Ch03 傅里叶级数与变换

本文仅供学习使用 本文参考&#xff1a; B站&#xff1a;DR_CAN Dr. CAN学习笔记-Ch03 傅里叶级数与变换 1. 三角函数的正交性2. 周期为 2 π 2\pi 2π的函数展开为傅里叶级数3. 周期为 2 L 2L 2L的函数展开4. 傅里叶级数的复数形式5. 从傅里叶级数推导傅里叶变换FT6. 总结 1. …
阅读更多...
【控制篇 / 策略】(7.4) ❀ 01. IP地理位置数据库和地理地址对象 ❀ FortiGate 防火墙

【控制篇 / 策略】(7.4) ❀ 01. IP地理位置数据库和地理地址对象 ❀ FortiGate 防火墙

【简介】在很多使用环境下&#xff0c;我们需要对指定国家的IP地址进行允许或禁止访问操作&#xff0c;例如只允许访问国内IP。以前只能手动添加IP地址对象到地址组&#xff0c;繁杂且效率低下&#xff0c;Fortinet提供了基于地理位置的IP库&#xff0c;就可以解决这个问题。 I…
阅读更多...
手把手教你学会接口自动化系列一-浅浅地尝试编写登录接口的自动化代码

手把手教你学会接口自动化系列一-浅浅地尝试编写登录接口的自动化代码

老痞既然要带你学会接口自动化，那么老痞肯定是要把你当做0基础，从最基本的开始写起，一步步写到最后。 我最先打开项目。 第一步就是需要登录，所以这个时候，我们查看下登录的接口，我抓到接口如下： 第一个就是登录的接口，接口的地址为： http://192.168.0.134:8081/log…
阅读更多...
【PostgreSQL创建索引的锁分析和使用注意】

【PostgreSQL创建索引的锁分析和使用注意】

1.1 创建普通B-tree索引的整体流程 如下是梳理的创建普通B-tree索引的大概流程&#xff0c;可供参考。 1.校验新索引的Catalog元数据|语法解析 ---将创建索引的sql解析成IndexStmt结构&#xff5c;校验B-Tree的handler -----校验内核是否支持该类型的索引,在pg_am中查找&q…
阅读更多...
2024年游泳耳机十大品牌排行榜，游泳耳机哪个牌子好

2024年游泳耳机十大品牌排行榜，游泳耳机哪个牌子好

游泳耳机市场正呈现蓬勃发展的趋势&#xff0c;而在众多品牌中选择一款适合自己的游泳耳机变得愈发重要。游泳不仅是锻炼身体的有效方式&#xff0c;还是缓解压力的良好途径。然而&#xff0c;游泳时的单调可能成为一些人的困扰&#xff0c;特别是那些希望在运动中欣赏音乐或聆…
阅读更多...
二叉树的层序遍历经典问题（算法村第六关白银挑战）

二叉树的层序遍历经典问题（算法村第六关白银挑战）

基本的层序遍历与变换 二叉树的层序遍历 102. 二叉树的层序遍历 - 力扣&#xff08;LeetCode&#xff09; 给你二叉树的根节点 root &#xff0c;返回其节点值的 层序遍历 。 &#xff08;即逐层地&#xff0c;从左到右访问所有节点&#xff09;。 示例 1&#xff1a; 输入…
阅读更多...
25 心形按钮

25 心形按钮

效果演示 实现了一个心形的心形图案&#xff0c;当用户点击图案时&#xff0c;图案会旋转并缩小&#xff0c;同时背景颜色会变成白色。 Code <div class"love"><input id"switch" type"checkbox"><label class"love-heart&…
阅读更多...
*5.1 Global Memory Bandwidth

*5.1 Global Memory Bandwidth

并行程序的执行速度可能因计算硬件的资源限制而有很大差异。虽然管理并行代码和硬件资源约束之间的交互对于在几乎所有并行编程模型中实现高性能很重要&#xff0c;但这是一种实用技能&#xff0c;最好通过为高性能设计的并行编程模型中的实践练习来学习。在本章中&#xff0c;…
阅读更多...
xss-labs（1-5）

xss-labs（1-5）

环境准备： 靶场下载：下载仓库 zhangmanhui/xss-labs - Gitee.com 启动phpStudy 搭建将文件解压拉到phpStudy的www目录下就行 最后直接访问：127.0.0.1/xss-labs-master/ 最后再准备一个浏览器的插件用来发送请求：HackBar 插件都配置好了，直接加载到你的浏览器的扩展…
阅读更多...
以unity技术开发视角对android权限的讲解

以unity技术开发视角对android权限的讲解

目录 前言 Android权限分类 普通权限 普通权限定义 普通权限有哪些 危险权限 危险权限的定义 危险权限有哪些 动态申请权限实例 申请单个权限实例 第一步&#xff1a;在清单文件中声明权限 第二步&#xff1a;在代码中进行动态申请权限 申请多个权限实例 第一步&am…
阅读更多...
【win11 绕过TPM CPU硬件限制安装】

【win11 绕过TPM CPU硬件限制安装】

Qt编程指南 VX&#xff1a;hao541022348 ■ 下载iso文件■ 右键文件点击装载出现如下问题■ 绕过TPM CPU硬件限制安装方法■ 虚拟机安装win11 ■ 下载iso文件 选择Windows11 &#xff08;multi-edition ISO&#xff09;在选择中文 ■ 右键文件点击装载出现如下问题 ■ 绕过T…
阅读更多...
希尔顿花园酒店喜迎入华十周年里程碑

希尔顿花园酒店喜迎入华十周年里程碑

【2024年1月8日&#xff0c;中国&#xff0c;上海】作为希尔顿集团旗下标志性高端精选服务酒店品牌&#xff0c;希尔顿花园酒店于今年正式迎来其在华经营十周年的里程碑。自2014年中国首家希尔顿花园酒店在深圳开业以来&#xff0c;中国市场已经成为希尔顿花园酒店全球增长的重…
阅读更多...
C++中的返回值优化（RVO）

C++中的返回值优化（RVO）

一、命名返回值优化&#xff08;NRVO&#xff09; 是Visual C2005及之后版本支持的优化。 具体来说&#xff0c;就是一个函数的返回值如果是一个对象。那么&#xff0c;正常的返回语句的执行过程是&#xff0c;把这个对象从当前函数的局部作用域&#xff0c;或者叫当前函数的…
阅读更多...
2024第15届电子教育、电子商务、电子管理和电子学习国际会议

2024第15届电子教育、电子商务、电子管理和电子学习国际会议

第十五届电子教育、电子商务、电子管理和电子学习国际会议&#xff08;IC4E 2024&#xff09;将于2024年3月18日-21日在日本福冈举办。本次会议以电子技术为核心&#xff0c;围绕电子教育、电子商务、电子管理以及电子学习等各个方面展开研讨&#xff0c;为相关领域的专家学者们…
阅读更多...
【Spring 篇】深入浅出：用Spring注解开发的奇妙之旅

【Spring 篇】深入浅出：用Spring注解开发的奇妙之旅

在编程的世界里&#xff0c;Spring框架如同一位慈祥的导师&#xff0c;为我们打开了无尽可能性的大门。而在Spring的广袤领域中&#xff0c;注解是我们最亲密的伙伴之一。本篇博客将深入浅出地介绍使用Spring注解进行开发的奇妙之旅&#xff0c;为你解开注解的神秘面纱。 前奏…
阅读更多...
2024年远控软件年度盘点：安全、稳定、功能之选

2024年远控软件年度盘点：安全、稳定、功能之选

这目录 前言2024年热门远控软件ToDesk向日葵TeamViewerAnyDeskSplashtopAirDroidChrome Remote DesktopMicrosoft远程桌面RayLinkParallels Access 远程控制软件如何选择&#xff1f;1、功能性2、安全性3、易用性4、稳定性 未来展望与建议结语 前言 随着信息技术不断发展&…
阅读更多...
最新文章

Copyright @ 2022~2023