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)多行子查询
- 使用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)
- 使用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大于其中任意一个就行;
- 集合操作
结果集操作:
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