Create, delete, and authorize database in MySQL database

Source: Internet
Author: User
Tags flush table name create database

MySQL add users, create a new database, user authorization, delete users, modify password (note that each line is followed by the following; Represents the end of a command statement):

1. New User

Login 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 Password: 1234.

Note: here, "localhost" means that the user can log on locally only, not remotely on another machine. If you want to log on remotely, change "localhost" to "%" to indicate that you can log on to 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

2. Authorize the user

Authorization format: Grant permission on database. * To User name @ login host identified by "password";

Login to MySQL (with root privileges), log in as root:
@>mysql-u root-p

@> Password

First create a database for the user (TestDB):
Mysql>create database TestDB;

Authorize the test user to have all the permissions of the TestDB database (all permissions for a database):
Mysql>grant all privileges in testdb.* to test@localhost identified by ' 1234 ';

Mysql>flush privileges;//Refresh System Permission table

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

If you want to specify partial permissions to a user, you can write this:
Mysql>grant select,update on testdb.* to test@localhost identified by ' 1234 ';

Mysql>flush privileges; Refresh System Permission Table

Authorize test users to have certain permissions for all databases:
Mysql>grant Select,delete,update,create,drop on *.* to test@ "%" identified by "1234";

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

  @ "%" indicates that all non-local hosts are authorized, excluding localhost. (localhost address set to 127.0.0.1, if set to true local address, do not know whether it can, no validation.) )

//To localhost authorization: Add a "grant all" privileges on testdb.* to test@localhost identified by ' 1234;

3. Delete User

 @>mysql-u root-p

 @> password

 mysql>delete from user Where user= ' Test ' and host= ' localhost ';

 mysql>flush privileges;

 mysql>drop db TestDB;//delete user's database

Delete account and permissions: >drop user username @ '% ';

>drop user name @ localhost;

4. Modify the 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;

5. List all databases

Mysql>show databases;

6. Switch database

Mysql>use ' database name ';

7. List all tables

Mysql>show tables;

8. Displaying the datasheet structure

Mysql>describe table name;

9. Delete database and datasheet

Mysql>drop database name;

Mysql>drop table datasheet name;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.