mysql表操作
字符编码与配置文件介绍
查看MySQL默认字符编码
\s
注意：不同的版本默认编码不同
例如：
| 版本系列 | 默认编码 |
|---|---|
| 5.X系列 | 默认的编码有多种（latin1，gbk） |
| 8.X系列 | 默认编码统一是utf8mb4 |
| utf8mb4是utf8优化版本，支持存储表情 |
统一字符编码
5.X默认编码有多种，可能会导致乱码，最好统一编码
修改文件：my-default.ini配置文件
步骤1
拷贝一份my-default.ini配置文件并修改名称为my.ini
步骤2
清空my.ini文件内的内容
步骤3
添加固定的配置信息
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
步骤4
保存并重启服务端即可生效
net stop mysql
net start mysql
存储引擎
什么是存储引擎
MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，能够获得额外的速度或者功能，从而改善应用的整体功能。
存储引擎的分类
MyISAM： 拥有较高的插入，查询速度，但不支持事务
BlackHole ：黑洞引擎，写入的任何数据都会消失，一般用于记录binlog做复制的中继
InnoDB ：5.5版本后Mysql的默认数据库，事务型数据库的首选引擎，支持ACID事务，支持行级锁定
BDB： 源自Berkeley DB，事务型数据库的另一种选择，支持COMMIT和ROLLBACK等其他事务特性
Memory ：所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在Mysql重新启动时丢失
Merge ：将一定数量的MyISAM表联合而成一个整体，在超大规模数据存储时很有用
Archive ：非常适合存储大量的独立的，作为历史记录的数据。因为它们不经常被读取。Archive拥有高效的插入速度，但其对查询的支持相对较差
Federated： 将不同的Mysql服务器联合起来，逻辑上组成一个完整的数据库。非常适合分布式应用
Cluster/NDB ：高冗余的存储引擎，用多台数据机器联合提供服务以提高整体性能和安全性。适合数据量大，安全和性能要求高的应用
CSV： 逻辑上由逗号分割数据的存储引擎。它会在数据库子目录里为每个数据表创建一个.CSV文件。这是一种普通文本文件，每个数据行占用一个文本行。CSV存储引擎不支持索引。
查看存储引擎
show engines;
需要了解的引擎
MyISAM
5.1之前版本MySQL默认的存储引擎
特点:存取数据的速度快，但功能较少，安全性较低
InnoDB
5.1之后版本MySQL默认的存储引擎
特点:功能较多，安全性较高，存取速度比MyISAM慢
BlackHole
黑洞引擎，写入的任何数据都会消失，一般用于记录binlog做复制的中继
Memory
所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在Mysql重新启动时丢失
自定义选择存储引擎
create table t1(id int)engine=myisam;
create table t2(id int)engine=innodb;
create table t3(id int)engine=blackhole;
create table t4(id int)engine=memory;
create table t5(id int)engine=archive ;
创建表的完整语法
create table 表名(
字段名1 字段类型(数字) 约束条件,
字段名2 字段类型(数字) 约束条件,
字段名3 字段类型(数字) 约束条件
);
注意事项：
- 字段名和字段类型必不可少
- 数字和约束条件可放空
- 约束条件可以有多个，需要空格隔开
例如：字段名1 字段类型(数字) 约束条件1 约束条件2 约束条件3 - 最后一行字段结尾不能有逗号，出现会报错
MySQL字段类型
字段类型之整型
整型的四种表示方式
| 表示方式 | 字节数 |
|---|---|
| tinyint | 1bytes |
| smallint | 2bytes |
| int | 4bytes |
| bigint | 8bytes |
注意事项：
- 需要考虑正负数的问题，存储负数需要占据一个比特位
- 注意手机号如果使用整型来存储，需要使用bigint
小技巧:有时候看似需要使用数字类型存储的数据其实可能使用的是字符串，优势在于字符串可以解决不同语言对数字不精确的缺陷
数字超出范围的情况
create table t6(id tinyint);
insert into t6 values(-129);
insert into t6 values(128);
在5.6版本
不会报错，会自动处理成最大范围(不合理，改变了输入的值)
5.6版本取消自动处理
步骤1:set global sql_mode = 'STRICT_TRANS_TABLES';
步骤2:退出客户端（关闭cmd窗口），重新登录即可
在5.7及以上版本
会直接报错(更加合理)
取消整型默认正负号
验证发现所有的整型都默认带有正负号，可以添加约束条件取消整型默认正负号
create table t7(id tinyint unsigned);
字段类型之浮点型
浮点型的三种表现形式
float
double
decimal
上述浮点型从上往下精确度越来越高
| 表现形式 | 括号内参数的含义 |
|---|---|
| float(255,30) | 总共255位，小数位占30位 |
| double(255,30) | 总共255位，小数位占30位 |
| decimal(65,30) | 总共65位，小数位占30位 |
检验精确度
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(8.88888888888888888888);
insert into t9 values(8.88888888888888888888);
insert into t10 values(8.88888888888888888888);
结果为：decimal精确度>double精确度>float精确度
字段类型之字符类型
字符类型两种表现形式
char
varchar
char和varchar区别在前者是定长，后者是变长
例如
char(5)
最大只能存储五个字符，超出则报错，不够五个字符则空格填充
varchar(5)
最大只能存储五个字符，超出则报错，不够五个字符有几个存几个
验证定长与变长特性
create table t11(name char(5));
create table t12(name varchar(5));
insert into t11 values('pierce');
insert into t12 values('pierce');
如果是5.6版本并且没修改严格模式，则会自动截取四个字符(不合理，改变了输入的值)
临时修改
步骤1:set global sql_mode = 'STRICT_TRANS_TABLES';
步骤2:退出客户端，重新登录即可
永久修改
修改my.ini配置文件
sql_mode = 'STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY'
重启服务端之后永久生效
验证空格填充
char_length() 获取字段数据的长度
注意：该方法无法直接获取到定长的真实长度 ，因为MySQL在存数据的时候会自动填充空格在取数据的时候又会自动移除空格
让MySQL在取数据的时候不自动移除空格（单次修改）
set session sql_mode = 'pad_char_to_full_length'
char与varchar的优缺点
| 字符类型关键字 | 优点 | 缺点 |
|---|---|---|
| char | 整存整取，速度快 | 会造成存储空间浪费 |
| varchar | 节省存储空间 | 存取数据的速度没有char快(取数据不知道数据的精确长度) |
字段类型括号内数字的含义
字段类型括号内的数字大部分情况下是用来限制存储的长度
但是在整型中并不是用来限制长度，而是用来控制展示长度
create table t13(id int(5));
insert into t13 values(111111); 正常添加
create table t14(id int(5) zerofill); 位数不够用0填充
insert into t14 values(1); 00001
insert into t14 values(1111111); 1111111，有几位就展示几位
小技巧：
涉及整型字段，都无需自己定义长度，直接使用默认长度
针对其他类型的字段，需自己定义长度
字段类型之枚举与集合
枚举
多选一
create table t15(
id int,
name varchar(32),
gender enum('male','female','others')
);
插入数据的时候，gender栏只能填写提前定义好的数值
集合
多选多(也可多选一)
create table t16(
id int,
name varchar(32),
hobby set('football','rugby','basketball','volleyball','tennis','beachball','handball','golf','icehockey','tabletennis')
);
字段类型之日期类型
| 日期关键字 | 对应时间 |
|---|---|
| date | 年月日 |
| datetime | 年月日时分秒 |
| time | 时分秒 |
| year | 年 |
MySQL字段约束条件
字段类型与约束条件的关系
约束条件是基于字段类型之上的额外限制
例如: id int unsigned
字段类型int规定了id字段只能存整数
约束条件unsignedid限制字段是整数基础之上还得是0和正数
1.无需正负号
unsigned
2.零填充
zerofill
3.非空
not null
插入数据的另外一种方式，打破字段顺序
create table t3(id int,name varchar(42));
insert into t3(name,id) values('tom',1);
insert into t3(name) values('sam');
insert into t3(id) values(8);
create table t4(id int,name varchar(42) not null);
insert into t4(id,name) values(1,''); 不报错
insert into t4(id) values(1); 报错, Field 'name' doesn't have a default value
insert into t4(id,name) values(1,null); 报错,Column 'name' cannot be null
4.默认值
default
create table t5(id int,name varchar(42) default 'anonymous');
insert into t5(id) values(1);
insert into t5(id,name) values(2,'nancy');
insert into t5(id,name) values(3,'macron');
5.唯一值
unique
单列唯一
某个字段下对应的数据不能重复,是唯一的
create table t6(
id int,
name varchar(42) unique,
age int
);
多列唯一
多个字段下对应的数据组合到一起的结果是唯一的,不能重复,
create table t7(
id int,
host varchar(42),
port int,
unique(host,port)
);
6.主键
primary key
从约束层面上而言
相当于not null + unique(非空且唯一)
create table t8(id int primary key,name varchar(42));
InnoDB存储引擎的主键特征
InnoDB存储引擎规定每一张表有且必须要有一个用于构建表的主键
主键可以加快数据的查询速度，类似于书的目录
如果创建表创建的时候没有设置主键也没有其他的键，InnoDB会采用一个隐藏的字段作为表的主键(隐藏就意味着而不能使用，即不能加快数据查询)
如果没有主键但是有非空且唯一的字段，那么会自动升级成主键(从上往下的第一个)
create table t9(
tid int,
pid int not null unique,
cid int not null unique,
sid int not null unique
);
联合主键
多个字段组合，本质还是一个主键
create table t10(
id int,
name varchar(42),
pwd int,
age int,
primary key(id,name)
);
7.自增
auto_increment
专门配合主键一起使用,以后在添加数据的时候就不需要手动设置主键值
create table t11(
id int primary key auto_increment,
name varchar(42),
height varchar(22),
weight varchar(22)
);
浙公网安备 33010602011771号