原文链接: https://anran758.github.io/blog/2022/05/16/mysql-note/
由于自考的实践要求有需要用到 mysql 进行考核,故记录一下在 mac 环境下试手的笔记。
初始环境
首先在 mysql 官网中下载你想要的版本。可以直接下载 dmg 安装包,按照安装指示一步一步安装,并设置 mysql 的密码。
下载完毕后,一般情况下直接通过命令行使用 mysql
命令会找不到对应的命令:
1 2
|
➜ ~ mysql -v zsh: command not found: mysql
|
因此需要对当前的命令行工具配置对应的环境变量,比如笔者使用的是 zsh
,则打开 ~/.zshrc
文件添加以下配置:
1
|
export PATH=${PATH}:/usr/local/mysql/bin/
|
若使用 bash
的用户同理,直接在 ~/.bashrc
添加相同代码。添加完毕后通过 source
命令重新加载对应的环境变量: source ~/.zshrc
接着就可以在命令行直接使用 mysql
了。输入 mysql -u root -p
登录 mysql,密码是在安装阶段时设置的密码。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
|
➜ ~ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 13 Server version: 8.0.29 MySQL Community Server - GPL
Copyright (c) 2000, 2022, 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>
|
数据库操作
DATABASE 可以不区分大小写,但只能要么全小写,要么全大写。一般会将这些参数用大写写出。
创建数据库
1 2 3
|
-- 还可以通过 DEFAULT CHARACTER SET 选项设置默认的编码集 mysql> CREATE DATABASE DANNY_DATABASE; Query OK, 1 row affected (0.01 sec)
|
查看现有的数据库
1 2 3 4 5 6 7 8 9 10 11
|
mysql> SHOW DATABASES; +----------------------------+ | Database | +----------------------------+ | information_schema | | DANNY_DATABASE | | mysql | | performance_schema | | sys | +----------------------------+ 6 rows in set (0.00 sec)
|
切换到指定数据库
1
|
mysql> USE DANNY_DATABASE
|
数据库的查看与删除
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32
|
-- 创建数据库: 准备稍后移除的数据库 mysql> CREATE DATABASE DANNY_DATABASE_WAIT_DELETE; Query OK, 1 row affected (0.01 sec)
mysql> SHOW DATABASES; +----------------------------+ | Database | +----------------------------+ | information_schema | | DANNY_DATABASE | | DANNY_DATABASE_WAIT_DELETE | | mysql | | performance_schema | | sys | +----------------------------+ 6 rows in set (0.00 sec)
-- 删除数据库 mysql> DROP DATABASE DANNY_DATABASE_WAIT_DELETE; Query OK, 0 rows affected (0.02 sec)
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | DANNY_DATABASE | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec)
|
查看当前使用的数据库
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
|
-- 未选择的情况下 mysql> SELECT DATABASE(); +----------------+ | DATABASE() | +----------------+ | null | +----------------+ 1 row in set (0.00 sec)
-- 切换指定数据库 use DANNY_DATABASE;
mysql> SELECT DATABASE(); +----------------+ | DATABASE() | +----------------+ | danny_database | +----------------+ 1 row in set (0.00 sec)
|
数据表操作
创建数据表
1 2 3 4 5 6 7 8 9 10
|
-- 创建名为 customers 的数据表 mysql> CREATE TABLE IF NOT EXISTS customers( -> cust_id INT NOT NULL AUTO_INCREMENT, -> cust_name CHAR(50) NOT NULL, -> cust_sex CHAR(1) NOT NULL DEFAULT 0, -> cust_address CHAR(50) NULL, -> cust_contact CHAR(50) NULL, -> PRIMARY KEY(cust_id) -> ); Query OK, 0 rows affected (0.11 sec)
|
其中 IF NOT EXISTS
参数是可选的,它的意思为若 customers 表不存在则创建它。
查看数据表与表列
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
|
-- 查看当前用户在当前数据库中可以访问的数据表 mysql> SHOW TABLES; +--------------------------+ | Tables_in_danny_database | +--------------------------+ | customers | +--------------------------+ 1 rows in set (0.00 sec)
-- 查看指定数据表中列的信息 -- DESC customers; 等价于如下命令 mysql> SHOW COLUMNS from customers; +--------------+-------------+------+-----+-----------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+-----------+----------------+ | cust_id | int(11) | NO | PRI | NULL | auto_increment | | cust_name | char(50) | NO | | NULL | | | cust_sex | char(1) | NO | | 0 | | | cust_address | char(50) | YES | | NULL | | | cust_contact | char(50) | YES | | NULL | | +--------------+-------------+------+-----+-----------+----------------+ 5 rows in set (0.00 sec)
|
删除数据表
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32
|
-- 添加一个数据表用于演示删除 mysql> CREATE TABLE IF NOT EXISTS customers_1( -> cust_id INT NOT NULL AUTO_INCREMENT, -> cust_name CHAR(50) NOT NULL, -> cust_sex CHAR(1) NOT NULL DEFAULT 0, -> cust_address CHAR(50) NULL, -> cust_contact CHAR(50) NULL, -> PRIMARY KEY(cust_id) -> ); Query OK, 0 rows affected (0.11 sec)
-- 查看当前的数据表 mysql> SHOW tables; +--------------------------+ | Tables_in_danny_database | +--------------------------+ | customers | | customers_1 | +--------------------------+ 2 rows in set (0.00 sec)
-- 删除指定数据表 mysql> DROP TABLES customers_1; Query OK, 0 rows affected (0.02 sec)
mysql> SHOW tables; +--------------------------+ | Tables_in_danny_database | +--------------------------+ | customers | +--------------------------+ 1 row in set (0.00 sec)
|
数据表添加新列
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
|
-- 插入新列 mysql> alter TABLE customers -> ADD COLUMN cust_city char(10) NOT NULL DEFAULT 'guangzhou' AFTER cust_sex; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0
-- 确认表列状态 mysql> SHOW COLUMNS from customers; +--------------+-------------+------+-----+-----------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+-----------+----------------+ | cust_id | int(11) | NO | PRI | NULL | auto_increment | | cust_name | char(50) | NO | | NULL | | | cust_sex | char(1) | NO | | 0 | | | cust_city | char(10) | NO | | guangzhou | | | cust_address | char(50) | YES | | NULL | | | cust_contact | char(50) | YES | | NULL | | +--------------+-------------+------+-----+-----------+----------------+ 6 rows in set (0.00 sec)
|
数据表修改表列
修改整列: 将列名 cust_sex 修改 sex,并修改默认值
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
|
mysql> alter TABLE customers -> CHANGE COLUMN cust_sex sex char(1) NULL DEFAULT 'M'; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW COLUMNS from customers; +--------------+-------------+------+-----+-----------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+-----------+----------------+ | cust_id | int(11) | NO | PRI | NULL | auto_increment | | cust_name | char(50) | YES | | NULL | | | sex | char(1) | YES | | M | | | cust_city | char(10) | NO | | guangzhou | | | cust_address | char(50) | YES | | NULL | | | cust_contact | char(50) | YES | | NULL | | +--------------+-------------+------+-----+-----------+----------------+ 6 rows in set (0.00 sec)
|
仅修改列的类型
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
|
mysql> ALTER TABLE customers -> MODIFY COLUMN cust_address varchar(50); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0
mysql> show COLUMNS from customers; +--------------+-------------+------+-----+-----------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+-----------+----------------+ | cust_id | int(11) | NO | PRI | NULL | auto_increment | | cust_name | char(50) | YES | | NULL | | | sex | char(1) | YES | | M | | | cust_city | char(10) | NO | | guangzhou | | | cust_address | varchar(50) | YES | | NULL | | | cust_contact | char(50) | YES | | NULL | | +--------------+-------------+------+-----+-----------+----------------+ 6 rows in set (0.00 sec)
|
修改指定列的指定字段
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
|
mysql> ALTER TABLE customers -> ALTER COLUMN cust_city SET DEFAULT 'shenzhen'; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW COLUMNS from customers; +--------------+-------------+------+-----+----------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+----------+----------------+ | cust_id | int(11) | NO | PRI | NULL | auto_increment | | cust_name | char(50) | YES | | NULL | | | sex | char(1) | YES | | M | | | cust_city | char(10) | NO | | shenzhen | | | cust_address | varchar(50) | YES | | NULL | | | cust_contact | char(50) | YES | | NULL | | +--------------+-------------+------+-----+----------+----------------+ 6 rows in set (0.00 sec)
|
移除数据表列: 移除 cust_contact 数据表项
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
|
mysql> ALTER TABLE danny_database.customers -> DROP COLUMN cust_contact; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW COLUMNS from customers; +--------------+-------------+------+-----+----------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+----------+----------------+ | cust_id | int(11) | NO | PRI | NULL | auto_increment | | cust_name | char(50) | YES | | NULL | | | sex | char(1) | YES | | M | | | cust_city | char(10) | NO | | shenzhen | | | cust_address | varchar(50) | YES | | NULL | | +--------------+-------------+------+-----+----------+----------------+ 5 rows in set (0.00 sec)
|
数据项操作
添加数据
默认情况下在命令行中 mysql 是不能直接插入中文的,这个跟字符集有关。可输入下面命令修改数据库或表的字符集:
1 2 3 4 5 6
|
-- 设置名为 danny_database 的数据库字符集 ALTER DATABASE danny_database character SET utf8;
-- 设置名为 customers 的数据库表字符集 (Tip: 若数据库已经被设置为 utf8, 则无需再设置表的字符集) ALTER TABLE customers convert to character SET utf8;
|
为数据表插入数据,显式设置字段
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
|
mysql> INSERT INTO danny_database.customers(cust_id, cust_name, sex, cust_address) -> VALUES(901, '张三', DEFAULT, '广州市'); Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO danny_database.customers(cust_id, cust_name, sex, cust_address) -> VALUES(0, '李四', DEFAULT, '广州市'); Query OK, 1 row affected (0.01 sec)
mysql> select * from customers; +---------+-----------+------+-----------+--------------+ | cust_id | cust_name | sex | cust_city | cust_address | +---------+-----------+------+-----------+--------------+ | 901 | 张三 | M | shenzhen | 广州市 | | 902 | 李四 | M | shenzhen | 广州市 | +---------+-----------+------+-----------+--------------+ 2 rows in set (0.00 sec)
|
由于 cust_id 是自增的,因此可以将此字段的值设置为 0 或 NULL 会自动自增。上例 “李四” 的 cust_id 在创建后就被自增为 902。
还可以通过 SET
语句设置部分值:
1 2
|
mysql> INSERT INTO danny_database.customers SET cust_name='王五', cust_address='武汉市', sex=DEFAULT; Query OK, 1 row affected (0.00 sec)
|
查询数据
可通过 SELECT
语句查询数据:
1 2 3 4 5 6 7 8 9
|
mysql> SELECT * FROM customers; +---------+-----------+------+-----------+--------------+ | cust_id | cust_name | sex | cust_city | cust_address | +---------+-----------+------+-----------+--------------+ | 901 | 张三 | M | shenzhen | 广州市 | | 902 | 李四 | M | shenzhen | 广州市 | | 903 | 王五 | M | shenzhen | 武汉市 | +---------+-----------+------+-----------+--------------+ 3 rows in set (0.00 sec)
|
仅展示指定字段:
1 2 3 4 5 6 7 8
|
+---------+-----------+------+ | cust_id | cust_name | sex | +---------+-----------+------+ | 901 | 张三 | M | | 902 | 李四 | M | | 903 | 王五 | M | +---------+-----------+------+ 3 rows in set (0.00 sec)
|
通过 WHERE
子句设置查询条件,筛选出符合查询条件的数据:
1 2 3 4 5 6 7 8 9
|
mysql> SELECT cust_id,cust_name,cust_address FROM customers -> WHERE cust_address="广州市"; +---------+-----------+--------------+ | cust_id | cust_name | cust_address | +---------+-----------+--------------+ | 901 | 张三 | 广州市 | | 902 | 李四 | 广州市 | +---------+-----------+--------------+ 2 rows in set (0.00 sec)
|
删除数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
|
-- 添加几项测试数据 mysql> INSERT INTO danny_database.customers(cust_id, cust_name, sex, cust_address) -> VALUES(1, 'test1', DEFAULT, '深圳市'); Query OK, 1 row affected (0.02 sec)
mysql> select * from customers; +---------+-----------+------+-----------+--------------+ | cust_id | cust_name | sex | cust_city | cust_address | +---------+-----------+------+-----------+--------------+ | 1 | test1 | M | shenzhen | 深圳市 | | 901 | 张三 | M | shenzhen | 广州市 | | 902 | 李四 | M | shenzhen | 广州市 | | 903 | 王五 | M | shenzhen | 武汉市 | +---------+-----------+------+-----------+--------------+ 4 rows in set (0.00 sec)
-- 删除表数据 mysql> DELETE FROM customers -> WHERE cust_id=1; Query OK, 1 row affected (0.02 sec)
mysql> select * from customers; +---------+-----------+------+-----------+--------------+ | cust_id | cust_name | sex | cust_city | cust_address | +---------+-----------+------+-----------+--------------+ | 901 | 张三 | M | shenzhen | 广州市 | | 902 | 李四 | M | shenzhen | 广州市 | | 903 | 王五 | M | shenzhen | 武汉市 | +---------+-----------+------+-----------+--------------+
|
更新数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14
|
-- 更新数据 mysql> UPDATE customers SET cust_address="深圳市" WHERE cust_name="李四"; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM customers; +---------+-----------+------+-----------+--------------+ | cust_id | cust_name | sex | cust_city | cust_address | +---------+-----------+------+-----------+--------------+ | 901 | 张三 | M | shenzhen | 广州市 | | 902 | 李四 | M | shenzhen | 深圳市 | | 903 | 王五 | M | shenzhen | 武汉市 | +---------+-----------+------+-----------+--------------+ 3 rows in set (0.00 sec)
|
实践
以一个 eShop 的需求为例做个简单的测试吧。
创建 eshop 数据库
在 MySQL 中创建一个名为 eshop 的数据库,选择字符集为 utf8mb4
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
|
mysql> CREATE DATABASE IF NOT EXISTS eshop DEFAULT CHARACTER SET utf8mb4; Query OK, 1 row affected (0.01 sec)
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | DANNY_DATABASE | | eshop | | mysql | | performance_schema | | sys | +--------------------+ 6 rows in set (0.01 sec)
-- 切换数据库 mysql> use eshop; Database changed
|
创建数据表及相关记录
相关表信息如下
表名:用户(t_user)
字段名 |
类型 |
大小 |
用户ID (id) |
自增类型 |
|
姓名 (user_name) |
文本 |
50,非空 |
联系电话 (phone_no) |
文本 |
20,非空 |
表名:商品(product)
字段名 |
类型 |
大小 |
商品ID(id) |
自增类型 |
|
商品名称(product_name) |
文本 |
50,非空 |
价格(price) |
数值类型 |
(整数位9位,小数位2位),非空 |
表名:购物车 (shopping_cart)
字段名 |
类型 |
大小 |
用户id(user_id) |
整数 |
非空,主键,参考用户表主键 |
商品id(product_id) |
整数 |
非空,主键,参考商品表主键 |
商品数量(quantity) |
整数 |
非空 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35
|
-- 用户表 mysql> CREATE TABLE IF NOT EXISTS t_user( -> `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> `user_name` CHAR(50) NOT NULL, -> `phone_no` CHAR(20) NOT NULL -> ); Query OK, 0 rows affected (0.06 sec)
-- 商品表 mysql> CREATE TABLE IF NOT EXISTS product( -> `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> `product_name` CHAR(50) NOT NULL, -> `price` DOUBLE(9, 2) -> ); Query OK, 0 rows affected (0.06 sec)
-- 购物车 mysql> CREATE TABLE IF NOT EXISTS shopping_cart( -> `user_id` INT NOT NULL, -> `product_id` INT NOT NULL, -> `quantity` INT NOT NULL, -> PRIMARY KEY(`user_id`, `product_id`) -> ); Query OK, 0 rows affected (0.05 sec)
-- 查看数据表 mysql> show tables; +-----------------+ | Tables_in_eshop | +-----------------+ | product | | shopping_cart | | t_user | +-----------------+ 3 rows in set (0.00 sec)
|
录入用户数据
用户信息
1 2 3 4
|
1;张三; 13333333333; 2;李四; 13666666666 3;王五; 13888888888 4;赵六; 13999999999
|
商品信息
1 2 3
|
1; C++程序设计教程; 45.5 2; 数据结构; 33.7 3; 操作系统; 51
|
购物车
1 2 3 4
|
1; 1; 5 1; 2; 3 2; 3; 6 2; 4; 8
|
录入数据:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63
|
-- 插入用户表数据 mysql> INSERT INTO t_user -> (id, user_name, phone_no) -> VALUES -> (1, '张三', '13333333333'), -> (2, '李四', '13666666666'), -> (3, '王五', '13888888888'), -> (4, '赵六', '13999999999'); Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM t_user; +----+-----------+-------------+ | id | user_name | phone_no | +----+-----------+-------------+ | 1 | 张三 | 13333333333 | | 2 | 李四 | 13666666666 | | 3 | 王五 | 13888888888 | | 4 | 赵六 | 13999999999 | +----+-----------+-------------+ 4 rows in set (0.00 sec)
-- 插入「商品信息」 mysql> INSERT INTO product -> (id, product_name, price) -> VALUES -> (1, 'C++程序设计教程', 45.5), -> (2, '数据结构', 33.7), -> (3, '操作系统', 51); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM product; +----+-----------------------+-------+ | id | product_name | price | +----+-----------------------+-------+ | 1 | C++程序设计教程 | 45.50 | | 2 | 数据结构 | 33.70 | | 3 | 操作系统 | 51.00 | +----+-----------------------+-------+ 3 rows in set (0.00 sec)
-- 插入购物车 mysql> INSERT INTO shopping_cart -> (user_id, product_id, quantity) -> VALUES -> (1, 1, 5), -> (1, 2, 3), -> (2, 3, 6), -> (2, 4, 8); Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM shopping_cart; +---------+------------+----------+ | user_id | product_id | quantity | +---------+------------+----------+ | 1 | 1 | 5 | | 1 | 2 | 3 | | 2 | 3 | 6 | | 2 | 4 | 8 | +---------+------------+----------+ 4 rows in set (0.00 sec)
|
数据的查询与更新
使用 SQL 语句列出「张三」购买商品清单信息,以购买数量升序排列:
1 2 3 4 5 6 7 8 9 10 11
|
mysql> SELECT u.user_name, p.product_name, u.phone_no, p.price, s.quantity FROM t_user u, product p, shopping_cart s -> WHERE u.user_name="张三" AND u.id = s.user_id AND p.id = s.product_id -> ORDER BY quantity asc -> LIMIT 100; +-----------+-----------------------+-------------+-------+----------+ | user_name | product_name | phone_no | price | quantity | +-----------+-----------------------+-------------+-------+----------+ | 张三 | 数据结构 | 13333333333 | 33.70 | 3 | | 张三 | C++程序设计教程 | 13333333333 | 45.50 | 5 | +-----------+-----------------------+-------------+-------+----------+ 2 rows in set (0.01 sec)
|
使用 SQL 语句选出李四购买商品的总价:
1 2 3 4 5 6 7 8 9
|
mysql> SELECT u.user_name, p.product_name, p.price, s.quantity, p.price*s.quantity AS total_price FROM t_user u, product p, shopping_cart s -> WHERE u.user_name="李四" AND u.id = s.user_id AND p.id = s.product_id -> LIMIT 100; +-----------+--------------+-------+----------+-------------+ | user_name | product_name | price | quantity | total_price | +-----------+--------------+-------+----------+-------------+ | 李四 | 操作系统 | 51.00 | 6 | 306.00 | +-----------+--------------+-------+----------+-------------+ 1 row in set (0.00 sec)
|
使用 SQL 语句列出购买数量排前两位的商品名称:
1 2 3 4 5 6 7 8 9 10 11
|
mysql> SELECT p.product_name, p.price, s.quantity FROM product p, shopping_cart s -> WHERE p.id = s.product_id -> ORDER BY quantity desc -> LIMIT 2; +-----------------------+-------+----------+ | product_name | price | quantity | +-----------------------+-------+----------+ | 操作系统 | 51.00 | 6 | | C++程序设计教程 | 45.50 | 5 | +-----------------------+-------+----------+ 2 rows in set (0.00 sec)
|
忘记密码
若忘记数据库密码后可通过 mysqld_safe
来修改密码:
-
在系统偏好设置中关闭 mysql 服务
-
打开终端,输入命令:
1 2
|
➜ ~ cd /usr/local/mysql/bin ➜ ~ sudo su
|
-
命令行变成以 sh-3.2#
开头后继续输入命令:
1 2 3 4
|
sh-3.2# ./mysqld_safe --skip-grant-tables &
mysqld_safe Logging to '/usr/local/mysql/data/DannydeMBP.err'. mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
|
-
新开个命令行窗口,进入 mysql
:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
|
➜ ~ /usr/local/mysql/bin/mysql
Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 30 Server version: 5.7.31
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
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> 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
|
-
更新密码
1 2 3 4
|
mysql> update user set authentication_string=password('admin') where Host='localhost' and User='root';
Query OK, 1 row affected, 1 warning (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 1
|
-
输入 exit
命令退出 mysql
,查出 mysqld_safe
进程号并杀掉:
1 2 3 4 5 6 7 8 9 10
|
mysql> exit Bye
➜ ~ ps -ax | grep mysql 8553 ttys004 0:00.03 /bin/sh ./mysqld_safe --skip-grant-tables 8623 ttys004 0:00.92 /usr/local/mysql-5.7.31-macos10.14-x86_64/bin/mysqld --basedir=/usr/local/mysql-5.7.31-macos10.14-x86_64 --datadir=/usr/local/mysql-5.7.31-macos10.14-x86_64/data --plugin-dir=/usr/local/mysql-5.7.31-macos10.14-x86_64/lib/plugin --user=mysql --skip-grant-tables --log-error=host-3-187.can.danny1.network.err --pid-file=host-3-187.can.danny1.network.pid
# 杀掉 mysql 的进程 ➜ ~ kill -9 8553 ➜ ~ kill -9 8623
|
-
此时返回系统偏好设置中看到 mysql 被关闭后就算正确退出了。接着继续输入 mysql -u root -p
命令连接数据库,再输入刚才修改的密码即可。
参考资料
本文转自: https://anran758.github.io/blog/2022/05/16/mysql-note/
本站仅做收录,版权归原作者所有。
Post Views: 97