[MySQL] Note(1) SELECT, FROM, WHERE, GROUP-BY, HAVING, ORDER-BY, LIMIT

Last Updated on 2021-05-13 by Clay

The article records how to use the following SQL instructions of:

• SELECT
• FROM
• WHERE
• GROUP BY
• HAVING
• ORDER BY
• LIMIT

There is a priority order for these 7 instructions, as shown in the figure:

The meaning of each instruction is:

In this case, it may still be unclear how to operate, so let's take a look some example.

Switch Database

First, open MySQL Workbench. If you have other programs that can practice SQL, they can also be used.

If you don’t have any programs like MySQL installed on your computer, maybe you can refer to: MySQL download and installation steps note

show databases;

Output:

information_schema
mysql
performance_schema
sakila
sys
world 

Here we first select the "world" database.

use world;

So we switch to this "world" database.

Then we show the tables in the database:

show tables;

Output:

city
country
countrylanguage 

If there are these tables, it means that there is no problem with the default database.

SELECT

FROM

These two instructions need to be used together. SELECT is followed by which column to select, and FROM is followed by which table we are querying.

For example:

select * from city;

Output:

I omitted some information to show the results.

We just used * to print all the fields; you can also select only the field names you want to query.

select ID from city;

Output:

In this way, we only have to print out the ID field.

If you want to print multiple fields:

select ID, Name from city;

Output:

WHERE

WHERE instruction like if in other programming languages, is used to make conditional judgments. For example, the program names we set to print all start with CountryCode as "AFG".

select Name, CountryCode from city where CountryCode = 'AFG';

Output:

GROUP BY

GROUP BY is more difficult to explain. For example, we can group the returned results.

select
    Continent, Name, max(GNP)
    from country
    group by Continent;

Output:

HAVING

HAVING is to look for eligible conditions. For example, we take the example just now and continue to do it. If today we want to see the result only with GNP exceeding 500,000, we can add the HAVING instruction at the end.

select
    Continent, Name, max(GNP)
    from country
    group by Continent
    having max(GNP) > 500000;

Output:

We filtered out data with a smaller GNP.

ORDER BY

ORDER BY, as the name implies, is to sort the data display method. For example, for the information we just query, we use Capital to sort.

select
    Capital, Continent, Name, max(GNP)
    from country
    group by Continent
    order by Capital desc;

Output:

We can see that the current sorting method is to sort Capital from largest to smallest.

LIMIT

LIMIT is a instruction to limit the number of displayed data.

select
    Capital, Continent, Name, max(GNP)
    from country
    group by Continent
    order by Capital desc
    limit 2;

Output:

we set LIMIT to 2, and our result will only print 2 data.