使用数据库 MySQL

鸿之翼

于 2023-11-28 15:28:12 发布

阅读量112

点赞数

分类专栏： Web基础文章标签：数据库 mysql

本文链接：https://blog.csdn.net/weixin_42350100/article/details/134596928

版权

Web基础专栏收录该内容

3 篇文章 0 订阅

订阅专栏

MySQL 数据库

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle旗下产品。

下载

下载地址 https://dev.mysql.com/downloads/

在这里插入图片描述
MySQL针对不同的用户提供了2中不同的版本：

MySQL Community Server：社区版。由MySQL开源社区开发者和爱好者提供技术支持，对开发者开放源代码并提供免费下载。
MySQL Enterprise Server：企业版。包括最全面的高级功能和管理工具，不过对用户收费。

选择 MySQL Community Server

在这里插入图片描述

在 macOS 上，uname 命令用于获取系统内核和操作系统的相关信息。以下是 uname 在 macOS 上的输出解析：
-a：打印全部系统信息，包括内核名称、主机名、操作系统版本号等。
-s：打印内核名称，通常为 “Darwin”。
-n：打印主机名。
-r：打印内核发布版本号。
-v：打印操作系统发行版信息。
-m：打印硬件架构名称，例如 “x86_64” 表示 64 位 Intel 架构，如果输出结果是 arm64，则表示你的系统是 arm64 架构。

根据自己的操作系统下载对应的32位或64位安装包。

% uname -m
x86_64

在这里插入图片描述
注册，登录，或者直接下载

安装

下载完成后双击安装 mysql-8.2.0-macos13-x86_64.dmg

在这里插入图片描述

在这里插入图片描述

按步骤完成安装，中途需要输入一次开机密码

在这里插入图片描述
配置MySQL Server的密码策略

use strong password encryption，使用强密码加密。mysql8 支持基于SHA256的更强的认证方式，所有新安装的mysql server建议使用这个方式。clients 或 connectors 如果不支持这个方式，就不能连接到这个 mysql server。☑️
use legacy password encryption，使用老式的密码加密。

在这里插入图片描述
配置root的密码：合理的密码规则为：至少8个字符，包含数字、字母、其他字符
记住输入的密码，用于后续连接数据库
再次输入一次开机密码

安装完成，可以选择将“MySQL 8.2.0-community”安装器移到废纸篓

打开“系统偏好设置”，发现mysql server已经安装了：

在这里插入图片描述

可以选择安装 MySQL Workbench，Navicat 等可视化工具操作数据库，或者在控制台进入 mysql 命令

配置 MySQL 环境变量

打开终端，进入配置文件 ~/.zshrc

% vim ~/.zshrc

zsh对应的配置文件是 .zshrc (vim ~/.zshrc)
bash对应的是 .bash_profile (vim ~/.bash_profile)
按照对应修改文件即可

按字母 i 进入编辑模式，加入 MySQL 环境变量

export PATH=$PATH:/usr/local/mysql/bin

英文状态下按下 esc 键，并输入 :wq
重新加载配置文件

% source ~/.zshrc

检查是否配置成功

 % mysql --version                  
mysql  Ver 8.2.0 for macos13 on x86_64 (MySQL Community Server - GPL)

如果正常显示adb版本号，说明配置成功

MySQL 命令

进入 mysql 命令行

格式： mysql -h 主机地址 -u 用户名－p 用户密码

连接到本机上的 MySQL：

% mysql -u root -p                 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.2.0 MySQL Community Server - GPL

Copyright (c) 2000, 2023, 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>

root 为数据库用户名，输入密码回车即可进入 mysql 命令行，MySQL的提示符是： mysql>

连接到远程主机上的 MySQL：

假设远程主机的IP为：110.110.110.110，用户名为root, 密码为abcd123。则键入以下命令：

mysql -h 110.110.110.110 -u root -p abcd123

退出 mysql 命令

格式： exit

mysql> exit
Bye

修改用户密码

格式：mysqladmin -u 用户名 -p password 新密码
输入旧密码

% mysqladmin -u root -p password sdr23qi2
Enter password: 
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

使用 alter user 修改用户密码
格式：alter user 用户名[@“主机”] identified by “密码”;
修改当前登陆用户的密码：alter user user() identified by “密码”;
使密码过期: alter user 用户名[@“主机”] identified by “密码” password expire;
使密码从不过期: alter user 用户名[@“主机”] identified by “密码” password expire never;
按默认设置过期时间: alter user 用户名[@“主机”] identified by “密码” password expire default;
指定过期间隔: alter user 用户名[@“主机”] identified by “密码” password expire interval 90 day;

创建数据库

格式：mysql> create database <数据库名>
MYSQL环境中的命令，所以后面都带一个 “分号” 作为命令结束符。

mysql> create database book;
Query OK, 1 row affected (0.16 sec)

mysql> create database chat_db;
Query OK, 1 row affected (0.04 sec)

显示数据库

格式：mysql> show databases

查询所有数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| book               |
| chat_db            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.01 sec)

删除数据库

格式：mysql> drop database <数据库名>

mysql> drop database book;
ERROR 1008 (HY000): Can't drop database 'book'; database doesn't exist
mysql> create database book;
Query OK, 1 row affected (0.03 sec)

mysql> drop database book;
Query OK, 0 rows affected (0.05 sec)

mysql> drop database if exists book
    -> ;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create database book;
Query OK, 1 row affected (0.01 sec)

mysql> drop database if exists book
    -> ;
Query OK, 0 rows affected (0.01 sec)

ERROR 1008 (HY000): Can’t drop database ‘book’; database doesn’t exist
发生错误，不能删除 ‘book’ 数据库，该数据库不存在。

if exists 判断数据库是否存在，不存在也不产生错误
drop database if exists book;

产生一个警告说明此数据库不存在
Query OK, 0 rows affected, 1 warning (0.00 sec)

使用数据库

格式：mysql> use <数据库名>

use 语句可以通告 MySQL 把 db_name 数据库作为默认(当前)数据库使用，用于后续语句。该数据库保持为默认数据库，直到语段的结尾，或者直到发布一个不同的 USE 语句

show databases 可以查询所有数据库，如果想跳到其他数据库，用 "use 其他数据库名字"命令就可以跳到其他数据库。

mysql> USE book
Database changed
mysql> USE chat_db
Database changed
mysql> 

mysql> use book
ERROR 1049 (42000): Unknown database 'book'
mysql> create database book;
Query OK, 1 row affected (0.00 sec)

mysql> use book;
Database changed

select：当前连接的数据库

格式：mysql> select database();

当前连接的数据库

mysql> select database();
+------------+
| database() |
+------------+
| book       |
+------------+
1 row in set (0.00 sec)

显示MySQL的版本

mysql> select version(); 
+-----------+
| version() |
+-----------+
| 8.2.0     |
+-----------+
1 row in set (0.00 sec)

显示当前时间

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2023-11-27 14:32:01 |
+---------------------+
1 row in set (0.00 sec)

显示年月日

mysql> SELECT DAYOFMONTH(CURRENT_DATE);
+--------------------------+
| DAYOFMONTH(CURRENT_DATE) |
+--------------------------+
|                       27 |
+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT MONTH(CURRENT_DATE);
+---------------------+
| MONTH(CURRENT_DATE) |
+---------------------+
|                  11 |
+---------------------+
1 row in set (0.26 sec)

mysql> SELECT YEAR(CURRENT_DATE); 
+--------------------+
| YEAR(CURRENT_DATE) |
+--------------------+
|               2023 |
+--------------------+
1 row in set (0.00 sec)

显示字符串

mysql> SELECT "welecome to my blog!";
+----------------------+
| welecome to my blog! |
+----------------------+
| welecome to my blog! |
+----------------------+
1 row in set (0.00 sec)

当计算器用

mysql> select ((4 * 4) / 10 ) + 25;
+----------------------+
| ((4 * 4) / 10 ) + 25 |
+----------------------+
|              26.6000 |
+----------------------+
1 row in set (0.06 sec)

拼接字符串

CONCAT()函数，用来把字符串串接起来。

mysql> select CONCAT(f_name, " ", l_name) 
    -> AS Name 
    -> from employee_data 
    -> where title = 'Marketing Executive';
ERROR 1146 (42S02): Table 'book.employee_data' doesn't exist

创建表

格式：mysql> create table <表名> ( <字段名1> <类型1> [,…<字段名n> <类型n>]);

mysql> create table Student(
    -> id int(4) not null primary key auto_increment,
    -> name char(20) not null,
    -> sex int(4) not null default '0',
    -> degree double(16,2)
    -> );
Query OK, 0 rows affected, 3 warnings (0.47 sec)

建立一个名为 Student 的表，

字段名	数字类型	数据宽度	是否为空	是否主键	自动增加	默认值
id	int	4	否	primary key	auto_increment
name	char	20	否
sex	int	4	否			0
degree	double	16	是

数据类型

char(size) 保存固定长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的长度。最多 255 个字符。
varchar(size) 保存可变长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的最大长度。最多 255 个字符。如果值的长度大于 255，则被转换为 TEXT 类型。
tinytext 存放最大长度为 255 个字符的字符串。
text 存放最大长度为 65,535 个字符的字符串。
blob 用于 BLOBs（Binary Large OBjects）。存放最多 65,535 字节的数据。
mediumtext 存放最大长度为 16,777,215 个字符的字符串。
mediumblob 用于 BLOBs（Binary Large OBjects）。存放最多 16,777,215 字节的数据。
longtext 存放最大长度为 4,294,967,295 个字符的字符串。
longblob 用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。
enum(x,y,z,etc.) 允许输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值，则插入空值。这些值是按照输入的顺序排序的。可以按照此格式输入可能的值： enum(‘X’,‘Y’,‘Z’)
set 与 ENUM 类似，不同的是，SET 最多只能包含 64 个列表项且 SET 可存储一个以上的选择。
tinyint(size) 带符号-128到127 ，无符号0到255。
smallint(size) 带符号范围-32768到32767，无符号0到65535, size 默认为 6。
mediumint(size) 带符号范围-8388608到8388607，无符号的范围是0到16777215。 size 默认为9
int(size) 带符号范围-2147483648到2147483647，无符号的范围是0到4294967295。 size 默认为 11
bigint(size) 带符号的范围是-9223372036854775808到9223372036854775807，无符号的范围是0到18446744073709551615。size 默认为 20
float(size,d) 带有浮动小数点的小数字。在 size 参数中规定显示最大位数。在 d 参数中规定小数点右侧的最大位数。
double(size,d) 带有浮动小数点的大数字。在 size 参数中规显示定最大位数。在 d 参数中规定小数点右侧的最大位数。
decimal(size,d) 作为字符串存储的 DOUBLE 类型，允许固定的小数点。在 size 参数中规定显示最大位数。在 d 参数中规定小数点右侧的最大位数。
date() 日期。格式：YYYY-MM-DD 支持的范围是从 ‘1000-01-01’ 到 ‘9999-12-31’
datetime() 日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS 支持的范围是从 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’
timestamp() 时间戳。TIMESTAMP 值使用 Unix 纪元(‘1970-01-01 00:00:00’ UTC) 至今的秒数来存储。格式：YYYY-MM-DD HH:MM:SS 支持的范围是从 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-09 03:14:07’ UTC
time() 时间。格式：HH:MM:SS 支持的范围是从 ‘-838:59:59’ 到 ‘838:59:59’
year() 2 位或 4 位格式的年。 4 位格式所允许的值：1901 到 2155。2 位格式所允许的值：70 到 69，表示从 1970 到 2069。

获取表结构

格式：desc 表名;
格式：show columns from 表名;

mysql> desc Student;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| id     | int          | NO   | PRI | NULL    | auto_increment |
| name   | char(20)     | NO   |     | NULL    |                |
| sex    | int          | NO   |     | 0       |                |
| degree | double(16,2) | YES  |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+
4 rows in set (0.14 sec)

mysql> show columns from Student
    -> ;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| id     | int          | NO   | PRI | NULL    | auto_increment |
| name   | char(20)     | NO   |     | NULL    |                |
| sex    | int          | NO   |     | 0       |                |
| degree | double(16,2) | YES  |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

Key 那一栏，可能会有4种值，即 ’ '，‘PRI’，‘UNI’，‘MUL’。

’ ', 该列的值可以重复, 表示该列没有索引, 或者是一个非唯一的复合索引的非前导列；
‘PRI’, 该列是主键的组成部分；
‘UNI’, 该列是一个唯一值索引的第一列(前导列),并不能含有空值(NULL)；
‘MUL’, 该列的值可以重复, 该列是一个非唯一索引的前导列(第一列)或者是一个唯一性索引的组成部分但是可以含有空值NULL。

同时满足上述4种情况的多种，比如一个列既是PRI, 又是UNI，那么"desc 表名"的时候，显示的Key值按照优先级来显示，PRI->UNI->MUL。

删除表

格式：drop table <表名>

mysql> drop table student;
Query OK, 0 rows affected (0.27 sec)

mysql> drop table student;
ERROR 1051 (42S02): Unknown table 'book.student'

mysql> drop table if exists student;
Query OK, 0 rows affected, 1 warning (0.00 sec)

drop table 用于取消一个或多个表。所有的表数据和表定义会被取消，所以使用本语句要小心！

表插入数据

格式：insert into <表名> [( <字段名1>[,…<字段名n > ])] values ( 值1 )[, ( 值n )]

mysql> insert into student(id,name,degree) values(1,'Tom',96.45),(2,'Joan',82.99
), (3,'Wang', 96.59);
ERROR 1146 (42S02): Table 'book.student' doesn't exist

mysql> create table Student( id int(4) not null primary key auto_increment, name char(20) not null, sex int(4) not null default '0', degree double(16,2) );
Query OK, 0 rows affected, 3 warnings (0.10 sec)

mysql> insert into student(id,name,degree) values(1,'Tom',96.45),(2,'Joan',82.99), (3,'Wang', 96.59);
Query OK, 3 rows affected (0.12 sec)
Records: 3  Duplicates: 0  Warnings: 0

查询表中的数据

格式： select <字段1，字段2，…> from < 表名 > where < 表达式 >
格式： select distinct <字段1，字段2，…> from < 表名 >;

查询所有行

mysql> select * from student;
+----+------+-----+--------+
| id | name | sex | degree |
+----+------+-----+--------+
|  1 | Tom  |   0 |  96.45 |
|  2 | Joan |   0 |  82.99 |
|  3 | Wang |   0 |  96.59 |
+----+------+-----+--------+
3 rows in set (0.02 sec)

查询前几行数据

mysql> select * from student order by id limit 0,2;
+----+------+-----+--------+
| id | name | sex | degree |
+----+------+-----+--------+
|  1 | Tom  |   0 |  96.45 |
|  2 | Joan |   0 |  82.99 |
+----+------+-----+--------+
2 rows in set (0.03 sec)

select一般配合where使用，以查询更精确更复杂的数据。

返回唯一不同的值

mysql> select * from user;
+----+------+-----+--------+----------+
| id | name | sex | degree | passtest |
+----+------+-----+--------+----------+
|  2 | Mary |   0 |  82.99 | 0        |
|  3 | Wang |   0 |  96.59 | 0        |
|  4 | Tom  |   0 |  96.45 | 1        |
|  5 | Test |   0 |  92.00 | 1        |
|  6 | Fren |   0 |  56.00 | 2        |
+----+------+-----+--------+----------+
5 rows in set (0.01 sec)

mysql> select distinct passtest from user;
+----------+
| passtest |
+----------+
| 0        |
| 1        |
| 2        |
+----------+
3 rows in set (0.06 sec)

删除表中数据

格式：delete from 表名 where 表达式

mysql> select * from student;
+----+------+-----+--------+
| id | name | sex | degree |
+----+------+-----+--------+
|  1 | Tom  |   0 |  96.45 |
|  2 | Joan |   0 |  82.99 |
|  3 | Wang |   0 |  96.59 |
+----+------+-----+--------+
3 rows in set (0.00 sec)

mysql> delete from student where id=1;
Query OK, 1 row affected (0.05 sec)

mysql> select * from student;
+----+------+-----+--------+
| id | name | sex | degree |
+----+------+-----+--------+
|  2 | Joan |   0 |  82.99 |
|  3 | Wang |   0 |  96.59 |
+----+------+-----+--------+
2 rows in set (0.00 sec)

修改表中数据

格式：update 表名 set 字段=新值,… where 条件

UPDATE 语法可以用新值更新原有表行中的各列。SET子句指示要修改哪些列和要给予哪些值。WHERE子句指定应更新哪些行。如果没有WHERE子句，则更新所有的行。如果指定了ORDER BY子句，则按照被指定的顺序对行进行更新。LIMIT子句用于给定一个限值，限制可以被更新的行的数目。

mysql> update student set name='Mary' where id=1;
Query OK, 0 rows affected (0.51 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> select * from student;
+----+------+-----+--------+
| id | name | sex | degree |
+----+------+-----+--------+
|  2 | Joan |   0 |  82.99 |
|  3 | Wang |   0 |  96.59 |
+----+------+-----+--------+
2 rows in set (0.06 sec)

mysql> update student set name='Mary' where id=2;
Query OK, 1 row affected (0.58 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+------+-----+--------+
| id | name | sex | degree |
+----+------+-----+--------+
|  2 | Mary |   0 |  82.99 |
|  3 | Wang |   0 |  96.59 |
+----+------+-----+--------+
2 rows in set (0.00 sec)

在已有的表中添加、修改或删除列

增加字段
格式：alter table table_name add field_name field_type 其他;

mysql> alter table student add passtest int(4) default '0';
Query OK, 0 rows affected, 1 warning (1.05 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> select * from student;
+----+------+-----+--------+----------+
| id | name | sex | degree | passtest |
+----+------+-----+--------+----------+
|  2 | Mary |   0 |  82.99 |        0 |
|  3 | Wang |   0 |  96.59 |        0 |
+----+------+-----+--------+----------+
2 rows in set (0.00 sec)

修改原字段名称及类型
格式：alter table table_name change old_field_name new_field_name field_type;
格式：alter table table_name modify field_name field_type;

mysql> alter table student change passtest past int(4) default '0';
Query OK, 0 rows affected, 1 warning (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> select * from student;
+----+------+-----+--------+------+
| id | name | sex | degree | past |
+----+------+-----+--------+------+
|  2 | Mary |   0 |  82.99 |    0 |
|  3 | Wang |   0 |  96.59 |    0 |
+----+------+-----+--------+------+
2 rows in set (0.00 sec)

mysql> alter table user modify passtest text;
Query OK, 2 rows affected (0.42 sec)
Records: 2  Duplicates: 0  Warnings: 0

删除字段
格式：alter table table_name drop field_name;
Or： alter table table_name drop column column_name;

mysql> alter table student drop past;
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+------+-----+--------+
| id | name | sex | degree |
+----+------+-----+--------+
|  2 | Mary |   0 |  82.99 |
|  3 | Wang |   0 |  96.59 |
+----+------+-----+--------+
2 rows in set (0.00 sec)

修改表名

格式：rename table 原表名 to 新表名;
Or： alter table t1 rename t2;

mysql> rename table student to user;
Query OK, 0 rows affected (0.41 sec)

mysql> select * from student;
ERROR 1146 (42S02): Table 'book.student' doesn't exist
mysql> select * from user;
+----+------+-----+--------+
| id | name | sex | degree |
+----+------+-----+--------+
|  2 | Mary |   0 |  82.99 |
|  3 | Wang |   0 |  96.59 |
+----+------+-----+--------+
2 rows in set (0.03 sec)

列出数据库中所有数据表

格式：show tables from db_name;

mysql> show tables from book;
+----------------+
| Tables_in_book |
+----------------+
| user           |
+----------------+
1 row in set (0.00 sec)

在不重启的情况下刷新用户权限

格式：flush privileges;

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

用户管理

查询用户

格式：
use mysql;
select * from user;

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> select user,plugin from user;
+------------------+-----------------------+
| user             | plugin                |
+------------------+-----------------------+
| mysql.infoschema | caching_sha2_password |
| mysql.session    | caching_sha2_password |
| mysql.sys        | caching_sha2_password |
| root             | caching_sha2_password |
+------------------+-----------------------+
4 rows in set (0.00 sec)

mysql> select * from user;
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| Host      | User             | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher             | x509_issuer              | x509_subject               | max_questions | max_updates | max_connections | max_user_connections | plugin                | authentication_string                                                  | password_expired | password_last_changed | password_lifetime | account_locked | Create_role_priv | Drop_role_priv | Password_reuse_history | Password_reuse_time | Password_require_current | User_attributes |
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| localhost | mysql.infoschema | Y           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          | 0x                     | 0x                       | 0x                         |             0 |           0 |               0 |                    0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N                | 2023-11-24 14:27:09   |              NULL | Y              | N                | N              |                   NULL |                NULL | NULL                     | NULL            |
| localhost | mysql.session    | N           | N           | N           | N           | N           | N         | N           | Y             | N            | N         | N          | N               | N          | N          | N            | Y          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          | 0x                     | 0x                       | 0x                         |             0 |           0 |               0 |                    0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N                | 2023-11-24 14:27:09   |              NULL | Y              | N                | N              |                   NULL |                NULL | NULL                     | NULL            |
| localhost | mysql.sys        | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          | 0x                     | 0x                       | 0x                         |             0 |           0 |               0 |                    0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N                | 2023-11-24 14:27:09   |              NULL | Y              | N                | N              |                   NULL |                NULL | NULL                     | NULL            |
| localhost | root             | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          | 0x                     | 0x                       | 0x                         |             0 |           0 |               0 |                    0 | caching_sha2_password | $A$005$_@x7@~{\^~C-pJw1waMsIZov9Dds2nf/SieyYIfqRDOcOW2HRAVMiNkU8Zs/ | N                | 2023-11-27 13:57:41   |              NULL | N              | Y                | Y              |                   NULL |                NULL | NULL                     | NULL            |
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
4 rows in set (0.00 sec)

创建用户

格式：
create user ‘用户名’@‘主机名’ identified by ‘密码’;
create user ‘用户名’@‘%’ identified by ‘密码’; – 任意主机都可以访问

mysql> create user 'test'@'localhost' identified by 'Test123';
Query OK, 0 rows affected (0.16 sec)

mysql> select user,plugin from user;
+------------------+-----------------------+
| user             | plugin                |
+------------------+-----------------------+
| mysql.infoschema | caching_sha2_password |
| mysql.session    | caching_sha2_password |
| mysql.sys        | caching_sha2_password |
| root             | caching_sha2_password |
| test             | caching_sha2_password |
+------------------+-----------------------+
5 rows in set (0.00 sec)

修改用户密码

格式：alter user ‘用户名’@‘主机名’ identified with mysql_native_password by ‘新密码’;

在 MySQL8 之前版本中加密规则是 mysql_native_password，而在 MySQL8 以后的加密规则为caching_sha2_password

mysql> select user,plugin from user;
+------------------+-----------------------+
| user             | plugin                |
+------------------+-----------------------+
| mysql.infoschema | caching_sha2_password |
| mysql.session    | caching_sha2_password |
| mysql.sys        | caching_sha2_password |
| root             | caching_sha2_password |
| test             | caching_sha2_password |
+------------------+-----------------------+
5 rows in set (0.00 sec)

mysql> alter user 'test'@'localhost' identified with mysql_native_password by 'Test1234';
Query OK, 0 rows affected (0.25 sec)

mysql> select user,plugin from user;
+------------------+-----------------------+
| user             | plugin                |
+------------------+-----------------------+
| mysql.infoschema | caching_sha2_password |
| mysql.session    | caching_sha2_password |
| mysql.sys        | caching_sha2_password |
| root             | caching_sha2_password |
| test             | mysql_native_password |
+------------------+-----------------------+
5 rows in set (0.00 sec)

删除用户

格式：drop user ‘用户名’@‘主机名’;

mysql> show grants for 'test'@'localhost';
+------------------------------------------+
| Grants for test@localhost                |
+------------------------------------------+
| GRANT USAGE ON *.* TO `test`@`localhost` |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> drop user 'test'@'localhost';
Query OK, 0 rows affected (0.06 sec)

mysql> show grants for 'test'@'localhost';
ERROR 1141 (42000): There is no such grant defined for user 'test' on host 'localhost'

控制权限

查询权限

格式：show grants for ‘用户名’@‘主机名’;

mysql> show grants for 'test'@'localhost';
+------------------------------------------+
| Grants for test@localhost                |
+------------------------------------------+
| GRANT USAGE ON *.* TO `test`@`localhost` |
+------------------------------------------+
1 row in set (0.09 sec)

授予权限

格式：grant 权限列表 on 数据库.表名 to ‘用户名’@‘主机名’;

常用权限

    all，all privileges：所有权限 

    select：查询数据

    insert：插入数据

    update：修改数据

    delete：删除数据

    alter：修改表

    drop：删除数据库、表、视图

    create：创建数据库、表

mysql> show grants for 'test'@'localhost';
+------------------------------------------+
| Grants for test@localhost                |
+------------------------------------------+
| GRANT USAGE ON *.* TO `test`@`localhost` |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> grant select,insert,update,delete on *.* to 'test'@'localhost';
Query OK, 0 rows affected (0.07 sec)

mysql> show grants for 'test'@'localhost';
+-------------------------------------------------------------------+
| Grants for test@localhost                                         |
+-------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `test`@`localhost` |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)

撤销权限

格式：revoke 权限列表 on 数据库.表名 from ‘用户名’@‘主机名’;

mysql> show grants for 'test'@'localhost';
+-------------------------------------------------------------------+
| Grants for test@localhost                                         |
+-------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `test`@`localhost` |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> revoke all privileges on *.* from 'test'@'localhost';
Query OK, 0 rows affected (0.08 sec)

mysql> show grants for 'test'@'localhost';
+------------------------------------------+
| Grants for test@localhost                |
+------------------------------------------+
| GRANT USAGE ON *.* TO `test`@`localhost` |
+------------------------------------------+
1 row in set (0.00 sec)

连接 MySQL，实现对数据库的增删改查

使用 npm 从命令行安装 mysql

命令：npm i mysql

% npm i mysql

added 9 packages, and audited 140 packages in 7s

12 packages are looking for funding
  run `npm fund` for details

7 vulnerabilities (2 low, 5 high)

To address issues that do not require attention, run:
  npm audit fix

To address all issues, run:
  npm audit fix --force

Run `npm audit` for details.

连接 MySQL

在项目中新建一个文件夹 plugins，文件夹下新建一个文件 db.js

/plugins/db.js


// 1. 导入 mysql 模块
import mysql from 'mysql'

// 2. 建立与 MySQL 数据库的连接 
const db = mysql.createPool({

    host: '127.0.0.1',// 数据库的 IP 
    user: 'root',// 登陆数据库的账号
    password: 'h23weTw7z',// 登陆数据库的密码
    database: 'book'// 连接的数据库
})

// 检测 mysql 模块能否正常工作
db.query('SELECT 1', (err, results) => {
    // connect ECONNREFUSED 127.0.0.1:3306
    // ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client
    if(err) return console.log(err.message)
    // [ RowDataPacket { '1': 1 } ] 代表数据库连接正常
    console.log(results)

})

export default db

在 app.js 里导入

import db from './plugins/db.js'

connect ECONNREFUSED 127.0.0.1:3306

无法连接到 MySQL 服务，检查 MySQL 服务是否正在运行，检查端口是否正确映射

ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client

在 mysql 8.0 之前版本中加密规则是 mysql_native_password，而在 mysql 8.0 以后的加密规则为caching_sha2_password，mysql 8.0 以上加密方式，Node 还不支持。

修改加密规则为普通模式，默认是严格加密模式：

进入到 mysql 的命令行(输入 mysql -u root -p，输入密码)中，
然后运行以下代码： ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘password’; // 'password’是你的数据库密码
输入刚刚修改的密码，再次测试连接
最后输入 flush privileges; 刷新权限（不输入也可以）

% mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 38
Server version: 8.2.0 MySQL Community Server - GPL

Copyright (c) 2000, 2023, 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> alter user 'root'@'localhost' identified with mysql_native_password by 'h23weTw7z';
Query OK, 0 rows affected (0.20 sec)

ER_ACCESS_DENIED_ERROR: Access denied for user ‘test’@‘localhost’(using password: YES)

检查用户名和密码是否正确

ER_DBACCESS_DENIED_ERROR: Access denied for user ‘test’@‘localhost’ to database ‘book’

检查用户权限

mysql> show grants for 'test'@'localhost';
+------------------------------------------+
| Grants for test@localhost                |
+------------------------------------------+
| GRANT USAGE ON *.* TO `test`@`localhost` |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> grant select,insert,update,delete on *.* to 'test'@'localhost';
Query OK, 0 rows affected (0.07 sec)

mysql> show grants for 'test'@'localhost';
+-------------------------------------------------------------------+
| Grants for test@localhost                                         |
+-------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `test`@`localhost` |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)

[ RowDataPacket { ‘1’: 1 } ]

数据库连接正常

增删改查

使用数据库操作

定义 sql 语句
使用 db.query() 方法执行 sql 语句，第一个参数放 sql 语句
箭头函数里，第一个参数是执行失败的信息，第二个参数是执行成功的结果
如果 err 存在，sql 语句执行失败
err 不存在，result 会拿到数据库中的信息

查

// 定义sql语句 查
const sql = "select * from student";
// 执行sql语句
db.query(sql, (err, result) => {
  // 执行失败
  if (err) {
    console.log(err)
  }
  console.log(result)
});

增

// 定义sql语句, 要添加的内容用?表示 增
const sql = "insert into student set ?"
// 执行sql语句, 第二个参数代表sql语句中？的值
db.query(sql, { id : 19, name : 'Qwert', degree : 98.1 }, (err,results)=>{
  // sql语句执行失败
  if(err) {
    
    console.log(err.message)
  }
  // 数据库语句执行成功，但影响的条数不等于1，没有增加，也属于失败
  if(results.affectedRows !== 1) {
    console.log('数据添加失败')
  }
  // sql语句执行成功，影响条数也等于1
  console.log('添加成功 - ' + results)
})

改

// 修改语句，将 student 表中id为 ? 的 degree 改为 ? ，两个问号的值从前端获取
const sql = "update student set degree=? where id=?";
// 执行sql语句
db.query(sql, [90, 19], (err, results) => {
  // sql语句执行失败
  if (err) {
    console.log(err.message)
  }
  // sql语句执行成功，但影响的条数不等于1，没有修改，也属于失败
  if (results.affectedRows !== 1) {
    console.log('数据修改失败')
  }
  // 执行成功
  console.log('修改成功 -- ' + results)
});

删

// 删
// 定义sql语句，从 student 表中将 id 为 ? 的那条数据删除
 const sql = "delete from student where id=?";
 db.query(sql, 19, (err, results) => {
   // sql语句执行失败
   if (err) {
      console.log(err.message)
   }
   // sql语句执行成功，但影响的条数不等于1，没有删除，也属于失败
   if (results.affectedRows !== 1) {
      console.log('删除失败')
   }
   // 执行成功
   console.log('删除成功 - ' + results)

 });