MySQL笔记

很全的MySQL常用指令

安装MySQL

在官方下载地址下载压缩包。

解压到要保存的路径，在 环境变量 -> 系统变量 -> Path 里面添加一行，指向MySQL的bin目录，如D:\mysql-8.0.33-winx64\bin。

在MySQL的bin文件夹里新建文件my.ini，文件内容为：

[mysqld]
# 设置mysql的安装目录(能看到bin即可)
basedir=D:\mysql-8.0.33-winx64\
# 设置mysql的数据存放目录
datadir=D:\mysql-8.0.33-winx64\data\
# mysql端口
port=3306
# 跳过授权
skip-grant-tables

记得把其中的路径换成你自己的安装路径。

然后以管理员身份打开cmd，进入MySQL的bin目录，输入：

# 安装 mysql 服务
mysqld –install

# 初始化 data 文件夹 （你可以初始化多次，但是每次必须清空data文件夹）
mysqld --initialize-insecure --user=mysql

# 启动 mysql 服务
net start mysql

# 登录 mysql(让你输密码，直接回车即可)
mysql -uroot -p

# 修改 root 账户密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY '新密码';

# 刷新权限，使之生效
flush privileges;

然后将my.ini里跳过授权给注释掉# skip-grant-tables

输入\q退出登录MySQL，然后重启MySQL用新设置的密码登录即可：

net stop mysql
net start mysql
mysql -uroot -p

mysql -uroot -p这句意思是用户名为root，也可以在-p后直接跟密码，这样密码是可见的。

Windows Server 2022 上遇到的问题

安装MySQL和初始化data文件夹时报错：找不到MSVCP140.dll文件

解决方法：安装 Microsoft Visual C++ Redistributable 客户端库，点击前往下载。

输入net start mysql时报错：服务没有响应控制功能

解决方法：下载VCRUNTIME140_1.dll，解压后拷贝到MySQL的bin文件夹下。

远程连接数据库

开放MySQL端口

首先要确保服务器的MySQL端口是放行的，在my.ini中我们设置了3306端口。

以阿里云服务器为例，在安全组中手动添加访问规则，目的端口3306，源0.0.0.0。

若端口没有放行，则会有以下报错：

ERROR 2003 (HY000): Can't connect to MySQL server on 'XXX.XXX.XXX.XXX:3306' (10060)

设置远程登录权限

在服务器上登录MySQL，然后输入：

use mysql;
update user set host='%' where user='root'; // 开放远程登录权限
flush privileges;   // 刷新权限

若没开启权限，则报错：

ERROR 1130 (HY000): Host 'XXX.XXX.XXX.XXX' is not allowed to connect to this MySQL server

远程登录MySQL

在自己的电脑上打开cmd，输入：

mysql -h主机地址 -u用户名 -p

MySQL数据的备份

终端进入MySQL安装目录下的bin目录，执行指令：

mysqldump -uroot -p 数据库名 > 备份文件路径及文件名

# 如：
mysqldump -uroot -p flower_shop > flower_shop_backup.sql

MySQL备份数据的还原

登录MySQL后执行以下指令：

create database 数据库名称;  // 创建一个新的数据库用于承载迁移来的数据
use 数据库名称;              // 切换到该数据库
source 文件路径;             // 导入备份数据

存储过程 / Procedure

相当于编程语言中的方法，定义SQL语句模板，可以传参调用。

创建一个存储过程，用于查询某种花的价格，调用后删除：

delimiter $$
create procedure getFlowerPrice(in name varchar(15))
begin
    select price from flower where flower.name=name;
end $$
delimiter ;

call getFlowerPrice('红玫瑰');

drop procedure getFlowerPrice;  # 删除存储过程

其中delimiter作用是修改SQL语句结束符，避免存储过程中的;和语句结束符混淆。

触发器 / Trigger

在数据插入、修改、删除时自动执行某些操作，可用于数据库日志记录等。

创建一个触发器，当录入一种花但未给出花语时，将花语设为“暂无”：

delimiter $$
create trigger my_trigger before insert on flower for each row
begin
    if new.flower_language is null then
        set new.flower_language='暂无';
    end if;
end $$
delimiter ;

show triggers;  # 查看触发器

drop trigger flower_shop.my_trigger;    # 删除触发器