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)