有用的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<Map> list= new ArrayList<Map>();
		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<String, String> m = new HashMap<String, String>();
				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<String, Object> m1 = new HashMap<String, Object>();
			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);