MySQL数据库入门到大蛇尚硅谷宋红康老师笔记高级篇part1

第01章_Linux下MySQL的安装与使用

首先在vmware中下载centos7，实际上8更好一点，不过centos已经是时代的眼泪了，我之前已经教过了，不过是忘了，所以重新说一遍，看文档即可

2.开机前修改mac地址

（注意：如果是动态生成IP地址，请在启动前先启动被克隆的虚拟机，以保证原来虚拟机的ip不会变，否则原来虚拟机ip会变，克隆后的虚拟机ip是原来的虚拟机ip）

点击编辑虚拟机设置,选择网络适配器，点击右下角高级然后下边就是mac地址，这个mac地址和被克隆的是一样的，我们点击生成，重新生成一个新的： 

3.开机后修改主机名称(这步不用改也可以)

右键打开linux的终端  输入如下：             （ 提示没有文件的，需要在vim后面加个空格

vim /etc/hostname 

这样就可以修改主机名了，接下来就是vim的基本操作，i解锁，慢慢输入，:wq保存 回到终端reboot。

 4.修改IP地址

此处需要注意的是：如果虚拟机使用的是动态ip分配，那么不需要更改ip，如果想改为静态ip，请修改：

vim /etc/sysconfig/network-scripts/ifcfg-ens33可以看到本机IP是192.168.169.130，或者右上角打开网络，设置，即可看到ip，dns所有东西了

5. 修改UUID

同样在修改IP地址的路径中修改即可，网络重启 systemctl restart network

安装有 Xshell 和 Xftp 等访问CentOS系统的工具

CentOS6和CentOS7在MySQL的使用中的区别 1. 防火墙：6是iptables，7是firewalld 2. 启动服务的命令：6是service，7是systemctl 

 现在我有三个虚拟机，是centos7，和centos7 1号，以及centos7 2号

centos7 1号 centos7 2号

2.mysql在linux的安装 MySQL Community Server 社区版本，开源免费，自由下载，但不提供官方技术支持，适用于 大多数普通用户。MySQL Enterprise Edition 企业版本，需付费，不能在线下载，可以试用30天。提供了更多的 功能和更完备的技术支持，更适合于对数据库的功能和可靠性要求较高的企业客户。MySQL Cluster 集群版，开源免费。用于架设集群服务器，可将几个MySQL Server封装成一个 Server。需要在社区版或企业版的基础上使用。MySQL Cluster CGE 高级集群版，需付费。

 现在最新版以及是9.2.0版本，最新的LTS是8.4.4版本，选择8.0.27版本，对应的操作系统就是RED HAT即可，相应的linux7，x86-64bit         下载第一个rpm bundle解压即可

在/opt 中选择client，client-plugins，common，libs和server5个即可，有人说直接用docker 拉取更方便，但是懒得学，以后再写吧

在shell中运行cd /opt然后 ll可以列举出五个文件即可，

1. 检查/tmp临时目录权限（必不可少） 由于mysql安装过程中，会通过mysql用户在/tmp目录下新建tmp_db文件，所以请给/tmp较大的权限。执行 ： chmod -R 777 /tmp

2. 安装前，检查依赖 rpm -qa|grep libaio 如果存在libaio包如下： rpm -qa|grep net-tools 如果存在net-tools包如下： rpm -qa|grep net-tools 如果不存在需要到centos安装盘里进行rpm安装。 安装linux如果带图形化界面，这些都是安装好 的

1. 将安装程序拷贝到/opt目录下

在mysql的安装文件目录下执行：（必须按照顺序执行）

rpm -ivh mysql-community-common-8.0.25-1.el7.x86_64.rpmrpm -ivh mysql-community-client-plugins-8.0.25-1.el7.x86_64.rpmrpm -ivh mysql-community-libs-8.0.25-1.el7.x86_64.rpmrpm -ivh mysql-community-client-8.0.25-1.el7.x86_64.rpmrpm -ivh mysql-community-server-8.0.25-1.el7.x86_64.rpm 

3. 查看MySQL版本

mysql --version     或    mysqladmin --version 

 或者rpm -qa|grep -i mysql           呈现mysql的工具

4. 服务的初始化

为了保证数据库目录与文件的所有者为 mysql 登录用户，如果你是以 root 身份运行 mysql 服务，需要执 行下面的命令初始化： mysqld --initialize --user=mysql

说明： --initialize 选项默认以“安全”模式来初始化，则会为 root 用户生成一个密码并将该密码标记为过 期，登录后你需要设置一个新的密码。生成的 临时密码会往日志中记录一份。 查看密码： cat /var/log/mysqld.log

 systemctl status mysqld检测mysql是否打开，

启动：systemctl start mysqld.service

关闭：systemctl stop mysqld.service

重启：systemctl restart mysqld.service

查看状态：systemctl status mysqld.service

6. 查看MySQL服务是否自启动

systemctl list-unit-files|grep mysqld.service 默认是enabled。

如不是enabled可以运行如下命令设置自启动 systemctl enable mysqld.service 如果希望不进行自启动，运行如下命令设置 systemctl disable mysqld.service

3. MySQL登录

通过 mysql -hlocalhost -P3306 -uroot -p 进行登录，在Enter password：录入初始化密码：我的是xuO-*layz92W

因为初始化密码默认是过期的，所以查看数据库会报错 修改密码： ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password'; 5.7版本之后（不含5.7），mysql加入了全新的密码安全机制。设置新密码太简单会报错

 我修改密码为abc123

 接下来继续完成其他两台的mysql安装即可，原模原样即可，.

3.3 设置远程登录

在用SQLyog或Navicat中配置远程连接Mysql数据库时遇到如下1130代号报错信息，这是由于Mysql配置了不支持远 程连接引起的

1.在远程机器上使用ping ip地址 保证网络畅通

2.在远程机器上使用telnet命令 保证端口号开放访问（在控制面板中打开“talent客户端”） telnet ip地址 端口号

关下windows和centos的防火墙，实际上可以选择开放端口

 

下面这个在navicat中不一定会出现，记得修改即可 

 4. MySQL8的密码强度评估（了解）

因为是了解，所以不在阐述

5. 字符集的相关操作 5.1 修改MySQL5.7字符集

1. 修改步骤

在MySQL 8.0版本之前，默认字符集为 latin1 ，utf8字符集指向的是 utf8mb3 。网站开发人员在数据库 设计的时候往往会将编码修改为utf8字符集。如果遗忘修改默认的编码，就会出现乱码的问题。从MySQL 8.0开始，数据库的默认编码将改为 utf8mb4 ，从而避免上述乱码的问题。

 操作1：查看默认使用的字符集

show variables like 'character%'; # 或者 show variables like '%char%';

MySQL 5.7 默认的客户端和服务器都用了 latin1 ，不支持中文，保存中文会报错。

操作2：修改字符集

vim /etc/my f

在MySQL5.7或之前的版本中，在文件最后加上中文字符集配置： character_set_server=utf8

操作3：重新启动MySQL服务

systemctl restart mysqld

但是原库、原表的设定不会发生变化，参数修改只对新建的数据库生效。 

2. 已有库&表字符集的变更 

MySQL5.7版本中，以前创建的库，创建的表字符集还是latin1。

修改已创建数据库的字符集 alter database dbtest1 character set 'utf8'; 修改已创建数据表的字符集 alter table t_emp convert to character set 'utf8';

注意：但是原有的数据如果是用非'utf8'编码的话，数据本身编码不会发生改变。已有数据需要导 出或删除，然后重新插入。

---

 5.2 各级别的字符集

MySQL有4个级别的字符集和比较规则，分别是： 服务器级别 数据库级别 表级别 列级别

character_set_server：服务器级别的字符集 character_set_database：当前数据库的字符集 character_set_client：服务器解码请求时使用的字符集 character_set_connection：服务器处理请求时会把请求字符串从character_set_client转为 character_set_connection character_set_results：服务器向客户端返回数据时使用的字符集 

5.3 字符集与比较规则(了解)

utf8 字符集表示一个字符需要使用1～4个字节，但是我们常用的一些字符使用1～3个字节就可以表示 了。而字符集表示一个字符所用的最大字节长度，在某些方面会影响系统的存储和性能，所以设计 MySQL的设计者偷偷的定义了两个概念： utf8mb3 ：阉割过的 utf8 字符集，只使用1～3个字节表示字符。 utf8mb4 ：正宗的 utf8 字符集，使用1～4个字节表示字符。

上表中，MySQL版本一共支持41种字符集，

#查看GBK字符集的比较规则 SHOW COLLATION LIKE 'gbk%'; #查看UTF-8字符集的比较规则 SHOW COLLATION LIKE 'utf8%'; # 查看服务器的字符集和比较规则 SHOW VARIABLES LIKE '%_server'; # 查看数据库的字符集和比较规则 SHOW VARIABLES LIKE '%_database'; # 查看具体数据库的字符集 SHOW CREATE DATABASE dbtest1; # 修改具体数据库的字符集 ALTER DATABASE dbtest1 DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'; USE dbtest1; # 查看表的字符集 SHOW CREATE TABLE empl; # 查看表的比较规则 SHOW TABLE STATUS FROM dbtest1 LIKE '%empl'; # 修改表的字符集和比较规则 ALTER TABLE empl DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'; 5.4 请求到响应过程中字符集的变化

为了体现出字符集在请求处理过程中的变化，我们这里特意修改一个系统变量的值： mysql> set character_set_connection = gbk; Query OK, 0 rows affected (0.00 sec)

现在假设我们客户端发送的请求是下边这个字符串： SELECT * FROM t WHERE s = '我';

为了方便大家理解这个过程，我们只分析字符“我”在这个过程中字符集的转换。 现在看一下在请求从发送到结果返回过程中字符集的变化： 1. 客户端发送请求所使用的字符集 一般情况下客户端所使用的字符集和当前操作系统一致，不同操作系统使用的字符集可能不一样，如下：    - 类 Unix 系统使用的是 utf8    - Windows 使用的是 gbk 当客户端使用的是 utf8 字符集，字符“我”在发送给服务器的请求中的字节形式就是：    0xE68891

提示 如果你使用的是可视化工具，比如navicat之类的，这些工具可能会使用自定义的字符集来编码发送到服务器的字符串，而不采用操作系统默认的字符集（所以在学习的时候还是尽量用命令行窗口）。

2. 服务器接收到客户端发送来的请求其实是一串二进制的字节，它会认为这串字节采用的字符集是 character_set_client，然后把这串字节转换为 character_set_connection 字符集编码的字符。 由于我的计算机上 character_set_client 的值是 utf8，首先会按照 utf8 字符集对字节串 0xE68891 进行解码，得到的字符串就是“我”，然后按照 character_set_connection 代表的字符集，也就是 gbk 进行编码，得到的结果就是字节串 0xCED2。

3. 因为表 t 的列 col 采用的是 gbk 字符集，与 character_set_connection 一致，所以直接到列中找字节值为 0xCED2 的记录，最后找到了一条记录。

提示 如果某个列使用的字符集和 character_set_connection 代表的字符集不一致的话，还需要进行一次字符集转换。

4. 上一步骤找到的记录中的 col 列其实是一个字节串 0xCED2，col 列是采用 gbk 进行编码的，所以首先会将这个字节串用 gbk 进行解码，得到字符串“我”，然后再把这个字符串使用 character_set_results 代表的字符集，也就是 utf8 进行编码，得到了新的字节串：0xE68891，然后发送给客户端。

5. 由于客户端是用的字符集是 utf8，所以可以顺利的将 0xE68891 解释成字符“我”，从而显示到我们的显示器上，所以我们人类也读懂了返回的结果。

6. SQL大小写规范 

在 SQL 中，关键字和函数名是不用区分字母大小写的，比如 SELECT、WHERE、ORDER、GROUP BY 等关 键字，以及 ABS、MOD、ROUND、MAX 等函数名。

不过在 SQL 中，你还是要确定大小写的规范，因为在 Linux 和 Windows 环境下，你可能会遇到不同的大 小写问题。 windows系统默认大小写不敏感，但是 linux系统是大小写敏感的。

通过如下命令查看： SHOW VARIABLES LIKE '%lower_case_table_names%' 

lower_case_table_names参数值的设置： 默认为0，大小写敏感。 设置1，大小写不敏感。创建的表，数据库都是以小写形式存放在磁盘上，对于sql语句都是转 换为小写对表和数据库进行查找。 设置2，创建的表和数据库依据语句上格式存放，凡是查找都是转换为小写进行。 

MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的：

1、数据库名、表名、表的别名、变量名是严格区分大小写的；

2、关键字、函数名称在 SQL 中不区分大小写；

3、列名（或字段名）与列的别名（或字段别名）在所有的情况下均是忽略大小写的； MySQL在Windows的环境下全部不区分大小写

6.2 Linux下大小写规则设置

当想设置为大小写不敏感时，要在 my f 这个配置文件 [mysqld] 中加入 lower_case_table_names=1 ，然后重启服务器。 

但是要在重启数据库实例之前就需要将原来的数据库和表转换为小写，否则将找不到数据库名。此参数适用于MySQL5.7。在MySQL 8下禁止在重新启动 MySQL 服务时将 lower_case_table_names 设置成不同于初始化 MySQL 服务时设置的 lower_case_table_names 值。如果非要将MySQL8设置为大小写不敏感，具体步骤为： 1、停止MySQL服务 2、删除数据目录，即删除 /var/lib/mysql 目录 3、在MySQL配置文件（ /etc/my f ）中添加 lower_case_table_names=1 4、启动MySQL服务  7. sql_mode的合理设置

sql_mode 会影响 MySQL 支持的 SQL 语法以及它执行的 数据验证检查。通过设置 sql_mode，可以完成不同严格程度的数据校验，有效地保障数据准确性。

MySQL 服务器可以在不同的 SQL 模式下运行，并且可以针对不同的客户端以不同的方式应用这些模式，具体取决于 sql_mode 系统变量的值。

MySQL 5.6 和 MySQL 5.7 默认的 sql_mode 模式参数是不一样的： - 5.6 的 mode 默认值为空（即：NO_ENGINE_SUBSTITUTION），其实表示的是一个空值，相当于没有什么模式设置，可以理解为宽松模式。在这种设置下是可以允许一些非法操作的，比如允许一些非法数据的插入。 - 5.7 的 mode 是 STRICT_TRANS_TABLES，也就是 严格模式。用于进行数据的严格校验，错误数据不能插入，报 error（错误），并且事务回滚。

7.2 宽松模式 vs 严格模式

宽松模式： 如果设置的是宽松模式，那么我们在插入数据的时候，即便是给了一个错误的数据，也可能会被接受， 并且不报错。

举例：我在创建一个表时，该表中有一个字段为name，给name设置的字段类型时 在插入数据的时候，其中name这个字段对应的有一条数据的 char(10) ，如果我 长度超过了10，例如'1234567890abc'，超 过了设定的字段长度10，那么不会报错，并且取前10个字符存上，也就是说你这个数据被存为 了'1234567890'，而'abc'就没有了。但是，我们给的这条数据是错误的，因为超过了字段长度，但是并没 有报错，并且mysql自行处理并接受了，这就是宽松模式的效果。

应用场景：通过设置sql mode为宽松模式，来保证大多数sql符合标准的sql语法，这样应用在不同数据 库之间进行 迁移时，则不需要对业务sql 进行较大的修改。

严格模式： 出现上面宽松模式的错误，应该报错才对，所以MySQL5.7版本就将sql_mode默认值改为了严格模式。所 以在生 产等环境中，我们必须采用的是严格模式，进而 开发、测试环境的数据库也必须要设置，这样在 开发测试阶段就可以发现问题。并且我们即便是用的MySQL5.6，也应该自行将其改为严格模式。

开发经验：MySQL等数据库总想把关于数据的所有操作都自己包揽下来，包括数据的校验，其实开发 中，我们应该在自己 开发的项目程序级别将这些校验给做了，虽然写项目的时候麻烦了一些步骤，但是这 样做之后，我们在进行数据库迁移或者在项目的迁移时，就会方便很多。

改为严格模式后可能会存在的问题： 若设置模式中包含了 NO_ZERO_DATE ，那么MySQL数据库不允许插入零日期，插入零日期会抛出错误而 不是警告。例如，表中含字段TIMESTAMP列（如果未声明为NULL或显示DEFAULT子句）将自动分配 DEFAULT '0000-00-00 00:00:00'（零时间戳），这显然是不满足sql_mode中的NO_ZERO_DATE而报错。

CREATE TABLE mytbl2 (id INT, NAME VARCHAR(16), age INT, dept INT) ; INSERT INTO mytbl2 VALUES (1, 'zhang3', 33, 101); INSERT INTO mytbl2 VALUES (2, 'li4', 34, 101); INSERT INTO mytbl2 VALUES (3, 'wang5', 34, 102); INSERT INTO mytbl2 VALUES (4, 'zhao6', 34, 102); INSERT INTO mytbl2 VALUES (5, 'tian7', 36, 102); # 查询每个部门年龄最大的人 SELECT NAME, dept, MAX(age) FROM mytbl2 GROUP BY dept ; # 查询每个部门年龄最大的人 SELECT NAME, dept, MAX(age) FROM mytbl2 GROUP BY dept ; # 设置sql_mode SET SESSION sql_mode = '';