Common commands to operate MySQL under cmd command line

First let’s talk about why we need to operate MySQL under DOS:
SQL has been around since the 19th century. At that time, due to hardware constraints, we could only use a DOS-style interface. Later, although the hardware supported a graphical interface (normal software operation interface), However, the command line interface (that is, the DOS interface) has been retained for its simplicity, efficiency, and convenience. This is the reason for using the DOS interface.
For example, if you want to delete D:123.txt in DOS, if you use the normal software operation interface, you need to open My Computer, find 123.txt, then right-click Delete, and then confirm... .It’s very complicated. With DOS, you only need to enter delete D:123.txt and press Enter to complete. Convenient right? This is the reason why the DOS interface is retained
Work experience: When you often encounter big data processing, you will encounter many problems when operating mysql from the graphical interface, such as the limit of uploading the largest file and the limit of running time. Although it can be solved on the program side, But it is quite troublesome.
This command is suitable for those who like to operate DOS (like me). It executes quickly under DOS and will not time out. . Very useful
win+r to open the command line cmd, the picture is shown directly below, it is difficult to write. . Haha

This will enter the mysql program interface. . . .

The first step, start and stop the mysql service
net stop mysql
net start mysql
The second step, log in to mysql
The syntax is as follows: mysql -u username -p user password
Type the command mysql -uroot -p, press Enter You will then be prompted to enter your password, enter 12345, and then press Enter to enter mysql. The prompt for mysql is:
mysql>
Note that if you are connecting to another machine, you need to add a parameter -h machine IP
The third step, add a new user
Format: grant permission on database.* to username@login host identified by "password"
For example, add a user user1 with password password1, so that he can log in on this machine, and All databases have query, insert, modify, and delete permissions. First connect to mysql as the root user, and then type the following commands:
grant select, insert, update, delete on *.* to user1@localhost Identified by "password1";
If you want the user to be able to log in to mysql on any machine, Then change localhost to "%".
If you don’t want user1 to have a password, you can type another command to remove the password.
grant select,insert,update,delete on mydb.* to user1@localhost identified by "";
The fourth trick: Operate the database
Log in to mysql, and then run the following commands at the mysql prompt, each command begins with End with semicolon.
1. Display the database list.
show databases;
There are two databases by default: mysql and test. The mysql library stores the system and user permission information of mysql. When we change passwords and add new users, we actually operate on this library.
2. Display the data table in the database:
use mysql;
show tables;
3. Display the structure of the data table:
describe table name;
4. Create and delete database:
create database database name;
drop database Library name;
5. Create table:
use library name;
create table table name (field list);
drop table table name;
6. Clear records in the table:
delete from table name;
7. Display table Record:
select * from table name;
The fifth step, export and import data
1. Export data:
mysqldump --opt test > mysql.test
Export the database test database to the mysql.test file, the latter It is a text file
For example: mysqldump -u root -p123456 --databases dbname > mysql.dbname
It is to export the database dbname to the file mysql.dbname.
2. Import data:
mysqlimport -u root -p123456 No need to explain.
3. Import text data into the database:
The field data of the text data are separated by the tab key.
use test;
load data local infile "file name" into table table name;
1: Use the SHOW statement to find out what database currently exists on the server:
mysql> SHOW DATABASES;
2:2. Create a database MYSQLDATA
mysql> CREATE DATABASE MYSQLDATA;
3: Select the database you created
mysql> USE MYSQLDATA; (When "Database changed" appears when you press Enter, the operation is successful!)
4: Check what tables exist in the current database
mysql> SHOW TABLES;
5:Create a database table
mysql> CREATE TABLE MYTABLE (name VARCHAR(20), sex CHAR(1));
6:Display the structure of the table:
mysql> DESCRIBE MYTABLE;
7:Add to the table Record
mysql> insert into MYTABLE values ​​("hyq", "M");
8: Load data into the database table in text mode (for example, D:/mysql.txt)
mysql> LOAD DATA LOCAL INFILE "D: /mysql.txt" INTO TABLE MYTABLE;
9: Import .sql file command (such as D:/mysql.sql)
mysql>use database;
mysql>source d:/mysql.sql;
10: Delete table
mysql> ;drop TABLE MYTABLE;
11: Clear the table
mysql>delete from MYTABLE;
12: Update the data in the table
mysql>update MYTABLE set sex="f" where name='hyq';