数据库(部分讲解)

字符编码与配置文件

1.\s查看MySQL相关信息

2.默认的配置文件是my-default.ini

# 字符编码相关配置
[mysqld]
	character-set-server=utf8mb4
	collation-server=utf8mb4_general_ci
[client]
	default-character-set=utf8mb4
[mysql]
	default-character-set=utf8mb4
'''
注意:
1.utf8mb4能够存储表情 功能更强大
2.utf8与utf-8是有区别的 MySQL中只有utf8
'''

3.修改了配置文件中关于[mysqld]的配置 需要重启服务端

利用配置文件我们可以偷懒

将管理员登录的账号密码直接写在配置文件中 之后使用mysql登录即可

数据库存储引擎

存储引擎

• 就是数据库针对数据采取的多种存取方式

查看常见存储引擎的方式

show engines;

需要了解的四种引擎

MyISAM

• MySQL5.5之前默认的存储引擎

优点：存取数据的速度快
缺点：功能较少 安全性较低
适用场景：对事务完整性没有要求 表的数据都会只读的

InnoDB

• MySQL5.5之后默认的存储引擎

优点：支持事务和崩溃修复能力 引入了行级锁和外键约束 安全性更高
缺点：占用的数据空间相对较大 存取速度没有MyISAM快
适用场景：需要事务支持 并且有较高的并发读写频率

Memory

• 基于内存存取数据 仅用于临时表数据存取

优点：访问速度较快
缺点：
	1.哈希索引数据不是按照索引值顺序存储 无法用于排序
	2.不支持部分索引匹配查找 因为哈希索引是使用索引列的全部内容来计算哈希值的
	3.只支持等值比较 不支持范围查询
	4.当出现哈希冲突时 存储引擎需要遍历链表中所有的行指针 逐行进行比较 直到找到符合条件的行

BlackHole

• 其表现就像一个黑洞 只进不出 进来就消失 换句话说，任何往其中写的数据都将丢失

使用场景:虽然其不保存数据 但对数据库的操作仍旧记录在binlog日志中 这就带来一个好处 可以将其作为主从复制的中介 将原来从主库中同步的操作变为从作为中介的BlackHole引擎数据库中同步

了解不同存储引擎底层文件个数

create database db2;
use db2;
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	表数据
.MYI	表索引

• memory一个文件
• blackhole一个文件

文件名	后缀名	作用
memory	.frm	表结构
blackhole	.frm	表结构

• 往四个表里面分别插入一条数据

insert into t1 values(1);
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);
'''注意:MySQL默认忽略大小写'''

创建表的完整语法

create table 表名(
	字段名 字段类型(数字) 约束条件,
	字段名 字段类型(数字) 约束条件,
 	字段名 字段类型(数字) 约束条件
);
1.字段名和字段类型是必须的
2.数字和约束条件是可选的
3.约束条件也可以写多个 空格隔开即可
4.最后一行结尾不能加逗号
ps:编写SQL语句报错之后不要慌 仔细查看提示 会很快解决 
    	near ')' at line 7

字段类型之整型

整数类型一共有 5 种，包括 TINYINT、SMALLINT、MEDIUMINT、INT（INTEGER）和 BIGINT

tinyint		   	1bytes		    正负号(占1bit)
smallint		2bytes		    正负号(占1bit)
int			4bytes		    正负号(占1bit)
bigint			8bytes		    正负号(占1bit)

类型	大小	范围(有符号)	范围(无符号)	用途
TINYINT	1 字节	-128~127	(0，255)	小整数值
SMALLINT	2 字节	-32768~32767	(0，65 535)	大整数值
MEDIUMINT	3 字节	-8388608~8388607	(0，16 777 215)	大整数值
INT或INTEGER	4 字节	-2147483648~2147483647	0~4294967295	大整数值
BIGINT	8 字节	-9223372036854775808~9223 372036854775807	0~18446744073709 551615	极大整数值

注意事项

• 需要考虑正负数的问题，如果需要存储负数，则需要占据一个比特位（比如1bytes（1bytes=2的八次方，256）如果需要存储负数，那么需要占据一个比特位放负数符号，剩下2的七次方）就剩128了

• 注意手机号如果使用整型来存储，需要使用bigint才可以

• 有时候看似需要使用数字类型存储的数据其实可能使用的是字符串，因为字符串可以解决不同语言对数字不精确的缺陷

• create table t2(id tinyint);

• insert into t5 values(-129),(256);

• 如果是在5.6版本不会报错 会自动处理成最大范围（没有意义）

验证整型默认是否携带正负号
create table t5(id tinyint);
insert into t5 values(-129),(128);
结果是-128和127 也就意味着默认自带正负号

我们也可以取消正负号
create table t6(id tinyint unsigned);
insert into t6 values(-129),(128),(1000);

严格模式

• 当我们在使用数据库存储数据的时候 如果数据不符合规范 应该直接报错而不是擅自修改数据 这样会导致数据的失真(没有实际意义)
  正常都应该报错 但是我们之前不小心改了配置文件
  

1.临时修改

set session sql_mode='strict_trans_tables';
	在当前客户端有效
set global sql_mode='strict_trans_tables';
	在当前服务端有效

2.永久修改

直接修改配置文件

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

字段类型之浮点型

概念

浮点型主要有 float，double 两个，浮点型在数据库中存放的是近似值，例如float(6,3)，如果插入一个数123.45678，实际数据库里存的是123.457。

语法简介

• float(20,10)
  总共存储20位数，小数点后面占10位数

类型	大小	用途
FLOAT	4字节	单精度浮点数值
DOUBLE	8字节	双精度浮点数值

• 定点型字段类型有 decimal 一个，主要用于存储有精度要求的小数

类型	大小	用途
DECIMAL	对DECIMAL(M,D),如果M>D,为M+2字节 否则为D+2	精确小数值

对于声明语法 DECIMAL(M,D) ，自变量的值范围如下：
M是最大位数（精度），范围是1到65。可不指定，默认值是10。
D是小数点右边的位数（小数位）。范围是0到30，并且不能大于M，可不指定，默认值是0。
例如字段 salary DECIMAL(5,2)，能够存储具有五位数字和两位小数的任何值，因此可以存储在salary列中的值的范围是从-999.99到999.99

create table t7(id float(60,20));
create table t8(id double(60,20));
create table t9(id decimal(60,20));
insert into t7 values(1.11111111111111111111);
insert into t8 values(1.11111111111111111111);
insert into t9 values(1.11111111111111111111);

• 总结:三者的核心区别在于精确度不同

字段类型之字符类型

• 字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

• 其中 char 和 varchar 是最常用到的。char 类型是定长的，MySQL 总是根据定义的字符串长度分配足够的空间。当保存 char 值时，在它们的右边填充空格以达到指定的长度，当检索到 char 值时，尾部的空格被删除掉。varchar 类型用于存储可变长字符串（即变长），存储时，如果字符没有达到定义的位数，也不会在后面补空格

char
	定长
    	char(4)  最多存储四个字符 超出就报错 不够四个空格填充至四个
varchar
	变长
    	varchar(4) 最多存储四个字符 超出就报错 不够则有几位存几位
    
create table t10(id int, name char(4));
create table t11(id int, name varchar(4));
insert into t10 values(1, 'jason1');
insert into t11 values(1, 'jason2');

• char_length()获取字段存储的数据长度

默认情况下MySQL针对char的存储会自动填充空格和删除空格

set global sql_mode='strict_trans_tables,pad_char_to_full_length';

char和varchar对比

• char
  优势:整存整取 速度快
  劣势:浪费存储空间

• varchar
  优势:节省存储空间
  劣势:存取数据的速度较char慢

• 比如在存储下方的字符时，char字段类型使用空格填充，varchar就需要使用报头，记录每一次记录的字符的长度，而报头需要占用一个字节

jacktonyjasonkevintomjerry
1bytes+jack1bytes+tony1bytes+jason1bytes+kevin1bytes+tom1bytes+jerry
"""
char与varchar的使用需要结合具体应用场景
"""

数字的含义

• 数字在很多地方都是用来表示限制存储数据的长度
• 但是在整型中数字却不是用来限制存储长度

create table t12(id int(3));  不是用来限制长度
insert into t12 values(12345);

create table t13(id int(5) zerofill);  而是用来控制展示的长度
insert into t13 values(123),(123456789);

总结:以后写整型无需条件数字

字段类型之枚举与集合

枚举

• 多选一

create table t15(
    	id int,
      	name varchar(32),
       gender enum('male','female','others')
    );
 	insert into t15 values(1,'tony','猛男');
  	insert into t15 values(2,'jason','male');
 	insert into t15 values(3,'kevin','others');

集合

• 多选多 (也可以多选一)

create table t16(
    	id int,
      	name varchar(16),
       hobbies set('basketabll','football','doublecolorball')
    );
 	insert into t16 values(1,'jason','study');
 	insert into t16 values(2,'tony','doublecolorball');
	insert into t16 values(3,'kevin','doublecolorball,football');

字段类型之日期类型

类型	大小	格式	用途
YEAR	1	YYYY	年份值
TIME	3	HH:MM:SS	时间值或持续时间
DATE	3	YYYY-MM-DD	日期值
DATETIME	8	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

datetime 年月日时分秒
date 年月日
time 时分秒
year 年

create table t17(
	id int,
  	name varchar(32),
 	register_time datetime,
 	birthday date,
 	study_time time,
 	work_time year
);

• 总结:以后涉及到日期相关字段一般都是系统自动回去 无需我们可以操作

动态获取时间

insert into t17 values(1,'jason','2000-11-11 11:11:11','1998-01-21','11:11:11','2000');