Oracle学习笔记（二）

最新推荐文章于 2024-09-13 09:38:25 发布

iteye_7141

最新推荐文章于 2024-09-13 09:38:25 发布

阅读量207

点赞数

文章标签： Oracle SQL 云计算

本文链接：https://blog.csdn.net/iteye_7141/article/details/81930167

版权

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 林飞