mysql取多个表的数据,MySql查询多个表的数据

深碍√TFBOYSˉ_ 2023-01-16 09:48 187阅读 0赞

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

\+-------+-------------+------+-----+---------+-------+

\+-------+-------------+------+-----+---------+-------+

| id | int(11) | NO | PRI | NULL | |

| age | int(11) | YES | | NULL | |

\+-------+-------------+------+-----+---------+-------+

3 rows in set (0.08 sec)

MariaDB \[my\_test\]> DESCRIBE t2;

\+-------+---------+------+-----+---------+-------+

\+-------+---------+------+-----+---------+-------+

| 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\]>

mysql取多个表的数据,MySql查询多个表的数据

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 mysql多个表的更新操作

相关 Mysql 使用存储过程合并多个表数据

相关 mysql清空多个表的数据

相关 mysql如何选择多个表,选择*从多个表。的MySQL

相关 mysql取多个表的数据,MySql查询多个表的数据

相关 mysql多表查询实验_mysql学习-mysql多表查询

相关如何同时查询多个表中的相关数据？

相关 MySQL：29 表空间以及划分多个数据页的数据区

相关 mysql多个程序同时一张表,Mysql：多个表还是一个大表？

相关 mysql 查询去除表中（多个条件确定的一个数据）