Detailed explanation of how to use CMD command to operate MySql database

First: Start and stop the mysql service

net stop mysql
net start mysql

Second: Login

mysql –u username [–h hostname or IP address] –p password

Note: Username is the user you log in as. Host name or IP address is optional. It is not required for local connection but is required for remote connection. Password is the password of the corresponding user.

Third: Add new users

Format: grant permission on database.* to username@login host identified by "password"

For example, add a user user1 with a password of password1, so that the user can log in on the local computer and have the permissions to query, insert, modify, and delete all databases. First, connect to mysql as 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 run another command to remove the password.

grant select,insert,update,delete on mydb.* to user1@localhost identified by "";

Fourth: Operate the database

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

1. Display the database list.

show databases;

By default, there are two databases: mysql and test. The mysql database stores mysql system and user permission information. When we change passwords and add new users, we are actually operating this database.

2. Display the data table in the library:

use mysql;
show tables;

3. Display the structure of the data table:

describe table name;

4. Create and delete database:

create database library name;
drop database library name;

5. Create a table:

use library name;
create table table name (field list);
drop table table name;

6. Clear the records in the table:

delete from table name;

7. Display the records in the table:

select * from table name;

8. Set encoding

set names utf8

Change the root user's password;

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

Fifth: Export and import data

1. Export data:

mysqldump –opt test > mysql.test

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

For example: mysqldump -u root -p123456 --databases dbname > mysql.dbname

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

2. Import data:

source D:\ceshi.sql

This is the address where the sql file is stored

Operation Manual:

The fields of 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 databases currently exist on the server:

mysql> SHOW DATABASES;

2: Create a database MYSQLDATA

mysql> CREATE DATABASE MYSQLDATA;

3: Select the database you created

mysql> USE MYSQLDATA; (Press Enter and the message "Database changed" will appear, indicating the operation was 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 records to the table

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

8: Load the data into the database table in text format (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 table

mysql>delete from MYTABLE;

12: Update the data in the table

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

13: Rename the table

For example, change the name of the table MyClass to YouClass:

mysql> rename table MyClass to YouClass;

14: Modify field names and attributes

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

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links