Win764-bit configuration mysql5.6 installation-free version, Initialization Configuration and Mysql create a new user

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

}