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]

当然，也可以省却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主键重复时则相当于使用update语句来更新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);

资料来源：http://blog.csdn.net/findsafety/article/details/50519790

 

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;

http://www.w3school.com.cn/sql/sql_view.asp

http://www.yiibai.com/sqlite/sqlite_views.html

 

二.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