一.單表查詢
(一). 查詢字段
I.單個字段
格式： select 字段名 from 表名；
說明：
① select 和from都是關鍵字
② 字段名和表名都是辨別符
③ SQL語句不區分大小寫，并且都是以“ ；”結尾
示範：
mysql> desc dept;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int | NO | PRI | NULL | |
| DNAME | varchar(14) | YES | | NULL | |
| LOC | varchar(13) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> select dname from dept;
+------------+
| dname |
+------------+
| ACCOUNTING |
| RESEARCH |
| SALES |
| OPERATIONS |
+------------+
4 rows in set (0.00 sec) II.查詢多個字段
方法： 在I的基礎上通過“ ，”将多個字段名隔開即可
示範：
mysql> select dname,deptno from dept;
+------------+--------+
| dname | deptno |
+------------+--------+
| ACCOUNTING | 10 |
| RESEARCH | 20 |
| SALES | 30 |
| OPERATIONS | 40 |
+------------+--------+
4 rows in set (0.00 sec) III.查詢所有字段
格式：
(1) select 字段一，字段二，字段三 ... from tablename; (所有字段)
(2)select * from tablename;
說明：
方式（2）特點：
①效率低（需要先将 * 轉化為 字段 ，然後再執行 ）
②可讀性差
應用場景：需要快速檢視表中所有資料時可以使用
示範：
方式一：
mysql> select dname,deptno,loc from dept;
+------------+--------+----------+
| dname | deptno | loc |
+------------+--------+----------+
| ACCOUNTING | 10 | NEW YORK |
| RESEARCH | 20 | DALLAS |
| SALES | 30 | CHICAGO |
| OPERATIONS | 40 | BOSTON |
+------------+--------+----------+
4 rows in set (0.00 sec) 方式二：
mysql> select* from dept;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec) (二). 列起别名
方法：通過使用as 關鍵字來實作的
說明：
① 使用as 關鍵字起别名隻是将結果列名顯示為别名，原表列名是不變的
② select 語句永遠不會進行修改操作的（隻負責查詢）
示範：
方式一（不省略關鍵字as）：
mysql> select* from dept;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)
mysql> select deptno,dname as deptname from dept; #deptname為dname的别名
+--------+------------+
| deptno | deptname |
+--------+------------+
| 10 | ACCOUNTING |
| 20 | RESEARCH |
| 30 | SALES |
| 40 | OPERATIONS |
+--------+------------+
4 rows in set (0.00 sec) 方式二(省略關鍵字as)：
mysql> select deptno,dname deptname from dept;
+--------+------------+
| deptno | deptname |
+--------+------------+
| 10 | ACCOUNTING |
| 20 | RESEARCH |
| 30 | SALES |
| 40 | OPERATIONS |
+--------+------------+
4 rows in set (0.00 sec)
mysql> select deptno,dname deptname name from dept;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
for the right syntax to use near 'name from dept' at line 1 補充，如果别名中存在空格，可以将這整個别名使用 ‘’ 或 “ ” 包括起來
mysql> select deptno,dname 'deptname name' from dept;
+--------+---------------+
| deptno | deptname name |
+--------+---------------+
| 10 | ACCOUNTING |
| 20 | RESEARCH |
| 30 | SALES |
| 40 | OPERATIONS |
+--------+---------------+
4 rows in set (0.00 sec) 注意：
① 在所有的資料庫當中，字元串統一使用單引号（标準）括起來
②雙引号在oracle資料庫中不能使用，但是在mysql資料庫中是可以使用的
字段可以使用數學表達式：
mysql> select ename,sal*12 as yearsal from emp;
+--------+----------+
| ename | yearsal |
+--------+----------+
| SMITH | 9600.00 |
| ALLEN | 19200.00 |
| WARD | 15000.00 |
| JONES | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE | 34200.00 |
| CLARK | 29400.00 |
| SCOTT | 36000.00 |
| KING | 60000.00 |
| TURNER | 18000.00 |
| ADAMS | 13200.00 |
| JAMES | 11400.00 |
| FORD | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
14 rows in set (0.00 sec) 說明：如果 别名 為中文，需要使用 單引号 ‘ ’ 括起來
(三) 條件查詢
介紹：根據條件查詢出符合要求的。
文法格式：
select 字段1，字段2，字段3 ...from tablename where 條件；
常見條件如下表：
| 運算符 | 說明 |
| = | 等于 |
| <> 或！= | 不等于 |
| < | 小于 |
| <= | 小于等于 |
| > | 大于 |
| >= | 大于等于 |
| between… and …. | 兩個值之間，等同于 **>= and <=** |
| is null | 為 null（is not null 不為空） |
| and | 并且 |
| or | 或者 |
| in | 包含，相當于多個 o（r not in 不在這個範圍中） |
| not | not 可以取非，主要用在 is 或 in 中 |
| like | like 稱為模糊查詢，支援 % 或下劃線比對 % 比對任意個字元 下劃線，一個下劃線隻比對一個字元 |
應用執行個體：
=
問題：查詢薪資等于 1600的員工姓名和編号？
mysql> select empno,ename from emp where sal=1600 ;
+-------+-------+
| empno | ename |
+-------+-------+
| 7499 | ALLEN |
+-------+-------+
1 row in set (0.01 sec) ！=
問題：查詢薪資不等于 1600的員工姓名和編号？
mysql> select empno,ename from emp where sal!=1600 ;
+-------+--------+
| empno | ename |
+-------+--------+
| 7369 | SMITH |
| 7521 | WARD |
| 7566 | JONES |
| 7654 | MARTIN |
| 7698 | BLAKE |
| 7782 | CLARK |
| 7788 | SCOTT |
| 7839 | KING |
| 7844 | TURNER |
| 7876 | ADAMS |
| 7900 | JAMES |
| 7902 | FORD |
| 7934 | MILLER |
+-------+--------+
13 rows in set (0.00 sec) <
問題：查詢薪資小于1600的員工姓名和編号？
mysql> select empno,ename,sal from emp where sal<1600 ;
+-------+--------+---------+
| empno | ename | sal |
+-------+--------+---------+
| 7369 | SMITH | 800.00 |
| 7521 | WARD | 1250.00 |
| 7654 | MARTIN | 1250.00 |
| 7844 | TURNER | 1500.00 |
| 7876 | ADAMS | 1100.00 |
| 7900 | JAMES | 950.00 |
| 7934 | MILLER | 1300.00 |
+-------+--------+---------+
7 rows in set (0.00 sec) =
問題：查詢SMITH的編号和薪資？
mysql> select empno,ename,sal from emp where ename='SMITH' ;
+-------+-------+--------+
| empno | ename | sal |
+-------+-------+--------+
| 7369 | SMITH | 800.00 |
+-------+-------+--------+
1 row in set (0.00 sec) 說明：字元串使用單引号
betweent ... and ...
問題：查詢薪資在2450到 4000之間的員工姓名、編号、薪資？
mysql> select empno,ename,sal from emp where sal between 2450 and 4000 ;
+-------+-------+---------+
| empno | ename | sal |
+-------+-------+---------+
| 7566 | JONES | 2975.00 |
| 7698 | BLAKE | 2850.00 |
| 7782 | CLARK | 2450.00 |
| 7788 | SCOTT | 3000.00 |
| 7902 | FORD | 3000.00 |
+-------+-------+--------- 說明：必須遵循左小右大原則
is null
問題：查詢哪些員工的津貼/補助為null？
mysql> select empno,ename,sal,comm from emp where comm is null;
+-------+--------+---------+------+
| empno | ename | sal | comm |
+-------+--------+---------+------+
| 7369 | SMITH | 800.00 | NULL |
| 7566 | JONES | 2975.00 | NULL |
| 7698 | BLAKE | 2850.00 | NULL |
| 7782 | CLARK | 2450.00 | NULL |
| 7788 | SCOTT | 3000.00 | NULL |
| 7839 | KING | 5000.00 | NULL |
| 7876 | ADAMS | 1100.00 | NULL |
| 7900 | JAMES | 950.00 | NULL |
| 7902 | FORD | 3000.00 | NULL |
| 7934 | MILLER | 1300.00 | NULL |
+-------+--------+---------+------+
10 rows in set (0.00 sec) 注意：在資料庫當中null不能使用等号來進行衡量。需要使用is null，因為資料庫中的null代表什麼也沒有，它不是一個值，是以不能使用等号衡量。
如果查詢員工的津貼不是null，通過如下指令：
mysql>select empno ,ename,sal,comm from emp where comm is not null;
mysql> select empno ,ename,sal,comm from emp where comm is not null;
+-------+--------+---------+---------+
| empno | ename | sal | comm |
+-------+--------+---------+---------+
| 7499 | ALLEN | 1600.00 | 300.00 |
| 7521 | WARD | 1250.00 | 500.00 |
| 7654 | MARTIN | 1250.00 | 1400.00 |
| 7844 | TURNER | 1500.00 | 0.00 |
+-------+--------+---------+---------+
4 rows in set (0.00 sec) and
問題：查詢工作崗位是MANAGER并且工資大于2500的員工資訊？？
mysql> select empno,ename,job,sal from emp where job ='MANAGER' and sal > 2500;
+-------+-------+---------+---------+
| empno | ename | job | sal |
+-------+-------+---------+---------+
| 7566 | JONES | MANAGER | 2975.00 |
| 7698 | BLAKE | MANAGER | 2850.00 |
+-------+-------+---------+---------+
2 rows in set (0.00 sec) or
問題：查詢崗位是MANAGER和 SALESMAN的員工？？
mysql> select empno,ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
+-------+--------+----------+
| empno | ename | job |
+-------+--------+----------+
| 7499 | ALLEN | SALESMAN |
| 7521 | WARD | SALESMAN |
| 7566 | JONES | MANAGER |
| 7654 | MARTIN | SALESMAN |
| 7698 | BLAKE | MANAGER |
| 7782 | CLARK | MANAGER |
| 7844 | TURNER | SALESMAN |
+-------+--------+----------+
7 rows in set (0.00 sec) 混合應用：
查詢工資大于2500,并且部門編号為10或20部門的員工
#錯誤寫法：and的優先級高于or(原因)
mysql> select * from emp where sal >2500 and deptno =10 or deptno =20;
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
+-------+-------+-----------+------+------------+---------+------+--------+
6 rows in set (0.00 sec)
#正确寫法
mysql> select * from emp where sal >2500 and( deptno =10 or deptno =20);
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
+-------+-------+-----------+------+------------+---------+------+--------+
4 rows in set (0.00 sec) 說明：and 與 or同時出現，and 的優先級高于 or
in
問題：查詢工作崗位是 MANAGER 和 SALESMAN？
#方式一
mysql> select empno,ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
+-------+--------+----------+
| empno | ename | job |
+-------+--------+----------+
| 7499 | ALLEN | SALESMAN |
| 7521 | WARD | SALESMAN |
| 7566 | JONES | MANAGER |
| 7654 | MARTIN | SALESMAN |
| 7698 | BLAKE | MANAGER |
| 7782 | CLARK | MANAGER |
| 7844 | TURNER | SALESMAN |
+-------+--------+----------+
7 rows in set (0.00 sec)
#方式二
mysql> select empno,ename,job from emp where job in('MANAGER','SALESMAN');
+-------+--------+----------+
| empno | ename | job |
+-------+--------+----------+
| 7499 | ALLEN | SALESMAN |
| 7521 | WARD | SALESMAN |
| 7566 | JONES | MANAGER |
| 7654 | MARTIN | SALESMAN |
| 7698 | BLAKE | MANAGER |
| 7782 | CLARK | MANAGER |
| 7844 | TURNER | SALESMAN |
+-------+--------+----------+
7 rows in set (0.00 sec) 注意：in後面跟的内容是不是一個區間 ，而是一個具體的值
like
(模糊查詢，支援%或下劃線 比對)
說明： %
示範：
mysql> select ename from emp where ename like '%O%';//查詢名字中有 字母 ‘O’的
+-------+
| ename |
+-------+
| JONES |
| SCOTT |
| FORD |
+-------+
3 rows in set (0.00 sec)
//查詢以 字母 ‘T’ 結尾的名字
mysql> select ename from emp where ename like'%T';
+-------+
| ename |
+-------+
| SCOTT |
+-------+
1 row in set (0.00 sec)
//查詢以字母 ‘K’開頭的名字
mysql> select ename from emp where ename like'K%';
+-------+
| ename |
+-------+
| KING |
+-------+
1 row in set (0.00 sec)
//查詢第二個是字母‘A’的名字
mysql> select ename from emp where ename like'_A%';
+--------+
| ename |
+--------+
| WARD |
| MARTIN |
| JAMES |
+--------+
3 rows in set (0.00 sec)
//查詢第三個字母是‘R’的名字
mysql> select ename from emp where ename like'__R%';
+--------+
| ename |
+--------+
| WARD |
| MARTIN |
| TURNER |
| FORD |
+--------+
4 rows in set (0.00 sec) 拓展：如果要查詢帶有下劃線‘_’的名字,可以使用反斜線 ‘ \ ’進行轉義
(四)排序
格式：select 字段1，字段2，字段3... from tablename order by 字段；
解釋：根據 字段 将字段1，字段2，字段3...進行升序
I.單個字段
升序：
mysql> select ename,sal from emp order by sal;//預設升序
+--------+---------+
| ename | sal |
+--------+---------+
| SMITH | 800.00 |
| JAMES | 950.00 |
| ADAMS | 1100.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN | 1600.00 |
| CLARK | 2450.00 |
| BLAKE | 2850.00 |
| JONES | 2975.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| KING | 5000.00 |
+--------+---------+
14 rows in set (0.00 sec)
mysql> select ename,sal from emp order by sal asc;//指定升序
+--------+---------+
| ename | sal |
+--------+---------+
| SMITH | 800.00 |
| JAMES | 950.00 |
| ADAMS | 1100.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN | 1600.00 |
| CLARK | 2450.00 |
| BLAKE | 2850.00 |
| JONES | 2975.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| KING | 5000.00 |
+--------+---------+
14 rows in set (0.00 sec) 降序：
mysql> select ename,sal from emp order by sal desc;
+--------+---------+
| ename | sal |
+--------+---------+
| KING | 5000.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| SMITH | 800.00 |
+--------+---------+
14 rows in set (0.00 sec) II.多個字段
格式：select 字段1，字段2，字段3... from tablename order by 字段1，字段2...；
解釋：先通過字段1進行排序，然後如果字段1中存在相等的，就根據字段2的方式進行排序.....
示範：
mysql> select ename,sal from emp order by sal asc,ename asc;
+--------+---------+
| ename | sal |
+--------+---------+
| SMITH | 800.00 |
| JAMES | 950.00 |
| ADAMS | 1100.00 |
| MARTIN | 1250.00 |
| WARD | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN | 1600.00 |
| CLARK | 2450.00 |
| BLAKE | 2850.00 |
| JONES | 2975.00 |
| FORD | 3000.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
+--------+---------+
14 rows in set (0.00 sec) 綜合案例
找出工資在1250到3000之間的員工資訊，要求按照薪資降序排列
mysql> select
-> ename,sal
-> from
-> emp
-> where
-> sal between 1250 and 3000
-> order by
-> sal desc;
+--------+---------+
| ename | sal |
+--------+---------+
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
+--------+---------+
10 rows in set (0.00 sec) 注意：執行順序
from --> where -->select --> order by (排序總是在最後才執行)
(五)函數
介紹：
①單行處理函數：指的是一個輸入對應一個輸出的函數
②多行處理函數：指的是多個輸入對應一個輸出的函數
單行處理函數
| lower | 轉換小寫 |
| upper | 轉換大寫 |
| substr | 取子串（substr (被截取的字元串，起始下标，截取的長度)） |
| length | 取長度 |
| trim | 去除字元串中的空格 |
| str_to_date | 将字元串轉換成日期 |
| date_format | 格式化日期 |
| format | 設定千分位 |
| round | 四舍五入 |
| rand() | 生成随機數 |
| Ifnull | 可以将 null 轉換成一個具體值 |
使用示範：
lower
mysql> select ename from emp;
+--------+
| ename |
+--------+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| SCOTT |
| KING |
| TURNER |
| ADAMS |
| JAMES |
| FORD |
| MILLER |
+--------+
14 rows in set (0.00 sec)
//全部轉化為小寫
mysql> select lower(ename) from emp;//通過 lower(字段名)方式，将字段名全部以小寫的形式輸出
+--------------+
| lower(ename) |
+--------------+
| smith |
| allen |
| ward |
| jones |
| martin |
| blake |
| clark |
| scott |
| king |
| turner |
| adams |
| james |
| ford |
| miller |
+--------------+
14 rows in set (0.01 sec) upper
mysql> select (ename) from emp; substr
找出員工名字第一個字母是A的員工資訊
mysql> select ename from emp where ename like 'A%';//方式一
+-------+
| ename |
+-------+
| ALLEN |
| ADAMS |
+-------+
2 rows in set (0.00 sec)
mysql> select ename from emp where substr(ename,1,1)='A';//方式二
+-------+
| ename |
+-------+
| ALLEN |
| ADAMS |
+-------+
2 rows in set (0.00 sec) length
mysql> select length(ename) enamelength from emp;
+-------------+
| enamelength |
+-------------+
| 5 |
| 5 |
| 4 |
| 5 |
| 6 |
| 5 |
| 5 |
| 5 |
| 4 |
| 6 |
| 5 |
| 5 |
| 4 |
| 6 |
+-------------+
14 rows in set (0.00 sec) trim
mysql> select * from emp where ename =trim(' KING');
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
+-------+-------+-----------+------+------------+---------+------+--------+
1 row in set (0.00 sec) str_to_date
dete_format
format
round
功能：四舍五入
格式：select 字段名 from 表名；
說明：如果select後面直接跟‘‘字面量/字面值’’，會根據表的結構形成重複的字面量資料
mysql> select 'abx' from emp;
+-----+
| abx |
+-----+
| abx |
| abx |
| abx |
| abx |
| abx |
| abx |
| abx |
| abx |
| abx |
| abx |
| abx |
| abx |
| abx |
| abx |
+-----+
14 rows in set (0.00 sec) 示範：
mysql> select round('1220.234',0) as result from emp;//參數0表示保留的小數位
+--------+
| result |
+--------+
| 1220 |
| 1220 |
| 1220 |
| 1220 |
| 1220 |
| 1220 |
| 1220 |
| 1220 |
| 1220 |
| 1220 |
| 1220 |
| 1220 |
| 1220 |
| 1220 |
+--------+
14 rows in set (0.00 sec)
mysql> select round('1220.234',-2) as result from emp;//保留到十位
+--------+
| result |
+--------+
| 1200 |
| 1200 |
| 1200 |
| 1200 |
| 1200 |
| 1200 |
| 1200 |
| 1200 |
| 1200 |
| 1200 |
| 1200 |
| 1200 |
| 1200 |
| 1200 |
+--------+
14 rows in set (0.00 sec) 如果參數是 0：保留整數位 1：保留一位小數 2：保留兩位小數 3：保留三位小數 ....
-1: 保留到十位 -2: 保留到百位
rand()
mysql> select rand() from emp;
+---------------------+
| rand() |
+---------------------+
| 0.16059188839103272 |
| 0.1701412919630681 |
| 0.3689308253758874 |
| 0.3342302817238777 |
| 0.5643589632253712 |
| 0.8191040016888678 |
| 0.40244314950187055 |
| 0.5549037731763942 |
| 0.5671894481100044 |
| 0.17123595175448428 |
| 0.15461144517605327 |
| 0.25934940135045853 |
| 0.8329127019577778 |
| 0.3865153364711584 |
+---------------------+
14 rows in set (0.01 sec)
mysql> select round(rand()*100,0) from emp;//生成100以内的随機數
+---------------------+
| round(rand()*100,0) |
+---------------------+
| 43 |
| 1 |
| 75 |
| 70 |
| 28 |
| 30 |
| 66 |
| 39 |
| 97 |
| 68 |
| 51 |
| 48 |
| 90 |
| 4 |
+---------------------+
14 rows in set (0.00 sec) ifnull
介紹：空處理函數(将null轉化為一個具體值)。
用法：isfull(資料，具體值)
解釋： 如果資料為null,就使用該 具體值 來進行 參與運算
說明：在所有的資料庫中，有null參與的數學運算，最終結果就是null。（使用該函數就可以避免）
mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
//将sal與comm段相加，别名為 salcomm
mysql> select ename ,sal+ comm as salcomm from emp;
+--------+---------+
| ename | salcomm |
+--------+---------+
| SMITH | NULL |
| ALLEN | 1900.00 |
| WARD | 1750.00 |
| JONES | NULL |
| MARTIN | 2650.00 |
| BLAKE | NULL |
| CLARK | NULL |
| SCOTT | NULL |
| KING | NULL |
| TURNER | 1500.00 |
| ADAMS | NULL |
| JAMES | NULL |
| FORD | NULL |
| MILLER | NULL |
+--------+---------+
14 rows in set (0.00 sec) 使用示範：
mysql> select ename ,(sal + ifnull(comm,0))*12 as yearsal from emp;//計算年薪
+--------+----------+
| ename | yearsal |
+--------+----------+
| SMITH | 9600.00 |
| ALLEN | 22800.00 |
| WARD | 21000.00 |
| JONES | 35700.00 |
| MARTIN | 31800.00 |
| BLAKE | 34200.00 |
| CLARK | 29400.00 |
| SCOTT | 36000.00 |
| KING | 60000.00 |
| TURNER | 18000.00 |
| ADAMS | 13200.00 |
| JAMES | 11400.00 |
| FORD | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
14 rows in set (0.00 sec) concat
功能：字元串的拼接
格式：select contace(字段1，字段2，字段3....) from tablename;
示範：
mysql> select from emp;
ERROR 1064 (42000): You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax to use near 'from emp' at line 1
mysql> select concat(empno,ename) from emp;
+---------------------+
| concat(empno,ename) |
+---------------------+
| 7369SMITH |
| 7499ALLEN |
| 7521WARD |
| 7566JONES |
| 7654MARTIN |
| 7698BLAKE |
| 7782CLARK |
| 7788SCOTT |
| 7839KING |
| 7844TURNER |
| 7876ADAMS |
| 7900JAMES |
| 7902FORD |
| 7934MILLER |
+---------------------+
14 rows in set (0.00 sec) 結論：select後面可以跟某個表的字段名（可以看做是變量名），也可以跟字面量/字面值（資料）
case ...when ,,,then...when...then...else...end
當員工的工作崗位是MANAGER時，工資上調10%。當工作崗位是SALESMAN的時候，工資上調50%,其他不變
說明：不修改資料庫，隻是将查詢結果顯示為工資上調
示範：
mysql> select ename,job,sal from emp;
+--------+-----------+---------+
| ename | job | sal |
+--------+-----------+---------+
| SMITH | CLERK | 800.00 |
| ALLEN | SALESMAN | 1600.00 |
| WARD | SALESMAN | 1250.00 |
| JONES | MANAGER | 2975.00 |
| MARTIN | SALESMAN | 1250.00 |
| BLAKE | MANAGER | 2850.00 |
| CLARK | MANAGER | 2450.00 |
| SCOTT | ANALYST | 3000.00 |
| KING | PRESIDENT | 5000.00 |
| TURNER | SALESMAN | 1500.00 |
| ADAMS | CLERK | 1100.00 |
| JAMES | CLERK | 950.00 |
| FORD | ANALYST | 3000.00 |
| MILLER | CLERK | 1300.00 |
+--------+-----------+---------+
14 rows in set (0.00 sec)
mysql> select ename,job,sal as oldsal,(case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal from emp;
+--------+-----------+---------+---------+
| ename | job | oldsal | newsal |
+--------+-----------+---------+---------+
| SMITH | CLERK | 800.00 | 800.00 |
| ALLEN | SALESMAN | 1600.00 | 2400.00 |
| WARD | SALESMAN | 1250.00 | 1875.00 |
| JONES | MANAGER | 2975.00 | 3272.50 |
| MARTIN | SALESMAN | 1250.00 | 1875.00 |
| BLAKE | MANAGER | 2850.00 | 3135.00 |
| CLARK | MANAGER | 2450.00 | 2695.00 |
| SCOTT | ANALYST | 3000.00 | 3000.00 |
| KING | PRESIDENT | 5000.00 | 5000.00 |
| TURNER | SALESMAN | 1500.00 | 2250.00 |
| ADAMS | CLERK | 1100.00 | 1100.00 |
| JAMES | CLERK | 950.00 | 950.00 |
| FORD | ANALYST | 3000.00 | 3000.00 |
| MILLER | CLERK | 1300.00 | 1300.00 |
+--------+-----------+---------+---------+
14 rows in set (0.00 sec) 多行處理函數（分組函數）
特點：輸入多行，輸出一行
說明：分組函數在使用時必須先進行分組，然後才能使用
若未對資料進行分組，預設整個表為一個分組
常見函數
| count | 取得記錄數(記數) |
| sum | 求和 |
| avg | 取平均（平均值） |
| max | 取最大的數（最大值） |
| min | 取最小的數（最小值） |
cout
mysql> select count(ename) from emp;
+--------------+
| count(ename) |
+--------------+
| 14 |
+--------------+
1 row in set (0.00 sec) sum
mysql> select sum(sal) as totalsum from emp;
+----------+
| totalsum |
+----------+
| 29025.00 |
+----------+
1 row in set (0.00 sec) avg
mysql> select avg(sal) as totalsum from emp;
+-------------+
| totalsum |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.01 sec) max
mysql> select max(sal) as totalsum from emp;
+----------+
| totalsum |
+----------+
| 5000.00 |
+----------+
1 row in set (0.00 sec) min
mysql> select min(sal) as totalsum from emp;
+----------+
| totalsum |
+----------+
| 800.00 |
+----------+
1 row in set (0.01 sec) 注意事項：
（1）分組函數自動忽略null，不需要提前對null進行處理
（2）分組函數中count(*)與count(具體字段)的差別？
mysql> select count(*) from emp;
+----------+
| count(*) |
+----------+
| 14 |
+----------+
1 row in set (0.00 sec)
mysql> select count(comm) from emp;
+-------------+
| count(comm) |
+-------------+
| 4 |
+-------------+
1 row in set (0.00 sec) 說明：
count(具體字段)： 表示統計該字段下所有不為null的元素的總數
count(*) :統計表當中的總行數。（隻要有一行資料count就加1）
每一行記錄不可能都為null，一行資料中有一列不為null，則這行資料就是有效的。
（3）分組函數不能夠直接使用在where子句中
找出比最低工資高的員工資訊
mysql> select ename ,sal from emp where sal > min(sal);
ERROR 1111 (HY000): Invalid use of group function 錯誤原因分析：
在where執行的時候，還沒有進行分組,是以where的後面不能出現分組函數
在select後面出現分組函數是可以的，因為在執行select之前已經分好組
(六) 分組查詢（重點）
基本介紹
在實際應用中·，将需要先分組然後再查詢的一種查詢方式稱為分組查詢
格式
select ... from ... group by ...
執行個體
找出每個崗位的工資和 ？？
思路：先根據工作崗位進行分組，然後在求每一組的工作和
mysql> select job,sum(sal) from emp group by job;
+-----------+----------+
| job | sum(sal) |
+-----------+----------+
| CLERK | 4150.00 |
| SALESMAN | 5600.00 |
| MANAGER | 8275.00 |
| ANALYST | 6000.00 |
| PRESIDENT | 5000.00 |
+-----------+----------+
5 rows in set (0.01 sec)
//執行順序： 先從emp表中查詢資料-->根據job字段進行分組 --> 然後對每一個分組資料進行求和（sum）
mysql> select ename,job,sum(sal) from emp group by job;
+-------+-----------+----------+
| ename | job | sum(sal) |
+-------+-----------+----------+
| SMITH | CLERK | 4150.00 |
| ALLEN | SALESMAN | 5600.00 |
| JONES | MANAGER | 8275.00 |
| SCOTT | ANALYST | 6000.00 |
| KING | PRESIDENT | 5000.00 |
+-------+-----------+----------+
5 rows in set (0.00 sec)
//以上寫法在mysql可以執行，但是在oracle中執行報錯（文法更加嚴格）---->無意義 找出每個部門的最高薪資？
思路：按照部門編号分組，然後求每一個分組的最大值
mysql> select deptno,max(sal) from emp group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 20 | 3000.00 |
| 30 | 2850.00 |
| 10 | 5000.00 |
+--------+----------+
3 rows in set (0.00 sec) 找出每個部門 ， 不同工作崗位的最高薪資？？
mysql> select ename,job,sal,deptno from emp order by deptno;
+--------+-----------+---------+--------+
| ename | job | sal | deptno |
+--------+-----------+---------+--------+
| CLARK | MANAGER | 2450.00 | 10 |
| KING | PRESIDENT | 5000.00 | 10 |
| MILLER | CLERK | 1300.00 | 10 |
| SMITH | CLERK | 800.00 | 20 |
| JONES | MANAGER | 2975.00 | 20 |
| SCOTT | ANALYST | 3000.00 | 20 |
| ADAMS | CLERK | 1100.00 | 20 |
| FORD | ANALYST | 3000.00 | 20 |
| ALLEN | SALESMAN | 1600.00 | 30 |
| WARD | SALESMAN | 1250.00 | 30 |
| MARTIN | SALESMAN | 1250.00 | 30 |
| BLAKE | MANAGER | 2850.00 | 30 |
| TURNER | SALESMAN | 1500.00 | 30 |
| JAMES | CLERK | 950.00 | 30 |
+--------+-----------+---------+--------+
14 rows in set (0.00 sec)
//技巧：兩個字段聯合成一個字段（兩個字段聯合分組）
mysql> select deptno,job,max(sal) from emp group by deptno,job;
+--------+-----------+----------+
| deptno | job | max(sal) |
+--------+-----------+----------+
| 20 | CLERK | 1100.00 |
| 30 | SALESMAN | 1600.00 |
| 20 | MANAGER | 2975.00 |
| 30 | MANAGER | 2850.00 |
| 10 | MANAGER | 2450.00 |
| 20 | ANALYST | 3000.00 |
| 10 | PRESIDENT | 5000.00 |
| 30 | CLERK | 950.00 |
| 10 | CLERK | 1300.00 |
+--------+-----------+----------+
9 rows in set (0.00 sec) 找出每個部門的最高薪資，要求顯示最高薪資大于3000？
思路：（1）找出每個部門的最高薪資（按照部門編号分組，求每一組的最大值）
（2）要求顯示最高薪資大于3000
//方式一
mysql> select deptno,max(sal) from emp group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 20 | 3000.00 |
| 30 | 2850.00 |
| 10 | 5000.00 |
+--------+----------+
3 rows in set (0.00 sec)
mysql> select deptno,max(sal) from emp group by deptno having max(sal)>3000;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 10 | 5000.00 |
+--------+----------+
1 row in set (0.01 sec)
//方式二
mysql> select deptno,max(sal) from emp where sal>3000 group by deptno;//where進行過濾
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 10 | 5000.00 |
+--------+----------+
1 row in set (0.00 sec) 說明：可以使用having來對分組後的資料進一步過濾，having不能夠單獨使用，having不能代替where，having必須和group by 聯合使用。（優先使用where）
找出每個部門平均薪資，要求顯示平均薪資高于2500的
思路：（1）找出每個部門平均薪資
（2）要求顯示平均薪資高于2500的
mysql> select deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 2916.666667 |
+--------+-------------+
3 rows in set (0.00 sec)
mysql> select deptno ,avg(sal) from emp group by deptno having avg(sal)>2500;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 10 | 2916.666667 |
+--------+-------------+
1 row in set (0.01 sec) conclusion:
在執行一條select語句中，如果含有group語句，select後面隻能跟：參加分組的字段或分組函數。
關鍵字組合：
select ... from ...where... group by ... order by ....
說明：關鍵字的使用先後順序不能颠倒
執行順序：
① from ② where ③ group by ④ select ⑤ order by
總結：
select ... where ... group by ... having ...order by ...
執行順序：
① from ② where ③ group by ④ having ⑤ select ⑥ order by
說明（執行過程）：
過程一：通過where條件篩選出有價值的資料
過程二：對有價值的資料進行分組
過程三：分組後通過having來及進行進一步的篩選
過程四： select 查詢
過程五：直接排序輸出
找出每個崗位的平均薪資，要求顯示平均薪資大于1500，除MANAGER崗位之外（薪資按照降序排列）
mysql> select job,avg(sal) as avgsal from emp where job <> 'MANAGER' group by job having avg(sal) >1500 order by avgsal desc;
+-----------+-------------+
| job | avgsal |
+-----------+-------------+
| PRESIDENT | 5000.000000 |
| ANALYST | 3000.000000 |
+-----------+-------------+
2 rows in set (0.00 sec) 關鍵字
distinct
作用：去除字段中的重複資料
使用如下：
#對于單個字段直接在查詢字段前添加distinct即可
mysql> select distinct job from emp;
+-----------+
| job |
+-----------+
| CLERK |
| SALESMAN |
| MANAGER |
| ANALYST |
| PRESIDENT |
+-----------+
5 rows in set (0.00 sec)
#對于多個字段之間的去重，distinct隻能夠使用在“所有查詢字段的”最前面
mysql> select deptno,distinct job from emp;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct job from emp' at line 1
mysql> select distinct deptno, job from emp;
+--------+-----------+
| deptno | job |
+--------+-----------+
| 20 | CLERK |
| 30 | SALESMAN |
| 20 | MANAGER |
| 30 | MANAGER |
| 10 | MANAGER |
| 20 | ANALYST |
| 10 | PRESIDENT |
| 30 | CLERK |
| 10 | CLERK |
+--------+-----------+
9 rows in set (0.00 sec) mysql> select count(distinct job) from emp;
+---------------------+
| count(distinct job) |
+---------------------+
| 5 |
+---------------------+
1 row in set (0.00 sec) ![SQLDay5--單表查詢1.資料過濾--WHERE:不能單獨使用；對“行”進行過濾；2.IFNULL(salepric[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)