图片 9

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

5.回去查询出的数据 以及你要呈现的字段

not

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

关键字:not,where子句中用来否认其后条件的重大字。上边的例证也足以用<>。在简练语句中,not尚无什么优势,然则,在更头眼昏花的子句中,not丰盛实用。举个例子,在与in操作符联合使用时,not能够非常轻巧的寻找与标准列表不包容的行。如下例子:

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

七,sql表的核心连接方式

表连接有二种?

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

新建两张表:

表1:student  截图如下:

 

图片 1

表2:course  截图如下:

 

图片 2

(此时那样建表只是为了演示连接SQL语句,当然实际支付中我们不会如此建表,实际支付中那五个表会有投机不相同的主键。)

一、外连接

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

1、左连接  left join 或 left outer join

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

实践结果:

 

图片 3

左外连接包含left
join左表全部行,即使左表中某行在右表未有匹配,则结果中对应行右表的一对全部为空(NULL).

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

2、右连接  right join 或 right outer join

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

实践结果:

 

图片 4

右外连接满含right
join右表全部行,假诺左表中某行在右表没有相配,则结果中对应左表的片段全部为空(NULL)。

注:相同此时大家不能够说结果的行数等于右表的行数。当然这里查询结果的行数等于左表数据的行数,因为左右两表此时为一对一事关。

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

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

实行结果:

 

图片 5

全然外接连富含full
join左右两表中有所的行,借使右表中某行在左表中从未匹配,则结果中对应行右表的一部分全部为空(NULL),假使左表中某行在右表中并未有相称,则结果中对应行左表的一些全部为空(NULL)。

二、内连接  join 或 inner join

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

进行结果:

 

图片 6

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

那时候一定于:select * from student,course where student.ID=course.ID

三、交叉连接 cross join

1.定义:未有 WHERE
子句的穿插联接将时有产生连接所提到的表的笛Carl积。第三个表的行数乘以第二个表的行数等于笛Carl积结果集的大大小小。

SQL语句:select * from student cross join course

实施结果:

 

图片 7

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

那时将再次来到符合条件的结果集,结果和inner join所示实施结果一律。

4.having

成立总括字段

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

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

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

一,sql品质优化基础方法论

对于效率,大家兴许知道必需更进一步什么;但对于品质难题,不常大家也许无从动手。其实,任何计算机应用系统最终队能够归纳为:

cpu消耗

内部存储器使用

对磁盘,互联网或别的I/O设备的输入/输出(I/O)操作。

 

但大家境遇质量难题时,要决断的首先点就是“在这两种能源中,是还是不是有哪种能源到达了有题指标档期的顺序”,因为那或多或少能指导大家搞通晓“要求优化重构什么”和“怎么着优化重构它”

图片 8

True,False 或 UNKNOWN ,  再次回到true 行 并区别等
不回来False  实际上是不回来 False 行 和 UNKNOWN 行
今后会再博客中特地讲NULL。

开创和垄断表

二,sql调优领域

行使程序级调优

sql语句调优

管理转换调优

示例级调优

内存

数据结构

实例配置

操作系统交互

I/O

swap

Parameters

何况 where 子句检索 完成后 
它回到的是搜索结果为True的行  ,但一向铭记, SQL
数据库使用三值谓词逻辑,也正是说有四个结果。

count(distinct name)

select count(distinct name) from tb_book_tag;

四,sql优化进度

恒久有题指标讲话

自己探讨施行安插

反省实践布署中优化器的总计音讯

剖析相关表的记录数、索引情状

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

有一些sql语句不持有优化的只怕,须要优化管理形式

落得最好推行布署

1.从 Orders 表查询数据

union规则

  • union必得由两条或两条以上的select语句组成,语句之间用关键字union分隔。
  • union中的每一种查询必需带有一样的列,表达式或聚焦函数(然而,种种列不必要以同等的次体系出)。
  • 列数据类型必需协作:类型不必千篇一律,但务必是DBMS可以分包转换的门类。

三,sql优化措施

优化学工业作数据

优化数据安顿

优化流程设计

优化sql语句

优化学物理理结构

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

优化I/O

优化内部存款和储蓄器竞争

优化操作系统

7.输出

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

八,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,制止爆发笛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

当提交三个暗含一对多表新闻(比方单位表和雇员表)的查询时,幸免在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条件语句中的列都建有适度的目录,但几九千0条数据下已经实行一点也不快了:

select * from record where amount/30<一千 (试行时间11s)

出于where子句中对列的别的操作结果都是在sql运行时逐行总计得到,因而它只能实行全表扫描,而从不使用方面包车型地铁目录;假如这个结果在询问编写翻译时就能够得到,那么就能够被sql优化器优化,使用索引,幸免全表扫描,由此sql重写如下:

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

12,尽量去掉”IN”,”O奥迪Q7″

满含”IN”、”O奥迪Q7″的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服务崩溃,还恐怕会让您的整台服务器立刻挂了。所以,尽管你有一个大的管理,你显著把其拆分。

 

 

 

 

 

 

 

建议、坑

重组查询

关键有二种情景必要利用组合查询:

  • 在二个询问中从不一样的表重临结构数据
  • 对贰个表推行几个查询,按二个询问重返数据

五,什么是好的sql语句

全力以赴简单,模块化

易读,易维护

节约能源

内存

cpu

扫描的数码块要少

少排序

不变成死锁

 

sum()

select sum(mobile) as sum_mobile from contacts;

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

六,sql语句的处理进程

sql语句的八个管理阶段:

 

图片 9

解析(PARSE):

自己商量语法

检查语义和血脉相通的权柄

在分享池中搜寻sql语句

联合(MEEnclaveGE)视图定义和子查询

分明实行布署

绑定(BIND)

在讲话中寻觅绑定变量

赋值(或重新赋值)

执行(EXECUTE)

选取实行铺排

实践须求的I/O和排序操作

提取(FETCH)

从询问结果中回到记录

必备时进行排序

使用ARRAY FETCH机制

6.order by

自联结

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处理统一远比管理子查询快得多

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

函数

一转眼所用到的是适用于sqlite的函数,不自然适用于任何DBMS。

输入的键入顺序和拍卖顺序不雷同是有缘由的,SQL设计员是为了让客商根据塞尔维亚共和国(Republic of Serbia)语的不二秘技提供本身的央浼

约束(constraint)

是5  count中加以呈现值 就能够暗许找出已知值 
也足以  count(distinct qty ) 再次回到的是4 去重新  那几个 能够用来 管理 
重返每一个不另行计算难题很方便 它和 select
distinct
有异常的大品质区别 现在会细讲 也足以
sum(distinct qty
) 是13
也是用作总括不另行数据。

分组数据

  1. where 子句比较重大  SQL Server 会对where 条件
    进行业评比估采访央求数据要采取的目录,通过索引能够大大降低表扫描时间

删去视图 drop view

drop view customeremaillist;

咱俩来详细深入分析一下sql语句的逻辑管理顺序,尽管select在每条语句的率先位,但实质上它是被最后才处理的

as

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

关键字:as,全称alias。它指示SQL创造贰个包含钦赐总结结果的名称叫title的测算字段,任何顾客端应用能够按名称援引那么些列,就像是三个实在表列同样。

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;

alert table

使用alert table改变表的布局,必得付出下边的新闻:

  • alter table而后给出要退换的表名。
  • 列出要做出怎么着改造。

alter table Vendors
add vend_phone char(20);

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

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

7.利用 order by 对有大气双重的字段进行排序是无效的  举个例子对日期举行排序
那样三个排序选10条 会有八个被以为是对的结果
所以大家要保管排序字段的数额独一性, 以及在 select distinct  时 排序
会导致 单个结实对应多少个源数据行。

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

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

联结表

4.因为 group by
属于行管理 在having 先计算机技术商讨所以having 中得以出现  聚合函数 。

%通配符

在探索字符串中,%表示其他字符出现任意次数

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

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

'%' 不会匹配为NULL的行

5.select

安排完整的行

6.结尾对客商id 和订单 进行排序

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

7.TOP

count()

select count(*) as num_cust from contacts;

select count(name) as num_name from contacts;

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

在条分缕析深入分析各样实践顺序代表的情致 (它的其实顺序)

聚拢不一样值

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;

where…in…

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

关键字:in,用来钦点条件限制,范围中的每一种条件都足以开展相称。in操作符一般比一组or操作符试行的更加快。in最大的独到之处是能够分包其余select语句,能够改变态的创设where子句。

1.from  

limit … offset …

关键字LIMIT ... OFFSET ...,limit后跟的数字钦赐展现多少行,offset后跟的数字代表从怎么着地方上马。(0是首先行)

2.依照法则筛选顾客ID等于71的

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

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

where…and…

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

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

2.where

自己争持约束 check

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

5.像下边包车型客车 “YEA奥迪Q3(orderdate)” SQL Server 只对它运营贰回 
能识别查询中重复使用的同等表明式

注释

 --this is a comment

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

 /* comments */

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

3.记住除count(*)之外, 
聚合函数都以忽视NULL标识  假若有一组数据“1,1,3,4,5,null”列名称为qty  
表达式Count(*) 重临的是6 但是Count(qty)

高级SQL特性

约束,索引,触发器。

3.group by

插入数据

  1. from 表时  最棒给定 库名和表名  Sales.Orders 
    让表突显表示 不用程序检索。

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语句如故能够健康干活。

率先看上边一条相比较造成语句,都以相比较分布的根本字。

独一约束 unique

独一约束用来担保一列中的数据是独一的。与主键的区分如下:

  • 表可含蓄三个唯一约束,但种种表只允好多个主键。
  • 独一约束列可含蓄NULL值。
  • 独一约束列可修改或更新。
  • 独一约束列的值可重复使用。
  • 与主键不一致样,独一约束无法用来定义外键。

成立高等联结

group by…having…

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

关键字:having。对分组举行过滤。而where对分组不起成效,它是对准表中每一行来过滤。

SQL 必知必会

在mac终端操作sqlite:

  • cd 数据库所在的目录
  • sqlite3 数据库文件名 //带后缀)(此时已经展开数据库)
  • .tables //呈现数据库中享有曾经创办的表
  • .schema //显示全数表的模式
  • .headers on //呈现字段名(查询时才会显得)
  • .mode column/list/line
  • 推行sql语句必需在末尾加分号

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;

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

关系表

为驾驭关系表,来看三个例证:

有二个含有产品目录的数额库表,个中每类货品占一行,对于每个物品,要存款和储蓄的音信包罗产品描述,价格以及生产该产品的中间商。
幸存同一供应商生产的有余货品,那么在何地存储分销商名联系方法等新闻?将这个多少与制品音信分别储存的理由是:

  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;

在会集八个表时,实际要做的是将第二个表中的每一行与第2个表中的每一行配对。where子句作为过滤条件,只含有这个相配给定准绳的行。未有where子句,第3个表中的每一行将与第四个表中的每一行配成对,而任由他们逻辑上是否能相称在一同。这种统一称为等值联结(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在运行时关联指定的每个表,以处理联结。这种处理可能非常耗资源,因此应该注意不要联结不必要的表。

索引 create index

索引用来排序数据以加速寻找和排序操作的速度。想象一本书后的目录。

在初阶创办索引前,应该记住以下内容:

  • 目录改进检索操作的个性,但下跌了数额插入,修改,和删除的属性。在施行这个操作时,DBMS必得动态的革新索引。
  • 目录数据或者要占领一大波的贮存空间。
  • 决不全体数据都符合做索引。
  • 目录用于数据过滤和多少排序。
  • 能够在目录中定义五个列(举个例子,州加上城市)。那样的目录仅在以州加城市的逐条排序时有用。假如想按城市排序,则这种索引未有用处。

CREATE INDEX index_name
ON table_name (column_name);

从一个表复制到另三个表

create table custcopy as select * from customers;

要想只复制部分列,可以明显给出列名。

delete

delete无需列名或通配符,因为它删除的是整行实际不是删除列,要刨除钦定列,使用update

delete from custcopy 
where cust_id = '1000000008';

一旦省略where子句,它将去除表中的每一种客商。假使想从表中删除全部行,不要采纳delete,可使用truncate
table语句,它的快慢越来越快,因为不记录数据的改观。

+ – * /

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

关键字:+-*/

整合聚焦函数

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

外键

外键是表中的一列,其值必得列在另一表的主键中。

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

列出触发器

SELECT name FROM sqlite_master
WHERE type = 'trigger';

采纳表外号

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 * from tb_book_tag where name like '计算机__';

利用通配符的手艺

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

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

更新和删除数据

更新表

创制视图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语句的档次,可用来简化数据处理,重新格式化或保卫安全基础数据。

使用子查询

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

注意:
作为子查询的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;

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

应用视图

视图是杜撰的表。与含蓄数据的表不等同,视图只含有使用时动态检索数据的询问。
视图(View)只然而是经过有关的称谓存款和储蓄在数据库中的四个 SQLite
语句。视图(View)实际上是贰个以预订义的 SQLite 查询格局存在的表的构成。

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

like

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

通配符寻找只可以用来文书字段(字符串),非文本数据类型字段不可能使用通配符搜索

删除索引 drop index

DROP INDEX index_name;

where…or…

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

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

瞩目:在同期选用and和or时要专心求值顺序,and优先级大于or。由此在其余时候利用具备and和or操作符的where子句时,都应有使用圆括号显著地分组操作符

upper()

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

关键字:upper()转大写

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

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

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 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.

distinct

 SELECT DISTINCT name FROM TB_BOOK_TAG;

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

avg()

select avg(mobile) as avg_id from contacts;

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

删除表 drop table

drop table Super;

where

SELECT * FROM TB_BOOK_TAG WHERE count = 1;

关键字:where,钦定搜索条件实行过滤。where子句在表名(from子句)之后给出。在同一时候利用whereorder by时,应该让order by位于where之后。

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

小心:NULL和非相称
通过过滤采用不包涵(如<>)内定值的有着行时,你可能希望重临含NULL值的行,可是那做不到,因为NULL有优良的含义,数据库不精晓它们是或不是相配,所以在拓展相配过滤或非相称过滤时,不会回来这一个结果。

使用子查询作为总计字段

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

对组合查询结果排序

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语句再次来到的保有结果。

union all

DBMS不撤废重复行。

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

主键

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

表中大肆列只要满意以下法规,都得以用于主键。

  • 轻松两行主键值都不等同。
  • 每行都持有二个主键值(既列中不允许NULL)。
  • 带有主键的列从不修改或更新。
  • 主键值无法重用。

试行算术总括