Oracle
一、注意事项
**登录:**使用SQL Plus登录时用户名应当这样输入——用户名 as sysdba
,而不是直接使用用户名。
二、Oracle 的存储结构
Oracle数据库由一耳光数据库和至少一个数据库实例组成,它们分别用来存储数据和管理数据库文件的内存结构。
1. 物理存储结构
物理存储结构有数据文件
、控制文件
、联机重做日志文件
组成。数据文件记录了逻辑数据库结构存储的表和索引等,事实上包含着存储的真实文件。控制文件则描述了元数据库的物理结构、数据库名称、数据文件位置等数据。联机重做日志文件由重做条目组成,记录了数据库数据的修改。
##### 2.逻辑存储结构
Oracle数据库使用逻辑存储结构对磁盘空间使用情况进行精细控制。以下是Oracle数据库中的逻辑存储结构:
- 数据块(Data blocks):数据块对应于磁盘上的字节数。
- 范围(Extents):范围是用于存储特定类型信息的逻辑连续数据块的具体数量。
- 段(Segments):段是分配用于存储用户对象(例如表或索引)的一组范围。
- 表空间(Tablespaces):数据库被分成称为表空间的逻辑存储单元。 表空间是段的逻辑容器。 每个表空间至少包含一个数据文件。
三、数据库表的操作
1.表操作
数据库中表的基本创建格式如下:
CREATE TABLE schema_name.table_name (
column_1 data_type column_constraint,
column_2 data_type column_constraint,
...
table_constraint
);
可以使用
DESC table_name;
查看表的结构
类型 定义方法 注释 NUMBER NUMBER(p,s) p表示精度,s是尺度,s不填或为0时表示整数,填入数字超过尺度会进行四舍五入,s运行为负(四舍五入) FLOAT FLOAT§ FLOAT只能指定精度,最大精度是126,且FLOAT的精度为二进制(大致等于十进制38位),公式P(d) = ⌈ 0.30103 * P(d) ⌉ CHAR CHAR(length BYTE)
CHAR(length CHAR)默认是BYTE不指定长度默认值是1,不是可变长字符串,没有达到长度会使用空格填充,需要时要使用 RTRIM()
函数去除空格NCHAR NCHAR(length) 用于存储固定长度的Unicode字符数据,且不能使用字节长度(BYTE) VARCHAR2 VARCHAR2(max_size BYTE)
VARCHAR2(max_size CHAR)默认字节长度,可变长字符串 NVARCHAR2 NVARCHAR2(length) 是可以存储Unicode字符的Unicode数据类型。 NVARCHAR2
的字符集是在数据库创建时指定的国家字符集。DATE DATE Oracle数据库有其自己的专用格式来存储日期数据。它使用 7
个字节的固定长度的字段,每个字段对应于世纪,年,月,日,时,分和秒来存储日期数据TIMESTAMP TIMESTAMP[(fractional_seconds_precision)] fractional_seconds_precision
指定SECOND
字段小数部分的位数。它的范围从0
到9
,这意味着可以使用TIMESTAMP
数据类型来存储到纳秒的精度。 如果省略fractional_seconds_precision
,则默认为6
。INTERVAL INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND用于存储一段时间 DATE
输入和输出的标准日期格式是
DD-MON-YY
,那么可以使用ALTER SESSION
语句来更改NLS_DATE_FORMAT
参数的值,如下所示:ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
要转换不是标准格式的日期值,可以使用带有格式字符串的
TO_DATE()
函数。TO_DATE('2021-12-3', 'YYYY-MM-DD' )
除了使用
TO_DATE()
函数之外,还可以使用以下语法将日期值指定为字符串文字:DATE 'YYYY-MM-DD'
TIMESTAMP
要指定
TIMESTAMP
文字,请使用以下格式:TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF'
如果想通过复制表的形式创建表可以使用`create ··· as (select ··· ) 的方式,示例如下:
CREATE TABLE newcustomers3
AS (SELECT regularcustomers.rcustomer_id, regularcustomers.rc_city, irregularcustomers.ircustomer_name
FROM regularcustomers, irregularcustomers
WHERE regularcustomers.rcustomer_id = irregularcustomers.ircustomer_id
AND regularcustomers.rcustomer_id < 5000);
要将表移动到回收站或将其从数据库中完全删除,使用DROP TABLE
语句:
DROP TABLE schema_name.table_name
[CASCADE CONSTRAINTS | PURGE];
CASCADE CONSTRAINTS
子句删除引用表中主键和唯一键的所有参照完整性约束。 如果存在这种引用完整性约束,并且不使用此子句,Oracle将返回错误并停止删除表。
如果想删除表格并且一次释放与之关联的空间,指定
PURGE
子句。 通过使用PURGE
子句,Oracle不会将表及其依赖对象放入回收站。且PURGE
不支持回滚。
2.列操作
列的添加基本语法如下:
ALTER TABLE table_name
ADD (
column_name type constraint,
column_name type constraint,
...
);
添加表中已有列时会出错,这时候就需要先进行检测,以下展示一个在添加之前检查
members
表是否有effective_date
列 的示例:SET SERVEROUTPUT ON SIZE 1000000 //打开输出,最大输出为1000000 DECLARE v_col_exists NUMBER //声明一个Number类型的变量 BEGIN SELECT count(*) INTO v_col_exists //把查询的次数赋值给变量 FROM user_tab_cols WHERE column_name = 'EFFECTIVE_DATE' AND table_name = 'MEMBERS'; IF (v_col_exists = 0) THEN EXECUTE IMMEDIATE 'ALTER TABLE members ADD effective_date DATE'; ELSE DBMS_OUTPUT.PUT_LINE('The column effective_date already exists'); END IF; END;
对已有的列进行修改的基本语法如下:
ALTER TABLE table_name
MODIFY (
column_1 type constraint,
column_1 type constraint,
...
);
删除已有列的基本语法
ALTER TABLE table_name
DROP (column_1,column_2,...);
从大表中删除数据往往需要耗费大量资源,所以通常使用
ALTER TABLE SET UNUSED COLUMN
语句来逻辑删除列,如下所示:
ALTER TABLE table_name
SET UNUSED COLUMN column_name;
待资源空闲时可以使用以下语句删除:
ALTER TABLE table_name DROP UNUSED COLUMNS;
也可以从
DBA_UNUSED_COL_TABS
视图查看每个表中未使用的列的数量:SELECT * FROM DBA_UNUSED_COL_TABS;
重命名列的基本语法
ALTER TABLE table_name
RENAME COLUMN column_name TO new_name;
如果不填写列名就是重命名该表名
3.截断表
删除表中所有数据即为截断表,可以使用DELETE
语句删除,但数据量大时效率并不高,以下说明了Oracle TRUNCATE TABLE
语句的语法:
TRUNCATE TABLE schema_name.table_name
[CASCADE]
[[ PRESERVE | PURGE] MATERIALIZED VIEW LOG ]]
[[ DROP | REUSE]] STORAGE ]
如果表通过外键约束与其他表有关系,则需要使用
CASCADE
子句截断关联表通过
MATERIALIZED VIEW LOG
子句,可以指定在表上定义的物化视图日志是否在截断表时被保留或清除。 默认情况下,物化视图日志被保留。
STORAGE
子句允许选择删除或重新使用由截断行和关联索引(如果有的话)释放的存储。 默认情况下,存储被删除。
四、Oracle CRUD
1.有条件的Oracle INSERT ALL语句
条件多项插入语句允许根据指定的条件将行插入到表中。
以下显示了条件多项插入语句的语法:
INSERT [ ALL | FIRST ]
WHEN condition1 THEN
INTO table_1 (column_list ) VALUES (value_list)
WHEN condition2 THEN
INTO table_2(column_list ) VALUES (value_list)
ELSE
INTO table_3(column_list ) VALUES (value_list)
Subquery
SQL
如果指定了ALL
关键字,则Oracle将在WHEN
子句中评估每个条件。如果条件评估/计算为true
,则Oracle执行相应的INTO
子句。
但是,当指定FIRST
关键字时,只会执行相应的第一个为true
的INTO
子句。
所有
INSERT INTO
子句中的列总和不得超过999
。单条件多项式插入语句最多可以有
127
个WHEN
子句。
2.Oracle的级联删除
在现实应用中,经常从与其他表中的行具有外键关系的表中来删除行记录。
例如,想要从orders
表中删除id
为1
的销售订单,并从order_items
表中删除与订单id
是1
关联的所有行项目。 通常可以发出两个DELETE
语句,如下所示:
-- 第一条
DELETE FROM
orders
WHERE
order_id = 1;
-- 第二条
DELETE FROM
order_items
WHERE
order_id = 1;
COMMIT WORK;
SQL
请注意,COMMIT WORK
语句确保两个DELETE
语句以全部或全部方式执行,以防第一条语句成功,但第二个DELETE
语句失败时在order_items
表中的孤行。
但是,如果知道如何正确设置表的约束,那么这是不必要的。
在这种情况下,当创建order_items
表时,可以使用DELETE CASCADE
选项定义一个外键约束,如下所示:
CREATE TABLE order_items
(
order_id NUMBER( 12, 0 ) ,
-- other columns
-- ...
CONSTRAINT fk_order_items_orders
FOREIGN KEY( order_id )
REFERENCES orders( order_id )
ON DELETE CASCADE
);
3.Oracle MERGE
以下说明了Oracle MERGE
语句的语法:
MERGE INTO target_table
USING source_table
ON search_condition
WHEN MATCHED THEN
UPDATE SET col1 = value1, col2 = value2,...
WHERE <update_condition>
[DELETE WHERE <delete_condition>]
WHEN NOT MATCHED THEN
INSERT (col1,col2,...)
values(value1,value2,...)
WHERE <insert_condition>;
五、Oracle 查询数据
1. ORACLE ORDER BY
可以对数据进行排序,使用方法如下:
SELECT
...
FROM
table_name
ORDER BY
[column1_name | column_id] [ASC | DESC] [NULLS FIRST | NULLS LAST],
[column2_name | column_id] [ASC | DESC] [NULLS FIRST | NULLS LAST]
ASC
表示按升序排序,为默认选项,DESC
表示按降序排序
NULLS FIRST
在非NULL
值之前放置NULL
值,为默认选项,NULLS LAST
在非NULL
值之后放置NULL
值。
2. ORACLE DISTINCT
DISTINCT
子句可以用来过滤结果集中的重复行。以下说明了SELECT DISTINCT
语句的语法:
SELECT DISTINCT
column_1
FROM
table_name;
DISTINCT
将NULL
视为重复值
3. Oracle FETCH
用于进行行限制,以下说明了语法:
[ OFFSET offset ROWS]
FETCH NEXT [ row_count | percent PERCENT ] ROWS [ ONLY | WITH TIES ]
OFFSET用于跳过前几行的数据再进行接下来的行限制,即为偏移量。
可以使用
FIRST
代替NEXT
,ROW
代替ROWS
WITH TIES
返回与最后一行相同的排序键。请注意,如果使用WITH TIES
,则必须在查询中指定一个ORDER BY
子句。如果不这样做,查询将不会返回额外的行。
4.ORACLE GROUP BY
GROUP BY
子句在SELECT语句中用于按行或表达式的值将行组合到分组汇总的行中。 GROUP BY
子句为每个分组返回一行。
以下说明了Oracle GROUP BY
子句的语法:
SELECT
column_list
FROM
T
GROUP BY
c1,
c2,
c3;
GROUP BY
子句按分组列中的值(如c1
,c2
和c3
)对行进行分组。GROUP BY
子句只能包含聚合或分组的列。如果要指定应该一次计算的多个分组级别,请使用以下
ROLLUP
语法:SELECT column_list FROM T GROUP BY ROLLUP(c1,c2,c3);
5.ORACLE HAVING
以下说明了OracleHAVING
子句的语法:
SELECT
column_list
FROM
T
GROUP BY
c1
HAVING
group_condition;
SQL
在这个语句中,HAVING
子句紧跟在GROUP BY
子句之后。如果使用不带GROUP BY
子句的HAVING
子句,则HAVING
子句将像WHERE
子句那样工作。
HAVING
子句过滤分组的行,而WHERE
子句过滤行。这是HAVING
和WHERE
子句之间的主要区别。
六、Oracle 连接表
##### 1.内连接、左连接、右连接、全连接
基本语法:
SELECT
column_list
FROM
T1
[ INNER | LEFT | RIGHT | OUTER ] JOIN T2 ON
join_predicate;
三者的区别:内连接会将T1表中每一项和T2进行比较,比较匹配的想并合并在这一行,在左/右连接中T1叫左表,T2叫右表,左连接中除了能生成和内连接一样的结果集,还会把NULL值与T1中未进行匹配的值进行组合输出,右连接则相反,外连接就是左/右连接的集合。
USING语句的使用展示即同义表达:
SELECT
column_list
FROM
T1
LEFT JOIN T2 USING(c1,c2,c3, ...);
SELECT
column_list
FROM
t1
LEFT JOIN t2 ON
t1.c1 = t2.c1
AND t1.c2 = t2.c2
AND t1.c3 = t2.c3
AND ... ;
2.笛卡尔连接
给定两个集合A
和B
,A x B
的笛卡尔乘积是所有有序对(a
,b
)的集合,属于A
,b
属于B
。要在Oracle中创建表的笛卡尔乘积,可以使用CROSS JOIN
子句。 以下说明了CROSS JOIN
子句的语法:
SELECT
column_list
FROM
T1
CROSS JOIN T2;
七、Orcale 操作符
1. UNION 、UNION ALL
下图展示两者区别:
是对列的合并
2. INTERSECT
Oracle INTERSECT
运算符比较两个查询的结果,并返回两个查询相同的行。
3. MINUS
MINUS
运算符比较两个查询,并返回第一个查询中但不是第二个查询输出的行。 换句话说,MINUS
运算符从一个结果集中减去另一个结果集。
八、Oracle 约束
1. 主键
创建主键示例:
CREATE TABLE purchase_orders (
po_nr NUMBER PRIMARY KEY,
vendor_id NUMBER NOT NULL,
po_status NUMBER(1,0) NOT NULL,
created_at TIMESTAMP WITH TIME ZONE NOT NULL
);
-- 明确地给PRIMARY KEY主键约束分配了一个名称:pk_purchase_orders。
CREATE TABLE purchase_orders (
po_nr NUMBER,
vendor_id NUMBER NOT NULL,
po_status NUMBER(1,0) NOT NULL,
created_at TIMESTAMP WITH TIME ZONE NOT NULL,
CONSTRAINT pk_purchase_orders PRIMARY KEY(po_nr)
);
-- 创建多个列主键
CREATE TABLE purchase_order_items (
po_nr NUMBER NOT NULL,
item_nr NUMBER NOT NULL,
product_id NUMBER NOT NULL,
quantity NUMBER NOT NULL,
purchase_unit NUMBER NOT NULL,
buy_price NUMBER (9,2) NOT NULL,
delivery_date DATE,
PRIMARY KEY (po_nr, item_nr)
);
将主键约束添加到一个存在的表。要做到这一点,只需要使用ALTER TABLE
语句,如下所示:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name
PRIMARY KEY (column1, column2, ...);
删除主键约束
ALTER TABLE table_name
DROP CONSTRAINT primary_key_constraint_name;
启用/禁用主键约束
要在将大量数据加载到表中或更新海量数据时需要提高性能,可以暂时禁用PRIMARY KEY
约束。
要禁用或启用表的主键约束,可以使用ALTER TABLE
语句:
ALTER TABLE table_name
[ DISABLE | ENABLE ] CONSTRAINT primary_key_constraint_name;
或者,
ALTER TABLE table_name
[ DISABLE | ENABLE ] PRIMARY KEY;
2.外键
创建外键示例:
CREATE TABLE child_table (
...
CONSTRAINT fk_name
FOREIGN KEY(col1, col2,...) REFERENCES parent_table(col1,col2)
ON DELETE [ CASCADE | SET NULL ]
);
当删除父表中的行时,使用
ON DELETE
子句来指定结果。
- ON DELETE CASCADE:如果父项中的一行被删除,那么子表中所有引用该行的行都将被删除。
- ON DELETE SET NULL:如果父项中的一行被删除,那么对该外键列的引用该行的子表中的所有行将被设置为
NULL
。
将外键约束添加到表中
如果要将外键约束添加到现有表中,请按如下所示使用ALTER TABLE
语句:
ALTER TABLE child_table
ADD CONSTRAINT fk_name
FOREIGN KEY (col1,col2) REFERENCES child_table (col1,col2);
3. NOT NULL 、UNQUE、CHECK
可以通过使用ALTER TABLE语句将NOT NULL
/UNIQUE
/CHECK
约束添加到现有表。
ALTER TABLE table_name MODIFY ( column_name NOT NULL)
/ UNIQUE(column_name)
/ CHECK(expression);
使用ALTER TABLE
语句从列中删除NOT NULL
约束,如下所示:
ALTER TABLE table_name MODIFY ( column_name NULL)
使用ALTER TABLE
语句从列中删除唯一、检查约束,如下所示:
ALTER TABLE table_name
DROP CONSTRAINT unique_name / check_name;
九、PL/SQL
1.定义变量
declare
--- 声明变量
--- 第一种 := 赋值
i number(2) :=10;
--- 第二种引用型变量使用into查询语句赋值
stu student.name%type;
info student%rowtype;
begin
--- 操作
select name into stu from student where id = 201800301323;
select * into info from student where id = 201800301323;
dbms_output.put_line(i);
dbms_output.put_line(stu);
dbms_output.put_line(info.name || '的性别为' || info.gender);
end;
2.条件判断
declare
i number (3) := ⅈ
begin
if i<18 then
dbms_output.put_line('未成年');
elsif i<40 then
dbms_output.put_line('中年人');
else
dbms_output.put_line('老年人');
end if;
end;
3.循环
--- while循环
declare
i number(2) := 1;
begin
while i<11 loop
dbms_output.put_line(i);
i := i + l;
end loop;
end;
--- exit循环
declare
i number(2) := 1;
begin
loop
exit when i>10;
dbms_output.put_line(i);
i := i + 1;
end loop;
end;
--- for循环
declare
begin
for i in 1..10 loop
dbms_output.put_line(i);
end loop;
end;
4.游标
游标可以存放多个对象、多行记录
declare
cursor c2 (eno emp.deptno%type) is select empno from emp where deptno = eno;
en emp.empno%type;
begin
open c2(10) ;
loop
fetch c2 into en;
exit when c2%notfound;
update emp set sal=sal+100 where empno=en;commit;
end loop;
close c2;
end;
5.存储过程
存储过程就是把已经编译的PL/SQL放置在数据库中以便随时调用,一般是固定步骤的业务。
CREATE OR REPLACE PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END
IN
:这是一个默认参数,它将值传递给子程序。
OUT
:必须指定,它向调用者返回一个值。
IN OUT
:必须指定,它将初始值传递给子程序并将更新的值返回给调用者。
6.存储函数
过程和函数的区别在于函数可以有一个返回值,而过程没有,但过程和函数都可以通过out指定一个或多个参数的打印
存储过程和存储函数的参数都不能带长度
7.触发器
触发器,就是制定一个规则,做增删改操作的时候,只要满足该规则,自动触发,无需调用。
**语句级触发器:**不包含有for each row
的触发器。
**行级触发器:**包含有for each row
的就是行级触发器。
for each row
是为了使用:old或者:new对象或者一行记录。
create or replace trigger t1
after
insert
on person
declare
begin
dbms_output.put_line ( '一个新员工入职');
end;
插入新数据后打印“一个新员工入职”。
create or replace trigger t2
before
update
on emp
for each row
declare
begin
if :old.sal> :new.sal then
raise_application_error(-20001, '不能给员工降薪' );
end if;
end;
修改工资前发现比之前更低发出警告。
raise_application_error(-20001~20999,‘错误提示信息’)