MySQL Learning-User creation authorization

Over time, the project was changed into a SaaS architecture, and each billing account could have access to a subdomain, a separate database, a single user, and the corresponding data.

Now there is time to back up the database-related commands with a blog. Some of them have not been tried, please correct me if there is any mistake.

1. Create a user

Create user ' username ' @ ' host ' identity by ' password ';

Insert into Mysql.user (Host,user,password) VALUES ("Host", "username", Password ("Password"));

If you want the specified user to be accessible only from one of the specified domains (domain) or host, you can specify host when you create the user. For example, if you want to be able to log on locally, you can set the host to LocalHost, and you can set the host to% if you want to be able to 10.10.10.22 on a single machine.

2. Create a View database

Show databases;//Display Database

Create Database dbname; Create a database

Use dbname;//into the database

Show tables;//Display table

Desc tablename;//Display Table structure

source sql/file/path; Import SQL file

3. User authorization

Grant all privileges the dbname.* to [e-mail protected] '% ' identified by ' password ';//authorized username user has all permissions for dbname database

Grant SELECT, update on dbname.* to [e-mail protected] '% ' identified by ' password ';//authorization username user has specified partial permissions for dbname database

4. Refresh Authorization

Flush privileges;

5. Delete users and revoke permissions

Drop User [email protected];//cancel an account and its permissions

Revoke privilege on Dbname.tablename from ' username ' @ ' host ';//un-authorized user

Delete from user where user = "username" and host = "host";//delete user

6. Modify User Password

Mysqladmin-uroot-proot password 123;//Change the root user's password to 123

Update Mysql.user set Password=password (' New password ') where user= "username" and host= "localhost";

Set password for ' username ' @ ' host ' = password (' newpassword ');

7. Deleting a database

Drop database dbname;//Delete an already determined existence

ALTER TABLE name engine= storage engine name;//Modify tables ' storage engine

ALTER TABLE name drop property name;//delete field

ALTER TABLE old table name rename to new table name;//Modify table name

ALTER TABLE name modify property name data type;//modify field data type

ALTER TABLE name change old property name new data type of new property name;//Modify field name

ALTER TABLE name drop foreing key foreign key alias;//delete child table FOREIGN KEY constraint

ALTER TABLE example add phone VARCHAR (20);//Add unconstrained fields

ALTER TABLE example add Age INT (4) does not null;//add constrained fields

ALTER TABLE example add num INT (8) PRIMARY KEY first;//table first position increment field

ALTER TABLE example add address VARCHAR (+) Not NULL add field after phone;//table at specified position

ALTER TABLE example modify name VARCHAR first;//change the field to the first bit

ALTER TABLE example Modify NUM INT (8) Ater phone;//after the field has been modified to the specified field

MySQL Learning-User creation authorization