本文已参与「新人创作礼」活动，一起开启掘金创作之路。

在 Ubuntu 18 中在线安装 MySQL

# 请从 https://dev.mysql.com/downloads/repo/apt/ 中取得最新的源
$ wget -c https://repo.mysql.com//mysql-apt-config_0.8.22-1_all.deb  
# ...
$ dpkg -i mysql-apt-config_0.8.22-1_all.deb  
# ...
# 选择第四行 `ok`, 回车, 将默认源设置为 `mysql-8.0`
# ...
$ apt update
# ...
$ apt-get install mysql-server
# ...
# 要求设置密码, 例如设置为12qw!@QW
# 选择默认的认证方式为 Use Legacy Authentication Method (Retain MySQL 5.x Compatibility)
# ...
# MySQL服务已经启动, 并且 root@localhost 认证方式为 mysql_native_password, 密码为 12qw!@QW

在 CentOS 7 中在线安装 MySQL

# 请从 https://dev.mysql.com/downloads/repo/yum/ 中取得最新的源
$ wget -i -c https://dev.mysql.com/get/mysql80-community-release-el7-5.noarch.rpm
# ...
$ yum -y install mysql80-community-release-el7-5.noarch.rpm
# ...
$ yum -y install mysql-community-server
# ...
# 这里结果与Ubuntu不同

在 CentOS 7 中离线安装 MySQL

# 请从 https://dev.mysql.com/downloads/mysql/ 中取得最新的源, **注意: 需要自行检查perl环境**  
# 检查是否存在冲突的 mariadb
$ yum list installed | grep mariadb
# 删除 mariadb
$ yum -y remove mariadb*
# 安装 mysql, 根据实际情况可能需要调整
$ rpm -ivh mysql-community-icu-data-files-8.0.28-1.el7.x86_64.rpm
$ rpm -ivh mysql-community-common-8.0.28-1.el7.x86_64.rpm
$ rpm -ivh mysql-community-client-plugins-8.0.28-1.el7.x86_64.rpm
$ rpm -ivh mysql-community-libs-8.0.28-1.el7.x86_64.rpm
$ rpm -ivh mysql-community-client-8.0.28-1.el7.x86_64.rpm
$ rpm -ivh mysql-community-server-8.0.28-1.el7.x86_64.rpm

首次安装需要进行数据库的配置

# 启动 mysql
$ systemctl start mysqld
# 查询初始密码
$ grep 'temporary password' /var/log/mysqld.log
# 进行初始化
$ mysql_secure_installation

配置远程访问

默认情况下, MySQL拒绝root远程登录, 需要对以下部分进行配置

# 远程访问
$ mysql -h 192.168.1.243 -P 3306 -u root -p

云服务供应商的安全策略

在阿里云实例安全组中配置MySQL的访问端口

服务器主机的防火墙

① 可以选择关闭防火墙

$ systemctl stop firewalld

② 在不关闭防火墙的情况下, 允许某端口的外来链接

# 查看3306端口是否开启
$ firewall-cmd --query-port=3306/tcp  
no
# 开启3306端口
$ firewall-cmd --zone=public --add-port=3306/tcp --permanent  
success
# 重启防火墙
$ firewall-cmd --reload  
success
# 查看3306端口是否开启
$ firewall-cmd --query-port=3306/tcp  
yes
# 端口33060同理

新增一个远程用户

$ mysql -u root -p
# Enter password: 
# Welcome to the MySQL monitor.  Commands end with ; or \g.
mysql> USE mysql;
mysql> SELECT Host, User, plugin, authentication_string FROM user;
# +-----------+------------------+-----------------------+------------------------------------------------------------------------+
# | host      | user             | plugin                | authentication_string                                                  |
# +-----------+------------------+-----------------------+------------------------------------------------------------------------+
# | localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
# | localhost | mysql.session    | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
# | localhost | mysql.sys        | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
# | localhost | root             | caching_sha2_password | $A$005$2ML2cG]vp,j *2td|%pmfxg7D19RlbbVQGtDIqs0w0A1tYTW7To2iCnLglT79   |
# +-----------+------------------+-----------------------+------------------------------------------------------------------------+
# 4 rows in set (0.00 sec)

# mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '12qw!@QW';
# Query OK, 0 rows affected (0.001 sec)

mysql> CREATE USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '12qw!@QW'; 
# Query OK, 0 rows affected (0.001 sec)

mysql> GRANT ALL ON *.* TO 'root'@'%'; 
# Query OK, 0 rows affected (0.001 sec)

mysql> FLUSH PRIVILEGES; 
# Query OK, 0 rows affected (0.00 sec)

mysql> EXIT;
# Bye

可能会遇到的问题

忘记 'root'@'localhost' 的密码

$ brew services stop mysql # 停止mysql
# Stopping `mysql`... (might take a while)
# ==> Successfully stopped `mysql` (label: homebrew.mxcl.mysql)
# 安全启动mysql                                              
$ /usr/local/Cellar/mysql/8.0.25_1/bin/mysqld_safe --skip-grant-tables & 
# [1] 4121
# $ 2021-11-05T09:38:31.6NZ mysqld_safe Logging to '/usr/local/var/mysql/Weizhens-Mac-mini.local.err'.
# 2021-11-05T09:38:31.6NZ mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql
# 
$ mysql  # 进入mysql
# Welcome to the MySQL monitor.  Commands end with ; or \g.
# Your MySQL connection id is 7
# Server version: 8.0.25 Homebrew

# Copyright (c) 2000, 2021, Oracle and/or its affiliates.

# Oracle is a registered trademark of Oracle Corporation and/or its
# affiliates. Other names may be trademarks of their respective
# owners.

# Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use mysql; # 切换数据库
# Reading table information for completion of table and column names
# You can turn off this feature to get a quicker startup with -A

# Database changed
mysql> flush privileges;
# Query OK, 0 rows affected (0.01 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Whmx0319';
# Query OK, 0 rows affected (0.01 sec)

mysql> exit;
# Bye
$ brew services start mysql
# ==> Successfully started `mysql` (label: homebrew.mxcl.mysql)

在 BASH 使用的命令

# 查询初始密码
$ grep 'temporary password' /var/log/mysqld.log
# 运行配置向导
$ mysql_secure_installation 
# 查找 mysqld 的路径
$ which mysqld
# 查找 mysqld 的配置文件
$ /usr/local/mysql/bin/mysqld --verbose --help |grep -A 1 'Default options'
# 通常情况下, 配置文件位于:
# /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

# 这个是远程登录的命令, 使用账号 root 和密码连接 MySQL
$ mysql -h 192.168.1.100 -P 3306 -u root -p 
# 这个是本机登录的命令, 使用账号 root 和密码连接 MySQL
$ mysql -u root -p 
# 这个是本机登录的命令, 可以将密码混入命令中
$ mysql -uroot -pMyPassword
$ mysql --user=MyUser --password=MyPassword

# 启动 MySQL
$ mysql.server start
# 停止 MySQL 
$ mysql.server stop 
# 查看 MySQL 运行状态
$ mysql.server status 
# 停止并重启 MySQL
$ mysql.server restart 

在 MySQL 客户端中使用的语句

-- 返回可用数据库的一个列表
SHOW DATABASES;

-- 选择指定的数据库 
USE mysql;

-- 返回当前选择的数据库内可用表的列表
SHOW TABLES;

-- 显示表的列
SHOW COLUMNS FROM mysql; 
-- 等效的另一种表达 
DESCRIBE mysql;

-- 显示广泛的服务器状态信息
SHOW STATUS;

-- 显示创建数据库SQL
SHOW CREATE DATABASE mxt21d05;

-- 显示创建表的SQL
SHOW CREATE TABLE part_entity;

-- 用来显示授予用户(所有用户或特定用户)的安全权限
SHOW GRANTS;
SHOW GRANTS FOR 'root';
SHOW GRANTS FOR 'root'@'%';
SHOW GRANTS FOR 'root'@'localhost';

-- 用来显示服务器错误消息
SHOW ERRORS;

-- 用来显示服务器警告消息
SHOW WARNINGS;

-- 查询时区
SHOW VARIABLES LIKE '%time_zone%';

-- 查看密码安全策略
SHOW VARIABLES LIKE '%validate_password%';

-- 密码策略: 密码长度最少为6
SET GLOBAL validate_password.length=6;

-- 密码策略: 特殊字符最少0个
SET GLOBAL validate_password.special_char_count=0;

-- 显示所有活动进程, 以及它们的线程ID和执行时间
SHOW PROCESSLIST;

-- 查看当前版本
SELECT @@VERSION;

-- 查看当前用户
SELECT CURRENT_USER;

-- 删除用户
DROP USER 'zhang'@'%';

-- 新增一个用户zhang, 密码为12345678, 可以从任何IP访问
CREATE USER 'zhang'@'%' IDENTIFIED BY '12345678'; 
CREATE USER 'zhang'@'%' IDENTIFIED WITH caching_sha2_password BY '12345678'; 

-- 修改认证方式
ALTER USER 'zhang'@'%' IDENTIFIED BY '88888888';
ALTER USER 'zhang'@'%' IDENTIFIED WITH mysql_native_password BY '88888888';

-- 数据控制语言(DCL)

-- 在使用 GRANT 和 REVOKE 时, 用户账号必须存在, 但对所涉及的对象没有这个要求. 这允许管理员在创建数据库和表之前设计和实现安全措施. 
-- 这样做的副作用是, 当某个数据库或表被删除时(用 DROP 语句), 相关的访问权限仍然存在. 而且, 如果将来重新创建该数据库或表, 这些权限仍然起作用. 

-- 设置zhang的权限为ALL(除 GRANT OPTION 外的所有权限), 可以访问任意库的任意表
GRANT ALL ON *.* TO 'zhang'@'%'; 

-- 取消zhang@%的这些权限: 任意库的任意表上的 SELECT 和 INSERT
REVOKE SELECT, INSERT ON *.* FROM 'zhang'@'%'; 

-- 使权限设置生效, 不需要重启 MySQL
FLUSH PRIVILEGES; 

-- 退出 `mysql client`
EXIT; 

-- 事务处理语言(DPL)

-- 数据定义语言(DDL)

-- 删除数据库
DROP DATABASE world;

-- 销毁表
DROP TABLE user;

-- 截断表, 相当于 DELETE FROM users
TRUNCATE TABLE users;

-- 创建表
CREATE TABLE IF NOT EXISTS user (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER);
CREATE TABLE user (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER);
CREATE TABLE user (id INTEGER, name TEXT, age INTEGER, PRIMARY KEY(id, name));

-- 修改表, 增加一列
ALTER TABLE tab_name ADD COLUMN col_name TEXT;

-- 修改表, 修改表名
ALTER TABLE tab_name RENAME TO new_tab_name;

-- 修改列, 修改列名和类型
ALTER TABLE sys_session 
CHANGE COLUMN `session`     `id`   varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL FIRST,
CHANGE COLUMN `create_time` `ct`   datetime NOT NULL AFTER `id`,
CHANGE COLUMN `update_time` `ut`   datetime NOT NULL AFTER `ct`,
CHANGE COLUMN `id`          `user` int      NOT NULL AFTER `update_time`,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`id`) USING BTREE;

-- 数据操纵语言 (DML：Data Manipulation Language)

-- 删除行
DELETE FROM user WHERE name = 'saber';

-- 添加行
INSERT INTO user(name, age) VALUES('saber', 18);
INSERT INTO user(name, age) VALUES('saber', 18), ('baserker', 22);
INSERT INTO user SET name = 'saber', age = 18;
INSERT INTO user_map (userid, deptid) SELECT u.id, u.deptid FROM user u;

-- 修改行
UPDATE user SET age = 16 WHERE name = 'saber';

-- 数据查询语言(DQL: Data Query Language)

-- 查询行
SELECT * FROM user;

-- 查询表的默认值
SELECT COLUMN_NAME, COLUMN_DEFAULT FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'library_name' AND TABLE_NAME = 'table_name';

-- 分组并按照组内字段排序, 取得最大(最小)值所在的行, 请注意与 GROUP BY 的区别
SELECT *, ROW_NUMBER() OVER(PARTITION BY product_sn ORDER BY start_time ASC) AS rn FROM test_general WHERE name = 'saber';

-- 从组内排序的结果中, 选择符合条件的行
SELECT * FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY product_sn ORDER BY start_time ASC) AS rn FROM test_general WHERE name = 'saber') b WHERE rn = 1 AND state = 'PASS' ORDER BY start_time;

-- 按照 name 分组取 val 最大
SELECT a.* FROM tb a WHERE val = (SELECT MAX(val) FROM tb WHERE name = a.name) ORDER BY a.name;

-- DISTINCT关键字应用于所有列。`backup, code` 都相同时, 只输出一行
SELECT DISTINCT backup, code FROM part_entity ORDER BY code LIMIT 10;

-- 正则匹配: MySQL 的正则表达式没有完全实现, 并且不能区分大小写
SELECT * FROM part_entity WHERE code REGEXP '1100001';
SELECT * FROM part_entity WHERE code REGEXP '1100001|1100002';
SELECT * FROM part_entity WHERE code REGEXP '110000[123]';
SELECT * FROM part_entity WHERE code REGEXP '^[a-z]0000[0-9]{2,}$';

-- 计算字段
SELECT CONCAT(code, '(', version, ')'), LTRIM(description), price * stock FROM part_entity LIMIT 10;

-- 条件, 分组, 过滤, 排序
SELECT pcode, COUNT(scode) AS total FROM part_formula WHERE backup = '' GROUP BY pcode HAVING total > 10 ORDER BY total DESC LIMIT 10;

-- 子查询. 如果子查询的结果不是一列并且一行, 会报错
SELECT *, (SELECT value FROM opts_entity WHERE class = 'default_baud' LIMIT 1) AS default_baud FROM opts_entity WHERE class = 'burning_mode';
SELECT * FROM part_formula WHERE pcode = (SELECT code FROM part_entity WHERE code = '2200016' LIMIT 1);

-- 多表查询, 叉联结, 内部联结, 等值联结. 
-- 它基于两个表之间的相等测试, 这几种表达方式等效, 但是推荐 INNER JOIN 写法
SELECT a.*, b.* FROM sess_entity a, user_entity b WHERE a.id = b.id;
SELECT a.*, b.* FROM sess_entity a CROSS JOIN user_entity b ON a.id = b.id;
SELECT a.*, b.* FROM sess_entity a INNER JOIN user_entity b ON a.id = b.id;