MySQL Database Fundamentals and Practical Examples: Chapter 5300

Welcome to Chapter 5 of our MySQL Database Fundamentals and Practical Examples series. In this chapter, we will delve into the essential concepts of data manipulation with INSERT, UPDATE, and DELETE statements, providing practical examples to solidify your understanding.

INSERT Statement

The INSERT statement is used to add new rows into a MySQL table. Its syntax is:```sql
INSERT INTO table_name (column_name1, column_name2, ...) VALUES (value1, value2, ...)
```

Example:```sql
INSERT INTO students (name, age) VALUES ('John', 23);
```

UPDATE Statement

The UPDATE statement is used to modify existing rows in a MySQL table. Its syntax is:```sql
UPDATE table_name SET column_name1 = value1, column_name2 = value2, ... WHERE condition
```

Example:```sql
UPDATE students SET age = 24 WHERE name = 'John';
```

DELETE Statement

The DELETE statement is used to remove rows from a MySQL table. Its syntax is:```sql
DELETE FROM table_name WHERE condition
```

Example:```sql
DELETE FROM students WHERE age = 24;
```

Inserting Multiple Rows

You can insert multiple rows using a single INSERT statement. This is useful when you have a large number of rows to insert. To do this, use the following syntax:```sql
INSERT INTO table_name (column_name1, column_name2, ...) VALUES (value1_1, value1_2, ...), (value2_1, value2_2, ...), ...
```

Example:```sql
INSERT INTO students (name, age) VALUES ('John', 23), ('Sarah', 21), ('Jackson', 25);
```

Updating Multiple Rows

You can update multiple rows using a single UPDATE statement. This is useful when you want to perform the same update on multiple rows. To do this, use the following syntax:```sql
UPDATE table_name SET column_name1 = value1, column_name2 = value2, ... WHERE condition1 AND condition2 AND ...
```

Example:```sql
UPDATE students SET age = age + 1 WHERE age >= 21;
```

Deleting Multiple Rows

You can delete multiple rows using a single DELETE statement. This is useful when you want to perform the same deletion on multiple rows. To do this, use the following syntax:```sql
DELETE FROM table_name WHERE condition1 AND condition2 AND ...
```

Example:```sql
DELETE FROM students WHERE age < 18;
```

Common Pitfalls

Here are some common pitfalls to avoid when using INSERT, UPDATE, and DELETE statements:* Forgetting to specify the table name in the statement.
* Using the wrong column names in the statement.
* Using incorrect values in the statement.
* Not specifying the WHERE condition correctly when updating or deleting rows.
* Trying to insert or update rows with duplicate values in unique columns.

Conclusion

In this chapter, we have covered the basics of data manipulation in MySQL using INSERT, UPDATE, and DELETE statements. We have also provided practical examples to illustrate the usage of these statements. By following the guidelines and avoiding the common pitfalls, you can effectively perform data manipulation operations in your MySQL database.

2024-12-19

Previous：Kingsoft Cloud Computing: Powering the Future of Technology

Next：Sakura School Simulator Video Editing Tutorial: CapCut