DELETE Command

Purpose

DELETE removes specific rows from a table based on given conditions.

Characteristics

• Deletes data row by row
• Supports rollback (transactional)
• Triggers row-level triggers
• Preserves table structure
• Slower than TRUNCATE when deleting all rows

Syntax and Examples

DELETE FROM table_name WHERE condition;

-- Example: Remove employees with salary below 30000
DELETE FROM staff WHERE salary < 30000;

-- Example: Delete all rows from the table
DELETE FROM staff;

Performance Considerations

• Can be slow for large tables, especiallly when deleting all rows
• Generates individual row locks, potentially affecting concurrency
• Logs each deleted row, which may lead to large transaction logs

TRUNCATE Command

Purpose

TRUNCATE quickly removes all rows from a table.

Characteristics

• Deletes all rows in a single operation
• Usuallly non-rollbackable in most databases (limited transactional support)
• Does not fire row-level triggers
• Resets identity/auto-increment columns
• Much faster than DELETE for removing all rows
• Maintains table structure

Syntax and Examples

TRUNCATE TABLE table_name;
-- Example: Clear all data from the staff table
TRUNCATE TABLE staff;

Performance Considerations

• Very fast, particularly for large tables
• Minimizes log recording (only logs page deallocations)
• Immediately releases table space in most databases

DROP Command

Purpose

DROP removes an entire table structure from the database.

Characteristics

• Deletes the complete table, including structure, indexes, and constraints
• Non-rollbackable (non-transactional)
• Removes all data and table definition from the database
• Faster than both DELETE and TRUNCATE

Syntax and Examples

DROP TABLE table_name;

-- Example: Remove the entire staff table from the database
DROP TABLE staff;

-- Example: Drop table only if it exists (avoids errors)
DROP TABLE IF EXISTS staff;

Performance Considerations

• Extremely fast as it only removes table metadata from system directories
• Minimizes log recording
• Immediately releases all space used by the table

Comparison Table

Feature	DELETE	TRUNCATE	DROP
Scope	Rows	All rows	Entire table
Speed	Slowest	Fast	Fastest
Rollback	Yes	Usually no	No
Triggers	Fired	Not fired	Not applicable
Conditions	Can use WHERE clause	Cannot use WHERE clause	Not applicable
Table structure	Preserved	Preserved	Deleted
Auto-increment reset	No	Yes	Not applicable
Space release	Not immediate	Immediate in most cases	Immediate
Transaction log	Logs each row	Minimal logging	Minimal logging

Usage Scenarios

• DELETE: When you need to remove specific rows or require rollback capability.
• TRUNCATE: When you want to quickly delete all data from a table while preserving its structure.
• DROP: When you want to completely remove a table from the database, including its structure and data.

Additional Notes

1. Permissions: DROP typically requires higher privileges than DELETE or TRUNCATE.
2. Referential integrity: Foreign key constraints may block TRUNCATE operations or require cascading DELETE.
3. Auditing: If tracking individual row deletions is needed, use DELETE instead of TRUNCATE.
4. Recovery: Data deleted via TRUNCATE or DROP is generally harder to recover than data deleted via DELETE.

Note that specific behaviors may vary between different database management systems, so always consult your database's documentation for accurate details.

Auto-increment Behavior with Table Operations

CREATE TABLE book_catalog 
( 
book_id INT NOT NULL AUTO_INCREMENT, 
book_title VARCHAR(255) NOT NULL, 
book_description VARCHAR(255) NOT NULL, 
PRIMARY KEY(book_id), 
UNIQUE(book_title) 
)
ENGINE=INNODB AUTO_INCREMENT=1001 DEFAULT CHARSET=gbk;

• book_id: This is an integer field that doesn't allow null values and auto-increments. When inserting new records, book_id automatically increases, starting from 1001 due to the AUTO_INCREMENT=1001 setting.

Using TRUNCATE

TRUNCATE TABLE book_catalog;

This command removes all data from the table and resets the auto-increment ID to its initial value (typically 1). To start from a specific value like 1001 after truncating, use:

ALTER TABLE book_catalog AUTO_INCREMENT = 1001;

Using DELETE

DELETE FROM book_catalog;
INSERT INTO book_catalog (book_title, book_description) VALUES ('Book Title 2', 'Description for Book 2');
INSERT INTO book_catalog (book_title, book_description) VALUES ('Book Title 3', 'Description for Book 3');

DELETE removes all rows but logs each deletion, which can be slower for large datasets. The auto-increment counter retains its last assigned value and doesn't reset.

Using DROP

DROP TABLE book_catalog;

This commend completely removes the table structure and all associated data, indexes, triggers, and constraints. The auto-increment state is lost along with the entire table definition.