mysql查询连续三天大于100 mysql 连续三天大于100

MySQL学习笔记

• 33. 查询练习-多表查询
• 34. 查询练习-in表示或者关系
• 35. 查询练习-where条件查询
• 36. 查询练习-子查询
• 37. 查询练习-union和notin的使用
• 38. 查询练习-any表示至少一个-desc降序
• 39. 查询练习-all表示所有
• 40. 查询练习-as取别名-union求并集
• 41. 查询练习-union求并集
• 42. 查询练习-复制表数据做条件查询
• 43. 查询练习-子查询
• 44. 查询练习-条件加分组筛选
• 45. 查询练习-notlike模糊查询取反
• 46. 查询练习-year函数与now函数
• 47. 查询练习-max与min函数
• 48. 查询练习-多字段排序
• 49. 查询练习-子查询
• 50. 查询练习-max函数与子查询
• 51. 查询练习-子查询
• 52. 查询练习-子查询
• 53. 查询练习-子查询

22. 查询选修某课程的同学人数多于5人的教师姓名
    select tname from teacher where tno in (select tno from course where cno in (select cno from score group by cno having count(*) > 5));
23. 查询95033班和95031班全体学生的记录。
    insert into student values('110', '张飞', '男', '1974-06-03', '95038' );
24. 查询存在 有85分以上成绩的课程Cno。
    select cno, degree from score where degree > 85;
25. 查询出“计算机系“教师所教课程的成绩表。
    select * from score where cno in (select cno from course where tno in (select tno from teacher where depart = '计算机系'));
26. 查询“计算机系”与“电子工程系“不同职称的教师的tname和prof。
    mysql> select * from teacher where depart = '计算机系' and prof not in ( select prof from teacher where depart = '电子工程系') union select * from teacher where depart = '电子工程系' and prof not in ( select prof from teacher where depart = '计算机系')
27. 查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree，并按Degree从高到低次序排序。
    select cno, sno, degree from score where cno = '3-105' and degree > any(select degree from score where cno = '3-245') order by degree desc;
28. 查询选修编号为“3-105"且成绩高于选修编号为“3-245"课程的同学的Cno、Sno和Degree。
    select * from score where cno = '3-105' and degree > all(select degree from score where cno = '3-245');
29. 查询所有教师和同学的name、sex和birthday。
    select tname as name, tsex as sex, tbirthday as birthday from teacher union select sname, sex, sbirthday from student;
30. 查询所有“女"教师和“女”同学的name、sex和birthday。
    select tname as name, tsex as sex, tbirthday as birthday from teacher where tsex = '女' union select sname as name, sex, sbirthday as birthday from student where sex = '女';
31. 查询成绩比该课程平均成绩低的同学的成绩表。
    select * from score a where degree < (select avg(degree) from score b where a.cno = b.cno);
32. 查询所有任课教师的Tname和Depart。
    select tname, depart from teacher where tno in (select tno from course);
33. 查询至少有2名男生的班号。
    select class from student where sex ='男' group by class having count(*) > 1;
34. 查询student表中不姓“王”的同学记录。
    select * from student where sname not like '王%';
35. 查询student表中每个学生的姓名和年龄
    select sname, year( now() ) - year(sbirthday) as '年龄' from student;
36. 查询student表中最大和最小的sbirthday日期值。
    select max(sbirthday) as '最大', min(sbirthday) as '最小' from student;
37. 以班号和年龄从大到小的顺序查询student表中的全部记录。
    select * from student order by class desc, sbirthday;
38. 查询“男”教师及其所上的课程。
    select * from course where tno in (select tno from teacher where tsex = '男' );
39. 查询最高分同学的sno、cno和degree列。
    select sno, cno, degree from score where degree in (select max(degree) from score);
40. 查询和“李军”同性别的所有同学的Sname。
    select sname from student where sex = (select sex from student where sname = '李军' );
41. 查询和“李军”同性别并同班的同学Sname。
    select sname from student where sex = (select sex from student where sname = '李军') and class = ( select class from student where sname = '李军' ) ;
42. 查询所有选修“计算机导论"课程的“男”同学的成绩表。
    select * from score where cno = (select cno from course where cname = '计算机导论') and sno in (select sno from student where sex = '男');

33. 查询练习-多表查询

22、查询选修某课程的同学人数多于5人的教师姓名

insert into score values('101', '3-105', '90');
insert into score values('102', '3-105', '91');
insert into score values('104', '3-105', '89');

mysql> select * from teacher;
mysql> select * from course where cno in (select cno from score group by cno having count(*) > 5);

mysql> select tname from teacher where tno in (select tno  from course where cno in (select cno from score group by cno having count(*) > 5));
+--------+
| tname  |
+--------+
| 王萍   |
+--------+
1 row in set (0.00 sec)

34. 查询练习-in表示或者关系

23、查询95033班和95031班全体学生的记录。

mysql> insert into student values('110', '张飞', '男', '1974-06-03', '95038' );

mysql> select * from student where class in ('95031', '95033');
+-----+-----------+------+---------------------+-------+
| sno | sname     | sex  | sbirthday           | class |
+-----+-----------+------+---------------------+-------+
| 101 | 曾华      | 男   | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明      | 男   | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽      | 女   | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军      | 男   | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳      | 女   | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆军      | 男   | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛    | 男   | 1976-02-20 00:00:00 | 95033 |
| 108 | 张全蛋    | 男   | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱    | 男   | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+------+---------------------+-------+
9 rows in set (0.00 sec)

35. 查询练习-where条件查询

24、查询存在 有85分以上成绩的课程Cno。

mysql> select * from score where degree > 85;


mysql> select cno, degree from score where degree > 85;
+-------+--------+
| cno   | degree |
+-------+--------+
| 3-105 |     90 |
| 3-105 |     91 |
| 3-105 |     92 |
| 3-245 |     86 |
| 3-105 |     89 |
| 3-105 |     88 |
+-------+--------+
6 rows in set (0.00 sec)

36. 查询练习-子查询

25、查询出“计算机系“教师所教课程的成绩表。

mysql> select * from teacher where depart = '计算机系';
mysql> select * from course where tno in (select tno  from teacher where depart = '计算机系');

mysql> select * from score where cno in (select cno from course where tno in (select tno  from teacher where depart = '计算机系'));
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-245 |     86 |
| 105 | 3-245 |     75 |
| 109 | 3-245 |     68 |
| 101 | 3-105 |     90 |
| 102 | 3-105 |     91 |
| 103 | 3-105 |     92 |
| 104 | 3-105 |     89 |
| 105 | 3-105 |     88 |
| 109 | 3-105 |     76 |
+-----+-------+--------+
9 rows in set (0.00 sec)

37. 查询练习-union和notin的使用

26、查询“计算机系”与“电子工程系“不同职称的教师的tname和prof。

mysql> select * from teacher where depart = '计算机系' and prof not in ( select prof  from teacher where depart = '电子工程系');
+-----+--------+------+---------------------+-----------+--------------+
| tno | tname  | tsex | tbirthday           | prof      | depart       |
+-----+--------+------+---------------------+-----------+--------------+
| 804 | 李诚   | 男   | 1958-12-02 00:00:00 | 副教授    | 计算机系     |
+-----+--------+------+---------------------+-----------+--------------+
1 row in set (0.01 sec)

mysql> select * from teacher where depart = '电子工程系' and prof not in ( select prof  from teacher where depart = '计算机系');
+-----+--------+------+---------------------+--------+-----------------+
| tno | tname  | tsex | tbirthday           | prof   | depart          |
+-----+--------+------+---------------------+--------+-----------------+
| 856 | 张旭   | 男   | 1969-03-12 00:00:00 | 讲师   | 电子工程系      |
+-----+--------+------+---------------------+--------+-----------------+
1 row in set (0.00 sec)

mysql> select * from teacher where depart = '计算机系' and prof not in ( select prof  from teacher where depart = '电子工程系')
    -> union
    ->  select * from teacher where depart = '电子工程系' and prof not in ( select prof  from teacher where depart = '计算机系')
    -> ;
+-----+--------+------+---------------------+-----------+-----------------+
| tno | tname  | tsex | tbirthday           | prof      | depart          |
+-----+--------+------+---------------------+-----------+-----------------+
| 804 | 李诚   | 男   | 1958-12-02 00:00:00 | 副教授    | 计算机系        |
| 856 | 张旭   | 男   | 1969-03-12 00:00:00 | 讲师      | 电子工程系      |
+-----+--------+------+---------------------+-----------+-----------------+
2 rows in set (0.00 sec)

38. 查询练习-any表示至少一个-desc降序

27、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree，并按Degree从高到低次序排序。
$\color{orange}{至少?大于其中至少一个}$
用$\color{orange}{any}$表示

mysql> select *  from score where cno = '3-105';
mysql> select cno, sno, degree from score where cno = '3-105' and degree >  any(select degree  from score where cno = '3-245') order by degree desc;

mysql> select cno, sno, degree from score
    -> where cno = '3-105'
    -> and degree >  any(select degree  from score where cno = '3-245')
    -> order by degree desc;
+-------+-----+--------+
| cno   | sno | degree |
+-------+-----+--------+
| 3-105 | 103 |     92 |
| 3-105 | 102 |     91 |
| 3-105 | 101 |     90 |
| 3-105 | 104 |     89 |
| 3-105 | 105 |     88 |
| 3-105 | 109 |     76 |
+-------+-----+--------+
6 rows in set (0.00 sec)

39. 查询练习-all表示所有

28、查询选修编号为“3-105"且成绩高于选修编号为“3-245"课程的同学的Cno、Sno和Degree。

mysql> select * from score where cno = '3-105';
mysql> select * from score where cno = '3-245';

mysql> select * from score 
 	-> where cno = '3-105' 
	-> and degree > all(select degree  from score where cno = '3-245');
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 101 | 3-105 |     90 |
| 102 | 3-105 |     91 |
| 103 | 3-105 |     92 |
| 104 | 3-105 |     89 |
| 105 | 3-105 |     88 |
+-----+-------+--------+
5 rows in set (0.00 sec)

40. 查询练习-as取别名-union求并集

29、查询所有教师和同学的name、sex和birthday。
$\color{orange}{别名}$
用$\color{orange}{as}$表示

此处union后的名字可不别名，会默认和第一排一对一

mysql> select tname as name, tsex as sex, tbirthday as birthday from teacher
    -> union
    -> select sname, sex, sbirthday from student;
+-----------+------+---------------------+
| name      | sex  | birthday            |
+-----------+------+---------------------+
| 李诚      | 男   | 1958-12-02 00:00:00 |
| 王萍      | 女   | 1972-05-05 00:00:00 |
| 刘冰      | 女   | 1977-08-14 00:00:00 |
| 张旭      | 男   | 1969-03-12 00:00:00 |
| 曾华      | 男   | 1977-09-01 00:00:00 |
| 匡明      | 男   | 1975-10-02 00:00:00 |
| 王丽      | 女   | 1976-01-23 00:00:00 |
| 李军      | 男   | 1976-02-20 00:00:00 |
| 王芳      | 女   | 1975-02-10 00:00:00 |
| 陆军      | 男   | 1974-06-03 00:00:00 |
| 王尼玛    | 男   | 1976-02-20 00:00:00 |
| 张全蛋    | 男   | 1975-02-10 00:00:00 |
| 赵铁柱    | 男   | 1974-06-03 00:00:00 |
| 张飞      | 男   | 1974-06-03 00:00:00 |
+-----------+------+---------------------+
14 rows in set (0.00 sec)

41. 查询练习-union求并集

30、查询所有“女"教师和“女”同学的name、sex和birthday。

mysql> select tname as name, tsex as sex, tbirthday as birthday from teacher where tsex = '女'
    -> union
    -> select sname as name, sex, sbirthday as birthday from student  where sex = '女';
+--------+------+---------------------+
| name   | sex  | birthday            |
+--------+------+---------------------+
| 王萍   | 女   | 1972-05-05 00:00:00 |
| 刘冰   | 女   | 1977-08-14 00:00:00 |
| 王丽   | 女   | 1976-01-23 00:00:00 |
| 王芳   | 女   | 1975-02-10 00:00:00 |
+--------+------+---------------------+
4 rows in set (0.00 sec)

42. 查询练习-复制表数据做条件查询

31. 查询成绩比该课程平均成绩低的同学的成绩表。
    同表同字段不能同时比较，需复制两张表a,b来比较！

mysql> select * from score a  where degree < (select avg(degree) from score b where a.cno = b.cno);
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 105 | 3-245 |     75 |
| 105 | 6-166 |     79 |
| 109 | 3-105 |     76 |
| 109 | 3-245 |     68 |
| 109 | 6-166 |     81 |
+-----+-------+--------+
5 rows in set (0.00 sec)

43. 查询练习-子查询

32、查询所有任课教师的Tname和Depart。

– 课程表中安排了课程

mysql> select * from teacher;
mysql> select * from course;

mysql> select tname, depart from teacher where tno in (select tno from course);
+--------+-----------------+
| tname  | depart          |
+--------+-----------------+
| 李诚   | 计算机系        |
| 王萍   | 计算机系        |
| 刘冰   | 电子工程系      |
| 张旭   | 电子工程系      |
+--------+-----------------+
4 rows in set (0.00 sec)

44. 查询练习-条件加分组筛选

33. 查询至少有2名男生的班号。
    $\color{orange}{having在分完组后运行，可以过滤组，是高配版where。}$

mysql> select * from student;
+-----+-----------+------+---------------------+-------+
| sno | sname     | sex  | sbirthday           | class |
+-----+-----------+------+---------------------+-------+
| 101 | 曾华      | 男   | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明      | 男   | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽      | 女   | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军      | 男   | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳      | 女   | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆军      | 男   | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛    | 男   | 1976-02-20 00:00:00 | 95033 |
| 108 | 张全蛋    | 男   | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱    | 男   | 1974-06-03 00:00:00 | 95031 |
| 110 | 张飞      | 男   | 1974-06-03 00:00:00 | 95038 |
+-----+-----------+------+---------------------+-------+
10 rows in set (0.00 sec)

mysql> select class from student where sex ='男' group by class having count(*) > 1;
+-------+
| class |
+-------+
| 95031 |
| 95033 |
+-------+
2 rows in set (0.00 sec)

45. 查询练习-notlike模糊查询取反

34. 查询student表中不姓“王”的同学记录。

mysql> select * from student;

mysql> select * from student where sname not like '王%';
+-----+-----------+------+---------------------+-------+
| sno | sname     | sex  | sbirthday           | class |
+-----+-----------+------+---------------------+-------+
| 101 | 曾华      | 男   | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明      | 男   | 1975-10-02 00:00:00 | 95031 |
| 104 | 李军      | 男   | 1976-02-20 00:00:00 | 95033 |
| 106 | 陆军      | 男   | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋    | 男   | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱    | 男   | 1974-06-03 00:00:00 | 95031 |
| 110 | 张飞      | 男   | 1974-06-03 00:00:00 | 95038 |
+-----+-----------+------+---------------------+-------+
7 rows in set (0.01 sec)

46. 查询练习-year函数与now函数

35. 查询student表中每个学生的姓名和年龄。
    $\color{orange}{年龄 = 当前年份 - 出生年份}$

mysql> select year( now() );
+---------------+
| year( now() ) |
+---------------+
|          2021 |
+---------------+
1 row in set (0.00 sec)

mysql> select year( sbirthday ) from student;
+-------------------+
| year( sbirthday ) |
+-------------------+
|              1977 |
|              1975 |
|              1976 |
|              1976 |
|              1975 |
|              1974 |
|              1976 |
|              1975 |
|              1974 |
|              1974 |
+-------------------+
10 rows in set (0.00 sec)

mysql> select sname, year(now())-year(sbirthday) as age   from student;
+-----------+------+
| sname     | age  |
+-----------+------+
| 曾华      |   44 |
| 匡明      |   46 |
| 王丽      |   45 |
| 李军      |   45 |
| 王芳      |   46 |
| 陆军      |   47 |
| 王尼玛    |   45 |
| 张全蛋    |   46 |
| 赵铁柱    |   47 |
| 张飞      |   47 |
+-----------+------+
10 rows in set (0.00 sec)

47. 查询练习-max与min函数

36. 查询student表中最大和最小的sbirthday日期值。

mysql> select * from student;

mysql> select max(sbirthday) as '最大', min(sbirthday) as '最小'  from student;
+---------------------+---------------------+
| 最大                | 最小                |
+---------------------+---------------------+
| 1977-09-01 00:00:00 | 1974-06-03 00:00:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)

48. 查询练习-多字段排序

37. 以班号和年龄从大到小的顺序查询student表中的全部记录。

mysql> select * from student order by class desc, sbirthday;
+-----+-----------+------+---------------------+-------+
| sno | sname     | sex  | sbirthday           | class |
+-----+-----------+------+---------------------+-------+
| 110 | 张飞      | 男   | 1974-06-03 00:00:00 | 95038 |
| 103 | 王丽      | 女   | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军      | 男   | 1976-02-20 00:00:00 | 95033 |
| 107 | 王尼玛    | 男   | 1976-02-20 00:00:00 | 95033 |
| 101 | 曾华      | 男   | 1977-09-01 00:00:00 | 95033 |
| 106 | 陆军      | 男   | 1974-06-03 00:00:00 | 95031 |
| 109 | 赵铁柱    | 男   | 1974-06-03 00:00:00 | 95031 |
| 105 | 王芳      | 女   | 1975-02-10 00:00:00 | 95031 |
| 108 | 张全蛋    | 男   | 1975-02-10 00:00:00 | 95031 |
| 102 | 匡明      | 男   | 1975-10-02 00:00:00 | 95031 |
+-----+-----------+------+---------------------+-------+
10 rows in set (0.00 sec)

49. 查询练习-子查询

38. 查询“男”教师及其所上的课程。

mysql> select *  from teacher where tsex = '男';
+-----+--------+------+---------------------+-----------+-----------------+
| tno | tname  | tsex | tbirthday           | prof      | depart          |
+-----+--------+------+---------------------+-----------+-----------------+
| 804 | 李诚   | 男   | 1958-12-02 00:00:00 | 副教授    | 计算机系        |
| 856 | 张旭   | 男   | 1969-03-12 00:00:00 | 讲师      | 电子工程系      |
+-----+--------+------+---------------------+-----------+-----------------+
2 rows in set (0.00 sec)

mysql> select *  from course where tno in (select tno from teacher where tsex = '男' );
+-------+--------------+-----+
| cno   | cname        | tno |
+-------+--------------+-----+
| 3-245 | 操作系统     | 804 |
| 6-166 | 数字电路     | 856 |
+-------+--------------+-----+
2 rows in set (0.00 sec)

50. 查询练习-max函数与子查询

39. 查询最高分同学的sno、cno和degree列。

mysql> select max(degree) from score;
+-------------+
| max(degree) |
+-------------+
|          92 |
+-------------+
1 row in set (0.00 sec)

mysql> select sno, cno, degree from score where degree in (select max(degree) from score);
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
+-----+-------+--------+
1 row in set (0.00 sec)

51. 查询练习-子查询

40. 查询和“李军”同性别的所有同学的Sname。

mysql> select sex from student where sname = '李军';
+------+
| sex  |
+------+
| 男   |
+------+
1 row in set (0.00 sec)

mysql> select sname from student where sex = (select sex from student where sname = '李军' );
+-----------+
| sname     |
+-----------+
| 曾华      |
| 匡明      |
| 李军      |
| 陆军      |
| 王尼玛    |
| 张全蛋    |
| 赵铁柱    |
| 张飞      |
+-----------+
8 rows in set (0.00 sec)

52. 查询练习-子查询

41、查询和“李军”同性别并同班的同学Sname。

mysql> select sex, class from student where sname = '李军';
+------+-------+
| sex  | class |
+------+-------+
| 男   | 95033 |
+------+-------+
1 row in set (0.00 sec)

mysql> select sname from student 
where sex = (select sex from student where sname = '李军') 
and class = ( select class from student where sname = '李军' ) ;
+-----------+
| sname     |
+-----------+
| 曾华      |
| 李军      |
| 王尼玛    |
+-----------+
3 rows in set (0.01 sec)

53. 查询练习-子查询

42. 查询所有选修“计算机导论"课程的“男”同学的成绩表。

mysql> select * from course where cname = '计算机导论';
+-------+-----------------+-----+
| cno   | cname           | tno |
+-------+-----------------+-----+
| 3-105 | 计算机导论      | 825 |
+-------+-----------------+-----+
1 row in set (0.00 sec)

mysql> select * from score
    -> where cno = (select cno from course where cname = '计算机导论')
    -> and sno in (select sno from student where sex = '男');
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 101 | 3-105 |     90 |
| 102 | 3-105 |     91 |
| 104 | 3-105 |     89 |
| 109 | 3-105 |     76 |
+-----+-------+--------+
4 rows in set (0.00 sec)