初始mysql与数据库设计

初始mysql与数据库设计

1-1使用数据库的意义：

电子文件柜，用户可以对文件中的数据进行新增、截取、更新、删除等操作
【存储大量数据】，存储结构便于【管理和访问】
可以有效保障数据一致性、完整性、降低数据冗余
可以满足应用的共享和安全方面的要求
故障恢复，防止数据被破坏

1-1-2必须要设计数据库的原因：节省空间，方便使用访问高效，保存数据安全；

良好的数据库设计
节省数据的存储空间
能够保证数据的完整性
方便进行数据库应用系统的开发
       vs
糟糕的数据库设计
数据冗余、存储空间浪费
内存空间浪费
数据更新和插入的异常

1.存储数据管理数据【500万数据以上需要进行索引优化】

数据库管理员DBA【Database Administrator】，是从事管理和维护数据库管理系统(DBMS)的相关工作人员的统称，属于运维工程师的一个分支，主要负责业务数据库从设计、测试到部署交付的全生命周期管理

数据库DB【database】

数据库管理系统DBMS【database management systems 】科学组织和存储数据、高效地获取和维护数据 ；持久数据

​ DBS 即Database System，中文意为“数据库系统”，是指带有数据库并利用数据库技术进行数据管理的[计算机系统]

RDBMS【relational database management systems 】关系型数据库管理系统例如MySQL

关系数据库，是建立在关系模型基础上的数据库 ，关系模型就是指【二维表格模型】,因而一个关系型数据库就是由二维表及其之间的【联系】组成的一个数据组织

MySQL是一个关系型数据库管理系统，由【瑞典MySQL AB】公司开发，目前属于【Oracle】公司。MySQL是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了【速度并提高了灵活性】。MySQL所使用的【SQL语言】是用于访问数据库的最常用标准化语言。MySQL软件采用了双授权政策（本词条“授权政策”），它分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般【中小型网站】的开发都选择MySQL作为网站数据库。由于其社区版的性能卓越，搭配【PHP和Apache】可组成良好的开发环境。

1-2MySQL运行机制

使用一个SQL语句，如select * from tablename ，从支持接口进来后，进入连接池后做权限、验证等环节，然后判断是否有【缓存】，有则直接放回结果，否则进入SQL接口，在查询之前查询优化器进行优化，最后进行解析，查询。并通过存储引擎与文件交互。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gc7VTe6x-1602605150063)(初始mysql/image-20201013160742613.png)]

名词解释：

支持接口：不同的编程语言与SQL的交互

连接池：管理缓冲用户连接，线程处理等需要缓存的需求

SQL接口：接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL接口

解析器：SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本。
主要功能：
a . 将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的；例如将 select * from tablename where 1=1；分解为select、*、from、tablename、where 、1=1，并去解析。
如果在分解构成中遇到错误，那么就说明这个SQL语句是不合理的。

查询优化器：SQL语句在查询之前会使用查询优化器对查询进行优化。他使用的是【“选取-投影-联接”】策略进行查询。
例： select uid,name from user where gender = 1;
a.先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤
b.先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤
将这两个查询条件联接起来生成最终查询结果。

缓存：
【如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。
这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等】

存储引擎：
存储引擎是MySql中具体的与文件打交道的子系统。也是Mysql最具有特色的一个地方。
Mysql的存储引擎是[插件式]的。它根据MySql AB公司提供的【文件访问层的一个抽象接口】来定制一种文件访问机制（这种【访问机制就叫存储引擎】）。
现在有很多种存储引擎，各个存储引擎的优势各不一样，最常用的MyISAM,InnoDB,BDB。
默认下MySql是使用MyISAM引擎，它查询速度快，有较好的索引优化和数据压缩技术。但是它不支持事务。
InnoDB支持事务，并且提供行级的锁定，应用也相当广泛。 Mysql也支持自己定制存储引擎，甚至一个库中不同的表使用不同的存储引擎，这些都是允许的

1-3验证数据库是否按照

net start mysql ：启动mysql服务
mysql -h服务器地址 -u用户名 -p密码 ：登陆
show databases; ：显示db列表
use 数据库名; ：使用指定数据库
show tables; ：显示table列表
update user set password=password(‘新密码’) where user=‘用户名’;
flush privileges; ：修改登录密码
? 命令名 ：显示命令帮助

1-4复合类型（MySQL特色，慎用，影响数据库迁移）

MySQL 还支持两种复合数据类型 【ENUM 和 SET】它们扩展了 SQL 规范。虽然这些类型在技术上是【字符串类型】，但是可以被视为不同的数据类型。一个 ENUM 类型只允许从一个集合中取得一个值；【而 SET 类型允许从一个集合中取得任意多个值。】
ENUM 类型
　　ENUM 类型因为只允许在集合中取得一个值，有点类似于单选项。在处理相互排拆的数据时容易让人理解，比如人类的性别。ENUM 类型字段可以从集合中取得一个值或使用 null 值，除此之外的输入将会使 MySQL 在这个字段中插入一个空字符串。另外如果插入值的大小写与集合中值的大小写不匹配，MySQL 会自动使用插入值的大小写转换成与集合中大小写一致的值。
　　 ENUM 类型在系统内部可以存储为数字，并且从 1 开始用数字做索引。一个 ENUM 类型最多可以包含 65536 个元素，其中一个元素被 MySQL 保留，用来存储错误信息，这个错误值用索引 0 或者一个空字符串表示。
　　MySQL 认为 ENUM 类型集合中出现的值是合法输入，除此之外其它任何输入都将失败。这说明通过搜索包含空字符串或对应数字索引为 0 的行就可以很容易地找到错误记录的位置。
SET 类型
　　SET 类型与 ENUM 类型相似但不相同。SET 类型可以从预定义的集合中取得任意数量的值。并且与 ENUM 类型相同的是任何试图在 SET 类型字段中插入非预定义的值都会使 MySQL 插入一个空字符串。如果插入一个即有合法的元素又有非法的元素的记录，MySQL 将会保留合法的元素，除去非法的元素。

NULL值
理解为“没有值”或“未知值”
不要用NULL进行算术运算，结果仍为NULL
*************************************
使用zerofill会默认加unsigned
`id` tinyint(4) zerofill
`id`将不能存负数，最大存储255
CREATE TABLE 表名 ( ... ) AUTO_INCREMENT = 100;
alter table 表名 auto_increment = x  ; 【如果x小于现在的值则修改无效】
【truncate 表名 ; 直接清空所有数据，下次插入数据时auto_increment字段会自动从0开始】。 VS【和delete from tbname不同的是delete只删除数据而不重置auto_incremnet】
SET @@auto_increment_increment = 10;	修改自动增长的步长（全局修改）

一个 SET 类型最多可以包含 64 项元素。在 SET 元素中值被存储为一个分离的“位”序列，这些“位”表示与它相对应的元素。“位”是创建有序元素集合的一种简单而有效的方式。并且它还去除了重复的元素，所以 SET 类型中不可能包含两个相同的元素。
　　希望从 SET 类型字段中找出非法的记录只需查找包含空字符串或二进制值为 0 的行。

1-5、数据表的存储位置

1-6mysql安装目录

1.bin文件夹：该文件夹下存放着可执行文件
include文件夹：该文件夹下存放着头文件
lib文件夹：该文件夹下存放着库文件
share文件夹：该文件夹下存放着字符集、语言等信息。

2、打开my.ini文件查看几个常用配置参数
通过修改MySQL配置文件来手动配置数据库服务器MySQL时，常用的参数如下。
default-character-set：客户端默认字符集
character-set-server：服务器端默认字符集
port:客户端和服务器端的端口号
default-storage-engine:MySQL默认存储引擎

1-7命令行修改MySQL

1-8系统数据库和用户数据库

1-9 结构化查询语句分类

名称	解释	命令
DDL（数据定义语言)	定义和管理数据对象，如数据库，数据表等change【字段名】moidfy【属性值】	CREATE、DROP、ALTER
DML（数据操作语言）	用于操作数据库对象中所包含的数据	INSERT、UPDATE、DELETE
DQL（数据查询语言）	用于查询数据库数据	SELECT
DCL（数据控制语言）	用来管理数据库的语言，包括管理权限及数据更改	GRANT、COMMIT、ROLLBACK

1-10 int varchar存储数值 与decimal

int(4)，仅设定显示宽度，不影响存储范围，长度超出4照样能存

decimal	【字符串形式】的浮点数	decimal**(m,** d**)**
		前面是总位数，后面是小数，多用于金融防止数据溢出

TIMESTAMP	YYYYMMDDhhmmss****格式表示的时间戳	【1970】10101000000 ~2037****年的某个时刻
YEAR	YYYY****格式的年份值	【1901】~2155

字符型char(4)会限制存储长度，与数值型不同
如果某一项中设置的是varchar(50)那么对英文当然是50
那么对中文呢utf-8的中文占3个字节
那么,这个varchar(50)是不是只能存16个汉字了?

不是这样的，mysql低版本之前是这样的，但是【5.0】以后就不是了
mysql varchar(50) 不管中文 还是英文 都是存50个的，但是一个表中所有varchar字段的总长度跟编码有关，如果是utf-8，那么大概65535/3，如果是gbk，那么大概65535/2.

MySQL5的文档，其中对varchar字段类型这样描述：varchar(m) 变长字符串。M 表示最大列长度。M的范围是0到65,535。(VARCHAR的最大实际长度由最长的行的大小和使用的字符集确定，最大有效长度是65,532字节）。
为何会这般变换？真是感觉MySQL的手册做的太不友好了，因为你要仔细的继续往下读才会发现这段描述：MySQL 5.1遵从标准SQL规范，并且不删除VARCHAR值的尾部空格。VARCHAR保存时用一个字节或两个字节长的前缀+数据。如果VARCHAR列声明的长度大于255，长度前缀是两个字节。
MySQL中varchar最大长度是多少？这不是一个固定的数字。本文简要说明一下限制规则。
1、限制规则
字段的限制在字段定义的时候有以下规则：
a)   存储限制
varchar 字段是将实际内容单独存储在聚簇索引之外，实际存储从第二个字节开始，接着要用1到2个字节表示实际长度（长度超过255时需要2个字节），因此最大长度不能超过65535。
b)   编码长度限制
字符类型若为gbk，每个字符最多占2个字节
字符类型若为utf8，每个字符最多占3个字节
若定义的时候超过上述限制，则varchar字段会被强行转为text类型，并产生warning。
c)   行长度限制
导致实际应用中varchar长度限制的是一个行定义的长度。 MySQL要求一个行的定义长度不能超过65535。若定义的表长度超过这个值，则提示
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs。
2、计算例子
举两个例说明一下实际长度的计算。
a)                  若一个表只有一个varchar类型，如定义为
create table t4(c varchar(N)) charset=gbk;
则此处N的最大值为(65535-1-2)/2= 32766。
减1的原因是实际行存储从第二个字节开始’;
减2的原因是varchar头部的2个字节表示长度;
除2的原因是字符编码是gbk。
b)                  若一个表定义为
create table t4(c int, c2 char(30), c3 varchar(N)) charset=utf8;
则此处N的最大值为 (65535-1-2-4-30*3)/3=21812
减1和减2与上例相同;
减4的原因是int类型的c占4个字节;
减30*3的原因是char(30)占用90个字节，编码是utf8。
如果被varchar超过上述的b规则，被强转成text类型，则每个字段占用定义长度为11字节，当然这已经不是“varchar”了。

CREATE TABLE 表名 ( … ) AUTO_INCREMENT = 100;】
alter table 表名 auto_increment = x ; 如果【x小于现在的值】则修改无效
truncate 表名 ; 直接清空所有数据，下次插入数据时auto_increment字段会自动从0开始。 和delete from tbname不同的是delete只删除数据而不重置auto_incremnet.
SET @@auto_increment_increment = 10; 修改自动增长的步长（全局修改）

1-11可视化数据库命令；操作数据库；操作表【含逆向操作】

1.windows命令net开头，net start mysql;
Mysql 可视化命令行：mysql>

2

在这里插入图片描述

3

4

2.数据库的分类

3.数据库的设计

1>收集信息
与该系统有关人员进行交流、座谈，充分了解用户需求，理解数据库需要完成的任务
2**>【标识】实体** （Entity）
标识数据库要管理的关键对象或实体，实体一般是名词
3>【标识】每个实体的属性（Attribute）
4>【标识】实体之间的关系（Relationship）【映射基数m-n对应关系】

ER图：entitys实体类relationship 关系

用【二维表】的形式表示实体和实体间联系的数据模型即关系模式
E-R图转换为关系模式的步骤

1. 把每个实体都转化为关系模式R（U）形式
2. 建立实体间联系的转换
   酒店管理系统数据库的关系模式是
   客房（客房号、客房描述、客房类型、客房状态、床位数、入住人数、价格）
   客人（客人编号，客人姓名、身份证号、入住日期、结账日期、押金、总金额、客房号）

CTRL+4直线，ctrl+9椭圆

4.数据库规范化

如果数据库没有进行相应的规范设计,虽然在查询数据库可能会比较容易,但有时会造成一些问题,主要的问题如下:

1.信息重复(会造成储存空间的浪费及一些其他问题).【内存大小及访问效率】

2.更新异常(冗余信息不仅浪费空间,还会增加更新的难度).

3.插入异常

4.删除异常(在某些情况下,当删除一行时,可能会丢失有用的信息).

5.三大范式**

范式的目标是确保每列的原子性
第一范式确保每列的原子性
第二范式要求每个表只描述一件事情
第三范式要求表中[各列必须和主键直接相关,形成直接依赖]，不能间接相关【不能时间接传递依赖 】
1.第一范式:1st NF

目标是确保每列的【原子性】.如果每列都是不可再分的【最小数据单元】,则满足第一范式.

2.第二范式:

第二范式在第一范式的基础上更进一层,其目标是确保表中的【每列都和主键相关】,也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中.如果一个关系满足第一范式,并且除了主键以外的其他列都依赖与该主键.则满足第二范式.

3.第三范式:

第三范式在第二范式的基础上更进一层,第三范式的目标是确保【每列都和主键列直接相关,而不是间接相关.】如果一个关系满足第二范式,并且除了主键以外的其他列都这能依赖于主键列,【列和列之间不存在相互依赖关系,】则满足第三范式.

6.规范性和性能的关系

为了满足三大范式,我们的数据操作性能会受到相应的影响,所以,在实际的数据库设计中,既要考虑三大范式,避免【数据的冗余】和各种数据操作异常;有要考虑到数据【访问性能】,有时,为了减少表间连接,提高数据库的访问性能,允许适当的数据冗余列,这可能是最合适的数据库设计方案.

7.数据库模型图

【文件->新建–>软件和数据库—>数据库模型图】

点击关系然后放到子表【子表（外键）（多【位客人】）指向（对一【间房】）主表（主键）】

关系直接拉到子表上中间位置，当子表四周边框变红停止，箭头末尾会出现红色小方格；

点击子表箭头，拉到主表中间，等到主表四周边框变红，释放鼠标，结果如上图所示。【完成了子表指向主表】

8.ER图的映射关系【3种】

不管是一对多还是多对一，都是要先有一个【基准参照】，所以一对多还是多对一，都是以参照物来表示的，通常的方式会采用【从左向右】的角度来判定映射关系

映射基数 1-n; m-n;

用[【二维表】的形式表示实体和实体间联系的【数据模型】即【关系模式】
E-R图转换为关系模式的步骤分以下两部

（1）把每个实体都转化为关系模式R（U）形式。【实体类与各自属性】
客房（客房号、客房描述、客房类型、客房状态、床位数、入住人数、价格）
客人（客人编号，客人姓名、身份证号、入住日期、结账日期、押金、总金额）

(2)建立实现间联系的转换。
实体间的联系分成一对一、一对多、多对多三种，当两个实体各自转化为关系模式后：
① 一对一的转换：把任意实体的主键放到另一个实体的关系模式中。
② 一对多的转换：把联系数量为【1的实体的主键】放到联系数量为N的实体关系模式中。
3》多对多的转换：把两个实体中的主键和联系的属性放到另一个关系模式中，注意多生成一个关系模式。
客房（客房号、客房描述、客房类型、客房状态、床位数、入住人数、价格）
客人（客人编号，客人姓名、身份证号、入住日期、结账日期、押金、总金额、客房号）。