delete and truncate in mysql

Delete

delete query delete the records of a table

DELETE LOW_PRIORITY 

FROM Orders
WHERE BookID=103
ORDER BY DateOrdered DESC
LIMIT 1;

In this statement, the rows to be deleted (those with a BookID of 103) are sorted according to the
DateOrdered column, in descending order, meaning that the rows with the most recent dates are deleted first. The LIMIT clause restricts the deletion to only one row. As a result, only the most recent order for the book with the BookID value of 103 is deleted.

DELETE statement to remove data from joined tables is not recommended for

InnoDB tables.  Instead, you should rely on the ON DELETE and ON CASCADE options specified in the foreign key constraints of the table definitions.

DELETE FROM Studios ;

delete all the records from Studios  table.

------------------------------------------------------------------

DELETE FROM Studios
WHERE StudID=’s108’ OR StudID=’s109’ ;

delete all the records from Studios  table where StudID=’s108’ OR StudID=’s109’ .

DELETE Orders.*
FROM Books, Orders
WHERE Books.BookID=Orders.BookID
AND Books.BookName=’Where I\’m Calling From’;

Truncate

The TRUNCATE statement removes all rows from a table. You cannot qualify this statement in any way. Any rows that exist are deleted from the target table. The following syntax describes how to create a TRUNCATE statement:

The following TRUNCATE statement removes all data from the Orders table:

TRUNCATE TABLE Orders;

• Another difference between the TRUNCATE statement and the DELETE statement is that the TRUNCATE statement starts the AUTO_INCREMENT count over again, unlike the DELETE statement. 

• TRUNCATE is generally faster than using a DELETE statement as well.

• The most important difference between the TRUNCATE statement and the DELETE statement is that the TRUNCATE statement is not transaction safe. A transaction is a set of one or more SQL statements that perform a set of related actions. The statements are grouped together and treated as a single unit whose success or failure depends on the successful execution of each statement in the transaction.

• TRUNCATE  delete all the records from table