Oracle常用语句及问题处理

  1. 1. Oracle基础及增删查改
    1. 1.1 Oracle基础概念
      1. 1.1.1 SQL语句分类
      2. 1.1.2 视图
      3. 1.1.3 索引
      4. 1.1.4 数据字典
      5. 1.1.5 空值null
      6. 1.1.6 主键和外键
      7. 1.1.7 联合主键
    2. 1.2 Oracle增删查改
      1. 1.2.1 新增数据
      2. 1.2.2 删除数据
      3. 1.2.3 查找数据
      4. 1.2.4 修改数据
  2. 2. Oracle常用函数及模板语句
    1. 2.1 Oracle常用函数
      1. 2.2.1 数值型函数
      2. 2.2.2 字符型函数
      3. 2.2.3 日期型函数
      4. 2.2.4 聚合函数
    2. 2.2 Oracle模版语句
      1. 2.2.1 查询指定时间段下的记录
      2. 2.2.2 查找用户与表的所属关系
      3. 2.2.3 在查询结果中新增一列序号
      4. 2.2.4 查找删除重复记录
      5. 2.2.5 解决数据库锁表问题
      6. 2.2.6 建立dblink跨库查询数据
      7. 2.2.7 查询数据表的创建时间
      8. 2.2.8 查询sql语句执行记录
      9. 2.2.9 使用merge语句批量增删改
      10. 2.2.10 比较两数据表的差异
      11. 2.2.11 与序列有关的查询
      12. 2.2.12 查询数据库版本
  3. 3. Oracle常见问题及SQL性能优化
    1. 3.1 Oracle常见问题
      1. 3.1.1 时间戳转日期
      2. 3.1.2 不存在的 LOB值 问题
      3. 3.1.3 批量插入数据
      4. 3.1.4 除数为0的解决办法
      5. 3.1.5 直接查询clob格式的数据
      6. 3.1.6 清理数据库内存的只需计划
      7. 3.1.7 单字段去重,然后再加列序号
      8. 3.1.8 将表的一列数据赋值给另一列
      9. 3.1.9 拼接单引号
    2. 3.2 Oracle语句对比
      1. 3.2.1 drop、truncate、delete的对比
      2. 3.2.2 in与exists的对比
      3. 3.2.3 select 1 与select * 的对比
      4. 3.2.4 case … when … then … else … end与decode的对比
      5. 3.2.5 外连接以及(+)号用法
      6. 3.2.6 union与union all的对比
      7. 3.2.7 delete from 与 delete的对比
      8. 3.2.8 for update 和select t.*,t.rowid编辑数据的对比
    3. 3.3 SQL性能优化
      1. 3.3.1 关于全表扫描的注意点
      2. 3.3.2 关键字替换查询
  4. 4. Oracle数据导入导出及备份
    1. 4.1 使用命令以dmp的形式导入导出
      1. 4.1.1 导入导出一个用户下的所有表
      2. 4.1.2 导入导出单个表
      3. 4.1.3 导入导出可能会遇到的问题
      4. 4.1.4 服务器上数据库定时备份
    2. 4.2 以Excel的形式导入导出
    3. 4.3 使用临时表备份旧表数据
      1. 4.3.1 创建新表保存旧表数据
      2. 4.3.2 使用已存在的表保存旧表数据
    4. 4.4 两个数据库进行数据同步
    5. 4.5 在当前用户下备份所有表的数据
  5. 5. MySQL与Oracle的不同之处
    1. 5.1 二者语法差异
    2. 5.2 MySQL模板语句
    3. 5.3 MySQL常见问题
  6. 6. 参考资料

1. Oracle基础及增删查改

1.1 Oracle基础概念

1.1.1 SQL语句分类

  • DQL(数据查询语言)select
  • DML(数据操作语言)insert、delete、update
  • DDL(数据定义语言)create、drop、alter
  • DCL(数据控制语言)grant、revoke
  • TCL(事务控制语言)commit、rollback

1.1.2 视图

  • 虚表,是从一个或几个基本表(或视图)导出的表
  • 只存放视图的定义,不存放视图对应的数据
  • 基表中的数据发生变化,从视图中查询出的数据也随之改变

1.1.3 索引

  • 建立索引的目的:加快查询速度
  • 关系数据库管理系统中常见索引:顺序文件上的索引、B+树索引、散列(hash)索引、位图索引
  • 特点:B+树索引具有动态平衡的优点、HASH索引具有查找速度快的特点
  • 谁可以建立索引:数据库管理员或表的属主
  • 谁维护索引:关系数据库管理系统自动完成
  • 使用索引:关系数据库管理系统自动选择合适的索引作为存取路径,用户不必也不能显式地选择索引

1.1.4 数据字典

  • 数据字典是关系数据库管理系统内部的一组系统表,它记录了数据库中所有定义信息

1.1.5 空值null

  • 不存在,数据库中任何数据类型均可取null值,null参与的所有运算结果都为null(null or true和 null and false除外),在sql中null只能用is null和is not null判断

1.1.6 主键和外键

  • 主键(PK):是表中的一个或多个字段,它的值唯一的标识表中的某一条记录(组成主键的列中不能包含空值null),一个表最多只能有一个主键。(唯一性、非空性)
  • 外键(FK):表示的是两个关系之间的相关联系。如果关系R中的某属性组不是R的主键,但确是另一个关系S的主键,则该属性组是关系R的外键(R和S可以是同一关系)。其中R叫做参照关系,S叫做被参照关系。外键的取值允许重复。(允许重复、允许空值)

1.1.7 联合主键

  • 数据库的每张表只能有一个主键,不可能有多个主键。
  • 所谓的一张表多个主键,我们称之为联合主键(就是用多个字段一起作为一张表的主键)
  • 主键的作用是保证数据的唯一性和完整性,同时通过主键检索表能够增加检索速度。

1.2 Oracle增删查改

1.2.1 新增数据

1
2
3
4
5
6
//直接插入
insert into tablename(fieldname1, fieldname2) values(value1,value2);
//从其他表获取值插入,需列数和类型一致
insert into tablenamea(fieldname1, fieldname2) select fieldname3,fieldname4 from tablenameb;
//创建数据表时插入
create table tablenamea as select fieldname1,fieldname2 from tablenameb;

1.2.2 删除数据

1
2
3
4
5
6
7
8
//删除表中全部数据(不删除表结构),可回滚
delete from tablename;
//删除表中符合条件的数据(不删除表结构),可回滚
delete from tablename where filedname expression value;
//删除表中全部数据(不删除表结构),比delete快,不可回滚
truncate table tablename;
//删除表中全部数据及表结构,不可回滚
drop table tablename;

1.2.3 查找数据

[1] 单表查询

1
2
3
4
5
6
7
select select_list
from table_list
[where] [where_clause]
[and] [and_clause]
[group_by] [group_by_clause]
[having] [condition]
[order_by] [order_by_clause]

[2] 连接查询

1
2
3
4
5
6
7
8
9
10
11
//交叉连接--得到两张表的笛卡尔积
select * from tablea a, tableb b;
//自连接--是连接表与自身的连接(示例见实例[2])
select * from tablea a, tablea b
//内连接--返回来自源表中的相关的行,用关系运算符过滤(并不是加了inner join的才叫内连接)
eg:select * from hr.countries t where region_id=1
//外连接
left join…on 左外连接(左边的表不加限制)
right join…on 右外连接(右边的表不加限制)
full join…on 全外连接(左右两表都不加限制)
(+)写法:+表示补充,即哪个表有加号,这个表就是匹配表(eg:如果加号写在右表,左表就是全部显示,是左连接)

[3] 常用语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
// 设置别名
select sysdate time from dual; 或 select sysdate as time from dual;
// 查询结果排序
order by fieldname desc|asc (desc降序、asc升序)
// 关系操作符
<,>,<>,!=, <=,=, >= (<>与!=都是不等于,完全等同)
// 比较操作符
is null, between…and…, in,exists,like (_代表一个字符,%代表多个字符)
// 逻辑操作符
and, or, not
// 分组
group by 一般和聚合函数一起使用才有意义
having (给group by添加限制条件) eg:group by age having count(*)>1
// 连接运算符
union 取并集,去重
union all 取并集,不去重
intersect 取交集
minus 用来返回前面查询减去后面查询的部分

1.2.4 修改数据

1
2
//修改满足条件的数据
update tablename set fieldname1 = value1 where fieldname2 = value2;

2. Oracle常用函数及模板语句

2.1 Oracle常用函数

2.2.1 数值型函数

函数 解释 输入 输出
abs(n) 绝对值 select abs(-100) from dual; 100
sign(n) 正负判断(正数返回1、0返回0、负数返回-1) select sign(-30) from dual; -1
mod(m,n) 取余(m除以n的余数, n为0返回m) select mod(7,3) from dual; 1
round(m,n) 四舍五入(m是操作数,n是四舍五入到第几位) select round(100.256,2) from dual; 100.26
trunc(m,n) 截断(m是操作数,n是截取到第几位) select trunc(100.256,2) from dual; 100.25
power(m, n) 指数函数(返回m的n次幂) select power(3,2) from dual; 9
exp(n) 指数函数(返回e的n次幂) select exp(2) from dual; 7.38905609893065
log(m,n) 对数函数(返回以m为底n的对数) select log(2,8) from dual; 3
ln(n) 对数函数(返回n的自然对数) select ln(exp(2)) from dual; 2
sqrt(n) 求n的算术平方根 select sqrt(4) from dual; 2

2.2.2 字符型函数

函数 解释 输入 输出
lower(string) 大写转小写 select lower(‘FUN’)from dual; fun
upper(string) 小写转大写 select upper(‘fun’)from dual; FUN
initcap(string) 首字母大写 select initcap(‘fun’)from dual; Fun
trim(string),Itrim(string),rtrim(string) 去左右空格 select trim(‘ adams ‘)from dual; adams
to_number(string) 字符转数字 select to_number(‘123’) from dual; 123
replace(string,oldstr,newstr) 替换字符串 select replace(‘jackandjue’,’j’,’bl’) from dual; blackandblue
instr(string,m) 测试子串是否存在(存在输出为子串初次出现的位置,不存在返回0) select instr(‘worldwide’,’d’) from dual; 5
|| 字符串连接 select ‘hello’||’world’ from dual; helloworld
substr(string,m,n) 截取子字符串(m代表从第几位开始取,n代表取几位) select substr(‘abcdefg’,5,3) from dual; efg
length(string) 返回字符串的长度 select length(‘abc’) from dual; 3
rpad(expr1,n,expr2) 在expr1右边用expr2填充,直到长度为 n select rpad(‘1’,5,’0’) from dual; 10000
lpad(expr1,n,expr2) 在expr1左边用expr2填充,直到长度为 n select lpad(‘1’,5,’0’) from dual; 00001
nvl(null,’0’) 空值转换 select nvl(null,’0’) from dual; 0
decode(input,value,result ,default_result) 它将输入数值与函数中的参数列表相比较,根据输入值返回一个对应值。 select decode(to_char(sysdate,’mm’),’01’,’1月’,’02’,’2月’,’03’,’3月’,’4-12月’) from dual; 如果当前为1-3月返回当前月数,否则返回4-12月
to_char(wm_concat(column)) 是将查询结果列合并成一行,之间用逗号分隔显示 select to_char(wm_concat(name)) from hr.sys_code where code = ‘LEVEL’ 省级,地市级,区县级
sys_guid() 产生并返回一个全球唯一的标识符,经常用来做表的主键 select sys_guid() from dual BC28432D40E 16746E0531C 33010AB136
distinct column 记录去重
rownum 截取记录数

2.2.3 日期型函数

函数 解释 输入 输出
sysdate 系统的当前日期和时间,包括年月日和时分秒 select sysdate from dual; 2020/11/12 16:30:11
to_char (date,’format’) 日期按照format格式转为字符串 select to_char(sysdate,’yyyymmddhh24miss’) from dual; 20201112152348
to_date (string,’format’) 字符串按照format格式转为日期 select to_date(20201112152348,’yyyymmddhh24miss’) from dual; 2020/11/12 15:23:48
months_between(date1, date2) 返回给定日期之间的月数(date1:结束时间,date2:开始时间) select months_between(to_date(‘202011’,’yyyymm’), to_date(‘202007’,’ yyyymm’)) from dual; 4
add_months(date, integer) 日期加指定月 select add_months(sysdate,2) from dual; 2021/1/12 16:00:28
last_day(date) 返回某月最后一天 select last_day(sysdate) from dual; 2020/11/30 16:04:30
next_day(date, int) 返回一周后的某天(int为星期几) select next_day(sysdate, 1) from dual; 2020/11/15 17:01:39

2.2.4 聚合函数

函数 解释 输入 输出
avg(column_name) 平均值
count(* 或 column_name) 求记录数量
max(column_name) 求最大值
min(column_name) 求最小值
sum(column_name) 求和

2.2 Oracle模版语句

2.2.1 查询指定时间段下的记录

字段类型为varchar2:

1
2
3
4
5
6
7
8
9
10
select time from table where to_char(to_date(time,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd')--本日
select time from table where to_char(to_date(time,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd')=to_char(sysdate-1,'yyyy-mm-dd')--昨日
select time from table where to_char(to_date(time,'yyyy-mm-dd hh24:mi:ss'),'iw')=to_char(sysdate,'iw')--本周
select time from table where to_char(to_date(time,'yyyy-mm-dd hh24:mi:ss'),'iw')=to_char(sysdate-7,'iw')--上周
select time from table where to_char(to_date(time,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm')=to_char(sysdate,'yyyy-mm') --本月
select time from table where to_char(to_date(time,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm')=to_char(add_months(sysdate,-1),'yyyy-mm') --上月
select time from table where to_char(to_date(time,'yyyy-mm-dd hh24:mi:ss'),'q')=to_char(sysdate,'q') --本季
select time from table where to_char(to_date(time,'yyyy-mm-dd hh24:mi:ss'),'q')=to_char(add_months(sysdate,-3),'q') --上季
select time from table where to_char(to_date(time,'yyyy-mm-dd hh24:mi:ss'),'yyyy')=to_char(sysdate,'yyyy') --本年
select time from table where to_char(to_date(time,'yyyy-mm-dd hh24:mi:ss'),'yyyy')=to_char(add_months(sysdate,-12),'yyyy') --去年

字段类型为date:

1
2
3
4
5
6
7
8
9
10
select time from table where to_char(time,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd')--本日
select time from table where to_char(time,'yyyy-mm-dd')=to_char(sysdate-1,'yyyy-mm-dd')--昨日
select time from table where to_char(time,'iw')=to_char(sysdate,'iw')--本周
select time from table where to_char(time,'iw')=to_char(sysdate-7,'iw')--上周
select time from table where to_char(time,'yyyy-mm')=to_char(sysdate,'yyyy-mm') --本月
select time from table where to_char(time,'yyyy-mm')=to_char(add_months(sysdate,-1),'yyyy-mm') --上月
select time from table where to_char(time,'q')=to_char(sysdate,'q') --本季
select time from table where to_char(time,'q')=to_char(add_months(sysdate,-3),'q') --上季
select time from table where to_char(time,'yyyy')=to_char(sysdate,'yyyy') --本年
select time from table where to_char(time,'yyyy')=to_char(add_months(sysdate,-12),'yyyy') --去年

2.2.2 查找用户与表的所属关系

查找某用户的所有表

1
select table_name from all_all_tables where owner = '大写用户名'

查询某用户下有某字段的所有表

1
select * from dba_tab_columns a where a.OWNER = '大写用户名' and a.COLUMN_NAME = '大写字段名';

查询某表的所属用户

1
select owner from dba_tables where table_name=upper('大写表名');

2.2.3 在查询结果中新增一列序号

1
select row_number() over (order by a.字段 asc) as number,a.* from 表名 a    

2.2.4 查找删除重复记录

查找重复记录:

1
2
3
4
1、查找全部重复记录
select * from 表 where 重复字段 in (select 重复字段 from 表 group by 重复字段 having count(*)>1)
2、查找重复记录只显示一条
select * from 表 where id in (select max(id) from 表 group by title)

删除重复记录:

1
2
3
4
1、删除全部重复记录
delete 表 where 重复字段 in (select 重复字段 from 表 group by 重复字段 having count(*)>1)
2、删除重复记录只保留一条
delete hzt where id not in (select max(id) from hzt group by title)

2.2.5 解决数据库锁表问题

查询锁表

1
2
3
select b.owner, b.object_name, a.session_id, a.locked_mode
from v$locked_object a, dba_objects b
where b.object_id = a.object_id;

查询出用以解锁锁表的alter语句

1
2
3
4
select 'alter system kill session '''||b.sid||','||b.serial#||''';' 
from v$locked_object a, v$session b
where a.session_id = b.sid
order by b.logon_time;

然后执行查出来的用以解除锁表的alter语句

2.2.6 建立dblink跨库查询数据

用途:跨库查询数据、跨库导入导出数据

前提:两个库之间的网络是通的

方式:创建dblink

1
2
3
create  public  database link 创建的dblink名
  connect to 另一个库的用户名 identified by 另一个库的密码
  using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 另一个库的地址)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';

使用:在原有sql基础上加上@dblink名即可(在原库中查询)

另注:dblink的查询与删除

1
2
select * from dba_db_links  --查询
drop public database link 建立的dblink名 --删除

2.2.7 查询数据表的创建时间

在sys用户中dba_objects这个视图中有一个created字段,这个字段记录着表的创建时间。

1
select created from dba_objects where object_name = 'table_name'

2.2.8 查询sql语句执行记录

1
2
3
4
5
6
select t.sql_text, t.first_load_time
from v$sqlarea t
where 1 = 1
and t.first_load_time like 'yyyy-MM-dd%' --筛选某时间
and t.parsing_schema_name in ('大写用户名') --筛选某用户
order by t.first_load_time desc

2.2.9 使用merge语句批量增删改

适用范围:在Oracle数据库中可用,在MySQL中不可用,适用于数据量比较大的情况。

使用场景:基于源表对目标表做INSERT,UPDATE,DELETE操作。

基本语法:

1
2
3
4
5
6
7
MERGE INTO target_table
USING source_table
ON condition
WHEN MATCHED THEN
operation
WHEN NOT MATCHED THEN
operation;

说明:

  • 最后语句分号不可以省略,且源表既可以是一个表也可以是一个子查询语句。
  • 在MERGE MATCHED 操作中,只能允许执行UPDATE 或者DELETE 语句。 在MERGE NOT MATCHED 操作中,只允许执行INSERT 语句

应用实例:

1
2
3
4
5
6
7
merge into oe.promotions_backup a
using oe.promotions b
on (a.promo_id = b.promo_id)
when matched then
update set a.promo_name = b.promo_name
when not matched then
insert (promo_id, promo_name) values (b.promo_id, b.promo_name);

2.2.10 比较两数据表的差异

[1] 比较表结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
(select column_name  
from user_tab_columns
where table_name = 'T_A'
minus
select column_name
from user_tab_columns
where table_name = 'T_B')
union
(select column_name
from user_tab_columns
where table_name = 'T_B'
minus
select column_name
from user_tab_columns
where table_name = 'T_A');

[2] 比较表数据

1
2
3
(select * from T_A  minus select * from T_B)  
union
(select * from T_B minus select * from T_A)

2.2.11 与序列有关的查询

[1] 查询当前用户下已创建的序列

1
select * from user_sequences;

[2] 查询所有序列及其所属的用户

1
select sequence_owner,sequence_name from dba_sequences

[3] 查询序列的下一个值

1
select 用户名.序列名.nextval from dual;

[4] 删除序列

1
drop sequence 用户名.序列名

注:在生成编号后,序列对象与其生成的编号之间没有延续关系,因此可以删除序列对象,即使生成的编号仍在使用。

2.2.12 查询数据库版本

1
select * from v$version;

3. Oracle常见问题及SQL性能优化

3.1 Oracle常见问题

3.1.1 时间戳转日期

解决方案:直接对时间戳字段+0即可

举例:select systimestamp + 0 from dual

3.1.2 不存在的 LOB值 问题

报错原因:执行sql时报错”不存在的 LOB 值”,原因为wm_concat查询出的是LOB类型,而oralce的SQL语句中若查询了LOB字段是不能使用distinct,union,和group by等关键字的。

解决方案:将sql脚本中的to_char(wm_concat(字段))换成listagg(字段, ',') within group(order by 字段) 即可

举例:将to_char(wm_concat(distinct b.bmdbh)) yxclbh替换成listagg(b.bmdbh, ',') within group (order by b.bmdbh) yxclbh

3.1.3 批量插入数据

用sql把要要插入的字段查出来(不同的查出来,相同的写死),insert进去。

1
2
insert into 表名 (字段1, 字段2, 字段3)
select 字段1, 字段2, 字段3 from ...

3.1.4 除数为0的解决办法

利用decode函数,当b = 0时,返回0,否则才返回a/b的结果。

1
select decode(b,0,0,a/b) from dual;

3.1.5 直接查询clob格式的数据

可以通过to_char()将clob转换成字符串直接查询出来,诸如:select to_char(column_name) from table

如果报错 “缓冲区对于CLOB到CHAR转换或BLOB到RAW转换而言太小(实际:xxx,最大:4000)”,可以使用substr()进行截取

注:如果clob中有中文会占两个字符,所以可以除以2进行截取,诸如:select to_char(substr(column_name,0,2000)) from table

3.1.6 清理数据库内存的只需计划

需求情景:当遇到sql在plsql执行快,但是在程序里执行慢的问题时。

执行命令:alter system flush shared_pool

注意事项:测试库可以随便搞,生产环境下必须在晚上没有业务的时候执行。

3.1.7 单字段去重,然后再加列序号

用group by来实现,distinct弄不了,示例如下:

1
2
select row_number() over(order by a.列名 asc) as value,a.列名 as name
from (select 列名 from 表名 group by 列名) a

3.1.8 将表的一列数据赋值给另一列

1
update table set column_name1=column_name2

3.1.9 拼接单引号

单引号'在sql中存在转义问题,应使用chr(39)进行拼接。

替换单引号–需要将a,b 替换为 ‘a’,’b’ 可以这么写:

1
select chr(39) || REPLACE('a,b',',',chr(39) || ',' || chr(39)) || chr(39) from dual

3.2 Oracle语句对比

3.2.1 drop、truncate、delete的对比

truncate和delete只删除表数据(truncate比delete删除快),而drop则删除整个表结构和数据

delete语句为DML,drop和truncate为DLL,delete能回滚而drop和truncate不能回滚

3.2.2 in与exists的对比

1
2
select * from A where id in (select id from B);    --适用于A>>B
select * from A where exists (select 1 from B where A.id=B.id); --适用于B>>A

以上两种语句功能相同,in是在内存里遍历比较,而exists需要查询数据库。

所以当B表数据量较大时,exists效率优于in;反之A表数据量较大时,in优于exists。

3.2.3 select 1 与select * 的对比

select 1的查询结果全是1,它的效率优于select *,如果你只是为了判断一定条件下是否有数据(如判断是否存在),则选用select 1的写法,一般与exists语句搭配使用

3.2.4 case … when … then … else … end与decode的对比

case … when … then … else … end (适用于各种判断情形)

1
select case when t.a > 1 then '成功' else '失败' end '其他' from table t;   --可以有多组when...then

decode(只适用于等于判断情形)

1
select decode(to_char(sysdate,'mm'),'01','1月','02','2月','03','3月','04','4月','5-12月') from dual;

注:case … when … then … else … end语句的结果如果要起别名应放在end之后(as xxx)

3.2.5 外连接以及(+)号用法

左外连接:left join是以左表的记录为基础的,示例中t_A可以看成左表,t_B可以看成右表,它的结果集是t_A表中的全部数据,再加上t_A表和t_B表匹配后的数据。换句话说,左表(t_A)的记录将会全部表示出来,而右表t_B只会显示符合搜索条件的记录。

用(+)来实现: 这个+号可以这样来理解: + 表示补充,即哪个表有加号,这个表就是匹配表。如果加号写在右表,左表就是全部显示,所以是左连接。

1
2
select * from t_A a left join t_B b on a.id = b.id;
select * from t_A a,t_B b where a.id=b.id(+);

右外连接:它和left join的结果刚好相反,是以右表t_B为基础的。它的结果集是t_B表所有记录,再加上t_A和t_B匹配后的数据。

1
2
select * from t_A a right join t_B b on a.id = b.id;
select * from t_A a,t_B b where a.id(+)=b.id;

全外连接:full join左表和右表都不做限制,所有的记录都显示, 全外连接不支持(+)写法。

1
select * from t_A a full join t_B b on a.id = b.id;

注:(+)写法不能和or同用,但left join on可以

3.2.6 union与union all的对比

union与union all的作用都是将多个结果合并在一起显示出来,区别如下:

  • union会自动压缩多个结果集合中的重复结果。
  • union all则将所有的结果全部显示出来,不管是不是重复。

3.2.7 delete from 与 delete的对比

二者作用完全相同,delete from的写法更规范一些。

3.2.8 for update 和select t.*,t.rowid编辑数据的对比

for update是加锁查询,如果未及时commmit,会导致锁表。

select t.*,t.rowid table 是不加锁查询,不会锁表。

3.3 SQL性能优化

3.3.1 关于全表扫描的注意点

[1] != 与 <>

=和<>都是不等于的意思,应尽量避免在 where 子句中使用 != 或 <> 操作符,否则会放弃使用索引而进行全表扫描。

[2] like的‘%%’ 与 ‘%’

like '%a%'将导致全表扫描,like '%a'可以用到索引,但最好是使用instr关键词进行替换查询。

[3] where子句

尽量避免在 where 子句中对字段进行表达式操作或函数操作,否则系统将可能无法正确使用索引。

[4] select *

代码里任何时候都不要使用 select * from table ,用具体的字段列表替换*,不要返回用不到的字段,避免全盘扫描。

3.3.2 关键字替换查询

[1] in与exists

根据A和B表数据量的大小来选择合适的关键字,如果数据量数量级相近则用哪个都可以。

1
2
select * from A where id in (select id from B);                    --适用于A>>B
select * from A where exists (select 1 from B where A.id = B.id); --适用于B>>A

注:in里面不要再关联数据量特别大的主表了。

[2] like与instr

like的写法都是低效的,统统可以用类似于instr(code, 'test') > 0的语句进行替换。

[3] or与union、in

or的写法都是低效的,统统可以用union、in等关键字进行替换。

[4] union与union all

当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候,尽量使用union all而不是union。

4. Oracle数据导入导出及备份

4.1 使用命令以dmp的形式导入导出

4.1.1 导入导出一个用户下的所有表

1
2
3
exp 用户名/密码@localhost:1521/orcl file=E:文件名.dmp log=用户名.log owner=(用户名)  --导出表(带数据)
exp 用户名/密码@localhost:1521/orcl file=E:文件名.dmp log=用户名.log owner=(用户名) rows=n --导出表结构(不带数据)
imp 用户名/密码@localhost:1521/orcl file=E:文件名.dmp log=用户名.log fromuser=(用户名) touser=(用户名) --导入

4.1.2 导入导出单个表

1
2
3
exp 用户名/密码@localhost:1521/orcl file=E:文件名.dmp log=用户名.log tables=(用户名.表名) --导出表(带数据)
exp 用户名/密码@localhost:1521/orcl file=E:文件名.dmp log=用户名.log tables=(用户名.表名) rows=n--导出表结构(不带数据)
imp 用户名/密码@localhost:1521/orcl file=E:文件名.dmp log=用户名.log fromuser=(用户名) tables=(表名)

注:导入导出多个表的时候,用逗号分隔即可

4.1.3 导入导出可能会遇到的问题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
[1] 账户被锁问题
sqlplus /nolog # 不以任何用户登录(只是打开登录窗口)
connect as sysdba # 然后需要输入待解锁的用户名和密码
alter user 用户名 account unlock; # 账户解锁
alter user 用户名 identified by 新密码; # 账户修改密码

[2] 对表进行授权
grant create session to 用户名;
grant create table to 用户名;

[3]对表空间无权限
alter user 用户名 quota unlimited on 表空间名

[4] “只有DBA才能导入由其他DBA导出的文件”的问题
grant dba to 用户名

[5] “XXX表空间不存在”的问题
需要创建表空间
create tablespace 表空间名
datafile 'Oracle安装路径/oradata/orcl/表空间名' --app目录的子目录
size 1M autoextend on next 50M maxsize unlimited;

4.1.4 服务器上数据库定时备份

写个shell脚本,用 crontab 定时执行即可,可参照我的另一篇博客:VPS基本部署环境的搭建与配置 (6.1.6 数据库的定时备份)

4.2 以Excel的形式导入导出

可以借助PLSQL、Navicat等工具,以Navicat为例:

1)往数据库里导入Excel文件的数据

右键数据表——导入向导——选择Excel文件,之后按照提示操作便可,注意Excel里的列标题和Oracle的字段要对应起来。

2)从数据库里导出Excel文件的数据

右键数据表——导出向导——选择导出选择Excel,之后按照提示操作便可。

4.3 使用临时表备份旧表数据

需求情景:[1] 备份数据,用于恢复 [2] 备份数据,修改表结构 [3] 转移数据

4.3.1 创建新表保存旧表数据

1
create table hr.runtime_summary_backup as select * from hr.runtime_summary

4.3.2 使用已存在的表保存旧表数据

1
insert into hr.runtime_summary_backup select * from hr.runtime_summary

4.4 两个数据库进行数据同步

Step1:创建dblink

1
2
3
create  public  database link 创建的dblink名
  connect to 另一个库的用户名 identified by 另一个库的密码
  using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 另一个库的地址)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';

Step2:检查两个库的表是否一致(不一致可借助文本比较工具找出差异的表)

1
2
select table_name from all_all_tables where owner = '大写用户名' order by table_name
select table_name from [email protected]创建的dblink名 where owner = '大写用户名' order by table_name

Step3:删除原库数据(也就是删除一个用户下的所有表)

1
2
3
select 'truncate table 用户名.' || t.table_name
from (select table_name from all_all_tables where owner = '大写用户名') t
执行跑出来的sql语句

Step4:从另一个库里迁移数据

1
2
3
select 'insert into 用户名.'|| t.table_name || ' select * from 用户名.' || t.table_name || '@创建的dblink名'
from (select table_name from all_all_tables where owner = '大写用户名') t
执行跑出来的sql语句

4.5 在当前用户下备份所有表的数据

1
2
3
4
select 'create table bsms.' || table_name ||
'_backup as select * from 用户名.' || table_name || ';' backup
from (select table_name from all_all_tables where owner = '大写用户名')
执行跑出来的sql语句

5. MySQL与Oracle的不同之处

5.1 二者语法差异

[1] MySQL的外连接不支持(+)的写法,应使用left join等原生写法。

[2] MySQL不支持to_char和to_date函数,应使用date_format函数转换日期与字符串。

1
2
3
4
把字符串转为日期格式
select date_format('2011-09-20 08:30:45', '%Y-%m-%d %H:%i:%S');
把日期转为字符串格式
select date_format(now(),'%Y-%m-%d %H:%i:%S');

[3] MySQL拼接字符串应使用concat(str1,str2), 数字与字符串的拼接用 || 的写法会出问题。

[4] MySQL字符串转数字,在字符串的末尾加0即可(如:’100’ + 0)

注:直接用数字字符串比较数值会有问题(比如price<=’10000’,过滤出的只有10000),需要转换。

[5] MySQL不支持decode的写法,用case…when…then…else…end来替代。

1
CASE WHEN total = '0' THEN '暂无数据' ELSE total END total,

[6] MySQL不支持listagg函数进行逗号分隔查询,可以使用group_concar()来实现。

1
select `code`,group_concat(`name` separator ',') from `sys_code` group by `code`

[7] 分页查询。MySQL是直接在SQL语句中写”select… from …where…limit x, y”就可以实现分页;而Oracle则是需要用到伪列ROWNUM和嵌套查询。

5.2 MySQL模板语句

[1] 逗号分割复制一个表中的全部字段

1
select group_concat(COLUMN_NAME) from information_schema.COLUMNS where table_name = '表名';

[2] 对表中的某个时间字段加去或减少一段时间

为日期增加一个时间间隔:date_add();为日期减少一个时间间隔:date_sub(),时间间隔选项如下:

1
2
1 microsecond 1毫秒、1 second 1秒、1 minute 1分钟、1 hour 1小时
1 day 1天、1 week 1周、1 month 1月、1 quarter 1季、1 year 1年

完整示例:对table_name表的time字段加3天

1
update table_name a set a.time = date_add(a.time, interval 3 day)

5.3 MySQL常见问题

[1] MySQL执行包含union的SQL语句报错:Illegal mix of collations for operation UNION

原因:union连接的表排序规则不一致

解决:保证表和字段的编码规则、排序规则一致即可

6. 参考资料

[1] sql中drop、truncate和delete的区别 from 博客园

[2] SQL查询中in和exists的区别分析 from 简书

[3] 数据库-Oracle条件判断语句 from 开源中国

[4] Oracle查询今天、昨天、本周、上周、本月、上月数据 from CSDN

[5] Oracle 查询当天、当周、当月和当季度的所有记录 from CSDN

[6] sql select * ,select 1, select count(*) ,select count(1)的区别 from CSDN

[7] Oracle PL\SQL操作(四)索引与约束 from go4it

[8] Oracle左连接、右连接、全外连接以及(+)号用法 from 何海洋

[9] MySQL之——查询重复记录、删除重复记录方法大全 from CSDN

[10] ORACLE系统视图 from 博客园

[11] ora-01950:对表空间XXX无权限 from CSDN

[12] 数据库中一张表可以有多少个主键?from CSDN

[13] date和timestamp相互转换 from Hider1214

[14] ORA-22922: 不存在的 LOB 值解决方法 from 百度经验

[15] Oracle对表空间 USERS 无权限问题 from CSDN

[16] 只有dba才能导入由其他dba导出的文件的解决方案 from 博客园

[17] 使用plsql创建表空间和用户 from CSDN

[18] for update 和select t.*,t.rowid编辑数据的区别 from CSDN

[19] Oracle中除数为0的解决办法 from CSDN

[20] ORA-22835 缓冲区对于 CLOB 到 CHAR 转换或 BLOB 到 RAW 转换而言太小 from 博客园

[21] oracle 创建新表,并复制旧表数据 from CSDN

[22] oracle 查看 、创建、删除 dblink from CSDN

[23] Oracle中查看某个表的创建时间 from CSDN

[24] 查询oracle数据库操作记录 from CSDN

[25] SQL高级知识——MERGE INTO from 知乎

[26] oracle中比较两表表结构差异和数据差异的方法 from CSDN

[27] oracle中查找某个表属于哪个用户 from 百度知道

[28] Oracle单引号拼接和替换 from 博客园

[29] mysql DATE_FORMAT函数用法 from 博客园

[30] MySQL中实现Oracle listagg函数 from CSDN

[31] sql注入时union出错(Illegal mix of collations for operation UNION)from CSDN

[32] 数据库怎么复制一个表中的全部字段/数据库复制一张表的全部字段(带逗号)的方法 from CSDN

[33] Mysql 更新时间(加上或者减去一段时间)from CSDN