You can use this example database schema for this question or otherwise, you may use your database table for testing.
CREATE TABLE `students` ( `id` int UNSIGNED NOT NULL, `stid` varchar(16) NOT NULL, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ); ALTER TABLE `students` ADD PRIMARY KEY (`id`);
Delete Records Using The Primary Key
This SQL query will delete every record which has a primary key
id less than or equal to 100.
DELETE FROM `students` WHERE `id` <= 100;
This SQL query will delete every record which has an
id greater than 900.
DELETE FROM `students` WHERE `id` > 900;
The above queries will delete records considering the value of
id of the table. The problem is, what happens when the
id of the table was not incremented exactly one by one. Or some of the records have been already removed. Or even the
id is not numerical. Under that kind of situation, the above-mentioned queries will not work as expected.
Delete Records Using
This query will sort the entire table by
created_at column and delete the first 100 records.
DELETE FROM `students` ORDER BY `created_at` ASC limit 100
We can use
DESC (descending) instead of
ASC (ascending) to invert the order of the above query, so it will delete the last 100 records.
DELETE FROM `students` ORDER BY `created_at` DESC limit 100