SQL语言小结和优化（整理）

我们在实际中最常用的就是基本的sql语句了，实际上我个人感觉sql很难记忆（哈哈，语法和编程语言差太多了），所以往往有时候写些复杂的sql语句就必须百度一下语法了，虽然现在有不错的ORM的工具，但还是觉得简单得数据库操作使用ORM比较方便，但太复杂的应用就不太爽了，当然我们可以利用面向对象的思路把复杂的sql语句给拆分成简单的。下面是新整理收藏的SQL相关资料。

1.数据定义语言(DDL)
常用的DDL语句
Create Table 创建数据库对象
Create Index 创建数据库表索引
Drop Table    删除数据库表
Drop Index    删除数据库表索引
Truncate      删除表中所有行业

Alert Table   增加表列,重定义表列,更改存储分配
Alert Table   ADD CONSTRAINT 在已有的表上增加约束

2.数据操纵语言(DML)
Insert 增加数据行到表
Delete 从表中删除数据行
Update 更改表中数据
Select 从表或视图中检索数据行

3.数据控制语言(DCL)
DCL用于规定数据库用户的各种权限

GRANT    将权限或角色授予用户或其他角色
REVOKE   从用户或数据库角色回收权限
Set ROLE 禁止或允许一个角色

4.数据库事务控制

常用的事务语句包括

COMMIT WORK 把当前事务更改永久化(写入磁盘)
ROLLBACK 作废上次提交以来的所有更改

SQL语句基本语法

每条SQL语句必须以分号结束.

每条SQL语句可以单独写成一行,但为了清楚,也可以分成若干行.

SQL语句对大小写不敏感,对于SQL语句的关键字(例如,Insert,Select 等),

表名、列名等，可以大小写混写；但是对列的内容是大小写敏感的。

别名

数据表名称 AS 数据表别名
或者是：
数据表名称 数据表别名

Eg:
Use SAMPLE
Select e.员工编号 ，e.员工姓名
From 员工数据表 AS e

Select 语句
Select 语句基本结构如下：
Select   select_list
[INTO new_table_name]
From table_list
[Where search_conditions]
[GROUP BY group_by_list]
[Having search_conditions]
[ODER BY order_list[ASC|DESC]]

1.DISTINCT关键字
   eg:
   Use SAMPLE
   Select DISTINCT 所属部门
   from 员工数据表
2.TOP 关键字

From子句
联接条件---on子句
派生表---sp

Select 员工数据表.员工编号,项目数据表.项目名称
From 员工数据表 join 项目数据表 ON
(员工数据表.员工编号 = 项目数据表.负责人)

Select EMP.员工编号,EMP.员工姓名,SP.部门名称
From 员工数据表 AS EMP ,
(Select 部门数据表.部门编号,部门数据表.部门名称
From 部门数据表
Where 部门数据表.部门编号>2
) AS SP
Where EMP.部门编号=SP.部门编号

Where 子句
Where子句中查询和限定的条件可以是
比较运算符(如常=、<>、<和>).
范围说明（BETWEEN 和 NOT BETWEEN）。
可选列表（IN、NOT IN）
模式匹配（Like 和NOT Like）.
是否为空值（IS NULL 和 IS NOT NULL）。
上述条件的逻辑组合（AND 、OR和NOT）。

GROUP BY 子句
   GROUP BY 子句的主要作用是可以将数据记录依据设置的条件分成多个组,
而且只有使用了GROUP BY

子句,SELECT中所使用的汇总函数(例如SUM、COUNT、MIN、MAX等)
才会起作用。
eg:
Select 所属部门，AVG（工资） AS 平均工资 From 员工数据表 GROUP BY
所属部门
   HAVING 关键字
HAVING子句将对GROUP BY 子句选择出来的结果进行再次筛选，最后输出符

合HAVING 子句中条件的记录。
eg:
Select 所属部门，AVG（工资） AS 平均工资 From 员工数据表 GROUP BY
所属部门 HAVING 平均工资>2000

   Where 子句用来筛选From子句中指定的操作所产生的记录
   GROUP BY 子句将Where子句中结果惊醒分组
   HAVING 子句将从经过分组后的中间结果集中筛选记录
  
   ALL关键字
   CUBE关键字
   WITH CUBE 关键字的主要作用是自动对GROUP BY 子句中列出的字段进行
分组汇总运算。
EG：
   Select 所属部门 ，性别，AVG（工资） From 员工数据表 GROUP BY
所属部门，性别 WITH CUBE

ROLLUP 关键字

ORDER BY 子句

ASC ---升序
DESC --降序

COMPUTE 和COMPUTE BY 子句

使用UNION 子句
UNION运算符可以用来将2个或多个查询结果集组合起来，成为一个结果集

。使用UNION运算符的结果集都必须满足下列条件：
具有相同的结构
字段数目相同
结果集中相对应的数据类型必须兼容
UNION运算符指定的格式如下：
   Select 语句
   UNION[ALL]
   Select 语句

汇总函数
SUM函数
AVG函数
COUNT函数
COUNT（*）函数
MAX函数
MIN函数

联接查询
   通过使用联接产寻可以根据各个数据表之间的逻辑关系从2个或多个
数据表中检索数据。
  
   如何提高Select语句的效率
   （1）.使用EXISTS关键字检查结果集
   （2）.使用标准的连接代替嵌套查询
   （3）.有效避免整表扫描
  
   联接的类型
  
   内联接
   内联结的格式为：
   数据表1 INNER JOIN 数据表2 ON 联接表达式
   内联接将通过使用比较运算符根据需要联接的数据表的公共的字段
值来匹配二表中的记录。
   eg:
    Select * From 员工数据表 INNER JOIN 项目数据表
    ON 员工数据表.员工编号 = 项目数据表.负责人

外联接
外联接包括3种 左向外联接、右向外联接、完整外部联接

左向外联接的格式为：
数据表1 LEFT JOIN 数据表2 ON 连接表达式
或者是：
数据表1 LEFT OUTER JOIN 数据表2 ON 连接表达式
使用左向联接进行查询的结果集将包括数据表1中所有的记录,而仅仅是连

接字段所匹配的记录,那么结果集想对应的有关数据表2的所有字段将为空值

.例如下面将检索员工数据表中所有记录,并将项目表中显得负责人字段匹配

的记录输出到结果集:
Select *
       From 员工数据表 LEFT JION 项目数据表
       ON   员工数据表.员工编号 =项目数据表.负责人

右向外联接
右向外联接的格式:
   数据表1 RIGHT JOIN 数据表2 ON 表达式
   或者是:
   数据表1 RIGHT OUTER JOIN 数据表2 ON 表达式
   和左向外联接相反.

完整外联接
完整外联接的格式
   数据表1 FULL JOIN 数据表2 ON 表达式  
   或者是:
   数据表1 FULL OUTER JOIN 数据表2 ON 表达式

交叉联接
交叉连接的格式为:
数据表1 CROSS JOIN 数据表2
如果在Select语句中没有使用Where子句,那么交叉联接将返回数据表1和数

据表2的卡笛尔积,即交叉联接返回数据表1中的所有记录,以及数据表1中所

有的记录与数据表2中所有句路的组合.结果集的记录数等于数据表1的记录

数目乘以数据表2的记录数目.

在From和Where分句中指定联接

嵌套查询
   嵌套查询指的是一个外层查询中包含一个内层查询.其中外层查询称为主

查询,内层查询称为子查询.
   使用IN 和 NOT IN 关键字
   Use SAMPLE
   Select 员工姓名
   From 员工数据表
   Where 员工编号 IN
   (Select DISTINCT 负责人
   From 项目数据表
   Where 结束日期<'January 1,1,2006')
使用比较运算符
主要是返回单个值
   Use SAMPLE
   Select 员工姓名
   From 员工数据表
   Where 员工编号 =
   (Select DISTINCT 负责人
   From 项目数据表
   Where 项目名称='Demo1')
使用EXISTS 和 NOT EXISTS 关键字

数据更改
   Insert
   Update
   Delete

下面是sql语句优化的一些提示要点，感谢提供者。

操作符优化

IN 操作符

用IN写出来的SQL的优点是比较容易写及清晰易懂，这比较适合现代软件开发的风格。

但是用IN的SQL性能总是比较低的，分析用IN的SQL与不用IN的SQL有以下区别：

        数据库试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功，但对于含有分组统计等方面的SQL就不能转换了。

       推荐方案：在业务密集的SQL当中尽量不采用IN操作符。

NOT IN操作符

       此操作是强列推荐不使用的，因为它不能应用表的索引。

       推荐方案：用NOT EXISTS 或（外连接+判断为空）方案代替

<> 操作符（不等于）

       不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。

推荐方案：用其它相同功能的操作运算代替，如

        a<>0 改为 a>0 or a<0

        a<>’’ 改为 a>’’

IS NULL 或IS NOT NULL操作（判断字段是否为空）

       判断字段是否为空一般是不会应用索引的，因为B树索引是不索引空值的。

       推荐方案：

用其它相同功能的操作运算代替，如

        a is not null 改为 a>0 或a>’’等。

       不允许字段为空，而用一个缺省值代替空值，如业扩申请中状态字段不允许为空，缺省为申请。

       建立位图索引（有分区的表不能建，位图索引比较难控制，如字段值太多索引会使性能下降，多人更新操作会增加数据块锁的现象）

> 及 < 操作符（大于或小于操作符）

       大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有100万记录，一个数值型字段A，30万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了，因为A>2时ORACLE会先找出为2的记录索引再进行比较，而A>=3时ORACLE则直接找到=3的记录索引。

LIKE 操作符

LIKE操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如LIKE ‘%5400%’ 这种查询不会引用索引，而LIKE ‘X5400%’则会引用范围索引。一个实际例子：用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号 YY_BH LIKE ‘%5400%’ 这个条件会产生全表扫描，如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE ’B5400%’ 则会利用YY_BH的索引进行两个范围的查询，性能肯定大大提高。

UNION 操作符

UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如：

select * from gc_dfys

union

select * from ls_jg_dfys

这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。

推荐方案：采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。

select * from gc_dfys

union all

select * from ls_jg_dfys

SQL书写的影响

同一功能同一性能不同写法SQL的影响

如一个SQL在A程序员写的为

        Select * from zl_yhjbqk

B程序员写的为

        Select * from dlyx.zl_yhjbqk（带表所有者的前缀）

C程序员写的为

        Select * from DLYX.ZLYHJBQK（大写表名）

D程序员写的为

        Select * from DLYX.ZLYHJBQK（中间多了空格）

 

以上四个SQL在数据库分析整理之后产生的结果及执行的时间是一样的。

 

WHERE后面的条件顺序影响

 

WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响，如

Select * from zl_yhjbqk where dy_dj = '1KV 以下 ' and xh_bz=1

Select * from zl_yhjbqk where xh_bz=1 and dy_dj = '1KV 以下 '

以上两个SQL中 dy_dj （电压等级）及xh_bz（销户标志）两个字段都没进行索引，所以执行的时候都是全表扫描，第一条SQL的 dy_dj = '1KV 以下 ' 条件在记录集内比率为99%，而 xh_bz=1 的比率只为0.5%，在进行第一条SQL的时候99%条记录都进行 dy_dj 及 xh_bz 的比较，而在进行第二条SQL的时候0.5%条记录都进行 dy_dj 及 xh_bz 的比较，以此可以得出第二条SQL的CPU占用率明显比第一条低。

 

查询表顺序的影响

在FROM后面的表中的列表顺序会对SQL执行性能影响，在没有索引及数据库没有对表进行统计分析的情况下数据库会按表出现的顺序进行链接，由此因为表的顺序不对会产生十分耗服务器资源的数据交叉。（注：如果对表进行了统计分析，数据库会自动先进小表的链接，再进行大表的链接）

 

SQL语句索引的利用

对操作符的优化（见上节）

对条件字段的一些优化

采用函数处理的字段不能利用索引，如：

substr(hbs_bh,1,4)=’ 5400’，优化处理：hbs_bh like ‘5400%’

trunc(sk_rq)=trunc(sysdate)， 优化处理：

sk_rq>=trunc(sysdate) and sk_rq<trunc(sysdate+1)

进行了显式或隐式的运算的字段不能进行索引，如：

ss_df+20>50，优化处理：ss_df>30

‘X’||hbs_bh>’X 5400021452’，优化处理：hbs_bh>’ 5400021542’

sk_rq+5=sysdate，优化处理：sk_rq=sysdate-5

hbs_bh=5401002554，优化处理：hbs_bh=’ 5401002554’， 注：此条件对hbs_bh 进行隐式的to_number转换，因为hbs_bh字段是字符型。

条件内包括了多个本表的字段运算时不能进行索引，如：

ys_df>cx_df，无法进行优化

qc_bh||kh_bh=’ 5400250000’，优化处理：qc_bh=’ 5400’ and kh_bh=’ 250000’

索引提示：

INDEX（TABLE INDEX）（使用表索引进行查询）