Oracle学习

Oracle 基础

---

 SQL语言的动词SQL功能命令动词功能数据查询Select查询数据定义Create、Drop、Alter创建、删除、修改数据操纵InSert、UpData、Delect插入、更新、删除数据控制Grant、Revoke授予权限、回收权限

---

 简单视图和复杂视图的比较特性简单视图复杂视图基表数只有一张基表一张或多张基表包含函数不允许允许包含数据的分组(Distinct 或 视图组)不允许允许对视图的DML操作允许不允许

---

 单行字符函数总结函数描述Concat联合两个字符串,与运算符 || 相等Instr在一个字符串中找到另一个字符串的数字型的开始位置Length统计一个字符串的长度Substr返回一个字符串中从基本一个字符位置开始特定长度的子串Lower将字符串的所有字符转换成小写Upper将字符串的所有字符转换成大写

---

 单行数字函数总结函数描述Abs返回绝对值Round四舍五入Sore返回一个数的平方根Trunc截断一个数

---

 单行日期函数总结函数描述ADD_Months给日期加几个月LAST_Day返回某一个月的最后一天MONTHS_Between返回两个日期之间相差的月数NEXT_Day返回某个日期后的第一个星期几的日期SysDate返回当前系统日期

---

 转换函数总结函数描述TO_Char将日期转换成一个字符串TO_Date将字符串按指定的格式转换成日期(具体格式请参见下表)

---

 组成日期的各单元的不同格式日期单元日期格式表示格式代码描述例子日DY简写的日期名称MON,TUE,FRIDAY全拼的星期名称MONDAY,TUESDAY,FRIDAYDD每个月中的(1 ~ 31)1,2,3,...,31月MM两位月的年01,02,02,...,12MON简写的月份名称JAN,FEB,MAR,...DECMONTH全拼的月份名称JANUARY,...,DECEMBER年YYYY4位数的年Nineteenninety-nineRR2位数的年 小时HH、HH12按12小时制的小时(1 ~ 12)1,2,3,...,12HH24按24小时制的小时(1 ~ 24)0,1,2,3,...,23分MI小时中的分(0 ~ 59)0,1,2,3,...,59秒SS分中的秒(0 ~ 59)0,1,2,3,...,59

---

 聚组函数总结函数描述Avg返回一列的平均值(该列必须是数字型的值)Count返回非 NULL 值的行,*表示返回所有行数Max返回一个最大值Min返回一个最小值Sum返回一列的和(该列必须是数字型的值)Oracle 运算符

---

---

 常用的查询条件查询条件谓词比较=、>、<、>=、<=、!=、NOT、等比较运算符确定范围Between And、Not Between And确定集合In、Not In字符匹配Like、Not Like空值Is Null、Is Not Null多重条件And、Or

---

 运算符的优先级别运算符优先级别目地-、+1单元运算符,正负数*、/2乘,除+、-、||3加,减,联合=、!=、<、>、<=、>=、Is Null、Like、Between、In4比较运算符Not5逻辑非And6与Or7或

---

 Any 或 All 谓词的比较运算符的含义Any 或 All 谓词的比较运算符的含义含义> Any大于子查询结果集中的某个值> All大于子查询结果集中的所有值< Any小于子查询结果集中的某个值< All小于子查询结果集中的所有值>= Any大于等于子查询结果集中的某个值>= All大于等于子查询结果集中的所有值<= Any小于等于子查询结果集中的某个值<= All小于等于子查询结果集中的所有值= Any等于子查询结果集中的某个值< > All不等于子查询结果集中的任何一个值Oracle 数据类型

---

 Oracle 数据类型数据类型关键字描述字符型Char(size)存储固定长度的字符串,最大长度由 size 确定,size 的范围值可从1 ~ 2000
Char()列中存储的数据如果不到 size 的长度,则补充空隔
例:
性别 char(2) '男'varChar2(size)存储可变长度的字符串,最大长度由 size 确定,最大可存储4000字节
varChar2()变量存储多少数据,就需要多少空间
例:
varchar2(10) '张三'数字型Number()范围 10^-38 ~ 10³⁸.可以表示小数,也可以表示整数
例:
Number(4) 最大表示4位整数 -9999 ~ 9999
Number(5,2) 表示5位有效数字,其中2位小数的一个小数 -999.99 ~ 999.99日期型Date包含年月日和时分秒,占7个字节大对象LOB(Large Object)LOB(Large Object,大对象)数据类型非结构化的数据,比如一个二进制文件,一幅图片或一个外部文件.有三种LOB类型,这此LOB类型的目的和存储位置是有区别的,如下表所示...

---

 Oracle LOB(Large Object,大对象)数据类型数据类型目的存储位置描述BLOB二进制大对象数据内部在数据库中可以存储高达 4GB 的二进制数据CLOB数据库语言的字符大对象数据数据内部在数据库中可以存储高达 4GB 的字符数据
Oracle 将数据转换成 UniCode 格式并存储在数据库中BFILE二进制文件数据库之外的文件系统中在数据库外面的操作系统文件中存储高达 4GB 的二进制文件
二进制文件的大小必须符合操作系统中的文件大小的要求
一个 BFILE 列存储了指向包含数据的外部文件的文件定位符
BFILE 数据类型是只读的,不能修改数据 Oracle 基本语法

---

 建表及删除表的语法

创建表 craate

create table student( --学生表student_ID number(4), --学号student_NAME varchar2(10), --姓名student_sex char(2), --性别student_birthday date, --日期student_sal number(7,2) --奖学金);create table class( --班级表class_ID number(2), --班级编号class_name varchar2(20) --班级名字);

添加字段(在学生表中添加班级 class_ID 字段)

alter table student add (class_ID number(2));

修改字段长度

alter table student modify (student_NAME varchar2(12));

修改字段类型(不能有记录的)

alter table student modify (student_ID varchar2(5));

删除一个字段

alter table student drop column student_sal;

删除表

drop table student;

修改表名

rename student to stu;

修改字段名

alter table student drop column student_sal; --先删除alter table student add (student_salary number(7,2)); --再添加

---

 插入记录 insert

1)插入整条数据

insert into student values ('A001','张三','男','01-5月-05',10);

2)部分字段插入

insert into student(student_ID,student_NAME,student_sex) values ('A003','JOHN','女');

3)插入空值

insert into student(student_ID,student_NAME,student_sex,student_birthday) values ('A004','MARTIN','男',null);

---

 删除记录 delete

1)删除所有记录,表结构还在,写日志,可以恢复的,速度慢

delete from student;

2)删除表的结构和数据

drop table student;

3)删除一条记录

delete from student where student_ID='A001';

4)删除表中的所有记录,表结构还在,不写日志,无法找回删除的记录,速度快

truncate table student; 

---

 修改记录 update

1)改一个字段

update student set student_sex='女' where xh='A001';

2)改多个字段

update student set sex='男',student_birthday='1980-04-01' where xh='A001';

3)改为空值 (修改为空时=null)

update student set student_birthday=null where student_ID='A001';

4)把生日为空的人的班级编号改为20(条件中的空是is null / is not null)

update student set classid=20 where student_birthday is null;

5)错误的没有达到要求

update student set classid=20 where student_birthday=null;

6)不表示空值,表示 student_ID 是 null 的字符串

update student set classid=20 where student_NAME='null';

---

 查询记录 select

1)查询所有字段

select * from student;

2)查询部分字段

select student_ID,student_NAME,student_sex from student;

3)使用 LIKE 进行模糊查询

select * from student where student_ID like 'A%1';   -- '%' 符号表示任意多个字符select * from student where student_ID like 'A_1';   -- '_' 符号表示1个字符select * from student where student_ID like '%A%';select * from student where student_ID like 'A%';select * from student where student_ID like '%A';select * from student where student_ID = 'A%';

4)使用排序查询

select * from student order by student_birthday ;  --升序 (order by student_birthday asc;)select * from student order by student_birthday desc; --降序select * from student order by student_birthday desc,student_ID asc; --按 student_birthday 降序,按 student_ID 升序(asc/默认)

5)给出条件查询

select * from student where student_sex='女' or student_birthday='1999-02-01';select * from student where student_sex='女' and student_birthday='1999-02-01';select * from student where student_salary > 20 and student_ID != 'B002';)

---

 日期相关

ORACLE中默认的日期格式'DD-MON-YY' dd 日子(天) mon 月份 yy 2位的年 如:'09-6月-99'

改日期的默认格式,1999年6月9日

alter session set nls_date_format = 'yyyy-mm-dd';insert into student values ('A002','MIKE','男','1905-05-06',10);

恢复ORACLE默认格式

alter session set nls_date_format = 'dd-mon-yy';

察看日期的格式

set linesize 1000select * from nls_session_parameterswhere parameter='NLS_DATE_FORMAT';

永久设置日期格式
改注册表oracle/HOME0 加字符串NLS_DATE_FORMAT 值yyyy-mm-dd

---

 其它

desc 表名  --查看表结构