数据库数据查询3

最新推荐文章于 2022-04-10 10:15:03 发布

花要开了

最新推荐文章于 2022-04-10 10:15:03 发布

阅读量342

点赞数

分类专栏：笔记文章标签： mysql

本文链接：https://blog.csdn.net/weixin_43393776/article/details/107889550

版权

笔记专栏收录该内容

45 篇文章 0 订阅

订阅专栏

27、查询选修编号为“3-105”课程且成绩至少高于选修编号为“3-245”的同学cno、sno和degree，并按照degree从高到低次序排序。

//any代表任一
ysql> select * from score where cno 
= '3-105'and degree >any(select degree 
from score
where cno = '3-245') order by 
degree desc;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 101 | 3-105 |     99 |
| 103 | 3-105 |     92 |
| 102 | 3-105 |     91 |
| 104 | 3-105 |     89 |
| 105 | 3-105 |     88 |
| 109 | 3-105 |     76 |
+-----+-------+--------+

28、查询选修编号为“3-105”课程且成绩高于选修编号为“3-245”的同学cno、sno和degree，并按照degree从高到低次序排序。

//all 全选
mysql> select * from score where cno 
= '3-105'and degree >all(select degree
 from score
where cno = '3-245') order by degree 
desc;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 101 | 3-105 |     99 |
| 103 | 3-105 |     92 |
| 102 | 3-105 |     91 |
| 104 | 3-105 |     89 |
| 105 | 3-105 |     88 |
+-----+-------+--------+

29、查询所有教师和同学的name、sex和birthday。

mysql> select tname as name,tsex as 
sex,tbirthday as birthday from teacher 
union select sname,ssex,sbirthday from 
student;
+--------------+-----+---------------------+
| name         | sex | birthday            |
+--------------+-----+---------------------+
| licheng      | nan | 1958-12-02 00:00:00 |
| wangping     | nv  | 1972-05-05 00:00:00 |
| liubing      | nv  | 1977-08-14 00:00:00 |
| zhangxu      | nan | 1969-03-12 00:00:00 |
| zenghua      | nan | 1977-09-01 00:00:00 |
| kuangming    | nan | 1975-10-02 00:00:00 |
| wangli       | nv  | 1976-01-23 00:00:00 |
| lijun        | nan | 1976-02-20 00:00:00 |
| wangfang     | nv  | 1975-02-10 00:00:00 |
| lujun        | nan | 1974-06-03 00:00:00 |
| wangnima     | nan | 1976-02-20 00:00:00 |
| zhangquandan | nan | 1975-02-10 00:00:00 |
| zhaotiezhu   | nan | 1974-06-03 00:00:00 |
| zhangfei     | nan | 1974-06-03 00:00:00 |
+--------------+-----+---------------------+

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

l> select tname as name,tsex as sex,
tbirthday as birthday from teacher 
where tsex = 'nv' union select sname,
ssex,sbirthday from student where 
ssex = 'nv';
+----------+-----+---------------------+
| name     | sex | birthday            |
+----------+-----+---------------------+
| wangping | nv  | 1972-05-05 00:00:00 |
| liubing  | nv  | 1977-08-14 00:00:00 |
| wangli   | nv  | 1976-01-23 00:00:00 |
| wangfang | nv  | 1975-02-10 00:00:00 |
+----------+-----+---------------------+

31、查询成绩比该课程平均成绩低的同学的成绩表。

> select * from score a where degree < (select avg(degree) from score b where a.cno = b.cno);
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 104 | 3-105 |     89 |
| 105 | 3-105 |     88 |
| 105 | 3-245 |     75 |
| 105 | 6-166 |     79 |
| 109 | 3-105 |     76 |
| 109 | 3-245 |     68 |
| 109 | 6-166 |     81 |
+-----+-------+--------+

32、查询所有任课教师的tname和depart。

mysql> select tname,depart from 
teacher where tno in (select tno from 
course);
+----------+-------------------+
| tname    | depart            |
+----------+-------------------+
| licheng  | jisuanjixi        |
| wangping | jisuanjixi        |
| liubing  | dianzigongchengxi |
| zhangxu  | dianzigongchengxi |
+----------+-------------------+

33、查询至少有2名男生的班号。

mysql> select class from student 
where ssex = 'nan' group by class 
having count(*) > 1; 
+-------+
| class |
+-------+
| 95031 |
| 95033 |
+-------+

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

mysql> select * from student where 
sname not like 'wang%';
+-----+--------------+------+---------------------+-------+
| sno | sname        | ssex | sbirthday           | class |
+-----+--------------+------+---------------------+-------+
| 101 | zenghua      | nan  | 1977-09-01 00:00:00 | 95033 |
| 102 | kuangming    | nan  | 1975-10-02 00:00:00 | 95031 |
| 104 | lijun        | nan  | 1976-02-20 00:00:00 | 95033 |
| 106 | lujun        | nan  | 1974-06-03 00:00:00 | 95031 |
| 108 | zhangquandan | nan  | 1975-02-10 00:00:00 | 95031 |
| 109 | zhaotiezhu   | nan  | 1974-06-03 00:00:00 | 95031 |
| 110 | zhangfei     | nan  | 1974-06-03 00:00:00 | 95038 |
+-----+--------------+------+---------------------+-------+

35、查询student表中每个学生的姓名和年龄。

----当前年份-出生年份=年龄

mysql> select sname,year(now())-
year(sbirthday) as 'nianling' from 
student;
+--------------+----------+
| sname        | nianling |
+--------------+----------+
| zenghua      |       43 |
| kuangming    |       45 |
| wangli       |       44 |
| lijun        |       44 |
| wangfang     |       45 |
| lujun        |       46 |
| wangnima     |       44 |
| zhangquandan |       45 |
| zhaotiezhu   |       46 |
| zhangfei     |       46 |
+--------------+----------+

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

mysql> select max(sbirthday) as 
'zuida',min(sbirthday) as 'zuixiao' 
from student;
+---------------------+---------------------+
| zuida               | zuixiao             |
+---------------------+---------------------+
| 1977-09-01 00:00:00 | 1974-06-03 00:00:00 |
+---------------------+---------------------+

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


mysql> select * from student order
 by class desc,sbirthday;
+-----+--------------+------+---------------------+-------+
| sno | sname        | ssex | sbirthday           | class |
+-----+--------------+------+---------------------+-------+
| 110 | zhangfei     | nan  | 1974-06-03 00:00:00 | 95038 |
| 103 | wangli       | nv   | 1976-01-23 00:00:00 | 95033 |
| 104 | lijun        | nan  | 1976-02-20 00:00:00 | 95033 |
| 107 | wangnima     | nan  | 1976-02-20 00:00:00 | 95033 |
| 101 | zenghua      | nan  | 1977-09-01 00:00:00 | 95033 |
| 106 | lujun        | nan  | 1974-06-03 00:00:00 | 95031 |
| 109 | zhaotiezhu   | nan  | 1974-06-03 00:00:00 | 95031 |
| 105 | wangfang     | nv   | 1975-02-10 00:00:00 | 95031 |
| 108 | zhangquandan | nan  | 1975-02-10 00:00:00 | 95031 |
| 102 | kuangming    | nan  | 1975-10-02 00:00:00 | 95031 |
+-----+--------------+------+---------------------+-------+

38、查询“nan”教师及其所上的课程。

mysql> select * from course where tno in(select 
tno from teacher where tsex='nan');
+-------+--------------+-----+
| cno   | cname        | tno |
+-------+--------------+-----+
| 3-245 | caozuoxitong | 804 |
| 6-166 | shuzidianlu  | 856 |
+-------+--------------+-----+

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

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

mysql> select * from score where 
degree=(select max(degree) from 
score);
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 101 | 3-105 |     99 |
+-----+-------+--------+

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

mysql> select ssex from student where sname='lijun';
+------+
| ssex |
+------+
| nan  |
+------+
1 row in set (0.00 sec)

mysql> select sname from student 
where ssex like(select ssex from 
student where sname='lijun');
+--------------+
| sname        |
+--------------+
| zenghua      |
| kuangming    |
| lijun        |
| lujun        |
| wangnima     |
| zhangquandan |
| zhaotiezhu   |
| zhangfei     |
+--------------+

41、查询和“李军”同性别同班的所有同学的sname。

mysql> select sname from student 
where ssex =(select ssex from 
student where sname='lijun') 
and class = (select class from 
student where sname='lijun');
+----------+
| sname    |
+----------+
| zenghua  |
| lijun    |
| wangnima |
+----------+

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

mysql> select * from score where sno 
in(select sno from student where 
ssex = 'nan') and cno=(select cno 
from course where cname
='jisuanjidaolun');
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 101 | 3-105 |     99 |
| 102 | 3-105 |     91 |
| 104 | 3-105 |     89 |
| 109 | 3-105 |     76 |
+-----+-------+--------+

43、假设使用如下命令建立了一个grade表：

create table grade(
	low int(3),
	upp int(3),
	grade char(1)
);
insert into grade values(90,100,'A');
insert into grade values(80,89,'B');
insert into grade values(70,79,'C');
insert into grade values(60,69,'D');
insert into grade values(0,59,'E');

现查询所有同学的sno、cno和grade。

mysql> select sno,cno,grade from 
score,grade where degree between 
low and upp;
+-----+-------+-------+
| sno | cno   | grade |
+-----+-------+-------+
| 101 | 3-105 | A     |
| 102 | 3-105 | A     |
| 103 | 3-105 | A     |
| 103 | 3-245 | B     |
| 103 | 6-166 | B     |
| 104 | 3-105 | B     |
| 105 | 3-105 | B     |
| 105 | 3-245 | C     |
| 105 | 6-166 | C     |
| 109 | 3-105 | C     |
| 109 | 3-245 | D     |
| 109 | 6-166 | B     |
+-----+-------+-------+