Oracle数据库的常用语句

最新推荐文章于 2020-09-24 22:51:28 发布

三月小跟班

最新推荐文章于 2020-09-24 22:51:28 发布

阅读量291

点赞数

分类专栏：数据库文章标签： Oracle数据库的常用指令

本文链接：https://blog.csdn.net/Xhj_1314/article/details/82931079

版权

数据库专栏收录该内容

10 篇文章 0 订阅

订阅专栏

/*CREATE TABLE STUDENT
(SNO VARCHAR(3) NOT NULL,
SNAME VARCHAR(4) NOT NULL,
SSEX VARCHAR(2) NOT NULL,
SBIRTHDAY DATE,
CLASS NUMBER NOT NULL);

CREATE TABLE COURSE
(CNO VARCHAR(5) NOT NULL,
CNAME VARCHAR(10) NOT NULL,
TNO VARCHAR(10) NOT NULL);

CREATE TABLE SCORE
(SNO VARCHAR(3) NOT NULL,
CNO VARCHAR(5) NOT NULL,
DEGREE NUMBER NOT NULL);

CREATE TABLE TEACHER
(TNO VARCHAR(3) NOT NULL,
TNAME VARCHAR(4) NOT NULL,
TSEX VARCHAR(2) NOT NULL,
TBIRTHDAY DATE NOT NULL,
PROF VARCHAR(6),
DEPART VARCHAR(10) NOT NULL);*/

/INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,‘曾华’ ,‘男’ ,to_date(‘1977-09-01’,‘yyyy-mm-dd’),95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,‘匡明’ ,‘男’ ,to_date(‘1975-10-02’,‘yyyy-mm-dd’),95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,‘王丽’ ,‘女’ ,to_date(‘1976-01-23’,‘yyyy-mm-dd’),95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,‘李军’ ,‘男’ ,to_date(‘1976-02-20’,‘yyyy-mm-dd’),95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,‘王芳’ ,‘女’ ,to_date(‘1975-02-10’,‘yyyy-mm-dd’),95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,‘陆君’ ,‘男’ ,to_date(‘1974-06-03’,‘yyyy-mm-dd’),95031);
commit;/

/INSERT INTO COURSE(CNO,CNAME,TNO)VALUES (‘3-105’ ,‘计算机导论’,825);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES (‘3-245’ ,‘操作系统’ ,804);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES (‘6-166’ ,‘数据电路’ ,856);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES (‘9-888’ ,‘高等数学’ ,100);
commit;/

/INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,‘3-245’,86);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,‘3-245’,75);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,‘3-245’,68);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,‘3-105’,92);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,‘3-105’,88);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,‘3-105’,76);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,‘3-105’,64);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,‘3-105’,91);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,‘3-105’,78);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,‘6-166’,85);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,‘6-106’,79);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,‘6-166’,81);
commit;/

/INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,‘李诚’,‘男’,to_date(‘1958-12-02’,‘yyyy-mm-dd’),‘副教授’,‘计算机系’);
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,‘张旭’,‘男’,to_date(‘1969-03-12’,‘yyyy-mm-dd’),‘讲师’,‘电子工程系’);
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (825,‘王萍’,‘女’,to_date(‘1972-05-05’,‘yyyy-mm-dd’),‘助教’,‘计算机系’);
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,‘刘冰’,‘女’,to_date(‘1977-08-14’,‘yyyy-mm-dd’),‘助教’,‘电子工程系’);
commit;/

–1、查询Student表中的所有记录的Sname、Ssex和Class列。
/select s.sname,s.ssex,s.class from student s;/
–2、查询教师所有的单位即不重复的Depart列。
/select t.depart from teacher t group by t.depart;/
–3、查询Student表的所有记录。
/select * from student;/
–4、查询Score表中成绩在60到80之间的所有记录。
/select * from score s where s.degree between 60 and 80;
select * from score s where s.degree >=60 and s.degree <=80;/
–5、查询Score表中成绩为85，86或88的记录。
/select * from score s where s.degree in (‘85’,‘86’,‘88’);/
–6、查询Student表中“95031”班或性别为“女”的同学记录。
/select * from student s where s.class=95031 or s.ssex=‘女’;/
–7、以Class降序查询Student表的所有记录。
/select * from student s order by s.class desc;/
–8、以Cno升序、Degree降序查询Score表的所有记录。
/select * from score s order by s.sno,s.cno,s.degree;/
/select from score s order by s.sno desc,s.cno desc,s.degree desc;/
–9、查询“95031”班的学生人数。
/select count(s.sname) 学生总人数 from student s where s.class=‘95031’;/
–10、查询Score表中的最高分的学生学号和课程号。
/select s.sno, s.cno from score s where s.degree = all(select max(degree) from score);/
–11、查询‘3-105’号课程的平均分。
/select s.cno,avg(s.degree) from score s where s.cno=‘3-105’ group by s.cno;/
–12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
/select s.cno,avg(s.degree) from score s where s.cno like’3_%’ group by s.cno having count(s.cno)>=5;/
–13、查询最低分大于70，最高分小于90的Sno列。
/select s.sno,s.degree from score s where s.degree between 70 and 90 ;/
–14、查询所有学生的Sname、Cno和Degree列。
/select s.sname,e.cno,e.degree from student s,score e where s.sno=e.sno;/
–15、查询所有学生的Sno、Cname和Degree列。
/select s.sno,c.cname,e.degree from student s,course c,score e where s.sno=e.sno and c.cno=e.cno;/
–16、查询所有学生的Sname、Cname和Degree列。
/select s.sname,c.cname,e.degree from student s,course c,score e where s.sno=e.sno and c.cno=e.cno;/
–17、查询“95033”班所选课程的平均分。
----------/select round(avg(e.degree),2) from score e left join student s on e.sno=s.sno where s.class=‘95033’;/
–18、假设使用如下命令建立了一个grade表：
/create table grade
(low number,upp number,rank char(1));
insert into grade values(90,100,‘A’);
insert into grade values(80,89,‘B’);
insert into grade values(70,79,‘C’);
insert into grade values(60,69,‘D’);
insert into grade values(0,59,‘E’);
commit;/
–现查询所有同学的Sno、Cno和rank列。
/select e.sno,e.cno,g.rank from score e,grade g where e.degree between g.low and g.upp ;/
–19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
--------/select e. from score e where e.cno=‘3-105’ and e.degree>=
------all(select e.degree from score e where e.cno=‘3-105’ and e.sno>109);/
–20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
-------select score from score e where degree < (select max(e.degree) from score e) group by score having count(e.sno)>1 order by degree;
–21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
/select * from score e where e.cno=‘3-105’ and e.degree=
all (select e.degree from score e where e.cno =‘3-105’ and e.sno=109);/
–22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
/select s.,s.sno,s.sname,s.sbirthday from student s where s.sbirthday=
(select s.sbirthday from student s where s.sno=108);/
–23、查询“张旭“教师任课的学生成绩。
/select t.,s.degree 学生成绩 from teacher t,course e,score s where t.tno=e.tno and e.cno=s.cno and t.tname=‘张旭’;/
–24、查询选修某课程的同学人数多于5人的教师姓名
---------select t.*,t.tname 老师名字 from teacher t,course c where t.tno=c.tno having (select )
–25、查询95033班和95031班全体学生的记录。
/select * from student s where s.class in (95033,95031) order by s.class/
–26、查询存在有85分以上成绩的课程Cno.
/select s.cno,s.degree from score s where s.degree>85/
–27、查询出“计算机系“教师所教课程的成绩表。
/select t.tname,c.cname,s.degree from teacher t left join course c on c.tno=t.tno left join score s on c.cno=s.cno
where t.depart=‘计算机系’;/
–28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
/select t.depart,t.tname,t.prof from teacher t group by t.depart,t.tname,t.prof order by t.depart;/
–29、查询选修编号为“3-105“且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
/select * from score s where s.cno=‘3-105’ and s.degree> all(select s.degree from score s where s.cno=‘3-245’) order by s.degree desc;/
–30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”的同学的Cno、Sno和Degree.
/select * from score s where s.cno=‘3-105’ and s.degree> all(select s.degree from score s where s.cno=‘3-245’);/
–31、查询所有教师和同学的name、sex和birthday.

–32、查询所有“女”教师和“女”同学的name、sex和birthday.
–33、查询成绩比该课程平均成绩低的同学的成绩表。
–34、查询所有任课教师的Tname和Depart.
–35、查询所有未讲课的教师的Tname和Depart.
–36、查询至少有2名男生的班号。
–37、查询Student表中不姓“王”的同学记录。
–38、查询Student表中每个学生的姓名和年龄。
–39、查询Student表中最大和最小的Sbirthday日期值。
–40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
–41、查询“男”教师及其所上的课程。
–42、查询最高分同学的Sno、Cno和Degree列。
–43、查询和“李军”同性别的所有同学的Sname.
–44、查询和“李军”同性别并同班的同学Sname.
–45、查询所有选修“计算机导论”课程的“男”同学的成绩表

答案：
–1、查询Student表中的所有记录的Sname、Ssex和Class列。
SELECT s.sname, s.ssex, s.class FROM student s
–2、查询教师所有的单位即不重复的Depart列。
SELECT DISTINCT depart FROM teacher
–3、查询Student表的所有记录。
SELECT * FROM student
–4、查询Score表中成绩在60到80之间的所有记录。
SELECT * FROM score sc WHERE sc.degree BETWEEN 60 AND 80
–5、查询Score表中成绩为85，86或88的记录。
SELECT * FROM score sc WHERE sc.degree IN (85, 86, 88)
–6、查询Student表中“95031”班或性别为“女”的同学记录。
SELECT * FROM student s WHERE s.ssex=‘女’ OR s.class=‘95031’
–7、以Class降序查询Student表的所有记录。
SELECT * FROM student s ORDER BY s.class DESC
–8、以Cno升序、Degree降序查询Score表的所有记录。
SELECT * FROM score sc ORDER BY sc.cno, DEGREE DESC
–9、查询“95031”班的学生人数。
SELECT COUNT(*) FROM student s WHERE s.class=‘95031’
–10、查询Score表中的最高分的学生学号和课程号。
SELECT * FROM score sc WHERE sc.degree=(SELECT MAX(DEGREE) FROM score)
–ALL
–11、查询‘3-105’号课程的平均分。
SELECT sc.cno, AVG(sc.degree) FROM score sc WHERE sc.cno=‘3-105’ GROUP BY sc.cno
–12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
SELECT sc.cno, AVG(sc.degree) FROM score sc WHERE sc.cno LIKE ‘3%’ GROUP BY sc.cno HAVING COUNT(sc.cno) >= 5
–13、查询最低分大于70，最高分小于90的Sno列。
SELECT * FROM score sc WHERE sc.degree BETWEEN 70 AND 90
–14、查询所有学生的Sname、Cno和Degree列。
SELECT s.sname, sc.cno, sc.degree FROM student s LEFT JOIN score sc ON s.sno=sc.sno

SELECT s.sname, sc.cno, sc.degree FROM student s, score sc WHERE s.sno=sc.sno
–15、查询所有学生的Sno、Cname和Degree列。
SELECT s.sno, (SELECT c.cname FROM course c WHERE c.cno=sc.cno) cname, sc.degree FROM student s LEFT JOIN score sc ON s.sno=sc.sno
–16、查询所有学生的Sname、Cname和Degree列。
SELECT s.sname, (SELECT c.cname FROM course c WHERE c.cno=sc.cno) cname, sc.degree FROM student s LEFT JOIN score sc ON s.sno=sc.sno
–17、查询“95033”班所选课程的平均分。
SELECT SC.CNO, AVG(SC.DEGREE)
FROM SCORE SC
WHERE SC.SNO IN (SELECT SNO FROM STUDENT S WHERE S.CLASS = ‘95033’)
GROUP BY SC.CNO
–18、现查询所有同学的Sno、Cno和rank列。
SELECT SC.SNO, SC.CNO, G.RANK
FROM SCORE SC
LEFT JOIN GRADE G
ON SC.DEGREE BETWEEN G.LOW AND G.UPP
–19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
SELECT *
FROM STUDENT S
WHERE S.SNO IN
(SELECT SC.SNO
FROM SCORE SC
WHERE SC.CNO = ‘3-105’
AND SC.DEGREE > ALL
(SELECT DEGREE FROM SCORE SC1 WHERE SC1.SNO = 109))
–20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
–第一种写法
SELECT *
FROM (SELECT SC1.,
ROW_NUMBER() OVER(PARTITION BY SC1.CNO ORDER BY SC1.DEGREE DESC) ORD
FROM SCORE SC1
WHERE SC1.CNO IN (SELECT SC.CNO
FROM SCORE SC
GROUP BY SC.CNO
HAVING COUNT() > 1)) R
WHERE R.ORD != 1
–第二种写法
WITH RE1 AS
(SELECT SC1.*
FROM SCORE SC1
WHERE SC1.CNO IN
(SELECT SC.CNO FROM SCORE SC GROUP BY SC.CNO HAVING COUNT() > 1))
SELECT *
FROM RE1
WHERE RE1.DEGREE NOT IN (SELECT MAX(DEGREE) FROM RE1 GROUP BY RE1.CNO)
–21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
SELECT *
FROM SCORE SC1
WHERE SC1.DEGREE > ANY (SELECT SC.DEGREE
FROM SCORE SC
WHERE SC.SNO = 109
AND SC.CNO = ‘3-105’) ORDER BY sc1.sno
/
any,all,some的区别
/
–22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
SELECT *
FROM STUDENT S
WHERE TO_CHAR(S.SBIRTHDAY, ‘yyyy’) =
(SELECT TO_CHAR(S1.SBIRTHDAY, ‘yyyy’)
FROM STUDENT S1
WHERE S1.SNO = ‘108’)
AND S.SNO != ‘108’
–23、查询“张旭“教师任课的学生成绩。
SELECT SC.
FROM COURSE C
LEFT JOIN TEACHER T
ON C.TNO = T.TNO
LEFT JOIN SCORE SC
ON SC.CNO = C.CNO
AND T.TNAME = ‘张旭’
–24、查询选修某课程的同学人数多于5人的教师姓名。
SELECT T.TNAME
FROM COURSE C
LEFT JOIN TEACHER T
ON C.TNO = T.TNO
LEFT JOIN SCORE SC
ON SC.CNO = C.CNO
GROUP BY T.TNAME
HAVING COUNT() > 5
–25、查询95033班和95031班全体学生的记录。
SELECT * FROM student s WHERE s.class IN (95033, 95031)
–26、查询存在有85分以上成绩的课程Cno.
SELECT * FROM score sc WHERE sc.degree > 85
–27、查询出“计算机系“教师所教课程的成绩表。
SELECT *
FROM SCORE SC
WHERE SC.CNO IN (SELECT C.CNO
FROM COURSE C, TEACHER T
WHERE C.TNO = T.TNO
AND T.DEPART = ‘计算机系’)
–28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
SELECT T.TNAME, T.PROF
FROM TEACHER T
WHERE DEPART = ‘计算机系’
AND PROF NOT IN (SELECT PROF FROM TEACHER WHERE DEPART = ‘电子工程系’);
–29、查询选修编号为“3-105“且成绩至少高于选修编号为“3-245”的同学的Cno,Sno和Degree并按Degree从高到低次序排序。
SELECT *
FROM SCORE
WHERE DEGREE > ANY (SELECT DEGREE FROM SCORE WHERE CNO = ‘3-245’)
ORDER BY DEGREE DESC;
–30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”的同学的Cno、Sno和Degree.
SELECT * FROM SCORE WHERE DEGREE > ALL(SELECT DEGREE FROM SCORE WHERE CNO=‘3-245’) ORDER BY DEGREE DESC;
–31、查询所有教师和同学的name、sex和birthday.
SELECT T.TNAME, T.TSEX, T.TBIRTHDAY
FROM TEACHER T
UNION ALL
SELECT S.SNAME, S.SSEX, S.SBIRTHDAY
FROM STUDENT S
–32、查询所有“女”教师和“女”同学的name、sex和birthday.
SELECT *
FROM (SELECT T.TNAME, T.TSEX, T.TBIRTHDAY
FROM TEACHER T
UNION ALL
SELECT S.SNAME, S.SSEX, S.SBIRTHDAY
FROM STUDENT S) R
WHERE R.TSEX = ‘女’
–33、查询成绩比该课程平均成绩低的同学的成绩表。
SELECT *
FROM SCORE SC1,
(SELECT SC.CNO, AVG(SC.DEGREE) AVD FROM SCORE SC GROUP BY SC.CNO) R
WHERE SC1.CNO = R.CNO
AND SC1.DEGREE < R.AVD
–34、查询所有任课教师的Tname和Depart.
SELECT t.tname, t.depart FROM teacher t
–35、查询所有未讲课的教师的Tname和Depart.
SELECT *
FROM TEACHER T
WHERE T.TNO NOT IN
(SELECT C.TNO
FROM COURSE C
WHERE C.CNO IN (SELECT SC.CNO FROM SCORE SC))
–36、查询至少有2名男生的班号。
SELECT s.class FROM student s GROUP BY s.class HAVING COUNT() >= 2
–37、查询Student表中不姓“王”的同学记录。
SELECT * FROM student s WHERE s.sname NOT LIKE ‘王%’
–38、查询Student表中每个学生的姓名和年龄。
SELECT s.sname, ROUND((SYSDATE-s.sbirthday)/365) 年龄 FROM student s
SELECT s.sname, to_char(SYSDATE, ‘yyyy’) - to_char(s.sbirthday,‘yyyy’) FROM student s
–39、查询Student表中最大和最小的Sbirthday日期值。
SELECT MAX(s.sbirthday), MIN(s.sbirthday) FROM student s
–40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
SELECT * FROM student s ORDER BY s.class DESC, s.sbirthday
–41、查询“男”教师及其所上的课程。
SELECT * FROM course c, teacher t WHERE c.tno=t.tno AND t.tsex=‘男’
–42、查询最高分同学的Sno、Cno和Degree列。
SELECT S.SNO, SC.CNO, SC.DEGREE
FROM STUDENT S, SCORE SC
WHERE S.SNO = SC.SNO
AND SC.DEGREE = (SELECT MAX(DEGREE) FROM SCORE)
–43、查询和“李军”同性别的所有同学的Sname.
SELECT *
FROM STUDENT S
WHERE S.SNAME != ‘李军’
AND S.SSEX = (SELECT DISTINCT SSEX FROM STUDENT WHERE SNAME = ‘李军’)
–44、查询和“李军”同性别并同班的同学Sname.
SELECT *
FROM STUDENT S
WHERE S.SNAME != ‘李军’
AND S.SSEX = (SELECT DISTINCT SSEX FROM STUDENT WHERE SNAME = ‘李军’)
AND S.CLASS = (SELECT DISTINCT CLASS FROM STUDENT WHERE SNAME = ‘李军’)
–45、查询所有选修“计算机导论”课程的“男”同学的成绩表
SELECT S.SNAME, S.SSEX, SC.*
FROM SCORE SC, COURSE C, STUDENT S
WHERE SC.CNO = C.CNO
AND S.SNO = SC.SNO
AND C.CNAME = ‘计算机导论’
AND S.SSEX = ‘男’