Mysql command line mode access operation mysql database operation

Usage Environment

In cmd mode, enter mysql --version (to view the installed version of MySQL).

The complete command can be obtained by using mysql --help.

The Mysql version used in this test is mysql5.

SQL used in this test: db_yves.sql: Link: https://pan.baidu.com/s/1vZWWgAp7TS48nrBiMDpcaQ Extraction code: vveu

1. Connect to the database

Without the help of database management software (such as Navicat, etc.), connect to the MySQL software library server through DOS and then operate the database.

The general format for connecting to a database is: mysql -P port number -h mysql host name or IP address -u username -p

Explanation: (-P uppercase P stands for port, lowercase p stands for password, h stands for host name or ip, u stands for user)"

EG: mysql -P 3306 -h 192.168.1.104 -u root -p

1. Local connection

If the command line is on the local machine where mysql is located and the default port 3306 is used, the statement can be simplified to:

mysql -u root -p

2. Remote connection

Note: When using a remote connection, the connection user and the user's current IP address should be the allowed user and allowed IP in the remote database, otherwise the connection is not allowed.

mysql -P 3306 -h 192.168.1.104 -u root -p

After successful login, the following figure is shown:

2. Operate the database

After successfully logging into the MySQL database using the username and password, you can operate the database within the scope of the user's permissions. db_yves is a data name I created myself. When operating data, each statement is marked with ; or \g to end.

1. View all databases

show databases;

2. Create a database

create database db_yves;

3. Use a database

use db_yves;

4. Display all tables in the database

show tables;

5. View the table structure

show columns from customers; Or use shortcut: DESCRIBE customers;

6. Delete the database

drop database db_yves;

About command line mode database file import and export:

In command line mode, you can import and export sql files by entering CMD.

Export database file

Including exporting the database to the specified table.

1. Export the structure and data of the database db_yves

mysqldump -h localhost -u root -p db_yves > D:\db_yves.sql

2. Export the structure of the database db_yves (add -d parameter):

mysqldump -h localhost -u root -p db_yves -d > D:\db_yves_stru.sql

3. Export the structure and data of the customers table in the database db_yves:

mysqldump -h localhost -u root -p db_yves customers > D:\customers.sql

4. Export the structure of the customers table in the database db_yves (add the -d parameter):

mysqldump -h localhost -u root -p db_yves -d > D:\customers_stru.sql

Importing a database file

Import the database file db_yves.sql into the database db_yves.

mysql -h localhost -u root -p db_yves < D:\db_yves.sql

Other commonly used phrases

SHOW STATUS, which displays extensive server status information;

SHOW CREATE DATABASE and SHOW CREATE TABLE, which are used to display the MySQL statements for creating a specific database or table, respectively;

SHOW GRANTS, used to display the security permissions granted to users (all users or specific users);

SHOW ERRORS and SHOW WARNINGS are used to display server error or warning messages.

Supplementary knowledge: Mysql | Combined where clause to filter data (AND, OR, IN, NOT)

MySQL allows the use of multiple where clauses, and the combination of where clauses allows the use of two methods: AND and OR clauses.

Operation symbols in the database: AND, OR, IN, NOT.

AND:

SELECT * FROM products WHERE products.vend_id = 1003 AND products.prod_price <= 10;

OR:

SELECT * FROM products WHERE products.vend_id = 1002 OR products.vend_id = 1003;

IN:

It is recommended not to use OR in clauses where IN can be used. IN has good performance and is easy to understand.

SELECT * FROM products WHERE products.vend_id IN (1002,1003);

NOT:

MySQL supports NOT only in the negation of IN, BETWEEN, and EXISTS clauses, which is different from most other databases that support various conditions.

SELECT * FROM products WHERE products.vend_id NOT IN (1002,1003);

Notice:

In a clause that contains both AND and OR, MySQL prioritizes AND operations. It is generally recommended to use () to determine the processing order and eliminate ambiguity.

For example: SELECT * FROM products WHERE (products.vend_id= 1002 OR products.vend_id=1003) AND prod_price >= 10;

The above article about Mysql command line mode access and operation of MySQL database operation is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.