数据库SQL的查询（select）学习

最新推荐文章于 2023-03-19 22:23:39 发布

zhiruochujian1

最新推荐文章于 2023-03-19 22:23:39 发布

阅读量855

点赞数

分类专栏： SQL 文章标签： SQL数据库 sql的语法 select查询

本文链接：https://blog.csdn.net/zhiruochujian1/article/details/102964584

版权

SQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

数据库

本篇章基于SQL数据库的实现，内已经创建了表格：

所以仅供参考！

一、查询 select

1.注释

"- - " 注释

--注释
--select *|字段名1 (as) 别名,字段名2 别名,字段... from 表名;
--select 后是结果集

1）select

select 后是结果集，
*select |字段名1 (as) 别名,字段名2 别名,字段… from 表名;

--查询所有员工的名字
select ename from emp;
--查询所有员工的编号,名字,薪资
select empno, ename, sal from emp;
--查询所有部门的部门信息
select deptno, dname, loc from dept;
select * from emp;

--查询公司中所有的部门编号
--查询的数据:deptno
--数据的来源: dept,emp
select deptno from dept;

2）distinct 去重

必须是结果集中的多条数据完全相同才能去重

--distinct 去重
--必须是结果集中的多条数据完全相同才能去重
--有员工存在的部门的部门编号
select distinct deptno from emp;

-- 表达式
select distinct 3*3 from emp;
-- 计算器
select distinct 123*456*789 from emp;

3）伪列

不是表中真实存在的字段

--伪列 不是表中真实存在的字段
--字符串 ''
select distinct 'haha' "哈 哈" from emp;

4）字符串 ‘’

字符串 || 拼接
"" 不是字符串，是原封不动打印

--字符串 ''
select distinct 'haha' "哈 哈" from emp;
--字符串 ||拼接
--给所有员工的名称前添加一个'SXT'
select 'SXT'||ename 员工姓名 from emp;

5）别名

如果别名中出现空格等特殊符号,或者想要原封不动显示使用"" 中的内容原封不动使用

--别名  如果别名中出现空格等特殊符号,或者想要原封不动显示 使用""  中的内容原封不动使用
--""不是字符串
--给字段别名  字段名 (as) 别名
--给表其别名  表名 别名  切记:不能使用as
select ename 姓名,sal "薪 资",comm as "Sum" from emp e;

6）null 值

处理null值
nvl (参数1,参数2) 如果参数1为null,函数最终的结果为参数2 ,如果参数1不为null,结果还是参数1

--null 值
--处理null值  nvl (参数1,参数2) 如果参数1为null,函数最终的结果为参数2 ,如果参数1不为null,结果还是参数1
--查询所有员工的姓名,薪资,和奖金,在原奖金上多发100
select ename , sal, comm, nvl(comm,0)+100 "实际奖金" from emp;

--null值和数字运算还是null
select ename,sal,comm,comm+100 from emp;
--null值和字符串运算结果是原串
select ename,sal,comm,comm||100 from emp;

7）虚表 dual

--虚表 dual
select 3*3 from dual;
select sysdate from dual;

2.按条件查询

select 内容 from 表 where 条件;
执行流程: from-> where -> select
a) = 、 >、 <、 >=、 <=、 !=、 <>、 between and
b) and 、or、 not、
c) all()、some() 、any()

--查询20部门的员工信息
select * from emp where deptno = 20;
--查询工资大于1000的员工的姓名 工作岗位  工资   所属部门编号
--查询数据: ename, job, sal , deptno
--数据来源: emp
--条件: 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;
--查询员工的年薪大于20000的 员工名称、岗位 年薪
select ename,job,(sal+nvl(comm,0))*12 from emp where (sal+nvl(comm,0))*12>20000;
--先查询所有员工的年薪,判断
select ename,job,(sal+nvl(comm,0))*12 money from emp; --数据来源
select * from (select ename,job,(sal+nvl(comm,0))*12 money from emp) where money>20000;
--假设现在 900,1000,1100

--all()
--查询工资比我们三个人都高的那些员工的信息
select * from emp where sal>all(900,1000,1100);
-- 查询工资比我们三个人都低的那些员工的信息

--some() any()
-- 查询比我们中随便一个工资高的员工信息
select * from emp where sal> some(900,1000,1100);
select * from emp where sal> any(900,1000,1100);
-- 查询 工种为’SALESMAN’的员工信息 （注意 内容区分大小写）
select * from emp where job = 'SALESMAN';
--检索 工资 2000, 3000员工名称 岗位 工资
select * from emp where sal=2000 or sal=3000;
select * from emp where sal in (2000,3000);
-- 工资在2000到3000之间的员工信息
select * from emp where sal>=2000 and sal<=3000;
select * from emp where sal between 2000 and 3000;

--查询 岗位 为 CLERK 且部门编号为 20的员工名称 部门编号，工资
select ename,deptno,sal,job from emp where job='CLERK' and deptno=20;
-- 查询 岗位 为 CLERK 或部门编号为 20的员工名称 部门编号，工资
select ename,deptno,sal,job from emp where job='CLERK' or deptno=20;
--查询 岗位 不是 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';

-- 查询 岗位 不为 CLERK 并且部门编号不为 20的员工名称 部门编号，工资
select ename,deptno,sal,job from emp where not( job='CLERK' or deptno=20);
select ename,deptno,sal,job from emp where job !='CLERK' and deptno!=20;
--存在佣奖金的员工名称
select ename,comm from emp where not comm is null;
select ename,comm from emp where comm is not null;
--不存在奖金的员工名称
select ename,comm from emp where comm is null;
-- 查询能领到奖金的员工姓名（>0） 
select ename,comm from emp where  comm >0;
--查询工资大于1500 或 含有佣金的人员姓名
select * from emp where sal>1500 or comm is not null;

3.单行函数

*select |字段名别名,… from 数据来源 where 条件 or|and 条件…

1）使用集合函数

Union，并集(去重) 对两个结果集进行并集操作，不包括重复行同时进行默认规则的排序；
Union All，全集(不去重) 对两个结果集进行并集操作，包括重复行，不进行排序；
Intersect，交集(找出重复) 对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；
Minus，差集(减去重复) 对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序

--select *|字段名 别名,.... from 数据来源 where 条件 or|and 条件...

--查询30部门的员工或 者薪资>2000
select * from emp where deptno = 30 or sal > 2000;
--使用集合函数
-- Union，并集(去重) 对两个结果集进行并集操作，不包括重复行同时进行默认规则的排序；
-- Union All，全集(不去重) 对两个结果集进行并集操作，包括重复行，不进行排序 ；
-- Intersect，交集(找出重复) 对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；
--Minus，差集(减去重复) 对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序
select * from emp where deptno = 30
Union
select * from emp where sal > 2000;

select * from emp where deptno = 30
Union All
select * from emp where sal > 2000;

--查询不存在员工的部门的部门编号
select deptno from dept
Minus
select distinct deptno from emp;

2）like 模糊匹配

%任意个任意字符 _一个任意字符
escape(‘a’); 转义字符

--like 模糊匹配  %任意个任意字符     _一个任意字符
select * from emp where ename like 'SMITH';  --精确匹配
--查询公司中员工姓名 以A开头的员工信息
select * from emp where ename like 'A%';
--查询公司中员工姓名 第二个字符为A的员工信息
select * from emp where ename like '_A%';
--查询公司中员工姓名 中存在%的员工   escape('a');  转义字符
select * from emp where ename like '%\%%' escape('\');

3）事务的提交和回滚

commit;
rollback;

--测试数据
insert into emp(empno,ename,sal) values(1000,'t_%test',8989);
insert into emp(empno,ename,sal) values(1200,'t_tes%t',8000);
--事务的提交和回滚
commit;
rollback;

4）函数

内置函数和自定义函数
单行函数 : 一条记录返回一个结果的
多行函数|组函数|聚合函数 : 多条记录返回一条结果的

-- 当前时间
select distinct sysdate from emp;
select sysdate from dual;
select current_date from dual;

-- 加减日期
-- 2天以后是几号
select sysdate+2 from dual;

-- 所有员工入职的3天前是几号
select hiredate,hiredate-3 from emp;

--add_months(日期对象,月份数)
-- 查询所有员工的试用期期到期（转正的日期） 3个月试用期
select hiredate,add_months(hiredate,3) from emp;

--months_between(大月份,小月份)
-- 查询所有员工到目前为止一共工作了几个月
select hiredate,months_between(sysdate,hiredate) from emp;

--last_day()
-- 查询当前月的最后一天
select hiredate,last_day(hiredate) from emp;

--next_day('星期三')
-- 下一个星期三是几号(即将要过的星期三)
select next_day(sysdate,'星期日') from dual;

--to_date(数据,格式)
--to_char(数据,格式)
-- 设定一个特定的时间（用一个特定的时间字符串转换为日期）
-- 设定一个时间   就是今天 '2018-9-5 16:18:25'
select to_date('2018/9/5 16:18:25','yyyy/mm/dd hh24:mi:ss')+3 from dual;

-- 将日期转为特定格式的字符串 
select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh12:mi:ss') from dual;

5）判定函数 decode

decode(判定字段,值1,结果1,值2,结果2,值3,结果3…(,默认结果))

--判定函数   
--decode(判定字段,值1,结果1,值2,结果2,值3,结果3....(,默认结果))
--给每个部门后后面添加一个伪列,如果10部门,伪列显示为十,二十,三十...
select deptno,dname,loc, decode(deptno,10,'十',20,'二十',30,'三十','四十')  中文部门编号 from dept;

4.in 和 exists

1）in

in (值1,值2,值3…) 当对一个字段的多个值进行等值匹配,可以使用in

-- in (值1,值2,值3...) 当对一个字段的多个值进行等值匹配,可以使用in
-- 查询工资为i 1500， 2000， 2500， 5000的员工的信息
select * from emp where sal=1500 or sal=2000 or sal=2500 or sal=5000;
select * from emp where sal in (1500 ,2000 ,2500 ,5000);

--部门名称为 SALES 或 ACCOUNTING 的雇员信息
--查询的数据 : 雇员信息
--数据的来源 : emp
--条件 :dname in ('SALES','ACCOUNTING')
--1)('SALES','ACCOUNTING')部门的部门编号
--查询的数据 :部门编号
--数据的来源 : emp,dept
--条件 : :dname in ('SALES','ACCOUNTING')
select deptno from dept where dname in ('SALES','ACCOUNTING');
--2)根据上一步中查询到的部门编号去员工表中查询在这个部门的员工信息
--deptno in (10,30)
select * from emp where deptno in (10,30);

--子查询

select *
  from emp
 where deptno in
       (select deptno from dept where dname in ('SALES', 'ACCOUNTING'));
       
--查询工资等级为 2的员工信息
--查询的数据 :员工信息
--数据的来源 : emp员工表
--条件 :工资等级 = 2
--工资等级为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);
       
select * from salgrade;
-- 查询 销售部（SALES） 中 工资大于1500的员工信息
select *
  from emp
 where deptno in (select deptno from dept where dname = 'SALES')
   and sal > 1500;

2）exists

--exists(结果集) 存在既保留,存在即合法
--select 数据 from 数据来源 where exists(结果集) 
--从from中拿出一条数据 去where 中进行判断 ,如果exists(结果集)中有值,当前这一条就保留|满足条件,如果exists(结果集)中没有值,当前这一条就不保留
--结果:查询出所有的结果
select *
  from emp
 where exists
 (select deptno from dept where dname in ('SALES', 'ACCOUNTING'));
 
--结果:空的
select * from emp where exists (select * from emp where deptno = 40);

--'SALES', 'ACCOUNTING'的员工信息
select *
  from emp
 where exists (select deptno
          from dept
         where dname in ('SALES', 'ACCOUNTING') --(10,30)
           and emp.deptno = dept.deptno);
           
--别名
select *
  from emp e
 where exists (select deptno
          from dept d
         where dname in ('SALES', 'ACCOUNTING') --(10,30)
           and e.deptno = d.deptno);
           
--结果:所有的数据
select *
  from emp e
 where exists (select deptno, dname
          from dept d
         where dname in ('SALES', 'ACCOUNTING') --(10,30)
           and e.deptno != d.deptno);
--结果:查询20部门员工信息  
select *
  from emp e
 where not exists (select deptno, dname
          from dept d
         where dname in ('SALES', 'ACCOUNTING') --(10,30)
           and e.deptno = d.deptno);
--           
select ename, sal
 from emp
where exists (select ename, sal from emp e2 where e2.sal >= 2000);

--查询所有的行记录
select empno,ename  from emp;  --推荐使用所有字段名而非*,因为效率高
select * from emp;
select * from emp where 1=1;
select * from emp where ename like '%';

5.聚合函数

组函数|聚合函数|多行函数
对结果集进行组函数计算，多行记录返回一个结果
count(条件) sum(条件) max() min() avg()

--排序:  先确定结果集然后对于结果集中的数据进行排序
--select 数据 from 数据来源 where 条件 order by 排序字段1,排序字段..;  desc 降序  asc升序(默认)
-- from where select order by
select * from emp where deptno = 30 order by sal desc,empno asc;
select * from emp where deptno = 30 order by comm nulls first;  --所有的nulls值最先first|最后显示last

--组函数|聚合函数|多行函数 : 对结果集进行组函数计算
--多行记录返回一个结果 
--count(条件) sum(条件) max() min() avg()
--注意: 组函数不能和非分组字段一起使用

-- 统计一下一共有多少个员工
select count(empno) from emp;
select count(deptno) from emp;
select count(*) from emp;
select count(1) from emp;  --伪列  相当于为每条数据的后面添加一个伪列字段 1

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

-- 统计有员工存在的部门总数
--查询有员工存在的部门编号的结果集,对这个结果集求个数
select count(distinct deptno) from emp;
select count(1)
  from dept
 where deptno in (select distinct deptno from emp);


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

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

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

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

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

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

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


-- 计算出所有员工的奖金总和  null 不参与运算
select sum(comm) from emp  where comm is not null;

-- 统计一共有多少个员工 null 不参与运算
-- 统计有奖金的员工有几个
select count(comm) from emp;


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

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

6.分组 group by

1）group by

--分组: group by 分组字段
--查询公式:select 数据 from 数据来源 where 行过滤条件 group by 分组字段1,.. having 过滤组信息(以组为单位过滤) order by 排序字段..;
--执行流程: from -- where --group by --having --select  -- order by
--注意:
--      1)select 后如果出现了组函数|分了组,组函数不能与非分组字段,可以与其他组函数或分组字段一起使用
--      2)where 后不能使用组函数   因为还没有组,执行流程问题       

--求出所有有员工存在的部门编号
select deptno from emp group by deptno;

-- 找出20部门和30部门的最高工资 
--20部门和30部门中的所有员工中的最高工资 
select max(sal) from emp where deptno in (30,20);
--找出20部门和30部门中每个部门的最高工资 
select max(sal),deptno from emp where deptno in (30,20) group by deptno;  --先过滤 后分组
select max(sal),deptno from emp group by deptno having deptno in (30,20); --先分组再过滤

-- 求出每个部门的平均工资
--数据: 每组的平均薪资
--来源: 员工表 
--条件: 一个部门一个部门求平均薪资  ,一个部门一个值  以部门为单位 如果不分组组函数对所有满足条件的数据进行计算,如果分组了,以组为单位
select avg(sal),deptno from emp group by deptno;

-- 求出每个部门员工工资高于1000的的部门平均工资
--数据: 部门平均工资
--来源: 员工表 
--条件: sal>1000 以部门为单位:按照部门进行分组
select avg(sal),deptno from emp where sal>1000 group by deptno;

-- 求出10和20部门部门的哪些工资高于1000的员工的平均工资
select avg(sal),deptno from emp where sal>1000 group by deptno having deptno in(10,20);
--不推荐使用,效率相对较低
select * from (select avg(sal),deptno from emp where sal>1000 group by deptno) where deptno in(10,20);

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

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

7.子查询

/*
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 * from tb_student;


--使用一条sql语句，查询每门课都大于80分的学生姓名
--数据: 学生姓名
--来源: tb_student
--条件: 1.学生考试科目数=一共有的科目数  and  2)这个人所有分数中最低分数>80
select name
  from tb_student
 group by name
having count(distinct course) = (select count(distinct course)
                                   from tb_student) and min(score) > 80;

select name,count(distinct course) from tb_student group by name;

--一共有几门课程
select count(distinct course) from tb_student;  --先去重再求个数

--求每个人的最低分
select min(score),name from tb_student group by name ;

--行专列
select name,max(decode(course,'语文',score)) 语文,min(decode(course,'数学',score)) 数学,max(decode(course,'英语',score)) 英语 from tb_student group by name;

--decode() 是单行函数
select name,decode(course,'语文',score) 语文,decode(course,'数学',score) 数学,decode(course,'英语',score) 英语 from tb_student;

8.rowid 和 rownum

--rowid 和 rownum 都是伪列
--rowid理解为记录在插入到数据库的表中时候就存在的数据的地址(对象的地址),其实不是地址,根据地址得到的值
--如果一个表中没有主键,没有不可重复的字段,可能会出现多条一模一样的数据,无法区分重复数据,可以根据rowid进行区分

select deptno,dname,loc,rowid from dept;
select empno,ename,rowid from emp;

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);

select * from tb_student;

--当一个表中有多条一模一样的数据的时候,实现去重,重复数据只保留一条
--查到要保留的数据
select id,name,course,score,min(rowid) from tb_student group by id,name,course,score; 
select * from tb_student where rowid in(select min(rowid) from tb_student group by id,name,course,score);

--查到要删除的数据
select *
  from tb_student
 where not
        rowid in
        (select min(rowid) from tb_student group by id, name, course, score);


--删除这些数据
delete from tb_student
 where not
        rowid in
        (select min(rowid) from tb_student group by id, name, course, score);


--rownum 结果集的序号 有一个结果集就有一个rownum select查到的结果集
--rownum 从1开始,每次+1
select deptno,dname,loc,rowid,rownum from dept;
select empno,ename,rownum from emp where rownum<=4;

--分页:在oracle中使用rownum.因为rownum规律可循,控制rownum序号从1开始,每次+1,方便判断
--查询
--一般如果根据主键字段进行排序,先排序后确定rownum
select deptno,dname,rownum from dept order by deptno desc;
--一般如果根据非主键字段进行排序,先确定rownum再排序
select deptno,dname,rownum from dept order by loc;

--保证一定先排序后确定rownum,在结果集的外面嵌套一层select,这个select的rownum肯定就是从1开始,根据这个有规律的,已确定的row进行判断操作就可以
select deptno,dname,rownum num from dept order by loc;
select rownum,deptno,dname,num from (select deptno,dname,rownum num from dept order by loc);

select empno,ename,sal,comm,rownum from (select empno,ename,sal,comm,rownum num from emp order by sal desc); 

--分页需求:
--i页数  num每页显示几个
--num= 3  i=4
--每一页要显示的数据的rownum   第一个:  rownum>=num*(i-1)+1                    最后一个为: row<=num*i
select *
  from (select empno, ename, sal, comm, rownum num2
          from (select empno, ename, sal, comm, rownum num
                  from emp
                 order by sal desc))
 where num2 >= 3 * (1 - 1) + 1
   and num2 <= 3 * 1;