SQL 必知必会

在mac终端操作sqlite：

• cd 数据库所在的目录
• sqlite3 数据库文件名 //带后缀）（此时已经打开数据库）
• .tables //显示数据库中所有已经创建的表
• .schema //显示所有表的模式
• .headers on //显示字段名（查询时才会显示）
• .mode column/list/line
• 执行sql语句必须在末尾加分号

distinct

 SELECT DISTINCT name FROM TB_BOOK_TAG;

关键字distinct，用于去除name列中所有行中重复元素。

limit

SELECT name FROM TB_BOOK_TAG LIMIT 5;

关键字limit,返回name列指定行数。

SELECT name FROM TB_BOOK_TAG LIMIT 5 OFFSET 0;
等同于下面写法（shortcut）
SELECT name FROM TB_BOOK_TAG LIMIT 0，5;

limit ... offset ...

关键字LIMIT ... OFFSET ...,limit后跟的数字指定显示多少行，offset后跟的数字表示从什么位置开始。（0是第一行）

注释

 --this is a comment

关键--加注释，单行注释。

 /* comments */

关键/**/，多行注释。

order by

 SELECT * FROM TB_BOOK_TAG ORDER BY name;

关键字：order by + 字段名，按该字段所属列的首字母进行排序。要确保该子句是select语句中最后一条子句，否则会出现错误。

 SELECT publisher,pubdate FROM TB_BOOK_ENTITY ORDER BY publisher,pubdate;

关键字：order by + 字段名 + 字段名，首先按publisher进行排序，然后按照pubdate进行排序。对于第二个字段的排序，当且仅当具有多个相同的publisher时才会对其按照pubdate进行排序，如果publisher列中所有值都是唯一的，则不会按pubdate进行排序。

desc

SELECT publisher,pubdate FROM TB_BOOK_ENTITY ORDER BY pubdate DESC;

关键字：desc，order by 默认是按升序进行排序，当在字段名后加desc后，将对该字段进行降序排列。

SELECT pubdate,price FROM TB_BOOK_ENTITY ORDER BY pubdate DESC,price;

pubdate按降序排列，price，仍然按照升序排列（在pubdate相同的行）。所以，如果想在多个列上进行降序，必须对每一列都指定desc关键字。

where

SELECT * FROM TB_BOOK_TAG WHERE count = 1;

关键字：where，指定搜索条件进行过滤。where子句在表名（from子句）之后给出。在同时使用where和order by时，应该让order by位于where之后。

注意：NULL和非匹配
通过过滤选择不包含（如<>）指定值的所有行时，你可能希望返回含NULL值的行，但是这做不到，因为NULL有特殊的含义，数据库不知道它们是否匹配，所以在进行匹配过滤或非匹配过滤时，不会返回这些结果。

where...and...

select * from contacts where name = "fff" and mobile = "d";

关键字：and，组合where子句。

where...or...

select * from contacts where name = "fff" or mobile = "d";

关键字：or，组合where子句。

注意：在同时使用and和or时要注意求值顺序，and优先级大于or。因此在任何时候使用具有and和or操作符的where子句时，都应该使用圆括号明确地分组操作符

where...in...

select * from contacts where mobile in ('12', '444') order by mobile;

关键字：in，用来指定条件范围，范围中的每个条件都可以进行匹配。in操作符一般比一组or操作符执行的更快。in最大的优点是可以包含其他select语句，能够更动态的建立where子句。

not

select * from contacts where not mobile = '12';

关键字：not，where子句中用来否定其后条件的关键字。上面的例子也可以用<>。在简单语句中，not没有什么优势，但是，在更复杂的子句中，not非常有用。例如，在与in操作符联合使用时，not可以非常简单的找出与条件列表不匹配的行。如下例子：

 SELECT * FROM CONTACTS WHERE NOT mobile IN ('111111', '3333');

like

通配符（wildcard）用来匹配值的一部分的特殊字符。
搜索模式（search pattern）由字面值，通配符或两者组合构成的搜索条件。

通配符搜索只能用于文本字段（字符串），非文本数据类型字段不能使用通配符搜索

%通配符

在搜索字符串中，%表示任何字符出现任意次数

select * from tb_book_tag where name like '计算机%';

注意字符串后面所跟的空格：
许多DBMS会用空格来填补字段内容。例如，如果某列有50个字符，而存储文本为Fish bean bag toy（17个字符），则为填满该列会在文本末尾追加33个空格。如果此时用‘F%y’来检索，便检索不到上述字符串。简单解决办法是‘F%y%’。更好的解决办法是用函数去掉空格。

'%' 不会匹配为NULL的行

下划线_通配符

用途和%一样，但它只匹配单个字符，而不是多个。

select * from tb_book_tag where name like '计算机__';

使用通配符的技巧

SQL通配符搜索比其他搜索更耗时。

1. 不要过度使用通配符，如果其他操作能达到目的，使用其他操作。
2. 在确实需要使用的时候，也尽量不要把它用在搜索模式的开始处。把通配符置于开始处，搜索起来是最慢的。
3. 特别要注意通配符的位置不要放错。

创建计算字段

计算字段并不实际存在于数据库表中，计算字段是运行时在SELECT语句内创建的。

select rtrim('~    ') || name from tb_book_tag;

关键字：||和rtrim()。 ||拼接操作符。rtrim()去除文本右边的空格。trim()去除两边的空格。

as

select name || 'is foolish' as title from contacts;

关键字：as，全称alias。它指示SQL创建一个包含指定计算结果的名为title的计算字段，任何客户端应用可以按名称引用这个列，就像一个实际表列一样。

执行算术计算

+ - * /

select mobile, (mobile + 1)*2 as count_mobile from contacts;

关键字：+或-或*或/；

函数

一下所用到的是适用于sqlite的函数，不一定适用于其他DBMS。

upper()

select name ,upper(name) as name_upper from contacts;

关键字：upper()转大写

sqlite中常用于文本处理函数：

avg()

select avg(mobile) as avg_id from contacts;

关键字：avg()，对表中某列所有行或特定行中的数据求平均值。该函数会忽略值为NULL的行。

count()

select count(*) as num_cust from contacts;

select count(name) as num_name from contacts;

关键字：count(),使用count(*)，对表中行的数目进行计数，不管表列中是否包含NULL值。使用count(column_name),对特定列中具有值的行进行计数，忽略NULL值。

sum()

select sum(mobile) as sum_mobile from contacts;

关键字：sum(), 忽略NULL值

聚集不同值

count(distinct name)

select count(distinct name) from tb_book_tag;

组合聚集函数

select count(*) as num_items, min(count) as count_min, max(count) as count_max, avg(count) as count_avg from tb_book_tag;

分组数据

group by

select name, count(*) as num_names from tb_book_tag group by name order by name;

关键字：group by，group by子句必须出现在where子句之后，order by子句之前。

group by...having...

select name , count(*) as amounts from tb_book_tag group by name having amounts >= 10;

关键字：having。对分组进行过滤。而where对分组不起作用，它是针对表中每一行来过滤。

使用子查询

select cust_id 
from orders 
where order_num in (select order_num 
                     from orderitems
                     where prod_id = 'RGAN01');

注意：
作为子查询的select语句只能查询单个列。企图检索多个列将返回错误。
同时要注意性能问题。

使用子查询作为计算字段

select cust_name, 
       cust_state,
       (select count(*) 
        from orders 
        where orders.cust_id = customers.cust_id) as orders from customers 
order by cust_name;

联结表

关系表

为理解关系表，来看一个例子：

有一个包含产品目录的数据库表，其中每类物品占一行，对于每种物品，要存储的信息包括产品描述，价格以及生产该产品的供应商。
现有同一供应商生产的多种物品，那么在何处存储供应商名联系方法等信息？将这些数据与产品信息分开存储的理由是：

1. 同一供应商的每个产品，其供应商的信息是相同的，对每个产品重复此信息既浪费时间又浪费空间；
2. 如果供应商信息发生变化，只需修改一次即可；
3. 如果有重复数九，则很难保证每次输入该数据的方式都相同，

相同的数据出现多次决不是一件好事，这是关系数据库设计的基础。关系表的设计就是要把信息分解成多个表，一类数据一个表。各表通过某些共同的值相互关联（所以才叫关系数据库）。

如果数据存储在多个表中，怎样用一条select语句就检索出数据？
答案是使用联结，联结是一种机制，用来在一条select语句中关联表

select vend_name, prod_name, prod_price 
from products, vendors 
where vendors.vend_id = products.vend_id;

等同于下面的写法：

select vend_name, prod_name, prod_price 
from vendors inner join products 
on vendors.vend_id = products.vend_id;

在联结两个表时，实际要做的是将第一个表中的每一行与第二个表中的每一行配对。where子句作为过滤条件，只包含那些匹配给定条件的行。没有where子句，第一个表中的每一行将与第二个表中的每一行配对，而不管他们逻辑上是否能匹配在一起。这种联结称为等值联结（equijoin），也称为内联结（inner join）。

笛卡尔积（cartesian product）：
由于没有联结条件的表关系返回的结果为笛卡尔积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

返回笛卡尔积的联结也叫叉联结（cross join）。

SQL不限制一条select语句可以联结的表的数目。如下：

select prod_name, vend_name, prod_price, quantity 
from orderitems, products, vendors 
where products.vend_id = vendors.vend_id 
and orderitems.prod_id = products.prod_id 
and order_num = 20007;

注意：性能考虑
DBMS在运行时关联指定的每个表，以处理联结。这种处理可能非常耗资源，因此应该注意不要联结不必要的表。

创建高级联结

使用表别名

select cust_name, cust_contact 
from customers as c, orders as o, orderitems as oi 
where c.cust_id = o.cust_id 
and oi.order_num = o.order_num 
and prod_id = 'RGAN01';

使用表别名的两个主要理由：

• 缩短SQL语句
• 允许在一条select语句中多次使用相同的表

自联结

select  cust_id, cust_name, cust_contact 
from customers 
where cust_name = (select cust_name 
                   from customers 
                   where cust_contact = 'Jim Jones');

以上子查询效果等同于自联结：

select c1.cust_id, c1.cust_name, c1.cust_contact 
from customers as c1, customers as c2 
where c1.cust_name = c2.cust_name 
and c2.cust_contact = 'Jim Jones';

通常情况下，许多DBMS处理联结远比处理子查询快得多

外联结

select customers.cust_id, orders.order_num 
from customers 
left outer join orders 
on customers.cust_id = orders.cust_id;

检索包括没有订单顾客在内的所有顾客。

SQLite支持left outer join,但不支持right outer join.

组合查询

主要有两种情况需要使用组合查询：

• 在一个查询中从不同的表返回结构数据
• 对一个表执行多个查询，按一个查询返回数据

union

select cust_name, cust_contact, cust_email 
from customers 
where cust_state in ('IL', 'IN', 'MI') 
union 
select cust_name, cust_contact, cust_email 
from customers 
where cust_name = 'Fun4All';

union规则

• union必须由两条或两条以上的select语句组成，语句之间用关键字union分隔。
• union中的每个查询必须包含相同的列，表达式或聚集函数（不过，各个列不需要以相同的次序列出）。
• 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含转换的类型。

union all

DBMS不取消重复行。

对组合查询结果排序

select cust_name, cust_contact, cust_email 
from customers 
where cust_state in ('IL', 'IN', 'MI') 
union 
select cust_name, cust_contact, cust_email 
from customers 
where cust_name = 'Fun4All'
order by cust_name, cust_contact;

在用union组合查询时，只能使用一条order by子句，它必须位于最后一条select语句之后，DBMS用它来排序所有的select语句返回的所有结果。

插入数据

插入完整的行

insert into... values

insert into customers 
values ('1000000006', 'Chenzhen', 'Hennansheng', 'henan', 'China', '476300', 'China', 'John jdge', 'chen@gaiml.com');

这种写法简单，但不安全，高度依赖表中列定义的次序，还依赖于其容易获得的次序信息。编写依赖列次序的SQL语句是很不安全的，这样做迟早会出问题。

更安全的方法：

insert into customers(cust_id,
                      cust_name, 
                      cust_address, 
                      cust_city, 
                      cust_state, 
                      cust_zip, 
                      cust_country, 
                      cust_contact, 
                      cust_email) 
values('1000000007',
       'Chenzhen', 
       'Hennansheng', 
       'henan', 
       'shangqiu', 
       '476300', 
       'China', 
       'John jdge', 
       'chen@gaiml.com');

插入行时，DBMS将用values列表中的相应值填入列表中的对应项。其优点是，即使表的结构改变，这条insert语句仍然可以正常工作。

insert into... select...from...

insert into customers(cust_id,
                      cust_name,
                      cust_address, 
                      cust_city, 
                      cust_state, 
                      cust_zip, 
                      cust_country, 
                      cust_contact, 
                      cust_email) 
select cust_id, 
       cust_name, 
       cust_address, 
       cust_city, 
       cust_state, 
       cust_zip, 
       cust_country, 
       cust_contact, 
       cust_email
from CustNew;

select语句从CustNew检索出要插入的值，而不是列出他们。DBMS不关心select返回的列名，它使用的是列的位置，因此select的第一列（不管列名如何）将用来填充表列中指定的第一列，如此等等。

insert select 语句可以包含where子句。

从一个表复制到另一个表

create table custcopy as select * from customers;

要想只复制部分列，可以明确给出列名。

更新和删除数据

update...set... where...

update customers 
set cust_email = 'chenzhen@gmainl.com' 
where cust_id = '1000000008';

更新多个列时，只需使用一条set命令：

update customers 
set cust_email = 'lala@qq.com',
    cust_contact = 'sam' 
where cust_id = '1000000008';

没有where子句，DBMS将会更新表中所有行。

delete

delete不需要列名或通配符，因为它删除的是整行而不是删除列，要删除指定列，使用update

delete from custcopy 
where cust_id = '1000000008';

如果省略where子句，它将删除表中的每个顾客。如果想从表中删除所有行，不要使用delete，可使用truncate table语句，它的速度更快，因为不记录数据的变动。

创建和操纵表

create

create table Super 
(
    prod_id char(10) not null, 
    vend_id char(10) not null, 
    prod_name char(254) not null, 
    prod_price decimal(8,2) not null,   default 10.2
    prod_desc varchar(1000) null
);

not null,可以阻止插入没有值的列。默认是null。

SQLite获得系统时间的函数date('now')

更新表

alert table

使用alert table更改表的结构，必须给出下面的信息：

• 在alter table之后给出要更改的表名。
• 列出要做出哪些更改。

alter table Vendors
add vend_phone char(20);

SQLite对使用alter table执行的操作有所限制。最重要的一个限制是，它不支持使用alter table定义主键和外键。

使用alter table要极为小心，应该在进行改动钱做完整的备份（表结构和数据的备份）。数据库表的更改不能撤销，如果增加了不需要的列，也许无法删除他们。

删除表 drop table

drop table Super;

使用视图

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。
视图（View）只不过是通过相关的名称存储在数据库中的一个 SQLite 语句。视图（View）实际上是一个以预定义的 SQLite 查询形式存在的表的组合。

SQLite仅支持只读视图，所以视图可以创建，可以读，但其内容不能更改。

删除视图 drop view

drop view customeremaillist;

创建视图create view

create view ProductCustomers as
select cust_name, cust_contact, prod_id
from Customers, Orders, OrderItems
where Customers.cust_id = Orders.cust_id
and OrderItems.order_num = Orders.order_num;

where子句与where子句
从视图检索数据时如果使用了一条where子句，则两组子句（一组子在视图中，另一组，另一组是传递给视图的）将自动组合。

视图为虚拟的表。它们包含的不是数据，而是根据需要检索数据的查询。视图提供了一种封装select语句的层次，可用来简化数据处理，重新格式化或保护基础数据。

管理事务处理

使用事务处理（transaction processing），通过确保成批的SQL操作要么完全执行，要么完全不执行，来维护数据库的完整性。

关于事务处理的一些术语：

• 事务（transaction）指一组SQL语句；
• 回退（rollback）指撤销指定SQL语句的过程；
• 提交（commit）指将未存储的SQL语句结果写入数据库表；
• 保留点（savepoint）指事务处理中设置的临时占位符，可以对它发布回退（与回退整个事务处理不同）。

可以回退哪些语句：
insert,update,delete

管理事务的关键在于将SQL语句分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退。

begin;
delete from orders where order_num = 20009;
delete from orderitems where order_num = 20009;
commit;

高级SQL特性

约束，索引，触发器。

约束（constraint）

主键

create table Orders
(
    order_num integer not null primary key,
    cust_id char(10) not null references Customers(cust_id)
);

表中任意列只要满足以下条件，都可以用于主键。

• 任意两行主键值都不相同。
• 每行都具有一个主键值（既列中不允许NULL）。
• 包含主键的列从不修改或更新。
• 主键值不能重用。

外键

外键是表中的一列，其值必须列在另一表的主键中。

外键有助防止意外删除。
在定义外键后，DBMS不允许删除在另一个表中具有关联行的行。例如不能删除关联订单的顾客，删除改顾客的唯一方法是首先删除相关的订单。

唯一约束 unique

唯一约束用来保证一列中的数据是唯一的。与主键的区别如下：

• 表可包含多个唯一约束，但每个表只允许一个主键。
• 唯一约束列可包含NULL值。
• 唯一约束列可修改或更新。
• 唯一约束列的值可重复使用。
• 与主键不一样，唯一约束不能用来定义外键。

检查约束 check

create table OrderItems
(
    ...
    quantity integer not null check (quantity > 0),
    ...
)

索引 create index

索引用来排序数据以加快搜索和排序操作的速度。想象一本书后的索引。

在开始创建索引前，应该记住以下内容：

• 索引改善检索操作的性能，但降低了数据插入，修改，和删除的性能。在执行这些操作时，DBMS必须动态的更新索引。
• 索引数据可能要占用大量的存储空间。
• 并非所有数据都适合做索引。
• 索引用于数据过滤和数据排序。
• 可以在索引中定义多个列（例如，州加上城市）。这样的索引仅在以州加城市的顺序排序时有用。如果想按城市排序，则这种索引没有用处。

CREATE INDEX index_name
ON table_name (column_name);

删除索引 drop index

DROP INDEX index_name;

触发器 Trigger

触发器是特殊的存储过程，它在特定的数据库活动发生时自动执行。

CREATE  TRIGGER trigger_name [BEFORE|AFTER] event_name 
ON table_name
BEGIN
 -- Trigger logic goes here....
END;

示例：

CREATE TRIGGER audit_log AFTER INSERT 
ON COMPANY
BEGIN
   INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));
END;

列出触发器

SELECT name FROM sqlite_master
WHERE type = 'trigger';