精华内容
下载资源
问答
  • where优先级

    2018-08-27 15:58:00
    select name from emply where id >5; 先找表from emply 再找条件 where id >5 最后打印 你想打印的字段 可以把select看成打印 优先级为from->where-->select 先找到表,然后where一条条数据过滤...

    select name from emply where id >5;

    先找表from emply

    再找条件 where id >5

    最后打印 你想打印的字段

    可以把select看成打印

    优先级为from->where-->select

    先找到表,然后where一条条数据过滤,发现符合的就扔给select,select再梳理出自己需要的数据进行打印。

     

    #where
    select id,name,age from employee where id > 7;

    select name,post,salary from employee where post='teacher' and salary > 8000;

    select name,salary from employee where salary >= 20000 and salary <= 30000;
    select name,salary from employee where salary between 20000 and 30000;

    select name,salary from employee where salary < 20000 or salary > 30000;
    select name,salary from employee where salary not between 20000 and 30000;


    select * from employee where age = 73 or age = 81 or age = 28;
    select * from employee where age in (73,81,28); 选一个

    select * from employee where post_comment is Null;  不可以=Null 要用is去判断
    select * from employee where post_comment is not Null;

    select * from employee where name like "jin%";  %等于后面任意匹配(即任意长度任意字符)
    select * from employee where name like "jin___";(_只匹配一个任意字符)

    转载于:https://www.cnblogs.com/FlFtFw/p/9542724.html

    展开全文
  • mysql 的 join where 优先级 定义 join功能 inner join(内连接,或等值连接) : 获取两个表中字段匹配关系的记录。 left join (左连接) : 获取左表所有记录,即使右表没有对应匹配的记录。 right join (右连接) : ...

    mysql 的 join 和 where 优先级

    定义

    join功能

    • inner join(内连接,或等值连接) : 获取两个表中字段匹配关系的记录。
    • left join (左连接) : 获取左表所有记录,即使右表没有对应匹配的记录。
    • right join (右连接) : 与 LEFT JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录。

    where

    where 是 mysql 语句的查询条件

    测试数据表

    表 a1

    xy
    110
    220
    330

    表 a2

    xy
    10100
    20200
    20300
    create table if not exists `a1`(
        `x` int(10),
        `y` int(10)
    );
    
    create table if not exists `a2`(
        `y` int(10),
        `z` int(10)
    )
    

    查询 sql 及结果

    正常左连接

    select * from a1 left join a2 on a1.y = a2.y;
    
    xyyz
    11010100
    22020200
    22020300
    330NULLNULL

    左连情况下, 由于左边a1.y = 30在右表无数据所以右表数据 (y,z)为 NULL

    左连 on && and

    select * from a1 left join a2 on a1.y = a2.y and a2.y = 10;
    
    xyyz
    11010100
    220NULLNULL
    330NULLNULL

    由于是左连, 所以判断 (a1.y = a2.y && a2.y = 10) 只能筛选出(10, 100)与左边匹配, 所以后面均为 NULL.
    即实际优先级是 select * from (a1 left join a2 on a1.y = a2.y and a2.y = 10)

    左连 on && where

    select * from a1 left join a2 on a1.y = a2.y where a2.y = 10;
    
    xyyz
    11010100

    只有一条数据, 因此可判断其优先级为select * from (a1 left join a2 on a1.y = a2.y) where a2.y = 10.
    也就是说 会先左连生成临时表, 然后再在整体表上进行 where 查询.

    展开全文
  • 1、select 语句优先级 select classid, userid, name, age from study where userid > 15 group by classid having classid > 89 order by classid desc; from > where > select &g...

    1、select 语句优先级

    select  classid, userid, name, age  from  study   where  userid > 15  group  by   classid   having  classid > 89  order  by   classid  desc;
    

    from > where > select > group by > having > order by

    转载于:https://www.cnblogs.com/Small-sunshine/p/10907546.html

    展开全文
  • 2.1 WHERE子句(限制行) 2.2 比较运算符 2.3 逻辑运算符 3 优先级 4 排序:ORDER BY子句 5 替换变量 5.1 & 5.2 && 5.3 DEFINE命令 0 登录数据库 查看数据库进程 # ps -ef | grep ora_ 只查看oracle...

    0 登录数据库

    • 查看数据库进程
      # ps -ef | grep ora_
      只查看oracle数据库进程

    • 连接到数据库软件
      $ sqlplus / as sysdba
      sqlplus是一个工具,可以把系统连接到数据库
      看到以下说明已经连接:

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    • 启动数据库
    SQL> startup
    ORACLE instance started.
    
    Total System Global Area  830930944 bytes
    Fixed Size                  2257800 bytes
    Variable Size             536874104 bytes
    Database Buffers          289406976 bytes
    Redo Buffers                2392064 bytes
    Database mounted.
    Database opened.
    

    看到Database opened说明已经连接成功。

    • 关闭数据库:
    SQL> shutdown immediate
    
    • 利用系统自带脚本utlsampl.sql创建scott用户及样本数据
    SQL> @?/rdbms/admin/utlsampl.sql               # 运行一个脚本
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
    Data Mining and Real Application Testing options
    

    建立完成以后会自动退出sqlplus,重新登录既可。

    • 通过其他用户登录
    [oracle@yr ~]$ sqlplus scott/tiger                    # 登录到scott用户,密码是tiger		sysdba是超级用户,相当于root
    SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 22 10:28:20 2019
    
    Copyright (c) 1982, 2013, Oracle.  All rights reserved.
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    • 登陆到scott用户的两种方法:
      1)直接登陆:sqlplus dcott/tiger
      2)切换用户:在数据库中
    SQL> conn scott/tiger                                      
    Connected.
    
    • 退出
      SQL>exit是退出到oracle用户。

    1 Select查询语句

    1.1 基本的SELECT子句

    功能:可以按col列(column)查看,也可以按row行查看;可以把意思相同的列或行关联,根据关联关系多表查询。
    语法:(不区分大小写)

    SELECT *|{[DISTINCT] column | expression [alias],...}FROM table;
    

    其中,
    *表示所有的列
    [DISTINCT] :[关键字]可写可不写 distinct是去重
    column:列的名字
    expression:表达式
    table:表名
    ; 代表一句话结束
    SELECT 标识选择哪些列
    FROM 标识从哪个表中选择

    在scott用户下登录

    • 查看dept所有列(通过* 或将所有列一一列举)
    SQL> show user    
    USER is "SCOTT"
    SQL> select * from dept;
    
        DEPTNO DNAME	  LOC				# DEPTNO 部门编号        DNAME 部门名称        LOC 地点
    ---------- -------------- -------------
    	10 ACCOUNTING	  NEW YORK
    	20 RESEARCH	  DALLAS
    	30 SALES	  CHICAGO
    	40 OPERATIONS	  BOSTON
    
    • 查看指定列
    SQL> select dname from dept;
    
    DNAME
    --------------
    ACCOUNTING
    RESEARCH
    SALES
    OPERATIONS
    
    • 查看多列(用","间隔)
    SQL> select dname,deptno from dept;
    
    DNAME		   DEPTNO
    -------------- ----------
    ACCOUNTING	       10
    RESEARCH	       20
    SALES		       30
    OPERATIONS	       40
    
    • 查看表达式
    SQL> select 1+1 from dept;
    
           1+1
    ----------
    	 2
    	 2
    	 2
    	 2
    
    SQL> select dname,deptno+100,deptno from dept;
    
    DNAME	       DEPTNO+100     DEPTNO				# 表达式也可以在列上进行计算
    -------------- ---------- ----------
    ACCOUNTING	      110	  10
    RESEARCH	      120	  20
    SALES		      130	  30
    OPERATIONS	      140	  40
    
    • 查看dept表,自定义表达式列
    SQL> select dname,deptno+100 as "noplus",deptno from dept;
    
    DNAME		   noplus     DEPTNO
    -------------- ---------- ----------
    ACCOUNTING	      110	  10
    RESEARCH	      120	  20
    SALES		      130	  30
    OPERATIONS	      140	  40
    
    SQL> select deptno+100 a from dept;
    
    	 A				# 列名一般都是默认大写的:
    ----------
           110
           120
           130
           140
    
    • 为查看的列名指定别名
    SQL> select dname,'is',deptno from dept;
    
    DNAME	       'I     DEPTNO
    -------------- -- ----------
    ACCOUNTING     is	  10
    RESEARCH       is	  20
    SALES	       is	  30
    OPERATIONS     is	  40
    
    SQL> select dname,'is' as "is",deptno from dept;
    
    DNAME	       is	 DEPTNO
    -------------- ----- ----------
    ACCOUNTING     is	     10
    RESEARCH       is	     20
    SALES	       is	     30
    OPERATIONS     is	     40
    
    • 格式化
      如果一行显示不了所有的列,并且分页显示,就需要格式化:
      set lines 200 线的长度设定为200
      set pages 200 一页显示200
      或打在一行:set lines 200 pages 200

    1.2 SQL语句的注意事项

    1)SQL语言大小写不敏感
    2)SQL可以写在一行或多行
    3)关键字不能被缩写也不能分行
    4)各子句一般要分行写
    5)使用缩进提高语句的可读性
    6)SQL语句在SQL Developer中,可以以;终止,当执行多个SQL时,结束分号是必需的。
    7)在SQL *Plus中,必须用;结束每条SQL语句

    1.3 SELECT语句中的算术表达式

    • 使用运算符 (+ - * /)
    SQL> set lines 200 pages 200
    SQL> select sal*12 from emp;
    
        SAL*12
    ----------
          9600
         19200
         15000
         35700
         15000
         34200
         29400
         36000
         60000
         18000
         13200
         11400
         36000
         15600
    
    14 rows selected.
    

    能进行计算的前提是这一列是数字类型,并且只是临时显示,不会更改表的数据

    • 算术运算符优先级
    SQL> select sal,12*sal+100,12*(sal+100) from emp;
    
           SAL 12*SAL+100 12*(SAL+100)
    ---------- ---------- ------------
           800	 9700	     10800
          1600	19300	     20400
          1250	15100	     16200
          2975	35800	     36900
          1250	15100	     16200
          2850	34300	     35400
          2450	29500	     30600
          3000	36100	     37200
          5000	60100	     61200
          1500	18100	     19200
          1100	13300	     14400
           950	11500	     12600
          3000	36100	     37200
          1300	15700	     16800
    
    14 rows selected.
    

    1.4 NULL

    1.4.1 意义

    1)NULL是无效的、未指定的、未知的或不可预知的值
    2)NULL不是0,也不是空格
    在这里插入图片描述

    1.4.2 计算

    包含空值的数学表达式的值都为空值

    SQL> select comm,2*comm from emp;
    
          COMM     2*COMM
    ---------- ----------
    
           300	  600
           500	 1000
    
          1400	 2800
    
    
    
    
    	 0	    0
    
    
    
    
    
    14 rows selected.
    
    SQL> select ename,sal,comm,sal*12+comm from emp;
    
    ENAME		  SAL	    COMM SAL*12+COMM
    ---------- ---------- ---------- -----------
    SMITH		  800
    ALLEN		 1600	     300       19500
    WARD		 1250	     500       15500
    JONES		 2975
    MARTIN		 1250	    1400       16400
    BLAKE		 2850
    CLARK		 2450
    SCOTT		 3000
    KING		 5000
    TURNER		 1500	       0       18000
    ADAMS		 1100
    JAMES		  950
    FORD		 3000
    MILLER		 1300
    
    14 rows selected.
    
    • nvl函数
      将null暂时设定成0,以便计算
    SQL> select ename,sal*12+nvl(comm,0) from emp;
    
    ENAME	   SAL*12+NVL(COMM,0)
    ---------- ------------------
    SMITH			 9600
    ALLEN			19500
    WARD			15500
    JONES			35700
    MARTIN			16400
    BLAKE			34200
    CLARK			29400
    SCOTT			36000
    KING			60000
    TURNER			18000
    ADAMS			13200
    JAMES			11400
    FORD			36000
    MILLER			15600
    
    14 rows selected.
    

    1.5 列别名

    重命名一个列标题,便于计算,紧跟列名(也可以在列名和别名之间加入关键字‘AS’),如果它包含空格或特殊字符,或者它是区分大小写的,那么需要双引号

    SQL> select dname name from dept;
    
    NAME
    --------------
    ACCOUNTING
    RESEARCH
    SALES
    OPERATIONS
    
    SQL> select dname as name from dept;
    
    NAME
    --------------
    ACCOUNTING
    RESEARCH
    SALES
    OPERATIONS
    # 加上双引号后会区分大小写
    SQL> select dname "name" from dept;
    
    name
    --------------
    ACCOUNTING
    RESEARCH
    SALES
    OPERATIONS
    

    1.6 连接符

    把列与列,列与字符连接在一起,用“||”,可以用来合成列。

    SQL> select deptno||dname name from dept;
    
    NAME
    ------------------------------------------------------
    10ACCOUNTING
    20RESEARCH
    30SALES
    40OPERATIONS
    

    1.7 字符串

    字符串可以是SELECT列表中的一个字符,数字,日期。日期和字符只能在单引号中出现。每当返回一行时,字符串被输出一次。

    SQL> select deptno,'aaa' from dept;
    
        DEPTNO 'AA
    ---------- ---
    	10 aaa
    	20 aaa
    	30 aaa
    	40 aaa
    
    SQL> select dname || ' is No. ' || deptno name from dept;
    
    NAME
    -------------------------------------------------------------
    ACCOUNTING is No. 10
    RESEARCH is No. 20
    SALES is No. 30
    OPERATIONS is No. 40
    
    • sysdate函数
      输出当前系统日期(日-月-年)
    SQL> select deptno,sysdate from dept;
    
        DEPTNO SYSDATE
    ---------- ---------
    	10 07-MAY-19
    	20 07-MAY-19
    	30 07-MAY-19
    	40 07-MAY-19
    
    • 在数据库里如果想执行Linux的命令,输入 !后再输入命令(!相当于暂时退出,去执行系统的命令)
    SQL> !ps -ef | grep ora_
    oracle    53858      1  0 May06 ?        00:00:24 ora_pmon_nsfcdc
    oracle    53860      1  0 May06 ?        00:00:20 ora_psp0_nsfcdc
    oracle    53862      1  0 May06 ?        00:00:31 ora_vktm_nsfcdc
    oracle    53866      1  0 May06 ?        00:00:04 ora_gen0_nsfcdc
    oracle    53868      1  0 May06 ?        00:00:08 ora_diag_nsfcdc
    oracle    53870      1  0 May06 ?        00:00:08 ora_dbrm_nsfcdc
    oracle    53872      1  0 May06 ?        00:01:23 ora_dia0_nsfcdc
    oracle    53874      1  0 May06 ?        00:00:04 ora_mman_nsfcdc
    oracle    53876      1  0 May06 ?        00:00:05 ora_dbw0_nsfcdc
    oracle    53878      1  0 May06 ?        00:00:05 ora_dbw1_nsfcdc
    oracle    53880      1  0 May06 ?        00:00:05 ora_dbw2_nsfcdc
    oracle    53882      1  0 May06 ?        00:00:05 ora_dbw3_nsfcdc
    oracle    53884      1  0 May06 ?        00:00:05 ora_dbw4_nsfcdc
    oracle    53886      1  0 May06 ?        00:00:06 ora_lgwr_nsfcdc
    oracle    53888      1  0 May06 ?        00:00:23 ora_ckpt_nsfcdc
    oracle    53890      1  0 May06 ?        00:00:05 ora_smon_nsfcdc
    oracle    53892      1  0 May06 ?        00:00:02 ora_reco_nsfcdc
    oracle    53894      1  0 May06 ?        00:00:17 ora_mmon_nsfcdc
    oracle    53896      1  0 May06 ?        00:01:52 ora_mmnl_nsfcdc
    oracle    53898      1  0 May06 ?        00:00:02 ora_d000_nsfcdc
    oracle    53900      1  0 May06 ?        00:00:01 ora_s000_nsfcdc
    oracle    53907      1  0 May06 ?        00:00:04 ora_rvwr_nsfcdc
    oracle    53910      1  0 May06 ?        00:00:02 ora_arc0_nsfcdc
    oracle    53912      1  0 May06 ?        00:00:02 ora_arc1_nsfcdc
    oracle    53914      1  0 May06 ?        00:00:03 ora_arc2_nsfcdc
    oracle    53916      1  0 May06 ?        00:00:02 ora_arc3_nsfcdc
    oracle    53920      1  0 May06 ?        00:00:02 ora_qmnc_nsfcdc
    oracle    53934      1  0 May06 ?        00:00:16 ora_cjq0_nsfcdc
    oracle    53942      1  0 May06 ?        00:00:01 ora_q000_nsfcdc
    oracle    53944      1  0 May06 ?        00:00:02 ora_q001_nsfcdc
    oracle    53969      1  0 May06 ?        00:00:04 ora_smco_nsfcdc
    oracle    56592      1  0 08:34 ?        00:00:00 ora_w000_nsfcdc
    oracle    56629  56579  0 09:00 pts/8    00:00:00 /bin/bash -c ps -ef | grep ora_
    oracle    56631  56629  0 09:00 pts/8    00:00:00 grep ora_
    

    1.8 distinct去掉重复的行

    默认情况下,查询会返回全部的行,包括重复行,通过distinct命令去除重复行

    • 去除重复行显示所有工作类型:
    SQL> select distinct job from emp;
    
    JOB
    ---------
    CLERK
    SALESMAN
    PRESIDENT
    MANAGER
    ANALYST
    
    SQL> select distinct job,deptno from emp;
    
    JOB	      DEPTNO
    --------- ----------
    MANAGER 	  20
    PRESIDENT	  10
    CLERK		  10
    SALESMAN	  30
    ANALYST 	  20
    MANAGER 	  30
    MANAGER 	  10
    CLERK		  30
    CLERK		  20
    
    9 rows selected.
    

    1.9 DESCRIBE显示表结构

    使用DESCRIBE命令显示表结构,或者选择表中的Connections树,并使用列选项卡,查看表结构。
    在这里插入图片描述

    • not null说明不允许有空值。
    • type 表示数据类型
      number(4)说明最多只可以有四位数字
      number(7,2)说明小数点前最多7位,小数点后最多两位 varchar 字符类型(a、b、c、1、2、3等)一个字母占一位,一个汉字占2个字符。

    2 过滤数据

    过滤和排序数据可以在查询中过滤行,在查询中对行进行排序,在运行时使用“&”字符替换来限制和排序输出。

    2.1 WHERE子句(限制行)

    作用:限制行、过滤行数据
    语法:只显示满足条件的行,把不满足的行都过滤掉。WHERE子句要紧跟FROM子句。

    SELECT *|{[DISTINCT] column | expression [alias],...}FROM tableWHERE condition(s);
    
    • 查看所有在部门20的员工:
    SQL> select ename,deptno from emp where deptno=20;
    
    ENAME	       DEPTNO
    ---------- ----------
    SMITH		   20
    JONES		   20
    SCOTT		   20
    ADAMS		   20
    FORD		   20
    
    SQL> select * from emp
      2  where deptno = 20;
    
         EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
          7369 SMITH      CLERK	      7902 17-DEC-80	    800 		   20
          7566 JONES      MANAGER	      7839 02-APR-81	   2975 		   20
          7788 SCOTT      ANALYST	      7566 19-APR-87	   3000 		   20
          7876 ADAMS      CLERK	      7788 23-MAY-87	   1100 		   20
          7902 FORD       ANALYST	      7566 03-DEC-81	   3000 		   20
    
    • 查看叫smith的员工的员工号:
    SQL> select ename,deptno from emp where ename = 'SMITH';
    
    ENAME	       DEPTNO
    ---------- ----------
    SMITH		   20
    
    SQL> select ename,deptno from emp where ename='smith';
    
    no rows selected
    

    字符和日期要包含在单引号中,字符大小写敏感,日期格式敏感,默认的日期格式是DD-MON-RR,number类型不用加引号如果给数字加了’‘,也可以返回,但是增加计算次数,浪费效率

    • 查看指定日期的行
    SQL> select ename,hiredate from emp where hiredate = '03-DEC-81';
    
    ENAME	   HIREDATE
    ---------- ---------
    JAMES	   03-DEC-81
    FORD	   03-DEC-81
    
    SQL> select ename from emp where hiredate = '03-DEC-81';
    
    ENAME
    ----------
    JAMES
    FORD
    
    • upper函数:转换为大写
    SQL> select ename,deptno from emp where ename = upper('smith');
    
    ENAME	       DEPTNO
    ---------- ----------
    SMITH		   20
    
    • lower函数:转换列上所有的数据为小写,然后再匹配。所以不论是大写还是小写或是大小写混合的,都可以。(但是不推荐在列上改东西,因为如果这个列很长,那么非常耗时,增加运算量)

    2.2 比较运算符

    = > >= < <=
    <>(不等于)或用 != 或 ^= 表示
    BETWEEN…AND… 区间
    IN(set) 多个值
    LIKE 匹配字符(要加通配符)
    IS NULL

    • 查询工资小于3000的名单
    SQL> select ename,sal from emp
      2  where sal<=3000;
    
    ENAME		  SAL
    ---------- ----------
    SMITH		  800
    ALLEN		 1600
    WARD		 1250
    JONES		 2975
    MARTIN		 1250
    BLAKE		 2850
    CLARK		 2450
    SCOTT		 3000
    TURNER		 1500
    ADAMS		 1100
    JAMES		  950
    FORD		 3000
    MILLER		 1300
    
    13 rows selected.
    
    • 工资在2500~3000的名单:
    SQL> select ename,sal from emp where sal between 2500 and 3000;
    
    ENAME		  SAL
    ---------- ----------
    JONES		 2975
    BLAKE		 2850
    SCOTT		 3000
    FORD		 3000
    
    • 在81年以后入职的员工
    SQL> select * from emp
      2  where hiredate >= to_date('01-JAN-81','DD-MON-RR');
    
         EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
          7499 ALLEN      SALESMAN	      7698 20-FEB-81	   1600        300	   30
          7521 WARD       SALESMAN	      7698 22-FEB-81	   1250        500	   30
          7566 JONES      MANAGER	      7839 02-APR-81	   2975 		   20
          7654 MARTIN     SALESMAN	      7698 28-SEP-81	   1250       1400	   30
          7698 BLAKE      MANAGER	      7839 01-MAY-81	   2850 		   30
          7782 CLARK      MANAGER	      7839 09-JUN-81	   2450 		   10
          7788 SCOTT      ANALYST	      7566 19-APR-87	   3000 		   20
          7839 KING       PRESIDENT 	   17-NOV-81	   5000 		   10
          7844 TURNER     SALESMAN	      7698 08-SEP-81	   1500 	 0	   30
          7876 ADAMS      CLERK	      7788 23-MAY-87	   1100 		   20
          7900 JAMES      CLERK	      7698 03-DEC-81	    950 		   30
          7902 FORD       ANALYST	      7566 03-DEC-81	   3000 		   20
          7934 MILLER     CLERK	      7782 23-JAN-82	   1300 		   10
    
    13 rows selected.
    
    SQL> select ename,hiredate from emp
      2  where hiredate > '01-JAN-81';
    
    ENAME	   HIREDATE
    ---------- ---------
    ALLEN	   20-FEB-81
    WARD	   22-FEB-81
    JONES	   02-APR-81
    MARTIN	   28-SEP-81
    BLAKE	   01-MAY-81
    CLARK	   09-JUN-81
    SCOTT	   19-APR-87
    KING	   17-NOV-81
    TURNER	   08-SEP-81
    ADAMS	   23-MAY-87
    JAMES	   03-DEC-81
    FORD	   03-DEC-81
    MILLER	   23-JAN-82
    
    13 rows selected.
    
    • 根据ASCII码找到k-s之间的名字
    SQL> select ename from emp 
      2  where ename between 'KING' and 'SMITH';
    
    ENAME
    ----------
    SMITH
    MARTIN
    SCOTT
    KING
    MILLER
    
    • 使用IN操作符返回smith、scott、king的sal列
    SQL> select ename,sal from emp
      2  where ename in ('SMITH','SCOTT','KING');
    
    ENAME		  SAL
    ---------- ----------
    SMITH		  800
    SCOTT		 3000
    KING		 5000
    
    • 使用LIKE操作符查看名字是S开头的员工的工资
    SQL> select ename,sal from emp            
      2  where ename like 's%';
    
    no rows selected
    
    SQL> select ename,sal from emp 
      2  where ename like 'S%';
    
    ENAME		  SAL
    ---------- ----------
    SMITH		  800
    SCOTT		 3000
    

    LIKE运算是选择类似的值,选择条件可以包含字符或数字:% 代表0个或多个字符,_ 代表一个字符
    不建议把%放在最前面,这样的性能是最差的

    在这里插入图片描述
    这里的 _ 是有实际意义的,不是通配符,所以要用一个 \ 进行转译

    • 使用IS NULL操作符判断空值,查询奖金是未知的员工名称
    SQL> select ename,comm from emp
      2  where comm is null;
    
    ENAME		 COMM
    ---------- ----------
    SMITH
    JONES
    BLAKE
    CLARK
    SCOTT
    KING
    ADAMS
    JAMES
    FORD
    MILLER
    
    10 rows selected.
    
    • IS NOT NULL
    SQL> select ename,comm from emp
      2  where comm is not null;
    
    ENAME		 COMM
    ---------- ----------
    ALLEN		  300
    WARD		  500
    MARTIN		 1400
    TURNER		    0
    

    2.3 逻辑运算符

    • AND 逻辑并,两个条件都为“真”则返回TRUE(并列关系)
    SQL> select ename,sal from emp where ename in ('SMITH','SCOTT','KING')    
      2  and sal > 4000;
    
    ENAME		  SAL
    ---------- ----------
    KING		 5000
    
    • OR 逻辑或,其中一个条件为“真”则返回TRUE
    SQL> select ename,sal from emp where ename in ('SMITH','SCOTT','KING')
      2  or sal > 2000;
    
    ENAME		  SAL
    ---------- ----------
    SMITH		  800
    JONES		 2975
    BLAKE		 2850
    CLARK		 2450
    SCOTT		 3000
    KING		 5000
    FORD		 3000
    
    7 rows selected.
    

    IN相当于OR,但是IN的执行效率更高

    • NOT 逻辑否,如果条件为“假”则返回TRUE
      NOT IN
      NOT BETWEEN … AND …
      NOT LIKE
      IS NOT NULL

    3 优先级

    在表达式中运算符的优先规则

    1 算术运算符
    2 连接符
    3 比较符
    4 IS [NOT] NULL,LIKE,[NOT] IN
    5 [NOT] BETWEEN
    6 不等于
    7 NOT
    8 AND
    9 OR(所以能用IN的就不用OR)
    可以使用括号改变优先级顺序

    • 例1
    SQL> select ename,sal,job from emp
      2  where job=' SALESMAN '
      3  or job = 'MANAGER'
      4  and sal > 2500;
    
    ENAME		  SAL JOB
    ---------- ---------- ---------
    ALLEN		 1600 SALESMAN
    WARD		 1250 SALESMAN
    JONES		 2975 MANAGER
    MARTIN		 1250 SALESMAN
    BLAKE		 2850 MANAGER
    TURNER		 1500 SALESMAN
    
    6 rows selected.
    

    因为工资大于2500这个条件和job等于经理这个条件是用and连接的,所以会先执行他们,最后的结果是工资大于2500且职位为经理。然后才执行or job=销售
    这个sql相当于:

    select ename,sal,job from emp
    where job = 'MANAGER'
    and sal >2500
    or job = 'MANAGER'
    

    如果有两个and的情况,就按顺序执行

    • 例2
    SQL> select ename,sal,job from emp
      2  where (job = 'SALESMAN'
      3  or job = 'MANAGER')
      4  and sal > 2500;
    
    ENAME		  SAL JOB
    ---------- ---------- ---------
    JONES		 2975 MANAGER
    BLAKE		 2850 MANAGER
    

    加了括号以后,会先执行括号内的
    SQL改写:(不改变结果,只改变效率)

    SQL> select ename,sal,job from emp
      2  where job in('MANAGER','SALESMAN')
      3  and sal > 2500;
    
    ENAME		  SAL JOB
    ---------- ---------- ---------
    JONES		 2975 MANAGER
    BLAKE		 2850 MANAGER
    

    4 排序:ORDER BY子句

    ASC:升序(默认可以不写)
    DESC:降序
    ORDER BY子句在SELECT语句的最后

    • 升序排序
    SQL> select ename,hiredate from emp
      2  order by hiredate;
    
    ENAME	   HIREDATE
    ---------- ---------
    SMITH	   17-DEC-80
    ALLEN	   20-FEB-81
    WARD	   22-FEB-81
    JONES	   02-APR-81
    BLAKE	   01-MAY-81
    CLARK	   09-JUN-81
    TURNER	   08-SEP-81
    MARTIN	   28-SEP-81
    KING	   17-NOV-81
    JAMES	   03-DEC-81
    FORD	   03-DEC-81
    MILLER	   23-JAN-82
    SCOTT	   19-APR-87
    ADAMS	   23-MAY-87
    
    14 rows selected.
    
    • 降序排序
    SQL> select ename,hiredate from emp
      2  order by hiredate desc;
    
    ENAME	   HIREDATE
    ---------- ---------
    ADAMS	   23-MAY-87
    SCOTT	   19-APR-87
    MILLER	   23-JAN-82
    FORD	   03-DEC-81
    JAMES	   03-DEC-81
    KING	   17-NOV-81
    MARTIN	   28-SEP-81
    TURNER	   08-SEP-81
    CLARK	   09-JUN-81
    BLAKE	   01-MAY-81
    JONES	   02-APR-81
    WARD	   22-FEB-81
    ALLEN	   20-FEB-81
    SMITH	   17-DEC-80
    
    14 rows selected.
    
    • 使用别名排序
    SQL> select ename,sal s from emp
      2  order by s;
    
    ENAME		    S
    ---------- ----------
    SMITH		  800
    JAMES		  950
    ADAMS		 1100
    WARD		 1250
    MARTIN		 1250
    MILLER		 1300
    TURNER		 1500
    ALLEN		 1600
    CLARK		 2450
    BLAKE		 2850
    JONES		 2975
    SCOTT		 3000
    FORD		 3000
    KING		 5000
    
    14 rows selected.
    
    • 将第三列排序
    SQL> select ename,job,sal,hiredate from emp
      2  order by 3;
    
    ENAME	   JOB		    SAL HIREDATE
    ---------- --------- ---------- ---------
    SMITH	   CLERK	    800 17-DEC-80
    JAMES	   CLERK	    950 03-DEC-81
    ADAMS	   CLERK	   1100 23-MAY-87
    WARD	   SALESMAN	   1250 22-FEB-81
    MARTIN	   SALESMAN	   1250 28-SEP-81
    MILLER	   CLERK	   1300 23-JAN-82
    TURNER	   SALESMAN	   1500 08-SEP-81
    ALLEN	   SALESMAN	   1600 20-FEB-81
    CLARK	   MANAGER	   2450 09-JUN-81
    BLAKE	   MANAGER	   2850 01-MAY-81
    JONES	   MANAGER	   2975 02-APR-81
    SCOTT	   ANALYST	   3000 19-APR-87
    FORD	   ANALYST	   3000 03-DEC-81
    KING	   PRESIDENT	   5000 17-NOV-81
    
    14 rows selected.
    
    • 多列排序(顺序根据需求)
    
    SQL> select deptno,ename,sal from emp
      2  order by deptno,sal desc;
    
        DEPTNO ENAME	     SAL
    ---------- ---------- ----------
    	10 KING 	    5000
    	10 CLARK	    2450
    	10 MILLER	    1300
    	20 SCOTT	    3000
    	20 FORD 	    3000
    	20 JONES	    2975
    	20 ADAMS	    1100
    	20 SMITH	     800
    	30 BLAKE	    2850
    	30 ALLEN	    1600
    	30 TURNER	    1500
    	30 MARTIN	    1250
    	30 WARD 	    1250
    	30 JAMES	     950
    
    14 rows selected.
    

    改写

    SQL> select deptno,ename,sal from emp
      2  order by 1,3 desc;
    
        DEPTNO ENAME	     SAL
    ---------- ---------- ----------
    	10 KING 	    5000
    	10 CLARK	    2450
    	10 MILLER	    1300
    	20 SCOTT	    3000
    	20 FORD 	    3000
    	20 JONES	    2975
    	20 ADAMS	    1100
    	20 SMITH	     800
    	30 BLAKE	    2850
    	30 ALLEN	    1600
    	30 TURNER	    1500
    	30 MARTIN	    1250
    	30 WARD 	    1250
    	30 JAMES	     950
    
    14 rows selected.
    

    5 替换变量

    替换变量的功能

    • 临时存储值
    • 利用它可以达到创建通用脚本的目的
    • 利用它可以达到和用户交互
      故在SQL *Plus中又称交互式命令

    5.1 &

    & :“&变量名”
    eg:&name;
    生命周期:单次引用中,不需要声明,如果替换字符或日期类型,最好用单引号扩起
    使用范围:where、order by、列表达式、表名、整个SELECT 语句中

    SQL> select ename,sal,deptno from emp
      2  where deptno = &no;
    Enter value for no: 10
    old   2: where deptno = &no
    new   2: where deptno = 10
    
    ENAME		  SAL	  DEPTNO
    ---------- ---------- ----------
    CLARK		 2450	      10
    KING		 5000	      10
    MILLER		 1300	      10
    SQL> l
      1  select ename,sal,deptno from emp
      2* where deptno = &no
    SQL> /
    Enter value for no: 20
    old   2: where deptno = &no
    new   2: where deptno = 20
    
    ENAME		  SAL	  DEPTNO
    ---------- ---------- ----------
    SMITH		  800	      20
    JONES		 2975	      20
    SCOTT		 3000	      20
    ADAMS		 1100	      20
    FORD		 3000	      20
    

    变量获取的是用户传进来的值。解析需要消耗CPU,有了变量就减少解析,不会过多地消耗CPU。

    替代变量可以替代以下内容:

    • WHERE条件
    • ORDER BY子句
    SQL> select ename,sal,deptno from emp
      2  where deptno = &no
      3  order by &a;
    Enter value for no: 20
    old   2: where deptno = &no
    new   2: where deptno = 20
    Enter value for a: 2
    old   3: order by &a
    new   3: order by 2
    
    ENAME		  SAL	  DEPTNO
    ---------- ---------- ----------
    SMITH		  800	      20
    ADAMS		 1100	      20
    JONES		 2975	      20
    FORD		 3000	      20
    SCOTT		 3000	      20
    
    • 列表达式
    • 表名
    SQL> select ename,sal,&col_name from &table_name
      2  where deptno = &no
      3  order by &a;
    Enter value for col_name: deptno,job
    Enter value for table_name: emp
    old   1: select ename,sal,&col_name from &table_name
    new   1: select ename,sal,deptno,job from emp
    Enter value for no: 20
    old   2: where deptno = &no
    new   2: where deptno = 20
    Enter value for a: 1
    old   3: order by &a
    new   3: order by 1
    
    ENAME		  SAL	  DEPTNO JOB
    ---------- ---------- ---------- ---------
    ADAMS		 1100	      20 CLERK
    FORD		 3000	      20 ANALYST
    JONES		 2975	      20 MANAGER
    SCOTT		 3000	      20 ANALYST
    SMITH		  800	      20 CLERK
    
    • 整个SELECT语句
    SQL> select &col_name
      2  from &table_name
      3  where deptno = &no
      4  order by &a;
    Enter value for col_name: ename,job,sal
    old   1: select &col_name
    new   1: select ename,job,sal
    Enter value for table_name: emp
    old   2: from &table_name
    new   2: from emp
    Enter value for no: 30
    old   3: where deptno = &no
    new   3: where deptno = 30
    Enter value for a: 2
    old   4: order by &a
    new   4: order by 2
    
    ENAME	   JOB		    SAL
    ---------- --------- ----------
    JAMES	   CLERK	    950
    BLAKE	   MANAGER	   2850
    TURNER	   SALESMAN	   1500
    WARD	   SALESMAN	   1250
    ALLEN	   SALESMAN	   1600
    MARTIN	   SALESMAN	   1250
    
    6 rows selected.
    
    
    SQL> select &col_name
      2  from &table_name
      3  where &where
      4  order by &a;
    Enter value for col_name: ename,sal,job,deptno
    old   1: select &col_name
    new   1: select ename,sal,job,deptno
    Enter value for table_name: emp
    old   2: from &table_name
    new   2: from emp
    Enter value for where: deptno in (10,20)
    old   3: where &where
    new   3: where deptno in (10,20)
    Enter value for a: 2,4
    old   4: order by &a
    new   4: order by 2,4
    
    ENAME		  SAL JOB	    DEPTNO
    ---------- ---------- --------- ----------
    SMITH		  800 CLERK		20
    ADAMS		 1100 CLERK		20
    MILLER		 1300 CLERK		10
    CLARK		 2450 MANAGER		10
    JONES		 2975 MANAGER		20
    SCOTT		 3000 ANALYST		20
    FORD		 3000 ANALYST		20
    KING		 5000 PRESIDENT 	10
    
    8 rows selected.
    

    5.2 &&

    “&&变量名”
    eg:&&name;
    生命周期:整个会话(session连接),不需要声明

    SQL> select ename,sal,deptno     
      2  from &&table_name
      3  where deptno = &no
      4  order by &a;
    Enter value for table_name: emp
    old   2: from &&table_name
    new   2: from emp
    Enter value for no: 30
    old   3: where deptno = &no
    new   3: where deptno = 30
    Enter value for a: deptno
    old   4: order by &a
    new   4: order by deptno
    
    ENAME		  SAL	  DEPTNO
    ---------- ---------- ----------
    ALLEN		 1600	      30
    WARD		 1250	      30
    MARTIN		 1250	      30
    BLAKE		 2850	      30
    TURNER		 1500	      30
    JAMES		  950	      30
    
    6 rows selected.
    
    SQL> /
    old   2: from &&table_name
    new   2: from emp
    Enter value for no: 10
    old   3: where deptno = &no
    new   3: where deptno = 10
    Enter value for a: 2
    old   4: order by &a
    new   4: order by 2
    
    ENAME		  SAL	  DEPTNO
    ---------- ---------- ----------
    MILLER		 1300	      10
    CLARK		 2450	      10
    KING		 5000	      10
    

    5.3 DEFINE命令

    define即host变量主要作用是起到一个替代变量的作用,是主机环境可以和oracle进行交互的一个媒介。通过define定义host变量的时候必须指定变量名和变量的值,如果变量名已经存在于host变量中,则自动覆盖,这个值不可以指定类型,一律按char存储。define变量需要预先声明,使用时用&引用声明的变量,生命周期是整个会话,但只在当前session环境中有效。

    • 语法
      define variable_name = value
    • 声明和初始化define变量
      声明define变量的时候必须同时初始化赋值变量
    SQL> define num = 1;
    SQL> define emp_deptno = 20
    
    • 通过define命令显示单个或全部的host变量的值和类型
      define变量类型都为char
    # 显示指定的define变量值和类型
    SQL> define num;
    DEFINE NUM	       = "1" (CHAR)
    SQL> define emp_deptno;
    DEFINE EMP_DEPTNO      = "20" (CHAR)
    
    # 显示所有的define变量值和类型
    SQL> define;
    DEFINE _DATE	       = "14-MAY-19" (CHAR)
    DEFINE _CONNECT_IDENTIFIER = "nsfcdc" (CHAR)
    DEFINE _USER	       = "SCOTT" (CHAR)
    DEFINE _PRIVILEGE      = "" (CHAR)
    DEFINE _SQLPLUS_RELEASE = "1102000400" (CHAR)
    DEFINE _EDITOR	       = "ed" (CHAR)
    DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
    DEFINE _O_RELEASE      = "1102000400" (CHAR)
    DEFINE EMP_DEPTNO      = "20" (CHAR)
    DEFINE NUM	       = "1" (CHAR)
    
    • 引用define变量
      在sqlplus和plsql developer的command window中都可以使用,在sql或plsql中引用host变量,使用&符号,相当于一个简单的替换动作
    SQL> select ename,sal,empno from emp
      2  where deptno=&emp_deptno;
    old   2: where deptno=&emp_deptno
    new   2: where deptno=20
    
    ENAME		  SAL	   EMPNO
    ---------- ---------- ----------
    SMITH		  800	    7369
    JONES		 2975	    7566
    SCOTT		 3000	    7788
    ADAMS		 1100	    7876
    FORD		 3000	    7902
    
    • 撤销define变量
    # 通过undefine命令撤销
    SQL> undefine emp_deptno;
    
    • 开启和关闭define变量定义功能
    set define on;
    set define off;
    
    • 查看当前用户下的所有表
    SQL> select * from tab;
    
    TNAME			       TABTYPE	CLUSTERID
    ------------------------------ ------- ----------
    BONUS			       TABLE
    DEPT			       TABLE
    EMP			       TABLE
    SALGRADE		       TABLE
    T1			       TABLE
    TEST_DEPT		       TABLE
    TEST_EMP		       TABLE
    
    7 rows selected.
    

    不预先声明初始化define变量,直接在sql中在字符串前面加&符号,会提示输入变量的值,然后替换。这种情况下是临时的变量,define查不到。

    经常使用host变量的场合是在写脚本的时候,脚本有些地方经常变化,其他地方固定,那么可以使用&引用。

    展开全文
  • Select 语句的优先级

    2010-04-12 10:53:00
    from on join where group by with cube 或 with rollup having select distinct order by top 转载于:https://www.cnblogs.com/sristc/archive/2010/04/12/1710025.html
  • mysql select 操作优先级

    2020-08-18 11:35:33
    (1) 单表查询操作 select filed1,filed2... form table where ... group by ... having .... order by ... ...
  • --如果是inner join, 放onwhere产生的结果一样,如果有outer join (left or right), 就有区别了, 因为on生效在先, 已经提前过滤了一部分数据, 而where生效在后.   转载于:...
  • 转自:... SELECT语句执行的顺序: (1)FROM <left_table> (2)ON <join_condition> (3)<join_type> JOIN <right_table> (4)WHERE <where_condit...
  • 2. select t.* from (select id from A where 分区=20181111) f join (select * from B where 分区=20181111) t on (f.id = t.id) 总结一: 1. 对于a join b,无论过滤条件放到on中还是where中,结果相同。 2...
  • SQL 优先级join>where

    2020-11-10 18:33:42
    SQL 优先级join>where
  • 一.下面举个例子来说明与from的sql语句 select empno ,ename, sal,emp.deptno,loc ...写完上面的语句以后,我们分别运行如下2个sql,分别查看 dept emp 表分别有多少记录。 select counut(*) from emp;
  • andor的优先级

    2020-08-28 17:22:17
    MySQL中andor的优先级MySQLphp MySQL MySQL中关于andor的运算,类似与*+的预算,举例如下 -- 这个返回返回全部数据 select * from table_a where 1 or 0 and 0; --这个返回空集 select * from table_a where ...
  • 但是正常拼写sql往往不能打到预期的效果,需要把and 的条件使用括号括起来或者or的条件括起来才会达到预期的效果,这是因为where条件中 and优先与or,所以加上括号就可以改变优先级关系。 例如: select * from ...
  • select join where执行顺序

    千次阅读 2016-03-16 16:15:13
    select xxxxx from t1 join t2 on t1.sss = t2.dddd where t1.mmm &gt; 3;   先执行 t1 join t2 在执行 where 条件 最后在执行 select xxxx   如果此时select xxx的字段是你重新命名的别名 , 那么...
  • 查询语句中select from where group by having order by的执行顺序 1.查询中用到的关键词主要包含六个,并且他们的顺序依次为 select–from–where–group by–having–order by ...其中select和from是必须的,其...
  • 原文:...select>from>where>group by>having>order by 其中select和from是必须的,其他关键词是可选的,这六个关键词的执行顺序与sql语句的书写顺序并不是一样的,而是按照下面的顺序来执行: f
  • 数据库sql中andor优先级的问题

    千次阅读 2015-12-29 18:08:27
    or 优先级 倒数第一 and 优先级 倒数第二 select * from AA where a >1 or b>1 and C>1 等同 select * from AA where a >1 or (b>1 and C>1 )
  • 原始sqlSELECT b.school_id , COUNT(school_id) shuliang FROM Sheet1 a join tag b ON a.学校编号=b.school_id GROUP BY school_id where COUNT(school_id)&lt;90;报错 !原因:大致解释如下,sql语句的执行...
  • SELECT DISTINCT column, AGG_FUNC(column_or_expression), … FROM mytable JOIN another_table ON mytable.column = another_table.column WHERE constraint_expression GROUP BY column HAVING constraint_...
  • sql中andor的优先级

    千次阅读 2018-06-12 15:32:20
     如 SELECT * FROM USER WHERE ID = 1 AND STATUS = 2 OR STATUS = 3,本来是想查询USER表中id为1的状态为2或者3的数据,其实只会这样执行,and比or优先,结果是查询id为1状态为2,或者状态为3的数据。 解决方...
  • where子句_等值条件非等值条件_模糊查询_is null_优先级1.等值条件2.非等值条件3.模糊查询4.IS NULL5.2.andor的优先级 1.等值条件 查询10部门所有员工的信息 select * from emp where deptno=10; 查询JAKE的...
  • SELECT语句 mysql&amp;amp;amp;gt; SELECT * FROM test; SELECT name FROM test; 表示从test中显示列名为name的行 SELECT name,address FROM test; 显示列名为name,address的行 (my...
  • 在SQL中执行的顺序 先连接from后的数据源(若有join,则先执行on后条件,再连接数据源)。 执行where条件 执行group by 4.执行having 5.执行order by 6.输出结果。...SELECT DISTINCT ORDER BY TOP ...
  • select * from student where sex='男' and age=20;   SELECT * FROM Persons WHERE City='Beijing' SELECT * FROM Persons WHERE Year&gt;1965 递增: SELECT Company, OrderNum...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 41,376
精华内容 16,550
关键字:

select和where的优先级