MySql查询多个表的数据

可以直接select或者创建视图后select。

MariaDB [mysql]> CREATE DATABASE my_test;
Query OK, 1 row affected (0.08 sec)

MariaDB [mysql]> use my_test;
Database changed
MariaDB [my_test]> CREATE TABLE t1 (id int NOT NULL, name VARCHAR(20), age int);
Query OK, 0 rows affected (0.62 sec)

MariaDB [my_test]> CREATE TABLE t2 (id int NOT NULL, score int);
Query OK, 0 rows affected (0.15 sec)

MariaDB [my_test]> INSERT INTO t1 VALUES (20160101, 'Johnson', 20);
Query OK, 1 row affected (0.14 sec)

MariaDB [my_test]> INSERT INTO t1 VALUES (20160101, 'Tom', 30);
Query OK, 1 row affected (0.04 sec)

MariaDB [my_test]> SELECT * FROM t1;
+----------+---------+------+
| id       | name    | age  |
+----------+---------+------+
| 20160101 | Johnson |   20 |
| 20160101 | Tom     |   30 |
+----------+---------+------+
2 rows in set (0.00 sec)

MariaDB [my_test]> ALTER TABLE t1 ADD PRIMARY KEY (id);
ERROR 1062 (23000): Duplicate entry '20160101' for key 'PRIMARY'
MariaDB [my_test]> DELETE FROM t1 WHERE name = 'Tom';
Query OK, 1 row affected (0.07 sec)

MariaDB [my_test]> ALTER TABLE t1 ADD PRIMARY KEY (id);
Query OK, 0 rows affected (0.39 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [my_test]> DESCRIBE t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.08 sec)

MariaDB [my_test]> DESCRIBE t2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
| score | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.02 sec)

MariaDB [my_test]> ALTER TABLE t2 ADD PRIMARY KEY (id);
Query OK, 0 rows affected (0.28 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [my_test]> INSERT INTO t1 VALUES (20160102, 'Tom', 30);
Query OK, 1 row affected (0.04 sec)

MariaDB [my_test]> INSERT INTO t2 VALUES (20160102, 100);
Query OK, 1 row affected (0.04 sec)

MariaDB [my_test]> INSERT INTO t2 VALUES (20160101, 90);
Query OK, 1 row affected (0.05 sec)

MariaDB [my_test]> SELECT * FROM t1;
+----------+---------+------+
| id       | name    | age  |
+----------+---------+------+
| 20160101 | Johnson |   20 |
| 20160102 | Tom     |   30 |
+----------+---------+------+
2 rows in set (0.00 sec)

MariaDB [my_test]> SELECT * FROM t2;
+----------+-------+
| id       | score |
+----------+-------+
| 20160101 |    90 |
| 20160102 |  100 |
+----------+-------+
2 rows in set (0.00 sec)

MariaDB [my_test]> CREATE VIEW view_all_data AS
    -> SELECT t1.id, t1.name, t1.age, t2.score
    -> FROM (t1 INNER JOIN t2 ON t1.id = t2.id);
Query OK, 0 rows affected (0.06 sec)

MariaDB [my_test]> SELECT * FROM view_all_data;
+----------+---------+------+-------+
| id       | name    | age  | score |
+----------+---------+------+-------+
| 20160101 | Johnson |   20 |    90 |
| 20160102 | Tom     |   30 |  100 |
+----------+---------+------+-------+
2 rows in set (0.07 sec)

MariaDB [my_test]> SELECT t1.id, t1.name, t1.age, t2.score FROM t1, t2 WHERE t1.id = t2.id;
+----------+---------+------+-------+
| id       | name    | age  | score |
+----------+---------+------+-------+
| 20160101 | Johnson |   20 |    90 |
| 20160102 | Tom     |   30 |   100 |
+----------+---------+------+-------+
2 rows in set (0.00 sec)

MariaDB [my_test]>