期末c++链接数据库课程设计，用到数据库的查询语句，忘记的差不多了，翻出来看看，以前自己敲过的mySQL语句。忘记哪条语句，直接ctrl+F搜索关键词，如求分数总和，搜索：sum!

第一部分：数据库查询语句
[cpp]--查询1987年以后出生的学生的姓名和出生年份
select sname,(2008-Sage) as birthday from student where (2008-Sage)>1987 order by (2008-Sage) asc;
--查询选修了2号课程学生的学号及其成绩，查询结果按分数降序排列
select Sno,grade from sc where cno='002' order by grade desc;
select * from sc order by cno asc,sno desc;
select count(*) from student;
select count(distinct sno) from sc;
--计算002号课程的学生的平均成绩
select avg(grade) from sc where cno='002'
--查询学习001号课程的学生最高分数
select max(grade) from sc where cno='001'
--查询每个系的系名和学生人数
select Sdept,count(sno) from student group by sdept
--求每门课学生的平均成绩
select cno,avg(grade) from sc group by cno
--求每个学生的总成绩
select sno,sum(grade) from sc group by sno
--查询选修了三门以上课程的学生的学号
select sno,count(cno) from sc group by sno having count(cno)>=3
--查询总成绩超过200分的学生的学号和总成绩
select sno,sum(grade) from sc group by sno having sum(grade)>200
--查询所有成绩为优秀的学生学号
select sno from sc where sno not in(select sno from sc where grade is null )group by sno having min(grade) >90
--查询每个学生及其选修课程的情况
select student.*,sc.* from student,sc where student.sno=sc.sno
select a.*,b.* from student a,sc b where a.sno=b.sno
--假设每个学生选修了课程表中的所有的课程，现在查询每个学生的学号和选修课程的课程号
select sno,cno from student,course;
--查询每一门课程的间接先修课(即先修课的先修课)
select first.cno,second.cpno from course first,course second where first.cpno=second.cno
select student.*,sc.* from student left join sc on student.sno=sc.sno
select first.cno,second.cpno from course first left join course second on first.cpno=second.cno
--查询和刘晨同在一个系学习的学生
select b.* from student a,student b where a.sname='刘晨' and a.sdept=b.sdept
--查询选修了002号课程且成绩大于90分的学生情况
select student.* from student,sc where student.sno=sc.sno and sc.cno='002' and grade >90
--查询选修了课程的学生姓名，选修课程名和成绩
select sname,cname,grade from student,sc,course where student.sno=sc.sno and sc.cno=course.cno
select sname,cname,grade from student,sc,course where sc.cno=course.cno and student.sno=sc.sno
--查询每个学生学号，姓名并把结果显示到一个列中
select sno+sname from student;
--查询所有成绩为优秀的学生姓名
select sname from student,sc where student.sno=sc.sno group by sname having min(grade)>=90
select sname from student,sc where student.sno=sc.sno and student.sno not in(select sno from student where grade is null) group by sname having min(grade)>=90
--查询年龄大于所有学生平均年龄的学生的信息
select avg(sage) from student
select * from student where sage>18.8
select * from student where sage>(select avg(sage) from student);
--查询和"刘晨"同一年龄的学生信息
select * from student where Sage =(select Sage from student where sname='刘晨')
select s1.* from student s1,student s2 where s2.sname='刘晨' and s1.sage=s2.sage
--查询所有被学生选修过的课程的信息
select * from course where Cno in(select distinct cno from sc)
--查询所有未被学生选修的课程的信息
select * from course where cno not in(select distinct cno from sc)
--查询选修了课程名为"数据库"的课程的学生信息
select * from student where sno in(select sno from sc where cno in(select cno from course where cname='数据库'))
select student.* from student,sc,course where student.sno=sc.sno and sc.cno=course.cno and course.cname='数据库'
--查询其他系中比IS系某一学生年龄小的学生名单
select * from student where sage <any(select sage from student where sdept='IS')and sdept<>'IS' order by sage desc
select * from student where sage <(select max(sage) from student where sdept='IS')and sdept<> 'IS' order by sage desc
select * from student order by sdept asc,sage desc
--查询其他系中比IS系所有学生年龄都大的学生名单
select * from student where sage >all(select sage from student where sdept='IS')and sdept<> 'IS' order by sage desc
select * from student where sage >(select max(sage) from student where sdept='IS') and sdept<> 'IS' order by sage desc
--查询年龄处于‘刘晨’和‘刘立’年龄之间的学生信息
select * from student where sage between (select sage from student where sname='刘晨') and (select sage from student where sname='刘立')
--查询跟'08001'学生同姓的学生信息
select * from student where sname like (select left(sname,1) from student where sno='08001')+'%'
--查询比本系平均年龄大的学生信息
select * from student s1 where sage >(select avg(sage) from student s2 where s1.sdept=s2.sdept)
--查询比本人平均成绩高的学生学号和课程号
select * from sc s1 where grade >(select avg(grade) from sc s2 where s1.sno=s2.sno)
--查询所有选修了001号课程的学生姓名
select student.sname  from student,sc where student.sno=sc.sno and sc.cno='001' --等值连接查询
select sname  from student where exists(select * from sc where sno=student.sno and cno='001')
--查询所有未选修001号课程的学生姓名
select sname from student where not exists(select * from sc where sno=student.sno and cno='001')
--查询选修了所有课程的学生姓名和所在系
select sname,sdept from student where not exists(select * from course where not exists(select * from sc where sno=student.sno and cno=course.cno))
--查询至少选修了'08002'选修的全部课程的学生学号
select distinct sc.sno from sc,sc sc1 where not exists(select * from sc,sc sc2 where sc2.sno='08002' and not exists(select * from sc,sc sc3 where sc3.sno=sc1.sno and sc3.cno=sc2.cno))
--用avg集函数统计所有学生的平均年龄
select (select sum(sage) from student)/count(*),avg(sage) from student
--用子查询作派生表,查询所有学生的学号和姓名
select A.* from (select sno,sname from student)A
--集合查询
--查询计算机科学系的学生及年龄不大于19岁的学生
select * from student where sdept='cs' union select * from student where sage<19
select * from student where sdept='cs' or sage<19
--查询选修了课程001或者选修了课程002的学生学号
select sno from sc where cno='001' union select sno from sc where cno='002'
select sno from sc where cno='001' or cno='002'
select sno from sc where cno in('001','002')
---查询既选修了课程001又选修了课程002的学生学号
select sno from sc where cno='001' intersect select sno from sc where cno='002'
select sno from sc where cno='001' and sno in(select sno from sc where cno='002')
select a.sno from sc a,sc b where a.sno=b.sno and a.cno='001' and b.cno='002'
--查询选修了课程001但没有选修课程002的学生学号
--select sno from sc where cno='001' and sno in(select sno from sc where cno<>'002')
select sno from sc where cno='001' and sno not in(select sno from sc where cno='002')
select * from student where sno<>'08001' order by sno
--select sno from sc where sno in(select sno from sc where cno<>'002')
select sno from sc where sno not in(select sno from sc where cno='002')
--将一个新学生记录插入student表
insert into student values('08020','李丹','男',18,'is')
--插入一个选课记录
insert into sc(sno,cno) values('08002','001')
select * from sc
--对每一个系求学生的平均年龄，并把结果存入数据库
create table Deptage(Sdept char(15),Avgage smallint);
insert into Deptage(Sdept,Avgage) select Sdept,avg(sage) from student group by sdept;
select * from Deptage
select * from sc
insert into sc(sno,cno) select sno,cno from student,course  --增加的sc表的行
select sno,cno,0 as grade into ok from student,course
select * from ok
--学生08001的年龄改为22岁
update student set sage=22 where sno='08001'
update student set sage=sage+1
select * from student
--将计算机科学系全体学生的成绩置0
update sc set grade=0 where 'cs'=(select sdept from student where student.sno=sc.sno)
update sc set grade=0 where sno in(select sno from student where sdept='cs')
--把选修了课程名为"数据库"的课程的学生的成绩改为0
update sc set grade=0 where cno = (select cno from course where cname='数据库')
--删除学号为08002的学生记录
delete from student where sno='08002'
select * from sc
delete from sc
--删除计算机系所有学生的选课记录
delete from sc where 'cs'=(select sdept from student where student.sno=sc.sno)
delete from sc where sno in(select sno from student where sdept='cs')
--建立用户,授予权限
sp_addlogin noruser,adminadmin,litengyue
sp_adduser noruser,noruser
grant create database,create view to noruser
grant update(lastname),select on employees to noruser
grant all privileges on employees to noruser with grant option
--收回权限
revoke update(lastname) on employees from noruser
revoke select on employees from publc
revoke create view from noruser
--拒绝权限
deny select on employees from public
deny create database,create view to noruser
--建立计算机系学生的视图
create view vw_student_cs as select sno,sname,sage from student where sdept='cs'
--由student,course,sc三个表，定义一个计算机系的学生成绩试图，其属性包括学号，姓名，课程名，成绩
create view vw_Stugrade(Stuno,Stuname,Stucourse,Stugrade) as select student.sno,student.sname,course.cname,sc.grade from student,course,sc where (student.sno=sc.sno) and (course.cno=sc.cno) and sdept='cs'
--将学生学号，总成绩，平均成绩定义成一个试图
create view vw_GradeState as select sno,sum(grade) as Totalgrade,avg(grade) as Avg from sc group by sno
--将有不及格情况的学生学号姓名课程名成绩定义成一个视图，并限制对视图的更新操作不能超过视图条件限制
create view vw_StuFail as select student.sno,student.sname,course.cname,sc.grade from student,course,sc where (student.sno=sc.sno) and (course.cno=sc.cno) and grade<60 with check option
--将有先修课的课程号，课程名，学分定义成一个视图，便于查询
select * from course
create view vw_Pcourse as select cno,cname,ccredit from course where cpno is not NULL
--修改视图
alter view vw_StuFail as select student.sno,student.sname,course.cname,sc.grade from student,course,sc where(student.sno=sc.sno) and (course.cno=sc.cno) and grade>=90 with check option
--查询视图
select * from vw_student_cs  where sage<20
select * from student where sdept='cs' and sage<20
--更新视图
update vw_student_cs set sname='zhang li' where sno='08001'
update student set sname='zhang li' where sno='08001' and sdept='cs'
update vw_GradeState set avg=90 where sno='08001'--notice--
--删除视图
drop view vw_GradeState[/cpp]

第二部分：较复杂查询

[cpp]查询所有选修过课程的学生学号
select distinct Sno from sc where cno is not NULL;
--查询每门课程不及格学生人数
select cno,count(sno) from sc where grade<60 group by cno
--查询不及格课程超过3门的学生学号
select sno,count(grade) from sc where grade <60 group by sno having count(grade)>=3
--查询年龄为10-19岁的学生信息
select * from student where sage between 10 and 19
--查询全体学生情况，按所在系升序排列，同一个系的学生按年龄降序排列
select * from student order by sdept asc,sage desc
--查询选了1号课程的学生平均成绩
select avg(grade) from sc where cno='001'
--查询选了3号课程的学生的最高分
select max(grade) from sc where cno='003'
--查询每个同学的总成绩
select sno,sum(grade) as grade from sc group by sno
--实验五
--1、查询每个学生及其选课情况
select a.*,b.* from student a,sc b where a.sno=b.sno
--2、查询每门课地间接先修课
select first.cno,second.cpno from course first,course second where first.cpno=second.cno
--3、将student，sc进行右链接
select student.*,sc.* from student right join sc on student.sno=sc.sno
--4、查询有不及格学生的姓名和所在系
select sname,sdept from student where sno in (select sno from sc where grade<60)
--5、查询所有成绩为优秀（>90）的学生姓名
select sname from student,sc where student.sno=sc.sno group by sname having min(grade)>=90
--6、查询既选修了2号课程又选修了3号课程的学生姓名、学号
select sname,sno from student where sno in(select sno from sc where cno='001' and sno in (select sno from sc where cno='002'))
--7、查询和刘晨同一年龄的学生
select * from student where sage =(select sage from student where sname='刘晨')
--8、选修了课程名为“数据库”的学生姓名和年龄
select * from student select * from course
select sname,sage from student where sno in(select sno from sc where cno in(select cno from course where cname='数据库'))
--9、查询其他系比is系任一学生年龄小的学生名单
select sname from student where sage< any(select sage from student where sdept='is')and sdept<>'is'
--10、查询其他系中比is系所有学生年龄都小的学生名单
select sname from student where sage < all(select sage from student where sdept='is')and sdept<>'is'
--11、查询选修了全部课程的学生姓名
select sname from student where sno in (select sno from sc where cno in (select cno from sc where sno='08001') and sno in(select sno from sc group by sno having count(cno)=7))
--12、查询计算机系学生及其性别是男的学生
select * from student where sdept ='cs' and ssex='男'
--13、查询选修课程1的学生集合和选修2号课程学生集合的差集
select sno from sc where cno='001' and sno not in (select sno from sc where cno='002')
--14、查询李丽同学不学的课程的课程号
select cno from course where cno not in (select cno from sc where sno =(select sno from student where sname='李丽'))
--15、查询选修了3号课程的学生平均年龄
select avg(sage) from student where sno in (select sno from sc where cno='003')
--16、求每门课程学生的平均成绩
select sno,avg(grade) from sc group by sno
--17、统计每门课程的学生选修人数（超过3人的才统计）。要求输出课程号和选修人数，结果按人数降序排列，若人数相同，按课程号升序排列
select cno,count(cno) as number from sc group by cno having count(cno)>3 order by  count(cno)desc ,cno asc
--18、查询学号比刘晨大，而年龄比他小的学生姓名
select sname from student where sno>(select sno from student where sname='刘晨') and sage<(select sage from student where sname='刘晨')
--19、求年龄大于女同学平均年龄的男同学姓名和年龄
select sname,sage from student where sage>(select avg(sage) from student where ssex='女')and ssex='男'
--20、求年龄大于所有女同学年龄的男同学姓名和年龄
select sname,sage from student where sage>all(select sage from student where ssex='女') and ssex='男'
--21、查询至少选修了08001选修的全部课程的学生号码
select sno from sc where cno in(select cno from sc where sno='08002') group by sno having count(cno)=(select count(cno) from sc where sno='08002')
--22、查询95001和95002两个学生都选修的课程信息
select * from course where cno in (select cno from sc where sno='08001') and cno in (select cno from sc where sno='08002')
--实验六（应用insert、update、delete语句进行更新操作）
--1、插入如下学生记录（学号：95030，姓名：李莉，年龄：18）
insert into student(Sno,Sname,Sage) values('95030','李莉',18)
--2、插入如下选课记录（95030，1）
insert into sc(Sno,Cno) values('08001','007')
delete sc where sno='95030'
select * from sc
--3、计算机系学生年龄改成20
update student set Sage=20 where Sdept='cs'
--4、数学系所有学生成绩改成0
update student set Sage=0 where Sdept='ma'
--5、把低于总平均成绩的女同学成绩提高5分
select * from sc where grade<(select avg(grade) from sc)and sno in(select sno from student where ssex='女')
update sc set Grade=Grade+5 where grade<(select avg(grade) from sc)and sno in(select sno from student where ssex='女')
select * from sc where sno in (select sno from student where ssex='女')and grade is not null ---avg忽略空值
--6、修改2号课程的成绩，若成绩小于75提高5%，成绩大于75提高4%（两个语句实现，注意顺序）
update sc set grade=grade*1.05 where cno='002' and grade<75
update sc set grade=grade*1.04 where cno='002' and grade>75
--8、删除95030学生信息
delete from sc where sno='95030'
--9、删除sc表中无成绩记录
delete from sc where grade is null
--10、删除张娜的选课记录
delete from sc where sno = (select sno from student where sname='张那')
--11、删除数学系所有学生选课记录
delete from sc where sno in (select sno from student where sdept='ma')
--12、删除不及格的学生选课记录
delete from sc where sno in (select sno from sc where grade<60)
--13、查询每一门课程成绩都大于等于80分的学生学号、姓名和性别，把值送往已经存在的基本表stu（sno，sname，ssex）中
create table stu(Sno char(5),Sname char(20),Ssex char(2))
select * from stu;select * from xtu
insert into stu(Sno,Sname,Ssex)select sno,sname,ssex from student where sno in (select sno from sc group by sno having min(grade)>80)
select sno,sname,ssex into xtu from student where sno in (select sno from sc group by sno having min(grade)>80)
--14、把所有学生学号和课程号连接追加到新表中
select sno+cno as newline into xdu from sc
select * from xdu
--15、所有学生年龄增加1
update student set sage=sage+1
--16、统计3门以上课程不及格的学生把相应的学生姓名、系别追加到另外一个表中
select sname,sdept into newtable from student where sno in (select sno from sc where grade<60 group by sno having count(grade)>=3)
select * from newtable
create table ntable(Sname char(20),Sdept char(2))
insert into ntable select sname,sdept from student where sno in (select sno from sc where grade<60 group by sno having count(grade)>=3)
select * from ntable[/cpp]