oracle学习总结-1(一)

1 创建表空间及临时表空间
create tablespace csdn1 datafile 'csdn1' size 30m autoextend on;
create temporary tablespace csdn2 tempfile 'csdn2' size 30m autoextend on;

2 创建用户指定表空间及临时表空间
create user csdn identified by csdn default tablespace csdn1 temporary tablespace csdn2;

3 授予用户各种权利
grant create session to csdn;
grant unlimited tablespace to csdn;
grant connect to csdn;
grant resource to csdn;
grant create sequence to csdn;
grant create table to csdn;

4 查询当前用户的权限
select * from user_sys_privs;

5 撤销用户各种权限
revoke create table from csdn;
revoke create session from csdn;
revoke create sequence to csdn;
revoke resource to csdn;
revoke connect to csdn;
revoke unlimited tablespace to csdn;

6 通过角色来赋予用户各种权限
create user root identified by root default tablespace csdn1 temporary tablespace csdn2;
create role role1;
grant create table to role1;
grant create session to role1;
grant con





nect to role1;
grant resource to role1;
grant create sequence to role1;

(1) 将角色赋予给用户
grant role1 to root;

(2) 删除角色
drop role role1;

7 序列
create sequence xulie
minvalue 1
maxvalue 222222
start with 1
increment by 1
nocache
nocycle

----csdn Login---------
1 创建表
drop table tb_book;
create table tb_book
(
book_id int primary key not null,
book_name varchar(32) not null,
book_des varchar(100) not null
);

2 通过序列来插入数据
insert into tb_book(book_id,book_name,book_des) values(xulie.nextval,'计算机科学与技术','计算机');
insert into tb_book(book_id,book_name,book_des) values(xulie.nextval,'信息管理技术','信管');
insert into tb_book(book_id,book_name,book_des) values(xulie.nextval,'专业英语','外语');
insert into tb_book(book_id,book_name,book_des) values(xulie.nextval,'土木工程建设','土木');
select * from tb_book;

3 创建学生表
create table tb_student
(
stu_id int primary key not null,
stu_name varchar(32) not null,
stu_sex char(2) not null check(stu_sex in('男','女')),
stu_age int not null
);

4 更改表的别名
rename tb_student to tb_stu;

5 创建借书记录表
create table tb_borrow
(
borrow_id int primary key not null,
stu_id int not null,
book_id int not null
);
rename tb_borrow to tb_j;
alter table tb_j add constraint b_b foreign key(book_id) references tb_book(book_id);
alter table tb_j add constraint b_s foreign key(stu_id) references tb_stu(stu_id);



6 查询语句
列出所有借书的记录号 书名 借书的人名
select j.borrow_id,s.stu_name,b.book_name from tb_stu s,tb_book b,tb_j j where s.stu_id=j.stu_id and b.book_id=j.book_id;

列出同一个专业 但是借了不同一本书的学生
select s.zhuanye,s.stu_name,b.book_name from tb_book b,tb_stu s order by s.zhuanye,b.book_name;

7 数值函数
select ceil(13.2) from tb_stu; --向上取整
select floor(12.9) from tb_stu;--向下取整
select power(9,19) from tb_stu;--9的19次方
select sqrt(8) from tb_stu;    --8的平方根
select sign(-2) from tb_stu;   --正数返1 负数返-1 0返0
select trunc(12.232323123,5) from tb_stu;--取5位小数
select round(1232.343,2) from tb_stu;--小数位为2 四舍五入
select exp(3) from tb_stu; --求指数
select mod(12,8) from tb_stu; --求余数
select ln(10) from tb_stu;--自然对数
select log(10,100) from tb_stu;--以10为底 100的对数
select vsize(1