insert

论坛上看到关于insert的帖子不错，整理一下，分享一下

点击(此处)折叠或打开

    一、Insert基础用法
    语法：
        Insert Into 表名 (字段1,字段2,字段3...）
        Values (值1,值2,值3...）
    例子：
    INSERT INTO departments(department_id, department_name,
    manager_id, location_id)
    VALUES (70, 'Public Relations', 100, 1700);
    语法：
        Insert Into 表名 (字段1,字段2,字段3...）
        select 语句
    不做任何解释，实在是没啥好说的?_?，注意别跟create table ...as select一样，insert中的select前面可没as ^_^
    二、Unconditional INSERT ALL 用法
    直接拿例子了：
    INSERT ALL
       INTO sal_history VALUES(EMPID,HIREDATE,SAL)
       INTO mgr_history VALUES(EMPID,MGR,SAL)
       SELECT employee_id EMPID, hire_date HIREDATE,
                      salary SAL, manager_id MGR
       FROM employees
       WHERE employee_id > 200;
    解释：将select查询出来的结果，每返回一行就分别插入表sal_history 和mgr_history 中，优点就是只做一次查询即可分别查询2个表，假如使用基础用法，将进行2次查询。
    三、Conditional INSERT ALL
    还是直接拿例子：
    INSERT ALL
        WHEN SAL > 10000 THEN
            INTO sal_history VALUES(EMPID,HIREDATE,SAL)
       WHEN MGR > 200 THEN
           INTO mgr_history VALUES(EMPID,MGR,SAL)
       SELECT employee_id EMPID,hire_date HIREDATE,
                      salary SAL, manager_id MGR
       FROM employees
       WHERE employee_id > 200;
    解释：将select查询出来的结果，每返回一行就判断，SAL > 10000 就插入表sal_history ，MGR > 200就插入mgr_history ，优点和前面提到一样。
    四、Conditional FIRST INSERT
    仍然是例子：
    INSERT FIRST
       WHEN SAL > 25000 THEN
           INTO special_sal VALUES(DEPTID, SAL)
       WHEN HIREDATE like ('%') THEN
          INTO hiredate_history_00 VALUES(DEPTID,HIREDATE)
       WHEN HIREDATE like ('？%') THEN
          INTO hiredate_history_99 VALUES(DEPTID, HIREDATE)
       ELSE
          INTO hiredate_history VALUES(DEPTID, HIREDATE)
       SELECT department_id DEPTID, SUM(salary) SAL,
                      MAX(hire_date) HIREDATE
       FROM employees
       GROUP BY department_id;
    解释：将select查询出来的结果，每返回一行就判断，SAL > 25000就插入表special_sal ，否则HIREDATE like ('%') ，符合就插入hiredate_history_00，前面2个条件还是不成力，就判断HIREDATE like ('?%') ，符合就插入表hiredate_history_99 ，前面3个条件都不符合，只好插入表hiredate_history 了。
    打完这我都头晕了，假如学过程序设计，看下面的清晰明了：
    if SAL > 25000 then
        INTO special_sal VALUES(DEPTID, SAL)
    else
    (
        if HIREDATE like ('%') THEN
             INTO hiredate_history_00 VALUES(DEPTID,HIREDATE)
        else
            (
                if HIREDATE like ('？%') THEN
                    INTO hiredate_history_99 VALUES(DEPTID, HIREDATE)
                else
                    INTO hiredate_history VALUES(DEPTID, HIREDATE)
            )
    )
    假如还看不理解，看官方的这句话吧，“If the first WHEN clause evaluates to true, the subsequent WHEN clauses for this row should be skipped.”
    五、Pivoting INSERT
    最后还是例子：
    INSERT ALL
       INTO sales_info VALUES (employee_id,week_id,sales_MON)
       INTO sales_info VALUES (employee_id,week_id,sales_TUE)
       INTO sales_info VALUES (employee_id,week_id,sales_WED)
       INTO sales_info VALUES (employee_id,week_id,sales_THUR)
       INTO sales_info VALUES (employee_id,week_id, sales_FRI)
    SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE,
                  sales_WED, sales_THUR,sales_FRI
    FROM sales_source_data;
    解释：老实说，看不出有啥用法，真的非要说，咱就把它当作行列转换吧，如果上面表在加多一列，比如INTO sales_info VALUES (employee_id,week_id,week_which,sales_MON) 改成 INTO sales_info VALUES (employee_id,week_id,'星期一',sales_MON)