oracle复习

1.
首先创建表空间:指定表空间名为test_lob 并分配50MB空间；

create tablespace test_lob
datafile 'E:\app\test.dbf'
size 50M;

2.
创建用户并指定所在表空间为test_lob，并指定该用户在该表空间可使用10M大小的空间

create user mr identified by px980305
default tablespace test_lob
quota 10M on test_lob;

3.修改：
修改用户在表空间中所在份额：

alter user mr quota 20M on test_lob;

改为份额为不受限制：

alter user mr quota unlimited on test_lob;

修改密码：

alter user mr identified by 123456;

在创建了用户后要进行解锁：

alter user mr account unlock;
conn mr/123456;

4.
授权：
授予连接数据库权限：

grant connect to mr;

授予创建表的权限： 

grant create table to mr;

授予mr用户查询scott用户下dept表的权限:

grant select on scott.dept to mr;

5.
创建角色并授予创建视图的权限

create role peng identified by 123456;
alter role peng not identified;出现：角色已丢弃
set role peng;
grant create view to peng;

将角色授予mr;

grant peng to mr;
drop role peng;
revoke resource from mr;
此处要注意system若无法删除用户时可连接到sys用户
drop user mr cascade;

1.创建用户
create user pengxiang identified by px980305
2.创建表空间
create tablespace text_space datafile 'E:\pengxiang\test_space';
2.为用户指定表空间
alter user pengxiang defalut tablespace test_space;
3.解锁新用户
alter user pengxiang account unlock;
4.授予dba权限，即管理员权限
grant dba to pengxiang;
创建会话权限
grant create session to pengxiang;
创建视图权限
grant create any view to pengxiang;

1.建表 ：主键约束，外键约束，检查约束

1）创建数据表宿舍表:（10分）
宿舍表(宿舍号 char(6)，宿舍电话) ；要求使用：主键(宿舍号)、宿舍电话：以633开头的7位电话号码；
【提示：查阅regexp_like（）函数的功能及字符模糊匹配、常用通配符的使用。】
法一：
create table sushe(
snumber char(6) primary key,
stelphone char(7) check(stelphone like'633[0-9][0-9[0-9][0-9]')
);

该方法在进行插入时会提示检查性约束错误故应采用法二：
create table sushe(
snumber char(6) primary key,
stelphone char(7) check(regexp_like(stelphone,'633[0-9][0-9][0-9][0-9]');
);

create table emp
 (id number ,
name1 varchar(8) ,
constraint aba_pr primary key(id,name1));该表中的主键有两个

 

（2）创建学生表：（10分）
学生表(学号 char(6)，姓名，性别，年龄，民族，身份证号，宿舍号)，要求使用：主键(学号)、默认(民族，汉族)、
非空(民族，姓名，年龄)、唯一(身份证号)、检查约束(性别)。


create table student(
sno char(6)primary key,  主键
sname varchar(8) not null,  //非空约束
sex char(2) check(sex='男' or sex='女'), 检查约束
age number(4) not null,
mincu varchar(10)default '汉族'not null,   //默认default
id char(18) unique,      唯一
constraint name unique(id), //约束名为name
snumber char(6)
);

在建表时添加主键可以不起约束名

create table  (
id number(6) primary key,--主键
name varchar2(20) not null,--非空
sex number(1),
age number(3) default 18,
birthday date,
address varchar2(50),
email varchar2(25) unique,--唯一
tel number(11),
deptno number(2) references dept(deptno)—外键
);

带约束名称：

create table user (
id number(6) constraint id_pk primary key,
name varchar2(20) constraint name_nn not null,
sex number(1),
age number(3) default 18,
birthday date,
address varchar2(50),
email varchar2(25) constraint email_uqe unique,
tel number(11),
deptno number(2) constraint dept_deptno_ref references dept(deptno)
);

2.修改表属性（注意：在已有数据的表中修改属性会报错）
在建表时若忘记了某些属性可通过alter table tablename add();来添加

alter table student add(snumber char(6)not null);添加属性列

添加外键约束

alter table student add constraint stu foreign key (snumber) references sushe(snumber);

由于主表为student表，参照的是sushe表中的数据，故应先在宿舍表中插入相应数据后才能在student表中插入，
否则会提示未找到父项关键字错误

insert into sushe values ('101','6331157');
insert into sushe values ('102','6331777');
insert into student(sno,sname,sex,age,mincu,id,snumber) values ('201601','李强','男',20,'汉族','371321199602046419','101');

添加属性列并设置默认值

alter table sushe add(snumber varchar(7) default '001');

删除表属性

alter table sushe drop column stelphone  要加column否则会认为删除该属性约束

修改字段属性

alter table sushe modify(sushe varchar(7));

修改字段名
alter table sushe rename column   原字段名 to 新字段名


更新数据
update table sushe set stelphone='6331158' where snumber='101';

3.查询表命令
 查询基本命令格式：

SELECT …
FROM…
WHERE….
GROUP BY…HAVING
ORDER BY…

 

多表连接查询
1.查询雇员的姓名、编号及所在部门名称；
代码: select emp.ename,dept.deptno,dept.dname 
from emp,dept
where emp.deptno=dept.deptno;外连接
内连接
select ename,emp.deptno,dname from emp inner join dept on emp.deptno=dept.deptno;

 

2.查询每个员工的姓名、所在部门名及对应领导的姓名。
代码: select emp1.ename as 员工姓名,dept.dname,emp2.ename as 领导姓名
from emp emp1 inner join emp emp2  起表别名
on emp1.empno=emp2.mgr
inner join dept
on emp1.deptno=dept.deptno;
在该题中，将emp表看作两个表，并且使用了三表联合查询的方法


查询每位员工的姓名、编号及工资等级。
代码: select ename,empno,grade as 工资等级
from emp inner join salgrade
on sal between losal and hisal;

 

左外连接
select emp.deptno,emp.ename,dept.dname
from emp left outer join dept
on emp.deptno=dept.deptno;
全外连接
select emp.ename,emp.empno,dept.dname
from emp full join dept
on emp.deptno=dept.deptno;
列出在每个(每个是关键字,对此group by)部门工作的员工数量、平均工资和平均服务期限。
代码: 
select count(empno) as num,round(avg(sal)) as 平均工资,round(avg(to_char(sysdate,'yyyy')-to_char(hiredate,'yyyy'))) as 平均服务年限
from emp
group by deptno;
列出各个部门的MANAGER的最低薪金。
代码: select dname,min(sal)
from emp inner join dept
on emp.deptno=dept.deptno
where job='MANAGER'
group by dname；
列出所有员工的年工资,按年薪从低到高排序。(年薪=月薪*12+佣金。提示：无需修改数据，使用nvl() :空值转化函数) 
代码: select ename,(sal*12+nvl(comm,0))as 年薪  //nvl的使用方法,两个参数，若第一个参数为空则设值为0
from emp;

 

查询10号和30号部门的最高工资,结果按照部门号升序序排序。
代码:

select deptno,max(sal)
from emp
where deptno=10 or deptno=30
group by deptno
order by deptno;


法二

select deptno,max(sal)
from emp
where deptno in (10,30)
group by deptno
order by deptno;

查询部门的名称、所在地及每个部门的人数。
代码:

select dname,loc,count(empno)
from emp inner join dept
on emp.deptno=dept.deptno
group by dname,loc;     group  by 的筛选条件不唯一

查询每个部门的部门名称及雇佣时间最长的员工姓名及其雇佣时间。（嵌套查询)
代码:

select ename,dname,hiredate 
from emp,dept 
where emp.deptno=dept.deptno and
hiredate=(select min(hiredate) from emp where emp.deptno=dept.deptno )