Android ---- query () parameter analysis in sqlite, androidsqlitequery

Android ---- query () parameter analysis in sqlite, androidsqlitequery

Public Cursor query (String table, String [] columns, String selection, String [] selectionArgs, String groupBy, String having, String orderBy, String limit)
Query the given table, returning a Cursor over the result set.


Parameters
String table ----------- The table name to compile the query against. table to be queried and table to be queried.
String [] columns --------- A list of which columns to return. which column is returned? If the parameter is null, all columns are returned (null is not encouraged to prevent reading data from being used) (for example, selectionArgs)


String selection --------- which row of the filter is returned, in the SQL WHERE format. If it is set to null, all rows of the table are returned.
A filter declaring which rows to return, formatted as an SQL WHERE clause (excluding the WHERE itself). Passing null will return all rows for the given table.
String [] selectionArgs ----------- the selection field may use '? 'To add some additional parameters. The selectionArgs field is to fill The conditions of The selection field (The values will be bound as Strings.). The following function:
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 the Array
Null, null, null );
Boolean exists = cursor. moveToFirst ();
Cursor. close ();
Return exists;
}
String groupBy ----------- how to group A filter --- if it is set to null, A filter declaring how to GROUP rows, formatted as an SQL GROUP BY clause (excluding the group BY itself) is not grouped ). passing null will cause the rows to not be grouped .?????
String having ------------ filter condition A filter declare which row groups to include in the cursor, if row grouping is being used, formatted as an SQL HAVING clause (excluding the HAVING itself ). passing null will cause all row groups to be pinned ded, and is required when row grouping is not being used .????????????
(GroupBy and having do not know much about it. See the reposted below)


String orderBy ------ sorting, in the same format as the SQL ORDER. Use the default (unordered unonder) for setting null.
Cursor cursor = mDb. query (SEARCHHISTORY_TABLE_NAME,
New String [] {SEARCHHISTORY_KEY },
Null,
SEARCHHISTORY_DATE + "DESC", /// <--- DESC/ASC: Descending/ascending (Format: String orderBy = "_ id desc ")
Integer. toString (size ));///----


String limit -------- number of returned rows. If this parameter is set to null, no restrictions are imposed.


Return Value: A Cursor object, which is positioned before the first entry (the first Entry ). note that Cursors are not synchronized, see the documentation for more details. (Non-synchronous, so add public synchronized xxx () {} in the function (){});
////----------------------------------

[Java]View plaincopy

1. /// Create this table
2. String createSearchhistoryTabelQuery = "create table if not exists"
3. + SEARCHHISTORY_TABLE_NAME + "("
4. + SEARCHHISTORY_KEY + "VARCHAR (200) primary key not null ,"
5. + SEARCHHISTORY_DATE + "datetime not null"
6. + ");";
9. Db.exe cSQL (createSearchhistoryTabelQuery );
11. Public synchronized void addSearchhistoryItem (String key ){
12. // Set the format to SQL date time
13. SimpleDateFormat dateFormat = new SimpleDateFormat ("yyyy-MM-dd HH: mm: ss ");
14. Date date = new Date ();
15. ContentValues values = new ContentValues ();
16. Values. put (SEARCHHISTORY_KEY, key );
17. Values. put (SEARCHHISTORY_DATE, dateFormat. format (date ));
20. MDb. replace (SEARCHHISTORY_TABLE_NAME, null, values );
21. }
22. Public synchronized ArrayList <String> getSearchhistory (int size ){
23. ArrayList <String> history = new ArrayList <String> ();
26. Cursor cursor = mDb. query (SEARCHHISTORY_TABLE_NAME,
27. New String [] {SEARCHHISTORY_KEY },
28. Null,
29. SEARCHHISTORY_DATE + "DESC ",
30. Integer. toString (size ));
31. While (cursor. moveToNext ()){
32. History. add (cursor. getString (0 ));
33. History. add (cursor. getString (1 ));
34. }
35. Cursor. close ();
38. Return history;
39. }
40. Public synchronized void clearSearchhistory (){
41. MDb. delete (SEARCHHISTORY_TABLE_NAME, null, null );
42. }

----------------------------------------------------------------------
Use count () for group by and HAVING in SQL statements ()
Before introducing the group by and HAVING clauses, we must first talk about a special function in SQL: aggregate function,
For example, SUM, COUNT, MAX, and AVG. The fundamental difference between these functions and other functions is that they generally work on multiple records.


Select sum (population) FROM bbc


SUM is used in the population field of all returned records. The result is that only one result is returned for this query, that is, all
Total population of the country.




Having is the filtering Condition After grouping. The grouped data groups are filtered again.
Where indicates filtering before grouping.


BY using the group by clause, SUM and COUNT functions can be used for a GROUP of data.
When you specify group by region, only one row of data belonging to the same region is returned.
That is to say, all fields except region (region) in the table can only return a value after SUM, COUNT, and other aggregate function operations.


HAVING clause allows us to filter data of groups after grouping.
The WHERE clause filters records before aggregation, that is, before the group by clause and HAVING clause.
The HAVING clause filters group records after aggregation.


Let's still understand the group by and HAVING clauses through specific instances, and use the bbc table introduced in section 3.


SQL instance:


1. shows the total population and total area of each region.
SELECT region, SUM (population), SUM (area)
FROM bbc
Group by region
First, return records are divided into multiple groups BY region, which is the literal meaning of group. After grouping, Aggregate functions are used to calculate different fields (one or more records) in each group.


2. The total population and total area of each region are displayed. Only those regions with an area exceeding 1000000 square meters are displayed.
SELECT region, SUM (population), SUM (area) 7]; Z & I! T % I
FROM bbc8 F4 w2 v (P-f
Group by region
Having sum (area)> 1000000
Here, we cannot use where to filter more than 1000000 of the regions, because such a record does not exist in the table.
On the contrary, the HAVING clause allows us to filter the data of each group after grouping.