做的网站客户拿去维违法建设厅网站如何查询企业信息网

当前位置： 首页 > news >正文

做的网站客户拿去维违法,建设厅网站如何查询企业信息网,wordpress带个人中心,慈溪住房和城乡建设部网站设计数据库 1. 数据建模1.1 概念模型1.2 逻辑模型1.3 实体模型主键外键外键约束 2. 标准化2.1 第一范式2.2 链接表2.3 第二范式2.4 第三范式 3. 数据库模型修改3.1 模型的正向工程3.2 同步数据库模型3.3 模型的逆向工程3.4 实际应用建议 4. 数据库实体模型4.1 创建和删除数据库… 设计数据库 1. 数据建模1.1 概念模型1.2 逻辑模型1.3 实体模型主键外键外键约束 2. 标准化2.1 第一范式2.2 链接表2.3 第二范式2.4 第三范式 3. 数据库模型修改3.1 模型的正向工程3.2 同步数据库模型3.3 模型的逆向工程3.4 实际应用建议 4. 数据库实体模型4.1 创建和删除数据库4.2 创建表4.3 更改表4.4 创建关系4.5 更改主键和外键约束4.6 字符集和排序规则4.7 存储引擎 1. 数据建模 数据建模的4个步骤 理解需求 理解和分析商业/业务需求遗憾是很多程序员跳过了这一步就急着去设计数据库里的表和列了实际上这一步是最关键的一步你对问题理解的越透彻你才越容易找到最合适的解决方案设计数据库也一样。所以在动手创建表和列之前要先完整了解你的业务需求包括和产品经理、行业专家、从业人员甚至终端用户深入交流以及收集查阅已有的领域相关的表、文件、应用程序、数据库以及其他与问题领域相关的任何信息或资料。概念建模 当收集并理解了所有相关信息后下一步就是为业务创建一个概念性的模型。这一步包括找出/识别/确认identify业务中的实体/事务/概念entities/things/concepts以及它们之间的关系。概念模型只是这些概念的一个图形化表达用来与利益相关方交流和达成共识。逻辑建模 创建好概念模型后转而创建数据模型data model或数据结构data structure for storing data即逻辑建模。这一步创建的是不依赖于具体数据库技术的抽象的数据模型主要是确认所需要的表和列以及大体的数据类型。实体建模 实体建模指的是将逻辑模型在具体某种DBMS上加以实现的过程相比于逻辑模型实体模型会确定更多细节包括各表主键的设定各列在某一DBMS下特定的具体的数据类型默认值是否可为空还包括储存过程和触发器等对象的创建。总之实体模型是在某一特定DBMS下对数据模型非常具体的实现。 1.1 概念模型 案例建立学生实体并确定相关属性建立课程实体并确定相关属性建立两个实体之间的关系。如上图所示。 将实体及其关系可视化的方法一种是实体关系图Entity Relationship, ER一种是统一建模语言Unified Modeling LanguageUML这里我们用实体关系图ER这里采用工具为VISIO。 NOTE: 建模是个迭代过程不可能第一次就建立完美模型需要在理解需求和模型设计之间不断反复多次调整。比如这里的学生属性可以先确定个大概之后可以根据需要再进行增删修改。 概念模型主要是从很高的视角来总览业务需求识别业务中的实体/事务/概念以及他们彼此间的关系通常这些实体包括人、事件、地点等。只是从概念上总揽全局目的是和业务人员交流保持理解一致。 1.2 逻辑模型 逻辑模型是在概念模型的基础上在不依赖特定数据库系统的前提下确定数据结构包括细化实体间的关系常常要为关系创造新的实体调整字段设置确定大体的数据类型。总之逻辑模型会基本确立数据库中的表、列以及表间关系。 对概念模型逻辑化的过程如下 细化实体间关系 考虑学生和课程的关系首先这是一种多对多关系通常意味着需要进一步细化这些属性相对于学生和课程而言都是一对多关系不管放在学生还是课程身上都不合适所以应该为学生和课程之间的关系即注册课程的事件另外设立一个实体 enrollmemt上面的注册日期和注册价格都应该是这个enrollment 注册事件的属性。调整字段并大体确定字段的数据类型 姓名name最好拆分为姓和名 (first_name 和 last_name)同理地址应该拆分为省、市、街道等等小的部分这样方便查询。注意课程里的 tags 标签字段不是一个好的设计之后讲归一化时再来处理。 这里的数据类型只需确定个大概即可如是 stringfloat 而非 VARCHAR, DECIMAL。等到下一步实体模型里再来确定某个DBMS下的具体数据类型。
1.3 实体模型 实体模型实际模型就是逻辑模型在具体DBMS的实现这里我们用MySQL实现的逻辑模型。实体模型是逻辑模型在特定DBMS上的实现主要是一些技术上的细化包括确定字段具体数据类型和性质能否为空等设置主键等。 操作方法Workbench-file-【new model】新建数据库上方用 add diagram 作 EER 图这里EER表示Enhanced Entity Relationship增强型实体关系图。为三个实体创建三张表设定表名、字段、具体的数据类型、是否可为空即是否为必须字段也可以选择设定默认值主键设定之后再讲。 NOTE: 表名之前逻辑模型里表名用单数但这里表名用复数。这只是一种惯例单复数都行关键是要保持一致。字段名以enrollments表为例注册事件的属性应该是dat日期和pric价格而非enrollment_date注册日期和enrollement_price注册价格不要将表名前缀加上字段上造成不必要的麻烦保持精简keep thingssimple。数据类型数据类型要根据业务需要来例如和业务人员确认后发现课程价格最高是999美元所以price价格就可以设定为DECIMAL(5,2)之后如果需求变了了也可以随时更改不要一上来就设定DECIMAL(9,2)浪费磁盘注意保持精简keep things small 主键 主键就是能唯一标识表中每条记录的字段。一般用数字ID来确定 主键要短可唯一标识记录且永不改变。我们增加一个 student_id 作为主键类型设为 INT最大可表示2亿一般足够了但记得总是根据具体的需求决定设为主键后自动变为不可为空另外还要设定 AIAutoIncremental自动递增这样会方便许多不用担心主键唯一性的问题最后我们把主键拖到表的第一列让表的结构看起来更清晰。
外键 实体模型需要更多得实现细节。 Note: 连线时记不得先连主表还是子表可以看状态栏的提示; MySQL自动添加的外键会带父表前缀没必要建议去掉. 注意 enrollments 表的特殊性它可以说是 students 和 courses 的衍生表先要有学生和课程才能有学生注册课程这一事件后者表述的是前两者的关系学生和课程是因注册课程这一事件是果。 MySQL里可以通过一对一或一对多两种连线表达这种先后关系/因果关系并自动建立外键其中学生和课程被称作父表或主键表注册事件被称作子表或外键表外键是子表里对父表主键的引用。 根据表间关系给enrollments表添加了student_id和course_id两个外键enrollments 的主键设置有两个选择 将这两个外键作为联合主键另外设置一个单独的主键 enrollment_id。 联合主键可以防止同一个学生重复注册同一门课程因为主键这里是联合主键是唯一不可重复的这可以防止一些不合理的数据输入。坏处是如果 enrollments 未来有新的子表就需要复制两个字段而不是 enrollment_id 这样的一个字段作为外键这也不一定是很大的麻烦要根据数据量以及子表是否还有子表等情况来考虑在一定情况下可能会造成不必要冗余和麻烦相对于将 enrollment_id 一个字段作为主键来说。 创建表之间关系和设置ID时先考虑当下的情况。 外键约束 有外键时需要设置约束以防止数据损坏/污染不一致。 右边 Foreign KeyOptions 可分别选择当父表里的主键被修改或删除Update / Delete时子表里的外键如何反应有三种选项 CASCADE: 瀑布/串联/级联表示随着主键改变而改变如主键某学生的student_id从1变成2则改学生的所有注册课程记录的 student_id也会全部变为2注意主键一般也最好是永远不要变的这里讨论的是特殊情况RESTRICT/NO ACTION: 等效作用都是禁止更改或删除主键。如对于有过注册记录的课程除非先删除该课程的注册购买记录不然不能在 courses表里删除该课程的信息。另外注意MySQL 里外键默认的 On Update 和 On Delete 的反应都是 NO ACTIONSET NULL: 就是当主键更改或删除时使得相应的外键变为空这样的子表记录就没有对应的主键和对应的父表记录了no parent被称为孤儿记录orphan record这是垃圾数据让我们不知道是谁注册的课程或不知道注册的是什么课程一般不用只在极其特殊的情况可能有用. 通常对于 UPDATE, 设置为 CASCADE 级联随之改变. 对于 DELETE看情况而定可能设置为 CASCADE 随之删除 也可能设置为 RESTRICT / NO ACTION 禁止删除。不要死板永远按照业务/商业需求来选择这也正是为什么之前强调“理解业务需求”是最重要的一步。比如我们课程注册记录里包含购买价格信息则应该禁止删除否则之后想查询某课或某时间段的收入情况就不能实现相反如果只是个用户登录并设定一系列提醒的软件可能用户允许用户注销并删除所有提醒就没什么大不了的但万一我们需要这些提醒记录来进行统计则又该设置为禁止删除总之一定要根据具体业务需求来always check with the business。

1. 标准化 正式建立数据库前我们先要检查并确定现在的设计是最优化的optimal关键是没有任何冗余或重复简洁且便于修改和保持一致性。重复数据会占用更多空间并且使得增删查改的操作复杂化比如如果用户名在多处出现的话一旦更改用户名就要到多处更改否则就会使得数据不一致出现无效数据。 为了防止重复冗余需要遵循数据库设计的7大规则或者说7大范式每一条都是建立在你已经遵循了前一条的基础上。实际上99%的数据库之需要遵循前3大范式就够了。 2.1 第一范式 第一范式要求一行中的每一个单元格都应该有单一值且不能出现重复列。Each cell should have a single value and we cannot have repeated columns。 案例courses 里的 tags 标签列就不符合第一范式。tags 列用逗号隔开多个标签不是单一值。若将 tags 分割成多列每个标签一列问题是我们不知道到底有多少标签每次出现新标签就要改动表结构这样的设计很糟糕。这也正是范式1要求没有重复列的原因。因此将tags列单独拉出生成表。
   2.2 链接表 建立courses和tags之间的联系发现两者是多对多关系MySQL里只有一对一和一对多没有多对多这说明两者的关系需要进一步细化我们添加一个course_tags表来专门描述两者间的关系记录每一对课程和标签的组合这个中间表或者说链表link table同时是 courses 和 tags 的子表与这两个父表均为一对多的关系建立两条一对多连线后MySql自动给 course_tags表增加了两个外键course_id和tage_id注意去掉自动添加的表前缀两者构成 course_tags表的联合主键。 建立链表细化多对多关系这是很常用的一种方法有时链表只包含引用的两个外键如course_tags 表有时链表还包含其它信息如 enrollments 表。 链表也可以直接用多对多来生成。当表中存在不确定数值的属性列时需要在实体模型中进一步细分。 2.3 第二范式 每个表都应该是单一功能的/应该表示一个实体类型这个表的所有字段都是用来描述这个实体的。Every table should describe one entity, and every column in that table should describe that entity。 以 courses 表为例course_id、title、price 都完全是属于课程的属性放在courses 表里没问题但注册时间enrollment_date放在courses表里就不合适因为一个课程可以被多个学生注册所以有多个注册时间同样的注册时间也不应该是students表的属性因为一个学生可以注册多门课所以可以有多个注册时间注册时间应该是属于“注册事件”的属性所以应该另外建个enrollments表放在该表里。 总之第一范式是要求单一值和无重复列这里第二范式是要求表中所有列都只能是完全描述该表所代表的实体的属性不属于该实体如订单表的、在记录中可重复的属性应该另外放在描述相应实体的表里如顾客表。 案例courses里的instructors虽然是单一值符合第一范式却不符合第二范式因为老师不是完全属于课程的属性老师在不同课程中可能重复。所以另外建立instructrors表作为父表包含instructor_id和name字段其中instructor_id为主键一对多连接courses表后自动引进courses表作为外键删除原先的instructor列。还有注意设置外键约束UPDATE 设置为CASCADEDELETE设置为NO ACTION也就是instructor_id会随着instructors表更改但不允许在某教师有课程的情况下删除该教师的信息。
   2.4 第三范式 一个表中的字段不应该是由表中其他字段推导而来。A column in a table should not be derived from other columns. 案例 invoices 发票表里假设有三个字段发票额、支付额 和 余额第三个可以由前两个相减得到所以不符合3NF每次前两者更新第三个就要随之更新假设没有这样做让三者出现了 1004080 这样不一致的数据就不知道到底该相信哪个了余额到底是 80 还是 100-4060如果表里已经有 first_name 和 last_name 就不该有 full_name因为第三者总是可以由前两者合并得到不管是上面的 余额balance 还是 全名fullname都是一种冗余应该删除。 这些范式目的减少数据重复和冗余增强数据的一致性和完整性。
2. 数据库模型修改 3.1 模型的正向工程 通过模型正向搭建数据库workbench 菜单的 Database 选项 → Forward Engineer 正向搭建数据库。依据向导保持默认不断点下一步就好了不要更改除非你知道你在做什么。 最后一步会展示对应的SQL代码里面有创建 school 数据库schema 架构模式纲目结构方案以及各表的SQL代码之后会详细讲。可以选择保存代码为文件以保存到仓库中或者复制到剪贴板然后到workbench查询窗口里以脚本方式运行这里我们直接运行返回local instance连接刷新界面就可以看到新的school数据库和里面的6张表了。 3.2 同步数据库模型 之后可能会修改数据库结构比如更改某些表中字段的数据类型或增加字段之类如果只是自己一个人用的一个本地数据库可以直接打开对应表的设计模式并点击更改即可但如果是在团队中工作通常不是这样。在中大型团队中我们通常有多个服务器来模拟各种环境其中有 生产环境production environment用户真正访问应用和数据库的地方模拟环境staging environment与生产环境十分接近测试环境testing environment存粹用来做测试的开发环境development environment 每次需要对数据库做修改时我们需要复制相同的修改到不同的环境以保持数据的一致性。 操作所以不能是在设计模式中直接点击修改相反是在之前模型标签注意模型可以保存为一个 MySQL 模型文件下次可以直接打开使用里的实体模型图EER Diagram中修改表或字段,并使用菜单中的 Database →Synchronize Model 用 模 型 创 建 数 据 库 时 用 Forward Engineer 对 已 有 数 据 库 进 行 同 步 修 改 时 用Synchronize Model 。 注 意 点 开 Synchronize Model 后 可 以 选 择 连 接 。 3.3 模型的逆向工程 为数据库创建模型之后可以在模型上更改便于同步各个环境下的数据库数据。 模型图中实线和虚线表示强联系和弱联系实线表示must be,虚线表示may be。 Note: 关闭当前 Model不然之后的逆向工程结果会添加到当前模型上最好是每个数据库都有一个单独的模型除非数据库间相互关联否者不要在一个模型中处理多个数据库.Database → Reverse Engineer可以选择目标数据库如上说所除非数据库相互关联否者最好一次只逆向工程一个数据库让每个数据库都有一个单独的模型.可以对逆向的表格进行筛选。 在反向搭建出的模型中可以更好的看到和理解数据库的结构设计可以修改表结构并将相关修改脚本保存并用于其它环境的数据库还可以发现问题。尽量消除没有关联的表格。 3.4 实际应用建议 不要死记范式规则重点在于消除冗余减轻数据库复杂度 比如发现一个name字段下出现的是一些重复的名字而不是重复的外键如某种id那就说明设计还不够归一化具体违反哪条范式并不重要关键是专注于避免重复性。不要对什么都建模 简单才是最高境界。Simplicity is the Ultimate Sophistication.第六范式不要在关系型数据库中再建关系型数据结构。 设计数据库时总是考虑当前的业务需求不要试图包罗万象总有开发人员会考虑各种未来可能出现的需求实际上大部分那些需求都从未发生反而使得数据库增加了很多没必要的复杂性复杂化了查询并拖慢了执行效率. 建立复杂模型不是本事让模型尽可能优美简单易懂又能满足目前的需求这才是本事如果还能有不错的拓展性以满足未来可能的新特性就更好了. 尽可能保持简洁简洁才是终极哲学无论你对未来的预测有多好总会有意料之外的需求出现总有一天你会写脚本改数据库甚至进行数据迁移这是避免不了的当前只需考虑如何最好地满足目前的需求就好了不要企图对全宇宙建模。
3. 数据库实体模型 4.1 创建和删除数据库 用workbench的向导来创建和修改数据库能够提高效率但作为 DBA (Database Administrator 数据库管理员)你必须要能理解并审核相关代码确保其不会对数据库有不利影响而且也有能力手动写代码完成创建和修改数据库的操作可以不依赖图形化和向导工具。 创建和删除数据库代码 CREATE DATABASE IF NOT EXISTS sql_store2; DROP DATABASE IF EXISTS sql_store2;4.2 创建表 DROP DATABASE IF EXISTS sql_store2; CREATE DATABASE IF NOT EXISTS sql_store2; – 没有就创建有就删除USE sql_store2; – 创建数据库后一定要选择相关数据库 DROP TABLE IF EXISTS customers; CREATE TABLE IF NOT EXISTS customers(customer_id INT PRIMARY KEY AUTO_INCREMENT,first_name VARCHAR(50) NOT NULL,points INT NOT NULL DEFAULT 0,email VARCHAR(255) NOT NULL UNIQUE );NOTE: 创建对象不管是数据库还是表有两种方式DROP …… IF EXIXTS …… CREAT …… 和 CREAT …… IF NOT EXISTS ……注意两种方式的区别在于当原对象存在时前者是推倒重建后者是保持原状放弃创建所以按通常的需求来看还是前者更符合。括号中设置列的方式为 列名 数据类型 各种列性质列间逗号分隔常用的列性质有PRIMARY KEY 、AUTO_INCREMENT、 NOT NULL 、DEFAULT 0、 UNIQUE。 4.3 更改表 ALTER TABLE customersADD COLUMN last_name VARCHAR(50) NOT NULL AFTER first_name,ADD city VARCHAR(50) NOT NULL,MODIFY COLUMN first_name VARCHAR(60) DEFAULT , DROP COLUMN points;NOTE: COLUMN 是可选的有的人喜欢加上以增加可读性;AFTER first_name 是可选的不加的话默认将新列添加到最后一列;MODIFY 修改已有列经实验发现其实应该是重置该列 DROP ADD所以注意要列出全部类型和属性信息如上例中将 first_name 修改为 VARCHAR(60) 类型并将默认值修改为空字符串’但忘了加 NOT NULL刷新后发现 first_name 不再有 NOT NULL 属性;列名最好不要有空格但如果有的话可用反引号包裹如 last name.修改表永远不要直接在生产环境中进行要首先在测试环境进行确保没有错误和不良影响后再到生产环境进行修改。 4.4 创建关系 CREATE DATABASE IF NOT EXISTS sql_store2; USE sql_store2; DROP TABLE IF EXISTS orders; DROP TABLE IF EXISTS customers; CREATE TABLE customers(customer_id INT PRIMARY KEY AUTO_INCREMENT,first_name VARCHAR(50) NOT NULL,points INT NOT NULL DEFAULT 0,email VARCHAR(255) NOT NULL UNIQUE ); CREATE TABLE orders(order_id INT PRIMARY KEY,customer_id INT NOT NULL,order_date DATE NOT NULL, – 在添加完所有的列之后设置外键FOREIGN KEY fk_orders_customers(customer_id)REFERENCES customers(customer_id)ON UPDATE CASCADEON DELETE NO ACTION );Note: 外键名的命名习惯fk_子表_父表语法结构 FOREIGN KEY 外键名 (外键字段) REFERENCES 父表 (主键字段) – 【设置外键约束】 ON UPDATE CASCADE ON DELETE NO ACTION应该把orders表子表的DROP语句放到最前面不然第二次运行会出问题。 4.5 更改主键和外键约束 USE sql_store2; ALTER TABLE ordersDROP PRIMARY KEY,ADD PRIMARY KEY (order_id),DROP FOREIGN KEY fk_orders_customers,ADD FOREIGN KEY fk_orders_customers (customer_id)references customers (customer_id)ON UPDATE CASCADEON DELETE NO ACTION;Note 这里不管是之前创建orders表时设置外键还是这里通过修改ADD增加外键外键名明明写的是 fk_orders_customers实际上都会变成 orders_ibfk_1要去设计模式手动修改才行可能是bug通过类似的 ALTER TABLE 语句增删主键可增加多个主键在括号内用逗号隔开注意说的是 ADD 其实是重置所以一次要把该表所有需要的主键声明完整。 4.6 字符集和排序规则 字符是以数字序列的形式储存于电脑中的字符集是数字序列与字符相互转换的字典不同的字符集支持不同的字符范围有些支持拉美语言字符有些也支持亚洲语言字符有些支持全世界所有字符查看MySQL支持的所有字符集 SHOW CHARSET;其中armscii8支持亚美尼亚语big5支持繁体中文gb2312和gbk支持简体中文gk是国标的拼音简称k是扩展的拼音简称而utf-8支持全世界的语言utf-8也是MySQL自版本5之后的默认字符集。还可以看到字符集描述默认排序规则最大长度。 排序规则collation指的是某语言内字符的排序方式utf-8的默认排序规则是utf8_general_ci其中ci表示case insensitive大小写不敏感即MySQL在排序时不会区分大小写这在大部分时候都是适用的比如用户输入名字的时候大小写不固定我们希望只按照字符顺序而不管大小写来对名字进行排序。总之99.9%的情况下都不需要更改默认排序规则。 对于字符集来说大部分时候用默认的utf-8就行了。但有时我们可以通过更改字符集来减少空间占用例如我们某个特定的应用对应的数据库/特定表/特定列是只能输入英文字符的那如果将该列的字符集从utf-8改为latin1占用空间就会缩小到原来的1/3以字段类型为CHAR(10)(固定预留10个字符)且有1百万条记录为例占用空间就会从约30MB减到10MB。接下来将如何用菜单和代码方式更改库/表/列的字符集。 Workbench更改办法 菜单方式更改字符集右键sql_store2 数据库点击 Schema Inspector可查看要修改库或者表和列的字符集直接点开库或者表的设计模式扳手按钮在里面选择更改即可。 代码更改 1在创建数据库时设置字符集或更改已有数据库字符集
   CREATE/ALTER DATABASE db_name CHARACTER SET latin12在创建表时设置字符集或更改已有表的字符集 CREATE/ALTER TABLE table1 (……) CHARACTER SET latin13在创建表时设置列的字符集 CREATE TABLE IF NOT EXISTS customers( customer_id INT PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(50) CHARACTER SET latin1 NOT NULL, points INT NOT NULL DEFAULT 0, email VARCHAR(255) NOT NULL UNIQUE)4.7 存储引擎 在MySQL中我们有若干种储存引擎储存引擎决定了我们数据的储存方式以及可用的功能. SHOW ENGINES;储存引擎有很多我们真正需要知道只有两个MyISAM读作 My-I-SAM 和 InnoDBMyISAM是曾经很流行的引擎但自MySQL5.5之后默认引擎就改为InnoDB了InnoDB支持更多的功能特性包括事务、外键等等所以最好使用InnoDB引擎是表层级的设置每个表都可以设置不同的引擎虽然这没必要。 外键是十分重要的它可以增加引用一致性/完整性referential integrity如果我们有一个老数据库的引擎是 MyISAM我们想要给它设置外键就必须要将其引擎升级为 InnoDB可以在表的设计模式里选择更改也可以用修改表的代码 ALTER TABLE customers ENGINE InnoDB;改变引擎是一个代价极高expensive的操作它会重建整个表在此期间无法方法访问数据。所以不要轻易在生产环境中改变储存引擎除非有特殊的理由。