如何从 MySQL 中仅当该表中的列值与另一个表的列值匹配时才选择数据?
mysqlmysqli database更新于 2024/2/9 14:09:00
为此，您可以将子查询与 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)
