[计算机软件及应用]5种数据库sql语句大全.doc
《[计算机软件及应用]5种数据库sql语句大全.doc》由会员分享,可在线阅读,更多相关《[计算机软件及应用]5种数据库sql语句大全.doc(67页珍藏版)》请在三一文库上搜索。
1、数据库sql语句大全一、连接列值db2/oracle/postgresqlselect name(字段)| Works AS a (文字)|job as msg from emp wheredeptno=10;mysqlselect concat(name, works as a,job) as msg from emp where deptno=10;sql serverselect name+ works as a +job as msg from emp where deptno=10;二、使用条件逻辑select name,salary, case when salary=4000 t
2、hen over else ok end as statusfrom emp三、限制返回的行数db2select * from emp fetch first 5 rows onlymysql/postgresqlselect * from emp limit 5oracleselect * from emp rownum=5sql serverselect top 5 * from emp四、随机返回记录db2select name,job from emp order by rahnd() fetch first 5 rows onlymysqlselect name,job from e
3、mp order by rand() limit 5postgresqlselect * from emp order by random() limit 5oracleselect * from (select name,job from emp order by dbms_random.value()where rownum=5sql serverselect top 5 name,job from emp order by newid()五、将空值转换成实际值select coalesce(comm,0) from emp不为空则返回comm值,空则返回0,comm类型与0类型必须一致六
4、、按子串排序(取消后面2位)db2/mysql/oracle/postgresqlselect name,job from emp order by substr(job,length(job)-2)sql serverselect name,job from emp order by substring(job,len(job)-2,2)七、对字母数字混合的数据排序oracle/postgresql/*order by deptno(数字)*/select data from emp v order by replace(data,replace(translate(data,0123456
5、789,#),#,),)/*order by name(字母)*/select data from emp order byreplace(translate(data,0123456789,#),#,)db2/*order by deptno(数字)*/select * from (select ename| |cast(deptno as char(2) as data fromemp ) v order by replace(data,replace(translate(data,#,0123456789),#,),)/*order by name(字母)*/select * from
6、(select name| |cast(deptno as char(2) as data fromemp v order by replace(translate(data,#,0123456789),#,)mysql/sqlserver当前不支持translate函数,无解决方案八、处理排序空值db2/mysql/postgresql/sqlserverselect name,sal,comm from (select name,sal,comm case when comm is nullthen 0 else 1 end as is_null from emp) x order by
7、is_null desc,commoracleselect name,sal,comm from emp order by comm nulls last/all nulls lastselect name,sal,comm from emp order by comm nulls first/all nulls first九、根据数据项的键排序select name,sal,job,comm from emp order by case when job=salesman thencomm else sal end十、记录集的叠加/使用union子句相当于对使用union all子句的结果使
8、用distinctselect ename as ename_and_dname,deptno from emp where deptno=10 unionall select -,null from t1 union all select dname,deptno from dept十一、从一个表红查找另一个表没有的值db2/postgresqlselect deptno from dept except select deptno from emporacleselect deptno from dept minus select deptno from empmysql/sqlserve
9、rselect deptno from dept where deptno not in (select deptno from emp)十二、在一个表中查找与其他表不匹配的记录db2/mysql/postgresql/sqlserverselect d.* from dept d left outer join emp e on(d.deptno=e.deptno) wheree.deptno is nulloracleselect d.* from dept d,emp e where d.deptno=e.deptno (+) and e.deptno isnull十三、向查询中增加联接
10、而不影响其他联接db2/mysql/postgresql/sqlserverselect e.ename,d.loc,eb.received from emp e join dept d on(e.deptno=d.deptno) left join emp_bonus eb on (e.empno=eb.empno) order by 2oracleselect e.ename,d.loc,eb.received from emp e,dept d,emp_bonus eb wheree.deptno=d.deptno and e.empno=eb.empno (+) order by 2s
11、elect e.ename,d.loc,(select eb.received from emp_bonus eb whereeb.empno=e.empno) as received from emp e,dept d where e.deptno=d.deptnoorder by 2十四、检测两个表中是否有相同的数据解决原理:1、首先,查找处表emp中存在而视图v中没有的行2、然后合并(union all)在视图v中存在,而在表emp中没有的行十五、识别和消除笛卡尔积在from子句对表进行联接来返回正确的结果集:select e.ename,d.loc from emp e,dept d
12、where e.deptno=10 and d.deptno=e.deptno十六、聚集与联接/*只对不相同的工资求和*/mysql/postgresqlselect deptno,sum(distinct sal) as total_sal,sum(bonus) as total_bonusfrom (select e.empno,e.ename,e.sal,e.deptno,e.sal*case when eb.type=1then .1 when eb.type=2 then .2 else .3 end as bonus from emp e,emp_bonuseb where e.e
13、mpno=eb.empno and e.deptno=1) x group by deptnodb2/oracle/sqlserverselect distinct deptno,total_sal,total_bonus from (selecte.empno,e.ename,sum(distinct e.sal) over (partition by e.deptno) astotal_sal,e.deptno,sum(e.sal*case when eb.type=1 then .1 when eb.type=2then .2 else .3 end) over (partition b
14、y deptno) as total_bonus from empe,emp_bonus eb where e.empno=eb.empno and e.deptno=10) x十七、聚集与外联接/*只对部门10中不同的工资进行汇总*/db2/mysql/postgresql/sqlserverselect deptno,sum(distinct sal) as total_sal,sum(bonus) as total_bonusfrom (select e.empno,e.ename,e.sal,e.deptno,e.sal*case when eb.type isnull then 0
15、when eb.type=1 then .1 when eb.type=2 then .2 else .3 end asbonus from emp e left outer join emp_bonus eb on(e.empno=eb.empno) wheree.deptno=10) group by deptnoselect distinct deptno,total_sal,total_bonus from (selecte.empno,e.ename,sum(distinct e.sal) over (partition by e.deptno) astotal_sal,e.dept
16、no,sum(e.sal*case when eb.type is null then 0 wheneb.type=1 then .1 when eb.type =2 then .2 else .3 end) over (partitionby deptno) as total_bonus from emp e left outer join emp_bonus eb on(e.empno=eb.empno) where e.deptno=10) xoracleselect deptno,sum(distinct sal) as total_sal,sum(bonus) as total_bo
17、nusfrom (select e.empno,e.ename,e.sal,e.deptno,e.sal*case when eb.type isnull then 0 when eb.type=1 then .1 when eb.type=2 then .2 else .3 end asbonus from emp e,emp_bonus eb where e.empno=eb.empno (+) ande.deptno=10) group by deptno十八、从多个表中返回丢失的数据db2/mysql/postgresql/sqlserverselect d.deptno,d.dnam
18、e,e.ename from dept d full outer join emp e on(d.deptno=e.deptno)select d.deptno,d.dname,e.ename from dept d right outer join emp eon(d.deptno=e.deptno) union select d.deptno,d.dname,e.ename from dept dleft outer join emp e on (d.deptno=e.deptno)oracleselect d.deptno,d.dname,e.ename from dept d,emp
19、e whered.deptno=e.deptno (+) union select d.deptno,d.dname,e.ename from deptd,emp e where d.deptno(+)=e.deptno十九、在运算和比较时使用null值select ename,comm from emp where coalesce(comm,0)(select comm from empwhere ename=WARD)二十、从一个表向另外的表中复制行insert into dept_east (deptno,dname,loc) select deptno,dname,loc fromd
20、ept where loc in(NEW YORK,BOSTON)二十一、复制表定义db2create table dept_2 like deptoracle/mysql/postgresqlcreate table dept_2 as select * from dept where 1=0sqlserverselect * into dept_2 from dept where 1=0二十二、一次向多个表中插入记录oracleinsert all when loc in(NEW YORK,BOSTON) then into dept_east(deptno,dname,loc) valu
21、es(deptno,dname,loc)when loc=CHICAGO then into dept_mid (deptno,dname,loc)values(deptno,dname,loc)else into dept_west (deptno,dname,loc) values(deptno,dname,loc)select deptno,dname,loc from deptdb2insert into (select * from dept_west union all select * from dept_eastunion all select * from dept_mid)
22、 select * from deptmysql/postgresql/sqlserver不支持多表插入操作二十三、阻止对某几列插入在表中创建一个视图,该视图将只显示允许用户进行操作的列,强制所有的插入操作都通过该视图进行create view new_emps as select empno,ename,job from emp 二十四、用其他表中的值更新db2/mysqlupdate emp e set(e.sal,m)=(select ns.sal,ns.sal/2 from new_sal nswhere ns.deptno=e.deptno) where exists(select
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 计算机软件及应用 计算机软件 应用 种数 sql 语句 大全
链接地址:https://www.31doc.com/p-1991431.html