Oracle基础教程文档~超级全

Oracle文档

基础知识

关于数据库语言的分类

DDL：数据库定义语言：create、drop
DML：数据库的操作语言：insert、update、delete
DQL：数据库的查询语言：select
DCL：数据库的控制语言：grant、revoke

数据类型

VARCHAR2(size)

可变长字符数据。VARCHAR2(n)数据类型用于定义可变长度的字符串，其中，n用于指定字符串的最大长度，n的值必须是正整数且不超过32767。

CHAR(size)

定长字符数据。CHAR(n)数据类型用于定义固定长度的字符串，其中，n用于指定字符串的最大长度，n的值必须是正整数且不超过32767。

NUMBER(p,s)

可变长数值数据。NUMBER(precision,scale)数据类型用于定义固定长度的整数和浮点数，其中，precision表示精度，用于指定数字的总位数；scale表示标度，用于指定小数点后的数字位数，默认值为0，即没有小数位数。

DATE

日期型数据。DATE数据类型用于定义日期时间类型的数据，分别描述年、月、日、时、分、秒。

TIMESTAMP

TIMESTAMP数据类型也用于定义日期时间数据，但与DATE仅显示日期不同，TIMESTAMP类型数据还可以显示时间和上下午标记，如“11-9月-2007 11:09:32.213 AM”。

LONG

可变长字符数据，最大可达到2G。LONG数据类型在其它的数据库系统中常被称为备注类型，它主要用于存储大量的可以在稍后返回的文本内容。

CLOB

字符数据，最大可达到4G。

BLOB

二进制数据，最大可达到4G。

ROWID

行地址，十六进制串，表示行在所在的表中唯一的行地址，该数据类型主要用于返回ROWID伪列，常用在可以将表中的每一条记录都加以唯一标识的场合。

提交回滚

在每次执行完数据变更的语句（insert\update\delete）后，需要提交数据

可以将执行数据变更的语句后跟上commit，进行提交

注意：执行变更数据（执行提交）之前，已经要检查语句，是否变更逻辑正确。

别名

as

SELECT count(1) as sjl FROM test1;

SELECT t.name FROM test1 as t;

sjl就是count(1)的别名

别名是为了方便计算和防止

通配符

‘%’(百分号)： 用来表示任意数量的字符，或者可能根本没有字符。

‘_’(下划线)： 表示确切的未知字符。

‘?’(问号)： 用来表示确切的未知字符。

‘#’(井号)： 用来表示确切的阿拉伯数字，0到9。

‘[a-d]’(方括号)：用来表示字符范围，在这里是从a到d。

用户和表空间

创建新用户

CREATE USER usename IDENTIFIED BY password;

授权

GRANT CONNECT, RESOURCE, DBA TO usename;

DBA: 拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。
RESOURCE:拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。
CONNECT:拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。

创建表空间

CREATE TABLESPACE spacename DATAFILE '表空间文件存放路径' SIZE 表空间大小 AUTOEXTEND ON;
-- 例
CREATE TABLESPACE dragon DATAFILE 'C:\Tablespace\dragon.dbf' SIZE 1000M AUTOEXTEND ON;

表和视图

创建表

CREATE

CREATE TABLE tablename(
num1 varchar2(200) null,
num2 varchar2(200) null,
num3 number(10,0) null,
num4 DATE null
)
tablespace spacename -- 指定表空间
comment on table tablename is '表注释';
comment on column tablename.num1 is '字段注释';

tablename:表名

num:字段名

create table test1 (
id varchar2(10) primary key,
name varchar2(200) not null,
phone varchar2(11) null,
adress varchar2(200) null
)

primary key:主键，主键的特性，不能重复且不为空

查询

SELECT

查询

-- 全字段显示
SELECT * FROM tablename;
SELECT num1,num2,num3,num4 FROM tablename;
-- 查询单一字段
SELECT num1 FROM tablename;
-- 查询多个字段
SELECT rowid,num1,num2 FROM tablename;

插入

INSERT INTO

插入

-- 全字段插入
INSERT INTO tablename(num1,num2,num3,num4)VALUES('10001','AT',20,'2022-04-03 00:00:00');
-- 指定字段插入
INSERT INTO tablename(num1,num2)VALUES('10001','AT');

更新

UPDATE

-- 全量更新
UPDATE tablename SET num2 = 'AG';
-- 条件更新
UPDATE tablename SET num2 = 'AG' WHERE num2 = 'AT';
UPDATE tablename SET num2 = 'AG' WHERE num1 = '10001';

FOR UPDATE

SELECT * FROM test1 FOR UPDATE;

*,rowid

SELECT t.*,t.rowid FROM test1 t;

删除表

DELETE

删除数据

-- 全量删除
-- 注1：delete只会删除数据，不会删表
-- 注2：delete删除数据可以回滚
-- 注3：如表数据量大，delete全量删除慢
DELETE FROM tablename;
-- 条件删除
DELETE FROM tablename WHERE num1 = '10001';

DROP

删除表

-- 注：无法回滚
-- 执行前请注意
DROP TABLE tablename;

TRUNCATE

清空表

-- 注:无法回滚
-- 执行速度极快
TRUNCATE TABLE tablename;

创建视图

视图(view)，也称虚表, 不占用物理空间，这个也是相对概念，因为视图本身的定义语句还是要存储在数据字典里的。视图只有逻辑定义。每次使用的时候,只是重新执行SQL。

视图是从一个或多个实际表中获得的，这些表的数据存放在数据库中。那些用于产生视图的表叫做该视图的基表。一个视图也可以从另一个视图中产生。

CREATE [OR REPLACE] VIEW v_test1 AS
select t.name,t.phone,t.adress,e.wq,e.sf,e.zx from test1 t inner join test2 e on t.name = e.name; 

语法:

create [or replace] [force] view view_name
           [(column1,column2,...)]
           as 
           select ...
           [ with read only ];

1. or replace: 如果存在同名的视图, 则使用新视图"替代"已有的视图
2. force: "强制"创建视图,不考虑基表是否存在,也不考虑是否具有使用基表的权限
3. with read only：创建的视图只能用于查询数据, 而不能用于更改数据.

视图的作用

1. 提供各种数据表现形式, 可以使用各种不同的方式将基表的数据展现在用户面前, 以便符合用户的使用习惯(主要手段: 使用别名)；
2. 隐藏数据的逻辑复杂性并简化查询语句, 多表查询语句一般是比较复杂的, 而且用户需要了解表之间的关系, 否则容易写错; 如果基于这样的查询语句创建一个视图, 用户就可以直接对这个视图进行"简单查询"而获得结果. 这样就隐藏了数据的复杂性并简化了查询语句.这也是oracle提供各种"数据字典视图"的原因之一,all_constraints就是一个含有2个子查询并连接了9个表的视图(在catalog.sql中定义)；
3. 执行某些必须使用视图的查询. 某些查询必须借助视图的帮助才能完成. 比如, 有些查询需要连接一个分组统计后的表和另一表, 这时就可以先基于分组统计的结果创建一个视图, 然后在查询中连接这个视图和另一个表就可以了；
4. 提供某些安全性保证. 不会让对方看到表结构，视图提供了一种可以控制的方式, 即可以让不同的用户看见不同的列, 而不允许访问那些敏感的列, 这样就可以保证敏感数据不被用户看见；
5. 简化用户权限的管理. 可以将视图的权限授予用户, 而不必将基表中某些列的权限授予用户, 这样就简化了用户权限的定义。

条件过滤

WHERE

指定过滤的条件返回符合查询条件的行记录

SELECT * FROM tablename where num2 = 'AB';

num1	num2
10001	AB
10001	AB

AND

并且

-- 查询无结果
SELECT * FROM tablename WHERE num2 = 'AB' AND num2 = 'AC'

SELECT * FROM tablename WHERE num2 = 'AC' AND num2 = '10002'

num1	num2
10002	AC

OR

或者

SELECT * FROM tablename WHERE num2 = 'AB' OR num2 = 'AC'

Fetch

-- 获取前10行的数据
SELECT * FROM test1 fetch next 10 rows only;
SELECT * FROM test1 fetch first 10 rows only;
SELECT * FROM test1 fetch next 10 rows with ties;
 -- 获取整表前10%的数据
SELECT * FROM test1 fetch next 10 percent rows only;

OFFSET

-- 获取10行之后的数据
SELECT *