Oracle数据库学习笔记 （四 select 高级查询【上】）

select * from emmm…出入门到放弃

目录

一、基本查询 (select)
	1.2 选择列表
	1.3 条件 + 逻辑 查询
	小测验 （一）
	1.4 查询结果排序
	小测试 （二）
二、联接查询
	2.1 交叉联接
		2.1.1 笛卡尔积
		2.1.2 等值联接
		2.1.3 自联接
		2.1.4 非等值联接
		decode 关键字使用
	2.2 内联接
	2.3 外联接
		2.3.1 左外联接 left outer join
		2.3.2 右外联接
		2.3.3 全外联接 FULL OUTER JOIN
		2.3.4 外联接
三、课后作业

DQL 进阶： 数据库查询语言，用来查询数据，包含 SELECT

• SELECT 是 SQL 语言中使用频率最高的语句
• SELECT语句具有强大的查询功能
• SELECT允许从一个或多个表中选择满足条件的一个或多个行或列的数据

一、基本查询 (select)

基本语法：select * fom 表名

select * from emp; -- 这句话才 Oracle 数据库中查询是有结果的
select * from dept;

1.2 选择列表

-- 1 最简单的查询 （查询全部列）
select * from emp 

-- 2 指定列查询，用逗号分隔
select empno,ename from emp 

-- 3 给列起别名
select empno 员工编号,ename 员工名字 from emp; 

-- 4 按职位 去掉重复的数据
select distinct  job from emp;

-- 5 字符串拼接，注意使用单引号 连接起来
select ename || '@gmai.com' from emp; -- result： [e-mail]

select ename || job from emp;

update emp set ename=ename || '@gmail.com' from emp; -- 直接把 员工姓名 改成 员工 + 邮箱的形式

select empno 员工编号,ename 员工名字,sal 员工薪水, sal*1.1 员工加薪之后薪水 from emp;

-- 字符串拼接：要使用字符串连接，利用连接字符 || 可拼接列及提示信息，用单引号括起来

-- 6 查询结果  姓名 is a 职位
select ename || ' is a ' || job as remark from emp;

-- 7 查询所有员工的姓名，月基本工资以及增长 10% 之后的月基本工资
select ename, sal as 月基本工资,sal * 1.1 as 增幅后的月基本工资 from emp

1.3 条件 + 逻辑 查询

• 使用条件查询，对特定的数据进行筛选工作
• select 语句中，使用 where 子句表示查询的条件
• 在 where 子句中，经常使用两种运算符，比较运算符和逻辑运算符

1. 比较运算符有
   • =、>、<、=、<=、>=、!=
2. 逻辑运算符

• and 、or 、not 、is null

--
select * from emp where hiredate>to_date('1981-4-2','yyyy-MM-dd')
--查询出1982年以后入职的人

select * from emp where comm is null --查询 奖金为 为空的 员工信息
 
select * from emp where comm is not null --查询 奖金 非空的 员工信息

select * from emp where sal>1500 and comm is not null--查询工资大于1500并且可以得到奖金的雇员信息

select * from emp where sal>1500 and sal<=3000 --查询工资小于2000并且没有奖金的员工信息

select * from emp where sal between 1500 and 3000 --在两者之间数值类型或者日期类型（between ... and ....）

-- 逻辑运算符 或者（or） 与 （and） 非 （not）
SELECT * from emp where empno=7369 or empno=7499

--in来做
SELECT * from emp where empno in(7369,7499) --数据会在里面的元素进行遍历操作

--- 模糊查询
--以T开头的
select * from emp where ENAME like 'T%' -- % 代表一个或者多个字符
--以T结尾的
select * from emp where ENAME like '%T'
--含有T
select * from emp where ENAME like '%T%'
-- _占位符
select * from emp where ename like '_L%' -- ‘_‘ 只能代表一个字符

--如果不加%表示的是第5个位置的字母只能是T，如果加了%表示的是第5个字母为T不管后面是否还有数据都会查出来
select * from emp where ename like '____T'

小测验 （一）

--查询出第三个字母是N的并且工资在1000-3000之间的员工有哪些
select * from emp where ename like '__N%' and sal between 1000 and 3000

--查询出是销售员的员工且在1981年3月份之后入职的员工有哪些
select * from emp where hiredate >=to_date('1981-1-1','yyyy-MM-dd') and job='SALESMAN'

--查询出部门编号是20的且没有奖金的员工且员工的名字的长度是5的员工有哪些
select * from emp where deptno=20 and comm is null and ename like '_____'

1.4 查询结果排序

基本语法 order by xxxx asc(desc) asc 升序， desc 降序

--升序
select * from emp order by sal asc
--降序
select * from emp order by sal desc
--不写默认升序
select * from emp order by sal

tips:
-- 使用 rownum 会生成一列 并不存在的序列，但是可以根据序列进行数据查询
```sql
select e.*,rownum from emp e;

--只能小于或者小于等于查询不可以(>、=、 >=) 查询
select e.*,rownum from emp e where rownum<2 ;

select e.*,rownum from emp e where rownum<=3 ; -- 查询 列为 3 的数据

--查询出员工里面 sal 前3名的员工信息
select e.*,rownum from emp e where rownum<=3 order by sal desc
select e.*,rownum from emp e order by sal desc where rownum<=3 ;

select * from emp order by sal desc

select e.*,rownum from (select * from emp order by sal desc) e where rownum<=3

-- 混合排序查找
select * from emp order by deptno asc,sal desc

小测试 （二）

--1:查询出在1980年5月份之后入职的员工有哪些

--2:查询出名字以S结尾且不属于20部门的员工有哪些
select * from emp where ename like '%S' and deptno!=20
--3:查询出没有奖金，且薪水在1800-2500之间的员工且不属于30部门的员工有哪些
select * from emp where sal between 1800 and 2500 and deptno!=30
--4:查询出比ADAMS员工的薪水高的员工有哪些?****
select sal from emp where ename='ADAMS'
select * from emp where sal>(select sal from emp where ename='ADAMS')
--5:查询出员工的名字的长度在3-4位的员工有哪些
select * from emp where ename like '___' or ename like '____'
select length(ename) from emp;
select * from emp where length(ename) between 3 and 4

--8:查询出比MARTIN入职晚的员工有哪些
select hiredate from emp where ename ='MARTIN'
select * from emp where hiredate>(select hiredate from emp where ename ='MARTIN')
--9:查询出和FORD同一工种且不属于部门30的员工有哪些
select job from emp where ename ='FORD'
select * from emp where job=(select job from emp where ename ='FORD') and deptno!=30

--10:查询在12月份入职的员工且中间三位字母是LER，且部门和JONE是同一部门的员工有哪些
select hiredate from emp

select to_char(hiredate,'mm') as nowMonth from emp
--aLERb
-- 不是以L开头的那么就防止了LER是最前面的 LLER
select * from emp where to_char(hiredate,'mm')=12 and ENAME   like '_MIT_'  and job =(select job from emp where ename='JONES')

二、联接查询

• 联接查询的前提是表与表之间是有关联的，也可以说是多表联级查询
• 多表联接查询的作用和分类
  作用：
• 通过联接查询可以将多个表作为一个表进行处理
• 当检索数据时，通过联接查询可检索出源于不同表的信息，提高用户操作的灵活性。

分类：

• 交叉联接
• 内联接
• 外联接

2.1 交叉联接

语法：

SELECT
{DISTINCT} | 字段名1 别名1.. .
FROM 表名1别名1 表名2别名2，
.. .
{WHERE条件表达式}
{ORDER BY排序字段1
ASC I DESC，.. .}

交叉联接分类：

• 笛卡尔积
• 等值联接
• 自联接
• 非等值联接

2.1.1 笛卡尔积

含义：两个集合中的每一个成员，都与对方集合中的任意一个成员有关联。即第一个表的行数乘以第二个表的行数等于笛卡尔积大小。

如进行多表查询

select * from emp,dept

返回结果 56，emp 有14 条，dept 有 4 条，总共为 14 x 4 条数据

2.1.2 等值联接

如何关联？

在 emp 表中有 deptno 字段，而在 dept 表中也有 deptno 字段，并且发现 emp 表中 deptno 的 取值范围否在 dept表中的 deptno 规定范围内，所以两张表 deptno 属于关联字段。
即：emp 总的 deptno = dept 中的 deptno、deptno是关联字段

作用：
eg：在多表查询中将关联字段加入 where 语句，即可消除笛卡尔积，此时称为 等值联接

语法：

select table1.column,table2.colunm
from table1,table2
where table1.column1 = table2.column2

-- 示例
select * from emp e,dept d where e.deptno = d.deptno -- 给列名取别名，使用起来更方便

2.1.3 自联接

自联接是数据库中经常要用到的连接方式，使用自联接可以将自身表的一个镜像当做另一个表来对待，从而得到一些特殊的数据

eg： 要求查询每个员工的姓名、工作、员工的直接上级领导的姓名
分析：员工的领导也是员工

select e.ename,e.job,m.ename from emp e,emp m where e.mgr = m.empno order by e.empno

2.1.4 非等值联接

非等值联接 ：现要求查询出每个雇员的姓名，工资，部门名称，工资在公司的等级(salgrade)，及领导姓名及工资所在公司的等级

分析:

1. 先确定工资等级表的内容(查询 salgrade 表)
2. 查询出每个雇员的姓名，工资，部门名称，工资在公司的等级
3. 在查询领导的姓名及工资在公司的等级。
   题解：

SELECT e.empno, e.ename，
e.sal, s.grade, d. dname，
m.enane, m.sal, ms. grade
FROM emp e, salgrade s, dept d, emp m, salgrade ms
WHERE e. deptno = d. deptno
AND e. sal BETWEEN s.losal AND s.hisal
AND e.mgr = m. empno
AND m. sal BETWEEN ms.losal AND ms. hisal ;

decode 关键字使用

语法： decode(表达式1,值1，返回值1，值2，返回值2…)

decode 关键字，相当于 switch case

-- decode 关键字学习 相当于 switch case
select e.ename,e.sal,s.grade,
decode(s.grade,
1,'第五等工资',
2,'第四等工资',
3,'第三等工资',
4,'第二等工资',
5,'第一等工资') as 工资等级
from emp e,salgrade s where e.sal between s.losal and s.hisal

select empno,
       ename,
         sal,
         case
            when sal >= 3000 then
                 'A'
            when sal >=1600 then
                 'B'
            when sal >= 1000 then
                 'C'
            else
                 'D'
            end salLevel
          from emp

2.2 内联接

含义：内连接只返回满足条件的数据

• 内连接是以具有关联关系 （相同名字）的列为连接条件的
• 可以使用 ON 子句指定额外的连接条件·
• 这个连接条件与其他条件是分开的
• ON 子句使语句具有更高的易读性

语法：

select table1.column,table2.column
	from table1
	inner join table2
		on (table1.column_name = table2.column_name)
	inner join table3
		on (table2.column_name = table3.column_name)

eg1: 查询员工信息

select * from emp e inner join dept d on (e.deptno = d.deptno)

eg2: 查询所有员工编号，员工姓名，部门名称，部门所在城市的记录

select e.empno,e.enmae,d.dname,l.city
	from emp e
	join dept d
		on d.deptno = e.deptno
	join location l
		on d.loc = l.location_id;

2.3 外联接

• 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行，这种连接成为左（或右）外联接
• 两个表在连接过程中除了返回满足连接条件的行以外还返回两个表中不满足条件的行,这种连接称为满外联接。

外链接分类

• 左连接(左外连接) LEFT JOIN、右连接(右外连接) RIGHT JOIN、满!外联接(全外连接) FULLJOIN

2.3.1 左外联接 left outer join

含义:两个表在连接过程中除了返回满足连接条件的行以外还返回左表中不
满足条件的行，这种连接称为左外联接。

语法：

SELECT tablel. column, table2 . columnn
FROM table1
LEFT [OUTER] JOIN table2
ON
(tablel. column name
: = table2. column name)
[LEFT OUTER JOIN table3
ON (table2 . column name = table3 . column name) ._]. ;

eg1： 查询所有员工及对应部门的记录，包括没有对应部门编号 deptno 的员工记录

select e.enmae,e.deptno,d.dname
	from emp e
	left outer join dept d
		on (e.deptno = d.deptno)

2.3.2 右外联接

• 右外联接 RIGHT OUTER KOIN
  含义：两个表在连接过程中除了返回满足连接条件的行以外还返回右表中不满足条件的行，这种连接称为右外联接。

SELECT tablel. column, table2. column
FROM table1
RIGHT 
[OUTER] JOIN table2
ON (table1. column name = table2 . colunn name)
[RIGHT OUTER JOIN table3
ON
(table2. column name = table3 . colunn name) ..].. ;
一

eg：查询所有员工以及对应部门的记录，包括没有任何员工的部门记录

select e.ename,e.deptno,d.dname from emp e right outer join dept d on (e.deptno = d.deptno)

2.3.3 全外联接 FULL OUTER JOIN

含义：两个表在连接过程中除了返回满足连接条件的行以外还返回左右表中不满足条件的行，这种连接称为右外联接。

语法：

SELECT table1. column, table2. column
FROM table1
FULL [OUTER] JOIN table2
ON (table1. column name = table2 . colunn name )
[FULL OUTER JOIN table3
ON (table2 . column name = table3 . column name) .. ;

eg：查询所有员工以及对应部门的记录，包括没有对应部门编号的 deptno 的员工记录和没有任何员工的部门记录

select e.ename,e.deptno,d.dname
	from emp e
	full outer join dept d
		on (e.deptn = d.deptno)

2.3.4 外联接

eg： 查询上级领导编号为 7839 的所有员工及对应部门的记录

select e.ename,e.deptno,d.dname
	from emp e
	join dept d
		on (e.deptno = d.deptno)
	AND e.mgr = 7839

三、课后作业

1. 查询出与 scott 相同的入职年份且不属于部门 30 的员工有哪些

select * from emp where ename = 'SCOTT';
select to_char(hiredate,'yyyy') from emp where ename = 'SCOTT'; -- 找到 scott 的入职年份
select * from emp where to_char(hiredate,'yyyy') = (select to_char(hiredate,'yyyy') from emp where ename = 'SCOTT') and deptno <> 30; 

2. 查询 job 为 销售员的薪资在 800-2500之间且名字长度是4位的员工有哪些

select * from emp where job = 'SALESMAN' and sal between 800 and 2500 and length(ename) = 4;

3. 查询除比 JONES 晚 6个月进去的员工且不属于部门 10 的员工有哪些

select to_char(hiredate,'yyyy'),to_char(hiredate,'mm') from emp where ename = 'JONES';
select * from emp where  to_char(hiredate,'yyyy')>(select to_char(hiredate,'yyyy') from emp where ename = 'JONES') and to_char(hiredate,'mm')>(select to_char(hiredate,'mm') from emp where ename = 'JONES') and deptno <> 10;

6:查询出按照员工的薪水前3-5名的员工信息****

select e.*,rownum from(select * from emp order by sal desc) e where rownum <=5 

7：查询出工龄在33年以上的员工有哪些****

select to_char(sysdate,'yyyy') from dual;
select ename from emp where to_char(hiredate,'yyyy')<=((select to_char(sysdate,'yyyy') from dual)-33);

–6:查询出按照员工的薪水前3-5名的员工信息****

–7：查询出工龄在33年以上的员工有哪些****