二、MySQL

最新推荐文章于 2024-07-11 16:55:59 发布

芈月坦途

最新推荐文章于 2024-07-11 16:55:59 发布

阅读量172

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/ON_THE_WAY2/article/details/126290261

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

MySQL字段类型及属性

1. Data Type
2. 字段属性
3. 练习
4. 经典问题

1. Data Type

1.1 数值类型

int
	整型：	
		Signed：有符号整数 即包含+、-，默认是有符号
		Unsigned：无符号整数
tinyint
smallint
mediumint
bigint
bit
float 浮点型：有近似值，不精确
double
decimal 定点型：数值非常精确，不会有近似值
	fixed-point
	空间是根据长度来计算的，一个数字占一个字节
函数：
# length
root@eng 15:20  mysql>select length(salary) from t3;
+----------------+
| length(salary) |
+----------------+
|              7 |
|              7 |
|              8 |
|              9 |
|             10 |
|             11 |
+----------------+
6 rows in set (0.00 sec)
# char_length
root@eng 15:20  mysql>select char_length(salary) from t3;
+---------------------+
| char_length(salary) |
+---------------------+
|                   7 |
|                   7 |
|                   8 |
|                   9 |
|                  10 |
|                  11 |
+---------------------+
6 rows in set (0.00 sec)
root@eng 15:20  mysql>
两类函数差异：
length()  查询存储空间的大小
char_length() 查看字符的长度--》字符的个数
length（）
	select length(name) from t4;
		查询字段值存储消耗的存储空间字节数
char_length（）
	select char_length(name) from t4;
		查询字段值的字符长度

1.2 字符串类型

char
varchar
# char和varchar的区别？
# 相同点：
CHAR和VARCHAR类型声明的长度指示您希望存储的最大字符数。 例如，CHAR(30)最多可以容纳30个字符。  
# 不同点：
1、列长度问题：
CHAR列的长度固定为创建表时声明的长度。 长度为0 ~ 255之间的任意值。 当存储CHAR值时，将它们右填充为指定长度的空格。 在检索CHAR值时，除非启用PAD_CHAR_TO_FULL_LENGTH SQL模式，否则将删除尾随空格。  
VARCHAR列中的值是可变长度的字符串。 长度可以指定为0到65535之间的值。 VARCHAR的有效最大长度受最大行大小(65,535字节，所有列共享)和使用的字符集的影响。 参见8.4.7节，“表列数和行大小的限制”。  
 # 实例
 root@(none) 10:03  mysql>use test;
Database changed
root@test 10:03  mysql>CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
    ->        c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
    ->        f VARCHAR(10000), g VARCHAR(6000)) ENGINE=InnoDB CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
root@test 10:04  mysql>CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),        c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),        f VARCHAR(10000), g VARCHAR(4000)) ENGINE=InnoDB CHARACTER SET latin1;
Query OK, 0 rows affected (0.01 sec)

root@test 10:05  mysql>desc t;
+-------+----------------+------+-----+---------+-------+
| Field | Type           | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| a     | varchar(10000) | YES  |     | NULL    |       |
| b     | varchar(10000) | YES  |     | NULL    |       |
| c     | varchar(10000) | YES  |     | NULL    |       |
| d     | varchar(10000) | YES  |     | NULL    |       |
| e     | varchar(10000) | YES  |     | NULL    |       |
| f     | varchar(10000) | YES  |     | NULL    |       |
| g     | varchar(4000)  | YES  |     | NULL    |       |
+-------+----------------+------+-----+---------+-------+
7 rows in set (0.04 sec)

root@test 10:05  mysql>
2、前缀空格问题
如果未启用严格SQL模式，并且为CHAR或VARCHAR列指定的值超过了该列的最大长度，则会截断该值，并生成警告。 对于截断非空格字符，可以使用严格的SQL模式导致出现错误(而不是警告)并禁止插入值。 参见5.1.10节，“服务器SQL模式”。
与CHAR相比，VARCHAR值存储为1字节或2字节长度的前缀加上数据。 长度前缀表示值中的字节数。 如果值不超过255字节，则列使用一个长度字节，如果值可能超过255字节，则使用两个长度字节。  
 
对于VARCHAR列，超过列长度的尾随空格将在插入之前被截断，并生成警告，而不管使用的是哪种SQL模式。 对于CHAR列，无论SQL模式如何，都将静默地截断插入值的多余尾随空格。  
 
存储VARCHAR值时不填充它们。 根据标准SQL，在存储和检索值时将保留尾随空格。

3、存储问题
存储在表最后一行的值仅在不使用严格SQL模式时适用; 如果启用严格模式，则不存储超过列长度的值，并产生错误。  
 
InnoDB将大于等于768字节的固定长度字段编码为可变长度字段，这些可变长度字段可以在页外存储。 例如，如果字符集的最大字节长度大于3，则CHAR(255)列可以超过768字节，utf8mb4就是这样。 
如果给定的值存储在CHAR(4)和VARCHAR(4)列中，从列中检索到的值并不总是相同的，因为在检索时从CHAR列中删除尾随空格。

text
	longtext
		0-4 294 967 295 bytes	极大文本数据
	tinytext 范围在0~255 类似于char
	MEDIUMTEXT 0-16 777 215 bytes	中等长度文本数据
	text
		0~65535
		varchar
	有了char和varchar ，为什么还要text？
		扩展
	Blob 和 text差别？
		一般是存储视频、音频的url到数据库，然后去文件系统中查找资源
blob
	二进制的文本
		图片
		音频
		视频
	tinyblob
	MEDIUMBLOB
	LONGBLOB
	blob
# 插入的数据必须是字段定义的类型范围内，否则不能插入其他元素
enum 枚举
		CREATE TABLE shirts (
	    name VARCHAR(40),
	    size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
	);
	INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),
	  ('polo shirt','small');
		什么是枚举类型？enumerate
	插入的数据必须是字段定义的类型范围内，否则不能插入其他元素
# 必须使用集合里的元素，并且会自动去重排序存储
set
	集合
		不允许出现重复东西
		什么是set类型？
	必须使用集合里的元素，并且会自动去重排序存储	
binary
varbinary
	The BINARY and VARBINARY types are similar to CHAR and VARCHAR, except that they store binary strings rather than nonbinary strings.

1.3 日志和时间类型

时间戳 timestamp
# 主要用在：
	注册账号时间
	交易
	下订单
# 耗费资源
	消耗4个字节 2^32
date 主要记录 年月日 
#主要用在
	生日
time
datetime
# 主要用在
	考勤
year
什么是日期和时间？
	date 年月日
	datetime 年月日小时分钟秒
	timestamp 时间戳
		记录从1970-01-01-00：00：00 UTC --- 2038-01-19-03：14：07 UTC
		到目前为止的时间以秒为单位
		存储4个字节
		主要用在交易下单时间记录等

各类型差异

1.4 json类型

1.5 spatial data

# 主要用在
空间数据类型、地理信息系统

参考链接

2. 字段属性

zerofill 使用0来填充，自动转为为unsigned
unsigned 无符号整数
signed 有符号整数
not null 不能为空
auto_increment 自增
SET @@auto_increment_offset = 10,  -- 设置起始值
@@auto_increment_increment=10;  -- 设置每次加几
默认的起始值是1 步长是1
存储引擎
	InnoDB 为默认搜索引擎
		t1.frm：表结构
		t1.ibd：存放索引和数据，即innodb data index data
	MyISAM
		t1.frm 表结构
		t1.MYI 索引文件 index
		t1.MYD 数据文件 data
	# 查看搜索引擎：
	SHOW  engines;
		csv：
			CSV storage engine  
			以逗号作为分割的文件
			数据分析
				数据处理
				数据清洗
				数据仓库
					软件
		blackhole：
			黑洞
			解决了主从复制架构里，让很多的从服务器直接到配置了blackhole存储引擎的master上拿二进制日志，让最上层的master的负载降低
			其实就是帮忙主从架构传递二进制日志，自己不执行二进制日志，只是传递
			
		memory：
			数据保存在内存里，特别适用于适用临时表的场景
# 经典的问题
		Innodb和myisam存储引擎的优缺点
		https://blog.csdn.net/helloxiaozhe/article/details/88601028
# 存储引擎和表绑定
CREATE TABLE `t1` (`id` tinyint(4) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8
foreign key
# 外键的好处： 
	直接引用别的表里的字段，自己的表里就不需要再创建相同的数据了，可以避免数据的冗余。
	命运共同体--》表和表之间互相引用
供应链
	外键的优势是什么？--》作用是什么？
父子表：
外宾： 是外国人--》在别的国家有身份有地位的人
外国人：
外键： 其实在另外表里是主键
A表里的主键被引用到B表里做一个字段，这个字段就是B表里的外键

多表连接查询使用
root@sanchuang 11:58  sc-mysql>select id,name,dname from emp join dept using(deptid);
+----+--------+-----------+
| id | name   | dname     |
+----+--------+-----------+
|  1 | 张三   | 市场部    |
|  2 | 李四   | 市场部    |
+----+--------+-----------+
2 rows in set (0.00 sec)

root@sanchuang 12:00  sc-mysql>select id,name,emp.deptid,dname from emp, dept where  emp.deptid=dept.deptid;
+----+--------+--------+-----------+
| id | name   | deptid | dname     |
+----+--------+--------+-----------+
|  1 | 张三   |     10 | 市场部    |
|  2 | 李四   |     10 | 市场部    |
+----+--------+--------+-----------+
2 rows in set (0.00 sec)

root@sanchuang 12:00  sc-mysql>

primary key 等同于not null+unique
	主键 不允许为空，而且也不允许重复
comment 注释 sqlyog查看
default 默认值
unique
	唯一性
	允许为null
	空值只能出现一次
		字符串为空  ''
			单引号或者双引号里没有任何内容
				空值
	null值和空值有什么区别？
字符集
	rpm方式安装默认字符集 latin1
	编译安装的默认字符集 utf8 3 、utf8mb4 4
	在配置文件里添加配置 character-set-server=utf8  --》指明mysql 服务器的 server 字符集是utf8
# 如何指定字符集
	SHOW CHARACTER SET; 查询你的mysql支持哪些字符集
	SHOW variables like '%CHARACTER%';查看你正在使用哪些字符集
# 继承问题
		服务器 库 表 列
CREATE TABLE t1 (c1 CHAR(1) CHARACTER SET latin1,c2 CHAR(1) CHARACTER SET ascii);
		优先级问题
		root@wuxiaohui 10:35  calimysql>create database  wuxiaohui2 default charset='utf8';
		查看表的字符集的问题
			show  create table sc;
		文本类型的数据，会牵涉到字符集
			varchar
			char
			text
数据的完整性：
	数据的正确性和一致性

3. 练习

作业：
0.建立库 hunan：
	1.表名:sc_student
	2.字段： 编号，名字、性别、年龄、专业，电话号码，地址，岗位，城市，公司的名字，出生日期，工资
    3.思考：哪些字段设置成什么类型，必须有主键，具体哪个字段做主键，自己定义
  4.往表里插入几行数据，
  5.查询显示一下数据，
  6.显示一下表的结构，字符集，存储引擎等信息
  7.思考一条记录会消耗多少存储空间？
  create database hunan;
use hunan;
create table sc_student(
id int primary key,
name varchar(20) not null,
sex enum('男','女') not null,
age tinyint,
major varchar(20),
phoneNo varchar(20),
address varchar(50),
position varchar(20),
city varchar(20),
company varchar(20),
birth date,
celery decimal(12,2),
college varchar(20)
);

insert into sc_student values(1,'唐浩明','男',23,'农业工程与信息技术','12345678911','江苏淮安',
'运维开发工程师','上海','大公司','2022-08-10',20000.00,'湖南农业大学');

select * from sc_student\G; # 以文本方式显示

--
int 4
name 4*3=12字节
sex 3
age 1
major 30
phoneno 15
address 60
position 21
city 9
company 36 
birth 3
salary 5+2=7byte
college 24byte
==
225byte *100万人

4. 经典问题

char和varchar的区别？

# char和varchar的区别？
# 相同点：
CHAR和VARCHAR类型声明的长度指示您希望存储的最大字符数。 例如，CHAR(30)最多可以容纳30个字符。  
# 不同点：
1、列长度问题：
CHAR列的长度固定为创建表时声明的长度。 长度为0 ~ 255之间的任意值。 当存储CHAR值时，将它们右填充为指定长度的空格。 在检索CHAR值时，除非启用PAD_CHAR_TO_FULL_LENGTH SQL模式，否则将删除尾随空格。  
VARCHAR列中的值是可变长度的字符串。 长度可以指定为0到65535之间的值。 VARCHAR的有效最大长度受最大行大小(65,535字节，所有列共享)和使用的字符集的影响。 参见8.4.7节，“表列数和行大小的限制”。  
 # 实例
 root@(none) 10:03  mysql>use test;
Database changed
root@test 10:03  mysql>CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
    ->        c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
    ->        f VARCHAR(10000), g VARCHAR(6000)) ENGINE=InnoDB CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
root@test 10:04  mysql>CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),        c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),        f VARCHAR(10000), g VARCHAR(4000)) ENGINE=InnoDB CHARACTER SET latin1;
Query OK, 0 rows affected (0.01 sec)

root@test 10:05  mysql>desc t;
+-------+----------------+------+-----+---------+-------+
| Field | Type           | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| a     | varchar(10000) | YES  |     | NULL    |       |
| b     | varchar(10000) | YES  |     | NULL    |       |
| c     | varchar(10000) | YES  |     | NULL    |       |
| d     | varchar(10000) | YES  |     | NULL    |       |
| e     | varchar(10000) | YES  |     | NULL    |       |
| f     | varchar(10000) | YES  |     | NULL    |       |
| g     | varchar(4000)  | YES  |     | NULL    |       |
+-------+----------------+------+-----+---------+-------+
7 rows in set (0.04 sec)
root@test 10:05  mysql>
2、前缀空格问题
如果未启用严格SQL模式，并且为CHAR或VARCHAR列指定的值超过了该列的最大长度，则会截断该值，并生成警告。 对于截断非空格字符，可以使用严格的SQL模式导致出现错误(而不是警告)并禁止插入值。 参见5.1.10节，“服务器SQL模式”。
与CHAR相比，VARCHAR值存储为1字节或2字节长度的前缀加上数据。 长度前缀表示值中的字节数。 如果值不超过255字节，则列使用一个长度字节，如果值可能超过255字节，则使用两个长度字节。  
 
对于VARCHAR列，超过列长度的尾随空格将在插入之前被截断，并生成警告，而不管使用的是哪种SQL模式。 对于CHAR列，无论SQL模式如何，都将静默地截断插入值的多余尾随空格。  
 
存储VARCHAR值时不填充它们。 根据标准SQL，在存储和检索值时将保留尾随空格。

3、存储问题
存储在表最后一行的值仅在不使用严格SQL模式时适用; 如果启用严格模式，则不存储超过列长度的值，并产生错误。  
 
InnoDB将大于等于768字节的固定长度字段编码为可变长度字段，这些可变长度字段可以在页外存储。 例如，如果字符集的最大字节长度大于3，则CHAR(255)列可以超过768字节，utf8mb4就是这样。 
如果给定的值存储在CHAR(4)和VARCHAR(4)列中，从列中检索到的值并不总是相同的，因为在检索时从CHAR列中删除尾随空格。

关系型和非关系型数据库的区别？

关系
	表和表
	MySQL，oracle，postgreSQL，MSSQL
非关系
	key:value
	redis,mongodb,Tidb
	做缓存
		一直在内存里

结构化和非结构化数据？

数据清洗
	分析软件
		数据分析

MySQL中的判断有何区别？

	# 忽略尾随空格
	select myname = 'Jones',myname = 'Jones ' from names;
	# 不忽略尾随空格
	select myname like 'Jones',myname like 'Jones ' from names;

Blob和text的区别？

一般是存储视频、音频的url到数据库，然后去文件系统中查找资源

芈月坦途

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
二、MySQL

MySQL
复制链接

扫一扫

专栏目录

二、MySQL

MySQL字段类型及属性

1. Data Type

1.1 数值类型

1.2 字符串类型

1.3 日志和时间类型

1.4 json类型

1.5 spatial data

2. 字段属性

3. 练习

4. 经典问题

“相关推荐”对你有帮助么？