SQlite学习笔记

SQlite介绍
 

    SQLite是一款开源，轻量级，嵌入式关系型数据库。是一个学习关系型数据库的很好的工具。SQLite除了作为一个存储容器外，还可以作为一个单纯的数据处理的工具。使用SQLite可以很容易地将应用程序所使用的数据结构转化为表，并保存在一个内在数据库中。通过sql语句操作数据库中的数据，可以完成很繁重的任务而不必写自己的算法来对数据结构进行操作和排序

从哪得到SQLite
SQLite 网站(www.sqlite.org)同时提供SQLite的已编译版本和源程序。用户可根据不同的操作系统平台Linux，Mac OS X，Windows，Windows Phone8，Windows Runtime选择相应的SQLite已编译版本.比如windows平台下可下载 sqlite-shell-win32-x86-3080100.zip SQLite命令行程序(CLP)。SQLite无需安装配置。解压后，通过DOS shell进入工作目录，在命令行上键入sqlite3命令即进入SQLite shell.然后就可以执行数据库创建，查询等命令了。

SQLite特殊命令
SQLiteshell将用户输入的任何语句都看成是一个查询命令(query)，除非命令是以点(.)开始，以点(.)开始的命令用于特殊操作。键入.help或.h 可以得到这些命令的完整列表。键入.exit 或.e 退出SQLite shell。

1 与查看数据库对象相关的命令
.tables ?TABLE?  列出所有的表名，如果指定TABLE只列出符合模式表达式的表的表名.
.indices ?TABLE? 列出所有的索引名.如果指定TABLE只列出符合模式表达式的表的索引.
.schema ?TABLE?  列出所有表的创建语句，如果指定TABLE只列出符合模式表达式的表的创建语句.

2 与数据库备份相关的命令
.backup ?DB? FILE 以指定的文件名备份数据。 如不指定db 则备份当前打开的数据库.
.dump ?TABLE? 以sql文本格式导出数据库所有的表，如果指定TABLE只导出符合模式表达式的表.
.read Filename 执行文件中的sql语句.
.import Filename tab 将文件中的数据导入到指定的表

3 与格式控制相关命令
.show 显示当前各种设置的值
.echo ON|OFF 打开或关闭回显
.header ON|OFF  是否显示表头
.output Filaname 将输出重定向到指定的文件
.output stdout    将输出重定向的屏幕
.nullvalue string 当遇到NULL 值时,使用此处指定的字符串来替代
.prompt value 替换掉sqlite shell 标准的提示符
.separaor string 改变由.mode和.import命令所使用的分隔符,.separator 指定的字符串只对.mode list有效
.width num1 num2 .. 设置列宽,当.mode 设置为column时.
.mode  MODE 设置输出模式 ,MODE只能为下面的值。默认为list格式
          csv  comma-separated values 值之间用逗号作为分隔符
          column 列左对齐
          html
          insert  sql插入语句的格式
          line      每个值一行的格式
          list     以.separator指定的字符串作为值之间的分隔符. （默认格式）
          tabs      以tab作为分隔符
          tcl
其它命令
.print string 原义打印字符串

SQL语言
sql(Structured QueryLanguage)是一种数据库查询语言,是访问关系型数据库的唯一方法，通过sql语言来操作关系型数据库.
sql与SQLite关系：
如上所说sql是一种操作关系型数据库的语言,而SQLite则是众多关系型数据库中的一种，所以SQLite数据库也支持使用sql语言来控制数据库的增删改查等等.

sql语法
和C语言类似，sql语句必须以分号(;)结尾

1 常量
常量也称字面值(literal),sql中常量分为字符串常量、数据常量和二进制常量.

字符串常量:和C语言不同的是，sql字符串常量为使用单引号扩起来的字符序列.比如'Jerry'.，如果字符串中本身包含单引号，需要双写。如“Kenny’schicken”需要写成：'Kenny''s chicken'

数字常量有整数、十进制数和科学记数法表示的数，如：-1, 3.142, 6.0221415E23.

二进制值用如x'0000'的表示法，其中每个数据是一个16 进制数。二进制值必须由两个两个
的16 进制数(8 bits)组成，如：x'01', X'0fff', x'0F0EFF', X'0f0effab'.

关键字：和其它编程语言类似，具有特殊含义的单词称为关键字，比如select,create,insert,updata,drop等
标识符：数据库里的具体对象，如表名或索引名，字段名.
sql不区分大小写.

2 注释
单行注释：以双减号开始（--）.比如:
--创建test表
create table test (id integer primary key,name text );
多行注释:，多行注释采用C 风格的形式

3 sql常用语句
数据库中所有的工作都围绕表进行。表由若干行和若干列组成，行也被称为记录，列也被称为字段.在SQL中，创建和删除数据库对象的语句一般被称为数据定义语言(data definitionlanguage，DDL)，操作这些对象中数据的语句称为数据操作语言(data manipulationlanguage，DML)。

1 创建表
创建表的一般格式为:
CREATE [[TEMP|TEMPORARY] TABLE table_name (column_definitions [,constraints]);

中括号表示可选项
竖线表示在多个中选一
{}包含一个选项列表，表示必须从中选择一个

TEMP:声明创建的表为临时表，只存活于当前会话，一旦连接断开，就会被自动删除。
table_name：表示表名.
column_definitions:表示一个用逗号分隔的列定义(也称字段列表),每个字段包括一个字段名、字段类型和字段约束如有多个约束，约束间使用空格分隔.
[, constraints]：表级约束.例如，你可以用UNIQUE约束来规定所有记录中某个字段的值要各不相同

如下所示为一个创建表的语句:
CREATE TABLE contacts ( id INTEGER PRIMARY KEY,
name TEXT NOT NULL COLLATE NOCASE,
phone TEXT NOT NULL DEFAULT 'UNKNOWN',
UNIQUE (name,phone) );
创建了一个名为contacts的表，该表包含三个字段，分别为：
id字段 integer类型 字段约束为PRIMARY KEY.  （表示该字段为关键Key）
name字段 text类型 字段约束为not null，COLLATE 和NOCASE.  (表示该字段值不允许空， 不区分大小写)
phone字段 text类型 字段约束为NOT NULL， DEFAULT 'UNKNOWN'(表示该字段值不允许空 默认值为unknow)
表级约束为UNIQUE(name,phone)表示(name和phone的组合是唯一的，即允许同名的联系人，但不允许同名又同电话号码的联系人).


2 改变表
改变表的一般格式为:
ALTER TABLE table_name { RENAME TO name | ADD COLUMN column_def}

{}:花括号包含一个选项列表，表示必须从中选择一个,此处表示你必选使用
alter table table_name rename to name 或
alter table table_name add column column_def语句.

列如：
ALTER TABLE contacts
ADD COLUMN email TEXT NOT NULL DEFAULT '' COLLATE NOCASE;
为contacts表增加email列

此时使用.schema contacts查询表contacts的创建语句变为:
CREATE TABLE contacts ( id INTEGER PRIMARY KEY,
name TEXT NOT NULL COLLATE NOCASE,
phone TEXT NOT NULL DEFAULT 'UNKNOWN',
email TEXT NOT NULL DEFAULT '' COLLATE NOCASE,
UNIQUE (name,phone) );
显然emai字段已被添加进去.

3 select语句(查询表)
select语句是sql中最最复杂的语句,是sql精华所在.从语法上说,select语句由一系列字句组成，每个字句完成一种特定的操作.select语句中除select字句外，其余所有的字句都是可选的.

在学习select语句前先了解一下sql的操作符：
我们都知道运算符以一个或多个值作为输入，产生一个新的值作为输出。sql中值的形式可以是是常量，变量(主要是表的字段名)，表达式，或函数返回值.同时sql的支持的运算符如下:
算术运算符 +,-,*,/,%
关系运算符<,<=,>,>=,=,==,<>,!=,like,glob
逻辑运算符 and,or,not,in
位运算符 |,&
其中LIKE运算符含义与相等符(=)很像，只是like运算符支持通配符,百分比(%)匹配任意个字符，下划线(_)匹配单个字符串.glob和like类似，只是用来匹配文件名.
in 操作符用来判断 某个值是否在集合当中 比如 2 in (1,2,3) 返回 true(1)


select语句的一般格式:
SELECT DISTINCT heading FROM tables WHERE predicate
GROUP BY columns HAVING predicate
ORDER BY columns LIMIT count,offset;
下图为select语句执行过程的流程图:


在数据库中表也被称为关系(Relation),select语句执行过程介绍:
如果所示:select语句首先从from字句开始执行,from字句接受一个或多个表（表也称为关系）作为输入，通过对输入的表执行链接操作，形成一个称为R1的新表(关系)，接着where字句以R1作为输入，对R1中的行(也称记录)根据指定的条件就行过滤，过滤掉不符合要求的记录后形成新的称为R2的表，接着group子句以R2作为输入，根据指定条件见R2划分成若干组，从而形成一个被称为R3的表,接着having字句以R3作为输入(having子句和where子句功能类似，用来过滤R3中的组)having子句对组进行过滤后形成新的被称为R4的表,select子句以R4作为输入，heading用来过滤R4中的某些列形成一个被称为R5的新的表，DISTINCT用来去掉R5中重复的行(记录)后形成新的称为R6的表(关系),接着order子句以R6为输入,对R5进行排序后形成新的被称为R7的表(关系),最后limit子句以R7为输入对最终输出结果的范围和大小进行限定.

from子句
from子句的参数是由逗号分隔的一个或多个表。from子句通过链接将输入的一个或多个表组合成新的关系R1.R1是最初的关系，所有后续操作都是在R1的基础上完成的。

where 子句：
where子句用来过滤R1中的行,其参数一般为一个表达式,表达式定义选行标准，决定了那些行会被过滤掉，那些行会被选入到新的关系R2中。
where expression

group by子句：
group by子句接受where子句的输出R2作为输入,根据某个字段(或多个字段)上的值是否相等将所有记录分成若干小组.

having 子句：
having子句功能和where子句类似,只是having子句用来过滤的是由group by子句产生的组.

select子句:
select子句用来过滤列,其参数为一系列以逗号分隔的字段
select [DISTINCT] column1 [,column2 ]

order by子句
用来对输出的结果进行排序,其参数和select子句很像，都是以逗号分隔的字段，每个字段可指定排序方式为升序还是降序:
order by column1 asc | desc [, column2 asc | desc];
asc表示升序，desc表示降序.column1表示首先根据column1进行排序，如多个记录在column1字段相同再根据column2进行排序.

limit子句:
limit子句用来指定输出结果中记录的最大数量和偏移量.
limit子句一般格式为:
{limit max_record | limit max_record offset off | limitoff,max_record}.
select * from station limit 5 offset 1;
表示最多输出车站表中的5行记录，从偏移量1开始输出.该条语句也可省略offset写成如下形式
select * from station limit 1,5; 注意省略offset后前面数组为偏移量，后面数组为大小。

SQLite内置函数
SQLite 提供了多种内置的函数，可以用在各子句中,关于SQLite内置函数的信息参见
SQLite内置函数


多表连接

1 内连接
内连接就是通过两个字段进行连接，连接过程的实质就是对于第一个表中的每一行，都要遍历第二个表中的所有行，寻找那些连接字段具有相同值得行，再将他们包含到新的关系中去.

2 交叉连接
如果两个表没有通过任何方式关联，select语句就会产生一种更基础的连接(也是最根本的)，称为交叉连接,笛卡尔积或交叉乘积.交叉连接是强制的几乎无意义的连接，即第一个表中的所有行和第二个表中所有的行联合起来。交叉连接后形成的新的关系记录总数等于第一个表记录数乘以第二个表记录数.

3 外连接
内连接根据根据给定的连接条件选择表中的行，外连接则是选择内连接的所有行外加一些关系外的行。外连接分为左外连接，右外连接，全外连接。左外连接操作sql语句中的左表,例如:
select * from foods left outer join foods_episodes on foods.id =foods_episodes.food_id;
进行左外连接时，左表中所有匹配的行会被包含到结果集中，不匹配的行也会被包含的结果集中。
右连接的含义和左连接类似.不管是否匹配右表中的行都会包含到结果集中去.全连接则是左右外连接的合集，它包含全部匹配的行，然后是左表和右表中不匹配的行。
SQLite目前只支持左外连接。但右外连接可以通过左外连接实现。

4自然连接
自然连接的实质其实就是内连接的另一种方式。只是语法和习惯上做了些改动。自然连接通过表中共有的字段名称将两个表连接起来。其连接条件就是两个表中共有的字段。故自然连接无需连接条件。但要求两个表必须包含共有的字段。

多表连接的语法;
1 通过where子句隐含实现内连接
    比如:select * from foods,food_type where foods.id = food_type.food_id;
    当列出数据库的多个表时，默认会做连接操作，最低限度会做交叉连接，本例中的where是内连接.这种隐式形式虽然很简洁，但实际上是一种应该避免的过时语法形式。
2 通过使用连接关键字显式指定连接方式.
  其一般形式为:
  from left_table join_type right_table onjoin_condition

  left_table:左表，right_table:右表
  join_type:连接类型,可以是:inner join, left outer join,right outer join, full outer join,crossjoin分别代表内连接，左外连接，右外连接，全连接，交叉连接.SQLite只支持inner join和left outer join,cross join.

名称和别名:
别名就是对表进行重命名，其一般语法格式:
base-name [as] alias
其中as关键字是可选的，但很多人倾向保留as关键字，这样是别名显得更清晰。
别名的用途主要有两个:一是实现了表的自我连接,二是select语句中执行过程中产生的新的关系命名(比如对子查询的结果命名后)。

子查询:
所谓子查询就是select语句中又嵌套了select语句.子查询主要用于where子句，和from子句当中.

复合查询
复合查询使用三种特殊的关系操作符（联合，交叉连接，差集）处理多个查询的结果.
复合查询操作有如下要求:
1 涉及的关系的字段数目必须相同
2 只能有一个order by子句，且该order by子句放在复合查询的最末尾，对联合结果进行排序

1 union(联合)操作:
输入两个关系A和B，将两者合并成一个只包含A和B中非重复数据的单一关系。
如下sql语句用来找出食物表中频率最高和频率最低的食品。
select f.*, top_foods.count from foods f
inner join
  (select food_id, count(food_id) as count fromfoods_episodes
        group by food_id
        order by count(food_id) desc limit 1) top_foods
  on f.id=top_foods.food_id
union
select f.*, bottom_foods.count from foods f
inner join
  (select food_id, count(food_id) as count fromfoods_episodes
        group by food_id
        order by count(food_id) limit 1) bottom_foods
  on f.id=bottom_foods.food_id
order by top_foods.count desc;


2 intersect(交叉)操作:intersect操作输入两个关系，选择那些既在A也在B的行


3 except(差集)操作：except操作输入两个关系，选择那些在A但不在B的行.



4 修改数据
1 插入记录
使用insert语句插入记录,其一般格式为:
insert into table_name (column_list) values (values_list)
table_name:表名
column_list:以逗号分隔的字段列表.
values_list:以逗号分隔的值列表.
如果insert语句为所有的字段都提供了值，可以省略字段列表.

2 更新记录
使用update语句更新表中的记录,该语句可以修改表中的一行或多行一个或多个字段。其一般格式为:
update table_name set update_list where predicate
update_list:以逗号分隔的字段赋值列表，字段赋值的格式为：column_name=value
where:where子句.

3 删除记录:
使用delete语句删除表中的记录,delete语句的一般格式为;
delete from table_name where predicate