A little bit of database-----Day 6 data grouping and data paging

A little bit of database-----Day 6 data grouping and data paging

----Reprint Please specify the Source: Coder-pig

Introduction to this section:

This section continues to learn the grouping of data in data queries in SQL ~

First build a table, record a few data, by reviewing the contents of the previous study ~

The T_worker have been used for a few days, so let's get a new watch today. T_product Commodity table:

Build Table sql:

CREATE TABLE t_product (PId integer,pname varchar), pkind varchar, pcity varchar, pprice NUMERIC (10,2), Fnum Integer,primary KEY (PId))

Insert several records inside:

Insert into T_product (pid,pname,pkind,pcity,pprice,fnum) VALUES (1, ' Dove chocolate ', ' Candy class ', ' Beijing ', 33.80,100); INSERT INTO T_ Product (Pid,pname,pkind,pcity,pprice,fnum) VALUES (2, ' 53 degrees Moutai flying ', ' drinks ', ' Guizhou ', 839.00,20); INSERT into T_product (PId, Pname,pkind,pcity,pprice,fnum) VALUES (3, ' Yili pure Milk *24 ', ' milk ', ' Inner Mongolia ', 72.00,50); INSERT into T_product (Pid,pname,pkind, Pcity,pprice,fnum) VALUES (4, ' Jie Rou paper ', ' Commodity class ', ' Guangzhou ', 17.90,100); INSERT into T_product (pid,pname,pkind,pcity,pprice,fnum VALUES (5, ' Vinda paper ', ' Commodity class ', ' Zhongshan ', 13.90,100), INSERT into t_product (pid,pname,pkind,pcity,pprice,fnum) VALUES (6, ' Sun roll ', ' Commodity class ', ' Zhongshan ', 20.80,100); INSERT into T_product (pid,pname,pkind,pcity,pprice,fnum) VALUES (7, ' Ferrero chocolate ', ' Candy class ', ' Italy ', 89.00,100); INSERT into T_product (pid,pname,pkind,pcity,pprice,fnum) VALUES (8, ' golden Arowana corn Oil 1L ', ' cereals and foodstuffs ', ' Guangzhou ', 59.90,100);

query all records in the following table:

SELECT * from T_product

Final Result:

Well, then the material is ready.

1. What is Data grouping:

Answer: in the previous section we have learned the function of the aggregate operation of Count, you need to query the table for some age, such as the company

How many employees are 18 years old, direct select COUNT (*) from t_worker WHERE Fage = 18;

But if you want to look at the number of people in each age group, this is a very inefficient approach, and this is when data grouping is great.

It's time!

Data grouping is the data in the table, grouped by the fields in the table according to individual needs, and then aggregated

arithmetic, that's what it says. Min,max,sum,avg,count Calculation ~ You can think of this group as a A temporary result set ,

and in SQL, we can use the GROUP by clause to complete the grouping of the data!

Of course, not to say absolutely with the aggregation function to use, but the purpose of the group is also for the aggregation operation, the following comparison, directly fetch data and

Data grouping

Ordinary:

SELECT pcity from T_product

GROUP BY Origin:

SELECT pcity from T_product GROUP by pcity

Take a look at the results after the run:

Maybe you've seen a little bit of this here, so write this SQL statement a little more complicated:

SELECT Pcity,count (pcity) as ' quantity ' from T_product GROUP by pcity

run under:

Believe you see here, you have realized the convenience of data grouping ~

2. Data grouping and aggregation functions ① with where in the group

For example: We want to query the table of the "commodity" category in the city of the group and the number, just add the WHERE statement:

SELECT Pcity,count (pcity) as ' quantity ' from t_product WHERE pkind = ' Commodity class ' GROUP by pcity  

Run the above SQL:

As mentioned above, it is very simple, just if the WHERE clause, but one thing to note is:

The GROUP by clause needs to be placed after the SELECT statement and placed in the where statement, or it will be an error!

By swapping the WHERE clause with the GROUP BY clause in the above SQL statement, the following error will occur!

② specifying multiple columns

The previous writing is grouped according to a column, of course you can I can specify more than one column, to achieve the "group of groups" effect,

In order, the first grouping column is grouped first, then the second ... And so on

For example, the following SQL

SELECT Pcity,pkind,count (*) as ' number ' from  t_product GROUP by Pcity,pkind

Run the above SQL:

If the position of pcity and Pkind is reversed

SELECT Pcity,pkind,count (*) as ' number ' from  t_product GROUP by pkind,pcity

Run the above SQL:

In addition, the fields of the select query need to be in the aggregate function or GROUP BY clause, such as the SQL written above:

SELECT Pcity,pkind,pprice from T_product GROUP by Pkind,pcity ORDER by Pprice ASC

The following error will be reported:

③ Example: Find out by Origin group, maximum and minimum value

SQL is as follows:

Run SQL:

3.HAVING statement:

From the above two points we learned to group the data, and then learn to filter the grouping

Use the HAVING statement in SQL! For example, a group that filters more than 1 records

Direct use of where COUNT (*) > 1 is wrong, because an aggregate function cannot be used in a WHERE statement!

Instead, use the HAVING statement:

SELECT pcity from T_product GROUP by pcity have COUNT (*) > 1

Run the preceding SQL statement:

The function of the above SQL is: first the data in the table is grouped by pcity, and then filter out the portion of the number of record bars greater than 1!

Also, use the having statement to be aware of:

The 1.HAVING clause needs to be placed after the GROUP BY clause!

The 2.HAVING clause cannot contain an ungrouped column name, which is not written in the following group by

If you still need to include it, this time you can use where!

4. Database Paging

I believe everyone has heard this noun, because when we query the data, the results of the query a lot of time, we

The query results are usually paged, for example, 10 records per page, users can click "Next Page", "previous page"

To see the different pages, the principle is to limit the number of result set rows of the query! Data paging in view of different DBMS implementations

, this shows only a demonstration of the MySQL restricted result set, using the LIMIT keyword, and sqlite also

Support this keyword, so, you understand ~, the approximate code is as follows:

Define two variables: current page numbers and the number of data displayed per page int goindex = 0;int PageSize = 10;//defines a method for displaying data private void ShowData () {//Display data on your own requirements}// The total number of data in the calculated table is private int getcount () {ResultSet rs = ExecuteSQL ("Select COUNT (*) as ITEMCOUNT from T_product"); return Rs.get Int ("ITEMCOUNT");} Defines a query on the current page of the private void  Dosearch () {//Calculate query page Starting line count of string beginindex = (Goindex * PageSize). toString (); String size = pagesize.tostring (); Result rs = ExecuteSQL ("select * from T_product LIMIT" + StartIndex + "," + size); ShowData ();} event handler for the button class Myclick implements Onclicklistener{private void OnClick (View v) {switch (V.getid ()) {//click on home case R.id.btnfirst:goindex = 0;dosearch ();//Click last Case r.id.btnlast:goindex = GetCount ()/pagesize;dosearch ();//Next R.id.btnnext:goindex = Goindex + 1;dosearch ();//prev Case r.id.btnfirst:goindex = Goindex-1;dosearch ();}}}

The principle of paging implementation is generally the above code, to see their own needs to improve it ~

5. Suppress data duplication

For example, if we want to retrieve the categories of goods in the table, we can directly

Run SQL:

At this point, the results are duplicated, and you can use SQL to provide the

Distinct can, usage is very simple, directly after the Select Add it!

To run the SQL statement:

Another thing to note:

Distinct is for the entire result set to suppress data repeatedly, rather than for each column!!!

Like what:

Run SQL:

A little bit of database-----Day 6 data grouping and data paging