一、复制表数据做条件查询
mysql> select cno,avg(degree) from score group by cno;
+--------+-------------+
| cno | avg(degree) |
+--------+-------------+
| 3-105 | 71.6667 |
| 3-245 | 89.2000 |
| 6-1166 | 74.0000 |
+--------+-------------+
3 rows in set (0.21 sec)
mysql> select * from score;
a表 b表
+-----+--------+--------+ +-----+--------+--------+
| sno | cno | degree | | sno | cno | degree |
+-----+--------+--------+ +-----+--------+--------+
| 100 | 3-105 | 75 | | 100 | 3-105 | 75 |
| 100 | 3-245 | 75 | | 100 | 3-245 | 75 |
| 101 | 6-1166 | 59 | | 101 | 6-1166 | 59 |
| 102 | 3-245 | 95 | | 102 | 3-245 | 95 |
| 103 | 3-105 | 81 | | 103 | 3-105 | 81 |
| 103 | 3-245 | 89 | | 103 | 3-245 | 89 |
| 103 | 6-1166 | 59 | | 103 | 6-1166 | 59 |
| 104 | 3-105 | 59 | | 104 | 3-105 | 59 |
| 104 | 3-245 | 98 | | 104 | 3-245 | 98 |
| 104 | 6-1166 | 89 | | 104 | 6-1166 | 89 |
| 105 | 3-245 | 89 | | 105 | 3-245 | 89 |
| 105 | 6-1166 | 89 | | 105 | 6-1166 | 89 |
+-----+--------+--------+ +-----+--------+--------+
12 rows in set (0.00 sec)
mysql> select * from score a --这里的a表和b表都是score表!!!!
-> where degree < (select avg(degree) from score b where a.cno = b.cno);
+-----+--------+--------+
| sno | cno | degree |
+-----+--------+--------+
| 100 | 3-245 | 75 |
| 101 | 6-1166 | 59 |
| 103 | 3-245 | 89 |
| 103 | 6-1166 | 59 |
| 104 | 3-105 | 59 |
| 105 | 3-245 | 89 |
+-----+--------+--------+
二、查询所有任课教师的tname和depart。
即课程表中要有对应课程!
mysql> select * from teacher;
+-----+-------+-------+---------------------+------------+--------+
| tno | tname | tsex | tbirthday | prof | depart |
+-----+-------+-------+---------------------+------------+--------+
| 804 | aaa | man | 1986-12-10 00:00:00 | fujiaoshou | cs |
| 811 | ddd | man | 1984-05-05 00:00:00 | zhujiao | dianzi |
| 825 | ccc | man | 1966-10-04 00:00:00 | zhujiao | cs |
| 856 | bbb | woman | 1945-02-09 00:00:00 | jiaoshou | dianzi |
+-----+-------+-------+---------------------+------------+--------+
4 rows in set (0.00 sec)
mysql> select * from course;
+--------+----------+-----+
| cno | cname | tno |
+--------+----------+-----+
| 3-105 | csdaolun | 825 |
| 3-245 | os | 856 |
| 6-1166 | math | 804 |
+--------+----------+-----+
3 rows in set (0.00 sec)
mysql> select tname,depart from teacher
-> where tno in (select tno from course); --就是经典的子查询!!!
+-------+--------+
| tname | depart |
+-------+--------+
| aaa | cs |
| ccc | cs |
| bbb | dianzi |
+-------+--------+
3 rows in set (0.03 sec)
三、条件+分组筛选!
查询至少有两名男生的班级编号:
mysql> select * from student;
+-----+--------+---------------------+-------+-------+
| sno | sname | sbirthday | class | ssex |
+-----+--------+---------------------+-------+-------+
| 100 | Java | 1977-09-01 00:00:00 | 95033 | man |
| 101 | C | 1975-08-21 00:00:00 | 95034 | woman |
| 102 | C++ | 1976-02-11 00:00:00 | 95035 | woman |
| 103 | C# | 1974-12-01 00:00:00 | 95034 | woman |
| 104 | Python | 1977-10-11 00:00:00 | 95033 | man |
| 105 | JS | 1974-11-11 00:00:00 | 95033 | woman |
+-----+--------+---------------------+-------+-------+
6 rows in set (0.00 sec)
mysql> select class from student where ssex='man'
-> group by class having count(*)>1; --以班级分组!注意这个写法!!!
+-------+
| class |
+-------+
| 95033 |
+-------+
1 row in set (0.00 sec)
mysql> select class from student where ssex='woman'
-> group by class having count(*)>1;
+-------+
| class |
+-------+
| 95034 |
+-------+
1 row in set (0.00 sec)
SQL语句高级(七)——复制表a、b
最新推荐文章于 2024-08-15 08:00:00 发布