Shulou(Shulou.com)05/31 Report--

This article is about what query clauses are commonly used in MySQL. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

For data analysis, MySQL often uses queries, such as data sorting, grouping, de-duplicating, summarization, string matching and so on. If the queried data involves multiple tables, you need to join those tables.

Query clauses commonly used in MySQL:

* 1. * sort: order by

Sorting is done sequentially, for example, by querying data from a stuinfo table and sorting by age. The sql statement is:

Select * from stuinfo order by age

Query results:

As you can see, the query results are sorted in ascending order by age. If you want to sort them in descending order, you only need to add desc after them. The sql statement is as follows:

Select * from stuinfo order by age desc

Query results:

At this time, the query results are sorted in descending order of age.

The above sort is for numeric data. If it is a string, you can also sort it with order by. For example, sort by name. The sql statement is:

Select * from stuinfo order by stuname

Query results:

As you can see from the above, string sorting is actually sorted in ascending order according to the first letter, and of course, you can also sort the dates. Please think for yourself.

* 2. * grouping: group by

Grouping is achieved through the group by clause, for example, grouping by gender, the sql statement is.

Select gender,count (*) from stuinfo group by gender

Query results:

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

* 3. * deduplicated: distinct

De-repetition is a common operation in data processing, which is achieved through the distinct clause, such as querying all the cities where the students are located, which needs to be de-duplicated because some students are in the same city.

The sql statement is:

Select distinct city from stuinfo

Query results:

As you can see, to remove the weight is to add the distinct before the weight field.

* 4. * Null value: is null/is not null

A null value is a record that determines whether a field is empty or not. For example, to query a record whose city is empty, the sql statement is as follows:

Select * from stuinfo where city is null

Query results:

As you can see, the record with an empty query is actually the where condition followed by is null.

Conversely, if you query records for which the city is not empty, follow is not null. The sql statement is:

Select * from stuinfo where city is not null

Query results:

* 5. * Summary: count, maximum, sum, mean

Summary is actually counting, finding the maximum / minimum value, summing up, finding the mean, and so on.

The number of records in the most commonly used statistical table is realized through count. The sql statement is as follows:

Select count (*) from stuinfo

Query the maximum / minimum value of a field in the table through max/min, such as the maximum age of the query. The sql statement is:

Select max (age) from stuinfo

Query results:

The next question: look up the name of the oldest student. How to write the sql sentence?

This is a conditional query, the condition is that the age should be equal to the maximum age, according to this idea, write the sql statement:

Select stuname,age from stuinfo where age= (select max (age) from stuinfo)

Query results:

As you can see from the above, in fact, the maximum age is queried as a condition, and then the corresponding name and age are queried.

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

Summation, you can use sum, must be logarithmic data summation, similar to count, so there is no demonstration.

Finally, there is the average, using avg, for example, to query the average age of all students, the sql statement is:

Select avg (age) from stuinfo

Query results:

* 6. * alias: as

The alias, which is as, means to use, for example, to query the maximum, minimum, and mean values of age. The sql statement is:

Select max (age), min (age), avg (age) from stuinfo

Query results:

It doesn't look good to display the field name this way. We can give it an alias with as. The sql statement is:

Select max (age) as age_max,min (age) as age_min,avg (age) as age_avg from stuinfo

Query results:

* 7, * connection of tables

When the record to be queried involves two or more tables, the join of the table is required.

There are three kinds of connections to a table: inner join, left join, and right join.

(1) Internal connection: inner join

The inner join is equivalent to the intersection of two tables, as shown in the following figure.

For example, there are two tables: the student information table and the score sheet, with a common column: the student number.

Now I would like to find out the name and score of each student. The query result is shown in the following figure.

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

Because the student number is a common column, according to the student number connection, the above connection method is to obtain the same record of the student number in two tables, which is called internal connection.

The specific sql statements are:

Select sname,score fromtb1 inner join tb2 on tb1.sid=tb2.sid

Query results:

It is important to note that the connection condition is on.

(2) left connection: left join

The left join is based on the left table to match the records in the right table, and is empty if there is no corresponding matching record in the right table.

The left join is represented by the Venn diagram in the collection as follows.

Still use the student information table and score sheet mentioned above, the schematic diagram of the left link is as follows.

The sql statement connected to the left is:

Select sname,score fromtb1 left join tb2 on tb1.sid=tb2.sid

Query results:

(3) right connection: right join

The right join is based on the right table to match the records in the left table. If there is no corresponding matching record in the left table, it will be empty.

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

Still use the student information table and score sheet mentioned above. The schematic diagram of the right link is as follows.

The sql statement connected to the right is:

Select sname,score fromtb1 right join tb2 on tb1.sid=tb2.sid

Query results:

* 8. * string matching: like

Sometimes when looking for a string, you need to find a string that matches a matching pattern.

For example, in the table stuinfo, look for records in the city that contain the string 'an', and the sql statement is:

Select * from stuinfo where city like'% an%'

Note: the% in the match pattern means to match any string of any length.

* 9. * Collection: in

Use in to find records that belong to a collection.

For example, to find a record where the city is Beijing or Tianjin, the sql statement is:

Select * from stuinfo where city in ('Beijing','Tianjin')

Query results:

* 10. * conditional statement: having

The conditional statement here is having, which is similar to where, but is generally used with statistical functions.

For example, to find a city with an average age of less than 25 years old, the sql statement reads:

Select city from stuinfo group by city having avg (age)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.