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;