Oracle

之前整理的Oracle笔记,作为一个后端开发,不懂Oracle是不能忍的(虽然我还只是只ROOKIE,2333333333333333)。

常见关键字

create 创建 alter 修改 drop 扔掉 truncate 截断

insert 新建 delete删除 update 修改 query 查询 grant 授权

revoke 撤销授权 commit 提交 rollback 回滚 savepoint 保存还原点

基本语句

解锁/加锁账户:

alter user username account unlock;

alter user username account lock;

修改密码:

alter user username identified by password;

创建账户:

create user username identified by password;

给用户授权/撤销授权:

grant dba to username;

revoke dba from username;

删除用户:

drop user username;

切换用户:

conn username/password;

查看当前用户:

show user;

查看表结构

desc 表名;

修改创建的表名

alter table 原表名 rename to 想更改表名;

建表之后增加字段

alter table 表名 add 字段名 字段类型;

建表之后修改字段名

alter table 表名 rename column 字段名 to 想要更改表名;

建表之后修改字段数据类型

alter table 表名 modify 字段名 想要的数据类型;

建表之后删除字段

alter table 表名 drop column 想删除字段;

删除表

drop table 表名 purge;

插入数据

insert into 表名() values();

修改数据

update student set name = ‘xiaogang’;

删除数据

delete from student where name = ‘xiaoxin’;

查询数据

select * from student;

基本符号

like:

%:代表任意位的任意字符

_:代表一位上任意字符

escape逃离符:

指定一个字符逃离,保证like之后的字符串中出现的字符看作是普通字符。

Ex:select * from student where name like ‘xiao,_%’ escape ‘,’;

运算符:

多行函数:聚组函数:组函数

count()记数sum()求和avg()平均 max()最大min()最小

单行函数:

ceil():返回大于等于x的最小整数

floor():返回小于等于x的最大整数

round():四舍五入

round(a1,a2):保留指定位小数位的四舍五入

Ex:select round(3.1415926,3) from dual; //返回3.142

trunc():直接截断

trunc(a1,a2):保留制定位数的小数

sign():求符号位 正数1 负数-1 零返回0

abs():且绝对值

power(a,b):求a的b次方

sqrt():求正平方根

转换函数

to_number(c):将一个字符类型的数字变成数值类型

to_char():将数值类型转换为字符串

  1. 将数字转换为字符串

    select * from student where ‘456.12’ = to_char(salary);

  2. 常用于在货币单位,格式化字符串

    select to_char(123123123123.00,’999,999,999,999,999.00’) from dual;

  3. 日期转换

    to_char(日期,‘yyyy-MM-dd hh24:mi:ss’)

    JAVA: yyyy-MM-dd HH:mi:ss

    Oracle:yyyy-mm-dd hh24:mi:ss:xff ff3 毫秒数保留三位

to_date(c1,c2)

c1:字符类型的日期

c2:格式

取出年

select to_char(to_date(‘2018-04-04’,‘yyyy-mm-dd’),’yyyy’) from dual;

取出月 ‘yyyy’换成‘month’

取出日 ‘yyyy’ 换成‘dd’

日期函数:

日期可以加减(整数)运算 单位是:天 相差结果是多少天

两个日子没法相加

yyyy 年 year 年 mm 月 month 带”月”的月份 ddd 年中的天 dd 月中的天

d 周中的天 hh 12小时制 hh24 24小时制 mi 分钟 ss 秒 xff 毫秒

add_months(c1,c2) 在某个日子上增加多少个月

c1:日期

c2:整数值

select add_months(sysdate,-1) from dual;

months_between(c1,c2) 计算两个日期之间的月份

计算方式:c1- c2

last_day():计算给定日期的所在月份的最后一天

next_day(c1,c2)

c1:日期

c2:周中的某天

字符函数

lower():转换成小写

upper():转换成大写

initcap():首字母大写

length():求长度

substr(c1,c2,c3) 截取字符串

c1:被截取的字符串

c2:从哪个位置开始截取

c3:截取长度 默认截取到最后

instr(c1,c2,c3,c4) 索引字符串

c1:被查询的字符串

c2:希望找到的字符

c3:从哪个位置开始找 默认为1

c4:第几次出现

select instr(‘woshizhizhuxia’,’z’,1,2) from dual;

concat(c1,c2) 拼接字符串 也可以用 || 拼接字符串

Ipad(c1,c2,c3) 左侧补全

rpad(c1,c2,c3)右侧补全

c1:希望补全的字符串

c2:补全到多少位

c3:以哪个字符来补全

trim(c1) 默认c1两侧去除空格

trim(c1 from c2) 把c2的两侧溢出指定的c1

ltrim(c1,c2) 左侧去除

rtrim(c1,c2)右侧去除

c1:被去除的字符串

c2:去除的字符串 默认是空格

replace(c1,c2,c3) 完全替换

c1:原字符串

c2:被替换的字符串

c3:替换的字符串

通用函数

nvl():空值处理

nvl(字段,替换显示的内容)

nvl2():空值处理二代

nvl2(字段,不是空显示什么,是空显示什么)

decode(c1,c2,c3,c4,c5,….Cx,Cx+1)

c1:原来拿来判断的值

从第二个参数开始,每两个参数看作是一组,拿每一组的第一个参数和c1进行比较

如果相同则返回该组的第二个参数

第一次判断:c2==c1?c3:

第二次判断:c4==c1?c5:

如果参数个数是奇数个,并且最终判断没有相同的数据,则返回空

如果参数个数是偶数个,并且最终判断没有相同的数据,则返回最后一个参数的值

条件取值语句:

,(case email –开始条件取值

when ‘3’ –当条件成立

then salary+200 –则

when ‘7’ then salary-200

else salary+500 –默认值

end) –结束

排序 order by

排序字段:desc 代表降序 asc 代表升序

order by 后面可以加多个字段

order by 字段1 desc,字段2 asc

分组 group by(前面不能用where 可以在后面用having)

根据某一个或多个列上的值,将该列上相同的值所在的记录划分为一个组,这样一张表就可以被划分为多个组,如

果以字段A分组,那么只能直接查询A。

或者用组函数统计其他字段,不能直接查询其他字段。

约束

主键约束:primary key

  1. 可在表中唯一确定数据的列,主键列应为非空且唯一。
  2. 一张表只能有一个主键

外键约束:foreign key 也可以写references

在子表中,若一个列上的值引用了母表中主键上的值,这个列便是外键。

非空:not null

唯一:unique

检查:check

  • 如何添加约束

    • 在创建表时直接添加约束

    • 1
      2
      3
      4
      5
      6
      7
      8
      alter table 表名 add constraint 约束名 约束;
      Ex:
      alter table test add constraint a primary key(id);
      除not null(有些特殊)
      alter table test modify 列名 constraint 约束名 not null;
      Ex:
      alter table test modify salary constraint a not null;
  • 如何删除约束

    1
    alter table 表名 drop constraint 约束名;

多表查询

内连接:inner join on 可简写为 join on

展示出两张表中有对应关系的数据,可以理解为取交集;

外连接

左外连接:left join on

展示左表中的全部数据和右表中与左表有对应关系的数据;

Ex:select * from student s left join class c on s.sid = c.id;

右外连接;right join on

展示右表中的全部数据与左表中与右表有对应关系的数据;

Ex:select * from student s right join class c on s.sid = c.id;

全连接:full join on

显示两张表中有对应关系的数据,无对应关系的也展示。

交叉连接(笛卡尔积):cross join

显示两张表中的数据一一交叉对应。

什么时候用多表查询?什么时候用子表查询?

如果查询数据在多个表内,要使用多表连接。

如果不使用子表的数据,只需要使用子表来当条件,需使用子表查询。

子查询中使用了In some any all 这几个关键字,效率低,可以使用多表关联的方式。

rownum

rownum是一个伪列,也就是说不是真是存在的,它是随着结果集生成的。

rownum都是从1开始的。

当我们输入:select * from 表 where rownum >5;不会匹配出任何数据,因为oracle只能确定第一个是哪个,

其他的它并不知道从那开始,认定为false;

想要实现 rownum > n

rownum 本身是不行的,但是可以先查出一个记过集合,既子查询。

* 实现rownum > n 时注意问题

  1. rownum 不能与order by 同一查询语句中出现。(但在子查询中可使用order by)
  2. 不能使用 表名.rownum
  3. 若rownum在where之后,只能写rownum > 0 || rownum < 任意数 || rownum >= 1

Ex:

1
2
3
4
5
6
7
8
9
10
select * from
(
select rownum rn,e.* from
(
select * from student order by salary
)e
where rownum < 9
//这里已经将取出的结果集再次排序
)
where rn > 5;

分页时确定当页从哪开始

开始数:int startcount = (curpage -1 ) * pagesize + 1;

结束数:int endcount = curpage * pagesize;

rowid

是映射每一行物理地址的唯一标识,通常用于删除完全重复的数据。

1
2
//删除重复的数据
delete from table_name where rowid not in (select min(rowid) from table_name);

联合关键字: union

union:

select * from school where name like ‘%大’

union

select * from school where name like ‘北%’;

若第一条语句中包含第二句中查询的内容,则只展示第一条语句查询的内容。

union all

若第一条语句中包含第二句中查询的内容,都展示出来。

intersect 求交集

只展示两条语句查询的内容重复部分。

minus 从第一个查询结果中减去第二个结果中重复出现的

in(效率低,用exists代替) not in

表示条件符合查询结果中某一个值就成立。

Ex:select * from school where id in (select sid from stduent);

select * from school where id not in (select sid from stduent);

some/any

用法与In相同,In用于无符号,some/any 用与有符号。

all 表示所有的值都大或都小(min,max代表第二条语句中的最大值最小值)

>any == >min

<any == <max

>all == >max

1
2
3
4
CREATE [OR REPLACE][FORCE][NOFORCE] VIEW view_name
AS SELECT_STATEMENT
[WITH CHECK OPTION[CONSTRAINT constraint_name]]
[WITH READ ONLY]

REPLACE: 如果创建视图时,已经存在此视图,则重新创建此视图,相当于覆盖。

FORCE:强制创建视图,不论视图依赖的基表是否存在或者是否有权限创建。

NOFORCE:只有在基表存在且拥有创建权限才创建视图。

WITH CHECK OPTION :指出在视图上进行修改要符合SELECT_STATEMENT锁指定的限定条件。

WITH READ ONLY:默认可以通过对基表进行增删改查操作,WITH READ ONLY只允许查看视图,现实开发中并不会通过视图对基表进行增删改操作。

  • 视图的定义原则

    • 视图的查询可以使用复杂的 SELECT 语法,包括 连接/分组 和 子查询。
    • 没有 WITH CHECK OPTION 和 READ ONLY,查询中不能使用 ORDER BY 子句。
    • OR REPLACE:不删除原视图便可更改其定义并重建,并重新赋予对象权限。
  • 删除视图

    • 删除视图的定义并不会影响基表的数据。
    • 只有视图所有者或者拥有DROP VIEW权限的用户可以删除视图。
    • 视图删除后,基于视图创建的其他视图将无效。
    1
    DROP VIEW view_name;
  • 视图分类

    • 简单视图
      • 只从单表获取数据。
      • 不包含函数和数据组。
      • 可以进行DML操作(不建议)。
    • 复杂视图
      • 从多个表里获取数据。
      • 包含函数和数据组。

小结

视图适用于查询数据。

不要通过视图更改基表数据!

不要通过视图更改基表数据!

不要通过视图更改基表数据!

重要事情说三遍。

Tablespace

概念

表空间:是一个或多个数据文件的逻辑集合。

表空间逻辑存储对象:

​ 永久段: 如表,索引,存储过程,视图。

​ 临时段:如临时表或排序段

​ 回滚段(UNDO):主要用于事物回滚,恢复,撤销数据

逻辑结构:

block –>extent –>segment–>tablespace–>database

创建表空间:

1
2
3
4
5
CREATE TABLESPACE tablespace_name nologging
DATAFILE '' --文件存在位置
SIZE 100M --表空间初始大小
AUTOEXTEND ON TEXT --下一次扩展大小
MAXSIZE --最大值

在创建时给用户指定表空间

1
CREATE USER user_name IDENTIFY BY password DEFAULT TABLESPACE tablespace_name;

更改用户表空间

1
ALTER USER user_name DEFAULT TABLESPACE tablespace_name;

删除表空间(删除表空间后,需要为原指向此表空间的用户重新指定表空间)

1
DROP TABLESPACE tablespace_name [INCLUDING ONTENTS AND DATAFILES];

Index

索引类型:

  1. 普通索引

    1
    CREATE INDEX index_name on table_name(字段)
  2. 唯一索引 unique

    1
    CREATE UNIQUE INDEX index_name on table_name(字段)
  3. 位图索引 bitmap //这种索引适合用在数据量比较大,基数比较小的列 Ex:男/女

    1
    CREATE BITMAP INDEX index_name on table_name(字段)
  4. 函数索引

    1
    CREATE INDEX index_name on table_name(函数(列名))

操作索引

  1. 重命名索引

    1
    ALTER INDEX index_name rename to new_name;
  2. 查看索引

    1
    SELECT index_name,index_type,tablespace_name,uniqueness from all_indexes where table_name = 'tablename';
  3. 删除索引

    1
    DROP INDEX index_name;

合理使用索引

创建唯一性索引,保证数据库表中每一行数据的唯一性。

大大加快数据的检索速度。

加速表与表之间的连接,特别是在实现数据的参考完整性方面特别有意义。

与此同时

创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加

索引需要占用物理空间,除了数据表占据空间之外,每一个索引都要占据一定的物理空间。

当对表中的数据进行增加,删除和修改的时候,索引也要动态的维护,降低了数据的维护速度。

存储过程 procedure

所谓存储过程就是在服务器端,能够被一个或多个应用程序调用的一段sql语句集。

创建存储过程:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE OR REPLACE PROCEDURE
过程名(参数名 in 参数类型,参数名 in 参数类型,参数名 out 参数类型,参数名 out 参数类型)
AS
变量名 变量类型:=值;
begin
sql语句集
end;
Ex:
create or replace procedure
pro_hi(key in number,value out varchar)
as
begin
if key=1
then value = '你好'
if key=2
then value = '再见'
end if;
end if;
end;
/

存储过程可以没有参数,如果没有参数则过程名之后不能出现括号。

存储过程可以有参数,可有多个参数传入或传出,也可只有传入或传出。

传入参数用in标明,传出参数用out表明。

存储过程并没有返回值,而是通过返回参数来返回数据的。

调用存储过程:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
declare
变量 类型:=初始值;
begin
过程名(参数,变量);
end;
Ex:
declare
val varchar2(20):='';
begin
pro_hi(1,val);
dbms_output.put_line(val);
pro_hi(2,val);
dbms_output.put_line(val);
end;

触发器 trigger

触发器,当某个事件发生时自动的隐式运行。

for each row 行级触发器

创建触发器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
create or replace trigger trigger_name
before/after insert/update/delete on table_name
for each row
begin
sql语句集;
end;
Ex:
--创建表
create table good(
id int primary key,
good_name varchar2(20)
);
--创建序列
create sequence seq_ids;
--创建触发器
create or replace trigger tri_insert_good
before insert on good
for each row
begin
select seq_ids.nextval
into:new.id
from dual; --从序列中生成一个新的数值,赋值给当前插入行的ID列
end;
--增加数据,触发器
insert into good(good_name)values('苹果');
--创建日志表
create table test_log(
name varchar2(20),
time date
);
--创建触发器
create or replace trigger test_dept
before delete or insert or update on dept
declare var_tag varchar2(20);--声明一个变量,存储对dept表执行的操作类型
begin
if inserting then
var_tag :='insert';
elsif updating then
var_tag :='update';
elsif deleting then
var_tag :='delete';
end if;
insert into test_log values(var_tag,sysdate);--向日志表中插入对dept表的操作信息
end;
--分别执行语句对dept表进行操作
insert into dept values(66,'教育部','济南');
update dept set loc='易途' where deptno = 66;
delete from dept where deptno=66;
select * from dept;
select * from test_log;

SQL优化

  1. 建议不要用*代替所有列名,需要什么查什么。
  2. 建议使用truncate代替delete(truncate在删除数据上比delete效率高)。
  3. 在确保语句的完整性的前提下尽量使用commit。
  4. 用exists代替in。
  5. SQL语句写的时候尽量用大写,节省编译时间。
  6. SQL语句中给表使用别名,减少解析时间。
  7. 合理使用索引。
  8. 表连接在where条件之前,数据多的纪录在where字句的末尾,表连接之前过滤掉的数据越多越好。
  9. 优化group by,将不需要的纪录在group by之前过滤掉。
  10. 尽量减少表的查询次数。

查询低效率执行的SQL

1
2
3
4
5
6
7
8
9
SELECT EXECUTIONS,DISK_READS,BUFFER_GETS,
ROUND((BUFFER_GETSDISK_READS)/BUFFER_GETS,2)HIT_RADIO,
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS > 0 AND BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC;

常见语句关键字优先级

from –优先级最高

where –优先级比from低

group by –优先级比where低

having – 优先级在group by之后

select –优先级高于order by

order by –优先级最低

  • 本文作者: Kit.Andrew.Lee
  • 本文链接: 2018/04/12/Oracle/
  • 版权声明: 本博客所有文章除特别声明外,均采用 CC BY-NC-SA 3.0 CN 许可协议。转载请注明出处!