sta 年龄 res 删除记录 增长 学生成绩 默认 字符
首先建表:
--学生表(学号,姓名,性别,出生日期,年级,电话,家住地址)create table student( stuno number(5,0) primary key, --学号 stuname varchar2(30) not null, --姓名 sex char(2) default(‘男‘) check(sex=‘男‘ or sex=‘女‘) not null , birthday date not null, gradeid number(5,0) not null, moible varchar2(15) not null, address varchar2(50) null)--年级表(年级编号,年级名称)create table grade( gradeid number(5,0) primary key, --年级编号 gradename varchar2(30) not null --年级名称)
--课程表(课程编号,课程名,所属的年级,学时)create table course( courseid number(5,0) primary key, --课程编号 coursename varchar2(30) not null,--课程名称 gradeid number(5,0) not null ,--所属年级编号 hours number(3,0) default(0) --学时)--成绩表(学号,课程编号,成绩)create table chenji( stuno number(5,0) not null , --学号 courseid number(5,0) not null ,--课程号 score number(4,1) check(score<=100 and score>=0))
select * from grade;select * from chenji;select * from course;select * from student;
--学生表关联到年级表 学生表从表,年级表是主表--增加外键关联 alter table student add constraint FK_stu_grade foreign key (gradeid) references grade(gradeid) --课程表关联到年级表 课程表是从表,年级表是主表 alter table course add constraint FK_course_grade foreign key (gradeid) references grade(gradeid) --主键约束,给成绩表增加主键约束 alter table chenji add constraint PK_chenji primary key(stuno,courseid)--成绩表中的学号关联到学生表的学号,学生表是主表,成绩表是从表
alter table chenji add constraint FK_chenj_student foreign key (stuno) references student(stuno) --成绩表中的课程号关联到课程表中的课程号 alter table chenji add constraint FK_chenji_course foreign key (courseid) references course(courseid)
--添加默认值约束alter table student MODIFY (address default ‘深圳‘)--添加check约束alter table student add constraint CK_student_sex check(sex=‘男‘ or sex=‘女‘)--删除约束alter table student drop constraint CK_student_sex
create table gradeTable( gradeid number(3,0) primary key, gradename varchar2(20) not null)--建表时建外键create table test1( id number(3,0) primary key, name varchar2(20) not null, gradeid number(3,0) not null, foreign key (gradeid) references gradeTable(gradeid))
--创建序列create sequence grade_seq --产生一个从1开始每次增长1的一个序列--删除序列drop sequence grade_seq--创建序列-指定启始值,增长值,最大值create sequence grade_seqstart with 100increment by 1nomaxvalue--修改序列alter sequence grade_seqincrement by 1nomaxvalue--创建递减序列create sequence grade_seq2start with 100maxvalue 100increment by -1minvalue -999999999999
select * from grade--向年级表中插入数据insert into grade(gradeid,gradename) values (grade_seq.nextval,‘一年级‘);insert into grade(gradeid,gradename) values (grade_seq.nextval,‘二年级‘);insert into grade(gradeid,gradename) values (grade_seq.nextval,‘三年级‘);insert into grade(gradeid,gradename) values (grade_seq.nextval,‘四年级‘);insert into grade(gradeid,gradename) values (grade_seq.nextval,‘五年级‘);insert into grade(gradeid,gradename) values (grade_seq.nextval,‘六年级‘);insert into grade(gradeid,gradename) values (grade_seq.nextval,‘初一年级‘);insert into grade values (grade_seq.nextval,‘初二年级‘);
--学生表插入数据select * from student--创建学生表的序列create sequence stu_seqstart with 1000 increment by 1nomaxvaluecache 10;
select * from student--SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
insert into student values (stu_seq.nextval,‘张三‘,default,‘10-9月-1990‘,1,‘13211111111‘,‘深圳福田‘);insert into student values (stu_seq.nextval,‘李四‘,default,to_date(‘1990-10-22‘,‘YYYY-MM-DD‘),1,‘13211111111‘,‘深圳福田‘);insert into student values (stu_seq.nextval,‘王五‘,default,to_date(‘1991-2-12 10:30:10‘,‘YYYY-MM-DD HH:mi:ss‘),1,‘13211111111‘,‘深圳福田‘);insert into student values (stu_seq.nextval,‘赵五‘,‘女‘,to_date(‘1991-2-12 10:30:10‘,‘YYYY-MM-DD HH:mi:ss‘),1,‘13211111111‘,‘深圳福田‘);
insert into student values (stu_seq.nextval,‘张四‘,default,‘10-9月-1990‘,3,‘13211111111‘,‘深圳宝安‘);insert into student values (stu_seq.nextval,‘李四‘,‘女‘,to_date(‘1993-10-22‘,‘YYYY-MM-DD‘),4,‘13211111111‘,‘深圳罗湖‘);insert into student values (stu_seq.nextval,‘赵五‘,default,to_date(‘1996-2-12 10:30:10‘,‘YYYY-MM-DD HH:mi:ss‘),3,‘13211111111‘,‘深圳南山‘);insert into student values (stu_seq.nextval,‘赵六‘,‘女‘,to_date(‘1991-2-12 10:30:10‘,‘YYYY-MM-DD HH:mi:ss‘),5,‘13211111111‘,‘深圳福田‘);insert into student values (stu_seq.nextval,‘张三三‘,default,to_date(‘1996-2-12 10:30:10‘,‘YYYY-MM-DD HH:mi:ss‘),3,‘13211111111‘,‘深圳南山‘);insert into student values (stu_seq.nextval,‘张四四‘,‘女‘,to_date(‘1991-2-12 10:30:10‘,‘YYYY-MM-DD HH:mi:ss‘),5,‘13211111111‘,‘深圳福田‘);insert into student values (stu_seq.nextval,‘张王‘,default,to_date(‘1996-2-12 10:30:10‘,‘YYYY-MM-DD HH:mi:ss‘),3,‘13211111111‘,null);insert into student values (stu_seq.nextval,‘张六‘,‘女‘,to_date(‘1991-2-12 10:30:10‘,‘YYYY-MM-DD HH:mi:ss‘),5,‘13211111111‘,‘‘);
--向课程表中插入数据insert into course values(100,‘java‘,101,60);insert into course values(101,‘oracle‘,102,50);insert into course values(102,‘html‘,101,40);insert into course values(103,‘jQuery‘,102,48);
insert into course values(104,‘CSS‘,101,30);insert into course values(105,‘JS‘,104,48);insert into course values(106,‘SSH‘,106,80);insert into course values(107,‘uml‘,103,48);
--修改学号为1001性别为‘女‘update student set sex=‘女‘ where stuno=1001update student set sex=‘男‘,address=‘广州花都‘ where stuno=1001--删除性别为女的学生delete from student where sex=‘女‘--删除学生表中所有记录delete from student--truncate删除学生表中所有记录--delete 和truncate,drop异同--相同点:都可以删除表中记录--不同点:1,truncate不能接条件,将表中记录全部删除;而delete可以接where条件,可以条件删除 --2.truncate删除表中记录时,如果表中主外键关联,不论是否有关联的数据,均无法执行 delete 没有关联到 数可以删除,如果数据有关联到其他表,则悬赏从表记录在删除主标记录 --3.truncate删除表时,不会记录到日志,不能回滚,删除数据不可恢复,也不会触发表中删除数据的触发器,执行速度快 --delete 删除数据会记录到日志,可以回滚,可恢复,会触发表中删除记录的触发器,执行速度慢 --delete 和 truncate 只删除表中数据,表还在 --drop 指的是删除表,表中的约束都会删除 --可以用sys_guid() 或序列产生主键 select sys_guid() from dual; truncate table student; select * from student; insert into chenji values (1013,100,90);insert into chenji values (1013,102,80);insert into chenji values (1013,104,73);insert into chenji values (1015,100,91);insert into chenji values (1015,102,90);insert into chenji values (1020,104,98); insert into gradetable values (1,‘aa‘);insert into gradetable values (2,‘ada‘);insert into gradetable values (3,‘aad‘);insert into gradetable values (4,‘aaa‘);commit; truncate table gradetable;
drop table gradetable;
select grade_seq2.nextval from dual; delete from grade where gradeid>10;
select * from student; select * from grade;select * from course;select * from chenji;
--查询所有男生信息select * from student where sex=‘男‘;--查询家住地址是深圳南山的学生的学号,姓名,性别,生日select stuno,stuname,sex,birthday from student where address=‘深圳南山‘;--查询年级编号为1的深圳的女同学select * from student where gradeid=1 and address like ‘深圳%‘ and sex=‘女‘
--模糊查询 --like和not like --通配符%表示0个或多个任意字符 _表示一个任意字符 --查询学生表中姓张的同学的学号,姓名,性别。年龄 select stuno,stuname,sex,birthday from student where stuname like ‘张%‘ --查询学生表中姓张的同学的学号,姓名,性别,年龄,名字是三个字 select stuno,stuname,sex,birthday from student where stuname like ‘张__‘ --in和not in select * from student where stuno=1008 or stuno=1018 or stuno=1022 --等价于 select * from student where stuno in (1008,1018,1022) select * from course; --查询jvaa, oracle,jquery三门课的课程情况select * from course where coursename in(‘java‘,‘oracle‘,‘jQuery‘)
--查询不是java ,oracle,jQuery三门课的课程情况select * from course where coursename not in(‘java‘,‘oracle‘,‘jQuery‘)
--between ...and... --查询学时在30到60之间课程的信息[30,60] 相当于>=30 and <=60 select * from course where hours between 30 and 60 --等价于 select * from course where hours<=60 and hours>=30 --查询学生成绩不等90分的成绩信息 select * from chenji where score<>90.0 --null 和 not null --查询地址为空的学生的信息 select * from student where address is null; --查询地址部位空的学生的信息 select * from student where address is not null;
--聚合函数 max(),min(),avg(),count(),sum() --查询学时最多的那门课的信息 select * from course where hours=(select max(hours) from course) --查询学时最少的那门课的信息 select * from course where hours=(select min(hours) from course) select * from chenji; --课程编号为100这门课的平均分 select avg(score) from chenji where courseid=100 --所有课程的平均学时 select avg(hours) from course --求学号为1013这个学生的总分 select stuno,sum(score) from chenji where stuno=1013 group by stuno --注意:查询的列中除了聚合函数那一列的其它列都必须作为group by 的条件 --统计课程号为100这门课,成绩大于80分的个数 select count(*) from chenji where score>80 and courseid=100 --统计学生表中年级一年级的人数 --方法一 select count(*) from student where gradeid in (select gradeid from grade where gradename=‘一年级‘); --方法二 select count(*) from student s,grade g where s.gradeid=g.gradeid and g.gradename=‘一年级‘ --查询一年级学生的学号,姓名,性别,年级名称 select stuno as 学号,stuname 姓名,sex 性别,gradename 年级 from student s,grade g where s.gradeid=g.gradeid and g.gradename=‘一年级‘ --查询一年级的年级编号 select gradeid from grade where gradename=‘一年级‘
Oracle基础操作
来源: http://www.bubuko.com/infodetail-2081092.html