文章目录
字符编码与配置文件
查看数据库基本信息(用户，字符编码)：\s
MySQL5.X系列 显示的编码有：
latin1, gbk等
字符编码的统一
引言
因为MySQL5.X版本编码不统一，会造成乱码，我们需要统一修改为>>>:utf8
我们需要对配置文件my-default.ini进行操作
操作步骤：
步骤一：我们先复制一份my-default.ini文件，将其重命名为my.ini
步骤二：清空my.ini文件里的所有代码
步骤三：添加固定的字符编码相关的配置代码
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
注意：如果配置文件涉及到mysqld相关的操作时，需要重启服务端才可以生效
net stop mysql
net start mysql
mysql> \s
--------------
mysql Ver 14.14 Distrib 5.6.44, for Win64 (x86_64)
Connection id: 1
Current database: ddd
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.6.44 MySQL Community Server (GPL)
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 1 hour 49 min 41 sec
Threads: 1 Questions: 56 Slow queries: 0 Opens: 79 Flush tables: 1 Open tables: 72 Queries per second avg: 0.008
--------------
偷懒操作：再配置文件中的mysql下提前写好用户名和密码之后，使用mysql的时候，就只需要输入mysql即可。
存储引擎
-
理解什么是存储引擎?
可以理解为就是针对相同的数据采用的不同的存储策略。 eg：同一个文件不同的人有不同的存储方式
查看所有的存储引擎：show engines;
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.02 sec)
需要掌握的存储引擎
-
MyISAMMySQL5.5及之前版本默认的存储引擎
存取数据的速度快 但是功能较少 安全性较低 -
InnoDBMySQL5.5之后版本默认的存储引擎
存取数据的速度没有MyISAM快 但是支持事务、行锁、外键等诸多功能，安全性较高 -
Memory基于内存的存储引擎 存取数据极快 但是断电立刻丢失
-
BlackHole黑洞 任何写进去的数据都会立刻丢失 类似于垃圾站
可以自定义选择存储的格式：
代码实现:
create table t2(id int) engine=innodb;
create table t3(id int) engine=myisam;
create table t4(id int) engine=memory;
create table t5(id int) engine=blackhole;
BlackHole
mysql> show create table t5;
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------+
| t5 | CREATE TABLE `t5` (
`id` int(11) DEFAULT NULL
) ENGINE=BLACKHOLE DEFAULT CHARSET=gbk |
+-------+----------------------------------------------------------------------------------------+
1 row in set (0.05 sec)
mysql> select * from t5;
Empty set (0.00 sec)
mysql> desc t5;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.06 sec)
mysql> insert into t5 values(1);
Query OK, 1 row affected (0.05 sec)
mysql> select * from t5;
Empty set (0.00 sec)
强调：
MySQL中是可以忽略大小写的
InnoDB(2个)
.frm 表结构
.ibd 表数据，表索引(加快数据查询)
MyISAM(3个)
.frm 表结构
.MYD 表数据
.MYI 表索引(加快数据的查询)
Memory(1个)
.frm 表结构
BlackHole(1个)
.frm (表结构)
MySQL字段类型
创建表的完整语句
create table 表名(
字段名1 字段类型(数字) 约束条件,
字段名2 字段类型(数字) 约束条件,
字段名3 字段类型(数字) 约束条件
)engine=存储引擎;
注意：
1.字段名和字段类型是必须有的
2.数字和约束条件是可选的(可有可无)
3.约束条件可以写多个，空格隔开即可
4.最后一个字段的结尾不能加逗号
字段类型之整型
| 分类名 | 大小 | 范围 |
|---|---|---|
| tinyint | 1bytes | 带符号的范围是-128到127。无符号的范围是0到255。 |
| smallint | 2bytes | 带符号的范围是-32768到32767；无符号的范围是0到65535。 |
| int | 4bytes | 带符号的范围是-2147483648到2147483647；无符号的范围是0到4294967295。 |
| bigint | 8bytes | 带符号的范围是-9223372036854775808到9223372036854775807；无符号的范围是0到18446744073709551615。 |
注意：
1.要考虑正负号的情况，如果需要存储负数的数据，需要占据一个比特位
2.注意如果是要存储手机号的话，我们需要使用bigint才可以
3.上述整型的区别在于从上往下能够存储数据的范围越来越大
验证整型是否自带符号
mysql> create table t2(id tinyint);
Query OK, 0 rows affected (0.11 sec)
mysql> insert into t1 values(-129),(256);
Query OK, 2 rows affected, 2 warnings (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 2
mysql> select * from t1;
+------+
| id |
+------+
| -128 |
| 127 |
+------+
2 rows in set (0.05 sec)
ps:发现自动填写为两个边界值 数据失真 没有实际意义
上述所有的整型类型默认都会带有负号
自定义移除负号
关键字：unsigned
约束条件之一，意思是不需要负号
mysql> create table t2(id tinyint unsigned);
Query OK, 0 rows affected (0.05 sec)
mysql> insert into t2 values(-129), (256);
Query OK, 2 rows affected, 2 warnings (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 2
mysql> select * from t2;
+------+
| id |
+------+
| 0 |
| 255 |
+------+
2 rows in set (0.01 sec)
问题：
插入的数据值超出了数据类型的范围 不应该让其插入并自动修改 没有意义
数据库应该直接报错(这个特性其实是有的 只是被我们改了>>>:配置文件)
解决问题的两种方法：
方式一(命令临时修改)
set session sql_mode='strict_trans_tables' 当前客户端操作界面有效
set global sql_mode='STRICT_TRANS_TABLES' 服务端不重启永久有效
方式二(配置文件永久修改)
在文件my.ini里的[mysqld]加上一行sql_mode='STRICT_TRANS_TABLES'
mysql> show variables like '%mode%';
+----------------------------+---------------------+
| Variable_name | Value |
+----------------------------+---------------------+
| binlogging_impossible_mode | IGNORE_ERROR |
| block_encryption_mode | aes-128-ecb |
| gtid_mode | OFF |
| innodb_autoinc_lock_mode | 1 |
| innodb_strict_mode | OFF |
| pseudo_slave_mode | OFF |
| slave_exec_mode | STRICT |
| sql_mode | STRICT_TRANS_TABLES |
+----------------------------+---------------------+
8 rows in set (0.05 sec)
字段类型之浮点型
| 分类名 | 存储大小 |
|---|---|
| float | 单精度 存储4x8=32位的长度 内存中占 4 个字节 |
| double | 双精度 存储8x8=64位的长度 内存中占 8 个字节 |
| decimal | 数据存储形式是，将每9位十进制数存储为4个字节(eg:存储金额（3888.00元）的字段) |
存储方式：
float(255,30) 总共255位 小数占30位
double(255,30) 总共255位 小数占30位
decimal(65,30) 总共65位 小数占30位
验证精确度问题
mysql> create table t3(id float(255,30));
Query OK, 0 rows affected (0.06 sec)
mysql> create table t4(id double(255,30));
Query OK, 0 rows affected (0.06 sec)
mysql> create table t5(id decimal(65,30));
Query OK, 0 rows affected (0.05 sec)
mysql> insert into t3 values(1.11111111111111111111111111111);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t4 values(1.11111111111111111111111111111);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t5 values(1.11111111111111111111111111111);
Query OK, 1 row affected (0.05 sec)
mysql> select * from t3;
+----------------------------------+
| id |
+----------------------------------+
| 1.111111164093017600000000000000 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> select * from t4;
+----------------------------------+
| id |
+----------------------------------+
| 1.111111111111111200000000000000 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> select * from t5;
+----------------------------------+
| id |
+----------------------------------+
| 1.111111111111111111111111111110 |
+----------------------------------+
1 row in set (0.00 sec)
结论：
float < double < decimal
正常业务使用float就可以了
高精度数据，可以使用decimal
字段类型之字符类型
| 分类名 | 范围 | 特点 |
|---|---|---|
| char | 0~255 | 定长 |
| varchar | 0~65535 | 变长 |
具体说明：
char(4)
最大只能存储四个字符，如果超过范围则直接报错
如果不超出范围，不够的位数会自动用空格填充
varchar(4)
最大只能存储四个字符，如果超过范围则直接报错
如果不超出范围，则有几位就存几位
验证定长和变长的特性
mysql> create table t6(id int, name char(4));
Query OK, 0 rows affected (0.11 sec)
mysql> create table t7(id int, name varchar(4));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t6 values(1,'jason');
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> insert into t7 values(2, 'jason');
ERROR 1406 (22001): Data too long for column 'name' at row 1
结论：
超出范围都会报错
如果是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> select char_length(id) from t15;
+-----------------+
| char_length(id) |
+-----------------+
| 1 |
| 1 |
+-----------------+
2 rows in set (0.05 sec)
char与varchar的区别
char
优：整存整取，速度快
劣：浪费存储空间
varchar
优：节省存储空间
劣：存取数据的速度慢于char
1.varchar在存数据的时候会生成一个1bytes的报头 记录数据长度
2.varchar在取数据的时候先会读取1bytes的报头 从中获取真实数据长度
3.VARCHAR类型可以根据实际内容动态改变存储值的长度，因此在不能确定字段需要多少字符时使用VARCHAR类型可以有效地节约磁盘空间、提高存储效率。
使用场景：
针对统一中国人的姓名 应该采取那个类型 >>> varchar
规模较小 数据量相对固定的字典 >>> char
数字的含义
数字大部分情况下都是用来限制字段的存储长度 但是整型除外!!!
不是用来限制存储的长度 而是展示的长度
zerofill
格式为字段名 int(M) zerofill。在插入数据时，当该字段的值的长度小于定义的长度时，会在该值的前面补上相应的0。
mysql> create table t8(id int(3));
Query OK, 0 rows affected (0.10 sec)
mysql> create table t9(id int(3) zerofill);
Query OK, 0 rows affected (0.11 sec)
mysql> insert into t8 values(1);
Query OK, 1 row affected (0.05 sec)
mysql> insert into t9 values(1);
Query OK, 1 row affected (0.05 sec)
mysql> select * from t8;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> select * from t9;
+------+
| id |
+------+
| 001 |
+------+
1 row in set (0.00 sec)
总结:
以后涉及到整型字段的定义 类型后面不需要加括号写数字 除非有业务需求必须固定位数
字段类型之枚举与集合
枚举
多选一
enum：只能在给定的范围内选一个值，如性别 sex 男male/女female
集合
多选多(包含多选一)
set：在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3…）
枚举
create table t10(
id int,
name varchar(32),
gender enum('male','female','others')
);
集合
create table t11(
id int,
name varchar(32),
hobbies set('read','run','music','rap')
);
字段类型之日期类型
| 分类名 | 格式 |
|---|---|
| date | 年月日 |
| datetime | 年月日,时分秒 |
| time | 时分秒 |
| year | 年份 |
mysql> create table t13(
-> id int,
-> name varchar(32),
-> birth date,
-> reg_time datetime,
-> study_time time,
-> join_time year
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t13 values(1,'jason','2022-11-11','2000-11-11 11:11:11','11:11:11','1995');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t13;
+------+-------+------------+---------------------+------------+-----------+
| id | name | birth | reg_time | study_time | join_time |
+------+-------+------------+---------------------+------------+-----------+
| 1 | jason | 2022-11-11 | 2000-11-11 11:11:11 | 11:11:11 | 1995 |
+------+-------+------------+---------------------+------------+-----------+
1 row in set (0.00 sec)
字段约束条件
概念
约束是一种限制，通过对表中的数据做出限制，来确保表中数据完整性，唯一性
1.unsigned
无需正负号
eg：id int unsigned
2.zerofill
在插入数据时，当该字段的值的长度小于定义的长度时，会在该值的前面补上相应的0。
eg：id int zero
3.not null
不能为空
eg：name varchar(32) not null
注意：空字符串不等于null
4.default
默认值
插入数据时，如果没有明确为字段赋值，则自动赋予默认值，在没有默认值的情况下，默认为null
mysql> create table t14(
-> id int,
-> name varchar(32) default 'lzq'
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t14(id) values(1);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t14;
+------+------+
| id | name |
+------+------+
| 1 | lzq |
+------+------+
1 row in set (0.00 sec)
5.unique
唯一值
限制一个字段的值不重复，该字段的数据不能出现重复。确保字段中的值唯一
eg：id int unique 单列唯一
eg：unique(字段名，字段名) 联合唯一
mysql> create table t15(
-> id int unique,
-> name varchar(32) default 'lzq'
-> );
Query OK, 0 rows affected (0.11 sec)
mysql> insert into t15(id) values(1);
Query OK, 1 row affected (0.05 sec)
mysql> insert into t15(id) values(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'id'
mysql> insert into t15(id) values(2);
Query OK, 1 row affected (0.00 sec)
课外补充：
二进制类型
MySQL 支持两类字符型数据：文本字符串和二进制字符串。
二进制字符串类型有时候也直接被称为“二进制类型”。
MySQL 中的二进制字符串有 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。
M为指定长度
| 类型名称 | 说明 | 存储需求 |
|---|---|---|
| BIT(M) | 位字段类型 | 大约 (M+7)/8 字节 |
| BINARY(M) | 固定长度二进制字符串 | M 字节 |
| VARBINARY (M) | 可变长度二进制字符串 | M+1 字节 |
| TINYBLOB (M) | 非常小的BLOB | L+1 字节，在此，L<2^8 |
| BLOB (M) | 小 BLOB | L+2 字节，在此，L<2^16 |
| MEDIUMBLOB (M) | 中等大小的BLOB | L+3 字节，在此，L<2^24 |
| LONGBLOB (M) | 非常大的BLOB | L+4 字节，在此，L<2^32 |
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
