MySQL 数据库学习笔记 2

 

MySQL 数据库学习笔记 2

标签： 数据库mysql

目录(?)[+]

1. 字符集

1.1 字符集变量

show variables; 
show variables like ‘character_set%’; 显示所有和字符集相关的变量 
show create database db_name; 显示数据库创建的字符集 
 
windows 中 的my.ini 和 linux 中的 my.conf 中 设置了 MySQL server 端的编码格式

1.2 校对规则

校对规则是指， 在当前编码格式下， 字符之间的比较顺序。 
1. ci : 不区分大小写 
2. cs: 区分大小写 
3. bin: 二进制比较， 默认区分

可以使用 show collation; 来查看字符集 
并且字符集一般都是 按照 : 字符集_地区名_比较规则 构成的。 
show collation like ‘utf8%’; 显示和utf 相关的字符集 
 
在创建表格的时候， 可以同时制定相应表格的字符集和相应的校对规则： 
create table tbl_name（id int） character set gbk collate gbk_chinese_ci; 

在我们创建完一个数据库后， 硬盘上会创建相应的 db.opt 文件， 里面有 数据库的基本信息， 其中就包含数据库所使用的字符集和 校对规则。 

另外， 校对规则一般对数据的存储没什么影响， 但是对涉及到 排序操作 order by 的时候， 会有影响。

2. 列类型

列类型主要可以分为 3 类： 
1. 数值类型 
2. 日期时间类型 
3. 字符串类型

2.1 数值类型

2.1.1 整数类型

1. tinyint ， smallint, mediumint, int, bigint, 分别 占据 1， 2， 3， 4， 8 个字节
2. 和 C/C++ 一样， 整数类型是区分有符号和 无符号的， 用unsigned 区分。
3. TYPE(M) , 这里 M 一般用来表示显示的最小宽度
4. 使用 前导零填充 zerofill

2.1.2 小数类型

2.1.2.1 浮点数

容易造成精度丢失的问题。 
float 一般 6 ~ 7 有效位数， 4 字节 
double 一般 16 ~ 17 有效位数， 8字节

TYPE(M, N) 可以用来控制表示范围， 其中, M 表示所有数字的位数， 不包含小数点和正负号。 而 N 表示所允许的小数部分的位数， 在确定了 M, N 之后， M-N 就确定了 整数部分的最大的位数。

2.1.2.2 定点数

decimal(M, N), M 表示所有有效数字的位数， N 为 小数部分的位数 
默认情况下， M ： 10， N ： 0 
他能保证精度不会丢失， 支持zerofill 
需要注意的是， 他是一个变长的类型。

2.2 日期和时间类型

1. Datetime 年月日时分秒， 8字节， 分割符可以随意
2. timestamp 时间戳类型， 4 字节， 按整数类型存储， 但是按照Datetime 类型表示
3. Date 年月日 3字节
4. time 时分秒 3字节 既可以表示1天中的某个时刻， 也可以表示35 天以内的时间间隔
5. year 年 1字节 1901 ~ 2155

ps： 检索的时候， xxx + 0 表示用他实际存储的类型表示

2.3 字符串类型

1. char(M) 固定长度， M 表示允许的字符串的长度(字符数)， 物理上， 最大字节数 255
2. varchar(M) 变长， 当字符数大于 255 时候， 需要2 个额外字节保存字符串长度， 小于 255 时候， 只要 1 个额外字节。
3. 字段的最大长度限制， 和 类型本身限制 以及 row 长度限制 有关联。
4. 真实 varchar 的长度讨论， 
   在一个表中， row 一般不超过 65535， 而 varchar 超过255时候， varchar 自身占据 2 个字节， 另外如果表中有允许null 属性的字段， 需要额外 1 个字节进行记录 null 的信息， 此时对结构：

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">create</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">table</span> tbl_name(
    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">varchar</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">65532</span>)
) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">character</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">set</span> latin1;</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li></ul>

这是成立的， 并且varchar 最大最大也就只能分配这么多给数据了。 
5. text， tinytext，mediumtext, longtext， 它相当于指定了最大长度的varchar。 
6. enum 枚举类型， 内部是整形 
7. set 集合类型， 8字节， 最多64 个状态， 每个状态使用一个bit 表示 
8. binary， varbinary, blob 二进制数据 字节表示的。

3. 列属性

1. not null
2. default
3. primary key 主键， 通常主键不应该与实体有联系， 他是代表记录唯一的索引。 
   2 中定义方法： 
   
   • type_name type [primary key]
   • primary key(key) 这种方法， 较上面这种可以定义组合主键 ： primary key(a, b), ie, 一个主键上面包含了 a, b 两个字段， 然而， 一个表韩式只有一个主键
4. auto_increment， 主动增长， 要求 整形数据， 带有索引。 在插入数据的时候， 可以插入null 或者 不插入该项数据。 
   另外， 我们可以设置表的默认自动增长的起始值， 默认为 1， 但是如果设置的值， 比我们当前的最大值 小的话， 是无效的。
5. 外键约束
6. 数据库中的相应约束：

唯一性	主键	外键	检查约束	空值	默认值
unique	primary key	foreign key	check	not null	default

4. 实体之间的关系

4.1 1: 1 实体关联

通常用在数据库优化的过程中， 将一个较大的表拆成两个部分， 一个部分是常用信息实体， 另一个部分是详细但不常用的信息实体（垂直分割） 
在数据库中， 通常采用 在两个实体表内存放相同主键字段来实现这种关联

4.2 1：n 实体关联

通常采用在较多对象那一侧添加一个字段的方式， 用来存储另一个实体的标识信息（主键） 
ex. 一个班级对应多个学生

4.3 M：N 实体关联

通常采用一个中间表， 记录实体之间的对应关系。 
本质上， 就是将 M ： N 实体关联拆解成 两个 1： N 实体关联。 
ex. 班级 和 讲师之间的关系