----------------------------------
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);
解决方案 »
- 关于ORA-12518: TNS:listener could not hand off client connection求指导
- DBA应该掌握的SQL语句
- 10g 进行高级复制时出现的问题
- 高手帮忙,在线等
- Oralce数据库,如何通过一个存储过程获得表的结构
- 任何在数据字典中查找到用户自定义触发器的内容?
- 高分求解二道练习题,急急之(2)
- 给视图加自增字段----
- 为何用xml写入中文字符到Clob字段不能正确显示(在线等待)
- ORA-12154: TNS:could not resolve the connect identifier specified
- 在sqlplus+中,怎么查看某个表的主键设置?
- 这样的SQL语句能在ORACLE中实现吗?在线等待!
-------------
(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;
----------------------------
现学现卖,今天才弄明白的!:)