13 Tuesday course 2018.01.15 mysql user management, common SQL statements, mysql database backup and recovery,

13 Tuesday course 2018.01.15 mysql user management, common SQL statements, mysql database backup and recovery,

13.4 mysql user management

By default, mysql has a root user, but the root directory permission is too high, which is dangerous. Therefore, you need to create a new user and grant it the corresponding permissions.

User Creation Method

Grant indicates authorization. all indicates all permissions. to specifies the name, @ indicates the source ip address (that is, the user can only log on to the database through this ip address) and identified indicates the password.

*. * Is the permission. The preceding * indicates the database name. You can write it as mysql. * Then he has permissions for all tables in the database. @ can also be written as %, meaning all ip addresses,

Then we can try again.

@ You can also specify a local device, that is, sock, as shown in figure

Then, you do not need to specify an IP address when logging on.

We can also grant permissions based on specific permissions (Authorization requires logon to the root user)

Then let's take a look at user2 user authorization,

If you need to log on to the database not only on MySQL 133.1, but also on MySQL 133.2, we can

Change the ip address of the Two-line authorization commands in, and then execute them again.

Then you can check

This is how users with the same password can log on to different ip addresses. (This method can be used when the password is unknown)

If you know the password, you can use the previous method.

13.5 common SQL statements

View the number of rows in the database table. The following shows the number of rows in the mysql database user table. (Count (*) indicates the number of rows in the Table)

View all the contents of the database table, such as all the contents of the mysql database db table.

(* It means everything. We do not recommend it at ordinary times, because some tables may have many things. It may take a lot of time and resources to do so)

View fields, such as db fields.

View two fields

Fuzzy query field, like is fuzzy match

Insert data. If we insert data to table t1 of database db1, we can see that it has two fields: id and name,

(Ip is 1, and name is abc)

Then let's take a look, and we will plug it in. (If the content to be inserted is a string, try to add single quotation marks, and the number can be left blank)

Modify data. For example, the id of the Data whose name is aaa is 1.

To delete data, run the following command to delete all data whose id is 2 in Table t1 of database db1.

Clear a table (clear the table content, but do not clear fields, retain the table structure)

If the table structure still exists

However, deleting a table deletes all the connected structures.

Delete a database.

13.6 mysql Database Backup Recovery

The specified database in the backup table is separated by spaces in the middle of the table. When restoring the table, you only need to write the database. You do not need to write the table.

Mysqldump is intended for small databases. If the data volume is large, using it for backup will be slow.