orcle

ORCLE：

ORCLE数据库源：

司机：

oracle.jdbc.OracleDriver

简单查询：

select *列名（别名）from表名/ *注释* /去重（distinct）

网址：

jdbc.oracle:thin:@localhost:1521:orcl

连接查询：

选择'员工编号是' || EMPNO || '姓名是' ||为ename

用户名：

密码：

限定查询：

select * from emp  其中 sal> 1500且conn not为null

select * from emp其中sal> = 1500且sal <= 3000

命令常用操作：

1.登录

运行CMD进入命令行

select * from emp其中sal  在1500  和  3000 之间

sqlplus用户名/密码as sysdba

select * from emp where to getate in to_date（'1981-1-1'，'yyyy-MM-dd'）and

TO_DATE（ '1981年1月1日'， 'YYYY-MM-DD'）

SYS / SYS

斯科特/老虎

select * from emp where ename ='SMITH'sql语句不区分大小写，表中的值是区分

2.查看

显示用户

select * from emp where empno in（7369,7499,7521）（不在取反<>！=）

3.切换

conn用户名/密码as sysdba

select * from emp where ename  like'_M％'   （第二个字符是M的员工）

VARCHAR2

把空串当空处理

号（7,2）

总长度7，整数位5，小数位2

结果集排序：

select by from emp order by sal（默认正序asc，从高到低。倒序：desc）

四则运算* / + -

选择*来自emp命令由sal desc，hiredate asc;

单行函数：

上（''）

降低（''）

INITCAP（ ''）

concat（''，''）

通用函数：

NVL（COMM，0）

解码（1， '1'， '输出1'， '2'， '输出2'）

来自dual（）

大写转换

小写转换

首字大写

连接

COMM为空则用零代替

如果别的（）

伪表（获取值）

1当然输出1其他无止境的情况

长度（''）

替换（'hx'，'x'，'l'）

SUBSTR（ '你好'，1,3）

多表查询：

select * from emp e，dept d，其中e.deptno = d.deptno

长度

查询输出hl

索引从1开始

外连接：

（能保留一张表的数据是全量，即使这张表没有关联也会被查全）

SYSDATE

MOD（10,3）

ROUND（12.112,2）

TRUNC（12.87）

select * from emp e,dept d where e.deptno=d.deptno（+） （在右边就是右连接）

当前时期

取余 输出1

四舍五入 输出 12.11

切去小数 输出 12

select1999:

1.交叉连接：

cross join

会产生笛卡尔积

日期函数：

select ename trunc(sysdate - hirdate)/7 from emp (查询入职周数)

(了解)

2.自然连接：

natural join

自动按列名相等连接

select ename trunc(months_between(sysdate - hirdate)/7) from emp

3.using子句

select * from emp join dept using (deptno)

4.on子句

select * from emp e join dept d on e.d=d.d

当前日期加

select add_months(sysdate,-12) from dual

select * from emp e left join dept d on e.d=d.d

下个星期日的日期

select next_day(sysdate,'星期日'） from dual

本月最后一天

select last_day(sysdate） from dual

分组函数：

count（）

min()

max（）

avg（）

sum()

统计

最低

最大

平均

求和

转换函数:

to_char

年：y

yyyy

时：HH

字符串转换函数

月：m

mm

分：mi

分组统计：

group by

排序前

前面的查出的名称必须后面有分组

to_char(sal,'$99,999')

日：d

dd

秒：ss

后面有的列前面才能有，后面没有前面绝对不能有

分隔

having

分组后

select ename ,to_char(hiredate,'yyyy')  年，to_char(hiredate,'mm') 月 ，

to_char(hiredate,'dd') 日 ，to_char(hiredate,'HH') 时，

子查询：

查询结果可以被当成表或单个数据使用

to_char(hiredate,'mi')  分，to_char(hiredate,'ss') 秒 ，

from  emp;

exists

union

合并时不去重，列名可以不一致，列数一致

存在则为真

并集

select to_char(sysdate,'yyyy-mm-dd HH24:mi:ss') from dual;

select ename, to_char(hiredate,'fmyyyy-mm-dd ') from dual;

增删改：

insert into 表名（列名，...）[可以不写] vlues(,...)

字符串转数值

to_number

'10'

输出10

update 表名 set 表名.列名=    where 条件

字符串转时间

to_date

('1985-04-22 12:30:20','yyyy-mm-dd HH24:mi:ss')

dalete from 表名 where 条件[oracle可以不写from，mydql不行]

事务处理：

增删改都要开启事务，提交后数据才真正变更，提交前才能回滚。

rownum：

列名

（通过这个来分页，只能设置<，不支持>）

commit

rollback

rownum小于结束行号，把rownum作为结果集

提交

回滚

select * from(select rownum rw,a.* from(select * from emp) a where

修改时事务处于挂起状态，这条数据被锁住。

rownum<6) b where b.rw>0  (分页)

starNum =(pageNo-1)*pageSize   endNum=pageNo*pageSize+1

建表：

create table 表名（ 列名  属性（大小），...）

删表：

drop table 表名

创建视图：

create or replace view 视图名称 as 子查询

添加列：

alter table 表名 add[modify 修改](列名 属性（大小）, ...)

修改视图会修改表的数据，不建议修改（+with read only 只读）

截断表：

truncate table 表名 （直接删除，谨慎使用）

序列：

不绑定某一张表，任何表都可以使用。插入一次自增一次

约束：

主键约束

primary key

加载属性后

select sequersonid.nextval from dual

非空唯一

查询序列的下一个值（重点）

自定义主键名称：

constraint 表名_列名_pk primary key(列名)

select sequersonid.currval from dual

非空约束：

not null

唯一约束：

unique

查看当前值是多少

自定义唯一约束：

constraint 表名_列名_uk primary key(列名)

创键序列：create sequence 序列名

检查约束：

check (列名 in(1,2))

自定义检查约束：

constraint 表名_列名_ck primary key(列名 in(1,2))

索引：

create index pname_index on person(pname);

大表才建

外键约束：

（删父表数据必须先删子表，删表也必须先删子表）

创建单例索引

层次<4

constraint 表名_列名_fk foreign key(列名) references 另张表名（列名）

create index pg_index on person(pname,gender);

强制删除表：

drop table 表名 cascade constraint  （不建议这样删除）

创建复合索引（使用时有顺序，相反时不调用索引）

设置后删父子表一并(级联)删除：

references 表名（列名） on delete cascade

select * from person t where t.pname= and t.gender=;

使用索引

pl/sql程序语法：

declare:

if分支：

into 赋值

说明部分

（变量说明，游标申明，例外说明）

1'

declarce:

pno number(4):=&num;

：= 赋值

begin:

begin

if pno <5 then

语句序列

（DML语句）

dbms_output.put_line('编号小于5');

exception:

end if;

例外处理语句

exception

end;

end;

基本数据类型：

2'

declarce:

pno number(4):=&num;

declarce:

pname varchar2(10) ; age number(3):=20;

begin

if pno =1 then

begin：

pname:='zs';dbms_output.put_line('hello world');

dbms_output.put_line('我是1');

exception

else

end;

dbms_output.put_line('我不是1');

引用数据类型：

end if;

declarce:

Myname emp.ename%type;

exception

begin：

select t.name into pname from myemp t where t.empno=7369;

end;

dbms_output.put_line(pname);(打印)

3'

declarce

pno number(4):=&num;

exception

begin

if pno =1 then

end;

dbms_output.put_line('我是1');

记录类型变量：

对应java中的对象类型

elseif pno=2 then

declarce:

Myname emp.ename%rowtype;

dbms_output.put_line('我是2');

begin：

select t.name into prc from myemp t where t.empno=7369;

else dbms_output.put_line('其他');

dbms_output.put_line(prc.ename|| ''||prc.sal);

end if;

exception

exception

end;

end;

loop循环：

游标Cursor：

cursor 游标名 [(参数名 数据类型，...)] is select 语句

declarce total number(4):=0;

例如：

cursor c1 is select ename from emp;

1' begin

while total<25000 loop

打开游标： open c1;(打开游标查询)

...

会向下移动

取一行游标的值： fetch c1 into pjob;(取一行到变量中)

total :=total +salary;

关闭游标： close c1;(关闭游标释放资源)

dbms_output.put_line(total);

游标结束方式 ： exit when c1%notfound

end  loop

注意：上面的pjob必须与emp表中的job列类型一致

end;

定义：pjob emp.empjob%type;

带有参数游标

declarce

2' begin

loop

cursor c1(dno myemp.deptno%type) is

常用

exit when total =100;

select * from myemp t where t.deptno=dno;

tatal:=total+1;dbms_output.put_line(total);

prec myemp%rowtype;

end  loop;

begin  open c1(10);     loop  fetch c1 into prec;

exit when c1%notfound;

3’begin

for total in 1..100 loop

update myemp t set t.sal=t.sal+1000 where t.empno=

适合连续数值遍历

dbms_output.put_line(total);

prec.empno;   end loop;  close c1;

end  loop;

commit; end;

例外：

存储过程：

create [or replace] procedure   过程名   [(参数名 in/out

对应java异常

no_data_found (没有找到数据)

创建

数据类型)]     as begin     PLSQL子程序体;     end;

too_many_rows（select ...into语句匹配多个行）

对应java类

create [or replace] procedure   helloword  as  begin

zero_divide （被零除）

dbms_ouput.put_line('hello world');   end;

value_error （算术或转换错误）

timeout_on_resource （在等待资源时发生超时）

create or replace procedure addsal(pno in myemp.empno%type)  as

declarce

pnum number;

prec myemp%rowtype;

begin

pnum :=1/0; 发生异常

begin  select * into prec from myemp t where t empno=pno;

exception

when zero_divide then  捕捉异常

dbms_output.put_line('涨前工资是'||prec.sal||'涨工资后是：'||

dbms_output.put_line('被0除');

（prec.sal+100）);       end; （带输入参数的存储过程）

end;

事务的提交要在调用端做

自定义例外：

declarce prec emp%rowtype; cursor c1 is

create or replace procedure addsal(pno in emp.empno%type,ysal out

select * from emp t where t.deptno=50;

number)              psal  emp.sal%type;   pcomm  emp.comm%type;

no_data exception;(自定义)

begin  select t.sal ,t.comm into psal,pcomm from emp t where

begin open c1 ;loop fecth c1 into prec;

t.empno=pno;        ysal:=psal*12+nvl(pcomm,0);     end countYSal;

if c1%notfound then rasie no_data;(抛出异常)

(既有输出又有输入的存储过程)

end if; end loop; close c1;

exception  when no_data then

用pl/sql调用： declare ysal number; begin countdsal(7369,ysal);

dbms_output.put_line('没有员工');    when others then

dbms_output.put_line(ysal);     end;   （把对应7369的值给ysal）

DBMS_OUTPUT.PUT_LINE（ '其他异常'）; 结束;

[可以在数据库的程序直接创建模版]（过程跟函数建议使用过程）

存储函数：

创建或替换funtion函数名（名称类型，名称

in type）return数据类型是结果变量数据类型;

begin return（结果变量）; 结束函数名;

（函数有返回值，过程没有，但过程可以从多个参数值）

：

创建或替换函数countysalf（emp.empno％type中的pno）返回编号是   结果编号;   psal emp.sal％type;   pcomm emp.comm％type; 开始   选择t.sal，t.comm为psal，pcomm来自emp t，其中t.empno = pno;  结果：= psal * 12 + nvl（pcomm，0）;  返回（结果）; 结束countysalf;

程序体调用过程

以及函数：