一、子查询
查询和“C”同学同性别的所有同学的sname:
mysql> select ssex from student where sname='C';
+-------+
| ssex |
+-------+
| woman |
+-------+
1 row in set (0.01 sec)
mysql> select sname,ssex from student
-> where ssex=(select ssex from student where sname='C');
+-------+-------+
| sname | ssex |
+-------+-------+
| C | woman |
| C++ | woman |
| C# | woman |
| JS | woman |
+-------+-------+
4 rows in set (0.00 sec)
二、子查询
查询和“C”同学同性别并同班的所有同学的sname:
mysql> select sname,ssex,class from student
-> where ssex=(select ssex from student where sname='C')
-> and class=(select class from student where sname='C');
+-------+-------+-------+
| sname | ssex | class |
+-------+-------+-------+
| C | woman | 95034 |
| C# | woman | 95034 |
+-------+-------+-------+
三、子查询
查询所有选修csdaolun课程的男同学的成绩表:
mysql> select * from student where ssex = 'man';
+-----+--------+---------------------+-------+------+
| sno | sname | sbirthday | class | ssex |
+-----+--------+---------------------+-------+------+
| 100 | Java | 1977-09-01 00:00:00 | 95033 | man |
| 104 | Python | 1977-10-11 00:00:00 | 95033 | man |
+-----+--------+---------------------+-------+------+
2 rows in set (0.00 sec)
mysql> select * from course where cname='csdaolun';
+-------+----------+-----+
| cno | cname | tno |
+-------+----------+-----+
| 3-105 | csdaolun | 825 |
+-------+----------+-----+
1 row in set (0.00 sec)
mysql> select * from score
-> where cno=(select cno from course where cname='csdaolun')
-> and sno in (select sno from student where ssex = 'man');
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 100 | 3-105 | 75 |
| 104 | 3-105 | 59 |
+-----+-------+--------+
SQL语句高级(十)——子查询
最新推荐文章于 2023-08-24 22:03:23 发布