MySQL Use action

Brief introduction

MySQL is a DBMS, which is a kind of database software. DBMS can be divided into two categories: a DBMS based on a shared file system, and a DBMS based on the client-server. The former is used for desktop purposes and is typically not used for high-end or more critical applications.

MySQL is a database based on the client-server. Client-server application is divided into two different parts. The server section is the software that is responsible for all data access and processing. This software runs on a computer called a data server. The client is the software that deals with the user.

Connect to a database using MySQL

Entering MySQL at the operating system command prompt will appear with the following prompt:

C:\Users\greenhat>Mysqlwelcome toThe MySQL Monitor. CommandsEnd  with;or\g.your MySQL Connection ID is 9Server Version:5.6. -MySQL Community Server (GPL) Copyright (c) -, ., Oracleand/orits affiliates. Allrights reserved. Oracle isA registered trademark ofOracle Corporationand/oritsaffiliates. Names may trademarks oftheir respectiveowners. Type'Help ;' or '\h'  forHelp. Type'\c'  toClear the Currentinput Statement.mysql>

The connection to the database is opened as indicated in the above interface. Can also be opened by the root user: Mysql-u root-p. This is a prompt for the password, if the installation is not set the password press ENTER. If the software installation does not set environment variables, you need to enter MySQL under the/bin of the installed directory to start.

Attention:

A. command input after mysql>;

B. order, or \g end;

C. Enter quit or exit to exit the command-line utility.

Select Database

When you initially connect to MySQL, there is no database open for you to use. You can then enter show DATABASES to view the existing database:

MySQL>SHOW DATABASES;+--------------------+| Database           |+--------------------+|Information_schema||Mysql||Performance_schema||Student||Students||Test|+--------------------+6Rowsinch Set(0.02Sec

Select a database from the existing database. Keyword use:

MySQL> use students; Database Changedmysql >

Use shoe TABLES to view the tables that exist in the database:

Mysql>SHOW TABLES;+--------------------+|Tables_in_students|+--------------------+|Student1||Students||Teacher||Teacher1|+--------------------+4Rowsinch Set(0.08Sec

The following SQL statements can be used to create a new table for the database.

　　

User Management and Maintenance

The security foundation of the MySQL server is that users should have the appropriate access to the data they need, neither more nor less. For example, most users simply read and write to the table, and some users (administrators) may need to handle user account permissions. MySQL creates a user account named Root, which has complete control over the entire MySQL service. You may have tried to log in with the root account, but you should never use root in the real-world routine. Should create a series of accounts, some for management, some for the user, some for developers to use, and so on.

MySQL user accounts and information are stored in MySQL database named MySQL:

Mysql>  UseMySQL;DatabaseChangedmysql> SELECT User  from User;+------+| User |+------+|Root|+------+

Create user account

Create a new user account, using the Create USER statement, as follows:

MySQL>CREATEUSER0 rows affected (0.48 sec)

　　

Then list the user to see the user we just created. We can also add a password when creating a user, using identified by PASSWORD, as follows:

MySQL>CREATEUSERby'123456'  0 rows affected (0.06 sec)

　　

When renaming a user account, use the Rename user statement, as follows:

MySQL>USERto0 rows affected (0.04 sec)

Delete user account 　 　

To delete a user account, use the drop USER statement, as follows:

MySQL>DROPUSER0 rows affected (0.00 sec)

Set access Permissions

After you create an account, you must then assign access rights. The newly created account does not have access rights. They can log in to MySQL, but cannot see the data and cannot perform any data manipulation.

To see the permissions given to the user account, use the show GRANTS for statement as follows:

Mysql>SHOW GRANTS forJackson;+--------------------------------------------------------------------------------------------------------+|Grants for[Email protected]%                         |+--------------------------------------------------------------------------------------------------------+| GRANTUSAGE on *.*  to 'Jackson'@'%'Identified byPASSWORD'*6bb4837eb74329105ee4568dda7dc67ed2ca2ad9' |+--------------------------------------------------------------------------------------------------------+1Rowinch Set(0.04Sec

The output shows that the user Jackson has a permission usage on *. *. Usage indicates that there is no permission at all.

To set permissions, use the GRANT statement. Grant asks you to give at least the following information:

A. The permissions to be granted;

B. The database and table to which access is granted.

C. User name.

Here is an example of the use of grant:

MySQL>GRANTSELECT on students. *  to  0 rows affected (0.09 sec)

This grant allows the user, Jackson, to use Select on students.* (all tables in the students database). By authorizing select Access, user Jackson has read-only access to all data in the students database.

To view the user's permissions after the change:

Mysql>SHOW GRANTS forJackson;+--------------------------------------------------------------------------------------------------------+|Grants for[Email protected]%                         |+--------------------------------------------------------------------------------------------------------+| GRANTUSAGE on *.*  to 'Jackson'@'%'Identified byPASSWORD'*6bb4837eb74329105ee4568dda7dc67ed2ca2ad9' || GRANT SELECT  on' Students '.*  to 'Jackson'@'%'                         |+--------------------------------------------------------------------------------------------------------+2Rowsinch Set(0.04Sec

Grant's rice operation is revoke, which is used to revoke specific permissions. As follows:

MySQL>REVOKESELECT on students. *  from  0 rows affected (0.01 sec)

Grant and revoke can control access at several levels:

A. Entire server, using grant all and REVOKE all;

B. Entire database, using on database.*;

C. Specific tables, using on database.table;

D. specific columns;

E. A specific stored procedure.

More specific permissions to view the relevant official documents.

Change Password

To change the user's password, use the Set PASSWORD statement. As follows:

MySQL>SET for jackson=Password ('654321'   0 rows affected (0.05 sec)

The new password must be passed to the password () function for encryption.

Backing up data

　　Like all data, MySQL's data must be backed up frequently. The following is a list of methods that can be backed up:

A. Private command-line utility mysqldump dumps all database content to an external file. This program should work properly before making a regular backup so that the correct backup dumps the file.

B. Command-line utility mysqlhotcopy to copy all data from a database.

C. You can use MySQL's backup TABLE or select INTO OUTFILE to dump all data to an external file. These two statements accept the system file name that will be created, which must exist, or an error will occur. The database can be restored using restore TABLE.

Database Maintenance

　　MySQL provides a series of statements that can be used to keep the database running correctly and properly.

ANALYZE Tanle is used to check that the table keys are correct. As follows:

Mysql>ANALYZETABLEstudents;+-------------------+---------+----------+----------+| Table             |Op|Msg_type|Msg_text|+-------------------+---------+----------+----------+|Students.students|Analyze|Status|Ok|+-------------------+---------+----------+----------+1Rowinch Set(1.12Sec

You can also use Chech table to check for multiple issues.

Reference documents

"MySQL must know" Ben Forta, People's post and telecommunications press.

MySQL Use action