葡京网投哪个正规 > 新葡亰-数据 > 必知必会,高效sql性能优化极简教程

原标题:必知必会,高效sql性能优化极简教程

浏览次数:53 时间:2019-10-25

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

SQL 必知必会

在mac终端操作sqlite:

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

一,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;

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语句

合并(MERGE)视图定义和子查询

确定执行计划

绑定(BIND)

在语句中查找绑定变量

赋值(或重新赋值)

执行(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 子句的交叉联接将产生连接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。

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子句)之后给出。在同时使用whereorder by时,应该让order by位于where之后。

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

注意:NULL和非匹配
通过过滤选择不包含(如<>)指定值的所有行时,你可能希望返回含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 子句--执行顺序为从后往前、从右到左

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

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服务崩溃,还可能会让你的整台服务器马上挂了。所以,如果你有一个大的处理,你一定把其拆分。

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设计师是为了让用户按照英文的方式提供自己的请求

函数

一下所用到的是适用于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.像上面的 “YEAR(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子句,第一个表中的每一行将与第二个表中的每一行配对,而不管他们逻辑上是否能匹配在一起。这种联结称为等值联结(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';

本文由葡京网投哪个正规发布于新葡亰-数据,转载请注明出处:必知必会,高效sql性能优化极简教程

关键词:

上一篇:葡京网投哪个正规:资源等待之PAGEIOLATCH,O硬盘交互

下一篇:STUFF函数的用法