Sql基础语句之DQL

最新推荐文章于 2023-06-08 08:37:04 发布

╰漠╯

最新推荐文章于 2023-06-08 08:37:04 发布

阅读量265

点赞数

分类专栏： oracle

本文链接：https://blog.csdn.net/qq_44694686/article/details/105641035

版权

oracle 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

文章目录

数据查询语言DQL

对数据表中的数据进行查询，只有select语句，但却是最复杂，功能最强的sql语句，查询是SQL语言的核心。

select 查询语句结构：

SELECT [ ALL | DISTINCT
  { * | expression | column1_name [ , column2_name ] [ , ... ] }
      [ INTO table_name]
  FROM { table1_name | ( subquery ) } [ alias ]
  [ , { table2_name | ( subquery ) } [ alias ] , ... ]
  [ WHERE condition ]
  [ CONNECT BY condition [ START WITH   condition ] ]  
        //connect by 搜索的查询描述
  [ GROUP BY expression [ ,... ] ]
  [ HAVING condition [ ,... ] ]
  [ { UNION | INTERSECT | MINUS } ]
  [ ORDER BY expression [ ASC | DESC ] [ , ... ] ]
  [ FOR UPDATE [ OF [ schema. ] table_name | view ] column ]
        //使用of子句选择对select的特定数据表进行加锁操作
  [ NOWAIT ] ; //更新锁定的表时不会等待锁的释放

注：from子句中为表名取别名时不能用as

where子句的条件支持表达式如下：
在这里插入图片描述
BETWEEN为闭区间 ：Between x and y 表示 [x, y]
Like 用占位符实现模糊查询，有两中占位符：

_ ：表示匹配指定位置的一个字符
% ：表示匹配从指定位置开始的任意个字符

IN 表示检索值是否在某个集合中：column_name in (v1,v2,v3)

select语句的执行顺序:

    (7) SELECT (8) DISTINCT (9) select_list　
    (1) FROM left_table
    (3) join_type JOIN right_table  (2) ON join_condition
    (4) WHERE where_condition
    (5) GROUP BY group_by_list
    (6) HAVING  having_condition  
    (10) ORDER BY  order_by_list

可以看出虽然select虽然是最先写的，但差不多是最后执行的。

Select基础查询

基本查询

select * from emp;  --查询emp表的所有员工信息

where 条件查询

--单一条件查询
select *
from emp
where sal > 1000
order by sal;

--多条件查询，多条件查询用可以用and、or进行连接条件
select *
from emp
where sal > 1000 and sal < 2500;

like模糊查询
使用时要和两个通配符一起使用：

_ ：可以替代一个字符
% ：可以替代多个字符

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

in 条件查询
条件列在in的集合中时才会查出信息

--查询10和20 部门员工信息并按照工资降序排序
select * 
from emp 
where deptno in(10,20) 
order by sal desc;

针对Null值的查询
is null可以查出null的记录信息
is not null 取反，查出不为null的信息

--查询mgr列值为null的信息
select empno, ename, job, mgr
from emp
where mgr is null;

order by子句
根据指定列排序，可以同时对多个列指定排序规则，多个列之间使用逗号( , )隔开。
首先按照第一个列的值进行排序，当第一个列的值相同时，再按照第二个列的值进行排序，以此类推

--对查询得到的结果首先按照hiredate列进行降序排列，然后再按照deptno列进行升序排列
select empno 雇员编号, ename as 雇员名称, hiredate as 受雇日期, deptno 部门编号
from emp
where empno>=7900
order by hiredate desc, deptno asc;

默认情况下把Null值看成最大值，升序时排在最后，降序时排在最前；在Null值排序时开发人员可以指定位置

--指定null值在首位
select *
from emp
order by mgr asc nulls first;

--指定null值在末位
select *
from emp
order by mgr desc nulls last;

distinct关键字
限定在检索结果中显示不重复的数据，对于重复值，只显示其中一个，使用时放在select子句的列名的前面，如果不指定，则默认使用all显示所有

--有重复行
select  deptno from emp;
--无重复行
select distinct deptno from emp;

select中使用一些简单运算
在SELECT语句中，不但可以对表和视图执行查询操作，还可以执行数学运算(如+、-、*、/)，也可以执行日期运算，也可以执行与列关联的运算。

--在select子句中的列名字段部分可以写算术运算
select 1+2+3+(4*5/6-7)+8+9 from dual;


--select中执行日期运算
select to_date('24-4月-2016')+2 from dual;


--null值进行运算时结果也为null，所以为null时要设置一个默认值
--  nvl(列，默认值) 函数作用是取列的值，若列为null则输出设定的默认值
select empno, ename, sal, comm, sal+nvl(comm, 0) as money
from emp
order by money desc;

连接运算符”||”
select语句中使用连接运算符”||”，能将字符串连接起来

--||连接符，可以拼接字符串，sql中不能用+拼接字符串
--  必须用单引号才表示字符串（反正双引号会报错）
select empno, ename||'的工资是：'||sal as 员工薪水
from emp;

别名
select子句中，在列名后面添加 ‘ as 别名 ’ 即可替换显示列名，as可以省略，但为表取别名则不能加as

--列的别名
select empno as 员工编号, ename 员工姓名
from emp;

使用聚合函数操作查询的字段
常用的select语句中的计算、统计函数：

min（列名）：求列值中最小值 min(age)
max（列名）：求列值中最大值 max(age)
avg（列名）：求列值的平均值 avg(chengji)
sum（列名）：求列值的和 sum(chengji)
count（列名）：统计列值的个 count([distinct]sid)
count（）：统计行的个数 count()

select avg(sal) as 全体平均工资 
from emp;

Select分组查询

group by子句
根据表中的某一列或某几列对表中的数据行进行分组，多个列之间使用逗号( , )隔开，根据多个列分组：首先根据第一列进行分组，然后在分出来的组中再按照第二列进行分组，以此类推。
分组后查询出的结果列，只能为分组依据列，或聚合函数的列，不能有其他的列，因为分组后查出来的是一个组，再写其他列名就不合适了。

--group by 分组查询
--以部门分组并查询平均工资
select deptno 部门号, avg(sal) as 平均工资
from emp
group by deptno;  

--这么写就会出错
select empno 员工号, deptno 部门号, avg(sal) as 平均工资
from emp
group by deptno;

having子句
having子句通常与group by子句一起使用，在完成对分组结果的统计后，可以使用having子句对分组的结果进行进一步的筛选。

如果在select语句中使用了group by子句，那么having子句将应用于group by子句创建的组
如果指定了where子句，而没有指定group by子句，那么having子句将应用于where子句的输出，并且这个输出被看作是一个组；
如果在select语句中既没有指定where子句，也没有指定group by子句，那么having子句将应用于from子句的输出，并且将这个输出看作是一个组。

select deptno, round(avg(sal), 2) as 平均工资
from emp
group by deptno
having avg(sal)>2000;

Select子查询

一个查询作为另一个查询的条件称为子查询，是将子查询的结果作为外部主查询的查找条件。
子查询常用于SELECT命令的WHERE子句中，但也可用于from子句，having子句，in、any、all、exists条件等。

子查询的类型：
子查询根据使用的操作符类型分为两类：

单行子查询：=、>、>=、<、<=、<>、!=
多行子查询：ALL、ANY、IN、EXISTS

含义就是子查询的结果为单个值，就是单行子查询，使用简单的比较操作符
结果为一个集合，就是多行子查询，使用集合操作符。

根据子查询返回结果分为下面3种子类型：

多列子查询：向外部的SQL语句返回多列。
关联子查询：引用外部的SQL语句中的一列或多列。在关联子查询中，可以使用EXISTS和NOT EXISTS操作符。
嵌套子查询：在子查询中包含有子查询。

注意：子查询需要使用括号 ( ) 括起来。

在where子句中的子查询结构：

SELECT  column_list
FROM  table_name
WHERE expression operator (
  SELECT  字段列表名    FROM  表名    WHERE  条件
  GROUP BY 字段   HAVING  限定条件  );

可以看到子查询没有group by子句，因为子查询中的分组并不会执行。

select *
from emp
where sal>(
    select sal from emp where empno=7934
);

having子句中使用子查询：
对分组进行过滤

select deptno, avg(sal) as 部门平均工资  
from emp
group by deptno
having avg(sal) >  (
    select avg(sal) from emp
);

any、all
使用前必须有一个单行操作符

select empno, ename, sal, deptno
from emp
where sal>any( --大于任一个即可
    select avg(sal)
    from emp
    group by deptno
);

select empno, ename, sal, deptno
from emp
where sal>all(  --必须大于所有
    select avg(sal)
    from emp
    group by deptno
);

多列子查询
指返回多列数据的子查询语句，一般在WHERE子句中使用多行操作符。（上面的子查询都是单列的）
两种方式：

成对比较：要求多个列的数据必须同时匹配。
非成对比较：通过指定连接关键字，例如AND或OR等，指定多个列的数据是否必须同时匹配。如果使用AND关键字，表示同时匹配，这样就可以实现与成对比较同样的结果；如果使用OR关键字，表示不必同时匹配。

--查询各个部门工资最高的员工信息,通过成对比较方式
select * 
from emp 
where (deptno, sal) in (
    select deptno, max(sal) from emp group by  deptno
); 

--查询各个部门工资最高的员工信息,通过非成对比较方式
select * 
from emp 
where deptno in(select deptno from emp) 
    and 
    sal in (select max(sal) from  emp group by deptno);

关联子查询
关联子查询会引用外部查询中的一列或多列。
实现原理：外部查询的每一行记录依次传递给子查询并应用，返回结果。

Inner & outer

--查询各个部门工资最高的员工信息,通过关联子查询方式
select * 
from emp outer 
where sal in (
    select max(sal) 
    from emp inner 
    where  outer.deptno=inner.deptno 
    group by deptno
);

--查询各个部门工资最高的员工信息,通过关联子查询方式(不用分组结果一样)
select * 
from emp e 
where e.sal = (
    selsec max(sal) 
    from emp 
    where e.deptno = emp.deptno
);

exists

select empno, ename, sal, hiredate
from emp e
where exists(
      select empno from emp
      where empno = e.empno
          and substr(hiredate, 8,2)='80'
);

嵌套子查询
嵌套子查询，指在子查询内部使用其他子查询。
嵌套子查询的嵌套层次最多为255层。
嵌套子查询一般在外层子查询的WHERE子句中

--两层嵌套的子查询
select *
from emp
where sal>(
    select max(avg(sal))
    from emp
    where deptno in (
        select deptno
        from dept
        where loc in ('NEW YORK' , 'CHICAGO')
    )
    group by deptno
);

from子句中的子查询

--from子句中的子查询被看作视图，并需要指定别名（不能用as）
select ename, job, sal
from emp e, (
    select deptno, avg(sal) avgsal
    from emp
    group by deptno
)temp_dept
where e.deptno=temp_dept.deptno
    and
    sal>temp_dept.avgsal;

insert、update、delete语句中使用子查询

--insert 要求查询字段与插入字段一一对应，且子查询不用()包裹。
insert into employee(id, name, title, salary)
   select empno, ename, job, sal from emp;

--update 除在where中使用外可以在set子句使用
update emp set sal=(
       select avg(sal) from emp)
    where empno=7839;

--delete 在where子句中使用子查询
delete from emp
where deptno in(
      select deptno
      from dept
      where loc='NEW YORK'
);

连接查询

简单连接

select empno, ename, sal, e.deptno, d.deptno, dname
from emp e, dept d
where e.deptno=d.deptno;

inner join内连接：
关键字inner可省

等值连接：属性名必须相同，用=号比较被连接的值，相等则连接。

select empno, ename, sal, d.deptno, dname
from emp e inner join dept d on e.deptno=d.deptno
where dname='SALES';

不等值连接：使用除=号外的比较符进行连接，满足条件则连接。

select empno, ename, sal, grade
from emp e inner join salgrade s
     on e.sal between s.losal and s.hisal;

自然连接：
无需显示指定连接条件，自动在两个表中寻找列名和数据类型都相同的字段，根据找到的字段进行连接

select ename, dname
from emp natural join dept;

outer join外连接
outer关键字可省略，除显示满足条件的行外，还显示不满足条件的左、右、两侧表数据，具体哪个根据关键字确定，无数据的补null值

left outer join 左外连接：将显示左表的所有行

select empno, ename, sal, s.grade
from emp e left join salgrade s
     on e.sal between s.losal and s.hisal;

right outer join 右外连接：显示右表的所有行。

select distinct e.deptno, d.deptno
from emp e right join dept d
     on e.deptno=d.deptno;

full outer join 全外连接：显示两侧所有行

select distinct e.deptno, d.deptno
from emp e full outer join dept d
     on e.deptno = d.deptno;

cross join 交叉连接
可以实现两个表的交叉连接，所得到的结果将是这两个表中各行数据的所有组合，即这两个表所有数据行的笛卡尔积。

select empno, ename, e.deptno, dname
from emp e cross join dept d
where e.deptno=30 and d.dname='SALES';

集合查询

union 操作符获取两个结果集的并集
all 关键字表示合并所有行，不加all则只保留重复行的一行

select empno, ename, sal, deptno
from emp
where empno>7800
union all
select empno, ename, sal, deptno
from emp
where deptno=30
order by deptno asc;

intersect 操作符获取两个结果集的交集

select empno, ename, sal, deptno
from emp where empno>7800
intersect
select empno, ename, sal, deptno
from emp
where deptno=30;

minus 操作符取结果集间的差集

select empno, ename, sal, deptno
from emp where empno>7800
minus
select empno, ename, sal, deptno
from emp
where deptno=30;

╰漠╯

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Sql基础语句之DQL

文章目录Select基础查询Select分组查询Select子查询连接查询集合查询数据查询语言DQL对数据表中的数据进行查询，只有select语句，但却是最复杂，功能最强的sql语句，查询是SQL语言的核心。select 查询语句结构：SELECT [ ALL | DISTINCT { * | expression | column1_name [ , column2_name ] ...
复制链接

扫一扫