MySQL 多表操作

There is recorded some MySQL multilist operation.

多表查询

• 使用别名进行多表查询

  mysql> select t1.* from t_user t1, t_deptment t2 where t1.id = t2.id;
  +----+------+------+
  | id | name | sex  |
  +----+------+------+
  |  1 | u1   | a    |
  |  2 | u2   | b    |
  |  3 | u3   | c    |
  +----+------+------+
  3 rows in set (0.00 sec)

  mysql> select t1.id,t1.name, t2.id,t2.name from
      -> t_user t1, t_deptment t2
      -> where t1.id = t2.id
      -> and t2.id = 2;
  +----+------+----+------+
  | id | name | id | name |
  +----+------+----+------+
  |  2 | u2   |  2 | bb   |
  +----+------+----+------+
  1 row in set (0.00 sec)

• 如果在GROUP BY后接WHERE的话会报错，GROUP BY后不能接WHERE子句，但是可以接HAVING子句

  mysql> select id from t_user group by id where id > 2;
  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 'where id > 2' at line 1

  mysql> select id from t_user group by id having id > 2;
  +----+
  | id |
  +----+
  |  3 |
  |  4 |
  +----+
  2 rows in set (0.00 sec)

多表连接

多表连接记得使用条件判断取出拥有相同条件的多表的数据，否则会出现重复的数据
多个表使用多个inner join连接
要查询的表

mysql> select * from t1;
+----+------+
| c1 | c2   |
+----+------+
| a  | b    |
| b  | c    |
| c  | d    |
+----+------+
3 rows in set (0.00 sec)

mysql> select * from t2;
+----+------+
| c3 | c4   |
+----+------+
| b  | d    |
| c  | f    |
| h  | j    |
+----+------+
3 rows in set (0.00 sec)

内连接

将两个表符合条件的数据查询出来并列在一起

mysql> select * from t1 inner join t2 on c1 = c3;
+----+------+----+------+
| c1 | c2   | c3 | c4   |
+----+------+----+------+
| b  | c    | b  | d    |
| c  | d    | c  | f    |
+----+------+----+------+
2 rows in set (0.00 sec)

左连接

将两个表符合条件的数据查询出来并列在一起，不符合的数据只放置第一个表的数据，另一个表的数据置空

mysql> select * from t1 left join t2 on c1 = c3;
+----+------+------+------+
| c1 | c2   | c3   | c4   |
+----+------+------+------+
| b  | c    | b    | d    |
| c  | d    | c    | f    |
| a  | b    | NULL | NULL |
+----+------+------+------+
3 rows in set (0.00 sec)

右连接

将两个表符合条件的数据查询出来并列在一起，不符合的数据只放置第二个表的数据，另一个表的数据置空

mysql> select * from t1 right join t2 on c1 = c3;
+------+------+----+------+
| c1   | c2   | c3 | c4   |
+------+------+----+------+
| b    | c    | b  | d    |
| c    | d    | c  | f    |
| NULL | NULL | h  | j    |
+------+------+----+------+
3 rows in set (0.00 sec)