oracle中的sql语句，对数据库的完全操作

 --创建表空间
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;