SQL语句编写的练习（MySQL）
一、建表
1、学生表（Student）
学号 | 姓名 | 性别 | 出生年月 | 所在班级
create table Student(
sno varchar(20) primary key,
sname varchar(20) not null,
ssex varchar(10) not null,
sbirthday datetime,
class varchar(20)
);
2、课程表 （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、成绩表（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)
);
4、教师表（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
);
二、添加数据
1、添加学生信息
insert into student values('108','小红','女','1997-02-23','95033');
insert into student values('107','小黑','男','1597-04-23','95034');
insert into student values('103','张三','女','1999-03-23','95036');
insert into student values('106','李四','男','2000-02-23','95035');
insert into student values('104','李白','男','1897-12-23','95034');
insert into student values('105','李芳','女','1997-02-23','95032');
insert into student values('101','大明','男','1597-04-23','95035');
insert into student values('102','许仙','女','1499-03-23','95033');
insert into student values('109','小芳','男','2010-02-23','95037');
2、添加教师信息
insert into teacher values('804','李成','男','1984-12-03','副教授','计算机系');
insert into teacher values('856','张旭','男','1974-11-03','教授','电子工程系');
insert into teacher values('825','王萍','女','1984-12-03','助教','计算机系');
insert into teacher values('831','刘冰','女','1994-12-03','讲师','电子工程系');
3、添加课程表信息
insert into course values('3-105','计算机导论','825');
insert into course values('3-245','c操作系统','804');
insert into course values('6-166','数字电路','856');
insert into course values('9-888','高等数学','831');
4、添加成绩表信息
insert into score values('108','3-245','87');
insert into score values('105','3-245','75');
insert into score values('109','3-245','68');
insert into score values('103','3-105','89');
insert into score values('105','3-105','88');
insert into score values('109','3-105','65');
insert into score values('103','3-105','64');
insert into score values('105','3-105','94');
insert into score values('109','6-166','87');
insert into score values('103','6-166','67');
insert into score values('105','6-166','98');
insert into score values('109','9-888','87');
三、查询练习
1、查询student表的所有记录
mysql> select * from student;
±----±-------±-----±--------------------±------+
| sno | sname | ssex | sbirthday | class |
±----±-------±-----±--------------------±------+
| 101 | 大明 | 男 | 1597-04-23 00:00:00 | 95035 |
| 102 | 许仙 | 女 | 1499-03-23 00:00:00 | 95033 |
| 103 | 张三 | 女 | 1999-03-23 00:00:00 | 95036 |
| 104 | 李白 | 男 | 1897-12-23 00:00:00 | 95034 |
| 105 | 李芳 | 女 | 1997-02-23 00:00:00 | 95032 |
| 106 | 李四 | 男 | 2000-02-23 00:00:00 | 95035 |
| 107 | 小黑 | 男 | 1597-04-23 00:00:00 | 95034 |
| 108 | 小红 | 女 | 1997-02-23 00:00:00 | 95033 |
| 109 | 小芳 | 男 | 2010-02-23 00:00:00 | 95037 |
±----±-------±-----±--------------------±------+
9 rows in set (0.01 sec)
2、查询student表中的所有记录的sname、ssex\class
select sname,ssex,class from student;
mysql> select sname,ssex,class from student;
±-------±-----±------+
| sname | ssex | class |
±-------±-----±------+
| 大明 | 男 | 95035 |
| 许仙 | 女 | 95033 |
| 张三 | 女 | 95036 |
| 李白 | 男 | 95034 |
| 李芳 | 女 | 95032 |
| 李四 | 男 | 95035 |
| 小黑 | 男 | 95034 |
| 小红 | 女 | 95033 |
| 小芳 | 男 | 95037 |
±-------±-----±------+
9 rows in set (0.00 sec)
3、查询教师所有的单位不重复的depart列
distinct排除重复
select distinct depart from teacher;
mysql> select distinct depart from teacher;
±----------------+
| depart |
±----------------+
| 计算机系 |
| 电子工程系 |
±----------------+
2 rows in set (0.10 sec)
4、查询score表中成绩在60到80的所有的记录
查询区间 between …and…
select * from score where degree>60 and degree<80;
mysql> select * from score where degree>60 and degree<80;
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 103 | 6-166 | 67 |
| 105 | 3-245 | 75 |
| 109 | 3-105 | 65 |
| 109 | 3-245 | 68 |
±----±------±-------+
4 rows in set (0.34 sec)
select * from score where degree between 60 and 80;
mysql> select * from score where degree between 60 and 80;
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 103 | 6-166 | 67 |
| 105 | 3-245 | 75 |
| 109 | 3-105 | 65 |
| 109 | 3-245 | 68 |
±----±------±-------+
4 rows in set (0.01 sec)
5、查询score表中成绩为85,86或88的记录。
表或者关系的查询 in
select * from score where degree in(85,86,88);
mysql> select * from score where degree in(85,86,88);
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 105 | 3-105 | 88 |
±----±------±-------+
1 row in set (0.35 sec)
6、查询student表中95031班或者性别为女的学生记录
select * from student where class='95033' or ssex='女';
mysql> select * from student where class=‘95033’ or ssex=‘女’;
±----±-------±-----±--------------------±------+
| sno | sname | ssex | sbirthday | class |
±----±-------±-----±--------------------±------+
| 102 | 刘思 | 男 | 1499-03-23 00:00:00 | 95033 |
| 103 | 张三 | 女 | 1999-03-23 00:00:00 | 95036 |
| 105 | 李芳 | 女 | 1997-02-23 00:00:00 | 95032 |
| 108 | 小红 | 女 | 1997-02-23 00:00:00 | 95033 |
±----±-------±-----±--------------------±------+
4 rows in set (0.00 sec)
7、以class降序查询student表的所有记录
升序 降序
select * from student order by class desc;
mysql> select * from student order by class desc;
±----±-------±-----±--------------------±------+
| sno | sname | ssex | sbirthday | class |
±----±-------±-----±--------------------±------+
| 109 | 小芳 | 男 | 2010-02-23 00:00:00 | 95037 |
| 103 | 张三 | 女 | 1999-03-23 00:00:00 | 95036 |
| 101 | 大明 | 男 | 1597-04-23 00:00:00 | 95035 |
| 106 | 李四 | 男 | 2000-02-23 00:00:00 | 95035 |
| 104 | 李白 | 男 | 1897-12-23 00:00:00 | 95034 |
| 107 | 小黑 | 男 | 1597-04-23 00:00:00 | 95034 |
| 102 | 刘思 | 男 | 1499-03-23 00:00:00 | 95033 |
| 108 | 小红 | 女 | 1997-02-23 00:00:00 | 95033 |
| 105 | 李芳 | 女 | 1997-02-23 00:00:00 | 95032 |
±----±-------±-----±--------------------±------+
9 rows in set (0.01 sec)
–升序 asc moren
mysql> select * from student order by sno asc;
±----±-------±-----±--------------------±------+
| sno | sname | ssex | sbirthday | class |
±----±-------±-----±--------------------±------+
| 101 | 大明 | 男 | 1597-04-23 00:00:00 | 95035 |
| 102 | 刘思 | 男 | 1499-03-23 00:00:00 | 95033 |
| 103 | 张三 | 女 | 1999-03-23 00:00:00 | 95036 |
| 104 | 李白 | 男 | 1897-12-23 00:00:00 | 95034 |
| 105 | 李芳 | 女 | 1997-02-23 00:00:00 | 95032 |
| 106 | 李四 | 男 | 2000-02-23 00:00:00 | 95035 |
| 107 | 小黑 | 男 | 1597-04-23 00:00:00 | 95034 |
| 108 | 小红 | 女 | 1997-02-23 00:00:00 | 95033 |
| 109 | 小芳 | 男 | 2010-02-23 00:00:00 | 95037 |
±----±-------±-----±--------------------±------+
9 rows in set (0.00 sec)
8、以cno升序，degree降序查询score表的所有记录
select * from score order by cno ,degree desc;
mysql> select * from score order by cno ,degree desc;
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 103 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 65 |
| 108 | 3-245 | 87 |
| 105 | 3-245 | 75 |
| 109 | 3-245 | 68 |
| 105 | 6-166 | 98 |
| 109 | 6-166 | 87 |
| 103 | 6-166 | 67 |
| 109 | 9-888 | 87 |
±----±------±-------+
10 rows in set (0.00 sec)
9、查询 9501班的学生人数
统计count
select count(*) from student where class='95033';
mysql> select count() from student where class=‘95033’;
±---------+
| count() |
±---------+
| 2 |
±---------+
1 row in set (0.01 sec)
10、查询score表中的最高分的学生学号和课程号（子查询或者排序）
select sno,cno from score where degree=(select max(degree) from score);
–1、找到最高分
select max(degree) from score
–2、根据最高分查询学号和课程号
mysql> select sno,cno from score where degree=(select max(degree) from score);
±----±------+
| sno | cno |
±----±------+
| 105 | 6-166 |
±----±------+
1 row in set (0.01 sec)
select sno,cno ,degree from score where degree=(select max(degree) from score);
mysql> select sno,cno ,degree from score where degree=(select max(degree) from score);
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 105 | 6-166 | 98 |
±----±------±-------+
1 row in set (0.00 sec)
–根据分数最少值查询学号和课程号
select sno,cno ,degree from score where degree=(select min(degree) from score);
select sno,cno ,degree from score order by degree desc limit 0,1;
–limit 第一个数字表示从多少开始
–第二个数字代表查询几条数据
mysql> select sno,cno ,degree from score order by degree desc limit 0,1;
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 105 | 6-166 | 98 |
±----±------±-------+
1 row in set (0.00 sec)
mysql> select * from score;
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 103 | 3-105 | 89 |
| 103 | 6-166 | 67 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 98 |
| 108 | 3-245 | 87 |
| 109 | 3-105 | 65 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 87 |
| 109 | 9-888 | 87 |
±----±------±-------+
10 rows in set (0.00 sec)
11、计算每门课的平均成绩
–avg
select avg(degree) from score where cno='3-105';
mysql> select avg(degree) from score where cno=‘3-105’;
±------------+
| avg(degree) |
±------------+
| 80.6667 |
±------------+
1 row in set (0.00 sec)
mysql>
select cno,avg(degree) from score group by cno;
–group by 分组
mysql> select cno,avg(degree) from score group by cno;
±------±------------+
| cno | avg(degree) |
±------±------------+
| 3-105 | 80.6667 |
| 3-245 | 76.6667 |
| 6-166 | 84.0000 |
| 9-888 | 87.0000 |
±------±------------+
4 rows in set (0.00 sec)
12、查询score表中至少有两名同学选修的并且以3开头的课程的平均分
select cno,avg(degree) ,count(*) from score
group by cno
having count(cno)>=2 and
cno like '3%';
–模糊查询like
mysql> select cno,avg(degree) ,count() from score group by cno having count(cno)>=2 and cno like ‘3%’;
±------±------------±---------+
| cno | avg(degree) | count() |
±------±------------±---------+
| 3-105 | 80.6667 | 3 |
| 3-245 | 76.6667 | 3 |
±------±------------±---------+
2 rows in set (0.00 sec)
mysql>
13、查询分数大于70小于90的sno列
select sno from score where degree between 70 and 90;
mysql> select sno from score where degree between 70 and 90;
±----+
| sno |
±----+
| 103 |
| 105 |
| 105 |
| 108 |
| 109 |
| 109 |
±----+
6 rows in set (0.00 sec)
select sno,degree from score where degree between 70 and 90;
14、查询所有学生的sname、con、和degree列
select sno,sname from student;
mysql> select sno,sname from student;
±----±-------+
| sno | sname |
±----±-------+
| 101 | 大明 |
| 102 | 刘思 |
| 103 | 张三 |
| 104 | 李白 |
| 105 | 李芳 |
| 106 | 李四 |
| 107 | 小黑 |
| 108 | 小红 |
| 109 | 小芳 |
±----±-------+
9 rows in set (0.00 sec)
select sno,cno,degree from score;
mysql> select sno,cno,degree from score;
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 103 | 3-105 | 89 |
| 103 | 6-166 | 67 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 98 |
| 108 | 3-245 | 87 |
| 109 | 3-105 | 65 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 87 |
| 109 | 9-888 | 87 |
±----±------±-------+
10 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 |
±-------±------±-------+
| 张三 | 3-105 | 89 |
| 张三 | 6-166 | 67 |
| 李芳 | 3-105 | 88 |
| 李芳 | 3-245 | 75 |
| 李芳 | 6-166 | 98 |
| 小红 | 3-245 | 87 |
| 小芳 | 3-105 | 65 |
| 小芳 | 3-245 | 68 |
| 小芳 | 6-166 | 87 |
| 小芳 | 9-888 | 87 |
±-------±------±-------+
10 rows in set (0.00 sec)
15、查询所有学生的sno,cname和degree列
select sno,cname,degree from course,score where score.cno=course.cno;
mysql> select sno,cname,degree from course,score where score.cno=course.cno;
±----±----------------±-------+
| sno | cname | degree |
±----±----------------±-------+
| 103 | 计算机导论 | 89 |
| 103 | 数字电路 | 67 |
| 105 | 计算机导论 | 88 |
| 105 | c操作系统 | 75 |
| 105 | 数字电路 | 98 |
| 108 | c操作系统 | 87 |
| 109 | 计算机导论 | 65 |
| 109 | c操作系统 | 68 |
| 109 | 数字电路 | 87 |
| 109 | 高等数学 | 87 |
±----±----------------±-------+
10 rows in set (0.34 sec)
16、查询所有学生的sname,cname和degree列
select sno,sname from student;
mysql> select sno,sname from student;
±----±-------+
| sno | sname |
±----±-------+
| 101 | 大明 |
| 102 | 刘思 |
| 103 | 张三 |
| 104 | 李白 |
| 105 | 李芳 |
| 106 | 李四 |
| 107 | 小黑 |
| 108 | 小红 |
| 109 | 小芳 |
±----±-------+
9 rows in set (0.00 sec)
select sname,cname,degree from student,course,score where 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 |
±-------±----------------±-------+
| 张三 | 计算机导论 | 89 |
| 张三 | 数字电路 | 67 |
| 李芳 | 计算机导论 | 88 |
| 李芳 | c操作系统 | 75 |
| 李芳 | 数字电路 | 98 |
| 小红 | c操作系统 | 87 |
| 小芳 | 计算机导论 | 65 |
| 小芳 | c操作系统 | 68 |
| 小芳 | 数字电路 | 87 |
| 小芳 | 高等数学 | 87 |
±-------±----------------±-------+
10 rows in set (0.00 sec)
select sname,cname,degree,student.sno,course.cno from student,course,score where student.sno=score.sno and course.cno=score.cno;
mysql> select sname,cname,degree,student.sno,course.cno from student,course,score where student.sno=score.sno and course.cno=score.cno;
±-------±----------------±-------±----±------+
| sname | cname | degree | sno | cno |
±-------±----------------±-------±----±------+
| 张三 | 计算机导论 | 89 | 103 | 3-105 |
| 张三 | 数字电路 | 67 | 103 | 6-166 |
| 李芳 | 计算机导论 | 88 | 105 | 3-105 |
| 李芳 | c操作系统 | 75 | 105 | 3-245 |
| 李芳 | 数字电路 | 98 | 105 | 6-166 |
| 小红 | c操作系统 | 87 | 108 | 3-245 |
| 小芳 | 计算机导论 | 65 | 109 | 3-105 |
| 小芳 | c操作系统 | 68 | 109 | 3-245 |
| 小芳 | 数字电路 | 87 | 109 | 6-166 |
| 小芳 | 高等数学 | 87 | 109 | 9-888 |
±-------±----------------±-------±----±------+
10 rows in set (0.00 sec)
17、 查询95031班学生每门课的平均分
mysql> select * from student where class=‘95037’;
±----±-------±-----±--------------------±------+
| sno | sname | ssex | sbirthday | class |
±----±-------±-----±--------------------±------+
| 109 | 小芳 | 男 | 2010-02-23 00:00:00 | 95037 |
±----±-------±-----±--------------------±------+
1 row in set (0.00 sec)
select * from score where sno in(select sno from student where class=‘95037’);
mysql> select * from score where sno in(select sno from student where class=‘95037’);
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 109 | 3-105 | 65 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 87 |
| 109 | 9-888 | 87 |
±----±------±-------+
4 rows in set (0.00 sec)
select cno,avg(degree)
from score
where sno in(select sno from student where class='95037')
group by cno;
mysql> select cno,avg(degree)
-> from score
-> where sno in(select sno from student where class=‘95037’)
-> group by cno;
±------±------------+
| cno | avg(degree) |
±------±------------+
| 3-105 | 65.0000 |
| 3-245 | 68.0000 |
| 6-166 | 87.0000 |
| 9-888 | 87.0000 |
±------±------------+
4 rows in set (0.00 sec)
18、查询选修3-105课程的成绩高于109号同学3-105成绩的所有同学的记录
select degree from score where sno='109' and cno='3-105';
mysql> select degree from score where sno=‘109’ and cno=‘3-105’;
±-------+
| degree |
±-------+
| 65 |
±-------+
1 row in set (0.00 sec)
select * from score where cno=‘3-105’ and degree>(select degree from score where sno=‘109’ and cno=‘3-105’);
mysql> select * from score where cno=‘3-105’ and degree>(select degree from score where sno=‘109’ and cno=‘3-105’);
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 103 | 3-105 | 89 |
| 105 | 3-105 | 88 |
±----±------±-------+
2 rows in set (0.00 sec)
19、查询成绩高于学号为109 、课程号为3-105的成绩的所有记录
select * from score where degree>(select degree from score where sno='109' and cno='3-105');
mysql> select * from score where degree>(select degree from score where sno=‘109’ and cno=‘3-105’);
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 103 | 3-105 | 89 |
| 103 | 6-166 | 67 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 98 |
| 108 | 3-245 | 87 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 87 |
| 109 | 9-888 | 87 |
±----±------±-------+
9 rows in set (0.00 sec)
20、查询和学号为108、101的同学同年出生的所有学生的sno、sname和sbirthday列
select year(sbirthday) from student where sno in(108,109);
mysql> select year(sbirthday) from student where sno in(108,109);
±----------------+
| year(sbirthday) |
±----------------+
| 1997 |
| 2010 |
±----------------+
2 rows in set (0.11 sec)
select * from student
where year(sbirthday)in(select year(sbirthday) from student where sno in(108,109));
mysql> select * from student
-> where year(sbirthday)in(select year(sbirthday) from student where sno in(108,109));
±----±-------±-----±--------------------±------+
| sno | sname | ssex | sbirthday | class |
±----±-------±-----±--------------------±------+
| 105 | 李芳 | 女 | 1997-02-23 00:00:00 | 95032 |
| 108 | 小红 | 女 | 1997-02-23 00:00:00 | 95033 |
| 109 | 小芳 | 男 | 2010-02-23 00:00:00 | 95037 |
±----±-------±-----±--------------------±------+
3 rows in set (0.00 sec)
21、查询张旭教师任课的学生成绩。
select * from teacher where tname='张旭';
mysql> select * from teacher where tname=‘张旭’;
±----±-------±-----±--------------------±-------±----------------+
| tno | tname | tsex | tbirthday | prof | depart |
±----±-------±-----±--------------------±-------±----------------+
| 856 | 张旭 | 男 | 1974-11-03 00:00:00 | 教授 | 电子工程系 |
±----±-------±-----±--------------------±-------±----------------+
1 row in set (0.00 sec)
mysql> select * from course where tno=(select tno from teacher where tname=‘张旭’ );
±------±-------------±----+
| cno | cname | tno |
±------±-------------±----+
| 6-166 | 数字电路 | 856 |
±------±-------------±----+
1 row in set (0.33 sec)
select * from score where cno=(select cno from course where tno=(select tno from teacher where tname=‘张旭’ ));
mysql> select * from score where cno=(select cno from course where tno=(select tno from teacher where tname=‘张旭’ ));
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 103 | 6-166 | 67 |
| 105 | 6-166 | 98 |
| 109 | 6-166 | 87 |
±----±------±-------+
3 rows in set (0.00 sec)
22、查询选修某课程的同学人数多于2人的教师姓名
select * from score group by cno having count(cno)>2 ;
mysql> select * from score group by cno having count(cno)>2 ;
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 103 | 3-105 | 89 |
| 105 | 3-245 | 75 |
| 103 | 6-166 | 67 |
±----±------±-------+
3 rows in set (0.00 sec)
select * from course where cno in(select cno from score group by cno having count(cno)>2);
mysql> select * from course where cno in(select cno from score group by cno having count(cno)>2);
±------±----------------±----+
| cno | cname | tno |
±------±----------------±----+
| 3-105 | 计算机导论 | 825 |
| 3-245 | c操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
±------±----------------±----+
3 rows in set (0.00 sec)
select tname from teacher where tno in(select tno from course where cno in(select cno from score group by cno having count(cno)>2));
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)>2));
±-------+
| tname |
±-------+
| 李成 |
| 王萍 |
| 张旭 |
±-------+
3 rows in set (0.00 sec)
23、查询95033班和95035班全体学生的记录
select * from student
where class in(95033,95035);
mysql> select * from student
-> where class in(95033,95035);
±----±-------±-----±--------------------±------+
| sno | sname | ssex | sbirthday | class |
±----±-------±-----±--------------------±------+
| 101 | 大明 | 男 | 1597-04-23 00:00:00 | 95035 |
| 102 | 刘思 | 男 | 1499-03-23 00:00:00 | 95033 |
| 106 | 李四 | 男 | 2000-02-23 00:00:00 | 95035 |
| 108 | 小红 | 女 | 1997-02-23 00:00:00 | 95033 |
±----±-------±-----±--------------------±------+
4 rows in set (0.00 sec)
24、查询存在有85分以上成绩的课程Cno
select * from score where degree>85;
mysql> select * from score where degree>85;
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 103 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 105 | 6-166 | 98 |
| 108 | 3-245 | 87 |
| 109 | 6-166 | 87 |
| 109 | 9-888 | 87 |
±----±------±-------+
6 rows in set (0.00 sec)
25、查询出计算机系教师所教课程的成绩表
select * from teacher where depart='计算机系';
mysql> select * from teacher where depart=‘计算机系’;
±----±-------±-----±--------------------±----------±-------------+
| tno | tname | tsex | tbirthday | prof | depart |
±----±-------±-----±--------------------±----------±-------------+
| 804 | 李成 | 男 | 1984-12-03 00:00:00 | 副教授 | 计算机系 |
| 825 | 王萍 | 女 | 1984-12-03 00:00:00 | 助教 | 计算机系 |
±----±-------±-----±--------------------±----------±-------------+
2 rows in set (0.00 sec)
select * from course where tno in(select tno from teacher where depart=‘计算机系’);
mysql> select * from course where tno in(select tno from teacher where depart=‘计算机系’);
±------±----------------±----+
| cno | cname | tno |
±------±----------------±----+
| 3-245 | c操作系统 | 804 |
| 3-105 | 计算机导论 | 825 |
±------±----------------±----+
2 rows in set (0.00 sec)
select degree,cno from score where cno in(select cno from course where tno in(select tno from teacher where depart=‘计算机系’));
mysql> select degree,cno from score where cno in(select cno from course where tno in(select tno from teacher where depart=‘计算机系’));
±-------±------+
| degree | cno |
±-------±------+
| 75 | 3-245 |
| 87 | 3-245 |
| 68 | 3-245 |
| 89 | 3-105 |
| 88 | 3-105 |
| 65 | 3-105 |
±-------±------+
6 rows in set (0.00 sec)
26、查询计算机系和电子工程系不同职称的教师的tname和prof
–union求并集
select * from teacher where depart=‘计算机系’;
mysql> select * from teacher where depart=‘计算机系’;
±----±-------±-----±--------------------±----------±-------------+
| tno | tname | tsex | tbirthday | prof | depart |
±----±-------±-----±--------------------±----------±-------------+
| 804 | 李成 | 男 | 1984-12-03 00:00:00 | 副教授 | 计算机系 |
| 825 | 王萍 | 女 | 1984-12-03 00:00:00 | 助教 | 计算机系 |
±----±-------±-----±--------------------±----------±-------------+
2 rows in set (0.00 sec)
select * from teacher where prof not in(select prof from teacher where depart='计算机系');
mysql> select * from teacher where prof not in(select prof from teacher where depart=‘计算机 系’);
±----±-------±-----±--------------------±-------±----------------+
| tno | tname | tsex | tbirthday | prof | depart |
±----±-------±-----±--------------------±-------±----------------+
| 856 | 张旭 | 男 | 1974-11-03 00:00:00 | 教授 | 电子工程系 |
±----±-------±-----±--------------------±-------±----------------+
1 row in set (0.00 sec)
select * from teacher where prof not in(select prof from teacher where depart=‘电子工程系’);
mysql> select * from teacher where prof not in(select prof from teacher where depart=‘电子工 程系’);
±----±-------±-----±--------------------±----------±-------------+
| tno | tname | tsex | tbirthday | prof | depart |
±----±-------±-----±--------------------±----------±-------------+
| 804 | 李成 | 男 | 1984-12-03 00:00:00 | 副教授 | 计算机系 |
±----±-------±-----±--------------------±----------±-------------+
1 row in set (0.00 sec)
select * from teacher where prof not in(select prof from teacher where depart='计算机系')
union
select * from teacher where prof not in(select prof from teacher where depart='电子工程系');
mysql> select * from teacher where prof not in(select prof from teacher where depart=‘计算机 系’)
-> union
-> select * from teacher where prof not in(select prof from teacher where depart=‘电子工 程系’);
±----±-------±-----±--------------------±----------±----------------+
| tno | tname | tsex | tbirthday | prof | depart |
±----±-------±-----±--------------------±----------±----------------+
| 856 | 张旭 | 男 | 1974-11-03 00:00:00 | 教授 | 电子工程系 |
| 804 | 李成 | 男 | 1984-12-03 00:00:00 | 副教授 | 计算机系 |
±----±-------±-----±--------------------±----------±----------------+
2 rows in set (0.01 sec)
27、查询选修编号为3-105课程且成绩至少高于编号为3-245的同学的cno\sno和degree
并按照degree从高到低次序排序
select * from score where cno='3-105';
mysql> select * from score where cno=‘3-105’;
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 103 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 65 |
±----±------±-------+
3 rows in set (0.00 sec)
select * from score where cno=‘3-245’;
mysql> select * from score where cno=‘3-245’;
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 105 | 3-245 | 75 |
| 108 | 3-245 | 87 |
| 109 | 3-245 | 68 |
±----±------±-------+
3 rows in set (0.00 sec)
select * from score
where cno='3-105'
and degree >any(select degree from score where cno='3-245')
order by degree desc;
mysql> 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 |
±----±------±-------+
| 103 | 3-105 | 89 |
| 105 | 3-105 | 88 |
±----±------±-------+
2 rows in set (0.00 sec)
28、查询选修编号为3-105课程且成绩高于编号为3-245的同学的cno\sno和degree
select * from score
where cno='3-105'
and degree >all(select degree from score where cno='3-245')
order by degree desc;
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 |
±----±------±-------+
| 103 | 3-105 | 89 |
| 105 | 3-105 | 88 |
±----±------±-------+
2 rows in set (0.00 sec)
29、查询所有教师和同学的name、sex和birthday
别名 as
select sname as name,ssex as sex,sbirthday as birthday from student
union
select tname,tsex,tbirthday from teacher;
mysql> select sname as name,ssex as sex,sbirthday as birthday from student
-> union
-> select tname,tsex,tbirthday from teacher;
±-------±----±--------------------+
| name | sex | birthday |
±-------±----±--------------------+
| 大明 | 男 | 1597-04-23 00:00:00 |
| 刘思 | 男 | 1499-03-23 00:00:00 |
| 张三 | 女 | 1999-03-23 00:00:00 |
| 李白 | 男 | 1897-12-23 00:00:00 |
| 李芳 | 女 | 1997-02-23 00:00:00 |
| 李四 | 男 | 2000-02-23 00:00:00 |
| 小黑 | 男 | 1597-04-23 00:00:00 |
| 小红 | 女 | 1997-02-23 00:00:00 |
| 小芳 | 男 | 2010-02-23 00:00:00 |
| 李成 | 男 | 1984-12-03 00:00:00 |
| 王萍 | 女 | 1984-12-03 00:00:00 |
| 刘冰 | 女 | 1994-12-03 00:00:00 |
| 张旭 | 男 | 1974-11-03 00:00:00 |
±-------±----±--------------------+
13 rows in set (0.00 sec)
30、查询所有女教师和女同学的name、sex、birthday
select sname as name,ssex as sex,sbirthday as birthday from student where ssex='女'
union
select tname,tsex,tbirthday from teacher where tsex='女';
mysql> select sname as name,ssex as sex,sbirthday as birthday from student where ssex=‘女’
-> union
-> select tname,tsex,tbirthday from teacher where tsex=‘女’;
±-------±----±--------------------+
| name | sex | birthday |
±-------±----±--------------------+
| 张三 | 女 | 1999-03-23 00:00:00 |
| 李芳 | 女 | 1997-02-23 00:00:00 |
| 小红 | 女 | 1997-02-23 00:00:00 |
| 王萍 | 女 | 1984-12-03 00:00:00 |
| 刘冰 | 女 | 1994-12-03 00:00:00 |
±-------±----±--------------------+
5 rows in set (0.00 sec)
31、查询成绩比该课程平均成绩低的学生成绩表
select cno,avg(degree) from score group by cno;
select * from score a where degree<(select avg(degree) from score b where a.cno=b.cno);
mysql> select * from score a where degree<(select avg(degree) from score b where a.cno=b.cno);
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 103 | 6-166 | 67 |
| 105 | 3-245 | 75 |
| 109 | 3-105 | 65 |
| 109 | 3-245 | 68 |
±----±------±-------+
4 rows in set (0.00 sec)
32、查询所有任课教师的Tname和depart;
select tno from course;
mysql> select tno from course;
±----+
| tno |
±----+
| 804 |
| 825 |
| 831 |
| 856 |
±----+
4 rows in set (0.00 sec)
select tname,depart from teacher where tno in(select tno from course);
mysql> select tname,depart from teacher where tno in(select tno from course);
±-------±----------------+
| tname | depart |
±-------±----------------+
| 李成 | 计算机系 |
| 王萍 | 计算机系 |
| 刘冰 | 电子工程系 |
| 张旭 | 电子工程系 |
±-------±----------------+
4 rows in set (0.01 sec)
33、查询至少有两名同学的班号
select class from student where ssex='男' group by class having count(*)>1;
mysql> select class from student where ssex=‘男’ group by class having count(*)>1;
±------+
| class |
±------+
| 95034 |
| 95035 |
±------+
2 rows in set (0.00 sec)
34、查询student表中不姓王的同学记录。
select * from student where sname not like '王%';
mysql> select * from student where sname not like ‘王%’;
±----±-------±-----±--------------------±------+
| sno | sname | ssex | sbirthday | class |
±----±-------±-----±--------------------±------+
| 101 | 大明 | 男 | 1597-04-23 00:00:00 | 95035 |
| 102 | 刘思 | 男 | 1499-03-23 00:00:00 | 95033 |
| 103 | 张三 | 女 | 1999-03-23 00:00:00 | 95036 |
| 104 | 李白 | 男 | 1897-12-23 00:00:00 | 95034 |
| 105 | 李芳 | 女 | 1997-02-23 00:00:00 | 95032 |
| 106 | 李四 | 男 | 2000-02-23 00:00:00 | 95035 |
| 107 | 小黑 | 男 | 1597-04-23 00:00:00 | 95034 |
| 108 | 小红 | 女 | 1997-02-23 00:00:00 | 95033 |
| 109 | 小芳 | 男 | 2010-02-23 00:00:00 | 95037 |
±----±-------±-----±--------------------±------+
9 rows in set (0.00 sec)
35、查询student表中每个学生的姓名和年龄
–年龄=当前年份-出生年份
select year(now());
mysql> select year(now());
±------------+
| year(now()) |
±------------+
| 2021 |
±------------+
1 row in set (0.01 sec)
select year(sbirthday) from student;
mysql> select year(sbirthday) from student;
±----------------+
| year(sbirthday) |
±----------------+
| 1597 |
| 1499 |
| 1999 |
| 1897 |
| 1997 |
| 2000 |
| 1597 |
| 1997 |
| 2010 |
±----------------+
9 rows in set (0.00 sec)
select sname,year(now())-year(sbirthday) as '年龄' from student;
mysql> select sname,year(now())-year(sbirthday) as ‘年龄’ from student;
±-------±-------+
| sname | 年龄 |
±-------±-------+
| 大明 | 424 |
| 刘思 | 522 |
| 张三 | 22 |
| 李白 | 124 |
| 李芳 | 24 |
| 李四 | 21 |
| 小黑 | 424 |
| 小红 | 24 |
| 小芳 | 11 |
±-------±-------+
9 rows in set (0.00 sec)
36、查询student表中最大和最小的sbirthday的日期值
select max(sbirthday) as '最大' ,min(sbirthday) as '最小' from student;
mysql> select max(sbirthday) as ‘最大’ ,min(sbirthday) as ‘最小’ from student;
±--------------------±--------------------+
| 最大 | 最小 |
±--------------------±--------------------+
| 2010-02-23 00:00:00 | 1499-03-23 00:00:00 |
±--------------------±--------------------+
1 row in set (0.00 sec)
37、以班号和年龄从大到小的顺序查询student表中的全部记录。
select * from student order by class desc,sbirthday ;
mysql> select * from student order by class desc,sbirthday ;
±----±-------±-----±--------------------±------+
| sno | sname | ssex | sbirthday | class |
±----±-------±-----±--------------------±------+
| 109 | 小芳 | 男 | 2010-02-23 00:00:00 | 95037 |
| 103 | 张三 | 女 | 1999-03-23 00:00:00 | 95036 |
| 101 | 大明 | 男 | 1597-04-23 00:00:00 | 95035 |
| 106 | 李四 | 男 | 2000-02-23 00:00:00 | 95035 |
| 107 | 小黑 | 男 | 1597-04-23 00:00:00 | 95034 |
| 104 | 李白 | 男 | 1897-12-23 00:00:00 | 95034 |
| 102 | 刘思 | 男 | 1499-03-23 00:00:00 | 95033 |
| 108 | 小红 | 女 | 1997-02-23 00:00:00 | 95033 |
| 105 | 李芳 | 女 | 1997-02-23 00:00:00 | 95032 |
±----±-------±-----±--------------------±------+
9 rows in set (0.00 sec)
38、查询男教师及其所上课程
select cno from course where tno in(select tno from teacher where tsex='男');
mysql> select cno from course where tno in(select tno from teacher where tsex=‘男’);
±------+
| cno |
±------+
| 3-245 |
| 6-166 |
±------+
2 rows in set (0.00 sec)
39、查询最高分同学的sno、cno，degree列
select max(degree) from score;
mysql> select max(degree) from score;
±------------+
| max(degree) |
±------------+
| 98 |
±------------+
1 row in set (0.00 sec)
select sno,cno,degree from score where degree=(select max(degree) from score);
mysql> select sno,cno,degree from score where degree=(select max(degree) from score);
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 105 | 6-166 | 98 |
±----±------±-------+
1 row in set (0.00 sec)
40、查询和小芳同性别的所有同学的sname
select * from student where sname='小芳';
mysql> select * from student where sname=‘小芳’;
±----±-------±-----±--------------------±------+
| sno | sname | ssex | sbirthday | class |
±----±-------±-----±--------------------±------+
| 109 | 小芳 | 男 | 2010-02-23 00:00:00 | 95037 |
| 110 | 小芳 | 女 | 2003-08-12 00:00:00 | 95037 |
| 111 | 小芳 | 女 | 2013-08-12 00:00:00 | 95036 |
±----±-------±-----±--------------------±------+
3 rows in set (0.00 sec)
select ssex from student where sname=‘李白’;
mysql> select ssex from student where sname=‘李白’;
±-----+
| ssex |
±-----+
| 男 |
±-----+
1 row in set (0.00 sec)
select sname from student where ssex=(select ssex from student where sname=‘李白’);
mysql> select sname from student where ssex=(select ssex from student where sname=‘李白’);
±-------+
| sname |
±-------+
| 大明 |
| 刘思 |
| 李白 |
| 李四 |
| 小黑 |
| 小芳 |
±-------+
6 rows in set (0.00 sec)
41、查询和李白同性别并且同班同学的sname
select sname from student
where ssex=(select ssex from student where sname='李白')
and class=(select class from student where sname='李白');
mysql> select sname from student
-> where ssex=(select ssex from student where sname=‘李白’)
-> and class=(select class from student where sname=‘李白’);
±-------+
| sname |
±-------+
| 李白 |
| 小黑 |
±-------+
2 rows in set (0.00 sec)
42、查询所有选修计算机导论课程的男同学的成绩表
select * from course where cname='计算机导论';
select * from score where cno=(select cno from course where cname=‘计算机导论’);
mysql> select * from score where cno=(select cno from course where cname=‘计算机导论’);
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 103 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 65 |
±----±------±-------+
3 rows in set (0.00 sec)
;
select * from score
where cno=(select cno from course where cname=‘计算机导论’)
and sno in(select sno from student where ssex=‘男’);
mysql> select * from score
-> where cno=(select cno from course where cname=‘计算机导论’)
-> and sno in(select sno from student where ssex=‘男’);
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 109 | 3-105 | 65 |
±----±------±-------+
1 row in set (0.00 sec)
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列。
select sno,cno,grade from score, grade where degree between low and upp;
mysql> select sno,cno,grade from score, grade where degree between low and upp;
±----±------±------+
| sno | cno | grade |
±----±------±------+
| 103 | 3-105 | B |
| 103 | 6-166 | D |
| 105 | 3-105 | B |
| 105 | 3-245 | C |
| 105 | 6-166 | A |
| 108 | 3-245 | B |
| 109 | 3-105 | D |
| 109 | 3-245 | D |
| 109 | 6-166 | B |
| 109 | 9-888 | B |
±----±------±------+
10 rows in set (0.00 sec)
