Oracle增删查改 day03
复习：1.查询语句的基本格式。
select ...
from 表名
where 条件
group by 列名
having 带组函数的条件
order by 列名
2.函数
1)组函数：count / avg / sum / max / min
2)单行函数：
a.字符函数：upper / lower / initcap
length / lpad / rpad
l : left ; r: right
select rpad(ename, 10, '*') from emp_ning;
b.数字函数： round / trunc / mod
select mod(salary, 1000) from emp_ning;
c.日期函数： months_between / add_months /
last_day(sysdate)
d.转换函数： to_char / to_date / to_number
to_char to_number
日期--------->字符 ---------->数字
<--------- <---------
to_date to_char
where password = '1234' and id = '1001'
select to_number('$7,912,345.67','$9,999,999.99')
from dual;
7912345.67
select to_char(7912345.67, '$9,999,999.99')
from dual;
$7,912,345.67
e.通用函数：nvl / coalesce / decode
使用频率比较高的单行函数：
upper / round / to_char / to_date / nvl
一、子查询
1.谁的薪水比张无忌高？
子查询
select salary from emp_ning
where ename = '张无忌';
10000
主查询
select ename from emp_ning
where salary > 10000;
张三丰
合并到一条SQL语句：
select ename from emp_ning
where salary > (select salary from emp_ning
where ename = '张无忌');
><>=<==<>: 单行比较运算符,
只能和一个数字比较。
insert into emp_ning(empno, ename, salary)
values(1014, '张无忌', 8000);
谁的薪水比所有叫张无忌的人的薪水都高？大于最大
10000
8000
select ename from emp_ning
where salary > ALL(select salary from emp_ning
where ename = '张无忌');
哪些人的薪水比任何一个叫张无忌的人高都行？大于最小
10000
8000
select ename from emp_ning
where salary > ANY(select salary from emp_ning
where ename = '张无忌');
2.谁和刘苍松同部门？列出除了刘苍松之外的员工名字。
select deptno from emp_ning
where ename = '刘苍松';
10
select ename from emp_ning
where deptno = 10
and ename <> '刘苍松';
select ename,salary,job
from emp_ning
where deptno = (select deptno from emp_ning
where ename = '刘苍松')
and ename <> '刘苍松';
--如果子查询得到的结果是多个，不能使用单行比较运算符
等号。改为：in
insert into emp_ning(empno, ename, deptno)
values(1015, '刘苍松', 20);
select ename,salary,job,deptno
from emp_ning
where deptno in (select deptno from emp_ning
where ename = '刘苍松')
and ename <> '刘苍松';
根据子查询返回的结果的行数：
返回一行：> < >= <= = <>
返回多行：>ALL >ANY <ALL <ANY in
3.每个部门拿最高薪水的是谁？
--返回多行
select deptno, max(salary)
from emp_ning
where deptno is not null
group by deptno;
30 5000
20 15000
10 10000
select ename, salary, job, deptno
from emp_ning
where (deptno, salary)
in (select deptno, max(salary)
from emp_ning
where deptno is not null
group by deptno);
4.哪个部门的人数比部门30的人数多？
select count(*) from emp_ning
where deptno = 30;
3
select deptno, count(*)
from emp_ning
group by deptno
having count(*) > (select count(*) from emp_ning
where deptno = 30);
5.哪个部门的平均薪水比部门20的平均薪水高？
select deptno, avg(nvl(salary,0)) avg_s
from emp_ning
group by deptno
having avg(nvl(salary,0)) >
(select avg(nvl(salary,0))
from emp_ning
where deptno = 20);
6.列出员工名字和职位，这些员工所在的部门平均薪水
大于5000元。
select deptno, avg(nvl(salary,0))
from emp_ning
group by deptno
having avg(nvl(salary,0)) > 5000;
105750
208000
select ename, job
from emp_ning
where deptno in (select deptno
from emp_ning
group by deptno
having avg(nvl(salary,0)) > 5000);
7.谁是张无忌的下属？
select empno from emp_ning
where ename = '张无忌';
1001
1014
select ename from emp_ning
where mgr in (1001, 1014);
select ename from emp_ning
where mgr in (select empno from emp_ning
where ename = '张无忌');
8.研发部有哪些职位？
select distinct job from emp_ning
where deptno = (select deptno
from dept_ning
where dname = '研发部');
页:
[1]