--Oracle数据库重要知识点整理 2017-01-24 soulsjie
目录
--一、创建及维护表... 2
--1.1 创建... 2
--1.2 维护表... 2
--二、临时表的分类、创建、使用... 3
--三、删除及截断表... 3
--1.删除表 delete 只是删除表的数据不释放表数据在内存中占用的空间... 3
--2.DROP table 删除表结构和数据... 3
--3.截断表... 4
--四、显示查看表信息... 4
--五、ORACLE常用数据类型... 4
--六、伪列ROWID和ROWNUM介绍以及使用... 5
--七、Oracle常用运算符和操作符的使用... 5
--八、简单查询... 6
--8.1日期列使用... 6
--8.2DISTINCT的使用... 6
--8.3处理NULL. 6
--8.4连接字符串... 6
--8.5WHERE语句... 6
--8.6ORDER BY子句|ASC|DESC. 6
--8.7多列排序... 6
--九、复杂查询... 7
--9.1分组查询以及HAVING子句的使用，HAVING子句可以对结果进项筛选... 7
--9.2分组函数MAX|MIN|AVG|SUM|COUNT使用... 7
--9.3连接查询语句... 7
--9.4相等连接查询... 8
--9.5不等连接查询... 8
--9.6自连接查... 8
--9.7内连接和外连接区别及使用... 8
--9.8子查询或嵌套查询的使用... 8
--9.9集合操作符的使用... 8
--十、常见内置函数... 8
--10.1 INSTR|LOWER|UPPER|LTRIM|RTRIM|TRIM|SUBSTR|REPLACE使用... 8
--10.2 LTRIM去除字符串左边的空格... 9
--10.3 RTRIM去除字符串右边的空格... 9
--10.4 TRIM去除字符串两边的空格... 9
--10.5 SYSDATE|ADD_MONTHS|LAST_DAY(d)|MONTHS_BETWEEN(d1,d2)|NEXT_DAY使用 9
--10.6 TO_CHAR|TO_DATE|TO_NUMBER使用 字符转换... 9
--十一、数据操作语句... 10
--11.1 INSERT语句使用... 10
--11.2 UPDATE语句使用... 10
--11.3 DELETE语句使用... 10
--十二、事务和约束... 10
--12.1 事务介绍... 11
--12.2 数据约束类型... 11
--十三、视图索引序列... 11
--13.1 创建维护以及使用视图... 12
--13.2 视图的使用... 12
--13.3 创建索引... 12
--十四、PL/SQL语句... 12
--14.1 PL/SQL语句块介绍以及基本语法... 12
--14.2 定义及使用变量... 13
--14.3 IF语句使用... 13
--14.4 CASE语句使用... 13
--14.5 基本循环语句使用... 14
--14.6 WHILE循环语句使用... 14
--14.7 FOR循环语句使用... 14
--14.8 游标定义及使用... 15
--十五、函数和过程... 16
--15.1创建函数... 16
--15.2创建过程... 16
--15.3调用函数... 17
--15.4调用过程... 17
--15.5异常处理以及异常抛出... 17
--附录、连接查询时用到的辅助表... 17
/*数据查询语句介绍
数据定义语句介绍
数据控制语句介绍
数据操纵语句介绍*/
-------------------------------
--一、创建及维护表
-------------------------------
--1.1 创建
CREATE TABLE ZYJ_GONGZI(
Id integer not null,
gongzi number,
fafang_date date,
us_name varchar(256)
);
--1.2 维护表
--1.2.1增加一个字段
ALTER TABLE ZYJ_GONGZI ADD marsk VARCHAR(256);
--1.2.2 修改一个字段
ALTER TABLE ZYJ_GONGZI MODIFY marsk VARCHAR(150);
--1.2.3删除表中的某个字段
ALTER TABLE ZYJ_GONGZI DROP COLUMN marsk;
--1.2.4将表重命名
ALTER TABLE ZYJ_GONGZI RENAME TO ZYJ_RICE;
ALTER TABLE ZYJ_RICE RENAME TO ZYJ_GONGZI;
--1.2.5修改表中的字段名
ALTER TABLE ZYJ_GONGZI RENAME COLUMN fafang_date TO fafang_riqi;
ALTER TABLE ZYJ_GONGZI RENAME COLUMN fafang_riqi TO fafang_date;
--1.2.6 给表添加注释
COMMENT ON TABLE ZYJ_GONGZI IS 'soulsjie 练习SQL创建的工资表';
--1.2.7 给字段添加注释
COMMENT ON COLUMN ZYJ_GONGZI.ID IS '工资表序号';
--------------------------------------
--二、临时表的分类、创建、使用
--------------------------------------
--------------------------------------
--三、删除及截断表
--------------------------------------
--1.删除表 delete 只是删除表的数据不释放表数据在内存中占用的空间
DELETE TABLE ZYJ_GONGZI;
--2.DROP table 删除表结构和数据
DROP TABLE ZYJ_GONGZI;
--3.截断表
TRUNCATE TABLE ZYJ_GONGZI;
--------------------------------------
--四、显示查看表信息
SELECT table_name FROM USER_TABLES;
--------------------------------------
--------------------------------------
--五、ORACLE常用数据类型
--------------------------------------
/*
Text 用于文本或文本与数字的组合。最多 255个字符。
Memo Memo 用于更大数量的文本。最多存储 65,536个字符。
注释：无法对 memo 字段进行排序。不过它们是可搜索的。
Byte 允许 0 到 255 的数字。 1 字节
Integer 允许介于 -32,768 到 32,767 之间的数字。2 字节
Long 允许介于 -2,147,483,648 与 2,147,483,647 之间的全部数字 4 字节
Single 单精度浮点。处理大多数小数。 4 字节
Double 双精度浮点。处理大多数小数。 8 字节
Currency 用于货币。支持 15 位的元，外加 4 位小数。
提示：您可以选择使用哪个国家的货币。8 字节
AutoNumber AutoNumber 字段自动为每条记录分配数字，通常从 1 开始。 4 字节
Date/Time 用于日期和时间 8 字节
Yes/No 逻辑字段，可以显示为 Yes/No、True/False 或 On/Off 。在代码中，使用常量 True 和 False （等价于 1 和 0）注释：Yes/No 字段中不允许 Null 值，1 比特
Ole Object 可以存储图片、音频、视频或其他 BLOBs (Binary Large OBjects) 最多 1GB
Hyperlink 包含指向其他文件的链接，包括网页。
Lookup Wizard 允许你创建一个可从下列列表中进行选择的选项列表。4 字节
*/
--------------------------------------
--六、伪列ROWID和ROWNUM介绍以及使用
--------------------------------------
--------------------------------------
--七、Oracle常用运算符和操作符的使用
--------------------------------------
SELECT 1+2 FROM DUAL; --'+'
SELECT 2-3 FROM DUAL; --'-'
SELECT 2*3 FROM DUAL; --'*'
SELECT 2/3 FROM DUAL; --'/'
SELECT MOD(3,2) FROM DUAL;--取余
DECLARE
BEGIN
DBMS_OUTPUT.put_line('ss'||'aa');--连接字符
END;
--------------------------------------
--八、简单查询
--------------------------------------
--8.1日期列使用
SELECT fafang_date FROM ZYJ_GONGZI;
--8.2DISTINCT的使用
SELECT DISTINCT identi FROM ZYJ_GONGZI;
--8.3处理NULL
--8.4连接字符串
SELECT 'A'||'B' FROM DUAL;
--8.5WHERE语句
SELECT* FROM ZYJ_GONGZI WHERE GONGZI>9000;
--8.6ORDER BY子句|ASC|DESC
SELECT us_name FROM ZYJ_GONGZI ORDER BY ID DESC;
SELECT us_name FROM ZYJ_GONGZI ORDER BY ID asc;
--8.7多列排序
SELECT * FROM ZYJ_GONGZI ORDER BY fafang_date,id DESC;--按发放时间排序，如果时间一样按序号大小排序
--------------------------------------
--九、复杂查询
--------------------------------------
--9.1分组查询以及HAVING子句的使用，HAVING子句可以对结果进项筛选
SELECT AVG(GONGZI) AS 平均工资 FROM ZYJ_GONGZI GROUP BY identi;--计算每类员工的平均工资
--9.2分组函数MAX|MIN|AVG|SUM|COUNT使用
--9.3连接查询语句
SELECT G.US_NAME,B.IDENTI_NAME FROM ZYJ_GONGZI G,ZYJ_BUMEN B WHERE G.IDENTI=B.IDENTI;
--9.4相等连接查询
--9.5不等连接查询
--9.6自连接查
--9.7内连接和外连接区别及使用
--9.8子查询或嵌套查询的使用
--9.9集合操作符的使用
SELECT * FROM (SELECT ID FROM ZYJ_GONGZI UNION SELECT ID FROM ZYJ_BUMEN);--并集组合所有结果，并删除重复记录
SELECT * FROM (SELECT ID FROM ZYJ_GONGZI INTERSECT SELECT ID FROM ZYJ_BUMEN);--交集 显示相同的数据结果
SELECT * FROM (SELECT ID FROM ZYJ_GONGZI MINUS SELECT ID FROM ZYJ_BUMEN);--差集 A MINUS B 相当于 A-B
--------------------------------------
--十、常见内置函数
--------------------------------------
--10.1 INSTR|LOWER|UPPER|LTRIM|RTRIM|TRIM|SUBSTR|REPLACE使用
SELECT INSTR('HELLO','H') FROM DUAL;--INSTR(字符串1,字符串2) 返回字符串2在字符串1的出现位置，字符串位置从1开始计
SELECT LOWER('HELLO')FROM DUAL; --返回指定字符串的全小写字符串
SELECT UPPER('hello') FROM DUALL;--返回指点字符串的全大写字符
--10.2 LTRIM去除字符串左边的空格
--10.3 RTRIM去除字符串右边的空格
--10.4 TRIM去除字符串两边的空格
SELECT SUBSTR('ZHESHIZIFUCHUAN',10,5) FROM DUAL;--从字符串的第10个字符开始截取5个字符
SELECT REPLACE('HAHAJIEJIE','JIE','QIAN') FROM DUAL;--在字符串中将所有的jie替换成qian
--10.5 SYSDATE|ADD_MONTHS|LAST_DAY(d)|MONTHS_BETWEEN(d1,d2)|NEXT_DAY使用
SELECT SYSDATE FROM DUAL;
SELECT ADD_MONTHS(SYSDATE,1) FROM DUAL;
--10.6 TO_CHAR|TO_DATE|TO_NUMBER使用 字符转换
SELECT TO_NUMBER('152')-2 FROM DUAL;
SELECT TO_DATE('2018/12/26','YYYY/MM/DD') FROM DUAL;
--------------------------------------
--十一、数据操作语句
--------------------------------------
--11.1 INSERT语句使用
INSERT ALL
INTO ZYJ_GONGZI(ID,GONGZI,FAFANG_DATE,US_NAME)
VALUES (1,6300,to_date('2017-02-16','yyyy-mm-dd'),'Jie')
INTO ZYJ_GONGZI(ID,GONGZI,FAFANG_DATE,US_NAME)
VALUES (2,6322,to_date('2017-03-16','yyyy-mm-dd'),'Jies')
INTO ZYJ_GONGZI(ID,GONGZI,FAFANG_DATE,US_NAME)
VALUES (3,4855,to_date('2017-04-16','yyyy-mm-dd'),'Tom')
INTO ZYJ_GONGZI(ID,GONGZI,FAFANG_DATE,US_NAME)
VALUES (4,4698,to_date('2017-03-16','yyyy-mm-dd'),'bob')
SELECT 1 FROM DUAL;
--11.2 UPDATE语句使用
UPDATE ZYJ_GONGZI SET fafang_date=to_date('2018-02-16','yyyy-mm-dd') WHERE ID=1;
--11.3 DELETE语句使用
DELETE FROM ZYJ_GONGZI WHERE ID=4;
--------------------------------------
--十二、事务和约束
--------------------------------------
--12.1 事务介绍
--事务：当前会话对数据的处理只能在当前会话有效必须提交事务，这部分数据才能被其他会话访问
--提交及回滚事务使用
SELECT * FROM ZYJ_GONGZI;
DELETE FROM ZYJ_GONGZI WHERE ID=4;--对数据进行删除操作
ROLLBACK;--回滚删除的操作
--提交事务使用：COMMIT;即可
--事务和锁
--12.2 数据约束类型
--PRIMARY KEY 主键约束
--CONSTRAINT fk_waijian FOREIGN KEY(ID) REFERENCE ZYJ_BUMEN(ID);
--CONSTRAINT un_weiyi UNIQUE (ID);
--CONSTRAINT ck_jiancha CHECK(ID>10);
--数据约束的定义及维护
--ALTER TABLE ZYJ_GONGZI ADD CONSTRAINT pkS_zhujian UNIQUE(us_name);添加新的约束
--ALTER TABLE ZYJ_GONGZI MODIFY CONSTRAINT pkS_zhujian CHECK(ID>10);修改约束
--ALTER TABLE ZYJ_GONGZI DROP CONSTRAINT pkS_zhujian;--删除约束
--------------------------------------
--十三、视图索引序列
--------------------------------------
--13.1 创建维护以及使用视图
--创建个人部门归属的视图
CREATE VIEW gerenguishu AS SELECT G.US_NAME,B.IDENTI_NAME FROM ZYJ_GONGZI G,ZYJ_BUMEN B WHERE G.IDENTI=B.IDENTI;
--13.2 视图的使用
SELECT * FROM gerenguishu;
--13.3 创建索引
CREATE INDEX chazhaoxinxi ON ZYJ_BUMEN(ID,IDENTI_NAME);
--序列创建以及使用
--同义词创建
--------------------------------------
--十四、PL/SQL语句
--------------------------------------
--14.1 PL/SQL语句块介绍以及基本语法
DECLARE
[变量声明]
BEGIN --开始
{函数体}
EXCEPTION
[异常处理]
END;--结束
--14.2 定义及使用变量
DECLARE
AA INTEGER:=12;
BB VARCHAR(20):='JIE';
BEGIN
DBMS_OUTPUT.put_line('AA的值是：'||AA);
DBMS_OUTPUT.put_line('BB的值是：'||BB);
END;
--14.3 IF语句使用
DECLARE
AA INTEGER:=1;
BEGIN
IF AA=1 THEN
DBMS_OUTPUT.put_line('数值为1');
ELSIF AA=2 THEN
DBMS_OUTPUT.put_line('数值为2');
ELSE
DBMS_OUTPUT.put_line('其他数值');
END IF;
END;
--14.4 CASE语句使用
DECLARE
AA INTEGER:=5;
BEGIN
CASE AA
WHEN 1 THEN DBMS_OUTPUT.put_line('数值为1');
WHEN 2 THEN DBMS_OUTPUT.put_line('数值为2');
ELSE DBMS_OUTPUT.put_line('其他数值');
END CASE;
END;
--14.5 基本循环语句使用
DECLARE
AA INTEGER:=0;
BEGIN
LOOP
EXIT WHEN AA>10;
AA:=AA+1;
DBMS_OUTPUT.put_line(AA);
END LOOP;
END;
--14.6 WHILE循环语句使用
DECLARE
AA INTEGER:=0;
BEGIN
WHILE AA<10 LOOP
DBMS_OUTPUT.put_line(AA);
AA:=AA+1;
END LOOP;
END;
--14.7 FOR循环语句使用
DECLARE
AA INTEGER:=0;
BEGIN
FOR AA IN 1..10 LOOP
DBMS_OUTPUT.put_line(AA);
END LOOP;
END;
--14.8 游标定义及使用
DECLARE
CURSOR YB IS SELECT g.id,g.gongzi FROM ZYJ_GONGZI g;
temp YB%ROWTYPE;--创建辅助行级变量
BEGIN
IF NOT YB%ISOPEN THEN
OPEN YB;--打开游标
DBMS_OUTPUT.put_line(TO_DATE(sysdate)||'open success!');
END IF;
LOOP --循环读取数据
FETCH YB INTO temp;
DBMS_OUTPUT.put_line('员工编号：'||temp.id||'的工资是：'||temp.gongzi);
EXIT WHEN YB%NOTFOUND;
END LOOP;
CLOSE YB;--关闭游标
DBMS_OUTPUT.put_line(sysdate||'close success!');
END;
--------------------------------------
--十五、函数和过程
--------------------------------------
--函数及存储过程创建
--15.1创建函数
CREATE OR REPLACE FUNCTION F_JIE(AA INTEGER) RETURN INTEGER IS
temp INTEGER;
BEGIN
temp:=AA;
DBMS_OUTPUT.put_line('你输入的数字是'||temp);
RETURN AA;
END F_JIE;
--15.2创建过程
CREATE OR REPLACE PROCEDURE p_JIE IS
CURSOR YB IS SELECT US_NAME FROM ZYJ_GONGZI;--创建游标
temp YB%ROWTYPE;--创建行级变量
BEGIN
IF NOT YB%ISOPEN THEN OPEN YB;
END IF;
LOOP
EXIT WHEN YB%NOTFOUND;
FETCH YB INTO temp;--游标遍历表格
DBMS_OUTPUT.put_line(temp.us_name);
END LOOP;
END P_JIE;
--15.3调用函数
SELECT F_JIE(&ad) FROM DUAL;--函数调用
--15.4调用过程
BEGIN
P_JIE;
END;
--15.5异常处理以及异常抛出
DECLARE
temp INTEGER:=1;
temp2 INTEGER:=1;
err_info EXCEPTION;--自定义异常
BEGIN
IF temp=temp2 THEN
RAISE err_info;
END IF;
EXCEPTION WHEN err_info THEN
DBMS_OUTPUT.put_line('警告两个id重复！');
END;
--------------------------------------
--附录、连接查询时用到的辅助表
--------------------------------------
CREATE TABLE ZYJ_BUMEN(
id INTEGER PRIMARY KEY,--主键
identi VARCHAR(20),--部门标识
identi_name VARCHAR(20)--部门名
);
COMMENT ON TABLE ZYJ_BUMEN IS 'soulsjie 练习SQL创建的部门表';
--插入数据
INSERT ALL INTO ZYJ_BUMEN VALUES(1,'laoban','老板部')
INTO ZYJ_BUMEN VALUES(2,'jingli','经理部')
INTO ZYJ_BUMEN VALUES(3,'yuangong','员工部')
SELECT 1 FROM DUAL;
SELECT * FROM ZYJ_BUMEN;