MySQL查詢命令-DQL

EMP //員工表

員工編號 員工名 崗位 領(lǐng)導(dǎo) 入職日期 工資 獎金 部門編號

+-------+--------+-----------+------+------------+---------+---------+--------+

| 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)

DEPT //員工部門表

部門編號 部門名 地址

+--------+------------+----------+

| DEPTNO | DNAME | LOC |

+--------+------------+----------+

| 10 | ACCOUNTING | NEW YORK |

| 20 | RESEARCH | DALLAS |

| 30 | SALES | CHICAGO |

| 40 | OPERATIONS | BOSTON |

+--------+------------+----------+

4 rows in set (0.00 sec)

SALGRADE //工資等級表

等級最少 最高

+-------+-------+-------+

| GRADE | LOSAL | HISAL |

+-------+-------+-------+

| 1 | 700 | 1200 |

| 2 | 1201 | 1400 |

| 3 | 1401 | 2000 |

| 4 | 2001 | 3000 |

| 5 | 3001 | 9999 |

+-------+-------+-------+

5 rows in set (0.00 sec)

常用命令

登錄mysql -uroot -p密碼

查看全部數(shù)據(jù)庫show databases;

創(chuàng)建數(shù)據(jù)庫create database 數(shù)據(jù)庫名

進(jìn)入(使用)數(shù)據(jù)庫use 數(shù)據(jù)庫名

查看當(dāng)前使用的數(shù)據(jù)庫中的所有表show tables;

初始化數(shù)據(jù)source 數(shù)據(jù)庫文件地址(例如： source D: \XiaoK. sq1)

刪除數(shù)據(jù)庫drop database 數(shù)據(jù)庫名

查看表結(jié)構(gòu)desc 表名

查看當(dāng)前使用的是哪個數(shù)據(jù)庫select database();

查看數(shù)據(jù)庫版本select version();

終止語句\c

查詢表的全部內(nèi)容select * from 表名

查看其中庫中的表show tables from exam;

查看創(chuàng)建表的語句show create table 表名；

如果表存在則刪除此表drop table if exists 表名；

一個完整的DQL語句

select5

...

from1

...

where2

...

group by3

...

having4

...

order by6

...

limit7

...

查詢

簡單的查詢語句

select 字段名1,字段名2,...from 表名;

案例1：查詢所有字段

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.01 sec)

案例2：查詢員工的年薪

mysql> select ename,sal*12 from emp;

+--------+----------+

| ename | sal*12 |

+--------+----------+

| 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)

案例3：給查詢結(jié)果的列重命名

其中如果需要更改所查詢的字段名則使用一下語句

select ename,sal*12 as yearsal from emp;

其中as也可省略，如果更改的字段名為中文則使用單引號括起來

條件查詢

select 字段1,字段2,... from 表名 where 條件;

運算符

說明

=

等于

<>或!=

不等于

<

小于

<=

小于等于

>

大于

>=

大于等于

between...and...

兩個值之間，等同于>= and <=

is null

為null(is not null不為空)

and

并且

or

或者

in

包含，相當(dāng)于多個or(not in 不在這個范圍中)

not

not可以取非，主要用在is或in中

like

like稱為模糊查詢，支持%或者下劃線匹配

案例1：找出哪些人津貼為NULL

在數(shù)據(jù)庫中null不是一個值，不能用等號衡量，使用語法是 is null 或 is not null

mysql> select ename,comm from emp where comm is null;

+--------+------+

| ename | comm |

+--------+------+

| SMITH | NULL |

| JONES | NULL |

| BLAKE | NULL |

| CLARK | NULL |

| SCOTT | NULL |

| KING | NULL |

| ADAMS | NULL |

| JAMES | NULL |

| FORD | NULL |

| MILLER | NULL |

+--------+------+

10 rows in set (0.00 sec)

案例2：找出哪些人津貼不為NULL

mysql> select ename,comm from emp where comm is not null;

+--------+---------+

| ename | comm |

+--------+---------+

| ALLEN | 300.00 |

| WARD | 500.00 |

| MARTIN | 1400.00 |

| TURNER | 0.00 |

+--------+---------+

4 rows in set (0.00 sec)

案例3：找出哪些人沒有津貼

mysql> select ename,comm from emp where comm is null or comm = 0;

+--------+------+

| ename | comm |

+--------+------+

| SMITH | NULL |

| JONES | NULL |

| BLAKE | NULL |

| CLARK | NULL |

| SCOTT | NULL |

| KING | NULL |

| TURNER | 0.00 |

| ADAMS | NULL |

| JAMES | NULL |

| FORD | NULL |

| MILLER | NULL |

+--------+------+

11 rows in set (0.00 sec)

案例4：找出工作崗位是manager和salesman的員工

mysql> select ename,job from emp where job = 'manager' or job = 'salesman';

+--------+----------+

| ename | job |

+--------+----------+

| ALLEN | SALESMAN |

| WARD | SALESMAN |

| JONES | MANAGER |

| MARTIN | SALESMAN |

| BLAKE | MANAGER |

| CLARK | MANAGER |

| TURNER | SALESMAN |

+--------+----------+

7 rows in set (0.00 sec)

案例5：找出薪資大于1000的并且部門編號是20或30部門的員工

mysql> select ename,sal,deptno from emp where sal > 1000 and (deptno = 20 or deptno = 30);

+--------+---------+--------+

| ename | sal | deptno |

+--------+---------+--------+

| ALLEN | 1600.00 | 30 |

| WARD | 1250.00 | 30 |

| JONES | 2975.00 | 20 |

| MARTIN | 1250.00 | 30 |

| BLAKE | 2850.00 | 30 |

| SCOTT | 3000.00 | 20 |

| TURNER | 1500.00 | 30 |

| ADAMS | 1100.00 | 20 |

| FORD | 3000.00 | 20 |

+--------+---------+--------+

9 rows in set (0.00 sec)

案例6：找出工作崗位是manager和salesman的員工，使用in

在這里in等同于or

mysql> select ename,job from emp where job in ('manager','salesman');

+--------+----------+

| ename | job |

+--------+----------+

| ALLEN | SALESMAN |

| WARD | SALESMAN |

| JONES | MANAGER |

| MARTIN | SALESMAN |

| BLAKE | MANAGER |

| CLARK | MANAGER |

| TURNER | SALESMAN |

+--------+----------+

7 rows in set (0.00 sec)

案例7：找出工資不是800和5000的所有人

這里使用的是 not in

mysql> select ename,sal from emp where sal not in (800,5000);

+--------+---------+

| ename | sal |

+--------+---------+

| ALLEN | 1600.00 |

| WARD | 1250.00 |

| JONES | 2975.00 |

| MARTIN | 1250.00 |

| BLAKE | 2850.00 |

| CLARK | 2450.00 |

| SCOTT | 3000.00 |

| TURNER | 1500.00 |

| ADAMS | 1100.00 |

| JAMES | 950.00 |

| FORD | 3000.00 |

| MILLER | 1300.00 |

+--------+---------+

12 rows in set (0.00 sec)

同樣可以用

mysql> select ename,sal from emp where sal<>800 and sal<>5000;

模糊查詢

在模糊查詢中，有兩個特殊的符號，一個是%，一個是_

%代表任意多個字符，_代表任意1個字符

案例1：找出名字中含有O的

mysql> select ename from emp where ename like '%o%';

+-------+

| ename |

+-------+

| JONES |

| SCOTT |

| FORD |

+-------+

3 rows in set (0.00 sec)

案例2：找出名字中第二個字母是o的

mysql> select ename from emp where ename like '_o%';

+-------+

| ename |

+-------+

| JONES |

| FORD |

+-------+

2 rows in set (0.00 sec)

同理：找出名字中第三個字母是o的可得

select ename from emp where ename like '_ _o%';

案例3：找出名字中有下劃線的

select ename from emp where ename like '%\_%';

使用轉(zhuǎn)義字符

排序

案例1：按照工資升序，找出員工名和薪資

這里使用到了 order by 默認(rèn)是升序排列

如何指定是升序或者是降序呢？asc表示升序，desc表示降序

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 |

| FORD | 3000.00 |

| SCOTT | 3000.00 |

| KING | 5000.00 |

+--------+---------+

14 rows in set (0.00 sec)

如果需要讓他降序排列則

select ename,sal from emp order by sal desc;

案例2：按照工資的降序排列，當(dāng)工資相同的時候再按照名字的升序排列。

mysql> select ename,sal from emp order by sal desc,ename asc;

+--------+---------+

| ename | sal |

+--------+---------+

| KING | 5000.00 |

| FORD | 3000.00 |

| SCOTT | 3000.00 |

| JONES | 2975.00 |

| BLAKE | 2850.00 |

| CLARK | 2450.00 |

| ALLEN | 1600.00 |

| TURNER | 1500.00 |

| MILLER | 1300.00 |

| MARTIN | 1250.00 |

| WARD | 1250.00 |

| ADAMS | 1100.00 |

| JAMES | 950.00 |

| SMITH | 800.00 |

+--------+---------+

14 rows in set (0.00 sec)

注意：越靠前的字段越能起到主導(dǎo)作用，只有當(dāng)前面的字段無法起到排序的時候，才會啟用后面的字段

排序order by也可以用戶按列

案例3：找出工作崗位是salesman的員工，并且要求按照薪資的降序排列

mysql> select ename,sal,job from emp where job='salesman' order by sal desc;

+--------+---------+----------+

| ename | sal | job |

+--------+---------+----------+

| ALLEN | 1600.00 | SALESMAN |

| TURNER | 1500.00 | SALESMAN |

| WARD | 1250.00 | SALESMAN |

| MARTIN | 1250.00 | SALESMAN |

+--------+---------+----------+

4 rows in set (0.00 sec)

執(zhí)行順序

select

*3

from

表名 1

where

條件 2

order by

..... 4

order by 是最后執(zhí)行的

分組函數(shù)

分組函數(shù)一共有5個

分組函數(shù)還有另一個名字：多行處理函數(shù)

多行處理函數(shù)的特點：輸入多行，最終輸出的結(jié)果是1行

分組函數(shù)自動忽略null，不需要ifnull函數(shù)

count

計數(shù)

sum

求和

avg

取平均值

min

取最小值

max

取最大值

案例1：找出員工工資總和

mysql> select sum(sal) from emp;

+----------+

| sum(sal) |

+----------+

| 29025.00 |

+----------+

1 row in set (0.00 sec)

案例2：找出最高工資

?select max(sal) from emp;

案例3：找出最低工資

?select min(sal) from emp;

案例4：找出平均工資

?select avg(sal) from emp;

案例5：找出總?cè)藬?shù)

?select count(*) from emp;

?select count(ename) from emp;

分組函數(shù)自動忽略NULL

mysql> select count(comm) from emp;

+-------------+

| count(comm) |

+-------------+

| 4 |

+-------------+

1 row in set (0.00 sec)

單行處理函數(shù)

什么是單行處理函數(shù)？

?輸入一行，輸出一行

案例：計算每個員工的年薪

?select ename , (sal+comm) * 12 from emp;

結(jié)果出現(xiàn)：

mysql> select ename,(sal+comm)*12 from emp;

+--------+---------------+

| ename | (sal+comm)*12 |

+--------+---------------+

| SMITH | NULL |

| ALLEN | 22800.00 |

| WARD | 21000.00 |

| JONES | NULL |

| MARTIN | 31800.00 |

| BLAKE | NULL |

| CLARK | NULL |

| SCOTT | NULL |

| KING | NULL |

| TURNER | 18000.00 |

| ADAMS | NULL |

| JAMES | NULL |

| FORD | NULL |

| MILLER | NULL |

+--------+---------------+

14 rows in set (0.00 sec)

重點：所有數(shù)據(jù)庫規(guī)定，只要有NULL參與運算的結(jié)果一定是NULL 例：1000+null = null

ifnull() 空處理函數(shù)

?ifnull(可能為NULL的數(shù)據(jù),被當(dāng)做什么處理)，屬于單行處理函數(shù)

所以可解決以上問題

mysql> select ename,(sal+ifnull(comm,0))*12 from emp;

+--------+-------------------------+

| ename | (sal+ifnull(comm,0))*12 |

+--------+-------------------------+

| 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)

案例：找出工資高于平均工資的員工？

?會出現(xiàn)以下錯誤

mysql> select ename,sal from emp where sal > avg(sal);

ERROR 1111 (HY000): Invalid use of group function

mysql> select ename,sal from emp where sal > (select avg(sal) from emp);

+-------+---------+

| ename | sal |

+-------+---------+

| JONES | 2975.00 |

| BLAKE | 2850.00 |

| CLARK | 2450.00 |

| SCOTT | 3000.00 |

| KING | 5000.00 |

| FORD | 3000.00 |

+-------+---------+

6 rows in set (0.00 sec)

注意：分組函數(shù)不能直接使用在where語句中

count(*)和count(具體的某個字段)，他們有什么區(qū)別？

?count(*):不是統(tǒng)計某個字段中數(shù)據(jù)的個數(shù)，而是統(tǒng)計總記錄條數(shù)

?count(sal):表示統(tǒng)計sal字段中不為NULL的數(shù)據(jù)總數(shù)量。

group by 和 having

注意：分組函數(shù)一般都會和group by 聯(lián)合使用，并且任何一個分組函數(shù)(count sum avg max min)都是在group by語句執(zhí)行結(jié)束之后才會執(zhí)行，當(dāng)一條sql語句沒有g(shù)roup by，整張表的數(shù)據(jù)就會自成一組。

group by ： 按照某個字段或者某些字段進(jìn)行分組。

having ： having是對分組以后的數(shù)據(jù)進(jìn)行再次過濾

案例：找出每個工作崗位的最高薪資

mysql> select ename,job,max(sal) from emp group by job;

+-------+-----------+----------+

| ename | job | max(sal) |

+-------+-----------+----------+

| SCOTT | ANALYST | 3000.00 |

| SMITH | CLERK | 1300.00 |

| JONES | MANAGER | 2975.00 |

| KING | PRESIDENT | 5000.00 |

| ALLEN | SALESMAN | 1600.00 |

+-------+-----------+----------+

5 rows in set (0.00 sec)

以上在mysql 當(dāng)中，查詢結(jié)果是有的，但是結(jié)果沒有意義，在Oracle數(shù)據(jù)庫當(dāng)中會報錯，語法錯誤，Oracle的語法規(guī)則比MySQL語法規(guī)則嚴(yán)謹(jǐn)。

記住一個規(guī)則：當(dāng)一條語句中有g(shù)roup by的話，select后面只能跟分組函數(shù)和參與分組的字段。

案例：每個工作崗位的平均薪資？

select job,avg(sal) from emp group by job;

多個字段能不能聯(lián)合起來一塊分組？

案例：找出每個部門不同工作崗位的最高薪資？

mysql> select job,deptno,max(sal) from emp group by deptno,job;

+-----------+--------+----------+

| job | deptno | max(sal) |

+-----------+--------+----------+

| CLERK | 10 | 1300.00 |

| MANAGER | 10 | 2450.00 |

| PRESIDENT | 10 | 5000.00 |

| ANALYST | 20 | 3000.00 |

| CLERK | 20 | 1100.00 |

| MANAGER | 20 | 2975.00 |

| CLERK | 30 | 950.00 |

| MANAGER | 30 | 2850.00 |

| SALESMAN | 30 | 1600.00 |

+-----------+--------+----------+

9 rows in set (0.00 sec)

案例：找出每個部門的最高薪資，要求顯示薪資大于2900的數(shù)據(jù)

第一步：找出每個部門的最高薪資

select max(sal),deptno from emp group by deptno;

第二步：找出薪資大于2900

select max(sal),deptno from emp group by deptno having max(sal) > 2900; //這種方式效率低，不推薦

mysql> select max(sal),deptno from emp where sal>2900 group by deptno;

+----------+--------+

| max(sal) | deptno |

+----------+--------+

| 5000.00 | 10 |

| 3000.00 | 20 |

+----------+--------+

2 rows in set (0.00 sec)

案例：找出每個部門的平均薪資，要求顯示薪資大于2000的數(shù)據(jù)

mysql> select avg(sal),deptno from emp group by deptno having avg(sal) > 2000;

+-------------+--------+

| avg(sal) | deptno |

+-------------+--------+

| 2916.666667 | 10 |

| 2175.000000 | 20 |

+-------------+--------+

2 rows in set (0.00 sec)

select deptno,avg(sal) from emp where avg(sal) >2000 group by deptno; //這樣是錯誤的

去重 distinct

1、關(guān)于查詢結(jié)果集的去重？

distinct關(guān)鍵字去除重復(fù)記錄

mysql> select distinct job from emp;

+-----------+

| job |

+-----------+

| CLERK |

| SALESMAN |

| MANAGER |

| ANALYST |

| PRESIDENT |

+-----------+

5 rows in set (0.00 sec)

select ename,distinct job from job; //這樣的sql語句是錯誤的

記住：distinct只能出現(xiàn)所有字段的最前面，如果出現(xiàn)在最前面則表示后面所有聯(lián)合起來的字段去重

案例：統(tǒng)計崗位的數(shù)量？

mysql> select count(distinct job) from emp;

+---------------------+

| count(distinct job) |

+---------------------+

| 5 |

+---------------------+

1 row in set (0.00 sec)

連接查詢

表的連接方式劃分：

內(nèi)連接

等值連接

非等值連接

自連接

外連接

左外連接(左連接)

右外連接(右連接)

全連接

mysql> select ename,dname from emp,dept;

+--------+------------+

| ename | dname |

+--------+------------+

| SMITH | ACCOUNTING |

| SMITH | RESEARCH |

| SMITH | SALES |

| SMITH | OPERATIONS |

| ALLEN | ACCOUNTING |

| ALLEN | RESEARCH |

| ALLEN | SALES |

| ALLEN | OPERATIONS |

| WARD | ACCOUNTING |

| WARD | RESEARCH |

| WARD | SALES |

| WARD | OPERATIONS |

...

**關(guān)于表的別名：**

select e.ename,d.dname from emp e ,dept d;

好處：一、執(zhí)行效率高二、可讀性好

**案例：找出每一個員工的部門名稱，要求顯示員工的員工名和部門名稱**

mysql> select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;//SQL92 語句已經(jīng)過時，不使用

內(nèi)連接

內(nèi)連接：假設(shè)A和B表進(jìn)行連接，使用內(nèi)連接的話，凡是A表和B表能夠匹配上的記錄查詢出來，這就是內(nèi)連接。

AB兩張表沒有主副之分，兩張表是平等的。

語法：

select

...

from

...

inner join//其中的inner是可以省略不寫的

...

on

...

等值連接

特點：條件是等量關(guān)系。

案例：查詢每個員工的部門名稱，要求顯示員工名和部門名。

SQL99(常用的)

語法：

select

...

form

...

join

...

on //類似where 后面跟的是條件

...

也可 join ... inner ...

mysql> select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;(SQL92 過時的)

mysql> select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;(SQL99 常用)

+--------+------------+

| ename | dname |

+--------+------------+

| CLARK | ACCOUNTING |

| KING | ACCOUNTING |

| MILLER | ACCOUNTING |

| SMITH | RESEARCH |

| JONES | RESEARCH |

| SCOTT | RESEARCH |

| ADAMS | RESEARCH |

| FORD | RESEARCH |

| ALLEN | SALES |

| WARD | SALES |

| MARTIN | SALES |

| BLAKE | SALES |

| TURNER | SALES |

| JAMES | SALES |

+--------+------------+

14 rows in set (0.00 sec)

非等值連接

特點：連接條件中的關(guān)系是非等值關(guān)系

案例：找出每個員工的工資等級，要求顯示員工名、工資、工資等級。

mysql> select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;

+--------+---------+-------+

| ename | sal | grade |

+--------+---------+-------+

| SMITH | 800.00 | 1 |

| ALLEN | 1600.00 | 3 |

| WARD | 1250.00 | 2 |

| JONES | 2975.00 | 4 |

| MARTIN | 1250.00 | 2 |

| BLAKE | 2850.00 | 4 |

| CLARK | 2450.00 | 4 |

| SCOTT | 3000.00 | 4 |

| KING | 5000.00 | 5 |

| TURNER | 1500.00 | 3 |

| ADAMS | 1100.00 | 1 |

| JAMES | 950.00 | 1 |

| FORD | 3000.00 | 4 |

| MILLER | 1300.00 | 2 |

+--------+---------+-------+

14 rows in set (0.00 sec)

自連接

特點：一張表看作兩張表，自己連接自己。

案例：找出每個員工的上級領(lǐng)導(dǎo)，要求顯示員工名和對應(yīng)的領(lǐng)導(dǎo)名

語法：

select

a.ename,b.ename

from

emp a

join

emp b

on

a.mgr = b.empno;

-------------------------------

mysql> select a.ename as '員工',b.ename as '領(lǐng)導(dǎo)' from emp a join emp b on a.mgr = b.empno;

+--------+-------+

| 員工 | 領(lǐng)導(dǎo) |

+--------+-------+

| SMITH | FORD |

| ALLEN | BLAKE |

| WARD | BLAKE |

| JONES | KING |

| MARTIN | BLAKE |

| BLAKE | KING |

| CLARK | KING |

| SCOTT | JONES |

| TURNER | BLAKE |

| ADAMS | SCOTT |

| JAMES | BLAKE |

| FORD | JONES |

| MILLER | CLARK |

+--------+-------+

13 rows in set (0.00 sec)

//因為KING對應(yīng)的領(lǐng)導(dǎo)是NULL所以匹配不到

外連接

外連接：假設(shè)A和B表進(jìn)行連接，使用外連接的話，AB兩張表中有一張表是主表，一張表是副表，主要查詢主表中的數(shù)據(jù)，捎帶著查詢副表，當(dāng)副表中的數(shù)據(jù)沒有和主表中的數(shù)據(jù)匹配上，副標(biāo)自動模擬出NULL與之匹配。

外連接的分類

左外連接(左連接)：表示坐標(biāo)的這張表是主表

右外連接(右連接)：表示右邊的這張表是主表

語法：

select

...

from

...

left/right outer join//其中的outer是可以省略不寫的

...

on

...

案例：找出每個員工的上級領(lǐng)導(dǎo)

mysql> select

-> a.ename emp,b.ename boss

-> from

-> emp a

-> left join

-> emp b

-> on

-> a.mgr=b.empno;

+--------+-------+

| emp | boss |

+--------+-------+

| SMITH | FORD |

| ALLEN | BLAKE |

| WARD | BLAKE |

| JONES | KING |

| MARTIN | BLAKE |

| BLAKE | KING |

| CLARK | KING |

| SCOTT | JONES |

| KING | NULL |

| TURNER | BLAKE |

| ADAMS | SCOTT |

| JAMES | BLAKE |

| FORD | JONES |

| MILLER | CLARK |

+--------+-------+

14 rows in set (0.00 sec)

案例：找出哪個部門沒有員工？

mysql> select d.* from emp e right join dept d on e.deptno=d.deptno where e.ename is null;

+--------+------------+--------+

| DEPTNO | DNAME | LOC |

+--------+------------+--------+

| 40 | OPERATIONS | BOSTON |

+--------+------------+--------+

1 row in set (0.00 sec)

同樣可一使用以下方法

mysql> select d.dname,count(e.ename) from emp e right outer join dept d on e.deptno=d.deptno group by dname;

+------------+----------------+

| dname | count(e.ename) |

+------------+----------------+

| ACCOUNTING | 3 |

| OPERATIONS | 0 |

| RESEARCH | 5 |

| SALES | 6 |

+------------+----------------+

4 rows in set (0.00 sec)

三張表怎么連接查詢？

案例：找出每一個員工的部門名稱以及工資等級

格式：

select

e.ename,d.dname,s.grade

from

emp e

join

dept d

on

e.deptno=d.deptno

join

salgrade s

on

e.sal between s.losal and s.hisal;

結(jié)果：

+--------+------------+-------+

| ename | dname | grade |

+--------+------------+-------+

| SMITH | RESEARCH | 1 |

| ALLEN | SALES | 3 |

| WARD | SALES | 2 |

| JONES | RESEARCH | 4 |

| MARTIN | SALES | 2 |

| BLAKE | SALES | 4 |

| CLARK | ACCOUNTING | 4 |

| SCOTT | RESEARCH | 4 |

| KING | ACCOUNTING | 5 |

| TURNER | SALES | 3 |

| ADAMS | RESEARCH | 1 |

| JAMES | SALES | 1 |

| FORD | RESEARCH | 4 |

| MILLER | ACCOUNTING | 2 |

+--------+------------+-------+

14 rows in set (0.00 sec)

案例：找出每一個員工的部門名稱、工資等級、以及上級領(lǐng)導(dǎo)。

select

e.ename,d.dname,s.grade,a.ename

from

emp e

join

dept d

on

e.deptno=d.deptno

join

salgrade s

on

e.sal between s.losal and s.hisal

left join

emp a

on

e.mgr=a.empno;

結(jié)果：

+--------+------------+-------+-------+

| ename | dname | grade | ename |

+--------+------------+-------+-------+

| SMITH | RESEARCH | 1 | FORD |

| ALLEN | SALES | 3 | BLAKE |

| WARD | SALES | 2 | BLAKE |

| JONES | RESEARCH | 4 | KING |

| MARTIN | SALES | 2 | BLAKE |

| BLAKE | SALES | 4 | KING |

| CLARK | ACCOUNTING | 4 | KING |

| SCOTT | RESEARCH | 4 | JONES |

| KING | ACCOUNTING | 5 | NULL |

| TURNER | SALES | 3 | BLAKE |

| ADAMS | RESEARCH | 1 | SCOTT |

| JAMES | SALES | 1 | BLAKE |

| FORD | RESEARCH | 4 | JONES |

| MILLER | ACCOUNTING | 2 | CLARK |

+--------+------------+-------+-------+

14 rows in set (0.00 sec)

子查詢

什么是子查詢？子查詢都可以出現(xiàn)在哪里？

子查詢可以出現(xiàn)在哪里？

select

...(select)

form

...(select)

where

...(select)

where子句中使用子查詢

案例：找出高于平均薪資的員工的信息

select * from emp where sal>(select avg(sal) from emp);

from后面嵌套子查詢

案例：找出每個部門平均薪水的薪資等級。

第一步：先找出每個部門平均薪水的薪資等級

mysql> select deptno,avg(sal) from emp group by deptno;

+--------+-------------+

| deptno | avg(sal) |

+--------+-------------+

| 10 | 2916.666667 |

| 20 | 2175.000000 |

| 30 | 1566.666667 |

+--------+-------------+

3 rows in set (0.00 sec)

第二步：將以上查詢的結(jié)果當(dāng)做臨時表t，讓t表和salgrade s表連接，條件是：t.avgsal between s.losal and s.hisal

mysql> select

-> t.deptno,t.avgsal,s.grade

-> from

-> (select deptno,avg(sal) avgsal from emp group by deptno) t

-> join

-> salgrade s

-> on

-> avgsal between s.losal and s.hisal;

+--------+-------------+-------+

| deptno | avgsal | grade |

+--------+-------------+-------+

| 30 | 1566.666667 | 3 |

| 10 | 2916.666667 | 4 |

| 20 | 2175.000000 | 4 |

+--------+-------------+-------+

3 rows in set (0.00 sec)

案例：找出每個部門平均的薪水等級

第一步：找出每個員工的薪水等級。

mysql> select e.ename,e.sal,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and

s.hisal;

+--------+---------+--------+-------+

| ename | sal | deptno | grade |

+--------+---------+--------+-------+

| SMITH | 800.00 | 20 | 1 |

| ALLEN | 1600.00 | 30 | 3 |

| WARD | 1250.00 | 30 | 2 |

| JONES | 2975.00 | 20 | 4 |

| MARTIN | 1250.00 | 30 | 2 |

| BLAKE | 2850.00 | 30 | 4 |

| CLARK | 2450.00 | 10 | 4 |

| SCOTT | 3000.00 | 20 | 4 |

| KING | 5000.00 | 10 | 5 |

| TURNER | 1500.00 | 30 | 3 |

| ADAMS | 1100.00 | 20 | 1 |

| JAMES | 950.00 | 30 | 1 |

| FORD | 3000.00 | 20 | 4 |

| MILLER | 1300.00 | 10 | 2 |

+--------+---------+--------+-------+

14 rows in set (0.00 sec)

第二步：基于以上結(jié)果，繼續(xù)按照deptno分組，求grade平均值

mysql> select e.deptno,avg(s.grade)

-> from emp e

-> join salgrade s

-> on e.sal between s.losal and s.hisal

-> group by e.deptno;

+--------+--------------+

| deptno | avg(s.grade) |

+--------+--------------+

| 10 | 3.6667 |

| 20 | 2.8000 |

| 30 | 2.5000 |

+--------+--------------+

3 rows in set (0.00 sec)

在select后面嵌套子查詢

案例：找出每個員工所在的部門名稱，要求顯示員工名和部門名

mysql> select

-> e.ename,(select d.dname from dept d where e.deptno=d.deptno) as dname

-> from emp e;

+--------+------------+

| ename | dname |

+--------+------------+

| SMITH | RESEARCH |

| ALLEN | SALES |

| WARD | SALES |

| JONES | RESEARCH |

| MARTIN | SALES |

| BLAKE | SALES |

| CLARK | ACCOUNTING |

| SCOTT | RESEARCH |

| KING | ACCOUNTING |

| TURNER | SALES |

| ADAMS | RESEARCH |

| JAMES | SALES |

| FORD | RESEARCH |

| MILLER | ACCOUNTING |

+--------+------------+

14 rows in set (0.00 sec)

union

可以將查詢結(jié)果集相加

案例：找出工作崗位是SALESMAN和MANAGER的員工？

第一種：select ename,job from emp where job='salesman' or job='manager';

第二種：select ename,job from emp where job in ('salesman','manager');

第三種:union

select ename,job from emp where job='manager'

union

select ename,job from emp where job='salesman';

union可以實現(xiàn)把兩張不相干的表中的數(shù)據(jù)拼接在一起顯示，前提必須是要顯示相同的列數(shù)

例如：把員工名和部門名拼接在一塊顯示

mysql> select ename from emp

-> union

-> select dname from dept;

+------------+

| ename |

+------------+

| SMITH |

| ALLEN |

| WARD |

| JONES |

| MARTIN |

| BLAKE |

| CLARK |

| SCOTT |

| KING |

| TURNER |

| ADAMS |

| JAMES |

| FORD |

| MILLER |

| ACCOUNTING |

| RESEARCH |

| SALES |

| OPERATIONS |

+------------+

18 rows in set (0.00 sec)

limit ★

limit：取結(jié)果集中的部分?jǐn)?shù)據(jù)。

limit是MySQL特有的，其他數(shù)據(jù)庫中沒有，不通用。(Oracle中有一個相同的機制，叫做rownum)

limit是sql語句最后執(zhí)行的一個環(huán)節(jié)

語法：limit startIndex,length

startIndex表示起始位置，可以省略不寫，默認(rèn)為0

length表示取幾個

案例：取出工資前5名的員工(思路：降序取前5個)

mysql> select ename,sal from emp order by sal desc limit 0,5;

+-------+---------+

| ename | sal |

+-------+---------+

| KING | 5000.00 |

| SCOTT | 3000.00 |

| FORD | 3000.00 |

| JONES | 2975.00 |

| BLAKE | 2850.00 |

+-------+---------+

5 rows in set (0.00 sec)

案例：找出工資排名在第4到第9名的員工？

mysql> select ename,sal from emp order by sal desc limit 3,6;

+--------+---------+

| ename | sal |

+--------+---------+

| JONES | 2975.00 |

| BLAKE | 2850.00 |

| CLARK | 2450.00 |

| ALLEN | 1600.00 |

| TURNER | 1500.00 |

| MILLER | 1300.00 |

+--------+---------+

6 rows in set (0.00 sec)

通用標(biāo)準(zhǔn)分頁sql

每頁顯示5條記錄：

第1頁：0, 5

第2頁：5， 5

第3頁：10，5

第4頁：15，5

第5頁：20，5

...

所以可得：每頁顯示pageSize條記錄：

第pageNo頁：(pageNo - 1) * pageSize , pageSize

pageSize : 代表每頁顯示多少條記錄

pageNo : 代表顯示第幾頁

java代碼{

int pageNo = 2;//頁碼是2

int pageSize = 10;//每頁顯示10條

limit (pageNo - 1) * pageSize , pageSize;

}

總結(jié)

以上是生活随笔為你收集整理的mysql怎么查找列命令_MySQL查询命令-DQL的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。