update (oracle的更新)
表空间:
create tablespace ts1 datafile 'd:\java\ts1.dbf size 50M autoextend on next 10M maxsize 1024M;
不可省略!
alter database default tablespace ts1;修改用户默认表空间(可在view中查看)
alter tablespace s1 rename to s2;
drop tablespace ts1 including contents and datafiles; (物理文件还在)
dual虚表:
查看时间,或者其他一些函数可以用dual虚表来直接查看:
select sysdate from dual;
序列:生成唯一的主键值
create sequence user_squ;
select user_squ.nextval from dual;
insert into table values(user_squ.nextval,'java');
(Ps:序列是oracle数据库的一个对象,他是单独的!)
select job,count(ename) as sum from emp group by job having count(ename)>3;
字符串类型:
char固定大小char(10); 查询快但是耗费空间
varchar2(20)可变大小;
select length(userName) as cahrlength,length(password) as varchar2length from table; 查询当前类型的长度
Number:
在设置表格的Number类型的 数据时可选俩个属性(precision, scale)
精度 小数位数(正小数,负整数位为0)
日期date:date timestamp
系统默认日期:sysdate systimestamp
insert into d values(1,sysdate,systimestamp);
设置格式:select to_char(d2,'YY-MM-DD HH:MI:SS') FROM d;
在添加日期时设置的格式:update d set d3 = to_date('2016 12 20','YYYYMMDD');
PS:若YYYYMMDD之间没有连接符,日期之间只能用空格处理,如果有链接符号(任意)则日期任意分开都可以使用
select next_day(d1,7) from d ; ps:返回当前日期下下一个出现的周几所在日期:1=日 2=一。。。
其他函数:
rownum每次取的时候都是从1开始,如果条件为:where rownum>5--取第一条1!>5故去掉,取下一条,1!>5..无法取得数据
解决办法,取个别名:select * from (select a.*,rownum rn(select * from emp)a where rownum <10) where rn > 5;
PS:同一级别where不能使用别名来识别当前列:
select rownum rn from emp where rn < 10;//报错,需要封装:select * from (select a.*,rownum rn from a) where rn <10;
一条语句看破天地:select b.*,rownum from (select a2.*,rownum r from a2 where rownum <10) b where r >5; rownum和r的区别;
触发器:
语句触发器用于记录log:inserting、updating、deleting或者用于before验证权限;
行触发器用于维护数据;其中关键字为::old :new(注意冒号)
函数: PS: ‘=’是比较,譬如where id = xx; ‘:=’是赋值号
create function table_count(table_name varchar2) return number as
begin
declare num number;
s_sql varchar2(300);
begin
s_sql := 'select count(*) from ' || table_name;
execute immediate s_sql into num; //在此范围内begin end内部有可执行语句 跳出函数则错误
return num;
end;
end table_count;
set serverout on;
begin
dbms_output.put_line(table_count('book')); //输入的是字符''
end;
存储过程:
输入:in
create or replace procedure addbook2(BN in varchar2,typeid in number)as
begin
declare num number;
maxid number;
begin
select count(*) into num from book where name = BN;
if num > 0 then
return;
end if;
select max(id) into maxid from book;
insert into book values(maxid+1,BN,typeid);
commit;
end;
end addbook2;
调用:
execute addbook2('java',1);
有返回值的:out
create or replace procedure addbook3(bname in varchar2,id in number,num1 out number,num2 out number) as
begin
declare maxid number;
n number;
begin
select count(*) into n from book where name = bname;
if n > 0 then
return;
end if;
select max(id) into maxid from book;
select count(*) into num1 from book;
insert into book values(maxid+1,bname,id);
commit;
select count(*) into num2 from book;
end;
end addbook3;
调用:
declare n1 number;
n2 number;
begin
addbook3('java',1,n1,n2); //n1,n2为返回值
dbms_output.put_line(n1||n2);
end;
PS:若想begin内定义declre,需要在定义之后在begin(相当于declare 的范围是下一个begin);
PS:调用时参数必须加满!
PS:begin end; ========== execute -------execute addbook2('java',1) =====begin addbook2('java',1); end;
函数和存储过程的程序包:
//创建程序包
create package pc_book as
function book_count(table_name varchar2);
procedure addbook3(BN in varchar2,id in number,n1 out number,n2 out number);
end pc_book;
//创建程序包体
create package body pc_book as
function book_count(table_name varchar2) as
declare n number;
s_sql varchar2(300);
begin
s_sql := 'select count(*) from ' || table_name;
execute immediate s_sql into n;
return n;
end;
end book_count;
...
end pc_book;
//调用函数:
dbms_output.put_line(pc_book.book_count('book'));
//调用存储过程:
declare n1 number;
n2 number;
begin
addbook3('java',1,n1,n2);
dbms_output.put_line(n1||' '||n2);
end;
用户权限:
登陆高级管理员(sys/system)
创建用户: create user test identified by 123 default tablespace users;
给予登陆权限: grant create session to test; //拥有传播性with admin option
关闭用户/开启用户: alter user test account lock/unlock;
修改密码: alter user test identified by 321;
删除用户及信息: drop user test cascade;
grant select on aaa to test1 with grant option;
grant create table to test1 with admin option;
查询不同用户的表时:select * from sys.aaa;(首先得有权限);
PS:关键点:语法、回收、传播性(类似与系统权限create session 传播--with admin option);
(关于表、对象的操作:with grant option);
角色:相当于权限的集合
创建:create role role_aaa;
赋予权限:grant create session,all on aaa to role_aaa;
给用户赋予角色:grant role_aaa to test1;