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) 域完整性 CHECK4) 用户自定义完整性
写法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 ;