mysql取多个表的数据,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]>