1.单表查询
1.精确查询
查询语句：
select * from T_OWNERS where watermeter = '1';
select id,name from T_OWNERS where watermeter = '1';2.模糊查询
select * from t_owners where name like '%xi%'3.And运算符
select * from t_owners where name like '%tommy%' and housenumber like '%5%';
4.or运算符
select * from t_owners where name like '%tommy%' or housenumber like '%5%';5.or 和And混合使用
因为And的优先级比or大，所以我们需要用（）来改变优先级
select * from t_owners where （Name like '%xi%' and housenumber like '%5%'） or addressid = 3;
6.范围查询
select * from T_Account where usernum >= 10000 and usernum <= 20000;
我们也可以用between And 来实现
select * from T_Account where usernum between 10000 And 20000;7.空值查询
select * from T_ACCOUNT where maxnum is null
select * from T_ACCOUNT where maxnum is not null8.去掉重复查询
select DISTINCT addressid from t_account；9.排序查询
升序：
select * from t_account order by usenum；
降序：
select * from t_account order by usenum desc；10.基于伪列的查询
在Oracle的表的使用的过程中，实际表中还有一些附加的列，成为伪列，伪列就像表中的列一样，但是在表中并不存储。伪列只能查询，不能进行增删改操作。关键字：ROWID和ROWNUM
ROWID：表中的每一行在数据文件中都有一个物理地址，ROWID伪列返回的就是改行的物理地址，使用ROWID可以快速的定位表中的某一行。ROWID的值可以唯一的标识表中的一行，由于ROWID返回的是该行的物理地址，因此在使用ROWID可以显示行是如何存储的。
select rowID，t.* from t_Area t；
可以通过指定的ROWID来查询记录
select rowID，t.* from T_AREA t where ROWID='';
ROWNUM:在查询结果集中，ROWNUM为结果集中每一行标识一个行号，第一行返回1，第二行返回2，以此类推,通过ROWNUM伪列可以限制查询结果集中返回的行数
select rownum，t.* from T_OWNERTYPE t；
11.聚合查询
--求和
select sum(usernum) from t_account where year=‘2012’；
--求平均
select avg(usernum) from t_account where year=‘2012’；
--求最大
select max(usernum) from t_account where year=‘2012’；
--求最小
select min(usernum) from t_account where year=‘2012’；
--求统计个数
select count(*) from t_account where year=‘2012’；
--分组聚合:select 后一定是分组聚合的条件或者是聚合函数
select sum（money） from t_account group by areaid;
select year，areaid，sum（money） from t_account group by areaid;
13.分组后条件查询
select areaid，sum（money） from t_account group by areaid having sum（money）> 10000;2.连接查询
1.多表内连接查询
select o.id idnumber,o.name name,ot.name type from T_OWNERS o, T_OWNERTYPE ot where o.ownertypeid = ot.id;2.左外连接
select ow.id，ow.name，year，month，money from t_owners ow，t_account ac where ow.id = ac.owneruuid
--SQL1999规范
select ow.id，ow.name，year，month，money from t_owners ow left join t_account ac on ow.id = ac.owneruuid
--Oracle的语法
select ow.id，ow.name，year，month，money from t_owners ow，t_account ac where ow.id = ac.owneruuid（+）
2.右外连接
select ow.id，ow.name，year，month，money from t_owners ow，t_account ac where ow.id = ac.owneruuid
--SQL1999规范
select ow.id，ow.name，year，month，money from t_owners ow right join t_account ac on ow.id = ac.owneruuid
--Oracle的语法
select ow.id，ow.name，year，month，money from t_owners ow，t_account ac where ow.id（+） = ac.owneruuid
3.子查询：嵌套查询
1.子查询：where 子句中的子查询
--单行子查询
只返回一条记录、单行操作符
select * from T_Account where year='2012' and month='01' and usernum > (select avg(usenum) from T_ACCOUNT where year = '2012' and month='01')
--多行子查询
IN ： 等于列表中的任何一个
ANY ： 和子查询返回的任意一个值比较
ALL ： 和子查询返回的所有值比较
--IN运算符
select * from T_OWNERS where addressid in（1,3,4）
select id from t_address where name like ‘%花园%’
select * from t_owners where addressid in （1,4）
=》 select * from t_owners where addressid in （select id from t_address where name like ‘%花园%’）
不包含=》 select * from t_owners where addressid not in （select id from t_address where name like ‘%花园%’）2.子查询：from子句中的子查询
--from子句的子查询是多行子查询
select * from （select o.id idnumber，o.name name，ot.name type from T_Owners o, T_OWNERTYPE ot where o.ownertypeid = ot.id） where type = '居民'3.子查询：select子句中的子查询：单行语句查询
select id，name，addressid from t_owners
select id，name，（select name from t_address where id = addressid）addressname from t_owners
select id，name，（select name from t_address where id = addressid）addressname （select (select name from t_area where id = areaid )from t_address where id = address）areaid from t_owners
4.分页查询
1.简单分页
分页查询台帐表T_Account，每页10条记录：
我们在Oracle进行分页查询，需要用到伪列ROWNUM和嵌套查询。
select rownum, t.* from t_Account t where rownum<=10
select * from (select rownum, t.* from t_Account t) where r <= 20 and r > 10
--基于排序的分页
select * from t_account order by usenum desc；
select * from (select rownum, t.* from t_Account t) where r <= 20 and r > 10
select * from (select rownum r, t.* from （select * from t_account t order by usenum desc）t ) where r <= 20 and r > 10
select * from(select rownum r, t.*) from(select * from T_ACCOUNT order by usenum desc) t where rownum<=20) where r>105.单行函数
5.1 字符函数
ASCII ： 返回对应的字符的十进制值。
CHR ： 给出十进制返回字符
CONCAT ： 拼接两个字符串，与II相同。
INITCAT ： 将字符串的第一个字母变成为大写。
INSTR ： 找出某个字符串的位置。
INSTRB ： 找出某个字符串的位置和字节数
LENGTH ： 以字符给出字符串的长度。
LENGTHB ： 以字节给出字符串的长度。
LOWER : 将字符串换成小写。
LPAD ： 使用指定的字符在字符的左边填充。
LTRIM ： 在左边裁剪掉制定的字符。
RPAD ： 使用指定的字符在字符的右边填充。
RTRIM ： 在右边裁剪掉指定的字符。
REPLACE ： 执行字符串的搜索和替换。
SUBSTR ： 取字符串的子串。
SUBSTRB ： 取字符串的子串以字节的形式。
SOUNDEX ： 返回一个同音字符串。
TRANSLATE ： 执行字符串搜索和替换。
TRIM ： 裁剪掉前面或后面的字符串。
UPPER ： 将字符串变为大写。
--常用字符函数
---求长度,dual是伪表
select length('ABCD') from dual;
---求字符串的子串SUBSTR
select substr('ABCD',2,2) from dual;
---字符串拼接
select concat('ABC','D') from dual;
select concat(concat('ABC','D'),'EF') from dual;
---'||'用于拼接
select 'ABC' || 'D' from dual;
5.2数值函数
ABS（） ： 绝对值；
CEIL（） ： 大于或等于
COS（） ： 余弦
COSH（）： 反余弦
EXP（）：e的次幂
FLOOR（）：小于或等于的最大整数
LN（）：自然对数
LOG（）：以10 为底的对数
MOD（value，divisor）求模
POWER（value，exponent）value的exponent次幂
ROUND（value，precision）按precision精度四舍五入
SIGN（value）value为正返回1，为负返回-1，为0返回0；
SIN：余弦
SINH：反余弦
SQRT：平方根
TAN：正切
TANH：反正切
TRUNC（value，按precision）按照precision截取value
VSIZE（value）：返回value在ORACLE的
--数值函数
select round(100.567) from dual;
select round(100.567,2) from dual;
--数字截取
select trunc(100.456) from dual;
--取模:余数
select mod(10,3) from dual;
5.3日期函数
--加月函数
select add_months(sysdate,-2) from dual；
--求最后一天
select last_day(sysdate) from dual;
select last_day(sysdate-4) from dual;
--日期截取
select trunc(sysdate) from dual; --按日截取，把时间去掉；
select trunc(sysdate,'mm') from dual;--按月截取，把日截掉
select trunc(sysdate,'yyyy') from dual;--按年截取
select trunc(sysdate,'hh') from dual;--按时截取
select trunc(sysdate,'mi') from dual;--按分截取
5.4转换函数
--数字转成字符串
select TO_ChAR(100) || 'fen' from dual;
--日期转字符串
select to_char(sysdate,'yyyy-mm-dd') from dual;
select to_char(sysdate,'yyyy')||‘年’ || tochar(sysdate,'mm')||‘月’ from dual;
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;
--字符串转日期
select to_date('2016-03-10','yyyy-mm-dd') from dual;
--字符串转数字
select to_number('100') + 10 from dual;
5.4其他函数
--空值处理函数 NVL ：检测的值，如果为null值
select NVL(null,0) from dual;
select NVL2(null,0,100) from dual;
select nvl2(maxnum,to_char(maxnum),'不限') from t_pricetable.
--条件取值 decode
select decode(100,1,2,3,4,100,200) from dual;
参数个数是奇数，就是会缺省。
select name,decode(ownertypeid,1,'people',2,'administrator') from t_owner;
select name,(case ownertypeid when 1 then 'citizen' when 2 then 'administrator' when 3 then 'customer' end) from t_owners;
select name,(case
when ownertypeid = 1 then 'citizen'
when ownertypeid = 2 then 'administrator'
when ownertypeid = 3 then 'customer'
end) from t_owners;
6.行列转换
select (select name from t_area where id=areaid) area,sum(case when month='01' then money else 0 end) January from t_account where year = '2012' group by areaid;
7.分析函数
--值相同，排名相同，序号跳跃
select rank() over( order by usenum desc),t.* from t_account t;
--值相同，排名相同，序号跳跃
select dense_rank() over( order by usenum desc),t.* from t_account t;
--序号连续，不管值是否相同
select row_number() over( order by usenum desc),t.* from t_account t;
--用分析函数实现分页
select * from (select row_number() over(order by usenum desc),t.* from t_account t) where rownumber<=20 and rownumber>10;
8.集合运算
--并集 union all (包括重复内容)
select * from t_owners where id>5
union all
select * from t_owners where id<8;
--并集（去掉重复内容）
select * from t_owners where id>5
union
select * from t_owners where id<8;
--交集，两个集合重复的部分
select * from t_owners where id>5
intersect
select * from t_owners where id<8;
--差集
select * from t_owners where id>5
minus
select * from t_owners where id<8;
--差集分页
select rownum , t.* from t_account t where rownum <= 20
minus
select rownum, t.* from t_account t where rownum <= 10;
9.综合查询
10.总结
扫一扫
600
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
