----------------------------------
2 在查询中避免特殊字符,比如_,%
---------------------------------
SELECT name FROM pepole WHERE id LIKE '%\_%' ESCAPE '\';
-------------------
3、去除表中重复行
------------------
(1) DELETE FROM table_name A WHERE ROWID > (
SELECT min(rowid) FROM table_name B
WHERE A.key_values = B.key_values);
(2)create table table2 as select distinct * from table1;
drop table1;
rename table2 to table1;
(3) Delete from mytable where rowid not in(
select max(rowid) from mytable
group by column_name );
(4)delete from mytable t1
where exists (select 'x' from my_table t2
where t2.key_value1 = t1.key_value1
and t2.key_value2 = t1.key_value2
...
and t2.rowid > t1.rowid);
2 在查询中避免特殊字符,比如_,%
---------------------------------
SELECT name FROM pepole WHERE id LIKE '%\_%' ESCAPE '\';
-------------------
3、去除表中重复行
------------------
(1) DELETE FROM table_name A WHERE ROWID > (
SELECT min(rowid) FROM table_name B
WHERE A.key_values = B.key_values);
(2)create table table2 as select distinct * from table1;
drop table1;
rename table2 to table1;
(3) Delete from mytable where rowid not in(
select max(rowid) from mytable
group by column_name );
(4)delete from mytable t1
where exists (select 'x' from my_table t2
where t2.key_value1 = t1.key_value1
and t2.key_value2 = t1.key_value2
...
and t2.rowid > t1.rowid);
-------------
(1)DDL is Data Definition Language statements. Some examples: 我怎么只能看到上面三行.
4、得到一个表所有的索引信息
---------------
select * from ml_tindex('mytable');
-----------------------
5、generate primary key values for a table
-----------------------
CREATE SEQUENCE sequence_name START WITH 1 INCREMENT BY 1;
UPDATE table_name SET seqno = sequence_name.NEXTVAL;****************************************************
大家多多补充呀!
是什么用法?
6、add a day/hour/minute/second to a date value
---------------------------
select sysdate, sysdate+1/24, sysdate +1/1440, sysdate + 1/86400 from dual;
----------------------------
7、对一列的不同值作统计
---------------------------
select jgbh 机构编号, sum( decode(sex,'男',1,0)) 男,
sum(decode(sex,'女',1,0)) 女
from t_yg
group by 机构编号;---------------------------------------------------
8、对一列的值作范围内的统计
----------------------------
select jgbh,
sum(decode(greatest(age,59), least(age,100), 1, 0)) "年龄60-100",
sum(decode(greatest(age,30), least(age, 59), 1, 0)) "年龄30-59",
sum(decode(greatest(age, 0), least(age, 29), 1, 0)) "年龄0-29"
from pepole
group by jgbh;
没错吗?
如何用!?
select * from ml_tindex('mytable');
我这里只有db2没有oracle
答:
包头:
create or replace package getstu is
type MY_refcur is ref cursor; --定义引用型游标
--定义返回值为引用型游标
function doget(stuname varchar2) return my_REFCUR;
end;
/
包体:
create or replace package body getstu as
function doget(stuname varchar2) return my_REFCUR
is
var_cur my_refcur;
begin
open var_cur for select xh,xm from stu where xm like '%'||stuname||'%';
return var_cur;
end;
end;
----------------------------
现学现卖,今天才弄明白的!:)