In the Android platform, an embedded relational database is integrated-SQLite, it supportsNULL、INTEGER、REAL(floating point numbers),TEXT(String text) andBLOB(Binary object) data type, although only five data types are supported, it can actually accept data types such as varchar(n), char(n), decimal(p,s), etc., and will be converted into the corresponding five data types when performing operations or saving.
ex: You can store strings in fields of Integer type, or floats in Boolean type fields, or dates in character type fields. But! Fields defined as INTEGER PRIMARY KEY can only store 64-bit integers. In addition, when writing CREATE TABLE statements, the data type following the field name can be omitted;
create table person(personid integer primary key autoincrement,name varchar(20))//The type of name can be omitted;
select * from tablename where tiaojian group by grouping sentence having... order by sort sentence
select * from person
select * from person order by id desc/asc
select name from person group by name having count(*)>1
Pagination: select * from Account limit 5 offset 3 or select * from Account limit 3,5
Insert statement:insert into tablename(field list) values(value list)
insert into person(name,age) values("Livingstone",22)
Update statement:update tablename set field1=val1,field2=val2 where conditional statement
update person set name="Livingstone" where id = 10
Delete statement:delete from tablename where conditional statement
delete from person where id=10
Get the ID value that grows after adding records:select last_insert_rowid()
SQLiteDatabase provides special methods for adding, deleting, updating, and querying: insert(), delete(), update() and query();
The Insert() method is used to add data. The data of each field is stored using ContentValues. ContentValues is similar to MAP. Compared with MAP, it provides the put(String key, Xxx value) and getAsXxx(String key) methods corresponding to accessing data;
long rowid=db.insert("person",null,values);//Return the line number of the newly added record, which has nothing to do with the primary key id;
Regardless of whether the third parameter contains data or not, executing the Insert() method must add a record. If the third parameter is empty, an exception is the primary key will be added.
The Insert() method actually completes the data addition by constructing SQL statements. The second parameter is used to specify the name of the null field: If the third parameter values are Null or the number of elements is 0, since the Insert() method requires that a record with other fields other than the primary keys be added. In order to meet the SQL syntax requirements, the insert statement must give a field name, ex:insert into person(name) values(NULL). If the field name is not given, the insert statement is:insert into person() values(). For field names, it is recommended to use fields other than the primary key. If a primary key field of INTEGER type is used, execute insert into person(personid) After the insert statement of values(NULL), the value of the primary key field will not be NULL. If the third parameter values are not Null and the number of elements is greater than 0, the second parameter is set to null.