SQLite的使用详解
SQLite可以使用的数据类型
SQLite的数据类型使用来说明表中的属性的值属于什么类型。(或者说,属性声明了类型,值就必须是这个类型的)。在 SQLite 中,值的数据类型与值本身是相关的,而不是与它的容器相关。
每个存储在 SQLite 数据库中的值都具有以下存储类之一:
- NULL, 值是一个 NULL 值。
- INTEGER, 值是一个带符号的整数,根据值的大小存储在 1、2、3、4、6 或 8 字节中。
- REAL, 值是一个浮点值,存储为 8 字节的 IEEE 浮点数字。
- TEXT, 值是一个文本字符串,使用数据库编码(UTF-8、UTF-16BE 或 UTF-16LE)存储。
- BLOB, 值是一个 blob 数据,完全根据它的输入存储。
SQLite支持列的亲和类型概念。任何列仍然可以存储任何类型的数据,当数据插入时,该字段的数据将会优先采用亲缘类型作为该值的存储方式。SQLite目前的版本支持以下五种亲缘类型:
- TEXT, 数值型数据在被插入之前,需要先被转换为文本格式,之后再插入到目标字段中。
- NUMERIC, 当文本数据被插入到亲缘性为NUMERIC的字段中时,如果转换操作不会导致数据信息丢失以及完全可逆,那么SQLite就会将该文本数据转换为INTEGER或REAL类型的数据,如果转换失败,SQLite仍会以TEXT方式存储该数据。对于NULL或BLOB类型的新数据,SQLite将不做任何转换,直接以NULL或BLOB的方式存储该数据。需要额外说明的是,对于浮点格式的常量文本,如”30000.0”,如果该值可以转换为INTEGER同时又不会丢失数值信息,那么SQLite就会将其转换为INTEGER的存储方式。
- INTEGER ,对于亲缘类型为INTEGER的字段,其规则等同于NUMERIC,唯一差别是在执行CAST表达式时。
- REAL, 其规则基本等同于NUMERIC,唯一的差别是不会将”30000.0”这样的文本数据转换为INTEGER存储方式。
- NONE, 不做任何的转换,直接以该数据所属的数据类型进行存储。
下面列出了当创建 SQLite3 表时可使用的各种数据类型名称,同时也显示了相应的亲和类型:
- INTEGER:INT、INTEGER、TINYINT、SMALLINT、MEDIUMINT、BIGINT、UNSIGNED BIG INT、INT2、INT8。
- TEXT:CHARACTER(20)、VARCHAR(255)、VARYING CHARACTER(255)、NCHAR(55)、ATIVE CHARACTER(70)、NVARCHAR(100)、TEXT、CLOB。
- NONE:BLOB、no datatype specified。
- REAL:REAL、DOUBLE、DOUBLE PRECISION、FLOAT。
- NUMERIC:NUMERIC、DECIMAL(10,5)、BOOLEAN、DATE、DATETIME。
注: Boolean 数据类型。SQLite 没有单独的 Boolean 存储类。相反,布尔值被存储为整数 0(false)和 1(true)。
SQLite的约束条件
表中的属性除了规定值的类型外还可以添加约束条件,约束是在表的数据列上强制执行的规则。插入表中的值除了要是属性申明的值意外,还要满足一定的约束条件。这确保了数据库中数据的准确性和可靠性。以下在 SQLite 中常用的约束:
- NOT NULL 约束:确保某列不能有 NULL 值。(即插入的值不能为null)
- DEFAULT 约束:当某列没有指定值时,为该列提供默认值。(数值型直接写,字符型加‘’。如 defaule 210 ; defaule ‘默认字符’)。
- UNIQUE 约束:确保某列中的所有值是不同的。(即这一列的所有值不能相同,可一null,但只能有一个)。
- PRIMARY Key 约束:唯一标识数据库表中的各行/记录。(这一列的所有值不能存在相同,并且一个也不能为null)。
- CHECK 约束:CHECK 约束确保某列中的所有值满足一定条件。(插入这列的值只有满足check的条件时,才会插入成功。如:check values > 100 ; 值大于100时才会插入)。
创建表、删除表、更新表
创建表:sql = “create table TabelName if not exists (<列名> <数据类型> <该列的约束条件> ,<列名> <数据类型> <该列的约束条件> ,<列名> <数据类型> <该列的约束条件> ,<列名> <数据类型> <该列的约束条件> ,<列名> <数据类型> <该列的约束条件>);
例: “create table Product if not exists (product_id primary key char(4) not null , product_name varchar(100) not null , product_type vachar(32) not null , sale_price int , purchase_price int , regist_date date )”;
注:表可以在任何时候创建。一个数据库可以有多张表。删除表:sql = “drop table TableName;
- 更新表
- 表重命名:sql = ” alter table tableName rename to newTableName ” ;
注:经过测试,修改表名,新的表名不能和旧的表名一样;旧的表名一定要存在。 - 怎加字段:sql = ” alter table tableName add column (这个可以写可以不写) address char(20)not null ;
注:1.不能与已存在的字段的字段名相同;2.column 关键字可以不用写;3 .SQLite 不支持一次增加多列;所以需要怎加多列时,要写多次。 - 删除字段:sql = “alter table tableName drop column address” ;
注:会报错,SQLite不支持删除列和修改列。
- 表重命名:sql = ” alter table tableName rename to newTableName ” ;
插入数据
sql = “ insert into tableName ( 列名1 , 列名2 , 列名3 , 列名4 )values ( values1 , values2 , values3 , valuers4 ) ;
- 1.每次插入一行;
- 2.列名和值用逗号分开,分别放在()内,这种形式成为清单(列清单和值清单);例:insert into tableName ( column1 , column2, column3 , column4 , … ) values ( value1 , value2 , value3 , value4 , …) ;
- 3.插入表中所有的值是,表名后面的列清单可以省略不写 , values 还是要写的。例:nsert into tableName values ( value1 , value2 , value3 , value4 ) ; 前提:列值清单中的数要与表中的列数一样 , 即每列值都要写。
- 4.插入null 值是,需要在值清单中 显示写出null。例:insert into tableName ( column1 , column2, column3 , column4 , … ) values ( value1 , value2 , null , value4 , …) ;
- 5.插入默认值可以通过两种方法实现,一:在values值清单中显示的使用default关键字表示该列值插入该列的默认值;二:在列清单中该列不写出来。
例:insert into tableName ( column1 , column2, column3 , column4 , … ) values ( value1 , value2 , default , value4 , …) ; - 6.使用insert select 向一个表中插入另一个表的值。(复制数据的效果)。
例:insert into tableName1 (column1 , column2, column3 , column4 , … ) select column1 , column2, column3 , column4 , … from tableName2 where ? ;
把 tableName2 中的数据复制到 tableName1 中 ,前提: tabelNmae1 和 tabelName2 的列的属性也一样的, 列名可以不一样。
删除数据
sql = “delete from tableName where ? ” ;
- 删除数据 , 删除数据需要指定:删除哪张表中的数据(即from ),删除的条件是什么(where 即什么样的行被删除)例: delete from tableName where ? where 为true 的行被删除。
- deleta from tableName ; 语句中如果忘记写了 from 或是 多了列名都会出错。
例: delete tableName ; 因为 delete 删除的对象是 行 而不是表 ( drop tableName) ;
例: delete from tableName column1 ; 因为delete删除的对象是行而不是列 - 没有条件就是全部删除。
更新数据
sql = ” updata from tableName set column1 = values1 ,column2 = values2 , column3 = values3 where ? ;
- 更新数据需要指出:更新哪一张表中的数据?(即from )。更新哪些列的值,值是什么?更新的条件是什么?(不指明条件就更新表中所有行)。
例: update tableName set column1 = values1 , column2 = values12 ; 更新所有行
例: update tableName set column1 = values1 , column2 = values12 where ? ; where为true的行更新。
选择语句
sql = ” select * from tableName ; // 选择所有列;
sql = ” select column1 .column2 , column3 from tableName ;// 选择部分列。
- select * from tableName ; 选择所有列,选出来列的顺序和表中的声明的顺序一样。
- select column1 , column2 , column3 from ; 选出部分列,选出来的结果列的顺序与列清单写的顺序一致。
- select 语句中可以为列设置别名。例: select column1 AS “第一列的别名(随便写) , column2 AS “第二列的别名(随便写)” ; 注: 别名一定要用 “” 括起来 。使用了别名后,在查询出来的结果集中就就不是列名了,而是别名。
- 对选出来的结果集去重,distinct 关键字的使用。例:select distinct column1 from tableName ;
- distinct去重(重是对列的值而言的),即使用了distinct 的列的值存在重复值时,查询出来的就只有一个(行),这列重复值的其他行就没有被选出来。( 去重是去调指定列值相同的行)。
- distinct 也可以用多列,当写了多列时,只有在写出的所有列的值同时相等时 distinct 才成立。
- distinct 只能写在 列名的前面 ,并且只能写在第一列的前面。若写为 :select column1 ,distinct column2 from tableName ; 会报错,应写为: select distinct column1 ,column2 from tableName ;。
- 当使用distinct关键字时,表中有值重复的两行,那查询出来的是那一行呢?仔细想一下其实那一行都没有关系,因为使用了distinct 关键字后 ,只有所有的写出来的所有的列的值都重复时才会成立,选取出来的集中也只有那写出来的那些列。因为distinct只能写在第一列的前面。
- distinct 关键字对null值算一种特殊的值,当某列多行值是null, 算一个。(行)。
- select 语句选出来结果的顺序(即行的顺序与表中行的顺序可能不一致的,且每次执行结果可能不一样)行顺序与列顺序不是同一个概念。
聚合函数
聚合函数 : 汇总的函数称为聚合函数。所谓聚合,就是将多行汇总为一行。实际上,所有的聚合函数都是这样的,输入多行输出一行。
- count:计算行数
例:select count(*) from tableName ;// 选择表中的总行数,包含null的值。
例:select count(column) from tableName ;// 计算指定列非null 的行数。-
- 计算表中所有的行数值可以使用:select count(*) from tableName ; 这个值包含:null在内。
- 计算非null的行数可以使用:select count( columnNmae) from tableName ; 这个值是 columnName 列 值为not null 的总行数。
- 注:总结:对于count聚合函数来说,计算列不同计算,返回的值可能不同。
public void getCount(){
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
String sql = "select count(regist_date) from "+ DBOpenHelper.tableName ; // 计算表中的所有行数
Cursor cursor = db.rawQuery(sql , null);
cursor.moveToFirst();
Log.d("TAG" , "总行数 count = "+ cursor.getLong(0));
String sql2 = "select count(distinct regist_date) from "+ DBOpenHelper.tableName ; // 是先删除重复的值在计算行数 值为;5 。注意执行顺序
Cursor cursor2 = db.rawQuery(sql2 , null);
cursor2.moveToFirst();
Log.d("TAG" , "总行数 count = "+ cursor2.getLong(0));
}
- sum:计算和值
sql = ” select sum(column) from tableName ;
注:SUM需要只能具体的列且是数值列。例 select sum(ColumnName) from tableName ; 若写为: selcet sum(*) from tableName ; 会报错。
public void getSUM(){
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
String sql = "select sum(sale_price) from "+ DBOpenHelper.tableName ; // 计算表中的所有行数
Cursor cursor = db.rawQuery(sql , null);
cursor.moveToFirst();
Log.d("TAG" , "sum = "+ cursor.getInt(0));
}
- avg:计算平均值
AVG需要只能具体的列且是数值列。例 select avg(ColumnName) from tableName ; 若写为: selcet avg(*) from tableName ; 会报错。
注:计算平均值时,若指定列有null值存在,这个unll值是不参数与计算的,如:一个表中的有8 行,columnName 数值列存在两个NULL值,那么计算平均值是 /6 而不是 /8 。
public void getAVG(){
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
String sql = "select avg(sale_price) from "+ DBOpenHelper.tableName ; // 计算表中的所有行数
Cursor cursor = db.rawQuery(sql , null);
cursor.moveToFirst();
Log.d("TAG" , "avg = "+ cursor.getDouble(0));
}
- max:计算指定列的最大值
MAX需要只能具体的列且是数值列,即不能使用作为参数:例 select max(ColumnName) from tableName ; 若写为: selcet max() from tableName ; 会报错。
public void getMAX(){
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
String sql = "select max(sale_price) from "+ DBOpenHelper.tableName ; // 计算表中的所有行数
Cursor cursor = db.rawQuery(sql , null);
cursor.moveToFirst();
Log.d("TAG" , "max = "+ cursor.getInt(0));
}
- min:计算指定列的最小值
MIN需要只能具体的列且是数值列,即不能使用作为参数:例 select min(ColumnName) from tableName ; 若写为: selcet min() from tableName ; 会报错。
public void getMIN(){
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
String sql = "select min(sale_price) from "+ DBOpenHelper.tableName ; // 计算表中的所有行数
Cursor cursor = db.rawQuery(sql , null);
cursor.moveToFirst();
Log.d("TAG" , "min = "+ cursor.getInt(0));
}
分组
对表进行分组 :对表进行分组就是使用聚合函数Group by 函数以聚合键将表分成几组。就像分蛋糕一样,安人数将蛋糕切分。
- 语句如:select column1 , column2 , column3 , column4 from tableName group by column1 , column2 , column3 , column4;
- 聚合键:在Group by 子句中的列称为聚合键 。上例:column1 , column2 , column3 , column4 都是聚合键。
- 当作为聚合键的列的值存在null 时,group by 会将null 作为一种特殊的值(不确定值)来处理,和distinct 关键字一样。(即将指定列的值为null的值的行分为一组)。
- 书写顺序:select -> from -> where -> group by ; 书写顺寻不能错乱,否则执行报错。
- 执行顺寻:from -> where -> group by -> select : 即从指定tableName 中 先使用where 进行过滤 ,对过后的数据在进行 broup by 分组 ,最后在select
注:使用group by 常见的错误。
- 在select 中多写了 broup by 聚合键中之外的列也是可以的,SQLite3是个特例。使用了group by 后 select 中能用(常数),(聚合函数count sum avg max min ), ((聚合键以及聚合键之外的列)。 分组后,一组一个(行)结果表示。一行代表一组。
- 在group by 中写了列的别名 如:selcet column ad “第一列的别名” from tableName group by 第一列的别名 ;
注:出错的原因从执行的顺序来找,别名是写在 select 子局中定义的 ,而 group by 的执行顺序比 select 先执行,所以在执行到 group by 中时 出现了别名,这时并不h知道它是什么,所以报错。 - 认为使用了Group by 后查询出来的结果是有序的。在没有排序函数时,查询出来的结果都是无序的。
- where 中使用聚合函数。(聚合函数只能出现在select havint order by 中)。
public void getGroupBy(){
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
String sql = "select purcgase_price , count(*) from "+ DBOpenHelper.tableName +" where product_type = '衣服' group by purcgase_price" ; // 计算表中的所有行数
Cursor cursor = db.rawQuery(sql , null);
if(!cursor.moveToFirst()) {
Log.d("TAG" , "null" );
return;
}
int count = cursor.getCount() ;
for( int i = 0 ; i < count ; i++ ){
String s1 = cursor.getString(0);
int s2 = cursor.getInt(1);
if(i == 0 ){
Log.d("TAG"+ i , " purcgase_price , count ") ;
}
Log.d("TAG"+ i , " " + s1 + " , "+ s2 + " ") ;
cursor.moveToNext();
}
cursor.close();
}
对表分组后进行筛选
问题:怎样选出分组后为两行的组?这时就可以使用having 了。
- where 自居用来指定数据行的条件,having子句用来指定分组的条件。
- 书写顺序:select -> form -> where -> group by -> having -> order by
- 执行顺序:from -> where -> groug by -> having -> select -> order by
注:having 的组成 (常数) , (聚合函数) , (聚合键以及聚合键以外的键)
public void groupByHaving(){
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
String sql = "select product_type , count(*) from "+ DBOpenHelper.tableName +" group by product_type having count(*)=2" ; // 选出为两行的组
String sq2 = "select product_type , avg(sale_price) from "+ DBOpenHelper.tableName +" group by product_type having avg(sale_price)>=2500" ; // 选出分组后sale_price>=2500的组
Cursor cursor = db.rawQuery(sq2 , null);
if(!cursor.moveToFirst()) {
Log.d("TAG" , "null" );
return;
}
int count = cursor.getCount() ;
// sql1
// for( int i = 0 ; i < count ; i++ ){
// String s1 = cursor.getString(0);
// int s2 = cursor.getInt(1);
// if(i == 0 ){
// Log.d("TAG"+ i , " product_type , count ") ;
// }
// Log.d("TAG"+ i , " " + s1 + " , "+ s2 + " ") ;
// cursor.moveToNext();
// }
// sql2
for( int i = 0 ; i < count ; i++ ){
String s1 = cursor.getString(0);
double s2 = cursor.getDouble(1);
if(i == 0 ){
Log.d("TAG"+ i , " product_type , avg ") ;
}
Log.d("TAG"+ i , " " + s1 + " , "+ s2 + " ") ;
cursor.moveToNext();
}
cursor.close();
}
排序
- asc (升序,默认的顺序), desc (降序)。
- 书写顺序:select -> form -> where -> group by -> having -> order by。
- 执行顺序:from -> where -> groug by -> having -> select -> order by。
所以order by 中可以使用 列的别名 (但不能使用列的编号)。 - 写在 order by 中的列称为排序键。
- 语句中使用了order by 后 不写 asc 或desc 时,默认使用的是asc。
- 排序键的值存在null时,在SQLite3中会把他当成0来处理。
- 排序键可以使用任何键;
- order by 中可以使用聚合函数,别名,所有列。(不能使用编号)。
- 排序键可以写多列,逗号隔开。优先级从左到有。
public void orderBy(){
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
dbOpenHelper.getReadableDatabase();
String sql = "select product_id , product_name , sale_price , purcgase_price from "+ DBOpenHelper.tableName +" order by purcgase_price desc , sale_price asc " ; // 选出为两行的组
String sq2 = "select product_id , product_name , sale_price , purcgase_price , product_type from "+ DBOpenHelper.tableName +" group by product_type order by count(*) " ; // 行数的从下到大的排序
Cursor cursor = db.rawQuery(sq2 , null);
if(!cursor.moveToFirst()) {
Log.d("TAG" , "null" );
return;
}
int count = cursor.getCount() ;
for( int i = 0 ; i < count ; i++ ){
String s1 = cursor.getString(cursor.getColumnIndex("product_id"));
String s2 = cursor.getString(cursor.getColumnIndex("product_name"));
int s3 = cursor.getInt(cursor.getColumnIndex("sale_price"));
int s4 = cursor.getInt(cursor.getColumnIndex("purcgase_price"));
String s5 = cursor.getString(cursor.getColumnIndex("product_type"));
if(i == 0 ){
Log.d("TAG"+ i , " product_id , product_name , sale_price , purcgase_price , purcgase_price ") ;
}
Log.d("TAG"+ i , " " + s1 + " , "+ s2 + " " + s3 + " " + s4 + " "+ s5) ;
cursor.moveToNext();
}
cursor.close();
}
运算符
- 算数运算符: +、—、* 、/ 。注:null参与算数运算符得到null , null + 1 = null , null - 1 = null , null*1=null , null /0= null , 0/null = null ;非法算数运算得到null , 例 5/0 = null ;(得到的是null,不会报错)。即null参与一切算数运算得到的都是null。
- 比较运算符:> ,< = ,!= , <= , >= 、!<、!>、<>。null不参与比较运算,product_type = null ; 这样写不报错,但是没有结果。如果判断是不是null , 使用 is null , not null 的方法来判断。
- 逻辑运算符:and 、or 、not、is null、exsist。and 的优先级比or的高。