oracle学习查询总结

Oracle查询学习

查询

查询产生一个虚拟表，看到的是表形式显示的结果，但结果并不真正存储，每次执行查询只是现从数据表中提取数据，并按照表的形式显示出来。

查询语法

SELECT   <列名>

FROM     <表名>

[WHERE   <查询条件表达式>]

[ORDER BY <排序的列名>[ASC或DESC]]

 示例

SELECT    StudentId,StudentName,Address

FROM               Students

WHERE   SEX= ‘男’

ORDER BY       StudentId

 列别名

可以使用as来为列指定一个新的名字，as可省略

列别名可以用双引号包围起来，也可以不用双引号。

“学号”或者学号都行。

Select studentid as “学号”

From

Student

;

NULL

查询时候，必须使用is null或者is not null

NULL值与数字进行算术运算的结果为NULL

可以使用NVL函数，为NULL值返回一个替代的值。

NVL(StudentResult, 0)即为，如果studentresult是NULL值，则返回0 

聚合函数

Sum 求和

Avg  求平均

Max 最大值

Min  最小值

Count       计数

 案例一

某公司印了一批充值卡，卡的密码是随机生成的，现在出现这个问题：

    卡里面的“O和0”（哦和零）“i和1”（哎和一），用户反映说看不清楚，公司决定，把存储在数据库中的密码中所有的“哦”都改成“零”，把所有的“i”都改成“1”；

         请编写SQL语句实现以上要求；

数据库表名：Card

密码字段名：PassWord

UPDATE Card

SET           PassWord= Replace(Replace(PassWord ,'O','0'),'i','1') 

案例二

在数据库表中有以下字符数据，如：

         13-1、13-2、13-3、13-10、13-100、13-108、13-18、13-11、13-15、14-1、14-2

 

         现在希望通过SQL语句进行排序，并且首先要按照前半部分的数字进行排序，然后再按照后半部分的数字进行排需，输出要排成这样：

         13-1、13-2、13-3、13-10、13-11、13-15、13-18、13-100、13-108、14-1、14-2

 数据库表名：SellRecord

字段名：ListNumber

 SELECT ListNumber

FROM   SellRecord

ORDER BY

to_number(substr(list_number,1,instr(list_number,'-')-1)),

to_number(substr(list_number,instr(list_number,'-')+1))

模糊查询

l  查询时，字段中的内容并不一定与查询内容完全匹配，只要字段中含有这些内容

使用LIKE关键字，配合通配符进行模糊查询。

字符匹配操作可以使用通配符“%” 和“_”

%：表示任意个字符，包括零个

_：表示一个任意字符

转义符

如果要模糊查询出含有%或者_等通配符的的记录时，可以使用转义符，

模糊查询出含有%的地址

select * from student where address like'%\%%' escape '\';

此处第一个%和第三个%为通配符，使用了escape ‘\’使第二个%失去了通配符的含义，它代表的就是%

模糊查询出含有_的地址

select * from student where address like'%b_%' escape 'b';

单引号的转义，使用两个单引号

update student set address = '2''3' wherestudentid = 1039; 

NULL值的处理

查询某个字段为空，使用is null

查询某个字段不为空，使用is not null

对NULL做算术运算结果仍为NULL，可以使用NVL函数，将NULL值转为0之后，再进行运算。

NULL排序比其他所有类型的都大，一个可以为空的字段倒排序，NULL值排在最前面。

下面的语句，gradeid是NULL的行排在前面。

select * from student order by gradeiddesc;

如果要想降序排列，同时使null的行排在最后面，可以使用NVL函数。

select * from student order bynvl(gradeid,0) desc;

 BetweenAND

l  把某一字段中内容在特定范围内的记录查询出来

SELECT StudentID, StudentResult FROM ResultWHERE StudentResult BETWEEN 60 AND 80;

上面语句等价于

StudentResult >= 60 and StudentResult<= 80

IN

l  把某一字段中内容与所列出的查询内容列表匹配的记录查询出来

SELECT StudentName AS 学员姓名,AddressAs 地址 FROM Students WHERE Address IN ('北京','广州','上海');

上面语句等价于

Address = ‘北京’ or Address = ‘广州’ or Address= ‘上海’ 

分组group by

问题1：统计每学期男女同学的人数，该怎么办？

第一步，将男生和女生分成两组，

第二部，分别对男生组和女生组计数

 select sex, count(*) from student group bysex;

多列分组

统计出每个年级的男生和女生的数量。需要使用多列分组。

SELECT COUNT(*) AS 人数,GradeId AS年级,Sex AS   性别

FROM Student

GROUP BY GradeId,Sex

ORDER BY GradeId

对分组后的结果进行过滤（筛选）

如何获得总人数超过10人的年级？

SELECT COUNT(*) AS 人数,GradeId AS年级 FROM Student

GROUP BY GradeId

HAVING COUNT(*)>10

 

WHERE与HAVING对比

WHERE子句：

用来筛选 FROM 子句中指定的操作所产生的行

GROUP BY子句：

用来分组 WHERE 子句的输出

HAVING子句：

用来从分组的结果中筛选行 

Where à groupby à having à order by

做题的总结：

 成绩表查询

select * from result order by examdate desc

学生表查询

select * from student

课程表查询

select * from subject

1.      最近一次查询的问题:查询最近一次oracle考试最高分和最低分

select max(studentresult) 最高分,min(studentresult) 最低分 from result where subjectid=(select subjectid from subject where subjectname='Oracle') and examdate=(select max(examdate) from result where subjectid=(select subjectid from subject where subjectname='Oracle'))

 

2.  最近一次没有参加oracle考试的学生，可以用not in解决

select * from student where studentid not in(select studentid from result where subjectid=(select subjectid from subject where subjectname='Oracle') and examdate=(select max(examdate) from result where subjectid=(select subjectid from subject where subjectname='Oracle'))）

结果：

 

3.  最近一次考试成绩大于80,用exists做，效率较高，其中的subjectid=3可以使用子查询替换掉，从subject表。

select *from student a where exists(select * from result where studentresult>80 andsubjectid=3 and a.studentid = studentid and examdate=(select max(examdate) fromresult where subjectid=3 ))

4.      case在查询时的使用：

selectstudentname, case when gradeid= 1 then '一年级' when gradeid= 2 then '二年级' when gradeid=3 then '三年级' end myclass  from student

5查询平均成绩排序中第3条到第6条的学生信息。（有是一个新表） 

selectt2.* from (select t1.*,rownum rn from (selects.studentID,studentName,avg(score)

fromt_result r,t_student s

wherer.studentID=s.studentID

groupby s.studentID,studentName

orderby avg(score) desc) t1 where rownum<=6) t2

wherern>=3;

6. 列出成绩高于平均水平的学生的 编号，姓名，考试科目名称，及成绩，并按年龄排序

   selects.studentID,studentName,subjectName,score

         from t_student s,t_result r,t_subject u

         where u.subjectid = r.subjectid ands.studentID=r.studentID

              and r.score>(select avg(score) from t_result)

         order by birthday desc;

 7. 删除上面除了编号不同,其他都相同的学生冗余信息。表名：t_test

delete from t_test where id not in(select MIN(id) from t_test group byno,name,subjectID,subjectName,score);

 8.school表中有班级（class）,性别（sex）,姓名（name）三列，求查询出男生数和女生数相等的班级有哪些。

selectt1.class from(select class,SEX,count(*) as s from shcool group by class,sexhaving sex='男') t1,(select class,SEX,count(*) as s from shcool groupby class,sex having sex='女') t2 where t1.class = t2.class and t1.s=t2.s 

9.

自己做的：

--第一问

selecteno,ename,esex,eage from emp e join dept d on d.dno = e.dno where d.dname = '研发部' order by e.ename

--第二问

selectename,ssal from emp e join sal s on e.eno = s.eno where s.ssal = (selectmax(ssal) from sal)

 --第三问

altertable emp add eremark varchar2(400)

 --第四问

updatesal set ssal = ssal+1000 where ssal<=3000

 --第五问

--利用左连接的特点，出现null。

selectt.eno,t.ename,dname from (select e.eno,ssal,e.ename,e.dno from emp e left joinsal s on e.eno=s.eno) t join dept d on d.dno = t.dno  where t.ssal is null