數(shù)據(jù)庫(kù)設(shè)計(jì)
比如我設(shè)計(jì)了一個(gè)基本Todo應(yīng)用淮阐，提供最小化的功能爆班，像創(chuàng)建一個(gè)todo note和賦予它一個(gè)tag(s)(類別)南吮。因此我們的數(shù)據(jù)庫(kù)只需要三個(gè)表花嘶。
這三個(gè)表是:
todos - 存儲(chǔ)所有的todo notes
tags - 存儲(chǔ)tags的列表
todo_tags- 存儲(chǔ)賦給todo的tags
下圖解釋了表的結(jié)構(gòu)和表之間的關(guān)系：
todos表
字段 |類型|鍵
---------|---------|-------
id |INTEGER|PK
note|TEXT|—
create-at|DATEIME|—
tags表
字段|類型|鍵
-------|--------|-----
id|INTEGER|PK
tag-name|TEXT|—
create-at|DATETIME|—
todo-tag表
字段|類型|鍵
-----|-----|-----
id|INTEGER|PK
todo-id|INTEGER|FK
tag-id|INTEGER|FK
Sample-Data
在Android中可以這樣創(chuàng)建這三個(gè)表
public class DatabaseHelper extends SQLiteOpenHelper {
// Logcat tag
private static final String LOG = "DatabaseHelper";
// Database Version
private static final int DATABASE_VERSION = 1;
// Database Name
private static final String DATABASE_NAME = "contactsManager";
// Table Names
private static final String TABLE_TODO = "todos";
private static final String TABLE_TAG = "tags";
private static final String TABLE_TODO_TAG = "todo_tags";
// Common column names
private static final String KEY_ID = "id";
private static final String KEY_CREATED_AT = "created_at";
// NOTES Table - column nmaes
private static final String KEY_TODO = "todo";
private static final String KEY_STATUS = "status";
// TAGS Table - column names
private static final String KEY_TAG_NAME = "tag_name";
// NOTE_TAGS Table - column names
private static final String KEY_TODO_ID = "todo_id";
private static final String KEY_TAG_ID = "tag_id";
// Table Create Statements
// Todo table create statement
private static final String CREATE_TABLE_TODO = "CREATE TABLE "
+ TABLE_TODO + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_TODO
+ " TEXT," + KEY_STATUS + " INTEGER," + KEY_CREATED_AT
+ " DATETIME" + ")";
// Tag table create statement
private static final String CREATE_TABLE_TAG = "CREATE TABLE " + TABLE_TAG
+ "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_TAG_NAME + " TEXT,"
+ KEY_CREATED_AT + " DATETIME" + ")";
// todo_tag table create statement
private static final String CREATE_TABLE_TODO_TAG = "CREATE TABLE "
+ TABLE_TODO_TAG + "(" + KEY_ID + " INTEGER PRIMARY KEY,"
+ KEY_TODO_ID + " INTEGER," + KEY_TAG_ID + " INTEGER,"
+ KEY_CREATED_AT + " DATETIME" + ")";
public DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
// creating required tables
db.execSQL(CREATE_TABLE_TODO);
db.execSQL(CREATE_TABLE_TAG);
db.execSQL(CREATE_TABLE_TODO_TAG);
}
public class DatabaseHelper extends SQLiteOpenHelper {
// Logcat tag
private static final String LOG = "DatabaseHelper";
// Database Version
private static final int DATABASE_VERSION = 1;
// Database Name
private static final String DATABASE_NAME = "contactsManager";
// Table Names
private static final String TABLE_TODO = "todos";
private static final String TABLE_TAG = "tags";
private static final String TABLE_TODO_TAG = "todo_tags";
// Common column names
private static final String KEY_ID = "id";
private static final String KEY_CREATED_AT = "created_at";
// NOTES Table - column nmaes
private static final String KEY_TODO = "todo";
private static final String KEY_STATUS = "status";
// TAGS Table - column names
private static final String KEY_TAG_NAME = "tag_name";
// NOTE_TAGS Table - column names
private static final String KEY_TODO_ID = "todo_id";
private static final String KEY_TAG_ID = "tag_id";
// Table Create Statements
// Todo table create statement
private static final String CREATE_TABLE_TODO = "CREATE TABLE "
+ TABLE_TODO + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_TODO
+ " TEXT," + KEY_STATUS + " INTEGER," + KEY_CREATED_AT
+ " DATETIME" + ")";
// Tag table create statement
private static final String CREATE_TABLE_TAG = "CREATE TABLE " + TABLE_TAG
+ "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_TAG_NAME + " TEXT,"
+ KEY_CREATED_AT + " DATETIME" + ")";
// todo_tag table create statement
private static final String CREATE_TABLE_TODO_TAG = "CREATE TABLE "
+ TABLE_TODO_TAG + "(" + KEY_ID + " INTEGER PRIMARY KEY,"
+ KEY_TODO_ID + " INTEGER," + KEY_TAG_ID + " INTEGER,"
+ KEY_CREATED_AT + " DATETIME" + ")";
public DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
// creating required tables
db.execSQL(CREATE_TABLE_TODO);
db.execSQL(CREATE_TABLE_TAG);
db.execSQL(CREATE_TABLE_TODO_TAG);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// on upgrade drop older tables
db.execSQL("DROP TABLE IF EXISTS " + TABLE_TODO);
db.execSQL("DROP TABLE IF EXISTS " + TABLE_TAG);
db.execSQL("DROP TABLE IF EXISTS " + TABLE_TODO_TAG);
// create new tables
onCreate(db);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// on upgrade drop older tables
db.execSQL("DROP TABLE IF EXISTS " + TABLE_TODO);
db.execSQL("DROP TABLE IF EXISTS " + TABLE_TAG);
db.execSQL("DROP TABLE IF EXISTS " + TABLE_TODO_TAG);
// create new tables
onCreate(db);
}
學(xué)生數(shù)據(jù)庫(kù)的設(shè)計(jì)
Student表
字段 |類型|鍵
---------|---------|-------
Sno|char|PK
Sname|char|-
Ssex|char|-
Sage|smallint|-
Sdept|char|-
Course表
字段 |類型|鍵
---------|---------|-------
Cno|char|PK
Cname|char|-
CPno|char|-
Ccredit|smallint|-
SC表
字段 |類型|鍵
---------|---------|-------
Sno|cha r|FK
Con|char|FK
Grade|smallint|-
創(chuàng)建表
create table Student
(Sno char primary key,
Same char ,
Sex char,
Sage smallint,
Sdept char
);
create table Cource
(Con char primary key,
Cname char not null,
Cpno char,
Ccredit smallint,
foreign key (Cpno) References Course(Con)
);
create table SC
(Sno Char,
Con char,
Gradle smallint,
primary key (Sno,Cno),
foreign key (Sno) references Student(Sno),
foreign key (Con) references Course(Cno)
);
連接查詢
若一個(gè)查詢同時(shí)設(shè)計(jì)兩個(gè)以上的表笋籽，則稱為連接查詢。
連接查詢是關(guān)系數(shù)據(jù)庫(kù)中最主要的查詢椭员，包括等值連接查詢车海，自然連接查詢，非等值查詢隘击，自身連接查詢侍芝，外連接查詢和復(fù)合連接查詢。
1埋同、等值與非等值連接查詢
連接查詢的where子句中用來(lái)連接兩個(gè)表的條件成為連接條件州叠。
格式：表名.列名 比較運(yùn)算符 表名.列名
Student.Sno=Sc.Sno
格式：表名.列名 between 表名.列名 and 表名. 列名
比如要查詢每個(gè)學(xué)生及其選修課程
select Student.*,Sc.* from Student,SC where Student.Sno=SC.Sno;
多表查詢
連接操作除了可以是兩表連接，一個(gè)表與其自身連接外凶赁，還可以是兩個(gè)以上的表進(jìn)行連接咧栗，后者通常稱為多表連接。
比如要查詢每個(gè)學(xué)生的學(xué)號(hào)虱肄，姓名致板，選修的課程名及成績(jī)。
select Student.Sno,Sname,Cname,Grade from Student,SC,Cource where Student.Sno=SC.Sno and Sc.Cno=Cource.Cno;
