《数据库系统概论》- 课本理论整理03之标准语言SQL

关系数据库标准语言 - SQL（Structed Query Language)

SQL在1974年提出

                    成功＝正确的方法+努力+少说废话 -  以梦为🐎，不负韶华 =》 Wangzz(2021年3月24日 22点52分)

SQL特点

  1. 在关系模型中实体和实体间的联系均用关系表示。
  2. SQL是一种高度非过程化的语言，SQL在进行数据操作的时候，只要向数据库引擎提出“做什么 - what to do"而不用指出“怎么做 - how to do”,因此无须了解存取路径。存取路径的选择以及SQL的操作过程由系统自动完成。（极大提供数据独立性）
  3. 面向集合的操作方式

非关系模型采用的是面向记录的操作方式，操作对象是一条记录。

SQL采用集合操作方式，不仅操作对象、查找结果可以是元组的集合，而且一次插入、删除、更新操作的对象也可以是元组的集合。

SQL的9大动词

SQL功能 动词
数据查询(data query) SELECT
数据定义(data definition) CREATE、ALTER、DROP
数据操纵(data manipulation) INSERT、UPDATE、DELETE
数据控制(data control) GRANT、REVOKE

SQL基本概念(basic conception)

数据定义(Data definition)

  • 一个关系DBMS的实例(instance)中可以创建多个数据库(databases)
  • 一个数据库(database)可以建立多个模式(schemas)
  • 一个模式(schema)可以建立多个表(table)、视图(view)、索引(index)

基本表的定义、删除与修改

数据类型 - 域(domain): 一组具有相同数据类型的值的集合

每一个属性都是来自一个域，它的取值必须是域中的值。

数据类型 含义
CHAR(n),CHARACTER(n) 长度为n的定长字符串
VARCHAR(n),CHARACTERVARYING(n) 长度为n的变长字符串
CLOB 字符串大对象
BLOB 二进制大对象
INT、INTEGER 长整数(4 Byte)
SMALLINT 短整数(2 Byte)
BIGINT 大整数(8 Byte)
NUMERIC(p, d) 定点数，p位数字（不包括符合、小数点）组成，小数点后有d位数字
DECIMAL(p, d) 与NUMERIC一样
FLOAT(n) 精度至少为n位数字
BOOLEAN 逻辑布尔量
DATE 日期，年、月、日，格式为YYYY-MM-DD
TIME 时间, 时、分、秒、格式为HH:MM:SS
TIMESTAMP 时间戳类型
INTERVAL 时间间隔类型
定义表
CREATE TABLE SC
(
Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY(Sno,Cno), /*主码由两个属性构成，必须作为表级完整性进行定义*/
FOREIGN KEY(Sno) REFERENCES Student(Sno), /*表级完整性约束条件，Sno是外码，被参照表是Student*/
FOREIGN KEY(Cno) REFERENCES Course(Cno) /*表级完整性约束条件，Cno是外码，被参照表是Course*/
);

修改表

修改模板
ALTER TABLE <表名>
ADD COLUMN <新列名> <数据类型> [完整性约束]
ADD <表级别完整性约束>
DROP COLUMN <列名> [CASCADE|RESTRICT]  
DROP CONSTRAINT <完整性约束名> [RESTRICT|CASCADE]
ALTER COLUMN <列名><数据类型>

DROP COLUMN <列名> [CASCADE | RESTRICT]区别:
DROP COLUMN <列名> CASCADE 自动删除引用了改列的其他对象。
DROP COLUMN <列名> RESTRICT 如果该列被其他对象引用，DBMS则拒绝删除该列。
SQL Server之中没有 CASCADE和RESTRICT选项

索引的建立与删除 - 索引是加快查询速度的有效手段。

常见索引： B+树索引(动态平衡)、散列索引(查找速度快)、位图索引以及顺序文件上的索引。
索引是RDBMS的内部实现技术（内模式）

索引加速数据的查询但占用一定的存储空间，当基本表更新的时候，索引进行相应的维护增加数据库的负担（按需创建索引）

建立索引

语句如下：

CREATE [UNIQUE][CLUSTER]  INDEX  <索引名>
ON
<表名> (<列名>[<次序>], <列名>[<次序>] ...);
 
次序： ASC升序或DESC降序
 
eg:
CREATE UNIQUE INDEX Stusno_indexes ON Student(Sno);
CREATE UNIQUE INDEX Coucno ON Course(Sno);
CREATE UNIQUE INDEX SCno ON SC(Sno ASC, Cno DESC);
UNIQUE和CLUSTER索引区别

MySQL的唯一限制和主键限制都是通过索引实现的。

  • UNIQUE唯一索引：此索引的每一个索引值只对应唯一的数据记录。
  • CLUSTER聚簇索引: 是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序。由于聚簇索引的索引页面指针指向数据页面，所以使用聚簇索引查找数据几乎总是比使用非聚簇索引快。每张表只能建一个聚簇索引，并且建聚簇索引需要至少相当该，每张表只能建一个聚簇索引，并且建聚簇索引需要至少相当该表120%的附加空间，以存放该表的副本和索引中间页。
修改索引名称
ALTER INDEX <old_index_name> RENAME TO <new_index_name>
删除索引
DROP INDEX <index_name>

数据字典(Data dictionary)

数据字典是RDBMS中的一组系统表，记录数据库中所有定义信息，包括关系模式定义、视图定义、索引定义、各类用户对数据库的操作权限等

数据查询

SELECT [ALL|DISTINCT] <目标列表达式>
FROM <表名或视图名> [AS] <别名>
WHERE <条件表达式>
GROUP BY <列名1> HAVING <条件表达式>
ORDER BY <列名2> [ASC|DESC];

含义如下：
从FROM子句指定的基本表、视图或派生表中找出满足WHERE条件的元组
按SELECT子句中的目标列表达式选出元组中的属性值形成结果表
如果有GROUP BY子句，将结果按<列名1>的值进行分组，该属性列值相等
的元组为一个组。在每组中作用聚集函数。如果GROUP BY子句带有HAVING短语
则满足指定条件的组才予以输出。

查询满足条件的元组

查询条件 谓词
比较 =,>,<,>=,<=,!=,<>,!>,!<
确定范围 BETWEEN AND, NOT BETWEEN AND
确定集合 IN, NOT IN
字符匹配 LIKE, NOT LIKE
空值 IS NULL, IS NOT NULL
多重条件(逻辑运算) AND, OR, NOT
  • 查询年龄在20~23岁之间（包括20岁和23岁）之间的学生的姓名、系别和年龄。
SELECT Sname, Sdept, Sage
FROM Student
WHERE Sage BETWEEN 20 AND 23;
  • 查询院系为CS、MA和IS学生的姓名和性别
SELECT Sname, Ssex
FROM Student
WHERE Sdept IN ('CS','MA','IS');
  • 字符匹配

%: 匹配任意字符
_: 匹配一个字符

聚集函数

函数名称 函数功能
COUNT(*) 统计元组个数
COUNT [DISTINCT ALL] <列名>
SUM [DISTINCT ALL] <列名>
AVG [DISTINCT ALL] <列名>
MAX [DISTINCT ALL] <列名>
MIN [DISTINCT ALL] <列名>

当聚集函数遇到空值的时候，除了COUNT(*)之外，都跳过空值而只处理非空值。
COUNT对元组计数，某个元组的一个或部分列为空值不影响COUNT的统计结果

聚集函数只能使用在SELECT子句和GROUP BY的HAVING子句之中

  • 查询选修了三门以上课程的学生学号
mysql> SELECT * FROM sc;
+-----------+-----+-------+
| sno       | cno | grade |
+-----------+-----+-------+
| 201215121 |   1 |    92 |
| 201215121 |   2 |    85 |
| 201215121 |   3 |    88 |
| 201215122 |   2 |    90 |
| 201215122 |   3 |    80 |
+-----------+-----+-------+
5 rows in set (0.00 sec)
 
mysql> SELECT sno
    -> FROM sc
    -> GROUP BY sno
    -> HAVING COUNT(*) >= 3;
+-----------+
| sno       |
+-----------+
| 201215121 |
+-----------+
1 row in set (0.00 sec)
  • WHERE子句与HAVING短语的区别
    a. WHERE子句作用域基本表或视图从中选择满足条件的分组。
    b. HAVING短语作用域组(即只能和GROUP BY 子句一起使用从中选择满足条件的组）。

连接查询🔗

连接查询分类：

  • 等值连接查询（保留重复列）
  • 自然连接查询（去除重复列）
  • 非等值连接查询
  • 自身连接查询
  • 外连接查询
  • 复合条件连接查询

等值连接查询 - 连接运算符为 = 时

连接字段的要求是各连接字段类型必须是可比的但名字不必相同。

mysql> SELECT Student.*, SC.*
    -> FROM Student, SC
    -> WHERE Student.Sno = SC.Sno;
+-----------+-------+------+------+-------+-----------+-----+-------+
| sno       | sname | ssex | sage | sdept | sno       | cno | grade |
+-----------+-------+------+------+-------+-----------+-----+-------+
| 201215121 | 李勇  | 男   |   20 | CS    | 201215121 |   1 |    92 |
| 201215121 | 李勇  | 男   |   20 | CS    | 201215121 |   2 |    85 |
| 201215121 | 李勇  | 男   |   20 | CS    | 201215121 |   3 |    88 |
| 201215122 | 刘晨  | 女   |   19 | CS    | 201215122 |   2 |    90 |
| 201215122 | 刘晨  | 女   |   19 | CS    | 201215122 |   3 |    80 |
+-----------+-------+------+------+-------+-----------+-----+-------+

执行该连接操作的过程是在Student中选取一个元组在通过SC表依次选取元组进行字段比对
如果比对成功则进行Student中元组与SC表中元组拼接形成结果表中的一个元组，全部查找玩之后
再找Student中第二个元组。

自然连接查询 - 即去掉在等值连接中出现重复性属性列

mysql> SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
    -> FROM Student, SC
    -> WHERE Student.Sno = SC.Sno;
+-----------+-------+------+------+-------+-----+-------+
| Sno       | Sname | Ssex | Sage | Sdept | Cno | Grade |
+-----------+-------+------+------+-------+-----+-------+
| 201215121 | 李勇  | 男   |   20 | CS    |   1 |    92 |
| 201215121 | 李勇  | 男   |   20 | CS    |   2 |    85 |
| 201215121 | 李勇  | 男   |   20 | CS    |   3 |    88 |
| 201215122 | 刘晨  | 女   |   19 | CS    |   2 |    90 |
| 201215122 | 刘晨  | 女   |   19 | CS    |   3 |    80 |
+-----------+-------+------+------+-------+-----+-------+
5 rows in set (0.00 sec)
  • 查询选修2号课程且成绩在90分以上的所有学生的学号和姓名
mysql> SELECT Student.Sno, Student.Sname
    -> FROM Student, SC
    -> WHERE Student.Sno = SC.Sno
    -> AND
    -> SC.Cno = 2
    -> AND
    -> SC.Grade >= 90;
+-----------+-------+
| Sno       | Sname |
+-----------+-------+
| 201215122 | 刘晨  |
+-----------+-------+
1 row in set (0.03 sec)
 
mysql> SELECT *
    -> FROM SC;
+-----------+-----+-------+
| sno       | cno | grade |
+-----------+-----+-------+
| 201215121 |   1 |    92 |
| 201215121 |   2 |    85 |
| 201215121 |   3 |    88 |
| 201215122 |   2 |    90 |
| 201215122 |   3 |    80 |
+-----------+-----+-------+
5 rows in set (0.00 sec)

WHERE Student.Sno = SC.Sno AND SC.Cno = '2' AND SC.Grade > 90;
一种优化（高效）的执行过程，先从SC中挑选出Cno='2'并且Grade>90的元组形成一个中间关系。再和Student中满足条件的元组连接得到最终的结果关系。

自身连接

表连接的操作不仅可以在表之间进行也可以表与自身进行连接；

mysql> SELECT *
    -> FROM Course;
+-----+------------+------+---------+
| cno | cname      | cpno | ccredit |
+-----+------------+------+---------+
|   1 | 数据库     |    5 |       4 |
|   2 | 数学       | NULL |       2 |
|   3 | 信息系统   |    1 |       4 |
|   4 | 操作系统   |    6 |       3 |
|   5 | 数据结构   |    7 |       4 |
|   6 | 数据处理   | NULL |    NULL |
|   7 | PASCAL语言 |    6 |       4 |
+-----+------------+------+---------+
7 rows in set (0.00 sec)
 
mysql> SELECT first.cname, '间接选修课为: ', second.cname
    -> FROM Course first, Course second
    -> WHERE first.cpno = second.cno;
+------------+----------------+------------+
| cname      | 间接选修课为:  | cname      |
+------------+----------------+------------+
| 数据库     | 间接选修课为:  | 数据结构   |
| 信息系统   | 间接选修课为:  | 数据库     |
| 操作系统   | 间接选修课为:  | 数据处理   |
| 数据结构   | 间接选修课为:  | PASCAL语言 |
| PASCAL语言 | 间接选修课为:  | 数据处理   |
+------------+----------------+------------+

外连接 (outer join)

左外连接

若某个学生没有选课，仍然把Student的悬浮元组保存在结果关系中，而在SC表的属性上填空值NULL

mysql> SELECT Student.Sno, Sname, Ssex, Sage, Cno, Grade
    -> FROM Student LEFT OUTER JOIN SC
    -> ON Student.Sno = SC.Sno;
+-----------+-------+------+------+------+-------+
| Sno       | Sname | Ssex | Sage | Cno  | Grade |
+-----------+-------+------+------+------+-------+
| 201215121 | 李勇  | 男   |   20 |    1 |    92 |
| 201215121 | 李勇  | 男   |   20 |    2 |    85 |
| 201215121 | 李勇  | 男   |   20 |    3 |    88 |
| 201215122 | 刘晨  | 女   |   19 |    2 |    90 |
| 201215122 | 刘晨  | 女   |   19 |    3 |    80 |
| 201215123 | 王敏  | 女   |   18 | NULL |  NULL |
| 201215125 | 张立  | 男   |   19 | NULL |  NULL |
+-----------+-------+------+------+------+-------+
7 rows in set (0.00 sec)

右外连接

mysql> SELECT Student.Sno, Sname, Ssex, Sage, Cno, Grade
    -> FROM Student RIGHT OUTER JOIN SC
    -> ON Student.Sno = SC.Sno;
+-----------+-------+------+------+-----+-------+
| Sno       | Sname | Ssex | Sage | Cno | Grade |
+-----------+-------+------+------+-----+-------+
| 201215121 | 李勇  | 男   |   20 |   1 |    92 |
| 201215121 | 李勇  | 男   |   20 |   2 |    85 |
| 201215121 | 李勇  | 男   |   20 |   3 |    88 |
| 201215122 | 刘晨  | 女   |   19 |   2 |    90 |
| 201215122 | 刘晨  | 女   |   19 |   3 |    80 |
+-----------+-------+------+------+-----+-------+
5 rows in set (0.00 sec)

嵌套查询 - (SELECT - FROM - WHERE (SELECT - FROM - WHERE))

将一个查询快嵌套在另一个查询快的WHERE子句或HAVING短语的条件中的查询称为嵌套查询(nested query)

mysql> SELECT Sno, Sname, Ssex, Sage
    -> FROM Student
    -> WHERE Sno
    -> IN
    -> (SELECT Sno
    -> FROM Course);
+-----------+-------+------+------+
| Sno       | Sname | Ssex | Sage |
+-----------+-------+------+------+
| 201215121 | 李勇  | 男   |   20 |
| 201215122 | 刘晨  | 女   |   19 |
| 201215123 | 王敏  | 女   |   18 |
| 201215125 | 张立  | 男   |   19 |
+-----------+-------+------+------+

子查询中不能使用order by子句（子查询只是为父查询提供可选条件order by的使用也没有意义当然也是不能使用的！）
ORDER BY子句只能对最终查询结果排序。

ANY(SOME)、ALL谓词与聚集函数、IN谓词的等价转换关系

= <>或!= < <= > >=
ANY IN -- <MAX <=MAX >MIN >=MIN
ALL -- NOT IN <MIN <=MIN >MAX >=MAX

集合查询

SELECT查询结果是元组的集合，多个SELECT语句可以进行集合操作。集合操作包括：【并-UNION】【差-EXCEPT】【交-INTERSECT】.

参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同。

视图

🍎视图是一个或几个基本表导出的表（虚表）。（是对基本表中的数据查询的过程和结果数据组织的定义）

视图定义模板
CREATE VIEW <视图名> [<列名1><列名2><列名3>...]
AS 
<子查询>
[WITH CHECK OPTION]

WITH CHECK OPION表示对视图进行DML(INSERT、UPDATE、DELETE）等操作的时候要保证插入、更新和删除的行满足视图定义中的谓词条件（子查询中条件表达式）

CREATE VIEW结果是把视图的定义存入数据字典并不执行其中的SELECT语句。只是在对视图查询的时候才按视图的定义从基本表中将数据检索出来。

示例
CREATE VIEW IS_Student
AS
SELECT Sno, Sname, Sage
FROM Student
WHERE Sdept = 'IS'
WITH CHECK OPTION;

由于添加了WITH CHECK OPTION当进行增、删、改的时候保证数据符合WHERE中的条件表达式即自动添加【Sdept = 'IS'】.

WHERE子句是不能用聚集函数作为条件表达式的，因此执行此修正后的查询将会出现语法错误！

错误的写法❌

SELECT Sno, AVG(Grade)
FROM SC
WHERE AVG(Grade) >= 90
GROUP BY Sno;

正确的写法✔

SELECT Sno, AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade) >= 90;

视图一旦定义，则永久保存在【数据字典】之中，之后的所有查询都是直接引用该视图。
派生表只是在语句执行的时候临时定义，语句执行完毕后立即被删除！
eg:

SELECT *
FROM
(SELECT Sno, AVG(Grade) 
FROM SC
GROUP BY Sno) AS S_G(Sno, Gavg) /* 子查询生成一个派生表 S_G*/
WHERE Gavg >= 90;

更新视图

更新视图通过对视图来插入（INSERT）、删除（DELETE）和修改（UPDATE）数据。

视图不是实际存储数据的实表，所有对视图的更新都会转换为对基本表的更新。
一般不推荐通过视图对基本表中的数据进行更新。

视图的作用

视图最终定义在基本表之上，对视图的一切操作最终要转换为对基本表的操作。
好处如下：

  1. 视图能够简化用户的操作
  2. 视图让用户以多种角度看待同一数据
  3. 视图对重构数据库提供一定程序的逻辑独立性
  4. 视图能够对机密数据提供安全保护
  5. 适当利用视图能够清晰地表达查询

习题

1. SQL语言特点：
① 综合统一。SQL语言集DDL、DML、DQL、DCL的功能于一体。
② 高度非过程化。SQL语言进行数据操作，只要提出做什么而不用指明怎么做。
③ 面向集合的操作方式。SQL语言采用集合的操作方式，不仅操作对象、查找结果是元素的集合而且一次插入、删除、更新操作的对象也可以是元组的集合。
④ 同一种语法结构提供两种使用方式。 SQL语言是自含式语言也是嵌入式语言。
SQL作为自含式语言能够独立地用于联机交互的使用方式
SQL作为嵌入式语言能够嵌入到其他高级语言程序中
⑤ 语言简洁，易学易用。
2.

① SELECT * FROM S WHERE A = 10;
② SELECT A, B FROM S;
③ SELECT A,B,T.C,T.D,E,F FROM S, T WHERE S.C = T.C AND S.D = T.D;
④ SELECT * FROM S, T WHERE S.C = T.C;
⑤ SELECT * FROM S, T WHERE S.A < T.E;
⑥ SELECT S.A,S.D, T.* FROM S,T;
3.
1. 找出所有供应商的姓名和所在城市；
SELECT SNAME, CITY FROM S;
 
2. 找出所有零件的名称、颜色、重量；
SELECT PNAME, COLOR, WEIGHT FROM P;
 
3. 找出使用供应商S1所供应零件的工程号码；
SELECT PNO FROM SPJ WHERE SNO = 'S1';
 
4. 找出工程项目J2使用的各种零件的名称以及数量；
SELECT P.Pname, COUNT(Pno)
FROM SPJ
INNER JOIN P ON P.PNO = SPJ.PNO
WHERE JNO = 'J2'
GROUP BY PNO;
 
5. 找出上海厂商供应的所有零件号码；
SELECT P.PNO
FROM P
INNER JOIN SPJ ON P.PNO = SPJ.PNO
INNER JOIN S ON S.SNO = SPJ.SNO
WHERE S.CITY = '上海';
 
6. 找出使用上海产的零件的工程名称；
SELECT J.JNAME
FROM J
INNER JOIN SPJ ON J.JNO = SPJ.JNO
INNER JOIN S ON S.SNO = SPJ.SNO
WHERE S.CITY = '上海';
 
 
7. 找出没有使用天津产的零件的工程名称；
SELECT J.JNAME
FROM J
INNER JOIN SPJ ON J.JNO = SPJ.JNO
INNER JOIN S ON S.SNO = SPJ.SNO
WHERE S.CITY != '天津';
 
 
8. 把全部红色零件的颜色改成蓝色；
UPDATE P
SET COLOR = '蓝'
WHERE COLOR = '红';
 
9. 由S5供给J4零件的P6改成由S3供应；
UPDATE SPJ
SET SNO = 'S3'
WHERE SNO = 'S5' AND JNO = 'J4' AND PNO = 'P6';
 
10. 从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录；
A、DELETE FROM S WHERE SNO = 'S2';
B、DELETE FROM SPJ WHERE SNO = 'S2';
 
11. 将(S2, J6, P4, 200)插入供应情况表；
INSERT INTO SPJ(SNO, PNO, JNO, QTY)
VALUES('S2', 'J6', 'P4', 200);
4. 什么是基本表？什么是视图？两者的区别和练习是什么？
答： 基本表：本身独立存在的表，在SQL中一个关系就对应一个表。

视图：从一个或几个基本表或视图导出的表。视图本身不独立存储在数据库中，是一个虚表。
即数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍然存放在视图的基本表中。

5. 数据库内外连接的区别？
内连接：指连接结果仅包含符合连接条件的行，参与连接的两个表都应该符合连接条件。（内连接，即最常见的等值连接）

外连接：连接结果不仅包含符合连接条件的行同时也包含自身不符合条件的行。包括左外连接、右外连接和全外连接。

  • 左外连接就是以左表为准，去匹配右表，左表有多少条数据，结果就是多少条数据
  • 右外连接就是与左外连接反之，以右表为准，去匹配左表，右表有多少条数据，结果就是多少条数据
  • 全外连接数据条数不一定，相当与是左外连接 和右外连接 的综合

posted @   Felix_Openmind  阅读(779)  评论(0)  编辑  收藏  举报
编辑推荐：
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· AI与.NET技术实操系列：向量存储与相似性搜索在 .NET 中的实现
· 基于Microsoft.Extensions.AI核心库实现RAG应用
· Linux系列：如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
阅读排行：
· winform 绘制太阳，地球，月球 运作规律
· 超详细：普通电脑也行Windows部署deepseek R1训练数据并当服务器共享给他人
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· AI 智能体引爆开源社区「GitHub 热点速览」
· 写一个简单的SQL生成工具
*{cursor: url(https://files-cdn.cnblogs.com/files/morango/fish-cursor.ico),auto;}
点击右上角即可分享
微信分享提示