Shulou(Shulou.com)06/01 Report--

MySQL database system is a typical application of C/S (client/server) architecture. To access MySQL database, special client software is needed. On Linux, the simplest and easiest MySQL client software is its own mysql command tool.

Log in to MySQL Server

MySQL database default administrator username is "root" and password is empty. To log in to the MySQL database on this machine as root without a password, the following is done

In the case of a password, the "-p" option should also be used for password verification

After successful verification, the database operation environment with prompt "mysql>" will be entered, and the user can enter various operation statements to manage the database. Each MySQL operation statement ends with a semicolon ";", which can be typed case-insensitive, but it is customary to capitalize the keyword part of MySQL statements.

Modify database password

Method 1: Modify the information in the user table in the mysql library

Method 2: Executed in a Linux command-line environment, using the mysqladmin tool to set the password

2. View database structure

(1) View all databases in the current server

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

MySQL database database files are stored in the/usr/local/mysql/data directory, and each database corresponds to a subdirectory for storing data table files. Each data table corresponds to three files with suffix names ".frm,"".myd," and ".myi."

(3) Look at the structure of the table

DESCRIBE statement: used to display the structure of the table, that is, the information of each field (column) that constitutes the table. "Library name." Table name "as a parameter; if only table name parameter is specified, switch to the target library through USE statement first.

3. Create and delete libraries and tables

1) Creating a new library

CREATE DATABASE database name

(2) Creating a new table

CREATE TABLE table name (field 1 name type, field 2 name type,..., PRIMARY KEY

(3) Delete a data table

DROP TABLE statement: used to delete tables in the library, you need to specify "library name. If you specify only the table name parameter, you need to switch to the target library by executing the "USE" statement.

(4)Delete a database

DROP DATABASE statement: Used to delete the specified library, requires the library name as an argument.

4. Data records in management tables

(1)insert data record

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

INSERT INTO table name (field 1, field 2,...) VALUES(value of field 1, value of field 2,..) ;

When inserting a new data record, the part of the insert statement that specifies the field can be omitted if the record includes the values of all the fields in the table.

(2)query data record

SELECT statement: used to find data records that meet the criteria from a specified table.

SELECT Field Name 1, Field Name 2,... FROM table name WHERE conditional expression

(3)modify data record

UPDATE statement: used to modify and update data records in a table

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

(4)delete data record

Delete statement: Used to delete a specified data record from a table.

Delete FROM table name WHERE conditional expression

5. User authorization for database

(1)grant permissions

GRANT statement: Used to set database user access rights. If the specified username does not exist, the GRANT statement creates a new user; otherwise, the GRANT statement modifies user information.

GRANT Permission List ON Library Name. Table Name TO User Name @ Source Address [IDENTIFIED BY 'Password']

Notes:

Permission list: Used to list authorized database operations, separated by commas, such as "select,insert,update". Use "all" to indicate all permissions, which can be authorized to perform any operation.

Name of library. Table Name: Wildcard " * " can be used to represent any.

User name @ Source address: Source address can be domain name,IP address, or "%" wildcard, indicating all addresses in a LAN or segment.

IDENTIFIED BY: When creating a new user, it can be omitted to indicate that the password is empty.

(2)view permission

SHOW GRANTS statement: Used to view the authorization information of database users. For clause specifies the user object to view.

SHOW GRANTS FOR Username @ Source Address

(3) Revocation of authority

REVOKE statement: Used to revoke the database permissions of the specified user. After the permissions are revoked, the user can still connect to the MySQL server, but will be prohibited from performing corresponding database operations.

REVOKE Permission List ON Database Name. Table Name FROM User Name @ Source Address

6. Backup and Recovery of Database

(1)backup database

Method 1: You can directly backup the directory/usr/local/mysql/data

Method 2: Use the specialized backup tool mysqldump

Export a subset of tables from a specified database: mysqldump [options] library name [tablename1] [tablename2]... > /backup path/backup file name

Export one or more complete libraries: mysqldump [options] - - databases Library name 1 [Library name 2]... > /backup path/backup file name

Backup all libraries in MySQL server: mysqldump [options] - -all-databases > /backup path/backup file name

(2)recover the database

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

When the backup file contains only table backups and does not include the statement creating the library, the library name must be specified when performing the import operation, and the target library must exist.

If the backup file already includes complete library information, you do not need to specify a library name when importing.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.