读书笔记《Oracle从入门到精通》

一、SQL基础

1.SQL种类

• 数据定义语言DDL ： 对数据库中的（表、视图、索引等）作增删改操作
• 数据操纵语言DML ： 对数据库中表作增删改操作
• 数据查询语言DQL ： 对数据库中表作查询操作
• 数据控制语言DCL ： 对数据库中用户或角色作权限设置操作

2.常用数据类型

• 字符型
  • varchar2（0~4000字节）（可变长度字符串）
  • char（0~2000字节）（定长的字符型数据）
• 数字型
  • number(p,s) : p代表精度，s代表保留保留的小数位
  • float
• 日期类型
  • date : 精确到秒
  • timestamp : 精确到小数秒
• 其他
  • blob : 存放二进制4GB
  • clob : 存放字符串4GB

3.DDL

• 创建表

CREATE TABLE tablename  
(  
    column_name1 DATATYPE [NULL | NOT NULL],  
    column_name2 DATATYPE [NULL | NOT NULL],  
    PRIMARY KEY(column_name1)  
    CONSTRAINT column1_fk FOREIGN KEY(column_name2)  
    REFERENCE table_name(column_name)  
)  

• 修改表

ALTER TABLE table_name  
ADD column_name DATATYPE;

ALTER TABLE table_name  
MODIFY column_name DATATYPE;
 
ALTER TABLE table_name  
DROP COLUMN column_name;

ALTER TABLE table_name  
ADD column_name DATATYPE  
MODIFY column_name DATATYPE;

• 删除表

DROP TABLE table_name

4.约束

4.1 主键约束

• 创建表时添加主键约束

CREATE TABLE class
(
    student varchar(100),
    remark varchar(500),
    PRIMARY KEY(student)
)

• 修改表时添加主键约束

ALTER TABLE class
ADD CONSTRAINTS class_pk PRIMARY KEY(student);

• 移除主键约束

ALTER TABLE class
DROP CONSTRAINTS class_pk;

4.2 外键约束

• 创建表时添加外键约束

CREATE TABLE student
(
    class_id varchar(100),
    remark varchar(500),
    CONSTRAINT class_fk FOREIGN KEY(class_id)
    REFERENCES class(id)
    ON DELETE CASCADE
)

• 修改表时添加外键约束

ALTER TABLE
ADD CONSTRAINT class_fk FOREIGN KEY(class_id)
REFERENCES class(id)
ON DELETE CASCADE

• 移除外键约束

ALTER TABLE
DROP CONSTRAINT class_fk

4.3 检查约束

• 创建表时添加检查约束

CREATE TABLE student
(
    age varchar(100),
    remark varchar(500),
    CONSTRAINT student_age_ck CHECK(age > 18 AND age < 50)
);

• 修改表时添加检查约束

ALTER TABLE student
ADD CONTRAINT student_age_ck CHECK(age > 18 AND age < 50)

• 移除检查约束

ALTER TABLE student
DROP CONSTRAINT student_age_ck

4.4 唯一约束

• 创建表时添加唯一约束

CREATE TABLE student
(
    id varchar(10),
    remark varchar(500),
    CONSTRAINT student_id_un UNIQUE(id)
);

• 修改表时添加唯一约束

ALTER TABLE student
ADD CONTRAINT student_id_un UNIQUE(id)

• 删除唯一约束

ALTER TABLE student
DROP CONSTRAINT student_id_un

4.5 非空约束

创建表时添加 NOT NULL
修改表时添加 MODIFY column DATATYPE NOT NULL

5.DML语句

• 添加数据

> 直接添加数据
INSERT INTO table_name (column1,column2...)
VALUES (data1,data2...);

> 通过其他表添加数据
INSERT INTO table_name(column1,column2...)
SELECT column1,column2... FROM table_name2;

> 通过其他表添加同时创建表
CREATE TABLE table_name
AS SELECT column1,column2... FROM table_name2;

• 修改数据

UPDATE table_name
SET column1=data1,column2=data2;

• 删除数据

DELETE FROM table_name
WHERE CONDITION;

• 其他DML语句

> TRUNCATE 删除表中全部记录，速度快于DELETE

> MERGE 与UPDATE功能相似，可同时对表作增加和修改
MERGE INTO table_name1
USING table_name2
ON CONDITION
WHEN MATCHED THEN expression
WHEN NOT MATCHED THEN expression

二、SELECT语句

1.结果集‘*’与指定列

• 查询明确列在查询效率上要币使用通配符‘*’效率高
• 只返回必要的列可以减少网络带宽消耗

2.拼接符 ||

select price || '*' || quantity || '=' || price*quantity as expression from table_name 

3.substr函数

select product_id ,substr(product_id,1,6) as 编号 from product_info;

结果：product_id    编号
      07800086      078000
      12345678      123456

4.instr函数

5.去重

select distinct(name) from student;

6.排序

• NULL在排序中默认当作最大值来处理，但是也可以由开发人员来指定

    - 系统默认：升序时NULL在首位
    - 系统默认：降序时NULL在末位
    - 手动指定：升序指定NULL在首位：select * from t order by qty NULLS FIRST
    - 手动指定：降序指定NULL在末位：select * from t order by qty NULLS LAST

• 使用表达式作为排序字段

select price,quantity,price*quantity from product order by price*quantity asc;

• 使用字段位置作为排序字段

select name,price,quantity from product order by 3 asc;

• 使用多个字段排序

按照第一个字段进行排序
在此基础上按照第二个字段进行排序，也就是说当第一个字段的数据相同时才对这些数据以第二个字段进行排序
利用多个字段排序，可以单独为每个字段指定排序方式

7.where子句检索

• 操作符
  • 关系操作符：< , <= , > , >= , = , != , <>
  • 比较操作符：IS NULL,IS NOT NULL,LIKE,BETWEEN…AND…,IN
  • 逻辑操作符：AND,OR,NOT
• 模糊查询LIKE
  • _：可以代替一个字符
  • %：可以替代多个字符
• 分组
  • 当查询中出现group by时，select列表中只能存在聚集函数或出现在group by子句中的字段
  • group by子句不允许出现在where条件中，where子句中不能使用聚集函数
  • having是分组（group by）后的筛选条件，分组后的数据组内再筛选。where则是在分组前筛选。
  • where子句不能作用于聚集函数而having子句可以
  • having子句限制的是组而不是行

8.连接查询

• 简单连接语句

select * from table1,table2;
查询结果是两个的表的笛卡尔积（两个表的记录数积）

• 内连接

select * from table1,table2 where table1.type = table2.typeId
select * from table1 inner join table2 where ...
select * from table1 join table2 where ...

• 自连接

select * from product p1,product p2
where p1.product_id != p2.product_id
and p1.quantity = p2.quantity

• 外连接
  • left join 左外连接：返回符合连接的记录，还包含了左边表中的全部记录，如果右边没匹配上则置空
  • right join 右外连接：返回符合连接的记录，还包含了右边表中的全部记录，如果左边没匹配上则置空
  • full join 全外连接：返回符合连接的记录，同时返回左表未匹配成功和右边未匹配成功的记录
• (+)的使用
  该操作符总是放在非主表的一方，并且需要使用where子句，不能存在outer join关键字，
  • +号的左连接：select * from t1.type_id = t2.type_id(+)
  • +号的右连接：select * from t1.type_id(+) = t2.type_id

三、oracle内置函数

1.数值型函数

• 绝对值：ABS(n)
• 余数：MOD(n2,n1) 返回n2除于n1的余数
• 符号：SIGN(n) 整数返回1 0返回0 负数返回-1
• 大于等于最小正整数：CEIL(n) 例：CEIL(10.5)==11
  小于等于最大正整数：FLOOR(n) 例：FLOOR(10.5)==10
• 四舍五入：
  • ROUND(n)
  • ROUND(n,integer):当integer为正整数时表示n被四舍五入为integer位小数，如果integer为负数时，则n被四舍五入至小数点向左integer位
  • ROUND(100.23456,2.56) == 100.23
  • ROUND(100.23456,4) == 100.2346
  • ROUND(155.23456,-2) == 200
• 截取：
  • TRUNC(n)
  • TRUNC(n,integer):若integer不是正整数则自动截取为整数部分，integer为正整数表示将n截取到integer位小数，为负数则截取到小数点左第integer位，被截取的部分用0代替
  • TRUNC(100.23456,2.56) == 100.23
  • TRUNC(100.23456,4) == 100.2345
  • TRUNC(155.23456,-2) == 100

2.字符型函数

• 字符串长度：LENGTH(‘ABC中’) == 4
• 字符串截取：SUBSTR(‘ABCD’,POSITION[,SUBSTR_LENGTH])
  POSITION:截取的开始位置，初始值从1开始，如果该值为负，则表示从char右边算起
  SUBSTR_LENGTH:截取的长度
• 字符串连接：CONCAT(CHAR1,CHAR2) 等效于 ‘||’
• 字符串搜索：INSTR(STRING,TO_MATCH_CHAR[,POSITION[,OCCURRENCE]])
  STRING:源字符串
  TO_MATCH_CHAR:匹配目标字符串
  POSITION:起始位置，默认为1
  OCCURRENCE:TO_MATCH_CHAR第几次出现，默认为1
• 大小写转换：UPPER(CHAR) LOWER(CHAR)
• 替换函数：REPLACE(STRING,TO_MATCH_CHAR[,REPLACE_STRING])
  REPLACE(‘ABCD’,‘BC’,‘AD’) == ‘AADD’

3.转换函数

• 数值转字符串：
  • TO_CHAR(number)
  • TO_CHAR(number[,fmt]) ：TO_CHAR(16.89,‘99.9’) == 16.8
  • TO_CHAR(date)
  • TO_CHAR(date[,fmt]):TO_CHAR(SYSDATE,‘HH24:MI:SS’)
  • TO_CHAR(SYSDATE,‘YYYY-MM-DD HH24:MI:SS’)  SYSDATE:系统日期
• 字符串转日期： TO_DATE(char[,fmt])
• 字符串转数字：
  • TO_NUMBER(char[,fmt])
  • TO_NUMBER(‘2345.6789’,‘99.99’) == 45.67

4.NULL函数

• NULL函数：
  • NVL(expression1，expression2)：如果expression1为null，则返回expression2，否则返回expression1
  • NVL(expression1，expression2,expression3)：如果expression1为null，则返回expression3，否则返回expression2

5.集合函数

• 平均值：
  • AVG(ALL | DISTINCT EXPRESSION)
  • ALL:所以值，包括重复的值，默认为ALL
  • DISTINCT:去重后取平均值
• 计算数量： COUNT(ALL | DISTINCT EXPRESSION):同上
• 最大值最小值：
  • MAX(ALL | DISTINCT EXPRESSION):同上
  • MIN(ALL | DISTINCT EXPRESSION):同上
• 求和： SUM(ALL | DISTINCT EXPRESSION):同上
• 匹配：
  • DECODE(EXPRESSION,SEARCH,RESULT[,SEARCH1,RESULT1][,DEFAULT])
  • 当expression符合条件search时就返回result，该过程可以重复多个，如果最后都没有匹配上的，则返回默认的default

四、PL/SQL

1.定义

DECLARE : 声明，可有可无
BEGIN : 执行开始，必须
EXCEPTION : 异常开始，可有可无
END : 结束，必须

2.变量

• 变量声明
  variable_name datatype not null := | default expression
  当使用not null时，大括号内容为必须，二选一
• 常量声明
  contant_name constatnt datatype not null := | default expression
• %type
  • 当有需求变动时，只需要改变被引用的变量或常量的数据类型其他引用的数据类型自然就改变了
  • 使用%type可以完全兼容数据，避免数据提取的时候出现溢出或者不符合的情况
  • 当表字段类型发生改变时，PL/SQL块变量的数据类型不需要修改

DECLARE
    v_product_id product.product_id%TYPE;
    v_pname contant product.pname%TYPE := 'abc';
    v_date DATE := SYSDATE;
    v_qty number(10) := 99.9;

• 复合型变量

数量可变变量

DECLARE
    TYPE prodect_record IS RECORD
    (
        v_id product.id%TYPE;
        v_name VARCHAR2(20);
    );
    v_product product_record;
BEGIN
        select id,name into v_product from product;
END;

数量固定

DECLARE
    v_product product%ROWTYPE;
BEGIN
    select * into v_product from product;
END;

3.结构控制语句

• IF结构

IF condition THEN
    todo
END IF;

IF condition THEN
    todo
ELSE
    todo
END IF;

IF condition1 THEN
    todo
ELSIF condition2 THEN
    todo
ELSE
    todo
END IF;

• CASE结构

简单CASE

CASE expression
WHEN result1 THEN
    todo
WHEN result2 THEN
    todo
ELSE 
    todo
END CASE;

搜索式CASE

CASE
WHEN boolean_expression THEN 
    todo
WHEN boolean_expression THEN
    todo
ELSE 
    todo
END CASE;

• 循环LOOP

使用exit来终止循环

<<BASIC_LOOP>>
LOOP
    todo
    IF num>3 THEN
        EXIT BASIC_LOOP;
    END IF;
END LOOP;

WHILE boolean_expression
LOOP
    todo
END LOOP;

其中index_name 为循环下标，lower_bound为起始数值

FOR index_name IN lower_bound..upper_bound LOOP
    todo
END LOOP;

4. PL/SQL中使用DML/DDL

可以使用EXCUTE IMMEDIATE 来执行动态SQL语句来操纵DDL语言在PL/SQL中使用

BEGIN
    create_string := 'CREATE TABLE student
    (
        name VARCHAR2(20) PRIMARY KEY
    )'
    EXCUTE IMMEDIATE create_string;
END;

5.异常

EXCEPTION
    WHEN exception1 THEN
        todo
    WHEN exception2 THEN
        todo
    WHEN OTHERS THEN
        todo

6.函数

• 语法

CERATE OR REPLACE FUNCTION function_name
    parameter)declaration
RETURN datatype
IS/AS
BEGIN
    function_body
END;

五、游标

1.显示游标语法

CURSOR cursor_name
    [(parameter_name datatype,...)]
IS
    select_statement
其中select_statement是游标关联的SELEC语句

2.使用步骤

• 声明游标 : DELARE CURSOR cursor_name IS select_statement
• 打开游标 : OPEN cursor_name
• 读取游标 : FETCH cursor_name INTO record_name
• 关闭游标 : CLOSE cursor_name

DECLARE
    CURSOR product_cursor
IS
    select * from product
    
cur_product product%ROWTYPE;
BEGIN
    OPEN product_cursor;
    FETCH product_cursor INTO cur_product;
    CLOSE product_cursor;
END;

在游标中FETCH..INTO..是单条提取
如果游标提取数据是多条，可使用LOOP语句

DELARE
    CURSOR product_cursor
    IS select id,name,age from product where age > 18;
    cur_id product.id%TYPE;
    cur_name product.name%TYPE;
    cur_age product.age%TYPE;
BEGIN
    OPEN product_cursor;
    LOOP
        FETCH product_cursor INTO cur_id,cur_name,cur_age;
        EXIT WHEN product_cursor%NOTFOUND;
    END LOOP;
    CLOSE product)cursor;
END;

使用CURSOR FOR LOOP

DECLARE
    CURSOR cur_1
    IS select name,age from student;
BEGIN
    FOR v_cursor IN cur_1;
    LOOP
        DBMS.OUTPUT.PUTLINE(v_cur.name || v_cur.age);
    END LOOP;
END;

六、视图

1.定义

一个虚拟的表，本身不包含任何数据，存放表的查询结果

2.作用

• 使数据简化，隐藏的数据的复杂性
• 使数据更加独立
• 增加安全性，视图通常设置为只读属性

3.语法

创建

CREATE OR REPLACE VIEW 
    view_name
AS
    select id,name,age from student;


查看

select id,name,age from view_name;


创建多表视图

CREATE OR REPLACE VIEW 
    view_name
AS
    select s.id,s.name,a.city
    from student s,address a
    where s.address_id = a.id;

4.只读设置

CREATE OR REPLACE VIEW
    view_name
AS
    select * from student
WITH READ ONLY;

5.删除视图

DROP VIEW view_name;

七、存储过程

1.作用

• 简化复杂操作，多个sql语句封装在一个独立的单元
• 增加数据的独立性
• 提高安全性
• 提高性能，多个sql语句只需要编译一次，只需要连接一次数据库交互

2.语法

创建

CREATE OR REPLACE PROCEDURE
    procedure_name
AS
BEGIN
    DBMS.OUTPUT.PUT_LINE('TEST');
END;

执行存储过程

BEGIN
    procedure_name
END;

查看存储过程

select * from user_sourcr where name = 'procedure_name';

查看错误提示,可以帮助程序员排查错误

show errors procedure procedure_name;

执行存储过程

EXEC procedure_name;

3.输入输出参数

如果没有关键字IN,则表示默认为IN

CERATE PROCEDURE procedure_name
(
    p_name in VARCHAR2
    p_age out NUMBER
    p_address VARCHAR2
    p_message in out VARCHAR2
)

执行

EXEC procedure_name('zhang',18,'深圳','OK');

4.删除存储过程

DROP PROCEDURE procedure_name;

八、触发器

1.能触发的条件

• DML操作
  • INSERT
  • UPDATE
  • DELETE
• DLL操作
  • CREATE
  • ALTER
  • DROP
• 数据库事件
  • LOGON/LOGOFF
  • STARTUP/SHUTDOWN
  • ERRORS

2.语法

DML触发器

of column : 指作用在某列上
on table : 指作用在某表上
for each row : 指行级触发器，省略则为语句触发器。语句触发器无视行有没有增加或删除是否成功都会执行。
follows : 指触发器的顺序

CREATE OR REPLACE TRIGGER
    trigger_name
BEFORE/AFTER/INSTEAD OF
INSERT/UPDATE/DELETE [OF COLUMN]
ON TABLE
[FOR EACH ROW]
FOLLOWS trigger
ENABLE/DISABLE
WHEN condition
trigger_body

DDL触发器

同上
CREATE/ALTER/DROP

3.实例

当执行删除语句会打印信息

CREATE TRIGGER first_trigger
    AFTER DELETE
    ON student
BEGIN
    IF DELETEING THEN
        DBMS.OUTPUT_PUT_LINE('删除人员信息');
    END IF;
END;

多种触发事件

CREATE TRIGGER first_trigger
    AFTER INSERT OR UPDATE OR DELETE
    ON student
BEGIN
    CASE
    WHEN INSERTING THEN
        DBMS.OUTPUT_PUT_LINE('新增人员信息');
    WHEN UPDATEING THEN
        DBMS.OUTPUT_PUT_LINE('修改人员信息');
    WHEN DELETEING THEN
        DBMS.OUTPUT_PUT_LINE('删除人员信息');
    END CASE;
END;

九、用户

1.语法

test为表空间，表空间限额10M，PASSWORD EXPIRE表示当前用户密码立即过期，下次登录需要修改密码

CREATE USER song
IDENTIFIED BY password
DEFAULT TABLESPACE test
QUOTA 10M ON test
PASSWORD EXPIRE;

2.修改用户密码

ALTER USER song
IDENTIFIED BY new_password
DEFAULT TABLESPACE test;

3.删除用户

删除用户及用户下所有的数据文件

DROP USER song CASCADE

4.授予权限

object_privilege : 权限对象放名称
ALL PRIVILEGES : 所有的系统权限
WITH ADMIN OPTION : 被授予权限的用户还可以给其他用户进行系统授权

GRANT object_privilege | ALL PRIVILEGES 
TO user_name IDENTIFIED BY password
WITH ADMIN OPTION;

5.撤销权限

REVOKE system_privileges
FROM user_name