-- NVL2(expr1, expr2, expr3),expr1不为null,得到expr2,否则得到expr3select ename, sal, comm, nvl2(comm,(sal + comm), sal)as 月总工资 from scott.emp;-- NVL(expr1, expr2),expr1为null,得到expr2,否则得到expr1本身select ename, sal, comm, nvl(comm,0)as 奖金 from scott.emp;-- 条件语句-- where 条件语句从左到右遵循简单到复杂-- 与 andselect*from scott.emp e
where e.sal >=3000and e.deptno =20;-- 或 orselect*from scott.emp e
where e.sal >=3000or e.deptno =20;-- 非 notselect*from scott.emp e
where e.comm isnotnull;-- 逻辑表达:==、>、<、!=、<>、>= 、<=select*from scott.emp e
where e.deptno <>30;-- 不等于 尽量使用<>,减少使用!=-- 模糊查询-- S开头select*from scott.emp e
where e.ename like'S%';-- 含有S(包含首尾)select*from scott.emp e
where e.ename like'%S%';-- S结尾select*from scott.emp e
where e.ename like'%S';-- in()函数返回结果集,in之前字段进行检索select*from scott.dept d
where d.deptno in(selectdistinct e.deptno from scott.emp e
);select*from scott.dept d
where d.deptno notin(selectdistinct e.deptno from scott.emp e
);-- 条件在exists函数里面判断select*from scott.dept d
wherenotexists(select1from scott.emp e where e.deptno = d.deptno
);-- between 下限 and 上限select*from scott.emp e
where e.sal between2500and4000;-- all()/any()函数 全部满足/任意符合的select*from scott.emp e
where e.sal >all(selectdistinct e.sal from scott.emp e where e.job <>'CLERK'and length(e.ename)>4);-- 排序order by指定字段排序,默认升序asc,降序在字段后加descselect*from scott.emp e
orderby e.hiredate;-- 降序descselect*from scott.emp e
where e.sal >2000orderby e.sal desc;-- 聚合函数:多行结果参与运算返回一行结果-- max/min函数select e.ename, e.sal
from scott.emp e
where e.sal in(selectmax(e.sal)as max_sal from scott.emp e
);-- sum/avg函数selectsum(e.comm)as sum_comm,round(avg(nvl(e.comm,0)),2)as avg_comm
from scott.emp e
where e.deptno =30;-- 统计函数count()selectcount(*)as total
from scott.emp e;-- 分组语句:group by后有什么字段,select后就要对应多少字段字段,不能出现多余字段(函数除外)-- having对group by分组结果进一步条件限制,只能与group by并用,无法单独使用select e.deptno,count(*)as total,sum(e.sal)as sum_sal -- 5from scott.emp e -- 1-- where -- 2groupby e.deptno -- 3havingcount(*)>3-- 4orderby total desc;-- 6-- 连接-- 等值连接select e.empno, e.ename, d.deptno, d.loc
from scott.emp e, scott.dept d
where e.deptno = d.deptno;-- 内连接select e.empno, e.ename, d.deptno, d.loc
from scott.emp e innerjoin scott.dept d on e.deptno = d.deptno
where d.loc ='CHICAGO'orderby e.empno desc;-- 左连接:左边是主表,右边是匹配表。显示主表所有信息,匹配表没有的项置nullselect d.deptno, d.dname, e.ename
from scott.dept d leftjoin scott.emp e on d.deptno = e.deptno;-- 右连接:右边是主表,左边是匹配表。减少使用,逻辑比较绕select d.deptno, d.dname, e.ename
from scott.dept d rightjoin scott.emp e on d.deptno = e.deptno;
-- 第一题-- 第一题droptable student;CREATETABLE student (s_no CHAR(6),s_name CHAR(10)NOTNULL,s_sex NCHAR(2),s_birthday DATE,s_score NUMBER (5,1),s_addf NUMBER (5,1),class_no CHAR(5));INSERTINTO student VALUES('0001','小明','男',TO_DATE('13-07-1990','dd-mm-yyyy'),89,12,'1101');INSERTINTO student VALUES('0002','小红','女',TO_DATE('13-07-1991','dd-mm-yyyy'),83,17,'1201');INSERTINTO student VALUES('0003','小强','男',TO_DATE('13-07-1990','dd-mm-yyyy'),80,11,'1102');INSERTINTO student VALUES('0004','小刚','男',TO_DATE('13-07-1991','dd-mm-yyyy'),75,19,'1202');INSERTINTO student VALUES('0005','小花','女',TO_DATE('13-07-1991','dd-mm-yyyy'),90,15,'1103');SELECT*FROM student;-- c) 查询编号为 c002班级中所有女同学 select*from student s
where s.class_no ='c002'and s.s_sex ='女';-- d) 查询所有学生出生日期,要求输出格式为 'yyyy-mm-dd';select s.s_no, s.s_name, s.s_sex, to_char(s.s_birthday,'yyyy-mm-dd')as s_birthday, s.s_score, s.s_addf, s.class_no
from student s;-- e) 查询总分(入学成绩+附加分)在 550和600之间的所有学生; select*from student s
where(s.s_score + s.s_addf)between550and600-- f) 查询名字中带“小”字的所有学生; select*from student s
where s.s_name like'%小%';-- g) 查询所有学生,按总分(入学成绩+附加分)降序排列 select*from student s
orderby(s.s_score + s.s_addf)desc;-- h) 查询入学成绩大于平均入学成绩的所有学生;select*from student s
where s.s_score >(selectavg(student.s_score)from student);-- 第二题dropTABLE dept;droptable emp;CREATETABLE dept (
deptno NUMBER (11)NOTNULL,
dname VARCHAR(14)DEFAULTNULL,
loc VARCHAR(13)DEFAULTNULL);INSERTINTO dept (deptno,dname,loc)VALUES(10,'ACCOUNTING','NEWYORK');INSERTINTO dept VALUES(20,'RESEARCH','DALLAS');INSERTINTO dept VALUES(30,'SALES','CHICAGO');INSERTINTO dept VALUES(40,'OPERATIONS','BOSTON');CREATETABLE emp (
empno NUMBER (11)NOTNULL,
ename VARCHAR(10)NOTNULL,
job VARCHAR(9)DEFAULTNULL,
mgr NUMBER (11)DEFAULTNULL,
hiredate DATEDEFAULTNULL,
sal NUMBER (10,2)DEFAULTNULL,
comm NUMBER (10,2)DEFAULTNULL,
deptno NUMBER (11)DEFAULTNULL);INSERTINTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(7369,'SMITH','CLERK',7902,to_date('1980-12-17','yyyy-mm-dd'),800,NULL,20);INSERTINTO emp VALUES(7499,'ddd','SALESMAN',7698,to_date('1981-02-20','yyyy-mm-dd'),1600,300,NULL);INSERTINTO emp VALUES(7521,'WARD','SALESMAN',7698,to_date('1981-02-22','yyyy-mm-dd'),1250,500,30);INSERTINTO emp VALUES(7566,'JONES','MANAGER',7839,to_date('1981-04-02','yyyy-mm-dd'),2975,NULL,20);INSERTINTO emp VALUES(7654,'MARTIN','SALESMAN',7698,to_date('1981-09-28','yyyy-mm-dd'),1250,1400,30);INSERTINTO emp VALUES(7698,'BLAKE','MANAGER',7839,to_date('1981-05-01','yyyy-mm-dd'),2850,NULL,30);INSERTINTO emp VALUES(7782,'CLARK','MANAGER',7839,to_date('1981-06-09','yyyy-mm-dd'),2450,NULL,10);INSERTINTO emp VALUES(7788,'SCOTT','ANALYST',7566,to_date('1987-07-03','yyyy-mm-dd'),3000,NULL,20);INSERTINTO emp VALUES(7839,'KING','PRESIDENT',NULL,to_date('1981-11-17','yyyy-mm-dd'),5000,NULL,10);INSERTINTO emp VALUES(7844,'TURNER','SALESMAN',7698,to_date('1981-09-08','yyyy-mm-dd'),1500,0,30);INSERTINTO emp VALUES(7876,'ADAMS','CLERK',7788,to_date('1987-07-13','yyyy-mm-dd'),1100,NULL,20);INSERTINTO emp VALUES(7900,'JAMES','CLERK',7698,to_date('1981-12-03','yyyy-mm-dd'),950,NULL,30);INSERTINTO emp VALUES(7902,'FORD','ANALYST',7566,to_date('1981-12-03','yyyy-mm-dd'),3000,NULL,20);INSERTINTO emp VALUES(7934,'MILLER','CLERK',7782,to_date('1981-01-23','yyyy-mm-dd'),1300,NULL,10);-- 练习1、请查询表DEPT中所有部门的情况select*from dept;-- 练习2、查询表DEPT中的部门号、部门名称两个字段的所有信息select d.deptno, d.dname
from dept d;-- 练习3、请从表EMP中查询10号部门工作的雇员姓名和工资。select e.ename, e.sal
from emp e
where e.deptno =10;-- 练习4、请从表EMP中查找工种是职员CLERK或经理MANAGER的雇员姓名、工资select e.ename, e.sal
from emp e
where e.job in('CLERK','MANAGER');-- 练习5、请在EMP表中查找部门号在10-30之间的雇员的姓名、部门号、工资、工作。select e.ename, e.deptno, e.sal, e.job
from emp e
where e.deptno between10and30;-- 练习6、请从表EMP中查找姓名以J开头所有雇员的姓名、工资、职位select e.ename, e.sal, e.job
from emp e
where e.ename like'J%';-- 练习7、请从表EMP中查找工资低于2000的雇员的姓名、工作、工资,并按工资降序排列select e.ename, e.sal, e.job
from emp e
where e.sal <2000orderby e.sal desc;-- 练习8、在表EMP中查询所有工资高于JONES的所有雇员姓名、工作和工资。select e.ename, e.sal, e.job
from emp e
where e.sal >(select emp.sal from emp where emp.ename ='JONES');-- 练习9、列出没有对应部门表信息的所有雇员的姓名、工作以及部门号select*from emp e
wherenotexists(select1from dept d where d.deptno <> e.deptno
);-- 练习10、找出奖金高于薪金的员工select*from emp e
where e.comm > e.sal;-- 练习11、找出奖金高于薪金的60%的员工select*from emp e
where e.comm > e.sal *0.6;-- 练习12、找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料.select*from emp e
where e.job ='MANAGER'and e.deptno =10or e.job ='CLERK'and e.deptno =20;-- 练习13、找出部门10中所有经理(MANAGER),部门20中所有办事员(CLERK),既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料.select*from emp e
where e.job ='MANAGER'and e.deptno =10or e.job ='CLERK'and e.deptno =20or e.job <>'MANAGER'and e.job <>'CLERK'and e.sal >=2000;-- 练习14、找出有奖金的员工的职位有哪些select*from emp e
where nvl(e.comm,0)>0;-- 练习15、找出各月倒数第3天受雇的所有员工select*from emp e
where last_day(e.hiredate)- e.hiredate =3;-- 练习16、以首字母大写的方式显示所有员工的姓名select concat(upper(substr(e.ename,1,1)), lower(substr(e.ename,2, length(e.ename))))from emp e;select initcap(ename)from emp;-- 练习17、显示正好为5个字符的员工的姓名select e.ename
from emp e
where length(e.ename)=5-- 练习18、显示不带有"R"的员工的姓名select e.ename
from emp e
where e.ename notin(select emp.ename from emp where emp.ename like'%R%');-- 练习19、显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面.select e.ename, e.hiredate
from emp e
orderby e.hiredate;-- 练习20、显示所有员工的姓名、工作和薪金,按工作的降序排序,若工作相同则按薪金排序.select e.ename, e.job, e.sal
from emp e
orderby e.job desc, e.sal desc;-- 练习21、显示在一个月为30天的情况(所有月份都按30天来计算)所有员工的日薪金,忽略余数select floor(e.sal /30)as sal
from emp e;-- 练习22、找出在(任何年份的)2月受聘的所有员工select*from emp e
where extract(monthfrom e.hiredate)=2;select*from emp e
where to_char(e.hiredate,'mm')='02';-- 练习23、 查询所有雇员的姓名、SAL与COMM之和select e.ename,(e.sal + nvl(e.comm,0))as sum_sal
from emp e;-- 练习24、查询列出来公司就职时间超过24年的员工名单select*from emp e
where floor(months_between(sysdate, e.hiredate)/12)>24;select*from emp e
where e.hiredate < add_months(sysdate,-12*24);-- 练习25、 查询于81年来公司所有员工的总收入(SAL和COMM)select e.hiredate,(e.sal + nvl(e.comm,0))as sum_sal
from emp e
wheremod(extract(yearfrom e.hiredate),100)=81;select e.hiredate,(e.sal + nvl(e.comm,0))as sum_sal
from emp e
where to_char(e.hiredate,'yy')='81';-- 练习26、查询显示每个雇员加入公司的准确时间,按××××年××月××日 时分秒显示。select e.ename, to_char(e.hiredate,'yyyy"年"mm"月"dd"日" hh"时"mi"分"ss"秒"')as date_time
from emp e;-- 注to_char格式中文用双引号