Oracle 基础学习笔记（三）基本查询、高级sql查询、子查询

Oracle 基础学习笔记（三）基本查询、高级sql查询、子查询

（一）基本查询SQL语法

select 字段 表达式 函数…
from 表名1，表2…
where 条件表达式
group by 字段
having 条件表达式
order by 字段

--基本语法格式；
SELECT [ ALL | DISTINCT ] [ * | [table.* | expr[alias] | view.*] [, [ table.* | expr[alias]]]...]
	FROM table [ alias ][ ,table[ alias ] ]...
	[ WHERE condition]
	[ GROUP BY expr [, expr] ...] 
	[ HAVING condition]
	[ ORDER BY expression  [ ASC | DESC ] ]

（二）语句执行顺序

执行顺序：form…select；

(三）知识梳理

3.单表查询

表结构如下

CREATE TABLE emp
  (
    empno    NUMBER(5),
    enname   VARCHAR(20),
    job      VARCHAR(20),
    salary   NUMBER(5),
    bonus    NUMBER(5),
    deptno   NUMBER(5),
    hiredate DATE,
    mgr      VARCHAR(20)
  );
CREATE TABLE dept
  (
    deotno   NUMBER(5),
    dname    VARCHAR(20),
    location VARCHAR(20)
  );
  select sysdate from dual;
   insert into emp values(1001,'Ann','sales',10000
  ,5000,10,'12-4月-11',1005);
  insert into emp values(1002,'Tom','developer',12000
  ,2000,20,'14-2月-10',1003);
  insert into emp values(1003,'Dio','sales',8000
  ,4000,10,'2-5月-15',1005);
  insert into emp values(1004,'Doom','clecker',5000
  ,null,30,'10-7月-11',1006);
  insert into emp values(1005,'Homelander','Manager',15000
  ,null,10,'14-1月-12',1006);
  insert into emp values(1006,'Storm','Manager',15000
  ,null,20,'12-9月-12',null);
  
   insert into dept values(10,'sales','北京');
   insert into dept values(20,'develop','天津');
    insert into dept values(30,'check','广州');
     insert into dept values(40,'option','上海');

1.查询全部

select * from emp;

SELECT 1 FROM student;
/*
1其实就是一个常量，查询到的所有行的值都是这个常量，这里可以将常量看做是一个临时列。
从效率上来说，SELECT临时列>SELECT索引列>SELECT普通列>SELECT *，
这是因为不用查询Oracle数据库的数据字典表，因此速度比较快
*/

2.聚合函数——（数组函数）

count(),sum(),max(),min();
--查询员工表中有多少条数据？
 select count(*) from emp;
 --查询10部门员工薪金最高是多少？最低是多少？平均薪金是多少？
--计算员工的总人数和总薪金；
select min(salary),max(salary),sum(salary)/count(*),avg(nvl(salary,0)) from emp;

select count(*),sum(salary) from emp;

3. 数据字典：

user_table是 只能读不能修改；

4.分组 group by

--select 后面出现的字段，如果没有被组函数应用，则必须出现在group by 后面；
--按部门计算，每个部门的最高最低薪金分别是多少？
select deptno,max(salary),min(salary) from emp group by deptno;
--按职位分组
select job,max(salary),min(salary),count(*) from emp group by job;

5.having :用于分组后的条件过滤；

--查询平均薪金大于7000的部门数据；
select deptno,avg(nvl(salary,0)) avg_sal from emp where deptno is not null
group by deptno having avg(nvl(salary,0))>7000;
--查询薪金总和大于20000的部门；
select deptno,sum(salary) from emp where deptno is not null
group by deptno having sum(salary)>20000;

6.BEWTEEN AND和NOT BEWTEEN AND

表示的一个闭区间，即BEWTEEN A AND B结构包括A和B在内。
BEWTEEN AND用于判断指定的条件项是否位于某个范围之内，BEWTEEN之后的表达式指定范围的最小值，AND之后的表达式指定范围的最大值。必须把最小值放在前面，否则查询不到正确的结果。比较的数据类型可以是字符型、数值型和日期时间型。

基本查询语句

• 单行子查询
• 多行子查询
• 多列子查询
• 关联子查询
• 嵌套子查询

（1）单行子查询

子查询不能包含order by。可以在where、having中。

//where中————查询年龄 小于 平均年龄的学生的学号和姓名
select id,name from student where age< (select avg(age) from student);

//语法错误，聚合函数应该在having中做比较
select id,name from student where age<avg(age);

//having中————查询课程为‘c002’的平均成绩 大于 所有学生的平均分
select cno,avg(score) from sc where cno='c002' group by cno 
having avg(score)>(select avg(score) from sc);

//from中————查询哪些员工的工资 高于 所任职位的平均工资
select ename,sal,avgjob from emp a,
(select avg(sal),job from emp group by job) b 
where a.job=b.job and a.sal>b.avgjob;

//错误写法，子查询返回的是一个表
select ename，工资 from 表名 where 工资>（select 职位，avg(工资) from 表名 group by 职位）;

（2）多行子查询

1. 使用IN操作符

//查询 各个职位中工资最高 的员工信息 (两个职位的最高工资相同时，只显示其中一个)
select empno,enname,job,sal from emp where sal in(select max(sal) from emp group by job)

//上面的改进版，由于查询多了个job，所以就算sal相同也会显示
select empno,enname,job,sal from emp where (sal,job) in(select max(sal),job from emp group by job)

2. 使用exists操作符

• 是否存在，返回真(true)或者假(false）。
• 返回存不存在
  1.exists 判断查询有没有返回数据，有 true 没有 false；
  2.exists 不关心子查询的结果，所有子查询select 后面是什么都可以；
  3.sql 执行顺序从主查询主开始，把主查询的参数传给子查询；
  4.如果数据中有null值not in 将不会返回结果，所有not in与not exists 不能在有null值情况下互换；

select empno,enname,sal from emp where exists(select * from dept where deptno = '40')

--查询那些人是其他人的经理；
select enname from emp emp1 where exists(select * from emp where mgr=emp1.empno);
select enname from emp emp1 where empno in(select distinct mgr from emp);

select enname from emp emp1 where not exists(select * from emp where mgr=emp1.empno);
select enname from emp emp1 where empno not in(select distinct mgr from emp);

3.使用all或any操作符

• all 返回列表中的每一个值
• all 为大于最大的（大于所有值）
• any 满足子查询中如何一个；
• any大于其中任意一个就行；

4. 集合操作

结果集操作：
1.两个结果集结构必须相同（列个数，顺序，类型一致）
2.union ，union all
union 自动去重； union all 不去重、
union 排序 union all不排序
在满足相同功能时，优先洗澡union all
3.intersect 求交集
4.minus 求差集；

1.union
用于合并两个或多个 SELECT 语句的结果集。UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2  

//可重复
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2  

2. intersect

• 求交集，两表相同的部分

SELECT column_name(s) FROM table_name1
intersect
SELECT column_name(s) FROM table_name2

--查询10部门，薪金高于10000的员工信息；
select * from emp where deptno=10 intersect select * from emp where salary>10000;

3. minus

• 求差集，两表不相同的部分

SELECT column_name(s) FROM table_name1
minus
SELECT column_name(s) FROM table_name2

高级SQL查询

多表连接查询

//两表联查
select a.sno,sname from student a join score c on a.sno=c.sno
//三表联查
select a.sno,sname,cname from student a join score c on a.sno=c.sno join course b on b.cno=c.cno

• 内连接
• 外连接
• 自然连接

1.内连接 inner join

• 语法格式

SELECT select_list
FROM table1
JOIN table2 USING（column1, column2…）;

表区分

• 检索两表的匹配行
  1. 等值连接

select a.sno,sname from student a join score c on a.sno=c.sno where a.age=20

2. 不等值连接

select a.sno,sname from student a join score c on a.sno=c.sno where a.age>20

3. 自然连接

• 通过相同的列把两个表连接起来，不用写 on；

select a.sno,sname from student natural join score
//等价于
select a.sno,sname from student a join score c on a.sno=c.sno
//等价于
select a.sno,sname from student join score using(sno)

2.外连接 outer join

语法格式:

SELECT select_list
FROM table1
LEFT \RIGHT [OUTER] JOIN table2 
ON table1.column = table2.column;

除了可以使用标准的SQL语法表示外连接外，Oracle数据库中还可以使用“(+)”运算符来表示一个连接是外连接。注意：无论左外连接还是右外连接，“(+)”都要放在没有匹配记录列值就被设置为空值的表的一端。

1. 左外连接 left join

• 两表匹配的行 + 左表中剩余的行

select a.sno,sname,cno from student a left outer join score c on a.sno=c.sno
//等价于
select a.sno,sname,cno from student a,score c where a.sno=c.sno(+)

2. 右外连接 right join

• 两表匹配的行 + 右表中剩余的行

select a.sno,sname,cno from student a right outer join score c on a.sno=c.sno
//等价于
select a.sno,sname,cno from student a,score c where a.sno(+)=c.sno

3.全连接 full join
语法格式：

SELECT select_list
FROM table1
FULL [OUTER] JOIN table2 
ON table1.column = table2.column;

select a.sno,sname,cno from student a full outer join score c on a.sno=c.sno

参考博客