计算思维与计算机导论 课件 09-数据库技术

8数据库技术

天津科技大学

计算机基础与信息技术教研部1目录9.1数据库概述9.2关系数据库 9.3SQLite简介9.4SQL9.5视图 9.1.1数据库体系结构数据库（DataBase，DB）是指长期存储在计算机内、有组织的、统一管理的相关数据的集合。数据库管理系统数据库管理系统（DBMS）是用于建立、使用、管理和维护数据库的系统软件，是数据库系统的核心组成部分。大型：Oracle、Sybase、SQLServer、DB2等中小型数据库管理系统有SQLite、MySQL、Access等国产自主研发：蚂蚁集团OceanBase、华为GaussDB等数据库管理系统数据库管理系统具有以下主要功能：（1）数据定义：定义数据库的各种对象。（2）数据处理：数据的基本操作（3）数据库的控制和管理：实现对数据库的控制和管理，确保数据正确有效和数据库系统的正常运行。括数据的并发性控制、完整性控制、安全性控制和数据库的恢复（4）数据库的建立和维护：数据库的建立包括数据库初始数据的输入、转换等数据库的维护包括数据库的转储、恢复、重组织与重构造、性能监视与分析等数据库系统（DBS）数据库系统（DataBaseSystem，DBS）是指带有数据库并利用数据库技术进行数据管理的计算机系统。数据库系统的软件（1）数据库管理系统（2）操作系统（3）应用系统（4）应用开发工具：编程语言：C、Python、Java、PHP、Delphi、VB数据库工具：SQLiteStudio等用户用户主要包括以下几类。（1）终端用户（2）应用程序员（3）数据库管理员（DataBaseAdministrator，DBA）数据库中存储和管理的数据都来源于现实世界的客观事物，计算机不能直接处理这些具体事物。现实世界信息世界

机器世界抽象（概念模型）抽象（数据模型）9.1.2概念模型9.1.2概念模型人们经常使用实体联系模型来表示概念模型。1．实体客观存在并且可以相互区别的事物称为实体。实体可以是具体的人、事、物。如一名学生、一本书、一门课程等；也可以是事件，如学生的一次选课、一场比赛、一次借书等。概念模型2．实体的属性实体所具有的某一特性称为属性。如学生实体有学号、姓名、性别、出生日期、专业等多个属性。属性包括属性名和属性值，如：学号、姓名、性别、出生日期、专业等为属性名，（13011103、许志华、男、06/12/1995、机械工程）为某个学生实体的属性值。概念模型3．实体型用实体名及其属性名来抽象描述同一类实体，称为实体型。如：学生（学号、姓名、性别、出生日期、专业）就是一个实体型，它描述的是学生这一类实体。概念模型4．实体集同类型实体的集合称为实体集。如：全体学生就是一个实体集而（13011103、许志华、男、06/12/1995、机械工程）是这个实体集中的一个实体。概念模型5．实体间的联系实体间的联系通常是指两个实体集之间的联系。联系有以下3种类型：（1）一对一联系（1：1）（2）一对多联系（1：n）（3）多对多联系（m：n）概念模型一对一联系（1:1） 如果对于实体集A中的每一个实体，实体集B中至多有一个实体与之对应；反之亦然，则称A与B具有一对一联系。例如：一个班级只有一个班长，而一个班长只能在一个班级任职。班级和班长之间具有一对一的联系概念模型一对多联系（1:n） 如果对于实体集A中的每一个实体，实体集B中有n个实体（n≥0）与之对应；反之，对于实体集B中的每一个实体,实体集A中至多只有一个实体与之对应，则称A与B具有一对多联系。例如：一个班级有多个学生，而每个学生只在一个班级中学习，则班级与学生之间具有一对多的联系。概念模型多对多联系（m:n） 如果对于实体集A中的每一个实体，实体集B中有n个实体（n≥0）与之对应；反之，对于实体集B中的每一个实体,实体集A中也有m个实体（m≥0）与之对应，则称A与B具有多对多联系。例如：一个学生可以选多门课，一门课也可以被多个学生选择课程与学生之间具有多对多的联系。概念模型6．E-R（Entity-Relationship实体-联系）图

包含实体、属性和联系E-R图（1）实体：用矩形框表示（2）属性：用椭圆形框表示，并用无向边将其与对应实体连接起来。（3）联系：用菱形框表示，框内写明联系名，并用无向边分别与有关实体连接起来，同时在无向边旁标注联系的类型（1：1，1：n或m：n）。关系模型数据模型是用来抽象和表示现实世界中事物与事物之间联系的结构模式。用二维表结构表示实体及实体间联系的数据模型称为关系模型。9.2关系数据库关系数据库是基于关系模型的数据库。在关系数据库中，数据存储在二维结构的表中，而一个关系数据库中，包含多个数据表。关系术语（1）关系： 一个关系就是一张二维表，每个关系有一个关系名。如：学生（2）元组： 表中的一行即为一个元组，也称为记录。如一行（3）属性：表中的一列即为一个属性，也称字段。如：学号、姓名、性别、出生日期、专业、生源地、民族、政治面貌、入学成绩关系术语（4）域：属性的取值范围。性别属性的域为文本（男，女）入学成绩属性的域为0～600的数字关系术语（5）关键字关系中能唯一标识元组的一个或一组属性称为关键字。如：学号（6）主关键字主关键字是在关系中指定的唯一标识元组的关键字。一个关系中只能有一个主关键字。如：学号是主关键字。关系术语（7）外部关键字：如果一个关系R中的某个属性不是本关系的主关键字，而是另一个关系S的主关键字，则称该属性为本关系R的外部关键字，R为参照关系，S为被参照关系。课程号是课程表的主关键字，学号是表学生表的主关键字，课程号和学号都是成绩表的外部关键字关系术语（7）关系模式对关系的描述，它对应一个关系的结构。其格式为：课程表的关系模式为：关系名（属性1，属性2，…，属性n）课程（课程号，课程名，课时，学分）关系完整性关系模型的完整性规则用于定义和保护表内部或表之间的数据关系，是对关系的某种约束条件。包括：实体完整性参照完整性用户定义完整性。关系完整性（1）实体完整性实体完整性也称行完整性。实体完整性规定：关系中所有元组的主关键字值不能为空值。

如：

学生表中学号为主关键字，所有学生的学号不能为空关系完整性（2）参照完整性参照完整性规定：若一个关系R的外部关键字F是另一个关系S的主关键字，则R中的每一个元组在F上的值必须是S中某一元组的主关键字的值，或者取空值。成绩表中课程号是外部关键字课程号是课程表的主关键字成绩表中的所有课程号都必须是课程信息表中的某个课程号。关系完整性（3）用户自定义完整性

用户定义完整性是针对某一具体关系的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。例如：成绩表，成绩应为数值型数据，取值范围为0～100学生表，性别为字符型数据，取值范围为（男，女）9.3SQLite简介SQLite是部署广泛的SQL数据库引擎之一SQLite使用嵌入式SQL数据库引擎，各引擎不与程序进程进行独立通信，而是被集成在用户程序中SQLite被广泛应用于消费电子、医疗、工业控制、军事等各领域。SQLite安装下载：sqlite-dll-win64-x64-3400100.zip工具包sqlite-tools-win32-x86-3400100.zip解压缩到一个文件夹下，如D:\SQLiteSQLite在msdos命令提示符下，切换到D:\SQLite到输入命令sqlite3，进入“SQLite数据库管理系统”。此时，可以进行数据库和数据操作。9.3.2SQLiteStudioSQLiteStudio是SQLite数据库的可视化工具，软件内部集成了SQLite数据库创建数据库执行“数据库→添加数据库”命令或者单击“添加数据库”按钮创建表在数据库中，表是整个数据库系统的基础，所有的原始数据都存储在表中创建表的工作包括：创建字段字段命名定义字段的数据类型设置字段属性创建表之前先要完成数据库的连接创建表执行“结构→新建表”命令或者单击“连接到数据库”按钮，填写“表名”。表中至少要有一个列创建表添加列：单击“添加列

”按钮，打开“列”对话框设定：列的名称数据类型约束数据类型包括①Text：文本②Integer：整数值③Real：浮点数④Blob：以二进制进行存储，任意数据类型⑤Numeric：亲和类型，在插入文本数据时，自动转换为Integer或者是Real类型数据，⑥Null：表示Null值，表示缺失信息的占位，可以根据输入数据的类型决定数据类型。数据类型数据表中某一列可能包含多种数据类型，它们也可以排序。SQLite数据库根据数据亲缘性与其他系统或数据库中的数据进行类型转换数据类型布尔值被存储为整数0（false）和1（true）Date与Time没有单独的存储类型，转换为Text、Real或者Integer存储。列的约束（1）主键：主键用于保证数据实体的完整性，主键可以是单列，也可以是多列的组合。一个表只能定义一个主键，不允许重复、不允许空值列的约束（2）外键：外键用于保证数据的参照完整性，即相关表中数据的一致性。外键引用其他表中主键字段，说明表与表之间的关系。学号为“学生”表的主键，学号为“选课”表的外键列的约束在更新或删除主键时，可能会破坏参照完整性。处理：“RESTRICT（限制）：则会报错，不执行改数据操作；“CASCATE（级联）”：将外键一起更新或删除。列的约束【例9.1】将“选课”表的“学号”字段设置为“学生”表中“学号”主键的外键，并限制更新操作、级联删除操作。双击“选课”表的“学号”字段，选中“外键”，配置外键。此时删除、更新主键，会影响到外键。列的约束（3）唯一：确保该列中的所有值都不重复。（4）条件：条件约束用于保证用户定义完整性，列的所有值满足一定条件。如：成绩字段的值大于等于0且小于等于100列的约束【例9.2】为“学生”表增加一个“电话号码”新的列，该列的长度必须为11位。增加一列，电话号码，设定条件列的约束SQLite有常用函数列的约束【例9.3】将“选课”表中的成绩值限制在0～100分。索引索引是在数据表中的列上建立的一种数据库对象，它保存着列的排序，并记录索引列在表中的物理存储位置，从而实现快速查找数据，提高查询的效率。索引在“教学管理”数据库中，学生表的索引，可以创建、编辑和删除索引。9.3.5修改表结构可以根据需要修改表的结构，设置字段类型、约束等。【例9.4】将学生表中“出生日期”的数据类型从原来的“Text”类型更改为“Integer”类型。选中“出生日期”字段，单击“编辑列”按钮9.3.6数据记录操作在数据表的维护过程中，可以:增加记录删除记录修改记录数据记录操作1．增加记录【例9.5】向学生表添加一条学生信息。打开学生表，单击“数据”选项卡，单击“插入行”按钮，依次填写数据记录操作2．修改记录双击数据表中的字段，该字段变为可编辑状态，可以直接修改内容。3．删除记录选中数据行，单击“删除行”按钮，即可删除记录。9.4.1SQL简介SQL（StructuredQueryLanguage，结构化查询语言）是一种通用的且功能强大的关系数据库语言，也是关系数据库的标准语言。包括：（1）数据定义语言（DataDefinitionLanguage，DDL），（2）数据处理语言（DataManipulationLanguage，DML）（3）数据控制语言（DataControlLanguage，DCL）常量SQL中常量的表示方法（1）数字型常量：25、-25、12.4。（2）文本型常量：西文的单/双撇号括起来

如

'英语'和"英语"（3）二进制常量：直接以二进制进行存储。算术运算符算术运算符用于进行算术运算关系运算符用于比较两个操作数的关系逻辑运算符逻辑运算符用于对操作数进行逻辑运算函数函数是一种能够完成某种特定操作或功能的数据形式，函数的返回值称为函数值。函数调用的格式：函数名([参数1][,参数2][,…])。max(12,34,56,38,30)运行SQL语句SQLiteStudio中，使用查询编辑器编写和运行SQL语句①执行菜单“工具→打开SQL编辑器”命令或者单击“打开SQL编辑器

”按钮②单击上方的“执行语句”按钮，执行SQL语句9.4.2CREATETABLE使用CREATETABLE语句定义表。语法格式为：CREATETABLE<表名>

(<字段名l><数据类型1>[(<大小>)][NOTNULL][PRIMARYKEY|UNIQUE]

,<字段名2><数据类型2>[(<大小>)][NOTNULL][PRIMARYKEY|UNIQUE]

[,…])（1）数据类型：用字符表示。（2）PRIMARYKEY：字段主键为主键。（3）UNIQUE：字段的唯一约束，不允许出现重复值。（4）NOTNULL：字段不允许为空。CREATETABLE【例9.6】使用SQL语句定义一个名为STUDENT的表。学号（文本型，8字符）、姓名（文本型，20字符）、性别（文本型，2字符）、出生日期（文本）、贷款否（整型）、简历（文本）、照片（BLOB），学号为主键，姓名不允许为空值。CREATETABLESTUDENT(学号TEXT(8)PRIMARYKEY,姓名TEXT(20)NOTNULL,性别TEXT(2),

出生日期

TEXT,贷款否INT,简历TEXT,照片BLOB)CREATETABLE9.4.3INSERTINTO使用INSERTINTO语句插入记录，语法格式为：INSERTINTO<表名>[(<字段名1>[,<字段名2>[，…]])]VALUES(<表达式1>[,<表达式2>[,…]])如果INTO后缺少字段名列表，则必须为该表的所有字段赋值，且各项数据和表中的字段顺序一一对应。提示执行完插入、更新、删除等操作，单击“刷新表数据

”按钮，将显示执行完SQL语句后的数据表。INSERTINTO【例9.7】使用SQL语句向STUDENT表中插入两条学生记录。INSERTINTOSTUDENTVALUES("22801109","朱晓","女","2004/9/26","是",null,null)INSERTINTOSTUDENT(学号,姓名,性别)VALUES("22801110","彭宇","男")9.4.4UPDATE使用UPDATE语句更新记录，语法格式为：UPDATA<表名>SET<字段名1>=<表达式1>[,<字段名2>=<表达式2>[，…]][WHERE<条件>]如果不带WHERE子句，则更新表中所有的记录；如果带WHERE子句，则只更新表中满足条件的记录。UPDATE【例9.8】使用SQL语句将“学生”表中所有女生的“贷款否”字段改为“否”。UPDATE学生SET贷款否="否"WHERE性别="女"9.4.5DELETE使用DELETE语句删除记录，其语法格式为：DELETEFROM<表名>[WHERE<条件>]如果不带WHERE子句，则删除表中所有的记录；如果带WHERE子句，则只删除表中满足条件的记录。DELETE【例9.9】使用SQL语句删除“学生”表中学号为“22801110”的学生记录。DELETEFROM学生WHERE学号="22801110"9.4.6SELECT语句SELECT语句从数据库中选取数据，其语法格式为：SELECT[ALL|DISTINCT][TOP<数值>[PERCENT]]<目标列>[[AS]<列标题>]FROM<表或查询1>[[AS]<别名1>],<表或查询2>[[AS]<别名2>][WHERE<连接条件>AND<筛选条件>][GROUPBY<分组项>[HAVING<分组筛选条件>]][ORDERBY<排序项>[ASC|DESC]][LIMITN]关系运算是一种抽象的查询语言，它用对关系的运算来表达查询，包括：投影选择连接1．单表查询单表查询仅涉及从一个表中的查询数据。（1）投影在关系运算中，从关系中挑选若干属性组成新的关系称为投影。SELECT[DISTINCT]<目标列1>[,<目标列2>[，…]]

FROM<表或查询>①查询指定的字段：指定要查询的各字段名。②查询所有的字段：在目标列中使用“*”。③消除重复的记录：在字段名前加上DISTINCT④查询计算的值。投影【例9.10】查询“学生”表中所有学生的姓名、性别和出生日期。SELECT姓名,性别,出生日期FROM学生（2）选择在关系运算中，从关系中找出满足给定条件的那些元组称为选择。条件是逻辑表达式，值为真的元组将被选取。SELECT<目标列>FROM<表名>WHERE<条件>选择【例9.11】查询“选课”表中成绩在80～90分的记录。SELECT*FROM选课WHERE成绩BETWEEN80AND90选择【例9.12】查询“学生”表中出生日期在“2004/1/1”之后的女生。SELECT*FROM学生WHERE出生日期>="2004/1/1"and性别="女"（3）排序排序使用ORDERBY子句对查询结果按照一个或多个列的升序（ASC）或降序（DESC）排列，默认是升序。【例9.13】查询成绩在50～90分的记录，同一门课程按成绩降序排。SELECT*FROM选课WHERE成绩BETWEEN50AND90ORDERBY课程号,成绩DESC排序使用LIMIT子句可以选出排在前面的若干记录。【例9.14】查询“选课”表中成绩排在前5名的记录。SELECT*FROM选课ORDERBY成绩DESCLIMIT5（4）分组查询使用GROUPBY子句可以对查询结果按照某一列的值分组分组查询通常与SQL聚合函数一起使用，先按指定的字段分组，再对各组进行合计。如果未分组，则聚合函数作用于整个查询结果。分组查询【例9.15】统计“学生”表中各系的学生人数。SELECT系部,COUNT(*)AS各系人数FROM学生GROUP

BY系部2．多表查询多表查询指的是从多个表中查询数据。（1）笛卡儿积笛卡儿积是指在数学中，集合A的元素与集合B的元素的所有可能有序对的集合，表示为A×B。在关系运算中，笛卡儿积指的是X关系的每一条记录和Y关系的每一条记录连接构成的新关系。多表查询【例9.16】查询“学生”表和“选课”表的笛卡儿积。SELECT*FROM学生,选课“学生”表中有20条记录，“选课”表有8条记录，查询结果将有160条记录。（2）连接运算在关系运算中，连接运算是从两个关系的笛卡儿积中选择属性间满足一定条件的元组。条件放在WHERE子句中，语法格式为：SELECT<目标列>FROM<表名1>,<表名2>WHERE<表名1