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 到9999number(5,2) 表示5位有效数字2位小数的一个小数-999.99 到999.993、日期date 包含年月日和时分秒7个字节4、图片blob 二进制大对象图像/声音4G二、如何建表学生表studentcreate 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、删除deletedelete from student; 删除所有记录,表结构还在,写日志,可以恢复的,速度慢drop table student; 删除表的结构和数据delete from student where xh='A001'; 删除一条记录truncate table student; 删除表中的所有记录,表结构还在,不写日志,无法找回删除的记录,速度快六、查询语句1、查询selectselect * 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 ifsal > 800 sal -800 > 0十、分组函数分组函数返回值是多条记录或计算后的结果group by、sum、avg1、计算记录的条数countselect count(comm) from emp; 字段上count 会忽略空值comm不为空值的记录的条数2、group by 分组统计--有分组函数的时候--分组统计的功能统计每种工作的工资总额是多少??select job,sum(sal) from empgroup by job; --行之间的数据相加select sum(sal) from emp; --公司的工资总额3、显示平均工资>2000的工作?？？<1>统计每种工作的平均工资是多少<2>塞选出平均工资>2000的工作从分组的结果中筛选havingselect job,avg(sal) from empgroup by jobhaving 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 <=1000classid number(2) references cla(id)); --必须要先有cla表才对--一定先建立班级cla表1）主键约束primary keynot nullcheckunique 唯一约束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、建完表后加约束学生表studentcreate table student( --学生表xh number(4), --学号xm varchar2(10), --姓名sex char(2), --性别birthday date, --日期sal number(7,2) --奖学金);加约束加主键alter table student add constraint pk_stuprimary 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_typefrom user_constraintswhere table_name = 'STUDENT'--查看表上有什么约束select * from user_constraints;--查看约束作用在什么字段上select * from user_cons_columnswhere 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 NULL primary key,--主键[SubjectName][char]NOT NULL)成绩表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( --课程表subjectid char(3) primary key, --课程编号subjectname varchar2(20) --课程的名字);create table grade( --分数表studentid number(3) references student(studentid), --学生id subjectid char(3) references subject(subjectid), --课程idmark 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 "学号",studentname "学生姓名", subjectname "课程名称",mark "成绩"from student a , subject b , grade cwhere a.studentid = c.studentidand b.subjectid = c.subjectid;[select a.studentid "学号",studentname "学生姓名", subjectname "课程名称",mark "成绩"from student a , subject b , grade c] 笛卡尔积3 * 3 * 6 = 54;第二问：查询出以下信息学号学生姓名课程名称成绩（只显示每科最高分）select a.studentid "学号",studentname "学生姓名", subjectname "课程名称",mark "成绩"from student a,subject b,grade cwhere a.studentid = c.studentidand b.subjectid = c.subjectidand (subjectname,mark)in (select subjectname "课程名称",max(mark) "成绩" from student a,subject b,grade cwhere a.studentid = c.studentidand b.subjectid = c.subjectidgroup by subjectname)(最高分---分数比我高的学生的人数=0)select a.studentid "学号",studentname "学生姓名",subjectname "课程名称",mark "成绩"from student a,subject b,grade cwhere a.studentid = c.studentidand b.subjectid = c.subjectidand (select count(*) from gradewhere subjectid = b.subjectid andmark > c.mark) = 0第三问：查询出以下信息学号学生姓名课程名称成绩（成绩大于60时的显示及格，小于60时的显示不及格）select a.studentid "学号",studentname "学生姓名",subjectname "课程名称",decode(sign(mark-60),-1,'不及格','及格') "成绩"from student a,subject b,grade cwhere a.studentid = c.studentidand b.subjectid = c.subjectid第四问：查询出以下信息学号学生姓名（查询出课程超过1门以上学生的信息）select a.studentid "学号",studentname "学生姓名",count(subjectname)from student a , subject b , grade cwhere a.studentid = c.studentidand b.subjectid = c.subjectidgroup by a.studentid,studentnamehavingcount(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,locfrom 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 * fromtest;二、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 seq2start with 1000 --起始值1000 1000开始increment by 2 --步长2 每次增加2maxvalue 9000 --最大值9000 一直到9000minvalue 900 --最小值900 回到900 重新开始cycle --循环序列。