sqlite3实验指导书

——2014版本

实验3 sqlite中的高级sql

实验目的：

1．熟练掌握在数据库中插入、修改和删除数据的操作方法，加深对标准sql更新语句的理解。

2．了解sqlite中用约束来维护数据完整性的机制。掌握在sqlite中如何创建、使用及删除约束的方法。

3．掌握在sqlite中创建、删除视图的sql语句的用法，加深理解视图的用途。

4．掌握在sqlite中创建、删除索引的sql语句，加深理解索引的用途。

5．掌握在sqlite中创建、删除触发器的sql语句的用法，加深理解触发器的用途。

6．掌握在sqlite中创建事务的sql语句的用法，加深理解事务的各种冲突解决方案。

实验内容：

1．基于实验一创建的教学管理jxgl数据库，参考教材数据更新的sqlite操作实例，实现数据的插入、修改和删除操作。

2．基于实验一创建的供应系统gyxt数据库，实现数据的插入、修改和删除操作。

3．基于实验一创建的教学管理jxgl数据库，实现约束的创建、查看和删除操作。

4．创建人事关系rsgx数据库及其中的职工数据表和部门数据表，并实现完整性约束条件的定义。

5．基于实验一创建的教学管理jxgl数据库及供应系统gyxt数据库，参考教材视图的sql操作实例，实现视图的创建、删除、查询操作。

6．基于jxgl数据库，学习如何创建索引。

7．基于jxgl数据库，创建触发器，体会触发器的作用。

8．基于jxgl数据库，参考教材上事务的应用实例，掌握事务的创建及各种冲突解决方案的用法。

实验步骤：

1．在教学管理jxgl数据库中进行如下操作：

(1) 将一个新学生记录（学号：200215126；姓名：陈冬；性别：男；所在系：is；年龄：18）插入student表中。

insert into student values(200215126,陈冬,男,18,is);

(2) 数据的插入操作：插入一条选课记录，学号为200215126的学生选修了1号课程。

(3) 对每门课程，求学生的平均分数，并把结果存入数据库。

(4) 将学生200215126转到数学系。

(5) 所有学生成绩乘系数0.8。

(6) 删除200215126学生的记录。

(7) 删除数学系所有学生的选课记录。

2．在供应系统gyxt数据库中进行如下操作：

(1) 往s，p，j，spj表中插入部分数据（此题实验一已完成）

(2) 将供应商序号为s5的名称改为宏民厂。

(3) s表中删除供应商序号为s5的记录，考虑对spj表的相应操作。

(4) 在s表中插入一条供应商信息：(s6,华天,深圳)。

(5) 把全部红色零件的颜色改为粉红色。

(6) 将s1供应给j1的零件p1改为由s2供给。

(7) 删去全部蓝色零件及相应的spj记录。

3．完整性约束

约束是sqlite提供的自动强制数据完整性的一种方法，它是通过定义列的取值规则来维护数据的完整性。常用的约束是not null（非空）、unique（唯一性）、defalt（默

认）、check（检查）、primary key（主键）、foreign key（外键）等约束。

在教学管理jxgl数据库中进行如下操作：

(1) 建立一个city表（cityno,cityname），将cityno指定为主键。

用sql语句创建主键，可使用create table命令完成：

create table city

( cityno integer primary key,

cityname varchar(20) not null);

(2) 针对jxgl数据库中的city表，为其“cityname”创建惟一约束。

先删除已经已存的city表，重新修改上面的create table语句，增加创建惟一约束的内容，sql语句如下：

drop table city;

create table city

( cityno integer primary key,

cityname varchar(20) unique not null );

(3) 为jxgl数据库中的sc表，为其“sno”和“cno”创建外键约束，为其“grade”创建检查取值范围为[0,100]的约束。

先删除已经已存的sc表，重新修改原来的create table语句，增加创建check约束的内容，sql语句如下：

drop table sc;

create table sc(sno integer references student(sno),cno integer references course(cno),grade integer,primary key(sno,cno) ,check(grade>=0 and grade<=100));

(4) 为jxgl数据库中的student表，为其“ssex”创建默认约束?m?。

4．创建人事关系rsgx数据库，并定义职工和部门两个关系模式：

职工（职工号，姓名，年龄，职务，工资，部门号），其中职工号为主码；

部门（部门号，名称，经理名，电话），其中部门号为主码；

要求在模式中完成以下完整性约束条件的定义：

(1) 定义每个模式的主码；

(2) 定义参照完整性；

(3) 定义职工年龄不得超过60岁。

5．在教学管理jxgl数据库中进行如下操作：

(1) 建立一个名为“cs_student”的计算机系学生视图，并要求进行修改和插入操作时仍须保证该视图只有计算机系的学生。（注：计算机系的sdept值为“cs”；在使用该视图时，将显示student表中的所有字段。）

输入下列sql语句，然后运行，输出结果。

create view cs_student

as

select sno,sname,ssex,sage

from student

where sdept=cs;

(2) 建立计算机系选修了1号课程的学生的视图cs_s1。（注：使用上题建立的视图cs_student。）

(3) 删除视图cs_s1。

(4) 在计算机系学生视图cs_student中找出选修了“数据库”课程且成绩在90分以上的学生。

sqlite> select vs.sno,sname from cs_student vs,sc,course c where vs.sno=sc.sno a nd c.cno=sc.cno and c.cname=数据库 and grade>90;

6．在供应系统gyxt数据库中进行如下操作：

(1) 创建视图vsj，它记录了给“三建”工程项目的供应零件的情况，包括供应商号、零件号和零件数量。

(2) 对vsj视图查询s1供应商的供货情况。

7．建立索引可加快表的查询速度。dbms一般会在主键列及唯一性列上自动建立索引。

注意：索引是由表的创建者创建的，但表的维护及使用是由dbms自动完成的，用户及dba无法干预。

(1) 创建1张表lx，并以其为基础创建唯一性索引。

create table lx(a text, b text);

create unique index idx_lx on lx(a,b);

insert into lx values(?010?,?beijing?);

insert into lx values(?025?,?nanjing?);

select * from lx;

insert into lx values(?010?,?beijin?);

此语句出错，为什么？

(2) 在jxgl数据库中的student表的sname字段上创建不区分大小写的索引。 create index idx_student_sname on student(sname collate nocase);

8．基于示例数据库foods，学习触发器的创建及应用。

1）触发器练习1篇二：sqlite3命令方式操作大全

sqlite库包含一个名字叫做sqlite3的命令行,它可以让用户手工输入并执行面向sqlite数据库的sql命令。本文档提供一个样使用sqlite3的简要说明.

一.qlite3一些常用sql语句操作

创建表:

删除表:

插入数据:

创建索引:

删除索引：

删除数据:

更新数据:

增加一个列:

选择查询:create table 表名(元素名 类型,…); drop table 表名;insert into 表名 values(, , ,) ; create [unique] index 索引名on 表名(col….); drop index 索引名(索引是不可更改的，想更改必须删除重新建) delete from 表名;update 表名 set 字段=’修改后的内容’ where 条件; alter table 表名 add column 字段 数据类型;select 字段(以”,”隔开) from 表名 where 条件;

日期和时间: select datetime(now)

日期:select date(now);

时间: select time(now);

总数：select count(*) from table1;

求和：select sum(field1) from table1;

平均：select avg(field1) from table1;

最大：select max(field1) from table1;

最小：select min(field1) from table1;

排序：select 字段 from table1 order by 字段(desc或asc) ;(降序或升序)

分组：select 字段 from table1 group by 字段,字段… ;

限制输出:select 字段 from table1 limit x offset y;

= select 字段 from table1 limit y , x;

(备注:跳过y行，取x行数据)

(操作仍待完善)…

sqlite支持哪些数据类型些？

null 值为null

integer 值为带符号的整型，根据类别用1，2，3，4，6，8字节存储

real 值为浮点型，8字节存储

text 值为text字符串，使用数据库编码(utf-8, utf-16be or utf-16-le)存储

blob 值为二进制数据，具体看实际输入

但实际上，sqlite3也接受如下的数据类型：

smallint16 位元的整数

interger32 位元的整数

decimal(p,s) p 精确值和 s 大小的十进位整数，精确值p是指全部有几个数(digits)大小值 ，s是指小数点後有几位数。如果没有特别指定，则系统会设为 p=5; s=0 。

float 32位元的实数。

double 64位元的实数。

char(n) n 长度的字串，n不能超过 254。

varchar(n)长度不固定且其最大长度为 n 的字串，n不能超过 4000。

graphic(n)和 char(n) 一样，不过其单位是两个字元 double-bytes， n不能超过127。这个形态是为了支援两个字元长度的字体，例如中文字。

vargraphic(n) 可变长度且其最大长度为 n 的双字元字串，n不能超过 2000。

date 包含了 年份、月份、日期。

time 包含了 小时、分钟、秒。

timestamp包含了 年、月、日、时、分、秒、千分之一秒。

如果将声明表的一列设置为 integer primary key，则具有：

1．每当你在该列上插入一null值时， null自动被转换为一个比该列中最大值大1的一个整数；

2．如果表是空的， 将会是1；

算术函数

abs(x)返回给定数字表达式的绝对值。

max(x,y[,...])返回表达式的最大值。

min(x,y[,...])返回表达式的最小值。

random(*)返回随机数。

round(x[,y])返回数字表达式并四舍五入为指定的长度或精度。

字符处理函数

length(x)返回给定字符串表达式的字符个数。

lower(x)将大写字符数据转换为小写字符数据后返回字符表达式。

upper(x)返回将小写字符数据转换为大写的字符表达式。

substr(x,y,z)返回表达式的一部分。

randstr()

quote(a)

like(a,b)

确定给定的字符串是否与指定的模式匹配。

glob(a,b)

条件判断函数

coalesce(x,y[,...])

ifnull(x,y)

nullif(x,y)

集合函数

avg(x)返回组中值的平均值。

count(x)返回组中项目的数量。

max(x)返回组中值的最大值。

min(x)返回组中值的最小值。

sum(x)返回表达式中所有值的和。

其他函数

typeof(x)返回数据的类型。

last_insert_rowid()返回最后插入的数据的 id 。

sqlite_version(*)返回 sqlite 的版本。

change_count()返回受上一语句影响的行数。

last_statement_change_count()

二.有关事务的操作

(成批操作的时候，启动事务，比不启动事务快n倍)

开始事物处理

begin transaction;

…………..

进行对数据库操作

…………..

事物提交

具体事例如下:

假设有一个 t1 表，其中有 a, b, c 三列， 如果要删除列 c ，以下过程描述如何做: begin transaction;

create temporary table t1_backup(a,b);

insert into t1_backup select a,b from t1;

drop table t1;

create table t1(a,b);

insert into t1 select a,b from t1_backup;

drop table t1_backup;

三.启动sqlite3程序

仅仅需要敲入带有sqlite数据库名字的sqlite3命令即可。如果文件不存在，则创建一个新的（数据库）文件。然后 sqlite3程序将提示你输入sql。敲入sql语句（以分号“；”结束），敲回车键之后，sql语句就会执行。

例如，创建一个包含一个表tb11名字为ex1的sqlite数据库，你可以这样做： 数据库、表的建立，记录的添加、查询、修改和删除

f:\>sqlite3 database.db

sqlite> create table admin(username text,age integer);

sqlite> insert into admin values(kuang,25);

sqlite> select * from admin;

sqlite> update admin set username=kk,age=24 where username=kuang and age=25; sqlite> delete from admin where username=kk;

注：每条sql语句后必须以;号结尾！

$sqlite3 ex1

sqlite version 3.3.17

enter .help for instructions

sqlite> create table tbl1(one varchar(10), two smallint);

sqlite> insert into tbl1 values(hello!, 10);

sqlite> insert into tbl1 values(goodbye, 20);

sqlite> select * from tbl1;

hello!|10

goodbye|20

sqlite>

你可以通过敲你所用系统的文件结束符（通常是ctrl + d）或者中断字符（通常是ctrl + c）。来终止sqlite3程序。确定你在每个sql语句结束敲入分号！sqlite3程序通过查找分号来决定一个sql语句的结束。如果你省略分 号，sqlite3将给你一个连续的命令提示符并等你给当前的sql命令添加更多的文字。这个特点让你输入多行的多个sql语句，例如： sqlite> create table tbl2(

...> f1 varchar(30) primary key,

...> f2 text,

...> f3 real

...> );

sqlite>

四.题外话：查询sqlite_master表

sqlite数据库的框架被保存在一个名叫sqlite_master的特殊的表中。你可以像查询其它表一样通过执行“select”查询这个特殊的表。例如：

$ sqlite3 ex1

sqlite vresion 3.3.10

enter .help for instructions

sqlite> select * from sqlite_master;

type = table

name = tbl1

tbl_name = tbl1

rootpage = 3

sql = create table tbl1(one varchar(10), two smallint)

sqlite>

但你不能在sqlite_master表中执行诸如drop table, update, insert 或者delete命令。sqlite_master表在你创建、删除和索引数据库时自动更新这个表。你不能手工更改sqlite_master表。

temporary表的结构没有存储在sqlite_master表中，由于temporary表对应用是不可见的，而不是应用程序创建这个表。 temporary表结构被存储在另外一个名叫sqlite_temp_master的特定的表中。sqlite_temp_master表是临 时表自身。

五.sqlite3的特殊命令

大多数候，sqlite3读入输入行，并把它们传递到sqlite库中去运行。但是如果输入行以一个点（“.”）开始，那么这行将被sqlite3程序自 己截取并解释。这些“点命令”通常被用来改变查询输出的格式，或者执行鞭个预封包（预定义prepackaged）的查询语句。 你可以在任何时候输入“.help”，列出可用的点命令。例如

sqlite> .help

.bail on|off

stop after hitting an error. default off(遇到错误时不再继续, 默认为off)

.databases

list names and files of attached databases(列出附加到数据库的数据库和文件)

.dump ?table? ...

dump the database in an sql text format(保存表到sql格式的文件中, 没有指表名, 则保存所有. 如果要保存到磁盘上需要结合 .output 命令.)

.echo on|off

一、查看版本信息：

#sqlite3 -version

二、sqlite3

常用命令

1、当前目录下建立或打开test.db数据库文件，并进入sqlite命令终端，以sqlite>前缀标识：

2、输出帮助信息：

sqlite>.help

3、查看数据库文件信息命令(注意命令前带字符.)：

sqlite>.database

4、退出sqlite终端命令：

sqlite>.quit

或

sqlite>.exit

列出当前显示格式的配置：

sqlite>.show

6、显示数据库结构：.schema

显示表的结构：.schema 表名

其实就是一些 sql 语句，他们描述了数据库的结构，如图

7、导出某个表的数据： .dump 表名

8、设置导出目标：

.output 文件名

或者

.outputstdout

先运行 .output cars.sql ，然后再运行 .dump 命令试试看？如果要回复成导出到终端（标准输出），则运行 .output stdout

10、设置分隔符：.separator 分隔符

我们可以首先运行 select * from cars； ，可以看到默认的分隔符是 |

运行.separator : 以后，再 select * from cars；，可以看到分隔符已经变成 : 了

11、显示标题栏：.headerson

12、设置显示模式：

.mode 模式

有好几种显示模式，默认的是 list 显示模式，一般我们使用 column 显示模式，还有其他几种显示模式可以 .help 看 mode 相关内容。看看下面的图，和上面是不是显示的不一样了？

13、设置 null 值显示成什么样子： .nullvalue 你想要的null值格式

默认情况下null值什么也不显示，你可以设置成你自己想要的样子

14、配置文件 .sqliterc

如果我们每次进入命令行都要重新设置显示格式，很麻烦，其实 .show 命令列出的所有设置项都可以保存到一个 .sqliterc 文件中，这样每次进入命令行就自动设置好了。.sqlterc 文件在 linux 下保存在用户的 home 目录下，在 windows 下可以保存到任何目录下，但是需要设置环境变量让数据库引擎能找到它，感兴趣的可以看看帮助。

三、数据库和表的相关命令

1、创建一个新的数据库：sqlite3 文件名

创建一个 test.db 数据库文件，打开控制台窗口，命令如下：

sqlite>sqlite3 test.db

2、打开一个已经存在的数据库：sqlite3 已经存在的文件名

创建一个新数据库和打开一个已经存在的数据库命令是一模一样的，如果文件在当前目录下不存在，则新建；如果存在，则打开。

3、建立数据表

create table table_name(field1 type1, field2 type1, ...);

table_name是要创建数据表名称，fieldx是数据表内字段名称，typex则是字段类型。 如：该语句创建一个记录学生信息的数据表。

sql的指令格式：所有sql指令都是以分号(;)结尾，两个减号(--)则表示注释。

4、添加数据记录

insert into table_name(列field1, field2, ...) values(值

val1, val2, ...);

valx为需要存入字段的值。

例，往老师信息表添加数据：

很简单，创建了一个 teachers 表并向其中添加了四条数据，设定了一些约束，其中有自动增加的主键、默认值等等。

SQLITEUPDATE语句