mysql查询练习

建表

– 学生表 student
– 学号
– 姓名
– 性别
– 出生年月
– 所在班级

create table student(
    sno varchar(20) primary key,
    sname varchar(20) not null,
    ssex varchar(10) not null,
    sbirthdasy datetime,
    class varchar(20)
);

– 教师表 teacher
– 教师编号
– 教师姓名
– 教师性别
– 出生年月日
– 职称
– 所在部门

create table teacher(
    tno varchar(20) primary key,
    tname varchar(20) not null,
    tsex varchar(20) not null,
    tbirthday datetime,
    prof varchar(10) not null,
    depart varchar(20) not null
);

– 课程表 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)
);

– 成绩表 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)
);

– 数据表中添加数据
添加学生表信息

insert into student values('101', '曾华','男','1977-09-01','95033');
insert into student values('105', '华服','女','1933-03-02','95433');
insert into student values('107', '汉斯','男','1923-09-04','93333');
insert into student values('104', '立白','女','1923-03-01','93333');
insert into student values('109', '马克','男','1974-03-02','93233');
insert into student values('103', '大道','女','1972-09-05','92333');
insert into student values('102', 'iris','女','1928-03-01','93333');
insert into student values('108', 'jack','男','1975-03-02','9328');
insert into student values('106', 'summer','女','1978-09-05','92383');

update student set class='92239' where sno='108';

添加教师表信息

insert into teacher values('804', '李斯', '男', '1935-03-02', '教授', '政治部');
insert into teacher values('803', '张良', '男', '1943-02-01', '法师', '中路');
insert into teacher values('802', '云中君', '男', '1933-02-02', '刺客', '野区');
insert into teacher values('801', '猴子', '雄', '1923-03-05', '刺客', '野区');

添加课程表

insert into course values('110', '除暴安良', '804');
insert into course values('120', '外科手术', '803');
insert into course values('123', '冲冲冲',   '802');
insert into course values('126', 'mail',    '801');

添加成绩表

insert into score values('101', '110', '92');
insert into score values('102', '110', '94');
insert into score values('103', '120', '54');
insert into score values('104', '120', '45');
insert into score values('105', '120', '64');
insert into score values('106', '123', '45');
insert into score values('107', '123', '25');
insert into score values('108', '126', '22');
insert into score values('109', '126', '32');

查询练习：

1. 查询student表中的所有记录

select * from student;
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthdasy          | class |
+-----+--------+------+---------------------+-------+
| 101 | 曾华   | 男   | 1977-09-01 00:00:00 | 95033 |
| 102 | iris   | 女   | 1928-03-01 00:00:00 | 93333 |
| 103 | 大道   | 女   | 1972-09-05 00:00:00 | 92333 |
| 104 | 立白   | 女   | 1923-03-01 00:00:00 | 93333 |
| 105 | 华服   | 女   | 1933-03-02 00:00:00 | 95433 |
| 106 | summer | 女   | 1978-09-05 00:00:00 | 92383 |
| 107 | 汉斯   | 男   | 1923-09-04 00:00:00 | 93333 |
| 108 | jack   | 男   | 1975-03-02 00:00:00 | 92239 |
| 109 | 马克   | 男   | 1974-03-02 00:00:00 | 93233 |
+-----+--------+------+---------------------+-------+

2. 查询student表中的所有sname、ssex 和class列。

select sname,ssex, class from student;
+--------+------+-------+
| sname  | ssex | class |
+--------+------+-------+
| 曾华   | 男   | 95033 |
| iris   | 女   | 93333 |
| 大道   | 女   | 92333 |
| 立白   | 女   | 93333 |
| 华服   | 女   | 95433 |
| summer | 女   | 92383 |
| 汉斯   | 男   | 93333 |
| jack   | 男   | 92239 |
| 马克   | 男   | 93233 |
+--------+------+-------+

3. 查询教师所有单位即不重复的depart列.

distinct排重

select depart from teacher;
+-----------+
| depart    |
+-----------+
| 野区      |
| 野区      |
| 中路      |
| 政治部    |
+-----------+

select distinct depart from teacher;
+-----------+
| depart    |
+-----------+
| 野区      |
| 中路      |
| 政治部    |
+-----------+

4. 查询分数在40到80之间的所有记录。

select * from score where degree between 40 and 80;
+-----+-----+--------+
| sno | cno | degree |
+-----+-----+--------+
| 103 | 120 |     54 |
| 104 | 120 |     45 |
| 105 | 120 |     64 |
| 106 | 123 |     45 |
+-----+-----+--------+

select * from score where degree > 40 and degree < 80;
+-----+-----+--------+
| sno | cno | degree |
+-----+-----+--------+
| 103 | 120 |     54 |
| 104 | 120 |     45 |
| 105 | 120 |     64 |
| 106 | 123 |     45 |
+-----+-----+--------+

5. 查询score表中成绩为85，86，或88的记录。

表示或者关系的查询 in

select * from score where degree in(54, 64);
+-----+-----+--------+
| sno | cno | degree |
+-----+-----+--------+
| 103 | 120 |     54 |
| 105 | 120 |     64 |
+-----+-----+--------+

6 查询student表中‘93333’班或性别为“女”的同学记录。

select * from student where class='93333' or ssex='女';
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthdasy          | class |
+-----+--------+------+---------------------+-------+
| 102 | iris   | 女   | 1928-03-01 00:00:00 | 93333 |
| 103 | 大道   | 女   | 1972-09-05 00:00:00 | 92333 |
| 104 | 立白   | 女   | 1923-03-01 00:00:00 | 93333 |
| 105 | 华服   | 女   | 1933-03-02 00:00:00 | 95433 |
| 106 | summer | 女   | 1978-09-05 00:00:00 | 92383 |
| 107 | 汉斯   | 男   | 1923-09-04 00:00:00 | 93333 |
+-----+--------+------+---------------------+-------+

7. 以class降序查询student表的所有记录。

升序asc，降序desc。

select * from student order by class desc;
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthdasy          | class |
+-----+--------+------+---------------------+-------+
| 105 | 华服   | 女   | 1933-03-02 00:00:00 | 95433 |
| 101 | 曾华   | 男   | 1977-09-01 00:00:00 | 95033 |
| 102 | iris   | 女   | 1928-03-01 00:00:00 | 93333 |
| 104 | 立白   | 女   | 1923-03-01 00:00:00 | 93333 |
| 107 | 汉斯   | 男   | 1923-09-04 00:00:00 | 93333 |
| 109 | 马克   | 男   | 1974-03-02 00:00:00 | 93233 |
| 106 | summer | 女   | 1978-09-05 00:00:00 | 92383 |
| 103 | 大道   | 女   | 1972-09-05 00:00:00 | 92333 |
| 108 | jack   | 男   | 1975-03-02 00:00:00 | 92239 |
+-----+--------+------+---------------------+-------+

8. 以con升序， degree降序查询score表的所有记录。

select * from score order by cno asc, degree desc;
+-----+-----+--------+
| sno | cno | degree |
+-----+-----+--------+
| 102 | 110 |     94 |
| 101 | 110 |     92 |
| 105 | 120 |     64 |
| 103 | 120 |     54 |
| 104 | 120 |     45 |
| 106 | 123 |     45 |
| 107 | 123 |     25 |
| 109 | 126 |     32 |
| 108 | 126 |     22 |
+-----+-----+--------+

9. 查询‘93333’班的人数。

select count(*) from student where class='93333';
+----------+
| count(*) |
+----------+
|        3 |
+----------+

10. 查询score表中最高分学生学号和课程号。

子查询。

select sno, cno from score where degree=(select max(degree) from score);
+-----+-----+
| sno | cno |
+-----+-----+
| 102 | 110 |
+-----+-----+

排序: 使用排序有一定的弊端，当初先两个以上的最高分时，不好处理。
— limit 0, 1: 第一个数字表示开始，第二个表示要查询到条数。

select sno, cno, degree from score order by degree desc limit 0, 1;
+-----+-----+--------+
| sno | cno | degree |
+-----+-----+--------+
| 102 | 110 |     94 |
+-----+-----+--------+

11. 查询每门课的平均成绩。

select distinct cno from course;
--- avg()
--- 一门课的平均成绩
select avg(degree) from score where cno='110';
+-------------+
| avg(degree) |
+-------------+
|     93.0000 |
+-------------+

— group by 分组
— 现将课程号分组，在计算其平均成绩

select cno,avg(degree) from score group by cno;
+-----+-------------+
| cno | avg(degree) |
+-----+-------------+
| 110 |     93.0000 |
| 120 |     54.3333 |
| 123 |     35.0000 |
| 126 |     27.0000 |
+-----+-------------+

12. 查询score表中至少有两名学生选修并以1开头的课程的平均成绩。

 select cno,avg(degree),count(*) from score group by cno 
 having count(cno)>=2 and cno like '1%';
+-----+-------------+----------+
| cno | avg(degree) | count(*) |
+-----+-------------+----------+
| 110 |     93.0000 |        2 |
| 120 |     54.3333 |        3 |
| 123 |     35.0000 |        2 |
| 126 |     27.0000 |        2 |
+-----+-------------+----------+

13. 查询分数大于30小于60的sno列。

select sno,degree from score where degree > 30 and degree < 60;
+-----+--------+
| sno | degree |
+-----+--------+
| 103 |     54 |
| 104 |     45 |
| 106 |     45 |
| 109 |     32 |
+-----+--------+

select sno, degree from score where degree between 30 and 60;
+-----+--------+
| sno | degree |
+-----+--------+
| 103 |     54 |
| 104 |     45 |
| 106 |     45 |
| 109 |     32 |
+-----+--------+

14. 查询所有学生的 sname、cno 和 degree列。

— 多表联查。

select sno,sname from student;
+-----+--------+
| sno | sname  |
+-----+--------+
| 101 | 曾华   |
| 102 | iris   |
| 103 | 大道   |
| 104 | 立白   |
| 105 | 华服   |
| 106 | summer |
| 107 | 汉斯   |
| 108 | jack   |
| 109 | 马克   |
+-----+--------+
select sno,cno,degree from score;
+-----+-----+--------+
| sno | cno | degree |
+-----+-----+--------+
| 101 | 110 |     92 |
| 102 | 110 |     94 |
| 103 | 120 |     54 |
| 104 | 120 |     45 |
| 105 | 120 |     64 |
| 106 | 123 |     45 |
| 107 | 123 |     25 |
| 108 | 126 |     22 |
| 109 | 126 |     32 |
+-----+-----+--------+

select sname,cno,degree from student,score
where student.sno=score.sno;
+--------+-----+--------+
| sname  | cno | degree |
+--------+-----+--------+
| 曾华   | 110 |     92 |
| iris   | 110 |     94 |
| 大道   | 120 |     54 |
| 立白   | 120 |     45 |
| 华服   | 120 |     64 |
| summer | 123 |     45 |
| 汉斯   | 123 |     25 |
| jack   | 126 |     22 |
| 马克   | 126 |     32 |
+--------+-----+--------+

15. 查询所有学生的sno、cname 和 degree列。

— 多表查询，先分开查询，在使用共同的字段去匹配。

select cno,cname from course;
+-----+--------------+
| cno | cname        |
+-----+--------------+
| 110 | 除暴安良     |
| 120 | 外科手术     |
| 123 | 冲冲冲       |
| 126 | mail         |
+-----+--------------+

select sno,degree,cno from score; 
+-----+--------+-----+
| sno | degree | cno |
+-----+--------+-----+
| 101 |     92 | 110 |
| 102 |     94 | 110 |
| 103 |     54 | 120 |
| 104 |     45 | 120 |
| 105 |     64 | 120 |
| 106 |     45 | 123 |
| 107 |     25 | 123 |
| 108 |     22 | 126 |
| 109 |     32 | 126 |
+-----+--------+-----+

select cname,sno,degree from course,score
where course.cno=score.cno;
+--------------+-----+--------+
| cname        | sno | degree |
+--------------+-----+--------+
| 除暴安良     | 101 |     92 |
| 除暴安良     | 102 |     94 |
| 外科手术     | 103 |     54 |
| 外科手术     | 104 |     45 |
| 外科手术     | 105 |     64 |
| 冲冲冲       | 106 |     45 |
| 冲冲冲       | 107 |     25 |
| mail         | 108 |     22 |
| mail         | 109 |     32 |
+--------------+-----+--------+

16. 查询所有学生的sname、cname和degree列。

— sname -> student
— cname -> course
— degree -> score

select sname, cname, degree from student, course, score
where student.sno=score.sno and course.cno=score.cno; 
+--------+--------------+--------+
| sname  | cname        | degree |
+--------+--------------+--------+
| 曾华   | 除暴安良     |     92 |
| iris   | 除暴安良     |     94 |
| 大道   | 外科手术     |     54 |
| 立白   | 外科手术     |     45 |
| 华服   | 外科手术     |     64 |
| summer | 冲冲冲       |     45 |
| 汉斯   | 冲冲冲       |     25 |
| jack   | mail         |     22 |
| 马克   | mail         |     32 |
+--------+--------------+--------+

— as 重命名

select sname,cname,degree, student.sno as student_sno, score.sno, course.cno as course_cno, score.cno
from student,course,score
where student.sno=score.sno and course.cno=score.cno;
+--------+--------------+--------+-------------+-----+------------+-----+
| sname  | cname        | degree | student_sno | sno | course_cno | cno |
+--------+--------------+--------+-------------+-----+------------+-----+
| 曾华   | 除暴安良     |     92 | 101         | 101 | 110        | 110 |
| iris   | 除暴安良     |     94 | 102         | 102 | 110        | 110 |
| 大道   | 外科手术     |     54 | 103         | 103 | 120        | 120 |
| 立白   | 外科手术     |     45 | 104         | 104 | 120        | 120 |
| 华服   | 外科手术     |     64 | 105         | 105 | 120        | 120 |
| summer | 冲冲冲       |     45 | 106         | 106 | 123        | 123 |
| 汉斯   | 冲冲冲       |     25 | 107         | 107 | 123        | 123 |
| jack   | mail         |     22 | 108         | 108 | 126        | 126 |
| 马克   | mail         |     32 | 109         | 109 | 126        | 126 |
+--------+--------------+--------+-------------+-----+------------+-----+

17. 查询‘93333’班学生每门课的平均分。

— 这些学生来自93333班级

select sno from student where class='93333';

select cno,avg(degree) from score 
where sno in(select sno from student where class='93333') 
group by cno;
+-----+-------------+
| cno | avg(degree) |
+-----+-------------+
| 110 |     94.0000 |
| 120 |     45.0000 |
| 123 |     25.0000 |
+-----+-------------+

18. 查询选修‘110’课程的成绩高于‘101’号同学‘110‘成绩的所有同学记录。

— 查询’101’同学’110’课程的成绩。
— 查询成绩改成绩成绩大于他的成绩。

select degree from score where sno='101' and cno='110';
select * from score 
where cno='110' and degree > (select degree from score where sno='101' and cno='110');
+-----+-----+--------+
| sno | cno | degree |
+-----+-----+--------+
| 102 | 110 |     94 |
+-----+-----+--------+

19. 查询成绩高于学号为‘101’，课程号为‘110’的成绩的所有记录。

— 查询学号为‘101’同学课程号为‘110’的成绩。

select * from score where sno='101' and cno='110';
+-----+-----+--------+
| sno | cno | degree |
+-----+-----+--------+
| 101 | 110 |     92 |
+-----+-----+--------+

— 查询高于92成绩的所有记录。

select * from score where degree > (select degree from score where sno='101' and cno='110');
+-----+-----+--------+
| sno | cno | degree |
+-----+-----+--------+
| 102 | 110 |     94 |
+-----+-----+--------+

20. 查询和学号108、101的同学同年出生的所有学生的sno、sname、sbirthday。

select year(sbirthdasy) from student where sno='108' or sno='101';
--- 或者
select year(sbirthdasy) from student where sno in(108, 101);

select sno,sname,sbirthdasy from student 
where year(sbirthdasy) in(select year(sbirthdasy) from student where sno in(108, 101));
--- 或者
select sno,sname,sbirthdasy from student 
where year(sbirthdasy) in(select year(sbirthdasy) from student where sno='108' or sno='101');

+-----+--------+---------------------+
| sno | sname  | sbirthdasy          |
+-----+--------+---------------------+
| 101 | 曾华   | 1977-09-01 00:00:00 |
| 108 | jack   | 1975-03-02 00:00:00 |
+-----+--------+---------------------+

21. 查询‘云中君’教师任课的学生成绩。

— 查询‘云中君’教师所任教师编号。

select tno from teacher where tname='云中君';
+-----+
| tno |
+-----+
| 802 |
+-----+

— 查询该教师编号对应的课程编号。

select cno from course where tno=(select tno from teacher where tname='云中君');
+-----+
| cno |
+-----+
| 123 |

查询该课程编号下的所有学生成绩。

select * from score 
where cno=(select cno from course where tno=(select tno from teacher where tname='云中君'));
+-----+-----+--------+
| sno | cno | degree |
+-----+-----+--------+
| 106 | 123 |     45 |
| 107 | 123 |     25 |
+-----+-----+--------+

22. 查询选修课程人数大于2人的教师姓名。

— 查询选修人数大于2人的课程。

select cno from score group by cno having count(*) > 2;
+-----+
| cno |
+-----+
| 120 |
+-----+

— 查询该课程的教师编号。

select tno from course 
where cno=(select cno from score group by cno having count(*) > 2);
+-----+
| tno |
+-----+
| 803 |
+-----+

— 多条记录的情况下，使用 in

select tno from course 
where cno in(select cno from score group by cno having count(*) > 1);
+-----+
| tno |
+-----+
| 801 |
| 802 |
| 803 |
| 804 |
+-----+

— 查询改编号下的教师姓名。

select tname from teacher 
where tno =(select tno from course where cno=(select cno from score group by cno having count(*) > 2));
+--------+
| tname  |
+--------+
| 张良   |
+--------+

– 查询‘93333’班和‘95433’班所有同学的记录。

insert into student values('110', 'json','男','1978-09-05','95433');
select * from student where class in(95433,93333);
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthdasy          | class |
+-----+--------+------+---------------------+-------+
| 102 | iris   | 女   | 1928-03-01 00:00:00 | 93333 |
| 104 | 立白   | 女   | 1923-03-01 00:00:00 | 93333 |
| 105 | 华服   | 女   | 1933-03-02 00:00:00 | 95433 |
| 107 | 汉斯   | 男   | 1923-09-04 00:00:00 | 93333 |
| 110 | json   | 男   | 1978-09-05 00:00:00 | 95433 |
+-----+--------+------+---------------------+-------+

24. 查询存在有65分以上成绩单课程的cno。

select cno,degree from score where degree > 85;

25. 查询野区所有教师所教课程的成绩表。

— 查询野区教师的教师编号。

select tno from teacher where depart='野区';

— 跟据教师编号，查询课程号。

select cno from course where tno in(select tno from teacher where depart='野区');

— 根据课程号查询成绩。

+-----+-----+--------+
| sno | cno | degree |
+-----+-----+--------+
| 108 | 126 |     22 |
| 109 | 126 |     32 |
| 106 | 123 |     45 |
| 107 | 123 |     25 |
+-----+-----+--------+

26. 查询‘野区’和‘中路’不同prof教师的tname和prof。

select prof from teacher where depart='野区';

select tname,prof from teacher 
where depart='中路' and prof not in(select prof from teacher where depart='野区')
union
select tname,prof from teacher 
where depart='野区' and prof not in(select prof from teacher where depart='中路');
+-----------+--------+
| tname     | prof   |
+-----------+--------+
| 张良      | 法师   |
| 猴子      | 刺客   |
| 云中君    | 刺客   |
+-----------+--------+

27. 查询选修编号为‘110’课程切成绩至少高于选修课程编号为‘120’课程的同学的sno、cno、degree。

select * from score where cno='110';
select * from score where cno='120';

select * from score 
where cno='110' 
and degree > any(select degree from score where cno='120')
order by degree desc;
+-----+-----+--------+
| sno | cno | degree |
+-----+-----+--------+
| 102 | 110 |     94 |
| 101 | 110 |     92 |
+-----+-----+--------+

28. 查询选修编号为‘120’且成绩高于选修课程编号为‘123’课程的同学的sno、cno和degree。

select degree from score where cno='120';
select degree from score where cno='123';

select * from score 
where cno='120' 
and degree > all(select degree from score where cno='123')
order by degree desc;
+-----+-----+--------+
| sno | cno | degree |
+-----+-----+--------+
| 105 | 120 |     64 |
| 103 | 120 |     54 |
+-----+-----+--------+

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

select sname as name,ssex as sex,sbirthdasy as birthday from student
union
select tname,tsex,tbirthday  from teacher; 

-- 或者，直接空格，不用as。
select sname name,ssex sex,sbirthdasy birthday from student
union
select tname,tsex,tbirthday  from teacher; 

30. 查询所有‘女’教师和‘女’同学的name、sex和birthday。

insert into teacher values('805', '武则天', '女', '1932-02-01', '法师', '中路');
insert into teacher values('806', '芈月', '女', '1911-02-01', '法师', '中路');
select tname name, tsex sex, tbirthday birthday from teacher where tsex='女'
union
select sname,ssex,sbirthdasy from student where ssex='女';                                
+-----------+-----+---------------------+         
| name      | sex | birthday            |         
+-----------+-----+---------------------+         
| 武则天    | 女  | 1932-02-01 00:00:00 |           
| 芈月      | 女  | 1911-02-01 00:00:00 |          
| iris      | 女  | 1928-03-01 00:00:00 |          
| 大道      | 女  | 1972-09-05 00:00:00 |          
| 立白      | 女  | 1923-03-01 00:00:00 |          
| 华服      | 女  | 1933-03-02 00:00:00 |          
| summer    | 女  | 1978-09-05 00:00:00 |          
+-----------+-----+---------------------+         

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

select cno,avg(degree) from score group by cno;
select * from score;
a表                                b表
+-----+-----+--------+            +-----+-----+--------+                    
| sno | cno | degree |            | sno | cno | degree |                    
+-----+-----+--------+            +-----+-----+--------+                    
| 101 | 110 |     92 |            | 101 | 110 |     92 |                    
| 102 | 110 |     94 |            | 102 | 110 |     94 |                    
| 103 | 120 |     54 |            | 103 | 120 |     54 |                    
| 104 | 120 |     45 |            | 104 | 120 |     45 |                    
| 105 | 120 |     64 |            | 105 | 120 |     64 |                    
| 106 | 123 |     45 |            | 106 | 123 |     45 |                    
| 107 | 123 |     25 |            | 107 | 123 |     25 |                    
| 108 | 126 |     22 |            | 108 | 126 |     22 |                    
| 109 | 126 |     32 |            | 109 | 126 |     32 |                    
+-----+-----+--------+            +-----+-----+--------+         

select * from score a 
where degree < (select avg(degree) from score b where a.cno=b.cno);
+-----+-----+--------+
| sno | cno | degree |
+-----+-----+--------+
| 101 | 110 |     92 |
| 103 | 120 |     54 |
| 104 | 120 |     45 |
| 107 | 123 |     25 |
| 108 | 126 |     22 |
+-----+-----+--------+

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

– 在课表中安排了课程

select * from course;

select tname,depart from teacher where tno in(select tno from course);
+-----------+-----------+
| tname     | depart    |
+-----------+-----------+
| 猴子      | 野区      |
| 云中君    | 野区      |
| 张良      | 中路      |
| 李斯      | 政治部    |
+-----------+-----------+

33. 查询至少有2名女生的班号。

select * from student;

select class from student where ssex='女' group by class having count(*)>1; 
+-------+
| class |
+-------+
| 93333 |
+-------+

34. 查询studeng表中，不姓马的同学记录。

select * from student;
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthdasy          | class |
+-----+--------+------+---------------------+-------+
| 101 | 曾华   | 男   | 1977-09-01 00:00:00 | 95033 |
| 102 | iris   | 女   | 1928-03-01 00:00:00 | 93333 |
| 103 | 大道   | 女   | 1972-09-05 00:00:00 | 92333 |
| 104 | 立白   | 女   | 1923-03-01 00:00:00 | 93333 |
| 105 | 华服   | 女   | 1933-03-02 00:00:00 | 95433 |
| 106 | summer | 女   | 1978-09-05 00:00:00 | 92383 |
| 107 | 汉斯   | 男   | 1923-09-04 00:00:00 | 93333 |
| 108 | jack   | 男   | 1975-03-02 00:00:00 | 92239 |
| 109 | 马克   | 男   | 1974-03-02 00:00:00 | 93233 |
| 110 | json   | 男   | 1978-09-05 00:00:00 | 95433 |
+-----+--------+------+---------------------+-------+

select * from student where sname not like '马%';
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthdasy          | class |
+-----+--------+------+---------------------+-------+
| 101 | 曾华   | 男   | 1977-09-01 00:00:00 | 95033 |
| 102 | iris   | 女   | 1928-03-01 00:00:00 | 93333 |
| 103 | 大道   | 女   | 1972-09-05 00:00:00 | 92333 |
| 104 | 立白   | 女   | 1923-03-01 00:00:00 | 93333 |
| 105 | 华服   | 女   | 1933-03-02 00:00:00 | 95433 |
| 106 | summer | 女   | 1978-09-05 00:00:00 | 92383 |
| 107 | 汉斯   | 男   | 1923-09-04 00:00:00 | 93333 |
| 108 | jack   | 男   | 1975-03-02 00:00:00 | 92239 |
| 110 | json   | 男   | 1978-09-05 00:00:00 | 95433 |
+-----+--------+------+---------------------+-------+

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

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

select year(now());
select year(sbirthdasy) from student;

select sname,year(now())-year(sbirthdasy)+1 as '年龄' from student; 
+--------+--------+
| sname  | 年龄   |
+--------+--------+
| 曾华   |     44 |
| iris   |     93 |
| 大道   |     49 |
| 立白   |     98 |
| 华服   |     88 |
| summer |     43 |
| 汉斯   |     98 |
| jack   |     46 |
| 马克   |     47 |
| json   |     43 |
+--------+--------+

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

select sbirthdasy from student order by sbirthdasy;

select max(sbirthdasy) as birthday from student
union
select min(sbirthdasy) from student;
+---------------------+
| birthday            |
+---------------------+
| 1978-09-05 00:00:00 |
| 1923-03-01 00:00:00 |
+---------------------+
 ---或者
select max(sbirthdasy) as '最小',min(sbirthdasy) as '最大' from student;
+---------------------+---------------------+
| 最小                | 最大                |
+---------------------+---------------------+
| 1978-09-05 00:00:00 | 1923-03-01 00:00:00 |
+---------------------+---------------------+

— max, min：这两个函数比较的是数值，也就是说1978大于1923;

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

select * from student order by class desc, sbirthdasy;
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthdasy          | class |
+-----+--------+------+---------------------+-------+
| 105 | 华服   | 女   | 1933-03-02 00:00:00 | 95433 |
| 110 | json   | 男   | 1978-09-05 00:00:00 | 95433 |
| 101 | 曾华   | 男   | 1977-09-01 00:00:00 | 95033 |
| 104 | 立白   | 女   | 1923-03-01 00:00:00 | 93333 |
| 107 | 汉斯   | 男   | 1923-09-04 00:00:00 | 93333 |
| 102 | iris   | 女   | 1928-03-01 00:00:00 | 93333 |
| 109 | 马克   | 男   | 1974-03-02 00:00:00 | 93233 |
| 106 | summer | 女   | 1978-09-05 00:00:00 | 92383 |
| 103 | 大道   | 女   | 1972-09-05 00:00:00 | 92333 |
| 108 | jack   | 男   | 1975-03-02 00:00:00 | 92239 |
+-----+--------+------+---------------------+-------+

38. 查询’男‘教师及其所上的课程。

select * from teacher where tsex='男';

select * from course where tno in(select tno from teacher where tsex='男');
+-----+--------------+-----+
| cno | cname        | tno |
+-----+--------------+-----+
| 123 | 冲冲冲       | 802 |
| 120 | 外科手术     | 803 |
| 110 | 除暴安良     | 804 |
+-----+--------------+-----+

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

select max(degree) from score;

select * from score where degree=(select max(degree) from score);
+-----+-----+--------+
| sno | cno | degree |
+-----+-----+--------+
| 102 | 110 |     94 |
+-----+-----+--------+

40. 查询和‘jack’同学同性别的所有同学的sname。

 select ssex from student where sname='jack';

select sname,ssex from student where ssex=( select ssex from student where sname='jack');
+--------+------+
| sname  | ssex |
+--------+------+
| 曾华   | 男   |
| 汉斯   | 男   |
| jack   | 男   |
| 马克   | 男   |
| json   | 男   |
+--------+------+

41. 查询和‘立白’同性别并且同班级同学sname。

select ssex from student where sname='立白';

select class from student where sname='立白';

select sname from student 
where ssex=(select ssex from student where sname='立白') 
and class=(select class from student where sname='立白');
+--------+
| sname  |
+--------+
| iris   |
| 立白   |
+--------+

42. 查询所有选修‘除暴安良’课程的男学生的成绩表。

—思路1:
— 查询课程号

select * from course where cname='除暴安良';

— 根据课程号查询学号

select * from score where cno=(select cno from course where cname='除暴安良');

— 根据性别和学号确定学号。

select sno from student 
where ssex='男' 
and sno in(select sno from score where cno=(select cno from course where cname='除暴安良'));

根据学号或者成绩。

select * from score 
where sno in(select sno from student where ssex='男' and sno in(select sno from score where cno=(select cno from course where cname='除暴安良')));

+-----+-----+--------+
| sno | cno | degree |
+-----+-----+--------+
| 101 | 110 |     92 |
+-----+-----+--------+

— 思路2:
— 查询男学生信息。

select * from student where ssex='男';

— 查询选修‘除暴安良’课程的信息。

select * from course where cname='除暴安良';

— 查询成绩表，且学号在‘男’学生中，课程号在‘除暴安良’内。

select * from score 
where sno in(select sno from student where ssex='男')
and cno in(select cno from course where cname='除暴安良');
+-----+-----+--------+
| sno | cno | degree |
+-----+-----+--------+
| 101 | 110 |     92 |
+-----+-----+--------+

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,56,'E');
insert into grade values( 0,59,'E');

delete from grade where upp=56;

— 现查询所有同学额的sno、cno好grade列。

select sno,cno,degree,grade from score,grade where degree between low and upp;
+-----+-----+--------+-------+
| sno | cno | degree | grade |
+-----+-----+--------+-------+
| 101 | 110 |     92 | A     |
| 102 | 110 |     94 | A     |
| 103 | 120 |     54 | E     |
| 104 | 120 |     45 | E     |
| 105 | 120 |     64 | D     |
| 106 | 123 |     45 | E     |
| 107 | 123 |     25 | E     |
| 108 | 126 |     22 | E     |
| 109 | 126 |     32 | E     |
+-----+-----+--------+-------+