SQL常用查询举例

SQL常用查询举例

"教学管理"数据库中目前已知的表有：

• 教师(编号,姓名,性别,年龄,工作时间,政治面貌,学历,职称,系别,联系电话,在职否)
• 课程(课程编号,课程名称,课程类别,学分)
• 学生(学生编号,姓名,性别,年龄,入校时间,团员否,住址,照片)
• 选课成绩(选课ID,学生编号,课程编号,成绩)

01-SQL简单查询

1、查询所有的课程信息

select * from 课程

2、列出前5个教师的姓名和工龄

select top 5 姓名,Year(Date())-Year(工作时间) as 工龄 from 教师

3、求出所有教师的平均年龄

select avg(年龄) as 平均年龄 from 教师

02-带条件的SQL查询

1、列出成绩在80分以上的学生记录

select * from 选课成绩 where 成绩>=80

2、求出四川住址的学生平均年龄

select avg(年龄) as 平均年龄 from 学生 where 住址='四川'

3、列出双流区和金牛区的学生名单

select 学生编号,姓名,住址 from 学生 where 住址 in ('双流区','金牛区')

4、列出成绩在80分到90分之间的学生名单

select 学生编号,成绩 from 选课成绩 where 成绩 between 80 and 100

5、列出所有姓"张"的学生名单

select 学生编号,姓名 from 学生 where 姓名 like '张%'

6、列出所有成绩为空值的学生编号和课程编号

select 学生编号,课程编号 from 选课成绩 where 成绩 is null

03-排序

1、按性别顺序列出学生编号、姓名、性别、年龄以及住址，性别相同的再按年龄由大到小排序

select 学生编号,姓名,性别,年龄,住址 from 学生 order by 性别,年龄 ASC |

2、将学生成绩降序排序，只显示前30%的记录

select top 30% from 选课成绩 order by 成绩 desc

04-分组查询

1、分别统计"学生"表中男女生人数

select 性别,count(*) as 人数 from 学生 group by 性别

2、按性别统计"教师"表中政治面貌为非党员的人数

select 性别,count(*) as 人数 from 教师 where 政治面貌='非党员' group by 性别

3、列出平均成绩大于75分的课程编号，并按平均成绩升序排序

select 课程编号,avg(成绩) as 平均成绩
from 选课成绩
group by 课程编号 having avg(成绩)>=75
order by avg(成绩) asc

4、统计每个学生选修课程的门数(超过1门的学生才统计)，要求输出学生编号和选修门数，查询结果按选课门数降序排序，若门数相同，按学生编号升序排序

select 学生编号,count(课程编号) as 选课门数
from 选课成绩
group by 学生编号 having count(课程编号)>=1
order by 2 desc 1

05-嵌套查询

1、列出选修"高等数学"的所有学生的学生编号

select 学生编号 from 选课成绩
where 课程编号=(select 课程编号 from 课程
where 课程名称='高等数学')

2、列出选修"101"课的学生中成绩比选修"105"的最低成绩高的学生编号和成绩

select 学生编号,成绩 from 选课成绩
where 课程编号='101' and 成绩>any(select 成绩 from 选课成绩
where 课程编号='105')

3、列出选修"101"课的学生的学生编号和成绩，这些学生的成绩要比选修"105"课的最高成绩还要高的学生

select 学生编号,成绩 from 选课成绩
where 课程编号='101' and 成绩>all(select 成绩 from 选课成绩
where 课程编号='105')

4、列出选修"高等数学"或"英语"的所有学生的学生编号

select 学生编号 from 选课成绩
where 课程编号 in (select 课程编号 from 课程
where 课程名称='高等数学' or 课程名称='英语')

06-联接查询

1、输出所有学生的成绩单，要求给出学生编号、姓名、课程编号、课程名称和成绩

select students.学生编号,姓名,grades.课程编号,课程名称,成绩
from 学生 students,选课成绩 grades,课程 course
where students.学生编号=grades.学生编号
and course.课程编号=grades.课程编号

2、列出团员学生的选课情况，要求列出学生编号、姓名、课程编号、课程名称和成绩

select a.学生编号,a.姓名,b.课程编号,课程名称,成绩
from 学生 a,选课成绩 b,课程 c
where a.学生编号=b.学生编号 and c.课程编号=b.课程编号 
and a.团员否

3、求选修"101"课程的女生的平均年龄

select avg(年龄) as 平均年龄 from 学生,选课成绩
where 学生.学生编号=选课成绩.学生编号
and 课程.课程编号=选课成绩.课程编号

07-联合查询

1、对"教学管理"数据库，列出选修"101"或"102"课程的所有学生的学生编号和姓名，要求建立联合查询

select 学生.学生编号,学生.姓名 from 选课成绩,学生
where 课程编号='101' and 选课成绩.学生编号=学生.学生编号
union select 学生.学生编号,学生.姓名 from 选课成绩,学生
where 课程编号='102' and 选课成绩.学生编号=学生.学生编号

08-定义数据表结构

1、在"教学管理"数据库中建立"教师情况"表，表中的字段包括：编号，姓名，性别，基本工资，出生年月，研究方向，其中出生年月允许为空值

create table 教师情况(
		编号 char(9),
		姓名 char(10),
		性别 char(2),
		基本工资 Money,
		出生年月 Datetime null,
		研究方向 text(50)
		);

09-修改表结构

1、为"课程"表增加一个整数类型的"学时"字段

alter table 课程 add 学时 smallint;

2、删除"课程"表中的"学时"字段

alter table 课程 drop column 学时;

10-删除表

1、在"教学管理"数据库中删除已经创建的"教师情况"表

drop table 教师情况;

11-向表中插入记录

1、向"学生"表中添加记录，学生编号为"1101"，姓名为"lgl"，入校时间为2019年9月1日

insert into 学生(学生编号,姓名,入校时间)
values('1101','lgl','2019/9/1');

12-更新记录

1、将"学生"表中"叶凡"同学的住址改为"四川"

update 学生 set 住址='四川' where 姓名='叶凡';

2、将所有团员学生的成就加2分

update 选课成绩 set 成绩=成绩+2
where 学生编号 in (select 学生编号 from 学生 where 团员否)

13-删除记录

1、删除"学生"表中所有男生的记录

delete from 学生 where 性别='男';

2、删除"选课成绩"表中成绩小于60的记录

delete from 选课成绩 where 成绩<60;