迪卡尔积
等值连接 （包括自连接）
非等值连接
左连接：显示左表的所有包括不满足条件的left join
右连接：显示右表的所有包括不满足条件的 right join
全连接：显示左右表的所有以及不满足条件的 full join
union 合并，重复会被去掉
union all 重复不会去掉
###############selct
sql / scott/tiger
select * from emp;
conn hr/hr
hr用户有那些表
select object_name from user_objects where object_type='TABLE';
OBJECT_NAME
------------------------------------------------------------------------------
REGIONS
LOCATIONS
JOB_HISTORY
JOBS
EMPLOYEES
DEPARTMENTS
COUNTRIES
2.条件和排序
select * from departments where department_id=10;
比较条件
> < >= <= !=
between....and 包括等于两边的
in like
is null/not null
select first_name,last_name,salary from employees where salary between 5000 and 8000;
select first_name,last_name,salary from employees where salary<=8000 and salary>=5000;
逻辑条件
and/or/not
排序
asc升序 默认的排序 空值在后面 数字从小到大 a——z 时间日期从早到晚
desc降序
select last_name,job_id,salary*12 salaries from employees order by salaries desc;
select first_name||' '|| last_name,job_id,salary*12 salaries from employees order by salaries desc;
select first_name||' '|| last_name,job_id,salary*12 salaries from employees order by job_id,salaries desc;
select job_id,rowid from employees; rowid 在oracle里面唯一的 是物理地址
###############################
语法：SELECT 语句1 [UNION|UNION ALL|INTERSECT|MINUS] SELECT 语句2
UNION
UNION操作符用于获取两个结果集的并集。当使用该操作符时，会自动出掉结果集中的重复行，并且会以第一列的结果进行排序。
SELECT ENAME,SAL,JOB FROM EMP WHERE SAL > 2500
union
SELECT ENAME,SAL,JOB FROM EMP WHERE JOB='MANAGER';
UNION ALL
UNION ALL操作符用于获取两个结果集的并集，但与UNION操作符不同，该操作符不会取消重复值，而且也不会以任何列进行排序。
SELECT ENAME,SAL,JOB FROM EMP WHERE SAL > 2500
UNION ALL
SELECT ENAME,SAL,JOB FROM EMP WHERE JOB='MANAGER';
INTERSECT
INTERSECT操作符用于获取两个结果集的交集，当使用该操作符时，只会显示同时存在于两个结果集中的数据，并且会以第一列进行排序。
SELECT ENAME,SAL,JOB FROM EMP WHERE SAL > 2500
INTERSECT
SELECT ENAME,SAL,JOB FROM EMP WHERE JOB='MANAGER';
MINUS
把集合{x∣x∈A,且x∉B}叫做A与B的差集，记作A－B
MINUS操作符用于获取两个结果集的差集，当使用该操作符时，只会显示在第一个结果集中存在，在第二个结果集中不存在的数据，并且会以第一列进行排序。
SELECT ENAME,SAL,JOB FROM EMP WHERE SAL > 2500
MINUS
SELECT ENAME,SAL,JOB FROM EMP WHERE JOB='MANAGER';
###########################################################################
A B
1 abc
1 bcd
1 asdfg
select A,B from table group by A
你说这样查出来是什么结果,
A B
abc
1 bcd
asdfg
group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面
conn hr/hr
查询hr用户有神魔表
select object_name from user_objects where object_type='TABLE';
OBJECT_NAME
-------------------
AA
COUNTRIES
DEPARTMENTS
EMPLOYEES
JOBS
JOB_HISTORY
LOCATIONS
REGIONS
#####################
desc employees;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
#####################
desc departments;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
DEPARTMENT_ID NOT NULL NUMBER(4)
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER(6)
LOCATION_ID NUMBER(4)
####################
desc locations;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
LOCATION_ID NOT NULL NUMBER(4)
STREET_ADDRESS VARCHAR2(40)
POSTAL_CODE VARCHAR2(12)
CITY NOT NULL VARCHAR2(30)
STATE_PROVINCE VARCHAR2(25)
COUNTRY_ID CHAR(2)
####查询员工属于哪个部门
select a.employee_id,a.first_name||' '||a.last_name,b.department_id,department_name from employees a join departments b on a.department_id=b.department_id;（不加on条件是迪卡尔积）
####员工对应的部门和城市
####select a.employee_id,a.first_name||' '||a.last_name name,b.department_id,b.department_name,c.city from employees a join departments b on a.department_id=b.department_id join locations c on b.location_id=c.location_id;
####save c.sql
####! vim c.sql
rem query employees_name,department_name,city ####注释
set linesize 500;
col name for a20;
col department_name for a30;
col city for a30;
select a.employee_id,a.first_name||' '||a.last_name name,b.department_id,b.department_name,c.city from employees a join departments b on a.department_id=b.department_id join locations c on b.location_id=c.location_id
/
####@c.sql
####所有的员工对应的部门
select a.employee_id,a.first_name||' '||a.last_name name,b.department_id,b.department_name from employees a left join departments b on a.department_id=b.department_id ;
--(+)放在行数少的一端，只能用到左连接和右连接，不能用到全连接
select a.last_name || ' ' || a.first_name names,
b.department_name department_name
from employees a, departments b where a.department_id=b.department_id(+);
/
#######所有的部门对应的员工
select a.employee_id,a.first_name||' '||a.last_name name,b.department_id,b.department_name from employees a right join departments b on a.department_id=b.department_id ;
--(+)放在行数少的一端，只能用到左连接和右连接，不能用到全连接
select a.last_name || ' ' || a.first_name names,
b.department_name department_name
from employees a, departments b where a.department_id(+)=b.department_id;
/
###查每个员工对应的上司
select a.employee_id,a.first_name||' '||a.last_name employee_name,b.employee_id manger_id,b.first_name||' '||b.last_name manager_name from employees a join employees b on a.manager_id=b.employee_id;
###########
组函数又称多行函数，把多行当作一个整体进行处理
avg()
sum()
max()
min()
distinct表示值不重复
group by
having
组函数使用原则：
1.所有组函数忽略空值 为了用一个值代替空值 用nvl nvl2 coalesce函数
2.使用group by 分组时 oracle默认使用升序排列
####统计每个部门的平均工资,最小,最大工资
select department_id,max(salary) max_salary,min(salary) min_salary,round(avg(salary),2) svg_salary from employees group by department_id;
####统计每个部门有多少员工
select department_id,count(*) total_member from employees group by department_id;
####查询每个部门commission_pct的平均值>0.2 的部门,
nvl(n1,n2) n1不为空返回n1 ，为空返回n2
所有组函数忽略空值 先把空值换成0，nvl(commission_pct,0)
having后的条件不能用别名
select department_id,avg(nvl(commission_pct,0)) nvg_commission_pct from employees group by department_id having avg(nvl(COMMISSION_PCT,0))>0.2;
##########################
子查询
子查询是指嵌套在其他的sql语句中的select语句 也称为嵌套查询
查询从事（雇员编号为141的员工的工作类型）的所有员工
单行子查询
select employee_id,first_name||' '|| last_name employee_name from employees where job_id=(select job_id from employees where employee_id=141);
多行子查询
---in
select employee_id,first_name||' '|| last_name from employees where job_id in (select job_id from employees where employee_id in (141,142));
--any
select employee_id,first_name||' '|| last_name from employees where salary< any(select salary from employees where job_id='IT_PROG') and job_id!='IT_PROG';
--all
select employee_id,first_name||' '|| last_name from employees where salary< all(select salary from employees where job_id='IT_PROG') and job_id!='IT_PROG';
####################################
conn scott/tiger
查寻所有表
select object_name from user_objects where object_type='TABLE';
OBJECT_NAME
------------------------------------------------------------------------------
BONUS 奖金表
DEPT 部门表
EMP 员工表
SALGRADE 薪水等级
查询表字段
#####################
desc bonus;
Name Null? Type
---------------------------------------- -------- ---------------------------
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
SAL NUMBER
COMM NUMBER
#######################
desc dept;
Name Null? Type
---------------------------------------- -------- ---------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
##################################
desc emp;
Name Null? Type
---------------------------------------- -------- ---------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
####################################
desc salgrade;
Name Null? Type
---------------------------------------- -------- ---------------------------
GRADE NUMBER
LOSAL NUMBER
HISAL NUMBER
1,列出至少有3个员工的所有部门名称和人数。
select a.dname, b.counts from dept a join (select deptno,count(*) counts from emp group by deptno having count(*)>=3) b on a.deptno=b.deptno;
select b.deptno,b.dname,count(*) counts from emp a join dept b on a.deptno=b.deptno group by b.deptno having count(*)>0;
2,列出薪水比"SMITH"多的所有员工。
select empno, ename from emp where sal > (select sal from emp where ename='SMITH');
3,列出所有员工的姓名及其直接上级的姓名。
select a.ename empname ,b.ename mgrname from emp a join emp b on a.mgr=b.empno;
4,列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称。
select a.empno,a.ename,c.dname
from emp a join emp b on a.mgr=b.empno
join dept c on a.deptno=c.deptno
where a.hiredate < b.hiredate;
5,列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
select a.empno, a.ename,a.sal,a.mgr,a.hiredate,a.job,b.deptno,b.dname from emp a right join dept b on a.deptno=b.deptno;
###6,列出所有job=CLERK的姓名及其部门名称，部门的人数。
select a.ename,b.dname,c.counts from emp a join dept b on a.deptno=b.deptno join (select deptno,count(*) counts from emp group by deptno ) c on c.deptno=a.deptno where a.job='CLERK';
###7,列出最低薪水大于1500的各种工作及此从事此工作的全部雇员人数。
select job,count(ename) from emp group by job having min(sal)>1500;
8,列出在部门"SALES"（销售部）工作的员工的姓名，假定不知道销售部的部门编号。
select a.ename from emp a join dept b on a.deptno=b.deptno where b.dname='SALES';
9,列出薪水高于公司平均薪水的所有员工，所在部门，上级领导，公司的工资等级。
方法一：select * from salgrade;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
select a.ename,b.ename mgr_name,c.dname,a.sal,
case
when a.sal between 700 and 1200 then 1
when a.sal between 1201 and 1400 then 2
when a.sal between 1401 and 2000 then 3
when a.sal between 2001 and 3000 then 4
when a.sal between 3001 and 9999 then 5
end "salgrade"
from emp a left join emp b on a.mgr=b.empno left join dept c on c.deptno=a.deptno where a.sal>(select avg(sal) from emp);
方法二：
select a.ename empname,b.ename mgrname,c.dname,a.sal,d.grade
from emp a left join emp b on a.mgr=b.empno
join dept c on c.deptno=a.deptno
join salgrade d on a.sal between losal and hisal
where a.sal > (select avg(sal) from emp);
10,列出与"SCOTT"从事相同工作的所有员工及部门名称。
select a.ename, b.dname from emp a join dept b on a.deptno=b.deptno where a.job=(select job from emp where ename='SCOTT');
11,列出薪水等于(部门号=30中员工的薪水)的所有员工的姓名和薪水。
select ename,sal from emp where sal in (select sal from emp where deptno=30);
12,列出薪水高于在部门号=30工作的所有员工的薪水的员工姓名和薪水,部门名称。
select a.ename,a.sal,b.dname from emp a join dept b on a.deptno=b.deptno where a.sal >all (select sal from emp where deptno=30);
13,列出在每个部门工作的员工数量,平均工资和平均服务期限。
select a.dname,b.count,b.avg_sal,b.avg_time from dept a join (select deptno, count(*) count,avg(sal) avg_sal,avg(sysdate-hiredate) avg_time from emp group by deptno) b on a.deptno=b.deptno;
14,列出所有员工的姓名,部门名称和工资。
select a.ename,b.dname,a.sal from emp a join dept b on a.deptno=b.deptno;
15,列出所有部门的详细信息和部门人数。
select a.*,b.counts from dept a join
(select deptno, count(*) counts from emp group by deptno) b on a.deptno=b.deptno ;
16,列出各种工作的最低工资及从事此工作的雇员姓名。
select b.job,a.ename,b.min_sal from emp a join (select job,min(sal) min_sal from emp group by job) b on a.job=b.job;
##17,列出各个部门的MANAGER（经理）的最低薪水。
select a.dname,b.ename mgrname, b.min_sal from dept a join(select ename,deptno,min(sal) min_sal from emp where job='MANAGER' group by deptno ,ename) b on a.deptno=b.deptno;
18,列出所有员工的年工资，按年薪(salary+comm)从高到底排序。
select ename,(sal+nvl(comm,0))*12 sal_yes from emp order by sal_yes desc;
求每个部门的平均年工资
select deptno,avg(sal+nvl(comm,0))*12 avg_year_salary from emp group by deptno;
19,查出某个员工的上级主管，并要求出这些主管中的薪水超过3000
select a.empno,a.ename ,b.empno,b.ename,b.sal from emp a join emp b on a.mgr=b.empno where b.sal>3000;
20,求出部门名称中，带‘S’字符的部门员工的部门名称,工资合计,部门人数
select b.dname,a.sum_sal,a.count from (select deptno,sum(sal) sum_sal,count(*) count from emp group by deptno) a join dept b on a.deptno=b.deptno
where b.dname in (select dname from dept where dname like '%S%');
#######21.求平均薪水最高的部门，显示部门名称和部门平均薪水。
select a.deptno,c.dname,b.avg_sal from
(select round(avg(sal),2) avg_sal,deptno from emp group by deptno) a
join (select max(round(avg(sal),2)) avg_sal from emp group by deptno) b
on a.avg_sal = b.avg_sal join dept c on c.deptno=a.deptno;
忽略了平均工资一样的：
select a.dname,b.avg_sal from dept a join (select deptno,round(avg(sal),2) avg_sal from emp group by deptno order by avg_sal desc) b on a.deptno=b.deptno where rownum=1;
########################################
###1,请查询表DEPT中所有部门的情况。
select a.ename,a.job,a.mgr,a.sala.comm,a.hiredate,a,deptno,b.dname from (select ename,job,mgr,hiredate,sal,comm,deptno from emp group by deptno) a right join dept b on a.deptno=b.deptno ;
2,查询表DEPT中的部门号,部门名称两个字段的所有信息。
select deptno,dname from dept ;
3,请从表EMP中查询10号部门工作的雇员姓名和工资。
select ename, sal from emp where deptno=10;
4,请从表EMP中查找工种是职员CLERK或经理MANAGER的雇员姓名,工资。
select ename,sal from emp where job in ('CLERK','MANAGER');
5,请在EMP表中查找部门号在10－30之间的雇员的姓名,部门号,工资,工作。
select ename,deptno,sal,job from emp where deptno between 20 and 30;
6,请从表EMP中查找姓名以J开头所有雇员的姓名,工资,职位。
select ename,sal,job from emp where ename like 'J%';
7,请从表EMP中查找工资低于2000的雇员的姓名,工作,工资，并按工资降序排列。
select ename,sal,job from emp where sal<2000 order by sal desc;
8,请从表中查询工作是CLERK的所有人的姓名,工资,部门号,部门名称以及部门地址的信息。
select a.ename,a.sal,b.deptno,b.dname,b.loc from emp a join dept b on a.deptno=b.deptno where a.job='CLERK';
9,查询表EMP中所有的工资大于等于2000的雇员姓名和他的经理的名字。
select a.ename empname,b.ename mgrname from emp a left join emp b on a.mgr=b.empno where a.sal>=2000;
10,在表EMP中查询所有工资高于JONES的所有雇员姓名,工作和工资。
select ename,sal,job from emp where sal > (select sal from emp where ename='JONES');
####11,列出没有对应部门表信息的所有雇员的姓名,工作以及部门号。
select ename,job,deptno from emp where deptno not in (select deptno from dept);
12,查找工资在1000～3000之间的雇员所在部门的所有人员信息。
select ename ,sal,mgr from emp where deptno in (select deptno from emp where sal between 1000 and 3000);
13,雇员中谁的工资最高。
select ename from emp where sal=(select max(sal) from emp );
#####14,雇员中谁的工资第二高(考虑并列第一的情况，如何处理)
select ename from (select ename ,sal from (select * from emp order by sal desc) where rownum<=2 order by sal) where rownum<=1;
15,查询所有雇员的姓名,SAL与COMM之和。
select ename ,sal+nvl(comm,0) from emp;
###16,查询所有81年7月1日以前来的员工姓名,工资,所属部门的名字
select a.ename,a.sal,b.dname from emp a join dept b on a.deptno=b.deptno where a.hiredate<to_date(19810701,'yyyy-mm-dd');
17,查询各部门中81年1月1日以后来的员工数
select a.dname,b.counts from dept a join (select deptno,count(*) counts from emp where hiredate > to_date(19810101,'yyyy-mm-dd') group by deptno) b on a.deptno=b.deptno;
18,查询所有在CHICAGO工作的经理MANAGER和销售员SALESMAN的姓名,工资
select a.ename,a.sal from emp a join dept b on a.deptno=b.deptno where a.job in ('MANAGER','SALESMAN') and b.loc='CHICAGO';
19,查询列出来公司就职时间超过24年的员工名单
select ename from emp where (sysdate-hiredate)>24*365;
####20,询于81年来公司所有员工的总收入(包括SAL和COMM)
select sum(sal+nvl(comm,0))*12 sum_year from emp where to_char(hiredate,'yyyy')=1981;
21,查询显示每个雇员加入公司的准确时间，按yyyy-mm-dd hh24:mi:ss显示。
select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') hiredate from emp ;
#######22,查询公司中按年份月份统计各地的录用职工数量
select to_char(a.hiredate,'yyyy-mm'),b.loc,count(*) total from emp a join dept b
on a.deptno=b.deptno group by to_char(a.hiredate,'yyyy-mm'),b.loc;
23查询列出各部门的部门名和部门经理名字
select a.ename,a.deptno,b.dname from emp a join dept b on a.deptno=b.deptno where a.job='MANAGER';
24,查询部门平均工资最高的部门名称和最低的部门名称
select dname from dept where deptno=(select deptno from (select deptno from emp group by deptno order by avg(sal) ) where rownum<=1)
union select dname from dept where deptno=(select deptno from (select deptno from emp group by deptno order by avg(sal) desc ) where rownum<=1);
25,查询与雇员号为7521员工的最接近的在其后进入公司的员工姓名及其所在部门名
select b.ename e_name,c.dname d_name
from
(select min(hiredate) hiredate
from emp
where hiredate >
(select hiredate
from emp
where empno='7521')) a,
emp b,
dept c
where a.hiredate=b.hiredate and b.deptno=c.deptno;
转载于:https://my.oschina.net/u/1458120/blog/225665
1657
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
