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

 

 

 

 

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页