常用的MySQL查询语句

期末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]