oracle要学好多东西,有用的oracle的SQL语句，可以学到很多东西

一条有用的oracle的SQL语句，可以学到很多东西

1.第一条SQL语句：

String sql = "INSERT INTO NEWS(ID,TITLE,CONTENT,AUTHOR,PUB_DATE) VALUES(IDINCREACE.NEXTVAL, '"+title+"','"+content+"','"+author+"',TO_DATE("+date+",'yyyymmdd'))";

Oracle和mysql不一样，自增长字段(ID)为必填字段，否则会报异常(不能为空)；在数据库中设置好ID的自增长，自增长的名字为INCREACE ，然后还需要在sql语句中设置自增长：IDINCREACE.NEXTVAL；

IDINCREACE 其实是个序列名，需要设置一个名为IDINCREACE的序列！！！

然后关于日期的格式定义：TO_DATE("+date+",'yyyymmdd'))"; 首先date要是格式化好的字符串，然后插入数据库中还是要有一次格式化！

2. 一些在java文件中写的sql语句：

String sql = "SELECT ID,TITLE,CONTENT,AUTHOR,PUB_DATE FROM NEWS";

String sql = "DELETE FROM NEWS WHERE ID = TO_NUMBER("+s+")";

String sql = "SELECT EMPNO,ENAME,JOB,SAL,HIREDATE FROM UP_DEMO_EMP " +

"WHERE ENAME LIKE '%"+ename+"%' AND JOB LIKE '%"+job+"%' ";

String sql = "INSERT INTO NEWS(ID,TITLE,CONTENT,AUTHOR,PUB_DATE) VALUES(IDINCREACE.NEXTVAL, '"+title+"','"+content+"','"+author+"',TO_DATE("+date+",'yyyymmdd'))";

String sql = "SELECT TITLE,CONTENT,AUTHOR,PUB_DATE FROM NEWS WHERE TITLE = '"+title+"'";

String sql = "UPDATE NEWS SET TITLE=' "+title+"' , CONTENT='"+content+"', AUTHOR='"+author+"' ,PUB_DATE=TO_DATE("+date+",'yyyymmdd') WHERE ID = TO_NUMBER("+id+")";

java代码中的sql串，加入字符串时都是先打单引号，跳到中间加双引号，然后再跳到中间加上++，最后才在中间写入变量值。

3.java中写的sql语句的单引号后面不要随便加空格，如以下语句：

sql = "UPDATE NEWS SET TITLE='"+title+"' , CONTENT='"+content+"', AUTHOR='"+author+"' ,PUB_DATE=' "+date+" ' WHERE ID = TO_NUMBER("+id+") ";

TITLE='"+title+"' ： TITLE=' "+title+"' 这两种写法最后写入到数据库中的内容就不一样，后者前面会多个空格！

4.我实现的分页

String sql = "SELECT  ID,TITLE,CONTENT,AUTHOR,PUB_DATE" +

" FROM (SELECT ID,TITLE,CONTENT,AUTHOR,PUB_DATE,ROWNUM RN FROM NEWS)" +

" WHERE RN>="+begin+" AND RN<="+end+"";

RN：是伪列。 翻页就是多次查询不同的伪列段，以至于实现分页！

5.分页中的倒序排序

SELECT  ID,TITLE,CONTENT,AUTHOR,PUB_DATE FROM (SELECT ID,TITLE,CONTENT,AUTHOR,PUB_DATE,ROWNUM RN FROM NEWS ORDER BY ID DESC) WHERE RN>="+begin+" AND RN<="+end+";

上面这条语句看起来可以，不过还是实现的先分页后排序，所以还是要使用三层嵌套，在最内层的括号中排序，然后再分页，sql语句如下：

SELECT ID,TITLE,CONTENT,AUTHOR,PUB_DATE FROM (SELECT ID,TITLE,CONTENT,AUTHOR,PUB_DATE,ROWNUM RN FROM(SELECT ID,TITLE,CONTENT,AUTHOR,PUB_DATE FROM NEWS ORDER BY ID DESC )) WHERE RN>=1 AND RN<=20;

6.多字段查询的简易方法(非nvl)

String sql = "SELECT ID,TITLE,CONTENT,AUTHOR,PUB_DATE FROM NEWS WHERE 1=1 ";

if (title != null && title != "") {

sql = sql+"AND TITLE LIKE '%"+ title+"%'";

}

if (author != null && author != "") {

sql = sql + "AND AUTHOR LIKE '%" + author + "%'";

}

if (date != null && date != "") {

sql = sql + "AND PUB_DATE LIKE '%" + date + "%'";

}

System.out.println("打印SQL语句："+sql);

7.列的复制(转化日期)

如：一列(varchar)的格式都是XXXX年XX月XX日变为date型，sql语句为：

UPDATE NEWS N SET N.PUB_DATE_1=TO_DATE(N.PUB_DATE,'YYYY"年"mm"月"dd"日"');

8.分页后关联表(注意表的别名的问题)

SELECT ID,TITLE,CONTENT,AUTHOR,PUB_DATE,CNAME

FROM (SELECT ID,CATALOG_NO,TITLE,CONTENT,AUTHOR,PUB_DATE,ROWNUM RN FROM (SELECT ID,CATALOG_NO,TITLE,CONTENT,AUTHOR,

PUB_DATE FROM NEWS ORDER BY ID DESC)) N,NEWS_CATALOG NC WHERE RN>=1 AND RN<=20 AND NC.CATALOG_NO = N.CATALOG_NO;

相关代码：

List list= new ArrayList();

int begin = pageSize*(pageNo-1)+1;

int end = pageSize*pageNo;

DBPersistenceManager dbpm = DBAccessHelper.getPMByName("BUS");

DataSet dset = null;

String sql = "SELECT ID,TITLE,CONTENT,AUTHOR,PUB_DATE,CNAME,N.CATALOG_NO" +

" FROM (SELECT ID,CATALOG_NO,TITLE,CONTENT,AUTHOR,PUB_DATE,ROWNUM RN FROM (SELECT ID,CATALOG_NO,TITLE,CONTENT,AUTHOR," +

"PUB_DATE FROM NEWS ORDER BY ID DESC)) N,NEWS_CATALOG NC WHERE RN>=? AND RN<=? AND NC.CATALOG_NO = N.CATALOG_NO";

try {

dset = (DataSet) dbpm.executeQuery(sql,new Object[]{begin,end});

System.out.println("要执行的sql语句是："+sql);

while (dset != null && dset.next()) {

Map m = new HashMap();

m.put("ID",dset.getString("ID"));

m.put("TITLE",dset.getString("TITLE"));

m.put("CONTENT",dset.getString("CONTENT"));

m.put("AUTHOR",dset.getString("AUTHOR"));

m.put("DATE",dset.getString("PUB_DATE"));

//不要写成m.put("CNO",dset.getString("N.CATALOG_NO"));

m.put("CNO",dset.getString("CATALOG_NO"));

m.put("CNAME",dset.getString("CNAME"));

list.add(m);

}

Map m1 = new HashMap();

m1.put("COUNT",count());

list.add(m1);

} catch (Exception e) {

//throwException(e);

} finally {

if (dbpm != null && !dbpm.isClosed()) {

dbpm.close();

}

}

return list;

}

9. 开窗函数，分析函数 做累加

sum(col1) over(partition by col2 order by col3 )

网上看到过 over可以理解为：基于。。。。。做。。。。。。

partition  ：分组，分类

order ：排序，对over前边的函数以什么样的顺序进行。并非把结果进行排序，即与where 后的order by 不是一个概念。

所以，以上的函数可以理解为：按col2 进行分组(partition )，每组以col3 进行排序(order)，并进行连续加总(sum)

在我的emp表中对部门号为20的员工的工资进行累加输出：

select e.*, sum(sal) over (partition by e.deptno order by e.hiredate ) from emp e where e.deptno =20;

10.简单的闪回

alter table emp enable row movement;

flashback table EMP to timestamp(to_date('2011-9-3 18:12:00','yyyy-mm-dd hh24:mi:ss'))

11.字段中是null的赋值为0

update value set value3=nvl(value3,0);