MySQL Introduction (2) – database information reference

Time:2020-3-24

We talked about MySQL and SQL structured query statements in the previous article, and we will learn about them step by step.

1. Login MySQL
MySQL – u user name – P password
For example: mysql-uroot-pabc123
The results are as follows:
MySQL Introduction (2) - database information reference

2. Check the database
show databases;
The results are as follows:
MySQL Introduction (2) - database information reference

3. Use the specified database
Use specified database
For example: use dateinfo
The results are as follows:
MySQL Introduction (2) - database information reference

4. View the currently used database
select database();
The results are as follows
MySQL Introduction (2) - database information reference

5. Check the port number used by the database
show variables like ‘port’
The results are as follows:
MySQL Introduction (2) - database information reference

6. Check the coding information of the database
show variables like ‘character%’
The results are as follows:
MySQL Introduction (2) - database information reference

Character set client is the client encoding mode;
Character set connection is the code used to establish the connection;
Character set database is the code of database;
Character ﹣ set ﹣ results is the encoding of the result set;
Character set server is the code of database server;
As long as the above coding methods are the same, there will be no garbled code.

7. View the table information of the database
show tables;
MySQL Introduction (2) - database information reference

Or use the following SQL statement to view the table information of a database.
Select * from information menu schema. Tables where table menu schema = ‘database name’;
For example:
select * from information_schema.tables where table_schema=’dateinfo’;
The results are as follows:
MySQL Introduction (2) - database information reference

8. View table structure
Desc table name
For example: desc lsl_user;
The results are as follows:
MySQL Introduction (2) - database information reference

9. View the permissions of a user
show grants for ‘root’@’localhost’;
The results are as follows:
MySQL Introduction (2) - database information reference
10. View the file storage path
show variables like ‘%datadir%’;
The results are as follows:
MySQL Introduction (2) - database information reference

11. View the current number of connections and concurrent data
show status like ‘Threads%’;
The results are as follows:
MySQL Introduction (2) - database information reference

Threads “cached: indicates how many idle threads are in the thread cache at this time.
Threads “connected: represents the number of currently established connections. Since a connection requires a thread, it can also be regarded as the number of currently used threads.
Threads “created: represents the number of threads that have been created since the last service startup.
Threads “running: represents the number of currently active (non sleeping) threads. It does not represent the number of threads in use. Sometimes the connection has been established, but the connection is in the sleep state. Here, the corresponding thread is also in the sleep state

Next, I’ll explain the addition, deletion, query and modification of MySQL and perform the operation drill. Thank you.