Oracle 学习笔记

概述

特点

• 支持多用户、大事务量的事务处理
• 数据安全性和完整性控制
• 支持分布式数据处理
• 可移植性
  
  

体系结构

• 全局数据库，只有一个数据库，可以把Oracle看作一个大数据库
• 实例，一系列的后台进程和内存结构组成，一个数据库有n个实例
• 数据文件，是数据库的物理储存单位
• 表空间，是Oracle对物理数据库上相关数据文件(ORA或者DBF文件)的逻辑映射
  数据库 ---> 表空间 ---> 段 ---> 区 ---> 数据块 ---> 磁盘块(物理级) 最小单位是数据块
• 用户，一个数据库下有很多用户，用户下建立表
  
  

创建表空间

[SQL] 纯文本查看 复制代码

create tablespace 表空间名称     ：创建表空间，waterboss是名字
datafile 'c:\waterboss.dbf'     ：表空间对应的数据文件
size 100m       :指定大小，m 代表兆，先占100兆
autoextend on   :大小满了，自动扩充
next 10m        :增长10兆，只要满了就增

创建用户

[SQL] 纯文本查看 复制代码

create user 用户名称        :创建用户wateruser
identified by itheima       :设置密码为 itheima
default tablespace waterboss    :默认表空间是waterboss用户赋权grant dba to 用户名称       :给wateruser用户，设置 dba 权限
resource    ：
connect     ：只能看

创建表

[SQL] 纯文本查看 复制代码

create table 表名称(
    字段名 类型(长度) 约束，
    字段名 类型(长度) 约束，
    ....
);

表数据类型

• 字符型
  char：固定长度的字符类型，最多储存2000个字节，不足2000字节，后面空格补充varchar2:可变长度的字符类型，最多储存4000个字节long:大文本类型，最大可以储存2个G
• 数值型
  number        :数值类型（默认18）number(5)        :最大可以存的数为99999  最大 5 位数number(5,2)        :最大可以存的数为999.99        最大 5 位数，小数位 2 位
• 日期型
  date:日期时间型，精确到秒timestamp:精确到秒的小数点后 9 位填写数据的时候，写 sysdate 就是存入当前日期
• 二进制型(大数据类型)
  clob        :存储字符，最大可以存 4 个 Gblob:存储图像、声音、视频等二进制数据，最多可以存 4 个 G图像、声音等等基本存到硬盘，数据库存地址
  
  

修改表类型

[SQL] 纯文本查看 复制代码

1.追加字段（给现有的表添加字段）
    alter table 表名称 add(
        字段名称 类型(长度)，
        ....
    )
2.修改字段
    alter table 表名称 modify(列名1 类型[default 默认值],列名2 类型[default 默认值],.....)
3.修改字段名
    alter table 表名称 rename column 原列名 to 新列名
4.删除字段名
    alter table 表名称 drop column 列名1，列名2；
5.修改字段长度

删除表

[SQL] 纯文本查看 复制代码

drop table 表名称

数据库增删改

[SQL] 纯文本查看 复制代码

1.插入数据
    insert into 表名[(列名1，列名2,...)]values(值1，值2，.....)
    执行 insert 后一定要再执行 commit 提交事务
2.修改数据
    update 表名 set 列名1=值1，列名2=值2，...where 修改条件；
    update 表名 set 日期字段=日期字段-3 where id = 1；

   

Oracle查询

• 单表查询
  
  

not,and,or关键字的执行优先级: not > and > or;可以用()来解决优先级

模糊查询: 使用like关键字,%作为站位符

and运算符: 表示并且

or运算符: 表示或者

范围查询: <,<=,>,>=,<>

between .. and ..也可以实现范围查询

示例:

[SQL] 纯文本查看 复制代码

select * from 表名 where 列名 between 开始值 and 结束值;

空值查询:

is null关键字查询为空的记录

is not null关键字查询不为空的记录

去重查询: 使用distinct关键字

排序查询: 使用order by 关键字,默认是升序asc(可以省略不写),降序是desc

伪列查询:

rowid : 表中的每一行数据的物理地址,唯一的

rownum : 查询结果集的行号 //可以用来实现分页查询

聚合函数:

求和 sum关键字

平均值 avg关键字

最大值 max关键字

最小值 min关键字

统计个数 count关键字

分组聚合: 使用group by关键字

注意: 有分组条件的话select 后只能是分组条件或者是聚合函数

分组后条件查询: 使用having关键字

MySQL语句顺序的写法(可以没有的但是顺序不可以颠倒):

[SQL] 纯文本查看 复制代码

(select)… F(from)…W(where)…G(group by)…H(having)…O(order by);

SQL语句的执行顺序

[SQL] 纯文本查看 复制代码

开始->FROM子句->WHERE子句->GROUP BY子句->HAVING子句->SELECT子句->LIMIT子句->ORDER BY 子句 最终结果

• 连接查询
  
  

内连接语法:

[SQL] 纯文本查看 复制代码

elect * from 表名1,表名2,... where 关联条件; 

外连接:

SQL1999 标准的语法:

左外: 使用left join关键字

右外: 使用right join关键字

oracle中也可以简化:

左外连接,就在右表所在的条件一端加上（+）,右外相反;

子查询:

概述: 一个语句的查询结果是另一个语句的查询条件

单行子查询

只返回一条记录

单行操作符 = <> > >= < <=

多行子查询

返回多条记录

多行操作符:

in: 等于任何一个

any: 和子句查询返回的任意一个值比较

all: 和子句查询返回的所有值比较

应用范围:

from子句中的子查询为多行子查询

select 子句的子查询必须为单行子查询

where子句中的子查询

分页查询

使用rownum进行分页查询

rownum后只能使用小于或小于等于

rownum为扫描到的匹配条件的记录添加一个从1开始的序号,每次都是从1开始

示例:

需求：分页查询台账表 T_ACCOUNT，每页 10 条记录，按使用字数降序排序。

语句:

[SQL] 纯文本查看 复制代码

select * from (select rownum r,t.* from (select * from T_ACCOUNT order by usenum desc) t) where r>10

• 单行函数:
  
  

字符函数:

length求字符串长度

substr截取字符串,也叫求字符串的子串

concat字符串拼接,也可以使用 || 对字符串拼接

数值函数:

round四舍五入

trunc截取函数

mod取模,取余

日期函数 :

sysdate获取当前日期和时间

add_months在当前日期基础上加指定的月

last_day获取所在月的最后一天

trunc日期截取

yyyy是年

mm是月

dd是日

hh时

mi分

ss秒

转换函数:

to_char数字转字符串(使用||+" "也可以)

to_char日期转字符串

to_date字符串转日期

to_number字符串转数字

(+0也可以转换)

其他函数

nvl空值处理

语法: nul(检测的值,如果为null输出的值);

[SQL] 纯文本查看 复制代码

select nvl(null,0) from dual;        //结果为0 

示例:

nvl2空值处理函数

语法: nvl2(检测的值,如果不为null输出的值,如果为null输出的值)

示例:

需求：显示价格表中业主类型 ID 为 1 的价格记录，如果上限值为 NULL,显示“不限”

语句:

[SQL] 纯文本查看 复制代码

select PRICE,MINNUM,NVL2(MAXNUM,to_char(MAXNUM) , '不限')from T_PRICETABLE where OWNERTYPEID=1; 

decode条件取值

语法: decode(条件,值1,输出的值1,... 缺省值);//根据条件返回相应的值,没有缺省值也没有对应的值返回一个空,有缺省值返回缺省值

示例:

[SQL] 纯文本查看 复制代码

select name,decode( ownertypeid,1,'居民',2,'行政事业单位',3,'商业') from T_OWNERS;

case ... when ... then语句也可以实现

写法一:

[SQL] 纯文本查看 复制代码

select name,(case
    when ownertypeid= 1 then '居民'
    when ownertypeid= 2 then '行政事业'
    when ownertypeid= 3 then '商业'
    else '其它'
    end)
from T_OWNERS

​

写法二:

[AppleScript] 纯文本查看 复制代码

select name ,(case ownertypeid
    when 1 then '居民'
    when 2 then '行政事业单位'
    when 3 then '商业'
    else '其它'
    end)
from T_OWNERS

​

分析函数

1. RANK 相同的值排名相同，排名跳跃

2. DENSE_RANK 相同的值排名相同，排名连续

3. ROW_NUMBER 返回连续的排名，无论值是否相等

集合运算

概述: 集合运算，集合运算就是将两个或者多个结果集组合成为一个结果集

集合运算包含:

UNION ALL(并集)，返回各个查询的所有记录，包括重复记录。

UNION(并集)，返回各个查询的所有记录，不包括重复记录。

INTERSECT(交集)，返回两个查询共有的记录。

MINUS(差集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。

可以使用minus运算符来实现分页

示例:

[SQL] 纯文本查看 复制代码

select rownum,t.* from T_ACCOUNT t where rownum<=20 
minus
select rownum,t.* from T_ACCOUNT t where rownum<=10;

Oracle 学习笔记

概述

特点

• 支持多用户、大事务量的事务处理
• 数据安全性和完整性控制
• 支持分布式数据处理
• 可移植性
  
  

体系结构

• 全局数据库，只有一个数据库，可以把Oracle看作一个大数据库
• 实例，一系列的后台进程和内存结构组成，一个数据库有n个实例
• 数据文件，是数据库的物理储存单位
• 表空间，是Oracle对物理数据库上相关数据文件(ORA或者DBF文件)的逻辑映射
  数据库 ---> 表空间 ---> 段 ---> 区 ---> 数据块 ---> 磁盘块(物理级) 最小单位是数据块
• 用户，一个数据库下有很多用户，用户下建立表
  
  

创建表空间

[SQL] 纯文本查看 复制代码

create tablespace 表空间名称     ：创建表空间，waterboss是名字
datafile 'c:\waterboss.dbf'     ：表空间对应的数据文件
size 100m       :指定大小，m 代表兆，先占100兆
autoextend on   :大小满了，自动扩充
next 10m        :增长10兆，只要满了就增

创建用户

[SQL] 纯文本查看 复制代码

create user 用户名称        :创建用户wateruser
identified by itheima       :设置密码为 itheima
default tablespace waterboss    :默认表空间是waterboss用户赋权grant dba to 用户名称       :给wateruser用户，设置 dba 权限
resource    ：
connect     ：只能看

创建表

[SQL] 纯文本查看 复制代码

create table 表名称(
    字段名 类型(长度) 约束，
    字段名 类型(长度) 约束，
    ....
);

表数据类型

• 字符型
  char：固定长度的字符类型，最多储存2000个字节，不足2000字节，后面空格补充varchar2:可变长度的字符类型，最多储存4000个字节long:大文本类型，最大可以储存2个G
• 数值型
  number        :数值类型（默认18）number(5)        :最大可以存的数为99999  最大 5 位数number(5,2)        :最大可以存的数为999.99        最大 5 位数，小数位 2 位
• 日期型
  date:日期时间型，精确到秒timestamp:精确到秒的小数点后 9 位填写数据的时候，写 sysdate 就是存入当前日期
• 二进制型(大数据类型)
  clob        :存储字符，最大可以存 4 个 Gblob:存储图像、声音、视频等二进制数据，最多可以存 4 个 G图像、声音等等基本存到硬盘，数据库存地址
  
  

修改表类型

[SQL] 纯文本查看 复制代码

1.追加字段（给现有的表添加字段）
    alter table 表名称 add(
        字段名称 类型(长度)，
        ....
    )
2.修改字段
    alter table 表名称 modify(列名1 类型[default 默认值],列名2 类型[default 默认值],.....)
3.修改字段名
    alter table 表名称 rename column 原列名 to 新列名
4.删除字段名
    alter table 表名称 drop column 列名1，列名2；
5.修改字段长度

删除表

[SQL] 纯文本查看 复制代码

drop table 表名称

数据库增删改

[SQL] 纯文本查看 复制代码

1.插入数据
    insert into 表名[(列名1，列名2,...)]values(值1，值2，.....)
    执行 insert 后一定要再执行 commit 提交事务
2.修改数据
    update 表名 set 列名1=值1，列名2=值2，...where 修改条件；
    update 表名 set 日期字段=日期字段-3 where id = 1；

   

Oracle查询

• 单表查询
  
  

not,and,or关键字的执行优先级: not > and > or;可以用()来解决优先级

模糊查询: 使用like关键字,%作为站位符

and运算符: 表示并且

or运算符: 表示或者

范围查询: <,<=,>,>=,<>

between .. and ..也可以实现范围查询

示例:

[SQL] 纯文本查看 复制代码

select * from 表名 where 列名 between 开始值 and 结束值;

空值查询:

is null关键字查询为空的记录

is not null关键字查询不为空的记录

去重查询: 使用distinct关键字

排序查询: 使用order by 关键字,默认是升序asc(可以省略不写),降序是desc

伪列查询:

rowid : 表中的每一行数据的物理地址,唯一的

rownum : 查询结果集的行号 //可以用来实现分页查询

聚合函数:

求和 sum关键字

平均值 avg关键字

最大值 max关键字

最小值 min关键字

统计个数 count关键字

分组聚合: 使用group by关键字

注意: 有分组条件的话select 后只能是分组条件或者是聚合函数

分组后条件查询: 使用having关键字

MySQL语句顺序的写法(可以没有的但是顺序不可以颠倒):

[SQL] 纯文本查看 复制代码

(select)… F(from)…W(where)…G(group by)…H(having)…O(order by);

SQL语句的执行顺序

[SQL] 纯文本查看 复制代码

开始->FROM子句->WHERE子句->GROUP BY子句->HAVING子句->SELECT子句->LIMIT子句->ORDER BY 子句 最终结果

• 连接查询
  
  

内连接语法:

[SQL] 纯文本查看 复制代码

elect * from 表名1,表名2,... where 关联条件; 

外连接:

SQL1999 标准的语法:

左外: 使用left join关键字

右外: 使用right join关键字

oracle中也可以简化:

左外连接,就在右表所在的条件一端加上（+）,右外相反;

子查询:

概述: 一个语句的查询结果是另一个语句的查询条件

单行子查询

只返回一条记录

单行操作符 = <> > >= < <=

多行子查询

返回多条记录

多行操作符:

in: 等于任何一个

any: 和子句查询返回的任意一个值比较

all: 和子句查询返回的所有值比较

应用范围:

from子句中的子查询为多行子查询

select 子句的子查询必须为单行子查询

where子句中的子查询

分页查询

使用rownum进行分页查询

rownum后只能使用小于或小于等于

rownum为扫描到的匹配条件的记录添加一个从1开始的序号,每次都是从1开始

示例:

需求：分页查询台账表 T_ACCOUNT，每页 10 条记录，按使用字数降序排序。

语句:

[SQL] 纯文本查看 复制代码

select * from (select rownum r,t.* from (select * from T_ACCOUNT order by usenum desc) t) where r>10

• 单行函数:
  
  

字符函数:

length求字符串长度

substr截取字符串,也叫求字符串的子串

concat字符串拼接,也可以使用 || 对字符串拼接

数值函数:

round四舍五入

trunc截取函数

mod取模,取余

日期函数 :

sysdate获取当前日期和时间

add_months在当前日期基础上加指定的月

last_day获取所在月的最后一天

trunc日期截取

yyyy是年

mm是月

dd是日

hh时

mi分

ss秒

转换函数:

to_char数字转字符串(使用||+" "也可以)

to_char日期转字符串

to_date字符串转日期

to_number字符串转数字

(+0也可以转换)

其他函数

nvl空值处理

语法: nul(检测的值,如果为null输出的值);

[SQL] 纯文本查看 复制代码

select nvl(null,0) from dual;        //结果为0 

示例:

nvl2空值处理函数

语法: nvl2(检测的值,如果不为null输出的值,如果为null输出的值)

示例:

需求：显示价格表中业主类型 ID 为 1 的价格记录，如果上限值为 NULL,显示“不限”

语句:

[SQL] 纯文本查看 复制代码

select PRICE,MINNUM,NVL2(MAXNUM,to_char(MAXNUM) , '不限')from T_PRICETABLE where OWNERTYPEID=1; 

decode条件取值

语法: decode(条件,值1,输出的值1,... 缺省值);//根据条件返回相应的值,没有缺省值也没有对应的值返回一个空,有缺省值返回缺省值

示例:

[SQL] 纯文本查看 复制代码

select name,decode( ownertypeid,1,'居民',2,'行政事业单位',3,'商业') from T_OWNERS;

case ... when ... then语句也可以实现

写法一:

[SQL] 纯文本查看 复制代码

select name,(case
    when ownertypeid= 1 then '居民'
    when ownertypeid= 2 then '行政事业'
    when ownertypeid= 3 then '商业'
    else '其它'
    end)
from T_OWNERS

​

写法二:

[AppleScript] 纯文本查看 复制代码

select name ,(case ownertypeid
    when 1 then '居民'
    when 2 then '行政事业单位'
    when 3 then '商业'
    else '其它'
    end)
from T_OWNERS

​

分析函数

1. RANK 相同的值排名相同，排名跳跃

2. DENSE_RANK 相同的值排名相同，排名连续

3. ROW_NUMBER 返回连续的排名，无论值是否相等

集合运算

概述: 集合运算，集合运算就是将两个或者多个结果集组合成为一个结果集

集合运算包含:

UNION ALL(并集)，返回各个查询的所有记录，包括重复记录。

UNION(并集)，返回各个查询的所有记录，不包括重复记录。

INTERSECT(交集)，返回两个查询共有的记录。

MINUS(差集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。

可以使用minus运算符来实现分页

示例:

[SQL] 纯文本查看 复制代码

select rownum,t.* from T_ACCOUNT t where rownum<=20 
minus
select rownum,t.* from T_ACCOUNT t where rownum<=10;