SQL数据库常⽤查询总结
查询⽅法
⼀、简单查询
（1）利⽤T-SQL语句在“teaching”数据库中实现下列数据查询操作：
1. 求数学系学⽣的学号和姓名。点击新建查询，输⼊并执⾏以下语句
USE teaching
SELECT学号=sno，姓名=sname FROM student
2.求选修了课程的学⽣学号。点击新建查询，输⼊并执⾏以下语句
USE teaching
SELECT*FROM student WHERE specialty  IS NOT NULL
3. 求选修C1课程的学⽣学号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同则按学号的升序排列。点击新建查询，输⼊并
执⾏以下语句
use teaching
select sno, score  from sc where cno='C001'
order by score desc,sno desc
4. 求选修课程C1且成绩在80～90分之间的学⽣学号和成绩，并将成绩乘以系数0.8输出。点击新建查询，输⼊并执⾏以下语句
use teaching
select sno, score=score*0.8
from sc where cno='C001'and score>=80and score<=90
5. 求数学系或计算机系姓张的学⽣的信息。点击新建查询，输⼊并执⾏以下语句
use teaching
select*from student where specialty='数学'or specialty='计算机'
6.求缺少了成绩的学⽣的学号和课程号。点击新建查询，输⼊并执⾏以下语句
use teaching
select*from sc where score is null
⼆、连接查询
1.查询每个学⽣的情况以及他(她)所选修的课程。点击新建查询，输⼊并执⾏以下语句
use teaching
select student.*,sco
from student left join sc
on student.sno=sc.sno
2.求学⽣的学号、姓名、选修的课程名及成绩。点击新建查询，输⼊并执⾏以下语句
use teaching
select student.sno,student.sname,sco,sc.score
from student join sc
on student.sno=sc.sno
3.求选修C1课程且成绩为90分以上的学⽣学号、姓名及成绩。点击新建查询，输⼊并执⾏以下语句
select student.sno,student.sname,sc.score
from student  join sc
on student.sno=sc.sno and sco='C001'and sc.score>='90'
4.查询每门课的选课⼈数。点击新建查询，输⼊并执⾏以下语句
use teaching
select  cno,count(sno)as⼈数from sc
group by cno
5.查询每个学⽣每⼀门课的选修情况。点击新建查询，输⼊并执⾏以下语句
use teaching
select  student.*,sc.*
from  student,sc
where student.sno=sc.sno
三、⼦查询
1.求选修了⾼等数学的学⽣学号和姓名。点击新建查询，输⼊并执⾏以下语句
use teaching
select student.sno,student.sname
from student join sc
on student.sno=sc.sno join course on sco=courseo and courseame='⾼等数学'
2.求C1课程的成绩⾼于张三的学⽣学号和成绩。点击新建查询，输⼊并执⾏以下语句
use teaching
select student.sno,sc.score
from student join sc
on student.sno=sc.sno and sco='C001'
and sc.score>
(select sc.score from student,
sc where student.sno=sc.sno and student.sname='张三')
3. 求其他系中⽐计算机系某⼀学⽣年龄⼩的学⽣(即求年龄⼩于计算机系年龄最⼤者的学⽣)。点击新建查询，输⼊并执⾏以下语句
use teaching
select student.*
from student
where sage <any(select student.sage from student where student.specialty='计算机')
4.求其他系中⽐计算机系学⽣年龄都⼩的学⽣（即求年龄⼩于计算机系年龄最⼩者的学⽣）。点击新建查询，输⼊并执⾏以下语句
use teaching
select student.*
from student
where sage <all(select student.sage from student where student.specialty='计算机')
5.求选修了数据库课程的学⽣姓名。点击新建查询，输⼊并执⾏以下语句
use teaching
select student.sname
from student
where student.sno=
(select sc.sno from sc where sco=(select courseo from course where cname='数据库'))
6.求没有选修C2课程的学⽣姓名。点击新建查询，输⼊并执⾏以下语句
select student.sname
from student
where student.sno =any(select sc.sno from sc where sco!='C002')
7. 查询选修了C1和C2课程的学⽣的姓名。点击新建查询，输⼊并执⾏以下语句
use teaching
select student.sname
from student
where student.sno =any(
常用的sql查询语句有哪些
select sc.sno
from sc
where sco='C002'or sco='C001')
8.求选修了学号为“S2”的学⽣所选修的课程的学⽣学号和姓名。点击新建查询，输⼊并执⾏以下语句
use teaching
select student.sname,student.sno
from student join sc
on student.sno = sc.sno
and sco=any(select sco
from student join  sc
on student.sno = sc.sno
and student.sno='S2')
四、组合查询与统计查询实验
1. 查这样的图书类别：要求类别中最⾼的图书定价不低于全部按类别分组的图书平均定价的2倍。
use图书借阅
select类别
from图书
group by类别
having MAX(定价)
>=all(
(select AVG(定价)*2from图书
group by类别
)
)
2. 求机械⼯业出版社出版的各类图书的平均定价，⽤GROUP BY表⽰。
use图书借阅
select类别,avg(定价)平均定价
from图书
where出版社='机械⼯业出版社'
group by类别
3. 列出计算机类图书的书号、名称及价格，求出册数和总价格。
use图书借阅
select书号，书名，定价
from图书
where类别='计算机'
compute count(书名),sum(定价)
4.列出计算机类图书的书号、名称及价格，并求出各出版社这类书的总价格和总册数。
select书号，书名，定价
from图书
where类别='计算机'
order by出版社
compute count(书名),sum(定价)by出版社
5.查询计算机类在机械⼯业出版社出版的图书。
use图书借阅
select图书.*
from图书
where出版社='机械⼯业出版社'and类别='计算机'

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。