01.SQL 基础-->SELECT 查询

 

SQL 基础-->SELECT 查询

一、SQL 结构化查询语言

包括DDL（数据定义语言）、DCL（数据控制语言）、DQL（数据查询语言）、DML（数据操纵语言）

二、SQL的特点

SQL 语句不区分大小写
SQL 语句能输入一行或多行
关键字不能整行缩写或分离
子句通常被放置在分开的行上
缩进可提高可读性

在SQL 开发工具，SQL 语句能选择分号结束(;) .当你运行多个SQL 语句的时候，需要分号

在SQL*Plus中, 你要用一个分号结束每个SQL 语句.(;)


三、SQL*PLUS特征：

字符日期左对齐
数字右对对齐
列名默认大写
SQL PLUS 自己的命令不需以分号“；”结束


四、SQL查询时，数字和日期类型的数据可用算术运算符

 + 加
－ 减
＊ 乘
／ 除
( ) 用于改变运算符的优先级


五、空值

空值一般用NULL表示
一般表示未知的、不确定的值，也不是空格
一般运算符与其进行运算时，都会为空
空不与任何值相等


表示某个列为空用：IS NULL 不能使用COMM＝NULL这种形式
某个列不为空：IS NOT NULL 不能使用COMM != NULL 这种形式

 

空值在作升序排列时，空值会放到最后。
相反作降序排列时，空值会放在最前。

空值作逻辑运算时：

AND运算：

 

F AND F ＝F       F AND T ＝F         F AND NULL ＝F
T AND F ＝F       T AND T ＝T         T AND NULL IS NULL
NULL AND F ＝F    NULL AND T IS NULL    NULL AND NULL IS NULL

　　

就是说AND的优先级是：F －＞NULL －＞T


OR运算：

1
2
3
T OR T ＝T     T OR F ＝T             T OR NULL ＝T
F OR T ＝T     F OR F ＝F             F OR NULL IS NULL
NULL OR T ＝T  NULL OR F IS NULL     NULL OR NULL IS NULL

 

OR运算优先级：T －＞NULL －＞F


NOT运算：

1
2
3
NOT T ＝F
NOT F ＝T
NOT NULL IS NULL

与空值相关的函数：

NVL 函数

格式：NVL（表达式1，表达式2）
作用：测试表达式的值，如果表达式1为空，则返回表达式2的值；不为空，返回表达式1的值。

NVL2 函数

格式：NVL2（表达式1，表达式2，表达式3）
作用：测试表达式的值，表达式1不为空，返回表达式2的值，如果为空，则返回表达式3的值。

NULLIF 相等为空

格式：NULLIF （表达式1，表达式2）
作用：比较表达式1和表达式2的值，如果两个相等则返回为空，否则返回表达式1的值。

COALESCE 找非空

格式：COALESCE （表达式1，表达式2，表达式3，...，表达式n)
作用：返回第一个不为空的值，如果所有的都为空，则返回NULL。


六、SELECT语句的用法

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

 

七、演示 */


--选择所有字段

SET LINESIZE 200
SELECT * FROM SCOTT.EMP;

1
2
3
4
5
6
7
8
9
idle> SET LINESIZE 200
SELECT * FROM SCOTT.EMP;
idle>
     EMPNO ENAME      JOB          MGR HIREDATE      SAL       COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK       7902 1980-12-17        800            20
      7499 ALLEN      SALESMAN        7698 1981-02-20       1600    300     30
      7521 WARD       SALESMAN        7698 1981-02-22       1250    500     30
      7566 JONES      MANAGER         7839 1981-04-02       2975            20

--选择部分字段

SELECT EMPNO,ENAME,SAL FROM SCOTT.EMP;

1
2
3
4
5
6
7
8
idle>  SELECT EMPNO,ENAME,SAL FROM SCOTT.EMP;
 
     EMPNO ENAME         SAL
---------- ---------- ----------
      7369 SMITH         800
      7499 ALLEN        1600
      7521 WARD         1250
      7566 JONES        2975

--算术加减运算

SELECT EMPNO,ENAME,SAL + 300 FROM SCOTT.EMP;

1
2
3
4
5
6
7
8
idle> SELECT EMPNO,ENAME,SAL + 300 FROM SCOTT.EMP;
 
     EMPNO ENAME     SAL+300
---------- ---------- ----------
      7369 SMITH        1100
      7499 ALLEN        1900
      7521 WARD         1550
      7566 JONES        3275

 

--优先级

SELECT EMPNO,ENAME,12 * (SAL + 300) FROM SCOTT.EMP;

1
2
3
4
5
6
7
8
idle> SELECT EMPNO,ENAME,12 * (SAL + 300) FROM SCOTT.EMP;
 
     EMPNO ENAME      12*(SAL+300)
---------- ---------- ------------
      7369 SMITH         13200
      7499 ALLEN         22800
      7521 WARD          18600
      7566 JONES         39300

 

SQL> SELECT EMPNO,ENAME,12 * SAL + 300 FROM SCOTT.EMP;

1
2
3
4
5
6
7
8
idle> SELECT EMPNO,ENAME,12 * SAL + 300 FROM SCOTT.EMP;
 
     EMPNO ENAME      12*SAL+300
---------- ---------- ----------
      7369 SMITH        9900
      7499 ALLEN       19500
      7521 WARD        15300
      7566 JONES       36000

 

--NULL,记录中COMM存在为NULL的情况

SELECT EMPNO,ENAME,SAL,COMM FROM SCOTT.EMP;

1
2
3
4
5
6
7
8
idle> SELECT EMPNO,ENAME,SAL,COMM FROM SCOTT.EMP;
 
     EMPNO ENAME         SAL       COMM
---------- ---------- ---------- ----------
      7369 SMITH         800
      7499 ALLEN        1600    300
      7521 WARD         1250    500
      7566 JONES        2975

--与NULL运算，结果为NULL

SELECT EMPNO,ENAME,SAL,COMM + 300 FROM SCOTT.EMP; 

1
2
3
4
5
6
7
8
idle>  SELECT EMPNO,ENAME,SAL,COMM + 300 FROM SCOTT.EMP;
 
     EMPNO ENAME         SAL   COMM+300
---------- ---------- ---------- ----------
      7369 SMITH         800
      7499 ALLEN        1600    600
      7521 WARD         1250    800
      7566 JONES        2975

 

--将COMM不为NULL的记录的COMM乘以

SQL> SELECT EMPNO,ENAME,SAL,COMM * 12 FROM SCOTT.EMP WHERE COMM IS NOT NULL

1
2
3
4
5
6
7
8
idle>  SELECT EMPNO,ENAME,SAL,COMM * 12 FROM SCOTT.EMP WHERE COMM IS NOT NULL;
 
     EMPNO ENAME         SAL    COMM*12
---------- ---------- ---------- ----------
      7499 ALLEN        1600       3600
      7521 WARD         1250       6000
      7654 MARTIN       1250      16800
      7844 TURNER       1500      0

--字段别名,字段后用AS 别名，AS可以省略

SELECT EMPNO,ENAME AS EmpName,SAL Salary FROM SCOTT.EMP;

1
2
3
4
5
6
7
8
idle> SELECT EMPNO,ENAME AS EmpName,SAL Salary FROM SCOTT.EMP;
 
     EMPNO EMPNAME    SALARY
---------- ---------- ----------
      7369 SMITH         800
      7499 ALLEN        1600
      7521 WARD         1250
      7566 JONES        2975

--DISTINCT，过滤重复行

SELECT DISTINCT EMPNO,ENAME FROM SCOTT.EMP; 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
idle> SELECT DISTINCT EMPNO,ENAME FROM SCOTT.EMP;
 
     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

--连接操作符,通过二个垂直的条描述(||),注意，日期和文字数值一定嵌入在单引号里面

SELECT EMPNO,ENAME || ' IS A ' ||JOB AS POSITION FROM SCOTT.EMP;

1
2
3
4
5
6
7
8
idle>  SELECT EMPNO,ENAME || ' IS A ' ||JOB AS POSITION FROM SCOTT.EMP;
 
     EMPNO POSITION
---------- -------------------------
      7369 SMITH IS A CLERK
      7499 ALLEN IS A SALESMAN
      7521 WARD IS A SALESMAN
      7566 JONES IS A MANAGER

 

--DESC table_name,显示表结构信息

DESC SCOTT.EMP

1
2
3
4
5
6
7
8
9
10
11
12
idle> set line 50
idle> DESC SCOTT.EMP
 Name            Null?    Type
 ----------------------- -------- ----------------
 EMPNO           NOT NULL NUMBER(4)
 ENAME                VARCHAR2(10)
 JOB                  VARCHAR2(9)
 MGR                  NUMBER(4)
 HIREDATE             DATE
 SAL                  NUMBER(7,2)
 COMM                 NUMBER(7,2)
 DEPTNO               NUMBER(2)

 

--NVL的用法

SELECT EMPNO,ENAME,NVL(TO_CHAR(COMM),'Not Applicable') FROM SCOTT.EMP;

 

1
2
3
4
5
6
7
8
9
10
idle>  SELECT EMPNO,ENAME,NVL(TO_CHAR(COMM),'Not Applicable') FROM SCOTT.EMP;
 
     EMPNO ENAME      NVL(TO_CHAR(COMM),'NOTAPPLICABLE')
---------- ---------- ----------------------------------------
      7369 SMITH      Not Applicable
      7499 ALLEN      300
      7521 WARD       500
      7566 JONES      Not Applicable
      7654 MARTIN     1400
      7698 BLAKE      Not Applicable

--NVL2的用法

SELECT empno,ename,sal,NVL2(TO_CHAR(comm),12 * (sal + comm),sal) AS Income FROM scott.emp;

1
2
3
4
5
6
7
8
9
idle> SELECT empno,ename,sal,NVL2(TO_CHAR(comm),12 * (sal + comm),sal) AS Income FROM scott.emp;
 
     EMPNO ENAME         SAL     INCOME
---------- ---------- ---------- ----------
      7369 SMITH         800    800
      7499 ALLEN        1600      22800
      7521 WARD         1250      21000
      7566 JONES        2975       2975
      7654 MARTIN       1250      31800

 

--NULLIF的用法

--等价于CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END

SQL>SELECT e.last_name, NULLIF(e.job_id, j.job_id) "Old Job ID"
FROM hr.employees e, hr.job_history j 
WHERE e.employee_id = j.employee_id 
ORDER BY last_name, "Old Job ID";

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
idle> SELECT e.last_name, NULLIF(e.job_id, j.job_id) "Old Job ID"
FROM hr.employees e, hr.job_history j
WHERE e.employee_id = j.employee_id
ORDER BY last_name, "Old Job ID";  2    3    4 
 
LAST_NAME         Old Job ID
------------------------- ----------
De Haan           AD_VP
Hartstein         MK_MAN
Kaufling          ST_MAN
Kochhar           AD_VP
Kochhar           AD_VP
Raphaely          PU_MAN
Taylor            SA_REP
Taylor
Whalen            AD_ASST
Whalen

　　

 

--下面是使用CASE WHEN的等价用法

SELECT e.last_name, CASE WHEN e.job_id = j.job_id THEN NULL ELSE e.job_id END AS "Old Job ID"
FROM hr.employees e, hr.job_history j
WHERE e.employee_id = j.employee_id
ORDER BY last_name, "Old Job ID";

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
idle> SELECT e.last_name, CASE WHEN e.job_id = j.job_id THEN NULL ELSE e.job_id END AS "Old Job ID"
FROM hr.employees e, hr.job_history j
WHERE e.employee_id = j.employee_id
ORDER BY last_name, "Old Job ID";  2    3    4 
 
LAST_NAME         Old Job ID
------------------------- ----------
De Haan           AD_VP
Hartstein         MK_MAN
Kaufling          ST_MAN
Kochhar           AD_VP
Kochhar           AD_VP
Raphaely          PU_MAN
Taylor            SA_REP
Taylor
Whalen            AD_ASST
Whalen

　　

--COALESCE的用法

--当COALESCE(exp1,exp2)包含两个表达式时，等价于CASE WHEN exp1 IS NOT NULL THEN exp1 ELSE exp2 END
--COALESCE (expr1, expr2, ..., exprn), for n>=3
--当n >= 3时，等价于
--CASE WHEN expr1 IS NOT NULL THEN expr1
-- ELSE COALESCE (expr2, ..., exprn) END

SELECT product_id, list_price, min_price,
COALESCE(0.9*list_price, min_price, 5) "Sale" 
FROM oe.product_information 
WHERE supplier_id = 102050 
ORDER BY product_id, list_price, min_price, "Sale";

　　

 

1
2
3
4
5
6
7
8
9
10
11
12
13
idle>  SELECT product_id, list_price, min_price,
COALESCE(0.9*list_price, min_price, 5) "Sale"
FROM oe.product_information
WHERE supplier_id = 102050
ORDER BY product_id, list_price, min_price, "Sale";  2    3    4    5 
 
PRODUCT_ID LIST_PRICE  MIN_PRICE       Sale
---------- ---------- ---------- ----------
      1769     48              43.2
      1770            73     73
      2378    305        247      274.5
      2382    850        731    765
      3355                5

 

posted @ 2017-12-22 10:24  zhuntidaoren  阅读(231)  评论(0)  编辑  收藏  举报