Mysql多表查詢 –一個例子基本搞通mysql語句

Mysql資料庫的進階查詢 多表查詢，聯表查詢

 

查詢emp表中，emp_name為嘯天的全部資訊

mysql> select * from emp where emp_name='嘯天';

 

查詢結果顯示如下：

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

| emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |

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

| 100005 | 嘯天 | 27 | 4000 | 1979-07-10 | male |

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

1 row in set (0.00 sec)

 

查詢emp表中，emp_sal，工資在5000以上的全部資訊

mysql> select * from emp where emp_sal>5000;

 

查詢結果顯示如下：

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

| emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |

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

| 100001 | 紅楓 | 29 | 9000 | 1977-01-01 | male |

| 100002 | 麗鵑 | 27 | 8000 | 1979-12-31 | fmale |

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

2 rows in set (0.00 sec)

 

查詢emp表中在1978年1月1日之後出生的

mysql> select * from emp where emp_bir>'1978-01-01';

 

查詢結果顯示如下：

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

| emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |

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

| 100005 | 嘯天 | 27 | 4000 | 1979-07-10 | male |

| 100002 | 麗鵑 | 27 | 8000 | 1979-12-31 | fmale |

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

2 rows in set (0.00 sec)

 

查詢emp表中在1979年12月1日之前出生，工資在5000以上的

mysql> select * from emp where emp_bir<'1979-12-01' and emp_sal>5000;

 

查詢結果顯示如下：

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

| emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |

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

| 100001 | 紅楓 | 29 | 9000 | 1977-01-01 | male |

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

1 row in set (0.00 sec)

 

2.6.2 欄位查詢

 

CEO查看員工工資情況

mysql> select emp_name,emp_sal from emp;

 

查詢結果顯示如下：

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

| emp_name | emp_sal |

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

| 嘯天 | 4000 |

| 紅楓 | 9000 |

| 麗鵑 | 8000 |

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

3 rows in set (0.00 sec)

 

查看1978年後出生的人的姓名、工資和性別

mysql> select emp_name,emp_sal,emp_sex from emp where emp_bir>"1977-12-31";

 

查詢結果顯示如下：

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

| emp_name | emp_sal | emp_sex |

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

| 嘯天 | 4000 | male |

| 麗鵑 | 8000 | fmale |

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

2 rows in set (0.00 sec)

 

2.6.3 查詢結果排序

 

用ORDER BY語句對emp表中所有員工工資高低順序查詢結果（預設是從低到高——升序）

mysql> select * from emp order by emp_sal;

 

查詢結果顯示如下：

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

| emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |

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

| 100005 | 嘯天 | 27 | 4000 | 1979-07-10 | male |

| 100002 | 麗鵑 | 27 | 8000 | 1979-12-31 | fmale |

| 100001 | 紅楓 | 29 | 9000 | 1977-01-01 | male |

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

3 rows in set (0.00 sec)

 

用DESC關鍵字來進行從高到低排序——降序

mysql> select * from emp order by emp_sal desc;

 

查詢結果顯示如下：

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

| emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |

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

| 100001 | 紅楓 | 29 | 9000 | 1977-01-01 | male |

| 100002 | 麗鵑 | 27 | 8000 | 1979-12-31 | fmale |

| 100005 | 嘯天 | 27 | 4000 | 1979-07-10 | male |

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

3 rows in set (0.00 sec)

 

2.6.4 查詢結果數量的限制

 

用LIMIT查看emp表中工資收入排名前兩個員工的資料：

mysql> select * from emp order by emp_sal desc limit 2;

 

查詢結果顯示如下：

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

| emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |

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

| 100001 | 紅楓 | 29 | 9000 | 1977-01-01 | male |

| 100002 | 麗鵑 | 27 | 8000 | 1979-12-31 | fmale |

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

2 rows in set (0.00 sec)

 

查看工資排名第2到第3的員工資料：

mysql> select * from emp order by emp_sal desc limit 1,2;

 

查詢結果顯示如下：

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

| emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |

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

| 100002 | 麗鵑 | 27 | 8000 | 1979-12-31 | fmale |

| 100005 | 嘯天 | 27 | 4000 | 1979-07-10 | male |

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

2 rows in set (0.01 sec)

 

使用rand()抽樣調查，隨機抽取2個員工，查看其資料

mysql> select * from emp order by rand() limit 2;

 

如如下結果：（隨機的）

 

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

| emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |

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

| 100005 | 嘯天 | 27 | 4000 | 1979-07-10 | male |

| 100001 | 紅楓 | 29 | 9000 | 1977-01-01 | male |

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

2 rows in set (0.01 sec)

 

2.6.5 查詢結果的欄位聯合和重新命名

 

mysql> select concat(emp_id," ",emp_name) from emp;

 

查詢結果：

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

| concat(emp_id," ",emp_name) |

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

| 100005 嘯天 |

| 100001 紅楓 |

| 100002 麗鵑 |

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

3 rows in set (0.00 sec)

 

用AS關鍵字重新給輸出結果命名標題

mysql> select concat(emp_id," ",emp_name) as info from emp;

 

查詢結果如下顯示：

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

| info |

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

| 100005 嘯天 |

| 100001 紅楓 |

| 100002 麗鵑 |

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

3 rows in set (0.00 sec)

 

2.6.6 日期查詢的相關運算

 

可以通過YEAR()、MONTH()、DAYOFMONTH()函數來提取日期的組成元素

 

查詢7月份出生的員工資料：

mysql> select * from emp where month(emp_bir)=7;

 

查詢結果顯示如下：

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

| emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |

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

| 100005 | 嘯天 | 27 | 4000 | 1979-07-10 | male |

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

1 row in set (0.00 sec)

 

可以利用英文月份來查詢：

mysql> select * from emp where monthname(emp_bir)="January";

 

查詢結果顯示如下：

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

| emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |

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

| 100001 | 紅楓 | 29 | 9000 | 1977-01-01 | male |

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

1 row in set (0.00 sec)

 

利用TO_DAYS()函數可以查詢出職工們從出生到現在所經理的時間，單位是天數

mysql> select to_days(current_date) - to_days(emp_bir) as livingdays from emp;

 

查詢後結果如下：

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

| livingdays |

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

| 9425 |

| 10345 |

| 9251 |

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

3 rows in set (0.00 sec)

 

計算從現在開始經曆100天后的日期

mysql> select date_add(now(),interval 100 day);

 

查詢結果如下：

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

| date_add(now(),interval 100 day) |

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

| 2005-08-07 13:56:58 |

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

1 row in set (0.00 sec)

 

計算從現在開始經曆100天后的日期

mysql> select date_sub(now(),interval 100 day);

 

查詢結果如下：

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

| date_sub(now(),interval 100 day) |

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

| 2005-01-19 14:00:20 |

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

1 row in set (0.00 sec)

 

2.6.7 資料統計

 

使用COUNT()Function Compute表中的資料數目（比如emp表中的員工數目）

mysql> select count(*) from emp;

 

查詢結果如下：

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

| count(*) |

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

| 3 |

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

1 row in set (0.01 sec)

 

統計工資上5000的數目

mysql> select count(*) from emp where emp_sal>5000;

 

查詢結果如下：

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

| count(*) |

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

| 2 |

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

1 row in set (0.00 sec)

 

統計男女職工數目：（GROUP BY語句分類）

mysql> select emp_sex,count(*) from emp group by emp_sex;

 

查詢結果如下：

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

| emp_sex | count(*) |

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

| fmale | 1 |

| male | 2 |

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

2 rows in set (0.01 sec)

 

使用資料統計函數（MIN(),MAX(),SUM(),AVG()）

mysql> select

-> min(emp_sal) as min_salary,

-> max(emp_sal) as max_salary,

-> sum(emp_sal) as sum_salary,

-> avg(emp_sal) as avg_salary,

-> count(*) as employee_num

-> from emp;

 

查詢結果如下：

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

| min_salary | max_salary | sum_salary | avg_salary | employee_num |

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

| 4000 | 9000 | 21000 | 7000.0000 | 3 |

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

1 row in set (0.00 sec)

 

2.6.8 從多個資料表中檢索資訊

 

根據前面的方法，分別進行如下操作：

1). 在資料庫asb中建立一個新表dept，表中有兩項元素：

dept_id --> varchar(6)

dept_name --> varchar(10)

2). 在表emp中插入如下一行新記錄：

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

| emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |

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

| 100003 | 小紅 | 30 | 8000 | 1976-11-11 | fmale |

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

3). 在新表dept中，輸入如下記錄

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

| dept_id | dept_name |

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

| 100005 | MTD |

| 100001 | MTD |

| 100002 | MTD |

| 100003 | HR |

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

 

查詢emp和dept這兩個表中，員工的姓名和部門資訊

mysql> select emp.emp_name,dept.dept_name from emp,dept

-> where emp.emp_id=dept.dept_id;

 

查詢結果如下：

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

| emp_name | dept_name |

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

| 嘯天 | MTD |

| 紅楓 | MTD |

| 麗鵑 | MTD |

| 小紅 | HR |

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

4 rows in set (0.00 sec)

 

多表查詢時注意：

1). FROM子句必須給出所查詢的表的全部名稱

2). 選擇欄位時候註明其所屬表的名稱（如emp表中的emp_id要表示為emp.emp_id）

3). 在Where子句中必須指明查詢的條件（如，emp.emp_id和dept.dept_id是相同意義的元素）