1. CREATEUSER Syntax: CREATEUSERusername @ hostIDENTIFIEDBYpassword; example: CREATEUSERdog @ localhostIDENTIFIEDBY123456; CREATEUSERpig@192.168.1.101 _ IDENDIFIEDBY123456; CREATEUSERpig @ % IDENTIFIEDBY123456; CREATEUSERpig @
1. CREATEUSER Syntax: CREATEUSER 'username' @ 'host' IDENTIFIEDBY 'Password'; example: CREATEUSER 'Dog' @ 'localhost' IDENTIFIEDBY '000000'; CREATEUSER 'pig' @ '2017. 168.1.101 _ 'idendifiedby '201312'; CREATEUSER 'pig' @ '% 'identifiedby '000000'; CREATEUSER 'pig '@
1. CREATE USER
Syntax:
Create user 'username' @ 'host' identified by 'Password ';
Example: create user 'dog '@ 'localhost' identified by '123 ';
Create user 'pig' @ '192. 168.1.101 _ 'idendified BY '123 ';
Create user 'pig' @ '%' identified by '000000 ';
Create user 'pig' @ '%' identified '';
Create user 'pig' @ '% ';
Instance 1:
Mysql> create user jss;
In this way, you can create a connection from any machine on which the mysql client is installed and can access the target server without a password. For example, execute a connection from the client with ip Address: 10.0.0.99:
Mysql-ujss-h 172.16.1.110
View this user:
Mysql> select user, host, password from user where user = 'jss ';
Select user (); // displays the current USER
Instance 2:
Mysql> create user jss_ps identified by 'jss ';
You must specify a password when connecting to a user. You can set the password by specifying the identified by clause when creating a user.
Login with password:
Mysql-ujss_ps-p-h 172.16.1.110
If you want the specified user to only access from a specified domain or host, you can specify the host when creating the user. For example, the specified user can only access from 10.0.0.99
Mysql> create user jss_ip@10.0.0.99 identified by password '20140901 ';
2. Use the GRANT statement
Syntax: mysql> grant permission 1, permission 2,... Permission n on database name. Table name to user name @ user address identified by 'Connection password ';
Permission 1, permission 2,... Permission n stands
Select, insert, update, delete, create, drop, index, alter, grant, references, reload, shutdown, process, file, and other 14 Permissions
Instance:
Mysql> grant select, insert, update, delete, create, drop on vtdc. employee to joe@10.163.225.87 identified by '20140901 ';
Assign the user joe from 10.163.225.87 the permission to perform select, insert, update, delete, create, drop, and other operations on the database's vtdc employee table, and set the password to 123.
Mysql> grant all privileges on vtdc. * to joe@10.163.225.87 identified by '20140901 ';
Assign the user joe from 10.163.225.87 the permission to perform all operations on all tables in the database vtdc and set the password to 123.
Mysql> grant all privileges on *. * to joe@10.163.225.87 identified by '20140901 ';
Assign the user joe from 10.163.225.87 the permission to perform all operations on all tables in all databases and set the password to 123.
Mysql> grant all privileges on *. * to joe @ localhost identified by '20140901 ';
Grant the local user joe the permission to perform all operations on all tables in all databases and set the password to 123.
3. insert records directly to the mysql. user table:
Mysql> insert into user (host, user, password) values ('%', 'jss _ insert', password ('jss '));
Mysql> flush privileges; // refresh the system permission list
4. Modify the mysql user password:
A. Use mysqladmin Syntax: mysqladmin-u username-p old password New password
For example, mysqladmin-u root-p 123 password 456;
B. directly modify the user password of the user table:
Syntax: update mysql. user set password = password ('new password') where User = "phplamp" and Host = "localhost ";
Instance: 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 the current login user uses set password = PASSWORD ("newpassword ");
Instance:
Set password for root @ localhost = password ('');
Set password for name = PASSWORD ('new password ');
Set password for 'pig' @ '%' = PASSWORD ("123456 ");
5. delete users and revoke permissions:
A. Cancel an account and its permissions
Drop USER user;
Drop user username @ '%'
Drop user username @ localhost
B. cancelling 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 sss @ localhost;
Revoke all on user. * from 'admin' @ '% ';
Show grants for 'pig' @ '%'; // view authorization
C. delete a user:
Syntax: Delete from user where user = "user_name" and host = "host_name ";
Example: delete from user where user = 'sss' and host = 'localhost ';
Ii. database tables
1. view all databases: Database Directory:/usr/local/mysql/data
Mysql> show databases; // displays the database
Mysql> USE abccs // enter the database
Mysql> show tables; // display table
Mysql> DESCRIBE mytable; // displays the table structure.
Mysql> create database abccs; // CREATE a DATABASE
Mysql> create table mytable (name VARCHAR (20), sex CHAR (1), birth DATE, birthaddr VARCHAR (20); // CREATE a TABLE
Mysql> insert into mytable values ('abccs ', 'F', '2017-07-07', 'China'); // insert 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 a database:
Mysql> drop database drop_database; // delete a specified database
Alter table name ENGINE = storage ENGINE name; // modify the storage ENGINE of the table
Alter table Name drop attribute name; // delete a field
Alter table old table name rename to new table name; // Modify table Name
Alter table name modify attribute name data type; // modify Field Data Type
Alter table name change old property name new data type; // Modify Field name
Alter table Name drop foreing key foreign KEY alias; // Delete the foreign KEY constraint of the sub-table
Add Table fields:
{Alter table example add phone VACGAR (20); // add a unrestricted Field
Alter table example add age INT (4) not null; // add a field with a increment constraint
Alter table example add num INT (8) primary key first; // add a field to the FIRST position of the table
Alter table example add address VARCHAR (30) not null after phone; // add a field AFTER the specified position of the table
Alter table example modify name VARCHAR (20) FIRST; // modify the field to the FIRST place.
Alter table example modify num INT (8) ATER phone; // After modifying a field to a specified field
}