Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
MySQL is by far the most popular database management system for small- to medium-sized web projects. In this course, Bill Weinman provides clear, concise tutorials that guide you through creating and maintaining a MySQL database of your own. Bill explores the basic syntax, using SQL statements to create, insert, update, and delete data from your tables. He also covers creating a new database from scratch, as well as data types, transactions, subselects, views, and stored routines. Plus, learn about the multi-platform PHP PDO interface that will help you connect your database to web applications.
The delete statement is used to remove rows from a table. In this lesson I'll be using the scratch database, and I'll be continuing with the test table that we've been using for the last few lessons. So I'll say select star from test. And we can see that those are the rows in the test table. Now, if I want to delete a row from this table, I can use delete from, test, where a equals two, semi-colon.
And this will delete the row where this condition is true, which is just that one row, sense of humor and all, and if I. Select go, we've still got our select statement in there, so we'll see the result. And that row with the two in it is gone. So just as with the update statement, this where clause is used to select which rows are going to be deleted. So, it's a good idea to test the where clause, right? If I say select. Star from test where a equals, and let's put a one in there now.
Then you'll see that that selects just these two rows. And if I go ahead and do a delete, then those two rows'll be deleted and our result will show the null and the three and the four. So if I say here, delete from test where a equals one. Now two rows have been deleted, we can see in our status two rows affected, three rows returned, and we have a null row and all the rows that don't have a 1 or a 2 in A. So it's a good idea to test that where clause with a select if you're typing at a console like we are here, because if we don't included the where clause at all.
All of these rows will be deleted, if I press go now, now all of these rows have been deleted and there's no result left for the select. So we're not going to be using this test table anymore for the rest of the chapter. So I'm going to go ahead and. Delete the actual table which is done with the drop command. We'll see drop later so if I say drop table test that'll actually delete the table from the database and that returns the database to the original state for the rest of the chapter.
So the delete statement is used to delete rows from a table you use the where clause to select the rows to be deleted. Just as you did with the update statement.
There are currently no FAQs about MySQL Essential Training.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.