数据库设计
比如我设计了一个基本Todo应用，提供最小化的功能，像创建一个todo note和赋予它一个tag(s)(类别)。因此我们的数据库只需要三个表。
这三个表是:
todos - 存储所有的todo notes
tags - 存储tags的列表
todo_tags- 存储赋给todo的tags
下图解释了表的结构和表之间的关系：
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中可以这样创建这三个表
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);
}
学生数据库的设计
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|-
创建表
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)
);
连接查询
若一个查询同时设计两个以上的表，则称为连接查询。
连接查询是关系数据库中最主要的查询，包括等值连接查询，自然连接查询，非等值查询，自身连接查询，外连接查询和复合连接查询。
1、等值与非等值连接查询
连接查询的where子句中用来连接两个表的条件成为连接条件。
格式：表名.列名 比较运算符 表名.列名
Student.Sno=Sc.Sno
格式：表名.列名 between 表名.列名 and 表名. 列名
比如要查询每个学生及其选修课程
select Student.*,Sc.* from Student,SC where Student.Sno=SC.Sno;
多表查询
连接操作除了可以是两表连接，一个表与其自身连接外，还可以是两个以上的表进行连接，后者通常称为多表连接。
比如要查询每个学生的学号，姓名，选修的课程名及成绩。
select Student.Sno,Sname,Cname,Grade from Student,SC,Cource where Student.Sno=SC.Sno and Sc.Cno=Cource.Cno;
网友评论