数据库初学者易犯的错误（持续更新）

起个名字这么难？！

已于 2024-06-27 17:55:22 修改

阅读量938

点赞数 6

文章标签： sql mysql 数据库

于 2024-06-09 21:06:29 首次发布

本文链接：https://blog.csdn.net/m0_75032658/article/details/139565782

版权

DDL（数据定义）

DROP 子句 用于删除指定的列名，

◼ 若选择RESTRICT ，则删除的基本表不能被其他表的约

束所引用（如 CHECK ， FOREIGN KEY 等约束），不

能有视图，不能有触发器，不能有存储过程或函数等。

如果存在这些依赖该表的对象，则此表不能被删除。

◼ 若选择 CASCADE ，则该表的删除没有限制条件。在删

除基本表的同时，相关的依赖对象，例如视图等都将被

一起删除。

◼ 一般在缺省情况下默认为 RESTRICT ， 与具体实现有关

DML（数据操作）

一。插入

INSERT INTO < 表名 > [(< 属性列 1>[,< 属性列 2 > … )]

VALUES (< 常量 1> [ ， < 常量 2>] …

);

◼ INTO 子句

◼ 指定要插入数据的表名及属性列

◼ 属性列的顺序可以与表定义中的顺序不一致

◼ 没有指定属性列 ：表示要插入的是一条完整的元组，

且属性列属性与表定义中的顺序一致

◼ 指定部分属性列：插入的元组在其余属性列上取空

值或者是默认值

◼ VALUES 子句

◼ 提供的值必须与 INTO 子句匹配：个数、顺序和值的

类型

【例】在学生表中插入一个学生元组，其学号为

101215 ，姓名为李斌，男， 19 岁，是计算机系

的学生。

INSERT INTO Student

VALUES( ‘ 101215 ’ , ‘ 李斌’ , ‘男’ ,19, ‘计算机’ );

INSERT INTO Student ( Sno, Sname, Sdept, Sage, Ssex )

VALUES( ‘ 101215 ’ , ‘ 李斌’ , ‘计算机’ ,19, ‘男’ );

将子查询结果插入指定表中

INSERT INTO < 表名 >[(< 列名 1>[,< 列名 2>,  ])]

<SELECT 语句 >; /* 子查询 */ ；

◼ INTO 子句

◼ 指定要插入数据的表名及属性列

◼ 属性列的顺序可与表定义中的顺序不一致

◼ 没有指定属性列，则表示插入一条完整的元组

◼ 指定部分属性列，则在其余属性列上取空值

◼ 子查询

◼ SELECT 子句目标列必须与 INTO 子句匹配：个数、

顺序和值的类型

【例】计算计算机系每个学生的平均成绩，并保存

在 CS-AVG 表中。

1. 生成学生的平均成绩表CS-AVG

2. 在CS-AVG中插入计算机系学生的平均成绩

CREATE TABLE CS-AVG

( Sno CHAR(6)NOT NULL ，

Grade NUMBER(4,1));

INSERT INTO CS-AVG (Sno, Grade)

SELECT Sno, AVG(Grade) FROM SC

WHERE Sno IN (

SELECT Sno FROM Student

WHERE Sdept=‘CS’)

GROUP BY Sno ;

二。更新与删除

TRUNCATE TABLE

◼ 语句格式

TRUNCATE TABLE table_name

◼ 功能

◼ 删除表中所有行（与不带 WHERE 子句的 DELETE 语

句相同），但不记录单个行删除操作

◼ 比 DELETE 速度快，使用的系统和事务日志资源少

◼ DELETE 语句每次删除一行，并在事务日志中为所删除的每行

记录一项。

◼ TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除

数据，并且只在事务日志中记录页的释放。

◼ 操作不能回滚，但 DELETE 可以回滚

DQL（数据查询）

一。注意 DISTINCT短语的作用范围是所有目标列

错误的写法

SELECT DISTINCT Cno ， DISTINCT Grade

FROM SC;

正确的写法

SELECT DISTINCT Cno ， Grade

FROM SC;

二。空值查询：“IS NULL” 不能用 “= NULL” 代替

三。使用GROUP BY子句分组查询

◼ 细化聚集函数的作用对象

◼ 未对查询结果分组，聚集函数将作用于整个查询结果

◼ 对查询结果分组后，聚集函数将分别作用于每个组

◼ 分组方法

◼ 按指定的一列或多列值分组， 值相等的为一组

◼ 使用 GROUP BY 子句后， SELECT 子句的列名列

表中只能出现分组属性和聚集函数

◼ GROUP BY 子句的作用对象是查询的中间结果表

【例】求各个课程号及相应的选课人数。

SELECT Cno ， COUNT(*) -- COUNT(Sno)

FROM SC

GROUP BY Cno ；

【例】查询选修了 3 门以上课程的学生学号

SELECT Sno

FROM SC

GROUP BY Sno

HAVING COUNT(*) >3 ；

查询有 3 门以上课程是 90 分以上的学生的学

号及（ 90 分以上的）课程数

SELECT Sno, COUNT(*)

FROM SC

WHERE Grade>=90

GROUP BY Sno

HAVING COUNT(*)>=3;

四。where与having的区别

WHERE 子句允许你指定过滤条件，这些条件基于表中的单个行。你不能在 WHERE 子句中使用聚合函数（如 SUM(), AVG(), COUNT(), 等）来过滤分组。
GROUP BY 子句通常与聚合函数一起使用，以计算每个组的汇总值。你不能在 GROUP BY 子句中指定单个行的条件，但可以在 HAVING 子句中这样做，HAVING 子句允许你基于聚合函数的结果来过滤分组。

五。连接查询

连接条件中各 连接字段 的类型必须是 可比的 ，但不必是

相同的

六。嵌套查询（又称子查询）

子查询的限制

◼ 不能使用 ORDER BY 子句

嵌套查询的分类与求解方法

◼ 不相关子查询

◼ 子查询的查询条件不依赖于父查询

◼ 由里向外逐层处理。每个子查询在上一级查询处理之前

求解，子查询的结果用于建立其父查询的查找条件。

◼ 相关子查询

◼ 子查询的查询条件依赖于父查询

◼ 先取外层查询中表的第一个元组，根据它与内层查询相

关的属性值处理内层查询，若 WHERE 子句返回值为真，

则取此元组放入结果表；然后再取外层表的下一个元组；

重复这一过程，直至外层表全部检查完为止

【例】找出每个学生所选修课程成绩超过该门课

程平均成绩的课程号。

SELECT Sno, Cno /* 外层查询 / 父查询 */

FROM SC x

WHERE Grade >=(SELECT AVG(Grade)

FROM SC y

WHERE y.Cno=x.Cno);

/* 内层查询 / 子查询 */

子查询不能在比较符之前

◼ 错误 的例子

SELECT Sno, Sname, Sdept

FROM Student

WHERE ( SELECT Sdept

FROM Student

WHERE Sname= ‘ 刘晨 ’ )

= Sdept ；

带有 ANY 或 ALL 谓词的子查询

> ANY

大于子查询结果中的某个值

> ALL

大于子查询结果中的所有值

= ANY

等于子查询结果中的某个值

=ALL

等于子查询结果中的所有值（通常没有实际意义）

!= （或 <> ） ANY 不等于子查询结果中的某个值

!= （或 <> ） ALL

不等于子查询结果中的任何一个值

ANY 和 ALL 谓词有时可以用聚集函数来实现

◼ 用聚集函数实现子查询通常比直接用 ANY 或 ALL 查询效

率要高，因为前者通常能够减少比较次数。

【例】查询其他系中比 CS 系 任意一个 学生年龄小的

学生姓名和年龄

◼ 用 ANY 谓词实现

SELECT Sname, Sage

FROM Student

WHERE Sage < ANY (

SELECT Sage

FROM Student

WHERE Sdept= 'CS')

AND Sdept <> 'CS' ;

◼ 用聚集函数实现

SELECT Sname, Sage

FROM Student

WHERE Sage < (

SELECT MAX(Sage)

FROM Student

WHERE Sdept= 'CS')

AND Sdept <> 'CS' ;

带有 EXISTS 谓词的子查询

◼ 带有 EXISTS 谓词的子查询不返回任何数据，只产生逻

辑真值“ true ”或逻辑假值“ false ”。

◼ 若内层查询结果 非空 ，则返回 真值

◼ 若内层查询结果为空，则返回假值

◼ 由 EXISTS 引出的子查询，其目标列表达式通常用 * , 因

为带 EXISTS 的子查询只返回真值或假值，给出列名无

实际意义

【例】查询所有选修了1号课程的学生姓名。

◼ 思路分析：在 Student 中依次取每个元组的 Sno值，用此

值去检查 SC 关系；若 SC 中存在这样的元组，其 Sno 值

等于此 Student.Sno 值,且Cno= ‘ 1 ’ ,则取此

Student.Sname 送入结果关系

用嵌套查询

SELECT Sname

FROM Student

WHERE EXISTS

(SELECT *

FROM SC

WHERE Sno=Student.Sno

AND Cno= ' 1 ') ；

◼ 所有带 IN 谓词、比较运算符、 ANY 和 ALL 谓词的子查询

都能用带 EXISTS 谓词的子查询等价替换。

◼ 带有 EXISTS 谓词的相关子查询只关心内层查询是否有

返回值，不需要查具体值，效率不低于相关子查询。

用 EXISTS/NOT EXISTS 实现全称量词

◼ SQL 语言中没有全称量词任取 （ For all ）

◼ 可以把带有全称量词的谓词转换为等价的带有存在量词

的谓词：

(任取 x)P ≡ 非 (存在 x( 非 P))

【例 】查询选修了全部课程的学生姓名。

SELECT Sname

FROM Student

WHERE NOT EXISTS #这样的课不存在

(SELECT *

FROM Course

WHERE NOT EXISTS #这门课他没选

(SELECT *

FROM SC

WHERE Sno= Student.Sno

AND Cno= Course.Cno)) ；

用 EXISTS/NOT EXISTS 实现逻辑蕴函

◼ SQL 语言中没有蕴函 (Implication) 逻辑运算

【例】查询至少选修了学生95001选修的全部课程的

学生的学号

解题思路：

◼ 用逻辑蕴函表达：查询学号为x的学生，对所有的课程y，

只要95001学生选修了课程y，则x也选修了y

◼ 变换后语义： 对于学生 x ，不存在这样的课程 y ，学生

95001选修了 y ，而学生 x 没有选 。

SELECT DISTINCT Sno

FROM SC SCX

WHERE NOT EXISTS

(SELECT *

FROM SC SCY

WHERE SCY.Sno = '95001'

AND NOT EXISTS

(SELECT *

FROM SC SCZ

WHERE SCZ .Sno= SCX. Sno AND

SCZ .Cno= SCY .Cno)) ；#一个表涉及多次查询时最好起别名，而且as是可以省略的

七。集合查询

并操作

◼ 语法形式

< 查询块 >

UNION [ALL]

< 查询块 >;

◼ 两个查询结果的属性列个数相同，对应项的数据

类型必须能够通过隐式转换相互兼容。

◼ 使用 UNION 合并多个结果集时，系统会 自动去掉

重复元组 。

◼ 使用 UNION ALL 操作符，可以保留重复元组

UNION 结果集中的列名与 UNION 运算中第一个

SELECT 语句的结果集中的列名相同，

其他的 SELECT 语句的结果集列名将被忽略。

差操作

◼ 标准 SQL 中没有提供集合差操作，但可用其他方

法间接实现。

【例】查询学生姓名与教师姓名的差集。 实际上是

查询学校中未与教师同名的学生姓名 。

SELECT DISTINCT Sname

FROM Student

WHERE Sname NOT IN

(SELECT Tname

FROM Teacher);

#另一种方式

SELECT Sname

FROM Student

EXCEPT

SELECT Tname

FROM Teacher;

对集合操作结果的排序

◼ 在执行集合操作时，默认按照最后结果表中第一

列数据的升序方式排列记录。

◼ 各 SELECT 子句不能含有 ORDER BY 子句，但是可

以将 ORDER BY 子句放在最后的 SELECT 语句后面，

以便对最后的结果表排序。

◼ ORDER BY 子句只能用于对最终查询结果排序，

不能对中间结果排序。

◼ 任何情况下， ORDER BY 子句只能出现在最后。

◼ 对集合操作结果排序时， ORDER BY 子句中最好

用数字指定排序的列属性，以免出错

错误写法

SELECT *

FROM Student

WHERE Sdept= 'CS'

ORDER BY Sno

UNION

SELECT *

FROM Student

WHERE Sage<=19

ORDER BY Sno ；

正确写法

SELECT *

FROM Student

WHERE Sdept= 'CS'

UNION

SELECT *

FROM Student

WHERE Sage<=19

ORDER BY 1 ；

基于派生表的查询

◼ 子查询出现在 FROM 子句中，这时子查询生成的

临时派生表（ Derived Table ）成为主查询的查询

对象

【例】找出每个学生超过他自己选修课程平均成绩

的课程号。

SELECT Sno, Cno

FROM SC, ( SELECT Sno, Avg(Grade) avg_grade

FROM SC

GROUP BY Sno ) AS Avg_sc

WHERE SC.Sno = Avg_sc.Sno

and SC.Grade >=Avg_sc.avg_grade;

八。查询优化

析取选择条件的实现

◼ 优化处理难度比较大，

例如 C6 ： Sage > 18 or Sdept = ‘ 计算机’

◼ 如果 Sdept 上有索引，而 Sage 上没有索引，基本上无

法进行优化。

◼ 只要任意一个条件没有索引，就只能使用顺序扫描方法。

◼ 对于条件中涉及的属性都具有索引时，才能通过优化检

索满足条件的元组，然后再通过合并操作消除重复元组。

◼ 尽量避免用 or 。

代数优化策略

◼ 基本原则：减少查询处理的中间结果的大小。

◼ 典型的启发式规则

◼ 1 ）选择操作尽可能早地执行

◼ 目的：减小中间关系

◼ 在优化策略中这是最重要、最基本的一条

◼ 2 ）投影运算和选择运算尽量同时进行

◼ 目的：避免重复扫描关系

◼ 如有若干投影和选择运算，并且它们都对同一个关

系操作，则可以在扫描此关系的同时完成所有的这

些运算以避免重复扫描关系

51 ◼ 3 ）将投影操作与其前或其后的二元操作结合起来同时

进行，以 减少扫描关系的遍数。

◼ 4 ）把某些选择同在它前面的笛卡尔积结合起来，合并

为一个连接操作， 可以节省时间和空间开销 。

◼ 5 ）找出公共子表达式，计算一次公共子表达式并把结

果写入中间文件， 以达到节省时间的目的 。

视图

一。创建视图

◼ 语句格式

CREATE VIEW < 视图名 > [(< 列名 1> [,< 列名 2>] … )]

AS < SELECT 语句 >

[WITH CHECK OPTION] ；

◼ DBMS 执行 CREATE VIEW 语句时只是把视图的

定义存入数据字典，并不执行其中的 SELECT 语句。

在对视图进行操作时才按照视图定义生成数据，

供用户使用。

◼ SELECT 语句 表示子查询，视图的属性列和数据

都是由该子查询决定的。

◼ 选项 [(< 列名 1>[, < 列名 2>]  )] 用来定义视图的

列名。

◼ 组成视图的属性列名可以 全部省略 或 全部指定

◼ 省略 :

由 SELECT 查询结果的目标列名组成

◼ 以下情况必须明确指定视图的所有列名 :

(1) 目标列中包含聚集函数或表达式

(2) 视图中包含出现在多个表中的相同列名

(3) 需要在视图中为某个列启用新的更合适的名字

◼ 建立带表达式的视图

【例】按系建立学生平均年龄的视图。

CREATE VIEW D-Sage (Sdept, Avgage)

AS SELECT Sdept, AVG(Sage)

FROM Student

GROUP BY Sdept;

◼ 因在 SELECT 目标表中有聚集函数 AVG ，视图定义中 必

须含有列名选项 。

◼ 视图的列名与 SELECT 后的列名相对应，即使有与基本

表相同的列名也不能省略。

◼ 建立基于多个基表的视图

【例】建立计算机系选修了 C2 课的学生姓名和成绩

的视图。

CREATE VIEW CS_SC( Sno, Sname, Grade )

AS SELECT Student.Sno , Sname, Grade

FROM Student, SC

WHERE Sdept=' 计算机 ' AND

Student.Sno=SC.Sno AND SC.Cno='C2' ；

以 SELECT * 方式创建的视图可扩充性差，应尽可能避免 .

二。 DBMS实现视图查询的方法

◼ 实体化视图（ View Materialization ）

1. 有效性检查：检查所查询的视图是否存在

2. 执行视图定义，将视图临时实体化，生成临

时表

3. 查询视图转换为查询临时表

4. 查询完毕删除被实体化的视图 ( 临时表 )

◼ 视图消解法（ View Resolution ）

1. 进行有效性检查，检查查询的表、视图等是

否存在；如果存在，则从数据字典中取出视

图的定义；

2. 把视图定义中的子查询与用户的查询结合起

来，转换成等价的对基本表的查询；

3. 执行修正 后的查询。

【例】查询专业系，要求学生平均年龄小于 21 岁。

CREATE VIEW D-Sage (Sdept, Avgage)

AS SELECT Sdept, AVG(Sage)

FROM Student

GROUP BY Sdept;

SELECT Sdept

FROM D-Sage

WHERE Avgage<21;

SELECT Sdept

FROM Student

GROUP BY Sdept

HAVING AVG(Sage)<21;

转化为

对基本表的查询

◼ 视图消解法的局限性

◼ 有些情况下，视图消解法不能生成正确查询。

◼ 采用视图消解法的DBMS会限制这类查询。

◼ 对于简单视图，视图消解是总能进行的。

◼ 实体化视图的方法

◼ 无限制

三。视图更新

1，若视图的字段来自聚集函数或含有GROUP BY子句、含有DISTINCT短语，则此视图不允许更新

【例】通过视图 D-Sage 插入计算机系学生的平均

年龄（ ' 计算机 ' ， 21 ）。

INSERT INTO D-Sage

VALUES (' 计算机 ',21);

以上插入语句无法执行，因为视图 D-Sage 为不可

更新视图。

CREATE VIEW D-Sage (Sdept, Avgage)

AS SELECT Sdept, AVG(Sage)

FROM Student

GROUP BY Sdept;

2，若视图由两个以上基本表导出不允许更新

【例】通过视图 CS_SC 删除学生刘明亮的信息。

DELETE FROM CS_SC

WHERE Sname=' 刘明亮 ';

以上语句无法执行，因为不能转化为对基本表的操作，

删除操作的语义不明确。

CREATE VIEW CS_SC( Sno, Sname, Grade )

AS SELECT Student.Sno , Sname, Grade

FROM Student, SC

WHERE Sdept=' 计算机 ' AND

Student.Sno=SC.Sno AND SC.Cno='C2' ；

3，若视图定义中有嵌套查询，并且内层查询的FROM子句中涉及的表也是导出该视图的基本表，则此视图不允许更新。

注：

◼ 仅在一个表上取其行列值且其列中 包含了候选键 ，

这样所形成的视图都是可更新的，这类视图称为

“ 行列子集视图 ” 。

◼ 除行列子集视图外的视图的更新都会受到限制

约束

索引

◼ 在一个基本表上最多只能建立一个聚集索引

◼ 聚集索引可以包含多个列（组合索引）

◼ 聚集索引的适用范围

◼ 很少对基表进行增删操作

◼ 很少对其中的变长列进行修改操作

数据库完整性

在关系数据库系统中， 完整性控制策略 包括默认

值、规则、约束、 触发器 和 存储过程 等。使用 Check 优于使用触发器、规则和默认值

实体完整性

◼ PRIMARY KEY 约束可以作为表定义的一部分在创

建表时定义，也可以在表创建之后再添加。

◼ 为了实施实体完整性，系统一般会在主键属性上自

动创建唯一的索引来强制唯一性约束。

◼ 参照完整性

◼ 刻画不同关系之间的联系

◼ 约束同一关系内部不同属性之间的联系

◼ 参照完整性约束规则

◼ 不允许引用不存在的元组

◼ 在关系模型中，外键字段的值要么为空值，要么是被引

用关系中元组的对应值

◼ 关系模型的参照完整性定义

◼ 用 FOREIGN KEY 短语定义哪些列为外键 ,

◼ 用 REFERENCES 短语指明外键参照哪些表的主键

维护参照完整性的策略

1. 参照关系 中外键空值的问题

◼ 需要定义外键是否允许为空值

◼ 外键是其主键的组成部分，外键值不允许为空

◼ 外键不是其主键的组成部分，可以根据具体的语义

确定外键值是否允许空值

2. 在 参照关系 中插入元组的问题

◼

受限插入

◼ 向 titles 中插入新的元组，但该元组的 pub_id 属性

值在表 publishers 中不存在，则系统拒绝

◼

递归插入【级联 (CASCADE) 插入】

◼ 首先向被参照关系插入相应的元组，其主键值等于

参照关系插入元组的外键值，然后再向参照关系插

入该元组

3. 在 参照关系 中修改元组的问题

◼

先删除、再插入

修改表中的完整性限制

◼ 使用 ALTER TABLE 语句修改表中的完整性限制

◼ 先删除原来的约束条件，再增加新的约束条件

【例】修改表 Student 中的约束条件，年龄由小于 30 改

为小于 40

ALTER TABLE Student

DROP CONSTRAINT C3 ;

ALTER TABLE Student

ADD CONSTRAINT C3

CHECK (Sage < 40) ;

触发器类型

按照 触发动作的间隔 尺度，触发器可分为：

◼ 行级触发器 (FOR EACH ROW) 。

对每一个修改的元组都会触发触发器的检查和执行

◼ 语句级触发器 (FOR EACH STATEMENT) 。

只在 SQL 语句执行时候进行触发条件的检查和触发

器的执行

【例】 Teacher 表上创建一个 AFTER UPDATE 触发

器。如果表 Teacher 有 1000 行，执行如下语句：

UPDATE Teacher SET Deptno=5;

语句级触发器，触发动作只发生 1 次 ;

行级触发器，触发动作将执行 1000 次。

触发器的执行，是由 触发事件激活 的，并由数据库服务器

自动执行

◼ 一个数据表上可能定义了 多个触发器

同一个表上的多个触发器，激活时遵循如下的执行顺序：

（ 1 ）执行该表上的 BEFORE 触发器；

（ 2 ）激活触发器的 SQL 语句（ 触发事件 ）；

（ 3 ）执行该表上的 AFTER 触发器。

注意： 如果激活触发器的 SQL 语句违反了约束条件，则不

会执行 AFTER 触发器 。

◼ 存在级联触发问题

存储过程与触发器

1.mysql> create trigger t1 after insert on test3 for each row
-> begin
-> select sname from test3;
-> end$
ERROR 1415 (0A000): Not allowed to return a result set from a trigger

2.可以创建空触发器