SQL基本查询大全

叁歲伎倆 2021-08-28 15:51 367阅读 0赞

## SQL简单查询 ##

表中**字符串常量**区分大小写，**表名**、**字段名**和**SQL命令**不区分大小写。

以`oracle`里这两张`EMP`表和`DEPT`表为例：

### EMP表的表头： ###

名称                                      是否为空? 类型
     ----------------------------------------- -------- ----------------------------
     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)

### EMP表的表体： ###

EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
     ---- ---------- --------- ---------- -------------- ---------- ---------- ----------
     7369 SMITH      CLERK           7902 17-12月-80            800                    20
     7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
     7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
     7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
     7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
     7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30
     7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
     7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20
     7839 KING       PRESIDENT            17-11月-81           5000                    10
     7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30
     7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20
     7900 JAMES      CLERK           7698 03-12月-81            950                    30
     7902 FORD       ANALYST         7566 03-12月-81           3000                    20
     7934 MILLER     CLERK           7782 23-1月 -82           1300                    10

### DEPT表的表头： ###

名称                                      是否为空? 类型
     ----------------------------------------- -------- ----------------------------
     DEPTNO                                    NOT NULL NUMBER(2)
     DNAME                                              VARCHAR2(14)
     LOC                                                VARCHAR2(13)

### DEPT表的表体： ###

DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON

### 1、`SELECT`子句和`FROM`子句： ###

#### **按列**查询： ####

SELECT [ALL|DISTINCT] 列名1，列名2	//SELECT后面的列名就是查询出来后显示的列
    FROM 表名|视图名						//ALL一般省略，DISTINCT是结果去掉重复行
    
    SQL> SELECT empno,ename
      2  FROM 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 *						   // * 就是查询表中所有列
    FROM 表名
    
    SQL> SELECT *
      2  FROM emp;
    
    EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
    ---- ---------- --------- ---------- -------------- ---------- ---------- ----------
    7369 SMITH      CLERK           7902 17-12月-80            800                    20
    7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
    7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
    7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
    7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
    7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30
    7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
    7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20
    7839 KING       PRESIDENT            17-11月-81           5000                    10
    7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30
    7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20
    7900 JAMES      CLERK           7698 03-12月-81            950                    30
    7902 FORD       ANALYST         7566 03-12月-81           3000                    20
    7934 MILLER     CLERK           7782 23-1月 -82           1300                    10

#### `AS`可以**改变**查询到的列的标题： ####

SELECT 表中字段名1 AS 你想要的标题名1,表中字段名2 AS 你想要的标题名2
    FROM 表名
    
    SQL> SELECT empno AS 员工编号,hiredate AS "入职 时间" //标题有空格需要加" "
      2  FROM emp;
    
      员工编号 入职 时间
    ---------- --------------
          7369 17-12月-80
          7499 20-2月 -81
          7521 22-2月 -81
          7566 02-4月 -81
          7654 28-9月 -81
          7698 01-5月 -81
          7782 09-6月 -81
          7788 19-4月 -87
          7839 17-11月-81
          7844 08-9月 -81
          7876 23-5月 -87
          7900 03-12月-81
          7902 03-12月-81
          7934 23-1月 -82

#### 可以显示**计算**后的列： ####

SELECT 字段名[+|-|*|/|%]			//把查询到的结果进行算术运算再输出
    FROM 表名
    
    SQL> SELECT sal+300 AS 工资加三百,sal+comm AS 工资和奖金
      2  FROM emp;
    
    工资加三百 工资和奖金
    ---------- ----------
          1100
          1900       1900
          1550       1750
          3275
          1550       2650
          3150
          2750
          3300
          5300
          1800       1500
          1400
          1250
          3300
          1600

#### `DISTINCT`去掉重复行： ####

SELECT DISTINCT 列名，列名
    FROM 表名
    
    SQL> SELECT DISTINCT sal			//去掉了sal这一字段重复的行
      2  FROM emp;
    
           SAL
    ----------
          2450
          5000
          1300
          1250
          2850
          2975
          1100
          3000
           800
          1600
          1500
           950

SQL> SELECT DISTINCT deptno			//去掉了deptno这一字段重复的行
      2  FROM emp;
    
        DEPTNO
    ----------
            30
            20
            10

SQL> SELECT DISTINCT sal,deptno		//去掉了sal和deptno都重复的行
      2  FROM emp;						//只重复sal或deptno不会被去掉
    
           SAL     DEPTNO
    ---------- ----------
          5000         10
          1300         10
          1600         30
          1250         30
          2450         10
          3000         20
           800         20
          2850         30
          1500         30
          1100         20
           950         30
          2975         20

### 2、`WHERE`子句： ###

#### 基本格式： ####

SELECT 列名
    FROM 表名|视图
    WHERE 条件
    
    SQL> SELECT *
      2  FROM emp
      3  WHERE ename='SMITH';			//字符串必须用单引号括起来
    
    EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
    ----- ---------- --------- ---------- -------------- ---------- ---------- ----------
     7369 SMITH      CLERK           7902 17-12月-80            800                    20
     
    SQL> SELECT *
      2  FROM emp
      3  WHERE hiredate='17-12月-80';	//日期必须单引号括起来，默认格式'DD-MM月-YYYY'
    
    EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
    ----- ---------- --------- ---------- -------------- ---------- ---------- ----------
     7369 SMITH      CLERK           7902 17-12月-80            800                    20

#### 比较运算符： ####

`=`等于，`<`小于，`>`大于，`<=`小于等于，`>=`大于等于，`<>`或`!=`不等于，`!>`不大于，`!<`不小于。

SQL> SELECT *
      2  FROM emp
      3  WHERE sal>2000;			//查询sal大于2000的所有列
    
    EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
    ----- ---------- --------- ---------- -------------- ---------- ---------- ----------
     7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
     7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30
     7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
     7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20
     7839 KING       PRESIDENT            17-11月-81           5000                    10
     7902 FORD       ANALYST         7566 03-12月-81           3000                    20

#### 逻辑运算符： ####

`AND`与，`OR`或，`NOT`非

SELECT 列名
    FROM 表名
    WHERE [NOT] 条件1 {
       AND|OR} [NOT] 条件2
    
    SQL> SELECT *
      2  FROM emp
      3  WHERE sal>1000 AND NOT deptno=20;	//查询sal大于1000并且deptno不等于20的所有列
    等价于WHERE sal>1000 AND deptno!=20;
    
    EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
    ----- ---------- --------- ---------- -------------- ---------- ---------- ----------
     7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
     7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
     7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
     7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30
     7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
     7839 KING       PRESIDENT            17-11月-81           5000                    10
     7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30
     7934 MILLER     CLERK           7782 23-1月 -82           1300                    10

SQL> SELECT *
      2  FROM emp
      3  WHERE NOT (sal>1000 AND job='SALESMAN');
      									//查询不是（工资大于1000且工作是SALESMAN）的所有列
    
    EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
    ----- ---------- --------- ---------- -------------- ---------- ---------- ----------
     7369 SMITH      CLERK           7902 17-12月-80            800                    20
     7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
     7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30
     7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
     7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20
     7839 KING       PRESIDENT            17-11月-81           5000                    10
     7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20
     7900 JAMES      CLERK           7698 03-12月-81            950                    30
     7902 FORD       ANALYST         7566 03-12月-81           3000                    20
     7934 MILLER     CLERK           7782 23-1月 -82           1300                    10

#### 字符串匹配： ####

SELECT 列名
    FROM 表名
    WHERE 字段 = 'ABCDFG'				//查询字段='ABCDEF'
    	  字段 LIKE '%A'				//匹配字段前面任意字符且最后是A
    	  字段 LIKE 'A%'				//匹配字段后面任意字符且首个是A
    	  字段 LIKE '%A%'				//匹配字段前后任意字符且中间是A
    	  字段 LIKE 'A_'				//匹配字段倒数第二个字符是A
    	  字段 LIKE '_A'				//匹配字段第二个字符是A
    	  字段 LIKE '%A_'				//匹配字段前面任意字符但倒数第二是A
    	  字段 NOT LIKE '%A'			//匹配字段不是前面任意字符且最后是A
    	  
    SQL> SELECT *
      2  FROM emp
      3  WHERE ename LIKE '%M%';	 //查询ename中间包含M的所有列
    
    EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
    ----- ---------- --------- ---------- -------------- ---------- ---------- ----------
     7369 SMITH      CLERK           7902 17-12月-80            800                    20
     7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
     7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20
     7900 JAMES      CLERK           7698 03-12月-81            950                    30
     7934 MILLER     CLERK           7782 23-1月 -82           1300                    10

#### 范围比较： ####

SELECT 列名
    FROM 表名
    WHERE 字段 [NOT] BETWEEN 表达式 AND 表达式			//闭区间
    
    SQL> SELECT *
      2  FROM emp
      3  WHERE hiredate BETWEEN '1-1月-1981' AND '1-1月-1983';
      							//查询入职日期是在1981.1.1到1983.1.1之间的所有列
      						
    EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
    ----- ---------- --------- ---------- -------------- ---------- ---------- ----------
     7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
     7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
     7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
     7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
     7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30
     7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
     7839 KING       PRESIDENT            17-11月-81           5000                    10
     7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30
     7900 JAMES      CLERK           7698 03-12月-81            950                    30
     7902 FORD       ANALYST         7566 03-12月-81           3000                    20
     7934 MILLER     CLERK           7782 23-1月 -82           1300                    10

SQL> SELECT *
      2  FROM emp
      3  WHERE sal NOT BETWEEN 1500 AND 3000;
      							//查询工资不在1500到3000之间的所有列
    
    EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
    ----- ---------- --------- ---------- -------------- ---------- ---------- ----------
     7369 SMITH      CLERK           7902 17-12月-80            800                    20
     7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
     7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
     7839 KING       PRESIDENT            17-11月-81           5000                    10
     7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20
     7900 JAMES      CLERK           7698 03-12月-81            950                    30
     7934 MILLER     CLERK           7782 23-1月 -82           1300                    10

#### 查询列表：（多个类似条件一起查询） ####

SELECT 列名
    FROM 表名
    WHERE 字段 [NOT] IN(值1，值2)
    
    SQL> SELECT *
      2  FROM emp
      3  WHERE job IN('CLERK','SALESMAN','MANAGER');
    									//查询工作是CLERK或SALESMAN或MANAGER
    
    EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
    ----- ---------- --------- ---------- -------------- ---------- ---------- ----------
     7369 SMITH      CLERK           7902 17-12月-80            800                    20
     7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
     7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
     7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
     7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
     7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30
     7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
     7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30
     7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20
     7900 JAMES      CLERK           7698 03-12月-81            950                    30
     7934 MILLER     CLERK           7782 23-1月 -82           1300                    10

#### 判断空值： ####

SELECT 列
    FROM 表
    WHERE 字段 IS [NOT] NULL					//判断字段是否为空

### 3、`ORDER BY`子句： ###

SELECT 列
    FROM 表
    ORDER BY 字段1|表达式1 [ASC|DESC][,字段2|表达式2 [ASC|DESC]]
    						//ASC升序排序（可省略），DESC降序排序，默认升序
    
    SQL> SELECT ename,sal
      2  FROM emp
      3  ORDER BY 2;		//根据第二列排序，也就是根据sal升序排序
    等价于ORDER BY sal
    
    ENAME             SAL
    ---------- ----------
    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

SQL> SELECT ename,sal,deptno
      2  FROM emp
      3  WHERE job='SALESMAN'
      4  ORDER BY deptno,sal DESC;			//根据deptno升序排序，sal降序排序
    										//如果deptno有相同值，再根据sal排序
    ENAME             SAL     DEPTNO
    ---------- ---------- ----------
    ALLEN            1600         30
    TURNER           1500         30
    MARTIN           1250         30
    WARD             1250         30

#### 统计函数： ####

<table> 
 <thead> 
  <tr> 
   <th align="left">函数名</th> 
   <th align="left">描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">AVG([DISTINCT]表达式)</td> 
   <td align="left">求<strong>平均值</strong>，DISTINCT是去掉重复值再求平均值，忽略空值</td> 
  </tr> 
  <tr> 
   <td align="left">MAX(表达式)</td> 
   <td align="left">返回<strong>最大值</strong></td> 
  </tr> 
  <tr> 
   <td align="left">MIN(表达式)</td> 
   <td align="left">返回<strong>最小值</strong></td> 
  </tr> 
  <tr> 
   <td align="left">SUM([DISTINCT]表达式)</td> 
   <td align="left"><strong>求和</strong>，DISTINCT是不算重复的值</td> 
  </tr> 
  <tr> 
   <td align="left">COUNT([DISTINCT]字段)</td> 
   <td align="left">返回该字段的<strong>记录数</strong>，也就是有多少行，DISTINCT忽略重复行</td> 
  </tr> 
  <tr> 
   <td align="left">COUNT(*)</td> 
   <td align="left">返回所有记录的行数</td> 
  </tr> 
 </tbody> 
</table>

SQL> SELECT AVG(sal) AS 平均工资,SUM(sal) AS 工资总和
      2  FROM emp;
    
      平均工资   工资总和
    ---------- ----------
    2073.21429      29025
    
    SQL> SELECT COUNT(*) AS 总人数
      2  FROM emp;
    
        总人数
    ----------
            14

### 4、`GROUP BY`子句： ###

SELECT 列
    FROM 表
    WHERE 条件
    GROUP BY 条件1[,条件2]				//先按条件1分组再按条件2分组

优先级是`WHERE`>`GROUP BY`：先将满足WHERE条件的结果查询出来再按GROUP BY进行分组

SQL> SELECT deptno AS 部门编号,COUNT(*) AS 员工数,AVG(sal) AS 平均工资,AVG(MONTHS_BETWEEN(sysdate,hiredate)/12) AS 平均工作年限
      2  FROM emp
      3  GROUP BY deptno;
      
      部门编号     员工数   平均工资 平均工作年限
    ---------- ---------- ---------- ------------
            30          6 1566.66667   38.7781976
            20          5       2175   36.5058802
            10          3 2916.66667   38.4797153

先将deptno相同的行分成一组，一共有3组，分别是deptno=10的一组，deptno=20的一组，deptno=30的一组，然后再分别在组里使用COUNT(\*)求所有记录数函数求出各组的行数（也就是每组的人数），同理也在会在组里使用AVG(sal)函数求出该组的sal列的平均值（也就是该组的平均工资）。

### 5、`HAVING`子句： ###

进一步将`GROUP BY`子句的结果进行筛选然后再输出

SELECT 列
    FROM 表
    WHERE 条件
    GROUP BY 条件
    HAVING 条件
    
    SQL> SELECT deptno AS 部门编号,COUNT(*) AS 员工数,AVG(sal) AS 平均工资,AVG(MONTHS_BETWEEN(sysdate,hiredate)/12) AS 平均工作年限
      2  FROM emp
      3  GROUP BY deptno
      4  HAVING AVG(sal)>2000;
    
      部门编号     员工数   平均工资 平均工作年限
    ---------- ---------- ---------- ------------
            20          5       2175   36.5058989
            10          3 2916.66667    38.479734

就是刚才的GROUP BY分组后的结果再按平均工资大于2000的条件筛选后再输出  
优先级是`WHERE`>`GROUP BY`>`HAVING`：先查询符合WHERE条件的结果，再按GROUP BY排序，再按HAVING的条件筛选然后输出