Simple operation of SQLite database

A SQLite introduction

SQLite is an embedded database, similar to a file system, that is with the program. and MySQL and other database programs and data separation is not the same.

Application scenario: Often used to save local configuration, similar to the local file system, so he embedded in Linux, Android, iOS and other systems, the use of the time does not need to reinstall. For example: local contacts on the phone, historical access records, etc. local data is basically saved using sqlite data.

Two-use

Create a table

Create table if not exists persion (_id integer primary key autoincrement, name text, salary real);

Query table structure sqlite_master This system table, used to store views, indexes, tables and other information

Select *from sqlite_master where type= ' table ';

Add data

Insert into person (name, salary) values (' Xiao Qiang ', 6000.0)

Fuzzy query + pagination + sorting

Select *from persion where name like '% small% ' ORDER by salarty ASC limit 0,3//ASC Ascending desc Descending

Common Party function min,max,avg,cout (*)

First group after aggregation, query the wages of the same person's information

Select *from Person where salary= (select salary from person group by salary have count (*) >1)

Create a View

Create View St as select _id,name from;

SQLite like clause: percent semicolon (%) represents 0, one or more digits or characters. An underscore (_) represents a single number or character. These symbols can be used in combination.

WHERE SALARY like ' 2_%_% '

Find any value starting with 2 with a length of at least 3 characters

Here is just the simplest record of operations, in addition to triggers, views, indexes and other related operations, can refer to the relevant information, here do not do too much elaboration

Simple operation of SQLite database