MySQL数据库(二)

MySQL数据库

一、字符编码与配置文件

		\s 			查看数据库基本信息
	show engines 	查看所有的存储引擎
	char_length() 	统计字段数据长度
	
	以下是WIN版本
		我们MySQL刚安装完 发现创建表记录的时候写入字符串Varchar 查看结果乱码
		解决方法如下：
			拷贝my-default.ini 					# windows下MySQL默认的配置文件
			拷贝上述文件并且重命名my.ini			#(随机取名 看见一大堆英文不要慌)
			由于5.6版本编码不统一 会造成乱码 我们需要统一修改>>>: Utf8
			[mysqld]							# Mysqld 服务端 
			    character-set-server=utf8		# 编码改成utf8  也可以utf8mb4(mb4表示可以显示表情包)
			    collation-server=utf8_general_ci
			[client]							# 客户端编码改成utf8
			    default-character-set=utf8
			[mysql]								# mysql客户端端编码改成utf8
			    default-character-set=utf8
			    user='root'						# 为了每次登录不需要输入密码
			    passoword='123'					# 直接写入配置文件 输入mysql即可直接使用
			注意如果配置文件涉及到mysqld相关的配置修改 那么需要重启服务端才可以生效

	以下是MAC版本
		我们MySQL刚安装完 默认是全部都有加载好的 不需要去配置文件
		如果需要配置的话 只需要在 /etc/ 文件夹里面 创建一个文件后缀名cnf 内容跟以上win一样
		(注意该文件权限不能太高 写完之后改成只读 文本里面不要有空格 重启mysql即可)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

二、存储引擎

存储引擎我们可以理解成针对相同的数据采用不同的存取策略(show engines;)

1.如何查看存储引擎

使用show engines;命令即可查看

2.重要的存储引擎

MyISAM

它是MySQL5.5版本及之前的版本默认的存储引擎，它的存取数据的速度很快，但是功能较少(不支持事务，支持表锁)，数据安全性较低

InnoDB

它是MySQL5.6及之后的版本默认的存储引擎，存取数的速度没有MyISAM快，但是支持事务、行锁、外键等诸多功能，并且安全性较高

MEMORY

它是基于内存存储的，存取数据极快，但是有着断电数据丢失、重启服务端数据就丢失的特性(基于内存的都是如此)

BlackHole

黑洞，任何写进去的数据都会立刻删除，类似于垃圾站

演示

	ps:MySQL中默认是大小写不敏感的(忽略大小写)
	格式：
		create table t1(id int)engine=MyISAM;
		create table t2(id int)engine=InnoDB;
		create table t3(id int)engine=MEMORY;
		create table t4(id int)engine=BlackHole;
	'ps:windows cmd终端鼠标右键的意思就是粘贴'
	
	'对于不同的存储引擎，硬盘中保存的文件个数也是不一样的'
	'''另外不同版本的数据库，里面的文件后缀也不一样，向5.6版本的存储表结构文件后缀则是.frm'''
	(以下以8.0的版本为准)
	MyISAM：3个文件
		.sdi 存储表结构
		.MYD 存储的是表数据
		.MYI 存索引(当成是字典的目录，加快查询速度)
	
	InnoDB：1个文件
		'在8.0版本中InnoDB不显示表结构文件,在5.6版本中显示'
		#.frm 存储表结构 (5.6版本中的显示)
		.ibd 存储数据和索引
	
	MEMORY：1个文件
		.sdi 存储表结构
	(断电或重启丢失数据)

	BlackHole：1个文件
		.sdi 存储表结构
	(放进去的数据立马丢失)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

三、创建表的完善语法

	create table 库名.表名(字段名  字段类型(数字) 约束条件)engine=存储引擎;
						id		int 0~255	 default		MyISAM

	1.字段名和字段类型是必须的(至少写一个)
	2.数字跟约束条件是可选(可有可无) # not_null default
	3.约束条件可以写多个 空格隔开即可
	4.最后一个字段的结尾千万不能加逗号
1
2
3
4
5
6
7

四、字段类型之整型

整型分类及存储范围

	验证整型默认情况下是否携带正负号？
		create table ts1(id tinyint);
		insert into ts1 values(-129),(256)
	'发现自动填写两个边界值，数据失真，没有实际意义'
	'上述所有的整型类型默认都会带有负号'
1
2
3
4
5

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

五、字段类型之浮点型

'以下三者都可以存储浮点型数据，但是各自的精确度不一致'
	float 	double 	decimal 
	folat(255,30) # 总位数255位，小数点后占60位
	double(255,30) # 总位数255位，小数点后占60位
	decimal(65,30) # 总位数65位，小数点后占30位
	
	create table ts4(id float(255,30));
	create table ts5(id double(255,30));
	create table ts6(id decimal(65,30));
	insert into ts4 values(1.11111111111111);
	insert into ts5 values(1.11111111111111);
	insert into ts6 values(1.11111111111111);
	
'''三者的精确度不一样：decimal >>> double >>> float(精确到7位了)'''
1
2
3
4
5
6
7
8
9
10
11
12
13
14

六、字段类型之字符类型

1.研究两者区别

	char 定长
	'字符串中括号中得数字代表的就是字符串所存储的范围大小'
		char(4)
			最大只能存储四个字符，如果超出范围则直接报错
			如果不超出范围，则用空格填充至四个字符

	varchar 变长
		varchar(4)
			最大只能存储四个字符，如果超过范围则直接报错
			如果不超出范围，则有几位数就存几位


	'验证两者的区别'
	create table ts7(id int ,name char(4));
	create table ts8(id int, name varchar(4));
	'''
	结果验证 超出范围两者都会报错
	注意sql_mode='strict_trans_tables';
	'''
	
	'''如果你想超出范围之后，直接报错，需要设置严格模式'''
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

2.严格模式

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

	方式1：命令临时修改
		set session sql_mode='strict_trans_tables';
		'在当前客户端操作界面有效'
		set global sql_mode='strict_trans_tables';
		'在当前服务端有效(服务端不重启永久有效)'
	
	方式2：配置文件永久修改
		[mysqld]
			sql_mode='STRICT_TRANS_TABLES'（不区分大小写）
1
2
3
4
5
6
7
8
9
10
11
12

3.研究定长、不定长、是否补充空格

	'验证定长和变长特性'
	cahr_length() # 统计字段数据的长度
	
	create table ts7(id int ,name char(4));
	create table ts8(id int, name varchar(4));
	insert into ts7 values(1,'an');
	insert into ts8 values(2,'an');
	
	'验证是否补充了空格'
	# ps:char_length()获取字段存储的数据长度
	select char_length(name) from ts7;
	select char_length(name) from ts8;
	'''
	默认情况下MySQL针对char的存储会自动填充空格和删除空格
	然后再读取的时候优惠自动将填充的空格移除，如果想取消该机制
	需要设置严格模式。就可以看到填充了空格
	set global sql_mode='strict_trans_tables,pad_char_to_full_length'; 不区分大小写
	上述目录是替换，不是新增，所以之前的配置也要写上
	'''
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

4.char VS varchar

	char 
		优势：整存整取 速度快
		劣势：浪费存储空间
		
	varchar
		优势：节省存储空间
		劣势：存取数据的速度相较char慢

	'char与varchar的使用需要结合具体应用场景'
1
2
3
4
5
6
7
8
9

七、数字的含义

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

		create table t13(id int(3)); 
		create table t14(id int(3) zerofill);  
		# zerofill 表示如果数据没有填写超过三位则以0补充支三位
		
		总结
			以后涉及到整型字段的定义 类型后面不需要加括号写数字 
			除非有业务需求必须固定位数
1
2
3
4
5
6
7
8
9
10

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

	枚举
		多选一
			eg:性别(男 女 其他)
		create table ts9(
			id int,
			name varchar(32),
			gender enum('male','female','others')
		);
	'只能选择里面的内容，如果没有的则报错'

	insert into ts9 values(1,'jack','boy');
	insert into ts9 values(1,'jack','male');
1
2
3
4
5
6
7
8
9
10
11
12

	集合
		多选多(包含多选一)
			eg：爱好(看小说，音乐，篮球)
		create table ts10(
			id int,
			name varchar(32),
			hobby set('read','music','basketabll')
		);
		
		'''只能选择里面的内容，如果没有的则报错'''
		insert into ts10 values(1,'jack','jump');
		insert into ts10 values(1,'jack','music');
		insert into ts10 values(2,'tom','read,music');
1
2
3
4
5
6
7
8
9
10
11
12
13

九、字段类型之日期类型

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

	create table ts11(
		id int,
		reg_time date,
		reg1_time datetime,
		reg2_time time,
		reg3_time year
	);

	insert into ts11 values(1,'2023-10-1','2023-11-11 11:11:11','11:11:11',2023);
	
	'ps:以后涉及到日期相关字段一般都是系统自动回去，无需我们可以操作'
1
2
3
4
5
6
7
8
9
10
11
12
13
14