Mysql学习笔记-ch4-查询练习

最新推荐文章于 2022-06-10 21:13:36 发布

SophiaSSSSS

最新推荐文章于 2022-06-10 21:13:36 发布

阅读量461

点赞数

分类专栏：数据分析工具学习帖文章标签： mysql

本文链接：https://blog.csdn.net/weixin_44216391/article/details/88685022

版权

数据分析工具学习帖专栏收录该内容

25 篇文章 1 订阅

订阅专栏

– mysql查询练习

cd C:\Program Files\MySQL\MySQL Server 5.7\bin

– 学生表
– Student
– 学号
– 姓名
– 性别
– 出生年月日
– 所在班级
create table student(
sno varchar(20) primary key,
sname varchar(20) not null,
ssx varchar(10) not null,
sbirthday datetime,
class varchar(20)
);

– 教师表
– Teacher
– 教师编号
– 教师名字
– 教师性别
– 出生年月日
– 职称
– 所在部门
create table teacher(
tno varchar(20) primary key,
tname varchar(20) not null,
tsex varchar(10) not null,
tbirthday datetime,
prof varchar(20) 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,
grade decimal,
foreign key(sno) references student(sno),
foreign key(cno) references course(cno),
primary key(sno,cno)
);

– -- 尝试解决"incorrect string value"，失败，与"foreign key"有关
alter table student convert to character set utf8mb4 collate utf8mb4_bin;
alter table teacher convert to character set utf8mb4 collate utf8mb4_bin;
alter table course convert to character set utf8mb4 collate utf8mb4_bin;
alter table score convert to character set utf8mb4 collate utf8mb4_bin;

ERROR 1833 (HY000): Cannot change column ‘cno’: used in a foreign key constraint ‘score_ibfk_2’ of table ‘selecttest.score’

– -- 再尝试解决"incorrect string value"，虽本次操作成功，但并不能解决"incorrect string"报错
ALTER DATABASE selecttest CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

mysql> ALTER DATABASE selecttest CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
Query OK, 1 row affected (0.01 sec)

– -- 后通过网上帖子，更改my.ini配置文件内默认格式utf8mb4和utf8mb4_general_ci，解决。

– 往数据表中添加数据
#添加学生信息
insert into student values(“101”,“曾华”,“男”,“1977-09-01”,“95033”);
insert into student values(“102”,“匡明”,“男”,“1975-10-02”,“95031”);
insert into student values(“103”,“王丽”,“女”,“1976-01-23”,“95033”);
insert into student values(“104”,“李军”,“男”,“1976-02-20”,“95033”);
insert into student values(“105”,“王芳”,“女”,“1975-02-11”,“95031”);
insert into student values(“106”,“陆君”,“男”,“1974-06-03”,“95031”);
insert into student values(“107”,“王尼玛”,“男”,“1976-02-20”,“95033”);
insert into student values(“108”,“张全蛋”,“男”,“1975-02-11”,“95031”);
insert into student values(“109”,“赵铁柱”,“男”,“1974-06-03”,“95031”);

#添加教师表
insert into teacher values(“804”,“李诚”,“男”,“1958-12-02”,“副教授”,“计算机系”);
insert into teacher values(“856”,“张旭”,“男”,“1969-03-12”,“讲师”,“电子工程系”);
insert into teacher values(“825”,“王萍”,“女”,“1972-05-05”,“助教”,“计算机系”);
insert into teacher values(“831”,“刘冰”,“女”,“1977-08-14”,“副教授”,“电子工程系”);

#添加课程表
insert into course values(“3-105”,“计算机导论”,“825”);
insert into course values(“3-245”,“操作系统”,“804”);
insert into course values(“6-166”,“数字电路”,“856”);
insert into course values(“9-888”,“高等数学”,“831”);

#添加成绩表
insert into score values(“103”,“3-245”,“86”);
insert into score values(“105”,“3-245”,“75”);
insert into score values(“109”,“3-245”,“68”);
insert into score values(“103”,“3-105”,“92”);
insert into score values(“105”,“3-105”,“88”);
insert into score values(“109”,“3-105”,“76”);
insert into score values(“103”,“6-166”,“85”);
insert into score values(“105”,“6-166”,“79”);
insert into score values(“109”,“6-166”,“81”);

– 如果表字段设置错误，需要删除原表后重新建表，则 drop table score 再重新create。

– 如果插入记录时把课程号cno误写为"2-245"，需要改成"3-245"，则可以如下操作：
mysql> delete from course where cno=“2-245”;
Query OK, 1 row affected (0.01 sec)
mysql> insert into course values(“3-245”,“操作系统”,“804”);
Query OK, 1 row affected (0.01 sec)
mysql> select * from course;
±------±----------------±----+
| cno | cname | tno |
±------±----------------±----+
| 3-105 | 计算机导论 | 825 |
| 3-245 | 操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
| 9-888 | 高等数学 | 831 |
±------±----------------±----+
4 rows in set (0.00 sec)

– 查询练习

– 1、查询student表的所有记录。
mysql> select * from student;
±----±----------±----±--------------------±------+
| sno | sname | ssx | 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-11 00:00:00 | 95031 |
| 106 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 108 | 张全蛋 | 男 | 1975-02-11 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
±----±----------±----±--------------------±------+
9 rows in set (0.00 sec)

– 2、查询student表的所有记录的sname、ssex和class列。

select sname,ssex,class from student;

– -- 发现之前建表的时候把ssex输入成ssx了，如何改正？
mysql> ALTER TABLE student CHANGE ssx ssex varchar(10) not null;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select sname,ssex,class from student;
±----------±-----±------+
| sname | ssex | class |
±----------±-----±------+
| 曾华 | 男 | 95033 |
| 匡明 | 男 | 95031 |
| 王丽 | 女 | 95033 |
| 李军 | 男 | 95033 |
| 王芳 | 女 | 95031 |
| 陆君 | 男 | 95031 |
| 王尼玛 | 男 | 95033 |
| 张全蛋 | 男 | 95031 |
| 赵铁柱 | 男 | 95031 |
±----------±-----±------+
9 rows in set (0.00 sec)

– 4、查询score表中成绩在60到80之间的所有记录。
– 查询区间
mysql> select * from score where grade between 60 and 80;
±----±------±------+
| sno | cno | grade |
±----±------±------+
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
±----±------±------+
4 rows in set (0.00 sec)

– -- 直接使用运算符比较（个人尝试新方法，发现可行，如下：）
mysql> select * from score where grade>60 and grade<80;
±----±------±------+
| sno | cno | grade |
±----±------±------+
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
±----±------±------+
4 rows in set (0.00 sec)

– 5、查询score表中成绩为85,86或88的记录。
– 表示或者关系的查询 in
mysql> select * from score where grade in (85,86,88);
±----±------±------+
| sno | cno | grade |
±----±------±------+
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
±----±------±------+
3 rows in set (0.00 sec)

– 6、查询student表中“95031”班或性别为“女”的同学记录。
– or 表示或者
mysql> select * from student where class=“95031” or ssex=“女”;
±----±----------±-----±--------------------±------+
| sno | sname | ssex | sbirthday | class |
±----±----------±-----±--------------------±------+
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-11 00:00:00 | 95031 |
| 106 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋 | 男 | 1975-02-11 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
±----±----------±-----±--------------------±------+
6 rows in set (0.00 sec)

– 7、以class降序查询student表的所有记录。
– 升序，降序
mysql> select * from student order by class desc;
±----±----------±-----±--------------------±------+
| sno | sname | ssex | sbirthday | class |
±----±----------±-----±--------------------±------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 105 | 王芳 | 女 | 1975-02-11 00:00:00 | 95031 |
| 106 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋 | 男 | 1975-02-11 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
±----±----------±-----±--------------------±------+
9 rows in set (0.00 sec)

– -- (一般默认升序，不用写asc) select * from student order by class; asc;

– 8、以cno升序、degree降序查询score表的所有记录。
mysql> select * from score order by cno asc, grade desc;
±----±------±------+
| sno | cno | grade |
±----±------±------+
| 103 | 3-105 | 92 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
| 103 | 3-245 | 86 |
| 105 | 3-245 | 75 |
| 109 | 3-245 | 68 |
| 103 | 6-166 | 85 |
| 109 | 6-166 | 81 |
| 105 | 6-166 | 79 |
±----±------±------+
9 rows in set (0.00 sec)

– 9、查询“95031”班的学生人数。
– 统计count
select count() from student where class=“95031”;
mysql> select count() from student where class=“95031”;
±---------+
| count(*) |
±---------+
| 5 |
±---------+
1 row in set (0.00 sec)

– 10、查询score表中的最高分的学生学号和课程号。(子查询或者排序)
mysql> select sno,cno,grade from score where grade=(select max(grade) from score);
±----±------±------+
| sno | cno | grade |
±----±------±------+
| 103 | 3-105 | 92 |
±----±------±------+
1 row in set (0.00 sec)

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

select * from course;

– -- avg()
select avg(grade) from score where cno=“3-105”;
select avg(grade) from score where cno=“3-245”;
select avg(grade) from score where cno=“6-166”;
select avg(grade) from score where cno=“9-888”;

select grade from score where cno=“3-105”;

– 我能不能够在一个sql语句中写呢？
mysql> select cno,avg(grade) from score group by cno;
±------±-----------+
| cno | avg(grade) |
±------±-----------+
| 3-105 | 85.3333 |
| 3-245 | 76.3333 |
| 6-166 | 81.6667 |
±------±-----------+
3 rows in set (0.00 sec)

– 12、查询score表中至少有2名学生选修的并以3开头的课程的平均分数。
– 使用having，而不是where (此处如需详细了解，可详细搜帖子，秒懂)
– 模糊查询使用 like"3%"

select cno,grade,count(*) from score group by cno
having count(cno)>=2 and cno like “3%”;

mysql> select cno,grade,count() from score group by cno
-> having count(cno)>=2 and cno like “3%”;
±------±------±---------+
| cno | grade | count() |
±------±------±---------+
| 3-105 | 92 | 3 |
| 3-245 | 86 | 3 |
±------±------±---------+
2 rows in set (0.00 sec)

– -- 输漏一点点，结果如下：
mysql> select cno from score group by cno
-> having count(cno)>=2 like “3%”;
±------+
| cno |
±------+
| 3-105 |
| 3-245 |
| 6-166 |
±------+
3 rows in set (0.00 sec)

– 13、查询分数大于70，小于90的sno列。
– 以下两种方式互通

mysql> select sno,grade from score
-> where grade between 70 and 90;
±----±------+
| sno | grade |
±----±------+
| 103 | 86 |
| 103 | 85 |
| 105 | 88 |
| 105 | 75 |
| 105 | 79 |
| 109 | 76 |
| 109 | 81 |
±----±------+
7 rows in set (0.00 sec)

mysql> select sno,grade from score
-> where grade>70 and grade<90;
±----±------+
| sno | grade |
±----±------+
| 103 | 86 |
| 103 | 85 |
| 105 | 88 |
| 105 | 75 |
| 105 | 79 |
| 109 | 76 |
| 109 | 81 |
±----±------+
7 rows in set (0.00 sec)

– 14、查询所有学生的sname、cno和grade列。

select sname,cno,grade from student,score
where student.sno=score.sno;

– -- 分步解析上面语句，如下：
mysql> select sno,sname from student;
±----±----------+
| sno | sname |
±----±----------+
| 101 | 曾华 |
| 102 | 匡明 |
| 103 | 王丽 |
| 104 | 李军 |
| 105 | 王芳 |
| 106 | 陆君 |
| 107 | 王尼玛 |
| 108 | 张全蛋 |
| 109 | 赵铁柱 |
±----±----------+
9 rows in set (0.00 sec)

mysql> select sno,cno,grade from score;
±----±------±------+
| sno | cno | grade |
±----±------±------+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
±----±------±------+
9 rows in set (0.00 sec)

– -- 得最终语句
mysql> select sname,cno,grade from student,score
-> where student.sno=score.sno;
±----------±------±------+
| sname | cno | grade |
±----------±------±------+
| 王丽 | 3-105 | 92 |
| 王丽 | 3-245 | 86 |
| 王丽 | 6-166 | 85 |
| 王芳 | 3-105 | 88 |
| 王芳 | 3-245 | 75 |
| 王芳 | 6-166 | 79 |
| 赵铁柱 | 3-105 | 76 |
| 赵铁柱 | 3-245 | 68 |
| 赵铁柱 | 6-166 | 81 |
±----------±------±------+
9 rows in set (0.00 sec)

– 15、查询所有学生的sno、cname和grade列。

– -- 个人先尝试，一次搞定，棒！
mysql> select sno,cname,grade from score,course
-> where course.cno=score.cno;
±----±----------------±------+
| sno | cname | grade |
±----±----------------±------+
| 103 | 计算机导论 | 92 |
| 103 | 操作系统 | 86 |
| 103 | 数字电路 | 85 |
| 105 | 计算机导论 | 88 |
| 105 | 操作系统 | 75 |
| 105 | 数字电路 | 79 |
| 109 | 计算机导论 | 76 |
| 109 | 操作系统 | 68 |
| 109 | 数字电路 | 81 |
±----±----------------±------+
9 rows in set (0.00 sec)

– -- 下面根据视频教程步骤走一遍。
mysql> select cno,cname from course;
±------±----------------+
| cno | cname |
±------±----------------+
| 3-105 | 计算机导论 |
| 3-245 | 操作系统 |
| 6-166 | 数字电路 |
| 9-888 | 高等数学 |
±------±----------------+
4 rows in set (0.00 sec)

mysql> select cno,sno,grade from score;
±------±----±------+
| cno | sno | grade |
±------±----±------+
| 3-105 | 103 | 92 |
| 3-245 | 103 | 86 |
| 6-166 | 103 | 85 |
| 3-105 | 105 | 88 |
| 3-245 | 105 | 75 |
| 6-166 | 105 | 79 |
| 3-105 | 109 | 76 |
| 3-245 | 109 | 68 |
| 6-166 | 109 | 81 |
±------±----±------+
9 rows in set (0.00 sec)

– -- 然后推导出最终完整的查询语句(详见一开头)。

– 16、查询所有学生的sname、cname和grade列。

select sname,cname,grade from score,student,course
where student.sno=score.sno and course.cno=score.cno;

– -- 个人尝试，一次搞定，无缝连接，棒！
mysql> select sname,cname,grade from score,student,course
-> where student.sno=score.sno and course.cno=score.cno;
±----------±----------------±------+
| sname | cname | grade |
±----------±----------------±------+
| 王丽 | 计算机导论 | 92 |
| 王丽 | 操作系统 | 86 |
| 王丽 | 数字电路 | 85 |
| 王芳 | 计算机导论 | 88 |
| 王芳 | 操作系统 | 75 |
| 王芳 | 数字电路 | 79 |
| 赵铁柱 | 计算机导论 | 76 |
| 赵铁柱 | 操作系统 | 68 |
| 赵铁柱 | 数字电路 | 81 |
±----------±----------------±------+
9 rows in set (0.00 sec)

– -- 尝试加上sno和cno，第一次报错。
mysql> select sname,cname,grade,sno,cno from score,student,course
-> where student.sno=score.sno and course.cno=score.cno;
ERROR 1052 (23000): Column ‘sno’ in field list is ambiguous

– -- 在sno和cno前加上表归属，成功。
mysql> select sname,cname,grade,student.sno,course.cno from score,student,course
-> where student.sno=score.sno and course.cno=score.cno;
±----------±----------------±------±----±------+
| sname | cname | grade | sno | cno |
±----------±----------------±------±----±------+
| 王丽 | 计算机导论 | 92 | 103 | 3-105 |
| 王丽 | 操作系统 | 86 | 103 | 3-245 |
| 王丽 | 数字电路 | 85 | 103 | 6-166 |
| 王芳 | 计算机导论 | 88 | 105 | 3-105 |
| 王芳 | 操作系统 | 75 | 105 | 3-245 |
| 王芳 | 数字电路 | 79 | 105 | 6-166 |
| 赵铁柱 | 计算机导论 | 76 | 109 | 3-105 |
| 赵铁柱 | 操作系统 | 68 | 109 | 3-245 |
| 赵铁柱 | 数字电路 | 81 | 109 | 6-166 |
±----------±----------------±------±----±------+
9 rows in set (0.00 sec)

– -- 也可以将sno和cno另起别名，对比一样。
mysql> select sname,cname,grade,student.sno as stu_sno,student.sno,course.cno as cou_cno,course.cno
-> from score,student,course
-> where student.sno=score.sno
-> and course.cno=score.cno;
±----------±----------------±------±--------±----±--------±------+
| sname | cname | grade | stu_sno | sno | cou_cno | cno |
±----------±----------------±------±--------±----±--------±------+
| 王丽 | 计算机导论 | 92 | 103 | 103 | 3-105 | 3-105 |
| 王丽 | 操作系统 | 86 | 103 | 103 | 3-245 | 3-245 |
| 王丽 | 数字电路 | 85 | 103 | 103 | 6-166 | 6-166 |
| 王芳 | 计算机导论 | 88 | 105 | 105 | 3-105 | 3-105 |
| 王芳 | 操作系统 | 75 | 105 | 105 | 3-245 | 3-245 |
| 王芳 | 数字电路 | 79 | 105 | 105 | 6-166 | 6-166 |
| 赵铁柱 | 计算机导论 | 76 | 109 | 109 | 3-105 | 3-105 |
| 赵铁柱 | 操作系统 | 68 | 109 | 109 | 3-245 | 3-245 |
| 赵铁柱 | 数字电路 | 81 | 109 | 109 | 6-166 | 6-166 |
±----------±----------------±------±--------±----±--------±------+
9 rows in set (0.00 sec)

– 17、查询“95031”班学生每门课的平均分。

– -- 一开始个人尝试，尝试失败，如下：
select sno,sname,cname,avg(grade) from score,course,student group by sno
where class=“95031”;

– -- 跟住视频教程老师节奏，如下：
select * from student where class=“95031”;
select sno from student where class=“95031”;

– -- 先找出sno
select * from score where sno in (select sno from student where class=“95031”);

mysql> select * from score where sno in (select sno from student where class=“95031”);
±----±------±------+
| sno | cno | grade |
±----±------±------+
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
±----±------±------+
6 rows in set (0.00 sec)

– -- 接下来找平均成绩
select cno,avg(grade),student.class
from score,student
where score.sno in (select sno from student where class=“95031”)
group by cno;

mysql> select cno,avg(grade) from score
-> where sno in (select sno from student where class=“95031”) group by cno;
±------±-----------+
| cno | avg(grade) |
±------±-----------+
| 3-105 | 82.0000 |
| 3-245 | 71.5000 |
| 6-166 | 80.0000 |
±------±-----------+
3 rows in set (0.00 sec)

– -- 可以试试如何加上class列，以下个人尝试，纠错3次，最后成功。
mysql> select cno,avg(grade),student.class
-> from score,student
-> where score.sno in (select sno from student where class=“95031”)
-> group by cno;
±------±-----------±------+
| cno | avg(grade) | class |
±------±-----------±------+
| 3-105 | 82.0000 | 95033 |
| 3-245 | 71.5000 | 95033 |
| 6-166 | 80.0000 | 95033 |
±------±-----------±------+
3 rows in set (0.00 sec)

– 18、查询选修“3-105”课程的成绩高于“109”号同学“3-105”成绩的所有同学的记录。

– -- 先个人尝试，首次成功，没有任何报错，棒！
select * from score
where grade>(select grade from score where sno=“109” and cno=“3-105”);
±----±------±------+
| sno | cno | grade |
±----±------±------+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 6-166 | 79 |
| 109 | 6-166 | 81 |
±----±------±------+
6 rows in set (0.00 sec)

– -- 随后检查发现查询结果包含了课程“3-105”以外的其他课程成绩，所以必须通过加多一个限制条件来剔除。

select * from score
where cno=“3-105” and grade>(select grade from score where sno=“109” and cno=“3-105”);
±----±------±------+
| sno | cno | grade |
±----±------±------+
| 103 | 3-105 | 92 |
| 105 | 3-105 | 88 |
±----±------±------+
2 rows in set (0.00 sec)

– 19、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

select * from score where grade>(select grade from score where sno=109 and cno=“3-105”);
±----±------±------+
| sno | cno | grade |
±----±------±------+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 6-166 | 79 |
| 109 | 6-166 | 81 |
±----±------±------+
6 rows in set (0.00 sec)

– 20、查询和学号为108、101的同学同年出生的所有同学的sno、sname和sbirthday列。
– 使用 year 函数

– 个人尝试不出来，直接跟住视频教程指导，如下：

select * from student where sno in(108,101);

mysql> select * from student where sno in(108,101);
±----±----------±-----±--------------------±------+
| sno | sname | ssex | sbirthday | class |
±----±----------±-----±--------------------±------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 108 | 张全蛋 | 男 | 1975-02-11 00:00:00 | 95031 |
±----±----------±-----±--------------------±------+
2 rows in set (0.00 sec)

mysql> select year(sbirthday) from student where sno in(108,101);
±----------------+
| year(sbirthday) |
±----------------+
| 1977 |
| 1975 |
±----------------+
2 rows in set (0.00 sec)

mysql> select sno,sname,sbirthday from student
-> where year(sbirthday) in (select year(sbirthday) from student where sno in(108,101));
±----±----------±--------------------+
| sno | sname | sbirthday |
±----±----------±--------------------+
| 101 | 曾华 | 1977-09-01 00:00:00 |
| 102 | 匡明 | 1975-10-02 00:00:00 |
| 105 | 王芳 | 1975-02-11 00:00:00 |
| 108 | 张全蛋 | 1975-02-11 00:00:00 |
±----±----------±--------------------+
4 rows in set (0.00 sec)

– 21、查询“张旭”教师任课的学生成绩。
– 多层嵌套子查询

– -- 先个人尝试，如下：

select cno,cname,tname from course,teacher where course.tno=teacher.tno;
±------±----------------±-------+
| cno | cname | tname |
±------±----------------±-------+
| 3-245 | 操作系统 | 李诚 |
| 3-105 | 计算机导论 | 王萍 |
| 9-888 | 高等数学 | 刘冰 |
| 6-166 | 数字电路 | 张旭 |
±------±----------------±-------+
4 rows in set (0.00 sec)

– -- 进一步筛选出教师“张旭”
select cno,cname,tname from course,teacher where course.tno=teacher.tno
having tname=“张旭”;
±------±-------------±-------+
| cno | cname | tname |
±------±-------------±-------+
| 6-166 | 数字电路 | 张旭 |
±------±-------------±-------+
1 row in set (0.00 sec)

– -- 开始选学生成绩，但尝试失败。
mysql> select * from score
-> where cno=(select sno from (select cno,cname,tname from course,teacher where course.tno=teacher.tno
-> having tname=“张旭”));
ERROR 1248 (42000): Every derived table must have its own alias

– 开始跟住视频教程节奏走。

select * from teacher where tname=“张旭”;
±----±-------±-----±--------------------±-------±----------------+
| tno | tname | tsex | tbirthday | prof | depart |
±----±-------±-----±--------------------±-------±----------------+
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
±----±-------±-----±--------------------±-------±----------------+
1 row in set (0.00 sec)

select cno from course where tno=(select tno from teacher where tname=“张旭”);
±------+
| cno |
±------+
| 6-166 |
±------+
1 row in set (0.00 sec)

select * from score
where cno=(select cno from course where tno=(select tno from teacher where tname=“张旭”));
±----±------±------+
| sno | cno | grade |
±----±------±------+
| 103 | 6-166 | 85 |
| 105 | 6-166 | 79 |
| 109 | 6-166 | 81 |
±----±------±------+
3 rows in set (0.00 sec)

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

– -- 先个人尝试
select cno,count() from score group by cno;
±------±---------+
| cno | count() |
±------±---------+
| 3-105 | 3 |
| 3-245 | 3 |
| 6-166 | 3 |
±------±---------+
3 rows in set (0.00 sec)

– -- 个人尝试失败
mysql> select cno from score
-> where count(select count() from score group by cno)>5;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select count() from score group by cno)>5’ at line 2

– 跟住视频教程节奏走：

select * from score;

– 发现最多每门课程只有三位同学选修，所以要先添加一些数据。

insert into score values(“101”,“3-105”,“92”);
insert into score values(“102”,“3-105”,“91”);
insert into score values(“104”,“3-105”,“89”);

select cno from score group by cno having count(*)>5;
±------+
| cno |
±------+
| 3-105 |
±------+
1 row in set (0.00 sec)

– -- 个人悄悄尝试了一下，不添加“group by cno”居然也能得出一样的结果，如下：
select cno from score
having count(*)>5;
±------+
| cno |
±------+
| 3-105 |
±------+
1 row in set (0.00 sec)

– 按照视频教程继续。

select * from teacher;

select tno from course where cno=(select cno from score having count(*)>5);

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

select * from teacher
where tno=(select tno from course where cno=(select cno from score having count(*)>5));

– 23、查询95033班和95031班全体学生的记录。
– in 表示或者

insert into student values(“110”,“张飞”,“男”,“1974-06-03”,“95038”);

select * from student;

select * from student where class in (“95031”,“95033”);
±----±----------±-----±--------------------±------+
| sno | sname | ssex | 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-11 00:00:00 | 95031 |
| 106 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 108 | 张全蛋 | 男 | 1975-02-11 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
±----±----------±-----±--------------------±------+
9 rows in set (0.00 sec)

– 24、查询存在有85分以上的课程Cno。
– where 条件

– -- 个人尝试，一次搞定。

select * from score where grade >85;

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

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

– -- 先个人尝试

select * from teacher where depart=“计算机系”;
±----±-------±-----±--------------------±----------±-------------+
| tno | tname | tsex | tbirthday | prof | depart |
±----±-------±-----±--------------------±----------±-------------+
| 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 |
±----±-------±-----±--------------------±----------±-------------+
2 rows in set (0.00 sec)

select tno from teacher where depart=“计算机系”;

select cno from course where tno in(select tno from teacher where depart=“计算机系”);
±------+
| cno |
±------+
| 3-245 |
| 3-105 |
±------+
2 rows in set (0.00 sec)

– -- 感觉要成功了，结果报错了。
selcet * from score
where cno in(select cno from course where tno in(select tno from teacher where depart=“计算机系”));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘selcet * from score
where cno in(select cno from course where tno in(select tno’ at line 1

– 下面跟住视频教程节奏

– -- 第一步与个人尝试一致
select * from teacher where depart=“计算机系”;

– -- 第二步，还是与个人尝试接近一致
select * from course where tno in(select tno from teacher where depart=“计算机系”);
±------±----------------±----+
| cno | cname | tno |
±------±----------------±----+
| 3-245 | 操作系统 | 804 |
| 3-105 | 计算机导论 | 825 |
±------±----------------±----+
2 rows in set (0.00 sec)

– -- 将上一步的 * 改成 cno，继续代入查询语句，感觉还是和个人尝试的几乎完全一致啊，
– -- 然而视频教程步骤跑出结果了，个人尝试失败了
select * from score
where cno in(select cno from course where tno in(select tno from teacher where depart=“计算机系”));
±----±------±------+
| sno | cno | grade |
±----±------±------+
| 103 | 3-245 | 86 |
| 105 | 3-245 | 75 |
| 109 | 3-245 | 68 |
| 101 | 3-105 | 92 |
| 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)

– -- 原因在哪呢？对比了好几遍语句，完全一致。脑壳疼。
– -- 再对比，还是一致。网上搜错误代码，确实有这情况存在，但是就是找不出差别啊。。。
– -- 算了先放下，以后遇到再解决，说不定就秒懂了。2019.3.12

– 26、查询“计算机系”与“电子工程系”不同职称的教师的tname和prof。
– union 求并集
– not in()

– -- 先个人尝试，看样子是搞定了。
select * from teacher where depart in(“计算机系”,“电子工程系”);
±----±-------±-----±--------------------±----------±----------------+
| tno | tname | tsex | tbirthday | prof | depart |
±----±-------±-----±--------------------±----------±----------------+
| 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 |
| 831 | 刘冰 | 女 | 1977-08-14 00:00:00 | 副教授 | 电子工程系 |
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
±----±-------±-----±--------------------±----------±----------------+
4 rows in set (0.00 sec)

select tname,prof from teacher where depart in(“计算机系”,“电子工程系”);
±-------±----------+
| tname | prof |
±-------±----------+
| 李诚 | 副教授 |
| 王萍 | 助教 |
| 刘冰 | 副教授 |
| 张旭 | 讲师 |
±-------±----------+
4 rows in set (0.00 sec)

– -- 然后来看看视频教程，看完之后发现个人尝试部分解题解错了。

select prof from teacher where depart=“电子工程系”;

select * from teacher
where depart=“计算机系” and prof not in(select prof from teacher where depart=“电子工程系”);
±----±-------±-----±--------------------±-------±-------------+
| tno | tname | tsex | tbirthday | prof | depart |
±----±-------±-----±--------------------±-------±-------------+
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 |
±----±-------±-----±--------------------±-------±-------------+
1 row in set (0.00 sec)

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)

– -- 然后使用 union 把它们联结起来

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 |
±----±-------±-----±--------------------±-------±----------------+
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 |
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
±----±-------±-----±--------------------±-------±----------------+
2 rows in set (0.01 sec)

– 27、查询选修编号为“3-105”课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Grade，
– 并按 grade 从高到低依次排序。
– any 语句表示至少

– -- 先个人尝试
select * from score where cno=“3-245”;
±----±------±------+
| sno | cno | grade |
±----±------±------+
| 103 | 3-245 | 86 |
| 105 | 3-245 | 75 |
| 109 | 3-245 | 68 |
±----±------±------+
3 rows in set (0.00 sec)

– -- 不懂了，直接看视频教程。

select * from score where cno=“3-245”;
±----±------±------+
| sno | cno | grade |
±----±------±------+
| 103 | 3-245 | 86 |
| 105 | 3-245 | 75 |
| 109 | 3-245 | 68 |
±----±------±------+
3 rows in set (0.00 sec)

select * from score where cno=“3-105”;
±----±------±------+
| sno | cno | grade |
±----±------±------+
| 101 | 3-105 | 92 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
±----±------±------+
6 rows in set (0.00 sec)

– 至少？大于其中至少一个，any

– -- 又开始个人尝试，虽然没报错但感觉结果不对。
select * from score
where cno=“3-105”
having grade>any(select grade from score where cno=“3-245”);
±----±------±------+
| sno | cno | grade |
±----±------±------+
| 101 | 3-105 | 92 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
±----±------±------+
6 rows in set (0.00 sec)

– 继续回到视频教程，发现，教程用“and”替换个人尝试的“having”，结果一样。
select * from score
where cno=“3-105”
and grade>any(select grade from score where cno=“3-245”);
±----±------±------+
| sno | cno | grade |
±----±------±------+
| 101 | 3-105 | 92 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
±----±------±------+
6 rows in set (0.00 sec)

select * from score
where cno=“3-105”
and grade>any(select grade from score where cno=“3-245”)
order by grade desc;
±----±------±------+
| sno | cno | grade |
±----±------±------+
| 101 | 3-105 | 92 |
| 103 | 3-105 | 92 |
| 102 | 3-105 | 91 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
±----±------±------+
6 rows in set (0.00 sec)

– 28、查询选修编号为“3-105”课程且成绩高于选修编号为“3-245”的同学的Cno、Sno和Grade。
– 且？ all 表示所有的关系

– -- 老规矩，先个人尝试，出了个结果。
select * from score
where cno=“3-105”
and grade>all(select grade from score where cno=“3-245”);
±----±------±------+
| sno | cno | grade |
±----±------±------+
| 101 | 3-105 | 92 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
±----±------±------+
5 rows in set (0.00 sec)

– -- 接着看视频教程，完全一致。

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

– 别名？ as
– 联合？ union

select tname as name,tsex as sex,tbirthday as birthday from teacher
union
select sname,ssex,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-11 00:00:00 |
| 陆君 | 男 | 1974-06-03 00:00:00 |
| 王尼玛 | 男 | 1976-02-20 00:00:00 |
| 张全蛋 | 男 | 1975-02-11 00:00:00 |
| 赵铁柱 | 男 | 1974-06-03 00:00:00 |
| 张飞 | 男 | 1974-06-03 00:00:00 |
±----------±----±--------------------+
14 rows in set (0.00 sec)

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

– union 求并集

– -- 个人尝试，直出。
select tname as name,tsex as sex,tbirthday as birthday from teacher where tsex=“女”
union
select sname,ssex,sbirthday from student where ssex=“女”;
±-------±----±--------------------+
| name | sex | birthday |
±-------±----±--------------------+
| 王萍 | 女 | 1972-05-05 00:00:00 |
| 刘冰 | 女 | 1977-08-14 00:00:00 |
| 王丽 | 女 | 1976-01-23 00:00:00 |
| 王芳 | 女 | 1975-02-11 00:00:00 |
±-------±----±--------------------+
4 rows in set (0.00 sec)

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

– 复制表数据（尚不熟悉，需回顾）

select cno,avg(grade) from score group by cno;
±------±-----------+
| cno | avg(grade) |
±------±-----------+
| 3-105 | 88.0000 |
| 3-245 | 76.3333 |
| 6-166 | 81.6667 |
±------±-----------+
3 rows in set (0.00 sec)

– -- 继续尝试失败
mysql> select sno,grade from score
-> where grade<in(select cno,avg(grade) from score group by cno);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘in(select cno,avg(grade) from score group by cno)’ at line 2
mysql> select sno,grade from score
-> where grade<(select avg(grade) from score group by cno);
ERROR 1242 (21000): Subquery returns more than 1 row
mysql> select sno,grade from score
-> where grade<(select avg(grade) from score group by cno) group by cno;
ERROR 1242 (21000): Subquery returns more than 1 row

– -- 转视频教程

select * from score;

– a（下表为a表，另外复制一个表，标识为“b”）
±----±------±------+
| sno | cno | grade |
±----±------±------+
| 101 | 3-105 | 92 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 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 |
±----±------±------+
12 rows in set (0.00 sec)

select * from score a
where grade <(select avg(grade) from score b where a.cno=b.cno);
±----±------±------+
| sno | cno | grade |
±----±------±------+
| 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)

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

– -- 课程表里安排了课程
select * from course;
±------±----------------±----+
| cno | cname | tno |
±------±----------------±----+
| 3-105 | 计算机导论 | 825 |
| 3-245 | 操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
| 9-888 | 高等数学 | 831 |
±------±----------------±----+
4 rows in set (0.00 sec)

33、查询至少有两名男生的班号。
– 条件加分组

select * from student;
±----±----------±-----±--------------------±------+
| sno | sname | ssex | 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-11 00:00:00 | 95031 |
| 106 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 108 | 张全蛋 | 男 | 1975-02-11 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)

– -- 这个错误，漏了 group by class
mysql> select class from student where ssex=“男”
-> having count(*)>1;
±------+
| class |
±------+
| 95033 |
±------+
1 row in set (0.00 sec)

– -- 以下正确
mysql> select class from student where ssex=“男” group by class
-> having count(*)>1;
±------+
| class |
±------+
| 95031 |
| 95033 |
±------+
2 rows in set (0.00 sec)

– 34、查询student表中不姓“王”的同学记录。
– not like 模糊查询

select * from student;

select * from student where sname not like"王%";
±----±----------±-----±--------------------±------+
| sno | sname | ssex | 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-11 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.00 sec)

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

– 年龄 = 当前年份year(now())-出生年份year(sbirthday)

select year(now());
±------------+
| year(now()) |
±------------+
| 2019 |
±------------+
1 row in set (0.00 sec)

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

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

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

select sbirthday from student order by sbirthday;

select max(sbirthday) as maxbirth, min(sbirthday) as minbirth from student;
±--------------------±--------------------+
| maxbirth | minbirth |
±--------------------±--------------------+
| 1977-09-01 00:00:00 | 1974-06-03 00:00:00 |
±--------------------±--------------------+
1 row in set (0.00 sec)

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

select * from student order by class desc,sbirthday;
±----±----------±-----±--------------------±------+
| sno | sname | ssex | 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-11 00:00:00 | 95031 |
| 108 | 张全蛋 | 男 | 1975-02-11 00:00:00 | 95031 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
±----±----------±-----±--------------------±------+
10 rows in set (0.00 sec)

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

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=(select tno from teacher where tsex=“男”);
ERROR 1242 (21000): Subquery returns more than 1 row
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)

– 39、查询最高分同学的sno、cno和degree列。
– max函数与……

select max(grade) from score;

select * from score where grade=(select max(grade) from score);
±----±------±------+
| sno | cno | grade |
±----±------±------+
| 101 | 3-105 | 92 |
| 103 | 3-105 | 92 |
±----±------±------+
2 rows in set (0.00 sec)

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

select ssex from student where sname=“李军”；

select sname from student
where ssex=(select ssex from student where sname=“李军”);
±----------+
| sname |
±----------+
| 曾华 |
| 匡明 |
| 李军 |
| 陆君 |
| 王尼玛 |
| 张全蛋 |
| 赵铁柱 |
| 张飞 |
±----------+
8 rows in set (0.01 sec)

– 41、查询和李军同性别并同班的同学的sname。
– 子查询

select sname from student
where ssex=(select ssex from student where sname=“李军”)
and class=(select class from student where sname=“李军”);
±----------+
| sname |
±----------+
| 曾华 |
| 李军 |
| 王尼玛 |
±----------+
3 rows in set (0.01 sec)

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

select * from student where ssex=“男”;
±----±----------±-----±--------------------±------+
| sno | sname | ssex | 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 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 108 | 张全蛋 | 男 | 1975-02-11 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
| 110 | 张飞 | 男 | 1974-06-03 00:00:00 | 95038 |
±----±----------±-----±--------------------±------+
8 rows in set (0.00 sec)

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

– 个人尝试，先出错后更正，注意 “=” 和 “in”。
mysql> select * from score
-> where sno=(select sno from student where ssex=“男”)
-> and cno=(select cno from course where cname=“计算机导论”);
ERROR 1242 (21000): Subquery returns more than 1 row
mysql> select * from score
-> where sno in (select sno from student where ssex=“男”)
-> and cno in (select cno from course where cname=“计算机导论”);
±----±------±------+
| sno | cno | grade |
±----±------±------+
| 101 | 3-105 | 92 |
| 102 | 3-105 | 91 |
| 104 | 3-105 | 89 |
| 109 | 3-105 | 76 |
±----±------±------+
4 rows in set (0.00 sec)

– 42、假设使用下面命令建立了一个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 和 rank 列。

– 按等级查询

select sno,cno,grade.grade from score,grade where score.grade 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 |
±----±------±------+
12 rows in set (0.00 sec)

– -- 其实让新建表格里面的字段与之前的表的字段不重名（grade），可能更方便，如下：

– -- 先删除原grade表
drop table grade;

– -- 然后重新建表，更改字段名，使之不与历史字段重复( 本次将grade 改成 grade0 )

create table grade(
low int(3),
upp int(3),
grade0 char(1)
);

– -- 现查询所有同学的sno、cno 和 rank 列
select sno,cno,grade0 from score,grade where grade between low and upp;
±----±------±-------+
| sno | cno | grade0 |
±----±------±-------+
| 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 |
±----±------±-------+
12 rows in set (0.00 sec)

SophiaSSSSS

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql学习笔记-ch4-查询练习

– mysql查询练习cd C:\Program Files\MySQL\MySQL Server 5.7\bin– 学生表– Student– 学号– 姓名– 性别– 出生年月日– 所在班级create table student(sno varchar(20) primary key,sname varchar(20) not null,ssx varchar(10) ...
复制链接

扫一扫