SQLite中的高级SQL

发布时间：2020-12-12 19:49:53 所属栏目：百科来源：网络整理

导读：插入记录使用insert插入记录。insert在表单上工作，使用insert命令可以一次插入一条记录，使用select命令可以一次插入多条记录。insert语句的一般格式为： insert into table(column list) values(value_list); 例如： insert into foods (name,type_id) va

插入记录

使用insert插入记录。insert在表单上工作，使用insert命令可以一次插入一条记录，使用select命令可以一次插入多条记录。insert语句的一般格式为：

insert into table(column list) values(value_list);

例如：

insert into foods (name,type_id) values ('Cinnamon Bobka',1);

如果在insert语句中为表的所有字段提供值，可以省略字段列表。例如：

sqlite> insert into foods values (NULL,1,'Blueberry Bobka');

插入一组行

子查询可以用在insert语句中，既可以作为值列表的一部分，也可以完全替代整个值列表。

sqlite> insert into foods
   ...> select last_insert_rowid()+1,type_id,name from foods
   ...> where name = 'Chocolate Bobka';
sqlite> select * from foods where name like '%Bobka';
id          type_id     name           
----------  ----------  ---------------
10          1           Chocolate Bobka
13          1           Cinnamon Bobka 
413         1           Cinnamon Bobka 
414         1           Blueberry Bobka
415         1           Chocolate Bobka

插入多行

使用select形式的insert可以一次插入多行，例如：

sqlite> create table foods2 (id int,type_id int,name text);
sqlite> insert into foods2 select * from foods;
sqlite> select count(*) from foods2;
count(*)  
----------
415

上述语句表示创建一个新表foods，并插入表foods的所有记录。

还有另一种形式：

sqlite> create table foods3 as select * from foods;

create table与从foods表选择数据插入表两步并为一步。这对创建临时表特别有用：

sqlite> select f.name food,t.name name,...> (select count(episode_id) from foods_episodes where food_id = f.id) episodes
   ...> from foods f,food_types t
   ...> where f.type_id = t.id;

更新记录

update命令用于更新表中的记录，update语句的一般格式为：

update table set update_list where predicate

例如：

sqlite> insert into foods values (415,'Chocolate Bobka');
Error: UNIQUE constraint failed: foods.id
sqlite> update foods set name = 'CHOCOLATE BOBKA'
   ...> where name = 'Chocolate Bobka';
sqlite> select * from foods where name like 'CHOCOLATE%';
id          type_id     name           
----------  ----------  ---------------
10          1           CHOCOLATE BOBKA
11          1           Chocolate Eclai
12          1           Chocolate Cream
222         9           Chocolates,box
223         9           Chocolate Chip 
224         9           Chocolate Cover
415         1           CHOCOLATE BOBKA

删除记录

使用delete命令可以删除表中的记录。一般格式为：

delete from table where predicate

实体完整性

数据库的所有域都是唯一的、可以定位的。域要可寻址，它相应的行必须是可以寻址的。因此，行必须在某种方式是唯一的。这就是主键的功能。

主键由至少带有unique约束的一个或一组字段组成。

唯一性约束

唯一性（unique）约束是主键的基础。

sqlite> create table contacts (
   ...> id integer primary key,...> name text not null collate nocase,...> phone text not null default 'UNKNOWN',...> unique (name,phone));
sqlite> insert into contacts (name,phone) values ('Jerry','UNKNOWN');
sqlite> insert into contacts (name) values ('Jerry');
Error: UNIQUE constraint failed: contacts.name,contacts.phone
sqlite> insert into contacts (name,'555-1212');

在contacts表中，对name和phone的联合有唯一性约束。字段值的联合必须是唯一的。

NULL和UNIQUE:注意NULL不等于任何值，甚至不等于其他NULL.理论上，您可以放入任意多个NULL。

主键约束

在SQLite中，不管你有没有定义主键，都有一个64-bit整型字段，称为rowid。它还有两个别名——_rowid_和oid。SQLite为主键字段提供自动增长特性。如果定义字段类型为integer primary key,SQLite将为该字段创建默认值，该默认值确保整数值是唯一的。实际上，该字段就是rowid的别名。

新创建的rowid不一定是按照严格顺序增长的。如果想要SQLite使用唯一的自动主键值，可以加入关键值autoincrement。

sqlite> select rowid,oid,_rowid_,id,name,phone from contacts;
id          id          id          id          name        phone     
----------  ----------  ----------  ----------  ----------  ----------
1           1           1           1           Jerry       UNKNOWN   
2           2           2           2           Jerry       555-1212

域完整性

默认值

default为字段提供一个默认值。例如，contacts的name字段默认值为'UNKNOWN'。

default还可以接受3中预定义格式的ANSI/ISO保留字，用于生产日期和时间值。current_time格式为（HH:MM:SS）,current_date格式为（YYYY-MM-DD）,current_timestamp会生成一个日期时间的组合，格式为（YYYY-MM-DD HH:MM:SS）.例如：

sqlite> create table times (id int,...> date not null default current_date,...> time not null default current_time,...> timestamp not null default current_timestamp);
sqlite> insert into times (id) values (1);
sqlite> insert into times (id) values (2);
sqlite> select * from times;
id          date        time        timestamp          
----------  ----------  ----------  -------------------
1           2015-01-25  09:10:01    2015-01-25 09:10:01
2           2015-01-25  09:10:16    2015-01-25 09:10:16

NOT NULL约束

NOT NULL约束可以确保字段部位NULL.

check约束

check约束允许定义表达式来测试要插入或者更新的字段值。例如，check约束可能确保电话号码字段的值至少是7位字符长度。

sqlite> create table contacts2
   ...> (id integer primary key,...> unique(name,phone),...> check (length(phone) >=7));

外键约束

SQLite支持关系理论中的关系完整性概念。关系完整性也叫外键，它确保了一个表中的关键值必须从另一个表中引用，且该数据必须在另一个表中实际存在。经典的例子就是父子关系、主从关系。SQLite支持在创建表时使用外键，可以使用如下语法：

create table table_name
(column_definition references foreign_table (column_name) 
on {delete|update} integrity_action 
[not] deferrable [initially {deferred|immediate},] ...);

例如，foods表和food_types表当前的定义如下：

CREATE TABLE foods(
  id integer primary key,type_id integer,name text );

CREATE TABLE food_types(
  id integer primary key,name text );

可以看出foods表使用了type_id字段，引用自表food_types。如果想要引用完整性确保SQLite保护您的数据，确保foods表的type_id值都存在于food_types的id字段，可以使用如下方式定义foods表：

sqlite> create table foods(
   ...> id integer primary key,...> type_id integer references food_types(id)
   ...> on delete restrict
   ...> deferrable initially deferred,...> name text);

type_id引用自表food_types的id字段。使用了delete restrict,该命令告诉SQLite阻止任何这样的删除行为，如果从表food_types中删除某行，将导致foods表的id没有父id的存在。完整的规则定义如下：

set null:如果父值被删除或者不存在了，剩余的子值将该为null
set default:如果父值被删除或者不存在了，剩余的子值修改为默认值
cascade：更新父值时，更新所有匹配的子值。删除父值时，删除所有的子值。要特别注意该选项，cascade的删除功能可能会出现意想不到的效果
restrict:更新或者删除父值可能会出现孤立的子值，从而阻止（终止）事物。
no action:使用一种松弛的方法，不干涉操作执行，只是观察变化。在整个语句（如果约束定义为deferred的事物也一样）的结尾报出错误。

SQLite支持deferrable子句，该子句控制定义的约束是立即强制实施还是延迟到整个事务结束时。

排序规则

排序规则涉及文本值如何比较。三种排序规则：

memcmp()逐字比较文本值
nocase:非大小写敏感排序算法
reverse：与二进制排序规则相反。reverse更多地用来测试。

存储类

SQLite有5个原始的数据类型。integer，real，text，blob，NULL。SQLite函数typeof（）根据值的表示法返回其存储类。

sqlite> select typeof(3.14),typeof('3.14'),typeof(314),typeof(x'3142'),typeof(NULL);
typeof(3.14)  typeof('3.14')  typeof(314)  typeof(x'3142')  typeof(NULL)
------------  --------------  -----------  ---------------  ------------
real          text            integer      blob             null

视图

视图即虚拟表，也称为派生表，因为它们的内容都派生自其他表的查询结果。虽然视图看起来感觉就像基本表一样，但是它们不是基本表。基本表的内容是持久的，而视图的内容是在使用时动态产生的。创建视图的语法如下：

create view name as select-stmt;

sqlite> create view details as
   ...> select f.name as fd,ft.name as tp,e.name as ep,e.season as ssn
   ...> from foods f
   ...> inner join food_types ft on f.type_id = ft.id
   ...> inner join foods_episodes fe on f.id = fe.food_id
   ...> inner join episodes e on fe.episodes_id = e.id;

使用drop view删除视图

drop view name;

索引

索引是一种用来在某种条件下加速查询的结构。创建索引的命令如下：

create index [unique] index_name on table_name (columns)

如果使用关键字unique，将会在索引上添加约束，索引中的所有值必须是唯一的。这不仅适用于索引，也适用于索引所在字段。unqiue关键字覆盖index中的所有字段，不管是联合值还是单个值：

sqlite> create table foo (a text,b text);
sqlite> create unique index foo_idx on foo(a,b);
sqlite> insert into foo values ('unique','value');
sqlite> insert into foo values ('unique','value');
Error: UNIQUE constraint failed: foo.a,foo.b

若要删除索引，可使用drop index命令

drop index index_name;

排序规则

索引中的每个字段都有相关的排序规则。例如，要在foods.name上创建大小写不敏感的索引，可以使用如下命令：

create index foods_name_idx on foods (name collate nocase);

.indices列出表中索引。也可以使用.schema命令查看更多的信息。

触发器

当具体的表发生特定的数据库事件时，触发器执行对应的SQL命令。命令格式如下：

create [temp|temporary] trigger name [before|after] [insert|delete|update|update of colums] on table action

可以通过关键字before或after来制定这些操作是在事件发生前还是发生后执行。事件包括在具体的表中执行的delete、insert和update命令。

更新触发器

与insert和delete触发器不同，update触发器可以在表的执行字段上定义，这种触发器的一般格式如下：

create trigger name [before|after] update of column on table action

事物

事务由3个命令控制：begin、commit和rollback.begin开始一个事务，begin之后的所有操作都可以取消，如果连接终止前没有发出commit，也会被取消。commit提交事物开始后所执行的所有操作。类似地，rollback还原begin之后的所有操作。例如：

sqlite> begin;
sqlite> delete from foods;
sqlite> rollback;
sqlite> select count(*) from foods;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!