Detailed Explanation of CMD Command Operating MySql Database

1: Start and stop of mysql service

net stop mysql
net start mysql

Number 2: Landing

mysql  In fact, in fact, the u User name  [ In fact, in fact, the h Hostname or IP Address ]  In fact, in fact, the p Password 

User name is your login user, host name or IP address is optional, if it is a local connection is not required, remote connection needs to be filled in, password is the corresponding user's password.

Number 3: Adding New Users

Format: grant Permissions on Database.* to Username @ Login Host identified by [Password]

For example, add a user user1 password to password1, so that he can log in on the machine and have the authority to query, insert, modify and delete all databases. First connect to mysql with the root user, and then type the following command:

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, 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 "";

Number 4: Operating the database

Log in to mysql and run the following commands at the prompt of mysql, each ending with a semicolon.

1. Display a list of databases.

show databases;

There are two databases by default: mysql and test. mysql inventory contains mysql system and user rights information. When we change passwords and add new users, we actually operate this library.

2. Display the data tables in the library:

use mysql;
show tables;

3. Display the structure of the data table:

describe  Table name ;

4. Building and deleting databases:

create database  Library name ;
drop database  Library name ;

5. Build a table:

use  Library name ;
create table  Table name ( Field list );
drop table  Table name ;

6. Empty the records in the table:

delete from  Table name ;

7. Display the records in the table:

mysql  In fact, in fact, the u User name  [ In fact, in fact, the h Hostname or IP Address ]  In fact, in fact, the p Password 

0

8. Set the code

set names utf8

Modify the password of root user;

mysql> update mysql.user set password=PASSWORD( 'New password ') where User='root'; 
mysql> flush privileges; 

Section 5: Exporting and Importing Data

1. Export data:

mysql  In fact, in fact, the u User name  [ In fact, in fact, the h Hostname or IP Address ]  In fact, in fact, the p Password 

3

Exporting the database test database to the mysql. test file, which is a text file

Such as: mysqldump -u root -p123456 --databases dbname > mysql.dbname

Is to export the database dbname to the file mysql. dbname.

2. Import data:

mysql  In fact, in fact, the u User name  [ In fact, in fact, the h Hostname or IP Address ]  In fact, in fact, the p Password 

4

This is the address where the sql file is stored

Operation manual:

The field data of text data are separated by tab key.

mysql  In fact, in fact, the u User name  [ In fact, in fact, the h Hostname or IP Address ]  In fact, in fact, the p Password 

5

1: Use the SHOW statement to find out what databases currently exist on the server:

mysql  In fact, in fact, the u User name  [ In fact, in fact, the h Hostname or IP Address ]  In fact, in fact, the p Password 

6

2: Create a database MYSQLDATA

mysql> CREATE DATABASE MYSQLDATA;

3: Select the database you created

mysql> USE MYSQLDATA; ( Press Enter to appear Database changed  The operation is successful when the !)

4: See what tables exist in the current database

mysql  In fact, in fact, the u User name  [ In fact, in fact, the h Hostname or IP Address ]  In fact, in fact, the p Password 

9

5: Create a database table

mysql> CREATE TABLE MYTABLE (name VARCHAR(20), sex CHAR(1));

6: Show the structure of the table:

mysql> DESCRIBE MYTABLE;

7: Add a record to the table

mysql> insert into MYTABLE values ("hyq","M");

8: Text-loaded data into database tables (e.g. 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: Empty the table

mysql>delete from MYTABLE;

12: Update the data in the table

mysql>update MYTABLE set sex="f" where name='hyq';

13: Rename table name

For example, in the table MyClass name is changed to YouClass:

mysql> rename table MyClass to YouClass;

14: Modify field names and properties

mysql> alter table test change t_name t_name_new varchar(20);

15: Table insert/add new fields

alter table `fy_images` add newColumn varchar(8) NOT NULL COMMENT ' Newly added field '

Summarize