无标题文章

In SQL, the ORDER BY keyword is used to sort results in ascending or descending order according to the values of one or more columns.

By default ORDER BY will sort in ascending order. If you want to sort the results in descending order, you can use the DESC keyword. For example,

SELECT title

FROM films

ORDER BY release_year DESC;

ORDER BY can also be used to sort on multiple columns. It will sort by the first column specified, then sort by the next, then the next, and so on. For example,

SELECT birthdate, name

FROM people

ORDER BY birthdate, name;

sorts on birth dates first (oldest to newest) and then sorts on the names in alphabetical order.The order of columns is important!

Now you know how to sort results! Often you'll need to aggregate results. For example, you might want to get count the number of male and female employees in your company. Here, what you want is to group all the males together and count them, and group all the females together and count them. In SQL, GROUP BY allows you to group a result by one or more columns, like so:

SELECT sex, count(*)

FROM employees

GROUP BY sex;

This might give, for example:

sexcount

male15

female19

Commonly,GROUP BY is used with aggregate functions like COUNT() or MAX() . Note that GROUP BY always goes after the FROM clause!

This means that if you want to filter based on the result of an aggregate function, you need another way! That's where theHAVINGclause comes in. For example,

SELECT release_year

FROM films

GROUP BY release_year

HAVING COUNT(title) > 10;

shows only those years in which more than 10 films were released.

Remember, if you only want to return a certain number of results, you can use the LIMIT keyword to limit the number of rows returned

In the real world however, you will often want to query multiple tables. For example, what if you want to see the IMDB score for a particular movie?

In this case, you'd want to get the ID of the movie from thefilmstable and then use it to get IMDB information from thereviewstable. In SQL, this concept is known as a join, and a basic join is shown in the editor to the right.

SELECT title, imdb_score

FROM films

JOIN reviews

ON films.id = reviews.film_id

WHERE title = 'To Kill a Mockingbird';