5.数据库
5.数据库
数据库的操作
- 创建数据库create database mydb ;
- 查看创建数据库的语句show create database mydb ;
- 改变当前的数据库use mydb ;
- 删除数据库drop database mydb ;
- 查看所有的数据库show databases ;
- 修改数据库mydb1的字符集为utf8
- alter database mydb1 character set utf8 ;
针对表的操作
- 创建表t
create table t( id int , name varchar(30) ) ;
- 查看创建表的源码
show create table t ;
- 创建表t1,使用字符集gbk
create table t1( id int , name varchar(30) )character set gbk ;
- 插入数据
设置客户端的字符集为gbk set character_set_client=gbk; 设置结果集的字符集为gbk set character_set_results=gbk ;
insert into t4(id,name) values(1,'张无忌') ; insert t4(id,name) values(2,'乔峰') ; *省略字段，意味着所有的字段都必须给值(自增例外) insert t4 values(3,'杨过','2014-4-3') ;
- 更新
将表t4的第三条记录姓名字段改为杨康 update t4 set name='杨康' where id = 3 ; 将所有记录的名字都改为东方不败 update t4 set name = '东方不败' ; *修改多个字段 update t4 set id=6,name='萧峰' where id = 2 ;
- 删除
delete from t4 where id = 4 ; 删除所有的记录 delete from t4 ; 删除所有的记录 truncate table t4 ;
- 给表t4增加一个字段address
alter table t4 add address varchar(100) ; *删除字段address alter table t4 drop column address ;
- 查找
查看所有数据 select from stu ; 查看小龙女的信息 select from stu where id = 2 ; select from stu where name='小龙女' ;
查看年龄在20~30之间的人 select from stu where age >=20 and age <=30 ; select from stu where age between 20 and 30 ; # 包括20和30 查看所有的的姓名 select name from stu ;
查看所有的的姓名,年龄，性别 select name,age,sex from stu ;
android中的数据库SQLite
- android中建立数据的过程：
a)首先进行自定义一个类进行继承SQLiteOpenHelper, 因为如果继承SQLiteDatabase那么会，要覆盖其中的许多的方法。如果已经有数据库，就不需要sqliteopenhelper
(1)MysqlLite(Context context, String name, CursorFactory factory,int version)
传入当前的应用环境,数据库的名称，游标的工厂,版本号,让底层为你进行创建数据库
(2)oncreate(SQLiteDatabase db):
当数据库创建好之后进行运行的函数，主要是在数据库创建好之后进行创建表
db.execSQL("create table person(_id integer primary key autoincrement, name char(10), salary char(20), phone integer(20))");书上：
integer表示整型，real表示浮点型，text表示文本类型，blob表示二进制类型。另外， primary key将 id列设为主键，并用 autoincrement关键字表示 id列是自增长的。
public static final String CREATE_BOOK = "create table book ("
+ "id integer primary key autoincrement, "
+ "author text, "
+ "price real, "
+ "pages integer, "
+ "name text)";当数据库进行更新后需要进行执行的函
b)使用测试类进行测试创建数据库：
public class TestCase extends AndroidTestCase {
//此时测试框架还没有初始化完毕，没有虚拟上下文对象，所以不能写在这里
// private MyOpenHelper oh = new MyOpenHelper(getContext(), "people.db", null, 1);
private MyOpenHelper oh;
private SQLiteDatabase db;
public void test(){
//getContext():获取一个虚拟的上下文
MyOpenHelper oh = new MyOpenHelper(getContext(), "people.db", null, 1);
//如果数据库不存在，先创建数据库，再获取可读可写的数据库对象，如果数据库存在，就直接打开，增删改用这个
SQLiteDatabase db = oh.getWritableDatabase();
//如果存储空间满了，那么返回只读数据库对象，查询时用这个
// SQLiteDatabase db = oh.getReadableDatabase();
}
//测试框架初始化完毕之后，在测试方法执行之前，此方法调用，这样就不需要每个测试方法都创建数据库
@Override
protected void setUp() throws Exception {
super.setUp();
oh = new MyOpenHelper(getContext(), "people.db", null, 1);
db = oh.getWritableDatabase();
}
//测试方法执行完毕之后，此方法调用
@Override
protected void tearDown() throws Exception {
// TODO Auto-generated method stub
super.tearDown();
db.close();
}
public void insert(){
//SQLite其实是不检测数据类型的，全部是字符串，加不加引号都无所谓，定义类型只不过给程序员看的
// db.execSQL("insert into person (name, salary, phone)values(?, ?, ?)", new Object[]{"小志的老婆[1]", "13000", 138438});
// db.execSQL("insert into person (name, salary, phone)values(?, ?, ?)", new Object[]{"小志的儿子", 14000, "13888"});
db.execSQL("insert into person (name, salary, phone)values(?, ?, ?)", new Object[]{"小志", 14000, "13888"});
}
public void delete(){
db.execSQL("delete from person where name = ?", new Object[]{"小志"});
}
public void update(){
db.execSQL("update person set phone = ? where name = ?", new Object[]{186666, "小志的儿子"});
}
public void select(){
Cursor cursor = db.rawQuery("select name, salary from person", null);
while(cursor.moveToNext()){
//通过列索引获取列的值，标准是第一种的写法
String name = cursor.getString(cursor.getColumnIndex("name"));
String salary = cursor.getString(1);
System.out.println(name + ";" + salary);
}
}
public void insertApi(){
//把要插入的数据全部封装至ContentValues对象
ContentValues values = new ContentValues();
values.put("name", "游天龙");
values.put("phone", "15999");
values.put("salary", 16000);
db.insert("person", null, values);
}
public void deleteApi(){
int i = db.delete("person", "name = ? and _id = ?", new String[]{"小志的儿子", "3"});
System.out.println(i);
}
public void updateApi(){
ContentValues values = new ContentValues();
values.put("salary", 26000);
int i = db.update("person", values, "name = ?", new String[]{"游天龙"});
System.out.println(i);
}这里使用了第三、第四个参数来指定具体更新哪几行。第三个参数对应的是 SQL语句的 where部分，表示去更新所有 name等于? 的行，而?是一个占位符，可以通过第四个参数提供的一个字符串数组为第三个参数中的每个占位符指定相应的内容。因此上述代码想表达的意图就是，将名字是游天龙的工资改成 2600。
public void selectApi(){
Cursor cursor = db.query("person", null, null, null, null, null, null, null);
while(cursor.moveToNext()){
String name = cursor.getString(cursor.getColumnIndex("name"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
String salary = cursor.getString(cursor.getColumnIndex("salary"));
System.out.println(name + ";" + phone + ";" + salary);
}
}
public void transaction(){
try{
//开启事务
db.beginTransaction();
ContentValues values = new ContentValues();
values.put("salary", 12000);
db.update("person", values, "name = ?", new String[]{"小志"});
values.clear();
values.put("salary", 16000);
db.update("person", values, "name = ?", new String[]{"小志的儿子"});
int i = 3/0;//加上这句就不会执行成功
//设置 事务执行成功
db.setTransactionSuccessful();
}
finally{
//关闭事务，同时提交，如果已经设置事务执行成功，那么sql语句就生效了，反之，sql语句回滚
db.endTransaction();
}
}
}分页查询
query最短的一个方法重载也需要传入七个参数。第一个参数是表名，表示希望从哪张表中查 询数据。第二个参数用于指定去查询哪几列，如果不指定则默认查询所有列。第三、第四个参数用于去约束查询某一行或某几行的数据，不指定则默认是查询所有行的数据。第五个参数用于指定需要去 group by的列，不指定则表示不对查询结果进行 group by操作。第六个参数用于对group by之后的数据进行进一步的过滤，不指定则表示不进行过滤。第七个参数用于指定查询结果的排序方式，不指定则表示使用默认的排序方式
总结：这个数据SQLiteOpenHelper类和SQLiteDatabase什么关系；
主要是通过SQLiteOpenHelper类进行创建SQLiteDatabase类。如果有数据库就不需要这个SQLiteOpenHelper类了
注意：
在SQLiteOpenHelper的实现的类中注意创建表的时候名称和你实现插入数据时的表的名称一致性。
//这样就写死
public class MyOpenHelper extends SQLiteOpenHelper {
public MyOpenHelper(Context context) {
super(context, "people.db", null, 1);
// TODO Auto-generated constructor stub
}
//再其他方法中
oh = new MyOpenHelper(getContext());总结：
添加数据的方法如下：
db.execSQL("insert into Book (name, author, pages, price) values(?, ?, ?, ?)",
new String[] { "The Da Vinci Code", "Dan Brown", "454", "16.96" });
db.execSQL("insert into Book (name, author, pages, price) values(?, ?, ?, ?)",
new String[] { "The Lost Symbol", "Dan Brown", "510", "19.95" });
更新数据的方法如下：
db.execSQL("update Book set price = ? where name = ?", new String[] { "10.99",
"The Da Vinci Code" });
删除数据的方法如下：
db.execSQL("delete from Book where pages > ?", new String[] { "500" });
查询数据的方法如下：
db.rawQuery("select * from Book", null);
可以看到，除了查询数据的时候调用的是 SQLiteDatabase的 rawQuery()方法，其他的操作都是调用的 execSQL()方法。
数据库常用操作
用到数据库就要想到
- 数据库单例，保证只要一个实例
- 定义javaben对象
- 将Javaben对象存到数据库
- 从数据库读取
实例： CoolWeatherOpenHelper
public class CoolWeatherOpenHelper extends SQLiteOpenHelper {
/**
* Province表建表语句
*/
public static final String CREATE_PROVINCE = "create table Province ("
+ "id integer primary key autoincrement, " + "province_name text, "
+ "province_code text)";
/**
* City表建表语句
*/
public static final String CREATE_CITY = "create table City ("
+ "id integer primary key autoincrement, " + "city_name text, "
+ "city_code text, " + "province_id integer)";
/**
* County表建表语句
*/
public static final String CREATE_COUNTY = "create table County ("
+ "id integer primary key autoincrement, " + "county_name text, "
+ "county_code text, " + "city_id integer)";
public CoolWeatherOpenHelper(Context context, String name,
CursorFactory factory, int version) {
super(context, name, factory, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_PROVINCE); // 创建Province表
db.execSQL(CREATE_CITY); // 创建City表
db.execSQL(CREATE_COUNTY); // 创建County表
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}CoolWeatherDB
public class CoolWeatherDB {
/**
* 数据库名
*/
public static final String DB_NAME = "cool_weather";
/**
* 数据库版本
*/
public static final int VERSION = 1;
private static CoolWeatherDB coolWeatherDB;
private SQLiteDatabase db;
/**
* 将构造方法私有化
*/
private CoolWeatherDB(Context context) {
CoolWeatherOpenHelper dbHelper = new CoolWeatherOpenHelper(context,
DB_NAME, null, VERSION);
db = dbHelper.getWritableDatabase();
}
/**
* 获取CoolWeatherDB的实例。
*/
public synchronized static CoolWeatherDB getInstance(Context context) {
if (coolWeatherDB == null) {
coolWeatherDB = new CoolWeatherDB(context);
}
return coolWeatherDB;
}
/**
* 将Province实例存储到数据库。
*/
public void saveProvince(Province province) {
if (province != null) {
ContentValues values = new ContentValues();
values.put("province_name", province.getProvinceName());
values.put("province_code", province.getProvinceCode());
db.insert("Province", null, values);
}
}
/**
* 从数据库读取全国所有的省份信息。
*/
public List<Province> loadProvinces() {
List<Province> list = new ArrayList<Province>();
Cursor cursor = db
.query("Province", null, null, null, null, null, null);
if (cursor.moveToFirst()) {
do {
Province province = new Province();
province.setId(cursor.getInt(cursor.getColumnIndex("id")));
province.setProvinceName(cursor.getString(cursor
.getColumnIndex("province_name")));
province.setProvinceCode(cursor.getString(cursor
.getColumnIndex("province_code")));
list.add(province);
} while (cursor.moveToNext());
}
return list;
}
/**
* 将City实例存储到数据库。
*/
public void saveCity(City city) {
if (city != null) {
ContentValues values = new ContentValues();
values.put("city_name", city.getCityName());
values.put("city_code", city.getCityCode());
values.put("province_id", city.getProvinceId());
db.insert("City", null, values);
}
}
/**
* 从数据库读取某省下所有的城市信息。
*/
public List<City> loadCities(int provinceId) {
List<City> list = new ArrayList<City>();
Cursor cursor = db.query("City", null, "province_id = ?",
new String[] { String.valueOf(provinceId) }, null, null, null);//如果全是null，那么查的是所有的数据，比如get(xx)查出来的是所有这列的数据，如果想根据xx去查询的话就需要这样写
if (cursor.moveToFirst()) {
do {
City city = new City();
city.setId(cursor.getInt(cursor.getColumnIndex("id")));
city.setCityName(cursor.getString(cursor
.getColumnIndex("city_name")));
city.setCityCode(cursor.getString(cursor
.getColumnIndex("city_code")));
city.setProvinceId(provinceId);
list.add(city);
} while (cursor.moveToNext());
}
return list;
}
/**
* 将County实例存储到数据库。
*/
public void saveCounty(County county) {
if (county != null) {
ContentValues values = new ContentValues();
values.put("county_name", county.getCountyName());
values.put("county_code", county.getCountyCode());
values.put("city_id", county.getCityId());
db.insert("County", null, values);
}
}
/**
* 从数据库读取某城市下所有的县信息。
*/
public List<County> loadCounties(int cityId) {
List<County> list = new ArrayList<County>();
Cursor cursor = db.query("County", null, "city_id = ?",
new String[] { String.valueOf(cityId) }, null, null, null);
if (cursor.moveToFirst()) {
do {
County county = new County();
county.setId(cursor.getInt(cursor.getColumnIndex("id")));
county.setCountyName(cursor.getString(cursor
.getColumnIndex("county_name")));
county.setCountyCode(cursor.getString(cursor
.getColumnIndex("county_code")));
county.setCityId(cityId);
list.add(county);
} while (cursor.moveToNext());
}
return list;
}升级数据库的最佳写法
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_BOOK);
db.execSQL(CREATE_CATEGORY);
Toast.makeText(mContext, "Create succeeded", Toast.LENGTH_SHORT).
show();
}
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("drop table if exists Book");
db.execSQL("drop table if exists Category");
onCreate(db);
}为了保证数据库中的表是最新的，只是简单地在 onUpgrade()方法中删除掉了当前所有的表，然后强制重新执行了
一遍 onCreate()方法。这种方式在产品的开发阶段确实可以用，但是当产品真正上线了之后就绝对不行了。想象以下
场景，比如你编写的某个应用已经成功上线，并且还拥有了不错的下载量。现在由于添加新功能的原因，使得数据库也
需要一起升级，然后用户更新了这个版本之后发现以前程序中存储的本地数据全部丢失了！那么很遗憾，你的用户群体
可能已经流失一大半了。
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_BOOK);
db.execSQL(CREATE_CATEGORY);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
switch (oldVersion) {
case 1:
db.execSQL(CREATE_CATEGORY);
default:
}在 onCreate()方法里新增了一条建表语句，然后又在 onUpgrade()方法中添加了一个 switch判断，如果用户当前数据库的版本号是 1，就只会创建一张 Category表。这样当用户是直接安装的第二版的程序时，就会将两张表一起创建。而当用户是使用第二版的程序覆盖安装第一版的程序时，就会进入到升级数据库的操作中，此时由于 Book表已经存在了，因此只需要创建一张 Category表即可
新的需求又来了，这次要给 Book表和 Category表之间建立关联，需要在 Book表中添加一个 category_id的字段
...oncreat方法和前面一样
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
switch (oldVersion) {
case 1:
db.execSQL(CREATE_CATEGORY);
case 2:
db.execSQL("alter table Book add column category_id integer");
default:
}
}首先在 Book表的建表语句中添加了一个 category_id列，这样当用户直接安装第三版的程序时，这个新增的列就已经自动添加成功了。然而，如果用户之前已经安装了某一版本的程序，现在需要覆盖安装，就会进入到升级数据库的操作中。在 onUpgrade()方法里，添加了一个新的 case，如果当前数据库的版本号是 2，就会执行 alter命令来为Book表新增一个 category_id列。这里请注意一个非常重要的细节，switch中每一个 case的最后都是没有使用 break的，这是为了保证在跨版本升级的时候， 每一次的数据库修改都能被全部执行到。比如用户当前是从第二版程序升级到第三版程序的，那么 case 2中的逻辑就会执行。而如果用户是直接从第一版程序升级到第三版程序的，那么 case 1和 case 2中的逻辑都会执行。使用这种方式来维护数据库的升级，不管版本怎样更新，都可以保证数据库的表结构是最新的，而且表中的数据也完全不会丢失了。