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