Oracle表管理(查询)05

1、L o g oL o g o Oracle表查询表查询查询条件；输出该条件下的内容vOracle单表查询 查询表结构 查询所有列 查询指定列 查询出不重复的数据简单查询 where条件查询v Oracle多表查询 等值连接(内连接) 左连接(左外连接) 右连接(右外连接) 完全连接(全外连接) v Oracle嵌套查询Oracle单表查询v 查询表结构 desc 表名;v 查询所有列 select * from 表名;v 查询指定列 select name,age,birthday from 表名;v 查询某些字段不同记录(去重) select distinct name,birthday f

2、rom 表名;v Oracle大小写不区分 字段名不区分大小写 字段中的内容区分大小写Oracle单表查询v 字段运算查询 select sal*12 ,age from 表名;v 使用列的别名 select sal *12 as “年工资”, age 年龄，name username from 表名;v 如何处理null值 select sal*12+nvl(jiang,0) as 年工资,age from 表名;v 去重复查询行 select distinct name,birthday from 表名;去重复用as 为sal*12 起别名 年工资如果jiang为空，则用nvl函数，jia

3、ng为空时取0不用as 为age 起别名 年龄运算符*乘Oracle单表查询涉及到的运算符v 运算符分类 比较运算符 逻辑运算符 算术运算符 连接运算符Oracle单表查询涉及到的运算符v 比较运算符名称说明=（等于）select * from a where job =IT;select * from a where sal =1100;!=（不等于）select * from a where job !=IT;select * from a where sal !=1100;=（不等于）select * from a where job =IT;select * from a where

4、sal =1100;（不等于）select * from a where job IT;select * from a where sal 1100;(小于)select * from a where sal (大于)select * from a where sal 1100;=(小于等于)select * from a where sal =(大于等于)select * from a where sal =1100;in(列表)select * from a where sal in(2000,3000,4000);（in输出指定内容）select * from a where job i

5、n(IT,MANAGER);not in(不在列表)select * from a where sal not in(2000,3000,4000);select * from a where job not in(IT,MANAGER);Oracle单表查询涉及到的运算符v 比较运算符名称说明between(介于之间)select * from a where sal between 200 and 300;not between(不介于之间)select * from a where sal not between 200 and 300;like(模糊匹配)select * from a

6、 where job likem%select * from a where job likem_not like(模糊匹配)select * from a where job not like(M%);select * from a where job not like(M_);is null(是否为空)select * from a where sal is null;select * from a where job is null;is not null(是否不为空)select * from a where sal is not null;select * from a where

7、job is not null;%代表N个任意字符，可放前或后_代表一个任意字符Oracle单表查询涉及到的运算符v 逻辑运算符名称说明and(与)select * from a where job =IT and sal2000;or(或)select * from a where job ！=IT or sal2000;not（非）select * from a where not name=zhansan;not job=IT等价于jobITOracle单表查询涉及到的运算符名称说明+(加)select from a ;-(减)select sal-200 from a ;*（乘）sel

8、ect sal*200 from a ;/（除）select sal/200 from a ;Oracle单表查询涉及到的运算符v 连接运算符：v 用于将两个或多个字符串合并在一起用于将两个或多个字符串合并在一起;或者将一个字符串与或者将一个字符串与一个数据值合并在一起一个数据值合并在一起名称说明|（连接)select id | name | sal from a ;Oracle单表查询涉及到的运算符v 操作符的优先级：Oracle单表查询v 使用where子句 查询出工资高于（等于）3000的员工 查询出部门不是IT部门的员工 查询出收入不等于3000之间的员工 查询出生日大于1970年10

9、月2日的员工v 使用模糊查询like(反之not like) like M% like M_v 在where条件中使用in（反之not in） select * from 表名 where sal in(2000,1000,3000);v 在where条件中使用between(介于之间) select * from 表名 where between 2000 and 3000;v 在where条件中使用is null(反之 is not null) select * from 表名 where sal is null;Oracle单表查询v 使用逻辑运算符组成条件查询 查询出工资高于3000或

10、者部门是IT部，并且姓名开头是M，年龄25岁以上的员工v 使用排序查询order by .ascv 使用排序查询order by .desv 使用排序查询order by .des.asc 排序的语法 select * from 表名表名 where 条件条件 order by 字字段段1,字段字段2 desc; select * from 表名表名 where 条件条件 order by 字字段段1,字段字段2 asc; select * from 表名表名 where 条件条件 order by 字字段段1 desc,字段字段2 asc;asc升序desc降序若有where条件order

11、by 放在where后面，多个排序字段之间通过逗号分割Oracle单表查询v 使用排序查询 select * from xs where sp_id=2 order by idno desc; select * from xs where sp_id=2 order by idno asc; select * from xs where sp_id=2 order by idno asc ,numb desc;idno按升序，numb相同按降序排列多字段用逗号分隔Oracle单表查询v 使用Oracle的函数进行查询 ceil(n),取大于等于数值n的最小整数 floor(n),取小于等于数值

12、n的最小整数 mod(m,n)取m整数除n后的余数 power(m,n)取m的n次方 round(m,n)四舍五入，保留位 avg(字段名)求平均值,要求字段为数值型 count(字段名)统计总数 min(字段名)计算数值型字段最小数 max(字段名)计算数值型字段最大数 sum( 字段名)计算数值型字段总和 lower(字段名)转换为小写 upper(字段名)转换为大写 abs(字段名)取绝对值Oracle单表查询v分组查询 在介绍GROUP BY 和 HAVING 子句前，我们必需先讲讲sql语言中一种特殊的函数:聚合函数 例如SUM, COUNT, MAX, AVG等。这些函数和其它函数

13、的根本区别就是它们一般作用在多条记录上。 select SUM(population) from b这里的SUM作用在所有返回记录的population字段上，结果就是该查询只返回一个结果，即所有国家的总人口数 通过使用GROUP BY 子句，可以让SUM 和 COUNT 这些函数对属于一组的数据起作用。v Oracle单表查询v分组查询 having子句可以让我们筛选成组后的各组数据，where子句在聚合前先筛选记录.也就是说作用在group by 子句和having子句前.而 having子句在聚合后对组记录进行筛选v 分组查询SELECT region, SUM(population),

14、 SUM(area)FROM bbcGROUP BY region当你指定 GROUP BY region 时， 属于同一个region(地区)的一组数据将只能返回一行值，也就是说，表中所有除region(地区)外的字段，只能通过 SUM, COUNT等聚合函数运算后返回一个值。SELECT region, SUM(population), SUM(area)FROM bbcGROUP BY regionHAVING SUM(area)1000000 HAVING子句可以让我们筛选成组后的各组数据注意Oracle单表查询v分组查询注意事项group by语句可以单独使用having语句只能配合

15、group by语句使用oracle的函数只能出现在选择列表、having、order by 子句中如果在 select 语句中同时包含group by ,having,order by那么它们的顺序是group by ,having,order by select deptno,avg(sal+nvl(comm,0) from scott.emp where sal+nvl(comm,0)500 group by deptno select aid,sum(scount) from schooltable group by aid having sum(scount)20000 order

16、by sum(scount)asc; Oracle多表查询v 多表查询：指从多个数据表查询数据 v 无条件多表查询无条件查询是将各表的记录以“笛卡尔”集的方式组合起来select a.uname,a.age,b.uanme,b.age from a,b;v 等值多表查询按照等值的条件查询多个数据表中关联的数据，要求关联的多个数据表的某些字段具有相同的属性，即具有相同的数据类型、宽度和取值范围select a.uname,a.age,b.uanme,b.age ,b.sal from a,b where a.age=b.age and b.sal1000;v 非等值多表查询select a.un

17、ame,a.age,b.uanme,b.age ,b.sal from a,b where a.age！=b.age and b.sal1000;表名用逗号分隔表名用逗号分隔两个表中有相同属性Oracle多表查询v 左连接：left（outer） join select * from aa left join bb on aa.aid=bb.bid; 是以aa表的记录为基础的,aa可以看成左表,bb可以看成右表,left join是以左表为准的. 换句话说,左表(aa)的记录将会全部表示出来,而右表(bb)只会显示符合搜索条件的记录(例子中为: aa.aid = bb.bid). bb表记录不

18、足的地方均为NULL. 等价于 select * from aa,bb where aa.aid=bb.bid(+);Oracle多表查询v 右连接：right （outer）join select * from aa right join bb on aa.aid=bb.bid; 和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充 等价于 select * from aa,bb where aa.aid(+)=bb.bid; Oracle多表查询v 等值连接：inner join select * from aa innner join bb on

19、aa.aid=bb.bid; 只返回两个表中联结字段相等的行 等价于 select * from aa,bb where aa.aid=bb.bid;Oracle多表查询v 完全连接：full （outer）join select * from aa full join bb on aa.aid=bb.bid; 只返回两个表中的所有行 等价于 select * from aa left join bb on aa.aid=bb.bid union select * from aa right join bb on aa.aid=bb.bid Oracle嵌套查询Avg max 不可以在不可以在

20、where后面使用因此用嵌套查询后面使用因此用嵌套查询v 嵌套查询：在select查询语句里可以嵌入select查询语句，称为嵌套查询，又称子查询 v 简单嵌套查询select bumen from a where employid=(select employid from b where name=张三) ;1) select employid from b where name=张三 查询出姓名是张三的员工编号 2）select bumen from a where employid=员工编号 查询出员工编号为10的员工所在部门嵌套查询Oracle嵌套查询v 带in的嵌套查询 selec

21、t employid,name,sal from a where sal in( select sal from b where name=张三); 也可select employid,name,sal from a where sal in(200,300); 等价于sal=200 or sal=300v 带any的嵌套查询 select employid,name,sal from a where sal any( select sal from b where name=张三); 也可select employid,name,sal from a where sal any(200,300); 等价于sal200 or sal300反之not inOracle嵌套查询v 带some的嵌套查询 select employid,name,sal from a where sal some( select sal from b where name=张三); 也可select employid,name,sal from a where sal some(200,300); 等价于sal200 or sal300v 带all的嵌套查询 select employid,name,sal from a whe