数据库--SELECT语句（上篇）

最新推荐文章于 2021-01-06 09:52:26 发布

ノラガミ

最新推荐文章于 2021-01-06 09:52:26 发布

阅读量216

点赞数

本文链接：https://blog.csdn.net/weixin_45407027/article/details/101620866

版权

数据库–SELECT（上篇）

表

表是从属于用户的 ,查询表(用户名.表名)，当前用户查询自己的表时，用户名.可以省略，其他用户查询别的用户表，不能省略，同时必须存在权限。
表是逻辑表(概念表)，不是物理表
- 块(8k) —>区(连续块)–>段(连续区) -->表(多个段) ，数据段不全是表，表一定是数据段。
表结构：
- 表名、字段(名称+类型+约束)、记录

三范式

在设计数据库时，存在行业的标准，这个标准也称为条件，即范式 Normal Form。一般遵循三个条件即可，也就是”三范式”(3NF)：

1 NF：是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式(1NF)中表的每一行只包含一个实例的信息
2 NF：是在第一范式(1NF)的基础上建立起来的，即满足第二范式(2NF)必须先满足第一范式(1NF)。第二范式(2NF)要求数据库表中的每个实例或行必须可以被唯一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。如emp表中加上了员工编号(empno)列，因为每个员工的员工编号是唯一的，因此每个员工可以被唯一区分。这个唯一属性列被称为主关键字或主键、主码。同时要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。即第二范式就是非主属性非部分依赖于主键
3 NF:必须先满足第二范式(2NF)。3NF要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。例如，存在一个部门信息表，其中每个部门有部门编号(deptno)、部门名称、地址等信息。那么员工信息表(emp)中列出部门编号后就不能再将部门名称、部门地址等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式(3NF)也应该构建它，否则就会有大量的数据冗余。即第三范式就是属性不依赖于其它非主属性。
简而言之，最终的目的避免数据重复冗余，1NF：列不可再分，最小原子(避免重复)；2NF：主键依赖(确定唯一)；3NF：消除传递依赖(建立主外键关联拆分表)

SELECT

SELECT [DISTINCT] {*, COLUMN, ALIAS...}
	FROM TABLE ALIAS
	WHERE 行过滤条件
	GROUP BY 分组字段
	HAVING 组过滤条件
	ORDER BY 排序字段;

执行顺序：from --> where --> group by --> having --> select --> order by

查询列（字段）

SELECT * FROM 表名;  查询某个表中所有的记录的所有字段信息
SELECT 列名 FROM 表名;  查询某个表中所有的记录的指定字段信息
SELECT 列名1,列名2 FROM 表名;  查询某个表中所有的记录的字段1 字段2
SELECT distinct 列名 FROM 表名;  去除重复记录
SELECT 表达式 FROM 表名;  查询表达式
SELECT xxx as 别名 FROM 表名表别名；  使用别名
注意：
- as ：字段别名可以使用as;表别名不能使用as
- " "：原样输出，可以存在空格与区分大小写
- 使用’ '表示字符串( 注意区分" " ) ，拼接使用 ||
伪列：不存在的列，构建虚拟的列
虚表：用于计算表达式，显示单条记录的值

null：null 遇到数字参与运算的结果为null；遇到字符串为空串，运算结果还是原串

处理null值：nvl(参数1,参数2) --> 如果参数1是null，函数的结果就为参数2；如果参数1不为null，结果就为参数1

--注释
--select *|字段名字1 (as) 别名1,字段名字2 别名2..  from 表名 别名;    select 后的内容称为结果集
--1)
--查询|检索|获取 所有员工的所有信息
select * from emp;

--2)
--查询所有的员工名字
select ename from emp;

--3)
--查询所有的员工编号和员工名称,员工上级的编号
--要查询的数据: empno, ename, mgr
--数据的来源: emp
--条件: 无
select empno,ename,mgr from emp;

--4)
--查询所有员工所在的部门编号
select deptno from emp;
--所有的部门编号
select deptno from dept;

--5)
--distinct 去重
--查询出所有员工所在的部门的部门标号,每个部门编号只出现一次
select distinct deptno from emp;

--6)
--查询出所有存在员工的部门的部门编号,以及员工名称
--需要结果集中的多条数据一模一样才能去重
select distinct deptno,ename from emp; 

--7)
--查询表达式   结果集中字段名使用表达式作为名字   字段的值使用表达式的结果
select distinct 123+456,123*456 from emp; 

--8)
--查询所有员工的名称(别名为:名字),员工编号(编号),薪资 salary
--""中内容原封不动的显示,不是字符串''
select ename "姓 名", empno as 编号, sal "salary" from emp e;

--9)
--字符串''
--原样输出""
select distinct 'haha' "haha" from emp;

--10)
--字符串拼接  ||
select distinct 'ab'||'-cd' from emp;
select 'sxt_'||ename 员工 from emp;

--11)
--伪列 :非表结构中真实存在的列就是伪列  表达式|字符串|数字
select 1 from emp;

--12)
--虚表
--dual 没有数据,没有字段
select 123*456 from dual;

--13)
--给每一个员工在原来的基础上+1块钱奖金,
--null 空  null和数字运算结果还是null
select comm+1 from emp;
--null 空  null和字符串运算结果还是原串
select comm||'1' from emp;
--处理null值  nvl(参数1,参数2) 如果参数1是null,函数的结果就为参数2,如果参数1不为null,结果就为参数1
select nvl(comm,0)+1 from emp;

--select ... from 数据来源;
--select后就是结果集 数据的集合
select * from emp;
select * from dept;
select deptno,dname from (select * from dept);  --查询嵌套查询

查询行（记录）

按条件查询，通过 where 过滤行记录：

= 、 >、 <、 >=、 <=、 !=、 <>、 between and
and 、or、 not
union、 union all、 intersect 、minus
null：is null、 is not null、not … is null
like :模糊查询 % 、 _ 、escape(‘单个字符’)

in 、 exists(难点) 及子查询

-- 按条件查询  过滤行记录
-- select ... from .. where 过滤行记录条件; 
--执行顺序: from where select先拿出一条数据来判断是否满足where的条件,如果满足就保留在结果集中给获取相应字段的值
-- 查询20部门的员工信息
select empno,ename,job from emp where deptno=20;

-- >  <  >=  <=  =  !=  <>(不等)
-- 查询工资大于1000的员工的姓名 工作岗位  工资   所属部门编号
--查询的数据: 姓名ename, 工作岗位job, 工资sal,部门deptno
--数据的来源: emp 员工表
--条件:工资大于1000  sal>1000
select ename,job,sal,deptno from emp where sal>1000;

-- 查询不在20部门工作的员工信息
select * from emp where deptno != 20;
select * from emp where deptno <> 20;
select * from emp where not deptno = 20;  --not 取反

-- 查询员工的年薪大于20000的 员工名称、岗位 年薪   
--数据:员工名称、岗位 年薪   
--来源: 员工表
--条件: (月薪+奖金)*12 年薪大于20000的
select ename,job, (sal+ nvl(comm,0))*12 from emp where (sal+ nvl(comm,0))*12 > 20000;
--在where中不能使用select后的字段别名,因为执行流程问题,先执行where在执行select
--先把所有的年薪都计算出来,再对这些已知的年薪进行过滤
select ename,job, (sal+ nvl(comm,0))*12 from emp;
select *
  from (select ename, job, (sal + nvl(comm, 0)) * 12 nianxin from emp) --自己查询的结果集,不是真实存在的表
 where nianxin > 20000; --别名可以使用

--or and not
--between 值1 and 值2 两者之间  值1<值2   >=  <=
--查询 工种为’SALESMAN’的员工信息 （注意 内容区分大小写）
SELECT * FROM EMP WHERE JOB='SALESMAN';

--检索 工资 2000, 3000员工名称 岗位 工资
SELECT ENAME,JOB,SAL FROM EMP WHERE SAL=2500 or SAL=3000;

--工资在2000到3000之间的员工信息  sal>=2000 and sal<=3000
select * from emp where sal>2000 and sal<3000;
select * from emp where sal between 2450 and 3000;

---查询 岗位 为 CLERK 且部门编号为 20的员工名称 部门编号，工资
select ename,deptno,sal from emp where deptno=20 and job='CLERK';

-- 查询 岗位 为 CLERK 或部门编号为 20的员工名称 部门编号，工资
select ename,deptno,sal from emp where deptno=20 or job='CLERK';

--查询 岗位 不是 CLERK 员工名称 部门编号，工资
select ename,deptno,sal,job from emp where job != 'CLERK';
select ename,deptno,sal,job from emp where job <> 'CLERK';
select ename,deptno,sal,job from emp where not job = 'CLERK';

--集合 Union、Union All、Intersect、Minus
select * from emp where sal>1500
Union 
select * from emp where not comm is null;

--查询显示存在雇员的所有部门号。
select deptno from dept
Intersect
select distinct deptno from emp;

--查询显示不存在雇员的所有部门号。
select deptno from dept
Minus
select distinct deptno from emp;

--like 模糊匹配 经常用于与%, _ 配合使用
--% 任意个任意字符
--_ 一个任意字符
--查询员工姓名中包含字符A的员工信息
select * from emp where ename like '%A%';

--可以实现精确匹配,但是效率低
select * from emp where ename like 'ALLEN';

--查询员工姓名中第二个字母为A的员工信息
select * from emp where ename like '_A%';

--测试数据
insert into emp(empno,ename,sal) values(1000,'t_%test',8989);
insert into emp(empno,ename,sal) values(1200,'t_tes%t',8000);
--当修改数据库中表中的数据信息时,增删改操作时,会自动开启事务,允许回滚(后退),提交(更新)
rollback;
commit;

--escape('单个字符')指定转义符
--查询员工姓名中包含字符%的员工信息
select * from emp where ename like '%a%%' escape('a');
--"\""

-- in (值1,值2,值3...)
-- 查询工资为 1500， 2000， 2500， 5000的员工的信息
select * from emp where sal=1500 or sal=2000 or sal=2500 or sal=5000;
--当对某些定值判断时,满足其中任意一个值行的时候,   in (值1,值2,值3...)
select * from emp where sal in(1500, 2000, 2500, 5000);

--查询部门名称为SALES 或 ACCOUNTING的部门编号
select deptno from dept where dname in('SALES','ACCOUNTING');
-- 查询10 部门和30部门的员工信息
select * from emp where deptno in(10,30);

--子查询  
--部门名称为 SALES 或 ACCOUNTING 的雇员信息
select *
  from emp
 where deptno in
       (select deptno from dept where dname in ('SALES', 'ACCOUNTING'));

-- 查询工资等级为2的员工信息
--工资等级  工资等级表
select * from salgrade;
--查询出工资等级为2的最低薪资
select losal from salgrade where grade=2;  --1201
select hisal from salgrade where grade=2;  --1400
select *
  from emp
 where sal between (select losal from salgrade where grade = 2) and
       (select hisal from salgrade where grade = 2);

-- 查询 销售部（SALES） 中 工资大于1500的员工信息
-- 先查询出销售部的部门编号
select deptno from dept where dname='SALES';
select *
  from emp
 where deptno = (select deptno from dept where dname = 'SALES')
   and sal > 1500;
   
-- 销售部中的所有员工信息
select * from emp where deptno = (select deptno from dept where dname = 'SALES');
select *
  from (select *
          from emp
         where deptno = (select deptno from dept where dname = 'SALES'))
 where sal > 1500;

-- 查询工资比SMITH高的同一部门的员工信息
-- sal>SMITH的薪资  and deptno = SMITH的部门编号
select *
  from emp
 where sal > (select sal from emp where ename = 'SMITH')
   and deptno = (select deptno from emp where ename = 'SMITH');

select sal from emp where ename='SMITH';
select deptno from emp where ename='SMITH';

-- exists(了解) 存在即保留,存在即合法 select * from 数据来源 where 后 exists(结果集);  
select * from emp where exists (select deptno from dept where dname='haha');

select *
  from emp e
 where exists (select deptno
          from dept d
         where dname in ('SALES','ACCOUNTING')
           and e.deptno =d.deptno);  -- emp.deptno 指代每一次从emp表中拿出的这条要比较的数据的部门编号


-- 求'SALES','ACCOUNTING'部门以外的其他员工信息
select *
  from emp e
 where not exists (select deptno, dname
          from dept d
        where dname in ('SALES', 'ACCOUNTING')
          --不对  and e.deptno != d.deptno);
          and e.deptno = d.deptno);
                
--有奖金的员工的员工信息
--如果查询语句的where条件如果有多个,能走到后面的条件判断时已经满足前面的条件了
--从e1表中拿出每一条数据,跟e2表中满足了有奖金的员工的员工编号是否相同
--相同就保留,不相同就不保留
select empno, ename, sal
  from emp e1
 where exists (select empno, ename, sal, comm
          from emp e2
         where comm is not null
           and e1.empno = e2.empno);

--有奖金的员工同部门的员工信息
--执行流程  from where,满足where后的条件才能被保留在最终select中
select empno, ename, sal,deptno
  from emp e1
 where exists (select empno, ename, sal, comm,deptno
          from emp e2
         where comm is not null
           and e1.deptno = e2.deptno);

排序

使用 ORDER BY 排序，排序不是真实改变存储结构的顺序，而是获取的集合的顺序

升序：asc（默认可不写）
降序：desc
多字段：在前面字段相等时，使用后面的字段排序

空排序：nulls first|last

--查询所有的员工,按照工资降序排序
select * from emp order by sal desc;

--根据薪资降序排序,如果相同的数据,根据员工编号升序排序
select * from emp order by sal desc,empno asc;

--根据员工的奖金进行升序排序
select * from emp order by comm desc nulls first;

单行函数

日期函数
- 当前日期：sysdate；current_date
- 日期可以直接做加减
- add_months()
- last_day()
- next_day()
转换函数
- to_date(c,m)  字符串以指定格式转换为日期
- to_char(d,m)  日期以指定格式转换为字符串：如果出现中文，前后添加""

判定函数

decode (字段, 值1, 结果1, 值2, 结果2, … , 默认值)
case 字段 when 值1 then 结果1 when 值2 then 结果2 … else end

--当前日期  2019/9/26 14:16:58 工具显示的日期形式
select sysdate from dual;
select current_date from dual;

--日期可以直接做加减
--查看所有员工的入职日期
select ename,hiredate from emp ;

--查看所有员工的入职日期3天后的日期
select ename,hiredate,hiredate+3 from emp ;

--查看所有员工的转正日期 3个月转正 30*3
select ename,hiredate 入职日期,hiredate+30*3 转正日期 from emp;

--add_months()
select ename,hiredate 入职日期,add_months(hiredate,3) 转正日期 from emp;

--last_day()
select ename,hiredate 入职日期,last_day(hiredate) from emp;

--next_day(sysdate,'星期一') 下一个要过的星期一
select next_day(sysdate,'星期一') from dual;

select next_day(sysdate,'星期五') from dual;

--to_date(c,m)  字符串以指定格式转换为日期
select to_date('2019-9-27 14:24:41','yyyy-mm-dd hh24:mi:ss')+3 from dual;

--to_char(d,m)  日期以指定格式转换为字符串 :如果出现中文,前后添加""
select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24:mi:ss') from dual;

--判定函数 decode(字段,值1,结果1,值2 , 结果2,…, 默认值)
--查询部门相关信息,最后添加一个伪列,如果部门编号为10,显示部门编号的中文形式 + ,二十...
select deptno,dname,loc,decode(deptno,10,'十',20,'二十',30,'三十',40,'四十') from dept;

组函数|多行函数|聚合函数

count() sum() max() min() avg()

-- 统计一下一共有多少个员工
select count(empno) from emp;
select count(*) from emp;
select count(1) from emp;

-- 统计一共有几个部门 
select count(deptno) from dept;
select count(1) from dept;

-- 统计有员工存在的部门总数
select count(distinct deptno) from emp;
select count(distinct 1) from emp;

-- 统计20部门一共有多少人
select count(*) from emp where deptno=20; 
select count(1) from emp where deptno=20; 
select count(deptno) from emp where deptno=20; 

--sum()
-- 计算本公司每个月一共要在工资上花费多少钱
select sum(sal) from emp;

-- 计算20部门每个月的工资花销
select sum(sal) from emp where deptno=20;

-- 查询本公司的最高工资和最低工资
select max(sal),min(sal) from emp;

--查看30部门的最高工资和最低工资
select  max(sal),min(sal) from emp where deptno=30;
select max(sal),min(sal) from (select * from emp where deptno=30);

-- avg 平均工资
select avg(sal) from emp;

-- 请查询出 20部门的平均工资
select avg(sal) from emp where deptno=20;

-- 计算出所有员工的奖金总和
select sum(comm) from emp;

-- 统计有奖金的员工有几个  null值不参与计算
select count(comm) from emp;
select count(1) from emp where comm is not null;

--查询 最高薪水的员工姓名， 及薪水
--查询最高薪水
select max(sal) from emp;
select ename, sal from emp where sal = (select max(sal) from emp);

--查询每个部门的平均薪资
select avg(sal),deptno from emp group by deptno;

-- 查询工资低于平均工资的员工编号，姓名及工资
select empno,ename,sal from emp where sal<(select avg(sal) from emp);

--查看 高于本部门平均薪水员工姓名
select ename from emp e1 where sal > (select avg(sal) from emp e2 where deptno=e1.deptno);

分组

分组 group by 分组字段,分组字段
select 查询的数据 from 数据来源 where 行过滤条件 group by 分组字段 having 组过滤信息 order by 排序字段
顺序: from where group by having select order by

where 中不能使用组函数

-- 找出20部门和30部门的最高工资 
select max(sal),deptno from emp where deptno in (20,30) group by deptno;
select max(sal),deptno from emp group by deptno having deptno != 10;

-- 求出每个部门的平均工资
select avg(sal),deptno from emp group by deptno;

-- 求出每个部门员工工资高于1000的的平均工资 (只保留每个部门中薪资高于1000的员工,对他们求部门平均信息)
select avg(sal) from emp where sal > 1000 group by deptno;

-- 求出10和20部门部门的哪些工资高于1000的员工的平均工资
--条件: 1)10和20部门     2)sal>1000
select avg(sal),deptno from emp where deptno in(10,20) and sal>1000 group by deptno;
select avg(sal),deptno from emp where sal>1000 group by deptno having deptno in(10,20) order by deptno;

-- 找出每个部门的最高工资
select max(sal) from emp group by deptno;

-- 求出平均工资高于2000的部门编号和平均工资
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
--求出每组的平均薪资
select avg(sal),deptno from emp group by deptno;
--不推荐使用
select * from (select avg(sal) avg_sal,deptno from emp group by deptno) where avg_sal>2000;

--求每个部门的平均薪资,和部门编号
select deptno,avg(sal) from emp group by deptno;
--求最低平均薪资
select min(avg(sal)) from emp group by deptno;
--查询 最低平均工资的部门编号
select deptno
  from (select deptno, avg(sal) avg_sal from emp group by deptno)
 where avg_sal = (select min(avg(sal)) from emp group by deptno);

行转列

/*
id name course score
1 张三 语文 81
2 张三 数学 75
3 李四 语文 81
4 李四 数学 90
5 王五 语文 81
6 王五 数学 100
7 王五 英语 90
*/
create table tb_student(
  id number(4) ,
  name varchar2(20),
  course varchar2(20),
  score number(5,2)
);
insert into tb_student values(1,'张三','语文',81);
insert into tb_student values(2,'张三','数学',75);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(7,'王五','英语',90);
commit;
drop table tb_student cascade constraints;

-- 如果有分组，select后只能是分组字段,或者组函数,不能出现其他字段
-- 行转列
select name,
       min(decode(course, '语文', score)) 语文,
       max(decode(course, '数学', score)) 数学,
       min(decode(course, '英语', score)) 英语
  from tb_student
 group by name;