java达内课件06oracle编程exday学习非关联子查询的数据环境和语法

练习列12对比非关联子查询,掌握3*456注 字符函数：lpad/ 数字函数：mod 日期函数：months_between/add_months/last_day 转换函数：to_char/to_date/ 分步查询查 单行比较运算符><>=<== Exists关键 交集 差集 leftouterjoin&&rightouter fullouterjoin全外连 练习1：单字符函数：lpad/【案1ename字段设置为10个长度,如果丌够左边用“*”号补SQL>SQL>selectlpad(ename,10,'*')fromemp_xxx【案2ename字段设置为10个长度,如果丌够右边用“#”号SQL>SQL>selectrpad(ename,10,'#')fromemp_xxx数字函数：modSQL>SQL>selectsalary,mod(salary,5000)fromemp_xxx日期函数：months_betweenadd_monthslast_daySQL>SQL>selectename,hiredatefromemp_xxxwhereename='amy';SQL>updateemp_xxxsethiredate=add_months(hiredate,-2)whereename='amy';SQL>selectename,hiredatefromemp_xxxwhereename='amy';SQL>SQL>selectsysdate,last_day(sysdate)fromdual转换函数to_charto_date【案例6】将“$7,912,345.67”乘以10,输出结SQL>SQL>selectto_number('$7,912,345.67','$9,999,999.99')fromdual【案例7】 .67按指定格式”$9,999,999.99“输SQL>SQL>select .67,'$9,999,999.99')fromdual练习2：子查询分步查询查SQL>selectsalarySQL>selectsalaryfromemp_xxxwhereename='SQL>selectenamefromemp_xxxwheresalary>10000SQL>selectenamefromSQL>selectenamefromwheresalary>(selectsalaryfromwhereename= ')单行比较运算符 >= =SQL>SQL>selectdistinctjobfromemp_xxxwheredeptno=(selectfromwheredname='developer')【案例10】谁的薪水比高？--如果有多个叫的人则会出--准备数据：在--准备数据：在emp_xxx中再揑入一个叫SQL>insertintoemp_xxx(empno,ename,values(1014, ',8000)SQL>selectename,salaryfromemp_xxx查询语句--使用单行比较运算符会出 SQL>SQL>selectename,salaryfromemp_xxxwhereSQL>selectenamefromwheresalary>ALL(selectsalaryfrom'whereename= ') 的&&工资最低的人高？--大于最小SQL>SQL>selectenamefromwheresalary>ANY(selectsalaryfromwhereename= ') 例SQL>selectdeptnoSQL>selectdeptnofromemp_xxxwhereename='刘苍松';SQL>selectenamefromwheredeptno10andename刘苍松SQL>selectename,salary,jobfromemp_xxxSQL>selectename,salary,jobfromemp_xxxwheredeptno=(selectdeptnofromwhereename刘苍松')andename<>'刘苍松';【案14】谁和刘苍松同部门？列出除了刘苍松之外的员工名字(如果子查询得到的结果是多个----数据准备：再添加1个“刘苍松”同学,部门号为SQL>insertintoemp_xxx(empnoenamedeptno)values(1015,'刘苍松',20);--如果子查询得到的结果是多个,丌能使用单行比较运算符“=”,SQL>selectename,salary,job,deptnofromemp_xxxwheredeptnoin(selectdeptnofromandename【案例15】谁是的下属？如果只有一个叫的员工,则无问题,如果有多个,需要用SQL>SQL>selectempnofromemp_xxxwhereename= 'SQL>SQL>selectenamefromwherewheremgrin10011014);mgr代表该员工上司的SQL>selectenamefromSQL>selectenamefromwheremgrin(selectempnofromwhereename= ')3：子查询SQL>selectdeptno,SQL>selectdeptno,max(salary)fromemp_xxxwheredeptnoisnotgroupbydeptnoSQL>SQL>selectename,salary,job,deptnofromemp_xxxwhere(deptno,salary)in(selectdeptno,fromwheredeptnoisnotnullgroupbydeptno);SQL>selectcount(*)fromSQL>selectcount(*)fromwheredeptno=30SQL>selectSQL>selectdeptno,count(*)fromemp_xxxgroupbyhavingcount(*)>(selectcount(*)fromwheredeptno=30)SQL>SQL>selectdeptno,avg(nvl(salary,0))avg_sfromemp_xxxgroupbyhavingavg(nvl(salary,0))>(selectfromwheredeptno=20)【案19】列出员工名字和职位部门平均薪水大于5000于SQL>SQL>selectdeptno,avg(nvl(salary,0))fromemp_xxxgroupbyhavingavg(nvl(salary,0))>5000SQL>selectename,fromwheredeptnoSQL>selectename,fromwheredeptnoin(selectfromemp_xxxgroupbydeptnohavingavg(nvl(salary,0))>5000)SQL>SQL>selectename,fromwheresalary<(selectfromemp_xxx);SQL>selectavg(nvl(salary,0)fromemp_xxx;SQL>SQL>selectename,salary,deptnofromemp_xxxawheresalary<(selectfromwheredeptno=a.deptno)--子查询丌再是Sql句,需要依赖主查询传来的参数Exists【案22】哪些人是其他人的经理SQL>selectenameSQL>selectenamefromemp_xxxwhereexists(select1fromwheremgr=a.empno)exists关键字判断子查询有没有数据返回,有则ture,falseexists不关心子查询的结果,所以子查询中select后面写什么都可以sql执行顺序从主查询开始,把主查询中的empno数据传入子查询,SQL>selectenameSQL>selectenamefromwhereempnoin(selectdistinctfromemp_xxx)【案例SQL>selectenameSQL>selectenamefromemp_xxxwherenotexists(select1fromwheremgr=a.empno)SQL>SQL>selectenamefromwhereempnonotin(selectdistinctfromwheremgrisnotnull)--notinnull注意：notin列表)：如果列表中有null没有结果返回in(列表)没有关系SQL>selectenamefromSQL>selectenamefromwhereempnonotin(1001,1005,1006,1008,null)【案例SQL>SQL>selectdeptno,dnamefromdept_xxxdwherenotexists(select1fromwheredeptno=d.deptno)合集【案25】合集union演SQL>SQL>selectename,salaryfromemp_xxxwheredeptno=10selectename,salaryfromemp_xxxwheresalary>6000;【案26】合集unionall演SQL>SQL>selectename,salaryfromemp_xxxwheredeptno=10unionselectename,salaryfromemp_xxxwheresalary>6000;交集【案27】交集intersect演SQL>SQL>selectename,salaryfromemp_xxxwheredeptno=10selectename,salaryfromemp_xxxwheresalary>6000;差集【案28】差集minus演SQL>SQL>selectename,salaryfromemp_xxxwheredeptno=10selectename,salaryfromemp_xxxwheresalary>6000;SQL>SQL>selectename,dname,locationfromemp_xxxejoindept_xxxdone.deptno=d.deptno;结果集(结果集中的数据一定是在主从表中能找到匹配数据的记录 amy 的部门号(deptno)为部门号为40的部门没有员工1Deptment对应多个员工EmployeeSQL>SQL>selectt1.ename,fromemp_xxxt1joinemp_xxx --t1示从t2示主ont1.mgr=t2.empnoleftouterjoin&&rightouter--驱动表--匹配表leftouterjoin左边的表为驱动SQL>selecte.empno,ename,d.deptno,d.dname,d.locationfromemp_xxxeleftouterjoindept_xxxdone.deptno=d.deptno--驱动表--匹配表rightouterjoin右边的表为驱动SQL>selecte.empno,ename,d.deptno,d.dname,d.locationfromde