1. What is CRUD?¶

In MySQL, CRUD is an abbreviation for four basic operations corresponding to the four core data operations in a database:

• Create (Create): Insert new data into the database
• Read (Read): Query existing data from the database
• Update (Update): Modify existing data in the database
• Delete (Delete): Remove data from the database

These operations are fundamental to MySQL data management. Mastering them allows you to easily implement data addition, deletion, modification, and querying.

2. Preparation: Create Test Table and Data¶

Before starting operations, we’ll create a simple table and test data for subsequent demonstrations. Open your MySQL client (e.g., Navicat, SQLyog, or command line) and execute the following SQL statements:

-- Create a student table
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,  -- Auto-incrementing primary key (unique identifier)
    name VARCHAR(50) NOT NULL,         -- Student name (cannot be empty)
    age INT,                           -- Age
    class VARCHAR(20)                  -- Class
);

-- Insert test data
INSERT INTO students (name, age, class) VALUES 
('小明', 18, '一班'),
('小红', 19, '二班'),
('小刚', 17, '一班'),
('小丽', 20, '三班');

There are now 4 records in the table, and all subsequent operations will be based on this table.

3. Create (Insert Data): INSERT Statement¶

Purpose: Add new records to the table.

Syntax:¶

INSERT INTO 表名 (字段1, 字段2, ...) VALUES (值1, 值2, ...);

Example 1: Insert a single record¶

Insert a new student “小强” (Xiao Qiang) with age 16 and class “二班” (Class 2):

INSERT INTO students (name, age, class) 
VALUES ('小强', 16, '二班');

Notes:
- The field list and value list must correspond one-to-one (same order and type).
- If inserting all fields, you can omit the field list (but ensure the value order matches the table definition):
INSERT INTO students VALUES (NULL, '小芳', 15, '四班');
(The id is an auto-incrementing primary key, generated automatically with NULL.)
- Values of string types must be enclosed in single quotes, while numeric types are written directly as numbers.

Example 2: Batch insert data¶

Insert multiple records at once:

INSERT INTO students (name, age, class) 
VALUES 
('小华', 17, '一班'),
('小亮', 18, '二班');

4. Read (Query Data): SELECT Statement¶

Purpose: Query data from the table, the most commonly used operation.

Basic Syntax:¶

SELECT 字段1, 字段2, ... FROM 表名;

Example 1: Query all data¶

Query all student information from the students table:

SELECT * FROM students;  -- * represents all fields

Example 2: Conditional query¶

• Filter students older than 18:

  SELECT name, age FROM students WHERE age > 18;

• Filter by class (exact match):

  SELECT * FROM students WHERE class = '一班';

• Combined conditions (age > 17 and class = 二班):

  SELECT * FROM students WHERE age > 17 AND class = '二班';

Example 3: Sorting and limiting results¶

• Sort by age in descending order (from largest to smallest) and take the top 2:

  SELECT * FROM students ORDER BY age DESC LIMIT 2;

• Sort by class in ascending order and deduplicate names:

  SELECT DISTINCT class, name FROM students ORDER BY class ASC;

Example 4: Fuzzy query¶

• Query students whose names contain “小” (Xiao):

  SELECT * FROM students WHERE name LIKE '%小%';

(% represents any number of characters, _ represents a single character)

5. Update (Modify Data): UPDATE Statement¶

Purpose: Modify existing records in the table.

Syntax:¶

UPDATE 表名 SET 字段1=值1, 字段2=值2, ... WHERE 条件;

Critical: Always include a WHERE condition! Otherwise, all records in the table will be updated!

Example 1: Update a single field¶

Change “小刚” (Xiao Gang)’s age to 18:

UPDATE students 
SET age = 18 
WHERE name = '小刚';

Example 2: Update multiple fields¶

Change “小丽” (Xiao Li)’s age to 21 and class to “四班” (Class 4):

UPDATE students 
SET age = 21, class = '四班' 
WHERE name = '小丽';

6. Delete (Remove Data): DELETE Statement¶

Purpose: Remove records from the table.

Syntax:¶

DELETE FROM 表名 WHERE 条件;

Critical: Always include a WHERE condition! Otherwise, all table data will be deleted!

Example 1: Delete records matching conditions¶

Delete students under the age of 16 (if any):

DELETE FROM students 
WHERE age < 16;

Example 2: Delete all data (use with caution!)¶

To empty the table, you can use a WHERE condition matching all rows (ensure safety first):

DELETE FROM students WHERE 1=1;  -- Equivalent to DELETE FROM students;

Notes:
- For quickly emptying the table structure, use TRUNCATE TABLE students; (irreversible and does not support WHERE conditions).
- DELETE is row-level deletion, while TRUNCATE rebuilds the table at the table level. Performance differences are minimal, but DELETE supports transaction rollback.

7. Summary and Precautions¶

1. WHERE condition is crucial:
   UPDATE, DELETE, and SELECT (filtering) all require a WHERE clause. Without it, all table data will be modified or deleted, causing data loss!

2. Strings and quotes:
   All string-type values (e.g., names, classes) must be enclosed in single quotes, e.g., '小明'.

3. Auto-incrementing primary key:
   The id field is an auto-incrementing primary key; no manual assignment is needed during insertion (use NULL or omit it).

4. Practice more:
   Execute the above examples in your MySQL client to observe results and familiarize yourself with the syntax rules.

CRUD are basic MySQL operations. Mastering them allows you to fulfill most database interaction needs. For advanced learning, you can explore more complex queries (e.g., JOINs) and transactions to build a solid foundation for further studies.