MySQL 字段控制查询

一、 去除重复查询

去除重复记录（两行或两行以上记录中系列的上的数据都相同），例如employee表中sal字段就存在相同的记录。当只查询employee表的sal字段时，那么会出现重复记录，那么想去除重复记录，需要使用DISTINCT

SELECT DISTINCT sal FROM employee; 

mysql> SELECT DISTINCT sal FROM employee; 
+---------+
| sal     |
+---------+
|  800.00 |
| 1600.00 |
| 1250.00 |
| 2975.00 |
| 2850.00 |
| 2450.00 |
| 3000.00 |
| 1500.00 |
| 1100.00 |
|  950.00 |
| 1300.00 |
+---------+

二、 查看雇员的月薪与佣金之和

因为sal和comm两列的类型都是数值类型，所以可以做加运算。
注意：如果sal或comm中有一个字段不是数值类型，那么会出错。

SELECT empno, ename, sal+comm FROM employee;

+-------+--------+----------+
| empno | ename  | sal+comm |
+-------+--------+----------+
|  7369 | SMITH  |     NULL |
|  7499 | ALLEN  |  1900.00 |
|  7521 | WARD   |  1750.00 |
|  7566 | JONES  |     NULL |
|  7654 | MARTIN |  2650.00 |
|  7698 | BLAKE  |     NULL |
|  7782 | CLARK  |     NULL |
|  7788 | SCOTT  |     NULL |
|  7844 | TURNER |  1500.00 |
|  7876 | ADAMS  |     NULL |
|  7900 | JAMES  |     NULL |
|  7902 | FORD   |     NULL |
|  7934 | MILLER |     NULL |
+-------+--------+----------+

出现NULL值的处理

comm列有很多记录的值为NULL，因为任何东西与NULL相加结果还是NULL，
所以结算结果可能会出现NULL。下面使用了把NULL转换成数值0的函数IFNULL：

SELECT *,sal+IFNULL(comm,0) FROM emp;

过滤 comm NULL 查询结果：

mysql> SELECT *,sal+IFNULL(comm,0) FROM employee;
+-------+--------+----------+------+------------+---------+---------+--------+--------------------+
| empno | ename  | job      | mgr  | hiredata   | sal     | comm    | deptno | sal+IFNULL(comm,0) |
+-------+--------+----------+------+------------+---------+---------+--------+--------------------+
|  7369 | SMITH  | CLERK    | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |             800.00 |
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |            1900.00 |
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |            1750.00 |
|  7566 | JONES  | MANAGER  | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |            2975.00 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |            2650.00 |
|  7698 | BLAKE  | MANAGER  | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |            2850.00 |
|  7782 | CLARK  | MANAGER  | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |            2450.00 |
|  7788 | SCOTT  | ANALYST  | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |            3000.00 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |            1500.00 |
|  7876 | ADAMS  | CLERK    | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |            1100.00 |
|  7900 | JAMES  | CLERK    | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |             950.00 |
|  7902 | FORD   | ANALYST  | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |            3000.00 |
|  7934 | MILLER | CLERK    | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |            1300.00 |
+-------+--------+----------+------+------------+---------+---------+--------+--------------------+

二 、给列起别名 AS

查询中出现列名为sal+IFNULL(comm,0)，给这个总和金额取一个别名为 total：
给列起别名

SELECT *, sal+IFNULL(comm,0) AS total FROM employee;

+-------+--------+----------+------+------------+---------+---------+--------+---------+
| empno | ename  | job      | mgr  | hiredata   | sal     | comm    | deptno | total   | 最后 total 计算的总和
+-------+--------+----------+------+------------+---------+---------+--------+---------+
|  7369 | SMITH  | CLERK    | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |  800.00 |
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 | 1900.00 |
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 | 1750.00 |
|  7566 | JONES  | MANAGER  | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 | 2975.00 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 | 2650.00 |
|  7698 | BLAKE  | MANAGER  | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 | 2850.00 |
|  7782 | CLARK  | MANAGER  | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 | 2450.00 |
|  7788 | SCOTT  | ANALYST  | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 | 3000.00 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 | 1500.00 |
|  7876 | ADAMS  | CLERK    | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 | 1100.00 |
|  7900 | JAMES  | CLERK    | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |  950.00 |
|  7902 | FORD   | ANALYST  | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 | 3000.00 |
|  7934 | MILLER | CLERK    | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 | 1300.00 |
+-------+--------+----------+------+------------+---------+---------+--------+---------+