--复制表
create table stuBak
as select * from stuInfo;
--复制表结构
create table stuBak2
as select * from stuInfo where 1=2;
--在已有的表结构中插入数据
insert into stuBak2
select * from stuBak;
update stuBak set s_sex=’男’;
savepoint mark;
rollback to savepoint mark;
rollback;
--给予其他用户权限
connect scott/tiger@itjob;
grant select on emp to yangrs; --只给查询
grant all on emp to yangrs --给所有的权限
connect yangrs/yangrs@itjob;
select * from scott.emp;
-- 取消权限
connect scott/tiger@itjob;
revoke select on emp from yangrs;
connect yangrs/yangrs@itjob;
select * from scott.emp; --此时已经连接不上去了
--伪列 rowid rownum
select rowid,rownum from stuInfo;
--用于分页
select * from (select rownum as num,stuInfo.* from stuInfo) where num>5;
--sqlserver中是使用top来分页
--哑元表
select sysdate from dual;
select systimestamp from dual;
--对表的修改
alter table stuInfo add(s_sal number(3));
--is null and is not null
select * from stuInfo where s_note is null;
select * from stuInfo where s_name like ’y%’; --%代笔任意个字符
select * from stuInfo where s_name like ’y_’; --—_代表一个字符
select * from stuInfo where s_name like ’y?’;
select * from stuInfo order by s_age desc; -- 排序
select * from stuInfo order by s_birthday asc;
select * from stuInfo order by s_age desc,s_birthday asc;
--可以有两个条件
--分组
select * from stuInfo where s_name<>’yangrs’;
select * from stuInfo where s_age=19;
select * from stuInfo where s_name<>’yangrs%’; --这样是不行的
--调用函数
select sum(s_sal) as 奖学金 from stuInfo;
select avg(s_age) 平均年龄 from stuInfo;
select s_name,s_age from stuInfo group by s_age;
select ’hell’||’loworld’ from dual;
select 1+1 from dual;
--转换大小写
update stuInfo set s_name=upper(s_name);
update stuInfo set s_name=lower(s_name);
--转换ascii码
select ascii(’A’) from dual;
select ’Hello’||’\t’||’World’ from dual;
select ’Hello’||chr(9)||’World’ from dual;
select to_char(sysdate,’yyyy/mm/dd hh24:mi:ss’) from dual;
select add_months(sysdate,-12) from dual;
-- 一年以前的今天
select last_day(sysdate) from dual;
select to_char(sysdate,’yyyy/mm/dd’) from dual; --改变日期格式
select to_char(to_date(’19990214’,’yyyymmdd’),’yyyy"我"mm"月"dd"日"’) from dual;
select to_char(to_date(’19990214’,’yyyymmdd’),’yyyy"我"mm"月"dd"日"’) from dual;
ref:http://www.zlksw.cn/html/jsj/Oraclerenzheng/xuexiziliao/200812/24-7834.html
posted on 2008-12-26 10:40 Tiger1102 阅读(447) 评论(0) 编辑 收藏 所属分类: 程序人生