Under linux system mysql add users, delete users, authorization, modify passwords and other sql commands to use

The following article mainly introduces the introduction of MySQL remote login and its common commands. The reason why MySQL remote login and its common commands can be widely used in a short time is also because of their unique features. The following article has an introduction to its related content.

MySQL Remote Login and Common Commands

Start and Stop MySQL Service

net stop MySQL

net start MySQL

Login MySQL

The syntax is as follows: MySQL -u username-puser password

Type the command MySQL -uroot -p, prompt you to enter the password after entering the car, enter 12345, then press Enter to enter In MySQL, the MySQL prompt is:

MySQL>

Note that if you are connecting to another machine, you need to add a parameter -h machine IP

Add new user

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

For example, add a user user1 password to password1, so that it can be in this machine Log in, and have the right to query, insert, modify, delete all databases. First connect to MySQL as the root user, then type the following command:

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

If you wish The user can log in to MySQL on any machine and change localhost to "%".

If you don't want user1 to have a password, you can use another command to remove the password.

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

MySQL remote login and common commands

Operation database

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

1. Display the database list.

show databases;

There are two databases by default: MySQL and test. MySQL inventory puts the MySQL system and user permissions information, we change the password and add users, in fact, is to operate on this library.

2. Display the data table in the library:

use MySQL;

show tables;

3. Display the structure of the data table:

describe table name;

4, build and delete library:

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, empty the table Record:

delete from table name;

7, display the records in the table:

select * from table name;

MySQL remote login and Common Commands

Exporting and Importing Data

1. Exporting Data:

MySQLdump –opt test > MySQL.test

Exporting the database test database to MySQL.test file, the latter is a text file

For example: MySQLdump -u root -p123456 –databases dbname > MySQL.dbname

is to export the database dbname to the file MySQL.dbname .

2. Import data:

MySQLimport -u root -p123456 < MySQL.dbname.

3. Import text data into the database:

The field data of the text data is separated by tabs.

use test;

load data local infile "filename" into table table name;

1: Use the SHOW statement to find out what database currently exists on the server: Br>

MySQL> SHOW DATABASES;

2:2, create a database MySQLDATA

MySQL> CREATE DATABASE MySQLDATA;

3: Select the database you created

MySQL> USE MySQLDATA; (The data change appears when the Enter key is displayed, the operation is successful!)

4: See what table exists 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 table Structure:

MySQL> DESCRIBE MYTABLE;

7: Add records to the table

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

8: Load data into a database table by text (eg D:/MySQL.txt)

MySQL> LOAD DATA LOCAL INFILE "D:/MySQL.txt" INTO TABLE MYTABLE ;

9: Import .sql file commands (eg D:/MySQL.sql)

MySQL>use database;

MySQL>source d:/MySQL.sql;

10: Delete Table

MySQL> drop TABLE MYTABLE;

11: Empty 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.data

14: Example 2: Connecting to a MySQL on a remote host

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

MySQL -h110.110.110.110 -uroot -pabcd123 //MySQL remote login

(Note: u and root can be used without spaces, others are the same)

3, exit the MySQL command: exit (Enter)

The above related content is the introduction of MySQL remote login and common commands, I hope you can gain something.