SQLite3 案例讲解.docx
《SQLite3 案例讲解.docx》由会员分享，可在线阅读，更多相关《SQLite3 案例讲解.docx（18页珍藏版）》请在冰点文库上搜索。
SQLite3案例讲解
SQLite3实验指导书
——2015版
实验1SQLite使用基础
数据类型
NULL:
表示该值为NULL值。
INTEGER:
无符号整型值。
REAL:
浮点值。
TEXT:
文本字符串，存储使用的编码方式为UTF-8、UTF-16BE、UTF-16LE。
BLOB:
存储Blob数据，该类型数据和输入数据完全相同，1表示true，0表示false。
SQLite数据库常用约束如下：
∙NOTNULL-非空
∙UNIQUE-唯一
∙PRIMARYKEY-主键
∙FOREIGNKEY-外键
∙CHECK-条件检查
∙DEFAULT-默认
主键
一般是整数或者字符串，只要保证唯一就行。
在SQLite中，主键如果是整数类型，该列的值可以自动增长。
默认值DEFAULT
有一些特别的字段列，在每一条记录中，他的值基本上都是一样的。
只是在个别情况下才改为别的值，这样的字段列我们可以给他设一个默认值。
非空NOTNULL
有一些字段我们可能一时不知到该填些什么，同时它也没设定默认值，当添加数据时，我们把这样的字段空着不填，系统认为他是NULL值。
但是还有另外一类字段，必须被填上数据，如果不填，系统就会报错。
这样的字段被称为NOTNULL非空字段,需要在定义表的时候事先声明。
唯一
除了主列以为，还有一些列也不能有重复值
条件检查CHECK
某些值必须符合一定的条件才允许存入，这是就需要用到这个CHECK约束。
CREATE TABLE Teachers（Id integer PRIMARY KEY,Age integer CHECK（Age>22））;
外键FOREIGNKEY
我们的数据库中已经有Teachers表了，假如我们再建立一个Students表，要求Students表中的每一个学生都对应一个Teachers表中的教师。
很简单，只需要在Students表中建立一个TeacherId字段，保存对应教师的Id号，这样，学生和教师之间就建立了关系。
问题是：
我们有可能给学生存入一个不在Teachers表中的TeacherId值，而且发现不了这个错误。
这种情况下，可以把Students表中TeacherId字段声明为一个外键，让它的值对应到Teachers表中的Id字段上。
这样，一旦在Students表中存入一个不存在的教师Id，系统就会报错。
实验目的：
1．熟悉Windows基础上的SQLite，掌握SQLite命令行程序（CLP）的使用方法；
2．掌握数据库和数据表的创建和删除，实践SQL的CREATE、ALTER和DROP命令；
3．掌握数据记录的插入方法。
实验内容：
1．SQLiteCLP是使用和管理SQLite数据库最常用的工具。
通过示例数据库foods.db熟悉并掌握常用的CLP的命令。
2.创建教学管理“JXGL”数据库，并建立学生STUDENT、课程COURSE和选修SC三个数据表，各表的结构及数据如下所示：
学生STUDENT：
STUDEN表结构
属性名
类型
完整性要求
Sno
Integer
主键
Sname
Text
不为空
Ssex
Text
默认值为”男”
Sage
Integer
默认值为19
Sdept
Text
默认值为”CS”
STUDEN表数据
Sno
Sname
Ssex
Sage
Sdept
200215121
李勇
M
20
CS
200215122
刘晨
F
19
CS
200215123
王敏
F
18
MA
200215124
张立
M
18
IS
课程COURSE：
COURSE表结构
属性名
类型
完整性要求
Cno
Integer
主键
Cname
Text
不为空
Cpno
Text
外键，引用Cno，可以为空
Ccredit
Integer
可以为空
COURSE表数据
Cno
Cname
Cpno
Ccredit
1
数据库
5
4
2
数学
2
3
信息系统
1
4
4
操作系统
6
3
5
数据结构
7
4
6
数据处理
2
7
C语言
6
4
选修SC：
SC表结构
属性名
类型
完整性要求
Sno
Integer
与Cno构成复合主键，外键引用Student中的Sno
Cno
Integer
与Sno构成复合主键，外键引用Course中的Cno
Grade
Integer
SC表数据
Sno
Cno
Grade
200215121
1
92
200215121
2
85
200215121
3
88
200215122
2
90
200215122
3
80
3.创建供应系统“GYXT”数据库，其中包括供应商表S、零件表P、工程项目表J和供应情况表SPJ四个数据表，各表中数据如下所示，表结构自己分析并确定。
供应商S：
S表数据
SNO
SNAME
CITY
S1
精益
天津
S2
万胜
北京
S3
东方
北京
S4
丰泰隆
上海
S5
康健
南京
零件P：
P表数据
PNO
PNAME
COLOR
WEIGHT
P1
螺母
红
12
P2
螺栓
绿
17
P3
螺丝刀
蓝
14
P4
螺丝刀
红
14
P5
凸轮
蓝
40
P6
齿轮
红
30
工程项目J：
J表数据
JNO
JNAME
CITY
J1
三建
北京
J2
一汽
长春
J3
弹簧厂
天津
J4
造船厂
天津
J5
机车厂
唐山
J6
无线电厂
常州
J7
半导体厂
南京
供应情况SPJ：
SPJ表数据
SNO
PNO
JNO
QTY
S1
P1
J1
200
S1
P1
J3
100
S1
P1
J4
700
S1
P2
J2
100
S2
P3
J1
400
S2
P3
J2
200
S2
P3
J4
500
S2
P3
J5
400
S2
P5
J1
400
S2
P5
J2
100
S3
P1
J1
200
S3
P3
J1
200
S4
P5
J1
100
S4
P6
J3
300
S4
P6
J4
200
S5
P2
J4
100
S5
P3
J1
200
S5
P6
J2
200
S5
P6
J4
500
实验步骤：
1．用SQLite示例数据库了解SQLite的命令行程序中的常用命令。
首先，在WindowsXP“开始”菜单的“运行”---“打开”输入框中输入“CMD”进入DOS环境。
然后使用如下命令进入SQLite3的shell程序环境。
D:
Cdsqlite3
Sqlite3foods.db
说明：
用sqlite3foods.db进入CLP环境的同时打开foods数据库（如果foods.db数据库不存在就创建一个新的数据库文件）。
下面开始学习Shell模式中的各常用命令的使用，具体命令的练习如下所示。
通过练习掌握每个命令的功能。
sqlite>.help--可显示shell模式中可使用的所有命令列表
sqlite>.modecolumn--使得SQL语句处理的结果以列对齐的方式显示
sqlite>.headerson--打开列标题显示开关，以使得查询结果在屏幕显示时具有列标题
sqlite>.tables--列出当前打开的数据库中共有多少张表
sqlite>Select*fromfoods;--查询foods表中的记录数据
sqlite>.exit--退出SQLite环境
F:
\sqlite3>Sqlite3foods.db--再次从DOS环境进入SQLite环境，
同时打开数据库文件foods.db
sqlite>.schemafoods--显示表foods创建时的SQL语句
sqlite>.schema--显示所有表被创建时的语句
sqlite>.dump--把创建表及向表中插入记录的所有SQL语句显示在屏幕上
sqlite>.outputfoodsdb.sql--指定dump命令输出到文件的文件名
sqlite>.dump--输出创建并插入数据到基本表的SQL语句到output指定的文件
sqlite>.outputstdout--恢复输出内容到标准输出设备（屏幕）
sqlite>.dump--此时输出的SQL语句转回到屏幕
sqlite>.show--显示shell模式中定义的与输出相关的一些设置
sqlite>Droptablefoods;--删除foods表语句
sqlite>Droptablefood_types;--删除food_types表语句
sqlite>Droptableepisodes;--删除episodes表语句
sqlite>Droptablefoods_episodes;--删除foods_episodes表语句
说明：
上述Drop为SQL语句，用于删除指定的表。
因为是SQL语句，因此以“;”结尾
sqlite>.readfoodsdb.sql--执行foodsdb.sql中的包含的所有SQL语句，用来重建刚删除的4张表及相关数据
sqlite>.outputfile.csv--设置输出文件格式为CSV，文件名为file.csv
sqlite>.separator,--设置select语句输出的列数据间以“，”分隔
sqlite>Select*fromfoods;--查询显示foods表中所有的数据
sqlite>.outputstdout--恢复输出内容到标准输出设备（屏幕）
注意：
下面的4个命令与上述的4个命令效果相同
sqlite>.outputfile.csv
sqlite>.modecsv
sqlite>Select*fromfoods;
sqlite>.outputstdout
其次，了解在DOS环境中使用SQLite的相关命令
F:
\sqlite3>Sqlite3foods.db.dump--在显示器上显示创建foods数据库中各表的语句
及数据插入语句
F:
\sqlite3>Sqlite3foods.db.dump>fooddb.sql--将创建foods数据库中各表的语句
及数据插入语句输出到文件fooddb.sql中保存
F:
\sqlite3>Sqlite3foods.db"select*fromfood_types"--查询foods数据库中food_types表中内容，并在dos环境中显示查询结果
F:
\sqlite3>Sqlite3foods2.db--用foodsdb.sql中的语句创建数据库foods2.db
F:
\sqlite3>Sqlite3–initfoodsdb.sqlfoods3.db.exit
--用foodsdb.sql中的语句创建数据库foods3.db
2．用SQL命令创建教学管理“JXGL”数据库。
（1）创建教学管理“JXGL”数据库。
在DOS命令窗口中，使用如下命令。
F:
\sqlite3>Sqlite3JXGL.db
（2）显示命令提示符sqlite3>后，输入如下SQL语句：
CREATETABLESTUDENT（Snointegerprimarykey,Snametextnotnull,Ssextextdefault‘男’,Sageintegerdefault19,Sdepttextdefault‘CS’）;
结构创建完成后，用下述语句插入数据：
INSERTINTOSTUDENTVALUES（‘95001’,’李勇’,’M’,20,’CS’）;
INSERTINTOSTUDENTVALUES（‘95002’,’刘晨’,’F’,19,’IS’）;
INSERTINTOSTUDENTVALUES（‘95003’,’王敏’,’F’,18,’MA’）;
INSERTINTOSTUDENTVALUES（‘95004’,’张立’,’M’,18,’IS’）;
插入的数据只初始化部分值
insertintostudent（sname,sage）values（'pengd',19）;
删除一行信息
deletefromstudentwheresname='zhangli';
修改一条记录的某个内容
UPDATEstudentSETsage=19WHEREsname='lilei';
（3）用类似方法创建COURSE、SC表并插入相关数据。
（4）用SELECT语句查看三个表中的内容。
SELECT*FROMSTUDENT;
Select*fromstudentwheresname=’lilei’;
Select*fromstudentwheresno=20;
selectspwdfromstudentwheresname='lilei';
sqlite>select*fromstuwheresname='lilei'andspwd='123456';
（5）修改数据表结构。
向STUDENT表增加“入学时间”列，其数据类型为TEXT型，用SELECT命令查看表中内容。
ALTERTABLESTUDENTADDspwdTEXTdefault‘123456’;
SELECT*FROMSTUDENT;
updatestudentsetspwd='123456'wheresname='lilei';
（6）删除数据表。
DROPTABLESTUDENT;
（7）修改表名字
altertablestudentrenametostu
（8）创建时间表
CREATETABLEtimes（idint,
dateNOTNULLDEFAULTCURRENT_DATE,
timeNOTNULLDEFAULTCURRENT_TIME,
timestampNOTNULLDEFAULTCURRENT_TIMESTAMP）;
INSERTINTOtimes
（1）;
INSERTINTOtimes
（2）;
SELECT*FROMStimes;
3．创建供应系统“GYXT”数据库。
下面写出实现如下操作的SQL语句：
（1）创建供应系统“GYXT”数据库。
（2）建立供应商表S。
（3）建立零件表P。
（4）建立工程项目表J。
（5）建立供应情况表SPJ。
SQLite外键（ForeignKey）支持
从SQLite3.6.19开始支持外键约束.（Ubuntu10.04的SQLite版本是3.6.22, Debian6.0的SQLite版本是 3.7.0）
外键约束用来强制两个表之间”存在”的关系.比如,考虑下面的SQL命令建立的schema
CREATETABLEartist（
artistidINTEGERPRIMARYKEY,
artistnameTEXT
）;
CREATETABLEtrack（
trackidINTEGER,
tracknameTEXT,
trackartistINTEGER—必须映射到一个artist.artistid记录
）;
使用这个数据库的应用可以假定:
对于在track表里每一行,都在artist表都存在一个对应的行. 不幸的是,如果用户使用外部工具编辑数据库,或者在应用程序中存在一个bug.那么可能在track表中插入一行,而在artist表中没有相应的记录.或者,在artist表中删除某些行, 就会在track表里留下孤儿行（orphanedrows）, 它们在artist表中剩下的记录找到任何对应的行. 这可能在以后会导致应用的功能出错.或者至少让编写应用程序更复杂.
一个解决方法就是,为数据库添加一个外键约束,在artist和track这两个表之间强制实施一个约束.增加外键定义的track表的定义如下:
CREATETABLEtrack（
trackid INTEGER,
trackname TEXT,
trackartistINTEGER,
FOREIGNKEY（trackartist）REFERENCESartist（artistid）
）;
这样,外键约束就由SQLite强制实施. 往track表插入一行在artist表中没有对应的数据的记录的企图注定是要失败的,o（∩∩）o
如果在track表还存在依赖于artist中的某行的记录,那么尝试从artist表删除该行,也会失败.
也就是说,对于在track表中的每一行,,下面的表达式都是真:
trackartistISNULLOREXISTS（SELECT1FROMartistWHEREartistid=trackartist）
在SQLite中启用外键支持
1）为了在SQLite中使用外键约束,编译sqlite库时,不能使用忽略外键和触发器,也就是 SQLITE_OMIT_FOREIGN_KEY和 SQLITE_OMIT_TRIGGER不能被定义
2）必须在运行时打开,因为 默认是关闭的
PRAGMAforeign_keys=ON;
要求和建议的数据库索引
通常,外键约束的父键在父表里是主键.如果它们不是主键,那么父键栏必须受一个UNIQUE约束或者有一个UNIQUE索引.
如果数据库schema还有外键错误,就需要查看多个表才能找到错误.数据表创建时不会检测这些错误,
这些错误会阻止应用程序用SQL语句来修改子表或者父表的内容.当内容被改变时,报告”DMLerrors”;当schema被改变时报告”DDLerrors”
也就是说,错误地配置外键约束,要求检查子表和父表的是DML错误,一般显示”foreignkeymismatch”或者“nosuchtable”
SELECTrowidFROMWHERE=:
parent_key_value
如果这个SELECT返回数据,那么SQLite就断定,从父表删除某行,将会违背外键约束,并返回错误.如果父键的值被修改或者在父表插入新的一行,也会产生类似的查询.
如果这些查询没有使用索引,它们将强迫对整个子表做线性查找（scan）,这代价可太大了
在大多数实际系统中, 应该在子键这一栏建立索引. 子键的索引不必（并且是通常都不必）有一个UNIQUE索引（因为在子表中的多行对应于父表中的一行）
CREATEINDEXtrackindexONtrack（trackartist）;
ONDELETE和ONUPDATE行为
外键的ONDELETE和ON UPDATE从句,可以用来配置 当从父表中删除某些行时发生的行为（ONDELETE）. 或者修改存在的行的父键的值,发生的行为（ONUPDATE）
单个外键约束可以为ONDELETE和ONUPDATE配置不同的行为. 外键行为在很多时候类似于触发器（trigger）
ONDELETE和ONUPDATE的行为是NOACTION, RESTRICT,SETNULL, SETDEFAULT或者CASCADE
如果没有明确指定星闻,那么默认就是NOACTION
NOACTION:
当父键被修改或者删除时,没有特别的行为发生
RESTRICT:
存在一个或者多个子键对应于相应的父键时, 应用程序禁止删除（ONDELETERESTRICT）或者修改（ONUPDATERESTRICT）父键
RESTRICT与普通的外键约束的区别是, 当字段（field）更新时,RESTRICT行为立即发生
SETNULL:
父键被删除（ONDELETESETNULL）或者修改（ONUPDATESETNULL）
SETDEFAULT:
类似于SETNULL
CASCADE:
将实施在父键上的删除或者更新操作,传播给与之关联的子键.
对于ONDELETECASCADE,同被删除的父表中的行相关联的子表中的每1行,也会被删除.
对于ONUPDATECASCADE, 存储在子表中的每1行,对应的字段的值会被自动修改成同新的父键匹配
举例:
CREATETABLEartist（
artistid INTEGERPRIMARYKEY,
artistname TEXT
）;
CREATETABLEtrack（
trackid INTEGER,
trackname TEXT,
trackartistINTEGERREFERENCESartist（artistid）ONUPDATECASCADE
）;
INSERTINTOartistVALUES（1,‘DeanMartin’）;
INSERTINTOartistVALUES（2,‘FrankSinatra’）;
INSERTINTOtrackVALUES（14,‘Mr.Bojangles’,2）;
INSERTINTOtrackVALUES（15,“That’sAmore”,2）;
INSERTINTOtrackVALUES（12,‘ChristmasBlues’,1）;
INSERTINTOtrackVALUES（13,‘MyWay’,2）;
sqlite>PRAGMAforeign_keys=ON;
（默认是关闭的,要在运行时打开）
sqlite>SELECT*FROMartist;
1|DeanMartin
2|FrankSinatra
sqlite>SELECT*FROMtrack;
14|Mr.Bojangles|2
15|That’sAmore|2
12|ChristmasBlues|1
13|MyWay|2
sqlite>UPDATEart
升级会员 