Day 44 MySQL

1、字符编码与配置文件

查看数据库基本信息(用户、字符编码)
\s

my-default.ini  # windows下MySQL默认的配置文件
#拷贝上述文件并且重命名为my.ini
1
2

由于5.6版本编码不统一 会造成乱码 我们需要统一修改>>>:utf8

添加字符编码相关的配置(百度查看即可)

[mysqld]
   character-set-server=utf8
   collation-server=utf8_general_ci
[client]
   default-character-set=utf8
[mysql]
   default-character-set=utf8  
1
2
3
4
5
6
7

• 注意如果配置文件涉及到了mysqld相关的配置修改 那么需要重启服务端才可以生效

偷懒小技巧
在配置文件中的mysql下提前写好用户名和密码 之后直接mysql登录

2、存储引擎

我们可以简单的理解为存储引擎就是针对相同的数据采用的不同的存取策略

show engines;  # 查看所有的存储引擎
1

需要掌握的存储引擎

1. MyISAM
   MySQL5.5及之前版本默认的存储引擎
   存取数据的速度快 但是功能较少 安全性较低
2. InnoDB
   MySQL5.5之后版本默认的存储引擎
   存取数据的速度没有MyISAM快 但是支持事务、行锁、外键等诸多功能 安全性较高
3. Memory
   基于内存的存储引擎 存取数据极快 但是断电立刻丢失
4. BlackHole
   黑洞 任何写进去的数据都会立刻丢失 类似于垃圾站

ps:MySQL中默认是大小写不敏感的(忽略大小写)

windows cmd终端鼠标右键的意思就是粘贴

不同存储引擎之间底层文件的区别

create table t1(id int) engine=innodb;
create table t2(id int) engine=myisam;
create table t3(id int) engine=memory;
create table t4(id int) engine=blackhole;
1
2
3
4

各文件作用

InnoDB

• frm 表结构
• ibd 表数据、表索引(加快数据查询)

MyISAM

• frm 表结构
• MYD 表数据
• MYI 表索引(加快数据查询)

Memory

• frm 表结构

Black Hole

• frm 表结构

3、创建表的完整语法

create table 表名(
	字段1 字段类型(数字) 约束条件,
    字段名2 字段类型(数字) 约束条件,
    字段名3 字段类型(数字) 约束条件
)engine=存储引擎；
1
2
3
4
5

• 字段名和字段类型是必须的(至少写一个)
• 数字跟约束条件是可选的(可有可无)
• 约束条件可以写多个 空格隔开即可
• 最后一个字段的结尾千万不能加逗号

3.1、字段类型之整型

验证整型是否自带负号

create table t6(id tinyint);
insert into t6 values(-129),(256);  #发现自动填写为两个边界值 数据失真 没有实际意义
#上述所有的整型类型默认都会带有负号
1
2
3

自定义移除负号

unsigned 约束条件之一 意思是不需要负号
create table t7(id tinyint unsigned);
insert into t7 values(-129),(256);
1
2
3

插入的数据值超出了数据类型的范围 不应该让其插入并自动修改 没有意义
数据库应该直接报错(这个特性其实是有的 只是被我们改了>>>:配置文件)

方式一：命令临时修改

set session sql_mode='strict_trans_tables'  当前客户端操作界面有效
set global sql_mode='STRICT_TRANS_TABLES'  服务端不重启永久有效
1
2

方式二：配置文件永久修改

[mysqld]
		sql_mode='STRICT_TRANS_TABLES'
1
2

3.2、字段类型之浮点型

使用方式

float(255,30)  # 第一个数表示总共多少位 第二个数表示小数占多少位
double(255,30) # 第一个数表示总共多少位 第二个数表示小数占多少位
decimal(65,30) # 第一个数表示总共多少位 第二个数表示小数占多少位
1
2
3

验证精确度问题

create table t8(id float(255,30));
 	create table t9(id double(255,30));
 	create table t10(id decimal(65,30));
 	insert into t8 values(1.111111111111111111111111111);
	insert into t9 values(1.111111111111111111111111111);
 	insert into t10 values(1.111111111111111111111111111);
1
2
3
4
5
6

float < double < decimal
一般情况下float足够使用了 如果想追求非常完美的精确度 可以使用字符串来代替

3.3、字段类型之字符类型

char（4）
最大只能存储四个字符 如果超过范围 则直接报错
如果不能超出范围 则用空格填充 至四个字符

varchar（4）
最大只能存储四个字符 如果超过范围则直接报错
如果不超出范围 则有几位就存几位

储备知识

show variables like '%mode%'; 3查看数据库配置中 变量名包含mode的配置参数
1

验证两者区别

create table t11(id int, name char(4));
create table t12(id int, name varchar(4));
1
2

结果验证 超出范围两者都会报错
注意 报错 摇配置文件内有报错机制sql_mode='strict_trans_tables'

验证定长和变长特性

char_length()  # 统计字段数据的长度
1

默认情况下char在存储的时候针对没有满足固定位数的字符会自动填充空格
然后在读取的时候又会自动将填充的空格移除 如果想取消该机制 需要sql_mode

set global sql_mode='strict_trans_tables,pad_char_to_full_length';
上述目录是替换 不是新增 所以之前的配置也要写上
1
2

char VS varchar

• char
  整存整取 速度快
  浪费存储空间

• Varchar

  节省存储空间
  存取数据的速度慢于char

char(4)
通过空格 将数据 填充至四个字符 存入 取得时候直接四个字符四个字符方便取
a son jacktom lili

varchar(4)
存取数据都需要操作报头(耗时)
1bytes+a1bytes+son1bytes+jack1bytes+tom

存储人得姓名>>>>:varchar
两者使用频率都很高 现在默认很多时候是varchar

4、数字得含义

数字大部分情况下都是用来限制字段的存储长度 但是整型除外
整型不是用来限制存储的长度 而是展示的长度

create table t13(id int(3)); 
create table t14(id int(3) zerofill);
1
2

总结
以后涉及到整型字段的定义 类型后面不需要加括号写数字 除非有业务需求必须固定位数

查看数据：
001
000012345

5、字段类型之枚举与集合

枚举
多选一：enum

• 性别（男 女 其他）

create table t15(
	id int,
    name varchar(32),
    gender enum('male','female','others')
);
1
2
3
4
5

集合
多选多(包含多选一)：set

• 爱好（唱 跳 rap 篮球）

create table t16(
	id int,
    name varchar(32),
    hobbies set('read','run','music','rap')
);
1
2
3
4
5

6、字段类型之日起类型

create table t17(
	id int,
	name varchar(32),
 	birth date,
  	reg_time datetime,
 	study_time time,
 	join_time year
);

>insert into t17 values(1,'kk','2022-8-15','2022-8-15 15:15:15','15:15:15',2022)
1
2
3
4
5
6
7
8
9
10

7、字段约束条件

可以自定义字段顺序

insert into 表名(字段2,字段1) vlaues()
1

无负号
unsigned

create table t(id int unsigned); # 不能添加负数
1

零填充
zerofill

create table t(id int zerofill);  #填入得数据 展示会自动被零填充至 展示得长度
1

非空
not null

create table t(name varchar(32) not null); #不能不填如入数据
1

默认值
default

create table t(gender varchar(32) default 'male') #数据默认为male
1

唯一值
unique

create table t(id int unique); #单列唯一 出现重复添加报错

create table t2(host varchar,port int,unique) #创建 联合唯一
#当 host 和 port 合并 出现重复时 添加报错 
1
2
3
4