数据库 9

SQL语句处理顺序的坑，高效sql性能优化

By admin在 上张贴Cat 数据库发表回复
  1. from 表时  最好给定 库名和表名  Sales.Orders 
    让表显示表示 不用程序检索。

执行算术计算

五，什么是好的sql语句

尽量简单，模块化

易读，易维护

节省资源

内存

cpu

扫描的数据块要少

少排序

不造成死锁

6.最好别使用 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处理联结远比处理子查询快得多

四，sql优化过程

定位有问题的语句

检查执行计划

检查执行计划中优化器的统计信息

分析相关表的记录数、索引情况

改写sql语句、使用HINT、调整索引、表分析

有些sql语句不具备优化的可能，需要优化处理方式

达到最佳执行计划

2.根据条件筛选客户ID等于71的

删除索引 drop index

DROP INDEX index_name;

三，sql优化方法

优化业务数据

优化数据设计

优化流程设计

优化sql语句

优化物理结构

优化内存分配

优化I/O

优化内存竞争

优化操作系统

4.having

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语句仍然可以正常工作。

二，sql调优领域

应用程序级调优

sql语句调优

管理变化调优

示例级调优

内存

数据结构

实例配置

操作系统交互

I/O

swap

Parameters

 

更新和删除数据

八，sql优化最佳实践

1，选择最有效率的表连接顺序

首先要明白一点就是SQL 的语法顺序和执行顺序是不一致的

SQL的语法顺序：

    select   【distinct】 ….from ….【xxx
 join】【on】….where….group by ….having….【union】….order
by……

SQL的执行顺序：

   from ….【xxx  join】【on】….where….group by
….avg()、sum()….having….select   【distinct】….order by……

from 子句–执行顺序为从后往前、从右到左

表名(最后面的那个表名为驱动表，执行顺序为从后往前,
所以数据量较少的表尽量放后）

where子句–执行顺序为自下而上、从右到左

将可以过滤掉大量数据的条件写在where的子句的末尾性能最优

group by 和order by 子句执行顺序都为从左到右

select子句–少用*号，尽量取字段名称。 使用列名意味着将减少消耗时间。

2，避免产生笛卡尔积

含有多表的sql语句，必须指明各表的连接条件，以避免产生笛卡尔积。N个表连接需要N-1个连接条件。

3，避免使用*

当你想在select子句中列出所有的列时，使用动态sql列引用“*”是一个方便的方法，不幸的是，是一种非常低效的方法。sql解析过程中，还需要把“*”依次转换为所有的列名，这个工作需要查询数据字典完成！

4，用where子句替换having子句

where子句搜索条件在进行分组操作之前应用；而having自己条件在进行分组操作之后应用。避免使用having子句，having子句只会在检索出所有纪录之后才对结果集进行过滤，这个处理需要排序，总计等操作。如果能通过where子句限制记录的数目，那就能减少这方面的开销。

5，用exists、not exists和in、not in相互替代

原则是哪个的子查询产生的结果集小，就选哪个

select * from t1 where x in (select y from t2)

select * from t1 where exists (select null from t2 where y =x)

IN适合于外表大而内表小的情况；exists适合于外表小而内表大的情况

6，使用exists替代distinct

当提交一个包含一对多表信息（比如部门表和雇员表）的查询时，避免在select子句中使用distinct，一般可以考虑使用exists代替，exists使查询更为迅速，因为子查询的条件一旦满足，立马返回结果。

低效写法：

select distinct dept_no,dept_name from dept d,emp e where
d.dept_no=e.dept_no

高效写法：

select dept_no,dept_name from dept d where  exists (select ‘x’ from
emp e where e.dept_no=d.dept_no)

备注：其中x的意思是：因为exists只是看子查询是否有结果返回，而不关心返回的什么内容，因此建议写一个常量，性能较高！

用exists的确可以替代distinct，不过以上方案仅适用dept_no为唯一主键的情况，如果要去掉重复记录，需要参照以下写法：

select * from emp  where dept_no exists (select Max(dept_no)) from
dept d, emp e where e.dept_no=d.dept_no group by d.dept_no)

7，避免隐式数据类型转换

隐式数据类型转换不能适用索引，导致全表扫描！t_tablename表的phonenumber字段为varchar类型

以下代码不符合规范：

select column1 into i_l_variable1 from t_tablename where
phonenumber=18519722169;

应编写如下：

select column1 into i_lvariable1 from t_tablename where
phonenumber=’18519722169′;

8，使用索引来避免排序操作

在执行频度高，又含有排序操作的sql语句，建议适用索引来避免排序。排序是一种昂贵的操作，在一秒钟执行成千上万次的sql语句中，如果带有排序操作，往往会消耗大量的系统资源，性能低下。索引是一种有序结果，如果order
by后面的字段上建有索引，将会大大提升效率！

9，尽量使用前端匹配的模糊查询

例如，column1 like
‘ABC%’方式，可以对column1字段进行索引范围扫描；而column1 kike
‘%ABC%’方式，即使column1字段上存在索引，也无法使用该索引，只能走全表扫描。

10，不要在选择性较低的字段建立索引

数据库，在选择性较低的字段使用索引，不但不会降低逻辑I/O，相反，往往会增加大量逻辑I/O降低性能。比如，性别列，男和女！

11，避免对列的操作

不要在where条件中对字段进行数学表达式运算，任何对列的操作都可能导致全表扫描，这里所谓的操作，包括数据库函数，计算表达式等等，查询时要尽可能将操作移到等式的右边，甚至去掉函数。

例如：下列sql条件语句中的列都建有恰当的索引，但几十万条数据下已经执行非常慢了：

select * from record where amount/30<1000 (执行时间11s)

由于where子句中对列的任何操作结果都是在sql运行时逐行计算得到，因此它不得不进行全表扫描，而没有使用上面的索引；如果这些结果在查询编译时就能得到，那么就可以被sql优化器优化，使用索引，避免全表扫描，因此sql重写如下：

select * from record where amount<1000*30 (执行时间不到1秒)

12，尽量去掉”IN”,”OR”

含有”IN”、”OR”的where子句常会使用工作表，使索引失效，如果不产生大量重复值，可以考虑把子句拆开；拆开的子句中应该包含索引；

select count(*) from stuff where id_no in(‘0′,’1’)

可以拆开为：

select count(*) from stuff where id_no=’0′

select count(*) from stuff where id_no=’1′

然后在做一个简单的加法

13,尽量去掉”<>”

尽量去掉”<>”，避免全表扫描，如果数据是枚举值，且取值范围固定，可以使用”or”方式

update serviceinfo set state=0 where state<>0;

以上语句由于其中包含了”<>”，执行计划中用了全表扫描（Table access
full），没有用到state字段上的索引，实际应用中，由于业务逻辑的限制，字段state智能是枚举值，例如0,1或2，因此可以去掉”<>”
利用索引来提高效率。

update serviceinfo set state=0 where state =1 or state =2

14,避免在索引列上使用IS NULL或者NOT

避免在索引中使用任何可以为空的列，导致无法使用索引

15，批量提交sql

如果你需要在一个在线的网站上去执行一个大的DELETE或INSERT查询，你需要非常小心，要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的，表一锁住了，别的操作都进不来了。

Apache会有很多的子进程或线程。所以，其工作起来相当有效率，而我们的服务器也不希望有太多的子进程，线程和数据库链接，这是极大的占服务器资源的事情，尤其是内存。

如果你把你的表锁上一段时间，比如30秒钟，那么对于一个有很高访问量的站点来说，这30秒所积累的访问进程或线程，数据库链接，打开的文件数，可能不仅仅会让你的WEB服务崩溃，还可能会让你的整台服务器马上挂了。所以，如果你有一个大的处理，你一定把其拆分。

 

 

 

 

 

 

 

5.像上面的 “YEAR(orderdate)” SQL Server 只对它运行一次 
能识别查询中重复使用的相同表达式

使用表别名

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语句中多次使用相同的表

一，sql性能优化基础方法论

对于功能，我们可能知道必须改进什么；但对于性能问题，有时我们可能无从下手。其实，任何计算机应用系统最终队可以归结为：

cpu消耗

内存使用

对磁盘，网络或其他I/O设备的输入/输出(I/O)操作。

 

但我们遇到性能问题时，要判断的第一点就是“在这三种资源中，是否有哪一种资源达到了有问题的程度”，因为这一点能指导我们搞清楚“需要优化重构什么”和“如何优化重构它”

数据库 1

USE Temp;

SELECT empid, YEAR(orderdate) AS orderyear, COUNT(*) AS numorders
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate)
HAVING COUNT(*) > 1
ORDER BY empid, orderyear;

组合聚集函数

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

七，sql表的基本连接方式

表连接有几种?

sql表连接分成外连接、内连接和交叉连接。

新建两张表：

表1：student  截图如下：

 

数据库 2

表2：course  截图如下：

 

数据库 3

（此时这样建表只是为了演示连接SQL语句，当然实际开发中我们不会这样建表，实际开发中这两个表会有自己不同的主键。）

一、外连接

外连接可分为：左连接、右连接、完全外连接。

1、左连接  left join 或 left outer join

SQL语句：select * from student left join course on student.ID=course.ID

执行结果：

 

数据库 4

左外连接包含left
join左表所有行，如果左表中某行在右表没有匹配，则结果中对应行右表的部分全部为空(NULL).

注：此时我们不能说结果的行数等于左表数据的行数。当然此处查询结果的行数等于左表数据的行数，因为左右两表此时为一对一关系。

2、右连接  right join 或 right outer join

SQL语句：select * from student right join course on
student.ID=course.ID

执行结果：

 

数据库 5

右外连接包含right
join右表所有行，如果左表中某行在右表没有匹配，则结果中对应左表的部分全部为空(NULL)。

注：同样此时我们不能说结果的行数等于右表的行数。当然此处查询结果的行数等于左表数据的行数，因为左右两表此时为一对一关系。

3、完全外连接  full join 或 full outer join

SQL语句：select * from student full join course on student.ID=course.ID

执行结果：

 

数据库 6

完全外连接包含full
join左右两表中所有的行，如果右表中某行在左表中没有匹配，则结果中对应行右表的部分全部为空(NULL)，如果左表中某行在右表中没有匹配，则结果中对应行左表的部分全部为空(NULL)。

二、内连接  join 或 inner join

SQL语句：select * from student inner join course on
student.ID=course.ID

执行结果：

 

数据库 7

inner join 是比较运算符，只返回符合条件的行。

此时相当于：select * from student,course where student.ID=course.ID

三、交叉连接 cross join

1.概念：没有 WHERE
子句的交叉联接将产生连接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。

SQL语句：select * from student cross join course

执行结果：

 

数据库 8

如果我们在此时给这条SQL加上WHERE子句的时候比如SQL:select * from student
cross join course where student.ID=course.ID

此时将返回符合条件的结果集，结果和inner join所示执行结果一样。

4.因为 group by
属于行处理 在having 先计算所以having 中可以出现  聚合函数 。

主键

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

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

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

六，sql语句的处理过程

sql语句的四个处理阶段：

 

数据库 9

解析（PARSE）:

检查语法

检查语义和相关的权限

在共享池中查找sql语句

合并（MERGE）视图定义和子查询

确定执行计划

绑定（BIND）

在语句中查找绑定变量

赋值（或重新赋值）

执行（EXECUTE）

应用执行计划

执行必要的I/O和排序操作

提取（FETCH）

从查询结果中返回记录

必要时进行排序

使用ARRAY FETCH机制

6.最终对客户id 和订单 进行排序

avg()

select avg(mobile) as avg_id from contacts;

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

输入的键入顺序和处理顺序不一致是有原因的，SQL设计师是为了让用户按照英文的方式提供自己的请求

where…or…

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

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

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

1.from  

like

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

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

  1. 再选出大于一个订单的组

对组合查询结果排序

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语句返回的所有结果。

3.group by

group by…having…

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

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

5.select

%通配符

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

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

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

'%' 不会匹配为NULL的行

7.使用 order by 对有大量重复的字段进行排序是无效的  例如对日期进行排序
这样一个排序选10条 会有多个被认为是对的结果
所以我们要确保排序字段的数据唯一性， 以及在 select distinct  时 排序
会导致 单个结果对应多个源数据行。

delete

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

delete from custcopy 
where cust_id = '1000000008';

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

7.TOP

唯一约束 unique

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

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

建议、坑

高级SQL特性

约束，索引，触发器。

在仔细分析每个执行顺序代表的意思 （它的实际顺序）

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进行排序。

  1. where 子句相当重要  SQL Server 会对where 条件
    进行评估访问请求数据要使用的索引，通过索引可以大大减少表扫描时间

函数

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

我们来详细分析一下sql语句的逻辑处理顺序，虽然select在每条语句的第一位，但实际上它是被最后才处理的

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')

7.输出

where…and…

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

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

3.记住除count(*)之外， 
聚合函数都是忽略NULL标记  如果有一组数据“1,1,3,4,5，null”列名为qty  
表达式Count(*) 返回的是6 但是
Count(qty)

插入完整的行

同时 where 子句检索 完成后 
它返回的是检索结果为True的行  ，但始终记住， SQL
数据库使用三值谓词逻辑，也就是说有三个结果。

创建高级联结

是5  count中给定显示值 就会默认寻找已知值 
也可以  count(distinct qty ) 返回的是4 去重复  这个 可以用来 处理 
返回每个不重复统计问题很方便 它和 select
distinct
有很大性能区别 以后会细讲 也可以
sum(distinct qty
) 是13
也是用作统计不重复数据。

+ – * /

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

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

5.返回查询出的数据 以及你要展示的字段

更新表

3.对客户id和订单年度 进行分组

管理事务处理

使用事务处理（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;

True,False 或 UNKNOWN ，  返回true 行 并不等同于
不返回False  实际上是不返回 False 行 和 UNKNOWN 行
以后会再博客中专门讲NULL。

where…in…

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

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

6.order by

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子句。

1.从 Orders 表查询数据

触发器 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;

FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate)
HAVING COUNT(*) > 1
SELECT empid, YEAR(orderdate) AS orderyear, COUNT(*) AS numorders
ORDER BY empid, orderyear;

创建计算字段

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

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

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

2.where

count()

select count(*) as num_cust from contacts;

select count(name) as num_name from contacts;

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

首先看下面一条比较完成语句，都是比较常见的关键字。

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将会更新表中所有行。

as

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

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

union规则

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

where

SELECT * FROM TB_BOOK_TAG WHERE count = 1;

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

操作符 说明
= 等于
<> 不等于
!= 不等于
< 小于
<= 小于等于
!< 不小于
> 大于
>= 大于等于
!> 不大于
BETWEEN 在指定的两个值之间
IS NULL 为NULL值

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

组合查询

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

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

约束（constraint）

联结表

检查约束 check

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

使用子查询作为计算字段

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

使用视图

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

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

分组数据

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子句之前。

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';

列出触发器

SELECT name FROM sqlite_master
WHERE type = 'trigger';

not

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

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

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

SQL 必知必会

在mac终端操作sqlite：

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

limit … offset …

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

count(distinct name)

select count(distinct name) from tb_book_tag;

使用子查询

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

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

外联结

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 all

DBMS不取消重复行。

注释

 --this is a comment

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

 /* comments */

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

删除视图 drop view

drop view customeremaillist;

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关键字。

alert table

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

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

alter table Vendors
add vend_phone char(20);

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

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

从一个表复制到另一个表

create table custcopy as select * from customers;

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

外键

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

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

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;

下划线_通配符

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

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

使用通配符的技巧

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

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

upper()

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

关键字：upper()转大写

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

函数 说明
length() 返回字符串的长度
lower() 将字符串转小写
ltrim() 去掉字符串左边的空格
rtrim() 去掉字符串右边的空格
upper() 将字符串转大写

索引 create index

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

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

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

CREATE INDEX index_name
ON table_name (column_name);

删除表 drop table

drop table Super;

聚集不同值

创建和操纵表

创建视图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语句的层次，可用来简化数据处理，重新格式化或保护基础数据。

sum()

select sum(mobile) as sum_mobile from contacts;

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

关系表

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

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

  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在运行时关联指定的每个表，以处理联结。这种处理可能非常耗资源，因此应该注意不要联结不必要的表。