32.MySQL数据库【二】存储引擎与基本数据类型.

标签：set 32 0.00 MySQL 数据类型 char sec mysql id

【一】MySQL之存储引擎

1）介绍

针对不同的数据有不同的处理机制，存储引擎就是不同的处理机制

2）MySQL主要的存储引擎

1.innodb

是MySQL5.5版本之后的默认存储引擎
支持事务、外锁、外键

2.myisam

是MySQL5.5版本之前的默认存储引擎
速度比innodb快，但数据安全性较弱

3.memory

内存引擎
数据全部存放在内存
存储速度快。但断电丢失

4.blackhole

黑洞引擎
无论存什么都立刻消失

5.查看所有数据库存储引擎

show engines;

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| ndbinfo            | NO      | MySQL Cluster system information storage engine                | NULL         | NULL | NULL       |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| ndbcluster         | NO      | Clustered, fault-tolerant tables                               | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.00 sec)

3）不同引擎存储数据的特点

# 建表
create table t1(id int) engine=innodb;
create table t2(id int) engine=myisam;
create table t3(id int) engine=blackhole;
create table t4(id int) engine=memory;
# 存数据
insert into t1 values(1);
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);
# 查看
select * from t1;
select * from t2;
select * from t3;
select * from t4;

1.Innodb

frm : 存储的表结构
ibd ：存储表的数据

特点：存储数据持久化存储退出也存在

mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

2.myisam

frm ：存储的表结构
MYD : 表的数据
MYI ：表的索引结构

特点：存储数据持久化存储，退出也存在

mysql> select * from t2;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

3.blackhole

frm ：存储的表结构
特点：存储数据不会有任何反应，不会存储任何数据
```
mysql> select * from t3;
Empty set (0.00 sec)
```

4.memory

frm：存储的表结构

特点就是基于内存存储，只要服务重启，所有数据丢失

mysql> select * from t4;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

重启MySQL服务后

net stop mysql80
net start mysql80

mysql> select * from t4;
Empty set (0.00 sec)

【二】约束条件（null）

null：默认模式，允许字段为空
not null：限制指定字段不能为空

建表

create table eg_not(
    id int,
    name char(10) not null
);

查看表

desc eg_not;

mysql> desc eg_not;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | YES  |     | NULL    |       |
| name  | char(10) | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

插入数据查看结果

insert eg_not values(01,'ST1');
# 插入
mysql> insert eg_not values(01,'ST1');
Query OK, 1 row affected (0.00 sec)
# 查看
mysql>  select * from  eg_not;
+------+------+
| id   | name |
+------+------+
|    1 | ST1  |
+------+------+
1 row in set (0.00 sec)

insert eg_not values(01,null);

insert eg_not values(02,null);
ERROR 1048 (23000): Column 'name' cannot be null

insert eg_not values(null,'ST2');

mysql> insert eg_not values(null,'ST2');
Query OK, 1 row affected (0.00 sec)
# 查看
mysql>  select * from eg_not;
+------+------+
| id   | name |
+------+------+
|    1 | ST1  |
| NULL | ST2  |
+------+------+
3 rows in set (0.00 sec)

【三】严格模式

控制MySQL里面存储数据的一些限制规定
控制超出长度不提示，控制超出长度提示报错

1）模糊匹配、查询

like "%mode"：匹配任意个字符
like "_mode"：只匹配单个字符

2）查看严格模式

show variables like "%mode";

mysql> show variables like "%mode";
+--------------------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name            | Value
                                                         |
+--------------------------+-----------------------------------------------------------------------------------------------------------------------+
| block_encryption_mode    | aes-128-ecb
                                                         |
| gtid_mode                | OFF
                                                         |
| innodb_autoinc_lock_mode | 2
                                                         |
| innodb_strict_mode       | ON
                                                         |
| offline_mode             | OFF
                                                         |
| pseudo_replica_mode      | OFF
                                                         |
| pseudo_slave_mode        | OFF
                                                         |
| rbr_exec_mode            | STRICT
                                                         |
| replica_exec_mode        | STRICT
                                                         |
| slave_exec_mode          | STRICT
                                                         |
| sql_mode                 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
| ssl_fips_mode            | OFF
                                                         |
| ssl_session_cache_mode   | ON
                                                         |
+--------------------------+-----------------------------------------------------------------------------------------------------------------------+
13 rows in set, 1 warning (0.00 sec)

3）修改严格模式

1.只在当前窗口有效（临时修改）

退出客户端重新连接，修改后严格模式还是原来的

set session;

2.全局有效（永久修改）

退出客户端重新连接，严格模式修改会一直生效

set global

3.语法

set session sql_mode = 'STRICT_TRANS_TABLES'
set global sql_mode = 'STRICT_TRANS_TABLES'

【四】基本数据类型

1）整形

默认都带有符号
括号内的宽度用来限制存储数据的范围

整数类型	字节	无符号数的取值范围	有符号数的取值范围
TINYINT	1	0~255	-128~127
SMALLINT	2	0~65535	-32768~32767
MEDIUMINT	3	0~16777215	-8388608~8388607
INT	4	0~4294967295	-2147483648~2147483647
BIGINT	8	0~18446744073709551615	-9223372036854774808~9223372036854774807

1.以tinyint存储

创建表

create table int_1(id tinyint);
# 查看
mysql> desc int_1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | tinyint | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

插入数据

insert into int_1 values(-129),(128);

insert into int_1 values(-129),(128);
ERROR 1264 (22003): Out of range value for column 'id' at row 1

insert into int_1 values(-128),(127);

insert into int_1 values(-128),(127);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
# 查看
mysql> select * from int_1;
+------+
| id   |
+------+
| -128 |
|  127 |
+------+
2 rows in set (0.00 sec)

2.约束条件之unsigned（无符号）

创建表

create table int_2(id tinyint unsigned);
# 查看
mysql> desc int_2;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | tinyint unsigned | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

插入数据

insert into int_2 values(-10);

mysql> insert into int_2 values(-10);
ERROR 1264 (22003): Out of range value for column 'id' at row 1

insert into int_2 values(200);

insert into int_2 values(200);
Query OK, 1 row affected (0.00 sec)
# 查看
mysql> select * from int_2;
+------+
| id   |
+------+
|  200 |
+------+
1 row in set (0.00 sec)

3.字段类型的宽度

字段类型后面跟着的宽度只是显示的宽度
插入的长度是按照谁，当前字段的最大长度进行限制

4.约束条件之zerofill（0填充至指定位数）

创建表

create table int_3(id int(5) unsigneg zerofill);
# 若输入create table int_3(id int(5) zerofill);
# 默认也会加上unsigneg

插入数据

insert into int_3 values(12),(123456);

insert into int_3 values(12),(123456);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
# 查看
mysql> select * from int_3;
+--------+
| id     |
+--------+
|  00012 |
| 123456 |
+--------+
2 rows in set (0.00 sec)

2）浮点型

数据类型	字节数	取值范围
FLOAT	4	-2^128~2 ^128，即-3.40E+38~+3.40E+38
DOUBLE	8	-2^1024~ 2^1024，即-1.79E+308~1.79E+308
DECIMAL	设置位数和精度。	65 ~ 30

1.存储限制

float(255,30)
# 总共255位 ， 小数部分占 30 位
double(255,30)
# 总共255位 ， 小数部分占 30 位
decimal(65,30)
# 总共65位 ， 小数部分占 30 位

2.创建表

create table flo_1(id float(255,30));
create table flo_2(id double(255,30));
create table flo_3(id decimal(65,30));
#  查看
mysql> desc flo_1;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | float(255,30) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> desc flo_2;
+-------+----------------+------+-----+---------+-------+
| Field | Type           | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| id    | double(255,30) | YES  |     | NULL    |       |
+-------+----------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> desc flo_3;
+-------+----------------+------+-----+---------+-------+
| Field | Type           | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| id    | decimal(65,30) | YES  |     | NULL    |       |
+-------+----------------+------+-----+---------+-------+
1 row in set (0.00 sec)

3.插入数据

insert into flo_1 values(6.66666666666666666666);
insert into flo_2 values(6.66666666666666666666);
insert into flo_3 values(6.66666666666666666666);

4.查看数据

mysql> select * from flo_1;
+----------------------------------+
| id                               |
+----------------------------------+
| 6.666666507720947000000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select * from flo_2;
+----------------------------------+
| id                               |
+----------------------------------+
| 6.666666666666667000000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select * from flo_3;
+----------------------------------+
| id                               |
+----------------------------------+
| 6.666666666666666666660000000000 |
+----------------------------------+
1 row in set (0.00 sec)

3）字符类型

数据类型	名称	功能
CHAR	定长	超过四个字符直接报错，不够,默认空格补全
VARCHAR	变长	超过四个字符直接报错，不够,有几个接存几个

1.创建表

create table char_1(name char(4));
create table char_2(name varchar(4));
# 查看
mysql> desc char_1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| name  | char(4) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> desc char_2;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| name  | varchar(4) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

2.插入数据

不足时

insert into char_1 values('a');
insert into char_2 values('a');
# 查看
mysql> select * from char_1;
+------+
| name |
+------+
| a    |
+------+
1 row in set (0.00 sec)

mysql> select * from char_2;
+------+
| name |
+------+
| a    |
+------+
1 row in set (0.00 sec)

超出时

insert into char_1 values('abcde');
insert into char_2 values('abcde');

mysql> insert into char_1 values('abcde');
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> insert into char_2 values('abcde');
ERROR 1406 (22001): Data too long for column 'name' at row 1

3.char_length（查看长度）

select char_length(name) from char_1;

mysql> select char_length(name) from char_1;
+-------------------+
| char_length(name) |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

select char_length(name) from char_2;

mysql> select char_length(name) from char_2;
+-------------------+
| char_length(name) |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

char 在硬盘上存的绝对是真正的数据，带有空格
但是在现实的时候MySQL会自动将多余的空格删除

4.取消默认去除空格

查看命令

show variables like '%mode';

mysql> show variables like '%mode';
+--------------------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name            | Value
                                         |
+--------------------------+-----------------------------------------------------------------------------------------------------------------------+
| block_encryption_mode    | aes-128-ecb
                                         |
| gtid_mode                | OFF
                                         |
| innodb_autoinc_lock_mode | 2
                                         |
| innodb_strict_mode       | ON
                                         |
| offline_mode             | OFF
                                         |
| pseudo_replica_mode      | OFF
                                         |
| pseudo_slave_mode        | OFF
                                         |
| rbr_exec_mode            | STRICT
                                         |
| replica_exec_mode        | STRICT
                                         |
| slave_exec_mode          | STRICT
                                         |
| sql_mode                 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
| ssl_fips_mode            | OFF
                                         |
| ssl_session_cache_mode   | ON
                                         |
+--------------------------+-----------------------------------------------------------------------------------------------------------------------+
13 rows in set, 1 warning (0.00 sec)

修改命令（覆盖模式）

set session sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH';

再次查看长度

mysql> select char_length(name) from char_1;
+-------------------+
| char_length(name) |
+-------------------+
|                 4 |
+-------------------+
1 row in set (0.00 sec)

mysql> select char_length(name) from char_2;
+-------------------+
| char_length(name) |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

5.char与varcher比较

char：
- 缺点：浪费空间
- 优点：存储简单
- 直接按照固定的字符存储数据即可
varchar：
- 优点：节省空间
- 缺点：存储麻烦
  - 存的时候，在真正数据的前面加报头（表示数据真正大小）
  - 取的时候，需要先读取报头，才能读取真实的数据

以前用 char 现在 varchar 使用较多

4）时间类型

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

1.创建表

create table student(
    id int,
    name varchar(16),
	born year,
	birth date,
	study_time time,
	reg_time datetime
);
# 查看
mysql> desc student;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int         | YES  |     | NULL    |       |
| name       | varchar(16) | YES  |     | NULL    |       |
| born       | year        | YES  |     | NULL    |       |
| birth      | date        | YES  |     | NULL    |       |
| study_time | time        | YES  |     | NULL    |       |
| reg_time   | datetime    | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

2.插入数据

insert into student values(
    1,
    'ST1',
    '2000',
	'2001-1-31',
    '11:11:11',
    '2023-6-30 11:11:11'
);
# 查看
mysql> select * from student;
+------+------+------+------------+------------+---------------------+
| id   | name | born | birth      | study_time | reg_time            |
+------+------+------+------------+------------+---------------------+
|    1 | ST1  | 2000 | 2001-01-31 | 11:11:11   | 2023-06-30 11:11:11 |
+------+------+------+------------+------------+---------------------+
1 row in set (0.00 sec)

5）枚举与集合类型

类型	名称	功能
Enum	枚举	多选一用枚举类型给多个备选项，但是你只能选一个
Set	集合	多选多用集合类型给多个备选项，但是你能选多个

1.枚举

创建表

create table user_enum(
	id int,
	name char(10),
	class enum('class_1','class_2','class_3')
);
# 查看
mysql> desc user_enum;
+-------+-------------------------------------+------+-----+---------+-------+
| Field | Type                                | Null | Key | Default | Extra |
+-------+-------------------------------------+------+-----+---------+-------+
| id    | int                                 | YES  |     | NULL    |       |
| name  | char(10)                            | YES  |     | NULL    |       |
| class | enum('class_1','class_2','class_3') | YES  |     | NULL    |       |
+-------+-------------------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

插入数据

insert into user_enum values(1,'ST1','class_2');
# 查看
mysql> select * from user_enum;
+------+------------+---------+
| id   | name       | class   |
+------+------------+---------+
|    1 | ST1        | class_2 |
+------+------------+---------+
1 row in set (0.00 sec)

# 插入其他会报错
mysql> insert into user_enum values(1,'ST1','class_4');
ERROR 1265 (01000): Data truncated for column 'class' at row 1

2.集合

创建表

create table user_set(
	id int,
	name char(10),
	hobby set('swimming','study','running')
);
# 查看
mysql> desc user_set;
+-------+-----------------------------------+------+-----+---------+-------+
| Field | Type                              | Null | Key | Default | Extra |
+-------+-----------------------------------+------+-----+---------+-------+
| id    | int                               | YES  |     | NULL    |       |
| name  | char(10)                          | YES  |     | NULL    |       |
| hobby | set('swimming','study','running') | YES  |     | NULL    |       |
+-------+-----------------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

插入数据

insert into user_set values(
1,
'ST1',
'swimming,running'
);
# 查看
mysql> select * from user_set;
+------+------------+------------------+
| id   | name       | hobby            |
+------+------------+------------------+
|    1 | ST1        | swimming,running |
+------+------------+------------------+
1 row in set (0.00 sec)

可多选，但不选、错选会报错

标签：set,32,0.00,MySQL,数据类型,char,sec,mysql,id
From： https://www.cnblogs.com/Mist-/p/18223778

32.MySQL数据库【二】存储引擎与基本数据类型.

【一】MySQL之存储引擎

1）介绍

2）MySQL主要的存储引擎

1.innodb

2.myisam

3.memory

4.blackhole

5.查看所有数据库存储引擎

3）不同引擎存储数据的特点

1.Innodb

2.myisam

3.blackhole

4.memory

【二】约束条件（null）

【三】严格模式

1）模糊匹配、查询

2）查看严格模式

3）修改严格模式

1.只在当前窗口有效（临时修改）

2.全局有效（永久修改）

3.语法

【四】基本数据类型

1）整形

1.以tinyint存储

2.约束条件之unsigned（无符号）

3.字段类型的宽度

4.约束条件之zerofill（0填充至指定位数）

2）浮点型

1.存储限制

2.创建表

3.插入数据

4.查看数据

3）字符类型

1.创建表

2.插入数据

3.char_length（查看长度）

4.取消默认去除空格

5.char与varcher比较

4）时间类型

1.创建表

2.插入数据

5）枚举与集合类型

1.枚举

2.集合

相关文章

赞助商

阅读排行