MySQL数据库学习day2

回顾

数据库基础知识：关系型数据库（磁盘）和非关系型数据库（内存）

关系型数据库：建立在关系模型上的数据库
数据结构：二维表（比较浪费空间）
操作数据的指令集合：SQL（DDL， DML[DQL]， DCL）
完整性约束：表内和表之间（实体）

Mysql关系型数据库：c/s结构软件（连接认证， 发送SQL指令， 服务器处理指令返回结果， 客户端接收结果解析结果）

Mysq服务端对象：DBMS -> Database -> Table -> fields

SQL基本操作：库操作，表操作（字段），数据操作

字符集问题：中文数据问题
改变服务器接收数据的字符集：character_set_client
改变服务器返回数据的字符集：character_set_results
快捷方式：set names 字符集（三件事情）

校对集问题：比较规则：_bin，_cs，_ci（利用排序实现）

web乱码问题：浏览器解析，php处理（本地文件），数据库处理

数据类型（列类型）

所谓的数据类型：对数据进行统一的分类，从系统的角度出发为了能够使用统一的方式进行管理：更好的利用有限的空间
SQL中将数据类型分成了三大类：数值类型、字符串类型和时间日期类型

数值型：

数据型数据：都是数值
系统将数值型分为整数型和小数型

整数型：

存放整型数据：在SQL中因为更多要考虑如何节省磁盘空间，所以系统将整型又细分成了5类：

Tinyint：迷你整型，使用一个字节存储，表示的状态最多为256中（常用）
Smallint：小整型，使用2个字节存储，表示的状态最多为65536种
Mediumint：中整型，使用3个字节存储
Int：标准整型，使用4个字节存储（常用）
Bigint：大整型，使用8个字节存储

创建一张整型表：

插入数据：只能插入整型，只能插入范围内的整型

SQL中的数据类型全部都是默认有符号：分正负
有时候需要使用无符号数据：需要给数据类型限定：int unsigned，无符号：从0开始
给表增加一个无符号类型;

数据的插入

查看表结构的时候，发现每个字段的数据类型之后都会自带一个括号，里面有指定的数字

显示宽度：没有特别的含义，只是默认的告诉用户可以显示的形式而已：实际上用户是可以控制的，这种控制不会改变数据本身的大小

显示宽度的意义：在于当数据不够显示宽度的时候，会自动让数据变成对应的显示宽度：通常需要搭配一个前导0来增加宽度，不改变值大小：zerofill（零填充）：零填充导致数值自动变成无符号

零填充+显示效果

零填充的意义（显示宽度）：保证数据格式

小数型

小数型：带有小数点或者范围超出整型的数值类型。
SQL中：将小数型细分为两种：浮点型和定点型
浮点型：小数点浮动，精度有限，而且丢失精度
定点型：小数点固定，精度固定，不会丢失精度

浮点型

浮点型数据是一种精度型数据：因为超出指定范围之后，会丢失精度（自动四舍五入）
浮点型：理论分为两种精度
Float：单精度，占用4个字节存储数据，精度范围大概为7位左右
Double：双精度，占用8个字节存储数据，精度范围大概为15位左右

创建浮点数表：浮点的使用方式：直接float表示没有小数部分，float(M, D)M代表总长度，D代表小数部分长度

插入数据：可以是直接小数，也可以是科学计数法

浮点数数据的插入：整型部分是不能超出长度的，但是小数部分可以超出长度（系统会自动四舍五入）

结果：浮点数一定会进行四舍五入（超出精度范围）：浮点数如果是因为系统进位导致整数部分超出指定长度，那么系统也会允许成立

定点型

定点型：绝对的保证整数部分不会被四舍五入（不会丢失精度），小数部分有可能（理论上小数部分也不会丢失精度）

创建定点数表，增加一个浮点数作为对比

插入数据：定点数的整数部分一定不能超过长度（进位不可以），小数部分长度可以随意超出（系统自动四舍五入）

浮点数如果进位导致长度溢出没有问题，但是定点数不行

查看数据效果：

时间日期类型

Datetime：时间日期，格式是YYYY-mm-dd HH:ii:ss，表示的范围是从1000到9999年，有0值，0000-00-00 00:00:00
Date：日期，就是datetime中的date部分
Time：时间（段），指定的某个区间之间，-时间到+时间
Timestamp：时间戳，并不是时间戳，只是从1970年开始的YYYY-mm-dd HH:ii:ss格式与datetime完全一致
Year：年份，两种形式year(2)和year(4)：1901—2155

创建时间日期表：

插入数据：时间time可以是负数，而且可以是很大的负数，year可以使用2位数插入，也可以使用4位数

timestamp字段：只要当前所在的记录被更新，该字段一定 会自动更新成当前时间

网站是以PHP为实现的主要操作对象：PHP中有非常强大的时间日期处理函数：date，只需要一个时间戳
就可以转换成任意类型的时间：以PHP为主的时候，都是在数据库使用时间戳(整型)来存储时间

字符串类型

在SQL中，将字符串类型分成了6类：char，varchar，text，blob，enum和set

定长字符串

定长字符串：char，磁盘(二维表)在定义结构的时候，就已经确定了最终数据的存储长度。

Char(L)：L代表length，可以存储的长度，单位为字符，最大长度值为255.
Char(4)：在utf8环境下，需要4*3=12个字节。

变长字符串

变长字符串：varchar，在分配空间的时候，按照最大的空间分配：但是实际上最终用了多少，是根据
具体的数据来确定。
Varchar(L)：L表示字符长度，理论长度是65536个字符，但是会多出1到2个字节来确定存储的实际长度。
但是实际上如果长度超过255，既不用定长也不用变长，使用字符串text。

Varchar(10)：的确存了10个汉字，utf8环境，10*3+1=31个字节，小于255多出一个字节，大于255多出两个字节。

实际存储长度	Char(4)	Varchar(4)	Char占用字节	Varchar占用字节
ABCD	ABCD	ABCD	4 * 3 = 12	4 * 3 + 1 = 13
A	A	A	4 * 3 = 12	1 * 3 + 1 = 4
ABCDE	×	×	数据超过长度	数据超过长度

定长与变长的存储实际空间(utf8)

实际存储长度	Char(4)	Varchar(4)	Char占用字节	Varchar占用字节
ABCD	ABCD	ABCD	4 * 3 = 12	4 * 3 + 1 = 13
A	A	A	4 * 3 = 12	1 * 3 + 1 = 4
ABCDE	×	×	数据超过长度	数据超过长度

如何选择定长或者变长字符串呢？

定长的磁盘空间比较浪费，但是效率高：如果数据基本上确定长度都一样，就是使用定长，
如 身份证、手机号码

变长的磁盘空间比较节省，但是效率低：如果数据不能确定长度(不同数据有变化)，如姓名、地址

文本字符串

如果数据量非常大，通常说超过255个字符串就会使用文本字符串

文本字符串根据存储的数据的格式进行分类：text和blob

text：存储文字(二进制数据实际上都是存储路径)

blob：存储二进制数据(通常不用)

枚举

枚举：enum，事先将所有可能出现的结果都设计好，实际上存储的数据必须是规定好的数据中的一个

枚举的使用方式
定义：enum(可能出现的元素列表); 如enum('男', '女', '保密')
使用：存储数据，只能存储上面定义好的数据

创建枚举表

加入数据：作用之一：规范数据格式：数据只能是规定数据中的其中一个

作用之二：节省存储空间(枚举通常有一个别名：单选框)，枚举实际存储的是数值而不是字符串本身

在mysql中，系统也是自动转换数据格式的：而且基本与PHP一样(尤其是字符串转数字)

证明字段存储的数据是数值：将数据取出来+0就可以判断出原来的数据存的到底是字符串还是数值：如果
是字符串最终结果永远为0，否则就是其他值

找出了枚举元素的实际规律，按照元素出现的顺序，从1开始编号

枚举原理：枚举在进行数据规范的时候(定义的时候),系统会自动建立一个数字与枚举元素的对应关系(关系放到日志中):
然后在进行数据插入的时候，系统自动将字符串转换成对应的数字存储，然后在进行数据提取的时候，系统自动将数值转换成
对应的字符串显示。

因为枚举实际存储的是数值，所以可以直接插入数值

集合字符串

集合跟枚举很类似：实际存储的是数值，而不是字符串(集合是多选)

集合使用方式：

定义：set(元素列表)
使用：可以使用元素列表中的元素(多个)，使用逗号分隔

创建集合表

插入数据：可以使用多个元素字符串组合，也可以直接插入数值

查看数据：数据 + 0查看

集合中：每一个元素都是对应一个二进制位，被选中为1，没有则为0，最后反过来。

集合中的顺序没有关系：最终系统都会去匹配顺序

集合的强大在于能够规范数据和节省空间：PHP也可以规范数据，但是对于PHP来说效率优先，
而且数据的维护可以通过数字进行，增加PHP的维护成本：PHP根本没有办法判断数据在数据库
的形式。

Mysql记录长度

Mysql中规定： 任何一条记录最长不能超过65535个字节。(varchar永远达不到理论值)。

varchar的实际存储长度能达到多少呢？看字符集编码。

utf8下varchar的实际顶配：21844字符

gbk下varchar的实际顶配：32766字符

想利用完整个65535字节长度：增加一个tinyint字段即可。

mysql记录中，如果有任何一个字段允许为空，那么系统会自动从整个记录中保留一个字节来存储NULL
若想释放NULL所占用的字节：必须保证所有的字段都不允许为空

Mysql中text文本字符串，不占用记录长度：额外存储。但是text文本字符串也是属于记录的一部分
一定需要占据记录中的部分长度：10个字节。(保存数据的地址和长度)

列属性

列属性：真正约束字段的是数据类型，但是数据类型的约束很单一。需要有一些额外的约束，
来更加保证数据的合法性。

列属性有很多：NULL/NOT NULL，default，Primary key，unique key， auto_increment，comment

空属性

两个值：NULL(默认的)和NOT NULL(不为空)

虽然是默认的，数据库基本都是字段为空，但是实际上在真实开发的时候，尽可能的要保证
所有的数据都不应该为空：空数据没有意义；空数据没有办法参与运算。

创建一个实际案例表：班级表(名字，教室)

列描述

列描述：comment，描述，没有实际含义：是专门用来描述字段，会根据表创建语句保存：
用来给程序员(数据库管理员)来了解的。

默认值

默认值：某一种数据会经常性的出现某个具体的值，可以在一开始就指定好。
在需要真实数据的时候，用户可以选择性的使用默认值。

默认值的生效：插入数据的时候不设置即可

想要使用默认值，可以不一定去指定这个列表，故意不使用字段列表：可以使用default关键字代替值

day 3