MySQL数据库基础实例教程

第1章数据库基础知识第1章数据库基础知识第1章数据库基础知识项目知识要点知识能力目标学时任务1了解数据库的基础知识

1数据与数据库2数据库技术的发展3数据库管理系统（DBMS）4数据库系统的组成5结构化查询语言SQL6常见的关系型数据库管理系统任务2安装与配置MySQL数据库

1

MySQL服务器安装与配置【典型工作任务】安装与配置MySQL数据库【技能要求】1

MySQL的安装和配置2

MySQL运行环境测试【知识要求】1数据库管理系统组成2服务器连接、启动和运行2项目知识要点与目标第1章数据库基础知识数据库基础知识数据狭义：数值数据广义：文字、声音、图形等一切能被计算机接收且能被处理的符号大量、海量数据库存储大量数据，方便检索和访问保持数据信息的一致、完整共享和安全通过组合分析，产生新的有用信息数据库管理系统（软件）管理大量的、持久的、可靠的、共享的数据的工具特点第1章数据库基础知识数据管理技术的发展阶段人工管理阶段-〉文件系统阶段-〉数据库管理第1章数据库基础知识数据管理技术的发展阶段没有专用的软件对数据进行管理。数据的组织方式必须由程序员自行设计与安排。只有程序的概念而没有文件的概念、数据面向应用，也就是一组数据只能对应一个程序。人工管理阶段第1章数据库基础知识数据管理技术的发展阶段文件系统的三个缺陷：数据冗余性数据不一致性数据联系弱。文件系统阶段第1章数据库基础知识数据库数据库就是“数据”的“仓库”

数据库由表、关系以及操作对象组成数据存放在表中所谓数据库(Database,DB)，是将数据按一定的数据模型组织、描述和存储，具有较小的冗余度，较高的数据独立性和易扩展性，并可为各种用户共享的数据集合。…订单客户产品数据表存储过程视

图….表产品数据库第1章数据库基础知识关系型数据库关系模型使用的存储结构是多个二维表格，表中每一行称为一条记录，用来描述一个对象的信息；每一列称为一个字段，用来描述对象的一个属性。数据表于数据库之间存在相应的关联，这些关联将用来查询相关的数据。关系型数据库列行（记录）第1章数据库基础知识数据库管理系统数据库管理系统（DBMS-DataBase

ManagementSystem

）对收集到的大量数据进行整理、加工、归并、分类、计算、存储等处理，产生新的数据，以便反映事物或现象的本质和特征及其内在联系。A003杨可A004马兰B007李新爱B008刘开泰数据库检索数据插入数据更新数据删除数据数据库管理系统-DBMS第1章数据库基础知识数据库系统的基本概念数据库服务器响应和提供数据应用程序客户端操作和查询数据库应用程序作用：响应操作并显示结果、向数据库请求数据要求：美观、操作简单方便数据库作用：存储数据、检索数据、生成新的数据要求：统一、安全、性能等数据库应用系统第1章数据库基础知识数据库系统

数据库系统(Database

System,DBS)一般由数据库、数据库管理系统(及其开发工具)、应用系统、数据库管理员和用户结构组成第1章数据库基础知识结构化查询语言SQL结构化查询语言SQL（Structured

Query

Language）SQL语言是用于关系数据库查询的结构化语言，最早由Boyce和Chambedin在1974年提出，称为

SEQUEL语言。1976年，IBM公司的San

Jose研究所在研制关系数据库管理系统System

R时修改为SEQUEL2，即目前的SQL语言。SQL语言集数据查询（data

query）、数据操纵（data

manipulation）、数据定义（data

definition）和数据控制（data

control）功能于一体，充分体现了关系数据语言的特点和优点。主要特点包括：综合统一高度非过程化面向集合的操作方式以同一种语法结构提供两种使用方式语言简洁，易学易用第1章数据库基础知识大数据时代的数据库管理系统SQL-关系型数据库管理系统（RDBMS）不同的角色（开发者，用户，数据库管理员）使用相同的语言。不同的RDBMS使用统一标准的语言。SQL使用一种高级的非结构化查询语言。坚持ACID

准则(原子性，一致性，隔离性，持久性)NoSQL-非关系性数据库

采用Key-value方式存储数据。采取最终一致性原则，更加适合互联网数据，但这也可能导致数据丢失。NewSQL-结合SQL和NoSQL

将SQL的ACID保证与NoSQL的可扩展性和高性能相结合，目前大多数NewSQL数据库都是专有软件或仅适用于特定场景，这显然限制了新技术的普及和应用。第1章数据库基础知识数据库产品分类图第1章数据库基础知识数据库流行排行榜Oracle、MySQL、SQL

Server排前三位，其流行度远远超过其他数据库。在非关系型数据库中，比较流行的有MongoDB，Elasticsearch，Redis等。第1章数据库基础知识时下流行的DBMS简介OracleOracle公司的产品，世界上最好的数据库系统“关系-对象”型数据库支持70多种操作系统，配置、管理和维护复杂主要满足对银行、金融、保险等企业、事业开发大型数据库需求MySQL瑞典MySQLAB公司开发，现在MySQL并入了Oracle旗下体积小、速度快、成本低、开放源码广泛地应用在Internet上的中小型网站中SQL

ServerMicrosoft公司的产品，针对不同用户群体的多个版本要求在Windows操作系统平台上运行易用性好第1章数据库基础知识MySQL的安装1、下载地址：https:///do

wnloads/--下载MySQL社区版第1章数据库基础知识MySQL的安装MySQL社区版是免费的，有MSI和zip两种安装方式：下载前先选择安装平台（windows）安装文件方式（MSI或Zip)第1章数据库基础知识MySQL的安装3.安装向导--产品类型选择：

Server

only（服务器）第1章数据库基础知识MySQL的安装4.配置向导--类型和网络配置：

Server

Computer第1章数据库基础知识MySQL的安装5.配置向导--账号和角色配置：为MySQL的超级用户root设置

密码第1章数据库基础知识连接服务器“开始”→“程序”→MySQL→“MySQL

Server

8.0”→“MySQL

8.0

Command

Line

Client”。在客户端窗口输入安装时为root用户设置的密码。窗口中出现MySQL命令行提示符“mysql>“，安装成功。第1章数据库基础知识MySQL图形化管理工具Navicat

for

MySQL是一个桌面版

MySQL数据库管理和开发工具，易学易用，很受大家的欢迎。本课程将以Navicat

for

MySQL为例介绍MySQL数据库管理工具的使用。第1章数据库基础知识THANKS第2章

数据库设计第2章数据库设计第2章

数据库设计项目知识要点知识能力目标学时任务1设计数据库关系模型

1数据模型2概念模型3

E-R图的设计4数据库关系模型的建立任务2数据库设计规范化关系型数据库范式理论数据库规范化实例综合实例PetStore数据库的设计【典型工作任务】对数据库系统进行分析，建立E-R图，关系模型【技能要求】设计数据库关系模型数据库规范化【知识要求】掌握数据库设计的相关知识运用E-R图等工具设计数据库4项目知识要点与目标第2章

数据库设计设计数据库关系模型为什么需要设计数据库数据库设计就是将数据库中的数据对象以及这些数据对象之间关系进行规划和结构化的过程修建茅屋需要设计吗？修建大厦需要设计吗？结论：当数据库比较复杂时我们需要设计数据库第2章

数据库设计设计数据库关系模型

概念模型:把现实世界转换为信息世界的模型,E-R模型。

实施模型:把信息世界转化为数据世界使用的模型,关系模型。现实世界建模信息世界模型转换规范化数据库世界数据库数据库事物类事物性质实体集实体属性文件记录数据项实体模型概念模型数据模型第2章

数据库设计实体模型

现实世界客观存在的、可以被描述事物都是“实体”第2章

数据库设计层次模型按层次模型组织的数据示例第2章

数据库设计网状模型按网状模型组织的数据示例第2章

数据库设计关系模型按关系模型组织的数据示例第2章

数据库设计概念模型-E-R图E-R图的组成要素及其画法学号总学分专业名出生日期性别姓名学生课程课程号学分学时开课学期类别课程名选课成绩建模：把现实世界转换为信息世界的模型,E-R模型实体名联系名属性名属性名或（5）映射基数1:1,1:n,m:n（1）实体（2）联系（3）属性（4）关键字第2章

数据库设计实体间不同联系情况的E-R图表示法例：某学校有若干个学院，每个学院只有一个院长。院长和学院之间是一对一的关系。一对一(1:1)联系：1院长管理1学院姓名年龄学历编号学院编号学院名称任职时间第2章

数据库设计实体间不同联系情况的E-R图表示法例：在某仓库管理系统中，有两个实体集：仓库和商品。仓库用来存放商品，且规定一类商品只能存放在一个仓库中，一个仓库可以存放多件商品。一对多（1：m）的联系仓库和商品是一对多（1：m）的联系，在存放联系中要反映出存放商品的数量。1仓库商品存放n地点面积仓库号商品号价格数量商品名第2章

数据库设计实体间不同联系情况的E-R图表示法假设在某教务管理系统中，一个教师可以上多门课，一门课也可以由多个老师去上。多对多（m:n)联系：教师和课程之间是多对多（m:n)联系，在“讲授”联系中应能反映出教师的授课质量。m教师课程讲授n教师名职称教师号课程号质量课程名

班级教师和课程之间的多对多联系第2章

数据库设计E-R图设计实例【例】网络图书销售系统处理会员图书销售。简化的业务处理过程为：网络销售的图书信息包括：图书编号，图书类别，书名，作者，出版社，出版时间，单价，数量，折扣，封

面图片等；用户需要购买图书必须先注册为会员，提供身份证号，会员姓名，密码，性别，联系电话，注册时间等信息；系统根据会员的购买订单形成销售信息，包括订单号，身份证号，图书编号，订购册数，订购时间，是否发货，是否收货，是否结清。请画出网络图书销售数据库E-R图。第2章

数据库设计E-R图设计步骤01OPTION02OPTION03OPTION04OPTION确定实体集：网络图书销售系统中有两个实体集：图书和会员确定实体集属性及主码：实体集会员属性有：身份证号，会员姓名，性别，联系电话，注册时间，密码。会员实体集中可用身份证号来唯一标识各会员，所以主码为身份证号。实体集图书属性有：图书编号，图书类别，书名，作者，出版社，出版时间，单价，数量，折扣，封面图片。图书实体集中可用图书编号来唯一标识图书，所以主码为图书编号。确定实体集之间的联系：图书销售给会员时图书与会员建立关联，联系“销售”的属性有：订购册数，订购时间，是否发货，是否收货，是否结清。为了更方便标识销售记录，可添加订单号作为该联系的主码。确定联系关系：因为一个会员可以购买多种图书，一种图书可销售各多个会员，所以这是一种多对多（m:n）的联系。根据以上分析画出的网络图书销售数据库E-R图第2章

数据库设计网络图书销售数据库E-R图第2章

数据库设计E-R图设计步骤对于复杂的系统，E-R图设计通常都应经过以下两个阶段：（1）针对每一用户画出该用户信息的局部E—R图，确定该用户视图的实体、属性和

联系。需注意的是：能作为属性的就不要作为实体，这有利于E—R图的简化。（2）综合局部E—R图，生成总体E—R图。在综合过程中，同名实体只能出现一次，还要去掉不必要的联系，以便消除冗余。一般来说，从总体E—R图必须能导出原来的所

有局部视图，包括实体、属性和联系。第2章

数据库设计复杂E-R图设计实例【例】工厂物流管理中的涉及雇员、部门、供应商、原材料、成品和仓库等实体，并且存在以下关联：（2）每一个部（门1）一个雇员只能在可以生产多种一成个部门工作，一个

品，但一种成部品门可以有多个雇员。只能由一个部门生产。（5）4（）各3购）部买一门的个从原供仓材应库料商中放

提在取仓可原库以料中供，应并成多将品种成也原品放材放在仓料库，中一。种一原个材仓料库也可

以可存以可放存以多放由个多部种个门产供的品应产，商品，一个种供部产货门品。的也产可品以也存可放以

存在放不在同不的同仓的库仓中库。中。画出简单的工厂物流管理系统E-R模型。第2章

数据库设计局部E-R图01OPTION02OPTION03OPTION04OPTION找出工厂物流管理系统实体集：分别是雇员、部门、成品、供应商、原材料和仓库。从生产的角度，根据（1）和（2）画出雇员、部门和成品三个实体间的初步联系。从供应的角度，根据（3）画出供应商和原材料两个实体间的初步联系。为是问题简化，实体的属性先不画出。第2章

数据库设计局部E-R图05OPTION06OPTION从仓储的角度，根据（4）和（5）画出仓库与各实体之间的联系，最终得到工厂物流管理系统的E-R图。实体联系中有多个数量属性，分别用数量1，数量2…以区分第2章

数据库设计课堂练习

学校有若干个学院，每个学院有各自的编号、名称和院长；每个学院有若干名教师和学生，教师有教师号、教师名和职称属性，每个教师可以担任若干门课程，一门课程只能由一位教师讲授，课程有课程号、课程名和学分，并参加多项项目，一个项目有多人合作，且责任轻重有个排名，项目有项目号、名称和负责人；学生有学号、姓名、年龄、性别，每个学生可以同时选修多门课程，选修有分数。设计此学校的教学管理的E-R模型第2章

数据库设计课堂练习分析

学校有若干个学院，每个学院有各自的编号、名称和院长；每个学院有若干名教师和学生，教师有教师号、教师名和职称属性，每个教师可以担任若干门课程，一门课程只能由一位教师讲授，课程有课程号、课程名和学分，并参加多项项目，一个项目有多人合作，且责任轻重有个排名，项目有项目号、名称和负责人；学生有学号、姓名、年龄、性别，每个学生可以同时选修多门课程，选修有分数。实体集：学院、教师、学生、课程、项目第2章

数据库设计E-R图设计中常见问题分析【例】画出出版社和图书的E-R图。1.实体与联系分析：一个出版社可以出版多本图书，一本图书只能由一个出版社出版，出版社和图书之间就是一对多的关系。图书实体有出版社、书名、作者、价格等属性。为了唯一标识图书，还应设置书号属性。根据以上分析，画出出版社与图书的E-R图如下：2.属性与主码：出版社实体有社名、地址、邮编、网址、联系电话等属性。为了建立出版社与图书实体一对多的联系，还应该有一个出版社代码来唯一标识出版社；第2章

数据库设计图书出版E-R图问题思考（1）1、怎么标识书号-属性内容如何选取？为了管理方便，国际上规定：全世界的每本书都应该有唯一的编号，这个号码叫做ISBN号，俗称书号。因此，用ISBN书号作为图书的唯一标识似乎是非常合理的。但是，有时候出版社也出版一些没有书号的资料。所以，在设计实际数据库系统的时候，可也以根据实际情况不以ISBN书号唯一标识图书实体，而自己定义唯一标识图书实体的

属性。在现实世界中，类似这样的问题有很多，通常需要为实体集定义额外的关键字段。第2章

数据库设计图书出版E-R图问题思考（2）2、有些属性的值如果有多个，该怎么办？如出版社实体应该有电话属性。但一个出版社一般不止一部电话，怎么处理？一种方法是仍使用一个电话属性，只记下一部或几部甚至全部的电话号码即可，这种方法适合于小单位。第二种方法是将电话属性独立出来，建立一个新的电话实体，通过出版社代码属性，建立和出版社的一对多联系。第2章

数据库设计图书出版E-R图问题思考（3）3、一个实体究竟有多少属性？实体的属性可以说是无穷无尽，到底应提取哪些属性，要结合具体应用系统考虑。比如，图书的一般属性有书号、社代码、书名、作者、价格、版次等，如果开发的是书店管理系统，这些属性一般够用了，但如果要开发印刷厂管理系统，还需要增加图书大小（32开还是16开，或具体数字）、印刷纸张规格（60克纸还是70克纸，书写纸还是双面胶）、是否彩印、彩印规格、印刷数量、交货日期等属性。所以，提取一个实体的属性也要具体问题具体分析。通过出版社—图书的例子引出的三个问题，说明建立在现实世界基础上的E-R模型并不只有唯一答案。面向不同的应用、使用不同的方法，可以设计出不同的E-R模型。第2章

数据库设计E—R模型到关系模型的转换把E-R图转换为关系模型可遵循如下原则:0102对于E—R图中的联系，情况比较复杂，要根据实体联系方式的不同，采取不同的手段加以实现。对于E—R图中每个实体集，都应转换为一个关系，该关系应包括对应实体的全部属性，并应根据关系所表达的语义确定哪个属性或哪几个属性组作为“主关键字”，主关键字用来标识实体。第2章

数据库设计（1:1）联系的E-R图转换联系单独对应一关系模式，则由联系属性、参与联系的各实体集的主码属性构成关系模式，其主码可选参与联系的实体集的任一方的主码。BJ（班级编号，院系，专业名，人数）BZ（学号，姓名）SY（学号，班级编号）联系不单独对应一关系模式，联系的属性及一方的主码加入另一方实体集对应的关系模式中。BJ（班级编号，院系，专业名，人数）

BZ（学号，姓名，班级编号）或者BJ（班级编号，院系，专业名，人数，学号）BZ（学号，姓名）第2章

数据库设计（1:n）联系的E-R图转换联系单独对应一关系模式，则由联系的属性、参与联系的各实体集的主码属性构成关系模式，n

端的主码作为该关系模式的主码。BJ（班级编号，院系，专业名，人数）XS(学号，姓名，专业名，性别，出生时间，总学分，备注)SY（学号，班级编号）（2）联系不单独对应一个关系模式，则将联系的属性及1端的主码加入n端实体集对应的关系模式中，主码仍为n端的主码BJ（班级编号，院系，专业名，人数）XS(学号，姓名，专业名，性别，出生时间，总学分，备注，班级编号)第2章

数据库设计（m:n）联系的E-R图转换对于（m:n）的联系，单独对应一关系模式，该关系模式包括联系的属性、参与联系的各实体集的主码属性，该关系模式的主码由各实体集的主码属性共同组成。BJ（班级编号，院系，专业名，人数）XS(学号，姓名，专业名，性别，出生时间，总学分，备注)KC（课程号，课程名称，类别，开课学期，学时，学分）XS_KC(学号，课程号，成绩)第2章

数据库设计E-R模型到关系模型的转换总结假设A实体集与B实体集是1:1的联系，联系的转换有三种方法：①把A实体集的主关键字加入到B实体集对应的关系中，如果联系有属性也一并加入；②把B实体集的主关键字加入到A实体集对应的关系中，如果联系有属性也一并加入；③建立第三个关系，关系中包含两个实体集的主关键字，如果联系有属性也一并加入。两实体集间1:n联系两实体集间1:n联系，可将“一方”实体的主关键字纳入“n方”实体集对应的关系中作为“外部关键字”，同时把联系的属性也一并纳入“n方”对应的关系中。两实体集间m:n联系对于两实体集间m:n联系，必须对“联系”单独建立一个关系，用来联系双方实体集。该关系的属性中至少要包括被它所联系的双方实体集的“主关键字”，并且如果联系有属性，也要归入这个关系中。第2章

数据库设计数据库设计规范化仅有好的RDBMS并不足以避免数据冗余，必须在数据库的设计中创建好的表结构关系数据库范式理论是在数据库设计过程中将要依据的准则，数据库结构必须要满足这些准则，才能确保数据的准确性和可靠性。这些准则则被称为规范化形式，即范式。Dr

E.F.codd最初定义了规范化的三个级别，范式是具有最小冗余的表结构。这些范式是：第一范式(1st

NF－First

Normal

Fromate)第二范式(2nd

NF－Second

Normal

Fromate)第三范式(3rd

NF－Third

Normal

Fromate)第2章

数据库设计第一范式(1st

NF)第一范式的目标是确保每列的原子性如果每列都是不可再分的最小数据单元（也称为最小的原子单元），则满足第一范式（1NF）BuyerIDAddressBuyerIDCountryAddress1中国北京市1中国北京2美国纽约市2英国伦敦3英国伦敦3日本东京4日本东京市4美国纽约…………………………第2章

数据库设计第二范式(2nd

NF)如果一个关系满足1NF，并且除了主键以外的其他列，都依赖与该主键，则满足第二范式（2NF）第二范式要求每个表只描述一件事情Orders字

段订单编号OrdersProducts例

子001产品编号A001订购日期2000-2-3产品名称洗衣机规格……字

段订单编号例

子001订购日期2000-2-3字

段产品编号例

子A001产品名称洗衣机第2章

数据库设计第三范式(3rd

NF)

如果一个关系满足2NF，并且除了主键以外的其他列都不传递依赖于主键列，则满足第三范式（3NF）Orders字

段Orders例

子订单编号001订单编号001产品编号AB001产品编号AB001单价30单价30数量50数量50金额……字

段例

子第2章

数据库设计课堂练习分析以下是两个同学设计的学生成绩系统的表格，A同学设计成表格1的形式，B同学设计了表格2、3、4三张表格，请问：A、B同学哪个的方案更合理？假设：设计的表格需要记录5000个同学的10门课成绩，用A同学设计表格要填写多少个数据？用B同学设计的表格要填写多少个数据？根据计算结果，哪种设计更节省空间。学号姓名年龄课程名称成绩学分010101张三20计算机基础802010102李四20计算机基础852010101张三20英语753010102李四20英语853A同学设计表格学号姓名年龄010101张三20010102李四20课程名称学分计算机基础2一张表：6*10*5000=30，0000

个数据

英语三张表：学生表：3*5000=15000；

课程表：2*10=20；

成绩表：3*10*5000=150000三张表合计：15000+20+150000=16，5020A与B方案数据差：30,0000-16,5020

=13,49803学号课程名称成绩010101

计算机基础010102

计算机基础010101

英语808575010102英语85B同学设计表格第2章

数据库设计规范化实例分析（1）假设某建筑公司要设计一个数据库。公司的业务规则概括说明如下：公司承担多个工程项目，每一项工程有：工程号、工程名称、施工人员等公司有多名职工，每一名职工有：职工号、姓名、性别、职务（工程师、技术员）等

公司按照工时和小时工资率支付工资，小时工资率由职工的职务决定（例如，技术员的小时工资率与工程师不同）公司定期制定一个工资报表，如图-1所示第2章

数据库设计规范化实例分析（2）工程号工程名称职工号姓名职务小时工资率工时实发工资A1花园大厦1001齐光明工程师6513845.001002李思岐技术员6016960.001004葛宇宏律师60191140.00小计2945.00A2立交桥1001齐光明工程师6515975.001003鞠明亮工人5517935.00小计1910.00A3临江饭店1002李思岐技术员60181080.001004葛宇洪技术员6014840.00小计1920.00第2章

数据库设计规范化实例分析（3）工程号工程名称职工号姓名职务小时工资率工时A1花园大厦1001齐光明工程师6513A1花园大厦1002李思岐技术员6016A2立交桥1001齐光明工程师6515A2立交桥1003鞠明亮工人5517A3临江饭店1002李思岐技术员6018A3临江饭店1004葛宇洪技术员6014第2章

数据库设计规范化实例分析（4）表中包含大量的冗余，可能会导致数据异常：添加异常若要增加一个新的职工时，首先必须给这名职工分配一个工程。或者为了添加一名新职工的数据，先给这名职工分配一个虚拟的工程。（因为主关键字不能为空）删除异常例如，1001号职工要辞职，则必须删除所有职工号＝1001的数据行。这样的删除操作，很可能丢失了其它有用的数据更新异常例如，修改职工号=1001的职务，则必须修改所有职工号=1001的行第2章

数据库设计规范化实例分析（5）图-3函数依赖图采用这种方法设计表的结构，虽然很容易产生工资报表，但是每当一名职工分配一个工程时，都要重复输入大量的数据。这种重复的输入操作，很可能导致数据的不一致性。一张表描述了多件事情，如图-3所示。工程号工程名称职工号姓名职务小时工资率工时工程信息员工信息项目工时信息第2章

数据库设计应用第二范式规范化工程号工程名称职工号姓名职务小时工资率工程号职工号工时图-4应用第二范式工程表员工表满足第三范式吗？项目工时表第2章

数据库设计应用第三范式规范化工程号工程名称职工号姓名职务职务小时工资率工程号职工号工时工程表员工表职务表工程表第2章

数据库设计课堂练习S公司商品销售管理系统中员工表如下：Employees（员工号，姓名，性别，出生年月，聘任日期，工资，奖金，所在部门和部门主管）其中奖金按工资*30%+工作年限*50计算而得。请对员工表进行规范化。第2章

数据库设计规范化和性能的关系通过在给定的表中添加额外的字段，以大量减少需要从中搜索信息所需的时间通过在给定的表中插入计算列（如成绩总分），以方便查询为满足某种商业目标，数据库性能比规范化数据库更重要进行规范化的同时，还需要综合考虑数据库的性能。第2章

数据库设计总结在需求分析阶段，设计数据库的一般步骤为：收集信息标识对象标识每个对象的属性标识对象之间的关系在概要设计阶段和详细设计阶段，设计数据库的步骤为：绘制E-R图将E-R图转换为表格应用三大范式规范化表格为了设计结构良好的数据库，需要遵守一些专门的规则，称为数据库的设计范式。第一范式（1NF）的目标：确保每列的原子性。第二范式（2NF）的目标：确保表中的每列，都和主键相关。第三范式（3NF）的目标：确保每列都和主键列直接相关，而不是间接相关。第2章

数据库设计THANKS第2章

数据库设计第3章数据定义第2章

数据库设计项目知识要点知识能力目标学时任务1创建与管理数据库

1创建数据库2管理数据库任务2创建与管理数据库表1创建数据库表2管理数据库表任务3数据完整性约主键约束替代键约束参照完整性约束【典型工作任务】学会创建与管理数据库和表【技能要求】掌握创建与管理数据库的方法掌握创建与管理数据库表的方法掌握建立数据完整性约束的方法【知识要求】数据库和数据库表的结构数据完整性约束数据库及表操作的SQL语句4项目知识要点与目标第2章

数据库设计E-R图设计实例【例1.1】网络图书销售系统处理会员图书销售。简化的业务处理过程为：网络销售的图书信息包括：图书编号，图书类别，书名，作者，出版社，出版时间，单价，数量，折扣，封面图片等；用户需要购买图书必须先注册为会员，提供身份证号，会员姓名，密码，性别，联系电话，注册时间等信息；系统根据会员的购买订单形成销售信息，包括订单号，身份证号，图书编号，订购册数，订购时间，是否发货，是否收货，是否结清。请画出网络图书销售数据库E-R图,并将其转化为关系模型第2章

数据库设计网络图书销售数据库E-R图第2章

数据库设计网络图书销售数据库E-R图关系模型：Bookstore（数据库）01OPTION02OPTION03OPTIONbook(图书编号,图书类别,书名,作者,出版社,出版时间,单价,数量,折扣,封面图片)，如下表。members(用户号,姓名,性别,密码,联系电话,注册时间)。sell(订单号,用户号,图书编号,订购册数,订购单价,订购时间,是否发货,是否收货,是否结清)。第2章

数据库设计网络图书销售数据库E-R图members表sell表第2章

数据库设计MySQL数据库模型在数据库服务器中可以存储多个数据库文件，所以建立数据库时要设定数据库的文件名，每个数据库有惟一的数据库文件名作为与其它数据库区别的标识。数据库文件：数据库是由相关数据表组成，一个数据库包括多个数据表。数据库文件用于记录数据库中数据表构成的信息。数据库只能由得到授权的用户访问，这样保证了数据库的安全。成绩数据库

xscj网络图书库

bookstore雇员管理库

YGGL……….库MySQL服务器第2章

数据库设计数据库和表数据库可以看成是一个存储数据对象的容器，这些数据对象包括表、视图、触发器、存储过程等，其中，表是最基本的数据对象，用以存放数据库的数据。必须首先创建数据库，然后才能创建数据库的数据对象。MySQL可以采用两种方式创建、操作数据库和数据对象（1）命令行方式（2）图形界面工具方式数据库数据表视图存储过程DELIMITER

$$CREATEPROCEDUREDELETE_STUDENT(IN

XHCHAR(6))BEGIN第2章

数据库设计创建数据库创建数据库

MySQL安装后，系统自动地创建information_scema和MySQL数据库，MySQL把有关数据库的信息存储在这两个数据库中。如果删除了这些数据库，MySQL就不能正常工作。对于用户的数据，需要创建新的数据库来存放。语法格式：CREATE

{DATABASE

|

SCHEMA}[IF

NOT

EXISTS]数据库名[[DEFAULT]CHARACTER

SET字符集名|

[DEFAULT]COLLATE校对规则名]说明：语句中“[]”内为可选项。{|}表示二选一。Create

database

bookstore第2章

数据库设计命令行方式创建数据库在SQL语言创建数据库命令create

database中，如果省略语句中“[]”中的所有可选项，其结构形式如下：CREATE

DATABASE数据库名;数据库名表示被创建数据库名，数据库名必须符合以下规则：数据库名必须唯一；名称内不能含有“/”及“.”等非法字符；最大不能超过64字节。【例3.1】创建一个名为Bookstore的数据库。CREATE

DATABASE

Bookstore;第2章

数据库设计显示数据库命令显示数据库显示服务器中已建立的数据库，使用SHOW

DATABASES命令。语法格式：SHOW

DATABASES此命令没有用户变量，执行“SHOW

DATABASES；”。注意：在MySQL中，每一条SQL语句都以“;”作为结束标志。第2章

数据库设计打开数据库因为MySQL服务器中有多个数据库，可以使用USE命令可指定当前数据库。语法格式：USE数据库名说明：这个语句也可以用来从一个数据库“跳转”到另一个数据库，在用CREATEDATABASE语句创建了数据库之后，该数据库不会自动成为当前数据库，需要用这条USE语句来指定。【例】将数据库test1设置为当前数据库。Use

test1;第2章

数据库设计MySQL中的字符集和校对规则

字符集是一套符号和编码。校对规则是在字符集内用于比较字符的一套规则。

ASCII字符集中四个字母：‘A’、‘B’、‘a’、‘b’，每个字母赋予一个数值：‘A’=65，‘B’=66，‘a’=97，‘b’=98。字母‘A’是一个符号，数字

65是‘A’的编码，字母和它们的编码组合在一起

是一个字符集。

假设我们希望比较两个字符串的值：‘A’和‘a’。比较的最简单的方法是查找编码：‘A’为65，‘a’为97。因为65小于97，我们可以说‘A’小于‘a’。我们做的仅仅是在我们的字符集上应用了一个校对规则。第2章

数据库设计MySQL字符集和校对规则应用MySQL支持30多种字符集的70多种校对规则。在同一台服务器、同一个数据库或甚至在同一个表中使用不同字符集或校对规则来混合字符串。可用SHOW

CHARACTER

SET命令查看。两个不同的字符集不能有相同的校对规则。每个字符集有一个默认校对规则。【例3.2】创建一个名为Bookstore的数据库，采用字符集gb2312和校对规则gb2312_chinese_ci。CREATE

DATABASE

BookstoreDEFAULT

CHARACTER

SET

gb2312COLLATE

gb2312_chinese_ci;第2章

数据库设计修改数据库数据库创建后，如果需要修改数据库的参数，可以使用ALTER

DATABASE命令。语法格式：ALTER

{DATABASE

|

SCHEMA}[数据库名][[DEFAULT]CHARACTER

SET字符集名|

[DEFAULT]COLLATE校对规则名]【例3.3】修改数据库Pet的默认字符集为latin1，校对规则为latin1_swedish_ci。ALTER

DATABASE

PetDEFAULT

CHARACTER

SET

latin1DEFAULT

COLLATE

latin1_swedish_ci;第2章

数据库设计删除数据库数据库test2命令如下：DROP

DATABASE

test2;

删显示服务器中已建立的数据库，使用SHOWDATABASES命令。语法格式：SHOW

DATABASES；已经创建的数据库需要删除，使用DROP

DATABASE命令。语法格式：DROP

DATABASE

[IF

EXISTS]数据库名第2章

数据库设计数据库表结构分析图书目录表book1表结构分析图书编号类别书名出版时间单价数量tp.4235计算机跟我学网页设计2009-09-1023.5012tp.3361计算机网页编程技术2008-10-0154.1523tp.0731计算机数据库应用技术2007-06-1033.2331字段名字段类型宽度是否为空默认值说明图书编号char10x主键，采用10位字符编号类别char8x计算机2-4个汉字书名varchar40x不超过40个字符出版时间datex单价Float5,2√元为单位，精确到分数量int√第2章

数据库设计MySQL数据类型MySQL常用数据类型：字符串类型数值类型时间类型大数据类型Char(n)intdateblobVarchar(n)Float(M,D)timedatetimetext第2章

数据库设计CREATE

TABLE命令为数据库创建数据表，可以使用SQL命令CREATE

TABLE来完成。此语句的完整语法是相当复杂的，因为存在很多的可选子句创建表使用CREATE

TABLE命令。语法格式：CREATE

TABLE

[IF

NOT

EXISTS]表名（列名数据类型[NOT

NULL

|

NULL][DEFAULT列默认值]…）ENGINE=存储引擎CREATE

TABLE命令的基本格式：CREATE

TABLE

tbl_name（列名1数据类型1

[NOT

NULL

|

NULL],列名2数据类型2

[NOT

NULL

|

NULL],…..）第2章

数据库设计命令行方式创建数据库表命令行方式创建数据库表的最基本格式：CREATE

TABLE数据库表名(字段名1数据类型1

[NOT

NULL

|

NULL],字段名2数据类型2

[NOT

NULL

|

NULL],…..

);命令行方式创建数据库表book1字段名字段类型宽度是否为空默认值说明图书编号char10x主键，采用10位字符编号类别char8x计算机2-4个汉字书名varchar40x不超过40个字符出版时间datex单价Float5,2√元为单位，精确到分数量int√第2章

数据库设计命令方式创建表bookCREATE

TABLE

book(图书编号

char(10)

NOT

NULL,类别varchar(8)NOT

NULL

DEFAULT'计算机',书名

varchar(40)

NOT

NULL

,出版时间dateNOT

NULL

,NULL

,单价

float(5,2)数量

int);图书编号

类别书名出版时间

单价数量第2章

数据库设计显示数据表信息（1）显示数据表文件名语法格式：SHOW

TABLES【例3.11】显示Bookstore数据库建立的数据表文件:USE

BookstoreSHOW

TABLES

;（2）显示数据表结构语法格式：{DESCRIBE

|

DESC}表名[列名|通配符]DESC是DESCRIBE的简写，二者用法相同。【例3.12】用DESCRIBE语句查看Book表的列的信息。USE

BookstoreDESCRIBE

Book

;【例3.13】查看Book表图书编号列的信息。USE

BookstoreDESC

Book图书编号;第2章

数据库设计修改表ALTER

TABLE用于更改原有表的结构。例如，可以增加或删减列，创建或取消索引，更改原有列的类型，重新命名列或表，还可以更改表的评注和表的类型。语法格式：/*添加列*//*修改默认值*//*对列重命名*/ALTER

TABLE表名ADD

[COLUMN]列定义[FIRST

|

AFTER列名]|

ALTER

[COLUMN]列名{SET

DEFAULT默认值|

DROP

DEFAULT}|

CHANGE

[COLUMN]旧列名列定义

[FIRST|AFTER列名]|

MODIFY

[COLUMN]列定义[FIRST

|

AFTER列名]|

DROP

[COLUMN]列名/*修改列类型*//*删除列*/|

RENAME

[TO]

新表名

/*重命名该表*/第2章

数据库设计复制表语法格式：CREATE

TABLE

[IF

NOT

EXISTS]新表名[LIKE参照表名]|

[AS

(select语句)]使用LIKE关键字创建一个与参照表相同结构的新表，但内容不会复制，因此创建的新表是一个空表。使用AS关键字可以复制表的内容，但索引和完整性约束是不会复制的。【例】假设数据库BookStore中有一个表Book，创建Book表的一个名为book_copy1的拷贝。

CREATE

TABLE

book_copy1

LIKE

Book;【例】创建表Book的一个名为book_copy2的拷贝，并且复制其内容。

CREATE

TABLE

book_copy2

AS

(SELECT

*

FROM

Book);第2章

数据库设计修改表结构增加列：ALTER

TABLE表名ADD列定义[FIRST

|

AFTER列名]【例】在表book中增加一列“浏览次数”。

ALTER

TABLE

book

ADD浏览次数int

NULL;删除列：ALTER

TABLE表名DROP列名【扩展】在表book中增加一列“浏览次数”，位置在单价列的后面。ALTER

TABLE

book

ADD浏览次数int

NULL

after单价;【例】删除book表中“浏览次数”列。

ALTER

TABLE

book

DROP浏览次数;【扩展】在表book中增加“浏览次数”列，删除

“封面图片”列。ALTER

TABLE

book

ADD浏览次数int

NULL,DROP封面图片;第2章

数据库设计修改表结构更改列名：ALTER

TABLE

表名

CHANGE

旧列名

列定义【例】将book表中“出版时间”列改为“出版日期”列。

ALTER

TABLE

book

CHANGE出版时间出版日期date

not

null;更改列类型：ALTER

TABLE

表名MODIFY

列定义【例】将book表中“出版日期”列类型改为日期时间类型。

ALTER

TABLE

book

MODIFY出版日期datetime

not

null;第2章

数据库设计修改表结构ALTER

TABLE用于更改原有表的结构。可以增加或删减列，创建或取消索引，更改原有列的类型，重新命名列或表，还可以更改表完整性约束和表的类型等。增加列：ALTER

TABLE

表名 ADD

列定义删除列：ALTER

TABLE表名DROP列名更改列名：ALTER

TABLE表名CHANGE旧列名列定义更改列类型：ALTER

TABLE表名MODIFY列定义第2章

数据库设计删除表需要删除一个表时可以使用DROP

TABLE语句。语法格式：DROP

TABLE

[IF

EXISTS]表名1[,表名2]...这个命令将表的描述、表的完整性约束、索引及和表相关的权限等都全部删除。【例】删除表test。USE

XSCJ

DROP

TABLE

IF

EXISTS

test

;第2章

数据库设计数据完整性输入的类型是否正确？——年龄必须是数字输入的格式是否正确？——Email必须包含@符号是否在允许的范围内？——性别只能是”男”或者”女”是否存在重复输入？——员工信息输入了两次是否符合其他特定要求？——信誉值大于5的客户才能够加入客户表……列值要求（约束）整行要求（约束）第2章

数据库设计数据完整性约束数据完整性：数据类型准确无误数据取值符合规定的范围

多个数据表之间的数据不存在冲突■实体完整性■域完整性■参照完整性■用户定义的完整性第2章

数据库设计实体完整性学号姓名地址……0010012李山山东定陶0010013吴兰湖南新田0010014雷铜江西南昌0010015张丽鹃江南新乡0010016赵可以河南亲乡江西南昌×0010014

雷铜约束方法：唯一约束、主键约束第2章

数据库设计域完整性学号姓名地址……0010012李山山东定陶0010013吴兰湖南新田0010014雷铜江西南昌0010015张丽鹃江南新乡0010016赵可以河南亲乡×8700000000

李亮

湖北江门约束方法：限制数据类型、检查约束、外键约束、默认值、非空约束第2章

数据库设计引用完整性学号姓名地址……科目学号分数……0010012李山山东定陶数学0010012880010013吴兰湖南新田数学0010013740010014雷铜江西南昌语文0010014670010015张丽鹃江南新乡语文0010015810010016赵可以河南亲乡数学001001698×0010021数学98约束方法：外键约束第2章

数据库设计自定义完整性用户编号用户姓名会员证……AV0012孙悟空AV378289AV0013猪悟能AV378290AV0014沙悟净AV378291CV0015玄痛AV372133账号姓名信用

……CV0016乔峰AV12132200192孙悟空7约束方法：存储过程、存储函数、触发器00288猪悟能612333段誉890111虚竹4093000岳不群-10×约束方法：存储过程、存储函数、触发器第2章

数据库设计约束与完整性之间的关系完整性类型约束类型描述约束对象列完整性Default当使用INSERT语句插入数据时，若已定义默认值的列没有提供指定值，则将该默认值插入记录中列Check指定某一列可接受的值实体完整性Primary

KeyUnique每行记录的惟一标识符，确保用户不能输入重复值，并自动创建索引，提高性能，该列不允许使用空值在列集内强制执行值的惟一性，防止出现重复值，表中不允许有两行的同一列包含相同的非空值行参考完整性Foreign

Key定义一列或几列，其值于本表或其他表的主键或UNIQUE列相匹配表与表之间第2章

数据库设计主键约束主键就是表中的一列或多个列的一组，其值能唯一地标志表中的每一行。通过定义PRIMARY

KEY约束来创建主键，而且PRIMARY

KEY约束中的列不能取空值。由于

PRIMARY

KEY约束能确保数据的唯一，所以经常用来定义标志列。如果PRIMARY

KEY约束是由多列组合定义的，则某一列的值可以重复，但PRIMARY

KEY约束定义中所有列的组合值必须唯一。可以用两种方式定义主键：作为列或表的完整性约束。作为列的完整性约束时，只需在列定义的时候加上关键字PRIMARY

KEY。作为表的完整性约束时，需要在语句最后加上一条

PRIMARY

KEY（col_name，…）语句。第2章

数据库设计主键约束（1）列的完整性约束：【例】创建表abook2，将编号定义为主键。CREATE

TABLE

book2(编号char(6)NOT

NULL

PRIMARY

KEY,书名char(10)NOT

NULL,数量int);（2）表的完整性约束：【例】创建cj表，将学号和课程号定义为复合主键。CREATE

TABLE

cj(学号char(6)

NOT

NULL,课程号char(3)

NOT

NULL,成绩

int

,PRIMARY

KEY(学号,课程号));第2章

数据库设计主键约束原则原则上，任何列或者列的组合都可以充当一个主键。但是主键列必须遵守一些规则。每个表只能定义一个主键。关系模型理论要求必须为每个表定义一个主键。唯一性原则：表中的两个不同的行在主键上不能具有相同的值。最小化规则：如果从一个复合主键中删除一列后，剩下的列构成主键仍然满足唯一性原则，那么，这个复合主键是不正确的，这条规则称为最小化规则。MySQL自动地为主键创建一个索引。通常，这个索引名为PRIMARY。然而，可以重新给这个索引起名。第2章

数据库设计替代键约束在关系模型中，替代键像主键一样，是表的一列或一组列，它们的值在任何时候都是唯一的。替代键是没有被选做主键的候选键。定义替代键的关键字是UNIQUE。【例】在表book_copy1中将图书编号作为主键，书名列定义为一个替代键。CREATE

TABLE

book_copy1(图书编号varchar(20)

NOT

NULL

primary

key,书名

varchar(20)

NOT

NULL

UNIQUE,出版日期date

NULL

unique);第2章

数据库设计ALTER

TABLE语句添加约束ADD

PRIMARY

KEY(列名,...)/*添加主键*/|

ADD

UNIQUE[索引名](列名,...)/*添加替代键约束*/|

DROP

PRIMARY

KEY/*删除主键*/|

DROP

INDEX索引名/*删除索引*/使用ALTER

TABLE语句使用ALTER

TABLE语句修改表，其中也包括向表中添加约束。语法格式如下：ALTER

TABLE表名第2章

数据库设计ALTER

TABLE举例【例】假设Book表中主键未设定，为Book表建立以图书编号为主键约束,以书名为唯一性约束。ALTER

TABLE

Book

ADD

PRIMARY

KEY(图书编号),ADD

UNIQUE

u_idx(书名);这个例子中，既包括主键约束,也包括唯一性约束，说明MySQL可以同时创建多个约束。记住，使用PRIMARY

KEY的列，必须是一个具有NOT

NULL属性的列。如果想要查看表中创建的约束的情况，可以使用SHOW

INDEX

FROM

tbl_name语句，例如：

SHOW

INDEX

FROM

book;【例】删除Book表上的主键和替代键约束。ALTER

TABLE

Book

DROP

PRIMARY

KEY,

DROP

INDEX

u_idx

;第2章

数据库设计替代键和主键的区别在MySQL中替代键和主键的区别主要有以下几点。02主键字段的值不允许为NULL，而UNIQUE字段的值可取NULL，但是必须使用NULL或NOTNULL声明。03一般创建PRIMARY

KEY约束时，系统会自动产生PRIMARY

KEY索引。创建UNIQUE约束时，系统自动产生UNIQUE索引。01一个数据表只能创建一个主键。但一个表可以有若干个UNIQUE键，并且它们甚至可以重合，例如，在C1和C2列上定义了一个替代键，并且在C2和C3上定义了另一个替代键，这两个替代键在C2列上重合了，而MySQL允许这样。第2章

数据库设计引用完整性约束方法：外键约束科目学号分数…数学001001288数学001001374语文001001267语文001001381数学001001698学号姓名地址…0010012李山山东定陶0010013吴兰湖南新田0010014雷铜江西南昌0010015张丽鹃河南新乡0010016河南新乡

×

数学

0010021

98主表（父表）参照表（子表）第2章

数据库设计参照完整性约束举例1、创建表的同时创建外键语法格式：CREATE

TABLE表名(列名,...）

|[外键定义]2、对已有表创建外键语法格式：ALTER

TABLE表名ADD[外键定义][外键定义]语法格式：FOREIGN

KEY(列名)REFERENCES表名[(列名[(长度)][ASC

|

DESC],...)][ON

DELETE

{RESTRICT

|

CASCADE

|

SET

NULL

|

NO

ACTION}][ON

UPDATE

{RESTRICT

|

CASCADE

|

SET

NULL

|

NO

ACTION}]第2章

数据库设计参照完整性约束说明Xs表（父表）学号姓名…081102程明081103王燕…….….学号姓名成绩081102程明80081103王燕90….Cj表（子表）RESTRICT：当要删除或更新父表中被参照列上在外键中出现的值时，拒绝对父表的删除或更新操作。如要当要删除xs表中081102记录时，因为cj中还有081102记录，拒绝对xs表的删除操作。CASCADE：从父表删除或更新行时自动删除或更新子表中匹配的行。如从xs表更新081102学号为

801102时自动更新cj表中学号为081102行为801102。第2章

数据库设计参照完整性约束说明Xs表（父表）学号姓名…081102程明081103王燕…….….学号姓名成绩081102程明80081103王燕90….Cj表（子表）SET

NULL：当从父表删除或更新行时，设置子表中与之对应的外键列为NULL。当从xs表删除081103行时，设置cj表中081103项为NULL。NO

ACTION：NO

ACTION意味着不采取动作，就是如果有一个相关的外键值在被参考的表里，删除或更新父表中主要键值的企图不被允许，和RESTRICT一样。SET

DEFAULT：作用和SET

NULL一样，只不过SET

DEFAULT是指定子表中的外键列为默认值。如果没有指定动作，两个参照动作就会默认地使用RESTRICT。第2章

数据库设计参照完整性约束举例CREATE

TABLE

book_ref(图书编号varchar(20)

NULL,书名varchar(20)

NOT

NULL,出版日期date

NULL,PRIMARY

KEY(书名),

FOREIGN

KEY(图书编号)REFERENCES

Book(图书编号)

ON

DELETE

RESTRICTON

UPDATE

RESTRICT)

ENGINE=INNODB;【例】创建book_ref表，所有的book_ref表中图书编号都必须出现在Book表中，假设已经使用图书编号列作为Book表主键。第2章

数据库设计参照完整性约束举例CREATE

TABLE

book_ref1

(图书编号

varchar(20)

NULL,书名

varchar(20)

NOT

NULL,出版日期

date

NULL,PRIMARY

KEY(书名),

FOREIGN

KEY(图书编号)REFERENCES

Book(图书编号)

ON

UPDATE

CASCADE)ENGINE=INNODB;这个参照动作的作用是在主表更新时，子表产生连锁更新动作，有些人称它为“级联”操作。举例说，如果book表中有一个图书编号为“TP.2525”的值修改为“TP.2525-1”，则book_ref1表中的“图书编号”列中的值“TP.2525”相应地改为“TP.2525-1”。【例】创建带有参照动作CASCADE的book_ref1表。第2章

数据库设计参照完整性约束举例ALTER

TABLE

sellADD

FOREIGN

KEY(用户号)REFERENCES

members(用户号)ON

DELETE

CASCADEON

UPDATE

CASCADE;【例】在网络图书销售系统中，只有会员才能下订单。因此Sell表中的所有用户号也必须出现在Members表的用户号列中。定义参照完整性约束来实现这种约束。第2章

数据库设计CHECK约束主键、替代键、外键都是常见的完整性约束的例子。但是，每个数据库都还有一些专用的完整性约束。例如，KC表中星期数要在1～7之间，XS表中出生日期必须大于1986年1月1日。

这样的规则可以使用CHECK完整性约束来指定。CHECK完整性约束在创建表的时候定义。可以定义为列完整性约束，也可以定义为表完整性约束。语法格式为：CHECK(expr)说明：expr是一个表达式，指定需要检查的条件，在更新表数据的时候，MySQL会检查更新后的数据行是否满足CHECK的条件。第2章

数据库设计CHECK约束举例【例】创建表student，只考虑学号和性别两列，性别只能包含男或女。CREATE

TABLE

student

(学号char(6)NOT

NULL,性别char(2)NOT

NULL

CHECK(性别IN('男','女')));这里CHECK完整性约束指定了性别允许哪个值，由于CHECK