mysql 姓刘或姓李_案例-多表查询、子查询实例02（有答案）

以下所有操作在查询管理器中手工书写sql语句完成

1、按下列步骤创建数据库和表

1.2、在这个数据库中创建一个名为[学生表]的表，由[学号]、[姓名]、[专业]、[班级]

字段组成。[学号] 字段为主键，类型为int；[姓名] 和 [专业] 字段类型为varchar,

长度为6和20；[班级] 字段类型为char,长度为4。这些字段均不允许NULL值。

use student

create table 学生表

(

学号 int primary key,

姓名 varchar(6) not null,

专业 varchar(20) not null,

班级 char(4) not null

)

1

2

3

4

5

6

7

8

9

10

usestudent

createtable学生表

(

学号intprimarykey,

姓名varchar(6)notnull,

专业varchar(20)notnull,

班级char(4)notnull

)

1.3、在这个数据库中创建一个名为 [成绩表] 的表，由 [学号] 、[课程编号]、 [成绩] 字段组成。

[学号] 为外键。[学号] 类型为int；

[课程编号] 类型为char,长度为2； [成绩] 类型为int

这些字段均不允许NULL值。

create table 成绩表

(

学号 int not null,

课程编号 char(2) not null,

成绩 float not null

constraint FK_成绩表_学生表 foreign key(学号) references 学生表(学号)

)

1

2

3

4

5

6

7

8

createtable成绩表

(

学号intnotnull,

课程编号char(2)notnull,

成绩floatnotnull

constraintFK_成绩表_学生表foreignkey(学号)references学生表(学号)

)

2、在学生表中添加以下记录：

学号 姓名 专业 班级

2001001 吴小亮 计算机及应用 0101

2001002 刘京生 计算机及应用 0101

2001003 李向名 计算机及应用 0102

2001004 高大山 计算机及应用 0102

2001005 王前 网络应用 0103

2001006 李云飞 网络应用 0103

全部添加完毕后，显示该表中的所有记录

insert into 学生表 values(2001001,'吴小亮','计算机及应用','0101')

insert into 学生表 values(2001002,'刘京生','计算机及应用','0101')

insert into 学生表 values(2001003,'李向名','计算机及应用','0102')

insert into 学生表 values(2001004,'高大山','计算机及应用','0102')

insert into 学生表 values(2001005,'王前','网络应用','0103')

insert into 学生表 values(2001006,'李云飞','网络应用','0103')

select * from 学生表

1

2

3

4

5

6

7

8

9

insertinto学生表values(2001001,'吴小亮','计算机及应用','0101')

insertinto学生表values(2001002,'刘京生','计算机及应用','0101')

insertinto学生表values(2001003,'李向名','计算机及应用','0102')

insertinto学生表values(2001004,'高大山','计算机及应用','0102')

insertinto学生表values(2001005,'王前','网络应用','0103')

insertinto学生表values(2001006,'李云飞','网络应用','0103')

select *from学生表

3、在成绩表中添加以下记录：

学号 课程编号 成绩

2001001 01 73

2001001 02 88

2001002 01 95

2001002 02 64

2001003 01 75

2001003 02 90

2001004 01 99

2001004 02 50

2001005 01 66

2001005 02 80

2001006 01 93

2001006 02 75

全部添加完毕后，显示该表中的所有记录

insert into 成绩表 values(2001001,'01',73)

insert into 成绩表 values(2001001,'02',88)

insert into 成绩表 values(2001002,'01',95)

insert into 成绩表 values(2001002,'02',64)

insert into 成绩表 values(2001003,'01',75)

insert into 成绩表 values(2001003,'02',90)

insert into 成绩表 values(2001004,'01',99)

insert into 成绩表 values(2001004,'02',50)

insert into 成绩表 values(2001005,'01',66)

insert into 成绩表 values(2001005,'02',80)

insert into 成绩表 values(2001006,'01',93)

insert into 成绩表 values(2001006,'02',75)

select * from 成绩表

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

insertinto成绩表values(2001001,'01',73)

insertinto成绩表values(2001001,'02',88)

insertinto成绩表values(2001002,'01',95)

insertinto成绩表values(2001002,'02',64)

insertinto成绩表values(2001003,'01',75)

insertinto成绩表values(2001003,'02',90)

insertinto成绩表values(2001004,'01',99)

insertinto成绩表values(2001004,'02',50)

insertinto成绩表values(2001005,'01',66)

insertinto成绩表values(2001005,'02',80)

insertinto成绩表values(2001006,'01',93)

insertinto成绩表values(2001006,'02',75)

select *from成绩表

4、在学生表中查找姓名为“王前”的记录并将这条记录中的班级更改为“0104”，

然后列出经过修改后的这一行记录

update 学生表 set 班级='0104' where 姓名='王前'

select * from 学生表 where 姓名='王前'

1

2

3

4

update学生表set班级='0104'where姓名='王前'

select *from学生表where姓名='王前'

5、分别从学生表和成绩表中删除学号为2001006的记录(注意顺序，为什么？)

delete from 成绩表 where 学号='2001006'

delete from 学生表 where 学号='2001006'

1

2

3

4

deletefrom成绩表where学号='2001006'

deletefrom学生表where学号='2001006'

6、查找显示成绩表中”学号”字段的全部数据

select 学号 from 成绩表

1

2

select学号from成绩表

7、查找显示成绩表中”学号”字段的全部数据，要求查询结果中不包含重复记录

select distinct 学号 from 成绩表

1

2

selectdistinct学号from成绩表

8、从学生表和成绩表中查找记录，要求以两个表中的“学号”字段作为连接字段，

结果显示学号,姓名,课程编号,成绩的内容

select 学生表.学号,姓名,课程编号,成绩 from 学生表,成绩表 where 学生表.学号=成绩表.学号

---标准内连接

select 学生表.学号,姓名,课程编号,成绩 from 学生表 inner join 成绩表 on 学生表.学号=成绩表.学号

1

2

3

4

5

6

select学生表.学号,姓名,课程编号,成绩from学生表,成绩表where学生表.学号=成绩表.学号

---标准内连接

select学生表.学号,姓名,课程编号,成绩from学生表innerjoin成绩表on学生表.学号=成绩表.学号

9、从学生表和成绩表中查找记录，看看学生表中有哪些学生以及这些学生的成绩如何。

要求使用左外部连接和别名

select 姓名,成绩 from 学生表 aaa left outer join 成绩表 bbb on aaa.学号=bbb.学号

1

2

select姓名,成绩from学生表aaaleftouterjoin成绩表bbbonaaa.学号=bbb.学号

10、在成绩表中查找课程编号为02的成绩高于80分的学生记录

select * from 成绩表 where 成绩>80 and 课程编号='02'

1

2

select *from成绩表where成绩>80and课程编号='02'

11、在学生表中找出姓刘或姓李的学生

select * from 学生表 where 姓名 like '刘%' or 姓名 like '李%'

1

2

select *from学生表where姓名like'刘%'or姓名like'李%'

12、在成绩表中查找01号课程成绩介于60与80之间的学生记录

select * from 成绩表 where 成绩 between 60 and 80 and 课程编号='01'

1

2

select *from成绩表where成绩between60and80and课程编号='01'

13、在学生表中找出姓刘、姓李、姓王的学生记录

select * from 学生表 where 姓名 like '刘%' or 姓名 like '李%' or 姓名 like '王%'

1

2

select *from学生表where姓名like'刘%'or姓名like'李%'or姓名like'王%'

14、在学生表中找出学号末位数字位于3-5范围内的学生记录

select * from 学生表 where 学号-2001000 between 3 and 5

1

2

select *from学生表where学号-2001000between3and5

15、把成绩表中课程编号=’01’的所有学生记录按成绩从高到低排列显示出来

select * from 成绩表 where 课程编号='01' order by 成绩 desc

1

2

select *from成绩表where课程编号='01'orderby成绩desc

16、从学生表和成绩表中查找记录，统计0101班01号课程的总成绩

select sum(成绩) from 成绩表 where 课程编号='01' and 学号 in(select distinct 学号 from 学生表 where 班级='0101')

1

2

selectsum(成绩)from成绩表where课程编号='01'and学号in(selectdistinct学号from学生表where班级='0101')

17、统计学生表中的记录总数(count(字段))

select count(*) from 学生表

1

2

selectcount(*)from学生表

18、在成绩表中，找出02号课程成绩中的最高分

select max(成绩) from 成绩表 where 课程编号='02'

1

2

selectmax(成绩)from成绩表where课程编号='02'

19、在成绩表中，计算每个学生各门功课的总成绩和平均成绩，并按照总成绩降序排列

select 学号,sum(成绩) 总成绩,avg(成绩) 平均成绩 from 成绩表 group by 学号 order by 总成绩 desc

1

2

select学号,sum(成绩)总成绩,avg(成绩)平均成绩from成绩表groupby学号orderby总成绩desc

家庭作业：

20、从成绩表中查找记录，列出02号课程成绩高于此课程平均成绩的记录

select * from 成绩表 where 课程编号='02' and 成绩>(select avg(成绩) from 成绩表 where 课程编号='02')

1

2

select *from成绩表where课程编号='02'and成绩>(selectavg(成绩)from成绩表where课程编号='02')

21、列出0102班的学生成绩

select 成绩 from 成绩表 where 学号 in(select 学号 from 学生表 where 班级='0102')

1

2

select成绩from成绩表where学号in(select学号from学生表where班级='0102')

22、查询所有考试01号课程分数比所有02号课程分数高的学生学号，姓名；

select 学生表.学号,姓名 from 学生表,

(select * from 成绩表 where 课程编号='01') a,

(select * from 成绩表 where 课程编号='02') b where a.成绩>b.成绩 and a.学号=b.学号 and 学生表.学号=a.学号

1

2

3

4

select学生表.学号,姓名from学生表,

(select *from成绩表where课程编号='01')a,

(select *from成绩表where课程编号='02')bwherea.成绩>b.成绩anda.学号=b.学号and学生表.学号=a.学号

select 学生表.学号,姓名 from 学生表,成绩表 where 成绩>all(select 成绩 from 成绩表 where 课程编号='02' )

and 课程编号='01' and 学生表.学号=成绩表.学号

1

2

3

select学生表.学号,姓名from学生表,成绩表where成绩>all(select成绩from成绩表where课程编号='02')

and课程编号='01'and学生表.学号=成绩表.学号

23、查询所有考试01号课程分数不低于所有02号课程分数的学生学号，姓名；

select 学生表.学号,姓名 from 学生表,成绩表 where 成绩>=all(select 成绩 from 成绩表 where 课程编号='02' )

and 课程编号='01' and 学生表.学号=成绩表.学号

1

2

3

select学生表.学号,姓名from学生表,成绩表where成绩>=all(select成绩from成绩表where课程编号='02')

and课程编号='01'and学生表.学号=成绩表.学号

24、查询和01号成绩最高的同学在一个班的同学的姓名；

select 姓名 from 学生表 where

班级=(select 班级 from 学生表 where 学号=(select 学号 from 成绩表 where 成绩=(select max(成绩) from 成绩表 where 课程编号='01')))

and 学号<>(select 学号 from 成绩表 where 成绩=(select max(成绩) from 成绩表 where 课程编号='01'))

select * from 学生表

select * from 成绩表 order by 成绩,课程编号

1

2

3

4

5

6

7

8

9

select姓名from学生表where

班级=(select班级from学生表where学号=(select学号from成绩表where成绩=(selectmax(成绩)from成绩表where课程编号='01')))

and学号<>(select学号from成绩表where成绩=(selectmax(成绩)from成绩表where课程编号='01'))

select *from学生表

select *from成绩表orderby成绩,课程编号

25、查询01号成绩进前三名的同学的姓名，班级；

select 姓名,班级 from 学生表,成绩表 where 成绩 in(select top 3 成绩 from 成绩表 where 课程编号='01' order by 成绩 desc) and 学生表.学号=成绩表.学号

1

2

select姓名,班级from学生表,成绩表where成绩in(selecttop3成绩from成绩表where课程编号='01'orderby成绩desc)and学生表.学号=成绩表.学号

26、查询02号成绩第三到第五名的所有同学的姓名；

select 姓名 from 学生表 where 学号 in

(select top 3 学号 from 成绩表 where 学号 not in (select top 2 学号 from 成绩表 where 课程编号='02' order by 成绩 desc) order by 成绩 desc)

1

2

3

4

select姓名from学生表where学号in

(selecttop3学号from成绩表where学号notin(selecttop2学号from成绩表where课程编号='02'orderby成绩desc)orderby成绩desc)

27、查询01号成绩不是第一名的所有同学的姓名；

select 姓名 from 学生表,成绩表 where 成绩<>(select max(成绩) from 成绩表 where 课程编号='01') and 学生表.学号=成绩表.学号 and 课程编号='01'

1

2

3

select姓名from学生表,成绩表where成绩<>(selectmax(成绩)from成绩表where课程编号='01')and学生表.学号=成绩表.学号and课程编号='01'

28、查询01号成绩和02号成绩都能进前三名的同学姓名；

select 姓名 from (select top 3 * from 成绩表 where 课程编号='01' order by 成绩 desc ) a

,(select top 3 * from 成绩表 where 课程编号='02' order by 成绩 desc) b ,学生表 where a.学号=b.学号 and 学生表.学号=a.学号

1

2

3

select姓名from(selecttop3*from成绩表where课程编号='01'orderby成绩desc)a

,(selecttop3*from成绩表where课程编号='02'orderby成绩desc)b,学生表wherea.学号=b.学号and学生表.学号=a.学号