裕-Oracle操作整理

1.DDL数据库定义语言(对表的操作)

(1)基本操作

-- 增加
create table 表名(列名 数据类型 [约束], 列名 数据类型 [约束] ...)
create table 表名 as 子查询

-- 删除
drop table 表名
truncate table 表名

-- 修改
alter table 表名 add(列名 数据类型 [约束], 列名 数据类型 [约束] ...)
alter table 表名 modify(列名 数据类型 [约束], 列名 数据类型 [约束] ...)
alter table 表名 drop column 列名
alter table 表名 rename column 旧列名 to 新列名
rename 旧表名 to 新表名

-- 查询(在命令窗口里写)
desc[ribe] 表名

(2)约束

-- 列级约束 
create table stus(    
	cid Number(20) references clses(cid); -- 学生表的班级编号指向班级表的班级编号 
);

-- 表级约束 
create table stus(    
	cid Number(20),    
	constraint fk_cls foreign key(cid) references clses(cid) on delete cascade; 
);

alter table student add unique(name); -- 添加约束
alter table student modify (name varchar2(20) unique ); -- 修改约束
alter table 表名 drop constraint 约束名 -- 删除约束
alter table 表名 disable constraint 约束名; -- 禁用约束
alter table 表名 enable constraint 约束名; -- 激活约束

-- 举例代码：
create table students(
       sid number(10),
       name varchar2(20) not null,
       sex varchar2(3) default '男',
       phone number(11) unique,
       age number(3) check(age > 0)
);

  

2.DML:数据操作语言(对表数据的操作)

(1)基本操作

-- 增加
insert into 表名 [(列名, ...)] values (值, ...)
insert into 表名 子查询

-- 删除
drop table  删除表
  - 删除数据
  - 删除表存储空间
  - 无法回滚
  - 删除表结构

truncate table  清空表
  - 删除数据 
  - 删除表存储空间
  - 无法回滚
  - 保留表结构

delete from  删除数据
  - 删除数据
  - 不会表存储空间
  - 可以回滚
  - 保留表结构

-- 修改
update 表名 set 列名 = 值, ...  [where 条件]

-- 查询
select * | 列名1, 列名2… from 表名
select sysdate  from dual;
select 列名 [as] 列别名 from 表名
select  distinct 列名 from 表名
select 查询内容 from 表名 where 查询条
select 查询内容 from 表名 order by 排序列名1 [asc | desc], 排序列名2 [asc | desc] ...
select * from stus where sname like '%#_%' escape '#';

  

3.DCL:数据库控制语言(访问权限)

(1)基本操作

1.用户
-- 增加
create user 用户名 identified by 密码

-- 删除
drop user 用户名 cascade;

-- 修改
alter user 用户名 identified by 密码

-- 查询
oracle命令，非sql语句
connect 用户名/密码


2.权限
系统权限: 对于数据库操作的权限，比如创建表 
对象权限: 操作数据库对象的权限，比如对表进行操作

-- 系统权限：
grant 权限,权限... to 用户 [public] [with admin option];		-- 授予系统权限
revoke 权限,权限...  from 用户 [PUBLIC]		--回收系统权限

-- 对象权限：
常用对象权限：(insert-插入 | alter-修改 | delete-删除 | update-更新 | select-选择 | all-所有权限)
授予对象权限： grant 权限,权限...| all on 对象名 to 用户 [public] [with admin option];
回收对象权限： revoke 权限,权限...| all  on 对象名 from 用户 [PUBLIC]


3.角色
系统角色：
connect，resource，dba等

自定义角色：
create role 角色名		-- 创建角色
drop role 角色名		-- 删除角色：
grant 角色1,角色2... to 用户1,用户2...;		-- 使用角色给用户授权：
grant 角色1,角色2... to 用户1,用户2... identified by 密码			-- 使用角色创建用户并授权：

eg：grant connect, resource to abc identified by 123;

  

4.TCL:事务控制语言(事务提交回滚)

(1)基本操作

原子性：事务必须是小工作单元。对于其数据操作，要么全都执行，要么全都不执行。 
一致性：事务在完成时，必须使所有的数据都保持一致状态。 
隔离性：由并发事务所作的修改必须与任何其他并发事务所作的修改隔离。 
持久性：事务完成之后，它对于系统的影响是永久性的。

commit：提交。
rollback：回滚。

savepoint 保存点名
rollback to 保存点名

  

5.用法例子

grant connect, resource to xiaoyu identified by 0824;	-- 创建用户名为xiaoyu、密码为0824的用户

connect xiaoyu/0824;	-- 使用该用户登录

create sequence seq_students2;	-- 创建序列
create sequence seq_users;			-- 创建序列
create sequence seq_roles;			-- 创建序列

-- 创建表
create table students(
       sid number(10) primary key ,	--主健
       name varchar2(20) not null ,	-- 不为空
       sex varchar2(3) default '男',		-- 默认为男
       age number(3) check(age > 0)	--年龄必须大于0岁
);

create table roles(
		r_Id number(10) ,
		r_Name varchar2(20) not null ,
		r_Level number(10) ,
		r_Remarks varchar2(100) ,
		constraint roles_id_pk primary key(r_Id)
);

create table users(
		u_Id number(10) ,
		u_Name varchar2(20) not null ,
		u_Password varchar2(50) not null ,
		u_Role number(10) ,
		u_Remarks varchar2(100) ,
		constraint users_id_pk primary key(u_Id) ,
		constraint users_roles_fk foreign key(u_Role) references Roles(r_Id) on delete set null
);

-- 插入测试数据
insert into students values (seq_students2.nextval, '小张','男', 22);
insert into students values (seq_students2.nextval, '小李',null, 22);
insert into students(sid,name, age) values (seq_students2.nextval, '小张', 22);

insert into roles values (seq_Roles.nextval, '权限1',1, '备注1');
insert into roles values (seq_Roles.nextval, '权限2',2, '备注2');
insert into roles(r_Id,r_Name,r_Level,r_Remarks) values (seq_Roles.nextval, '权限3',3, null);

insert into users values (seq_Users.nextval, 'admin','admin',1, '备注');
insert into users values (seq_Users.nextval, '用户名','password',2, null);
insert into users(u_Id,u_Name,u_Password,u_Role,u_Remarks) values (seq_Users.nextval, 'AL00001','12345',null, null);

-- 查询全部：
select * from users;
select * from roles;

-- 条件查询：
select * from users where u_Name like '%'+?+'%' and u_Role =? ;
select * from roles where r_Name like '%'+?+'%' ;

-- 更改：
update users set u_Name=?, u_Password=?, u_Role=?, u_Remarks=? where u_Id = ? ;
update roles set r_Name=?,r_Level=?,r_Remarks=? where r_Id = ? ;

-- 删除：
delete from users where u_Id = ? ;
delete from roles where r_Id = ? ;

commit;	-- 事务提交

  

6.分页

-- 它这个自带的分页感觉挺好用的，分享一下
-- 代码固定的...把数据第几条到第几条放进去就好。
SELECT * FROM (SELECT ROWNUM AS rowno, t.* FROM orders t WHERE ROWNUM < ?) table_alias WHERE table_alias.rowno >= ?