Oracle默认用户
- SYS:权限最大的管理员账号
- SYSTEM:仅次于SYS,用于创建和管理数据库中的表与视图
- SYSMAN：企业管理的超级管理员账号
- DBSNMP：智能代理用户, 用于监控和管理数据库相关性能
- SYSDBA：执行数据库启动关闭、备份恢复等操作
- SYSOPER：可以启动关闭数据库
sqlplus system/abcdef@orcl //以system用户登录数据库
sqlplus sys/abcdef@orcl as sysdba //以sys用户登录数据库
connect simhr/abcdef //在sqlplus里进行账号切换
Oracle体系结构
oracle的物理存储结构主要分为：数据、日志、控制、初始化参数文件.
逻辑存储将存储空间依次划分为表空间、段、盘区与数据块。
一个数据库从逻辑结构上划分多个表空间,一个表空间继续划分为多个段,一个段又被划分为多个盘区,一个盘区又被划分为多个数据块。
表空间是数据库中最大的逻辑存储结构, 盘区是最小的磁盘空间分配单元, 数据块是Oracle最小的数据读写单元.
oracle方案对象
方案对象：表、视图、索引、触发器、同义词、序列、函数、过程、包…
非方案对象：表空间、用户、角色、回退段、目录…
Tables
//创建表
CREATE TABLE T_DEPT(deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13));
//查询表结构
DESCRIBE T_USER;
//修改表
ALTER TABLE T_DEPT ADD (job VARCHAR2(9));
ALTER TABLE T_DEPT MODIFY (dname VARCHAR2(15));
ALTER TABLE T_DEPT SET UNUSED COLUMN JOB;
DROP UNUSED COLUMNS;
//删除表, 不能回退
DROP TABLE T_DEPT CASCADE CONSTRAINTS;
//改名
RENAME T_DEPT TO T_DEPARTMENT;
//截断表
TRUNCATE TABLE T_DEPT;
//增加注释
COMMENT ON TABLE T_DEPT IS 'DEPT INFORMATION';
oracle中可以为表中的字段定义PRIMARY KEY、NOT NULL、UNIQUE、FOREIGN KEY、CHECK共5中约束.
//定义约束
CREATE TABLE emp(
empno NUMBER(4),
ename VARCHAR2(10),
deptno NUMBER(7,2) NOT NULL,
CONSTRAINT emp_empno_pk PRIMARY KEY(EMPNO),
CONSTRAINT emp_dpeno_fk FOREIGN KEY(deptno) REFERENCES dept(deptno)
CONSTRAINT emp_deptno_ck CHECK(DEPTNO BETWEEN 10 AND 99));
//管理约束
ALTER TABLE emp ADD CONSTRAINT emp_mgr_fk FOREIGN KEY(mgr) REFERENCES emp(empno);
ALTER TABLE emp DROP CONSTRAINT emp_mgr_fk;
ALTER TABLE dept DROP PRIMARY KEY CASCADE;
ALTER TABLE emp ENABLE CONSTRAINT emp_mgr_fk;
Views
//创建视图
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY]
CREATE VIEW usersView AS SELECT * from T_USERS
WITH CHECK OPTION CONSTRAINT empvu20_ck;
CREATE OR REPLACE VIEW emp_view AS SELECT empno EMP_NUMBER, ename, sal, loc
FROM emp, dept
WHERE emp.deptno = dept.deptno AND dept.deptno = 10;
//查询视图
SELECT ename from emp_view WHERE empno = 9876;
//删除视图
DROP VIEW emp_view
Sequences
- 序列号为ORACLE整数, 最多38个数字, 一般指出序列名字、上升下降、序列号之间间距与其他信息.
CREATE SEQUENCE project_team_seq
START WITH 1000 //起始值
INCREMENT BY 1 //步长
MINVALUE 1 //最小值
MAXVALUE 10000 //最大值
CYCLE //循环
CACHE 1000 //缓存
NOCYCLE NOORDER;
SELECT project_team_seq.NEXTVAL FROM DUAL; //向序列中取下一个值,第一次取值必须要先取到NEXTVAL值才会有CURRVAL值
SELECT project_team_seq.CURRVAL FROM DUAL; //向序列中取当前值
Synonyms
- 为任何表、视图、快照、序列、过程、函数或包的别名.
- 有私有与公有两种同义词.
CREATE SYNONYM SLAG FOR SCOTT.SALRADE; //私有
CREATE PUBLIC SYNONYM P_SLAG FOR SCOTT.SALGRADE; //共有
Roles
CREATE USER user_name //用户名
IDENTIFIED BY password //密码
[DEFAULT TABLESPACE tablespace_name] //初始表空间
[TEMPORARY TABLESPACE tablespace_name] //临时初始表空间
[QUOTA quota_number [K | M] | UNLIMITED ON tablespace_name] //允许使用的最大字节数
[PROFILE profile_name] //概要文件名字
[PASSWORD expire] //设置口令为过期状态
[ACCOUNT lock| unlock] //账号锁定
CREATE USER simhr IDENTIFIED BY abcdef
DEFAULT TABLESPACE users
QUOTA 10M ON users
PASSWORD EXPIRE
ACCOUNT lock;
GRANT CREATE SESSION TO user; //授予数据库建立会话/登录的系统权限
GRANT CREATE table TO user;
GRANT CONNECT TO user;
DML
- 如果算术表达式中包含NULL值，则表达式的值也为NULL.
//插入
INSERT INTO dept(deptno, dname, loc) VALUES (50, 'DEVELOPMENT', 'DETROIT');
INSERT INTO emp(deptno, hiredate) VALUES (5, SYSDATE);
INSERT INTO su(id, name, address) SELECT N_USER_ID, VC_LOGIN_NAME, VC_PASSWORD FROM T_USERS; //插入多行
//更新
UPDATE emp SET (job, deptno) = (SELECT job, deptno FROM emp WHERE empno = 7499) WHERE empno = 7698;
//查询
SELECT ename "Name", sal * 12 "Annual Salary" FROM emp; //别名
SELECT ename || ' ' || 'is a' || ' '|| job AS "Employee Details" FROM emp; //连接符
SELECT ename from emp WHERE sal BETWEEN 1000 AND 1500;
SELECT ename from emp WHERE ename LIKE '_A%'; // _匹配任意单一字符, %匹配0个或多个字符.
SELECT ename from emp WHERE ename LIKE '_A\%' ESCAPE '\'; //转义特殊符号
常用函数
LPAD(X,Y,Z) //在X的左边加入Y个Z
RPAD(X,Y,Z) //在X的右边加入Y个Z
LOWER(X)
UPPER(X)
INITCAP(X) //第一个字母大写,剩下的都小写
LENGTH(X)
SUBSTR(X,Y,Z) //从字符串X的第Y个字符开始,取出Z个字符
INSTR(X,Y) //将Y插入到X的位置
CONCAT(X,Y) //连接字符串
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM DUAL; //Oracle默认日期格式DD-MON-YY
MONTHS_BETWEEN(date1, date2) //两个日期相差月数
NVL(expr1, expr2) //如果expr1为null,返回expr2值,否则返回expr1值.
NVL2(expr1, expr2, expr3) //如果expr1为null,返回expr3值,否则返回expr2值.
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
DECODE(col|expression, search1, result1
[, search2, result2,...,]
[, default])
CASE job WHEN 'ANALYST' THEN 1.10 * sal
WHEN 'CLERK' THEN 1.15 * sal
WHEN 'SALESMAN' THEN 1.20 * sal
ELSE sal
END
DECODE(job,'ANALYST', 1.10 * sal, 'CLERK', 1.15 * sal, 'SALESMAN', 1.20 * sal, sal);
PL/SQL
//匿名块
DECLARE
v_number NUMBER(20);
BEGIN
v_number := 100 / 0;
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('DIVIDED BY ZERO');
END;
//记录类型
DECLARE
TYPE record_type_emp IS RECORD(
name VARCHAR2(10),
job VARCHAR2(9),
salary NUMBER(10));
record record_type_emp;
BEGIN
SELECT ename,job,salary INTO record FROM scott.emp WHERE empno=7369;
dbms_output.put_line('ename:' || emp_record.name||' job:'||emp_record.job||' sal:'|| emp_record.salary);
END;
//记录表类型
DECLARE
TYPE dept_table_type IS TABLE OF VARCHAR(14) INDEX BY BINARY_INTEGER;
table dept_table_type;
BEGIN
FOR i IN 1..4 LOOP
SELECT dname INTO dept_table_type(i) FROM scott.dept WHERE deptno = 10 * i;
dbms_output.put_line('dname:'|| dept_table(i));
END LOOP;
END;
//在PL/SQL中使用INSERT
DECLARE
v_deptno dept.deptno%TYPE;
v_dname dept.dname%TYPE;
BEGIN
v_deptno := &no;
v_dname := '$name';
INSERT INTO dept(deptno, dname) VALUES (v_deptno,v_dname);
END;
//循环
DECLARE
v_number NUMBER(2);
i NUMBER(2);
j NUMBER(2);
BEGIN
v_number := 6;
i := 0;
LOOP
i := i + 1;
FOR j IN 1..i LOOP
DBMS_OUTPUT.PUT('*');
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
EXIT WHEN i = v_number;
END LOOP;
END;
//系统自定义异常
DECLARE
e_amount_remaining EXCEPTION;
BEGIN
RAISE e_amount_remaining;
EXCEPTION
WHEN e_amount_remaining THEN
:g_message := 'There is still an amount
in stock.';
END;
//触发器
CREATE OR REPLACE TRIGGER audit_emp_values
AFTER DELETE OR INSERT OR UPDATE ON emp
FOR EACH ROW
BEGIN
INSERT INTO audit_emp_table (user_name, timestamp,empno,
old_ename, new_ename, old_job,new_job, old_sal, new_sal)
VALUES (USER, SYSDATE, :OLD.empno,:OLD.ename, :NEW.ename,
:OLD.job,:NEW.job, :OLD.sal, :NEW.sal );
END;
//存储过程
CREATE OR REPLACE PROCEDURE insert_emp
(no emp.empno%TYPE, name emp.ename%TYPE,
Job emp.job%TYPE, mgr emp.mgr%TYPE,
hiredate emp.hiredate%TYPE , salary emp.sal%TYPE ,
comm emp.comm%TYPE , deptno emp.deptno%TYPE
) IS
BEGIN
INSERT INTO emp VALUES(no,name,job,mgr,hiredate,salary,comm,deptno);
END;
CREATE OR REPLACE PROCEDURE swap
(x IN OUT NUMBER ,y IN OUT NUMBER)
IS z NUMBER;
BEGIN
z:=x;
x:=y;
y:=z;
END;
//函数
CREATE OR REPLACE FUNCTION get_sal(p_id IN emp.empno%TYPE)
RETURN NUMBER
IS
v_salary emp.sal%TYPE :=0;
BEGIN
SELECT sal INTO v_salary FROM emp WHERE empno = p_id;
RETURN v_salary;
END get_sal;