多表查询
多表查询
新建表
mysql> create table employee6(
-> emp_id int auto_increment primary key,
-> emp_name varchar(50),
-> age int,
-> dept_id int);
Query OK, 0 rows affected (0.18 sec)
mysql> create table department6(
-> dept_id int,
-> dept_name varchar(50));
Query OK, 0 rows affected (0.17 sec)
插入数据
mysql> insert into employee6(emp_name,age,dept_id) values
-> ('tianyun',19,200),
-> ('tom',26,201),
-> ('jack',30,202),
-> ('alice',24,203),
-> ('robin',40,204),
-> ('narasha',28,205);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> insert into department6 values(200,'hr'), (201,'it'), (202,'sale'), (203,'fd');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
查看表
mysql> select * from employee6;
+--------+----------+------+---------+
| emp_id | emp_name | age | dept_id |
+--------+----------+------+---------+
| 1 | tianyun | 19 | 200 |
| 2 | tom | 26 | 201 |
| 3 | jack | 30 | 202 |
| 4 | alice | 24 | 203 |
| 5 | robin | 40 | 204 |
| 6 | narasha | 28 | 205 |
+--------+----------+------+---------+
6 rows in set (0.00 sec)
mysql> select * from department6;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 200 | hr |
| 201 | it |
| 202 | sale |
| 203 | fd |
+---------+-----------+
4 rows in set (0.00 sec)
内连接(只连接匹配的行)
mysql>select employee6.emp_id,employee6.emp_name, employee6.age, department6.dept_name
->from employee6, department6
->where employee66.dept_id = department6.dept_id;
+--------+----------+------+-----------+
| emp_id | emp_name | age | dept_name |
+--------+----------+------+-----------+
| 1 | tianyun | 19 | hr |
| 2 | tom | 26 | it |
| 3 | jack | 30 | sale |
| 4 | alice | 24 | fd |
+--------+----------+------+-----------+
4 rows in set (0.00 sec)
可以简写为,但是如果该字段两张表都存在,则不能简写
mysql> select emp_id,emp_name,age,dept_name from employee6, department6 where employee6.dept_id = department6.dept_id;
+--------+----------+------+-----------+
| emp_id | emp_name | age | dept_name |
+--------+----------+------+-----------+
| 1 | tianyun | 19 | hr |
| 2 | tom | 26 | it |
| 3 | jack | 30 | sale |
| 4 | alice | 24 | fd |
+--------+----------+------+-----------+
4 rows in set (0.00 sec)
发现只显示了有部门的员工
外连接
左连接 以left join左面的表为准
mysql> select emp_id,emp_name,age,dept_name from employee6 left join department6 on emplooyee6
+--------+----------+------+-----------+
| emp_id | emp_name | age | dept_name |
+--------+----------+------+-----------+
| 1 | tianyun | 19 | hr |
| 2 | tom | 26 | it |
| 3 | jack | 30 | sale |
| 4 | alice | 24 | fd |
| 5 | robin | 40 | NULL |
| 6 | narasha | 28 | NULL |
+--------+----------+------+-----------+
6 rows in set (0.00 sec)
右连接,以right join左面的表为准
mysql> insert into department6 values(510,'os');
Query OK, 1 row affected (0.04 sec)
mysql> select emp_id,emp_name,age,dept_name from employee6 right join department6 on emplloyee6
+--------+----------+------+-----------+
| emp_id | emp_name | age | dept_name |
+--------+----------+------+-----------+
| 1 | tianyun | 19 | hr |
| 2 | tom | 26 | it |
| 3 | jack | 30 | sale |
| 4 | alice | 24 | fd |
| NULL | NULL | NULL | os |
+--------+----------+------+-----------+
5 rows in set (0.00 sec)
复合查询
以内连接的方式查询employee6和department6表,并且employee6表中的age字段值需要大于25
找出公司部门中年龄大于25岁的员工
mysql> select emp_id,emp_name,age,dept_name from employee6, department6 where employee6.dept_id = department6.dept_id and age>=25;
+--------+----------+------+-----------+
| emp_id | emp_name | age | dept_name |
+--------+----------+------+-----------+
| 2 | tom | 26 | it |
| 3 | jack | 30 | sale |
+--------+----------+------+-----------+
2 rows in set (0.00 sec)
以内连接的方式查询employee6和department6表,并且以age字段的升序显示
mysql> select emp_id,emp_name,age,dept_name from employee6, department6 where employee6.dept_id = department6.dept_id order by age;
+--------+----------+------+-----------+
| emp_id | emp_name | age | dept_name |
+--------+----------+------+-----------+
| 1 | tianyun | 19 | hr |
| 4 | alice | 24 | fd |
| 2 | tom | 26 | it |
| 3 | jack | 30 | sale |
+--------+----------+------+-----------+
4 rows in set (0.00 sec)