Android⾯试整理之SQLite数据库——sql语句和常⽤函数
（⼀）
⼀.SQLite中常⽤的sql语句
1. SQLite数据库如何查询表table1的第20条到第30条记录？
select * from table1 limit 19, 11；
ps：SQLite与MySql⼀样，select语句也⽀持limit字句。在使⽤limit字句时，要注意记录从0开始，20条到30条记录数为11。
扩充知识：SQLite的LIMIT⼦句⽤于限制由SELECT语句返回的数据数量（通俗地讲，⽤于查询数据库后，按照限制返回数据记录条数）
（1）带有LIMIT⼦句的SELECT语句的基本语法如下：
SELECT column1,column2,column3….columnN (*) FROM table_name LIMIT [no of rows]
即：select [数据列] from [表名] limit [限制返回记录数⽬]
举例：如有⼀个表COMPANY，其字段和已有记录如下：
A．若要求只从表中提取6条记录——
select * from COMPANY limit 6;
查询结果为：
B．若要求从⼀个特定的便宜开始查询，例如：从第3位开始提取3个记录-----
select * from COMPANY limit 3 offset 2;
查询结果为：
（2）即LIMIT⼦句与OFFSET⼦句⼀起使⽤时的语法为：
SELECT column1,column2…columnN FROM table_nameLIMIT [no of raws] OFFSET [row num]
sql语句替换表中内容当然，也可以省却OFFSET关键字，例如上⾯的例⼦可写为：
SELECT * FROM COMPANY ORDER BY ID LIMIT 2,3;
2. Sqlite不存在某条记录就插⼊，存在就更新，只⽤⼀条sql语句实现？
replace into table1 (id, name) VALUES (1,’bill’);
或者replace into table1(id,name) value(1, ’bill’);
为什么不⽤insert语句（本题直接⽤insert语句肯定不⾏，insert语句遇到约束冲突后就会抛出异常，在SQLite中提供了replace语句，可使⽤replace语句来替换insert，这样，当id主键重复时则相当于使⽤u
pdate语句来更新name字段值）
(1) 表不存在则创建
create table if not exists table1_student ( _id Integerprimary key autoincrement, name Text, age Integer);
(2)表中的数据不存在时插⼊数据
Insert into table1_student(name, age) select ‘bill’ , 25where not exists (select * from table1_student where name=’bill’ and age=25 ); ---重复执⾏多次，仍只有⼀条数据
(3)当表中的数据不存在则插⼊，存在则更新（使⽤replace语句）
这时候创建表的sql语句为：create table if not exists table1_student(_id Integer primary key,name Text, age Integer)
不存在插⼊，存在更新sql语句为replace into table1_student(_id, name, age) VALUES (1, ’bill’, 25);-----重复执⾏多次，仍只有1条数据
(4)将年龄改为35，发现并没有插⼊新数据-----修改⼀条记录中⾮主键字段，只更新对应数据
replace into table1_student(_id, name, age) VALUES (1，’bill’, 35);
(5)将学⽣id(主键)改为2，则发现插⼊了⼀条新数据--------修改主键，相当于新插⼊⼀条记录
replace into table1_student(_id, name, age) VALUES (2,’bill’,35);
3.如何才能将table1的表结构和数据复制到表table2上？
复制表的⽅法很多，最简单的⽅法就是使⽤create table 语句复制表的结构和数据
create table table2 as select * from table1;
(1)复制表结构+数据
create table table_name_new as select * fromtable_name_old;
(2)只复制表结构
create table table_name_new as select * from table_name_oldwhere 1=2;
或者
create table table_name_new like table_name_old;
(3)只复制表数据
若两个表结构⼀样：
Insert into table_name_new select * from table_name_old;
若两个表结构不⼀样：
Inset into table_name_new(column1, column2….) selectcolumn1,column2….from table_name_old;
SELECT INTO也有这样的功能：
如：SELECT * INTO table_name-new FROM table_name_old;
SELECT *INTO table_name_new FROM table_name_old WHERE 1=2;
4.请写出SQL语句？（创建视图即查询table1表的所有记录）
create view if not exits view1 as select * from table1;
⽹上查到的都是：create view view_name as select * from table_name where conditions;
或CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHEREcondition;
⼆.SQLite中的函数
1. SQLite中的核⼼函数
（1）abs(X)函数：
例：请说出abs(X)函数的返回值有哪⼏种情况？
由于abs(X)函数会⾃动尝试将当前字段值转换为整型，再取绝对值，因此，只要当前字段值可以成功地转换为数值型的值，abs(X)函数就可以成功返回正确的结果。因此，该函数的返回值会根据字段X的值类型不同⽽不同；此外，还要考虑abs(X)函数抛异常的情况。
总结起来，abs(X)函数的返回值共有如下⼏种情况：
1）若X为正值和0，则abs(X)函数返回X本⾝；
2）若X为负值，则abs(X)函数返回X的绝对值，也就是-X；
3）若X为NULL，则abs(X)函数也返回NULL；
4）若X为字符串、Blob等不能转换为数值的类型，则abs(X)函数返回0；不过，若⾮数值类型可以转换为数值abs(X)函数仍会取该值的绝对值，如X=字符串”-34”，则abs(“-34”)=34
5）若X的值超出64位这个数值范围，abs(X)函数则会抛出⼀个溢出错误。
（2）changes()函数：获取最近⼀次影响的记录⾏数；
例：如何获取最近⼀次执⾏SQL语句所影响的记录条数，并写出相应的SQL语句？
insert into tables values(1,”bill”);//最近⼀次执⾏的是这条insert语句
//返回insert语句影响的记录条数
select changes();
（3）coalesce()函数：返回第⼀个不为null的字段值
如何只⽤⼀条sql语句从图（1）所⽰表中查询出图（2）所⽰结果？
分析：对⽐两个图可知，本题的实质就是查询第⼀个不为null的字段值的问题。当使⽤selcet语句查询表table1的id和name字段时，要求显⽰第⼀个不为null的字段值。
select coalesce(name,id) as value fromtable1;
此外，该题⽤ifnull函数也可以实现。
select ifnull(name,id) as value fromtable1;
ps: coalesce()函数和ifnull()函数的区别：
两者功能相同，只是ifnull()函数只有2个参数，相当于有2个参数的coalesce函数
（4）length()函数：返回字符串的长度
例：请说出length(X)函数再以下⼏种情况下返回怎样的值？
1） X为字符串类型---------------------返回字符串中包含的字符数
2） X为Blob类型的值-----------------返回⼆进制数据中包含的字节数
3） X为NULL-----------------------------返回NULL
4） X为数值型---------------------------length会将X作为字符串处理，例如length(123)的值为3
（5）like函数和like⼦句----使⽤通配符对字符串进⾏匹配
例：请分别使⽤like⼦句和like函数查询table1中name字段值包含bill的记录？
like⼦句：select *from table1 where name like ‘%bill%’;
like函数：select *from table1 where like (‘%bill%’, name);
ps: like(X,Y)函数与”Y like X”⼦句的功能完全相同,like函数与”Y like X”⼦句都可以使⽤通配符对字符串进⾏匹配（例
如，“%”表⽰0个或任意多个字符串；“_”表⽰任意的单个字符），但两者在X和Y的位置正好相反，此处X表⽰含通配符的字符串，Y表⽰待匹配的字符串。
like(含通配符包含某个⼦串的字符串, 待匹配的字段值)
“待匹配的字段值” like “含通配符包含某个字串的字符串”
扩充知识：like(X,Y,Z)：有3个参数的like函数，第三个参数X为转意符号
使⽤场景：当想要查询的字段值中包含了通配符，就需要指定转意字符，SQLite中提供的⼀个escapte⼦句⽤于指定转意符，与之对应的like函数可以通过第三个参数指定这个转意符。例如，匹配以百分号%开头的字符串，可⽤如下SQL语句：
select like (‘a%%’, ‘%abcd’ , ‘a’);------------------------------执⾏后，结果返回1（true）
分析：此处’a’被第三个参数设置为转意符，因此’a%%’中第⼀个%不会被看作通配符，⽽会被当作普通字符处理，第⼆个%才是通配符。所以，此条SQL语句匹配的是所有以”%”开头的字符串，”%abcd”符合匹配条件。
（6）nullif(X,Y)函数：
例：请阐述nullif(X,Y)函数的⽤法，并举例说明？
当X和Y不同时，nullif函数返回X；当X和Y相同时，nullif函数返回NULL，例如：
--输出20
select nullif(20,30);
--输出NULL
select nullif(20,20);
（7）substr()函数：截取字符串的⼦串
例：请阐述substr函数的⽤法，并举例说明？
substr()函数⽤于截取字符串的⼦串，substr有substr(X,Y)和substr(X,Y,Z)两种形式，X为原字符差，Y为要截取的⼦串的第⼀个字符在原字符串的位置（原字符串的起始位置为1），Z为截取的⼦字符串的长度。
若不指定Z,substr函数会截取Y以后的所有字符串；
若Y为正值，表⽰的起始位置从原字符串左侧开始
若Y为负值，表⽰的起始位置凑个原字符串的右侧开始
若Z为负数，会取Z的绝对值
举例如下：
--从”abcdefg”的第2个位置截取后⾯所有的字符串，结果是”bcdefg”
select substr(‘abcdefg’,2);
--从”abcdefg”的第2个位置截取长度为3的字符串，结果为”bcd”
select substr(‘abcdefg’,2,3);
--从”abcdefg”右侧第2个位置截取所有的字符串，结果为”fg”
select substr(‘abcdefg’,-2);
--从”abcdefg”右侧第4个位置截取长度为2的字符串，结果为”de”
select substr(‘abcdefg’,-4,2);
（8）typeof()函数：获取当前字段的数据类型
例：如何知道当前字段是什么数据类型？
select typeof(field1) from table1
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论