SQLite3中的SQL基本语句和高级语句

一、SQL基本语句

1. 创建表：

至少需要包含一个表名和一个字段名，
创建基本表：

	CREATE TABLE stu  (id int, name text, score real);	基本表会在数据库中永久存在。

创建临时表：

CREATE TEMP TABLE stu  (id int, name text, score real);	临时表是暂时存活，一旦连接断开就会自动销毁。

字段解析：

stu：表名
id/name/score：字段名
int/text/real：类型

SQLite有五种类型：integer、real、text、blob、null。

创建表允许在字段后添加约束：

CREATE TABLE stu  (id int primary key, name text, score real); 为字段id添加主键。

2. 修改表：修改表的结构

增加一个无初始值字段：

alter table stu add column <字段名> <类型>;

增加一个有初始值字段：

alter table stu add column <字段名> <类型> default value;

修改表名：

alter table <tablename_old> rename to <tablename_new>

3. 数据库查询：

select是SQL中最大、最复杂的命令。最常见的select命令由 select、from、where组成：

	select * from <table_name>; 

1.关系操作：大部分SQL实现中，select语句提供了混合、比较、过滤数据的“关系操作”。通常分为以下三类。

• 基本操作：限制、投影、笛卡儿积、联合、差、重命名。基本操作定义了基础的关系操作

• 附加操作：交叉、自然连接、赋值。附加操作提供了频繁操作的快速方式，由基本操作组成。

• 扩展操作：广义投影、左外连接、右外连接、全外连接。扩展操作对基本操作和附加操作的扩展。
  所有这些操作都定义在关系中，也就是我们说的表。他们将一个或多个关系操作作为输入，然后产生另一个关系的输出。如：

   select name from(select name, id from(select *from stu));
  

  最里层的输出作为次里层的输入，次里层的输出作为最外层的输入。

2.select命令与管道操作
select命令用一系列子句将很多关系操作组合在一起，每个子句代表一种特定的关系操作。

select [distinct] heading from tables where predicate group by columns having predicates order by columns limit count,offset;

3.过滤：where是一个过滤器

值：真实的数字（1，2，3）或者字符串（“xxxxx”）。

操作符：一个或多个值作为输入产生一个新值作为输出。

二元操作符：算术操作符：+ - * / ：输入数值产生一个数值

关系操作符：< > = ：比较值和值产生一个逻辑结果

逻辑操作符：AND OR NOT IN:根据输入产生具体真假值 如：（x > 5）AND (y < 2)

LIKE与GLOB操作符：用于匹配通配符指定模式的文本，匹配成功返回查找到的内容，glob区分大小写

• like： %_
  where salary like ‘200%’ 查找以200开头的任意值
  where salary like ‘%200%’ 查找任意位置包含200的任意值
  where salary like ‘00%’ 查找第二位和第三位为00的任意值
  where salary like '2%_%’ 查找以2开头、且长度至少为3个字符的任意值
  where salary like ‘%2’ 以2结尾的任意值
  where salary like ‘_2%3’ 查找第二位为2，且以3结尾的任意值
  where salary like ‘2___3’ 查找长度为5，且以2开头以3结尾的任意值。

   例子：select * from company where age Like '2%';
  

• glob: ?
  where salary glob '200’ 查找以200开头的任意值
  where salary glob ‘200’ 查找任意位置包含200的任意值
  where salary glob ‘?00*’ 查找第二位和第三位为00的任意值
  where salary glob ‘2??’ 查找以2开头、且长度至少为3个字符的任意值
  where salary glob ‘2’ 以2结尾的任意值
  where salary glob '?23’ 查找第二位为2，且以3结尾的任意值
  where salary glob ‘2???3’ 查找长度为5，且以2开头以3结尾的任意值。

   例子：select * from company where age glob '2*';		
  

4. 限定和排序

：使用limit和offset关键字限定结果集的大小和范围。limit限定返回记录最大数，offset指定偏移量。
例子：从id的第三位提取三个记录。desc降序 asc升序（默认）

select *from company order by id limit 3 offset 2; 

5. 函数和聚合

：SQLite提过了很多函数和聚合，可用在不同的子句中，如：计算绝对值（abs()）,字符串格式函数，
字符串大小写转化（upper(),lower()）。

例子：select id, upper(name),length(name) from company where length(name)<5;

聚合：是一类特殊的函数如：sum()、count()、avg()、min()、max()。

例子：select count(*) from company;

6. 分组：group by：把集合分为多个组

例子：select id count(*) from company group by id;

约束：having 对group by起到了约束作用，having对每个组应用过滤，通过过滤的组传递给select子句做聚合和映射。having子句必须出现在group by 子句之后，order by之前。

例子：select *from company group by name having count(age>20);	查找年纪大于20岁的记录

7.去重

：distinct与select一起使用，消除重复记录，只获取唯一一次的记录。

例子：select distinct name from company;

8.多表连接

：连接（join）是多表数据工作的关键，它的输出作为其他子句的输入。一个表中字段的值与另一个表中字段值对应，这样就存在了关联。要实现连接，数据库需要找到匹配的行，对于第一个表的每一行，数据库都要查询第二个表的所有行，寻找那些连接字段具有相同值得行。然后将他们包含的输入关系中。

• 内连接：通过表中两个字段进行连接。内连接使用关系代数中一种集合操作-交叉。找出同时存在两个集合的相同元素。

   例子：select * from foods inner join food_types on foods.id = food_types.id;
  

• 交叉连接：两个表之间没有任何联系，强制将他们连在一起，称为交叉连接或笛卡尔积。两个表之间行与行之间没有联系没有连接，只是简单组合在一起。

• 外连接：外连接选择内连接的所有行外加一些关系之外的行。外连接有三种：左外连接、右外连接、全外连接。

• 自然连接：通过表中共有的字段名称将两个表连接起来。自然连接会连接两个表中具有相同名称的字段。

9.别名

:如果表名很长的话可以简化对表重命名

例子：

 select foods.name, food_types.name from foods, food_types where foods.type_id = food_types.id limit 10;
 select f.name, t.name from foods f, food_types t where f.type_id = t.id limit 10;

10.子查询：

子查询是指select语句中嵌套select语句。

11.复合查询

与子查询相反，它是使用三种特殊的关系操作符（联合、交叉连接和差集）处理多个查询结果。对应关键字：union、intersect和except。
1.涉及的关系的字段数目必须相等。
2. 只能有一个oder by 子句，并且在复合查询的末尾处，对联合结果进行排序。
3.union子句：操作输入的两个关系：A和B，将A和B联合成只包含A和B中非重复字段的单一关系。如果想保留重复数据则使用union all。

例子：SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID UNION SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;

4.except子句：找出在A但不在B的行。

例子：SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID except SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;		

12.NULL:

未知的、不可知的特殊占位符，不是真、不是假、不是空、不是零。

二、SQL高级语句

1.修改数据：insert、update、delete。

1.插入记录：insert：向表中插入数据：insert into <table_name> values(value1, value2, …);

例子：insert into stu values(1, 'ZhangSan', 89.9); //插入整条数据
	 insert into stu(id, name) values(5, 'XiaoMing'); //只插入部分字段
	 insert into stu values(null, select id from stu2 where name='xxx'); //插入一组
	 insert into stu select * from stu2;	//将stu2 数据插入stu1中。
	 create table stu as select * from stu2;	//创建stu并插入stu2中数据。 

2.更新记录：update：修改表中一行或者多行中一个字段或者多个字段的记录。当遇到unqiue约束时候，更新可能会失败。

例子：update stu set id=4,score=99.9 where id=5;

3.删除记录：delete

例子：delete from stu where id=4;	 //按条件删除记录
	delete from stu;  //删除表中所有记录

2.数据完整性

用于定义和保护表内部和表之间的数据关系。有四种完整性：域完整性、实体完整性、引用完整性、用户自定义完整性。数据完整性是通过约束实现的。约束就是对字段存储值得一种限制，数据库对字段中存储值进行完整性约束强制实施。字段级约束包括：not null、unique、primary key、foreign key、check和collate。
1.实体完整性：

• 主键：primary key：行必须在某种方式上是唯一的。主键必须由带有unique(唯一性)约束的一个或一组字段组成。unique约束是主键的基础。主键提供自增长功能。表默认会创建一个主键（64-bit类型）。
  我们也可以自己创建主键，可以定义在多个字段，也不必要是int型。

• unique：唯一性约束：保证一个或一组字段的所有值互不相同，如果视图插入一个重复值，将引发非法约束终止操作。

2.域完整性：定义中的字段值必须是字段定义范围内的。由两部分组成：类型检查和范围检查。默认值、not null 和 check以及排序。

• 默认值：如果insert时候没有为该字段指定值，则关键字default为字段设定一个默认值。还接受3种预留定义格式的ANSI/ISO保留字,用于生成时间日期。

   current_time: hh:mm:ss
   current_date: yyyy-mm-dd
   current_timestamp: yyyy-mm-dd hh:mm:ss
  

• not null:该约束保证该字段不为null。如果给限制not null的字段insert或者update 为null则报错。一般与default一起使用，未赋值的时候使用默认值。

• check：测试要插入或者更新的字段值，如果不满足则报错。

   例子：create table stu (id integer primary key, name text not null, phone text not null default 'unknown', unique(name, phone),check(length(phone)>7)); 
  

• 外键：确保一个表中关键字从另一个表中引用，且数据在另一个表中实际存在。

   例子：create table food (id integer primary key, type_id integer references food_types(id) on delete restrict deferrable initially deferred, name text);
   	delete restrict:阻止这样的删除，如果删除food_types表中id，那么food中id没有父id存在，则阻止删除事务。
   	deferrable:控制定义的约束立即强制实施还是延迟执行。
  

3.存储类

SQLite有五个原始数据类型，integer(整数)、real(浮点数)、text(字符串)、blob(二进制)、null。
在SQLite中不同存储类的值可以存储在同一个字段中。他们可以排序 null < integer/real < text < blob

4.视图

即虚拟表，也称派生表，因为他们的内容都是派生自其他表的查询结果。视图虽然看起来像基本表，但是他们不是，基本表是永久的，他们是动态产生的。视图是只读的，无法进行insert、update、delete。
创建视图

create view name as select id from stu;	name:视图名称

删除视图：

drop view name;

查询视图：创建完视图之后就可以像查询表一样查询视图：

select *from name;

5.索引

一种用来在某种条件下加速查询的结构。SQLite使用B-tree做索引。索引会增加数据库的大小，索引可以加快查询速度，但是会降低insert、update、和delete的速度。

基本语法：

CREATE INDEX checkin_f_query on checkin_f;	checkin_f_query：索引名。checkin_f索引所在的表名。

唯一索引：不允许重复的值插入表中。

CREATE UNIQUE INDEX checkin_f_query on checkin_f (userid, check_in_time);	索引值是唯一的。

单例索引：基于一个表上一个列的索引。

CREATE INDEX checkin_f_query on checkin_f (userid);

删除索引：

DROP INDEX checkin_f_query;

6.触发器

是数据库的回调函数，当具体表发生特定的数据库事件时，会执行对应的SQL语句。触发器是通过名称、行为、表定义的。行为由一系列SQL命令组成，当发生某些特定事件时触发器就会执行这些命令。
可以通过关键字before和after来指定是在事发前执行还是事发后执行。事件包括具体在表中执行的delete、update、insert。
例子：当 record_f_num > 12000时候，删除checkin_f 表中时间最早的一条记录

CREATE TRIGGER checkin_max_12K after update on log WHEN new.key='record_f_num' AND new.value>12000
					BEGIN delete from checkin_f where check_in_time in (select check_in_time from checkin_f order by check_in_time limit 1 offset 0); END

错误处理：定义事件发生执行前可以使用触发器阻止事件，也可以在发生后使用触发器检查事件。SQL函数raise()函数供触发器使用，该函数允许在触发器内产生错误。函数定于：

raise(resolution, err_msg);
参数：resolution:冲突解决策略，replace、abort、fail、ignore、rollback
	 err_msg:错误消息

7.事务

事务定义了一组SQL命令的边界，这组命令或者作为一个整体被全部执行，或者都不执行。

事务的范围：事务由三个命令控制：begin、commit、rollback。begin开始一个事务，begin之后的所有操作都可以被取消。commit提交事务开始后所执行的所有操作。rollback回滚begin之后的所有操作。

冲突解决：既可以在SQL命令中指定也可以在表和索引中执行。处理约束违法的方法

replace：当违反了唯一性约束时，SQL将违反的记录删除，以插入或者修改新的记录替代。当违反not null约束时就使用该字段的默认值来替代null，如果没有默认字段则使用abort的处理方式。

ignore：当违反约束时选择忽视，SQL命令继续执行，违反约束的行保持不变，且不报错。

fail：当违反约束时，SQLite终止命令，但是不恢复之前已经修改过的记录。

abort:当违反约束时,SQLite恢复命令所做的所有改变并终止。是SQLite默认的解决办法。

rollback：当违反约束时，执行回滚-终止当前命令和整个事务。当前命令所作的改变和事务中之前所作的改变都被回滚。

例子：sqlite> begin;
	 sqlite> insert or replace into table (col_List) values (value_list);
	 sqlite> commit;

数据库锁：SQLite采用粗粒度锁，当一个连接要写数据库时，所有其他的连接都被锁住，直到写连接结束它的事务。SQLite有一个加锁表，帮助不同的写数据库能够在最后一刻加锁，以保证最大的并发性。
SQLite有五种不同的锁状态：未加锁、共享、预留、未决和排它。数据库在同一时刻只能有一种锁状态。

• 未加锁：最初的状态，连接却没有访问数据库或者已经begin一个事务时，都是未加锁状态。

• 共享锁：为了从数据库读数据，连接必现进入共享锁，多个连接可以同时获得并保持共享锁，多个连接可以同一时刻同时从一个数据库读数据。但是如果有共享锁没有释放就不可以写数据。

• 预留锁：如果想要写数据，必须获取一个预留锁，一个数据库只能有一个预留锁，预留锁可以与共享锁共存，他不会影响其他连接继续获取共享锁。一旦连接获取到预留锁就可以写数据库，但是只写到缓存中，并没有写到磁盘中。

• 排它锁：当一个连接写完数据想要提交时候，就需要从预留锁提升为排它锁。但是在这之前需要先提升为未决锁，获得未决锁之后其他连接就不可以继续获得共享锁，但是拥有共享锁的仍可以继续工作。此时拥有未决锁的连接等待其他获得共享锁的连接释放共享锁。当其他共享锁都释放完之后，未决锁提升为排它锁，此时就可以对数据库进行修改，然后保存到数据库文件。

事务的类型：SQLite有三种不同的事务，他们以不同的锁状态启动事务。

begin [deferred | immediate | exclusive] transaction;
deferred:直到使用时候才获取锁
immediate：在begin执行时候视图获取预留锁，成功则写数据库，失败则返回SQLITE_BUSY错误。
exclusive：获取数据库的排它锁。成功就可以对数据库进行任意操作。

8.数据库管理

控制数据库如何操作

附加数据库：SQLite允许使用attach命令将多个数据库附加到当前连接上。当附加上一个数据库时，他的所有内容在当前数据库文件的全局范围内都是可存取的。

语法：attach database filename as database_name; filename:数据库文件名。database_name:引用数据库和对象的逻辑名称。

数据库配置：SQLite没有配置文件，它所有配置文件都是用编译指示来实现的。

连接缓冲区大小：缓存区尺寸大小的编译指示控制一个连接可以在内存中使用多少个数据库页。缓冲区越大，连接在获取排它锁之前所做的事就越多。

查看缓冲区大小：

pragma cache_size;

设置缓冲区大小：

pragma cache_size=10000;

获取数据库信息：

pragma database_list;	//列出所有附着的数据库
pragma index_info;		//列出索引内字段的相关信息
pragma index_list;		//列出表中索引信息
pragma table_info;		//列出表中所有字段信息

写同步：SQLite会在关键时候将所有的变化提交到磁盘以确保事务的持久性。可以通过synchronous编译指示来实现。该编译指示有三种：

• full：在关键的暂停以确保所有数据都写入磁盘，最安全但是比较慢。

• normal：会在绝大多数关键点暂停，没有full频繁。

• off：将数据抛给操作系统后继续执行，一旦断电或者崩溃数据库将无法保存到数据。