Mysql系列 (八) 编写脚本

首要步骤(一般可省略):
开启MySQL服务(默认是开机时就自动运行了):
控制面板-->管理工具-->服务,在里面找到名称为mysql的项,双击就可看它的服务状态是启动还是停止,把它设为启动

连接mysql:
在命令行下输入 mysql -h localhost -u root -p回车,然后输入密码即可;或直接运行mysql自带的连接工具,然后输入密码即可.

1.编写sql脚本,假设内容如下:

create database dearabao;
  use dearabao;
  create table niuzi (name varchar(20));

保存脚本文件,假设我把它保存在F盘的hello world目录下,于是该文件的路径为:F:/hello world/niuzi.sql

2.执行sql脚本,可以有2种方法:
第一种方法:
在命令行下(未连接数据库),输入 mysql -h localhost -u root -p123456 < F:/hello world/niuzi.sql (注意路径不用加引号的!!) 回车即可.
第二种方法:
在命令行下(已连接数据库,此时的提示符为 mysql> ),输入 source F:/hello world/niuzi.sql (注意路径不用加引号的) 或者 /. F:/hello world/niuzi.sql (注意路径不用加引号的) 回车即可

SQL编写规范

1.书写格式
示例代码：

存储过程SQL文书写格式例

select 
        c.dealerCode,
        round(sum(c.submitSubletAmountDLR + c.submitPartsAmountDLR + c.submitLaborAmountDLR) / count(*), 2) as avg, 
        decode(null, 'x', 'xx', 'CNY') 
from ( 
        select 
                a.dealerCode,
                a.submitSubletAmountDLR, 
                a.submitPartsAmountDLR, 
                a.submitLaborAmountDLR 
        from SRV_TWC_F a
        where (to_char(a.ORIGSUBMITTIME, 'yyyy/mm/dd') >= 'Date Range(start)' 
        and to_char(a.ORIGSUBMITTIME, 'yyyy/mm/dd') <= 'Date Range(end)'   
        and nvl(a.deleteflag, '0') <> '1') 
        union all 
        select 
                b.dealerCode,
                b.submitSubletAmountDLR, 
                b.submitPartsAmountDLR, 
                b.submitLaborAmountDLR 
        from SRV_TWCHistory_F b
        where (to_char(b.ORIGSUBMITTIME, 'yyyy/mm/dd') >= 'Date Range(start)' 
        and to_char(b.ORIGSUBMITTIME,'yyyy/mm/dd') <= 'Date Range(end)'   
        and nvl(b.deleteflag,'0') <> '1')
) c
group by c.dealerCode 
order by avg desc;

Java source里的SQL字符串书写格式例

strSQL = "insert into Snd_FinanceHistory_Tb "    
+ "(DEALERCODE, "   
 + "REQUESTSEQUECE, "   
  + "HANDLETIME, "    
  + "JOBFLAG, "    
  + "FRAMENO, "    
  + "INMONEY, "   
   + "REMAINMONEY, "   
   + "DELETEFLAG, "   
    + "UPDATECOUNT, "   
     + "CREUSER, "    
     + "CREDATE, "    
     + "HONORCHECKNO, "    
     + "SEQ) "    
     + "values ('" + draftInputDetail.dealerCode + "', "   
      + "'" + draftInputDetail.requestsequece + "', "    
      + "sysdate, "    + "'07', "    + "'" + frameNO + "', "    
      + requestMoney + ", "    + remainMoney + ", "    + "'0', "    + "0, "   
       + "'" + draftStruct.employeeCode + "', "    + "sysdate, "    
       + "'" + draftInputDetail.honorCheckNo + "', "    + index + ")";

1).缩进
对于存储过程文件，缩进为8个空格
对于Java source里的SQL字符串，不可有缩进，即每一行字符串不可以空格开头

2).换行
1>.Select/From/Where/Order by/Group by等子句必须另其一行写
2>.Select子句内容如果只有一项，与Select同行写
3>.Select子句内容如果多于一项，每一项单独占一行，在对应Select的基础上向右缩进8个空格（Java source无缩进）
4>.From子句内容如果只有一项，与From同行写
5>.From子句内容如果多于一项，每一项单独占一行，在对应From的基础上向右缩进8个空格（Java source无缩进）
6>.Where子句的条件如果有多项，每一个条件占一行，以AND开头，且无缩进
7>.(Update)Set子句内容每一项单独占一行，无缩进
8>.Insert子句内容每个表字段单独占一行，无缩进；values每一项单独占一行，无缩进
9>.SQL文中间不允许出现空行
10>.Java source里单引号必须跟所属的SQL子句处在同一行，连接符（"+"）必须在行首

3).空格
1>.SQL内算数运算符、逻辑运算符连接的两个元素之间必须用空格分隔
2>.逗号之后必须接一个空格
3>.关键字、保留字和左括号之间必须有一个空格

2.不等于统一使用"<>"
Oracle认为"!="和"<>"是等价的，都代表不等于的意义。为了统一，不等于一律使用"<>"表示

3.使用表的别名
数据库查询，必须使用表的别名

4.SQL文对表字段扩展的兼容性
在Java source里使用Select *时，严禁通过getString(1)的形式得到查询结果，必须使用getString("字段名")的形式
使用Insert时，必须指定插入的字段名，严禁不指定字段名直接插入values

5.减少子查询的使用
子查询除了可读性差之外，还在一定程度上影响了SQL运行效率
请尽量减少使用子查询的使用，用其他效率更高、可读性更好的方式替代

6.适当添加索引以提高查询效率
适当添加索引可以大幅度的提高检索速度
请参看ORACLE SQL性能优化系列

7.对数据库表操作的特殊要求
本项目对数据库表的操作还有以下特殊要求：

1).以逻辑删除替代物理删除
注意：现在数据库表中数据没有物理删除，只有逻辑删除
以deleteflag字段作为删除标志，deleteflag='1'代表此记录被逻辑删除，因此在查询数据时必须考虑deleteflag的因素
deleteflag的标准查询条件：NVL(deleteflag, '0') <> '1'

2).增加记录状态字段
数据库中的每张表基本都有以下字段：DELETEFLAG、UPDATECOUNT、CREDATE、CREUSER、UPDATETIME、UPDATEUSER
要注意在对标进行操作时必须考虑以下字段

插入一条记录时要置DELETEFLAG='0', UPDATECOUNT=0, CREDATE=sysdate, CREUSER=登录User
查询一条记录时要考虑DELETEFLAG，如果有可能对此记录作更新时还要取得UPDATECOUNT作同步检查
修改一条记录时要置UPDATETIME=sysdate, UPDATEUSER=登录User, UPDATECOUNT=(UPDATECOUNT+1) mod 1000,
删除一条记录时要置DELETEFLAG='1'

3).历史表
数据库里部分表还存在相应的历史表，比如srv_twc_f和srv_twchistory_f
在查询数据时除了检索所在表之外，还必须检索相应的历史表，对二者的结果做Union（或Union All）

8.用执行计划分析SQL性能
EXPLAIN PLAN是一个很好的分析SQL语句的工具，它可以在不执行SQL的情况下分析语句
通过分析，我们就可以知道ORACLE是怎样连接表，使用什么方式扫描表（索引扫描或全表扫描），以及使用到的索引名称

按照从里到外，从上到下的次序解读分析的结果
EXPLAIN PLAN的分析结果是用缩进的格式排列的，最内部的操作将最先被解读，如果两个操作处于同一层中，带有最小操作号的将首先被执行

目前许多第三方的工具如PLSQL Developer和TOAD等都提供了极其方便的EXPLAIN PLAN工具
PG需要将自己添加的查询SQL文记入log，然后在EXPLAIN PLAN中进行分析，尽量减少全表扫描

ORACLE SQL性能优化系列

1.选择最有效率的表名顺序(只在基于规则的优化器中有效)
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表（基础表driving table）将被最先处理
在FROM子句中包含多个表的情况下，必须选择记录条数最少的表作为基础表
当ORACLE处理多个表时，会运用排序及合并的方式连接它们
首先，扫描第一个表（FROM子句中最后的那个表)并对记录进行排序；
然后扫描第二个表（FROM子句中最后第二个表)；
最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并
例如:
表 TAB1 16,384 条记录
表 TAB2 5 条记录
选择TAB2作为基础表 (最好的方法)
select count(*) from tab1,tab2 执行时间0.96秒
选择TAB2作为基础表 (不佳的方法)
select count(*) from tab2,tab1 执行时间26.09秒

如果有3个以上的表连接查询，那就需要选择交叉表（intersection table）作为基础表，交