各种数据库的分页查询

DB2
SELECT * FROM (Select 字段1,字段2,字段3, rownumber() over (ORDER BY 排序用的列名 ASC) AS rn from 表名) AS a1 WHERE a1.rn BETWEEN 10 AND 20

Oracle
SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) A WHERE ROWNUM <= 40) WHERE RN >= 21

SQL Server
select top @pagesize * from table1 where id not in (select top @pagesize*(@page-1) id from table1 order by id) order by id

MySQL
select * from table LIMIT 5,10

SQL之DROP COLUMN的workaround

如果数据库(db2 v8, db2z, db2i)不提供drop column(有的特殊字段也不能drop的，如xml字段)，怎么办呢？

原来的表是t1
1. 创建表t1', 它不含有应该drop的column
2. insert into t1' select x,x,x from t1
3. drop table t1
4. rename t1' to t1

DB2之RENAME

用来改table, view和index的名字

例如
RENAME TABLE test.table1 TO newname

注意新名字里面不要带schema

DB2之SQL优化

这个题目太大，先开个头, 后面再补充

1. 用索引(废话), 建了索引还要经常RUNSTATS

2. exists比in快

3. 少用select *, 不但慢还不好维护

4.

SQL之BETWEEN...AND

只有一点要说，BETWEEN …AND…搜索的是闭区间

SQL之集合操作

和集：UNION
保留重复行的和集：UNION ALL
交集：INTERSECT
集合相减：EXCEPT

后面两个用法似乎我还没咋用过啊

Create Table中不熟悉的用法: LIKE, GENERATED

LIKE: 按已有表结构生成另一种表
CREATE TABLE MYBOOKS LIKE BOOKS

GENERATED: 字段是自动生成的，最普通的情况是自增
CREATE TABLE BOOKS ( BOOKID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
FICTIONBOOKS INTEGER,
NONFICTIONBOOKS INTEGER,
TOTALBOOKS INTEGER
GENERATED ALWAYS AS (FICTIONBOOKS + NONFICTIONBOOKS)
)

SQL之data-change-table-reference

这也是偶还没用过的SQL，还是用例子说话:

SELECT salary FROM OLD TABLE (UPDATE employee SET salary = salary * 1.07 WHERE empno = '000220'); 这条语句取出update之前的数据

SELECT * FROM FINAL TABLE (INSERT INTO customers (cust_name) VALUES ('Lamarr')); 这条取插入后的数据
注意，这个方法非常有用，可以取出自增的字段

SQL之MERGE

之前一直没用过这个语句，学习一下：
MERGE INTO employee AS e #要merge到这个表里
USING (SELECTempno, firstnme, midinit, lastname, workdept, phoneno,hiredate, job, edlevel, sex, birthdate, salaryFROM my_emp) AS m #merge的内容,也可以是表和视图
ON e.empno = m.empno #merge的条件
WHEN MATCHED THEN UPDATE SET (salary) = (m.salary) #有冲突就更新，可以删除
WHEN NOT MATCHED THEN INSERT (empno, firstnme, midinit, lastname, workdept, phoneno,hiredate, job, edlevel, sex, birthdate, salary)VALUES (m.empno, m.firstnme, m.midinit, m.lastname,m.workdept, m.phoneno, m.hiredate,m.sex, m.birthdate, m.salary) #没冲突就插入

详细的说明在这：http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.admin.doc/doc/r0010873.htm?resultof=%22%4d%45%52%47%45%22%20%22%6d%65%72%67%22%20