Oracle学习笔记（二）
SQL> select * from teachers 2 where (did,wage) in 3 (select did,min(wage) from teachers group by did); TID NAME TITLE HIREDATE BONUS WAGE DID---------- -------- ------ -------------- ---------- ---------- ---------- 10128 王晓 05-9月 -07 1000 101 10328 张笑 29-9月 -07 1000 103 10228 赵天宇 18-9月 -07 1000 102
?一、记录排序
?
1.升序排序 按工资由小到大的顺序进行检索teachers表 SQL> select name,hiredate,title,wage from teachers order by wage;NAME HIREDATE TITLE WAGE-------- -------------- ------ ----------林飞 11-10月-07 1000张笑 29-9月 -07 1000王晓 05-9月 -07 1000赵天宇 18-9月 -07 1000韩冬梅 01-8月 -02 助教 1800车东日 05-9月 -01 助教 1900崔天 05-9月 -00 助教 1900邹人文 21-1月 -96 讲师 2400臧海涛 29-6月 -99 工程师 2400孙晴碧 11-5月 -98 讲师 2500赵昆 18-2月 -96 讲师 2700张珂 16-8月 -97 讲师 2700孔世杰 06-7月 -94 副教授 2700王彤 01-9月 -90 教授 3000杨文化 03-10月-89 教授 3100齐沈阳 03-10月-89 高工 3100?
2.降序排序 按照学生姓名降序排序SQL> select name,sex,dob from students order by 1 desc;NAME SE DOB---------- -- --------------赵风雨 男 25-10月-90赵迪帆 男 22-9月 -89张杨 男 08-5月 -90张冬云 女 26-12月-89张纯玉 男 21-7月 -89曾程程 男王晓芳 女 07-5月 -88王天仪 男 26-12月-89王刚 男 03-4月 -87欧阳春岚 女 12-3月 -89刘春苹 女 12-8月 -91林紫寒 女李秋枫 男 25-11月-90韩刘 男 03-8月 -91高淼 男 11-3月 -87高山 男 08-10月-90白昕 男白菲菲 女 07-5月 -88?
3.多列排序 按专业、姓名升序检索students表SQL> select specialty,name from students order by 1,name asc;SPECIALTY NAME---------- ----------机电工程 白菲菲机电工程 高山机电工程 曾程程机电工程 张冬云机电工程 张杨机电工程 赵迪帆计算机 白昕计算机 韩刘计算机 刘春苹计算机 王天仪计算机 王晓芳计算机 张纯玉自动化 高淼自动化 李秋枫自动化 林紫寒自动化 欧阳春岚自动化 王刚自动化 赵风雨
按照专业升序、姓名降序检索students表SQL> select specialty,name from students order by 1,name desc;SPECIALTY NAME---------- ----------机电工程 赵迪帆机电工程 张杨机电工程 张冬云机电工程 曾程程机电工程 高山机电工程 白菲菲计算机 张纯玉计算机 王晓芳计算机 王天仪计算机 刘春苹计算机 韩刘计算机 白昕自动化 赵风雨自动化 王刚自动化 欧阳春岚自动化 林紫寒自动化 李秋枫自动化 高淼??
二、分组查询
?? ①计算teacher的平均工资
?
SQL> select avg(wage) 平均工资 from teachers; 平均工资---------- 2137.5
?
? ②求全体教师工资的方差
?
SQL> select variance(wage) from teachers;VARIANCE(WAGE)-------------- 614500
?
③查询每一个系教师的最大工资和最小工资
?
SQL> select did,max(wage) 最高工资,min(wage) 最低工资 from teachers group by did; DID 最高工资 最低工资---------- ---------- ---------- 102 3100 1000 101 3000 1000 103 3100 1000
?④将所平均工资高于2200的系，和平均工资
?
SQL> select did,avg(wage) 平均工资 from teachers group by did having avg(wage)>2200; DID 平均工资---------- ---------- 102 2240 103 2220
三、子查询
? ①查找工资低于平均工资的教师
?
SQL> select name,wage from teachers 2 where wage< 3 (select avg(wage) from teachers);NAME WAGE-------- ----------韩冬梅 1800崔天 1900车东日 1900王晓 1000张笑 1000赵天宇 1000
?
?②未被学生选修的课程
?
SQL> select cid,cname from cources 2 where cid not in 3 ( 4 select cid from stu_grade 5 ); CID CNAME---------- ------------------------------ 10102 C++语言程序设计 10202 模拟电子技术 10302 理论力学 10103 离散数学 10203 数字电子技术 10303 材料力学
?
③查询工资低于任何一个部门平均工资的教师信息
?
SQL> select name,wage from teachers 2 where wage < any 3 (select avg(wage) from teachers group by did);NAME WAGE-------- ----------韩冬梅 1800崔天 1900车东日 1900王晓 1000张笑 1000赵天宇 1000
?
④查询工资大于任何一个部门平均工资的教师信息
?
SQL> select * from teachers 2 where wage > all 3 (select avg(wage) from teachers group by did); TID NAME TITLE HIREDATE BONUS WAGE DID---------- -------- ------ -------------- ---------- ---------- ---------- 10101 王彤 教授 01-9月 -90 1000 3000 101 10104 孔世杰 副教授 06-7月 -94 800 2700 101 10103 邹人文 讲师 21-1月 -96 600 2400 101 10210 杨文化 教授 03-10月-89 1000 3100 102 10209 孙晴碧 讲师 11-5月 -98 600 2500 102 10207 张珂 讲师 16-8月 -97 700 2700 102 10308 齐沈阳 高工 03-10月-89 1000 3100 103 10309 臧海涛 工程师 29-6月 -99 600 2400 103 10307 赵昆 讲师 18-2月 -96 800 2700 103
?⑤查询与王天仪专业相同、生日相同的学生。
?
SQL> select * from students 2 where (specialty,dob)= 3 (select specialty,dob from students where name='王天仪'); SID MID NAME SE DOB SPECIALTY---------- ---------- ---------- -- -------------- ---------- 10103 10101 王天仪 男 26-12月-89 计算机
?
⑥查询各自在该部门工资最低的教师
?
SQL> select * from teachers 2 where (did,wage) in 3 (select did,min(wage) from teachers group by did); TID NAME TITLE HIREDATE BONUS WAGE DID---------- -------- ------ -------------- ---------- ---------- ---------- 10128 王晓 05-9月 -07 1000 101 10328 张笑 29-9月 -07 1000 103 10228 赵天宇 18-9月 -07 1000 102
?⑦查询已被选修的课程
?
SQL> select cname from cources 2 where exists 3 (select 2 from stu_grade where cources.cid=stu_grade.cid);CNAME------------------------------计算机组成原理自动控制原理工程制图
?
⑧查询没有安排教师的系。
?
SQL> select dname from department 2 where did not in 3 (select did from teachers);DNAME--------工商管理
四、连接查询
? ①查询教师编号、姓名和所在系的名称。
?
SQL> select tid,name,dname from teachers,department 2 where teachers.did=department.did; TID NAME DNAME---------- -------- -------- 10101 王彤 信息工程 10104 孔世杰 信息工程 10103 邹人文 信息工程 10106 韩冬梅 信息工程 10210 杨文化 电气工程 10206 崔天 电气工程 10209 孙晴碧 电气工程 10207 张珂 电气工程 10308 齐沈阳 机电工程 10306 车东日 机电工程 10309 臧海涛 机电工程 10307 赵昆 机电工程 10128 王晓 信息工程 10328 张笑 机电工程 10228 赵天宇 电气工程
?②查询有职称的教师编号、姓名、和所在系。
?
SQL> select tid,name,dname from teachers,department 2 where teachers.did=department.did and title is not null; TID NAME DNAME---------- -------- -------- 10101 王彤 信息工程 10104 孔世杰 信息工程 10103 邹人文 信息工程 10106 韩冬梅 信息工程 10210 杨文化 电气工程 10206 崔天 电气工程 10209 孙晴碧 电气工程 10207 张珂 电气工程 10308 齐沈阳 机电工程 10306 车东日 机电工程 10309 臧海涛 机电工程 10307 赵昆 机电工程
?③查询教师编号、教师姓名、所在系。
?
SQL> select tid,name,dname from teachers 2 left outer join department 3 on teachers.did=department.did;或:SQL> select tid,name,dname from teachers,department? 2?? where teachers.did=department.did(+); TID NAME DNAME---------- -------- -------- 10128 王晓 信息工程 10106 韩冬梅 信息工程 10103 邹人文 信息工程 10104 孔世杰 信息工程 10101 王彤 信息工程 10228 赵天宇 电气工程 10207 张珂 电气工程 10209 孙晴碧 电气工程 10206 崔天 电气工程 10210 杨文化 电气工程 10328 张笑 机电工程 10307 赵昆 机电工程 10309 臧海涛 机电工程 10306 车东日 机电工程 10308 齐沈阳 机电工程 11111 林飞
?