mysql add, delete, check and modify

MySQL is a very commonly used relational database management system. It provides rich functions and flexible operation methods to meet the data management needs of various enterprises and individuals. Among them, addition, deletion, query and modification are the basic operations of MySQL. Let's introduce these operations in detail below.

1. Connect to MySQL

Before performing MySQL operations, we need to connect to the MySQL server first. There are many ways to connect, such as using command line tools or GUI tools (such as Navicat or HeidiSQL); among them, we use the command line tool here for demonstration.

First, enter the following command on the command line to connect:

mysql -h hostname -u username -p

Where, hostname is The address of the MySQL server, username is the name of the database account. Once entered, the system will prompt for a password. After entering the password, we successfully connected to the MySQL server.

2. Add: Insert data

MySQL supports a variety of ways to insert data, such as using the INSERT INTO statement and the LOAD DATA INFILE statement.

1. Use the INSERT INTO statement

The INSERT INTO statement is used to insert data into the table. The format is as follows:

INSERT INTO table_name(column1, column2, column3, ...) VALUES(value1, value2, value3, ...)

Among them, table_name is the name of the target table, column1, column2, column3 are the column names in the table, value1, value2 and value3 are the data values ​​to be inserted.

For example, we want to insert data into the table named student, including student number, name, gender and date of birth. The command is as follows:

INSERT INTO student(id, name , gender, birthday) VALUES(10001, 'Zhang San', 'Male', '2000-01-01')

2. Use the LOAD DATA INFILE statement

LOAD DATA The INFILE statement is used to import data from an external file into a MySQL table. The format is as follows:

LOAD DATA INFILE 'filename' INTO TABLE table_name

where filename is external The path to the file, table_name is the name of the target table.

For example, we want to import data from a file named data.txt into a table named student. The command is as follows:

LOAD DATA INFILE 'data.txt' INTO TABLE student

3. Delete: Delete data

MySQL supports the use of DELETE statement and DROP TABLE statement to delete data.

1. Use the DELETE statement

The DELETE statement can delete data in the table according to conditions. The format is as follows:

DELETE FROM table_name WHERE condition

Among them, table_name is the name of the target table, and condition is the condition for deleting data.

For example, if we want to delete the record with the middle school ID 10001 in the table named student, the command is as follows:

DELETE FROM student WHERE id=10001

2. Use the DROP TABLE statement

The DROP TABLE statement is used to delete the entire table. The format is as follows:

DROP TABLE table_name

where table_name is The name of the table to delete.

For example, if we want to delete the table named student, the command is as follows:

DROP TABLE student

4. Query: Query data

MySQL supports a variety of ways to query data, such as using SELECT statements and WHERE clauses, LIMIT to limit the number of query results, etc.

1. Use the SELECT statement

The SELECT statement is the most commonly used query statement in MySQL. The format is as follows:

SELECT column1, column2, column3, ... FROM table_name

Among them, column1, column2, and column3 are the column names to be queried, and table_name is the table name to be queried.

For example, we want to query the student ID, name, and gender in the table named student. The command is as follows:

SELECT id, name, gender FROM student

2. Use the WHERE clause

The WHERE clause is used to specify query conditions. The format is as follows:

SELECT column1, column2, column3, ... FROM table_name WHERE condition

Among them, condition is the query condition.

For example, we want to query the record with the middle school ID 10002 in the table named student. The command is as follows:

SELECT * FROM student WHERE id=10002

3. Use LIMIT to limit the number of query results

The LIMIT statement is used to limit the number of query results. The format is as follows:

SELECT column1, column2, column3, ... FROM table_name LIMIT offset, count

Among them, offset is the offset of the query result, and count is the number of query results.

For example, if we want to query the first 10 records in the table named student, the command is as follows:

SELECT * FROM student LIMIT 0, 10

5. Change: Update data

MySQL uses the UPDATE statement to update the data in the table. The format is as follows:

UPDATE table_name SET column1=value1, column2=value2, .. . WHERE condition

Among them, table_name is the name of the target table, column1 and column2 are the column names to be updated, value1 and value2 are the values ​​to be updated, and condition is the condition for updating the data.

For example, we want to change the name of the record with school number 10002 in the table named student to "李思", the command is as follows:

UPDATE student SET name='李4' WHERE id=10002

6. Summary

MySQL's addition, deletion, query, and modification are basic operations. When operating, we must pay attention to specifying the correct target table, column names, and conditions, and using appropriate statements and methods. At the same time, we can also use command line tools or GUI tools to perform these operations, which is very flexible and convenient. In short, being proficient in the addition, deletion, query, and modification operations of MySQL is essential basic knowledge for learning and applying the MySQL database.

The above is the detailed content of mysql add, delete, check and modify. For more information, please follow other related articles on the PHP Chinese website!