Oracle数据库操作

本例使用oracle数据库，使用PL/SQL可视化工具：

--查询员工表数据 （emp为pl/sql自带的表，也可自己新建）
select * from emp;

--创建表空间
create tablespace sp_java1608
datafile 'e:\data\data1.mdf' size 100 M;

--修改表空间

--空间变为200M
alter tablespace sp_java1608
add datafile 'e:\data\data2.mdf' size 100 M;

--删除表空间
drop tablespace sp_java1608 including contents and datafiles;

--创建用户（create user username identified by password）设置默认表空间

create user java1608
identified by java1608
default tablespace sp_java1608;

--删除用户
drop user java1608;

--赋予权限:

--connect :可以登录Oracle，不可以创建实体，不可以创建数据库结构。

--session 创建会话的权限

--DBA:拥有全部特权

--RESOURCE:拥有Resource权限的用户只可以创建实体，不可以创建数据库结构

grant create session to java1608;

grant create connect to Java1608;

grant resource to java1608;

--回收权限
revoke resource from java1608;

--rowID,rownum   都是伪列

--rowid在数据库中具有唯一性，在同一个数据库中是不会重复的，而rownum则可以理解为行号。
--常用于数据库分页
--如下例子：3当前是第几页,2代表每页显示几条数据
select * from
(
select rownum as eid,emp.* from emp
) tmp where eid between (3*2)+1 and (3+1)*2;

--单表查询
--select 列名1,列名2,... from 表名 where 条件

select * from emp;

select ename,sal from emp;

select * from emp where empno = 7369;

--distinct 除去列名重复

select distinct job from emp;

--oracle默认是隐式事务
--插入数据
insert into emp values(1000,'张三','CLERK',7698,to_date('2000/01/01','yyyy/mm/dd'),300.5,100.6,20);

insert into emp values(1001,'lisi','CLERK',7698,to_date('2000/01/01','yyyy/mm/dd'),300.5,100.6,20);

--rollback为撤回行为 与之相对的为commit提交事务

rollback;

--计算列
select ename,sal+comm as totalsal from emp where comm is not null;

select * from emp where ename like '%E%';

select * from emp where ename like '%E_';-- _伟%

--连接运算符

--dual 是为了让查询语法完成而存在的关键字
select 'a' || 'b' from dual;

--聚合函数，数据库通用函数 
select min(sal) from emp;

select job, max(sal) from emp group by job having max(sal)>=2000;

select avg(sal) from emp;

select sum(sal) from emp;

select count(*) from emp;

--创建表
create table t_student
(
sno char(10),
name nvarchar2(6),
sex char(1),
address varchar2(200),
birthday date
);

select * from t_student;

insert into t_student(sex) values('m');

--数据完成整性
--修改表/增加列
alter table t_student
add mphone char(11);

--修改表/修改列
alter table t_student
modify mphone char(12) not null;

--修改表/删除列
alter table t_student
drop column mphone;

--表改名
alter table t_student_1
rename to t_student;

--删除表
drop table t_student;

--实体完整性\域完整性\引用完整性(约束来实现)
--主键约束(非空,唯一,聚焦索引)
alter table t_student
add constraint pk_student_sno primary key (sno);

--非空约束
alter table t_student
modify name varchar2(6) not null;

alter table t_student
modify sex not null;

--默认值约束
alter table t_student
modify sex default 'm';

--唯一性约束
alter table t_student
add constraint un_student_name unique (name);

--检查性约束

--between为前后闭区间
alter table t_student
add constraint ck_student_sex check (sex='m' or sex ='f');

alter table t_student
add constraint ck_student_birthday check (birthday between to_date('1900/01/01','yyyy/mm/dd') and to_date('2016/11/27','yyyy/mm/dd'));

--外键约束

--主键（主表）；外键 (从表)

alter table t_score
add constraint fk_score_sno foreign key (sno) references t_student(sno);

create table t_score
(
sid int primary key,
math int not null,
chenese int not null,
sno char(10) 
) ;

select * from t_score;

--删除表所有数据

delete from t_score;

--关联表数据一一对应

insert into t_score values(1,20,30,'s001');
insert into t_score values(2,20,30,'s002');
insert into t_score values(3,20,30,'s003');
insert into t_score values(4,20,30,'s004');

insert into t_student(sno,name,sex,birthday) values('s001','zhang','f',to_date('1900/01/01','YYYY/MM/DD'));
insert into t_student(sno,name,sex,birthday) values('s002','li','m',to_date('1900/01/01','YYYY/MM/DD'));
insert into t_student(sno,name,sex,birthday) values('s003','zhao','f',to_date('1900/01/01','YYYY/MM/DD'));
insert into t_student(sno,name,sex,birthday) values('s004','wang','m',to_date('1900/01/01','YYYY/MM/DD'));

select * from t_student;

delete from t_student;