没有高并发，亿万级数据优化的sql脚本二

大家好，今天咱们进行基础篇第二章节。上一章节讲到oracle的删除，现在再来看看db2的删除。db2的删除和oracle的删除语法不同的地方也有，看下面例子：

delete from mytable where (name, age) = ( '张三', 18 )

上面的删除语句表示同时把符合条件 name='张三'，age=18的记录删除。 db2可以进行多个字段组合成一个条件进行删除。这种语法是不适用oracle的。该语句等价于下面的删除语句。

delete from mytable where name= '张三' and age = 18

笔者认为，一些相对偏“冷门”的sql写法不需要执意的去学习或者书写。只要在工作当中能够看懂并知道它的含义即可。有通用的写法还是优先使用通用的写法吧。因为使用非通用的写法在一些工作场景中如果同时涉及到多个数据库的操作的话就会容易书写出问题来。

说到删除，不得不提的另一个关键字 truncate。 truncate 和delete 一样，表面上都是删除，但是truncate 只能做全表删除，不能进行带条件的删除。所以

truncate table mytable1 where age >100

这条删除语句是错的。当然，这2个删除关键字还有一些其他方面包括执行机制的区别。这些知识点都会在后续章节一一提到

3：改(update)语句

        update mytable a
                set a.name = (select b.name from mytable1 b where a.age=b.age)
        where exists (select 1 from mytable1 b where a.age=b.age);

在实际dml语句操作中,update应该是除了select使用频率最高的sql语句了吧。咱们来看看上面这条语句，这条语句对不对呢，可对可不对，对不对就要看你的造化了。为何这么说呢，结合业务场景来看，上面这条语句肯定是不符合业务场景的，不可能根据一个人的年龄去修改一个人的名字，倒有可能会根据一个人的名字去修改一个人的年龄。那么上面那条sql出错的概率就会大大减少，但是一旦遇到有同名同姓的人，就又会出错了。笔者曾在工作当中因为业务分析不全面，就发生过一次类似的事故，印象很深。

上面这条update语句语法上是完全没问题的。但是为什么会报错呢。我们来分析下这条语句，where子条件表示mytable表中的age值必须也要同时存在于表mytable1中。过滤出这些mytable表的记录后再去修改这些记录的名字。修改的逻辑是通过两个表的age值相等去关联，把查到mytable1表的name值赋给mytable表的name。这里可能就会出现这样一种情况，假设mytable表中的age值等于18的记录有一条，而mytable1表中age值等于18的有两条，此时匹配到mytable1表的name值就有2个了。这时oracle就不知道应该把哪个name值赋给mytable表了。所以oracle只能给你一个错误提示【ora01427 单行子查询返回多个行】。这个错误在很多业务场景中经常出现，所以在处理类似需求时候，一定要考虑好子查询的是否唯一性。

再来看看这条update语句。

update mytable set (name, age) = (select 'zhang', 11 from dual ) ;

这条语句是不是和该篇第一条的delete语句很像，不同的地方就是把等号左边的表达式改成查询语句。这种写法oracel是支持的，可以在oracle里成功运行。该章节第一条的delete 语句等号左边改成一条子查询语句，在oracle环境中同样可以运行。

以上两条sql的语法操作同样适用于db2数据库，注意db2的虚拟表是sysibm.dual

4：查(select)语句

查询语句是dml语句使用最频繁的关键字了，也是优化场景的主场。有时候同一个需求通过不同的sql实现，其效率可以相差很多。select 要学习和注意的地方算是最多的。我们来看看几条select语句。

select name, count(1) from mytable where age = 23 group by name having count(1) > 1 order by name desc;

select * from mytable1 t1 inner join mytable t2 on t1.id = t2.id and t2.age = 23 where t1.age = 24;

select t1.* from mytable1 t1 where t1.created_time between trunc(sysdate - 7) and sysdate and rownum <= 500;

这三条sql虽不复杂但在实际工作中却会经常遇到。

第一条是个带where条件的分组统计查询语句，它根据name进行字典排序。还记得前一章节提到过group by语句的易错点吧。部分人可能会写成select name, count(1) from mytable group by name having count(1) > 1 where age = 23 order by name desc; 总之，where, group by, having 三个关键词的位置不能正确摆放。第二条是带where条件的双表内联查询。第三条是查询当前时间起历史7天的数据。

说说db2的查询吧，oracle的查询大多适用于db2的查询。区别主要是体现在一些系统函数及内置处理上，例如:

select age || '123' from mytable ;

如果age的值为空，db2返回结果就直接是null。但是 oracle 返回的结果就不会是null.

select current timestamp from sysibm.dual

上面这条查询语句是查询当前日期时间。等价于oracle的如下sql：

select sysdate from dual ;

有关系统函数的介绍和使用，笔者计划在后续章节当独讲解。

增删改查已经经过两轮介绍了。各位应该都熟悉了其基本的语法了吧。这些都是些很基础的脚本知识，是必须要掌握的。

从上面这些语句当中是否能够隐约刻画出表的轮廓来呢！接下来，咱们聊聊表的创建吧。

在实际工作当中，读者们是否都注意到了一个表中有这么几个字段一般情况都是必不可少的：id，create_time, update_time。在线上运行的表特别是业务数据表中基本上都会含有这3个字段，这可是很多厂家建表规范之一。否则，dba审核不会给你通过。为什么要加上这三个字段呢。

id主键，大家肯定都知道其重要程度，作用之一就是唯一化一条表记录。一般都会通过sequence生成。

create_time，创建时间。加上这个字段，就可以明确知道数据的创建时间，我们可以根据它来按时间查询，修改，删除。包括dba进行数据归档操作时候，创建时间对他们的作用也很大。当表数据量很大的时候，通过它来进行表分区，也是个很好的解决方案。比如老板要求把最近30天生成的订单查出来，就可以通过create_time轻松的完成任务。
update_time，修改时间。每次修改了数据，都变更下 update_time。好处也特别的多。例如最新业务数据变更查询，日志查询，这些作用都是巨大的。建表规则咱们聊完之后，现在来创建张表吧。

第一步，先创建表序列，用于表的主键。

        create sequence seq_mytable
            minvalue 1
            maxvalue 999999999999
            start with 1
            increment by 1
            cache 1000
            noorder;

运行完上面的sql脚本后，一个新的序列就创建好了。序列的名字是seq_mytable，最小值是1，最大值是999999999999，最小值和最大值作用是限制序列值的生成范围。序列值从1开始生成，步长是1，也就是每次递增1。缓存1000个数据到内存中，尽量使用noorder，虽不保证一定排序生成，但是可提高效率。用上它后，id的值就都唯一了。序列有两个常用属性，一个是currval，表示查询序列的当前值，一个是nextval，表示查询序列的下一个值。sql语句如下：

select seq_mytable.currval from dual ;
select seq_mytable.nextval from dual ;

新建的序列，先执行nextval 然后再执行currval 哈，保证序列的值在会话中被定义好了。

第二步，建表。

create table mytable2(id number(12), name varchar(256), age number(3), create_time date, update_time date);

这样，一张新表就创建完成了。顺便提下，建表语句术语是DDL语句，表示定义结构语句。select,update, delete ,insert 是DML‌语句，表示操作数据的语句。

当业务需求扩增，需要往表里增加性别字段时。语句如下：

alter table mytable2 add gender number(2); 注意现在的很多版本不能在add后面加上column关键字，否则会报错。

删除字段语句如下:

alter table mytable2 drop column gender ;

修改字段长度语句如下：

alter table mytable2 modify(gender number(10));

删除表语句: drop table mytable2;

为了优化表的使用效率，需要添加表的索引，语句如下：

alter table mytable2 add constraint mytable2_pk primary key (id);

create index mytable2_index1 on mytable2 (name);

这样一张比较“健壮”的表就创建好了。完整的建表语句如下：

        -- create table
        create table mytable2
        (
          id          number(12) not null,
          name        varchar2(256),
          age         number(3),
          create_time date,
          update_time date
        );
        -- add comments to the columns
        comment on column mytable2.id
          is '主键id';
        comment on column mytable2.name
          is '姓名';
        comment on column mytable2.age
          is '年龄';
        comment on column mytable2.create_time
          is '创建时间';
        comment on column mytable2.update_time
          is '更新时间';
        -- create/recreate primary, unique and foreign key constraints
        alter table mytable2 add constraint mytable2_pk primary key (id);
        -- create/recreate indexes
        create index mytable2_index1 on mytable2 (name);

接下来看看db2的建表sql吧。创建序列脚本和oracle是一样的，其中db2中maxvalue的最大值有限制。最大不能超过 ‌999,999,999‌（9个9）。查询序列的当前值和下一个值语法也都一样，唯一的区别就是oracle和db2的系统虚表不同，db2的系统虚表有sysibm.dual和sysibm.sysdummy1，笔者喜欢用sysibm.dual，因为和oracle接近。其它DDL表结构语句也都差不多。建表语句也差别不大，只是其中有些字段类型和关键字的差异。一条完整的建表语句如下：

        CREATE TABLE MYTABLE3 (
            ID            INTEGER NOT NULL,
            NAMEM         VARCHAR(256),
            AGES          INTEGER,
            CREATE_TIME   DATE,
            UPDATE_TIME   DATE,
            CONSTRAINT MYTABLE3_PK PRIMARY KEY(ID)
        )
        GO
        COMMENT ON COLUMN MYTABLE3.ID IS '主键id'
        GO
        COMMENT ON COLUMN MYTABLE3.NAMEM IS '姓名'
        GO
        COMMENT ON COLUMN MYTABLE3.AGES IS '年龄'
        GO
        COMMENT ON COLUMN MYTABLE3.CREATE_TIME IS '创建时间'
        GO
        COMMENT ON COLUMN MYTABLE3.UPDATE_TIME IS '更新时间'
        GO
        CREATE INDEX MYTABLE3_INDEX1
            ON MYTABLE3(NAMEM ASC)
        GO