可以直接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]>