Mysql query (note two)

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Table data transplant between the same 1 two structure

The Inset into table

The Select field 1, field 2, field... N from table two.

When setting up the database database code

Create database charset uft8 database name

Fuzzy query

%(any character, any number)

_(single character)

2 aggregation functions must be used with group by given group

For example, there is a table

Average performance is to use aggregate functions (AVG) to develop the scope of function basis

The 3 group, sort, filter again, take a few execution order

Where>group by >having>order by >limit

An average of 4 as indicated in the table 2 door and 2 door asking queries over students

Method: step by step query

Let's find out 2 door and 2 door more than students who fail

Then check the average of these students, 2 select statements

select name from stu where score<60  group by name having count(name)>=2

They get table


Zhang San

Li Si

Note that the order of execution of SQL

The first implementation of select name from stu where score<60

Get Table 2:


Zhang San

Zhang San

Li Si

Li Si

Wang Wu

After the packet and re screening

But it should be noted that although group by before having, but group by is used as a screening group of having

Then in Table 2 is having screening

The second step:

Find these students, the students most query filter again, because the first step to filter out the pass of the achievement, it destroys original data, so it has to have a screening

select name,avg(score) from stu where name in (select name from stu where

score<60  group by name having count(name)>=2) group by name;

Method two: use select to directly is the judgment and the characteristics of

Analysis: first run SQL queries

select name ,score<60 from stu;

Get under the table 3:



Zhang San


Zhang San


Zhang San


Li Si


Li Si


Wang Wu


In the field or the conditions of select after MySQL will contrast data sequentially with the table, such as score<60 would take 60 one by one with table score value

For example, name no field values to compare it to display it in the table values

From table three, found: sum (score<60) value is the number of failed subjects


select name ,avg(score),sum(score<60) as gk from stu group by name having


Aggregate functions perform simultaneously

Results table:

Obviously the method two than a more compact and efficient

To be continued....!

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Brian at November 26, 2013 - 10:57 PM