SQLite中的高级SQL

前端之家收集整理的这篇文章主要介绍了SQLite中的高级SQL前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

插入记录

使用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;

猜你在找的Sqlite相关文章