oracle数据库学习总结

oracle数据库学习总结 一、ORACLE中字段的数据类型 1、字符型

1）char 范围最大2000个字节定长

char(10) '张三' 后添空格6个把10个字节补满'张三' 性别char(2) '男'

2）varchar2 范围最大4000个字节变长 varchar2(10) '张三' 在数据库中'张三'

2、数字number 范围10的-38次方到10的38次方 可以表示小数也可以表示整数

number(4) 最大表示4位整数-9999 到9999

number(5,2) 表示5位有效数字2位小数的一个小数-999.99 到999.99

3、日期date 包含年月日和时分秒7个字节 4、图片blob 二进制大对象图像/声音4G 二、如何建表 学生表student

create table student( --学生表 xh number(4), --学号 xm varchar2(10), --姓名 sex char(2), --性别 birthday date, --日期 sal number(7,2) --奖学金 );

三、字段的添加、删除、修改 1、添加字段(学生所在班级classid)

alter table student add (classid number(2)); 2、修改字段的长度

alter table student modify (xm varchar2(12)) ; 3、修改字段的类型(不能有记录的)

alter table student modify (xh varchar2(5)); 4、删除一个字段

alter table student drop column sal; 5、删除表

drop table student; 6、表的名字修改 rename student to stu; 7、字段如何改名字 --先删除

a)alter table student drop column sal; --再添加

b)alter table student add (salary number(7,2));

四、插入字段（values是insert语句的一个关键词，后面跟要插入的一组字段值。）

1、所有字段都插入

insert into student values ('A001','张三','男','01-5月-05',10); 2、部分字段插入

insert into student(xh,xm,sex) values ('A003','JOHN','女'); 3、插入空值 insert

into

student(xh,xm,sex,birthday)

values

('A004','MARTIN','男',null);

五、删除语句 1、删除delete

delete from student; 删除所有记录,表结构还在,写日志,可以恢复的,速度慢

drop table student; 删除表的结构和数据

delete from student where xh='A001'; 删除一条记录

truncate table student; 删除表中的所有记录,表结构还在,不写日

志,无法找回删除的记录,速度快

六、查询语句 1、查询select select * from student;

select xh,xm,sex from student;

select * from student where xh like 'A%1'; %任意多个字符 select * from student where xh like 'A__1'; _1个字符 七、混合函数 1、求最大值

select greatest(100,90,80,101,01,19) from dual; 2、求最小值

select least(100,0,-9,10) from dual; 八、空值转换函数

nvl(comm,0) 字段为空值那么就返回0 否则返回本身 select comm,nvl(comm,0) from emp; comm 类型和值的类型是一致的 九、复杂的函数

1、decode 选择结构(if ... elseif .... elesif ... else结构) 例、要求:

sal=800 显示低工资 sal=3000 正常工资 sal=5000 高工资 只能做等值比较

解决：select sal,decode(sal,800,'低工资',3000,'正常工资',5000,'高工资','没判断')

from emp;

表示如下的if else 结构 if sal=800 then '低工资'

else if sal =3000 then

'正常工资'

else if sal = 5000 then '高工资' else '没判断' end if

sal > 800 sal -800 > 0 十、分组函数

分组函数返回值是多条记录或计算后的结果 group by、sum、avg 1、计算记录的条数count

select count(comm) from emp; 字段上count 会忽略空值 comm不为空值的记录的条数 2、group by 分组统计 --有分组函数的时候 --分组统计的功能

统计每种工作的工资总额是多少?? select job,sum(sal) from emp group by job; --行之间的数据相加

select sum(sal) from emp; --公司的工资总额 3、显示平均工资>2000的工作?？？ <1>统计每种工作的平均工资是多少 <2>塞选出平均工资>2000的工作 从分组的结果中筛选having select job,avg(sal) from emp group by job

having avg(sal) > 2000;

group by 经常和having搭配来筛选 十一、约束

主键约束-- 每个表要有主键，唯一的标识一行数据

非空约束 唯一性 外键约束 检查约束

1、建立表的同时使用约束 create table student( --学生表 xh number(4) primary key, --学号主键 xm varchar2(10) not null, --姓名不能为空 sex char(2) check (sex in ('男','女')), --性别 birthday date unique, --日期

sal number(7,2) check (sal between 500 and 1000),--奖学金sal >=500 and sal <=1000

classid number(2) references cla(id) ); --必须要先有cla表才对 --一定先建立班级cla表 1）主键约束primary key not null check

unique 唯一约束

create table student( --学生表

xh number(4) constraint pk_stu primary key, --学号主键 xm varchar2(10) constraint nn_stu not null, --姓名不能为空 sex char(2) constraint ck_stu_sex check (sex in ('男','女')), --性别

birthday date constraint uq_bir unique, --日期

sal number(7,2) constraint ck_sal check (sal between 500 and 1000)--奖学金sal >=500 and sal <=1000

);

2、建立约束的同时给约束指定名字，便于删除 create table cla( --班级表

id number(2) constraint pk_cla primary key, --班级编号 cname varchar2(20) constraint nn_cla not null --班级名字 );

3、建完表后加约束 学生表student

create table student( --学生表 xh number(4), --学号 xm varchar2(10), --姓名 sex char(2), --性别 birthday date, --日期 sal number(7,2) --奖学金 ); 加约束 加主键

alter table student add constraint pk_stu primary key (xh); 加非空

alter table student modify (xm not null); 检查约束

alter table student add check(sex in ('男','女'));

alter table student add constraint ck_sal check(sal between 500 and 1000));

给student加班级字段

alter table student add (classid number(2)); 4、如何查看约束?? 约束一定加在表上 一个表上到底有哪些约束

select constraint_name,constraint_type from user_constraints

where table_name = 'STUDENT' --查看表上有什么约束

select * from user_constraints; --查看约束作用在什么字段上 select * from user_cons_columns where CONSTRAINT_NAME='PK_STU'; user_constraints数据字典表 十二、实例

《1》子查询和关联查询 建立表如下: 学生基本信息表 CREATE Student(

[Studentid][Int]IDENTITY(1,1)NOT NULL primary key,--主键[StudentName][char]NOT NULL

)

课程信息表 CREATE Subject( [SubjectID][char]NOT ) 成绩表

CREATE Grade(

[Studentid][Int]NOT NULL, --联合主键[SubjectID][char]NOT NULL, --联合主键[Grade] [INT]NOT NULL,

primary key (studentid,subjectid) )

1.将建表命令改为ORACLE语句在ORACLE中建表create table student( --学生表

studentid number(3) primary key, --学生编号 studentname varchar2(20) --学生的姓名 );

create table subject( --课程表

NULL

primary

key,

--主键

[SubjectName][char]NOT NULL

subjectid char(3) primary key, --课程编号 subjectname varchar2(20) --课程的名字 );

create table grade( --分数表

studentid number(3) references student(studentid), --学生id subjectid char(3) references subject(subjectid), --课程id

mark number(3), --分数

primary key (studentid,subjectid) --联合主键 );

insert into student values (101,'张三'); insert into student values (102,'李云'); insert into student values (103,'未'); insert into subject values ('A01','C++'); insert into subject values ('A02','ASP'); insert into subject values ('A03','JA V A'); insert into grade values (101,'A01',59); insert into grade values (101,'A02',72); insert into grade values (101,'A03',90); insert into grade values (102,'A01',75); insert into grade values (102,'A02',91); insert into grade values (103,'A01',71); 2.作如下4题

第一问：查询出以下信息

学号学生姓名课程名称成绩（要全部学生信息）关联查询(多张表的)

别名

select a.studentid as \"学号\\"学生姓名\subjectname \"课程名称\成绩\"

from student a , subject b , grade c where a.studentid = c.studentid

and b.subjectid = c.subjectid;

[select a.studentid \"学号\\"学生姓名\subjectname \"课程名称\成绩\"

from student a , subject b , grade c] 笛卡尔积 3 * 3 * 6 = 54;

第二问：查询出以下信息

学号学生姓名课程名称成绩（只显示每科最高分）

select a.studentid \"学号\\"学生姓名\subjectname \"课程名称\成绩\"

from student a,subject b,grade c where a.studentid = c.studentid and b.subjectid = c.subjectid and (subjectname,mark)

in (select subjectname \"课程名称\\"成绩\" from student a,subject b,grade c

where a.studentid = c.studentid and b.subjectid = c.subjectid group by subjectname)

(最高分---分数比我高的学生的人数=0)

select a.studentid \"学号\学生姓名\subjectname \"课程名称\成绩\" from student a,subject b,grade c where a.studentid = c.studentid and b.subjectid = c.subjectid and (select count(*) from grade where subjectid = b.subjectid and mark > c.mark) = 0 第三问：查询出以下信息

学号学生姓名课程名称成绩（成绩大于60时的显示及格，小于60时的显示不及格）select a.studentid \"学号\学生姓名

\

subjectname \"课程名称\

decode(sign(mark-60),-1,'不及格','及格') \"成绩\" from student a,subject b,grade c where a.studentid = c.studentid and b.subjectid = c.subjectid 第四问：查询出以下信息

学号学生姓名（查询出课程超过1门以上学生的信息） select a.studentid \"学号\学生姓名\count(subjectname)

from student a , subject b , grade c where a.studentid = c.studentid and b.subjectid = c.subjectid group by a.studentid,studentname having

count(subjectname) >= 2 拓展：

一、复制一张表的结构 --数据一起复制

create table mydept as select * from dept; --只复制结构

create table mydept1 as select * from dept where 1=2; --把数据从一个表复制到另一个结构相同的表 insert into mydept1 select * from dept; --只复制部分数据

insert into mydept1 select * from dept where deptno>20; insert into mydept1(deptno,loc) select deptno,loc from dept;

a.没有主键的表如何消重复记录 create table test(

id number(2), name varchar2(10));

insert into test values (1,'aa'); insert into test values (1,'aa'); insert into test values (1,'aa'); insert into test values (2,'bb'); insert into test values (3,'cc');

create table test1 as select distinct * from test;

二、ORACLE中建立数据库对象 约束

序列sequence --- 自动编号---- IDENTITY (SQLSERVER) <1>建立序列

create sequence seq1; 从1开始每次自动增加1 没有最大值 <2>怎么使用

select seq1.nextval from dual; nextval 伪列下一个值

select seq1.currval from dual; currval 伪列当前值 create sequence seq2

start with 1000 --起始值1000 1000开始 increment by 2 --步长2 每次增加2 maxvalue 9000 --最大值9000 一直到9000

minvalue 900 --最小值900 回到900 重新开始cycle --循环序列