MySQL 如何仅从一张表中选择数据,其中该表的列值与另一张表的列值匹配
为此,您可以使用子查询与 EXISTS。先创建一个表 –
mysql> create table DemoTable1
-> (
-> Id int,
-> SubjectName varchar(20)
-> );
Query OK, 0 rows affected (0.58 sec)
使用insert命令在表中插入一些记录 –
mysql> insert into DemoTable1 values(111,'MySQL');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable1 values(112,'MongoDB');
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable1 values(113,'Java');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable1 values(114,'C');
Query OK, 1 row affected (0.27 sec)
mysql> insert into DemoTable1 values(115,'MySQL');
Query OK, 1 row affected (0.23 sec)
使用select语句显示表中的所有记录 –
mysql> select * from DemoTable1;
这将产生以下输出 –
+------+-------------+
| Id | SubjectName |
+------+-------------+
| 111 | MySQL |
| 112 | MongoDB |
| 113 | Java |
| 114 | C |
| 115 | MySQL |
+------+-------------+
5 rows in set (0.00 sec)
以下是查询以创建第二个表 –
mysql> create table DemoTable2
-> (
-> FirstName varchar(20),
-> StudentSubject varchar(20)
-> );
Query OK, 0 rows affected (0.73 sec)
使用 insert 命令在表中插入一些记录 –
mysql> insert into DemoTable2 values('Chris','MySQL');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable2 values('Bob','MySQL');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable2 values('Sam','MySQL');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable2 values('Carol','C');
Query OK, 1 row affected (0.19 sec)
使用select语句显示表中的所有记录 –
mysql> select * from DemoTable2;
这将产生以下输出 –
+-----------+----------------+
| FirstName | StudentSubject |
+-----------+----------------+
| Chris | MySQL |
| Bob | MySQL |
| Sam | MySQL |
| Carol | C |
+-----------+----------------+
4 rows in set (0.00 sec)
以下是选择仅从一张表中选择数据的查询,其中该表的列值与另一张表的列值匹配 –
mysql> select Id from DemoTable1
-> where exists
-> (
-> select 1 from DemoTable2
-> where SubjectName=StudentSubject
-> );
这将产生以下输出 –
+------+
| Id |
+------+
| 111 |
| 114 |
| 115 |
+------+
3 rows in set (0.00 sec)
阅读更多:MySQL 教程