MySQL Database basic Operations Command

MySQL database system is a typical application of C/S (client/server) architecture, which requires specialized client software to access MySQL database. The simplest and easiest to use MySQL client software in a Linux system is its own MySQL command tool.

1. Log on to the MySQL server
   

   The MySQL database default administrator user name is "root" and the password is empty. To log on to this computer's MySQL database without setting a password, the following

   In the case of a password, you should also use the "-P" option to verify the password

Successful verification will enter the database operating environment with the prompt as "mysql>", and the user can enter various action statements to manage the database. Each MySQL action statement ends with a semicolon ";" , which can be typed in a case-insensitive way, but is accustomed to capitalizing the keyword portion of a MySQL statement.

To modify a database password

Method 1: Modify the information in the User table in the MySQL library

Method 2: Execute in the Linux command-line environment and use the mysqladmin tool to set the password

2. View the database structure

(1) View all databases in the current server

(2) View the tables contained in the currently used database

The database files of the MySQL database are stored in the/usr/local/mysql/data directory, and each database corresponds to a subdirectory for storing the data table files. Each data table corresponds to three files with a suffix of ". Frm ",". MyD ", and". Myi ".

(3) View the structure of the table

DESCRIBE statement: Used to display the structure of a table, which is the information that makes up the fields (columns) of the table. You need to specify the library name. Table name as a parameter; If you specify only the table name parameter, you need to switch to the target library by using the USE statement first.

3. Create and delete libraries and tables

(1) Create a new library

CREATE database name

(2) Create a new table

CREATE Table table name (field 1 name type, field 2 name type, ... , primary key (primary key name))

(3) Delete a data table

DROP TABLE statement: Used to delete a table in a library, you need to specify the library name. Table name "as a parameter; If you specify only the table name parameter, you must first switch to the target library by executing a" use "statement.

(4) Delete a database

DROP DATABASE statement: To delete the specified library, you need to specify the library name as the parameter.

4. Manage data records in a table

(1) Inserting data records

INSERT into statement: Used to insert a new data record into a table.

INSERT into table name (Field 1, Field 2, ...)  Values (the Value of field 1, the value of field 2,..) ;

When inserting a new data record, the part of the specified field in the INSERT statement can be omitted if the record completely includes the values of all the fields in the table.

(2) Querying data records

SELECT statement: Used to find the qualifying data record from the specified table.

SELECT Field name 1, field name 2,... From table name WHERE conditional expression

(3) Modify the data record

UPDATE statement: For modifying, updating data records in a table

UPDATE table name SET field Name 1 = field value 1[, field name 2 = field value 2] WHERE condition expression

(4) Delete data records

Delete statement: Used to delete the data records specified in the table.

DELETE from table name WHERE conditional expression

5. User authorization of the database

(1) Grant permission

Grant statement: Used to set access permissions for a database user. When the specified user name does not exist, the GRANT statement creates a new user, otherwise the GRANT statement is used to modify the user information.

GRANT permission list on library name. Table name to User name @ source address [identified by ' Password ']

Precautions:

Permissions list: Used to list the various database operations used by authorization, separated by commas, such as "select,insert,update". Use "all" to represent all permissions and authorize any action.

Library name. Table Name: You can use the wildcard character "*" to represent any.

User name @ Source Address: The source address can be a domain name, an IP address, or you can use the "%" wildcard, which represents all the addresses in an area network or network segment.

Identified by: When creating a new user, it can be omitted, indicating that the password is empty.

(2) View permissions

show GRANTS statement: Used to view authorization information for a database user and to specify which user object to view through the FOR clause.

SHOW GRANTS for User name @ Source Address

(3) Revoke permission

REVOKE statement: Used to revoke the database permissions of the specified user, the user can still connect to the MySQL server after revoking the permission, but will be prevented from performing the corresponding database operation.

REVOKE the permissions list on the database name. Table name from user name @ Source Address

6. Database Backup and Recovery

(1) Backing Up the database

method 1 : You can directly back up the directory /usr/local/mysql/data

Method 2: Use the dedicated backup tool mysqldump

Export some tables in the specified database: mysqldump [options] Library name [table name 1] [table Name 2] ... >/backup path/backup file name

Export one or more full libraries: mysqldump [Options]--Databases library name 1 [library Name 2] ... >/backup path/backup file name

Back up all libraries in the MySQL server: mysqldump [Options]--all-databases >/backup path/backup file name

(2) Recovering a database

mysql [options] [library name] [table name] </backup path/backup file name

When the backup file contains only a backup of the table, not the statement that created the library, you must specify the library name when you perform the import operation, and the destination library must exist.

If the full library information is already included in the backup file, you do not need to specify the library name when you perform the import operation.

MySQL Database basic Operations Command