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.
Most of the time when you're getting data from a table, you're looking for specific data, data that fits a particular, specific criteria. The where clause gives you the tools you need to get just the rows you're looking for, instead of the entire table. Selecting the world database here. In SID, and if we select everything from country like this, then we get the 239 rows with all of the data in them. This is a very rich table and it has a lot of information about each country.
For example, if you want to know which countries have a population under 100,000 it's easy to construct a query like this. Let's get the name and the continent and the population from country where population is less than 100,000, and we'll order by population descending. So when I press go, now we have a list of the name, the continent and the population in order of the population descending.
Just the countries with under 100000 of population. So there's forty rows returned, and we know now that forty rows in our table match that criteria. So there where clause takes a boolean expression. So this population is less than 100000, that's either true or false, that's called a boolean expression. Rows are returned only if the Boolean expression evaluates as true for that row. Now you'll notice that we're missing some countries from this list. We come down here towards the bottom.
You notice that we're missing some countries that really belong here, where the population is unknown or very small, countries like Antarctica, because those countries have null. In their population because it's an unknown quantity. That data was not available for the people who were creating this database. So, we add another expression to the where clause to include those countries. I'm going to start splitting this up into different lines to make this a little bit more readable here.
So put the from clause on one line and the where clause on the other line and this is SQL. You are allowed to do this as long as it's all terminated with that semi-colon. So my where clause now, I'm going to add an or population. IS NULL, like that. And when I press Go, we now get 47 rows. If we come down here to the bottom of the list, we'll see where there's a few with NULL, Antarctica, French Southern Territories, that's also in Antarctica.
Most of these are in Antarctica or in Oceania, and there's one in Africa, the British Indian Ocean Territory, in Africa. So those are the rows that have NULL in their population. So boolean operators, like this or, are used to bind different expressions together. Here I used and or operator to bind two boolean expressions, and I get the result when either of these two expressions are true. I could use an and operator to bind the expressions so that rows are returned only when both of the expressions are true. For example, instead of this or, I can say AND Continent = 'Oceania', and I press Go.
And now we just get the rows where the continent is equal to Oceania, and the populations under 100,000. So, we're only getting 16 rows now because both of those conditions have to be true, because they're bound together with the AND operator. The Where clause is used to filter results so you just get the rows that you want to see from your query. In the next couple of movies I'll show you some SQL operators that are specifically designed to provide additional power and flexibility for Where.
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.