SQLite中的高级SQL
来源：互联网 发布：手机淘宝全屏海报尺寸 编辑：程序博客网 时间：2024/06/06 02:22
插入记录
使用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
还有另一种形式：
- 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, 1, '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, phone) values ('Jerry', '555-1212');
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,
- ...> name text not null collate nocase,
- ...> phone text not null default 'UNKNOWN',
- ...> 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的事物也一样）的结尾报出错误。
排序规则
排序规则涉及文本值如何比较。三种排序规则：
- 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
更新触发器
与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中的高级SQL
- SQLite中的高级SQL
- 学习sqlite3（四）---SQLite中的高级SQL
- SQLite中的SQL
- SQLite中的SQL
- SQLite中的SQL
- Sqlite中sql高级用法整理
- Android SQLite SQL语句高级进阶
- SQL中的高级查询
- sqlite数据库中的sql语句
- SQLite数据库中的SQL语句
- SQLite数据库中的SQL语句
- SQLite数据库中的SQL语句
- sqlite数据库中的sql语句
- sqlite数据库中的sql语句
- sqlite数据库中的sql语句
- SQLite数据库中的SQL语句
- sqlite数据库中的sql语句
- 移动端css reset
- execl、word转pdf
- imgproc模块--filter2D实现线性滤波器
- win7本地安装discuz及实现外网访问
- js,jQuery获取html5的data-*属性
- SQLite中的高级SQL
- iOS 逆向 汇编指令
- 【记录】在ubuntu 17.04 下用shadowsock和polipo设置代理来下载android源码
- Android 之多线程下载原理
- XMind如何自由定位主题 ？
- 设计模式-适配器模式
- 万元大奖邀您参与阿里云数加 MaxCompute最佳实践征文大赛
- thinkphp 前台数据提交后往数据库内添加
- 贪心之背包问题
