Oracle数据库操作用例总结

• Oracle数据库控制

1. 新建Oracle用户userA，密码设为abc

create user userA identified by abc;

2. 给userA用户授权角色

CONNECT角色： --是授予最终用户的典型权利，最基本的权力，能够连接到ORACLE数据库中，并在对其他用户的表有访问权限时，做SELECT、UPDATE、INSERTT等操作。
ALTER SESSION --修改会话
CREATE CLUSTER --建立聚簇
CREATE DATABASE LINK --建立数据库链接
CREATE SEQUENCE --建立序列
CREATE SESSION --建立会话
CREATE SYNONYM --建立同义词
CREATE VIEW --建立视图
RESOURCE角色： --是授予开发人员的，能在自己的方案中创建表、序列、视图等。
CREATE CLUSTER --建立聚簇
CREATE PROCEDURE --建立过程
CREATE SEQUENCE --建立序列
CREATE TABLE --建表
CREATE TRIGGER --建立触发器
CREATE TYPE --建立类型
DBA角色，是授予系统管理员的，拥有该角色的用户就能成为系统管理员了，它拥有所有的系统权限

grant connect,dba,resource to userA;

3. 将userB用户下的表tableB的增删改查的权限授权给userA

grant insert,delete,update,select on  userB.tableB to userA;

4. 删除用户userA

drop user userA cascade; //加上cascade则将用户连同其创建的东西全部删除

5. 修改用户userA的密码为123

alter user userA identified by 123;

• Oracle数据库单表查询

1. 查询数据库tableA表中的所有记录时：

select * from tableA;

2. 查询数据库tableA表中id为1的记录时（精确查询）：

select * from tableA where id = '1';

3. 查询数据库tableA表中id含有1的记录（模糊查询）：

select * from tableA where id like '%1%';

4. 查询数据库tableA表中的name字段，并且去重复：

select distinct name from tableA;

5. 查询数据库tableA表中的所有记录，按照workdate字段排序：
   需要注意：当需要多个字段排序时，需要在每个字段后面都加上asc或者desc；且多字段排序先排最左边的字段，再依次排右边的字段

升序排序：select * from tableA order by workdate;或者select * from table order by workdate asc;
降序排序：select * from tableA order by workdate desc;

• Oracle数据库单表插入记录

1. 往tableA表中插入一条新记录

--指定字段与值一一对应
insert into tableA (id,name,workdate,worktime)values('1','张三','20000101','160122');

--可行但不建议这样操作
insert into tableA values('1','2','3');

• Oracle数据库单表更新记录

1. 将tableA表中的name字段值为张三的记录更新，将张三更名为李四

update tableA set name='李四' where name='张三';

• Oracle数据库单表删除记录

1. 将tableA表中的name字段值为张三的记录删除

delete from tableA where name = '张三';

• Oracle数据库新增表

1. 新增tableA表

create table tableA(
id varchar2(10),
name varchar2(10),
workdate varchar2(8),
worktime varchar2(8)
);

• Oracle数据库修改表结构

1. 往tableA表中新加字段

新增一个字段birthday：alter table tableA add birthday varchar2(9);
新增两个字段birthday和address：alter table tableA add(birthday varchar2(9),address varchar2(50));

2. 删除tableA表中的字段

删除一个字段birthday：alter table tableA drop  column birthday;

3. 修改tableA表中的字段

将birthday的类型改为date：alter table tableA  modify(birthday date); 
--注意：当该字段有数据时，不能将字段的长度减小，只能增加长度。
将address重名民为addr：alter table tableA rename column address to addr;

4. 给tableA表添加注释

将tableA表命名为测试用例表：comment on table tableA  is '测试用例表'; 
将tableA表中的name字段命名为姓名：comment on column tableA.name is '姓名';

5. 给tableA表的id字段添加索引

CREATE  INDEX index_tableA ON tableA (id);

• 用Oracle数据库计算两个日期之间相差多少天
  当需要通过数据库计算日期dateA距离日期dateB之间相差多少天时，可以使用一下sql语句:

select ceil(to_date(dateA,'yyyymmdd')-to_date(dateB,'yyyymmdd')) from tableA;

其中tableA为数据库表名，dateA和dateB为日期参数，且dateA和dateB既可以是tableA中的字段，也可以是外界传过来的参数。

注意事项

• oracle数据库中，若需要在已有表的基础上新增字段，并且还需要自定义字段位置时，目前只能通过删表重建的方法实现，或者在已有表的最后字段后添加新字段，而不能自定义字段位置