关系数据库标准SQL语言（二）

已于 2025-05-25 19:57:35 修改
阅读量1.1k 收藏 29
点赞数 31
文章标签： sql 数据库 大数据
于 2025-05-25 19:56:36 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/a1943481463/article/details/148212780
版权

5.索引

  • 定义：是一种数据结构，用于加快数据的检索速度。它类似于一本书的目录，通过记录表中的值以及各值的存储位置，帮助数据库程序快速找到表中的数据，而不必扫描整个表。

  • 作用：能显著提升数据查询性能，减少查询时的磁盘 I/O 操作。例如在一个有大量记录的Students表中，若要查找特定学号的学生信息，有索引时可快速定位，无索引则可能需全表扫描。

  • 分类：

    • 按存储结构：
      • 聚簇索引：决定表中数据的物理存储顺序，表中数据按索引列的值排序存放，一个表只能有一个聚集索引 。如在学生表中以学号列创建聚集索引，数据会按学号顺序物理存储 。
      • 非聚集索引：不改变表中数据的物理存储顺序，数据与索引分开存储，通过索引指向的地址与表中的数据相关联，一个表可以有多个非聚集索引 。
    • 按数据唯一性：
      • 唯一索引：确保索引列中的值唯一，不允许出现重复值，可用于加速对唯一值的查询，也可作为主键约束 。
      • 非唯一索引：索引列中的值可以重复。
    • 按键列个数：
      • 单列索引：基于单个列创建的索引。
      • 多列索引：基于多个列组合创建的索引 。

  • 利弊：

    • 优点：加快数据查询速度，减少查询响应时间；辅助实现表与表之间的参照完整性等 。
    • 缺点：创建和维护索引会占用额外的存储空间；对表进行增、删、改操作时，数据库需要同时更新索引，会增加操作开销，降低这些操作的执行效率 。

  • 索引创建

    1. 聚簇索引
      • CREATE CLUSTER INDEX Stusname ON Student(Sname);
      • CREATE CLUSTERED INDEX Stusname ON Student(Sname);
    2. 唯一索引
      • 为Student表按学号升序建唯一索引：CREATE UNIQUE INDEX Stusno ON Student(Sno);
      • 为Course表按课程号升序建唯一索引：CREATE UNIQUE INDEX Coucno ON Course(Cno);
      • 为SC表按学号升序和课程号降序建唯一索引：CREATE UNIQUE INDEX SCno ON SC(Sno ASC, Cno DESC);

    索引删除

    1. 通用格式：DROP INDEX <索引名>; ，如删除Student表的Stusname索引：DROP INDEX Stusname;
    2. SQL Server 特定格式
      • DROP INDEX Stusname ON Student;
      • DROP INDEX Student.Stusname;

6.查询

  1. 基本查询格式：SELECT语句，可选择列、指定表或视图、添加条件过滤、分组、排序等操作。例如SELECT Sno, Sname FROM Student WHERE Sdept = 'CS' ORDER BY Sno;，从Student表中查询计算机系学生的学号和姓名，并按学号升序排序。
  2. 单表查询
    • 选择列：可指定列名或使用通配符 “*” 查询全部列，还可对列进行计算和重命名。例如：
-- 查询全体学生的学号和姓名
SELECT Sno,Sname
FROM Student;
-- 查询全体学生的姓名及其出生年份
SELECT Sname,2024-Sage AS BirthYear
FROM Student;
  • 选择元组：消除重复行用DISTINCT关键字；根据条件查询使用各种谓词，如比较、范围、集合、字符匹配、空值判断等；对查询结果排序用ORDER BY子句。例如：
-- 查询选修了课程的学生学号，消除重复行
SELECT DISTINCT Sno
FROM SC;
-- 查询年龄在20 - 23之间的学生的姓名、系别和年龄
SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage BETWEEN 20 AND 23;
-- 查询所有姓刘的学生的姓名、学号和性别
SELECT Sname,Sno,Ssex
FROM Student
WHERE Sname LIKE '刘%';
查询条件谓词
比 较=，>，<，>=，<=，!=，<>，!>，!<； NOT+上述比较符
确定范围BETWEEN AND，NOT BETWEEN AND
确定集合IN，NOT IN
字符匹配LIKE，NOT LIKE
空 值IS NULL，IS NOT NULL
多重条件AND，OR
  • 集函数与分组：常用集函数有COUNT、SUM、AVG、MAX、MIN；分组使用GROUP BY子句，可结合HAVING子句筛选分组结果。例如：
-- 查询学生总数
SELECT COUNT(*)
FROM Student;
-- 求各个课程号及相应的选课人数
SELECT Cno, COUNT(Sno)
FROM SC
GROUP BY Cno;
-- 查询选修了3门以上课程的学生学号
SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*)>3;

7.连接

连接类型语法特征结果集包含内容典型场景
隐式内连接FROM 表1, 表2, ... WHERE 连接条件仅包含所有表均满足连接条件的记录查询学生及其对应课程的成绩（必须有选课记录）
显式内连接FROM 表1 INNER JOIN 表2 ON 连接条件同上（与隐式内连接完全等价，仅语法不同）同上，更推荐使用显式语法提高可读性
左外连接FROM 表1 LEFT OUTER JOIN 表2 ON 连接条件包含左表所有记录，右表无匹配时字段为 NULL查询所有学生（包括未选课学生）及其课程成绩
右外连接FROM 表1 RIGHT OUTER JOIN 表2 ON 连接条件包含右表所有记录，左表无匹配时字段为 NULL极少见（如以课程表为主，查询所有课程及选课学生）
全外连接FROM 表1 FULL OUTER JOIN 表2 ON 连接条件包含左右表所有记录，无匹配时字段为 NULL对比两张表数据完整性（如学生表与课程表的差异）
  1. 连接查询
    • 等值与非等值连接：连接条件指定连接的列和比较运算符，自然连接是特殊的等值连接。例如：
-- 查询每个学生及其选修课程的情况（等值连接）
SELECT Student.*, SC.*
FROM Student, SC
WHERE Student.Sno=SC.Sno;
-- 用自然连接完成上述查询
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student, SC
WHERE Student.Sno=SC.Sno;
  • 自身连接：同一表与自身连接，用于查询间接关系。例如：
-- 查询每一门课的间接先修课（即先修课的先修课）
SELECT FIRST.Cno, SECOND.Cpno
FROM Course FIRST, Course SECOND
WHERE FIRST.Cpno=SECOND.Cno;
  • 外连接：包括左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）、全连接（FULL JOIN），用于保留特定表的所有行。
1. 左外连接（LEFT OUTER JOIN）
  • 定义：查询结果会保留左表中的所有记录 ，然后根据连接条件匹配右表中的记录。若右表中无匹配记录，对应字段以 NULL 填充。
  • 语法：FROM table1 LEFT OUTER JOIN table2 ON table1.key = table2.key （table1 为左表，table2 为右表，key 为连接条件 ）。
  • 应用场景：常用于获取主表（左表）全部信息及其关联子表相关信息，如查询所有学生（主表）及其选课记录（子表），即使学生没选课也能在结果中显示，只是选课相关字段为 NULL 。
2. 右外连接（RIGHT OUTER JOIN）
  • 定义：结果保留右表中的全部记录，依据连接条件去匹配左表记录，左表无匹配时对应字段为 NULL 。
  • 语法：FROM table1 RIGHT OUTER JOIN table2 ON table1.key = table2.key （table2 是右表，table1 是左表 ）。
  • 应用场景：相对较少用，比如当以选课记录（右表）为主，要查看所有选课记录对应的学生（左表）信息，即使某些选课记录无对应学生（异常情况） 。实际中很多时候可通过交换表顺序用左外连接替代。
3. 全连接（FULL JOIN ）
  • 定义：将左表和右表的所有记录都包含在结果集中，按连接条件匹配，无匹配的字段用 NULL 填充。
  • 语法：FROM table1 FULL JOIN table2 ON table1.key = table2.key 。
  • 应用场景：使用场景也不多，一般用于全面对比两个表数据完整性，比如找出学生表和选课表中各自独有的记录，排查可能的数据错误等情况 。
4. 例题

原始 SQL 中的WHERE Student.Sno=SC.Sno(*)使用了Oracle 旧版的外连接语法（(*)符号），这是 Oracle 数据库在 ANSI 标准之前的 proprietary 语法，用于表示左外连接。这种语法有几个问题：

  1. 非标准：只有 Oracle 支持，其他数据库（如 MySQL、SQL Server）不支持
  2. 可读性差：语法符号不直观，容易混淆
  3. 复杂查询中容易出错：在多表连接时难以维护
ANSI 标准的外连接语法

现代 SQL 标准（ANSI）提供了统一的外连接语法：

-- 左外连接（保留左表所有记录，右表匹配不上的字段为NULL）
SELECT ...
FROM table1
LEFT OUTER JOIN table2 ON table1.key = table2.key;

-- 右外连接（保留右表所有记录，左表匹配不上的字段为NULL）
SELECT ...
FROM table1
RIGHT OUTER JOIN table2 ON table1.key = table2.key;

-- 全连接（保留左右表所有记录，匹配不上的字段为NULL）
SELECT ...
FROM table1
FULL OUTER JOIN table2 ON table1.key = table2.key;
改写后的 SQL 分析
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade                  
FROM Student
LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno);

这个改写有以下改进：

  1. 使用标准语法：LEFT OUTER JOIN是 ANSI 标准语法，所有主流数据库都支持
  2. 分离连接条件：将连接条件（ON子句）与过滤条件（WHERE子句）明确分开，逻辑更清晰
  3. 功能等价：与原始 SQL 中的WHERE Student.Sno=SC.Sno(*)功能完全相同，保留所有学生记录，无论是否有选课记录
不同外连接的应用场景
  1. 左外连接（LEFT OUTER JOIN）
    • 典型场景：查询所有学生及其选课记录，即使学生没有选课（结果中 SC 表的字段为 NULL）
    • 示例：统计各学生的选课数量（包括未选课学生）
  2. 右外连接（RIGHT OUTER JOIN）
    • 典型场景：查询所有选课记录及其对应的学生信息，即使某些选课记录没有对应的学生（这种情况很少见）
    • 实际中右外连接很少使用，因为通常可以通过交换表顺序用左外连接替代
  3. 全连接（FULL OUTER JOIN）
    • 典型场景：查询所有学生和所有选课记录，包括没有选课的学生和没有对应学生的选课记录（这种情况更少见）
    • 示例：对比学生表和选课表的完整性，找出可能的数据错误
为什么选择左外连接？

在学生 - 选课的场景中，左外连接是最常用的，因为业务需求通常是：

  • 查询所有学生的信息，无论他们是否选了课
  • 如果学生没有选课，选课相关字段（Cno, Grade）显示为 NULL
  • 可以通过COALESCE(Grade, 0)等函数进一步处理 NULL 值
进一步优化建议

如果需要明确排除没有选课的学生，可以在左外连接后加 WHERE 过滤：

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,SC.Cno,Grade                  
FROM Student
LEFT OUTER JOIN SC ON Student.Sno=SC.Sno
WHERE SC.Sno IS NOT NULL;  -- 只保留有选课记录的学生

这个查询实际上等价于内连接：

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,SC.Cno,Grade                  
FROM Student
INNER JOIN SC ON Student.Sno=SC.Sno;
  • 例如：
-- 左外连接查询学生及其选修课程情况（ANSI标准）
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student
LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno);
  • 复合条件连接：多个连接条件和其他条件组合查询。例如：
-- 查询选修2号课程且成绩在90分以上的所有学生
SELECT Student.Sno, Sname
FROM Student, SC
WHERE Student.Sno=SC.Sno AND
    SC.Cno='2' AND
    SC.Grade>90;
  1. 嵌套查询
    • 概念：一个查询块嵌套在另一个查询块的条件中。
    • 类型：带有IN、比较运算符、ANY或ALL、EXISTS谓词的子查询，不同类型适用于不同场景，部分子查询可相互转换。例如：
-- 带有IN谓词的子查询，查询与刘晨在同一个系学习的学生
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN (
    SELECT Sdept
    FROM Student
    WHERE Sname='刘晨'
);
-- 带有比较运算符的子查询，重写上述查询
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept = (
    SELECT Sdept
    FROM Student
    WHERE Sname='刘晨'
);
  1. 集合查询：使用UNION（并）、INTERSECT（交）、EXCEPT（差）操作对查询结果集进行处理。例如
-- 查询计算机科学系的学生及年龄不大于19岁的学生
SELECT *
FROM Student
WHERE Sdept='CS'
UNION
SELECT *
FROM Student
WHERE Sage<=19;
-- 查询计算机科学系中年龄不大于19岁的学生（交操作）
SELECT *
FROM Student
WHERE Sdept='CS'
INTERSECT
SELECT *
FROM Student
WHERE Sage<=19;
  1. 基于派生表的查询：子查询在FROM子句中生成临时表，作为主查询的对象。例如：
-- 找出每个学生超过他自己选修课程平均成绩的课程号
SELECT Sno, Cno
FROM SC, (
    SELECT Sno, AVG(Grade)
    FROM SC
    GROUP BY Sno
) AS Avg_sc(avg_sno,avg_grade)
WHERE SC.Sno=Avg_sc.avg_sno AND
    SC.grade>=Avg_sc.avg_grade;

8.数据更新（增删改查）

  1. 插入数据
    • 插入单个元组：INSERT INTO...VALUES语句，指定表名和列值。例如：
-- 将一个新学生记录插入到Student表中
INSERT INTO Student
VALUES ('201215128','陈冬','男',18,'IS');
  • 插入子查询结果：INSERT INTO...子查询语句，将子查询结果插入表中。例如：
-- 对每一个系，求学生的平均年龄，并把结果存入数据库
CREATE TABLE Deptage(Sdept CHAR(15), Avgage SMALLINT);
INSERT INTO Deptage(Sdept,Avgage)
SELECT Sdept,Avg(Sage)
FROM Student
GROUP BY Sdept;
  1. 修改数据：UPDATE语句，指定表名、修改的列和表达式，可带条件修改单个或多个元组。例如
-- 将学生201215121的年龄改为22岁
UPDATE Student
SET Sage=22
WHERE Sno='201215121';
-- 将所有学生的年龄增加1岁
UPDATE Student
SET Sage=Sage+1;
  1. 删除数据：DELETE FROM语句，带条件删除单个或多个元组，注意数据一致性和事务处理。例如：
-- 删除学号为201215128的学生记录
DELETE FROM Student
WHERE Sno='201215128';
-- 删除所有学生的选课记录
DELETE FROM SC;

9.视图

1.视图的概念

视图（View）是数据库中一个虚表，它的数据来自一个或多个基本表（或其他视图），但本身不存储实际数据。数据库仅保存视图的定义（即查询逻辑），数据仍存放在原始表中。视图的作用类似于 “窗口”，通过特定逻辑筛选或重组数据，提供更简洁、安全的访问方式。

2.视图的核心特性
  1. 虚表本质：不存储数据，仅定义查询逻辑。
  2. 动态性：每次查询视图时，数据实时从原始表获取。
  3. 可复用性：可在多个查询或其他视图中引用。
  4. 操作限制：对视图的更新（增、删、改）受限于原始表结构和查询逻辑。
3.视图与列表的区别
维度视图列表（结果集）
存储方式不存储数据，仅保存定义临时生成，不持久化
生命周期持久化存在，直到被显式删除查询结束后自动消失
可复用性可重复使用，支持嵌套定义每次查询需重新生成
数据独立性屏蔽底层表结构变化，保护业务逻辑直接依赖原始表结构
权限控制可针对视图设置独立权限依赖原始表权限，无法单独控制
4.视图的优势

  1. 简化复杂查询
    将多表关联、过滤等复杂逻辑封装为视图，简化后续查询操作。

    CREATE VIEW EmployeeSummary AS
SELECT e.id, e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;

  2. 提升安全性
    通过视图限制用户访问字段或行，例如：

    • 隐藏敏感字段（如薪资、密码）。
    • 仅展示特定部门的数据。

  3. 逻辑独立性
    底层表结构变化时，只需修改视图定义，无需调整上层业务逻辑。

  4. 支持嵌套查询
    可在视图基础上创建新视图，实现更复杂的逻辑分层。

  5. 优化性能
    部分数据库（如 MySQL）会缓存视图执行计划，提升高频查询效率。

5.视图的局限性
  • 更新限制：并非所有视图都支持增删改操作，需满足特定条件（如基于单表、无聚合函数等）。
  • 性能开销：复杂视图可能导致查询效率下降，需合理设计。
6.总结

视图是数据库中重要的抽象工具，通过封装数据逻辑，平衡了灵活性、安全性和开发效率。适用于需要简化查询、保护数据或增强逻辑独立性的场景。

  1. 定义与删除视图
    • 定义视图：CREATE VIEW语句，可带WITH CHECK OPTION确保更新时满足视图条件，根据不同情况需指定视图列名。例如：
-- 建立信息系学生的视图
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept='IS';
-- 建立信息系学生的视图，并要求更新视图时仍保证视图只有信息系的学生
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept='IS'
WITH CHECK OPTION;
  • 删除视图：DROP VIEW语句。如DROP VIEW IS_S1;，删除名为IS_S1的视图。
  1. 查询与更新视图
    • 查询视图：与查询基本表类似，但需注意视图消解后的查询正确性。例如：
-- 在信息系学生的视图中找出年龄小于20岁的学生
SELECT Sno,Sage
FROM IS_Student
WHERE Sage<20;

  • 更新视图：部分视图可更新，行列子集视图通常允许更新，不同数据库系统对视图更新有更多限制。

    常见不可更新情况

    • 多表导出：若视图由两个及以上表导出，像 DB2 等系统规定此类视图不允许更新 。因为更新操作可能无法明确对应到具体某张表，造成数据不一致。
    • 字段来源问题：
      • 字段来自字段表达式或常数时，不允许 INSERT 和 UPDATE 操作（但允许 DELETE ） 。比如视图字段是通过计算得到的表达式结果，更新会破坏表达式逻辑。
      • 字段来自集函数（如 SUM、AVG、COUNT 等 ），视图不允许更新 。集函数结果是统计汇总值，更新无实际意义且会破坏统计逻辑。
    • 定义包含特定子句或短语：
      • 视图定义中有 GROUP BY 子句 ，不允许更新。GROUP BY 是分组统计操作，更新会使分组统计结果混乱。
      • 视图定义含 DISTINCT 短语 ，不允许更新。DISTINCT 用于去除重复行，更新可能破坏去重逻辑。
    • 嵌套查询问题：视图定义中有嵌套查询，且内层查询 FROM 子句涉及的表是导出该视图的基本表 ，不允许更新。这种复杂结构下更新难以保证数据一致性。
    • 基于不可更新视图：在一个本身不允许更新的视图上再定义的视图，同样不允许更新 。

  • 例如：

-- 将信息系学生视图IS_Student中学号为200215122的学生姓名改为“刘辰”
UPDATE IS_Student
SET Sname='刘辰'
WHERE Sno='201215122';
  1. 视图作用：简化用户操作、提供多种数据视角、增强数据库逻辑独立性、保护机密数据、清晰表达查询。比如为不同权限的用户创建不同的视图，普通用户只能看到部分数据，保护了机密信息；开发人员可以通过视图简化复杂查询的编写。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值