应用数据库相关设计和创建

应用数据库相关设计和创建CONTENTS3.1数据库基础目录3.2系统所涉的实体及其属性3.3项目数据库表3.4MySQL数据库的构建3.5数据库操作数据库，顾名思义，就是应用中所需要的数据都保存在其中，数据库设计的简单与复杂也直接影响到应用设计的框架和结构，所以，在数据库设计时，需要用户花费比较多得时间去思考应用中所需要的数据，并将他们整理成数据表。3.1数据库基础3.1.1基本概念在人们平常所提到的数据处理概念中，最主要的就是“数据”和“信息”，那么他们各自代表什么含义呢？其实数据和信息二者之间的关系是密不可分的，信息是对现实世界中存在的客观实体、现象和关系进行描述的具有特定意义的数据，是经过加工处理的数据。·数据：数据从字面上理解就是用来描述客观事物的符号、标记、图形等信息的组合；那么从实际应用上来看，数据就是能够直观的体现出与应用相关的信息。·信息：以数据为载体的对客观实际存在的事物、事件和概念的抽象反应，是经过加工处理过的数据。·数据处理：数据处理就是将数据进行相应的加工，处理之后成为信息。数据处理是将数据转换成信息的过程，是指对信息进行收集、整理、存储、加工以及传播等一系列活动的总和。我们可以简单的运用下面的公式表述数据、信息和数据处理三者之间的关系：信息=数据+数据处理数据是原材料，而信息则是产品，数据处理的真正意义是为了产生信息而去处理相关的数据。数据库设计的过程主要是三个阶段，需求分析阶段、数据库设计与实施阶段和数据库维护阶段。（1）需求分析阶段（2）数据库设计与实施阶段（3）数据库维护有些教科书上将数据库的设计分为6个阶段，这样就将设计阶段进行更加详细的划分。1.需求分析阶段2.概念设计阶段3.逻辑设计阶段4.物理设计阶段5.数据库实施阶段6.数据库运行和维护阶段3.1.2数据库设计的基本过程数据库的基本操作主要包括5方面：1.创建数据库表2.添加数据3.删除数据4.修改数据5.查询数据3.1.3数据库基本操作创建数据库表首先我们要知道，数据库表是二维表，包含行与列，创建表包括定义列名称、数据类型和约束等等。这些都是在创建数据表中完成。列名称的选取最好是取一个具有代表意义的名称，比如学生姓名用SName，而不要去使用没有代表意义的名称，那样会使得阅读表产生困难，不易于理解；数据类型是定义字段的取值类型和取值范围，用户也可以自定义数据类型；表约束则是更进一步限制了列的取值范围。这些约束包括下面几种类型：·主键约束：限制列的取值不为空，而且不能重复。·外键约束：限制列的取值受其他列或者其他表中列的取值范围约束。·列取值范围约束：限制列的取值必须是有意义的，比如性别只能取“男”或“女”等。·取值是否允许为空等。通过SQL语句创建表的一般格式：CREATETABLE<表名>(<列名><数据类型>[列级完整性约束条件][,<列名><数据类型>[列级完整性约束条件]]……[,<表级完整性约束条件>])下面我们通过一个创建“用户”表User来详细了解创建表的方法。可以在Sql-Server或MySQL工具中输入代码。1.CREATETABLEUser(2.UNo

char(10)NOTNULL,3.UNamechar(10)NOTNULL,4.USexchar(2)5.UAgetinyint,6.UAreachar(10)7.UEmailchar(20),8.PRIMARYKEY(UNo))在用户表中，我们定义了6个字段，分别是用户编号，用户名，用户性别，用户年龄、用户地区和电子邮件地址，如代码中2-6行所示。其中用户编号和用户名是不为空的，最后将用户编号设置为主键约束。这样一张数据库表就创建好了。添加数据插入数据有两种方式：一是向表中添加一行数据，二是一次添加多条数据。（1）单行数据的插入插入单行数据的SQL语句格式如下：INSERTINTO<表名>[(<列名表>)VALUES(<值列表>)]那么用户在使用单行插入语句时应该注意以下2点：1、值列表中的值与列名表中的列按顺序对应，要求它们的数据类型必须一致。2、如果<表名>后边没有指明列名，则新插入记录的值的顺序必须与表中列的顺序一致，而且每一列都必须有值（可以为空）。例如，我们向User表中添加一条记录INSERTINTOUserVALUES(‘2014011’,’张三’,’男’,18’,’江西’,45234123@qq’)这样我们就在User表中添加了一条记录。（2）多行数据的插入多行数据的插入实际上就是将某个查询语句的结果插入到相应的表中，插入多行数据的SQL语句格式如下：INSERTINTO<表名>[(<列表名>)子查询语句]我们通常会在以下两种情况下使用多行插入：1、当用户需要从表中提取数据，来生成新表时。2、当表结构变更之后，原有的数据可以重新置入。例如我们现在要求用户表中所有用户的平均年龄，并把结果存入新的数据库表中。那么，我们首先要创建一张新的数据表UAveage。1.CREATETABLEUAveage(UAreachar(10),UAveagesmallint)2.INSERTINTOUAveage(UArea,UAveage)3.SELECTUArea,UAveage(UAge)FROMUser4.GROUPBYUAveage删除数据删除数据比较容易，但是需要考虑到删除的条件是否是自己需要的，不要错删了重要的数据。删除数据的SQL语句格式如下：DELETEFROM<表名>[WHERE<删除条件>]Where条件可以有可无，如果不带where条件语句，那么就将删除整张表，删除后，表还是存在，只是内容为空。例如我们删除User表中年龄为20岁的用户。1.DELETEFROMUser2.WHEREUAge=20IN3.(SELETEUNoFROMUser)这样就从User表中删除了年龄为20岁的用户。如果需要删除整张表中的数据，直接删除即可。DELETEFROMUser修改数据在向表中添加了数据之后，如果需要再次修改数据，那么可以使用UPDATE语句来对数据进行更新操作。修改数据的SQL语句格式如下：

UPDATE<表名>SET<列名

=表达式>[,……n][WHERE<更新条件>]

<表名>代表需要修改数据的表的名称。SET指定需要修改的列，表达式指定需要更新的数据内容，WHERE子句可以不写，如果不写那么就会修改表中指定列的全部值。例如，我们现在更新表中学号为’2014011’用户的年龄为22。1.UPDATEUserSETUAge=222.WHEREUNo=‘2014011’查询数据SQL语言的核心就是数据的查询，从庞大的数据找找出我们需要的数据是一件多么伟大的事啊。虽然它的命令只有SELECT一个，但是它可以涵盖多个子句进行组合查询。我们简单介绍一下查询语句的格式。1.SELECT[ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]…2.FROM<表名或视图名>[,<表名或视图名>]…3.[WHERE<条件表达式>]4.[GROUPBY<列名1>[HAVING<条件表达式>]]5.[ORDERBY<列名2>[ASC|DESC]]

我们根据WHERE条件子句从FROM子句制定的表或视图中找到满足条件的元组，再按照SELECT子句中的目标列表达式，选出元组中的属性值形成结果。以表的形式显示出来。ORDERBY表示查询的结果是按升序排列还是降序排列。3.2系统所涉的实体及其属性“生源省市”实体主要保存我国各个省或直辖市的信息，如下图3-1所示。图3-1“生源省市”实体图图3-2“学校所在地区”实体图“学校所在地区”实体主要保存学校所在省市县的信息，如图3-2所示。“批次”实体主要保存录取的批次信息，包括一本、二本、三本、专科和提前本科共五种，如图3-3所示。“科类”主要保存专业类别，分为文史、理工、综合、体育和艺术共五类，如图3-4所示。图3-3“批次”实体图图3-4“科类”实体图“学校”的主要属性包括：学校编号、学校代码、学校名称、是否211、是否985、是否教育部直属以及学校所在地，如图3-5所示。学校与学校所在地区为一对多的关系，如图3-6所示。一个地区可以有多个学校，但一个学校只位于一个地区，当学校在其它地区有分校时，将其看做是两个学校。图3-5“学校”实体图图3-6学校与学校所在地区E-R图每个学校有一个学校招生部门。学校招生部门包括联系编号、学校名称、用户名（用于登录）、密码、联系人姓名、邮箱、办公室、手机号码、号、网址、被联系次数、创建时间、最后登录时间，如图3-7所示。“招生部门联系人”是学校招生部门的联系人，主要包括，联系人编号、联系人姓名、职位、和邮箱，如图3-8所示。图3-7“学校招生部门”实体图图3-8“招生部门联系人”实体图“专业”主要包括属性：专业编号、专业名称、专业代码，如图3-9所示。学校每个专业有一个专业负责人，“专业负责人”主要包括：负责人编号、负责人姓名、负责人和负责人邮箱，如下图3-10所示。图3-9“专业”实体图图3-10“专业负责人”实体图学校、专业及专业负责人有关系，一个学校开办多个专业，一个专业可以由多个专业开办，因此学校和专业是一对多的关系。一个专业拥有一个专业负责人，一个专业负责人负责一个专业，因此专业和专业负责人是一对一的关系，E-R图如图3-11所示。生源省市跟批次、科类等实体有关系，同时还应包括年份、分数和省控线编号属性。其中生源省市与批次、科类的关系，均为一对多，只有批次、科类以及年份三个个因素才能唯一确定一个生源省市的省控线分数，如图3-12所示。图3-11学校与专业E-R图图3-12省控线E-R图学校跟生源省市、批次、科类、学校所在地区等实体有关系，同时还应包括年份、平均分、投档线、最高分属性。其中学校与批次、生源省市、科类间的关系，均为一对多，学校与学校所在地区的关系是一对一的关系。只有生源省市、批次、科类以及年份四个因素才能唯一确定这一学校某年具体批次具体科类的录取线。录取线包括平均分和最高分两项，如图3-13所示。图3-13学校录取E-R图学校跟专业、生源省市、批次、科类等实体有关系，同时还应包括年份、省控线、最高分、平均分属性。其中专业与学校、生源省市与学校、批次学校、科类与学校间的关系，均为一对多，只有专业、生源省市、批次、科类以及年份五个因素才能唯一确定这一学校某个专业某年具体批次具体科类的录取线，如图3-14所示。录取线包括平均分和最高分两项。注意：同一个专业在不同学校批次可能不同，有些专业即可招文科生也可招理科生。图3-14学校与专业录取E-R图“高考咨讯”实体保存与高考相关的资讯信息，包括：咨讯编号、生源省市编号和咨讯内容，如图3-15所示。图3-15“高考咨讯”实体图“豹考通”App数据总体E-R图如图3-16所示。图3-16“豹考通”App数据总体E-R图编号名称字段代码类型长度是否可空主键备注1专业编号specialtyIdint

非空是自增2专业名称specialtyNamevarchar20非空

3专业代码specialtyCodevarchar6允许空

3.3项目数据库表专业表主要包括专业编号、专业名称、专业代码字段，如表3-1所示。表3-1专业表（specialty）地区表主要包括地区编号、地区名称字段，如表3-2所示。编号名称字段代码类型长度是否可空主键备注1地区编号areaIdshort

非空是自增2地区名称areaNamevarchar10非空

表3-2地区表（area）编号名称字段代码类型长度是否可空主键备注1批次编号batchIdshort

非空是自增2批次名称batchNamevarchar10非空

批次表主要包括批次编号、批次名称字段。批次分为：一本、二本、三本、专科、提前本科，如表3-3所示。表3-3批次表（batch）科类表主要包括科类编号、科类名称字段。批次分为：文史、理工、综合、艺术、体育，如表3-4所示。编号名称字段代码类型长度是否可空主键备注1科类编号categoryIdshort

非空是自增2科类名称categoryNamevarchar10非空

表3-4科类表（category）编号名称字段代码类型长度是否可空主键备注1学校编号schoolIdshort

非空是自增2学校名称schoolNamevarchar10非空

3学校代码schoolCodevarchar6允许空

4学校所在地schoolAreashort

非空

外键5是否211is211boolean

默认false6是否985is985boolean

默认false7是否直属isMinistryboolean

默认false学校表主要包括学校编号、学校名称、学校代码、学校所在地、是否211、是否985、是否直属字段，如表3-5所示。表3-5学校表（school）编号名称字段代码类型长度是否可空主键备注1省控线编号controlLineIdshort

非空是自增2省控线值controlLineshort

3年份yearvarchar4非空

4批次编号batchIdshort

非空

外键5科类编号categoryIdshort

非空

外键6生源地sourceAreashort

非空

外键省控线表主要包括省控线编号、省控线值、年份、批次编号、科类编号、生源地，如表3-6所示。表3-6省控线表（controlLine）编号名称字段代码类型长度是否可空主键备注1录取线编号schoolRecruitIdshort

非空是自增2学校编号schoolIdshort

非空

外键3年份yearvarchar4非空

4批次编号batchIdshort

非空

外键5科类编号categoryIdshort

非空

外键6生源地sourceAreashort

非空

外键7录取平均分averageshort

允许空

8录取最高分highestshort

允许空

9投档线enterLineshort

允许空

10最低排名enterLineRankshort

允许空

11冒险预测riskEstimateshort

允许空

12稳妥预测safeEstimateshort

允许空

13保守预测keepEstimateshort

允许空

学校录取线表主要包括录取线编号、年份、批次编号、科类编号、学校编号、生源地、录取平均分、录取最高分、投档线、投档最低排名、预测投档线，如表3-7所示。表3-7学校录取表（schoolRecruit）编号名称字段代码类型长度是否可空主键备注1录取线编号majorRecruitIdshort

非空是自增2学校编号schoolIdshort

非空

外键3年份yearvarchar4非空

4批次编号batchIdshort

非空

外键5科类编号categoryIdshort

非空

外键6生源地sourceAreashort

非空

外键7专业编号majorIdshort

非空

8专业名称majorNamelong

非空

9专业录取线majorScoreshort

非空

专业录取线表主要包括录取线编号、年份、批次编号、科类编号、学校编号、生源地、专业编号、专业名称和专业录取线，如表3-8所示。表3-8专业录取表（majorRecruit）编号名称字段代码类型长度是否可空主键备注1信息编号schoolInfoIdshort

非空是自增2学校编号schoolIdshort

非空

外键3用户名schoolUsernamevarchar20非空

4密码schoolUserPsdvarchar20非空

5联系人姓名trueNamevarchar20允许空

6邮箱schoolUserEmailvarchar20允许空

7办公室电话school