MySQL Common operation statement (1: Start, connect database and user actions)

Below is a summary of the commonly used MySQL operation statements (Win7) below:

1. Start and close the database

　　"Administrator" permissions, MySQL installation directory under the bin directory//:

1.1 Start

@>net start MySQL

1.2 Close

@>net stop MySQL;

1.3 Problem: Invalid service name

This is the case when the MySQL service is not installed, and you need to install the service in the same directory:

@>mysqld-install

Accordingly, the Unload command is:

2. Connect to the database

　　"Administrator" permissions, MySQL installation directory under the bin directory//:

Command format:

@>mysql-h host Address-u user name-P user Password

2.1 Connecting native MySQL

@>mysql-u root-p, enter the password after entering.

If you want to log in directly with a password, use the following format:

@>mysql-u root-p Password

　 Note:'-P ' and ' password ' have no spaces.

2.2 Connecting to a remote host MySQL

@>mysql-h 110.110.110.110-u root-p123

2.2 Exit

@>quit or exit

3. Change the password

　　"Administrator" permissions, MySQL installation directory under the bin directory//:

@>mysqladmin-u root-poldpsd Password NEWPSD

If the original user does not have the password, then the '-poldpsd ' can be removed. Note that there are no spaces between '-P ' and ' oldpsd '.

4. User action (this part is excerpted from a blog post)

　　MySQL Environment//:

MySQL add user, new database, user authorization, delete user, change password (note that each line followed by a ' ; ' Represents the end of a command statement.

4.1 New User

• Log in to MySQL:

@>mysql-u root-p@> Password

• To create a user:

Mysql>insert into Mysql.user (Host,user,password) VALUES ("localhost", "Test", Password ("1234"));

This creates a user named: Test with a password of: 1234.

Note: "localhost" here means that the user can only log on locally and cannot telnet to another machine. If you want to telnet, change "localhost" to "%", which means you can log on on any computer. You can also specify that a machine can log on remotely.

• Then log in:

Mysql>exit;@>mysql-u test-p@> Enter password mysql> login successful

4.2 Licensing for users

Authorization format:

Grant permissions on the database. * to User name @ login host identified by "password";

• Log in to MySQL (with root) and log in as root here:

@>mysql-u root-p@> Password

• First create a database (TestDB) for the user:

Mysql>create database TestDB;

• The authorization test user has all the permissions of the TestDB database (all permissions for a database):

Mysql>grant all privileges in testdb.* to [e-mail protected] identified by ' 1234 '; mysql>flush privileges; Refresh System Permissions Table

Format: Grant permissions on database. * To User name @ login host identified by "password";

• If you want to specify partial permissions to a user, you can write:

Mysql>grant select,update on testdb.* to [e-mail protected] identified by ' 1234 '; mysql>flush privileges; Refresh System Permissions Table

• The authorization test user has some permissions for all databases:

Mysql>grant Select,delete,update,create,drop On * * to [e-mail protected] "%" identified by "1234";

The test user has Select,delete,update,create,drop permissions on all databases.

@ "%" indicates authorization for all non-local hosts, excluding localhost. (The localhost address is set to 127.0.0.1, if set to the real local address, do not know whether it can, no authentication.) ）

Authorization to localhost: plus a grant all privileges on testdb.* to [email protected] identified by ' 1234 ';

4.3 Deleting a user

@>mysql-u root-p@> password mysql>delete from user Where user= ' test ' and host= ' localhost '; mysql>flush privileges; Mysql>drop database TestDB; Delete a user's database

Delete accounts and permissions:

4.4 Modifying a specified user password

@>mysql-u root-p@> password mysql>update mysql.user set Password=password (' New password ') where user= "test" and host= " localhost "; mysql>flush privileges;

　　

MySQL Common operation statement (1: Start, connect database and user actions)