数据库--基础--Oracle_数据类型可以存储高达4g字节-CSDN博客

1 Oracle的数据类型

1) VARCHAR

2) NUMBER(n) --> INT || NUMBER(n,m) ---> FLOAT

3) DATE

4) CLOB 大文本数据，可以存放4G的文本

5) BLOB 二进制数据，可以存放视频什么的

2 创建表

CREATE TABLE person( 
  pid    VARCHAR2(18) , 
  name   VARCHAR2(200) , 
  age    NUMBER(3) , 
  birthday  DATE , 
  sex    VARCHAR2(2)  DEFAULT ' '
);

修改表

ALTER TABLE person ADD (address VARCHAR(200) DEFAULT ' ') ; 
ALTER TABLE person MODIFY (name VARCHAR2(20) DEFAULT ' ') ;

当然，列的容量只能改大不能改小。

表名重命名

RENAME person TO tperson ;

3 完整性

1) 实体完整性 PRIMARY / NOT　NULL / UNIQUE

2) 参照完整性

CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid)
CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid) ON DELETE CASCADE

级联删除：主表删除，子表跟删

3) 域完整性 CHECK

4) 用户自定义完整性

写法1

DROP TABLE person ; 
CREATE TABLE person 
(
  pid    VARCHAR2(18) , 
  name   VARCHAR2(200)  NOT NULL , 
  age    NUMBER(3)  NOT NULL  CHECK(age BETWEEN 0 AND 150), 
  birthday  DATE , 
  sex    VARCHAR2(2)  DEFAULT ' '  CHECK(sex IN (' ',' ',' ')), 
  CONSTRAINT person_pid_pk PRIMARY KEY(pid) , 
  CONSTRAINT person_name_uk UNIQUE(name)  
) ;

写法2

DROP TABLE person ; 
CREATE TABLE person 
(
  pid    VARCHAR2(18) , 
  name   VARCHAR2(200)  NOT NULL , 
  age    NUMBER(3)  NOT NULL, 
  birthday  DATE , 
  sex    VARCHAR2(2)  DEFAULT ' ', 
  CONSTRAINT person_pid_pk PRIMARY KEY(pid) , 
  CONSTRAINT person_name_uk UNIQUE(name) , 
  CONSTRAINT person_age_ck CHECK(age BETWEEN 0 AND 150) , 
  CONSTRAINT person_sex_ck CHECK(sex IN (' ',' ',' ')) 
) ;

修改约束

ALTER TABLE person ADD CONSTRAINT person_pid_PK PRIMARY KEY(pid) ; 
ALTER TABLE person ADD CONSTRAINT person_name_UK UNIQUE(pid) ; 
ALTER TABLE person ADD CONSTRAINT person_age_CK CHECK(age BETWEEN 0 AND 150) ; 
ALTER TABLE person ADD CONSTRAINT person_sex_CK CHECK(sex IN (' ',' ',' ')) ;
ALTER TABLE person DROP CONSTRAINT person_age_CK ;

4 查询 Oracle查询区分大小写

关键字处理顺序：FROM--> WHERE --> GROUP --> HAVING --> SELECT -->OVER,DISTINCT,TOP --> ORDER

4.1 SELECT 子句：
4.1.1 Oracle中实现字符串连接的方法
和其他数据库系统类似，Oracle字符串连接使用“||”进行字符串拼接，其使用方式和MSSQLServer中的加号“+”一样。

比如执行下面的SQL语句：

SELECT '工号为'||FNumber||'的员工姓名为'||FName FROM T_Employee WHERE FName IS NOT NULL

除了“||”，Oracle还支持使用CONCAT()函数进行字符串拼接，比如执行下面的SQL语句：

SELECT CONCAT('工号:',FNumber) FROM T_Employee

如果CONCAT中连接的值不是字符串，Oracle会尝试将其转换为字符串，比如执行下面的SQL语句：

SELECT CONCAT('年龄:',FAge) FROM T_Employee

与MYSQL的CONCAT()函数不同，Oracle的CONCAT()函数只支持两个参数，不支持两个以上字符串的拼接。
4.1.2 Oracle中可以在查询各个子句中实现各种四则运算，例如

select 12*val from table1;

4.2 WHERE子句

='......' > >= < <= <>(!=)
LIKE % _
IN / EXISTS
AND / OR
BETWEEN ...AND...

4.3 单值函数 (详见Oracle函数大全那篇)
介绍2个

NVL( string1, replace_with)
功能：如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值，如果两个参数都为NULL ，则返回NULL。

select nvl(rulescore,0) from zwjc_graderule where rulecode='FWTD';

DECODE(value, if1, then1, if2,then2, if3,then3, . . . else )

功能：实现IF ELSEIF ...的功能

select sid,serial,username,
DECODE(command,
0,’None’,
2,’Insert’,
3,’Select’,
6,’Update’,
7,’Delete’,
8,’Drop’,
‘Other’) command
from v$session where username is not null;

5 表连接

WHERE或者JOIN系列

JOIN || CROSS JOIN || LEFT OUTER JOIN / RIGHT ....

6 组函数
常用：COUNT MAX MIN SUM AVG

7 分组统计
GROUP BY col1,col2...
HAVING

8 子查询
分为独立子查询、相关子查询；
子查询：结果为单值、多列单行、单列多行、多列多行；
IN
ANY =ANY >ANY <ANY
ALL =ALL >ALL <ALL
1)

 SELECT ename,sal FROM emp WHERE sal >ALL 
 (SELECT sal FROM emp WHERE deptno=30)  AND deptno!=30 ;

2)（多值对应）：

SELECT * FROM emp  WHERE (sal,NVL(comm,-1)) IN 
(SELECT sal,NVL(comm,-1) FROM emp WHERE deptno=20) ;

9 UPDATE、INSERT以及DELETE

1）CREATE TABLE myemp AS SELECT * FROM emp ; 
2）INSERT INTO myemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(7899,' ',' ',7369,'14-2 -1995',9000,300,40) ;
3）UPDATE myemp SET sal=5000 WHERE empno=7899; 
4）DELETE FROM myemp WHERE empno=7899 ;

10 事务处理
commit / rollback

11 ROWNUM(表示行号，建立虚拟列)

SELECT ROWNUM,empno,ename,job,sal,hiredate FROM emp ; 
SELECT ROWNUM,empno,ename,job,sal,hiredate FROM emp WHERE ROWNUM BETWEEN 5 AND 10;

序列创建一个序列：

CREATE SEQUENCE myseq ;

最大值:MAXVALUE
每次增长的长度:INCREMENT BY
起始位置:START WITH，默认从1开始

从10开始，每次进2

DROP SEQUENCE myseq ; 
CREATE SEQUENCE myseq INCREMENT BY 2 START WITH 10 ;

在1,3,5,7,9之间循环

DROP SEQUENCE myseq ; 
CREATE SEQUENCE myseq MAXVALUE 10 INCREMENT BY 2 START WITH 1 ;

使用序列：
nextVal：取得序列的下一个内容
currVal：取得序列的当前内容

INSERT INTO testseq(next,curr) VALUES (myseq.nextval,myseq.currval) ;

12 集合操作

UNION ---> 集合多个查询结果，去除重复。
UNION ALL ---> 包含重复
INTERSECT ---> 返回多个集合中的相同部分
MINUS ---> 返回集合中的差集

13 视图

CREATE VIEW empv20  AS  
SELECT empno,ename,job,sal,hiredate FROM emp  WHERE deptno=20 ; 
DROP VIEW empv20;

CREATE OR REPLACE VIEW empv20  AS  
SELECT empno,ename,job,sal,hiredate FROM emp  WHERE deptno=20 ;

WITH CHECK OPTION:update、insert等修改操作不能改变视图的创建条件。

CREATE VIEW EMP_VIEW2(EMPNO,EMPNAME,DEPTNO,JOBTITLE,HIREDATE)  
AS SELECT ID,NAME,DEPT,JOB,HIREDATE FROM EMPLOYEE  WHERE DEPT=10  
WITH CHECK OPTION;

WITH READ ONLY:不允许修改

CREATE OR REPLACE VIEW empv20 AS 
SELECT * FROM emp WHERE deptno=20 
WITH READ ONLY ;

14 同义词：用于简化，例如定义“用户名.表名”--->某一个名称

15 用户管理
创建用户:CREATE USER 用户名 IDENTIFIED BY 密码 ;
赋予权限:GRANT 权限1,权限2... TO 用户名 ;
修改密码:ALTER USER 用户名 IDENTIFIYED BY 密码 ;
锁住用户:ALTER USER 用户名 ACCOUNT LOCK ;
ALTER USER 用户名 ACCOUNT UNLOCK ;

16 数据库备份与恢复

EXP命令
IMP命令

17 嵌套表，将某表设为一个数据类型

CREATE TYPE project_ty AS OBJECT( 
  proid   NUMBER(4), 
  proname    VARCHAR2(50), 
  prodate    DATE  
) ; 
CREATE TYPE project_nt AS TABLE OF project_ty ;