oracle基础操作

--2013-19
create tablespace myspace
datafile 'c:\muyspace.dbf' size 10 M autoextend on;

alter tablespace myspace add datafile 'c:\muyspace2.dbf' size 10M autoextend on;

--新建一个用户
CREATE USER cssp
IDENTIFIED BY cssp1234
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;

select * from dba_users;
select * from dba_users where username='cssp';

grant connect to cssp;
grant resource to cssp;
grant create sequence to cssp;

alter user cssp quota unlimited on users;

grant select on test to cssp;
grant update on test to cssp;
grant all on test to cssp;

--2013-01-20
--第二天
select sysdate from dual; --当前系统时间

 

--学生信息表
create table cssp.students
(
 studentsID number primary key,
 SNme char(10) not null,
 SAddress varchar(50),
 SGrade float,
 SEmail varchar(50),
 SSex number
);

 

 

--插入学生信息表
insert into cssp.students values(1,'张三','湖南长沙',3,'zhangsan@cssp.com',1);
insert into cssp.students values(2,'李四','湖南浏阳',3,'lisi@cssp.com',0);
insert into cssp.students values(3,'王五','湖南湘西',3,'wangwu@cssp.com',1);
insert into cssp.students values(4,'赵六','湖南邵阳',3,'zhaoliu@cssp.com',0);

 

 --查询表信息
 select * from cssp.students;
 
 
 --科目表
 create table cssp.course
 (
    courseid int primary key,
    coursename varchar(50)
 );
 
 
 --科目表插入
insert into cssp.course values(1,'语文');
insert into cssp.course values(2,'数学');
insert into cssp.course values(3,'英语');

 select * from cssp.course;
 
 --成绩表
 create table cssp.scoure
(
    scoureid number primary key,      --主键
    studentid number not null,        --学号
    courseid number,                  --科目
    scoure number            
   
 ); 
 --成绩插入
 --语文
insert into cssp.scoure values(1,1,1,80);
insert into cssp.scoure values(2,2,1,70);
insert into cssp.scoure values(3,3,1,60);
insert into cssp.scoure values(4,4,1,56);
--补考
insert into cssp.scoure values(13,4,1,12);
 

 --成绩插入
 --数学
insert into cssp.scoure values(5,1,2,70);
insert into cssp.scoure values(6,2,2,90);
insert into cssp.scoure values(7,3,2,40);
insert into cssp.scoure values(8,4,2,36);

--成绩插入
 --英语
insert into cssp.scoure values(9,1,3,67);
insert into cssp.scoure values(10,2,3,50);
insert into cssp.scoure values(11,3,3,70);
insert into cssp.scoure values(12,4,3,86);

--更新数据
update cssp.scoure set scoure=40 where scoureid=7;
 

 

--删除数据
delete cssp.scoure where scoure=40;

select * from cssp.scoure;

--加外键约束
alter table cssp.scoure
add constraint fk_student_id
foreign key(studentid)
references cssp.students;

 

 

alter table cssp.scoure
add constraint fk_course_id
foreign key(courseid)
references cssp.course;

 

--伪列
select a.* ,rowid,rownum from cssp.students a;

--查询前两笔数据
select * from cssp.students where rownum <=3;

--利用现表建新表
create table cssp.students_new
as
select * from cssp.students;

select * from cssp.students_new;

--复制表结构
create table cssp.students_new
as
select * from cssp.students where 1=2;

--删除表
drop table cssp.students_new;

 

 

--2013-22
--去重
select  distinct studentid from cssp.scoure where courseid=1;

--不去重
select  studentid from cssp.scoure where courseid=1;

--别名
select  studentid as 学号, sname as 姓名 from cssp.students;

select * from cssp.students;

--增加日期字段
alter table cssp.students add birthday date;

--默认日期格式
insert into cssp.students(studentid,sname,birthday) values(5,'老七','1-1月-91');

--TO_DATE函数
insert into cssp.students(studentid,sname,birthday)
values(6,'老八',to_date('1992-01-11','YYYY-MM-DD'));

--不转换 报错:文件与格式字符串不匹配。
insert into cssp.students(studentid,sname,birthday)
values(6,'老八','1992-01-11')

select * from CSSP.students;

--从现有的表中提取数据
--新建一个表
create table scoure_new as
select* from  cssp.scoure where 1=2;

--提取科目1的成绩插入到scoure_new中
insert into scoure_new
select* from  cssp.scoure
where courseid=1;

select * from CSSP.scoure;
select * from scoure_new;

--事务控制
savepoint make1; --回滚点1
delete from cssp.students where studentid=5;
savepoint make2 ;--回滚点2
delete from cssp.students where studentid=6;
rollback to savepoint make2;--回滚
commit;               --提交

 

select * from CSSP.students;

--执行算数操作符+ - * /
--新建一个表
create table test(
a int,
b int
);
--插入数据
insert into test values(20,50);
insert into test values(10,50);

select * from test;
--列
select a.*,a+b,a-b,A*B,A/B FROM test a;

--常量
select a.*,a+100,a-50,A*12,A/10 FROM test a;

--比较操作符
--..省略
--逻辑操作符
--..省略 自己去看看啊