数据库笔记03

-- NVL2(expr1, expr2, expr3)，expr1不为null，得到expr2，否则得到expr3
select 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 条件语句从左到右遵循简单到复杂
	-- 与 and
select * 
from scott.emp e
where e.sal >= 3000 and e.deptno = 20;
	-- 或 or
select * 
from scott.emp e
where e.sal >= 3000 or e.deptno = 20;
	-- 非 not
select * 
from scott.emp e
where e.comm is not null;

	-- 逻辑表达：==、>、<、!=、<>、>= 、<=
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 (
	select distinct e.deptno from scott.emp e
);

select * 
from scott.dept d
where d.deptno not in (
	select distinct e.deptno from scott.emp e
);

-- 条件在exists函数里面判断
select * 
from scott.dept d
where not exists (
	select 1 from scott.emp e where e.deptno = d.deptno
);

-- between 下限 and 上限
select * from scott.emp e
where e.sal between 2500 and 4000;

-- all()/any()函数 全部满足/任意符合的
select * 
from scott.emp e
where e.sal > all (
	select distinct e.sal from scott.emp e where e.job <> 'CLERK' and length(e.ename) > 4
);

-- 排序order by指定字段排序，默认升序asc，降序在字段后加desc
select * 
from scott.emp e
order by e.hiredate;
-- 降序desc
select * 
from scott.emp e
where e.sal > 2000
order by e.sal desc;


-- 聚合函数：多行结果参与运算返回一行结果
	-- max/min函数
	select e.ename, e.sal
	from scott.emp e
	where e.sal in(
		select max(e.sal) as max_sal from scott.emp e
	);

	-- sum/avg函数
	select sum(e.comm) as sum_comm, round(avg(nvl(e.comm, 0)), 2) as avg_comm
	from scott.emp e
	where e.deptno = 30;

	-- 统计函数count()
	select count(*) 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		-- 5
from scott.emp e												-- 1
-- where														-- 2
group by e.deptno												-- 3
having count(*) > 3												-- 4
order by 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 inner join scott.dept d on e.deptno = d.deptno
	where d.loc = 'CHICAGO'
	order by e.empno desc;
	
	-- 左连接：左边是主表，右边是匹配表。显示主表所有信息，匹配表没有的项置null
	select d.deptno, d.dname, e.ename
	from scott.dept d left join scott.emp e on d.deptno = e.deptno;
	-- 右连接：右边是主表，左边是匹配表。减少使用，逻辑比较绕
	select d.deptno, d.dname, e.ename
	from scott.dept d right join scott.emp e on d.deptno = e.deptno;

-- 第一题
-- 第一题
drop table student;

CREATE TABLE student (s_no CHAR (6),s_name CHAR (10) NOT NULL,s_sex NCHAR (2),s_birthday DATE,s_score NUMBER (5,1),s_addf NUMBER (5,1),class_no CHAR (5));

INSERT INTO student VALUES ('0001','小明','男',TO_DATE('13-07-1990','dd-mm-yyyy'),89,12,'1101');
INSERT INTO student VALUES ('0002','小红','女',TO_DATE('13-07-1991','dd-mm-yyyy'),83,17,'1201');
INSERT INTO student VALUES ('0003','小强','男',TO_DATE('13-07-1990','dd-mm-yyyy'),80,11,'1102');
INSERT INTO student VALUES ('0004','小刚','男',TO_DATE('13-07-1991','dd-mm-yyyy'),75,19,'1202');
INSERT INTO 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) between 550 and 600


-- f) 查询名字中带“小”字的所有学生； 
select *
from student s
where s.s_name like '%小%';


-- g) 查询所有学生，按总分（入学成绩+附加分）降序排列 
select *
from student s
order by (s.s_score + s.s_addf) desc;

-- h) 查询入学成绩大于平均入学成绩的所有学生；
select *
from student s
where s.s_score > (select avg(student.s_score) from student);


-- 第二题
drop TABLE dept;
drop table emp;

CREATE TABLE dept (
	deptno NUMBER (11) NOT NULL,
	dname VARCHAR (14) DEFAULT NULL,
	loc VARCHAR (13) DEFAULT NULL
);
INSERT INTO dept (deptno,dname,loc) VALUES (10,'ACCOUNTING','NEWYORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES (30,'SALES','CHICAGO');
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');

CREATE TABLE emp (
	empno NUMBER ( 11 ) NOT NULL,
	ename VARCHAR ( 10 ) NOT NULL,
	job VARCHAR ( 9 ) DEFAULT NULL,
	mgr NUMBER ( 11 ) DEFAULT NULL,
	hiredate DATE DEFAULT NULL,
	sal NUMBER ( 10, 2 ) DEFAULT NULL,
	comm NUMBER ( 10, 2 ) DEFAULT NULL,
	deptno NUMBER ( 11 ) DEFAULT NULL 
);
INSERT INTO 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);
INSERT INTO emp VALUES (7499,'ddd','SALESMAN',7698,to_date('1981-02-20', 'yyyy-mm-dd'),1600,300,NULL);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('1981-02-22', 'yyyy-mm-dd'),1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('1981-04-02', 'yyyy-mm-dd'),2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('1981-09-28', 'yyyy-mm-dd'),1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1981-05-01', 'yyyy-mm-dd'),2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('1981-06-09', 'yyyy-mm-dd'),2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('1987-07-03', 'yyyy-mm-dd'),3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('1981-11-17', 'yyyy-mm-dd'),5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('1981-09-08', 'yyyy-mm-dd'),1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('1987-07-13', 'yyyy-mm-dd'),1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('1981-12-03', 'yyyy-mm-dd'),950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('1981-12-03', 'yyyy-mm-dd'),3000,NULL,20);
INSERT INTO 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 between 10 and 30;

-- 练习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 < 2000
order by 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
where not exists(
	select 1 from 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 = 10 or e.job = 'CLERK' and e.deptno = 20;

-- 练习13、找出部门10中所有经理(MANAGER),部门20中所有办事员(CLERK),既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料.
select *
from emp e
where e.job = 'MANAGER' and e.deptno = 10 or e.job = 'CLERK' and e.deptno = 20 or 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 not in(
	select emp.ename from emp where emp.ename like '%R%'
);

-- 练习19、显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面.
select e.ename, e.hiredate
from emp e
order by e.hiredate;

-- 练习20、显示所有员工的姓名、工作和薪金,按工作的降序排序,若工作相同则按薪金排序.
select e.ename, e.job, e.sal
from emp e
order by 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(month from 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
where mod(extract(year from 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格式中文用双引号