oracle增删改查语句6,oracle从建表到增删改查

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 connect 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(1010) from tb_stu;–返回1010所占空间的大小

8 常用的函数

select initcap(stu_name) from tb_stu;–首字符转换成大写

select stu_name,instr(stu_name,’s’) from tb_stu;–查找s在stu_name中的位置 返回相应的值(0 n)

select length(stu_name) from tb_stu;–返回长度

select upper(stu_name) from tb_stu;–换大写

select lower(stu_name) from tb_stu;–换小写

select lpad(stu_name,11,’Hello’) from tb_stu;–长度不够则在左边填充Hello 直到够11