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 Select statement is used for most data retrieval in SQL. Here, I have the SID application open in my browser and I'm going to choose the world database and, I'm going to enter a simple Select statement, select and a single quote Hello, World and a closing single quote and a semicolon and I'll press the go button here and you see the result is Hello, World. The select statement is used for queries that will return a value or a set of values.
And select is used to display the result of any query. In this case, the query is a literal string with the words Hello, World in it. But it's still a query. It just doesn't come from the database. You could just as easily say select 1 plus 2. I still have the semi-colon there and you see that the query is 1.2 and the result is 3. So now let's get some rows from a table in the database. If I say select asterisk from and the table name is country, like that, semi-colon and we get all the rows from the country table.
You notice the status display says 239 rows returned and it took 13.5 milliseconds to do that. The astrix is a special token that means return all the available columns for this query and the from clause designates the source of the query, in this case a table named country. So this query returned 239 rows, all the rows from the country table. And you'll notice that it's in no particular order. We haven't specified an order, so it may or may not even be in the same order on your system as it is on mine.
SQL does not guarantee that data is returned in any particular order unless you specify an order. You can specify the order with the order by clause and so out here before the semi colon I'm going to type order by and we'll order it by the name column here. So that's this column here and if I press go, you'll see that now they are in alphabetical order by the name of the country. You can also specify which columns you want in your result by listing column names instead of the asterisks.
So if I come over here and select my asterisk, I can say I want the name column, and the life expectancy column, and if I press go you see, now I just get those 2 columns, the name and life expectancy. You can even specify an alternate name for each column in your result set. You notice that here, my result set has this heading which is the name in the result set. And I can specify that that's different than the name of the column by saying as Life Expectancy with a space in it.
And you notice that I have these same single quotes for the literal string, and when I say Go now my column heading which is actually the column name as returned in the results, and that now has Life Expectancy with a space in it. You can also use functions in your select queries for example, instead of all of this, I can say count, parentheses, asterisk, close parentheses. So now it says select, count, from country, order by, name, and inside the parentheses is the parameter to the function.
And this case the only parameter just has the one parameter which is an asterisk which means to count all the rows. And so when I say Go, you see it now has a count of 239 rows. So the count function returns the number of rows in the country table. Now again, if I just bring back our asterisk, so this is going to be all the columns, and if I say Go here you see we get 239 rows, like we did at first. Now, I can limit the number of rows in my result by using the limit clause.
And say LIMIT 5, and now we just get 5 rows returned, you see it says 5 rows up there in the status line, and we have 5 rows here in our results. You can specify an offset for the starting point with an offset clause, so if I say LIMIT 5 OFFSET 5. It'll start those 5, 5 in. So, I'll get whatever's after Andorra, which is Angola and I'll get the next 5. And this is really useful for paging applications, and you'll want to notice that the offset clause must be after the limit clause.
If I put these in the other order, that will not work. The select statement is how you get a result from an SQL query. It's fundamental in SQL and you'll use it for many purposes, here we've been using it to get rows of data from the table. You'll see a lot more examples throughout the rest of the course.
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.