必知必会,SQL语句处理顺序的坑

第大器晚成看上边一条比较形成语句,都以相比布满的首要字。

SQL 必知必会

在mac终端操作sqlite:

  • cd 数据库所在的目录
  • sqlite3 数据库文件名 //带后缀卡塔尔国(那个时候曾经展开数据库卡塔尔
  • .tables //呈现数据库中兼有曾经创办的表
  • .schema //展现全体表的方式
  • .headers on //展现字段名(查询时才博览会示卡塔 尔(阿拉伯语:قطر‎
  • .mode column/list/line
  • 进行sql语句必得在末尾加分号

生机勃勃,sql品质优化功底方法论

对此成效,大家也许知道必需更上生机勃勃层楼什么;但对此品质难题,有时我们或许无从入手。其实,任何Computer应用体系最终队能够总结为:

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;

distinct

 SELECT DISTINCT name FROM TB_BOOK_TAG;

关键字distinct,用于去除name列中有着行中重复成分。

二,sql调优领域

接收程序级调优

sql语句调优

治本浮动调优

示例级调优

内存

数据结构

实例配置

操作系统交互作用

I/O

swap

Parameters

大家来详细分析一下sql语句的逻辑管理顺序,即便select在每条语句的率先位,但骨子里它是被最后才管理的

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;

三,sql优化措施

优化工作数据

优化数据布置

优化流程设计

优化sql语句

优化学物理理构造

优化内部存款和储蓄器分配

优化I/O

优化内存竞争

优化操作系统

1.from  

limit … offset …

关键字LIMIT ... OFFSET ...,limit后跟的数字钦定展现多少行,offset后跟的数字代表从怎么样地点上马。(0是先是行卡塔尔国

四,sql优化进度

定位非常的言辞

自己商量施行陈设

反省实行安插中优化器的计算音信

浅析相关表的记录数、索引景况

改写sql语句、使用HINT、调度目录、表深入分析

微微sql语句不具有优化的或是,须求优化管理格局

达到最棒实行安顿

2.where

注释

 --this is a comment

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

 /* comments */

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

五,什么是好的sql语句

尽量简单,模块化

易读,易维护

节约财富

内存

cpu

围观的数目块要少

少排序

不变成死锁

3.group by

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

六,sql语句的管理进度

sql语句的多个管理阶段:

 

图片 2

解析(PARSE):

检查语法

自己商量语义和血脉相像的权力

在分享池中找找sql语句

归拢(ME奇骏GE卡塔 尔(阿拉伯语:قطر‎视图定义和子查询

鲜明实施安顿

绑定(BIND)

在言辞中搜寻绑定变量

赋值(或再一次赋值卡塔 尔(英语:State of Qatar)

执行(EXECUTE)

应用实行安顿

试行要求的I/O和排序操作

提取(FETCH)

从询问结果中回到记录

必要时展开排序

使用ARRAY FETCH机制

4.having

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

七,sql表的大旨连接格局

表连接有两种?

sql表连接分成外接连、内三番四次和交叉连接。

新建两张表:

表1:student  截图如下:

 

图片 3

表2:course  截图如下:

 

图片 4

(那个时候那样建表只是为着演示连接SQL语句,当然实际开销中大家不会那样建表,实际支付中那三个表会有友好分歧的主键。卡塔尔

一、外连接

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

1、左连接  left join 或 left outer join

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

实施结果:

 

图片 5

左外连接包罗left
join左表全数行,就算左表中某行在右表未有匹配,则结果中对应行右表的生龙活虎对全部为空(NULL).

注:那时我们不能够说结果的行数等于左表数据的行数。当然这里查询结果的行数等于左表数据的行数,因为左右两表当时为生机勃勃对一事关。

2、右连接  right join 或 right outer join

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

实施结果:

 

图片 6

右外连接满含right
join右表全部行,若是左表中某行在右表未有相称,则结果中对应左表的大器晚成对全体为空(NULL)。

注:同样那时大家无法说结果的行数等于右表的行数。当然这里查询结果的行数等于左表数据的行数,因为左右两表当时为黄金时代对风流罗曼蒂克关联。

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

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

实施结果:

 

图片 7

统统外接连满含full
join左右两表中享有的行,要是右表中某行在左表中平素不相称,则结果中对应行右表的有个别全部为空(NULL),若是左表中某行在右表中未有相称,则结果中对应行左表的片段全部为空(NULL)。

二、内连接  join 或 inner join

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

施行结果:

 

图片 8

inner join 是比较运算符,只回去切合条件的行。

那儿相当于:select * from student,course where student.ID=course.ID

三、交叉连接 cross join

1.定义:未有 WHERE
子句的接力联接将爆发连接所涉及的表的笛Carl积。第三个表的行数乘以第叁个表的行数等于笛Carl积结果集的大小。

SQL语句:select * from student cross join course

实行结果:

 

图片 9

万生龙活虎大家在那时给那条SQL加上WHERE子句的时候譬喻SQL:select * from student
cross join course where student.ID=course.ID

那儿将赶回切合条件的结果集,结果和inner join所示实施结果生龙活虎致。

5.select

where

SELECT * FROM TB_BOOK_TAG WHERE count = 1;

关键字:where,钦赐寻觅条件进行过滤。where子句在表名(from子句卡塔 尔(英语:State of Qatar)之后给出。在同一时间利用whereorder by时,应该让order by位于where之后。

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

只顾:NULL和非相称
由此过滤采取不分包(如<>卡塔 尔(英语:State of Qatar)钦赐值的具有行时,你也许希望回到含NULL值的行,可是那做不到,因为NULL有新鲜的含义,数据库不驾驭它们是不是合作,所以在扩充相配过滤或非相称过滤时,不会回来那个结果。

八,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 子句–实行顺序为从后往前、从右到左

表名(最前边的这二个表名字为驱动表,实践顺序为从后往前,
所以数据量超级少的表尽量放后卡塔 尔(英语:State of Qatar)

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

将得以过滤掉多量数额的准则写在where的子句的终极性能最优

group by 和order by 子句施行各样都为从左到右

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

2,幸免生出笛Carl积

含有多表的sql语句,必需指明各表的连续几日条件,以制止发生笛Carl积。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

当提交三个暗含风姿罗曼蒂克对多表音讯(比如单位表和雇员表卡塔 尔(英语:State of Qatar)的查询时,防止在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”,”O途睿欧”

带有”IN”、”O库罗德”的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 of Qatar),未有应用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服务崩溃,还会令你的整台服务器登时挂了。所以,若是你有四个大的拍卖,你早晚把其拆分。

 

 

 

 

 

 

 

6.order by

where…and…

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

关键字:and,组合where子句。

7.TOP

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

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;

not

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

关键字:not,where子句中用来否认其后条件的主要字。下面的事例也能够用<>。在轻巧语句中,not从未怎么优势,可是,在更头晕目眩的子句中,not至极常有效。比如,在与in操作符联合使用时,not可以特别简单的寻觅与准则列表不相称的行。如下例子:

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

1.从 Orders 表查询数据

like

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

通配符搜索只可以用于文书字段(字符串卡塔尔,非文本数据类型字段不能够选择通配符搜索

2.基于法规筛选客商ID等于71的

%通配符

在寻找字符串中,%表示其他字符出现率性次数

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

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

'%' 不会匹配为NULL的行

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

下划线_通配符

用处和%同样,但它只匹配单个字符,并不是四个。

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

应用通配符的本事

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

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

开创总结字段

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

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

关键字:||rtrim()
||东挪西凑操作符。rtrim()去除文本右侧的空格。trim()剔除两边的空格。

5.重回查询出的数据 以至你要显得的字段

as

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

关键字:as,全称alias。它提醒SQL创制七个包括钦点总结结果的名字为title的精打细算字段,任何客商端应用能够按名称援引那些列,就好像多个实际上表列同样。

6.尾声对客商id 和订单 实行排序

试行算术计算

7.输出

+ – * /

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

关键字:+-*/

输入的键入顺序和拍卖顺序区别等是有缘由的,SQL设计师是为着让顾客根据罗马尼亚(罗曼ia卡塔 尔(英语:State of Qatar)语的方式提供本人的乞请

函数

转眼所用到的是适用于sqlite的函数,不料定适用于任何DBMS。

建议、坑

upper()

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

关键字:upper()转大写

sqlite中常用于文本管理函数:

函数 说明
length() 返回字符串的长度
lower() 将字符串转小写
ltrim() 去掉字符串左边的空格
rtrim() 去掉字符串右边的空格
upper() 将字符串转大写
  1. from 表时  最棒给定 库名和表名  Sales.Orders 
    让表突显表示 不用程序检索。

avg()

select avg(mobile) as avg_id from contacts;

关键字:avg(),对表中某列全部行或特定行中的数据求平均值。该函数会忽视值为NULL的行。

  1. where 子句举足轻重  SQL Server 会对where 条件
    举行业评比估访谈诉求数据要采纳的目录,通过索引能够大大减弱表扫描时间

count()

select count(*) as num_cust from contacts;

select count(name) as num_name from contacts;

关键字:count(),使用count(*),对表中央银行的多寡实行计数,不管表列中是还是不是含有NULL值。使用count(column_name),对特定列中具备值的行开展计数,忽视NULL值。

还要 where 子句检索 达成后 
它回到的是寻觅结果为True的行  ,但始终铭刻, SQL
数据库使用三值谓词逻辑,相当于说有多个结实。

sum()

select sum(mobile) as sum_mobile from contacts;

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

True,False 或 UNKNOWN ,  重返true 行 并不等同
不回去False  实际上是不回去 False 行 和 UNKNOWN 行
今后会再博客中等职业学园门讲NULL。

聚焦分裂值

3.记住除count(*)之外, 
聚合函数都以忽略NULL标识  倘使有大器晚成组数据“1,1,3,4,5,null”列名叫qty  
表明式Count(*) 重临的是6 不过Count(qty)

count(distinct name)

select count(distinct name) from tb_book_tag;

是5  count中加以显示值 就能够暗中同意搜索已知值 
也能够  count(distinct qty ) 重回的是4 去重新  那一个 能够用来 管理 
再次来到每一种不另行总括难点很方便 它和 select
distinct
有非常的大质量差异 未来会细讲 也得以
sum(distinct qty
) 是13
也是用作计算不重复数据。

组成集中函数

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

4.因为 group by
归于行处理 在having 先计算机本领切磋所以having 中能够现身  聚合函数 。

分组数据

5.像上边的 “YEALX570(orderdate)” SQL Server 只对它运维三遍 
能辨别查询中重复使用的同等表达式

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

6.最棒别使用 select * 就算你要查询 全体字段。

group by…having…

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

关键字:having。对分组举行过滤。而where对分组不起效率,它是针对性表中每风流洒脱行来过滤。

7.施用 order by 对有大气重复的字段实行排序是低效的  举个例子对日期实行排序
这样八个排序选10条 会有多少个被认为是没有错结果
所以大家要承保排序字段的数码唯后生可畏性, 以至在 select distinct  时 排序
会引致 单个结实对应七个源数据行。

使用子查询

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子句,第1个表中的每一行将与第一个表中的每后生可畏行配成对,而无论他们逻辑上是还是不是能协作在联合签字。这种联合称为等值联结(equijoin卡塔 尔(英语:State of Qatar),也称得上内统生机勃勃(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的第一列(不管列名如何卡塔 尔(英语:State of Qatar)将用来填充表列中钦赐的率先列,如此等等。

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