MySQL学习笔记（一）

数据库学习

一、数据库基础知识

1.1 查看数据库版本号：

C:\Users\Crush>mysql --version
mysql  Ver 8.0.26 for Win64 on x86_64 (MySQL Community Server - GPL)

1.2 MySQL的登录

1.2.1 登录方式1：MySQL自带客户端

开始菜单 → 所有程序 → MySQL → MySQL 8.0 Command Line Client

说明：仅限于root用户

1.2.2 登录方式2：windows命令行

• 格式：

mysql -h 主机名 -P 端口号 -u 用户名 -p密码

• 举例：

mysql -h localhost -P 3306 -u root -pabc123  # 这里我设置的root用户的密码是abc123

注意：

（1）-p与密码之间不能有空格，其他参数名与参数值之间可以有空格也可以没有空格。如：

mysql -hlocalhost -P3306 -uroot -pabc123

（2）密码建议在下一行输入，保证安全

mysql -h localhost -P 3306 -u root -p
Enter password:****

（3）客户端和服务器在同一台机器上，所以输入localhost或者IP地址127.0.0.1。同时，因为是连接本机：
-hlocalhost就可以省略，如果端口号没有修改：-P3306也可以省略

简写成：

mysql -u root -p
Enter password:****

连接成功后，有关于MySQL Server服务版本的信息，还有第几次连接的id标识。

也可以在命令行通过以下方式获取MySQL Server服务版本的信息：

c:\> mysql -V

c:\> mysql --version

或登录后，通过以下方式查看当前版本信息：

mysql> select version();

1.2.3 退出登录

exit
或
quit

1.2.4 启动数据库8.0并退出

C:\Users\Crush>mysql -uroot -p123456 -hlocalhost -P3307
mysql: [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 11
Server version: 8.0.26 MySQL Community Server - GPL

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> quit
Bye

1.2.5 启动数据库5.7并退出

C:\Users\Crush>mysql -uroot -p123456 -hlocalhost -P3306
mysql: [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 239
Server version: 5.7.34-log MySQL Community Server (GPL)

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> quit
Bye

1.3 建议启动数据库的安全方式

C:\Users\Crush>mysql -u root -p -hlocalhost -P3307
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.26 MySQL Community Server - GPL

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> quit
Bye

1.4 查看字符集

mysql> show variables like 'character_%';
+--------------------------+---------------------------------------------------------------+
| Variable_name            | Value                                                         |
+--------------------------+---------------------------------------------------------------+
| character_set_client     | gbk                                                           |
| character_set_connection | gbk                                                           |
| character_set_database   | utf8mb4                                                       |
| character_set_filesystem | binary                                                        |
| character_set_results    | gbk                                                           |
| character_set_server     | utf8mb4                                                       |
| character_set_system     | utf8mb3                                                       |
| character_sets_dir       | D:\SoftProject\MySQL\MySQL Server 8.0\install\share\charsets\ |
+--------------------------+---------------------------------------------------------------+
8 rows in set, 1 warning (0.18 sec)

1.5 MySQL的编码设置

1.5.1 MySQL5.7中

问题再现：命令行操作sql乱码问题

mysql> INSERT INTO t_stu VALUES(1,'张三','男');
ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5\xC8\xFD' for column 'sname' at row 1

问题解决

步骤1：查看编码命令

show variables like 'character_%';
show variables like 'collation_%';

步骤2：修改mysql的数据目录下的my.ini配置文件

[mysql]  #大概在63行左右，在其下添加
... 
default-character-set=utf8  #默认字符集

[mysqld]  # 大概在76行左右，在其下添加
...
character-set-server=utf8
collation-server=utf8_general_ci

注意：建议修改配置文件使用notepad++等高级文本编辑器，使用记事本等软件打开修改后可能会导致文件编码修改为“含BOM头”的编码，从而服务重启失败。

步骤3：重启服务

步骤4：查看编码命令

show variables like 'character_%';
show variables like 'collation_%';

1.5.2 MySQL8.0中

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

二、数据库操作

2.1 显示所有数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.13 sec)

2.2 创建数据库并显示所有

mysql> create database test01;
Query OK, 1 row affected (0.14 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test01             |
+--------------------+
5 rows in set (0.03 sec)

2.3 使用数据库，创建表，显示表

mysql> use test01;
Database changed

mysql> create table employee(id int primary key auto_increment,name varchar(15));
Query OK, 0 rows affected (1.54 sec)

mysql> show tables;
+------------------+
| Tables_in_test01 |
+------------------+
| employee         |
+------------------+
1 row in set (0.09 sec)

2.4 显示表结构

mysql> show create table employee;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                 |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employee | CREATE TABLE `employee` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1004 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

2.5 插入语句并查询数据

mysql> insert into employee values(1001,'Tom');
Query OK, 1 row affected (0.08 sec)

mysql> insert into employee values(1002,'Jane');
Query OK, 1 row affected (0.10 sec)

mysql> insert into employee values(1003,'Mark');
Query OK, 1 row affected (0.09 sec)

mysql> select * from employee;
+------+------+
| id   | name |
+------+------+
| 1001 | Tom  |
| 1002 | Jane |
| 1003 | Mark |
+------+------+
3 rows in set (0.01 sec)

2.6 显示所有表

mysql> show tables;
+------------------+
| Tables_in_test01 |
+------------------+
| employee         |
+------------------+
1 row in set (0.00 sec)

mysql>

2.7 删除表

mysql> drop table employee;
Query OK, 0 rows affected (1.56 sec)

mysql> show tables;
Empty set (0.00 sec)

2.8 显示所有数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test01             |
+--------------------+
5 rows in set (0.00 sec)

2.9 删除库

mysql> drop database test01;
Query OK, 1 row affected (0.68 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.03 sec)

三、Navicat连接MySQL8.0,出现错误

2059 - Authentication plugin 'caching_sha2_password' cannot be loaded: +X   9g頫

原因：

上面的选项是MySQL8.0提供的新的授权方式，采用SHA256基础的密码加密方法；下面的选项是传统授权方法（保留5.x版本兼容性）。

解决问题：

出现这个原因是MySQL8之前的版本中加密规则是mysql_native_password，而在MySQL8之后，加密规则是caching_sha2_password。解决问题方法有两种，第一种是升级图形界面工具版本，第二种是把MySQL8用户登录密码加密规则还原成mysql_native_password。

第二种解决方案如下，用命令行登录MySQL数据库之后，执行如下命令修改用户密码加密规则并更新用户密码，这里修改用户名为“root@localhost”的用户密码规则为“mysql_native_password”，密码值为“123456”，如图所示。

#登录MySQL8.0版本
 	mysql -h 主机名 -P 端口号 -u 用户名 -p密码
 	mysql -h localhost -P 3306 -u root -p123456  # 这里我设置的root用户的密码是123456
#安全
 	mysql -h localhost -P 3306 -u root -p
 	Enter password:****

#使用mysql数据库
	USE mysql; 

#修改加密规则 密码123456
	ALTER USER 'root'@'localhost' IDENTIFIED BY '密码' PASSWORD EXPIRE NEVER;

#修改'root'@'localhost'用户的密码规则和密码
	ALTER USER 'root'@'localhost' IDENTIFIED WITH 	mysql_native_password BY '密码'; 

#刷新权限
	FLUSH PRIVILEGES;

#重置密码
	alter user 'root'@'localhost' identified by '新密码';