Oracle笔记
Oracle 笔记
1. 用户相关操作
查看当前用户
show user; /* 或 */ select user from dual;查看所有用户名
select * from all_users;创建新用户
sqlplus /nolog /* 进入 sqlplus 环境, nolog 参数表示不登录 */ sqlplus /as sysdba /* 以系统管理员身份登录 */ sqlplus (用户名)/密码 /* 登录一般用户 */ create user xixi identified by mima; /* 创建用户 xixi, 密码为 mima */给用户授予权限
GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW , DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE, DBA, CONNECT,RESOURCE,CREATE SESSION TO xixi;切换新用户
conn(ect) xixi/mima;删除用户
drop user xixi; /* 如果用户拥有数据表, 则不能直接删除, 要用上关键字 cascade */ drop user xixi cascade;
2. 创建和管理表
命名规则（表名和列名）：
- 必须以字母开头
- 必须在1-30个字符之间
- 必须只能包含 A-Z, a-z, 0-9, _, $ 和 #
- 必须不能和用户定义的其他对象重名
- 必须不能是 Oracle 的保留字
/* 创建表 */
CREATE TABLE dept(
deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13)
);
/* 删除表 (所有相关索引被删除, 正在运行的相关事物被提交, 不能回滚) */
DROP TABLE dept;
/* 查看表的描述 */
DESCRIBE dept;
/* 查看用户定义的表 */
SELECT table_name FROM user_tables;
/* 查看用户定义的各种数据库对象 */
SELECT DISTINCT object_type FROM user_objects;
/* 查看用户定义的表, 视图, 同义词和序列 */
SELECT * FROM user_catalog;
/* 追加一个新列 */
ALTER TABLE dept ADD (DAGE NUMBER(3) NOT NULL);
/* 修改一个列 (可以修改列的数据类型, 尺寸和默认值, 对默认值的修改只影响今后对表的修改) */
ALTER TABLE dept MODIFY (DAGE NUMBER(2) NULL);
/* 删除一个列 */
ALTER TABLE dept DROP COLUMN dage;
/* 改变对象的名称 */
RENAME dept TO detail_dept;
/* 清空表 (删除表中所有的数据, 释放表的存储空间, 不能回滚) */
TRUNCATE TABLE detail_dept;
/* 给表添加描述 */
COMMENT ON TABLE detail_dept IS 'This is detail_dept table';
/* 查看当前用户所有表的描述 */
SELECT * FROM user_tab_comments;3. 处理数据
插入数据 (需要
commit后才能生效)INSERT INTO detail_dept(deptno, dname, loc) VALUES(1, 'xk', 'china');向表中插入空值
INSERT INTO detail_dept(deptno, dename) VALUES(30, 'psf'); INSERT INTO detail_dept VALUES(1, 'sdf', NULL); /* SYSDATE 记录当前系统的日期和时间 */ INSERT INTO emp(hire_date) VALUES(SYSDATE);更新数据
UPDATE dept SET dname = 'xh' WHERE deptno = 1; /* 如果省略 WHERE 子句, 则表中所有数据都将被更新 */ UPDATE dept SET dname = 'np';删除数据
DELETE FROM dept WHERE dname = 'xh'; /* 如果省略 WHERE 子句, 则表中的全部数据将被删除 (效率不如 truncate table) */ DELETE FROM dept;批量插入数据
INSERT ALL INTO dept(deptno, dname, loc) VALUES(10, 'shei', 'us') INTO dept(deptno, loc, dname) VALUES(11, 'us', 'sei') INTO dept(loc, deptno, dname) VALUES('japan', 12, 'xiba') SELECT 13, 'hi', 'chian' FROM dual;
4. 数据库事务
数据库事务由以下的部分组成：
一个或多个 DML 语句
以下面其中之一作为结束：
- COMMIT 或 ROLLBACK 语句
- DDL 或 DCL 语句（自动提交）
- 用户回话正常结束
- 系统异常终了
执行 DML 操作的用户可以通过 SELECT 语句查询之前的修正, 其他用户不能看到当前用户所做的改变, 直到当前用户结束事务, DML 语句所涉及到的行被锁定, 其他用户不能操作
一个 DDL 语句
一个 DCL 语句
5. 查询数据
/* 选择全部列 */
SELECT * FROM dept;
/* 选择特定列 */
SELECT dname FROM dpet;
/* 排除重复行 */
SELECT DISTINCT dname FROM dept;
/* 使用算术运算符 */
SELECT deptno + 300 FROM dept;
/**
空值是无效的, 未指定的, 未知的或不可预知的值
空值不是空格或者0
包含空值的数学表达式的值都为空值, 可以用 NVL 和 NVL2 处理空值
**/
SELECT NVL(deptno, 0) FROM dept; // deptno 为空则取 0
SELECT NVL2(deptno, deptno + 1, 1) FROM dept; // 不为空取 deptno + 1, 为空取1
/* 使用连接符将多列结果合成一列 (日期和字符只能在单引号中出现) */
SELECT dname || loc as 'new_col' FROM dept;
/* LIKE 运算 (%代表一个或多个字符, _代表一个字符) */
SELECT dname
FROM dept
WHERE dname LIKE 'x_'; // xk, xh
SELECT loc
FROM dept
WHERE loc LIKE '_h%'; // china
/** 大小写控制函数
LOWER('SQL Course') -> sql course
UPPER('SQL Course') -> SQL COURSE
INITCAP('SQL Course') -> Sql Course
REPLACE('oracle Sql', 'oracle', 'training') -> training Sql
**/
/** 字符控制函数
CONCAT('Hello', 'World') -> HelloWorld
SUBSTR('HelloWorld', 1, 5) -> Hello
LENGTH('HelloWorld') -> 10
INSTR('HelloWorld', 'W') -> 6
**/
/** 数字函数
四舍五入: ROUND(45.926, 2) -> 45.93
截断(保留小数点): TRUNC(45.926, 2) -> 45.92
求余: MOD(1600, 300) -> 100
**/
SELECT ROUND(45.923, 2), ROUND(45.923, 0), ROUND(45.923, -1)
FROM dual;
/* dual 是一个'伪表', 可以用来测试函数和表达式 */
/** 日期
Oracle 内部使用数字存储日期: 世纪, 年, 月, 日, 小时, 分钟, 秒
默认的日期格式是 DD-MON-RR
在日期上加上或减去一个数字结果仍为日期
两个日期相减返回日期之间相差的天数
可以用数字除24来向日期中加上或减去小时
**/
/** 日期函数
MONTHS_BETWEEN() : 两日期相差多少月
ADD_MONTHS() : 加月份以日期
NEXT_DAY() : 指定日期的下一天
LAST_DAY() : 一个月中的最后一天
ROUND() : Round 日期
TRUNC() : Truncate 日期
TO_CHAR(date, 'format_model') : 日期转换, 例 YYYY-MM-DD
TO_CHAR(number, 'format_model') : 数字转换
对于 format_model:
9 -> 代表一个数字
0 -> 强制显示0
$ -> 放置一个 $ 符
L -> 放置一个浮动本地货币符
. -> 显示小数点 D
, -> 显示千位指示符 G (通常和 D 搭配)
**/
SELECT TO_CHAR(sal, '$99,999.00') SALARY
FROM emp
WHERE ename = 'ALLAN';
/* 使用 TO_NUMBER 函数将字符转换成数字 */
SELECT TO_NUMBER('100.00', '999,999.99999') FROM emp;
/* 使用 TO_DATE 函数将字符转换成日期 */
SELECT TO_DATE('2009-08-06', 'YYYY-MM-DD') FROM DUAL;
/* NULLIF(e1, e2) : 当 e1 等于 e2 时, 返回 NULL, 否则返回 e1 */
/* COALESCE(e1, e2, e3) : e1 不为空返回 e1, 否则返回 e2, e2 为空返回 e3 */
/* DECODE(expression, value, result1, result2) : 若 expression 结果为 value, 返回
result1, 否则返回 result2 */
/* 多表查询 */
/* 1.等值连接 */
SELECT e.empno, d.deptno
FROM emp e, dept d
WHERE e.deptno = d.deptno;
/* 2.非等值连接 */
SELECT e.ename, e.sal, j.grade_level
FROM emp e, job_grades j
WHERE e.sal BETWEEN j.lowest_sal AND j.highest_sal;
/* 3.外连接
使用外连接可以查询不满足连接条件的数据
外连接的符号是(+)
没有(+)的一侧将限制全部记录, 有(+)的一侧显示匹配的记录
*/
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column(+) = table2.column;
/* 4. 自连接 */
SELECT worker.ename || ' works for ' || manager.ename
FROM emp worker, emp manager
WHERE worker.mgr = manager.empno;
/* 5. 叉集
使用 CROSS JOIN 子句使连接的表产生叉集
叉集和笛卡尔集是相同的
*/
SELECT * FROM emp
CROSS JOIN dept;
/* 6. 自然连接
NATURAL JOIN 子句, 会以两个表中具有相同名字的列作为条件创建等值连接
在表中查询满足等值条件的数据
*/
SELECT deptno, dname, loc, name
FROM dept
NATURAL JOIN location;
/* 7. 使用 USING 子句创建连接
使用 USING 可以在有多个列满足条件时进行选择
不要给选中的列加上表名前缀或别名
*/
SELECT e.empno, e.ename, d.loc
FROM emp e JOIN dept d
USING (deptno)
/* 8. 内连接
内连接只返回满足连接条件的数据
*/
SELECT e.empno, e.ename, e.deptno, d.deptno, d.loc
FROM emp e JOIN dept d
ON (e.deptno = d.deptno);
/* 9. 左外连接
FROM 表中不满足 ON 条件的也返回
*/
SELECT e.ename, e.deptno, d.dname
FROM emp e
LEFT OUTER JOIN dept d
ON (e.deptno = d.deptno);
/* 10. 右外连接
JOIN 表中不满足 ON 条件的也返回
*/
SELECT e.ename, e.deptno, d.dname
FROM emp e
RIGHT OUTER JOIN dept d
ON (e.deptno = d.deptno);
/* 11. 全外连接
两张表中不满足的也都会返回
*/
SELECT e.ename, e.deptno, d.dname
FROM emp e
FULL OUTER JOIN dept d
ON (e.deptno = d.deptno);6. 约束
约束是表级的强制规定，维护数据的完整性
约束阻止删除有关联关系的数据
有以下五种约束：
- NOT NULL
- UNIQUE
- PRIMARY KEY
- FOREIGN KEY
- CHECK
可以在表级或列级定义约束
可以通过数据字典视图查看约束
/* NOT NULL 约束只能定义在列级 */
CREATE TABLE emp(
last_name VARCHAR2(25) NOT NULL,
hire_date DATE
CONSTRAINT emp_hire_date_nn
NOT NULL,
...
);
/* UNIQUE 约束可以定义在表级或者列级 */
CREATE TABLE emp(
email VARCHAR2(25),
...
CONSTRAINT emp_email_uk UNIQUE(email)
);
/* PRIMARY KEY 约束可以定义在表级或列级 */
CREATE TABLE emp(
emp_id NUMBER(4),
CONSTRAINT emp_id_pk PRIMARY KEY(emp_id)
);
/* FOREIGN KEY 约束可以定义在表级或列级 */
CREATE TABLE emp(
dept_id NUMBER(6),
CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id)
REFFENCES dept(dept_id)
)
/* CHECK 约束定义每一行必须满足的条件 */
CREATE TABLE emp(
emp_deptno NUMBER(2),
CONSTRAINT emp_deptno_ck CHECK(emp_deptno BETWEEN 10 AND 99)
);
/* 创建表之后添加约束 */
ALTER TABLE dept
ADD CONSTRAINT dept_deptno_pk
PRIMARY KEY(deptno);
/* 删除约束 */
ALTER TABLE dept
DROP CONSTRAINT dept_deptno_pk;
/* 使用 CASCADE 选项删除约束 */
ALTER TABLE dept
DROP PRIMARY KEY CASCADE;
/* 查询约束 */
SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'DEPT';
/* 查询定义约束的列 */
SELECT constraint_name, column_name
FROM user_cons_columns
WHERE table_name = 'DEPT';7. 分组
分组作用于一组数据, 并对一组数据返回一个值, 组函数可以对一组数据进行操作：
- AVG
- COUNT
- MAX
- MIN
- SUM
SELECT COUNT(*) FROM dept GROUP BY dept.loc; /* 返回记录总数 */
SELECT COUNT(DISTINCT dname) FROM dept; /* 返回不为空且不重复的记录总数 */
/* 组函数忽略空值 */
SELECT AVG(comm) FROM emp;
/* 在组函数中使用 NVL */
SELECT AVG(NVL(comm, 0)) FROM emp;
/*
在 SELECT 列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中
包含在 GROUP BY 子句中的列不必包含在 SELECT 列表中
可以使用多个列分组， 不能在 WHERE 子句中使用组函数, 可以在 HAVING 子句中使用组函数
*/
SELECT deptno, AVG(sal)
FROM emp
GROUP BY deptno
HAVING AVG(sal) > 10;8. 视图
/* 从子查询创建视图 */
CREATE VIEW dept_vu
AS (SELECT * FROM dept);
/* 描述视图结构 */
DESCRIBE dept_vu;
/* 创建视图时在子查询中给列定义别名, 在选择视图中的列时应使用别名 */
CREATE VIEW dept_vu
AS SELECT deptno no, dname name
FROM dept;
/* 修改视图 */
CREATE OR REPLACE VIEW dept_vu
AS SELECT deptno no, dname name
FROM dept;
/* 视图中使用 DML 的规定
可以在简单视图中执行 DML 操作
当视图定义中包含以下元素之一时不能使用 delete:
1. 组函数
2. GROUP BY 子句
3. DISTINCT 关键字
4. ROWNUM 伪列
当视图定义中包含以下元素之一时不能使用 update:
1. 组函数
2. GROUP BY 子句
3. DISTINCT 关键字
4. ROWNUM 伪列
5. 列的定义为表达式
当视图定义中包含以下元素之一时不能使用 insert:
1. 组函数
2. GROUP BY 子句
3. DISTINCT 关键字
4. ROWNUM 伪列
5. 列的定义为表达式
6. 表中非空的列在视图定义中未包括
*/
INSERT INTO dept_vu(no, name) VALUES(1, 'hi');
/* 删除视图只是删除视图的定义, 并不会删除基表的数据 */
DROP VIEW dept_vu;
/* 临时视图
临时视图可以是嵌套在 SQL 语句中的子查询
在 FROM 子句中的子查询是临时视图
临时视图不是数据库对象
*/
/* Top-N 分析 */
SELECT [column_list], ROWNUM
FROM (SELECT [column_list] FROM t ORDER BY Top-N_column)
WHERE ROWNUM <= N;9. 序列
- 自动提供唯一的数值
- 共享对象
- 主要用于提供主键值
- 代替应用代码
- 将序列值装入内存可以提高访问效率
/* 创建序列 */
CREATE SEQUENCE deptid_seq
INCREMENT BY 10
START WITH 120
MAXVALUE 9999
NOCACHE
NOCYCLE;
/* 查询数据字典 user_sequences 获取序列定义信息 */
SELECT sequence_name, min_value, max_value,
increment_by, last_number
FROM user_sequences;NEXTVAL 和 CURRVAL 伪列：
- NEXTVAL 返回序列中下一个有效的值, 任何用户都可以引用
- CURRVAL 中存放序列的当前值
- NEXTVAL 应在 CURRVAL 之前指定, 二者应同时有效
INSERT INTO departments(department_id,
department_name, location_id)
VALUES (dept_deptid_seq.NEXTVAL,
'Support', 2500);
/* 查询序列 dept_deptid_seq 的当前值 */
SELECT dept_deptid_seq.CURRVAL
FROM dual;
/* 修改序列: 可以修改序列的增量, 最大值, 最小值, 循环选项或是否装入内存 */
ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 20
MAXVALUE 999999
NOCACHE
NOCYCLE;
/* 删除序列 : 删除之后, 序列不能再次被引用 */
DROP SEQUENCE dept_deptid_seq;10. 索引
/* 创建索引
自动创建: 在定义 PRIMARY KEY 或 UNIQUE 约束后系统自动在相应的列上创建唯一性索引
手动创建: 用户可以在其它列上创建非唯一的索引, 以加速查询
*/
CREATE INDEX dept_name_idx
ON dept(dname);
/* 查询索引信息 */
SELECT ic.index_name, ic.column_name, ic.column_position col_pos, ix.uniqueness
FROM user_indexes ix, user_ind_columns ic
WHERE ic.index_name = ix.index_name
AND ic.table_name = 'DEPT';
/* 删除索引 */
DROP INDEX dept_deptno_pk;11. 同义词
使用同义词访问相同的对象：
- 方便访问其它用户的对象
- 缩短对象名字的长度
/* 创建视图同义词 */
CREATE SYNONYM d_dept
FOR dept_vu;
/* 删除同义词 */
DROP SYNONYM d_dept;12. 集合操作
UNION : 返回两个查询的结果集的并集
SELECT emp_id, job_id FROM emp UNION SELECT emp_id, job_id FROM job_history;UNION ALL : 返回两个查询的结果集的并集以及两个结果集的重复部分(不去重)
INTERSECT : 返回两个结果集的交集
MINUS : 返回两个结果集的补集
All articles in this blog are licensed under CC BY-NC-SA 4.0 unless stating additionally.
