【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

时间： 2024-09-14 14:04:17

【sql查询与优化】1.单表查询的相关文章

用SQL进行单表查询

  单表查询是相对多表查询而言的,指从一个数据表中查询数据.4.2.1 查询所有的记录    在[命令编辑区]执行输入"select * from scott.emp",然后单击[执行]按钮,出现如图4.3所示的emp数据表所有记录.    [参见光盘文件]:\第4章\4.2\421.sql.    select * from 数据表,这里的"*"代表数据表中所有的字段.4.2.2 查询所有记录的某些字段    在[命令编辑区]输入"select empn

求一条sql语句，单表查询的

问题描述 求一条sql语句,单表查询的 表结构 (姓名,课程,成绩) 现在求获的该表总成绩最高学员的名字 解决方案 mysql 数据库select sum(成绩) as t from table group by 姓名 order by t limit 0,1 oracle数据库 select * from (select sum(成绩) as t from table group by 姓名 order by t) WHERE ROWNUM<=1 sqlserver数据库 select top

python实现简易数据库（二） 单表查询和top N实现

上一篇中,介绍了我们的存储和索引建立过程,这篇将介绍SQL查询.单表查询和TOPN实现. 一.SQL解析 正规的sql解析是用语法分析器,但是我找了好久,只知道可以用YACC.BISON等,sqlite使用的lemon,捣整了一天没实现,就用了python的正则表达式. 1.删除无用的空格.跳格符.换行符等: 我们以分号';'作为一个sql语句的结束符,在输入分号之前,我们将输入的sql语句串接成一个string,在将整个sql语句的一些无用的字符删掉, 1 def rmNoUseChar(sq

6.单表查询

1.语法格式         select语句的功能就是查询数据,在SQL语句中功能最丰富,可单表查询.多表连接查询.子查询. SELECT  NAME, grade FROM student WHERE grade >80; 数据源student可以是表.视图等:select后列表用于确定选择哪些列(* 即所有列),where确定选择哪些行(无则选出所有行). select语句中可使用算术运算符(+.-.*./)形成算术表达式,用于数值型.日期型的数据列.变量.常量:运算符可在两列间进行运算.

SQL判断语句用法和多表查询_MsSql

1.格式化时间sql语句 本例中本人随便做了两张表,和实际不是很相符,只是想说明sql语句的写法. 例1表格式如下: 需求:查询出本表,但需要使time字段的时间格式为yyyy-MM-dd,比如:2013-08-13 sql写法: SELECT u.id,u.userId,u.timeType,DATE_FORMAT(time,'%Y-%m-%d') AS time,secondId FROM `user` u 运行结果: 2.多表查询(三表查询) 例二三表结构如下: 需求:查询出主表,要求在主

SQL判断语句用法和多表查询

1.格式化时间sql语句 本例中本人随便做了两张表,和实际不是很相符,只是想说明sql语句的写法. 例1表格式如下: 需求:查询出本表,但需要使time字段的时间格式为yyyy-MM-dd,比如:2013-08-13 sql写法: SELECT u.id,u.userId,u.timeType,DATE_FORMAT(time,'%Y-%m-%d') AS time,secondId FROM `user` u 运行结果: 2.多表查询(三表查询) 例二三表结构如下: 需求:查询出主表,要求在主

oracle 全表单表查询慢，如何解决

问题描述 各位高手如题,表结构如图!~ 问题补充:那请问如果有大文本的内容的时候不用clob,那如何办?!tnt-scott 写道 解决方案 1.检索lob字段的时候,按需去取lob字段,如果你不用,则给它设成null,比如我要id='10'的lob字段,那么sql:select decode(id,'10',lob_col,null) from tb2.给lob字段建立单独的表空间,并设定把CACHE 设定成reads提高读取速度.代码:--创建表空间CREATE TABLESPACE MON

Access使用查询--1.3.用交叉表查询体现一组数据的对比

3.用交叉表查询体现一组数据的对比 新建查询,选择"交叉表查询向导",接着选择"车队运输查询",出现上面的对话框,从可用字段中选择"司机"字段作为行标题.单击下一步. 接着从字段中选择"货物类别"作为列标题.单击下一步. 选择"净重"字段,函数选择"求和",最后为这个交叉表查询起一个名字,向导结束. 上图是刚生成的查询的设计视图. 下表是交叉表的查询结果,清晰地对比了每个司机运输各种货物

联合查询-mysql 结构不同多表查询，请教语句

问题描述 mysql 结构不同多表查询,请教语句 我原来有几个结构相同的3个表 tab1.tab2.tab3 这3个表都有字段 col1.col2.col3 原来使用语句 (select * from tab1 where col1='$wd' or col2 LIKE '$wd%') union all (select * from tab1 where col1='$wd' or col2 LIKE '$wd%') union all (select * from tab1 where co