mysql表的内连接，左连接，右连接，全连接，自然连接

mysql> desc department;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | 0       |       |
| dep_name | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> desc employee;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| emp_id   | int(11)     | YES  |     | NULL    |       |
| emp_name | varchar(20) | YES  |     | NULL    |       |
| dep_id   | int(11)     | YES  | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> select e.emp_id,e.emp_name,d.id,d.dep_name from employee as e,department as d where e.dep_id=d.id;
+--------+----------+----+----------+
| emp_id | emp_name | id | dep_name |
+--------+----------+----+----------+
|      1 | cai      |  1 | java     |
|      2 | tang     |  1 | java     |
|      3 | zhou     |  2 | c#       |
|      4 | wang     |  2 | c#       |
|      5 | jiao     |  2 | c#       |
+--------+----------+----+----------+
5 rows in set (0.05 sec)

mysql> select e.emp_id,e.emp_name,d.id,d.dep_name from employee as e inner join  department as d on e.dep_id=d.id;
+--------+----------+----+----------+
| emp_id | emp_name | id | dep_name |
+--------+----------+----+----------+
|      1 | cai      |  1 | java     |
|      2 | tang     |  1 | java     |
|      3 | zhou     |  2 | c#       |
|      4 | wang     |  2 | c#       |
|      5 | jiao     |  2 | c#       |
+--------+----------+----+----------+
5 rows in set (0.00 sec)

mysql> select e.emp_id,e.emp_name,d.id,d.dep_name from employee as e left outer join  department as d on e.dep_id=d.id;
+--------+----------+------+----------+
| emp_id | emp_name | id   | dep_name |
+--------+----------+------+----------+
|      1 | cai      |    1 | java     |
|      2 | tang     |    1 | java     |
|      3 | zhou     |    2 | c#       |
|      4 | wang     |    2 | c#       |
|      5 | jiao     |    2 | c#       |
+--------+----------+------+----------+
5 rows in set (0.07 sec)

mysql> select e.emp_id,e.emp_name,d.id,d.dep_name from employee as e right outer join  department as d on e.dep_id=d.id;
+--------+----------+----+----------+
| emp_id | emp_name | id | dep_name |
+--------+----------+----+----------+
|      1 | cai      |  1 | java     |
|      2 | tang     |  1 | java     |
|      3 | zhou     |  2 | c#       |
|      4 | wang     |  2 | c#       |
|      5 | jiao     |  2 | c#       |
|   NULL | NULL     |  3 | php      |
+--------+----------+----+----------+
6 rows in set (0.00 sec)

mysql> select e.emp_id,e.emp_name,d.id,d.dep_name from employee as e right outer join  department as d on e.dep_id=d.id union select e.emp_id,e.emp_name,d.id,d.emp_name from employee as e left outer join department as d on e.dep_id=d.id;
ERROR 1054 (42S22): Unknown column 'd.emp_name' in 'field list'
mysql> select e.emp_id,e.emp_name,d.id,d.dep_name from employee as e right outer join  department as d on e.dep_id=d.id union select e.emp_id,e.emp_name,d.id,d.dep_name from employee as e left outer join department as d on e.dep_id=d.id;
+--------+----------+------+----------+
| emp_id | emp_name | id   | dep_name |
+--------+----------+------+----------+
|      1 | cai      |    1 | java     |
|      2 | tang     |    1 | java     |
|      3 | zhou     |    2 | c#       |
|      4 | wang     |    2 | c#       |
|      5 | jiao     |    2 | c#       |
|   NULL | NULL     |    3 | php      |
+--------+----------+------+----------+
6 rows in set (0.00 sec)

mysql> alter table employee
    -> change column emp_id id;
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 '' at line 2
mysql> drop table employee
    -> ;
Query OK, 0 rows affected (0.06 sec)

mysql> drop table department;
Query OK, 0 rows affected (0.07 sec)

mysql> exit