Continuous Learning & Continuous Updating...

Learning Attitude: Keep away

Basic Commands

If you are prompted for Denial of Service when you enter a command, open the command prompt interface as a system administrator and try again

Start MySQL Service Command

net start MySQL service name

Stop MySQL Service Command

net stop MySQL service name

Log in to MySQL

mysql -h host name -P Port number -u User name -p Password

Give an example

mysql -h localhost -P 3306 -u root -pabc123  # The password for the root user I set here is abc123

Be careful

• Passwords are recommended on the next line for security

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

• Client and server are on the same machine, so enter localhost or IP address 127.0.0.1. At the same time, because it is connected to the local machine:
  -hlocalhost can be omitted if the port number has not been modified: -P3306 can also be omitted

Short form:

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

Log out

exit

or

quit

View MySQL version information

Information about the MySQL Server service version.

Information about the MySQL Server service version can be obtained from the command line in the following ways:

c:\> mysql -V

c:\> mysql --version

After you log in, view the current version information in the following ways:

select version();

Basic Use - Library

View all databases

show databases;

information_schema is the database that comes with the MySQL system. It mainly stores the system information of the MySQL database server, such as the name of the database, the name of the data table, the name of the field, access rights, the folder where the data file is located, the folder used by the system, and so on.

performance_schema is the database that comes with the MySQL system and can be used to monitor various MySQL performance metrics.

sys is the database that comes with the MySQL system. Its main function is to show the various performance indicators of the MySQL database server in a more understandable way, to help system administrators and developers monitor the technical performance of MySQL.

MySQL stores system information needed by the MySQL database server to run, such as data folders, character sets currently in use, constraint checking information, and so on.

Create your own database

create database Database Name;
#Create an atguigudb database that cannot have the same name as an existing database.
create database atguigudb;

Use your own database

use Database Name;
#Using the atguigudb database
use atguigudb;

Be careful

• If you do not use the use statement and subsequent operations against the database are not qualified with a "data name", you will be reported as "ERROR 1046 (3D000): No database selected".
• After using the use statement, if the next SQL is for a database operation, there is no need to repeat the use
• If you want to operate against another database, use it again.

Delete database

drop database Database Name;

#Delete atguigudb database
drop database atguigudb;

View database creation information

show create database Database Name\G

#View detailed creation information for the atguigudb database
show create database atguigudb\G

The results are as follows:

*************************** 1. row ***************************
Database: atguigudb
Create Database: CREATE DATABASE `atguigudb` /*!40100 DEFAULT CHARACTER SET latin1 */
1 row in set (0.00 sec)

The results above show that the atguigudb database also does not support Chinese, and the character set defaults to latin1.

Basic Use - Tables

View all tables in a library

show tables;  #Require a use statement before
show tables from Database Name;

Create a new table

create table Table Name(
	Field name data type,
	Field name data type
);

Be careful

• If it's the last field, you don't need to add a comma after it because the purpose of the comma is to split each field.

#Create Student Table
create table student(
	id int,
    name varchar(20)  #Say a name up to 20 characters
);

View data from a table

select * from Database table name;

#View data from the student table
select * from student;

Add a record to the table

insert into Table Name values(Value List);
#Add two records to the student table
insert into student values(1,'Zhang San');
insert into student values(2,'Li Si');

Error (Character Set Problem)

mysql> insert into student values(1,'Zhang San');
ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5\xC8\xFD' for column 'name' at row 1
mysql> insert into student values(2,'Li Si');
ERROR 1366 (HY000): Incorrect string value: '\xC0\xEE\xCB\xC4' for column 'name' at row 1
mysql> show create table student;

Delete Table

drop table Table Name;

#Delete Student Table
drop table student;

View table creation information

show create table Table Name
#View detailed creation information for the student table
show create table student

The results are as follows:

*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

The result above shows that the default character set of the student table is that "latin1" does not support Chinese.

Encoding settings for MySQL

Teacher Li Mingjie's Ways

default-character-set=utf8mb4
default-character-set=utf8mb4
character_set_server=utf8mb4

MySQL 5.7

Problem Reproduction: Command Line Operations sql Scrambling Problem

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

Problem solving

• Step 1: View the encoding commands

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

• Step 2: Modify my.ini configuration file under mysql's data directory

[mysql]  #Around 63 lines, add below
... 
default-character-set=utf8  #Default Character Set

[mysqld]  # Around 76 lines, add below
...
character-set-server=utf8
collation-server=utf8_general_ci

Note: It is recommended to use advanced text editor such as notepad+. Opening the modification with software such as Notepad may result in the modification of the file encoding to the encoding with BOM header, which will result in the service restart failure.

• Step 3: Restart the service

• Step 4: View the encoding commands

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

• If the above configuration is correct. Then we can create a new database, create a new data table, and then add the data that contains the Chinese language.

In MySQL 8.0

Prior to MySQL version 8.0, the default character set was latin1, and the utf8 character set pointed to utf8mb3. Web site developers often modify the encoding to the utf8 character set when designing a database. If you forget to modify the default encoding, you will have a problem with scrambling. Beginning with MySQL 8.0, the default encoding of the database was changed to utf8mb4, which avoided the above scrambling problem.

Solving common problems

Question 1: root user password forgotten, reset operation

1: Shut down mysqld (service process) through task manager or service management
2: Open mysqld with command line + special parameters

mysqld --defaults-file="D:\ProgramFiles\mysql\MySQLServer5.7Data\my.ini" --skip-grant-tables

3:At this time, the mysqld service process has been opened. And no permission checks are required
4: mysql-uroot logon server without password. Start another client
5:Modify Permission Table
(1)use mysql;
(2) update user set authentication_string=password('new password') where user='root'and Host='localhost';
(3)flush privileges;
6: Shut down the mysqld service process through the Task Manager.
7:Open mysql service again through service management.
You can log in with the new password you changed.

Question 2: The mysql command reports "Not an internal or external command"

Configure the bin directory of the mysql installation directory to the environment variable path if you enter the mysql command with the message "Not an internal or external command".

Question 3: Error ERROR: Manipulate tables and data without selecting a database

ERROR 1046 (3D000): No database selected

• Solution 1: Use the "USE database name;" statement so that the next statement operates against the database by default
• Solution 2: All table objects are preceded by a database.

Question 4: Character set issues for command line clients

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

Reason: The server considers your client's character set to be utf-8, whereas your client's character set is actually GBK.

View all character sets: SHOW VARIABLES LIKE'character_ Set_%';

Solution, set the currently connected client character set "SET NAMES GBK;"

Q5: Modify the character encoding of databases and tables

Modify encoding:

(1) Stop the service first, (2) Modify my.ini file, (3) Restart the service

Explain:

If you built the library and table before modifying my.ini, the encoding of the library and table is the same as Latin1, either removing the rebuild or using the alter statement to modify the encoding.

mysql> create database 0728db charset Latin1;
Query OK, 1 row affected (0.00 sec)

mysql> use 0728db;
Database changed

mysql> create table student (id int , name varchar(20)) charset Latin1;
Query OK, 0 rows affected (0.02 sec)


mysql> show create table student\G
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> alter table student charset utf8; #Modify table character encoding to UTF8
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> show create table student\G
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `name` varchar(20) CHARACTER SET latin1 DEFAULT NULL,  #Field is still latin1 encoding
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


mysql> alter table student modify name varchar(20) charset utf8; #Modify field character encoding to UTF8
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> show create table student\G
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show create database 0728db;;
+--------+-----------------------------------------------------------------+
|Database| Create Database                                                 |
+------+-------------------------------------------------------------------+
|0728db| CREATE DATABASE `0728db` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+------+-------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> alter database 0728db charset utf8; #Modify the character encoding of the database to utf8
Query OK, 1 row affected (0.00 sec)


mysql> show create database 0728db;
+--------+-----------------------------------------------------------------+
|Database| Create Database                                                 |
+--------+-----------------------------------------------------------------+
| 0728db | CREATE DATABASE `0728db` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+--------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

Reference resources

Song Hongkang, Shangxi Valley: MySQL database (entry to advanced, novice to bull).

Xiao Ma Ge Li Mingjie: Java from 0 to Architect II The cornerstone of JavaEE Technology.

Thank you for your attention and support!