一、SQL的基本语法：
SQL本质上是一种编程语言，它的学名叫作“结构化查询语言”（全称为Structured Query Language，简称SQL）。不过SQL语言并非通用的编程语言，它专用于数据库的访问和处理，更像是一种操作命令，所以常说SQL语句而不说SQL代码。标准的SQL语句分为3类：数据定义、数据操纵和数据控制，但不同的数据库往往有自己的实现。
SQLite是一种小巧的嵌入式数据库，使用方便、开发简单。如同MySQL、Oracle那样，SQLite也采用SQL语句管理数据，由于它属于轻型数据库，不涉及复杂的数据控制操作，因此App开发只用到数据定义和数据操纵两类SQL。此外，SQLite的SQL语法与通用的SQL语法略有不同，接下来介绍的两类SQL语法全部基于SQLite。
1､ 数据定义语言：
数据定义语言全称Data Definition Language，简称DDL，它描述了怎样变更数据实体的框架结构。就SQLite而言，DDL语言主要包括3种操作：创建表格、删除表格、修改表结构，分别说明如下。
（1）创建表格表格的创建动作由create命令完成，格式为“CREATETABLEIFNOTEXISTS 表格名称（以逗号分隔的各字段定义）；”。以用户信息表为例，它的建表语句如下所示：
sql.png
上面的SQL语法与其他数据库的SQL语法有所出入，相关的注意点说明见下：
①SQL语句不区分大小写，无论是create与table这类关键词，还是表格名称、字段名称，都不区分大小写。唯一区分大小写的是被单引号括起来的字符串值。
②为避免重复建表，应加上IF NOT EXISTS关键词，例如CREATE TABLE IF NOT EXISTS 表格名称……
③SQLite支持整型INTEGER、长整型LONG、字符串VARCHAR、浮点数FLOAT，但不支持布尔类型。布尔类型的数据要使用整型保存，如果直接保存布尔数据，在入库时SQLite会自动将它转为0或1，其中0表示false，1表示true。
④建表时需要唯一标识字段，它的字段名为id。创建新表都要加上该字段定义，例如id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL。
（2）删除表格表格的删除动作由drop命令完成，格式为“DROP TABLE IF EXISTS 表格名称;”。下面是删除用户信息表的SQL语句例子：
user.png
（3）修改表结构表格的修改动作由alter命令完成，格式为“ALTER TABLE 表格名称 修改操作;”。不过SQLite只支持增加字段，不支持修改字段，也不支持删除字段。对于字段增加操作，需要在alter之后补充add命令，具体格式如“ALTER TABLE 表格名称 ADD COLUMN 字段名称 字段类型;”。下面是给用户信息表增加手机号字段的SQL语句例子：
info.png
注意，SQLite的ALTER语句每次只能添加一列字段，若要添加多列，就得分多次添加。
2､数据操纵语言：
数据操纵语言全称Data Manipulation Language，简称DML，它描述了怎样处理数据实体的内部记录。表格记录的操作类型包括添加、删除、修改、查询4类，
分别说明如下：
（1）添加记录记录的添加动作由insert命令完成，格式为“INSERT INTO 表格名称（以逗号分隔的字段名列表） VALUES （以逗号分隔的字段值列表）；”。下面是往用户信息表插入一条记录的SQL语句例子：
insert.png
（2）删除记录
记录的删除动作由delete命令完成，格式为“DELETE FROM 表格名称 WHERE 查询条件;”，其中查询条件的表达式形如“字段名=字段值”，多个字段的条件交集通过“AND”连接，条件并集通过“OR”连接。下面是从用户信息表删除指定记录的SQL语句例子：
delete.png
（3）修改记录记录的修改动作由update命令完成，格式为“UPDATE 表格名称 SET 字段名=字段值WHERE 查询条件;”。
下面是对用户信息表更新指定记录的SQL语句例子：
update.png
（4）查询记录记录的查询动作由select命令完成，格式为“SELECT 以逗号分隔的字段名列表 FROM 表格名称 WHERE 查询条件;”。如果字段名列表填星号“*”，则表示查询该表的所有字段。下面是从用户信息表查询指定记录的SQL语句例子：
select1.png
查询操作除了比较字段值条件之外，常常需要对查询结果排序，此时要在查询条件后面添加排序条件，对应的表达式为“ORDERBY 字段名 ASC或者DESC”，意指对查询结果按照某个字段排序，其中ASC代表升序，DESC代表降序。下面是查询记录并对结果排序的SQL语句例子：
select2.png
二、数据库管理器SQLiteDatabase
SQL语句毕竟只是SQL命令，若要在Java代码中操纵SQLite，还需专门的工具类。SQLiteDatabase便是Android提供的SQLite数据库管理器，开发者可以在活动页面代码调用openOrCreateDatabase方法获取数据库实例，如下：
base_data.png
获得数据库实例之后，就能对该数据库开展各项操作了。数据库管理器SQLiteDatabase提供了若干操作数据表的API，常用的方法有3类，列举如下：
1．管理类，用于数据库层面的操作:
openDatabase：打开指定路径的数据库。
isOpen：判断数据库是否已打开。
close：关闭数据库。
getVersion：获取数据库的版本号。
setVersion：设置数据库的版本号。
2．事务类，用于事务层面的操作:
beginTransaction：开始事务。
setTransactionSuccessful：设置事务的成功标志。
endTransaction：结束事务。执行本方法时，系统会判断之前是否调用了setTransactionSuccessful方法，如果之前已调用该方法就提交事务，如果没有调用该方法就回滚事务。
3．数据处理类，用于数据表层面的操作:
execSQL：执行拼接好的SQL控制语句。一般用于建表、删表、变更表结构。
delete：删除符合条件的记录。
update：更新符合条件的记录信息。
insert：插入一条记录。
query：执行查询操作，并返回结果集的游标。
rawQuery：执行拼接好的SQL查询语句，并返回结果集的游标。
在实际开发中，比较经常用到的是查询语句，建议先写好查询操作的select语句，再调用rawQuery方法执行查询语句。
三、数据库帮助器SQLiteOpenHelper
由于SQLiteDatabase存在局限性，一不小心就会重复打开数据库，处理数据库的升级也不方便；因此Android提供了数据库帮助器SQLiteOpenHelper，帮助开发者合理使用SQLite。SQLiteOpenHelper的具体使用步骤如下：
步骤一，新建一个继承自SQLiteOpenHelper的数据库操作类，按提示重写onCreate和onUpgrade两个方法。其中，onCreate方法只在第一次打开数据库时执行，在此可以创建表结构；而onUpgrade方法在数据库版本升高时执行，在此可以根据新旧版本号变更表结构。
步骤二，为保证数据库安全使用，需要封装几个必要方法，包括获取单例对象、打开数据库连接、关闭数据库连接，说明如下：
获取单例对象：确保在App运行过程中数据库只会打开一次，避免重复打开引起错误。
打开数据库连接：SQLite有锁机制，即读锁和写锁的处理；故而数据库连接也分两种，读连接可调用getReadableDatabase方法获得，写连接可调用getWritableDatabase获得。
关闭数据库连接：数据库操作完毕，调用数据库实例的close方法关闭连接。
步骤三，提供对表记录增加、删除、修改、查询的操作方法。
能被SQLite直接使用的数据结构是ContentValues类，它类似于映射Map，也提供了put和get方法存取键值对。
区别之处在于：ContentValues的键只能是字符串，不能是其他类型。ContentValues主要用于增加记录和更新记录，对应数据库的insert和update方法。记录的查询操作用到了游标类Cursor，调用query和rawQuery方法返回的都是Cursor对象，若要获取全部的查询结果，则需根据游标的指示一条一条遍历结果集合。
Cursor的常用方法可分为3类，说明如下：
1．游标控制类方法，用于指定游标的状态
close：关闭游标。
isClosed：判断游标是否关闭。
isFirst：判断游标是否在开头。
isLast：判断游标是否在末尾。
2．游标移动类方法，把游标移动到指定位置
moveToFirst：移动游标到开头。
moveToLast：移动游标到末尾。
moveToNext：移动游标到下一条记录。
moveToPrevious：移动游标到上一条记录。
move：往后移动游标若干条记录。
moveToPosition：移动游标到指定位置的记录。
3．获取记录类方法，可获取记录的数量、类型以及取值
getCount：获取结果记录的数量。
getInt：获取指定字段的整型值。
getLong：获取指定字段的长整型值。
getFloat：获取指定字段的浮点数值。
getString：获取指定字段的字符串值。
getType：获取指定字段的字段类型。
下面是SQLiteOpenHelper实现代码示例：
public class UserDBHelper extends SQLiteOpenHelper {
private static final String DB_NAME = "user.db";
private static final String TABLE_NAME = "user_info";
private static final int DB_VERSION = 2;
private static UserDBHelper mHelper = null;
private SQLiteDatabase mRDB = null;
private SQLiteDatabase mWDB = null;
private UserDBHelper(Context context) {
super(context, DB_NAME, null, DB_VERSION);
}
// 利用单例模式获取数据库帮助器的唯一实例
public static UserDBHelper getInstance(Context context) {
if (mHelper == null) {
mHelper = new UserDBHelper(context);
}
return mHelper;
}
// 打开数据库的读连接
public SQLiteDatabase openReadLink() {
if (mRDB == null || !mRDB.isOpen()) {
mRDB = mHelper.getReadableDatabase();
}
return mRDB;
}
// 打开数据库的写连接
public SQLiteDatabase openWriteLink() {
if (mWDB == null || !mWDB.isOpen()) {
mWDB = mHelper.getWritableDatabase();
}
return mWDB;
}
// 关闭数据库连接
public void closeLink() {
if (mRDB != null && mRDB.isOpen()) {
mRDB.close();
mRDB = null;
}
if (mWDB != null && mWDB.isOpen()) {
mWDB.close();
mWDB = null;
}
}
// 创建数据库，执行建表语句
@Override
public void onCreate(SQLiteDatabase db) {
String sql = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (" +
"_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
" name VARCHAR NOT NULL," +
" age INTEGER NOT NULL," +
" height LONG NOT NULL," +
" weight FLOAT NOT NULL," +
" married INTEGER NOT NULL);";
db.execSQL(sql);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
String sql = "ALTER TABLE " + TABLE_NAME + " ADD COLUMN phone VARCHAR;";
db.execSQL(sql);
sql = "ALTER TABLE " + TABLE_NAME + " ADD COLUMN password VARCHAR;";
db.execSQL(sql);
}
public long insert(User user) {
ContentValues values = new ContentValues();
values.put("name", user.name);
values.put("age", user.age);
values.put("height", user.height);
values.put("weight", user.weight);
values.put("married", user.married);
// 执行插入记录动作，该语句返回插入记录的行号
// 如果第三个参数values 为Null或者元素个数为0， 由于insert()方法要求必须添加一条除了主键之外其它字段为Null值的记录，
// 为了满足SQL语法的需要， insert语句必须给定一个字段名 ，如：insert into person(name) values(NULL)，
// 倘若不给定字段名 ， insert语句就成了这样： insert into person() values()，显然这不满足标准SQL的语法。
// 如果第三个参数values 不为Null并且元素的个数大于0 ，可以把第二个参数设置为null 。
//return mWDB.insert(TABLE_NAME, null, values);
try {
mWDB.beginTransaction();
mWDB.insert(TABLE_NAME, null, values);
//int i = 10 / 0;
mWDB.insert(TABLE_NAME, null, values);
mWDB.setTransactionSuccessful();
} catch (Exception e) {
e.printStackTrace();
} finally {
mWDB.endTransaction();
}
return 1;
}
public long deleteByName(String name) {
//删除所有
//mWDB.delete(TABLE_NAME, "1=1", null);
return mWDB.delete(TABLE_NAME, "name=?", new String[]{name});
}
public long update(User user) {
ContentValues values = new ContentValues();
values.put("name", user.name);
values.put("age", user.age);
values.put("height", user.height);
values.put("weight", user.weight);
values.put("married", user.married);
return mWDB.update(TABLE_NAME, values, "name=?", new String[]{user.name});
}
public List<User> queryAll() {
List<User> list = new ArrayList<>();
// 执行记录查询动作，该语句返回结果集的游标
Cursor cursor = mRDB.query(TABLE_NAME, null, null, null, null, null, null);
// 循环取出游标指向的每条记录
while (cursor.moveToNext()) {
User user = new User();
user.id = cursor.getInt(0);
user.name = cursor.getString(1);
user.age = cursor.getInt(2);
user.height = cursor.getLong(3);
user.weight = cursor.getFloat(4);
//SQLite没有布尔型，用0表示false，用1表示true
user.married = (cursor.getInt(5) == 0) ? false : true;
list.add(user);
}
return list;
}
public List<User> queryByName(String name) {
List<User> list = new ArrayList<>();
// 执行记录查询动作，该语句返回结果集的游标
Cursor cursor = mRDB.query(TABLE_NAME, null, "name=?", new String[]{name}, null, null, null);
// 循环取出游标指向的每条记录
while (cursor.moveToNext()) {
User user = new User();
user.id = cursor.getInt(0);
user.name = cursor.getString(1);
user.age = cursor.getInt(2);
user.height = cursor.getLong(3);
user.weight = cursor.getFloat(4);
//SQLite没有布尔型，用0表示false，用1表示true
user.married = (cursor.getInt(5) == 0) ? false : true;
list.add(user);
}
return list;
}
}
网友评论