sql使用整理

下面的使用都是在oracle上，其他不清楚，不同的版本好像也可能不同，使用的表是oracle自带的数据库

1.查询列重新指定列名

select a b from c;

//从表c中选出a列作为b列

2.查询一个表中的内容再作为另一个表使用

select t.ename name from emp,(select * from emp where empno=7369) twhere emp.empno = t.empno

//这里只是为了说明如何把一个表查询作为另一个表再使用

3.限制返回行

select top 2 * from a;//返回头两行

select top 2 percet from a;//返回2%行

4.求出每个部门的每个职位的薪水大于1700的员工的平均薪水，按平均薪水，部门号，职位列出

select avg(sal) ,deptno,job from emp where sal > 1700group by deptno,job;

//group by使用时，返回的结果中要么是group by的字段，要么是聚集函数的结果

5.求出每个部门的每个职位的薪水大于1700的员工的平均薪水，且平均薪水要高于2500，按平均薪水，部门号，职位列出

select avg(sal) ,deptno,job from emp where sal > 1700group by deptno,jobhaving avg(sal) > 2500;

//HAVING 子句对 GROUP BY 子句设置条件的方式与 WHERE 子句和 SELECT 语句交互的方式类似。WHERE 子句搜索条件在进行分组操作之前应用；而HAVING 搜索条件在进行分组操作之后应用。

6.求出每个部门的每个职位的薪水大于1700的员工的平均薪水，且平均薪水要高于2500，按平均薪水，部门号，职位列出，按部门号，平均薪水排序

select avg(sal) ,deptno,job from emp where sal > 1700group by deptno,jobhaving avg(sal) > 2500 order by deptno,avg(sal);

//sql处理的流程应该是先取数据，再where过滤，再分组，再分组having过滤，再排序

7.结果集中如果有聚集函数如max,min,avg,sum等，则其余的字段可以没有，或者必须是group by中的字段

select max(sal),ename from emp;//这句是错误的，因为ename字段不在group by后

8.查询薪水最高者的姓名及薪水

select ename from emp,(select max(sal) salary from emp) twhere emp.sal = t.salary;

9.求出每个部门的最高薪水者

select ename,emp.empno,emp.deptno,t.salary from emp,(select max(sal) salary,deptnofrom emp group by emp.deptno) t whereemp.sal = t.salary and emp.deptno = t.deptno order by t.deptno;

//先查询出每个部门最高薪水和部门编号，再把每个人的薪水和部门号与之匹配

10.求出平均薪水最高的部门

法1：//先按部门求平均薪水作为表t1，t2，再从表t2中求出最高薪水作为表t3，再从表t1，t3中取出薪水为最高薪水的部门

select t1.salary,t1.deptno from (select avg(sal) salary,deptno from emp group by emp.deptno) t1,(select max(salary) max_sal from (select avg(sal) salary,deptno from emp group by emp.deptno) t2) t3 where t1.salary = t3.max_sal;

法2：//先按部门求平均薪水作为表t1，t2(是按平均薪水降序)，再从表t2中取出第一条记录即最高薪水作为表t3，再从表t1，t3中取出薪水为最高薪水的部门

select t1.salary,t1.deptno from (select avg(sal) salary,deptno from emp group by emp.deptno) t1,(select salary from (select avg(sal) salary,deptno from emp group by emp.deptno order by salary desc) t2where rownum = 1)t3 where t1.salary = t3.salary;

//rownum是表中的行号

11.统计各部门，各薪水段的人数，列名显示为部门号，1000以下的，1000到2000的，2000以上的

select deptno,count(case when sal < 1000 then 1 else null end) "<1000",count(case when sal between 1000 and 2000 then 1 else null end ) "1000-2000",count(case when sal > 2000 then 1 else null end) ">2000" from emp group by deptno order by deptno;

//这里要知道case when的用法就可以了,case when是标准sql,decode是oracle特有的

12.有student(sno,sname,ssex),course(cno,cname,tno),sc(sno,cno,score),teacher(tno,tname)表

查询每个学生的学号，姓名，所修课程，及成绩

select sno,sname,cname,score from student,course,sc where student.sno = sc.sno and course.cno = sc.cno;

将课程号为002的授课老师改为"张三"

update teacher set teacher.tname = '张三' where teacher.tno in(select teacher.tno from course,teacher where course.tno = teacher.tno and cno = 002);

//先查询出002课程的老师的编号，再更新，字符串都用单引号

删除002同学的001课程的成绩

delete from sc where sno =002 and cno = 001;

查询出两门以上不及格的同学的学号及平均成绩

法1：

select sc.sno,avg(score) from sc,(select sno,count(case  when score < 60 then 1 else null end) count1 from sc group by sno) t2 where sc.sno = t2.sno and t2.count1 >=2 group by sc.sno ;

法2：

select sc.sno,avg(score) from sc,(select sno,count(1) count1 from sc where score < 60group by sno) t2 where sc.sno = t2.sno and t2.count1 >=2 group by sc.sno ;

统计各科分数段的人数，列名显示为课程号，课程名称，100-85，85-60，<60

select sc.cno,count(case when score between 85 and 100 then 1 elsenull end) 分数85_100,count(case when score between 85 and 60 then 1 else null end)分数60_85,count(case when score <60 then 1 else null end)分数0_60 from sc group by cno

//case when score between 85 and 100 then 1 else null end要算入统计就返回1,不算入统计就返回null,0对于count是不对的，但0对于sum是可以的，作为新的列显示时直接写列名就可以了，但不能以数字，‘-’，‘_’开头，列名也不能有‘-’‘<’‘[’等

13.在创建了表后，自动commit的，但修改数据的话，要手动commit