MySQL数据库基础实例教程整本书课件完整版电子教案全套课件最全教学教程ppt(最新)

1、第一章数据库基础知识项目知识要点与目标项目知识要点知识能力目标学时任务1 了解数据库的基础知识1 数据与数据库2 数据库技术的发展3 数据库管理系统（DBMS）4 数据库系统的组成5 结构化查询语言SQL6 常见的关系型数据库管理系统任务2 安装与配置MySQL数据库1 MySQL服务器安装与配置2 连接与断开服务器【典型工作任务】安装与配置MySQL数据库【技能要求】：1 MySQL的安装和配置2 MySQL运行环境测试【知识要求】：1 数据库管理系统组成2 服务器连接、启动和运行2项目实训安装MySQL数据库2数据库基础知识数据狭义：数值数据广义：文字、声音、图形等一切能被计算机接收且能被

2、处理的符号大量、海量数据库存储大量数据，方便检索和访问保持数据信息的一致、完整共享和安全通过组合分析，产生新的有用信息特点数据库管理系统（软件）管理大量的、持久的、可靠的、共享的数据的工具数据管理技术的发展阶段人工管理阶段-文件系统阶段 - 数据库管理数据管理技术的发展阶段-没有专用的软件对数据进行管理。-数据的组织方式必须由程序员自行设计与安排。-只有程序的概念而没有文件的概念、数据面向应用， 也就是一组数据只能对应一个程序。数据管理技术的发展阶段文件系统的三个缺陷：数据冗余性、数据不一致性、数据联系弱。数据管理技术的发展阶段数据库 订单 客户 产品 产品数据库表数据库就是“数据”的“仓库”

3、数据库由表、关系以及操作对象组成数据存放在表中数 据 表存储过程视 图. 所谓数据库(Database, DB)，是将数据按一定的数据模型组织、描述和存储，具有较小的冗余度，较高的数据独立性和易扩展性，并可为各种用户共享的数据集合。列行(记录)关系型数据库关系型数据库关系模型使用的存储结构是多个二维表格，表中每一行称为一条记录，用来描述一个对象的信息；每一列称为一个字段，用来描述对象的一个属性。数据表于数据库之间存在相应的关联，这些关联将用来查询相关的数据。数据库管理系统马兰A004杨可A003刘开泰B008李新爱B007数据库检索数据插入数据更新数据删除数据数据库管理系统-DBMS 数据库管

4、理系统（DBMS-DataBase Management System ）对收集到的大量数据进行整理、加工、归并、分类、计算、存储等处理，产生新的数据，以便反映事物或现象的本质和特征及其内在联系。数据库系统的基本概念 数据库服务器响应和提供数据应用程序客户端操作和查询数 据 库应用程序作用：响应操作并显示结果、向数据库请求数据要求：美观、操作简单方便数据库作用：存储数据、检索数据、生成新的数据要求：统一、安全、性能等数据库应用系统数据库系统数据库系统(Database System, DBS)一般由数据库、数据库管理系统(及其开发工具)、应用系统、数据库管理员和用户结构组成结构化查询语言SQL

5、2结构化查询语言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）综合统一 （2）高度非过程化（3

6、）面向集合的操作方式（4）以同一种语法结构提供两种使用方式（5）语言简洁，易学易用大数据时代的数据库管理系统SQL-关系型数据库管理系统（RDBMS）不同的角色（开发者，用户，数据库管理员）使用相同的语言。不同的RDBMS使用统一标准的语言。SQL使用一种高级的非结构化查询语言。坚持ACID准则(原子性，一致性，隔离性，持久性),NoSQL-非关系性数据库采用Key-value方式存储数据。采取最终一致性原则，更加适合互联网数据，但这也可能导致数据丢失。NewSQL-结合SQL和NoSQL 将SQL的ACID保证与NoSQL的可扩展性和高性能相结合，目前大多数NewSQL数据库都是专有软件或仅

7、适用于特定场景，这显然限制了新技术的普及和应用。 数据库产品分类图数据库流行排行榜Oracle、MySQL、SQL Server排前三位，其流行度远远超过其他数据库。在非关系型数据库中，比较流行的有MongoDB，Elasticsearch，Redis等。时下流行的DBMS简介OracleOracle公司的产品，世界上最好的数据库系统“关系-对象”型数据库支持70多种操作系统，配置、管理和维护复杂主要满足对银行、金融、保险等企业、事业开发大型数据库需求MySQL瑞典MySQLAB公司开发， 现在MySQL并入了Oracle旗下体积小、速度快、成本低、开放源码 广泛地应用在Internet上的中

8、小型网站中SQL ServerMicrosoft公司的产品，针对不同用户群体的多个版本要求在Windows操作系统平台上运行易用性好1.- 下载MySQL社区版任务二 MySQL的安装与配置 2. MySQL社区版是免费的，有MSI和zip两种安装方式下载前先选择安装平台（windows）和安装文件方式（MSI或Zip)MySQL的安装3.安装向导-产品类型选择：Server only（服务器）MySQL的安装4.配置向导-类型和网络配置：Server ComputerMySQL的安装5.配置向导-账号和角色配置：为MySQL的超级用户root设置密码MySQL的安装连接服务器“开始”“程序”

9、MySQL“MySQL Server 8.0” “MySQL 8.0 Command Line Client”在客户端窗口输入安装时为root用户设置的密码窗口中出现MySQL命令行提示符“mysql“，安装成功MySQL图形化管理工具 Navicat for MySQL是一个桌面版MySQL数据库管理和开发工具，易学易用，很受大家的欢迎。本课程将以Navicat for MySQL为例介绍MySQL数据库管理工具的使用。第二章数据库设计项目知识要点与目标项目知识要点知识能力目标学时任务1 设计数据库关系模型1 数据模型2 概念模型3 E-R图的设计4 数据库关系模型的建立任务2 数据库设计规

10、范化1 关系型数据库范式理论 2 数据库规范化实例 3 综合实例 PetStore数据库的设计 【典型工作任务】 对数据库系统进行分析，建立E-R图，关系模型【技能要求】 1 设计数据库关系模型2 数据库规范化【知识要求技能训练】1 掌握数据库设计的相关知识2 运用E-R图等工具设计数据库 4项目实训创建“图书管理系统”的数据模型2修建茅屋需要设计吗？修建大厦需要设计吗？结论：当数据库比较复杂时我们需要设计数据库为什么需要设计数据库 数据库设计就是将数据库中的数据对象以及这些数据对象之间关系进行规划和结构化的过程任务一 设计数据库关系模型 设计数据库关系模型概念模型:把现实世界转换为信息世界的

11、模型,E-R模型实施模型:把信息世界转化为数据世界使用的模型,关系模型现实世界建模信息世界模型转换 规范化数据库世界数 据 库数 据 库事物类事物性质实体集实体属性文件记录数据项实体模型概念模型数据模型实体模型 现实世界客观存在的、可以被描述事物都是“实体”层次模型 按层次模型组织的数据示例 网状模型 按网状模型组织的数据示例 关系模型 按关系模型组织的数据示例 学号总学分专业名出生日期性别姓名学生课程课程号学分学时开课学期类别课程名选课成绩实体名联系名属性名属性名或E-R图的组成要素及其画法（5）映射基数 1:1,1:n,m:n（1）实体（2）联系（3）属性（4）关键字mn概念模型-E-R图

12、建模：把现实世界转换为信息世界的模型,E-R模型实体间不同联系情况的E-R图表示法一对一(1:1)联系：1院长学院管理1姓名年龄学历编号学院编号学院名称任职时间例：某学校有若干个学院，每个学院只有一个院长。院长和学院之间是一对一的关系。主任与系之间的一对一的联系一对多（1：m）的联系例： 在某仓库管理系统中，有两个实体集：仓库和商品。仓库用来存放商品，且规定一类商品只能存放在一个仓库中，一个仓库可以存放多件商品。仓库和商品是一对多（1：m）的联系，在存放联系中要反映出存放商品的数量。 1仓库商品存放n地点面积仓库号商品号价格数量商品名实体间不同联系情况的E-R图表示法仓库和商品之间一对多的联系

13、多对多（m:n)联系：假设在某教务管理系统中，一个教师可以上多门课，一门课也可以由多个老师去上。教师和课程之间是多对多（m:n)联系，在“讲授”联系中应能反映出教师的授课质量。 m教师课程讲授n教师名职称教师号课程号班级质量课程名实体间不同联系情况的E-R图表示法教师和课程之间的多对多联系 E-R图设计实例【例】 网络图书销售系统处理会员图书销售。简化的业务处理过程为：网络销售的图书信息包括：图书编号，图书类别，书名，作者，出版社，出版时间，单价，数量，折扣，封面图片等；用户需要购买图书必须先注册为会员，提供身份证号，会员姓名，密码，性别，联系电话，注册时间等信息；系统根据会员的购买订单形成销

14、售信息，包括订单号，身份证号，图书编号，订购册数，订购时间，是否发货，是否收货，是否结清。 请画出网络图书销售数据库E-R图。 E-R图设计步骤1.确定实体集： 网络图书销售系统中有两个实体集：图书和会员2.确定实体集属性及主码： （1）实体集会员属性有：身份证号，会员姓名，性别，联系电话，注册时间，密码。 会员实体集中可用身份证号来唯一标识各会员，所以主码为身份证号。（2）实体集图书属性有：图书编号，图书类别，书名，作者，出版社，出版时间，单价，数量，折扣，封面图片。 图书实体集中可用图书编号来唯一标识图书，所以主码为图书编号。3. 确定实体集之间的联系：图书销售给会员时图书与会员建立关联，

15、 联系“销售”的属性有：订购册数，订购时间，是否发货，是否收货，是否结清。 为了更方便标识销售记录，可添加订单号作为该联系的主码。4.确定联系关系： 因为一个会员可以购买多种图书，一种图书可销售各多个会员，所以这是一种多对多（m:n）的联系。 根据以上分析画出的网络图书销售数据库E-R图网络图书销售数据库E-R图E-R图设计步骤 对于复杂的系统，E-R图设计通常都应经过以下两个阶段： （1）针对每一用户画出该用户信息的局部ER图，确定该用户视图的实体、属性和联系。需注意的是：能作为属性的就不要作为实体，这有利于ER图的简化。 （2）综合局部ER图，生成总体ER图。在综合过程中，同名实体只能出现

16、一次，还要去掉不必要的联系，以便消除冗余。一般来说，从总体ER图必须能导出原来的所有局部视图，包括实体、属性和联系。复杂E-R图设计实例 【例】 工厂物流管理中的涉及雇员、部门、供应商、原材料、成品和仓库等实体，并且存在以下关联： （1）一个雇员只能在一个部门工作，一个部门可以有多个雇员。（2）每一个部门可以生产多种成品，但一种成品只能由一个部门生产。（3）一个供应商可以供应多种原材料，一种原材料也可以由多个供应商供货。（4）购买的原材料放在仓库中，成品也放在仓库中。一个仓库可以存放多种产品，一种产品也可以存放在不同的仓库中。（5）各部门从仓库中提取原料，并将成品放在仓库中。一个仓库可以存放多

17、个部门的产品，一个部门的产品也可以存放在不同的仓库中。 画出简单的工厂物流管理系统E-R模型。局部E-R图1、找出工厂物流管理系统实体集：分别是雇员、部门、成品、供应商、原材料和仓库。2.从生产的角度，根据（1）和（2）画出雇员、部门和成品三个实体间的初步联系3.从供应的角度，根据（3）画出供应商和原材料两个实体间的初步联系，4.为是问题简化，实体的属性先不画出。全局E-R图5.从仓储的角度，根据（4）和（5）画出仓库与各实体之间的联系，最终得到工厂物流管理系统的E-R图6.实体联系中有多个数量属性，分别用数量1，数量2以区分课堂练习学校有若干个学院，每个学院有各自的编号、名称和院长；每个学院

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

19、号、名称和负责人；学生有学号、姓名、年龄、性别，每个学生可以同时选修多门课程，选修有分数。实体集：学院、教师、学生、课程、项目E-R图设计中常见问题分析【例】 画出出版社和图书的E-R图。 1.实体与联系分析：一个出版社可以出版多本图书，一本图书只能由一个出版社出版，出版社和图书之间就是一对多的关系。 2.属性与主码：出版社实体有社名、地址、邮编、网址、联系电话等属性。为了建立出版社与图书实体一对多的联系，还应该有一个出版社代码来唯一标识出版社； 图书实体有出版社、书名、作者、价格等属性。为了唯一标识图书，还应设置书号属性。 根据以上分析，画出出版社与图书的E-R图如下：图书出版E-R图问题思

20、考（1）1、怎么标识书号-属性内容如何选取？为了管理方便，国际上规定：全世界的每本书都应该有唯一的编号，这个号码叫做ISBN号，俗称书号。因此，用ISBN书号作为图书的唯一标识似乎是非常合理的。但是，有时候出版社也出版一些没有书号的资料。所以，在设计实际数据库系统的时候，可也以根据实际情况不以ISBN书号唯一标识图书实体，而自己定义唯一标识图书实体的属性。在现实世界中，类似这样的问题有很多，通常需要为实体集定义额外的关键字段。图书出版E-R图问题思考（2）2、有些属性的值如果有多个，该怎么办？如出版社实体应该有电话属性。但一个出版社一般不止一部电话，怎么处理？一种方法是仍使用一个电话属性，只记

21、下一部或几部甚至全部的电话号码即可，这种方法适合于小单位。第二种方法是将电话属性独立出来，建立一个新的电话实体，通过出版社代码属性，建立和出版社的一对多联系，图书出版E-R图问题思考（3）3、一个实体究竟有多少属性？实体的属性可以说是无穷无尽，到底应提取哪些属性，要结合具体应用系统考虑。比如，图书的一般属性有书号、社代码、书名、作者、价格、版次等，如果开发的是书店管理系统，这些属性一般够用了，但如果要开发印刷厂管理系统，还需要增加图书大小（32开还是16开，或具体数字）、印刷纸张规格（60克纸还是70克纸，书写纸还是双面胶）、是否彩印、彩印规格、印刷数量、交货日期等属性。所以，提取一个实体的属

22、性也要具体问题具体分析。 通过出版社图书的例子引出的三个问题，说明建立在现实世界基础上的E-R模型并不只有唯一答案。面向不同的应用、使用不同的方法，可以设计出不同的E-R模型。ER模型到关系模型的转换 把E-R图转换为关系模型可遵循如下原则 : （1）对于ER图中每个实体集，都应转换为一个关系，该关系应包括对应实体的全部属性，并应根据关系所表达的语义确定哪个属性或哪几个属性组作为“主关键字”，主关键字用来标识实体。 （2）对于ER图中的联系，情况比较复杂，要根据实体联系方式的不同，采取不同的手段加以实现。（1 : 1）联系的E-R图转换（1）联系单独对应一关系模式，则由联系属性、参与联系的各实

23、体集的主码属性构成关系模式，其主码可选参与联系的实体集的任一方的主码。BJ（班级编号，院系，专业名，人数）BZ（学号，姓名）SY（学号，班级编号）（2）联系不单独对应一关系模式，联系的属性及一方的主码加入另一方实体集对应的关系模式中。 BJ（班级编号，院系，专业名，人数） BZ（学号，姓名，班级编号）或者 BJ（班级编号，院系，专业名，人数，学号） BZ（学号，姓名）（1 : n）联系的E-R图转换（1）联系单独对应一关系模式，则由联系的属性、参与联系的各实体集的主码属性构成关系模式，n端的主码作为该关系模式的主码。BJ（班级编号，院系，专业名，人数）XS (学号，姓名，专业名，性别，出生时间

24、，总学分，备注)SY（学号，班级编号）（2）联系不单独对应一个关系模式，则将联系的属性及1端的主码加入n端实体集对应的关系模式中，主码仍为n端的主码 BJ（班级编号，院系，专业名，人数） XS (学号，姓名，专业名，性别，出生时间，总学分，备注，班级编号)（m : n）联系的E-R图转换 对于（m : n）的联系，单独对应一关系模式，该关系模式包括联系的属性、参与联系的各实体集的主码属性，该关系模式的主码由各实体集的主码属性共同组成。XS (学号，姓名，专业名，性别，出生时间，总学分，备注)KC（课程号，课程名称，类别，开课学期，学时，学分）XS_KC (学号，课程号，成绩)假设A实体集与B实

25、体集是1:1的联系，联系的转换有三种方法： 把A实体集的主关键字加入到B实体集对应的关系中，如果联系有属性也一并加入； 把B实体集的主关键字加入到A实体集对应的关系中，如果联系有属性也一并加入； 建立第三个关系，关系中包含两个实体集的主关键字，如果联系有属性也一并加入。 两实体集间1:n联系 两实体集间1:n联系，可将“一方”实体的主关键字纳入“n方”实体集对应的关系中作为“外部关键字”，同时把联系的属性也一并纳入“n方”对应的关系中。两实体集间m:n联系 对于两实体集间m:n联系，必须对“联系”单独建立一个关系，用来联系双方实体集。该关系的属性中至少要包括被它所联系的双方实体集的“主关键字”

26、，并且如果联系有属性，也要归入这个关系中。 E-R模型到关系模型的转换总结数据库设计规范化仅有好的RDBMS并不足以避免数据冗余，必须在数据库的设计中创建好的表结构关系数据库范式理论是在数据库设计过程中将要依据的准则，数据库结构必须要满足这些准则，才能确保数据的准确性和可靠性。这些准则则被称为规范化形式，即范式。Dr E.F.codd 最初定义了规范化的三个级别，范式是具有最小冗余的表结构。这些范式是：第一范式(1st NF First Normal Fromate)第二范式(2nd NFSecond Normal Fromate)第三范式(3rd NF Third Normal Fromat

27、e)第一范式 (1st NF)BuyerIDCountryCity1342中国英国日本美国北京伦敦东京纽约BuyerIDAddress1234中国北京市 美国纽约市 英国伦敦日本东京市 第一范式的目标是确保每列的原子性如果每列都是不可再分的最小数据单元（也称为最小的原子单元），则满足第一范式（1NF）第二范式 (2nd NF)如果一个关系满足1NF，并且除了主键以外的其他列，都依赖与该主键，则满足第二范式（2NF） 第二范式要求每个表只描述一件事情Orders字段例子订单编号产品编号订购日期产品名称001A0012000-2-3洗衣机规格 Orders字段例子订单编号订购日期0012000-2

28、-3Products字段例子产品编号产品名称A001洗衣机规格第三范式 (3rd NF)如果一个关系满足2NF，并且除了主键以外的其他列都不传递依赖于主键列，则满足第三范式（3NF） Orders字段例子订单编号产品编号单价001AB00130数量50金额 Orders字段例子订单编号产品编号单价001AB00130数量 50一张表：6*10*5000=30，0000 个数据三张表：学生表：3*5000=1500 0 课程表：2*10=20 成绩表：3*10*5000=150000三张表合计：15000+20+150000=16，5020A与B方案数据差:30,0000-16,5020 =13

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

30、02计算机基础85010101英语75010102英语85A同学设计表格B同学设计表格课堂练习分析规范化实例分析（1）假设某建筑公司要设计一个数据库。公司的业务规则概括说明如下：公司承担多个工程项目，每一项工程有：工程号、工程名称、施工人员等公司有多名职工，每一名职工有：职工号、姓名、性别、职务（工程师、技术员）等公司按照工时和小时工资率支付工资，小时工资率由职工的职务决定（例如，技术员的小时工资率与工程师不同）公司定期制定一个工资报表，如图-1所示规范化实例分析（2）工程号工程名称职工号姓名职务小时工资率工时实发工资A1花园大厦1001齐光明工程师6513845.001002李思岐技术员60

31、16960.001004葛宇宏律师60191140.00小计2945.00A2立交桥1001齐光明工程师6515975.001003鞠明亮工人5517935.00小计1910.00A3临江饭店1002李思岐技术员60181080.001004葛宇洪技术员6014840.00小计1920.00图-1 某公司的工资表规范化实例分析（3）工程号工程名称职工号姓名职务小时工资率工时A1花园大厦1001齐光明工程师6513A1花园大厦1002李思岐技术员6016A2立交桥1001齐光明工程师6515A2立交桥1003鞠明亮工人5517A3临江饭店1002李思岐技术员6018A3临江饭店1004葛宇洪技术

32、员6014图-2 某公司的项目工时表规范化实例分析（4）表中包含大量的冗余，可能会导致数据异常：更新异常 例如，修改职工号=1001的职务，则必须修改所有职工号=1001的行添加异常 若要增加一个新的职工时，首先必须给这名职工分配一个工程。或者为了添加一名新职工的数据，先给这名职工分配一个虚拟的工程。（因为主关键字不能为空）删除异常 例如，1001号职工要辞职，则必须删除所有职工号1001的数据行。这样的删除操作，很可能丢失了其它有用的数据 采用这种方法设计表的结构，虽然很容易产生工资报表，但是每当一名职工分配一个工程时，都要重复输入大量的数据。这种重复的输入操作，很可能导致数据的不一致性。一

33、张表描述了多件事情，如图-3所示。规范化实例分析（5）工程号工程名称职工号姓名职务小时工资率工时图-3 函数依赖图工程信息员工信息项目工时信息应用第二范式规范化工程号工程名称职工号姓名职务小时工资率工程号职工号工时图-4 应用第二范式工程表员工表项目工时表满足第三范式吗？ 应用第三范式规范化工程号工程名称职工号姓名职务职务小时工资率工程号职工号工时工程表员工表职务表工程表课堂练习S公司商品销售管理系统中员工表如下：Employees （员工号，姓名，性别，出生年月，聘任日期，工资，奖金，所在部门和部门主管） 其中奖金按工资*30%+工作年限*50计算而得。请对员工表进行规范化。规范化和性能的关

34、系 为满足某种商业目标，数据库性能比规范化数据库更重要通过在给定的表中添加额外的字段，以大量减少需要从中搜索信息所需的时间通过在给定的表中插入计算列（如成绩总分），以方便查询进行规范化的同时，还需要综合考虑数据库的性能。总结在需求分析阶段，设计数据库的一般步骤为：收集信息标识对象标识每个对象的属性标识对象之间的关系在概要设计阶段和详细设计阶段，设计数据库的步骤为：绘制E-R图将E-R图转换为表格应用三大范式规范化表格为了设计结构良好的数据库，需要遵守一些专门的规则，称为数据库的设计范式。第一范式（1NF）的目标：确保每列的原子性。第二范式（2NF）的目标：确保表中的每列，都和主键相关 。第三范

35、式（3NF）的目标：确保每列都和主键列直接相关，而不是间接相关 。第三章数据定义项目知识要点知识能力目标学时任务1 创建与管理数据库1 创建数据库2 管理数据库任务2 创建与管理数据库表1 创建数据库表2 管理数据库表任务3 数据完整性约束 1 主键约束 2 替代键约束 3 参照完整性约束 4 CHECK完整性约束 【典型工作任务】： 学会创建与管理数据库和表【技能要求】：1 掌握创建与管理数据库的方法2 掌握创建与管理数据库表的方法3 掌握建立数据完整性约束的方法【知识要求】：1 数据库和数据库表的结构2 数据完整性约束3 数据库及表操作的SQL语句4项目实训创建“图书管理系统”数据库4项目

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

37、,书名,作者,出版社,出版时间,单价,数量,折扣,封面图片) 2、members(用户号,姓名,性别,密码,联系电话,注册时间)3、sell (订单号,用户号 ,图书编号,订购册数,订购单价,订购时间,是否发货,是否收货,是否结清)网络图书销售数据库E-R图1.book表2. members表3. sell表MySQL数据库模型在数据库服务器中可以存储多个数据库文件，所以建立数据库时要设定数据库的文件名，每个数据库有惟一的数据库文件名作为与其它数据库区别的标识。数据库文件： 数据库是由相关数据表组成，一个数据库包括多个数据表。数据库文件用于记录数据库中数据表构成的信息。数据库只能由得到授权的用

38、户访问，这样保证了数据库的安全。成绩数据库xscj网络图书库bookstore雇员管理库YGGL.库MySQL服务器数据库数据库和表数据库可以看成是一个存储数据对象的容器，这些数据对象包括表、视图、触发器、存储过程等，其中，表是最基本的数据对象，用以存放数据库的数据。必须首先创建数据库，然后才能创建数据库的数据对象。 MySQL可以采用两种方式创建、操作数据库和数据对象（1）命令行方式（2）图形界面工具方式数据表视图存储过程DELIMITER $CREATE PROCEDURE DELETE_STUDENT(IN XH CHAR(6)BEGIN创建数据库 MySQL安装后，系统自动地创建inf

39、ormation_scema和MySQL数据库，MySQL把有关数据库的信息存储在这两个数据库中。如果删除了这些数据库，MySQL就不能正常工作。 对于用户的数据，需要创建新的数据库来存放。 语法格式： CREATE DATABASE | SCHEMA IF NOT EXISTS 数据库名 DEFAULT CHARACTER SET 字符集名 | DEFAULT COLLATE 校对规则名说明：语句中“ ”内为可选项。 | 表示二选一。Create database bookstore创建数据库 命令行方式创建数据库 在SQL语言创建数据库命令create database中，如果省略语句中“

40、 ”中的所有可选项，其结构形式如下： CREATE DATABASE 数据库名;数据库名 表示被创建数据库名，数据库名必须符合以下规则： 数据库名必须唯一；名称内不能含有“/”及“.”等非法字符；最大不能超过64字节。【例3.1】 创建一个名为Bookstore的数据库。 CREATE DATABASE Bookstore; 显示数据库显示服务器中已建立的数据库，使用SHOW DATABASES命令。语法格式： SHOW DATABASES 此命令没有用户变量，执行“SHOW DATABASES；” 。注意：在MySQL中，每一条SQL语句都以“;”作为结束标志。显示数据库命令因为MySQL服

41、务器中有多个数据库，可以使用USE命令可指定当前数据库。 语法格式： USE 数据库名说明：这个语句也可以用来从一个数据库“跳转”到另一个数据库，在用CREATE DATABASE语句创建了数据库之后，该数据库不会自动成为当前数据库，需要用这条USE语句来指定。【例】将数据库test1设置为当前数据库。 Use test1;打开数据库MySQL中的字符集和校对规则字符集是一套符号和编码。校对规则是在字符集内用于比较字符的一套规则。ASCII字符集中四个字母：A、B、a、b，每个字母赋予一个数值：A=65，B= 66，a= 97，b= 98。字母A是一个符号，数字65是A的编码，字母和它们的编码

42、组合在一起是一个字符集。假设我们希望比较两个字符串的值：A和a。比较的最简单的方法是查找编码：A为65，a为97。因为65 小于97，我们可以说A小于a。我们做的仅仅是在我们的字符集上应用了一个校对规则。MySQL字符集和校对规则应用 MySQL支持30多种字符集的70多种校对规则。在同一台服务器、同一个数据库或甚至在同一个表中使用不同字符集或校对规则来混合字符串。 可用SHOW CHARACTER SET命令查看。两个不同的字符集不能有相同的校对规则。每个字符集有一个默认校对规则。【例3.2】 创建一个名为Bookstore的数据库，采用字符集gb2312和校对规则gb2312_chines

43、e_ci。CREATE DATABASE Bookstore DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci; 修改数据库数据库创建后，如果需要修改数据库的参数，可以使用ALTER DATABASE命令。 语法格式： ALTER DATABASE | SCHEMA 数据库名 DEFAULT CHARACTER SET 字符集名 | DEFAULT COLLATE 校对规则名 【例3.3】 修改数据库Pet的默认字符集为latin1，校对规则为latin1_swedish_ci。ALTER DATABASE PetDEFAULT CH

44、ARACTER SET latin1DEFAULT COLLATE latin1_swedish_ci; 删除数据库已经创建的数据库需要删除，使用DROP DATABASE命令。 语法格式： DROP DATABASE IF EXISTS 数据库名数据库test2命令如下： DROP DATABASE test2;删显示服务器中已建立的数据库， 使用SHOW DATABASES命令。 语法格式： SHOW DATABASES；数据库表结构分析 book1表结构分析图书编号类别书名出版时间单价数量SC13/02机械机械制图2016-10-1645.5045TP10/04计算机PHP_MySQL网

45、站制作2013-08-1633.2550TP21/01计算机JavaScript网站制作2015-08-1633.7260图书目录表图书编号类别书名出版时间单价数量tp.4235计算机跟我学网页设计2009-09-1023.5012tp.3361计算机网页编程技术2008-10-0154.1523tp.0731计算机数据库应用技术2007-06-1033.2331字段名字段类型宽度是否为空默认值说明图书编号char10 x主键，采用10位字符编号类别char8x计算机2-4个汉字书名varchar40 x不超过40个字符出版时间datex单价Float5,2元为单位，精确到分数量int MyS

46、QL数据类型大数据类型：textblob时间类型：datetimedatetime数值类型：intFloat(M,D)字符串类型：Char(n)Varchar(n)MySQL常用数据类型：CREATE TABLE命令 为数据库创建数据表，可以使用SQL命令CREATE TABLE来完成。此语句的完整语法是相当复杂的，因为存在很多的可选子句创建表使用CREATE TABLE命令。 语法格式： CREATE TABLE IF NOT EXISTS 表名 （列名 数据类型 NOT NULL | NULL DEFAULT 列默认值） ENGINE = 存储引擎 CREATE TABLE命令的基本格式：

47、 CREATE TABLE tbl_name （列名1 数据类型 1 NOT NULL | NULL , 列名2 数据类型 2 NOT NULL | NULL , .） 命令行方式创建数据库表命令行方式创建数据库表的最基本格式： CREATE TABLE 数据库表名 ( 字段名1 数据类型1 NOT NULL | NULL, 字段名2 数据类型2 NOT NULL | NULL, . ); 图书编号类别书名出版时间单价数量字段名字段类型宽度是否为空默认值说明图书编号char10 x主键，采用10位字符编号类别char8x计算机2-4个汉字书名varchar40 x不超过40个字符出版时间dat

48、ex单价Float5,2元为单位，精确到分数量int 命令行方式创建数据库表book1命令方式创建表bookCREATE TABLE book ( 图书编号 char(10) NOT NULL, 类别 varchar(8) NOT NULL DEFAULT 计算机, 书名 varchar(40) NOT NULL , 出版时间 date NOT NULL , 单价 float(5,2) NULL , 数量 int ); 图书编号类别书名出版时间单价数量显示数据表信息（1）显示数据表文件名 语法格式： SHOW TABLES 【例3.11】显示Bookstore 数据库建立的数据表文件: USE

49、 Bookstore SHOW TABLES ;（2）显示数据表结构 语法格式： DESCRIBE | DESC 表名 列名 | 通配符 DESC是DESCRIBE的简写，二者用法相同。【例3.12】 用DESCRIBE语句查看 Book 表的列的信息。 USE Bookstore DESCRIBE Book ;【例3.13】 查看 Book 表图书编号 列的信息。 USE Bookstore DESC Book 图书编号 ;修改表 ALTER TABLE用于更改原有表的结构。例如，可以增加或删减列，创建或取消索引，更改原有列的类型，重新命名列或表，还可以更改表的评注和表的类型。语法格式：AL

50、TER TABLE 表名 ADD COLUMN 列定义 FIRST | AFTER列名 /*添加列*/ | ALTER COLUMN 列名 SET DEFAULT 默认值| DROP DEFAULT /*修改默认值*/ | CHANGE COLUMN 旧列名 列定义 /*对列重命名*/ FIRST|AFTER列名 | MODIFY COLUMN 列定义 FIRST | AFTER 列名 /*修改列类型*/ | DROP COLUMN 列名 /*删除列*/ | RENAME TO 新表名 /*重命名该表*/ 复制表语法格式：CREATE TABLE IF NOT EXISTS 新表名 LIKE

51、参照表名 | 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); 修改表结构增加列 ALTER TABLE

52、表名 ADD 列定义 FIRST | AFTER 列名例在表book中增加一列“浏览次数”。ALTER TABLE book ADD 浏览次数 int NULL;扩展在表book中增加一列“浏览次数”，位置在单价列的后面。ALTER TABLE book ADD 浏览次数 int NULL after 单价;删除列 ALTER TABLE 表名 DROP 列名例删除book表中“浏览次数”列。ALTER TABLE book DROP 浏览次数;在表book中增加“浏览次数”列，删除“封面图片”列。ALTER TABLE book ADD 浏览次数 int NULL, DROP 封面图片;扩展

53、修改表结构更改列名 ALTER TABLE 表名 CHANGE 旧列名 列定义 例将book表中“出版时间”列改为“出版日期”列。ALTER TABLE book CHANGE 出版时间 出版日期 date not null;更改列类型 ALTER TABLE 表名 MODIFY 列定义例将book表中“出版日期”列类型改为日期时间类型。ALTER TABLE book MODIFY 出版日期 datetime not null;修改表结构 ALTER TABLE用于更改原有表的结构。可以增加或删减列，创建或取消索引，更改原有列的类型，重新命名列或表，还可以更改表完整性约束和表的类型等。增加列

54、 ALTER TABLE 表名 ADD 列定义删除列 ALTER TABLE 表名 DROP 列名更改列名 ALTER TABLE 表名 CHANGE 旧列名 列定义 更改列类型 ALTER TABLE 表名 MODIFY 列定义删除表需要删除一个表时可以使用DROP TABLE语句。语法格式： DROP TABLE IF EXISTS 表名1 ,表名2 . 这个命令将表的描述、表的完整性约束、索引及和表相关的权限等都全部删除。【例】 删除表test。 USE XSCJ DROP TABLE IF EXISTS test ;数据完整性输入的类型是否正确？年龄必须是数字输入的格式是否正确？Ema

55、il必须包含符号是否在允许的范围内？性别只能是”男”或者”女”是否存在重复输入？员工信息输入了两次是否符合其他特定要求？信誉值大于5的客户才能够加入客户表列值要求（约束）整行要求（约束） 数据完整性约束 实体完整性域完整性参照完整性用户定义的完整性 实体完整性河南新乡赵可以0010016河南新乡张丽鹃0010015江西南昌雷铜0010014湖南新田吴兰0010013山东定陶李山0010012.地址姓名学号江西南昌雷铜0010014约束方法：唯一约束、主键约束域完整性河南新乡赵可以0010016河南新乡张丽鹃0010015江西南昌雷铜0010014湖南新田吴兰0010013山东定陶李山00100

56、12.地址姓名学号湖北江门李亮8700000000约束方法：限制数据类型、检查约束、外键约束、默认值、非空约束引用完整性河南新乡赵可以0010016河南新乡张丽鹃0010015江西南昌雷铜0010014湖南新田吴兰0010013山东定陶李山0010012地址姓名学号980010021数学约束方法：外键约束科目学号分数数学001001288数学001001374语文001001267语文001001381数学001001698自定义完整性AV121322乔峰CV0016AV372133玄痛CV0015AV378291沙悟净AV0014AV378290猪悟能AV0013AV378289孙悟空AV0

57、012.会员证用户姓名用户编号约束方法：存储过程、存储函数、触发器帐号姓名信用.00192孙悟空700288猪悟能612333段誉890111虚竹4093000岳不群-10触发器：检查信用值约束与完整性之间的关系 完整性类型约束类型描 述约束对象列完整性Default当使用INSERT语句插入数据时，若已定义默认值的列没有提供指定值，则将该默认值插入记录中 列Check指定某一列可接受的值实体完整性Primary Key每行记录的惟一标识符，确保用户不能输入重复值，并自动创建索引，提高性能，该列不允许使用空值行Unique在列集内强制执行值的惟一性，防止出现重复值，表中不允许有两行的同一列包含

58、相同的非空值参考完整性Foreign Key定义一列或几列，其值于本表或其他表的主键或UNIQUE列相匹配表与表之间主键约束 主键就是表中的一列或多个列的一组，其值能唯一地标志表中的每一行。通过定义PRIMARY KEY约束来创建主键，而且PRIMARY KEY约束中的列不能取空值。由于PRIMARY KEY约束能确保数据的唯一，所以经常用来定义标志列。如果 PRIMARY KEY 约束是由多列组合定义的，则某一列的值可以重复，但 PRIMARY KEY 约束定义中所有列的组合值必须唯一。可以用两种方式定义主键：作为列或表的完整性约束。作为列的完整性约束时，只需在列定义的时候加上关键字PRIM

59、ARY KEY。作为表的完整性约束时，需要在语句最后加上一条PRIMARY KEY（col_name，）语句。主键约束 (1)列的完整性约束： 【例】 创建表book2，将编号定义为主键。 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(学号, 课程号

60、) );主键约束原则 原则上，任何列或者列的组合都可以充当一个主键。但是主键列必须遵守一些规则。 （1）每个表只能定义一个主键。 （2）关系模型理论要求必须为每个表定义一个主键。 （3）唯一性原则：表中的两个不同的行在主键上不能具有相同的值。 （4）最小化规则：如果从一个复合主键中删除一列后，剩下的列构成主键仍然满足唯一性原则，那么，这个复合主键是不正确的，这条规则称为最小化规则。MySQL自动地为主键创建一个索引。通常，这个索引名为PRIMARY。然而，可以重新给这个索引起名。替代键约束在关系模型中，替代键像主键一样，是表的一列或一组列，它们的值在任何时候都是唯一的。替代键是没有被选做主键的