随笔 - 44  文章 - 78  trackbacks - 0
<2008年12月>
30123456
78910111213
14151617181920
21222324252627
28293031123
45678910

 Happy 牛 Year
一、一周至少写一篇博文;
二、每天至少学习半个小时。
三、奔向小牛!

常用链接

留言簿(6)

我参与的团队

随笔分类

随笔档案

文章档案

搜索

  •  

最新评论

阅读排行榜

评论排行榜

     --创建表空间
  create tablespace yyspace
  datafile ’d:\yyspace.dbf’
  size 10m
  autoextend on;
  --创建临时表空间
  create temporary tablespace yytemp
  tempfile ’d:\yytemp.dbf’
  size 10m
  autoextend on;
  --创建用户
  create user yangrs
  identified by yangrs;
  alter user yangrs
  default tablespace yyspace;
  alter user yangrs
  temporary tablespace yytemp;
  --赋权
  grant connect,resource to yangrs
  --connect
  connect yangrs/yangrs;
  --建表
  --删表
  drop table stuInfo
  create table stuInfo
  (
  s_id number(4),
  s_name varchar2(10),
  s_sex char(2),
  s_age number(3),
  s_birthday date default(sysdate),
  s_note varchar2(50)
  );
  create table stuScore
  (
  stuid number(4),
  scoreid varchar2(10),
  score number(3)
  );
  drop table stuScore;
  --加约束
  --主键
  alter table stuInfo
  add constraint PK_s_id primary key(s_id);
  --检查
  alter table stuInfo
  add constraint CK_s_sex check(s_sex in (’男’,’女’));
  alter table stuInfo
  add constraint CK_s_age check(s_age>0 and s_age<100);
  --加默认的不行
  alter table stuInfo
  add constraint DK_s_birthday default(systimestamp );
  --外键
  alter table stuScore
  add constraint FK_stuid foreign key(stuid) references stuInfo(s_Id);
  --insert
  insert into stuInfo(s_id,s_name,s_age,s_Sex,s_Note) values(1000,’刘德华’,20,’男’,’just do it’);
  insert into stuInfo(s_Id,s_name,s_age,s_sex,s_birthday,s_note) values(1001,’yangrs’,19,’男’,to_date(’1989-01-01’,’yyyy-mm-dd’),’i am what i am’);
  insert into stuInfo(s_Id,s_name,s_age,s_sex,s_birthday,s_note) values(1002,’yangrs2’,19,’男’,to_date(’1989-01-01’,’yyyy-mm-dd’),’i am what i am’);
  insert into stuInfo(s_Id,s_name,s_age,s_sex,s_birthday,s_note) values(1003,’yangrs3’,19,’男’,to_date(’1989-01-01’,’yyyy-mm-dd’),’i am what i am’);
  insert into stuInfo(s_Id,s_name,s_age,s_sex,s_birthday,s_note) values(1004,’yangrs4’,19,’男’,to_date(’1989-01-01’,’yyyy-mm-dd’),’i am what i am’);
  insert into stuInfo(s_id,s_name,s_age,s_Sex,s_Note) values(1005,’华仔’,20,’男’,’just do it’);  
  insert into stuScore(Stuid,scoreid,Score) values(1001,’1’,100);
  insert into stuScore(Stuid,scoreid,Score) values(1001,’1’,100);
  insert into stuScore(Stuid,scoreid,Score) values(1000,’1’,100);
  insert into stuScore(Stuid,scoreid,Score) values(1000,’1’,100);

  --复制表
  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 阅读(502) 评论(0)  编辑  收藏 所属分类: 程序人生

只有注册用户登录后才能发表评论。


网站导航: