❌

阅读视图

↗️

安装MySQL8

✇Liu Zijian's Blog

1.下载

打开官网下载页面: https://downloads.mysql.com/archives/community/，在这里我选择8.0.25版本，Operating System一栏通常没有CentOS，选择RedHat即可，OS Version选择7，X86_64，然后下载第一个mysql-8.0.25-1.el7.x86_64.rpm-bundle.tar

下载完成后，将文件移动到/opt下，并解压，会解压出多个rpm软件包

[root@localhost opt]# tar -xvf mysql-8.0.25-1.el7.x86_64.rpm-bundle.tar mysql-community-client-8.0.25-1.el7.x86_64.rpmmysql-community-client-plugins-8.0.25-1.el7.x86_64.rpmmysql-community-common-8.0.25-1.el7.x86_64.rpmmysql-community-devel-8.0.25-1.el7.x86_64.rpmmysql-community-embedded-compat-8.0.25-1.el7.x86_64.rpmmysql-community-libs-8.0.25-1.el7.x86_64.rpmmysql-community-libs-compat-8.0.25-1.el7.x86_64.rpmmysql-community-server-8.0.25-1.el7.x86_64.rpmmysql-community-test-8.0.25-1.el7.x86_64.rpm

2.安装前准备

2.1 卸载自带的mariadb

安装基于CentOS 7.9.2009

[root@localhost ~]# cat /etc/redhat-release CentOS Linux release 7.9.2009 (Core)

CentOS 7.9系统预装了一个mariadb数据库，这个数据库是MySQL的”姐妹”，都是同一个作者编写的，mariadb会在/etc目录生成一个my.cnf作为配置文件，MySQL安装后也会在/etc目录生成一个my.cnf，因此为了排除干扰，首先要把预装的mariadb卸载掉。

查看是否预装：

[root@localhost ~]# rpm -qa | grep -i mariamariadb-libs-5.5.68-1.el7.x86_64
[root@localhost ~]# ls /etc/ | grep mymy.cnfmy.cnf.d

卸载mariadb

rpm -e --nodeps mariadb-libs

2.2 安装依赖

截至目前，CentOS7的yum源已经无法访问，因此需要先更换为阿里巴巴的yum源

cp /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.backup
curl -o /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
yum clean all
yum makecache

然后安装依赖项，我采用的系统版本需要额外安装这两个依赖，缺失这两个将导致后续安装mysql-community-server时出错

yum install -y net-toolsyum install -y perl

3.安装和初始化

cd进入/opt目录安装刚刚解压出的软件包，不需要每个都安装，仅安装MySQL的Server和Client以及依赖的包，安装需要按照顺序，因为rpm包有相互的依赖关系，需要安装的包以及安装顺序如下：

  1. mysql-community-common-8.0.25-1.el7.x86_64.rpm
  2. mysql-community-client-plugins-8.0.25-1.el7.x86_64.rpm
  3. mysql-community-libs-8.0.25-1.el7.x86_64.rpm
  4. mysql-community-client-8.0.25-1.el7.x86_64.rpm
  5. mysql-community-server-8.0.25-1.el7.x86_64.rpm

使用rpm -ivh命令安装这些包

[root@localhost opt]# rpm -ivh mysql-community-common-8.0.25-1.el7.x86_64.rpm警告：mysql-community-common-8.0.25-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY准备中...                          ################################# [100%]正在升级/安装...   1:mysql-community-common-8.0.25-1.e################################# [100%][root@localhost opt]# rpm -ivh mysql-community-client-plugins-8.0.25-1.el7.x86_64.rpm'警告：mysql-community-client-plugins-8.0.25-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY准备中...                          ################################# [100%]正在升级/安装...   1:mysql-community-client-plugins-8.################################# [100%][root@localhost opt]# rpm -ivh mysql-community-libs-8.0.25-1.el7.x86_64.rpm警告：mysql-community-libs-8.0.25-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY准备中...                          ################################# [100%]正在升级/安装...   1:mysql-community-libs-8.0.25-1.el7################################# [100%][root@localhost opt]# rpm -ivh mysql-community-client-8.0.25-1.el7.x86_64.rpm警告：mysql-community-client-8.0.25-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY准备中...                          ################################# [100%]正在升级/安装...   1:mysql-community-client-8.0.25-1.e################################# [100%][root@localhost opt]# rpm -ivh mysql-community-server-8.0.25-1.el7.x86_64.rpm警告：mysql-community-server-8.0.25-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY准备中...                          ################################# [100%]正在升级/安装...   1:mysql-community-server-8.0.25-1.e################################# [100%]

安装完成，查看一下安装的版本

[root@localhost opt]# mysql --versionmysql  Ver 8.0.25 for Linux on x86_64 (MySQL Community Server - GPL)[root@localhost opt]# mysqladmin --versionmysqladmin  Ver 8.0.25 for Linux on x86_64 (MySQL Community Server - GPL)

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

mysqld --initialize --user=mysql

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

cat /var/log/mysqld.log
[root@localhost opt]# mysqld --initialize --user=mysql[root@localhost opt]# cat /var/log/mysqld.log2025-04-06T10:34:02.749835Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.25) initializing of server in progress as process 94792025-04-06T10:34:02.921735Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.2025-04-06T10:34:06.304242Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.2025-04-06T10:34:10.865584Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: /*E.4-*jf_#)[root@localhost opt]#

接下来启动服务

systemctl start mysqld
[root@localhost opt]# systemctl status mysqld● mysqld.service - MySQL Server   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)   Active: active (running) since 日 2025-04-06 18:39:55 CST; 7s ago     Docs: man:mysqld(8)           http://dev.mysql.com/doc/refman/en/using-systemd.html  Process: 9531 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS) Main PID: 9556 (mysqld)   Status: "Server is operational"   CGroup: /system.slice/mysqld.service           └─9556 /usr/sbin/mysqld4月 06 18:39:46 localhost.localdomain systemd[1]: Starting MySQL Server...4月 06 18:39:55 localhost.localdomain systemd[1]: Started MySQL Server.

4.登录和基础配置

4.1 登录并重置密码

用之前生成的初始密码登录MySQL的root用户，命令是：mysql -uroot -p，Enter password:后输入密码，登录成功并进入MySQL交互命令行，但是不能操作，提示需要重置root密码

[root@localhost opt]# mysql -uroot -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 8Server version: 8.0.25Copyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.mysql>

执行这个语句重置密码

alter user 'root'@'localhost' identified by 'lzj';

4.2 打开远程连接

安装好的MySQL会自带4个数据库，连接配置的表位于mysql库的user表上，use mysql;将当前库切换为mysql，通过select host,user from user;查看得知系统中的4个用户都仅允许本机访问

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                |+--------------------+4 rows in set (0.65 sec)mysql> use mysql;Database changedmysql> select host,user from user;+-----------+------------------+| host      | user             |+-----------+------------------+| localhost | mysql.infoschema || localhost | mysql.session    || localhost | mysql.sys        || localhost | root             |+-----------+------------------+4 rows in set (0.01 sec)

通过执行以下语句修改root用户可访问的地址，可以采用通配符，按自己需要修改即可，我选择直接修改为%

允许root用户从192.168.1.x主机访问

update mysql.user set host ='192.168.1.%' where user = 'root';

允许root用户从一切远程主机访问

update mysql.user set host ='%' where user = 'root';

修改完成，刷新权限，即可远程连接

flush privileges;

远程连接前一定要关闭CentOS7自带的防火墙firewalld

4.3 修改字符集

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

mysql> show variables like '%char%';+--------------------------+--------------------------------+| Variable_name            | Value                          |+--------------------------+--------------------------------+| character_set_client     | utf8mb4                        || character_set_connection | utf8mb4                        || character_set_database   | utf8mb4                        || character_set_filesystem | binary                         || character_set_results    | utf8mb4                        || character_set_server     | utf8mb4                        || character_set_system     | utf8mb3                        || character_sets_dir       | /usr/share/mysql-8.0/charsets/ |+--------------------------+--------------------------------+8 rows in set (0.12 sec)
  •  
  • ↗️
↗️

安装MySQL5.7

✇Liu Zijian's Blog

1.下载

打开官网下载页面: https://downloads.mysql.com/archives/community/，在这里我选择5.7.28版本，Operating System一栏通常没有CentOS，选择RedHat即可，OS Version选择7，X86_64，然后下载第一个mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar

下载完成后，将文件移动到/opt下，并解压，会解压出多个rpm软件包

[root@localhost opt]# tar -xvf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar mysql-community-embedded-5.7.28-1.el7.x86_64.rpmmysql-community-libs-compat-5.7.28-1.el7.x86_64.rpmmysql-community-devel-5.7.28-1.el7.x86_64.rpmmysql-community-embedded-compat-5.7.28-1.el7.x86_64.rpmmysql-community-libs-5.7.28-1.el7.x86_64.rpmmysql-community-test-5.7.28-1.el7.x86_64.rpmmysql-community-common-5.7.28-1.el7.x86_64.rpmmysql-community-embedded-devel-5.7.28-1.el7.x86_64.rpmmysql-community-client-5.7.28-1.el7.x86_64.rpmmysql-community-server-5.7.28-1.el7.x86_64.rpm

2.安装前准备

2.1 卸载自带的mariadb

安装基于CentOS 7.9.2009

[root@localhost ~]# cat /etc/redhat-release CentOS Linux release 7.9.2009 (Core)

CentOS 7.9系统预装了一个mariadb数据库，这个数据库是MySQL的”姐妹”，都是同一个作者编写的，mariadb会在/etc目录生成一个my.cnf作为配置文件，MySQL安装后也会在/etc目录生成一个my.cnf，因此为了排除干扰，首先要把预装的mariadb卸载掉。

查看是否预装：

[root@localhost ~]# rpm -qa | grep -i mariamariadb-libs-5.5.68-1.el7.x86_64
[root@localhost ~]# ls /etc/ | grep mymy.cnfmy.cnf.d

卸载mariadb

rpm -e --nodeps mariadb-libs

2.2 安装依赖

截至目前，CentOS7的yum源已经无法访问，因此需要先更换为阿里巴巴的yum源

cp /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.backup
curl -o /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
yum clean all
yum makecache

然后安装依赖项，我采用的系统版本需要额外安装这两个依赖，缺失这两个将导致后续安装mysql-community-server时出错

yum install -y net-toolsyum install -y perl

3.安装和初始化

cd进入/opt目录安装刚刚解压出的软件包，不需要每个都安装，仅安装MySQL的Server和Client以及依赖的包，安装需要按照顺序，因为rpm包有相互的依赖关系，需要安装的包以及安装顺序如下：

  1. mysql-community-common-5.7.28-1.el7.x86_64.rpm
  2. mysql-community-libs-5.7.28-1.el7.x86_64.rpm
  3. mysql-community-client-5.7.28-1.el7.x86_64.rpm
  4. mysql-community-server-5.7.28-1.el7.x86_64.rpm

使用rpm -ivh命令安装这些包

[root@localhost opt]# rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm警告：mysql-community-common-5.7.28-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY准备中...                          ################################# [100%]正在升级/安装...   1:mysql-community-common-5.7.28-1.e################################# [100%][root@localhost opt]# rpm -ivh mysql-community-libs-mysql-community-libs-5.7.28-1.el7.x86_64.rpm         mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm[root@localhost opt]# rpm -ivh  mysql-community-libs-5.7.28-1.el7.x86_64.rpm警告：mysql-community-libs-5.7.28-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY准备中...                          ################################# [100%]正在升级/安装...   1:mysql-community-libs-5.7.28-1.el7################################# [100%][root@localhost opt]# rpm -ivh  mysql-community-client-5.7.28-1.el7.x86_64.rpm警告：mysql-community-client-5.7.28-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY准备中...                          ################################# [100%]正在升级/安装...   1:mysql-community-client-5.7.28-1.e################################# [100%][root@localhost opt]# rpm -ivh  mysql-community-server-5.7.28-1.el7.x86_64.rpm警告：mysql-community-server-5.7.28-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY准备中...                          ################################# [100%]正在升级/安装...   1:mysql-community-server-5.7.28-1.e################################# [100%]

通过rpm -qa已经能看到装上去的包了

[root@localhost opt]# rpm -qa | grep mysqlmysql-community-libs-5.7.28-1.el7.x86_64mysql-community-common-5.7.28-1.el7.x86_64mysql-community-client-5.7.28-1.el7.x86_64mysql-community-server-5.7.28-1.el7.x86_64

查看一下安装的版本

[root@localhost opt]# mysql --versionmysql  Ver 14.14 Distrib 5.7.28, for Linux (x86_64) using  EditLine wrapper

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

mysqld --initialize --user=mysql

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

cat /var/log/mysqld.log
[root@localhost opt]# mysqld --initialize --user=mysql[root@localhost opt]# cat /var/log/mysqld.log2025-04-06T12:26:08.004754Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).2025-04-06T12:26:08.339163Z 0 [Warning] InnoDB: New log files created, LSN=457902025-04-06T12:26:08.403698Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.2025-04-06T12:26:08.470936Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 5151ca04-12e2-11f0-8409-000c29920d74.2025-04-06T12:26:08.472919Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.2025-04-06T12:26:10.249602Z 0 [Warning] CA certificate ca.pem is self signed.2025-04-06T12:26:10.762512Z 1 [Note] A temporary password is generated for root@localhost: G*kRrqwYn7KX

接下来启动服务

systemctl start mysqld
[root@localhost opt]# systemctl status mysqld● mysqld.service - MySQL Server   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)   Active: active (running) since 日 2025-04-06 20:27:54 CST; 8s ago     Docs: man:mysqld(8)           http://dev.mysql.com/doc/refman/en/using-systemd.html  Process: 1468 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)  Process: 1451 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS) Main PID: 1471 (mysqld)   CGroup: /system.slice/mysqld.service           └─1471 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid4月 06 20:27:53 localhost.localdomain systemd[1]: Starting MySQL Server...4月 06 20:27:54 localhost.localdomain systemd[1]: Started MySQL Server.

4.登录和基础配置

4.1登录并重置密码

用之前生成的初始密码登录MySQL的root用户，命令是：mysql -uroot -p，Enter password:后输入密码回车，登录成功并进入MySQL交互命令行，但是不能操作，提示需要重置root密码

[root@localhost opt]# mysql -uroot -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.7.28Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

执行这个语句重置密码

alter user 'root'@'localhost' identified by 'lzj';

4.2 打开远程连接

安装好的MySQL会自带4个数据库，连接配置的表位于mysql库的user表上，use mysql;将当前库切换为mysql，通过select host,user from user;查看得知系统中的4个用户都仅允许本机访问

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                |+--------------------+4 rows in set (0.00 sec)mysql> use mysql;Database changedmysql> select host,user from user;+-----------+---------------+| host      | user          |+-----------+---------------+| localhost | mysql.session || localhost | mysql.sys     || localhost | root          |+-----------+---------------+3 rows in set (0.00 sec)

通过执行以下语句修改root用户可访问的地址，可以采用通配符，按自己需要修改即可，我选择直接修改为%

允许root用户从192.168.1.x主机访问

update mysql.user set host ='192.168.1.%' where user = 'root';

允许root用户从一切远程主机访问

update mysql.user set host ='%' where user = 'root';

修改完成，刷新权限，即可远程连接

flush privileges;

远程连接前一定要关闭CentOS7自带的防火墙firewalld

4.3 修改字符集

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

查看当前字符集

show variables like '%char%';
mysql> show variables like '%char%';+--------------------------+----------------------------+| Variable_name            | Value                      |+--------------------------+----------------------------+| character_set_client     | utf8                       || character_set_connection | utf8                       || character_set_database   | latin1                     || character_set_filesystem | binary                     || character_set_results    | utf8                       || character_set_server     | latin1                     || character_set_system     | utf8                       || character_sets_dir       | /usr/share/mysql/charsets/ |+--------------------------+----------------------------+8 rows in set (0.00 sec)

字符集不支持中文，因此MySQL5.7需要修改服务端字符集，修改字符集的配置文件位于/etc/my.cnf，将配置添加在[mysqld]节点上

[mysqld]character-set-server = utf8

修改后新建的库和表才会使用utf8，之前建的库表的字符集不会被自动修改，需要手动修改：

⚠️ utf8兼容latin1，因此之前的数据支持修改字符集为utf8，如果不兼容就可能出现乱码问题，不兼容的情况可以将数据导出在重新插入

修改库

alter database db_xxx character set 'utf8';

修改表

alter table tb_xxx convert to character set 'utf8';
  •  
  • ↗️
↗️

RockyLinux9环境下编译MySQL8

✇Liu Zijian's Blog

Linux版本: Rocky Linux release 9.5 (Blue Onyx)

1.下载

打开MySQL-Community-Server官方下载页面:https://downloads.mysql.com/archives/community/

筛选出要下载的版本，Product Version选择8.0.39，Operating System选择Source Code，OS Version选择All Operating Systems，然后选择最后一个自带boost的MySQL源码包All Operating Systems (Generic) (Architecture Independent), Compressed TAR Archive
Includes Boost Headers (mysql-boost-8.0.39.tar.gz)，进行下载，并将下载到的源码包先放到服务器的/opt目录下。

下载链接:

解压tar包，得到文件夹mysql-8.0.39，里面就是MySQL源码和一些构建信息

[root@localhost opt]# cd mysql-8.0.39/[root@localhost mysql-8.0.39]# lltotal 684-rw-r--r--.  1 7161 31415  94660 Jul 13 03:15 CMakeLists.txtdrwxr-xr-x.  2 7161 31415     65 Jul 13 03:43 Docs-rw-r--r--.  1 7161 31415   5425 Jul 13 03:15 Doxyfile-ignored-rw-r--r--.  1 7161 31415 123326 Jul 13 03:15 Doxyfile.in-rw-r--r--.  1 7161 31415    333 Jul 13 03:15 INSTALL-rw-r--r--.  1 7161 31415 282186 Jul 13 03:15 LICENSE-rw-r--r--.  1 7161 31415    118 Jul 13 03:15 MYSQL_VERSION-rw-r--r--.  1 7161 31415    666 Jul 13 03:15 READMEdrwxr-xr-x.  3 7161 31415     26 Jul 13 03:45 boostdrwxr-xr-x. 10 7161 31415   4096 Jul 13 03:43 clientdrwxr-xr-x.  4 7161 31415   4096 Jul 13 03:43 cmakedrwxr-xr-x. 16 7161 31415   4096 Jul 13 03:43 components-rw-r--r--.  1 7161 31415  10843 Jul 13 03:15 config.h.cmake-rw-r--r--.  1 7161 31415  23642 Jul 13 03:15 configure.cmakedrwxr-xr-x.  3 7161 31415    124 Jul 13 03:43 doxygen_resourcesdrwxr-xr-x. 19 7161 31415   4096 Jul 13 03:43 extradrwxr-xr-x.  4 7161 31415   8192 Jul 13 03:43 includedrwxr-xr-x.  5 7161 31415    112 Jul 13 03:43 libbinlogeventsdrwxr-xr-x.  3 7161 31415     39 Jul 13 03:43 libbinlogstandalonedrwxr-xr-x.  4 7161 31415     54 Jul 13 03:43 libchangestreamsdrwxr-xr-x.  7 7161 31415   4096 Jul 13 03:43 libmysqldrwxr-xr-x.  2 7161 31415   4096 Jul 13 03:43 libservicesdrwxr-xr-x.  2 7161 31415   4096 Jul 13 03:45 mandrwxr-xr-x. 10 7161 31415   4096 Jul 13 03:43 mysql-testdrwxr-xr-x.  2 7161 31415   4096 Jul 13 03:43 mysysdrwxr-xr-x. 10 7161 31415    131 Jul 13 03:43 packagingdrwxr-xr-x. 25 7161 31415   4096 Jul 13 03:43 plugindrwxr-xr-x.  6 7161 31415    155 Jul 13 03:43 router-rw-r--r--.  1 7161 31415   6268 Jul 13 03:15 run_doxygen.cmakedrwxr-xr-x.  4 7161 31415   4096 Jul 13 03:43 scriptsdrwxr-xr-x.  3 7161 31415    155 Jul 13 03:43 sharedrwxr-xr-x. 24 7161 31415  24576 Jul 13 03:44 sqldrwxr-xr-x.  3 7161 31415   4096 Jul 13 03:43 sql-commondrwxr-xr-x. 14 7161 31415   4096 Jul 13 03:45 storagedrwxr-xr-x.  3 7161 31415   4096 Jul 13 03:43 stringsdrwxr-xr-x.  2 7161 31415   4096 Jul 13 03:43 support-filesdrwxr-xr-x.  2 7161 31415    101 Jul 13 03:43 testclientsdrwxr-xr-x.  5 7161 31415     70 Jul 13 03:43 unittestdrwxr-xr-x.  2 7161 31415   4096 Jul 13 03:43 utilitiesdrwxr-xr-x.  2 7161 31415   4096 Jul 13 03:43 vio

和编译MySQl5.7一样，进入boost/文件夹下，查看boost是哪个版本，一会编译时会用到。

[root@localhost mysql-8.0.39]# cd boost/[root@localhost boost]# lltotal 0drwxr-xr-x. 3 7161 31415 19 Jul 13 03:45 boost_1_77_0

2.编译安装前准备

1.新添加一个用户: mysql

useradd mysql

2.安装编译所需依赖

dnf install -y make cmake gcc gcc-c++ ncurses-devel  openssl-devel libtirpc

还有些不能通过yum安装的软件包要手动下载安装

yum install -y wgetwget https://dl.rockylinux.org/pub/rocky/9/devel/x86_64/os/Packages/l/libtirpc-devel-1.3.3-9.el9.x86_64.rpmwget https://dl.rockylinux.org/pub/rocky/9/AppStream/x86_64/os/Packages/r/rpcgen-1.4-9.el9.x86_64.rpmrpm -ivh libtirpc-devel-1.3.3-9.el9.x86_64.rpmrpm -ivh rpcgen-1.4-9.el9.x86_64.rpm

然后再安装MySQL8.0.39需要的gcc工具集，安装前，需要升级软件包libsemanage为高版本，同样需要下载软件包到本地，然后rpm -Uvh更新

wget https://dl.rockylinux.org/pub/rocky/9/BaseOS/x86_64/os/Packages/l/libsemanage-3.6-2.1.el9_5.x86_64.rpmrpm -Uvh libsemanage-3.6-2.1.el9_5.x86_64.rpm

然后执行安装gcc工具集命令

yum install gcc-toolset-12-gcc gcc-toolset-12-gcc-c++ gcc-toolset-12-binutils gcc-toolset-12-annobin-annocheck gcc-toolset-12-annobin-plugin-gcc

3./usr/local/下必须新建文件夹mysql

mkdir /usr/local/mysql

3.编译安装

1.在源码目录/opt/mysql-8.0.39下执行cmake命令，进行编译前的检查和配置，使用CMake配置和生成MySQL源码编译所需的构建系统文件，安装MySQL到/usr/local/mysql目录下，-DWITH_BOOST=的路径根据源码包内自带boost的版本而定，默认端口3306，数据路径/usr/local/mysql/data，字符集utf8mb4

cmake \-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \-DINSTALL_DATADIR=/usr/local/mysql/data \-DMYSQL_USER=mysql \-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysqld.sock \-DDEFAULT_CHARSET=utf8mb4 \-DDEFAULT_COLLATION=utf8mb4_general_ci \-DWITH_INNOBASE_STORAGE_ENGINE=1 \-DWITH_EMBEDDED_SERVER=1 \-DFORCE_INSOURCE_BUILD=1 \-DWITH_MYISAM_STORAGE_ENGINE=1 \-DENABLED_LOCAL_INFILE=1 \-DEXTRA_CHARSETS=all \-DWITH_BOOST=boost/boost_1_77_0/

2.cmake命令执行成功后，执行以下make命令开始编译安装，-j 2是同时开启两个线程编译，加速编译

make -j 2 && make install

执行后，开始编译，不断输出日志，编译的过程很耗时间，需要等待很久，编译完成后，可以在/usr/local/mysql目录下看到编译好的MySQL

[root@localhost mysql-8.0.39]# cd /usr/local/mysql/[root@localhost mysql]# lltotal 704-rw-r--r--.  1 root root 282186 Jul 13 03:15 LICENSE-rw-r--r--.  1 root root 282186 Jul 13 03:15 LICENSE-test-rw-r--r--.  1 root root 115948 Jul 13 03:15 LICENSE.router-rw-r--r--.  1 root root    666 Jul 13 03:15 README-rw-r--r--.  1 root root    666 Jul 13 03:15 README-test-rw-r--r--.  1 root root    679 Jul 13 03:15 README.routerdrwxr-xr-x.  2 root root   4096 Dec 21 18:08 bindrwxr-xr-x.  2 root root     69 Dec 21 18:08 docsdrwxr-xr-x.  3 root root   4096 Dec 21 18:08 includedrwxr-xr-x.  6 root root   4096 Dec 21 18:08 libdrwxr-xr-x.  4 root root     30 Dec 21 18:08 mandrwxr-xr-x. 10 root root   4096 Dec 21 18:08 mysql-test-rw-r--r--.  1 root root   1672 Dec 21 13:33 mysqlrouter-log-rotatedrwxrwxr-x.  2 root root      6 Dec 21 18:08 rundrwxr-xr-x. 28 root root   4096 Dec 21 18:08 sharedrwxr-xr-x.  2 root root     77 Dec 21 18:08 support-filesdrwxr-xr-x.  3 root root     17 Dec 21 18:08 var

4.安装后配置

1.在/usr/local/mysql创建一个mysql配置文件，并写入字符集相关配置进去。

vi /usr/local/mysql/my.cnf
[mysqld]port = 3306character-set-server=utf8mb4default_authentication_plugin=mysql_native_password[mysql]default-character-set=utf8mb4

2.在/usr/local/mysql下执行命令初始化MySQL数据库

./bin/mysqld  \--defaults-file=/usr/local/mysql/my.cnf  \--user=mysql \--basedir=/usr/local/mysql  \--datadir=/usr/local/mysql/data  \--initialize-insecure

3.在/usr/local/mysql目录执行以下命令，将文件所有者设置为之前新建的mysql用户

chown -R mysql:mysql ./

5.启动和测试MySQL Server

在/usr/local/mysql下执行启动脚本文件启动数据库

./support-files/mysql.server start

关闭

./support-files/mysql.server stop

重启

./support-files/mysql.server restart

登录数据库测试，切换到目录/usr/local/mysql，执行./bin/mysql -uroot -p命令登录数据库，root密码默认是空

[root@localhost mysql]# ./bin/mysql -uroot -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 8Server version: 8.0.39 Source distributionCopyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                |+--------------------+4 rows in set (0.01 sec)

修改默认密码

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'lzj';

修改后要刷新权限

mysql> FLUSH PRIVILEGES;

刷新权限后退出使用新密码再次登录，此时已经可以正常操作数据库了。

[root@localhost mysql]# ./bin/mysql -uroot -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 9Server version: 8.0.39 Source distributionCopyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                |+--------------------+4 rows in set (0.01 sec)mysql> select version();+-----------+| version() |+-----------+| 8.0.39    |+-----------+1 row in set (0.00 sec)mysql> SHOW VARIABLES LIKE 'character_set%';+--------------------------+----------------------------------+| Variable_name            | Value                            |+--------------------------+----------------------------------+| character_set_client     | utf8mb4                          || character_set_connection | utf8mb4                          || character_set_database   | utf8mb4                          || character_set_filesystem | binary                           || character_set_results    | utf8mb4                          || character_set_server     | utf8mb4                          || character_set_system     | utf8mb3                          || character_sets_dir       | /usr/local/mysql/share/charsets/ |+--------------------------+----------------------------------+8 rows in set (0.02 sec)

至此，就完成了在RockyLinux9上面安装和运行MySQL8

  •  
  • ↗️
↗️

RockyLinux9环境下编译MySQL5.7

✇Liu Zijian's Blog

Linux版本: Rocky Linux release 9.5 (Blue Onyx)

1.下载

打开MySQL-Community-Server官方下载页面:https://downloads.mysql.com/archives/community/

筛选出要下载的版本，Product Version选择5.7.44，也就是最后一个小版本，Operating System选择Source Code，OS Version选择All Operating Systems，然后选择最后一个自带boost的MySQL源码包(mysql-boost-5.7.44.tar.gz)，进行下载，并将下载到的源码包先放到服务器的/opt目录下。

解压tar包，得到文件夹mysql-5.7.44，里面就是MySQL源码和一些构建信息

[root@localhost opt]# cd mysql-5.7.44/[root@localhost mysql-5.7.44]# lltotal 528drwxr-xr-x.  2 7161 31415   4096 Oct 11  2023 BUILD-rw-r--r--.  1 7161 31415  29939 Oct 11  2023 CMakeLists.txtdrwxr-xr-x.  2 7161 31415     98 Oct 11  2023 Docs-rw-r--r--.  1 7161 31415  67369 Oct 11  2023 Doxyfile-perfschema-rw-r--r--.  1 7161 31415    333 Oct 11  2023 INSTALL-rw-r--r--.  1 7161 31415 260678 Oct 11  2023 LICENSE-rw-r--r--.  1 7161 31415    566 Oct 11  2023 README-rw-r--r--.  1 7161 31415     88 Oct 11  2023 VERSIONdrwxr-xr-x.  3 7161 31415     26 Oct 11  2023 boostdrwxr-xr-x.  6 7161 31415   4096 Oct 11  2023 clientdrwxr-xr-x.  4 7161 31415   4096 Oct 11  2023 cmake-rw-r--r--.  1 7161 31415  14584 Oct 11  2023 config.h.cmake-rw-r--r--.  1 7161 31415  25217 Oct 11  2023 configure.cmakedrwxr-xr-x.  2 7161 31415   4096 Oct 11  2023 dbugdrwxr-xr-x.  8 7161 31415   4096 Oct 11  2023 extradrwxr-xr-x.  5 7161 31415   4096 Oct 11  2023 includedrwxr-xr-x.  5 7161 31415    135 Oct 11  2023 libbinlogeventsdrwxr-xr-x.  3 7161 31415     39 Oct 11  2023 libbinlogstandalonedrwxr-xr-x.  4 7161 31415   4096 Oct 11  2023 libmysqldrwxr-xr-x.  3 7161 31415   4096 Oct 11  2023 libmysqlddrwxr-xr-x.  2 7161 31415   4096 Oct 11  2023 libservicesdrwxr-xr-x.  2 7161 31415   4096 Oct 11  2023 mandrwxr-xr-x. 10 7161 31415   4096 Oct 11  2023 mysql-testdrwxr-xr-x.  2 7161 31415   4096 Oct 11  2023 mysysdrwxr-xr-x.  2 7161 31415   4096 Oct 11  2023 mysys_ssldrwxr-xr-x. 10 7161 31415    131 Oct 11  2023 packagingdrwxr-xr-x. 18 7161 31415   4096 Oct 11  2023 plugindrwxr-xr-x.  4 7161 31415     36 Oct 11  2023 rapiddrwxr-xr-x.  2 7161 31415   4096 Oct 11  2023 regexdrwxr-xr-x.  4 7161 31415   4096 Oct 11  2023 scriptsdrwxr-xr-x.  2 7161 31415      6 Oct 11  2023 source_downloadsdrwxr-xr-x.  8 7161 31415  20480 Oct 11  2023 sqldrwxr-xr-x.  2 7161 31415   4096 Oct 11  2023 sql-commondrwxr-xr-x. 13 7161 31415    175 Oct 11  2023 storagedrwxr-xr-x.  2 7161 31415   4096 Oct 11  2023 stringsdrwxr-xr-x.  4 7161 31415   4096 Oct 11  2023 support-filesdrwxr-xr-x.  2 7161 31415     98 Oct 11  2023 testclientsdrwxr-xr-x.  5 7161 31415     70 Oct 11  2023 unittestdrwxr-xr-x.  3 7161 31415   4096 Oct 11  2023 viodrwxr-xr-x.  2 7161 31415     32 Oct 11  2023 win

进入boost/文件夹下，可以看到目前版本依赖的boost版本是哪个，一会编译时会用到。

[root@localhost mysql-5.7.44]# cd boost/[root@localhost boost]# lltotal 0drwxr-xr-x. 3 7161 31415 19 Oct 11  2023 boost_1_59_0

2.编译安装前准备

1.新添加一个用户: mysql

useradd mysql

2.安装编译所需要的软件包依赖

yum -y install make gcc-c++ cmake bison  perl autoconf ncurses-devel openssl-devel libtirpc

在Rocky9上，有的依赖包没有自带，也无法通过yum安装，需要下载.rpm到本地，然后通过rpm -ivh安装

yum install -y wgetwget https://dl.rockylinux.org/pub/rocky/9/devel/x86_64/os/Packages/l/libtirpc-devel-1.3.3-9.el9.x86_64.rpmwget https://dl.rockylinux.org/pub/rocky/9/AppStream/x86_64/os/Packages/r/rpcgen-1.4-9.el9.x86_64.rpmrpm -ivh libtirpc-devel-1.3.3-9.el9.x86_64.rpmrpm -ivh rpcgen-1.4-9.el9.x86_64.rpm

3./usr/local/下必须新建文件夹mysql

mkdir /usr/local/mysql

3.编译安装

1.在源码目录/opt/mysql-5.7.44下执行cmake命令，进行编译前的检查和配置，使用CMake配置和生成MySQL源码编译所需的构建系统文件，安装MySQL到/usr/local/mysql目录下，-DWITH_BOOST=的路径根据源码包内自带boost的版本而定，默认端口3306，数据路径/usr/local/mysql/data，字符集utf8mb4

cmake \-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \-DMYSQL_DATADIR=/usr/local/mysql/data \-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \-DSYSCONFDIR=/usr/local/mysql \-DWITH_MYISAM_STORAGE_ENGINE=1 \-DWITH_ARCHIVE_STORAGE_ENGINE=1 \-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \-DWITH_INNOBASE_STORAGE_ENGINE=1 \-DWITH_MEMORY_STORAGE_ENGINE=1 \-DWITH_READLINE=1 \-DMYSQL_TCP_PORT=3306 \-DENABLED_LOCAL_INFILE=1 \-DWITH_PARTITION_STORAGE_ENGINE=1 \-DEXTRA_CHARSETS=all \-DDEFAULT_CHARSET=utf8mb4 \-DDEFAULT_COLLATION=utf8mb4_general_ci \-DWITH_BOOST=boost/boost_1_59_0/

2.cmake命令执行成功后，执行以下make命令开始编译安装

make && make install

执行后，开始编译，不断输出日志，编译的过程很耗时间，需要等待很久，编译完成后，可以在/usr/local/mysql目录下看到编译好的MySQL

[root@localhost mysql-5.7.44]# cd /usr/local/mysql/[root@localhost mysql]# lltotal 280-rw-r--r--.  1 root root 260678 Oct 11  2023 LICENSE-rw-r--r--.  1 root root    566 Oct 11  2023 README-rw-r--r--.  1 root root    566 Oct 11  2023 README-testdrwxr-xr-x.  2 root root   4096 Dec 15 20:12 bindrwxr-xr-x.  2 root root     73 Dec 15 20:12 docsdrwxr-xr-x.  3 root root   4096 Dec 15 20:12 includedrwxr-xr-x.  4 root root    192 Dec 15 20:12 libdrwxr-xr-x.  4 root root     30 Dec 15 20:12 mandrwxr-xr-x. 10 root root   4096 Dec 15 20:12 mysql-testdrwxr-xr-x. 28 root root   4096 Dec 15 20:12 sharedrwxr-xr-x.  2 root root     90 Dec 15 20:12 support-files

4.安装后配置

1.在/usr/local/mysql创建一个mysql配置文件，并写入字符集相关配置进去。

vi /usr/local/mysql/my.cnf
[mysqld]# 设置字符集为 utf8mb4character-set-server = utf8mb4collation-server = utf8mb4_unicode_ci# 可选: 设置客户端和连接的字符集[client]default-character-set = utf8mb4[mysql]default-character-set = utf8mb4

2.在/usr/local/mysql下执行命令初始化MySQL数据库

./bin/mysqld --defaults-file=/usr/local/mysql/my.cnf  --initialize --user=mysql

命令执行完成后，会为我们生成一个临时的root密码TKwei-cPH7>?，初始化完成后，目录内会多出一个data文件夹

[root@localhost mysql]# ./bin/mysqld --defaults-file=/usr/local/mysql/my.cnf  --initialize --user=mysql2024-12-15T13:56:33.192253Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).2024-12-15T13:56:34.692706Z 0 [Warning] InnoDB: New log files created, LSN=457902024-12-15T13:56:34.976850Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.2024-12-15T13:56:35.074466Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 658fc67b-baec-11ef-9db1-000c29c17c8f.2024-12-15T13:56:35.079233Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.2024-12-15T13:56:35.873224Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.2024-12-15T13:56:35.873307Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.2024-12-15T13:56:35.882642Z 0 [Warning] CA certificate ca.pem is self signed.2024-12-15T13:56:36.037915Z 1 [Note] A temporary password is generated for root@localhost: TKwei-cPH7>?
[root@localhost mysql]# cd /usr/local/mysql/[root@localhost mysql]# lltotal 284-rw-r--r--.  1 root  root  260678 Oct 11  2023 LICENSE-rw-r--r--.  1 root  root     566 Oct 11  2023 README-rw-r--r--.  1 root  root     566 Oct 11  2023 README-testdrwxr-xr-x.  2 root  root    4096 Dec 15 20:12 bindrwxr-x---.  5 mysql mysql   4096 Dec 15 21:56 datadrwxr-xr-x.  2 root  root      73 Dec 15 20:12 docsdrwxr-xr-x.  3 root  root    4096 Dec 15 20:12 includedrwxr-xr-x.  4 root  root     192 Dec 15 20:12 libdrwxr-xr-x.  4 root  root      30 Dec 15 20:12 man-rw-r--r--.  1 root  root       0 Dec 15 21:56 my.cnfdrwxr-xr-x. 10 root  root    4096 Dec 15 20:12 mysql-testdrwxr-xr-x. 28 root  root    4096 Dec 15 20:12 sharedrwxr-xr-x.  2 root  root      90 Dec 15 20:12 support-files

3.在/usr/local/mysql目录执行以下命令，将文件所有者设置为之前新建的mysql用户

chown -R mysql:mysql ./
[root@localhost mysql]# lltotal 284-rw-r--r--.  1 mysql mysql 260678 Oct 11  2023 LICENSE-rw-r--r--.  1 mysql mysql    566 Oct 11  2023 README-rw-r--r--.  1 mysql mysql    566 Oct 11  2023 README-testdrwxr-xr-x.  2 mysql mysql   4096 Dec 15 20:12 bindrwxr-x---.  5 mysql mysql   4096 Dec 15 21:56 datadrwxr-xr-x.  2 mysql mysql     73 Dec 15 20:12 docsdrwxr-xr-x.  3 mysql mysql   4096 Dec 15 20:12 includedrwxr-xr-x.  4 mysql mysql    192 Dec 15 20:12 libdrwxr-xr-x.  4 mysql mysql     30 Dec 15 20:12 man-rw-r--r--.  1 mysql mysql      0 Dec 15 21:56 my.cnfdrwxr-xr-x. 10 mysql mysql   4096 Dec 15 20:12 mysql-testdrwxr-xr-x. 28 mysql mysql   4096 Dec 15 20:12 sharedrwxr-xr-x.  2 mysql mysql     90 Dec 15 20:12 support-files

5.启动和测试MySQL Server

在/usr/local/mysql下执行启动脚本文件启动数据库

./support-files/mysql.server start

关闭

./support-files/mysql.server stop

重启

./support-files/mysql.server restart

登录数据库测试，切换到目录/usr/local/mysql，在执行./bin/mysql -uroot -p命令登录数据库操作时，系统提示我们需要修改默认的root密码

[root@localhost mysql]# ./bin/mysql -uroot -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 5Server version: 5.7.44Copyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

修改默认密码

mysql> SET PASSWORD = PASSWORD('lzj');

修改后要刷新权限

mysql> FLUSH PRIVILEGES;

刷新权限后退出使用新密码再次登录，此时已经可以正常操作数据库了。

[root@localhost mysql]# ./bin/mysql -uroot -plzjmysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 7Server version: 5.7.44 Source distributionCopyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                |+--------------------+4 rows in set (0.00 sec)mysql> select version();+-----------+| version() |+-----------+| 5.7.44    |+-----------+1 row in set (0.00 sec)mysql> SHOW VARIABLES LIKE 'character_set%';+--------------------------+----------------------------------+| Variable_name            | Value                            |+--------------------------+----------------------------------+| character_set_client     | utf8mb4                          || character_set_connection | utf8mb4                          || character_set_database   | utf8mb4                          || character_set_filesystem | binary                           || character_set_results    | utf8mb4                          || character_set_server     | utf8mb4                          || character_set_system     | utf8                             || character_sets_dir       | /usr/local/mysql/share/charsets/ |+--------------------------+----------------------------------+8 rows in set (0.01 sec)

至此，就完成了在RockyLinux9上面安装和运行MySQL5.7

  •  
  • ↗️
↗️

MySQL数据定义语言

✇Liu Zijian's Blog

1.库操作

1.1 创建数据库

CREATE DATABASE book

1.2 创建数据库，如果存在

CREATE DATABASE IF NOT EXISTS book

1.3 切换到某个数据库

use book

1.4 更改库字符集

ALTER DATABASE book CHARACTER SET utf8ALTER DATABASE book CHARACTER SET gbk

1.5 删除数据库

DROP DATABASE bookDROP DATABASE IF EXISTS book

2.数据类型

2.1 整形

整形 TINYINT SMALLINT MEDIUMINT INT(INTEGER) BIGINT，对于整形，长度由范围决定，设置的长度用来补零，但必须在建立字段时搭配zerofill，一旦zerofill，默认为无符号，如果不设置无符号，默认是有符号，如果插入数值超出范围，系统会警告，默认插入临界值。

# 设置有无符号create table user( age int UNSIGNED , id int )INSERT INTO `user` VALUES (-9,9)

2.2 浮点数

浮点型小数 float(m,d) double(m,d)
定点型小数 DECIMAL(m,d) ，精度高，最大取值范围与double相同，m 整数部分和小数部分合起来的位数，d 小数点后保留位数，超出范围默认是临界值

2.3 字符型

短文本char VARCHAR，长文本text

CHAR(m)，m指最大字符数, 固定长度字符，开的空间与长度有关 ，耗费空间，效率高，可以省略m， m默认是1。
VARCHAR(m) m指最大字符数, 可变长度的字符，开的空间与实际占用有关，节省空间，效率低，不能省略m。

2.4 枚举型

enum('c','b') 掺入的值不在枚举内，默认是空，不区分大小写

2.5 SET

一次可以插入多个值，不区分大小写

2.6 日期值

必须用单引号引起，每个类型作用范围不同

date 1000-01-01 - 9999-12-31
time 838:59:59
datetime 1000-01-01 - 9999-12-31
year 1901 - 2155
TIMESTAMP 1970 - 2038年某一时刻，受时区影响，更能反应实际日期，与MySQL版本和sqlmode关系很大

3.表操作

3.1 创建表

CREATE TABLE book(id INT(10) ,`name` VARCHAR (30),price DOUBLE,authorId INT(10),publishDate DATETIME);CREATE TABLE author(id INT,`name` VARCHAR(20));

3.2 查看表描述

DESC book

3.3 修改列名 类型

ALTER TABLE book CHANGE COLUMN publishDate pubDate datetime

3.4 修改类约束 类型

ALTER TABLE book MODIFY COLUMN pubDate datetime

3.5 添加新列

ALTER TABLE author ADD COLUMN last_name VARCHAR(20)ALTER TABLE author ADD COLUMN first_name VARCHAR(20)

3.6 删除列

ALTER TABLE author DROP COLUMN first_name

3.7 修改表名

ALTER TABLE author RENAME TO `authors`

3.8 删除表

DROP TABLE IF EXISTS `authors`

3.9 复制

仅仅复制结构

CREATE TABLE 1_author LIKE `author`

复制结构和数据

CREATE TABLE 12_author SELECT * FROM `author`

条件复制

CREATE TABLE 12_author SELECT * FROM `author` WHERE id = 1

复制部分列

CREATE TABLE 12_author SELECT `name` FROM `author` WHERE id = 1

复制部分列，不要数据，使用恒不成立

CREATE TABLE 12_author SELECT `name` FROM `author` WHERE 2 = 1CREATE TABLE 12_author SELECT `name` FROM `author` WHERE 0 /*0==false*/

4.约束

6种约束

  1. NOT NULL (非空约束): 确保列不能有 NULL 值。常用于必须填写的字段。
  2. DEFAULT (默认值约束): 如果插入数据时未指定该列的值，则使用默认值填充。
  3. PRIMARY KEY (主键约束): 保证字段值的唯一性和非空，常用于唯一标识表中每一行的字段。
  4. UNIQUE (唯一约束): 确保列中的值唯一，但可以有 NULL 值。
  5. CHECK (检查约束): 验证字段的值是否符合指定条件，但 MySQL 并不支持 CHECK 约束。
  6. FOREIGN KEY (外键约束): 设定两个表之间的关系，确保该字段的值必须来自于主表的关联列。

表级与列级约束:

  • 表级约束: 可以在创建表时定义，除 NOT NULL 和 DEFAULT 之外的所有约束都支持。
  • 列级约束: 语法上支持所有约束，但 MySQL 中外键在列级定义时可能无法有效执行（需要在表级定义才能生效）。

4.1 主键

添加主键

ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY-- 支持表级约束的也可以这样ALTER TABLE stuinfo ADD PRIMARY KEY (id)
CREATE TABLE stuinfo(id int ,stuname VARCHAR(20) ,gender CHAR (1) ,seat INT ,age  INT UNSIGNED ,majarId INT,CONSTRAINT pk/*mysql中，主键改名无效*/ PRIMARY KEY (id))

主键和唯一的对比

都可以保证唯一，主键不能为空，每个表只能有1个，可以两个列自合一起，唯一允许为空，一个表可以有多个，不能多个为空，可以两个列自合一起。

外键，从表设置外键关系，数据类型一致或兼容，主表被关联列必须是一个key,一般是主键或唯一，插入数据时，先插入主表在插入从表，删除数据，先删除从表，再删除主表。

4.2 非空

CREATE TABLE stuinfo(id int ,stuname VARCHAR(20) ,gender CHAR (1) ,seat INT ,age  INT UNSIGNED ,majarId INT)-- 修改表时添加约束ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULLALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL

4.3 唯一

添加唯一

ALTER TABLE stuinfo ADD UNIQUE (seat)

4.4 外键

添加外键

ALTER TABLE stuinfo ADD FOREIGN KEY (majarId) REFERENCES majar(id)

添加外键 名字

ALTER TABLE stuinfo ADD CONSTRAINT fk_majar FOREIGN KEY (majarId) REFERENCES majar(id)

4.5删除约束

删除非空

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL

不添加条件，就会删除默认约束

ALTER TABLE stuinfo MODIFY COLUMN age

删除主键

ALTER TABLE stuinfo DROP PRIMARY KEY

先查到，在删除唯一约束

SHOW INDEX FROM stuinfo ALTER TABLE stuinfo DROP INDEX seat

删除外键

ALTER TABLE stuinfo DROP FOREIGN KEY fk_majar

4.6标识列

限制某个字段，又叫自增长列，默认从1开始，必须是一个key，同一张表标识列只能有一个，只能是数值类型

CREATE TABLE stuinfo(id int PRIMARY KEY auto_increment,stuname VARCHAR(20) ,gender CHAR (1) ,seat INT ,age  INT UNSIGNED ,majarId INT)

步长auto_increment_increment 起始值auto_increment_offset,mysql 可以设置步长，但是不能设置起始值

show VARIABLES like '%auto_increment%'

设置删除标识列

ALTER TABLE stuinfo MODIFY COLUMN id int PRIMARY KEY auto_incrementALTER TABLE stuinfo MODIFY COLUMN id int
  •  
  • ↗️
↗️

MySQL存储过程

✇Liu Zijian's Blog

使用存储过程，减少编译次数和连接数据库次数，提高效率

1. 创建

参数模式

  • IN :可以作为输入，需要调用者传入值
  • OUT ：可以作为输出，可以作为返回值
  • INOUT：既可以传入值，又可以返回值

语法

CREATE PROCEDURE 存储过程名(参数模式 参数名 参数类型,参数模式 参数名 参数类型,...) BEGIN （语句只有一条 begin end 可省略）合法有效的SQL语句;（分号）合法有效的SQL语句;END

示例

delimiter $CREATE PROCEDURE pr1()BEGININSERT INTO book(`name`, price, author) VALUES('012',23.56,'lzj');INSERT INTO book(`name`, price, author) VALUES('45',23.56,'lzj');INSERT INTO book(`name`, price, author) VALUES('12',23.56,'lzj');INSERT INTO book(`name`, price, author) VALUES('87',23.56,'lzj');INSERT INTO book(`name`, price, author) VALUES('32',23.56,'lzj');END $

调用示例

CALL name(var, var);CALL pr1();

2. IN

创建

delimiter $CREATE procedure pr2(IN bid INT)BEGINSELECT * FROM beauty WHERE boyfriend_id = bid;END $delimiter $CREATE PROCEDURE pr3(IN username VARCHAR(20), IN `password` VARCHAR(20))BEGINDECLARE res INT;SELECT COUNT(*) INTO res FROM admin WHERE admin.username = username AND admin.`password` = `password`;SELECT res;END $

调用

CALL pr2(12);CALL pr3('john','8888');

3. OUT

创建

delimiter $CREATE PROCEDURE pr4(IN girl INT, OUT boy INT)BEGINSELECT beauty.boyfriend_id INTO boy FROM beauty WHERE beauty.id = girl;END $

调用

CALL pr4(1, @boy);SELECT @boy;

4. 两个OUT的存储过程

创建

delimiter $CREATE PROCEDURE pr5(IN girlid INT, OUT boyid INT, OUT girlname VARCHAR(20))BEGINSELECT beauty.boyfriend_id,  beauty.`name` INTO boyid, girlnameFROM beauty WHERE beauty.id = girlid;END $

调用

CALL pr5(2, @boyid, @girlname);SELECT  @boyid;SELECT @girlname;

5. INOUT

创建

delimiter $CREATE PROCEDURE pr6(INOUT a INT, INOUT b INT) BEGINSET a = a*2;SET b = b*2;END $

调用

SET @i = 2;SET @j = 4;call pr6(@i, @j);SELECT @i;SELECT @j;

6. 删除

只能同时删除一个

DROP PROCEDURE pr2;

7. 查看

show CREATE PROCEDURE pr3;
  •  
  • ↗️
↗️

MySQL变量

✇Liu Zijian's Blog

  • 系统变量：

    • 全局变量：整个服务器有效
    • 会话变量：一次连接

  • 自定义变量：

    • 用户变量
    • 局部变量

查看所有会话变量

SHOW VARIABLES ;

所有全局变量

SHOW GLOBAL VARIABLES ;

查看部分全局变量的值

SHOW GLOBAL VARIABLES LIKE '%char%';SHOW SESSION VARIABLES ;SHOW SESSION VARIABLES LIKE '%auto%'SHOW GLOBAL VARIABLES LIKE '%auto%'

查看系统变量名 

-- 默认查会话变量SELECT  @@varname ;SELECT  @@session.varname ;-- 查询系统变量SELECT @@globle.varname ;SELECT @@auto_increment_increment ;

为变量赋值

SET GLOBAL (如果是SESSION可以不写) varname = valueSET @@global varname = valueSET @@session varname = value

自定义变量

用户变量针对当前连接有效，声明时必须初始化，可以不指定类型

三种语法

SET @mycat = 'helloworld';SET @car := 'car';SELECT @cat := 'cat';

更新

适用于声明的语法，还可以查出一个值赋给变量

SELECT employees.email INTO @cat FROM employees WHERE employee_id = 100

查询变量的值

SELECT @cat

使用用户变量也需要@

SET @a = 1;SET @b = 2;SET @c = @a + @b;SELECT @c
  •  
  • ↗️
↗️

MySQL视图

✇Liu Zijian's Blog

视图，即基于查询的虚拟表，始于mysql 5.1 ，通过普通表动态生成的数据，适用于复杂查询，在使用时动态生成，只保存了SQL逻辑，适用于多个地方用到同一个SQL，且SQL复杂，可以隐藏列定义，保护数据，不会暴漏原始表。

1.创建

CREATE VIEW empjobdept AS SELECT employees.last_name AS lastname,employees.employee_id AS eid,departments.department_name AS deptname,jobs.job_title AS jobtitleFROM employees LEFT JOIN departments ON departments.department_id = employees.department_idLEFT JOIN jobs ON jobs.job_id = employees.job_id

2.使用视图

SELECT * from empjobdept

3.修改视图

存在就修改，不存在就创建

CREATE OR REPLACE VIEW AS 查询语句ALTER VIEW 视图名 AS 查询语句

4.删除视图

DROP VIEW 视图名，视图名....

5.查看视图

DESC empjobdept;show create view empjobdept;

6.修改数据

视图的数据支持更新修改和删除，而且会影响到原始表。

不能编辑视图的情况

1.包含分组函数， GROUP BY ，HAVING ，去重， 联合， 子查询等语句。
2.from不能更改的视图。
3.常量视图。
4.WHERE字句子查询引用了from字句中的表时。

7.视图和表的对比

创建语法不同，视图数据不占用物理空间，只保存逻辑。

  •  
  • ↗️
↗️

MySQL事务

✇Liu Zijian's Blog

1.事务

MySQL的事务是一组被视为一个单一逻辑工作单元的SQL操作。事务确保这些操作要么全部成功，要么在出现错误时全部失败并回滚，以保持数据库的一致性和完整性。事务通常用于需要多个步骤的操作，这些步骤必须全部成功才能确保数据的完整性，比如在银行转账中，转出和转入账户的操作必须同时完成。

在MySQL中，不同的存储引擎具有不同的特性，myisam memory存储引擎均不支持事务，如果你需要事务支持，InnoDB 是最推荐的选择，因为它在性能和事务处理方面表现优异，并且是MySQL的默认存储引擎。

查看当前存储引擎

SHOW ENGINES;

查看事务是否自动提交

SHOW VARIABLES LIKE '%autocommit%';

MySQL隐式事务INSERT， UPDATE， DELETE默认开启自动提交

2.事务的特性

  • Atomicity, 原子性：每个事务都是不可分割的单位，要么全部执行，要么全部失败
  • Consistency, 一致性：使数据库从一个一致的状态切换到另一个一致性的状态
  • Isolation, 隔离性：一个事务执行不受其他事务干扰（需要隔离级别控制）
  • Durability, 持久性：一个事务，一旦提交，就是永久性改变

3.事务的语法

提交一个事务

-- 必须,使用多个语句的事务，需要禁用隐式事务SET autocommit = 0;  -- 可选START TRANSACTION; -- 只有增删改查可以有事务，ddl语句没有事务UPDATE account SET money = money-20 WHERE id = 1; UPDATE account SET money = money+20 WHERE id = 2; -- 结束事务,应用程序外无法决定使用提交还是回滚,COMMIT,ROLLBACK只能手动选择一个COMMIT;-- ROLLBACK;

回滚到回滚点

-- 必须,使用多个语句的事务，需要禁用隐式事务SET autocommit = 0;   -- 可选START TRANSACTION; -- 只有增删改查可以有事务，ddl语句没有事务UPDATE account SET money = money-20 WHERE id = 1; -- 回滚点SAVEPOINT aUPDATE account SET money = money+20 WHERE id = 2; -- 结束事务,应用程序外无法决定使用提交还是回滚,COMMIT,ROLLBACK只能手动选择一个-- COMMIT;ROLLBACK TO a;

4.事务的隔离级别

运行多个事务，访问相同数据，如果不采取隔离机制，就会引发并发问题，设置隔离级别避免并发问题，Oracle支持:读已提交，串行化，默认读已提交，MySQL支持:读未提交 ，读已提交 ，可重复读 ，串行化，默认可重复读。

4.1 事务中的读现象

  1. 脏读：

    • 定义：一个事务读取到另一个未提交事务修改的数据，如果该事务回滚，那么读取的数据将是无效的。
    • 影响：导致数据不一致，通常与更新操作相关。

  2. 不可重复读：

    • 定义：一个事务内多次读取同一数据，结果却不一致，这是因为在两次读取之间，其他事务修改了数据。
    • 影响：导致一个事务在不同时间点读取相同数据的结果不同，通常与更新操作相关。

  3. 幻读：

    • 定义：一个事务读取到的结果和之后的读取不一致，是因为其他事务插入或删除了数据行。
    • 影响：事务在读取同一范围的数据时，发现新插入或删除的“幻影”行，通常与插入或删除操作相关。

4.2 事务的隔离级别及其特性

  1. 读未提交（Read Uncommitted）：

    • 允许现象：脏读。
    • 特点：事务可以读取到其他事务未提交的修改。
    • 问题：数据一致性最低。

  2. 读已提交（Read Committed）：

    • 避免：脏读。
    • 允许现象：不可重复读和幻读。
    • 特点：一个事务只能读取其他已提交事务的数据。
    • 问题：数据在同一事务内多次读取时，结果可能不一致。

  3. 可重复读（Repeatable Read）：

    • 避免：脏读和不可重复读。
    • 允许现象：幻读。
    • 特点：保证一个事务内多次读取同一数据时结果一致。
    • 问题：数据一致性更高，但仍可能出现幻读。

  4. 串行化（Serializable）：

    • 避免：脏读、不可重复读、幻读。
    • 特点：事务按顺序执行，完全隔离，达到最高级别的数据一致性。
    • 问题：性能最低，因事务需要排队，常引起锁竞争。

总结：

  • 脏读可以通过将隔离级别提高到 读已提交 或更高来避免。
  • 不可重复读可通过使用 可重复读 或 串行化 隔离级别来避免。
  • 幻读只有在 串行化 隔离级别下才完全解决，但这会牺牲性能。

4.3设置隔离级别

查看当前隔离级别

SELECT @@tx_isolation

设置隔离级别为读未提交

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  •  
  • ↗️
↗️

MySQL插入修改和删除

✇Liu Zijian's Blog

插入

支持一次性插入多行，支持子查询

INSERT INTO beauty(id,`name`,sex,borndate,phone,photo,boyfriend_id) VALUES (13, 'fbb', '女', '1997-01-01', '', NULL, 2);

多行

INSERT INTO beauty(id,`name`,sex,borndate,phone,photo,boyfriend_id) VALUES (17, '杨紫', '女', '1997-01-01', '', NULL, 2),(18, '蒋欣', '女', '1997-01-01', '', NULL, 2);

不支持一次插入多行

INSERT INTO beauty SET id = 14,`name` = '刘涛',phone = '135'

子查询

INSERT INTO beauty(id, `name`, phone)SELECT 26, "赵丽颖", "454" ;

修改

修改多表记录

UPDATE boys boyINNER JOIN beauty bea ON bea.boyfriend_id = boy.idSET bea.phone = "12344555"WHERE boy.id = 1UPDATE beauty LEFT JOIN boys ON boys.id = beauty.boyfriend_idSET beauty.phone = "cn666"WHERE beauty.boyfriend_id is NULL

删除

删除，有返回值，自增长从上个记录开始，支持事务回滚

DELETE FROM beauty WHERE id = 27

删谁delete后面就写谁

DELETE beautyFROM beauty INNER JOIN boys ON beauty.boyfriend_id = boys.idWHERE boys.id = 1DELETE beauty, boysFROM beauty INNER JOIN boys ON beauty.boyfriend_id = boys.idWHERE boys.id = 3

清空整个表,没有返回值，自增长从1开始，不支持事务回滚

TRUNCATE TABLE beautyy
  •  
  • ↗️
↗️

MySQL查询

✇Liu Zijian's Blog

1.常量

SELECT 6;

2.字符常量

SELECT 'a';SELECT "aaa";

3.表达式

SELECT 100 * 98;

4.函数

SELECT VERSION();SELECT DATABASE();SELECT USER();

5.别名

SELECT 'A' AS B;SELECT 'A' B;SELECT last_name AS 姓, last_name AS 名 FROM employees;

别名有特殊字符，加双引号

SELECT last_name AS "SELECT #" FROM employees;

6.去重

查员工表涉及的部门编号，字段前加上 DISTINCT

SELECT DISTINCT department_id FROM employees;

7.拼接

加号的作用是数学运算不能连接字符串，两个操作数都为数值，则进行运算，其中一方为字符型，则试图将字符转换为数值，如果转换成功，继续运算，如果转换失败，则字符型的值转换为 0，只要其中一方为 NULL，结果为 NULL

SELECT NULL + 10;SELECT last_name + first_name AS 姓名FROM employees;SELECT CONCAT(last_name, first_name) AS 姓名 FROM employees AS 姓名;

拼接 

SELECT CONCAT('a', 'b', 'c');

NULL 和任何值拼接，结果都是 NULL

SELECT CONCAT('a', 'b', NULL);

ifnull() 如果为空值

SELECT IFNULL(NULL, 'default') AS ifn;

8.条件查询

SELECT * FROM employees WHERE salary > 12000;SELECT * FROM employees WHERE department_id <> 90;SELECT * FROM employees WHERE salary >= 10000 AND salary <= 20000;SELECT * FROM employees WHERE department_id < 90 OR department_id > 110 OR salary > 15000;SELECT * FROM employees WHERE NOT(department_id >= 90 AND department_id <= 110) OR salary > 15000;

9.模糊查询

百分号代表通配符，任意字符，也包含 0 个字符，下划线 _ 代表任意单个字符，

SELECT * FROM employees WHERE last_name LIKE '%o%';

查询第三个字母是 n，第五个字母是 n

SELECT * FROM employees WHERE last_name LIKE '__n_l%';

查询第二个字符就是下划线的特殊情况，要进行转义或切换通配符，否则直接__%查询会把所有查出

SELECT * FROM employees WHERE last_name LIKE '_\_%';SELECT * FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';

10.范围查询

BETWEEN AND 可以提高简捷度，结果包含区间值，临界值的位置不能颠倒，否则零行

SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;SELECT * FROM employees WHERE salary NOT BETWEEN 8000 AND 17000;

11.IN 查询

IN 值必须相同或兼容，不能使用通配符

SELECT * FROM employees WHERE job_id IN ('IT_PROG', 'AD_VP');

12.NULL 查询

查询 NULL 用 IS / IS NOT，等于和不等于号不能判断空值

SELECT * FROM employees WHERE commission_pct IS NULL;SELECT * FROM employees WHERE commission_pct IS NOT NULL;

安全等于可用于普通值也可以判断空值

SELECT * FROM employees WHERE commission_pct <=> NULL;

13.排序查询

升序， ASC 可以省略

SELECT * FROM employees ORDER BY salary ASC;

降序

SELECT * FROM employees ORDER BY salary DESC;

条件排序

SELECT * FROM employees WHERE department_id >= 90 ORDER BY hiredate ASC;

表达式排序

SELECT   last_name,   first_name,   salary * 12 * (1 + IFNULL(commission_pct, 0)) AS annual_salary FROM   employeesORDER BY annual_salary ASC;

按照姓名长度

SELECT   LENGTH(CONCAT(last_name, first_name)) AS length,   last_name,   first_name,   salaryFROM   employeesORDER BY length;

多字段排序

SELECT * FROM employees ORDER BY salary ASC, employee_id DESC;

14.子查询

出现在其他语句中的查询语句就是子查询，也叫内查询。

子查询分为几种

  • 标量子查询：结果集一行一列
  • 列子查询：结果集一列多行
  • 行子查询：结果集一行多列
  • 表子查询:只要是查询结果就构成

子查询的使用规则

  1. 子查询必须放在小括号内。
  2. 子查询通常放在条件的右侧，例如 WHERE、HAVING 子句中的条件判断。
  3. 标量子查询：通常配合单行操作符（如 =、<> 等）使用。
  4. 列子查询：通常配合多行操作符（如 IN、ANY、SOME、ALL）使用。

子查询位置与支持情况

  • SELECT 后面：仅支持标量子查询。
  • FROM 后面：支持表子查询。
  • WHERE 和 HAVING ：支持标量子查询、列子查询，并且能支持行子查询。
  • EXISTS ：后面支持表子查询，通常用于判断某个条件是否存在。

14.1标量子查询

工资大于107号员工的人

SELECT last_name FROM employees WHERE salary > (SELECT salary FROM employees WHERE employee_id = 107)

与141号相同工种，工资比143号大的人

SELECT employee_id, last_name, job_id, salary FROM employees WHERE job_id = (select job_id FROM employees WHERE employee_id = 141)AND salary > (SELECT salary FROM employees WHERE employee_id = 143)

哪个部门最低工资大于50号部门的最低工资

SELECT job_id, MIN(salary) AS min FROM employees GROUP BY job_idHAVING min > (SELECT MIN(salary) FROM employees WHERE department_id = 50)

工资最少

select * from employeesWHERE salary = (select MIN(salary) FROM employees )

14.2 列子查询

location_id 是1400,1700的部门的员工

SELECT last_name, department_id FROM employees WHERE department_id IN (SELECT DISTINCT department_id FROM departments WHERE location_id IN (1400,1700))

比job_id 是 ‘IT_PROG’的任意一个员工工资少的其他工种的员工

SELECT * FROM employees WHERE salary < ANY(SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG')AND job_id <> 'IT_PROG'

比job_id 是 ‘IT_PROG’的所有的员工工资都少的其他工种的员工

SELECT * FROM employees WHERE salary < ALL(SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG')AND job_id <> 'IT_PROG'

查询编号最小，工资最高的人 行子查询

SELECT * FROM employees WHERE (employee_id, salary) = (SELECT MIN(employee_id), MAX(salary) FROM employees)

14.3 SELECT 后的子查询

每个部门员工个数

SELECT dept.department_id AS deptId, (SELECT COUNT(*) FROM employees WHERE department_id = dept.department_id) AS countFROM departments dept

102号员工的部门名

SELECT employees.employee_id, employees.last_name,(SELECT department_name FROM departments WHERE department_id = employees.department_id) AS deptNameFROM employees WHERE employee_id = 102

from后的子查询

每个部门平均工资等级

SELECT jg.grade_level AS lavel ,avg_dep.salary AS salary,avg_dep.deptId AS deptIdFROM job_grades jgRIGHT JOIN (SELECT AVG(salary) AS salary, department_id AS deptId FROM employees GROUP BY department_id) avg_depON avg_dep.salary BETWEEN jg.lowest_sal AND highest_sal

14.4 EXISTS后子查询

相关子查询，布尔类型，看查询是否有值，括号内写查询语句

SELECT EXISTS(SELECT * FROM employees)SELECT EXISTS(SELECT * FROM employees WHERE salary = 251526)

查询有员工的部门

SELECT department_name FROM departments WHERE EXISTS (SELECT last_name, department_id FROM employees WHERE employees.department_id = departments.department_id)

没有配偶的男生

SELECT * FROM boys WHERE NOT EXISTS (SELECT * from beauty WHERE boyfriend_id = boys.id)

15.连接查询

连接查询的分类:

按年代

  • sql92标准
  • sql99标准（推荐）

按功能

  • 内连接 ：等值连接 非等值连接 自连接
  • 外连接 ：左外连接 右外连接 全外连接
  • 交叉连接

15.1 笛卡尔积

所有行全部连接，因缺少条件造成

select `name`,boyName from beauty, boys

15.2 等值连接sql92

员工名和部门名

SELECT last_name, department_name FROM departments, employeesWHERE employees.department_id = departments.department_id

有奖金的员工名，工种号和工种名

SELECT last_name, jobs.job_id, job_title FROM jobs, employeesWHERE employees.job_id = jobs.job_idAND employees.commission_pct is not null

每个城市的部门数量

SELECT COUNT(departments.department_id) , locations.city FROM locations, departments WHERE departments.location_id = locations.location_idGROUP BY locations.city

15.3 非等值连接sql92

SELECT salary, employee_id ,grade_level from employees, job_grades AS gradesWHERE salary BETWEEN grades.lowest_sal AND grades.highest_sal

15.4 自连接sql92

员工名和上级的名字

SELECT emp.last_name , emp.employee_id , emp.manager_id FROM employees emp , employees mangWHERE emp.manager_id = mang.employee_id

15.5 等值连接sql99

SELECT job_title, last_name FROM employees empINNER JOIN jobs job ON job.job_id = emp.job_id

INNER省略

SELECT job_title, last_name FROM employees emp JOIN jobs job ON job.job_id = emp.job_id

15.6 非等值连接sql99

SELECT salary, employee_id ,grade_level from employeesINNER JOIN job_grades AS grades ON salary BETWEEN grades.lowest_sal AND grades.highest_sal

15.7 外连接

外连接
查一个表有一个表没有，主表都显示，副表和主表匹配的显示出来，没有匹配的用空填充。

15.7.1左外连接

left join 左边是主表

select beauty.`name`, boys.boyName FROM beautyLEFT OUTER JOIN boys ON beauty.boyfriend_id = boys.id

15.7.2 右外连接

right join 右边的是主表

select beauty.`name`, boys.boyName FROM boysRIGHT OUTER JOIN beauty ON beauty.boyfriend_id = boys.id

没有员工的部门

select emp.last_name , dept.department_id FROM departments dept LEFT OUTER JOIN employees empON dept.department_id = emp.department_id WHERE emp.employee_id is NULL

15.7.3 交叉连接

笛卡尔积

SELECT beauty.*, boys.* FROM beautyCROSS JOIN boys
  •  
  • ↗️
↗️

MySQL系统命令

✇Liu Zijian's Blog

登录命令

mysql -h 主机 -P 端口 -u root -p

密码与p之间不得有空格，其他项之间可以有

mysql -h 主机 -P 端口 -u root -p密码

数据库备份

mysqldump -h主机名 -P端口 -u用户名 -p密码 --database 数据库名 > 文件名.sql

导入数据库

mysql -u用户名 -p密码 feaf_accounting < /tmp/accounting.sql

  •  
  • ↗️
↗️

MySQL函数

✇Liu Zijian's Blog

MySQL函数分为两种： 单行函数：做处理 ，分组函数：做统计

1.单行函数

字节数

SELECT LENGTH('1234哈哈') AS len -- 字节数

查看客户端字符集

SHOW VARIABLES LIKE '%char%'

字符串连接 大写 小写

SELECT CONCAT('a','b','c') SELECT UPPER('ASddfDSU')SELECT LOWER('ASddfDSU')

索引从1开始，从某一位开始的截取 字符长度

SELECT SUBSTR('helloworld', 6)

从某一索引开始，截取某段长度 字符长度

SELECT SUBSTR('helloworld',1,3)SELECT CONCAT(  UPPER( SUBSTR(last_name, 1, 1) )    ) FROM employees

返回起始索引 如果找不到返回0

SELECT INSTR('helloworld','or')

去前后空格

SELECT TRIM('  vdfsv  scs  ')

去掉首尾的o

SELECT TRIM('o' FROM 'ooooooooooooooooooheoooolloooooooooooooooooooooooooooooooo')

指定字符左填充到指定长度，如果超过，右边的被截断

SELECT LPAD('hello',10,'*')SELECT LPAD('hellohellohtllohello',10,'*')

指定字符右填充到指定长度，如果超过，右边的被截断

SELECT RPAD('hello',10,'*')SELECT RPAD('hellohellohtllohello',10,'*')

替换

SELECT REPLACE('王老八夜里打酱油和酱油','酱油','酒')

数学函数

ROUND(x) 四舍五入

SELECT ROUND(1.65)

ROUND(x, y) 小数保留

SELECT ROUND(1.6545, 2)SELECT ROUND(1.4578, 2)

上取整，返回大于等于参数的最小整数

SELECT CEIL(1.025)SELECT CEIL(1.00)SELECT CEIL(-1.025)SELECT CEIL(-1.00)

下取整，返回小于等于参数的最大整数

SELECT FLOOR(1.021)SELECT FLOOR(9.6)SELECT FLOOR(-9.6)

截断

SELECT TRUNCATE(1.65,1)

%:余数

SELECT MOD(-11,3)SELECT MOD(11,3)

日期时间函数

SELECT NOW()SELECT CURRENT_TIME()SELECT YEAR(NOW())SELECT YEAR('1996-08-14')SELECT MONTH(NOW())SELECT MONTHNAME(NOW())

字符通过指定格式转换成日期

SELECT STR_TO_DATE('14-8-1996','%d-%m-%Y')SELECT STR_TO_DATE('14-8-96','%d-%m-%y')SELECT * FROM employees WHERE hiredate = '1992-4-3'SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y')SELECT DATE_FORMAT(NOW(),'%y-%m-%d')SELECT DATE_FORMAT(NOW(),'%Y-%m-%d')SELECT DATE_FORMAT(NOW(),'%c-%d %Y')SELECT last_name, DATE_FORMAT(hiredate,'%y年/%m月 %d日') AS hiredate FROM employees

流程控制函数

SELECT IF(1=1,1,0)SELECT IF(1=41,1,0)SELECT last_name, commission_pct, IF(commission_pct IS NOT NULL, '有奖金', '没奖金') FROM employees ORDER BY commission_pct DESC/*case结构1 类似switch 适合判断等值运算*/SELECTlast_name,department_id,salary,CASEdepartment_id WHEN 30 THENsalary * 1.2 WHEN 40 THENsalary * 1.5 ELSE salary END AS new_salary FROMemployees/*case结构1 类似多重if 适合判断范围运算*/SELECTlast_name,department_id,salary,CASE WHEN salary > 20000 THEN 'A'WHEN salary > 15000 THEN 'B'WHEN salary > 10000 THEN 'C'ELSE'D'END AS 工资级别 FROM employees;

2.分组函数

分组函数用作统计，又叫组函数，聚合函数，统计函数

求和，忽略空值

SELECT SUM(salary) FROM employees;

平均，忽略空值

SELECT AVG(salary) FROM employees;

最大最小值，忽略空值

SELECT MIN(salary) FROM employeesSELECT MAX(salary) FROM employeesSELECT MAX(last_name), MIN(last_name) FROM employeesSELECT MAX(hiredate), MIN(hiredate) FROM employees

count函数
计算非空的值的个数，myisam下 COUNT()效率最高，INNODB下 COUNT() COUNT(1) 效率差不多，COUNT(字段)效率最低。

SELECT COUNT(employees.commission_pct) FROM employeesSELECT COUNT(employees.employee_id) FROM employees

可以用来查询总行数，某一列一个字段有值就统计上

SELECT COUNT(*) FROM employees

加上常量值，相当于表中添加一列，可以用来查询总行数

SELECT COUNT(1) FROM employees

DISTINCT 去重后统计

SELECT count(salary) FROM employeesSELECT count(DISTINCT salary) FROM employees

分组查询

和分组函数一同查询的字段要求是group by 后的字段

每个部门的平均工资

SELECT  AVG(salary) , department_id FROM employees GROUP BY department_id

工种最高工资

SELECT MAX(salary) salary, job_id FROM employees GROUP BY job_id

每个领导下有奖金的员工的最高工资（分组前的筛选）

SELECT MAX(salary), manager_id FROM employeesWHERE commission_pct IS NOT NULLGROUP BY manager_id

哪个部门的员工数量大于2（分组后的筛选：HAVING）

SELECT COUNT(*) as count , department_id FROM employees GROUP BY department_idHAVING count > 2

每个工种有奖金的员工最高工资大于12000的工种编号和最高工资

SELECT MAX(salary) as max, job_id FROM employees WHERE  commission_pct is not null GROUP BY job_idHAVING max > 12000

领导编号大于102 的员工最低工资大于5000的 领导

SELECT min(salary) as min, manager_id FROM employees WHERE manager_id > 102GROUP BY manager_idHAVING min > 5000

按表达式筛选

按员工姓名长度分组，查员工个数大于5的姓名长度有几个

SELECT count(*) as count, LENGTH(last_name) len from employees GROUP BY lenHAVING count > 5

多个字段分组

每个部门每个工种的平均工资

SELECT AVG(salary) as salary, job_id, department_id FROM employeesGROUP BY job_id, department_idORDER BY salary DESC

每个部门每个工种的平均工资中大于10000的

SELECT AVG(salary) as salary, job_id, department_id FROM employeesGROUP BY job_id, department_idHAVING salary > 10000ORDER BY salary DESC

查各工种平均最大最小和总和

select sum(salary) as sum,min(salary) as min,max(salary) as max,avg(salary) as avg,job_id FROM employeesGROUP BY job_idORDER BY job_id DESC

3.自定义函数

  1. 函数和存储过程的区别

    函数只能有一个返回，而且必须有返回，存储过程适合批量插入，更新，函数适合处理数据后，得到一个结果，适合查询。

  2. 创建

CREATE FUNCTION 名(参数列表) RETURNS 返回类型BEGIN  函数体(必须有return 语句)(函数体只有一句话，可以省略BEGIN END)END
delimiter $CREATE FUNCTION fun1() RETURNS INTBEGINDECLARE count INT DEFAULT 0;SELECT COUNT(*) INTO count FROM myemployees.employees;RETURN count;END $
delimiter $CREATE FUNCTION getPriceFromId(empId INT) RETURNS DOUBLEBEGINDECLARE price DOUBLE DEFAULT 0.0;SELECT employees.price INTO price FROM employees WHERE employee_id = empId;RETURN price;END $
  1. 调用
SELECT fun1();
  1. 查看函数
SHOW CREATE FUNCTION getPriceFromId
  1. 删除函数
DROP FUNCTION getPriceFromId
  1. 循环结构
  • WHILE :
标签:WHILE 条件 DO循环体END WHILE 标签;
  • LOOP:
标签: LOOP循环体;IF 退出条件 THENLEAVE 标签; END IF; END LOOP 标签;
  • REPEAT:
标签:REPEAT循环体UNTIL 结束条件 END REPEAT 标签;
  1. 循环控制语句：

  • ITERATE(继续，结束本次继续下次)

  • LEAVE (break,跳出所在循环)

  •  
  • ↗️