MySQL数据库学习笔记

今日内容概要

• 字符编码与配置文件
• 数据库存储引擎
• 创建表的完整语法
• MySQL字段类型
• MySQL字段约束

今日内容详细

字符编码与配置文件

查看数据库基本信息(用户，字符编码)的命令是：\s

mysql版本为5.x系列的显示的编码有多种，如果是8.X系列，显示的统一是utf8mb4（utf8mb4是utf8优化版本，支持存储表情）

统一字符编码

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

步骤一:进入mysql的目录下找到my-default.ini配置文件，拷贝一份该配置文件并修改名称为my.ini。

步骤二:清空my.ini文件的内容，添加下面固定的配置信息即可。

[mysqld]
    character-set-server=utf8
    collation-server=utf8_general_ci
[client]
    default-character-set=utf8
[mysql]
    default-character-set=utf8

步骤三:保存并重启服务端即可生效，然后可以通过\s查看编码是否修改成功。

ps:偷懒操作 在配置文件中的mysql下提前写好用户名和密码 之后直接mysql登录

数据库存储引擎

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

查看所有的存储引擎的命令: show engines;

需要掌握的存储引擎

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

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

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

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;

innodb
    .frm  表结构
    .ibd  表数据，表索引(加快数据查询)
myisam
    .frm  表结构
    .MYD  表数据
    .NYI  表数据(加快数据查询)
memory
    .frm  表结构
blackhole
    .frm  表结构

创建表的完整语法

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

注意:

1.字段名和字段类型是必须的(至少写一个)

2.数字跟约束条件是可选的(可有可无)

3.约束条件可以写多个，空格隔开即可

4.最后一个字段的结尾千万不能加逗号

MySQL字段类型

字段类型之整型

整数类型	字节	有负号范围	无负号范围
tinyint	1bytes	-128~127	0-255
smallint	2bytes	-32768~32767	0~65535
int	4bytes	-2147483648~2147483647	0~4294967295
bigint	8bytes	-263~263-1	0~2^64-1

上述整型的区别在于从上往下能够存储的数字范围越来越大。

1.验证整型是否自带负号
    create table t5(id tinyint);
    insert into t5 values(-129),(256);
    ps:发现自动填写为两个边界值，数据失真，没有实际意义
    上述所有的整型类型默认都会带有负号

2.自定义移除负号
    '''unsigned 约束条件之一 意思是不需要负号'''
    create table t6(id tinyint unsigned);
    insert into t6 values(-129),(256);

注意事项

1.需要考虑正负数的问题 如果需要存储负数 则需要占据一个比特位
2.注意手机号如果使用整型来存储 需要使用bigint才可以
数字的含义:
整形字段括号里的数字并不是用来限制长度，而是用来控制展示长度。
create table t7(id int(3));
insert into t7 values(1111);  # 不会报错

create table t8(id int(3) zerofill);  # 位数不够用0填充
insert into t8 values(1);  # 001
insert into t8 values(1111);  # 有几位就展示几位
结论：以后涉及到整型字段 都无需自己定义长度 直接使用自带的即可，而针对其他类型的字段 则需要自己添加数字

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

方式1：命令临时修改
    set session sql_mode='strict_trans_tables' # 当前客户端操作界面有效
    set global sql_mode='STRICT_TRANS_TABLES' # 服务端不重启永久有效
方式2：配置文件永久修改
    步骤1：找到配置文件my.ini并打开
    步骤2：在[mysqld]下面添加
    sql_mode='STRICT_TRANS_TABLES'
    步骤3：重启服务端永久生效

字段类型之浮点型

浮点型	使用方式	说明
float	float(255,30)	总共255位，小数占30位
double	double(255,30)	总共255位，小数占30位
decimal	decimal(65,30)	总共65位，小数占30位

验证精确度问题
    create table t9(id float(255,30));
    create table t10(id double(255,30));
    create table t11(id decimal(65,30));
    insert into t9 values(1.11111111111111111111111111111);
    insert into t10 values(1.11111111111111111111111111111);
    insert into t11 values(1.11111111111111111111111111111);

精确度结论： decimal>double>float。虽然三者精确度有差距，一般情况下float足够使用了，如果想追求非常完美的精确度，可以使用字符串来代替。

字段类型之字符类型

字符类型	使用方式	说明	优点	缺点
char(定长)	char(3)	最大只能存储三个字符，如果超过范围则直接报错，如果不超过范围则用空格填充至三个字符	整存整取，速度快	浪费存储空间
varchar(变长)	varchar(3)	最大只能存储三个字符，如果超过范围则直接报错，如果不超过范围则有几位就存几位	节省存储空间	存取数据的速度慢于char

验证两者的区别
    create table t12(id int, name char(4));
    create table t13(id int, name varchar(4));
1.结果验证，超出范围两者都会报错
    注意sql_mode='strict_trans_tables'
2.验证定长和变长特性
    char_length() # 统计字段数据的长度

"""
默认情况下char在存储的时候针对没有满足固定位数的字符会自动填充空格
然后在读取的时候又会自动将填充的空格移除，如果想取消该机制需要sql_mode
   set global sql_mode='strict_trans_tables,pad_char_to_full_length';
上诉目录是替换，不是新增，所以之前的配置也要写上
"""

注意：

varchar在存数据的时候会生成一个1bytes的报头，记录数据长度；varchar在取数据的时候先会读取1bytes的报头，从中获取真实数据长度。

工作中使用char还是varchar？注意根据使用场景决定使用哪个。比如:针对统一中国人的姓名应该采取那个类型varchar，规模较小，数据量相对固定的字典采用char。

字段类型之枚举与集合

枚举(enum)：多选一

create table t14(
    id int,
    name varchar(32),
    gender enum('male','female','others')
  );
insert into t14 value(1,'tom','男');  # ERROR 1265 (01000): Data truncated for column 'gender' at row 1
'''插入数据的时候 针对gender只能填写提前定义好的数值'''

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

create table t15(
    id int,
    name varchar(32),
    hobbies set('read','run','rap')
);
insert into t15 value(1,'tom','rap');
insert into t15 value(1,'tom','rap,read,run');

字段类型之日期类型

日期类型	格式	示例
date	年月日	2022-08-15
datetime	年月日时分秒	2022-08-15 19:27:32
time	时分秒	19:27:38
year	年份	2022

create table t16(
    id int,
    name varchar(32),
    birth date,
    reg_time datetime,
    study_time time,
    join_time year
);
insert into t16 values(1,'jason','2022-08-15','2022-08-15 19:27:32','19:27:38','2022')

字段约束条件

insert into 表名 vlaues() # 默认按照创建表的字段顺序添加
insert into 表名(字段) vlaues() # 可以自定义字段顺序

1.无需正负号---unsigned

create table t17(id tinyint unsigned);
insert into t17 value(-1);  # ERROR 1264 (22003): Out of range value for column 'id' at row 1

2.零填充---zerofill

create table t18(id int(3) zerofill);  # 位数不够用0填充
insert into t18 values(1);  # 001

3.非空---not null

create table t19(id int,name varchar(32));
'''插入数据的另外一种方式 打破字段顺序'''
insert into t19(name,id) values('jason',1);
insert into t19(id) values(2);

create table t20(id int,name varchar(32) not null);
insert into t20(id) values(2);  # 报错
insert into t20(id,name) values(2,null);  # 报错
insert into t20(id,name) values(2,'');  # 不报错

4.默认值---default

create table t21(id int,name varchar(32) default 'jason');
insert into t21(id) values(1);  # 不传值使用默认的值
insert into t21(id,name) values(2,'kevin');  # 传值则使用传入的值

5.唯一值---unique

'''单列唯一:某个字段下对应的数据不能重复 是唯一的'''
create table t22(
    id int unique,
    name varchar(32)
  );
insert into t22 value(1,'tom');
insert into t22 value(1,'bob');  # ERROR 1062 (23000): Duplicate entry '1' for key 'id'
 '''联合唯一:多个字段下对应的数据组合到一起的结果不能重复 是唯一的'''
create table t23(
    id int,
    host varchar(32),
    port int,
    unique(host,port)
  );