数据库入门查询语句

创建表 

-- 学生表
CREATE TABLE STUDENT
(
    ID INT PRIMARY KEY NOT NULL,
    STUDENT_NAME VARCHAR2(80),
    BIRTHDAY DATE,
    SEX VARCHAR2(2)
)
-- 课程表
CREATE TABLE COURSE
(
    ID INT PRIMARY KEY NOT NULL,
    COURSE_NAME VARCHAR2(80),
    TEACHER_ID INT
)
-- 教师表
CREATE TABLE TEACHER
(
    ID INT PRIMARY KEY NOT NULL,
    TEACHER_NAME VARCHAR2(80)
)
-- 成绩表
CREATE TABLE SCORE 
(
    ID INT PRIMARY KEY NOT NULL,
    STUDENT_ID INT,
    COURSE_ID INT,
    SCORE INT
)

添加表数据

-- 学生信息
INSERT INTO STUDENT VALUES(01 , '赵雷' , to_date('1990-01-01','YYYY-MM-DD') , 'm');
INSERT INTO STUDENT VALUES(02 , '钱电' , to_date('1990-12-21','YYYY-MM-DD') , 'm');
INSERT INTO STUDENT VALUES(03 , '孙⻛' , to_date('1990-12-20','YYYY-MM-DD') , 'm');
INSERT INTO STUDENT VALUES(04 , '李云' , to_date('1990-12-06','YYYY-MM-DD') , 'm');
INSERT INTO STUDENT VALUES(05 , '周梅' , to_date('1991-12-01','YYYY-MM-DD') , 'f');
INSERT INTO STUDENT VALUES(06 , '吴兰' , to_date('1992-01-01','YYYY-MM-DD') , 'f');
INSERT INTO STUDENT VALUES(07 , '郑⽵' , to_date('1989-01-01','YYYY-MM-DD') , 'f');
INSERT INTO STUDENT VALUES(09 , '张三' , to_date('2017-12-20','YYYY-MM-DD') , 'f');
INSERT INTO STUDENT VALUES(10 , '李四' , to_date('2017-12-25','YYYY-MM-DD') , 'f');
INSERT INTO STUDENT VALUES(11 , '李四' , to_date('2012-06-06','YYYY-MM-DD') , 'f');
INSERT INTO STUDENT VALUES(12 , '赵六' , to_date('2013-06-13','YYYY-MM-DD') , 'f');
INSERT INTO STUDENT VALUES(13 , '孙七' , to_date('2014-06-01','YYYY-MM-DD') , 'f');
 
-- 课程信息
INSERT INTO COURSE VALUES(01 , '语⽂' , 02);
INSERT INTO COURSE VALUES(02 , '数学' , 01);
INSERT INTO COURSE VALUES(03 , '英语' , 03);
 
-- 教师信息
INSERT INTO TEACHER VALUES(01 , '张三');
INSERT INTO TEACHER VALUES(02 , '李四');
INSERT INTO TEACHER VALUES(03 , '王五');
 
-- 成绩
INSERT INTO SCORE VALUES(1,1 , 1 , 80);
INSERT INTO SCORE VALUES(2,1 , 2 , 90);
INSERT INTO SCORE VALUES(3,1 , 3 , 99);
INSERT INTO SCORE VALUES(4,2 , 1 , 70);
INSERT INTO SCORE VALUES(5,2 , 2 , 60);
INSERT INTO SCORE VALUES(6,2 , 3 , 80);
INSERT INTO SCORE VALUES(7,3 , 1 , 80);
INSERT INTO SCORE VALUES(8,3 , 2 , 80);
INSERT INTO SCORE VALUES(9,3 , 3 , 80);
INSERT INTO SCORE VALUES(10,4 , 1 , 50);
INSERT INTO SCORE VALUES(11,4 , 2, 30);
INSERT INTO SCORE VALUES(12,4, 3 , 20);
INSERT INTO SCORE VALUES(13,5 , 1, 76);
INSERT INTO SCORE VALUES(14,5, 2, 87);
INSERT INTO SCORE VALUES(15,6 , 1, 31);
INSERT INTO SCORE VALUES(16,6 , 3, 34);
INSERT INTO SCORE VALUES(17,7 , 2 , 89);
INSERT INTO SCORE VALUES(18,7, 3 , 98);

查询题型大全

-- 1.查询"数学 "课程比" 语文 "课程成绩高的学生的信息及课程分数
select* from 
(select * from score where COURSE_ID=1)c,
(select * from score where COURSE_ID=2)m
where c.student_id=m.student_id
and m.score>c.score


-- 1.1 查询同时存在" 数学 "课程和" 语文 "课程的情况
select * from
(select * from score where COURSE_ID=1)c,
(select * from score where COURSE_ID=2)m
where c.student_id=m.student_id


-- 1.2 查询存在" 数学 "课程但可能不存在" 语文 "课程的情况(不存在时显示为 null )
select * from
(select * from score where COURSE_ID=1)c
right join 
(select * from score where COURSE_ID=2)m
on c.student_id=m.student_id


-- 1.3 查询不存在" 数学 "课程但存在" 语文 "课程的情况
select * from
(select * from score where COURSE_ID=1)c
left join 
(select * from score where COURSE_ID=2)m
on c.student_id=m.student_id*


-- 2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT  STUDENT_ID,AVG(SCORE)  FROM STUDENT
JOIN SCORE ON STUDENT.ID=SCORE.STUDENT_ID
GROUP BY STUDENT_ID
HAVING AVG(SCORE)>=60
 
 
-- 3.查询在 成绩 表存在成绩的学生信息
SELECT DISTINCT STUDENT_NAME,SCORE, COURSE_ID,STUDENT_ID FROM STUDENT
LEFT JOIN SCORE ON STUDENT.ID=SCORE.STUDENT_ID
 
 
-- 4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
SELECT A.ID,A.STUDENT_NAME,B.COU,B.SCORE FROM STUDENT A
LEFT JOIN
(SELECT STUDENT_ID,COUNT(*) COU,SUM(SCORE) SCORE FROM SCORE
GROUP BY STUDENT_ID 
)B ON A.ID=B.STUDENT_ID
 
 
-- 4.1 查有成绩的学生信息
SELECT DISTINCT STUDENT_ID,STUDENT_NAME,SCORE, COURSE_ID FROM STUDENT
LEFT JOIN SCORE ON STUDENT.ID=SCORE.STUDENT_ID
 

-- 5.查询「李」姓老师的数量
SELECT * FROM TEACHER
WHERE TEACHER_NAME LIKE '李%'


-- 6.查询学过「张三」老师授课的同学的信息
SELECT * FROM SCORE 
LEFT JOIN STUDENT ON SCORE.STUDENT_ID=STUDENT.ID
WHERE  COURSE_ID IN
(
SELECT ID FROM  COURSE
WHERE TEACHER_ID IN (SELECT ID FROM TEACHER WHERE TEACHER_NAME='张三')
)


-- 7.查询没有学全所有课程的同学的信息
SELECT STUDENT.* FROM STUDENT 
RIGHT JOIN
(
SELECT STUDENT_ID,COUNT(*) FROM SCORE
GROUP BY STUDENT_ID
HAVING COUNT(*)>=3
 )SCORE ON  STUDENT.ID=SCORE.STUDENT_ID


-- 8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
SELECT STUDENT_ID,STUDENT_NAME, COURSE_ID FROM STUDENT,SCORE
WHERE STUDENT.ID=SCORE.STUDENT_ID
AND  COURSE_ID IN(SELECT  COURSE_ID FROM SCORE WHERE STUDENT_ID='01')
 

--多表查询同时查学生表和信息表
SELECT STUDENT_ID,STUDENT_NAME,COURSE_ID FROM STUDENT,SCORE
WHERE STUDENT.ID=SCORE.STUDENT_ID
AND COURSE_ID IN (SELECT COURSE_ID FROM SCORE WHERE STUDENT_ID=01)
 
 
-- 9.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
SELECT STUDENT_ID FROM SCORE WHERE COURSE_ID IN
(SELECT COURSE_ID FROM SCORE WHERE STUDENT_ID='01')
GROUP BY STUDENT_ID
HAVING COUNT(*)=(SELECT COUNT(*) FROM SCORE WHERE STUDENT_ID=01)
 

--方法二：
SELECT STUDENT_ID,COUNT(*) FROM SCORE
WHERE STUDENT_ID!= 1
GROUP BY STUDENT_ID
HAVING COUNT(*)=(select count(*) from course )


-- 10.查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT * FROM STUDENT
WHERE ID NOT IN
(
    SELECT STUDENT_ID FROM SCORE
    WHERE COURSE_ID IN
(
    SELECT ID FROM COURSE
    WHERE TEACHER_ID IN
(   SELECT ID FROM TEACHER 
    WHERE TEACHER_NAME='张三'
)
)
)


-- 11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT * FROM STUDENT 
RIGHT JOIN  
(
SELECT STUDENT_ID,COUNT(*),AVG(SCORE) FROM SCORE
WHERE SCORE<60
GROUP BY STUDENT_ID
HAVING COUNT(*)>=2
) SCORE ON STUDENT.ID=SCORE.STUDENT_ID
-- 12.检索" 数学 "课程分数小于 60，按分数降序排列的学生信息
SELECT * FROM SCORE
WHERE COURSE_ID='3' AND SCORE<60
ORDER BY SCORE DESC


-- 13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select student_id,sum(chinese)chinese,sum(math)math,sum(english)english,avg(nvl(chinese,0)+nvl(math,0)+nvl(english,0))as df from
 
(select student_id,
(case when course_id=1 then score end)chinese,
(case when course_id=2 then score end)math,
(case when course_id=3 then score end)english
from score)
group by student_id
order by df desc


-- 14.查询各科成绩最高分、最低分和平均分：
select course_id,max(score),min(score),avg(score) from score group by course_id
 
 
-- 15.以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
/*
  及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
  要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
  按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
*/  
 
select COURSE_ID,max(score),min(score),avg(score),count(*) 选修人数,
sum(case when score>=60 then 1 end)/count(*)及格率,
sum(case when score>=70 and score<80 then 1 end)/count(*)中等,
sum(case when score>=80 and score<90 then 1 end)/count(*)优良,
sum(case when score>=90 then 1 end)/count(*)优秀
from score 
group by COURSE_ID

 
-- 15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次
select * from
(select a.*,dense_rank() over (partition by COURSE_ID order by score desc)b from score a)
-- 16.查询学生的总成绩，并进行排名，总分重复时保留名次空缺
select sco1.student_id,sum(sco1.score) sum_score,
rank() over(order by (sum(sco1.score)) desc) rank
from score sco1
group by sco1.student_id


-- 16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
select sco1.student_id,sum(sco1.score) sum_score,
dense_rank() over(order by (sum(sco1.score)) desc) rank
from score sco1
group by sco1.student_id


-- 17.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
select COURSE_ID,max(score),min(score),avg(score),count(*) 选修人数,
round(sum(case when score>=60 then 1 else 0 end)/count(*),2)*100 || '%'及格率,
round(sum(case when score>=70 and score<80 then 1 else 0 end)/count(*),2)*100 || '%'中等,
round(sum(case when score>=80 and score<90 then 1 else 0 end)/count(*),2)*100 || '%'优良,
round(sum(case when score>=90 then 1 else 0 end)/count(*),2)*100 || '%'优秀
from score 
group by COURSE_ID


-- 18.查询各科成绩前三名的记录
select * from
(select a.*,dense_rank() over (partition by COURSE_ID order by score desc)s from score a)
where s<=3
 
 
-- 19.查询每门课程被选修的学生数
select COURSE_id, count(student_id) from score
group by COURSE_id


-- 20.查询出只选修两门课程的学生学号和姓名
SELECT STUDENT_ID,COUNT(COURSE_ID) FROM STUDENT
LEFT JOIN SCORE ON STUDENT.ID=SCORE.STUDENT_ID
GROUP BY STUDENT_ID
HAVING COUNT(COURSE_ID)=2


-- 21.查询男生、女生人数
SELECT SEX,COUNT(*) FROM STUDENT
GROUP BY SEX 

 
-- 22.查询名字中含有「风」字的学生信息
SELECT * FROM STUDENT 
WHERE STUDENT_NAME LIKE '%风%' 
 

-- 23.查询同名同性学生名单，并统计同名人数(错0
SELECT STUDENT_NAME,
COUNT(*) FROM STUDENT
GROUP BY STUDENT_NAME
HAVING COUNT(STUDENT_NAME)>1
 

-- 24.查询 1990 年出生的学生名单
select student_name,  BIRTHDAY from student
where to_char(  BIRTHDAY,'yyyy')='1990'
 

-- 25.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT COURSE_ID,AVG(SCORE) FROM SCORE 
GROUP BY COURSE_ID
ORDER BY AVG(SCORE) DESC,COURSE_ID ASC


-- 26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
SELECT SCORE.STUDENT_ID,student_name,
AVG(SCORE) FROM STUDENT
RIGHT JOIN SCORE ON STUDENT.ID=SCORE.STUDENT_ID
GROUP BY SCORE.STUDENT_ID,student_name
HAVING AVG(SCORE) >=85
 
 
-- 27.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
select student_name,score.score,course.course_name from student 
join score on student.id=score.student_id
join  course on score.course_id=course.id
where course.course_name='数学' and score<60


-- 28.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
/*学生表连接成绩表 左表是student表，主表score表*/
SELECT STUDENT_NAME,COURSE_ID,SCORE FROM STUDENT 
LEFT JOIN SCORE ON STUDENT.ID=SCORE.STUDENT_ID
 

-- 29.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
/*学生表连接课程表和成绩表，多表查询内联查询inner*/
SELECT STUDENT_NAME,COURSE_NAME,SCORE FROM STUDENT 
JOIN SCORE ON STUDENT.ID=SCORE.STUDENT_ID
JOIN COURSE ON SCORE.COURSE_ID=COURSE.ID
WHERE SCORE >70


-- 30.查询不及格的课程
-- 方法一
SELECT DISTINCT COURSE_NAME FROM COURSE
JOIN SCORE ON COURSE.ID=SCORE.COURSE_ID
WHERE SCORE<60

 
--方法二
SELECT DISTINCT COURSE_NAME,STUDENT_NAME,SCORE FROM STUDENT
JOIN SCORE ON STUDENT.ID=SCORE.STUDENT_ID
JOIN COURSE ON SCORE.COURSE_ID=COURSE.ID
WHERE SCORE<60

 
-- 31.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
/*
查询student表 连接score表 
score是主表，条件COURSE_id=01 and score>80
*/
 
SELECT STUDENT_ID,STUDENT_NAME,SCORE,COURSE_id FROM STUDENT
JOIN SCORE ON STUDENT.ID=SCORE.STUDENT_ID
WHERE COURSE_ID=01 AND SCORE>=80 ;
 

-- 32.求每门课程的学生人数
/*每门课表示要对课程号分组*/
select COURSE_id, count(student_id) from score
group by COURSE_id
-- 33.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT c.STUDENT_NAME,a.COURSE_ID,a.SCORE FROM score a
left join score b on a.score<=b.score and a.course_id=b.course_id
JOIN student c ON a.student_id=c.id
JOIN COURSE d ON a.COURSE_ID=d.id
JOIN TEACHER e ON e.id=d.teacher_id
WHERE e.TEACHER_NAME='张三' 
group by c.STUDENT_NAME,a.COURSE_ID,a.SCORE
having count(a.student_id)<=1


--34.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
/**/
select * from student a
join
(select course_id,max(score) from score where course_id=2 group by course_id)j
 on a.id=j.course_id


-- 35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
/*首先把语文成绩数学成绩英语成绩搞出来，*/
--方法一
SELECT * FROM 
(SELECT * FROM SCORE WHERE COURSE_ID =1)A,
(SELECT * FROM SCORE WHERE COURSE_ID =2)B,
(SELECT * FROM SCORE WHERE COURSE_ID =3)C           
WHERE
A.STUDENT_ID=B.STUDENT_ID AND A.STUDENT_ID=C.STUDENT_ID
AND A.SCORE=B.SCORE AND B.SCORE=C.SCORE
 
 
--方法二
select a.student_id as 学生编号,a.score as 语文成绩,b.score as 数学成绩,c.score as 英语成绩 from 
(select * from score where course_id=1)a,
(select * from score where course_id=2)b,
(select * from score where course_id=3)c
where a.student_id=b.student_id and a.student_id=c.student_id and a.score=b.score and a.score=c.score 
 

-- 36.查询每门功成绩最好的前两名
ROW_NUMBER() OVER (PARTITION BY ORDER BY )/**/
RANK /*并列名次，不覆盖重复的没有名次*/
DENSE_RANK/*并列名次，不覆盖重复的有名次*/ 
select * from
(SELECT A.*,ROW_NUMBER() OVER (PARTITION BY COURSE_ID  ORDER BY SCORE DESC)S FROM SCORE A)/*正确，对对成绩排序（名次）*/
where S<=2
 
 
-- 37.统计每门课程的学生选修人数（超过 5 人的课程才统计）。
SELECT COURSE_ID ,COUNT(STUDENT_ID) AS 人数 FROM SCORE
GROUP BY  COURSE_ID
HAVING COUNT(STUDENT_ID)>5
 
 
-- 38.检索至少选修两门课程的学生学号
SELECT STUDENT_ID,COUNT(COURSE_ID) FROM SCORE 
GROUP BY STUDENT_ID 
HAVING  COUNT(COURSE_ID)>=2


- 39.查询选修了全部课程的学生信息
SELECT STUDENT_ID,STUDENT_NAME FROM STUDENT a
JOIN SCORE ON a.ID=SCORE.STUDENT_ID
GROUP BY  STUDENT_ID,STUDENT_NAME
HAVING COUNT(COURSE_ID )=(SELECT COUNT(ID ) FROM COURSE)
 
 
-- 40.查询各学生的年龄，只按年份来算
SELECT STUDENT_NAME, BIRTHDAY ,
to_char(sysdate,'yyyy')-TO_CHAR(birthday,'yyyy')as 年龄 FROM STUDENT
 
 
-- 41.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select student_name,birthday,floor((sysdate-birthday)/365) age from student


-- 42.查询本周过生日的学生
select student_name,birthday from student
where to_char(sysdate,'ww')=to_char(birthday,'ww')


-- 43.查询下周过生日的学生
select student_name,birthday from student
where to_char(sysdate,'ww')=to_char(birthday,'ww')


-- 44.查询本月过生日的学生
select student_name,birthday from student
where to_char(sysdate,'mm')=to_char(birthday,'mm')


-- 45.查询下月过生日的学生
select student_name,birthday from student
where to_char(sysdate,'mm')=to_char(birthday,'mm')