【sql查询与优化】1.单表查询

简介:
注:以下所有sql案例均取自"oracle查询优化改写技巧与案例"丛书。

EMP表的详细:


1.查询表中所有的行与列

查询所有信息,
SQL> select * from emp;

     EMPNO ENAME                JOB                       MGR HIREDATE       SAL        COMM       DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------


      1110 张三                 主管                     3322 12-3月 -14      5200                    20


      1111 李四                 销售                     3321 03-11月-15      3400        500         30


      1112 王五                 销售                     3321 25-4月 -12      4400        800         30


      1113 赵二                 后勤                     3320 30-5月 -11      3450                    40


      1114 李磊磊               会计                     3319 22-12月-15      2500                    50


      1115 张少丽               销售                     3321 11-3月 -16      3400       1400         30


      1116 林建国               主管                     3322 22-1月 -16      5700                    20


      1117 马富邦               后勤                     3320 22-7月 -13      2800                    40


      1118 沈倩                 会计                     3319 06-5月 -10      2100                    50
已选择9行。

2.从表中检索部分行
查看公司有多少销售人员
SQL> select * from emp where job='会计';

     EMPNO ENAME                JOB                       MGR HIREDATE       SAL        COMM       DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
      1114 李磊磊               会计                     3319 22-12月-15     2500                    50
  
      1118 沈倩                 会计                     3319 06-5月 -10     2100                    50


3.查找空值
SQL> select * from emp where comm is null;

    EMPNO ENAME                JOB                       MGR HIREDATE        SAL        COMM       DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
      1110 张三                 主管                     3322 12-3月 -14     5200                    20


      1113 赵二                 后勤                     3320 30-5月 -11     3450                    40


      1114 李磊磊               会计                     3319 22-12月-15     2500                    50


      1116 林建国               主管                     3322 22-1月 -16     5700                    20


      1117 马富邦               后勤                     3320 22-7月 -13     2800                    40


      1118 沈倩                 会计                     3319 06-5月 -10     2100                    50

已选择6行。

4.将空值转换为实际值
SQL> select coalesce(comm,0) from emp;
COALESCE(COMM,0)
----------------
               0
        5.0E+002
        8.0E+002
               0
               0
        1.4E+003
               0
               0
               0

已选择9行。

注:
COALESCE (expression_1, expression_2, ...,expression_n)依次参考各参数表达式,遇到非null值即停止并返回该值。如果所有的表达式都是空值,最终将返回一个空值。使用COALESCE在于大部分包含空值的表达式最终将返回空值。
还有一个类似的函数NVL(E1, E2):
NVL(E1, E2)的功能为:如果E1为NULL,则函数返回E2,但此函数有一定局限,所以就有了NVL2函数。
拓展:NVL2函数:Oracle/PLSQL中的一个函数,Oracle在NVL函数的功能上扩展,提供了NVL2函数。NVL2(E1, E2, E3)的功能为:如果E1为NULL,则函数返回E3,若E1不为null,则返回E2。
比较:相对于nvl来说,coalesce支持多个参数,能很方便地返回第一个不为空的值。如果上面使用nvl,就要嵌套很多层。

5.查找满足多个条件的行
我们要查询部门20中的所有员工、所有得到提成的员工,以及部门40中工资低于3000元的员工
我们把这三个条件整理成逻辑表达式的形式:(部门20中的员工 OR 所有得到提成的员工 OR (工资<3000 and 部门号=40))
SQL> select * 
         from emp 
         where (deptno=20 
                or comm is not null /*千万不要写成comm <> null*/
                or (sal < 3000 and deptno = 40));

    EMPNO ENAME                JOB                       MGR HIREDATE        SAL        COMM       DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
      1110 张三                 主管                     3322 12-3月 -14      5200                    20


      1111 李四                 销售                     3321 03-11月-15      3400        500         30


      1112 王五                 销售                     3321 25-4月 -12      4400        800         30


      1115 张少丽               销售                     3321 11-3月 -16      3400       1400         30


      1116 林建国               主管                     3322 22-1月 -16      5700                    20


      1117 马富邦               后勤                     3320 22-7月 -13      2800                    40


6.从表中检索部分列
明确指定查询哪些列即可。
SQL> select empno,ename,hiredate,sal from emp where deptno = 30;
     EMPNO ENAME                HIREDATE              SAL
---------- -------------------- -------------- ----------
      1111 李四                 03-11月-15           3400
      1112 王五                 25-4月 -12           4400
      1115 张少丽               11-3月 -16           3400

7.为列取有意义的名称
可以给列取别名
SQL> select ename as 姓名,deptno as 部门编号,sal as 工资,comm as 提成 from emp;
姓名                   部门编号       工资       提成
-------------------- ---------- ---------- ----------
张三                         20       5200
李四                         30       3400        500
王五                         30       4400        800
赵二                         40       3450
李磊磊                       50       2500
张少丽                       30       3400       1400
林建国                       20       5700
马富邦                       40       2800
沈倩                         50       2100

已选择9行。

8.在Where子句中引用取别名的列
下例是寻找工资低于3000元的人
SQL> select * from (select sal as 工资,comm as 提成 from emp) where 工资 < 3000;
      工资       提成
---------- ----------
      2500
      2800
      2100

9.拼接列
用字符串连接符“||”来把各列拼在一起
SQL> select ename || '的工作是' || job as msg from emp where deptno = 20;
MSG
--------------------------------------------------------------
张三的工作是主管
林建国的工作是主管


10.在Select语句中使用条件逻辑
如:当员工信息小于或等于2500元时,就返回消息“过低”,大于或等于4000元时,就返回消息“过高”,如果在两者之间,就返回“OK”。
SQL> select ename,sal,
     case
       when sal <= 2500 then '过低'
       when sal >= 4000 then '过高'
     else 'OK'
     end as status
     from emp;

ENAME                       SAL STATUS
-------------------- ---------- ------------
张三                       5200 过高
李四                       3400 OK
王五                       4400 过高
赵二                       3450 OK
李磊磊                     2500 过低
张少丽                     3400 OK
林建国                     5700 过高
马富邦                     2800 OK
沈倩                       2100 过低

已选择9行。

这种方式还常用在报表中,比如要按工资分档次统计人数
SQL> select 档次,count(*) as 人数
     from (select (case
                      when sal <= 2500 then '1000-2500'
                      when sal <= 3000 then '2500-3000'
                      when sal <= 4000 then '3000-4000'
                      when sal <= 5000 then '4000-5000'
                   else '好高'
                   end) as 档次,
                   ename,sal
                from emp)
     group by 档次
     order by 1;

档次                     人数
------------------ ----------
1000-2500                   2
2500-3000                   1
3000-4000                   3
4000-5000                   1
好高                        2

11.限制返回的行数
并不要求每次都要返回所有的数据,比如,进行抽查的时候会只要求返回两条数据。
我们可以用伪列rownum来过滤,rownum一次对返回的每一条数据做一个标识。

SQL> select * from emp where rownum <= 2;

    EMPNO ENAME                JOB                       MGR HIREDATE        SAL        COMM       DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
      1110 张三                 主管                     3322 12-3月 -14      5200                    20


      1111 李四                 销售                     3321 03-11月-15      3400        500         30

12.从表中随机返回n条记录
我们先用dbms_random来对数据进行随机排序,然后取其中三行。
SQL> select empno,ename
         from (select empno,ename from emp order by dbms_random.value())
       where rownum <= 3;
     EMPNO ENAME
---------- --------------------
      1118 沈倩
      1110 张三
      1111 李四

SQL> select empno,ename
              from (select empno,ename from emp order by dbms_random.value())
            where rownum <= 3;

     EMPNO ENAME
---------- --------------------
      1115 张少丽
      1118 沈倩
      1117 马富邦

可以看到两次取的随机值不同
注意:
正确的做法是:先随机排序,再取数据
错误的做法是:先取数据,再随机排序,这样每次执行语句得到的都是相同的结果。

13.模糊查询
先建立如下view:
SQL> create or replace view v as
     select 'ABCDEF' as vname from dual
     union all
     select '_BCDEF' as vname from dual
     union all
     select '_BCEDF' as vname from dual
     union all
     select '_\BCEFG' as vname from dual
     union all
    select 'XYCEG' as vname from dual;

创建之后的表为:
SQL> select * from v;
VNAME
--------------
ABCDEF
_BCDEF
_BCEDF
_\BCEFG
XYCEG

要求一:查出vname中包含字符串"CDE"的
SQL> select * from v where vname like '%CDE%';

VNAME
--------------
ABCDEF
_BCDEF

要求二:查出vname中包含字符串“_BCE”的
SQL> select * from v where vname like '%_BCE%';

VNAME
--------------
_BCEDF
_\BCEFG

发现多了一个_\BCEFG。因为在LIKE子句中有两个通配符:“%”(替代一个或多个字符)、“_”被当做通配符了,怎么办呢?我们可以用转义字符:
SQL> select * from v where vname like '\_BCE%' escape '\';

VNAME
--------------
_BCEDF
其中escape 把‘\’标识为转义字符,而‘\’把‘_’转义为字符,而非其原意(通配符)。

想查到_\BCE可以用双转义:
SQL> select * from v where vname like '_\\BCE%' escape '\';

VNAME
--------------
_\BCEFG

对于字符串中包含“%”的情况,与上面的处理方法一样。

注:
(1)union all
SQL UNION 操作符
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意,UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。
SQL UNION 语法

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

注释:默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用 UNION ALL。

SQL UNION ALL 语法

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

另外,UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

(2)Dual
在Oracle数据库内有一种特殊的表Dual。Dual表是Oracle中的一个实际存在的表,任何用户均可读取,常用在没有目标表的Select中。Dual表由Oracle连同数据字典一同创建,所有的用户都可以用名称DUAL访问该表。这个表里只有一列DUMMY,该列定义为VARCHAR2(1)类型,有一行值X。从DUAL表选择数据常被用来通过SELECT语句计算常数表达式,由于DUAL只有一行数据,所以常数只返回一次。
转载请注明出处:http://blog.csdn.net/acmman/article/details/51026148
相关文章
|
7天前
|
SQL 运维 监控
SQL查询太慢?实战讲解YashanDB SQL调优思路
本文是Meetup第十期“调优实战专场”的第二篇技术文章,上一篇《高效查询秘诀,解码YashanDB优化器分组查询优化手段》中,我们揭秘了YashanDB分组查询优化秘诀,本文将通过一个案例,助你快速上手YashanDB慢日志功能,精准定位“慢SQL”后进行优化。
|
4天前
|
SQL 索引
【YashanDB知识库】字段加上索引后,SQL查询不到结果
【YashanDB知识库】字段加上索引后,SQL查询不到结果
|
1月前
|
SQL 关系型数据库 MySQL
MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?
在日常研发工作当中,系统性能优化,从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化,除了DBA在集群性能、服务器调优需要投入精力,我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后,SQL执行效率可能就会出现瓶颈,影响系统业务响应。掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能,在工作中解决SQL性能问题显得非常关键。
|
4天前
|
SQL 大数据 数据挖掘
玩转大数据:从零开始掌握SQL查询基础
玩转大数据:从零开始掌握SQL查询基础
75 35
|
15天前
|
SQL 关系型数据库 MySQL
基于SQL Server / MySQL进行百万条数据过滤优化方案
对百万级别数据进行高效过滤查询,需要综合使用索引、查询优化、表分区、统计信息和视图等技术手段。通过合理的数据库设计和查询优化,可以显著提升查询性能,确保系统的高效稳定运行。
35 9
|
24天前
|
SQL 关系型数据库 分布式数据库
利用 PolarDB PG 版向量化引擎,加速复杂 SQL 查询!完成任务领发财新年抱枕!
利用 PolarDB PG 版向量化引擎,加速复杂 SQL 查询!完成任务领发财新年抱枕!
|
15天前
|
SQL 关系型数据库 OLAP
云原生数据仓库AnalyticDB PostgreSQL同一个SQL可以实现向量索引、全文索引GIN、普通索引BTREE混合查询,简化业务实现逻辑、提升查询性能
本文档介绍了如何在AnalyticDB for PostgreSQL中创建表、向量索引及混合检索的实现步骤。主要内容包括:创建`articles`表并设置向量存储格式,创建ANN向量索引,为表增加`username`和`time`列,建立BTREE索引和GIN全文检索索引,并展示了查询结果。参考文档提供了详细的SQL语句和配置说明。
32 1
|
1天前
|
SQL 缓存 关系型数据库
SQL为什么不建议执行多表关联查询
本文探讨了SQL中不建议执行多表关联查询的原因,特别是MySQL与PG在多表关联上的区别。MySQL仅支持嵌套循环连接,而不支持排序-合并连接和散列连接,因此在多表(超过3张)关联查询时效率较低。文章还分析了多表关联查询与多次单表查询的效率对比,指出将关联操作放在Service层处理的优势,包括减少数据库计算资源消耗、提高缓存效率、降低锁竞争以及更易于分布式扩展等。最后,通过实例展示了如何分解关联查询以优化性能。
|
2月前
|
SQL NoSQL Java
Java使用sql查询mongodb
通过MongoDB Atlas Data Lake或Apache Drill,可以在Java中使用SQL语法查询MongoDB数据。这两种方法都需要适当的配置和依赖库的支持。希望本文提供的示例和说明能够帮助开发者实现这一目标。
63 17
|
2月前
|
SQL Oracle 关系型数据库
如何在 Oracle 中配置和使用 SQL Profiles 来优化查询性能?
在 Oracle 数据库中,SQL Profiles 是优化查询性能的工具,通过提供额外统计信息帮助生成更有效的执行计划。配置和使用步骤包括:1. 启用自动 SQL 调优;2. 手动创建 SQL Profile,涉及收集、执行调优任务、查看报告及应用建议;3. 验证效果;4. 使用 `DBA_SQL_PROFILES` 视图管理 Profile。

热门文章

最新文章