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

name

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:

name

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:

name

Score<60

Zhang San

0

Zhang San

1

Zhang San

1

Li Si

1

Li Si

1

Wang Wu

1

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

Therefore

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

gk>=2;

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