SQLite支持的SQL数据操作

2021-09-27 14:14:13

事务处理

Posted on 2013 年 1 月 1 日 by 林溪
 

事务为一组SQL命令的集合，这些SQL命令在执行时不可进行分割，即要么全部执行这些SQL命令，要么一个都不进行执行，事务操作不允许出现执行SQL命令集合中的一部分命令。
事务的提交
事务的提交是指对这个事务中的SQL命令全部执行，更新数据库的状态。下面的事务提交完成后会向数据库中增加13个数据记录。

  1. BEGIN TRANSACTION;
  2. INSERT INTO students VALUES(0,'Zhang Lincea',13,'F','1999-2-8',701);
  3. INSERT INTO students VALUES(1, 'Zhang Jim',14,'M','1998-10-8',701);
  4. INSERT INTO students VALUES(2, 'Wang Lucy',12,'F','2000-4-13', 501);
  5. INSERT INTO students VALUES(3, 'Wang May',12,'F','2000-3-29', 501);
  6. INSERT INTO students VALUES(4, 'Chang Lenny',13,'M','1999-4-29', 601);
  7. COMMIT TRANSACTION;

事务的启动使用BEGIN TRANSACTION命令，事务的提交使用COMMIT TRANSACTION命令。
事务的回退
事务的回退是指对这个事务的SQL命令取消执行，将数据库的状态回退到执行事务之前的状态。下面使用事务向数据库中添加13条记录，但在添加13条数据记录后，事务进行了回退处理，那么这些添加记录操作全部取消，不会向数据库中添加一条记录。

  1. BEGIN;
  2. INSERT INTO students VALUES(0,'Zhang Lincea',13,'F','1999-2-8',701);
  3. INSERT INTO students VALUES(1, 'Zhang Jim',14,'M','1998-10-8',701);
  4. INSERT INTO students VALUES(2, 'Wang Lucy',12,'F','2000-4-13', 501);
  5. INSERT INTO students VALUES(3, 'Wang May',12,'F','2000-3-29', 501);
  6. INSERT INTO students VALUES(4, 'Chang Lenny',13,'M','1999-4-29', 601);
  7. ROLLBACK;

事务的启动使用BEGIN TRANSACTION命令，事务的回退使用ROLLBACK TRANSACTION命令，事务处理命令可以简写，省略TRANSACTION关键字。

Posted in SQLite数据库 | Tagged Transaction, 事务回退, 事务处理, 事务提交 | Leave a reply

触发器

Posted on 2013 年 1 月 1 日 by 林溪
 

触发器是在表中当发生某些事件（如添加、删除或更新等）而自动执行的数据库SQL语句。SQLite常见的触发器为添加触发器、删除触发器、更新触发器和替代触发器。

添加触发器

添加触发器是指在向表中添加数据记录时所触发的一些列SQL命令操作，这类触发器可以用作数据库表自动备份。下面的命令创建了一个添加触发器tgr_insert。

  1. CREATE TRIGGER tgr_insert AFTER INSERT ON students
  2. BEGIN
  3. INSERT INTO tbl_insert(id, name, age, gender, birthday, teacher_id)
  4. VALUES(NEW.id, NEW.name, NEW.age, NEW.gender, NEW.birthday, NEW.teacher_id);
  5. END;

表示当向表students中增加一条数据记录之后，触发器会自动向另一个表tbl_insert中增加一条同样的记录。触发器中使用的关键字NEW表示新增的记录对象，使用此对象可以访问这个记录的各个字段值。

下面我们使用INSERT命令向students表中新增一条数据记录：

  1. INSERT INTO students( name, age, gender, birthday,teacher_id)
  2. VALUES('Wang Sean',13,'M','1999-1-1', 601);

数据在完成添加到students表中之后，触发器tgr_insert会将新增的这条记录插入到tbl_insert表中。查询表tbl_insert：

  1. SELECT * FROM tbl_insert;

其结果显示为
SQLite支持的SQL数据操作

删除触发器

删除触发器是当在表中执行删除操作时触发的一系列SQL命令。删除触发器可以作为数据库操作的日志系统，记录下没一次删除操作的数据记录。
下面命令创建一个删除触发器tgr_delete，BEFORE表示在将数据记录从表students删除之前触发器就会触发，所执行的命令为向tbl_delete表中增加刚才删除的数据记录。OLD表示刚刚删除的数据记录对象，SQL触发指令可以使用此对象获取数据记录的字段值。

  1. CREATE TRIGGER tgr_delete AFTER DELETE ON students
  2. BEGIN
  3. INSERT INTO tbl_delete(id, name, age, gender, birthday, teacher_id)
  4. VALUES(OLD.id, OLD.name, OLD.age, OLD.gender, OLD.birthday, OLD.teacher_id);
  5. END;

在表students中执行删除操作，删除所有含有“Zhang”的数据记录：

  1. DELETE FROM students WHERE name LIKE '%Zhang%';

查看表tbl_delete中是否添加了刚刚删除的记录：

  1. SELECT * FROM tbl_delete;

查询结果为
SQLite支持的SQL数据操作

更新触发器

更新触发器是当在表中执行更新操作时触发的一系列SQL命令。更新触发器可以作为数据库操作的日志系统，记录下每一次更新操作的数据记录，此记录包含更新之前和之后的记录，分别使用OLD和NEW表示。
下面命令创建一个更新触发器tgr_update，BEFORE表示在将数据记录从表students更新之前触发器就会触发，所执行的命令为向tbl_update表中增加刚才删除的数据记录和新增的记录。OLD表示刚刚删除的数据记录对象，NEW表示刚刚新增的数据记录对象，SQL触发指令可以同时使用这两个对象获取数据记录对应新旧字段值。

  1. CREATE TRIGGER tgr_update AFTER UPDATE ON students
  2. BEGIN
  3. INSERT INTO tbl_update(id, name, age, gender, birthday, teacher_id)
  4. VALUES(NEW.id, NEW.name, NEW.age, NEW.gender, NEW.birthday, NEW.teacher_id);
  5. INSERT INTO tbl_update(id, name, age, gender, birthday, teacher_id)
  6. VALUES(OLD.id, OLD.name, OLD.age, OLD.gender, OLD.birthday, OLD.teacher_id);
  7. END;

执行下面的更新命令， 将name为“Wang Mouse”的数据记录的name字段值修改为“Li Suan”，将gender字段值修改为“F”。

  1. UPDATE students SET name = 'Li Susan', gender = 'F'
  2. WHERE name = 'Wang Mouse';

触发器会添加两条数据记录操作，一个是更新之前的记录，另一个是更新之后的记录，执行下面的命令查询tbl_update表：

  1. SELECT * FROM tbl_update;

SQLite支持的SQL数据操作

替代触发器

替代触发器通常是建立在视图之上，结合ON INSERT，ON DELETE和ON UPDATE，替代触发器可以使视图支持INSERT，DELETE和UPDATE操作，但它与普通表还是有所区别，当在创建有替代触发器的视图上进行操作时，触发器的SQL操作将会被执行。
下面演示如何为视图增加INSERT命令操作，先创建一个视图viw_students，执行命令：

  1. CREATE VIEW IF NOT EXISTS viw_students AS
  2. SELECT name, age, gender, birthday, teacher_id FROM students;

这个视图相当于表students的一个别名。

接下来，在视图viw_students上创建一个替代触发器tgr_on_viw_students，INSTEAD OF表示所创建的触发器类型为替换触发器，当在视图viw_students上执行INSERT命令时，这个触发器的SQL操作将会被执行，即将数据记录添加之students表中。

  1. CREATE TRIGGER tgr_on_viw_students INSTEAD OF INSERT ON viw_students
  2. BEGIN
  3. INSERT INTO students(name, age, gender, birthday, teacher_id)
  4. VALUES(NEW.name, NEW.age, NEW.gender, NEW.birthday, NEW.teacher_id);
  5. END;

完成替代触发器的创建之后，直接向视图viw_students中插入数据，执行下面的命令：

  1. INSERT INTO viw_students(name, age, gender, birthday, teacher_id)
  2. VALUES( 'Wang Sean',13,'M','1999-1-1', 601);

查看视图中是否包含了新增的数据记录

  1. SELECT * FROM viw_students;

替代触发器将数据添加至表students中，故查询viw_students视图时可以显示刚刚插入的数据记录。
替代触发器的DELETE和UPDATE命令使用方式与INSERT相同，只是有DELETE和UPDATE命令来触发，NEW和OLD数据记录对象同样适用于这几个替代触发器。
SQLite支持的SQL数据操作

Posted in SQLite数据库 | Tagged SQL触发器, Trigger, 删除触发器, 更新触发器, 替代触发器, 添加触发器| Leave a reply

视图

Posted on 2013 年 1 月 1 日 by 林溪
 

视图在数据库中是一个虚拟表，它可以是一个或多个表查询的结果，其数据不独立存储，而是动态更新，当查询表的数据发生变化时，视图的内容也会做相应的更新，因此视图为一个动态表。视图可以简化复杂数据查询操作。但SQLite的视图不能向普通表那样进行添加、删除或更新操作。视图操作命令为CREATE VIEW，下面的命令创建一个视图。

  1. CREATE VIEW students_teachers AS
  2. SELECT s.name, s.age, t.id, t.name AS Teacher
  3. FROM students AS s INNER JOIN teachers AS t
  4. ON s.teacher_id = t.id;

SQLite支持的SQL数据操作
CREATE VIEW命令后接所要创建的视图名称，AS关键字之后为生成视图的查询命令，本例为students表与teachers表内连接查询，视图创建之后，就可以像使用表一样进行查询了。
下面的命令执行从students_teachers视图查询命令，显示视图中所有的记录，如下图所示。
如果我们试图从视图中添加、删除或更新记录，如执行下面的删除命令

  1. DELETE FROM students_teachers;

SQLite会给出错误信息，提示视图是不允许进行编辑操作的。
Query Error: cannot modify students_teachers because it is a view Unable to execute statement
如果我们向视图的原始数据表中添加、删除或更新数据，查看视图的数据记录会有哪些变化。向students表中添加一条记录。

  1. INSERT INTO students( name, age, gender, birthday,teacher_id)
  2. VALUES('Wang Sean',13,'M','1999-1-1', 701);

SQLite支持的SQL数据操作
我们再来查看视图students_teachers的输出结果中是否包含刚刚加入的记录，执行查询命令：

  1. SELECT * FROM students_teachers;

SQLite支持的SQL数据操作
从查询结果来看，新添加的记录已经包含在视图students_teachers中，视图进行了动态更新。

Posted in SQLite数据库 | Tagged SQL视图, VIEW, 动态更新, 虚拟表 | Leave a reply

索引

 

索引的主要目的是为了加速数据库查询速度，它是基于表中的一列或多列而创建。虽然索引可以加快数据库查询速度，但创建索引需要额外的存储空间，这个空间会随着索引创建列的个数多少而变化。

索引的创建

索引的创建使用CREATE INDEX命令，每一个索引都创建在一个表或视图中，如果索引所依赖的数据表被删除了，则这个表对应的所有索引也将随之被删除。索引的创建可以基于表一个或多个列，下面的命令创建了基于三个列(name, age和gender）的索引，名称为 idx_student

  1. CREATE INDEX idx_student ON students( name, age, gender);

SQLite支持的SQL数据操作
图显示了索引与表的关系，创建了索引index_students之后，查询时使用name, age或gender将会是哦那个索引加快查询速度。

性能比较

索引可以加快数据查询速度，下表列出了1000000条数据记录在使用和不使用索引的情况下查询时间。

无索引 244 236 242 236 236 236 236 236 237 236
有索引 188 175 175 175 176 175 176 175 175 176

注：数据查询时间为ms

索引个数对存储空间影响

下面我们将创建多个索引，每一个索引采用一个字段, 一共创建5个含有一个字段的索引F1~F5，统计在不同索引数目的情况下数据库占用的存储空间。

  1. CREATE INDEX Fn ON  idx_random(F1);
索引个数 0 1 2 3 4 5
数据库大小 22975488 35492864 48009216 60524544 73040896 85557248

注：数据库大小单位为字节

列个数对存储空间影响

下面对students表创建了多个索引，每一个索引基于表的一个列，表x显示了不同个数的索引占用的存储空间大小。
创建一个索引，使用不同个数的字段, 一共创建5个字段，索引含有的字段为1~5个。
CREATE INDEX Fn ON tbl_random(F1, F2, F3, F4, F5);

字段个数 0 1 2 3 4 5
数据库大小 22975488 35492864 38790144 42011648 45302784 48563200

注：数据库大小单位为字节
从这两个表中可以看出， 使用多个字段创建索引时，每增加一个字段，数据库的大小会相应的增加；每创建一个索引，数据库的大小也会相应的增加；增加索引所占用的存储空间远大于索引增加字段所占用空间。

索引的删除

索引的删除可以分为直接删除和间接删除，直接删除是通过索引的删除命令DROP INDEX进行删除，如下面的命令，删除索引F1

如果在索引不存在的情况下执行上述删除索引的命令，SQLite会因找不到对应索引而报错，此时可以使用IF EXISTS进行修饰。

  1. DROP INDEX IF EXISTS F1；

此命令只在索引存在的情况下将其删除，否则不进行操作。
间接删除索引是索引所基于的表被删除了，则这个索引也会同时被删除。

Posted in SQLite数据库 | Tagged Index, 创建索引, 删除索引, 索引性能分析, 索引空间分析 | Leave a reply

更新记录

 

更新操作是将数据库中的记录按照指定的条件对一条或多条记录进行数据更新，如果没有指定更新条件则更新表中所有的记录。下面的命令对所有名称为”Zhang Jim”的记录进行更新，将name字段值更新为“Li Susan”，gender字段值更新为”F”。

  1. UPDATE students SET name = 'Li Susan', gender = 'F'
  2. WHERE name = 'Zhang Jim';

SQLite支持的SQL数据操作
如果为对更新的数据添加更新条件，则更新所有的数据记录，如下命令将更新所有的记录name字段值为”Li Susan”，gender字段值为“F”。

  1. UPDATE students SET name = 'Li Susan', gender = 'F';<br>

SQLite支持的SQL数据操作

Posted in SQLite数据库 | Tagged UPDATE, 更新记录 | Leave a reply

删除记录

 

删除操作是将数据库中的记录按照指定的条件删除，如果没有指定删除条件则删除表中所有的记录。
下面的命令删除所有年龄大于13的学生记录

  1. DELETE FROM students WHERE age > 13;

SQLite支持的SQL数据操作
如果要删除students表中所有的数据，执行下面的命令

  1. DELETE FROM students;

Posted in SQLite数据库 | Tagged DELETE, 删除记录 | Leave a reply

多表查询

 

多表查询是查询数据分布在两个表中，习惯上称之为左表和右表。根据对两个表中非公共记录处理的方式不同，将多表查询分为：内连接查询、交叉查询、左连接查询、右连接查询和全连接查询等。
下面演示将采用两个表，students表作为左表，teachers表作为右表，其内容为：
SQLite支持的SQL数据操作
student表
SQLite支持的SQL数据操作
teachers表

内连接

两个表只显示两个表共有的数据记录。下面查询命令

  1. SELECT s.name, s.age, c.id, c.name
  2. FROM students AS s INNER JOIN teachers AS c
  3. ON s.teacher_id = c.id;

SQLite支持的SQL数据操作
此输出结果中只包含students表中teacher_id为701的学生；但不包含teacher表中id为101的老师，也不包含students表中的501和601的学生信息。这些信息仅仅存在各自的表，无法在对方表中进行匹配。

交叉连接

左表中的每一条记录都和右表中的记录进行组合，两个表进行乘积运算，得到一个包含两个表所有数据的组合表。

  1. SELECT s.name, s.age, c.id, c.name
  2. FROM students AS s CROSS JOIN teachers AS c;

查询结果包含了左表和右表中所有的记录信息，由于交叉连接查询会生成庞大的数据表，故在使用此查询方式时一定要明确这么做的目的是什么。
SQLite支持的SQL数据操作

左连接

左连接查询的查询重点在左表，查询的结果中包含左表中的每一条记录，即使右表中没有对应的记录，同样会显示在查询的结果中。

  1. SELECT s.name, s.age, c.id, c.name
  2. FROM students AS s LEFT OUTER JOIN teachers AS c
  3. ON s.teacher_id = c.id
  4. ORDER BY c.id;

在查询结果中由于teacher_id字段值501和601指定的信息在teachers表中不存在，但查询结果同样会显示，只不过以空值显示，而内连接查询则不显示这些字段内容。
SQLite支持的SQL数据操作

右连接

右连接查询的查询重点在右表，查询的结果中包含右表中的每一条记录，即使左表中没有对应的记录，同样会显示在查询的结果中。遗憾的是，SQLite不支持右连接查询，但从右连接定义来看，我们通过左连接同样可以实现右连接查询操作，只需要调换左连接查询的左表与右表的位置。
SQLite支持的SQL数据操作
从查询结果来看，teachers表中id字段值101在students表中不存在对应的信息，但左连接查询将teachers表中所有的记录全部显示出来，包括students表中没有的记录。

Posted in SQLite数据库 | Tagged 交叉连接, 内连接, 右连接, 左连接 | Leave a reply

上一篇：java开发实用技巧(更新中...)


下一篇：MySQL数据库学习一