A股上市公司传智教育(股票代码 003032)旗下技术交流社区北京昌平校区

 找回密码
 加入黑马

QQ登录

只需一步,快速开始

本帖最后由 小石姐姐 于 2018-6-21 16:41 编辑

【石家庄校区】oracle知识总结

Oracle01
1、Oracle数据库可以理解成只有一个统一的数据库。这一个数据库里可以有多个实例,一个实例可以对应多个表空间和多个用户。一个表空间离可以创建多个用户。然后每个用户在自己名下创建多个表。

2、Oracle数据的基本使用
A、创建表空间
create tablespace 表空间名
datafile ‘真实的文件路径’
size 初始文件大小
autoextend on
next 下次自动扩展的空间
例子:
create tablespace waterboss
datafile 'c:\waterboss.dbf'
size 100m
autoextend on
next 10m;
B、创建用户
create user 用户名
identified by 密码
default tablespace 表空间名;
例子:
create user wateruser
identified by itcast
default tablespace waterboss;
C、给用户赋权限
grant dba|resource|connect to 用户名;
例子:grant dba to wateruser;

3、Oracle数据库的数据类型
字符型
char:  固定长度的字符类型,最多存储2000个字节
varchar2:可变长度的字符类型,最多存储4000个字节
long:大文本类型。最大可以存储2个G
数值型
number:数值类型  
number(5)    最大可以存的数为99999   
                     number(5,2)  最大可以存的数为999.99   
日期型
date:日期时间型,精确到秒  
timestamp:精确到秒的小数点后9位
二进制型(大数据类型)
clob:存储字符,最大可以存4个G
blob:存储图像、声音、视频等二进制数据,最多可以存4个G
  
4、创建表
语法:
create table表名称(
  字段名 类型(长度),
  字段名 类型(长度),
  .......
);
例子:
create table t_owners
(
id number,
name varchar2(30),
addressid number check(addressid>0),
housenumber varchar2(30),
watermeter varchar2(30),
adddate date,
ownertypeid number
);

5、修改表
增加字段语法:
alter table表名称 add (列名1 类型 [default 默认值],列名2 类型 [default 默认值]  ...)
修改字段语法:
alter table表名称 modify(列名1 类型 [default默认值],列名2 类型 [default 默认值]...)
修改字段名语法:
alter table 表名称 reanme column 原列名 TO 新列名
删除字段名
删除一个字段
alter table表名称 drop column列名
删除多个字段
alter table表名称 drop(列名1,列名2...)

6、删除表
语法:drop table表名称

7、约束
主键约束
建表时在主键列后添加primary key
例子:
create table t_ownertype
(
id number primary key,
name varchar2(30)
);
如果表已经创建完成,也可以后添加主键约束,语句如下:
alter table t_owners add  primary key(id);
外键约束
alter table t_owners add foreign key (ownertypeid) references t_ownertype(id);
关键字说明:
foreign key:该选项用于指定在表级定义外部键约束。
references:该选项用于指定主表名及其主键列。当定义外部键约束时,该选项必须指定。
非空约束
alter table t_owners modify name not null;
唯一约束
alter table t_owners modify watermeter unique;
检查约束
alter table t_owners modify addressid check(addressid>0);

8、数据增删改
插入数据
语法:insert into表名[(列名1,列名2,...)]values(值1,值2,...)
执行insert后一定要再执行commit提交事务
修改数据
语法:update 表名 set 列名1=值1,列名2=值2,....where 修改条件
执行update后一定要再执行commit提交事务

除数据
语法1:delete from 表名where 删除条件;
执行delete后一定要再执行commit提交事务
(因为Oracle数据库默认的不是自动提交事务,所以在每次操作完增、删、改之后必须commit)
语法2:truncate table 表名称
比较truncat与delete区别:
delete(dml) 事物
truncat (ddl) drop -à create
delete删除的数据可以rollback(删除的数据放在数据段)。
delete删除可能产生碎片,并且不释放空间。
truncate是先摧毁表结构,再重构表结构。
Oracle02
ORACLE查询

一、表单查询
(一)简单条件查询
like '%a%' 是指字符串中有a都算,%是一个或多个字符like '%a' 是指字符串中以a结尾like 'a%' 是指字符串中以a开头like 'a' 是指字符串中有a都算,_只是一个字符like '_a' 是指字符串中以a结尾like 'a_' 是指字符串中以a开头
1.=:精确查询,等号两边的值都是相等的; userid=‘101’
2.like:模糊查询,’%条件%‘ ;username like '%张%'
3.and:并集,两边的条件都成立,才可以;
username like '%张%'and username=30
4.or:或者,两边的条件有一个成立就可以;
username like'%张%' or username=30
5.and 与or的优先级,因为and的优先级比or大,多疑我们需要用() 来改变优先级
6.<=、>=:范围查询 sal>=100、sal<=3000
between..and.. :会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。
7.空值查询: is null(为空)、is not null(不为空)
(二) 去掉重复记录
关键词 distinct用于返回唯一不同的值。
(三) 排序查询
ORDER BY从句后跟要排序的列。ORDER BY 从句出现在SELECT语句的最后。
排序有升序和降序之分,ASC表示升序排序,DESC表示降序排序。如果不指明排序顺序,默认的排序顺序为升序ASC。如果要降序,必须书写DESC关键字
1.升序排序
语句:select * from T_ACCOUNT order by usenum
2.降序排序
语句:select * from T_ACCOUNT order by usenum desc
(四)基于伪劣的查询
Oracle 的表的使用过程中,实际表中还有一些附加的列,称为伪列。伪列就像表中的列一样,但是在表中并不存储,而且伪列只能查询,不能进行增删改操作。
.ROWID伪列
.表中的每一行在数据文件中都有一个物理地址,ROWID伪列返回的就是该行的物理地址,使用ROWID可以快速的定位表中的某一行。(快速定位单行记录)
.ROWID值可以唯一的标识表中的一行。
.由于ROWID返回的是该行的物理地址,因此食用ROWID可以显示行是如何存储的。
查询语句:select rowID,t.* from T_AREA t
t.*------表示查询表t 所有字段
t---T_AREA表的别名,用于书写的时候方便简单,与T_AREA完全相同,只是名字不同而已,就像你的名字和你的小明一样。
2. Rownum伪列
对于rownum来说他是Oracle系统顺序分配为从查询返回的行的编号,返回的第一行分配的是1,第二行是2,依次类推,这个伪字段可以用于限制查询返回的总行数。
查询语句:select * rownum,t.* from T_OWNERTYPE t
(五) 聚合统计
1.聚合函数
(1) sum求和:select sum(usenum) from t_account where year=’2012’
(2)avg求平均:select avg(usenum) from T_ACCOUNT where year='2012'
(3) max求最大值:select max(usenum) from T_ACCOUNT where year='2012'
(4)min求最小值 :select min(usenum) from T_ACCOUNT where year='2012'
(5)count统计记录个数 :select count(*) from T_OWNERS t where ownertypeid=1year='2012‘

2.分组聚合Group by
GROUP BY:select areaid,sum(money) from t_account group by areaid

3.分组后条件查询 having
select areaid,sum(money) fromt_account groupbyareaid having sum(money) > 169000
二、连接查询
(一) 多表内连接查询
查询多个表中所有符合where条件的结果
两张表:
select o.id 业主编号,o.name 业主名称,ot.name 业主类型

from T_OWNERS o,T_OWNERTYPE ot

where o.ownertypeid=ot.id

三张表:


select o.id 业主编号,o.name 业主名称,ot.name 业主类型

from T_OWNERS o,T_OWNERTYPE ot,T_ADDRESS ad

where o.ownertypeid=ot.id and o.addressid=ad.id

四张表:


select o.id 业主编号,o.name 业主名称,ot.name 业主类型

from T_OWNERS o,T_OWNERTYPE ot,T_ADDRESS ad,T_AREA ar

where o.ownertypeid=ot.id and o.addressid=ad.id and ad.areaid=ar.id

(二) 左外连接查询
以左边的表为主,左边的表数据都能显示,并显示右边表中与左边的表中关联的数据。
标准的写法,使用left join 表名 on 条件:


SELECT ow.id,ow.name,ac.year,ac,month,ac.money

FROM T_OWNERS ow left join T_ACCOUNT ac

on ow.id=ac.owneruuid

Oracle提供的写法:使用(+)在被关联的表一方


SELECT ow.id,ow.name,ac.year,ac.month,ac.money FROM T_OWNERS ow,T_ACCOUNT ac WHERE ow.id=ac.owneruuid(+)

(三) 右外来连接查询
以右边的表为主,显示右边的表中的所有数据的同时,显示左边关联的数据。
标准的写法:使用right join 表名 on


select ow.id,ow.name,ac.year,ac.month,ac.money

from T_OWNERS ow right join T_ACCOUNT ac

on ow.id=ac.owneruuid

Oracle提供的方法:使用(+)在左边的表上


select ow.id,ow.name,ac.year,ac.month,ac.money

from T_OwNERS ow, T_ACCOUNT ac

where ow.id(+)=ac.owneruuid

三、子查询
(一)where子句中的子查询
单行子查询 语法:
select 字段列表
from table
where 表达式 operator(select 字段列表 from table);
只能使用单行操作符
操作符
含义
=
Equal to
>
Greater than
>=
Greater than or equal to
<
Less than
<=
Less than or equal to
<>
Not equal to
多行子查询
多行操作符:
操作符
含义
IN
等于列表中的任何一个
ANY
和子查询放回的任意一个值比较
ALL
和子查询返回的所有值比较


select * from emp

where sal>any(select avg(sal) from emp group by deptno)



select * from emp

where sal>all(select avg(sal)from emp group by deptno);



select * from emp

where job in(select job from emp where ename='martin' or  ename='ssss');

(二)from子句中的子查询
Select 查询到的结果我们可以作为一个虚拟的表,所以我们可以在其中在进行查询


select * from

(select o.id 业主编号,o.name 业主名称,ot.name 业主类型

from T_OWNERS o,T_OWNERTYPE ot

where o.ownertypeid=ot.id)

where 业主类型='居民'

(三)select子句中的子查询
select 子句的子查询必须为单行子查询。


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=addressid)

adrename

from t_owners;

四、分页查询
(一) 简单分页
分页的原理,借助于oracle提供的伪列rownum,为结果集生成行号,但是rownum只能使用<=,不能使用>,所以还需要使用子查询来完成。


select rownum,t.* from T_ACCOUNT t

where rownum>10 and rownum<=20

五、单行函数
(一)字符函数
常用的字符函数:
CONCAT:拼接两个字符串,相当于||


select concat('hello,'oracle')"concat",HELLO,'||'ORACLE' from dual

INITCAP:将字符串的第一个字母变为大写
LOWER/UPPER:把字符转换成小写/大写


select initcap('oracle')"首字母大写",;pwer('OraCle')"转小写",upper('OraCol')"转大写" from dual

INSTR:找出某个字符的位置


select instr('oracle','r') from dual

LENGTH:字符串的长度
​ LENGTHB:字符串的长度,(当字符串是中文时,得到的值有length得到的值不同)


select length('oracle'),length('中文'),lengthb(''oracle),lengthb('中文') from dual

LPAD/RPAD:用指定的字符在左侧/右侧填充
LTRIM/RTRIM:把左边/右边的指定字符剪掉
TRIM:剪掉左右两边的指定字符


select lpad('oracle',12,'hello'),read('oracle',9,'10g'),ltrim('aoraclea','a')

trim('a' from 'aoraclea') from dual

REPLACE:替换字符


select replace('hello,oracle 10g','10g','11g') from dual

SUBSTR:截取字符串


select substr('hello,oracle',3),substr('hello,oracle',4,3)

from dual

(二)数值函数
MOD:求模


select mod(100,7) from dual

ROUND:四舍五入


select round(155,445),round(155,445,2),round(155.445,1),round(155,445,-1) from dual

TRUNC:截取数值


select trunc(155.445),trunc(155,445,2),trunc(155.445.1),trunc(155.445,-1) from dual

CEIL:向上取整
FLOOR:向下取整


select ceil(155,445)"向上取整",floor(155.445) "向下取整" from dual

(三)日期函数
获取系统时间:


select sysdate from dual;

常用的日期函数
ADD_MONTHS(date,months):咋达特日期上加上months个月


select sysdate,add_months(sysdate,1)"+1",add_months(sysdate,-2) "-2" from dual

LAST_DAY(date):date所在月份的最后一天的日期


select sysdate "当前日期",last_day(sysdate)"该月最后一天" from dual

MONTHS_BETWEEN(date1,date2):计算两个日期之间相隔的月数


select months_between(to_date('2016-2-2',yyyy-mm-dd'),to_date('2016-01-02','yyyy-mm-dd')) from dual

NEXT_DAY(date,’day’): 给出日期date之后下一天的日期,这里的day为星期,如: MONDAY,Tuesday等。


select sysdate,next_day(sysdate,'星期一')"next_day" from dual

ROUND(date,’format’):
select sysdate,round*(sysdate),round(sysdate,'yyyy'),round(sysdate,'mm'),round(sysdate,'dd') from dual;
TRUNC(date,’format’):
select sysdate,trunc(sysdate),trunc*(sysdate,'yyyy'),trunc(sysdate,'mm'),trunc(sysdate,'dd')from dual;
(四) 转换函数
常用的转换函数:
TO_DATE():
select to_date('199912','yyyymm'),
to_date('2000.05.20'.'yyyy.mm.dd'),
(date '2008-12-31') XXdate,
to_date('2008-12-31 12:31:30','yyyy-mm-dd hh24:mi:ss'),
(timestamp '2008-12-31  12:31:30') XXtimestamp
from dual;
TO_NUMBER():
select TO_NUMBER('199912').TO_NUMBER('450.05') from dual;
TO_CHAR()
to_char(1210.73,'9999.9') 返回'1210.7'
to_char(1210.73,'9,999.99')返回'1',210.73'
to_char(1210.73.'$9,999,00') 返回 '$1,210.73'
to_char(21,'000099') 返回 '000021'
to_char(852,'xxxx')返回 '354'
to_char(sysdate,'d')每周第几天
to_char(sysdate,'dd')每月第几天
to_char(sysdate,'ddd')每年第几天
to_char(sysdate,'ww')每年第几周
to_char(sysdate,'mm')每年第几月
to_char(sysdate,'q')每年第几季
to_char(sysdate,'yyyy')年
(五)其他函数
1、空值处理函数NVL/NVL2
【语法】NVL (expr1.expr2)
【功能】若expr1为NULL,返回expr2; expr1不为NULL,返回expr1
        注意两者的类型要一致
【语法】NVL2(expr1,expr2, expr3)
【功能】expr1不为NULL,返回expr2;expr2为NULL,返回expr3.
                expr2和expr3类型不同的话,expr3会转换为expr2的类型
2、条件取值
含义解释
decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
该函数的含义如下:
IF 条件=值 1 THEN
RETURN(翻译值1)
ElSIF 条件=值2 THEN
RETURN(翻译值2)
......
ELSIF 条件=值1 THEN
RETURN(翻译值n)
ELSE
RETURN(缺省值)
END IF
六、行列转换
case when then end的实际应用
使用case when then end能实现的都能使用decode实现
七、分析函数
RANK:相同的值排名相同,排名跳跃;
DENSE_RANK:相同的值排名相同,排名连续
【语法】RANK () OVER([query_partition_clause]   order_by_clause)
dense_RANK() OVER([query_partition_clause] order_by_clause)
【功能】聚合函数RANK和dense_rank主要的功能是计算一组数值中的排序值。
ROW_NUMBER:返回连续的排名,无论值是否相等
【语法】ROW_NUMBER() OVER (PARYITION BY COL1 ORDER BY CLOL2)
【功能】表示根据COL1分组,在分组内部根据COL2排序,而这个值就表示每组内部排序后的顺序编号(组内连续的唯一的 row_number()返回的主要是"行"的信息,并没有排名)
主要功能:用于取前几位,或者最后几名等
八、集合运算
(一)并集运算: UNION ALL 并集,不会去掉重复记录,
UNION:并集,去掉重复记录
(二)交集运算:INTERSECT
(三)查集运算:MINUS(可以用来实现分页查询)


0 个回复

您需要登录后才可以回帖 登录 | 加入黑马