MySQL Database operation instructions

I. Database creation User

1) Simple creation

CREATE USER [email protected];

2) with password

CREATE USER [email protected] identified by ' 123456 '

Here [email protected] is the user name created,123456 is the user's password.

Note: The MySQL database command is not case sensitive.

Second, after creating a user, you need to give the user the appropriate permissions, generally with the GRANT directive

The format is:GRANT permissions on permission range to user name @ Login Host Identified by ' password ';

1) GRANT all on * * to ' ty ' @ ' localhost ';

Allow the user to log on locally and give all of its database, table all permissions, "All" means all permissions, in clause *. * is all databases, tables ,"localhost" means log on locally.

2)GRANT select,update,delete,insert on * * to ' ty ' @ ' percent ' identified by ' 123456 '

Let the user Ty in all places can be signed in to give them and other permissions to change, and here the '% ' means that the user in all places any host can log in.

Third, view permissions

1) Current user

Show grants;

2) Other users (use keywords for)

Show grants for [email protected];

Iv. Revocation of Rights

Revoke the permissions that have been given to the Mysql user, using the revoke instruction, and simply replace the keyword "to" with the"from "can:

Grant all on * * to [email protected];

Revoke all on * * from [email protected];

V. Database operation instructions

1.1 Creating a database

Create database Tang;

1.2 creating a data table

Create Table UserInfo (

ID int (4) NOT null primary key auto_increment,

UserName char () NOT NULL,

Password char (a) not null);

2.1 View Display Database

Show databases;

2.2 Display Data Sheet

Get the data table structure using the desc statement

mysql> desc userinfo;

+-----------+-------------+----------+---------+----------------+--------+

| Field | Type | Null | Key | Default | Extra |

+-----------+-------------+----------+----------+---------------+--------+

| ID | Int (11) | NO | PRI |             NULL | |

| UserName | varchar (20) |              NO | |             NULL | |

| password | varchar (20) |               NO | |             NULL | |

+----- ------+-------------+---------+----------+---------------+---------+

3 rows in Set (0.01 sec

3.1 Deleting a database

Drop database Tang;

Drop database if exists Tang;

3.2 Delete a table field

Alter table UserInfo drop new_sex;

3.3 Deleting a data table

drop table userinfo;

3.4 Delete the specific data in the table ( Note that the command used here is also different, that is, delete is not a drop)

Delete from userinfo where id=0;

Note: When you are unsure whether a database or table exists, if you want to perform a delete instruction, it is best to add a condition to determine "if exists", which avoids an error.

4.1 Add table fields, which are added on the structure of the table

Alter table userinfo Add sex char (TEN) not null;

4.2 insert data into a table, which is added to the contents of the table

Insert into userinfo values (0, ' Ty ', ' 1234 '), (1, ' xy ', ' 5678 ');

5.1 Modifying the password for a database

Set password for [email protected] = Old_password (' 123456 ');

5.2 Modifying the fields of the original table

Alter table userinfo change sex new_sex bit not null;

5.3 modifying data in a table

Updata userinfo set username= ' th ' where id=1;

5.4 modifying table names

Using the rename directive

RENAME table UserInfo to user;

The 6.use directive indicates the selection of a database

Use Tang;

7. querying data in a table (Select)

Querying all data

Select * from UserInfo;

The query ID is The row data between 0~2

Select * from UserInfo ORDER by ID limit 0, 2;

Query the first 5 Data

Select * from userinfo limit 5;

Query a specific piece of data

Select *from userinfo where id=1;

MySQL Database operation instructions