（七）MySQL資料操作DQL:多表查詢2

標籤：and   rom   外串連   資料   com   join   values   嵌套   not   

（1）準備環境1）建立員工表

mysql> create table company.employee6(    -> emp_id int auto_increment primary key not null,    -> emp_name varchar(10),    -> age int,    -> dept_id int);mysql> insert into employee6(emp_name,age,dept_id) values    -> (‘tom‘,19,200),    -> (‘jack‘,30,201),    -> (‘alice‘,24,202),    -> (‘robin‘,40,200),    -> (‘natasha‘,28,204);

2）建立部門表

mysql> create table company.department(    -> dept_id int,    -> dept_name varchar(100));mysql> insert into department values (200,‘hr‘), (201,‘it‘), (202,‘sale‘), (203,‘fd‘);

（2）交叉串連

產生笛卡爾積，不使用任何匹配條件
文法：select 表1.欄位1,表1.欄位2,表2.欄位1 from 表1,表2;

mysql> select employee6.emp_name,employee6.age,employee6.dept_id,department.dept_name from employee6,department;+----------+------+---------+-----------+| emp_name | age  | dept_id | dept_name |+----------+------+---------+-----------+| tom      |   19 |     200 | hr        || tom      |   19 |     200 | it        || tom      |   19 |     200 | sale      || tom      |   19 |     200 | fd        || jack     |   30 |     201 | hr        || jack     |   30 |     201 | it        || jack     |   30 |     201 | sale      || jack     |   30 |     201 | fd        || alice    |   24 |     202 | hr        || alice    |   24 |     202 | it        || alice    |   24 |     202 | sale      || alice    |   24 |     202 | fd        || robin    |   40 |     200 | hr        || robin    |   40 |     200 | it        || robin    |   40 |     200 | sale      || robin    |   40 |     200 | fd        || natasha  |   28 |     204 | hr        || natasha  |   28 |     204 | it        || natasha  |   28 |     204 | sale      || natasha  |   28 |     204 | fd        |+----------+------+---------+-----------+

（3）內串連：根據兩張表的相同欄位只串連匹配的行

文法：select 表1.欄位n,表2.欄位n from 表1,表2 表1.欄位 = 表2.欄位
根據員工表的dept_id 和部門表的dept_id進行串連，只匹配dept_id相同的行

mysql> select employee6.dept_id,employee6.emp_name,employee6.age,department.dept_name from employee6,department where employee6.dept_id = department.dept_id;+---------+----------+------+-----------+| dept_id | emp_name | age  | dept_name |+---------+----------+------+-----------+|     200 | tom      |   19 | hr        ||     201 | jack     |   30 | it        ||     202 | alice    |   24 | sale      ||     200 | robin    |   40 | hr        |+---------+----------+------+-----------+

（4）外串連

文法：select 欄位列表 from 表1 left|right join 表2 on 表1.欄位 = 表2.欄位

1）外串連之左串連：會顯示左邊表內所有的值，不論在右邊表內匹不匹配

mysql> select emp_id,emp_name,age,dept_name from employee6 left join department on employee6.dept_id = department.dept_id;+--------+----------+------+-----------+| emp_id | emp_name | age  | dept_name |+--------+----------+------+-----------+|      1 | tom      |   19 | hr        ||      4 | robin    |   40 | hr        ||      2 | jack     |   30 | it        ||      3 | alice    |   24 | sale      ||      5 | natasha  |   28 | NULL      |+--------+----------+------+-----------+

2）外串連之右串連:會顯示右邊表內所有的值，不論在左邊表內匹不匹配

mysql> select emp_id,emp_name,age,dept_name from employee6 right join department on employee6.dept_id = department.dept_id;+--------+----------+------+-----------+| emp_id | emp_name | age  | dept_name |+--------+----------+------+-----------+|      1 | tom      |   19 | hr        ||      2 | jack     |   30 | it        ||      3 | alice    |   24 | sale      ||      4 | robin    |   40 | hr        ||   NULL | NULL     | NULL | fd        |+--------+----------+------+-----------+

（5）複合條件串連查詢

以內串連的方式查詢 employee6 和 department 表，並且 employee6 表中的 age 欄位值必須大於 25，排序

mysql> select emp_id,emp_name,age,dept_name from employee6,department where employee6.dept_id = department.dept_id and age >25;+--------+----------+------+-----------+| emp_id | emp_name | age  | dept_name |+--------+----------+------+-----------+|      4 | robin    |   40 | hr        ||      2 | jack     |   30 | it        |+--------+----------+------+-----------+2 rows in set (0.00 sec)mysql> select emp_id,emp_name,age,dept_name from employee6,department where employee6.dept_id = department.dept_id and age >25 order by age;+--------+----------+------+-----------+| emp_id | emp_name | age  | dept_name |+--------+----------+------+-----------+|      2 | jack     |   30 | it        ||      4 | robin    |   40 | hr        |+--------+----------+------+-----------+2 rows in set (0.00 sec)mysql> select emp_id,emp_name,age,dept_name from employee6,department where employee6.dept_id = department.dept_id and age >25 order by age desc;+--------+----------+------+-----------+| emp_id | emp_name | age  | dept_name |+--------+----------+------+-----------+|      4 | robin    |   40 | hr        ||      2 | jack     |   30 | it        |+--------+----------+------+-----------+

（6）子查詢

子查詢是將一個查詢語句嵌套在另一個查詢語句中。內層查詢語句的查詢結果，可以為外層查詢語句提供查詢條件。

1）帶in的子查詢

mysql> select * from employee6 where dept_id in (select dept_id from department);+--------+----------+------+---------+| emp_id | emp_name | age  | dept_id |+--------+----------+------+---------+|      1 | tom      |   19 |     200 ||      2 | jack     |   30 |     201 ||      3 | alice    |   24 |     202 ||      4 | robin    |   40 |     200 |+--------+----------+------+---------+4 rows in set (0.00 sec)

2）帶比較子的子查詢

mysql> select dept_name from department where dept_id in (select dept_id from employee6 where age >25); +-----------+| dept_name |+-----------+| it        || hr        |+-----------+2 rows in set (0.00 sec)

（七）MySQL資料操作DQL:多表查詢2