Oracle(一)
-----查询
-----查询显示学号， 学员姓名，学的成绩
-----查询显示比某一个学员年龄大其他的学员信息
-----获得100105的学员的年龄
----子查询
select * from student where stuage>(select stuAge from student where stuno=100105)
----笛卡尔积
select
student.stuNo,stuName,wirtesoc ,courseNo,labsor, student.stuno,score.stuno
from student,score where student.stuno=score.stuno
-------------------------------------------------------------
--查询显示学号， 姓名，学员参加考试的科目名称 ，学的成绩，
select stuNo,stuName,ce.coursename,wirtesoc ,labsor
from
(
select student.stuNo,stuName,courseNo, wirtesoc ,labsor
from student,score where student.stuno=score.stuno
) ss
,course ce where ss.courseNo = ce.courseno
-------------------------------------------------------------
select * from student;
select * from score;
select * from course
--------------------------------------------------------------
----日期数据类型的默认格式为“DD-MON-RR”
-----修改系统的默认日期格式
alter session set nls_date_format ='yyyy-mm-dd';
insert into student (stuNo,stuName,stuAGE,stuaddress,stuphone,stusex,stubirth)
values(100108,'奥特曼',22,'火星','14785236998','男','2010-10-10')
------------------------------------------------------------------
-------------------------连接查询----------------------------------
----内连接 inner join
select * from student where stuno in
(
select distinct stuNo from score
)
select distinct student.stuno,stuname,stuage,stuaddress,stuphone,stusex,stubirth
from score inner join student on student.stuno=score.stuno
---外连接
----左外连接 查询没有参加考试的学员信息
----左表中的数据全部显示，如果没有满足条件的数据用空填充 ，右表中满足条件的数据显示
select * from student
left join score on student.stuno=score.stuno where score.stuNo is null;
---------------------------------------------------------------------------
----右表中的数据全部显示，如果没有满足条件的数据用空填充 ，左表中满足条件的数据显示
select * from score right join student on student.stuno=score.stuno where score.stuNo is null
-----查询或的所有参加过考试的学员信息
select distinct student.stuNo,stuName,stuAGE,stuaddress,stuphone,stusex,stubirth from student right join score on student.stuno = score.stuno
-----------------------------------------------------------------------------------------
select student.stuNo,stuName,courseName,wirtesoc,labsor from
student inner join score on student.stuno=score.stuno
inner join course on course.courseno=score.courseno
----------------------------------------------------------
------------------------------------------------------------------------------------------
--------------------------------------oralce常用函数---------------------------------------
select initcap('hello') from dual; ----- 首写字母变为大写
select lower('AdminM') from dual; ----upper
select ltrim('admin hello',' admin') from dual -----rttim();
---select rtrim('lixiaolong',long) from dual
select translate('hello wangwu','w','l') from dual
select trim(' amin ') from dual
select lpad('hello',10,'x') from dual -----rpad
select replace('123 is not person','123','xxxx') from dual
select instr('hello admin','a') from dual
select substr('hello oralce',4,6) from dual
select length('admin') from dual
select decode(1,2,6,4) from dual
select concat('hello' ,'admin' )from dual
--------------------------------日期函数--------------------------------------
select add_months('2011-10-10',-10) from dual
update student set stuage=stuage+1,stubirth=(add_months(stubirth,12))
select add_months(stubirth,-12) from student
------查询年龄相差7岁以上的学员信息
select months_between('2008-11-27','2011-11-27') from dual
---select * from student where months_between(stubirth,stubirth)>7
select * from scott.emp where months_between('1981-12-31',hiredate)>=12 ;
-------------------------------------------------------------------------
select last_day('2010-10-12') from dual
----0----6
----1----7
select next_day(sysdate,2) from dual
select TRUNC(to_date('2010-10-10','yyyy-mm-dd')) from dual;
select sysdate from dual;
select extract(day from sysdate) from dual
---------------------------------------------------------------------------------
--------------------------------------数字函数---------------------------------------
select abs(-1) from dual;
select ceil(10.24) from dual
select floor(10.24) from dual
select mod(4,3) from dual
select round(10.276,2) from dual
---SELECT TO_CHAR(itemrate,‘C99999’) FROM itemfile;
---select to_char(stuAge,'C999') from student
-----------------------------------------------------------------------------------
--------------------------类型转换函数------------------------------------
--to_char()
--to_date()
--to_number()
select stuName||' '||stuaddress from student
select to_char(stuno,'L9999,00') from student
select to_char(stuno,'c999999') from student
select to_number('123')+1 from dual
select to_date('2010-10-11','yyyy-mm-dd') from dual
----------------------------------
select nvl(stuaddress,'xxx') from student
select nvl2(stuaddress,'北京','西安') from student -----stuaddress is null result is exp3 else result is exp2
----------------------------------------------------------------------------------------
----同义词
----scott---emp
select * from scott.emp
----给student表创建同义词
----private
create synonym show_student for admin.student
----public
create or replace public synonym show_student2 for admin.student
---删除同义词
drop synonym show_student
select * from admin.student
select * from show_student
-----创建序列
create sequence inc_stuNo
----初始值
start with 100110
----每次增长多少
increment by 1
----最大值
maxvalue 1000000
minvalue 100110
nocycle
cache 100110
---
insert into student (stuno,stuname,stuage,stuaddress,stuphone,stusex,stubirth)
values(inc_stuno.nextval,'test',23,'test0','14785236698','男','2011-1-1')
--------------------------------
select inc_stuno.nextval from dual
---视图 view-----------------------------------------------------------------------------
select stuNo,stuName,ce.coursename,wirtesoc ,labsor
from
(
select student.stuNo,stuName,courseNo, wirtesoc ,labsor
from student,score where student.stuno=score.stuno
) ss
,course ce where ss.courseNo = ce.courseno
---创建视图
create or replace view show_join_exam
as
select stuNo,stuName,ce.coursename,wirtesoc ,labsor
from
(
select student.stuNo,stuName,courseNo, wirtesoc ,labsor
from student,score where student.stuno=score.stuno
) ss
,course ce where ss.courseNo = ce.courseno
select * from show_join_exam
drop view show_join_exam
----SELECT itemdesc, NVL2(re_level,re_level,max_level)
---FROM itemfile;
select nvl2('q' ,6,1) from dual
select * from student;
select * from score;
select * from course
----ddl
---dml
---dcl
---tcl
-----------------------------------pl/sql-----------------------------------
---pl中的语句块
declare
stuAge CONSTANT number(3):=20; ----- stuAge CONSTANT number(3):=20; 声明常量
stuName varchar2(20); ----声明变量
begin
--tuAge:=10;
stuName:='李四';
dbms_output.put_line(stuAge);
dbms_output.put_line(stuName);
end;
-----------------------------------------------------
begin
insert into student (stuno,stuname,stuage,stuaddress,stuphone,stusex,stubirth)
values(inc_stuno.nextval,'浏览',22,'地方','74185296332','男',SYSDATE);
end;
--------------------------------------------------------
declare
stuAge1 student.stuage%type;
stuName1 student.stuname%type;
stu student%rowtype;
begin
select stuAge ,stuName, stuAddress into stuAge1,stuName1,stu.stuAddress from student where stuNo=100100;
dbms_output.put_line(stuAge1);
dbms_output.put_line(stuName1);
dbms_output.put_line(stu.stuAddress);
end;
------------------------------------pl/sql逻辑控制-------------------------
--if 条件 then
-- else
--end if;
--if 条件 then
-- else if 条件 then
-- else if 条件 then
-- else if 条件 then
--end if;
declare
age number(3):=50;
begin
--select avg(stuAge) into age from student;
if age<=30 then
--select * from student where stuAge<=30;
dbms_output.put_line(age);
else
dbms_output.put_line('else==='||age);
end if;
end;
---------------------------------------------------------
declare
avgjava number(3);
begin
select avg(labsor) into avgjava from score where courseNo =1;
if avgjava >=90 then
dbms_output.put_line('平局分为：'||avgjava||' 平均分等级A');
elsif avgjava>=80 then
dbms_output.put_line('平局分为：'||avgjava||' 平均分等级B');
elsif avgjava>=70 then
dbms_output.put_line('平局分为：'||avgjava||' 平均分等级C');
else
dbms_output.put_line('平局分为：'||avgjava||' 平均分等级D');
end if;
end;
---------------------------------------------------------------------------
declare
i number(4):=1;
begin
loop
dbms_output.put_line('xxxx'||i);
i:=i+1;
if i>=10 then
exit;
end if;
end loop;
end;
declare
i number(4):=1;
begin
loop
dbms_output.put_line('xxxx'||i);
i:=i+1;
exit when i>=11;
end loop;
end;
---------------------------------------------------------------------------
declare
stuCount number(4):=0; ----保存没有几个人数
begin
select count(*) into stuCount from score where labsor<80;
while stuCount !=0 loop
update score set labsor=labsor+5;
select count(*) into stuCount from score where labsor<60;
end loop;
dbms_output.put_line(sql%rowcount);
end;
------------------------------------------------------------
declare
grade varchar2(2):='D';
res varchar2(30);
begin
case grade
when 'A' THEN res:='等级为A';
when 'B' THEN res:='等级为B';
when 'C' THEN res:='等级为C';
when 'D' THEN res:='等级为D';
when 'E' THEN res:='等级为E';
end case;
dbms_output.put_line(res);
end;
----------------------------------------------------
------------------------游标---------------------------
-----sql游标的名字
begin
update student set stuAge=23 where stuNo in(100100,100101) ;
--insert into student (stuno,stuname,stuage,stuaddress,stuphone,stusex,stubirth)
-- values(inc_stuno.nextval,'浏览',22,'地方','74185296332','男',SYSDATE);
if sql%isopen then
dbms_output.put_line('isopen');
end if;
if sql%found then
dbms_output.put_line('修改数据成功 '||sql%rowcount);
else
dbms_output.put_line('无受影响的行');
end if;
end;
---------------------------------------------------------
declare
stuAge1 number(4);
stuName1 varchar2(20);
cursor cur_stu is select * from student; -----声明游标
begin
open cur_stu;
fetch cur_stu into stuAge1,stuName1;
dbms_output.put_line(stuAge1||' '||stuName1);
end;
------------------------------------------------------
declare
stuName1 varchar2(50);
stuAge1 number(5);
cursor stu_cur is select stuName,stuAge from student;
begin
open stu_cur;
loop
fetch stu_cur into stuName1,stuAge1;
EXIT WHEN stu_cur%NOTFOUND;
dbms_output.put_line(stuName1||' '||stuAge1);
end loop;
end;
select * from student left join score on student.stuno=score.stuno where score.stuno is null
---查询显示 学好 姓名 笔试 机试 同时显示缺考还是没参加过考试
---缺考的人 score有学号但是成绩
--没参加考试人 score没有学号也没有成绩
declare
xxxx number(3);
begin
select score.stuno into xxxx from student left join score on student.stuno=score.stuno where student.stuno=100121;
if xxxx is null then
dbms_output.put_line('xxxxx');
end if;
end;
---------------------------------------存储过程------------------------------------------------------
/**
CREATE [OR REPLACE] PROCEDURE
<procedure name> [(<parameter list>)]
IS|AS
<local variable declaration>
BEGIN
<executable statements>
[EXCEPTION
<exception handlers>]
END;
*/
----输入参数-----in
----输出参数-----out
create or replace procedure proccount_pass(wirteExam number,labExam number)
as
isPass number(3);
begin
select count(*) into isPass from score where (wirteSoc>=wirteExam and labSor>=labExam) and courseNo=1;
---dbms_output.put_line(isPass);
end;
-------------------------------
create or replace procedure proccount_pass2(wirteExam number,labExam number, isPass out number)
as
-- isPass number(3);
begin
select count(*) into isPass from score where (wirteSoc>=wirteExam and labSor>=labExam) and courseNo=1;
---dbms_output.put_line(isPass);
end;
------------------------------------------------------------------------
declare
pass number(4);
begin
proccount_pass2(40,40,pass);
dbms_output.put_line('没通过考试的人数 '||pass);
end;
-------------------------------------------------------------------------
----------------------------------------------------------
create or replace procedure proc_insert
as
begin
insert into student (stuno,stuname,stuage,stuaddress,stuphone,stusex,stubirth)
values(inc_stuno.nextval,'浏览22ss',22,'地方','74185296332','男',SYSDATE);
end;
-----------------------------------------------
begin
proc_insert ;
end ;
----------------------------------------
select * from student
create or replace procedure proc_stu
as
stuNo1 number(6);
stuName1 varchar2(30);
writeExam number(3);
labExam number(3);
flag varchar2(30);
cursor stu_cur is select score.stuno,stuName,score.wirtesoc,score.labsor from student left join score on student.stuno=score.stuno ;
begin
open stu_cur;
loop
fetch stu_cur into stuNo1 ,stuName1, writeExam ,labExam;
exit when stu_cur%notfound;
if stuNo1 is null then
flag:='未参加考试';
---stuNo1:=888888;
end if;
if stuNo1 is not null and writeExam is null then
flag:='缺考';
end if;
dbms_output.put_line(nvl(to_char(stuNo1) ,' ')||' '||stuName1||' '|| writeExam ||' '||labExam||' '||flag);
end loop;
end;
begin
proc_stu;
end;
select * from score
---------------------------------------------------------------------------
SELECT * FROM STUDENT;
select * from score
------------------tcl----------------
select * from account
---给李四减去响应的余额
declare
flag number(3):=0;
flag2 number(3):=0;
begin
update account set cblance =cblance +1000 where cid =1001001000;
---dbms_output.put_line(flag);
update account set cblance =cblance -1000 where cid =1001001001;
/***
if sql%found then
flag:=flag+sql%rowcount;
end if;
if sql%found then
flag2:=flag2+sql%rowcount;
end if;
*/
---- flag:=flag+sql%rowcount;
end;
--------------------------------------------------------------------
---事务：程序的某个操作需要分若干个步骤完成----对于若干个步骤要么同时成功 要么同时失败
