SQL语句编写的练习（MySQL）

SQL语句编写的练习（MySQL）

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 scorewhere 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 studentwhere 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='计算机系')unionselect * 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 unionselect 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='女'unionselect 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)

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。