"MySQL Create user | Delete user | Modify User permissions | Common commands"

Original link: http://blog.csdn.net/leili0806/article/details/8573636

1. CREATE USER

Grammar:

CREATE USER ' username ' @ ' host ' identified by ' password ';

Example: CREATE USER ' dog ' @ ' localhost ' identified by ' 123456 ';

CREATE USER ' pig ' @ ' 192.168.1.101_ ' idendified by ' 123456 ';

CREATE USER ' pig ' @ '% ' identified by ' 123456 ';

CREATE USER ' pig ' @ '% ' identified by ';

CREATE USER ' pig ' @ '% ';

Example 1:

mysql> Create user JSS;

This creates a user who can create a connection from any machine that has a MySQL client installed and has access to the target server, without the need for a password. For example, to perform a connection from a ip:10.0.0.99 client:

Mysql-ujss-h 172.16.1.110

View the User:

Mysql> Select User,host,password from user where user= ' JSS ';

SELECT USER (); Show Current User

Example 2:

Mysql> create user Jss_ps identified by ' JSS ';

When a user connects, a password must be specified, and the password can be set by specifying the identified by clause when the user is created

Login with Password:

Mysql-ujss_ps-p-H 172.16.1.110

If you want the specified user to be accessible only from one of the specified domains or hosts, you can specify host when you create the user, for example, specify that the user can access only from 10.0.0.99

mysql> create user [email protected] identified by password ' 123456 ';

2. Using the GRANT statement

Syntax:mysql> grant permission 1, permission 2,... Permission n on the database name. Table name to User name @ user address identified by ' connection password ';

Permissions 1, Permissions 2,... Permission N represents

14 privileges such as Select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file

Instance:

Mysql>grant Select,insert,update,delete,create,drop on Vtdc.employee to [e-mail protected] identified by ' 123 ';

Assign the user Joe from 10.163.225.87 the ability to perform operations such as Select,insert,update,delete,create,drop on the employee table VTDC the database, and set the password to 123.

Mysql>grant all privileges in vtdc.* to [e-mail protected] identified by ' 123 ';

For users from 10.163.225.87, Joe assigns permissions to all operations on the database VTDC all tables, and sets the password to 123.

Mysql>grant all privileges on * * to [e-mail protected] identified by ' 123 ';

For users from 10.163.225.87, Joe assigns permissions to all the tables in all databases and sets the password to 123.

Mysql>grant all privileges on * * to [e-mail protected] identified by ' 123 ';

Assign the native user Joe permission to all operations on all tables in all databases, and set the password to 123.

3. Insert the record directly into the Mysql.user table:

mysql> INSERT INTO User (Host,user,password) values ('% ', ' Jss_insert ', password (' JSS '));

Mysql>flush privileges; Refresh System Permissions Table

4. Modify the MySQL user password method:

A. Using mysqladmin syntax: mysqladmin-u user Name----old password password new password

For example: mysqladmin-u root-p 123 password 456;

B. Directly modify the user password for the users table:

Syntax: Update mysql.user set Password=password (' New password ') where user= "Phplamp" and host= "localhost";

Example: Update user set Password=password (' 54netseek ') where user= ' root ';

Flush privileges;

C. Use the Set Password statement to modify the password: syntax:

SET PASSWORD for ' username ' @ ' host ' = PASSWORD (' NewPassword ');

If it is the current login user with Set PASSWORD = PASSWORD ("NewPassword");

Instance:

Set password for [email Protected]=password (');

SET PASSWORD for Name=password (' New PASSWORD ');

SET PASSWORD for ' pig ' @ '% ' = PASSWORD ("123456");

5. Delete users and Revoke permissions:

A. Cancelling an account and its permissions

Drop user User;

drop user [email protected] '% '

drop user [email protected]

B. Cancellation of authorized users:

Syntax: REVOKE privilege on databasename.tablename from ' username ' @ ' host ';

Example: REVOKE SELECT on * * from ' pig ' @ '% ';

REVOKE SELECT on Test.user from ' pig ' @ '% ';

Revoke all on * * from [email protected];

Revoke all on user.* from ' admin ' @ '% ';

SHOW GRANTS for ' pig ' @ '% '; View authorizations

C. Delete users:

Syntax: Delete from user where user = "user_name" and host = "HOST_NAME";

Example: Delete from user where user= ' sss ' and host= ' localhost ';

Second, the database table

1. View all databases: Database directory:/usr/local/mysql/data

Mysql> SHOW DATABASES; Display Database

mysql> use ABCCS//Enter database

Mysql> SHOW TABLES; Show Table

Mysql> DESCRIBE mytable; Show Table Structure

mysql> CREATE DATABASE Abccs; Create a database

Mysql> CREATE TABLE mytable (name VARCHAR), sex CHAR (1), birth DATE, Birthaddr VARCHAR (20)); Create a table

mysql> INSERT INTO mytable values (' Abccs ', ' f ', ' 1977-07-07 ', ' China '); inserting table Data

Insert Data Using text:

{

Mysql.txt content: Abccs F 1977-07-07 China

Mary F 1978-12-12 USA

Tom M 1970-09-02 USA

mysql> LOAD DATA LOCAL INFILE "Mytable.txt" into TABLE pet; Import TXT file data

}

2. Delete the database:

mysql> drop Database drop_database; Delete a database that has been determined to exist

ALTER TABLE name engine= storage engine name;//modify Tables Storage engine

ALTER TABLE name drop attribute 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

Add Table field:

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

ALTER TABLE example add Age INT (4) is not NULL; Fields that increase the constraint

ALTER TABLE example add num INT (8) PRIMARY KEY first; Increment field at the first position of a table

ALTER TABLE example add address VARCHAR (+) not NULL after phone; Adds a field after the specified position in the table

ALTER TABLE example modify name VARCHAR (a) 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 Create user | Delete user | Modify User permissions | Common commands"