MySQL command line

MySQL command line

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 to prompt you to enter your password, enter 12345, and then press Enter to enter mysql. The prompt of mysql is:
mysql>
Note that if you are connecting to another machine, you need to join One 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 the password password1, so that he can Log in on this machine and have query, insert, modify, and delete permissions on all databases. 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 step: Operate the database
Log in to mysql, and then run the following commands at the mysql prompt, each command End with a 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 the 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, and then The or is a text file, such as: mysqldump -u root -p123456 --databases dbname > mysql.dbname, which is to export the database dbname to the file mysql.dbname.
2. Import data:
mysqlimport -u root -p123456 < mysql.dbname.
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; (Press the Enter key and Database changed appears, indicating that 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 (for example, 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'; 13: Back up the database mysqldump -u root Library name>xxx.data14:

Example 2: Connect to MYSQL on the remote host

Assume that the IP of the remote host is: 110.110.110.110, the user name is root, and the password is abcd123. Then type the following command: 　　

　mysql -h110.110.110.110 -uroot -pabcd123 　　

　 (Note: u and root do not need to add spaces, the same goes for others) 　　

3. Exit the MYSQL command: exit