字符编码、配置文件、存储引擎、mysql的相关语法
学习内容总结
今日学习内容总结
昨天，我们开始了新的学习内容，数据库。我们从数据库的演变史，到发展史，到本质，最后到使用。而今天的主要学习内容主要也是对sql语法的学习。
字符编码与配置文件
因为我们学习数据库，：MySQL是大家必备的数据库，但在服务器上有时新装的MySQL默认编码格式不正确，导致无法正常显示中文，因此需要重设默认编码。
查看MySQL默认字符编码
# mysql可以通过这些命令来查看默认的字符编码
\s
show variables like '%char%';
上述代码需要连接进入MySQL后执行。通用模式为：
| Variable_name | Value |
+--------------------------+---------------------------------------------------------+
| character_set_client | utf8mb3 |
| character_set_connection | utf8mb3 |
| character_set_database | utf8mb3 |
| character_set_filesystem | binary |
| character_set_results | utf8mb3 |
| character_set_server | utf8mb3 |
| character_set_system | utf8mb3 |
| character_sets_dir | C:\Program Files\MySQL\MySQL Server 8.0\share\charsets\ |
如果是5.x系列，显示的默认编码大部分是这样的:
| Variable_name | Value |
+--------------------------+---------------------------------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8mb3 |
| character_sets_dir | C:\Program Files\MySQL\MySQL Server 8.0\share\charsets\ |
如果是8.x系列，其实中有两条是gbk的。
统一字符编码
5.x系列：
my-default.ini配置文件
步骤1:拷贝一份该配置文件并修改名称为my.ini
步骤2:清空my.ini文件内的内容
步骤3:添加固定的配置信息即可
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
步骤4:保存并重启服务端即可生效
net stop mysql
net start mysql
8.x系列：
一般修改mysql的默认编码都是修改在安装目录下的my.ini文件或者my-default.ini文件方可修改。但是在Mysql8.0的安装目录下是没有my.ini文件的。该文件应该在 C:\ProgramData\MySQL\MySQL Server 8.0 文件夹内而不是 C:\Program Files\MySQL\MySQL Server 8.0 内。修改费方式：
#带中括号的是文件内自带的标签如果没有可以自行加上
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8
collation-server = utf8_unicode_ci
init_connect='SET NAMES utf8'
修改后重启服务输入show variables like "%char%";查看编码是否成功如果和上面我的那个一样全是utf8说明成功了，其他编码类型在上面改换汤不换药。
存储引擎
什么是存储引擎
数据库存储引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。
存储引擎可以理解为处理数据的不同方式。
查看存储引擎的方式: 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 |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
需要了解的引擎介绍：
MyISAM
5.1之前版本MySQL默认的存储引擎
特点:存取数据的速度快 但是功能很少 安全性较低
InnoDB
5.1之后版本MySQL默认的存储引擎
特点:有诸多功能 安全性较高 存取速度没有MyISAM快
BlackHole
任何写入的数据都会立刻消失(类似于垃圾回收处理站)
Memory
以内存作为数据存取地 速度快但是断电立刻丢失
自定义选择存储引擎
create table t1(id int)engine=myisam;
create table t2(id int)engine=innodb;
create table t3(id int)engine=blackhole;
create table t4(id int)engine=memory;
选择mysql存储引擎
不同的存储引擎都有各自的特点，以适应不同的需求，如表所示。为了做出选择，首先要考虑每一个存储引擎提供了哪些不同的功能。
| 功能 | MylSAM | MEMORY | InnoDB | Archive |
|---|---|---|---|---|
| 存储限制 | 256TB | RAM | 64TB | None |
| 支持事务 | No | No | Yes | No |
| 支持全文索引 | Yes | No | No | No |
| 支持树索引 | Yes | Yes | Yes | No |
| 支持哈希索引 | No | Yes | No | No |
可以根据以下的原则来选择 MySQL 存储引擎：
1. 如果要提供提交、回滚和恢复的事务安全（ACID 兼容）能力，并要求实现并发控制，InnoDB 是一个很好的选择。
2. 如果数据表主要用来插入和查询记录，则 MyISAM 引擎提供较高的处理效率。
3. 如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存的 MEMORY 引擎中，MySQL 中使用该引擎作为临时表，存放查询的中间结果。
4. 如果只有 INSERT 和 SELECT 操作，可以选择Archive 引擎，Archive 存储引擎支持高并发的插入操作，但是本身并不是事务安全的。Archive 存储引擎非常适合存储归档数据，如记录日志信息可以使用 Archive 引擎。
# 使用哪一种引擎要根据需要灵活选择，一个数据库中多个表可以使用不同的引擎以满足各种性能和实际需求。使用合适的存储引擎将会提高整个数据库的性能。
mysql创建表之完整语法
create table 表名(
字段名1 字段类型(数字) 约束条件,
字段名2 字段类型(数字) 约束条件,
字段名3 字段类型(数字) 约束条件
);
1.字段名和字段类型是必须的
2.数字和约束条件是可选的
3.约束条件可以写多个 空格隔开即可(后面详细讲解)
字段名1 字段类型(数字) 约束条件1 约束条件2 约束条件3
4.最后一行字段结尾不能加逗号，极其容易被忽略
mysql字段类型
mysql字段类型之整型
mysql中的整型：
上述整型的区别在于从上往下能够存储的数字范围越来越大：
1. tinyint：存储所占一个字节，一个字节等于8bit，根据1bit可以存储0到1两种可能性，因此tinyint类型可以存储2的8次方，也就是256种可能性，从0开始计数，无符号也就是可以 存储0~255，如果是有符号，那就是-128~127。
2. smallint：存储所占两个字节，同上也就是可以存储2的16次方，也就是可以存储65536种可能性，无符号从0开始则是可以存储0~65535，有符号则是-32768~32767。
3. mediumint：存储所占三个字节，也就是2的24次方，可以存储16777216种可能性，无符号可以存储0~16777215，有符号可以存储-8388608~8388607。
4. int：存储所占四个字节，也就是2的32次方，可以存储4294967296种可能性，无符号可以存储0~4294967295，有符号则是-2147483648~2147483647。
5. bigint：存储所占8个字节，也就是2的64次方，可以存储2的64次种可能性，无符号可以存储0~((2³²×²)-1)，有符号则是-(2³²×²)/2 ~ (2³²×²)/2-1。
注意事项
1.需要考虑正负数的问题 如果需要存储负数 则需要占据一个比特位
2.注意手机号如果使用整型来存储 需要使用bigint才可以
小技巧
有时候看似需要使用数字类型存储的数据其实可能使用的是字符串，因为字符串可以解决不同语言对数字不精确的缺陷。
有个问题：我们经常会看到的表的定义：int(11)、int(2)是什么意思呢？
实际上括号里的数字表示的是最大显示宽度，这个数字和存储大小及其类型没有任何关系，也就是说int(11)和int(3)都是存储四个字节，并不会因为括号里的数字改变，这个数字仅仅是显示最大宽度。
显示的最大宽度，其实就是一个数以多少位来显示，不足的位数在其最前面补0；如：int(3)存储3表示为003；int(5)存储23表示为00023.
create table t5(id tinyint);
insert into t5 values(-129),(256);
如果是在5.6版本不会报错，会自动处理成最大范围(没有意义)。
步骤1:set global sql_mode = 'STRICT_TRANS_TABLES';
步骤2:退出客户端 重新登录即可
如果是在5.7及以上版本，则会直接报错(更加合理)。
所有的整型都默认带有正负号，如何修改不带正负号(约束条件)：
create table t6(id tinyint unsigned);
字段类型之浮点型
MySQL支持的三个浮点类型是 FLOAT、DOUBLE 和 DECIMAL 类型。他们的区别在于精度不一样。
float(255,30) 总共255位 小数位占30位
double(255,30) 总共255位 小数位占30位
decimal(65,30) 总共65位 小数位占30位
主要研究精度问题：
mysql> create table temp2(id float(10,2),id2 double(10,2),id3 decimal(10,2));
Query OK, 0 rows affected (0.18 sec)
mysql> insert into temp2 values(1234567.21, 1234567.21,1234567.21),(9876543.21,
-> 9876543.12, 9876543.12);
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from temp2;
+------------+------------+------------+
| id | id2 | id3 |
+------------+------------+------------+
| 1234567.25 | 1234567.21 | 1234567.21 |
| 9876543.00 | 9876543.12 | 9876543.12 |
+------------+------------+------------+
2 rows in set (0.01 sec)
mysql> desc temp2;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id | float(10,2) | YES | | NULL | |
| id2 | double(10,2) | YES | | NULL | |
| id3 | decimal(10,2) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
虽然三者精确度有差距，是具体用哪个应该结合实际情况。比如在正常业务中，使用float足够，如果是高精度，可以使用decimal。
字段类型之字符类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。
char和varchar：
两个字符类型的区别在于一个是定长一个是变长。
char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此。
char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，所以varchar(4),存入3个字符将占用4个字节。
char类型的字符串检索速度要比varchar类型的快。
其定长与变长特性，如果是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在存数据的时候会自动填充空格在取数据的时候又会自动移除空格。
不自动移除空格的方式： set session sql_mode = 'pad_char_to_full_length' 。
数字的含义
字段类型括号内的数字大部分情况下是用来限制存储的长度，但是在整型中并不是用来限制长度，而是用来控制展示长度。
create table t12(id int(3));
insert into t12 values(1111); # 不会报错
create table t13(id int(3) zerofill); # 位数不够用0填充
insert into t13 values(1); # 001
insert into t13 values(1111); # 有几位就展示几位
所以涉及到整型字段，都无需自己定义长度，直接使用自带的即可。而针对其他类型的字段，则需要自己添加数字。
字段类型之枚举与集合
枚举
多选一
create table t14(
id int,
name varchar(32),
gender enum('male','female','others')
);
插入数据的时候，针对gender只能填写提前定义好的数值。
集合
多选多(也可以多选一)
create table t15(
id int,
name varchar(32),
hobby set('篮球','足球','双色球','排球','水球','肉球')
);
字段类型之日期类型
| 功能 | 描述 |
|---|---|
| date | 年月日 |
| datetime | 年月日时分秒 |
| time | 时分秒 |
| year | 年 |
针对时间数据一般都是通过代码自动获取并添加，我们这里手动模拟。
insert into t16 values(1,'jason','2000-11-11','2002-01-21','11:11:11','2015');
# 自动添加方式
create table t16(
id int,
name varchar(32),
reg_time datetime,
birth date,
study_time time,
join_time year
);
约束条件
字段类型与约束条件的关系
约束条件是基于字段类型之上的额外限制
比如：id int unsigned 。字段类型int规定了id字段只能存整数，约束条件unsigned指的是整数基础之上还必须是正数。
无需正负号
unsigned
零填充
zerofill
非空
not null
比如：
create table t18(id int,name varchar(32) not null);
insert into t18(id) values(2); # 报错
insert into t18(id,name) values(2,null); # 报错
insert into t18(id,name) values(2,''); # 不报错
插入数据的另一种方式：打破字段顺序：
create table t17(id int,name varchar(32));
insert into t17(name,id) values('jason',1);
insert into t17(id) values(2);
默认值
default：案例
create table t19(id int,name varchar(32) default 'jason');
insert into t19(id) values(1);
insert into t19(id,name) values(2,'kevin');
唯一值
unique，单列唯一:某个字段下对应的数据不能重复，是唯一的。多列唯一:多个字段下对应的数据组合到一起的结果不能重复 是唯一的。
# 单列唯一
create table t20(
id int,
name varchar(32) unique
);
# 多列唯一
create table t21(
id int,
host varchar(32),
port int,
unique(host,port)
);
主键
primary key ，写法：
create table t22(id int primary key);
1. 单从约束层面上而言 相当于not null + unique(非空且唯一)。
2. 是InnoDB存储引擎规定的一张表有且必须要有一个主键，用于构建表。
主键可以加快数据的查询速度(类似于书的目录)。如果创建表创建的时候没有设置主键也没有其他的键，么InnoDB会采用一个隐藏的字段作为表的主键(隐藏就意味着而无法使用 即无法加快数据查询)。
如果没有主键但是有非空且唯一的字段，那么会自动升级成主键(从上往下的第一个)。
所以创建表应该有一个序号字段(id\pid\cid)并且应该将该字段设置成主键。
还可以设置
联合主键(多个字段组合 本质还是一个主键) ：
create table t24(
id int,
name varchar(32),
pwd int,
primary key(id,pwd)
);
自增
auto_increment 。专门配合主键一起使用，用户以后在添加数据的时候就不需要自己记忆主键值。
所以以后创建规范的表的时候，一般都会有一个主键字段的编写如下：
id int primary key auto_increment
浙公网安备 33010602011771号