scott/tiger
system /manager
--创建一个临时表空间
create temporary tablespace bbb_temp
tempfile 'e:/bbb_temp.dbf'
size 10m
autoextend on ;
--创建一个用户默认的表空间
create tablespace bbb_data
logging
datafile 'e:/bbb_data.dbf'
size 10m
autoextend on ;
--创建一个用户并指定对应的表空间
create user bbb
identified by bbb
default tablespace bbb_data
temporary tablespace bbb_temp;
--授权
grant
create session , create any table , create any view , create
any index , create any procedure ,
alter any table , alter any procedure ,
drop any table , drop any view , drop any index , drop any
procedure ,
select any table , insert any table , update any table ,
delete any table
to bbb;
grant connect , resource to bbb;
--查询一个用户下面有哪些表
select * from user_tables;
--字符类型
char (10)固定长度的10个字符
varchar2 (10)可变长度的10个字符
long长文本
--数字类型
number (4,2)
--日期类型
date ,sysdate获取当前日期
--查询系统的当前时间,包含dual单行单列的表
select sysdate from dual;
17-11月-09
rowid
select rowid,ename from emp;
rownum
可以使用rownum来进行分页查询
select * from ( select a.*, rownum rn from
( select * from emp) a where rownum <= 10)
where rn >= 5;
--建表建约束
create table student
(
stuid number (4) constraint pk_stu_id primary key not null ,
stuname varchar2 (10) not null ,
stuage number (2) constraint ck_stu_age check (stuage between 16 and
45),
stuemail varchar2 (20),
stuaddress varchar2 (50)
);
--查看表结构
desc student;
--给stuemail添加检查约束
alter table student
add constraint ck_stu_email check (stuemail like '%@%' );
--给stuaddress添加默认值
alter table student modify(stuaddress default '荆州' );
--创建课程表
create table course
(
courseid number (2) not null ,
coursename varchar2 (10) not null
);
--给course表加主键
alter table course add constraint pk_course_id primary key (courseid);
--创建成绩表
create table score
(
scoreid number (4) constraint pk_score_id primary key not null ,
stuid number (4) not null ,
courseid number (2) not null ,
score number (4,2) not null constraint ck_score_score check (score between 0 and 100),
constraint fk_student_score foreign key (stuid) references student(stuid),
constraint fk_course_score foreign key (courseid) references course(courseid)
);
--创建三个序列
create sequence student_seq start with 1001 increment by 1;
create sequence course_seq start with 1 increment by 1;
create sequence score_seq start with 1 increment by 1;
--使用序列增加测试数据
insert into student values (student_seq.nextval, '刘德华' ,30, 'liu@163.com' , '沙市' );
insert into student values (student_seq.nextval, '张学友' ,28, 'zhang@163.com' , null );
insert into student values (student_seq.nextval, '范冰冰' ,18, 'fan@163.com' , '公安' );
insert into student values (student_seq.nextval, '张国荣' ,31, 'guorong@163.com' , '荆州' );
insert into course values (course_seq.nextval, 'java' );
insert into course values (course_seq.nextval, 'sql' );
insert into course values (course_seq.nextval, 'c#' );
commit ;
insert into score values (score_seq.nextval,1001,1,80);
insert into score values (score_seq.nextval,1001,2,54);
insert into score values (score_seq.nextval,1001,3,90);
insert into score values (score_seq.nextval,1002,1,34);
insert into score values (score_seq.nextval,1002,2,56);
insert into score values (score_seq.nextval,1002,3,78);
insert into score values (score_seq.nextval,1003,1,32);
insert into score values (score_seq.nextval,1003,2,78);
insert into score values (score_seq.nextval,1003,3,99);
commit ;
--查询java课程的总成绩
select avg (score) java平均成绩 from score where courseid=1;
--查询每个学员的三门课的平均成绩
select stuid 学员编号, avg (score) 平均成绩 from score group by stuid;
--按总分从高到低排列
select stuid 学号, sum (score) 总分 from score group by stuid order by 总分 desc ;
--查询每个学员的总成绩
select stuid 学号, sum (score) 总成绩 from score group by stuid;
--查询java成绩的前三名
select score 成绩 from score where courseid=1 and rownum<=3;
--查询地址为空的学员
select * from student where stuaddress is null ;
--查询没有参加c#(3)考试的学员id
select stuid from student where stuid not in ( select stuid from score where courseid=3);
--查询学员的sql成绩并显示学号、sql成绩、以及学员姓名
select stu.stuid 学号,sco.score 成绩,stu.stuname 姓名
from student stu inner join score sco
on (stu.stuid=sco.stuid);
--查询哪些学生没有参加考试
select stu.stuid 学号,sco.score 成绩,stu.stuname 姓名
from student stu left join score sco
on (stu.stuid=sco.stuid) where sco.score is null ;
--等价于
select stu.stuid 学号,sco.score 成绩,stu.stuname 姓名
from student stu,score sco where stu.stuid=sco.stuid(+) and sco.score is null ;
(+)在左边是右连接
--查询显示学员的java成绩，如果成绩<60分 显示’不及格’
60<=成绩<80 显示’三等’
80<=成绩<90 显示’二等’
90<=成绩 显示’一等’
set serveroutput on ;
declare
num number (4);
begin
num :=&请输入一个数;
case ( num )
when 0 then dbms_output.put_line( '不及格' );
when 1 then dbms_output.put_line( '三等' );
when 2 then dbms_output.put_line( '二等' );
when 3 then dbms_output.put_line( '一等' );
else dbms_output.put_line( '成绩不正确' );
end case ;
end ;
/
set serveroutput on ;
declare
javascore score.score% type ;
begin
--假设返回单条记录就可以接收，多条记录就报异常
select score into javascore from score where courseid=100;
if javascore<60 then
dbms_output.put_line( '不及格' );
elsif javascore between 60 and 79 then
dbms_output.put_line( '三等' );
elsif javascore between 80 and 89 then
dbms_output.put_line( '二等' );
elsif javascore between 90 and 100 then
dbms_output.put_line( '一等' );
else
dbms_output.put_line( '错误' );
end if ;
exception
when too_many_rows then
dbms_output.put_line( '返回行数不只一行' );
when no_data_found then
dbms_output.put_line( '找不到需要的数据' );
end ;
/
set serveroutput on ;
declare
javascore score.score% type ;
--1、定义游标
cursor score_cur is select score from score where courseid=1;
begin
--2、打开游标
open score_cur;
--3、从游标里面取数据
loop
fetch score_cur into javascore;
if javascore<60 then
dbms_output.put_line( '不及格' );
elsif javascore between 60 and 79 then
dbms_output.put_line( '三等' );
elsif javascore between 80 and 89 then
dbms_output.put_line( '二等' );
elsif javascore between 90 and 100 then
dbms_output.put_line( '一等' );
else
dbms_output.put_line( '错误' );
end if ;
--游标结束
exit when score_cur%notfound;
end loop ;
--4、关闭游标
close score_cur;
exception
when too_many_rows then
dbms_output.put_line( '返回行数不只一行' );
when no_data_found then
dbms_output.put_line( '找不到需要的数据' );
end ;
/
循环
set serveroutput on ;
declare
i number (4):=0;
begin
loop
i:=i+1;
dbms_output.put_line( 'accp' );
exit when i>10;
end loop ;
end ;
/
set serveroutput on ;
declare
i number (4):=0;
begin
while i<10 loop
i:=i+1;
dbms_output.put_line( 'accp' );
end loop ;
end ;
/
set serveroutput on ;
declare
i number (4):=0;
begin
for i in 1..10 loop
dbms_output.put_line( 'accp' );
end loop ;
end ;
/
--编写程序，显示从2到100之间的
set serveroutput on ;
declare
i number (4):=1;
j number (4):=1;
begin
loop
i:=i+1;
j:=1;
loop
j:=j+1;
exit when mod (i,j)=0;
end loop ;
if i=j then
dbms_output.put_line(i);
end if ;
exit when i>100;
end loop ;
end ;
/
--循环加分，java成绩普遍很低，现都给与加分，但是总分不能超过100分，直到所有人都及格，停止加分。
set serveroutput on ;
declare
num number (4):=1;
begin
while num >0 loop
update score set score=100 where score>=95 and courseid=1;
update score set score=score+5 where score<95 and courseid=1;
select count (*) into num from score where score<60 and courseid=1;
end loop ;
commit ;
end ;
/
--使用游标查询并显示学员信息
set serveroutput on ;
declare
stu_name student.stuname% type ;
stu_age student.stuage% type ;
--1、定义游标
cursor score_cur is select stuname,stuage from student;
begin
--2、打开游标
open score_cur;
--3、从游标里面取数据
loop
fetch score_cur into stu_name,stu_age;
dbms_output.put_line( '姓名' ||stu_name|| ',年龄' ||stu_age);
--游标结束
exit when score_cur%notfound;
end loop ;
--4、关闭游标
close score_cur;
exception
when too_many_rows then
dbms_output.put_line( '返回行数不只一行' );
when no_data_found then
dbms_output.put_line( '找不到需要的数据' );
end ;
/
set serveroutput on ;
declare
stu_row student%rowtype;
--1、定义游标
cursor score_cur is select * from student;
begin
--2、打开游标
open score_cur;
--3、从游标里面取数据
loop
fetch score_cur into stu_row;
dbms_output.put_line( '姓名' ||stu_row.stuname|| ',年龄' ||stu_row.stuage);
--游标结束
exit when score_cur%notfound;
end loop ;
--4、关闭游标
close score_cur;
end ;
/
--使用参数游标传递学员学号，查询与之相关的信息
set serveroutput on ;
declare
stu_row student%rowtype;
id student.stuid% type ;
--1、定义游标
cursor score_cur(stu_id student.stuid% type ) is select * from student where stuid>stu_id;
begin
--2、打开游标
id:=&输入学号;
open score_cur(id);
--3、从游标里面取数据
loop
fetch score_cur into stu_row;
dbms_output.put_line( '姓名' ||stu_row.stuname|| ',年龄' ||stu_row.stuage);
--游标结束
exit when score_cur%notfound;
end loop ;
--4、关闭游标
close score_cur;
end ;
/
--编写过程，接收学员的学号，求出此学员的三门课的平均成绩打印输出。
create or replace procedure
findstudent
(
stu_id number
)
as
stu_name student.stuname% type ;
begin
select stuname into stu_name from student where stuid=stu_id;
dbms_output.put_line(stu_name);
end ;
/
create or replace procedure findscore
(
stu_id number
)
as
cursor avg_score is select avg (score) from score where stuid=stu_id group by courseid;
score_avg score.score% type ;
begin
open avg_score;
loop
fetch avg_score into score_avg;
dbms_output.put_line(score_avg);
exit when avg_score%notfound;
end loop ;
close avg_score;
end ;
/
--编写过程，带输入参数学号和课程编号，输出参数为这个学生这门课的成绩，如果缺考就为0
create or replace procedure findscore
(
stu_id in number ,
course_id in number ,
outscore out number
)
as
score1 number (4);
begin
select score into score1 from score where stuid=stu_id and courseid=course_id;
outscore:=score1;
exception
when no_data_found then
outscore:=0;
end ;
/
调用
set serveroutput on ;
declare
stu_id score.stuid% type ;
course_id score.courseid% type ;
outscore score.score% type :=0;
begin
stu_id:=&输入学号;
course_id:=&输入课程编号;
findscore(stu_id,course_id,outscore);
dbms_output.put_line(outscore);
end ;
/
--编写函数，接收课程的编号，求出这门课的总成绩并返回，调用此函数。
create or replace function
findtotalscore(course_id number )
return number
as
stotal number (4);
begin
select sum (score) into stotal from score where courseid=course_id;
return stotal;
end ;
/
select findtotalscore(1) from dual;
set serveroutput on ;
declare
totalscore number (4);
begin
totalscore:=findtotalscore(&请输入课程号);
dbms_output.put_line(totalscore);
end ;
/
189
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
