数据库-查询语句习题

最新推荐文章于 2024-09-09 16:50:56 发布

小学生10086

最新推荐文章于 2024-09-09 16:50:56 发布

阅读量1k

点赞数 22

分类专栏：数据库文章标签：数据库 sql

本文链接：https://blog.csdn.net/2301_78066243/article/details/139784759

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

SELECT Sname '姓名','year of birth:' 出生年,YEAR(GETDATE())-Sage BIRTHYEAR,LOWER(SNAME) SNAME --起别名没有特殊字符不需要引号，有特殊字符要加引号；别名（解释作用显示给用户看）用空格或as连接
FROM STUDENT;
--消除重复行 DISTINCT
--查询选修了课程的学生学号在SC中出现表示选课了
SELECT DISTINCT Sno
From SC
WHERE CNO IS NOT NULL;
--比较大小
--查询年龄在20岁以下全体学生的名单
SELECT Sname
FROM Student
WHERE Sage<20;
--确定范围 between and
--查年龄20-23的sname sage
SELECT SNAME,SAGE
FROM Student
WHERE SAGE BETWEEN 20 AND 23;
--确定集合 in not in 属于不属于差
SELECT SNAME,SSEX
FROM STUDENT
WHERE SSEX IN('男','女');
--字符匹配 like 字符串查询没有通配符=与like没啥区别
--通配符不精确查询对象 _下划线：匹配单个字符 %百分号：匹配任意长度（包括0）字符串【】中括号：匹配其中任意字符
--查询所有姓刘赵王学生SNAME SNO SSEX 查多个，可用【】括起来
SELECT SNAME,SNO,SSEX
FROM STUDENT
WHERE SNAME LIKE '[赵王张]%';
--姓欧阳全名三个字只要不是固定长度的数据尽量用VARCHAR类型不然之后补空格无法处理定长字符串把后边的空格缩成最后一个空格了？？？？？？？？
--查找的东西有%_，通配符不当通配符，转码字符\只要不是查询的内容内就可以,通过关键字ESCAPE来指示
--查DB_Design课程的课程号
SELECT CNO
FROM COURSE
WHERE CNAME LIKE 'DB\_Design' ESCAPE '\';
--空值查询IS NULL IS NOT NULL 空值参与运算不是等于NULL
--多重条件查询 AND OR条件组合用OR实现IN运算区分逻辑运算的并交
--对查询结果排序 ORDER BY ASC升 DESC降 ASC可省略可加TOP 数，TOP几几 persent 前百分之几（最好的）
--查询选修了3号课程的，结果按分数降序排列
SELECT SNO,GRADE
FROM SC
WHERE CNO='3'
ORDER BY GRADE DESC
--使用聚集函数统计数据聚集函数/统计函数/集合函数：作用是对数据进行汇总和统计通过满足条件的多行进行操作
--主要聚集函数：统计元组个数COUNT（*/distinct默认不去除重复）统计一列中值的个数COUNT（<列名>）计算一列值的总和SUM 最大值MAX 最小值MIN
--聚集函数不能嵌套
--查询学生总数
SELECT COUNT(*)
FROM STUDENT
--查询选修课程的学生人数
SELECT COUNT(DISTINCT SNO)
FROM SC;
--计算一号课程的学生平均成绩 AVG与Sno不是一一对应
SELECT AVG(GRADE)
FROM SC
WHERE Cno='1';
--对查询结果分组 GROUP BY，完成分组统计和限定一些分组应满足的条件，分完组后聚集函数对每个组运算;若要决定某个组满足某个条件HAVING；
--求各个课程号及相应选课人数 CNO与人数一一对应了
SELECT CNO,COUNT(*)
FROM SC
GROUP BY CNO --课程号分组
--查询选修了三门以上课程学号 where只能判断当前行，不能整聚集函数判断满足条件的组（HAVING）
SELECT SNO,COUNT(*)
FROM SC
GROUP BY SNO
HAVING COUNT(*)>2;
--统计各个系各个出生年龄的学生人数聚集函数之外的表达式必须在组内起别名后，排序可以用别名（排序是对得到结果进行显示的），其余的操作不能用别名
--sno换位sdept
SELECT SNO,YEAR(GETDATE())-Sage,COUNT(*)
FROM STUDENT
GROUP BY SNO,YEAR(GETDATE())-Sage--GROUP BY SDEPT 不行
--连接查询
--两表有相同属性名，要指定哪个表的，.操作符（参与连接的属性）
--非ANSI 表1.列名1 表2.列名2 关系表达式；ANSI FROM 表1，表2 where
--等值非等值连接
--查询每个学生及其选修课程i情况
--重复两次学号，student.sno,sname,.....替换*（自然连接）
SELECT *
FROM STUDENT,SC
--Student join SC
WHERE STUDENT.SNO=SC.SNO;
--ON s.sno=sc.sno
--自身连接整个副本，起别名，起别名后，在查询中任何地方都使用该别名而不使用原来表名
--找间接先修课
SELECT FIRST.CNO,SECOND.CPNO
FROM COURSE FIRST,COURSE SECOND
WHERE FIRST.CPNO=SECOND.CNO;
--FROM COURSE FIRST JOIN COURSE SECOND ON FIRST.Cpno=Second.Cno
--起别名：表名太长，简化表名；两个表相关，起别名，区分
--既学过1有学过2号课的学生学号
select sc1.sno
from sc sc1,sc sc2
where sc1.sno=sc2.sno and sc1.cno='1' and sc2.cno='2';
--1号课程成绩比2号课程成绩高的学生学号
select sc1.sno
from sc sc1,sc sc2
where sc1.sno=sc2.sno and sc1.cno='1' and sc2.cno='2' and sc1.grade>sc2.grade;
--复合条件连接除连接条件外还有其他限制条件WHERENASI 非NASI用AND
--查询选修二号课程且成绩在90以上所有学生
SELECT Student.sno,sname
From student,sc
where student.sno=sc.sno

--查询每个学生学号姓名选修课程名及成绩三表连接成绩大于80
select student.sno,sname,cname,grade
from student,course,sc
where student.sno=sc.sno and sc.cno=course.cno and grade>80;
--ANSI
select student.sno,sname,cname,grade
from student join sc on student.sno=sc.sno
join course on sc.cno=course.cno;
--外连接左外右外全连接full join right join left join outer可加可不加在join前
--学生学号学生姓名课程号成绩没选课也要其成绩
select student.sno,sname,cno,grade
from student left join sc on student.sno=sc.sno;
--查询选修超过三门的学生学号、姓名及选课数量
select student.sno,sname,count(*)
from student,sc
where student.sno=sc.sno
group by student.sno,sname
having count(*)>3;

SELECT student.sno,sname,sclass,cname,chour,grade
FROM student,sc,course
where student.sno=sc.sno and sc.cno=course.cno;

SELECT student.sno,sname,AVG(grade)
FROM student,sc
where student.sno=sc.sno
Group by student.sno,sname
ORDER BY AVG(grade) DESC;

SELECT s2.sno,s2.sname,s2.sclass
FROM sc,course,Student s1,student s2
where s1.sname='王辰' and s1.sno=sc.sno and course.cno=sc.cno and s2.sage=s1.sage;

Select student.sno,sname,grade
From student,sc,course
Where student.sno=sc.sno and sc.cno=course.cno and cname='计算机网络';

--嵌套查询子查询父查询没扫描一个父查询看其是否在子查询子查询不需要去除重复、排序等 in类似自连接 not in类似差运算
--含有in谓词的嵌套查询 where 表达式 not in 子查询先执行子查询，利用子查询结果执行父查询
--查询与刘晨在同一系学习学生
Select sno,sname,sdept
from student
where sdept in (
       select sdept
       from student
       where sname='刘晨');
--自连接
Select s1.sno,s1.sname,s1.sdept
from student s1, student s2
where s1.sdept=s2.sdept and s2.sname='刘晨';
--查询选修课为信息系统学号姓名
Select sno,sname
from student
where sno in (
select sno
from sc
where cno in (
select cno
from course
where cname='信息系统'));
--查询没有选修课程的sname，sno <>不等号，不能使，课程不等于1直接输出了，但是下一行可能还有等于1 使用正向思维，1-正向，用差not in
Select sno,sname
from student
where sno not in (
           select sno
           from sc);
--含有比较运算符的嵌套，确保子查询返回的结果为单值
--刘晨只有一个系
--查询与刘晨在同一系学习学生
Select sno,sname,sdept
from student
where sdept = (
       select sdept
       from student
       where sname='刘晨');
--多值使用any all 某一个所有
--查询其他系中比信息系某一学生年龄小的学生姓名年龄
select sname,sage
from student
where sage<any(
           select sage
           from student
           where sdept='IS')
   and sdept<>'IS';
--也可使用聚集函数最好使用聚集函数，但是不能嵌套
select sname,sage
from student
where sage<(
           select min(sage) from student
           where sdept='is')
   and sdept<>'is';
--相关不相关嵌套查询
--子查询查询条件依赖于父查询子父查询改名
--找每个学生超过它选修课程平均成绩的课程号同一表用两次，相关——改名
select sno,cno
from sc scx--找选课记录对应的sno cno
where grade>(
           select avg(grade)
           from sc scy
           where scy.sno=scx.sno);--找平均成绩高的
--具体解释：首先外层查询取出一个SC元组SCX，然后根据SCX对应的学号即SCX.Sno，去在SC表的另个副本SCY中计算该学生对应学号 SCX.Sno的平均成绩，如果元组SCX对应的成绩超过了计算出的
--平均成绩，则输出元组SCX对应的课程号，否则继续提取下个SC元组重复上述过程，直到外层查询扫描SC表一遍
--带有exists 存在量词得到真假两个逻辑值 not exists 不存在 in not in所有都可用exists not exists做，反过来不一定用存在一般相关子查询，表达能力超强
--查询所有选修一号课程sname,sno
select sname,sno
from student
where exists (
           select *
           from sc
           where sc.sno=student.sno and cno='1');
--刘晨一个系
Select sno,sname,sdept
from student
where sdept in (
       select sdept
       from student
       where sname='刘晨');
--自连接
Select s1.sno,s1.sname,s1.sdept
from student s1, student s2
where s1.sdept=s2.sdept and s2.sname='刘晨';

select sno,sname
from student s1
where exists(
           select *
           from student s2
           where s2.sname='刘晨' and s2.sdept=s1.sdept);
--全称量词改量词否结论否定
--查询选修了所有课程的sname 1）要查询这样的学生student对应的学生姓名sname
select sname
from student
--2)不存在一门课程course
where not exists (
           select *
           from course
--3)student没有选course，也就是不存在一个student选修course的选课记录sc
           where not exists (
                           select *
                           from sc
                           where sc.sno=student.sno and sc.cno=course.cno));
--蕴含逻辑运算 p->q等价非p并q
--查询至少选修201215122选修的全部课程学生号码不存在它选了但是检索到的学生没选
--1)我们要查询这样的学生student对应的学号sno
select sno
from student
--2)不存在一个201215122学生的选课记录scx 后边还涉及选课
where not exists(
               select *
               from sc scx
               where sno='0905010102'
--3)student没有选修scx对应的课程，也即是不存在一个student选修了scx.cno对应课程的选课记录scy
                   and not exists (
                   select *
                   from sc scy
                   where scy.sno=student.sno and scy.cno=scx.cno));--没有这句话就是全部学生学号都输出了
select sno
from student
--2)不存在一个201215122学生的选课记录scx 后边还涉及选课
where not exists(
               select *
               from sc scx
               where sno='201215122'
--3)student没有选修scx对应的课程，也即是不存在一个student选修了scx.cno对应课程的选课记录scy
                   and scx.sno not in (
                   select scy.sno
                   from sc scy
                   where scy.sno=student.sno and scy.cno=scx.cno));
--只差了sno，可以使用sc
--1)我们要查询这样的学生scx应的学号sno
select distinct sno
from sc scx
--2)不存在一个201215122学生的选课记录scy 后边还涉及选课
where not exists(
               select *
               from sc scy
               where sno='201215122'
--3)student没有选修scx对应的课程，也即是不存在一个scx修了scy.cno对应课程的选课记录scz
                   and not exists (
                   select *
                   from sc scz
                   where scz.sno=scx.sno and scz.cno=scy.cno));
--集合查询查询结果元组集合多个select得到的结果由相同结果
--并：自动去除重复Union 【all】 all不去重复
--交：Intersect
--查询同时选修了课程1、2的sno
select sno from sc where cno='1'
intersect
select sno from sc where cno='2';
--不能使用cno=‘1’and cno=‘2’ 一行中不能由两个cno 使用嵌套查询
--差 except
--基于派生表的查询 from表此处可以是一个查询，但是要存成表，临时表
--超过平均值
select sno,cno
from sc,(
           select sno,avg(grade)
           from sc
           group by sno
           as avg_sc(avg_sno,avg_grade))
where sc.sno=avg_sc.avg_sno
   and sc.grade>=avg_grade);
--#选修数据库原理并且分数与王辰该课程成绩相同sno
select student.sno
from course,student,sc sc1,sc sc2
where sc1.sno=student.sno and sc1.cno=course.cno and sc1.sno=sc2.sno
       and course.cname='数据库原理' and sc1.grade=sc2.grade;

select distinct student.sno
from student,sc s1,sc s2,course
where s1.cno=s2.cno and s2.grade=s1.grade and course.cno=s2.cno and cname='数据库原理' and
s2.sno<>'王辰';

select distinct s1.sno
from sc s1,sc s2
where s1.sno=s2.sno and s1.grade=s2.grade and s2.cno in (
   select cno
   from course
   where cname='数据库原理');
select distinct s.sno
from student s
join sc s1 on s.sno=s1.sno
join sc s2 on s1.sno=s2.sno and s2.grade=s1.grade
join course c on s2.cno=c.cno
where c.cname='数据库原理' and s2.sno<>'王辰';
--不及格课程sname sno
select sname,sc.sno
from student,sc
where student.sno=sc.sno and grade<70;
select sname,sno
from student
where sno in (
       select sno
       from sc
       where grade<70);
--数据库原理最高分
select max(grade)
from sc
where grade in (
               select grade
               from course
               where course.cname='数据库原理');
--查询平均成绩80分以上学生sname
select sname
from student
where sno in (
           select sno
           from sc
           group by sno
           having avg(grade)>80);
--查询没有选修数据库原理课程的学生姓名和所在班级
select student.sname,sclass
from student
where sno not in(
           select sno
           from sc
           where cno in(
                   select cno
                   from course
                   where cname='数据库原理'));
--#被所有计算机091班学生选修的课程的cname和chour
select cname,chour
from course
where not exists(
               select *
               from student
               where sclass='计算机091' and not exists(
                                               select *
                                               from sc));
--被所有计算机091班学生选修的课程的cname和chour
SELECT cname, chour
FROM course c
WHERE NOT EXISTS (
SELECT *
FROM student s
WHERE sclass = '计算机091' AND sno NOT IN (
SELECT sno
FROM sc
WHERE sc.cno = c.cno
)
);
--not in与not exists 转换通过一个连接进行
SELECT cname, chour
FROM course c
WHERE NOT EXISTS (
SELECT *
FROM student s
WHERE sclass = '计算机091' AND not exists (
SELECT *
FROM sc
WHERE sc.cno = c.cno and sc.sno=s.sno
)
);
--查询至少选修201215122选修的全部课程学生号码不存在它选了但是检索到的学生没选
--1)我们要查询这样的学生student对应的学号sno
select sno
from student
--2)不存在一个201215122学生的选课记录scx 后边还涉及选课
where not exists(
               select *
               from sc scx
               where sno='0905010102'
--3)student没有选修scx对应的课程，也即是不存在一个student选修了scx.cno对应课程的选课记录scy
                   and not exists (
                   select *
                   from sc scy
                   where scy.sno=student.sno and scy.cno=scx.cno));--没有这句话就是全部学生学号都输出了

--查询年龄超过所在班级平均年龄的学生姓名、性别、年龄、所在班级
select sname,ssex,sage,sclass
from student s1
where sage>(
       select avg(sage)
       from student s2
       where s1.sclass=s2.sclass);

select sno from course,sc
where course.cno=sc.cno and sno=sc.sno and cname='数据库原理' and grade=(
select grade from sc,student,course
where sc.sno=student.sno and course.cno=sc.cno and sname='王辰' and cname='数据库原理');

select top 1 sname from student,sc
where sc.sno=student.sno and sclass='计算机091' and student.sno in (
   select sno
   from sc,course
   where sc.cno=course.cno and cname='数据库原理'
)
order by grade desc;

select sno
from course,sc
where cname='数据库原理' and grade=(
   select grade
   from sc,course,student
   where sc.sno=student.sno and course.cno=sc.cno and sname='王辰' and cname='数据库原理');

select distinct student.sno,student.sname
from student,sc
where student.sno=sc.sno and grade<70;

select max(grade)
from sc,course
where sc.cno=course.cno and cname='数据库原理';

select sname
from sc,student
where sc.sno=student.sno
group by sc.sno,sname
having avg(grade)>70;

select s.sname,s.sclass
from student s
where sno not in(
   select sno
   from sc
   where cno in(
       select cno
       from course
       where cname='数据库原理'));