Create and delete users in MySQL

Add a user to MySQL, create a database, authorize the user, delete the user, and modify the password (note that each line is followed by a command; it indicates that a command statement ends ):

1. Create a user

• Log on to MySQL:

@> Mysql-u root-P

@> Password

• Create a user:

Mysql> insert into mysql. User (host, user, password) values ("localhost", "test", password ("1234 "));

In this way, a user named: Test password: 1234 is created.

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

• Then log on:

Mysql> exit;

@> Mysql-U test-P

@> Enter the password

Mysql> logon successful

2. Authorize the user

Authorization format: grant permission on database. * To username @ login host identified by "password ";

• Log on to MySQL (with root permission). Log On As root:

@> Mysql-u root-P

@> Password

• First, create a database (testdb) for the user ):

Mysql> Create Database testdb;

• Authorize the test user to have all permissions of the testdb database (all permissions of a database ):

Mysql> grant all privileges on testdb. * To test @ localhost identified by '123 ';

Mysql> flush privileges; // refresh the system permission list

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

• If you want to assign some permissions to a user, you can write as follows:

Mysql> grant select, update on testdb. * To test @ localhost identified by '123 ';

Mysql> flush privileges; // refresh the system permission list

• Authorize the test user to have certain permissions on all databases:

Mysql> grant select, delete, update, create, drop on *. * To test @ "%" identified by "1234 ";

// The test user has the select, delete, update, create, and drop permissions on all databases.

// @ "%" Indicates authorizing all non-local hosts, excluding localhost. (The localhost address is set to 127.0.0.1. If it is set to a real local address, you do not know if it is OK. No verification is performed .)

// Authorize localhost: add the grant all privileges on testdb. * To test @ localhost identified by '20140901.

3. delete 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 account and permissions:> drop User username @ '% ';

> Drop User username @ localhost;

4. change the password of a specified user

@> Mysql-u root-P

@> Password

Mysql> Update mysql. User SET Password = PASSWORD ('new password') where user = "test" and host = "localhost ";

Mysql> flush privileges;

5. list all databases

Mysql> show database;

6. Switch the database

Mysql> Use 'database name ';

7. list all tables

Mysql> show tables;

8. display the data table structure

Mysql> describe table name;

9. Delete databases and data tables

Mysql> drop database database name;

Mysql> drop table data table name;