- 浏览: 309553 次
- 性别:
- 来自: 南昌
文章分类
最新评论
-
秋风扫落叶:
不错,学习了。shadow文件中记录的格式是什么?
/etc/passwd & /etc/shadow 详解 -
key232323:
重拾jstl——写得很好。我收藏下。
JSTL标签用法 -
zxl10059:
不错,非常简明扼要
Hibernate分页 -
Batmankwok:
很好,所见略同,
自定义组件MXML和as两种实现的方式和区别 -
Dxx23:
摘话回帖!
通过response对象的sendRedirec ...
response.sendRedirect方式的转向与RequestDispatcher的forward方法的比较
二十四、用其他表中的值更新
db2/mysql
update emp e set(e.sal,e.comm)=(select ns.sal,ns.sal/2 from new_sal ns where ns.deptno=e.deptno) where exists(select nul from new_sa ns where ns.deptno=e.deptno)
oracle
update(select e.sal as emp_sal,e.comm as emp_comm,ns.sal as ns_sal,ns.sal/2 as ns_comm frm emp e,new_sal ns where e.deptno=ns.deptno) set emp_sal=ns_sal,emp_comm=ns_comm
postgresql
update emp set sal=ns.sal,comm=ns.sal/2 from new_sal ns where ns.deptno=emp.deptno
sqlserver
update e set e.sal=ns.sal,e.comm=ns.sal/2 from emp e,new_sal ns where ns.deptno=emp.deptno
二十五、合并记录
oracle
merge into emp_commission ec using(select * from emp) emp on(ec.empno=emp.empno) when matched then update set ec.comm=1000 delete where (sal<2000) when not matched then insert (ec.empno,ec.ename,ec.deptno,ec.comm) values (emp.empno,emp.ename,emp.deptno,emp.comm)
二十六、删除违反参照完整性的记录
delete from emp where not exists(select * from dept where dept.deptno=emp.deptno)
delete from emp where deptno not in(select deptno from dept)
二十七、删除重复记录
delete from dupes where id not in (select min(id) from dupes group by name(需要判断重复的字段))
二十八、删除从其他表引用的记录
delete from emp where deptno in (select deptno from dept_accidents group by deptno having count(*)>=3)
(以下模式名schema为smeagol)
二十九、列出模式中的表
db2
select tabname from syscat.table where tabschema='smeagol'
oracle
select table_name from all_tables where owner='smeagol'
postgresql/mysql/sqlserver
select tablename from information_schema.tables where table_schema='smeagol'
三十、列出表的列
db2
select colname,typename,colno from syscat.columns where tablename='emp' and tabschema='smeagol'
oracle
select column_name,data_type,column_id from all_tab_columns where owner='smeagol' and table_name='emp'
postgresql/mysql/sqlserver
select column_name,data_type,ordinal_position from information_schema='smeagol' and table_name='emp'
三十一、列出表的索引列
db2
select a.tabname,b.indname,b.colname,b.colseq from syscat.indexes a,syscat.indexcoluse b where a.tabname='emp' and a.tabschema='smeagol' and a.indschema=b.indschema and a.indname=b.indname
oracle
select table_name,index_name,column_name,column_position from sys.all_ind_columns where table_name='emp' and table_owner='smeagol'
postgresql
select a.tablename,a.indexname,b.column_name from pg_catalog.pg_indexes a,information_schema.columns b where a.schemaname='smeagol' and a.tablename=b.table_name
mysql
show index from emp
sqlserver
select a.name table_name,b.name index_name,d.name column_name,c.index_column_id from sys.tables a,sys.indexes b,sys.index_columns c,sys.columns d where a.object_id=b.object_id and b.object_id=c.object_id and b.index_id=c.index_id and c.object_id=d.object_id and c.column_id=d.column_id and a.name='emp'
三十二、列出表约束
db2
select a.tabname,a.constname,b.colname,a.type from syscat.tabconst a,syscat.columns b where a.tabname='emp' and a.tabschema='smeagol' and a.tabname=b.tabname and a.tabschema=b.tabschema
oracle
select a.table_name,a.constraint_name,b.column_name,a.constraint_type from all_constraints a,all_cons_columns b where a.table_name='emp' and a.owner='smeagol' and a.table_name=b.table_name and a.owner=b.owner and a.constraint_name=b.constraint_name
postgresql/mysql/sqlserver
select a.table_name,a.constraint_name,b.column_name,a.constraint_type from information_schema.table_constraints a,information_schema.key_column_usage b where a.table_name='emp' and a.table_schema='smeagol' and a.table_name=b.table_name and a.table_schema=b.table_schema and a.constraint_name=b.constraint_name
三十三、列出没有相应索引的外键
db2
select fkeys.tabname,fkeys.constname,fkeys.colname,ind_cols.indname from (select a.tabschema,a.tabname,a.constname,b.colname from syscat.tabconst a,syscat.keycoluse b where a.tabname='emp' and a.tabschema='smeagol' and a.type='f' and a.tabname=b.tabname and a.tabschema=b.tabschema) fkeys left join (select a.tabschema,a.tabname,a.indname,b.colname from syscat.indexes a,syscat.indexcoluse b where a.indschema=b.indschema and a.indname=b.indname) ind_cols on(fkeys.tabschema=ind_cols.tabschema and fkeys.tabname=ind_cols.tabname and fkeys.colname=ind_cols.colname)where ind_cols.indname is null
oracle
select a.table_name,a.constraint_name,a.column_name,c.index_name from all_cons_columns a,all_constraints b,all_ind_columns c where a.table_name='emp' and a.owner='smeagol' and b.constraint_type='r' and a.owner=b.owner and a.table_name=b.table_name and a.constraint_name=b.constraint_name and a.owner=c.table_owner(+) and a.table_name=c.table_name(+) and a.column_name=c.column_name(+) and c.index_name is null
postgresql
select fkeys.table_name,fkeys.constraint_name,fkeys.column_name,ind_cols.index_name from (select a.constraint_schema,a.table_name,a.constraint_name,a.column_name from information_schema.key_column_usage a,information_schema.referential_constraints b where a.table_name='emp' and a.constraint_schema='smeagol' and a.constraint_name=b.constraint_name and a.constraint_schema=b.constraint_schema) fkeys left join (select a.schemaname,a.tablename,a.indexname,b.column_name from pg_catalog.pg_indexes a,information_schema.columns b where a.schemaname=b.table_schema and a.tablename=b.table_name) ind_cols on(fkeys.constraint_schema=ind_cols.schemaname and fkeys.table_name=ind_cols.tablename and fkeys.column_name=ind_cols.column_name)where ind_cols.indexname is null
mysql
使用show index来检索索引信息,查询information——schema.key_column_usage列出指定表的外键
sqlserver
select fkeys.table_name,fkeys.constraint_name,fkeys.column_name,ind_cols.index_name from (select a.object_id,d.column_id,a.name table_name,b.name constraint_name,d.name column_name from sys.tables a join sys.foreign_keys b on (a.name='emp' and a.object_id=b.parent_object_id) join sys.foreign_key_columns c on(b.object_id=c.constraint_object_id) join sys.columns d on (c.constraint_column_id=d.column_id and a.object_id=d.object_id) fkeys left join (select a.name index_name,b.object_id,b.column_id from sys.indexes a,sys.index_columns b where a.index_id=b.index_id) ind_cols on (fkeys.object_id=ind_cols.object_id and fkeys.column_i=ind_cols.column_id) where ind_cols.index_name is null
三十四、使用sql来生成sql
oracle
select 'select count(*) from '||table_name||';' cnts from user_tables;
三十五、在oracle中描述数据字典视图
select table_name,comments from dictionary order by table_name
select column_name,comments from dict_columns where table_name='all_tab_columns'
三十六、遍历字符串
select substr(e.ename,iter.post,1) as c from (select ename from emp where ename='king') e,(select id as pos from t10) iter where iter.pos<=length(e.ename)
三十七、计算字符在字符串中出现的次数
select (length('10,clark,manager')-length(replace('10,clark,manager',',','')))/length(',') as cnt from t1
三十八、从字符串中删除不需要的字符
db2
select ename,replace(translate(ename,'aaaaa','AEIOU'),'a','') stripped1,sal,replace(cast(sal as char(4)),'0','') stripped2 from emp
mysql/sqlserver
select ename,replace(replace(replace(replace(replace(ename,'A',''),'E',''),'I',''),'O',''),'U','') as stripped1,sal,replace(sal,0,'') stripped2 from emp
oracle/postgresql
select ename,replace(translate(ename,'AEIOU','aaaa'),'a') as stripped1,sal,replace(sal,0,'') as stripped2 from emp
三十九、将字符和数字数据分离
db2
select replace(translate(data,'0000000000','0123456789'),'0','') ename,cast(replace(translate(lower(data),repeat('z',26),'abcdefghijklmnopqrstuvwxyz'),'z','') as integer) sal from (select ename||cast(sal as char(4)) data from emp) x
oracle
select replace(translate(data,'0123456789','0000000000'),'0') ename,to_number(replace(translate(lower(data),'abcdefghijklmnopqrstuvwxyz',rpad('z',26,'z')),'z')) sal from (select ename||sal data from emp)
postgresql
select replace(translate(data,'0123456789','0000000000'),'0','') as ename,cast(replace(translate(lower(data),'abcdefghijklmnopqrstuvwxyz',rpad('z',26,'z')),'z','') as integer) as sal from (select ename||sal as data from emp) x
四十、判断字符串是不是字母数字型的
db2
select data from view where translate(lower(data),repeat('a',36),'0123456789abcdefghijklmnopqrstuvwxyz')=repeat('a',length(data))
mysql
create view V as select ename as data from emp where deptno=10 union all select concat(ename,',$',sal,'.00') as data from emp where deptno=20 union all select concat(ename,deptno) as data from emp where deptno=30
select data from V where data regexp '[^0-9a-zA-Z]'=0
oracle/postgresql
select data from V where translate(lower(data),'0123456789abcdefghijklmnopqrstuvwxyz',rpad('a',36,'a'))=rpad('a',length(data),'a')
sqlserver
select data from (select v.data,iter.pos,substring(v.data,iter.pos,1) c,ascii(substring(v.data,iter.pos,1)) val from v,(select id as pos from t100) iter where iter.pos<=len(v.data)) x group by data having min(val) between 48 and 122
四十一、提取姓名的大写首字母缩写
db2
select replace(replace(translate(replace('Stewie Griffin','.',''),repeat('#',26),'abcdefghijklmnopqrstuvwxyz'),'#',''),' ','.')||'.' from dual
mysql
select case when cnt=2 then trim(trailing '.' from concat_ws('.',substr(substring_index(name,' ',1),1,1),substr(name,length(substring_index(name,' ',1))+2,1),substr(substring_index(name,' ',-1),1,1),'.')) else trim(trailing '.' from concat_ws('.',substr(substring_index(name,' ',1),1,1),substr(substring_index(name,' ',-1),1,1))) end as initials from (select name,length(name)-length(replace(name,' ','')) as cnt from (select replace('Stewie Griffin','.','') as name from dual ) y ) x
oracle/postgresql
select replace(replace(translate(replace('Stewie Griffin','.',''),'abcdefghijklmnopqrstuvwxyz',rpad('#',26,'#')),'#',''),' ','.')||'.' from dual
四十二、按字符串中的部分内容排序
db2/oracle/mysql/postgresql
select ename from emp order by substr(ename,length(ename)-1,2)
sqlserver
select ename from emp order by substring(ename,len(ename)-1,2)
四十三、按字符串中的数值排序
db2
select data from V order by cast(replace(translate(data,repeat('#',length(data)),replace(translate(data,'##########','0123456789'),'#','')),'#','') as integer)
oracle
select data from V order by to_number(replace(translate(data,replace(translate(data,'0123456789','##########'),'#'),rpad('#',20,'#')),'#'))
postgresql
select data from V order by cast(replace(translate(data,replace(translate(data,'0123456789','##########'),'#',''),rpad('#',20,'#')),'#','') as integer)
四十四、根据表中的行创建一个分割列表
db2
with x (deptno,cnt,list,empno,len) as (select deptno,count(*) over (partition by deptno), cast(ename as varchar(100)),empno,1 from emp union all select x.deptno,x.cnt,x.list||','||e.name,e.empno,x.len+1 from emp e,x where e.deptno=x.deptno and e.empno>x.empno) select deptno,list from x where len=cnt
mysql
select deptno,group_concat(ename order by empno separator,',') as emps from emp group by deptno
oracle
select deptno,ltrim(sys_connect_by_path(ename,','),',') emps from (select deptno,ename,row_number() over (partition by deptno order by empno) rn,count(*) over (partition by deptno) cnt from emp) where level=cnt start with rn=1 connect by prior deptno=deptno and prior rn=rn-1
postgresql
select deptno,rtrim(max(case when pos=1 then emps else '' end)||max(case when pos=2 then emps else '' end)||max(case when pos=3 then emps else '' end)||max(case when pos=4 then emps else '' end)||max(case when pos=5 then emps else '' end)||max(case when pos=6 then emps else '' end),',') as emps from (select a.deptno,a.ename||',' as emps,d.cnt,(select count(*) from emp b where a.deptno=b.deptno and b.empno<=empno ) as pos from emp a,(select deptno,count(ename) as cnt from emp group by deptno) d where d.deptno=a.deptno) x group by deptno order by 1
sqlserver
with x (deptno,cnt,list,empno,len) as (select deptno,count(*) over (partition by deptno), cast(ename as varchar(100)),empno,1 from emp union all select x.deptno,x.cnt,cast(x.list+','+e.name as varchar(100)),e.empno,x.len+1 from emp e,x where e.deptno=x.deptno and e.empno>x.empno) select deptno,list from x where len=cnt order by 1
四十五、将分隔数据转换为多值IN列表
db2
select empno,ename,sal,deptno from emp where empno in (select cast(substr(c,2,locate(',',c,2)-2) as integer) empno from (select substr(csv.emps,cast(iter.pos as integer)) as c from (select ','||'7654,7698,7782,7788'||',' emps from t1 ) csv,(select id as pos from t100) iter where iter.pos<=length(csv.emps)) x where length(c)>1 and substr(c,1,1)=',' ) y
mysql
select empno,ename,sal,deptno from emp where empno in (select substring_index(substring_index(list.vals,',',iter.pos),',',-1) empno from (select id pos from t10) as iter,(select '7654,7698,7782,7788' as vals from t1) list where iter.pos<=(length(list.vals)-length(replace(list.vals,',','')))+1) x
oracle
select empno,ename,sal,deptno from emp where empno in (select to_number(rtrim(substr(emps,instr(emps,',',1,iter.pos)+1,instr(emps,',',1,iter.pos+1)-instr(emps,',',1,iter.pos)),',')) emps from (select ','||'7654,7698,7782,7788'||',' emps from t1) csv,(select rownum pos from emp) iter where iter.pos<=((length(csv.emps)-length(replace(csv.emps,',')))/length(','))-1
postgresql
select empno,ename,sal,deptno from emp where empno in (select cast (empno as integer) as empno from (select split_part(list.vals,',',iter.pos) as empno from (select id as pos from t10) iter,(select ','||'7654,7698,7782,7788'||',' as vals from t1) list where iter.pos<=length(list.vals)-length(replace(ist.vals,',',''))) z where length(empno)>0) x
sqlserver
select empno,ename,sal,deptno from emp where empno in (select substring(c,2,charindex(',',c,2)-2) as empno from (select substring(csv.emps,iter.pos,len(csv.emps)) as c from (select ','+'7654,7698,7782,7788'+',' as emps from t1) csv,(select id as pos from t100) iter where iter.pos<=len(csv.emps)) x where len(c)>1 and substring(c,1,1)=',') y
四十六、按字母顺序排列字符串
db2
select ename,max(case when pos=1 then c else '' end)||max(case when pos=2 then c else '' end)||max(case when pos=3 then c else '' end)||max(case when pos=4 then c else '' end)||max(case when pos=5 then c else '' end)||max(case when pos=6 then c else '' end) from (select e.ename,cast(substr(e.ename,iter.pos,1) as varchar(100)) c,cast(row_number() over (partition by e.ename order by substr(e.ename,iter.pos,1)) as integer) pos from emp e,(select cast(row_number() over() as integer) pos from emp) iter where iter.pos<=length(e.ename)) x group by ename
mysql
select ename,group_concat(c order by c separator '') from ( select ename,substr(a.ename,iter.pos,1) c from emp a,(select id pos from t10) iter where iter.pos<=length(a.ename)) x group by ename
oracle
select old_name,new_name from (select old_name,replace(sys_connect-by_path(c,' '),' ') new_name from (select e.ename old_name,row_number() over(partition by e.ename order by substr(e.ename,iter.pos,1)) rn,substr(e.ename,iter.pos,1) c from emp e,(select rownum pos from emp) iter where iter.pos<=length(e.ename) order by 1) x start with rn=1 connect by prior rn=rn-1 and prior old_name=old_name) where length(old_name)=length(new_name)
postgresql
必须先遍历每个字符串,先要知道姓名的最大长度
sqlserver
select ename,max(case when pos=1 then c else '' end)+max(case when pos=2 then c else '' end)+max(case when pos=3 then c else '' end)+max(case when pos=4 then c else '' end)+max(case when pos=5 then c else '' end)+max(case when pos=6 then c else '' end) from (select e.ename,substring(e.ename,iter.pos,1) as c,row_number() over (partition by e.ename order by substring(e.ename,iter.pos,1)) as pos from emp e,(select row_number() over(order by ename) as pos from emp) iter where iter.pos<=len(e.ename)) x group by ename
发表评论
-
5种数据库sql语句大全一
2010-08-05 00:18 1193数据库sql语句大全 一、连接列值db2/oracle/ ... -
SQL语句导入导出大全
2010-06-13 15:08 793/******* 导出到excel EXEC master. ... -
最近写的三个关于考勤的存储过程
2009-09-13 23:41 1073前一阵做一个考勤记录的东东,自己写了三个存储过程,记录如下:第 ... -
【SQL Server】存储过程的设计与概念(2)从存储过程接收信息
2009-09-13 23:06 1900从存储过程结束信息的途径有4种: 1、结果集 2、 ... -
【SQL Server】存储过程的设计与概念(1)存储过程的创建、维护
2009-09-13 23:01 1358从逻辑上讲,存储过程 ...
相关推荐
5种数据库sql语句大全,轻松写sql跨库
5种数据库sql语句大全,绝对值得收藏。本人多年积累,拿出来与大家分享。
Access数据库-SQL语句资料.mdb
Oracle数据库SQL语句大全
Oracle数据库Sql语句详解大全,提供给大家快速查询复习哦!
小区物业管理系统配套数据库SQL语句,在Oracle中导入该SQL语句,数据库建立成功
SQL语句,各类SQL语句及用法,SQL语句大全 数据库的各种操作
全国省市数据库 二级 sql语句全国省市数据库 二级 sql语句全国省市数据库 二级 sql语句全国省市数据库 二级 sql语句全国省市数据库 二级 sql语句
Oracle数据库SQL语句跟踪器,通常被称为SQL Monitor,是一种强大的工具,用于监控和分析数据库中的SQL语句执行情况。在Oracle环境中,理解SQL语句的行为是优化数据库性能的关键。SQL Monitor提供实时视图,帮助DBA...
"数据库图书信息管理数据库SQL语句分享" 本资源摘要信息是关于数据库图书信息管理数据库SQL语句分享的详细知识点总结。 实验目的 1. 了解数据库以及数据表的设计 2. 熟悉 SQL Server 2005 中的数据类型 3. 熟悉...
包含常见的增删改查sql语句,开发中经常打开看看对照着写sql,非常好用
sql 语句优化 SQL Server数据库查询速度慢的原因有很多
约束是数据库中的数据完整性实现的具体方法,在 SQL Server 中,包括 5 种约束类型:primary key 约束、foreign key 约束、unique 约束、check 约束和 default 约束。 四、数据查询 数据查询是数据库中最基本的...
SQL_ServerDB2数据库SQL语句比较
sql语句sql语句sql语句sql语句sql语句
Java知识点总结大全(七) -- 数据库SQL语句基本操作关注后面会分享面向对象,io,集合,多线程,网络,sql的总结
sql 语句就是对数据库进行操作的一种语言。 常见语句 更新:update table1 set field1=value1 where 范围 查找:select * from table1 where field1 like ’%value1%’ (所有包含‘value1’这个模式的字符串) ...