Query () parameter analysis in the android----SQLite

Public Cursor query (string table, string[] columns, string selection, string[] Selectionargs, String groupBy, String Havi Ng, string by-clause, string limit)
Query the given table, returning a Cursor over the result set.


Parameters
String table-----------The table name to compile the query against. Which table, which table to query.
string[] Columns---------A list of which columns to return. Returns which column, if the argument is null, returns all columns (not encouraged to be null to prevent the data from being read out) (see Selectionargs for example)


String selection---------Returns the filter for the row, in the format SQL where, set to NULL, to return all rows of this table.
A filter declaring which rows to return, formatted as a SQL WHERE clause (excluding the where itself). Passing NULL would return all rows for the given table.
String[] Selectionargs-----------may use '? ' in the selection field. form to add some extra parameters, the Selectionargs field of the selection field is populated with the criteria (the values will is bound as Strings.). The following functions:
Public synchronized Boolean mediadirexists (String path) {
cursor cursor = mdb.query (Dir_table_name,
New string[] {Dir_row_path},
Dir_row_path + "=?",
New string[] {path},///<-----may be multiple fills, so use an array
NULL, NULL, NULL);
Boolean exists = Cursor.movetofirst ();
Cursor.close ();
return exists;
}
String GroupBy-----------A filter, how the grouping---is set to null is not grouped a filter declaring how to group rows, formatted as a SQL group by CLA Use (excluding the GROUP by itself). Passing null would cause the rows to is grouped.?????
String has--------------filter conditions for aggregation after grouping a filter declare which row groups to include in the cursor, if row grouping is being Used, formatted as an SQL has clause (excluding the having itself). Passing null would cause all row groups to be included, and was required when row grouping was not being used. ????????????
(GroupBy and having had not quite understood. See reprint below)


String is ordered by order------, in the same format as SQL. Set NULL to use the default (unordered Unonder) arrangement.
cursor cursor = mdb.query (Searchhistory_table_name,
New string[] {Searchhistory_key},
NULL, NULL, NULL, NULL,
Searchhistory_date + "DESC",///<---DESC/ASC: Descending/Ascending (format is string by order = "_id DESC")
integer.tostring (size)); ///----


The number of rows returned by the String limit--------, set to NULL means there are no restriction clauses.


Returns: A Cursor object, which is positioned before, the first entry (one entry). Note that Cursors is not synchronized, see the documentation for more details. (non-synchronous, so the function plus public synchronized xxx () {});
////----------------------------------

[Java]View Plaincopy

2. Create this table
4. String createsearchhistorytabelquery = "CREATE TABLE IF not EXISTS"
6. + Searchhistory_table_name + "("
8. + Searchhistory_key + "VARCHAR ($) PRIMARY KEY not NULL,"
10. + searchhistory_date + "DATETIME not NULL"
12. + ");";
18. Db.execsql (Createsearchhistorytabelquery);
22. public synchronized void Addsearchhistoryitem (String key) {
24. //Set the format to SQL date time
26. SimpleDateFormat DateFormat = new SimpleDateFormat ("Yyyy-mm-dd HH:mm:ss");
28. Date date = new Date ();
30. Contentvalues values = new Contentvalues ();
32. Values.put (Searchhistory_key, KEY);
34. Values.put (Searchhistory_date, Dateformat.format (DATE));
40. Mdb.replace (Searchhistory_table_name, null, values);
42. }
44. public synchronized arraylist<string> getsearchhistory (int size) {
46. arraylist<string> history = new Arraylist<string> ();
52. cursor cursor = mdb.query (Searchhistory_table_name,
54. New string[] {Searchhistory_key},
56. NULL, null, null , NULL,
58. Searchhistory_date + "DESC",
60. integer.tostring (size));
62. While (Cursor.movetonext ()) {
64. History.add (Cursor.getstring (0));
66. History.add (cursor.getstring (1));
68. }
70. Cursor.close ();
76. return to history;
78. }
80. public synchronized void Clearsearchhistory () {
82. Mdb.delete (Searchhistory_table_name, null, null);
84. }

----------------------------------------------------------------------
Use COUNT () for group by and have in SQL statements
Before we introduce the group BY and HAVING clause, we must first talk about a special function in the SQL language: the aggregate function,
such as Sum, COUNT, MAX, AVG, and so on. The fundamental difference between these functions and other functions is that they generally function on more than one record.


SELECT SUM (population) from BBC


The SUM function here is on the population field of all returned records, and the result is that the query returns only one result, that is, all
Total population of the country.




Having is the filter after grouping (group by), and then filtering within the grouped data group
Where is the filter before grouping


By using the GROUP BY clause, you can have the sum and COUNT functions work on data that belongs to a group.
When you specify group by regions, a set of data that belongs to the same region will only return one row of values.
That is, all fields except region (regions) in the table can only be returned with a value after the SUM, count, and other aggregate function operations.


The HAVING clause allows us to filter the group data after the group.
The WHERE clause filters the records before aggregating. That is, it works before the GROUP BY clause and the HAVING clause.
The HAVING clause filters the group records after aggregation.


Let's take a concrete example to understand the group BY and having clauses, as well as the BBC table introduced in section Iii.


SQL instance:


First, show the total population and area of each area.
SELECT region, sum (population), sum (area)
From BBC
GROUP by region
The return record is divided into groups with region first, which is the literal meaning of group by. After the group is finished, the different fields (one or more records) in each group are calculated with an aggregate function.


Second, show the total population and area of each area. Only those areas with more than 1000000 area are shown.
SELECT region, sum (population), sum (area) 7]; z& i! t% I
From Bbc8 F4 W2 V (p-f
GROUP by region
Have SUM (area) >1000000
Here, we cannot use where to filter more than 1000000 of the area, because there is no such record in the table.
Instead, having clauses allows us to filter groups of data

Query () parameter analysis in the android----SQLite