Ali software test engineer hand in hand teaching – MySQL common 15 query clauses

​

The query results are sorted in descending order by age.

Select * from ‘order by’; select * from ‘order by’;

select * from stuinfo order by stuname;
Copy the code

Query result:

​

The above SQL statement groups by gender and gives the number of people in each group. Grouping by other fields is similar.

*3, * delete: distinct

Deduplication is a common operation in data processing, implemented through a distinct clause, such as querying all cities of students, which requires deduplication because some students are in the same city.

The SQL statement is:

select distinct city from stuinfo;
Copy the code

Query result:

​

As you can see, a query for an empty record is essentially a WHERE condition followed by is NULL.

SQL > select * from city where city is not null;

select * from stuinfo where city is not null;
Copy the code

Query result:

​

Query the maximum and minimum values of a field in a table by Max /min. For example, query the maximum age. The SQL statement is as follows:

select max(age) from stuinfo;
Copy the code

Query result:

​

As can be seen from the above, in fact, the maximum age query out as a condition, then query the corresponding name and age.

Leave a question: how to query the name of the youngest student?

Sum, just sum, must be a numeric sum, just like count, so I’m not going to show you.

For example, to query the average age of all students, the SQL statement is:

select avg(age) from stuinfo;
Copy the code

Query result:

​

Mysql > alter table alias = as; mysql > alter table alias = as;

select max(age) as age_max,min(age) as age_min,avg(age) as age_avg from stuinfo;
Copy the code

Query result:

​

For example, there are two tables: the student information table and the grade table, which have one column in common: the student number.

​

The names in the above query results need to be extracted from the first table and the results need to be extracted from the second table, which is called table join.

Because the student number is a common column, so according to the student number join, the above join method is to obtain two tables with the same number of records, called inner join.

The specific SQL statement is:

select sname,score fromtb1 inner join tb2 on tb1.sid=tb2.sid;
Copy the code

Query result:

​

The above mentioned student information table and score table are used again, and the diagram on the left is as follows.

​

(3) Right join

The right join is based on the right table to match the record in the left table. If there is no corresponding record in the left table, it is null.

The right join is represented by a Venn diagram in the set as follows.

​

SQL statement for right join:

select sname,score fromtb1 right join tb2 on tb1.sid=tb2.sid;
Copy the code

Query result:

​

*10, * conditional statement: having

The conditional statement having is similar to where, but is usually used with statistical functions.

For example, to find cities where the average age is less than 25, the SQL statement would be:

select city from stuinfo group by city having avg(age)<25;
Copy the code

Query result:

​

*12, * unite

Joins, also known as unions, are used to join the results of two queries and de-duplicate them.

For example, two tables have student numbers, use SQL statements to query the student numbers from these two tables, and then join.

select sid from tb1unionselect sid from tb2;
Copy the code

Query result:

​

*13, * date format: date_format

Date-time handling is also a common problem in SQL.

For example, to group data by year, month, or date, you need to extract the year, month, and date from the date and time.

For example, to get the year of birth of each student, the SQL statement would be:

select date_format(birthdate,'%Y') as birth_year from stuinfo;
Copy the code

Query result:

​

After obtaining the year and month, you can query statistics in groups according to the year and month.

Conditional branch: case statement

Conditional branching is a very important application in data statistics. In MySQL, conditional branching is expressed by case statements. Case statements have the following two uses.

Usage:

For example, to mark the records as first-tier cities and others based on the student’s city, the SQL statement is:

select distinct city, Elseif case city when 'Beijing' then 'Shanghai' then 'Shanghai' else 'end as city_level from stuinfo;Copy the code

Query result:

As can be seen from the above, case is followed by the condition when, that is, when the age meets any condition, it is divided into the corresponding category, and else represents other cases.

The entire case statement is equivalent to adding a field to the original table: age_level, by which we can group queries.

*15, * variable

Finally, let’s talk about variables in MySQL. Of course, there are several types of variables in MySQL, but I’m just going to say user variables, like @var.

For example, if we want to calculate how many years each student has until age 35 based on their age, we can define a variable and assign 35, then subtract the age from this variable to get the result.

First, define variable var, SQL statement:

set @var:=35;
Copy the code

Then, using this variable, the SQL statement is:

select @var-age as age_gap from stuinfo;
Copy the code

Query result: