首页 > 数据库 >Chapter8_一用就会的数据库

Chapter8_一用就会的数据库

时间：2022-12-09 09:14:14浏览次数：59  
标签：DBMS Chapter8 一用 主键 SQL rst 顾客 数据库

热身回答

  • 数据库术语中的“表”是什么意思？
    • table， 表示数据的集合
    • 表（Table）就是被整理成表格形式的数据
    • 一张表由若干个列和行构成。列也被称为字段（Field），行也被称为记录（Record）。
  • DBMS 是什么的简称？
    • Database Management System 数据库管理系统
    • DBMS 是 Database Management System（数据库管理系统）的简称。
    • 市面上的 DBMS 有 SQL Server、Oracle、DB2 等。无论是哪种 DBMS 都可以用基本相同的 SQL 语句操作。
  • 键和索引的区别是什么？
    • 键用于设定表和表之间的关系（Relationship），而索引是提升数据检索速度的机制。
    • 其上每个值都能够唯一标识一条记录的字段称为主键。为了在表和表之间建立关系而在表中添加的、其他表主键的字段称为外键。而索引是与键无关的机制。

8.1 数据库是数据的基地

为什么需要数据库?

  • 数据库Database是数据data的基地base， 也就是说数据都汇集在数据库， 更方便管理和使用。
  • 卡片型数据库
    • 将汇集在一起的数据原封不动地移植到计算机中就是卡片型数据库。存储一条数据就好比把一张账单或是名片上的信息记录到一个文件中。卡片型数据库适用于想要实现小规模的数据库的情况。像是地址簿管理程序、存储 Web 电子公告板上的评论等，使用的都是卡片型数据库。
    • 缺点： 无法管理所有信息， 因为卡片与卡片之间缺乏关联性
    • image-20221125081257156
  • 关系型数据库 Relational Database
    • 在关系型数据库中，数据被拆分整理到多张表中，同时表与表之间的关系也可以被记录下来。
    • image-20221125081557135

8.2 数据文件、DBMS 和数据库应用程序

我们可以用DBMS类的软件（如Microsoft Access、Oracle、SQL Server、DB2）帮助我们编写数据库应用程序（程序用来操作数据库）。

数据库的实质虽然是某种数据文件，但是我们的应用程序并不是直接去读写这些数据文件，而是以 DBMS 作为中介间接地读写（如图 8.3 所示）。DBMS 不但可以使应用程序轻松地读写数据文件，而且还具有一致并且安全地存储数据的功能。

image-20221125081820090

数据库系统的构成：

  • 数据文件
  • DBMS
  • 应用程序

  1. 独立型系统： 在小型系统中，把三个要素全部部署在一台计算机上，称作“独立型系统”。

  2. 文件共享型系统： 在中型系统中，把数据文件部署在一台计算机上，并且使数据文件被部署了 DBMS 和应用程序的多台计算机共享，这样的系统被称为“文件共享型系统”。

  3. 服务器型系统： 在大型系统中，把数据文件和 DBMS 部署在一台（或者多台）计算机上，然后用户从另外一些部署着应用程序的计算机上访问，这样的系统被称作“客户端／服务器型系统”。

    • 其中部署着数据文件和 DBMS 的计算机是服务器（Server），即服务的提供者；
    • 部署着应用程序的计算机是客户端（Client），即服务的使用者。

  4. Web系统： 如果把服务器和客户端之间用互联网联结起来，就形成了 Web 系统。在 Web 系统中，一般情况下应用程序也是部署在服务器中的，在客户端只部署 Web 浏览器（如图8.4 所示）。

独立型系统 文件共享型系统 服务器型系统 Web系统
数据文件 部署在同一台计算机上 部署在计算机A上 部署在计算机B、C···上（服务器） 部署在计算机B、C···上（服务器）
DBMS 部署在同一台计算机上 部署在计算机B、C···上 部署在计算机B、C···上（服务器） 部署在计算机B、C···上（服务器）
应用程序 部署在同一台计算机上 部署在计算机B、C···上 部署在计算机A上（客户端） 部署在计算机B、C···上（服务器）
Web浏览器 无 无 无 计算机A（客户端）
关系 计算机A的数据文件被B、C等多台计算机共享 这里B、C称作服务器， A叫做客户端。 客户端和服务器之间用互联网连接起来

image-20221125082217889

image-20221125082431464

8.3 设计数据库

制作表

  • 考虑到数据的分类以及属性

如果是在酒铺管理的应用程序中， 如何设计数据库。

第一步， 因为数据库中都是数据， 所以我们为了让用户想要了解酒铺的知识时及时得到数据， 我们要将数据分类， 分成一些必要的数据。

  • 商品名称
  • 单价（日元）
  • 销售量
  • 顾客姓名
  • 住址
  • 电话号码

第二步， 我们为了方便管理还要考虑数据的属性（模式）具体来说就是数据的类型（是数字还是字符串），数字的话是整数还是浮点小数，字符串的话最多允许包含多少个字符，是否允许 NULL 值（表示未知或者不存在的值），等等

image-20221125085750114

关系型数据库的一些术语：

  • 录入到表中的每一行数据都称为记录record

    • 记录有时也被称为行或元组（Tuple）

  • 构成一条记录中的各个数据项（在本例中是商品名称、单价等）所在的列都称作字段field

    • 字段有时也被称为列或属性（Attribute）

  • 上面提到的属性（数据的类型）就是设置在字段上的。为了代表字段所存储数据的内容还要为每个字段起一个名字。

8.4 通过拆表和整理数据实现规范化

  • 为什么需要规范化？

为了避免一张表中有多条重复的数据，或者表示相同意思但是不同写法的字段（如下图威士忌、维士忌）， 我们需要对表进行限制（规范化）。

image-20221125090636098

  • 规范化是什么?

规范化的目的是在一个数据库中避免重复存储相同的数据。 规范化就是将一张大表分割成多张小表，然后再在小表之间建立关系，以此来达到整理数据库结构的目的。通过规范化，可以形成结构更加优良的数据库。

在这里， 把酒铺的数据库分为“商品表”“顾客表”和“销售记录表”三张表，然后再在它们之间建立关系（用被细线连接起来的短杠表示）。通过这样的处理，既省去了多次重复录入相同的顾客姓名、住址、电话号码的麻烦，又能防止把相同的商品名称输入成不同名称的错误。

image-20221125090716955

image-20221125090824881

8.5 用主键和外键在表间建立关系

键Key: 为了反映表与表之间关系而添加的新字段就是Key。

主键Primary Key： 用来唯一地标识表中的一条记录的字段

如： 在顾客表中添加的“顾客 ID”字段，在销售记录表中添加的“销售记录 ID”字段以及在商品表中添加的“商品ID”字段，都是主键。

通常情况下， 我们将主键命名为“xxID”， 因为ID是Identification（识别码）的缩写， 而且主键存储的就是能够唯一识别一条记录的ID。

image-20221128074853181

如下图所示，如果顾客表中顾客 ID 是 1 的话，就能确定是日经次郎这条记录；顾客 ID 是 2 的话，就能确定是矢泽三郎这条记录。正因为这种特性，在主键上绝不能存储相同的值。如果试图录入在主键上含有相同值的记录，DBMS 就会产生一个错误通知，这就是 DBMS 所具备的一种一致并且安全地存储数据的机制。

在主键上不能存储相同的值 <——> 作唯一标识

外键Foreign Key： 外表主键

在销售记录表上，还要添加顾客 ID 和商品 ID 字段，这两个字段分别是另外两张表的主键，对于销售记录表来说，它们就都是“外键”（Foreign Key）。主键和外键

然后通过主键和外键上相同的值， 多个表之间产生了关系， 我们就可以通过主键和外键将多个表之间的信息连接起来。

例如，销售记录表中最上面的一条记录是（1，1，1，3），分别表示该销售记录 ID 为 1，顾客 ID 为 1 的顾
客买了 3 个商品 ID 为 1 的商品。

通过顾客表，可以知道顾客 ID 为 1的顾客信息是（1，日经次郎，东京都千代田区，03-2222-2222）。

通过商品表，可以知道商品 ID 为 1 的商品信息是（1，日本酒，2000）。

连起来就是顾客id为1的顾客住在xxxx电话号码是xxxx， 购买了商品id为1的日本就单价为2000， 且买了3瓶。

虽然作为销售记录表主键的“销售记录 ID”字段并不是其他表的外键，但是考虑到以后有可能会与其他表发生关联，并且习惯上必定要在表中设置一个主键，所以予以保留。主键既可以只由一个字段充当，也可以将多个字段组合在一起形成复合主键。

image-20221128075904631

表之间的关系使记录和记录关联了起来。记录之间虽然在逻辑上有一对一、多对多以及一对多（等同于多对一）三种关系，但是在关系型数据库中无法直接表示多对多关系。这是因为在多个字段中以顺藤摸瓜的方式查找数据并不是那么容易的。如果将酒铺的数据库只分为顾客表和商品表，那么这两张表就形成了多对多关系。也就是说一位顾客可以购买多个商品，反过来一种商品可以被多位顾客所购买。

当出现多对多关系时，可以在这两张表之间再加入一张表，把多对多关系分解成两个一对多关系（如下图所示）。加入的这张表被称作连接表（Link Table）。在酒铺数据库中，销售记录表就是连接表。在表示一对多关系的连线的两端，写有“1”的一侧表示“一”，写有“∞”、即无穷大符号的一侧表示“多”。

image-20221128075246896

image-20221128080557246

DBMS还可以检查参照完整性， 为了一致且安全地存储数据。

例如，在目前的酒铺数据库中，如果从商品表中删除了“日本酒”这条记录，那么在销售记录表中，曾经记录着买的是日本酒的那两条记录就不再能说明买的是什么商品了。但是一旦勾选了实施参照完整性的选项（如图 8.11 所示），在应用程序中再执行这类操作时，DBMS 就会拒绝执行。

image-20221128080730422

8.6 索引能够提升数据的检索速度

索引Index： 用来提升数据检索和排序速度的内部机制。 一旦在字段上设置了索引，DBMS 就会自动为这个字段创建索引表。 可以在表的各个字段上设置索引（Index），这也是 DBMS 所具备的功能之一。

虽然索引和键这两个概念容易让人混淆，但其实两者是完全不同的。索引仅仅是提升数据检索和排序速度的内部机制。

image-20221128083236429

索引表 Index Table： 是一种数据结构，存储着字段的值以及字段所对应记录的位置。

优点：索引就像目录一样， 而索引表表中有两个字段分别存储着顾客姓名和位置， 索引表比原来的顾客表相比字段数更少， 所以可以更高效地进行数据的检索和排序。

缺点： 但是如果所有字段上都加上索引的话， 会导致 每次向表中插入数据时DBMS都必须更新索引表， 字段越多就会导致插入或者更新数据速度越慢， 所以只有对那些要频繁地进行检索和排序的字段才需要设置索引。

image-20221128083334617

8.7 设计用户界面

优先设计数据库，然后再设计用户界面

上面我们通过拆表实现了规范化、设置了主键和外键、确保没有多对多关系、根据需要设置了参照完整性和索引，那么数据库的设计就告一段落了， 为了利用数据库中的数据， 下面我们开始编写数据库应用程序。

CRUD： CREATE（插入）、REFER（获取）、UPDATE（更新）、DELETE（删除）。 对数据库进行的操作的种类通常称为CRUD。

那么设计的数据库只要能够对记录进行CRUD的操作就可以了。

由于 DBMS 具有自动生成主键和外键上的值的功能，所以在设计用户界面时，需要显示其余的字段，并要使 CRUD 操作能够通过按钮和菜单来完成。

下图展示了一个用四个按钮分别进行 CRUD 操作的例子。对于购买了多种商品的顾客，还可以通过“下一条”和“上一条”按钮交替地在界面上显示每种商品的名称、单价和销售量。请诸位注意一点，虽然数据被拆分成三个表存放，但是透过应用程序，用户感到他所处理的是一个相关数据的集合。界面中所显示的数据，是从三张表中用顺藤摸瓜的方式取出来的。

image-20221128084125922

8.8 向 DBMS 发送 CRUD 操作的 SQL 语句

为了让用户可以通过应用程序操作数据库， 我们就得让应用程序向DBMS发送命令。 这里所使用的命令就是 SQL 语言（Structural Query Language，结构化查询语言）。SQL 语言的标准是由 ISO（International Organization for Standardization，国际标准化组织）制订的。现在市面上几乎所有的DBMS 都支持 SQL 语言。

一旦向 DBMS 发送了一条命令（SQL 语句），与此相应的操作就会立刻被执行。

而SQL语句和英文句子很相似, 下面是一句SQL语句的例子：

SELECT 顾客姓名 , 住址 , 电话号码 , 商品名称 , 单价 , 销售量
FROM 顾客表 , 商品表 , 销售记录表
WHERE 顾客表 . 顾客姓名 = "日经次郎 "
AND 销售记录表 . 顾客 ID = 顾客表 . 顾客 ID
AND 销售记录表 . 商品 ID = 商品表 . 商品 ID ;

开头的 SELECT 所表示的就是 CRUD 中的 R 操作，也就从表中获取数据。在 SELECT 后面列出了想获取的字段的名字，用逗号分隔。在 FROM 后面，列出了用逗号分隔的表名。WHERE 后面则列出了查询条件。其中的 AND 表示多个查询条件是逻辑与的关系（条件 A 和条件 B 都成立）。而像“顾客表 . 顾客姓名”这样用“.”分隔的形式表示顾客姓名字段是属于顾客表的。在 SQL 语句的末尾放置一个分号表示语句的结束。

DBMS 不仅提供了手动向 DBMS 发送 SQL 语句的工具，而且还提供了通过可视化操作自动生成 SQL 语句的工具。将上述 SQL 语句发送到 DBMS 执行以后，结果如图 8.15 所示。日经次郎购入的商品一目了然。

image-20221128084755204

CRUD 中的 C、U、D 分别对应着 SQL 语言中的 INSERT（插入）、UPDATE（更新）、DELETE（删除）语句。在 SQL 语言中除了 CRUD语句，还有新建数据库以及后面将要介绍的事务控制等语句，有兴趣的读者，可以查查 SQL 语言的文档等资料。

附连接：

8.9 使用数据对象向 DBMS 发送 SQL 语句

在 Windows 应用程序中，向 DBMS 发送 SQL 语句时，一般情况下使用的都是被称为数据对象（Data Object）的软件组件（参考第 7 章所介绍的类）。一般的开发工具中也都包含了数据对象组件。在 Visual
Basic 6.0 中，使用的是被称为 ADO（ActiveX Data Object，ActiveX 数据对象）的数据对象。

ADO 是以下几个类的统称，其中包括用于建立和 DBMS 连接的Connection 类， 向 DBMS 发 送 SQL 语 句 的 Command 类 以 及 存 储DBMS 返回结果的 Recordset 类等。

如下面的代码所示， 在程序启动时连接 DBMS，然后进行与各个按钮对应的 CRUD 操作，在程序结束时关闭与 DBMS 的连接。在使用 ADO 时必不可少的是 SQL 语句，其中主要是 SELECT 语句。而插入、更新、删除语句可以通过Recordset 类所提供的 AddNew、Update、Delete 方法（类中所提供的函数）执行。可以认为这些方法在内部自动生成了SQL 语句并发送给了 DBMS。

代码清单 8.1 使用 ADO 访问数据库的示例程序（VB 6.0）：

' 实例化 ADO 提供的类
Dim con As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
' 处理程序启动事件
Private Sub Form_Load()
    con.ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=liquor_store.mdb"
    con.Open
End Sub
' 处理“录入”按钮单击事件
Private Sub cmdCreate_Click()
    rst.AddNew
    SetRecordset
    rst.Update
End Sub
' 处理“获取”按钮单击事件
Private Sub cmdRetrieve_Click()
    If rst.State = adStateOpen Then
        rst.Close
    End If
    rst.Open "SELECT 顾客姓名 , 住址 , 电话号码 , 商品名称 , 单价 , 销售量 " & _
    "FROM  顾客表 , 商品表 , 销售记录表 " & _
    "WHERE 顾客表 . 顾客姓名 = """ & txtCustomer.Text & """" & _
    "AND 销售记录表 . 顾客 ID = 顾客表 . 顾客 ID " & _
    "AND 销售记录表 . 商品 ID = 商品表 . 商品 ID", _
    con, adOpenKeyset, adLockOptimistic
    If rst.RecordCount > 0 Then
        rst.MoveFirst
        ShowRecordset
    Else
        MsgBox " 找不到符合条件的数据！ ", vbInformation, ""
    End If
End Sub
' 处理“更新”按钮单击事件
Private Sub cmdUpdate_Click()
    SetRecordset
    rst.Update
End Sub
' 处理“删除”按钮单击事件
Private Sub cmdDelete_Click()
    rst.Delete
    rst.Update
End Sub
' 处理“上一条”按钮单击事件
Private Sub cmdPrev_Click()
    rst.MovePrevious
    If rst.BOF Then
        rst.MoveFirst
    End If
    ShowRecordset
End Sub
' 处理“下一条”按钮单击事件
Private Sub cmdNext_Click()
    rst.MoveNext
    If rst.EOF Then
        rst.MoveLast
    End If
    ShowRecordset
End Sub
' 处理程序退出事件
Private Sub Form_Unload(Cancel As Integer)
    con.Close
End Sub
' Recordset 显示 Recordset 中的内容
Private Sub ShowRecordset()
    txtCustomer.Text = rst.Fields(0)
    txtAddress.Text = rst.Fields(1)
    txtPhone.Text = rst.Fields(2)
    txtItems.Text = rst.Fields(3)
    txtUnitPrice.Text = rst.Fields(4)
    txtSales.Text = rst.Fields(5)
End Sub
' Recordset 设置 Recordset 中的数据
Private Sub SetRecordset()
    rst.Fields(0) = txtCustomer.Text
    rst.Fields(1) = txtAddress.Text
    rst.Fields(2) = txtPhone.Text
    rst.Fields(3) = txtItem.Text
    rst.Fields(4) = txtUnitPrice.Text
    rst.Fields(5) = txtSales.Text
End Sub

8.10 事务控制也可以交给 DBMS 处理

事务控制： DBMS的一个高级功能。

事务Transaction： 由若干条SQL 语句构成，表示对数据库一系列相关操作的集合。

  • 例如：

    为了从顾客 A 的账户中给顾客 B 的账户汇入 1 万日元，就需要将以下两条 SQL 语句依次发送给 DBMS ：1. 把 A 的账户余额更新（UPDATE 语句）为现有余额减去1 万日元；2. 把 B 的账户余额更新（UPDATE 语句）为现有余额加上1 万日元。此时这两条 SQL 语句就构成了一个事务。

问题：执行事务中可能会发生问题，可能导致数据不一致。

假设在第一条 SQL 语句执行后，网络或计算机发生了故障，第二条 SQL 语句无法执行，那么会发生什么呢？ A 的账户余额虽然减少了1 万日元，但是 B 的账户余额却没有相应地增加 1 万日元，这就导致了数据不一致。

解决问题：

为了防止出现这种问题，在 SQL 语言中设计了以下三条语句：

  1. BEGIN TRANSACTION（开启事务）语句，用于通知 DBMS开启事务；
  2. COMMIT（提交事务）语句，用于通知 DBMS 提交事务；
  3. ROLL BACK（事务回滚）语句，用于在事务进行中发生问题时，把数据库中的数据恢复到事务开始前的状态

在使用ADO 创建应用程序时，可以分别使用 Connection 类的 BeginTrans、CommitTrans 和 RollbackTrans 方法实现上述三个操作。

image-20221128090333757

总结

数据库： database， 为了方便管理和使用数据， 我们将数据集合在数据库中。

  • 卡片型数据库： 将几个信息集合在一个卡片上， 多个卡片汇集起来就成了数据库，但是卡片与卡片之间缺乏关联性。
  • 关系型数据库: 数据被拆分整理到多个表中， 表与表之间有关系。

DBMS： database management system， 作为中介帮助用户通过应用程序操作数据库。 不仅可以让应用程序可以轻松读写数据文件， 还具有一致并且安全地存储数据的功能。

数据库系统： 数据文件、DBMS、应用程序

  • 独立型系统 文件共享型系统 服务器型系统 Web系统

设计数据库

  • 制作表： 考虑到数据的分类以及属性（数字还是字符串）
    • 表中的每一行数据都叫做记录record/元组tuple/行
    • 构成一条记录的各个数据项所在的列叫做字段field/列/属性attribute
    • 键Key： 为了反应表与表之间的关系而添加的新字段
      • 主键Primary Key： 唯一标识
      • 外键Foreign Key： 外表主键
    • 索引Index： 最好在需要频繁进行检索和排序的字段上设置索引， 用来提升数据检索和排序速度
      • 索引表
    • 表与表之间的关系： 一对多、多对多、一对一

设计用户界面

  • CRUD： CREATE、REFER(SELECT)、UPDATE、DELETE
    • SELECT: 从表中获取数据
    • WHERE： 后面跟查询条件
    • AND： 表示逻辑与
    • INSERT： 插入数据
    • UPDATE： 更新已有的数据
    • DELETE： 删除
  • 事务Transaction： 是对数据库一系列操作的集合
    • 为了防止执行事务中出现意外， 避免导致数据不一致， 我们可以使用下面三条语句：
      1. BEGIN TRANSACTION
      2. COMMIT
      3. ROLL BACK

标签：DBMS,Chapter8,一用,主键,SQL,rst,顾客,数据库
From： https://www.cnblogs.com/Natsumeno/p/16968017.html

相关文章

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99