Query statements used in the Sqlit

The recent use of SQLite query is much more, including even table query and so on.

Record it. Lest forget!

1. Select the first 10 according to the time order:

SELECT * from Mybill ORDER BY createtime DESC limit 10.

2. The and of the price:

Select SUM (price) as Sum from Mybill;

3. This is a classification form, there is a bill table. According to the classification of the main classification of the classification of Statistics re-order:

Select SUM (mysum), tb.pid from "
+ "(select SUM (Price) as mysum,consumetype.title as tt,consumetype.parentid as PID from Mybill"
+ "LEFT join Consumetype on Mybill.typeid =consumetype.id"
+ "GROUP by Mybill.typeid", as TB Group by tb.pid ORDER by sum (mysum) desc ";

4. This is a classification table, there is a bill table, according to the classification of sub-classification statistics re-order:

Select SUM (Price), consumetype.title from Mybill "
+ "LEFT join Consumetype on Mybill.typeid =consumetype.id"
+ "GROUP by Mybill.typeid Order by sum (price) desc";

5. Compare the time to choose:

SELECT * from Mybill where Createtime < datetime (' 2015-05-19 ')

The corresponding other time function methods such as the following:

*   Select DateTime (' Now ');
* Select DateTime (' 2006-10-17 00:20:00 ', ' +1 hour ', ' -12 minute ');
*Select Date (' 2006-10-17 ', ' +1 Day ', ' +1 Year ');
*Select DateTime (' Now ', ' start of Year ');
*Select DateTime (' Now ', ' start of Month ');
*Select DateTime (' Now ', ' start of Day ');
*Select DateTime (' Now ', ' +10 hour ', ' start of day ', ' +10 Hour ');
*Select DateTime (' Now ', ' localtime ');
*Select DateTime (' Now ', ' +8 Hour ');

Query statements used in the Sqlit