Routine Use and Maintenance of MySQL Databases

I. Using a MySQL database:

1. view the database structure:

View the databases on the current server:

SHOWDATABASES statement: Used to list the databases contained in the current MySQL server. After initialization, the MySQL server creates three databases by default: test, mysql, and information_schema (the mysql database contains tables related to user authentication)

Mysql> show databases;

View tables in the currently used database:

SHOWTABLES statement: Used to list tables in the current database. Before the operation, you must USE the USE statement to switch to the database in USE:

Mysql> USE mysql;

Mysql> show tables;

Data files of the Mysql database are stored in the/usr/local/mysql/var/directory by default. Each database corresponds to a sub-directory for storing data table files;

Each data table corresponds to three files with the suffix ". frm" ". MYD" ". MYI ".

1. View the table structure:

• DESCRIBE statement: used to display the table structure, that is, the information of each field (column) in the table;

• You must specify "database name. Table Name" as the parameter;

• If you only specify the table name parameter, you must first USE the "USE" statement to switch to the target database.

Create and delete databases and tables:

B. Create a new database:

CREATEDATABASE statement: used to create a new database. You must specify the database name as the parameter.

Eg: Create a library named welcome:

Mysql> create database welcome;

The new empty database automatically generates an empty folder with the same name as the new database under the/usr/local/mysql/var/directory.

C. Create a new table:

CREATETABLE statement: used to create a table in the current database. You must specify the table name as a parameter and define fields used in the table.

Format: CREATETABLE table name (Field 1 Name type, Field 2 Name type ,... , PRIMARY kEY (PRIMARY kEY name ))

Create a users table in the welcome database. In the field definition section, DEFAULT is used to set the DEFAULT password string, and PRIMARY is used to set the PRIMARY key field name.

Mysql> USE welcome;

Mysql> create table users (user_name CHAR (16) not null, user_passwd CHAR (48) DEFAULT '', primary key (user_name ));

Delete A data table:

DROPTABLE statement: Used to delete tables in the database. You must specify "database name. Table Name" as the parameter. If you only specify the table name parameter, you must first switch to the target database through the "USE" statement.

Eg: Delete the users table in the welcome database:

Mysql> drop table welcome. users;

Delete A database:

DROPDATABASE statement: Used to delete a specified database. You must specify the database name as the parameter.

Eg: delete a database named welcome:

Mysql> drop database welcome;

3. Manage data records in a table:

Insert data records:

INSERTINTO statement: used to insert new data records into a table.

Format: INSERTINTO table name (Field 1, Field 2 ,...) VALUES (value of Field 1, value of Field 2 ,...)

Insert a record to the users table in the welcome Database: User www. The password is "123456 ":

Mysql> use welcome;

Mysql> insert into users (user_name, user_passwd) VALUES ('www ', PASSWORD ('123 '));

Eg: Insert a new record to the users table in the welcome database. The user qqq corresponds to the password "654321 ".

Mysql> use welcome;

Mysql> insert into users VALUES ('qqq', PASSWORD (654321 ));

Query data records:

SELECT statement: Used to find qualified data records from a specified table.

Format: SELECT field name 1, field name 2 ,... FROM table name WHERE condition expression

The wildcard "*" can be used for all fields. to list all data records, the WHERE condition clause can be omitted.

Eg: view all data records in the users table in the welcome database:

Mysql> select * from welcome. users;

The WHERE Condition Clause is essential for searching records based on specific conditions.

Eg: Find the record with the username qqq in the users table and output the username and password fields:

Mysql> SELECT user_name, user_passwd from welcome. users where user_name = 'qqq ';

Modify data records:

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 condition expression

Eg: Modify the username in the users table to www, set the password string to null, and verify:

Mysql> UPDATE welcome. users SET user_passwd = PASSWORD ('') WHERE user_name = 'www ';

Mysql> select * from welcome. users;

In the mysql database server, all user information used to access the database is stored in the user table of the mysql database.

Eg: Set the root password of the database user to 123456. This password must be used for verification when "mysql-uroot-p" is used to access the mysql database server again.

Mysql> UPDATE mysql. user SET password = PASSWORD ('000000') WHERE user = 'root ';

Mysql> flush privileges;

[Root @ ns ~] # Mysqldump-u root-p password '000000'

Delete data records:

DELETE statement: Used to DELETE the specified data records in the table.

Format: delete from table name WHERE condition expression

Eg: Delete the data record named www in the users table and verify that:

Mysql> delete from welcome. users WHERE user_name = 'www ';

Mysql> SELECT * FROM welcome. users;

On the mysql database server, empty users accessing the database from the local machine are added by default (user and password are empty ). These empty users should be deleted based on database security considerations.

Mysql> SELECT user, host, password FROM mysql. user WHERE user = '';

Mysql> delete from mysql. user WHERE user = '';

Recommended reading:

Percona_Toolkit introduction and installation-a required tool kit for MySQL Administrators

1 2 Next Page