流水账第十三章

MySQL-查询练习

1.创建学生表Student
学号
性别
出生年月日
所在班级
create table student(
  sno varchar(20) primary key,
  sname varchar(20) not null,
  ssex varchar(20) not null,
  sbirth datetime,
  class varchar(20)
);

1.1添加数据
insert into student values('21','张三','男','2000-01-01','3班');
insert into student values('52','李四','女','2001-11-21','1班');
insert into student values('11','王五','男','2000-03-12','1班');
insert into student values('42','赵六','男','1999-03-25','1班');
insert into student values('12','方七','女','2000-09-19','3班');
insert into student values('53','黄八','男','1998-07-10','3班');
insert into student values('23','李华','男','2000-01-01','3班');
insert into student values('12','范桶','男','2003-09-12','1班');
insert into student values('28','悟空','女','2000-07-29','3班');

2.创建教师表Teacher

教师编号
教师姓名
教师性别
出生年月日
职称
所在部门
create table teacher(
  tno varchar(20) primary key,
  tname varchar(20) not null,
  tsex varchar(20) not null,
  tbirth datetime,
  prof varchar(20) not null,
  depart varchar(20) not null
);

2.1添加数据
insert into teacher values('101','王尼玛','男','1992-02-12','辅导员','电子与信息系');
insert into teacher values('121','张全蛋','男','1972-09-22','助教','计算机系');
insert into teacher values('132','赵铁柱','女','1992-10-19','教授','电子工程系');
insert into teacher values('109','李翠花','女','1992-12-31','讲师','音乐系');
insert into teacher values('111','库里','男','1992-02-12','助教','电子与信息系');

3.课程表Course
课程号
课程名称
教师编号
create table Course(
  cno varchar(20) primary key,
  cname varchar(20) not null,
  tno varchar(20) not null,
  foreign key (tno) references teacher(tno)
);

3.1添加数据
insert into Course values('2-109','机器学习','121');
insert into Course values('1-219','钢管舞','109');
insert into Course values('2-301','信号处理','132');
insert into Course values('5-921','神经网络','101');

4.成绩表Score
学号
课程号
成绩
create table score(
  sno varchar(20) not null,
  cno varchar(20) not null,
  degree decimal,
  foreign key(sno) references student(sno),
  foreign key(cno) references course(cno),
  primary key(sno,cno)s
);

4.1添加数据
insert into score values('21','2-109','94');
insert into score values('52','2-109','89');
insert into score values('11','2-109','88');
insert into score values('42','1-219','78');
insert into score values('12','1-219','87');
insert into score values('53','1-219','47');
insert into score values('23','2-301','87');
insert into score values('12','2-301','80');
insert into score values('28','5-921','99');

---查询练习；
1.查询student表中的所有记录。*指所有

mysql> select * from student;
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirth              | class |
+-----+--------+------+---------------------+-------+
| 11  | 王五   | 男   | 2000-03-12 00:00:00 | 1班   |
| 12  | 方七   | 女   | 2000-09-19 00:00:00 | 3班   |
| 21  | 张三   | 男   | 2000-01-01 00:00:00 | 3班   |
| 23  | 李华   | 男   | 2000-01-01 00:00:00 | 3班   |
| 28  | 悟空   | 女   | 2000-07-29 00:00:00 | 3班   |
| 42  | 赵六   | 男   | 1999-03-25 00:00:00 | 1班   |
| 52  | 李四   | 女   | 2001-11-21 00:00:00 | 1班   |
| 53  | 黄八   | 男   | 1998-07-10 00:00:00 | 3班   |
+-----+--------+------+---------------------+-------+
8 rows in set (0.01 sec)

2.查询student表中的所有记录的sname、ssex和class列
mysql> select sname,ssex,class from student;
+--------+------+-------+
| sname  | ssex | class |
+--------+------+-------+
| 王五   | 男   | 1班   |
| 方七   | 女   | 3班   |
| 张三   | 男   | 3班   |
| 李华   | 男   | 3班   |
| 悟空   | 女   | 3班   |
| 赵六   | 男   | 1班   |
| 李四   | 女   | 1班   |
| 黄八   | 男   | 3班   |
+--------+------+-------+
8 rows in set (0.00 sec)

3.查询学生表所有的班级即不重复的class列
mysql> select distinct class from student;
+-------+
| class |
+-------+
| 1班   |
| 3班   |
+-------+
2 rows in set (0.01 sec)

4.查询score表中成绩在80-90之间的记录
mysql> select * from score where degree between 80 and 90;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 11  | 2-109 |     88 |
| 12  | 1-219 |     87 |
| 12  | 2-301 |     80 |
| 23  | 2-301 |     87 |
| 52  | 2-109 |     89 |
+-----+-------+--------+
5 rows in set (0.00 sec)
---等用于
select * from score where degree > 80 and degree < 90;

5.查询score表中的成绩为88，87，94的成绩
mysql> select * from score where degree in(88,87,94);
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 11  | 2-109 |     88 |
| 12  | 1-219 |     87 |
| 21  | 2-109 |     94 |
| 23  | 2-301 |     87 |
+-----+-------+--------+
4 rows in set (0.00 sec)

6.查询student表中‘1班’的同学或者性别为'女'的同学
select * from student where class='1班' or ssex='女';
mysql> select * from student where class='1班' or ssex='女';
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirth              | class |
+-----+--------+------+---------------------+-------+
| 11  | 王五   | 男   | 2000-03-12 00:00:00 | 1班   |
| 12  | 方七   | 女   | 2000-09-19 00:00:00 | 3班   |
| 28  | 悟空   | 女   | 2000-07-29 00:00:00 | 3班   |
| 42  | 赵六   | 男   | 1999-03-25 00:00:00 | 1班   |
| 52  | 李四   | 女   | 2001-11-21 00:00:00 | 1班   |
+-----+--------+------+---------------------+-------+
5 rows in set (0.00 sec)

7.在score表中将成绩进行降序 order顺序  默认为升序，因此asc可以写
--降序desc;
mysql> select * from score order by degree desc;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 28  | 5-921 |     99 |
| 21  | 2-109 |     94 |
| 52  | 2-109 |     89 |
| 11  | 2-109 |     88 |
| 12  | 1-219 |     87 |
| 23  | 2-301 |     87 |
| 12  | 2-301 |     80 |
| 42  | 1-219 |     78 |
| 53  | 1-219 |     47 |
+-----+-------+--------+
9 rows in set (0.00 sec)

--升序asc 默认
mysql> select * from score order by degree;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 53  | 1-219 |     47 |
| 42  | 1-219 |     78 |
| 12  | 2-301 |     80 |
| 12  | 1-219 |     87 |
| 23  | 2-301 |     87 |
| 11  | 2-109 |     88 |
| 52  | 2-109 |     89 |
| 21  | 2-109 |     94 |
| 28  | 5-921 |     99 |
+-----+-------+--------+
9 rows in set (0.00 sec)

8.先将con升序、再按degree降序
mysql> select * from score order by cno asc,degree desc;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 12  | 1-219 |     87 |
| 42  | 1-219 |     78 |
| 53  | 1-219 |     47 |
| 21  | 2-109 |     94 |
| 52  | 2-109 |     89 |
| 11  | 2-109 |     88 |
| 23  | 2-301 |     87 |
| 12  | 2-301 |     80 |
| 28  | 5-921 |     99 |
+-----+-------+--------+
9 rows in set (0.01 sec)

9.统计'1班'的人数
mysql> select count(*) from student where class='1班';
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.01 sec)

--统计不及格人数
mysql> select count(*) from score where degree<60;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

10.查询score表中的最高分学生学号sno和课程号cno。
--先找最高分
mysql> select max(degree) from score;
+-------------+
| max(degree) |
+-------------+
|          99 |
+-------------+
1 row in set (0.00 sec)

--对成绩进行排序
mysql> select sno,cno from score order by degree;
+-----+-------+
| sno | cno   |
+-----+-------+
| 53  | 1-219 |
| 42  | 1-219 |
| 12  | 2-301 |
| 12  | 1-219 |
| 23  | 2-301 |
| 11  | 2-109 |
| 52  | 2-109 |
| 21  | 2-109 |
| 28  | 5-921 |
+-----+-------+
9 rows in set (0.00 sec)

--在找出对应的sno、cno
mysql> select sno,cno from score where degree=(select max(degree) from score);
+-----+-------+
| sno | cno   |
+-----+-------+
| 28  | 5-921 |
+-----+-------+
1 row in set (0.00 sec)

11.找出成绩degree前三名
--limit 第一位数表示从多少开始，最底为0
--      第二位数表示展示多少个。
mysql> select sno,cno,degree from score order by degree desc limit 0,3;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 28  | 5-921 |     99 |
| 21  | 2-109 |     94 |
| 52  | 2-109 |     89 |
+-----+-------+--------+
3 rows in set (0.00 sec)

12.查询每门课程的平均成绩
---查询课程
mysql> select * from course;
+-------+--------------+-----+
| cno   | cname        | tno |
+-------+--------------+-----+
| 1-219 | 钢管舞       | 109 |
| 2-109 | 机器学习     | 121 |
| 2-301 | 信号处理     | 132 |
| 5-921 | 神经网络     | 101 |
+-------+--------------+-----+
4 rows in set (0.01 sec)

--求平均值 单个
mysql> select avg(degree) from score where cno='1-219';
+-------------+
| avg(degree) |
+-------------+
|     70.6667 |
+-------------+
1 row in set (0.00 sec)
---求平均值 组
mysql>  select avg(degree) from score group by cno;
+-------------+
| avg(degree) |
+-------------+
|     70.6667 |
|     90.3333 |
|     83.5000 |
|     99.0000 |
+-------------+
4 rows in set (0.00 sec)
---求平均显示cno
mysql> select cno,avg(degree) from score group by cno;
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 1-219 |     70.6667 |
| 2-109 |     90.3333 |
| 2-301 |     83.5000 |
| 5-921 |     99.0000 |
+-------+-------------+
4 rows in set (0.00 sec)

13.查询score表中至少有3名学生存在并且以1开头的课程的平均分
--先找出至少有3名学生存在的课程 结果有两组
mysql> select cno from score group by cno
    -> having count(cno)>=3;
+-------+
| cno   |
+-------+
| 1-219 |
| 2-109 |
+-------+
2 rows in set (0.00 sec)

--再找出其中1开头的课程and cno like '1%'。%代表通配符 后面随意
mysql> select cno from score group by cno
    -> having count(cno)>=3 and cno like '1%';
+-------+
| cno   |
+-------+
| 1-219 |
+-------+
1 row in set (0.00 sec)

--再求平均分 avg(degree)
mysql> select cno,avg(degree) from score group by cno
    -> having count(cno)>=3 and cno like '1%';
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 1-219 |     70.6667 |
+-------+-------------+
1 row in set (0.01 sec)

--可再观察大于3个学生的数量
mysql> select cno,avg(degree),count(*) from score group by cno
    -> having count(cno)>=3 and cno like '1%';
+-------+-------------+----------+
| cno   | avg(degree) | count(*) |
+-------+-------------+----------+
| 1-219 |     70.6667 |        3 |
+-------+-------------+----------+
1 row in set (0.00 sec)

14.查询分数再大于等于80，小于90的sno列。
14.1方法1
mysql> select sno,degree from score where degree >=80 and degree<90;
+-----+--------+
| sno | degree |
+-----+--------+
| 11  |     88 |
| 12  |     87 |
| 12  |     80 |
| 23  |     87 |
| 52  |     89 |
+-----+--------+
5 rows in set (0.00 sec)

14.2方法2
mysql> select sno,degree from score where degree between 80 and 90;
+-----+--------+
| sno | degree |
+-----+--------+
| 11  |     88 |
| 12  |     87 |
| 12  |     80 |
| 23  |     87 |
| 52  |     89 |
+-----+--------+
5 rows in set (0.00 sec)

15.双表合并 将sname、cno、degree合并
--1查询cno、degree
mysql> select sno,cno,degree from score;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 11  | 2-109 |     88 |
| 12  | 1-219 |     87 |
| 12  | 2-301 |     80 |
| 21  | 2-109 |     94 |
| 23  | 2-301 |     87 |
| 28  | 5-921 |     99 |
| 42  | 1-219 |     78 |
| 52  | 2-109 |     89 |
| 53  | 1-219 |     47 |
+-----+-------+--------+
9 rows in set (0.00 sec)

--sno、sname
mysql> select sno,sname from student;
+-----+--------+
| sno | sname  |
+-----+--------+
| 11  | 王五   |
| 12  | 方七   |
| 21  | 张三   |
| 23  | 李华   |
| 28  | 悟空   |
| 42  | 赵六   |
| 52  | 李四   |
| 53  | 黄八   |
+-----+--------+
8 rows in set (0.00 sec)

-将名字合并
--select sname,cno,degree from student,Score
--where student.sno = score.sno;
mysql> select sname,cno,degree from student,Score
    -> where student.sno = score.sno;
+--------+-------+--------+
| sname  | cno   | degree |
+--------+-------+--------+
| 王五   | 2-109 |     88 |
| 方七   | 1-219 |     87 |
| 方七   | 2-301 |     80 |
| 张三   | 2-109 |     94 |
| 李华   | 2-301 |     87 |
| 悟空   | 5-921 |     99 |
| 赵六   | 1-219 |     78 |
| 李四   | 2-109 |     89 |
| 黄八   | 1-219 |     47 |
+--------+-------+--------+
9 rows in set (0.00 sec)

16.双表合并 sno cname degree
mysql> select cno,cname from course;
+-------+--------------+
| cno   | cname        |
+-------+--------------+
| 1-219 | 钢管舞       |
| 2-109 | 机器学习     |
| 2-301 | 信号处理     |
| 5-921 | 神经网络     |
+-------+--------------+
4 rows in set (0.06 sec)

mysql> select cno,sno,degree from score;
+-------+-----+--------+
| cno   | sno | degree |
+-------+-----+--------+
| 2-109 | 11  |     88 |
| 1-219 | 12  |     87 |
| 2-301 | 12  |     80 |
| 2-109 | 21  |     94 |
| 2-301 | 23  |     87 |
| 5-921 | 28  |     99 |
| 1-219 | 42  |     78 |
| 2-109 | 52  |     89 |
| 1-219 | 53  |     47 |
+-------+-----+--------+
9 rows in set (0.01 sec)

mysql> select sno,cname,degree from course,score
    -> where course.cno=score.cno;
+-----+--------------+--------+
| sno | cname        | degree |
+-----+--------------+--------+
| 12  | 钢管舞       |     87 |
| 42  | 钢管舞       |     78 |
| 53  | 钢管舞       |     47 |
| 11  | 机器学习     |     88 |
| 21  | 机器学习     |     94 |
| 52  | 机器学习     |     89 |
| 12  | 信号处理     |     80 |
| 23  | 信号处理     |     87 |
| 28  | 神经网络     |     99 |
+-----+--------------+--------+
9 rows in set (0.01 sec)

17.三表合并 sname,cname,degree
--1.查询sno，sname
mysql> select sno,sname from student;
+-----+--------+
| sno | sname  |
+-----+--------+
| 11  | 王五   |
| 12  | 方七   |
| 21  | 张三   |
| 23  | 李华   |
| 28  | 悟空   |
| 42  | 赵六   |
| 52  | 李四   |
| 53  | 黄八   |
+-----+--------+
8 rows in set (0.00 sec)
--2.查询cno，cname
mysql> select cno,cname from course;
+-------+--------------+
| cno   | cname        |
+-------+--------------+
| 1-219 | 钢管舞       |
| 2-109 | 机器学习     |
| 2-301 | 信号处理     |
| 5-921 | 神经网络     |
+-------+--------------+
4 rows in set (0.00 sec)
--3.查询sno、cno、degree
mysql> select sno,cno,degree from score;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 11  | 2-109 |     88 |
| 12  | 1-219 |     87 |
| 12  | 2-301 |     80 |
| 21  | 2-109 |     94 |
| 23  | 2-301 |     87 |
| 28  | 5-921 |     99 |
| 42  | 1-219 |     78 |
| 52  | 2-109 |     89 |
| 53  | 1-219 |     47 |
+-----+-------+--------+
9 rows in set (0.00 sec)
--4.将其合并student.sno=score.sno and course.cno=score.cno;
mysql> select sname,cname,degree from student,course,score
where student.sno=score.sno and course.cno=score.cno;
+--------+--------------+--------+
| sname  | cname        | degree |
+--------+--------------+--------+
| 方七   | 钢管舞       |     87 |
| 赵六   | 钢管舞       |     78 |
| 黄八   | 钢管舞       |     47 |
| 王五   | 机器学习     |     88 |
| 张三   | 机器学习     |     94 |
| 李四   | 机器学习     |     89 |
| 方七   | 信号处理     |     80 |
| 李华   | 信号处理     |     87 |
| 悟空   | 神经网络     |     99 |
+--------+--------------+--------+
9 rows in set (0.00 sec)

18.查询1班同学每门课的平均分
select * from student where class='1班';
select cno,avg(degree) from score
where sno in (select sno from student where class='1班')
group by cno;

mysql> select cno,avg(degree) from score
    -> where sno in (select sno from student where class='1班')
    -> group by cno;
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 2-109 |     88.5000 |
| 1-219 |     78.0000 |
+-------+-------------+
2 rows in set (0.01 sec)

19.查询选修"1-219"课程的成绩高于“53”号同学“1-219”成绩的所有同学的记录
 --select degree from score where sno='53' and cno='1-219';
 --select * from score where cno='1-219' and degree>(select degree from score where sno='53' and cno='1-219');
 mysql> select * from score where cno='1-219'
 and degree>(select degree from score where sno='53' and cno='1-219');
 +-----+-------+--------+
 | sno | cno   | degree |
 +-----+-------+--------+
 | 12  | 1-219 |     87 |
 | 42  | 1-219 |     78 |
 +-----+-------+--------+
 2 rows in set (0.00 sec)

20.查询成绩高于“53”号同学“1-219”成绩的所有同学的记录
--select * from score where degree>(select degree from score where sno='53' and cno='1-219');
mysql> select * from score where degree>(select degree from score where sno='53' and cno='1-219');
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 11  | 2-109 |     88 |
| 12  | 1-219 |     87 |
| 12  | 2-301 |     80 |
| 21  | 2-109 |     94 |
| 23  | 2-301 |     87 |
| 28  | 5-921 |     99 |
| 42  | 1-219 |     78 |
| 52  | 2-109 |     89 |
+-----+-------+--------+
8 rows in set (0.00 sec)

21.查询和学号为12、42的同学同年出生的所以同学的sno、sname、sbirth列
--先查年份
--select year(sbirth) from student where sno in (12,42);
mysql> select year(sbirth) from student where sno in (12,42);
+--------------+
| year(sbirth) |
+--------------+
|         2000 |
|         1999 |
+--------------+
2 rows in set (0.01 sec)

--让年份等于它
--select sno,sname,sbirth from student where year(sbirth) in (select year(sbirth) from student where sno in (12,42));
mysql> select sno,sname,sbirth from student where year(sbirth) in (select year(sbirth) from student where sno in (12,42));
+-----+--------+---------------------+
| sno | sname  | sbirth              |
+-----+--------+---------------------+
| 11  | 王五   | 2000-03-12 00:00:00 |
| 12  | 方七   | 2000-09-19 00:00:00 |
| 21  | 张三   | 2000-01-01 00:00:00 |
| 23  | 李华   | 2000-01-01 00:00:00 |
| 28  | 悟空   | 2000-07-29 00:00:00 |
| 42  | 赵六   | 1999-03-25 00:00:00 |
+-----+--------+---------------------+
6 rows in set (0.01 sec)

22.查询“王尼玛”教师任课的学生成绩
--查询王尼玛教师号tno
mysql> select tno from teacher where tname='王尼玛';
+-----+
| tno |
+-----+
| 101 |
+-----+
1 row in set (0.00 sec)

--查询它的课程号
--select cno from course where tno='101'
mysql> select cno from course where tno=(select tno from teacher where tname='王尼玛');
+-------+
| cno   |
+-------+
| 5-921 |
+-------+
1 row in set (0.00 sec)

--查询它的成绩
-- select degree from score where cno='5-921';
mysql> select degree from score where cno=(select cno from course where tno=(select tno from teacher where tname='王尼玛'));
+--------+
| degree |
+--------+
|     99 |
+--------+
1 row in set (0.00 sec)

--23.查询选修某课程的同学人数大于等于3人的教师名字

select * from score;
select * from teacher;
select * from course;

--查询大于等于3的课程号
mysql> select cno from score group by cno having count('cno')>=3;
+-------+
| cno   |
+-------+
| 1-219 |
| 2-109 |
+-------+
2 rows in set (0.01 sec)

--查询它的教师号tno
mysql> select tno from course where cno in (select cno from score group by cno having count('cno')>=3);
+-----+
| tno |
+-----+
| 109 |
| 121 |
+-----+
2 rows in set (0.01 sec)

--查询它的教师名tname
mysql> select tname from teacher where tno in (select tno from course where cno in (select cno from score group by cno having count('cno')>=3));
+-----------+
| tname     |
+-----------+
| 李翠花    |
| 张全蛋    |
+-----------+
2 rows in set (0.00 sec)

24.查询1班和3班的全体学生记录
--多组用class in(a,b)
--一组用class = 'a'
mysql> select * from student where class in ('1班','3班');
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirth              | class |
+-----+--------+------+---------------------+-------+
| 11  | 王五   | 男   | 2000-03-12 00:00:00 | 1班   |
| 12  | 方七   | 女   | 2000-09-19 00:00:00 | 3班   |
| 21  | 张三   | 男   | 2000-01-01 00:00:00 | 3班   |
| 23  | 李华   | 男   | 2000-01-01 00:00:00 | 3班   |
| 28  | 悟空   | 女   | 2000-07-29 00:00:00 | 3班   |
| 42  | 赵六   | 男   | 1999-03-25 00:00:00 | 1班   |
| 52  | 李四   | 女   | 2001-11-21 00:00:00 | 1班   |
| 53  | 黄八   | 男   | 1998-07-10 00:00:00 | 3班   |
+-----+--------+------+---------------------+-------+
8 rows in set (0.00 sec)

25.查询除‘电子与信息系’教师所教课程的成绩表
--查询其tno
select tno from teacher where depart='电子与信息系';
--查询cno
select cno from course where tno in (select tno from teacher where depart='电子与信息系');
--查询成绩
select degree from score where cno=(select cno from course where tno in (select tno from teacher where depart='电子与信息系'));

26.查询'电子与信息系'中与'计算机系'的proof职称不同的教师名字和职称
--选择'计算机系'的proof职称
select prof from teacher where depart='计算机系';
--令其 not in 找出
select * from teacher where depart='电子与信息系' and prof not in(select prof from teacher where depart='计算机系');

mysql> select * from teacher where depart='电子与信息系' and prof not in(select prof from teacher where depart='计算机系');
+-----+-----------+------+---------------------+-----------+--------------------+
| tno | tname     | tsex | tbirth              | prof      | depart             |
+-----+-----------+------+---------------------+-----------+--------------------+
| 101 | 王尼玛    | 男   | 1992-02-12 00:00:00 | 辅导员    | 电子与信息系       |
+-----+-----------+------+---------------------+-----------+--------------------+
1 row in set (0.00 sec)

 27.查询课程编号为'2-109'课程且成绩至少高于选修编号为'1-219'的同学的cno，sno，degree
    并按degree从高到低排序
 mysql> select * from score where cno='1-219';
 +-----+-------+--------+
 | sno | cno   | degree |
 +-----+-------+--------+
 | 12  | 1-219 |     87 |
 | 42  | 1-219 |     78 |
 | 53  | 1-219 |     47 |
 +-----+-------+--------+
 3 rows in set (0.00 sec)

 mysql> select * from score where cno='2-109';
 +-----+-------+--------+
 | sno | cno   | degree |
 +-----+-------+--------+
 | 11  | 2-109 |     88 |
 | 21  | 2-109 |     94 |
 | 52  | 2-109 |     89 |
 +-----+-------+--------+
 3 rows in set (0.00 sec)

 mysql> select * from score
     -> where cno='2-109'
     -> and degree > any(select degree from score where cno='1-219')
     -> order by degree desc;
 +-----+-------+--------+
 | sno | cno   | degree |
 +-----+-------+--------+
 | 21  | 2-109 |     94 |
 | 52  | 2-109 |     89 |
 | 11  | 2-109 |     88 |
 +-----+-------+--------+
 3 rows in set (0.01 sec)

 28.查询课程编号为'2-109'课程且成绩高于选修编号为'1-219'的所有同学的cno，sno，degree
并按degree从高到低排序

 mysql> select * from score
     -> where cno='2-109'
     -> and degree > all(select degree from score where cno = '1-219')
     -> order by degree desc;
 +-----+-------+--------+
 | sno | cno   | degree |
 +-----+-------+--------+
 | 21  | 2-109 |     94 |
 | 52  | 2-109 |     89 |
 | 11  | 2-109 |     88 |
 +-----+-------+--------+
 3 rows in set (0.00 sec)

29.找出所有教师、学生的名字、性别和出生的年份 用union
--先合并
select tname,tsex,year(tbirth) from teacher
union
select sname,ssex,year(sbirth) from student;
+-----------+------+--------------+
| tname     | tsex | year(tbirth) |
+-----------+------+--------------+
| 王尼玛    | 男   |         1992 |
| 李翠花    | 女   |         1992 |
| 库里      | 男   |         1992 |
| 张全蛋    | 男   |         1972 |
| 赵铁柱    | 女   |         1992 |
| 王五      | 男   |         2000 |
| 方七      | 女   |         2000 |
| 张三      | 男   |         2000 |
| 李华      | 男   |         2000 |
| 悟空      | 女   |         2000 |
| 赵六      | 男   |         1999 |
| 李四      | 女   |         2001 |
| 黄八      | 男   |         1998 |
+-----------+------+--------------+
13 rows in set (0.00 sec)

--改标题 as
select tname as '姓名',tsex as '性别',year(tbirth) as '出生年份' from teacher
union
select sname,ssex,year(sbirth) from student;
+-----------+--------+--------------+
| 姓名      | 性别   | 出生年份     |
+-----------+--------+--------------+
| 王尼玛    | 男     |         1992 |
| 李翠花    | 女     |         1992 |
| 库里      | 男     |         1992 |
| 张全蛋    | 男     |         1972 |
| 赵铁柱    | 女     |         1992 |
| 王五      | 男     |         2000 |
| 方七      | 女     |         2000 |
| 张三      | 男     |         2000 |
| 李华      | 男     |         2000 |
| 悟空      | 女     |         2000 |
| 赵六      | 男     |         1999 |
| 李四      | 女     |         2001 |
| 黄八      | 男     |         1998 |
+-----------+--------+--------------+
13 rows in set (0.00 sec)

30.找出所有'女'教师和'女'同学的name、sex和birth
select tname as '姓名',tsex as '性别',year(tbirth) as '出生年份' from teacher where tsex='女'
union
select sname,ssex,sbirth from student where ssex='女';
+-----------+--------+---------------------+
| 姓名      | 性别   | 出生年份            |
+-----------+--------+---------------------+
| 李翠花    | 女     | 1992                |
| 赵铁柱    | 女     | 1992                |
| 方七      | 女     | 2000-09-19 00:00:00 |
| 悟空      | 女     | 2000-07-29 00:00:00 |
| 李四      | 女     | 2001-11-21 00:00:00 |
+-----------+--------+---------------------+
5 rows in set (0.01 sec)