0%

Oracle入门

服务、监听
启动oracle服务

1
2
3
4
5
su - oracle
sqlplus /nolog
sql> conn /as sysdba
sql> startup
sql> exit

启动监听服务

1
lsnrctl start

配置监听

1
netca

启动em控制台

1
emctl start dbconsole

创建数据库

1
dbca

连接数据库

1
2
sqlplus /nolog
conn / as sysdba;

连接数据库

1
conn system/password;

断开数据库连接

1
disc[onnect]

将屏幕上的内容输入到文件中

1
2
3
spool d:\a.txt
……
spool off;

创建用户

密码必须以字母开头

1
create user xiaoming identified by m123;

sys用户:超级管理员,权限最高,dba角色,默认密码:change_on_install
system用户:系统管理员,没有create database权限,默认密码:manager
普通用户:新建的用户无登录数据库权限
查看当前用户

1
show user;

修改密码

1
2
1. passw[ord]
2. alter user 用户名 identified by 新密码;

删除用户
如果用户已经创建表,删除的时候加一个cascade参数

1
drop user 用户名 cascade

如果一个用户正在被占用,报错ORA-01940: cannot drop a user that is currently connected

1
SELECT 'ALTER SYSTEM KILL SESSION '||''''||SID||''''||','||''''||SERIAL#||''''||';' as KILLER FROM V$SESSION WHERE USERNAME='USER';

授予权限

1
2
grant select on emp to xiaoming;
grant all on emp to xiaoming;

收回权限

1
revoke all on emp to xiaoming;

收回上级权限,下级权限也相应取消
对象权限授权

1
2
3
grant select on emp to xiaoming with grant option;
conn xiaoming/m1234;
grant select on scott.emp to xiaohong;

系统权限授权

1
2
3
grant connect to xiaoming with admin option;
conn xiaoming/1234;
grant connect to xiaohong;

用户口令管理
建立数据库时候,oracle会建立默认的default的profile,当没有给用户指定profile的时候,oracle会将default的profile分配给用户
创建profile文件
用户输入3次错误密码后被锁定2天

1
2
create profile lock_account limit failed_login_attempts 3 password_lock_time 2;
alter user xiaoming profile lock_account;

用户解锁

1
alter user xiaoming account unlock;

定期修改密码
要求用每隔30天修改登录密码,宽限期为2天,32天后没有修改将不能登录

1
2
create profile myprofile limit password_life_time 30 password_grace_time 2;
alter user xiaoming profile myprofile;

口令历史
希望用户修改密码的时候,不能使用之前的密码;使用口令历史,oracle会将之前的密码存在数据字典中,当用户修改密码时,oracle会将新输入的密码和旧密码进行比对,如果一样,会提示重新输入。
不能使用10内使用过的密码

1
2
create profile password_history limit password_life_time 10 password_grace_time 2 password_reuse_time 10
alter user xiaoming profile password_history;

删除profile文件

1
drop profile password_history [cascade]

###############Day2############
创建表
必须以字母开头
长度不能超过30个字符
不能使用oracle保留字
只能使用a-zA-Z0-9$#

字符型
char 2000
varchar2 4000
clob 4000+
数字型
number 整数,小数 -(10^38) ~ 10^38
日期类型
date 包含年月日和时分秒
timestamp 时间戳,精度更高
图片
blob

创建student表

1
2
3
4
5
6
7
create table student(
xh number(4),
xm varchar2(20),
sex char(2),
birthday date,
sal num(7,2)
);

添加一个字段

1
alter table student add(classID number(2));

修改字段长度

1
alter table student modify(xm varchar2(30));

修改字段的类型/或者名字(不能有数据)

1
alter table student modify (xm char(30));

删除一个字段

1
alter table student drop column sal;

修改表名字

1
rename student to stu;

删除表

1
drop table student;

修改日期默认格式

1
alter session set nls_date_format='mm-dd-yyyy'

插入数据
插入所有字段

1
insert into student values(1,'小明','男','03-14-2017',3200.00)

插入部分字段

1
insert into student(xh,xm,sex) values('23',小张','男')

插入空值
修改数据

1
update student set sex='女' where xm='小明';

修改多个字段,用逗号隔开

将一行数据复制多条

1
insert into users (userid,username,userpass) select * from users;

查询字段为[非]空

1
select * from student where brithday is [not] null;

删除数据,不删除表结构,记录在日志中,删除之前建立一个回滚点savepoint aa;可恢复

1
delete from student;

删除数据后回滚

1
rollback to aa;

删除表的结构和数据

1
drop table student;

删除表数据,不删除表结构,不记录在日志中,不可恢复,速度快;

1
truncate table student;

查看表结构

1
desc table_name;

查询所有列

1
select * from dept;

查询指定列

1
select ename,sal,job,deption from dept;

取消重复行

1
select distinct deptno,job from emp;

打开显示操作时间

1
set timing on;

nvl处理null值,当字段为null,当0处理

1
select sal*13+nvl(comm,0)*13 "年工资",ename,comm from emp;

like
%表示0到多个字符
_表示任意单个字符

多个值匹配

1
select * from emp where empno in(123,234,500);

or & and

1
select * from emp where (sal>500 or job='Manager') and ename like 'J%';

默认排序低到高

1
2
3
4
order by [asc/desc]
select * from emp order by sal;
````
按部门编号升序,薪水降序列;

select * from emp order by depto, sal desc;

1
列别名排序

select ename,(sal+nvl(comm,0))*12 [as] “年薪” from emp order by “年薪”;

1
2
3
4

复杂查询
max,min,avg,sum,count
查询一个最高工资,最低工资

select max(sal),min(sal) from emp;

1
查出最高工资员工

select ename,sal from emp where sal=(select max(sal) from emp);

1
显示工资高于平均工资的员工

select * from emp where sal>(select avg(sal) from emp);

1
2
group by
按部门分组,查出部门平均工资和最高工资

select avg(sal),max(sal),deptno from emp group by deptno;

1
显示每个部门的每种岗位的平均工资,最高工资

select avg(sal),max(sal),deptno,job from emp group by deptno,job;

1
2
having

select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)>2000;

1
2
3
4
5
6
7

同一条语句出现顺序,优先级group by > having > order by
在选择列中,如果有列,表达式和分组函数,那么这些列和表达式中必须有一个出现在group by字句中,否则会报错

多表查询
多表查询的条件是至少不能少于表的个数-1
笛卡尔集

select a1.ename,a1.sal,a2.dname from emp a1, dept a2 where a1.deptno=a2.deptno;

1
between 在两者之间

select a1.ename,a1.sal,a2.grade from emp a1,salgrade a2 where a1.sal between a2.losal and a2.hisal;

1
多表排序

select a1.ename,a2.dname,a1.sal from emp a1,dept a2 where a1.deptno=a2.deptno order by a1.deptno;

1
2
3
4
5
自连接

子查询
数据库执行sql语句从左到右
单行子查询

select * from emp where deptno=(select deptno from emp where ename=’SMITH’);

1
多行子查询

select * from emp where job in(select distinct job from emp where deptno=10);

1
2
all 
查出工资比部门30的所有员工的工资都高的员工的姓名,工资和部门号

select ename,sal,deptno from emp where sal>all(select all from emp where deptno=30);
select * from emp where sal>(select max(sal) from emp where deptno=30)

1
2
any
查出工资比部门30的任意一个员工的工资高的员工的姓名,工资和部门号

select ename,sal,dept from emp where sal>any(select all from emp where deptno=30);
select * from emp where sal>(select min(sal) from emp where deptno=30)

1
2
多列子查询
查出与SMITH部门和岗位完全相同的所有职员

select * from emp where (deptno,job)=(select deptno,job from emp where ename=’SMITH’);

1
查询高于自己部门平均工资的员工的信息

select a2.ename,a2.sal,a2.deptno,a1.mysal from emp a2,(select deptno,avg(sal) mysql from emp group by deptno) a1 where a2.deptno=a1.deptno and a2.sal > a1.mysal;

1
2
3
4
5
6
内嵌视图就是子查询当一个表使用,当在from子句中使用子查询时,必须给子查询指定别名;
列加别名可以加as,表加别名不能加as

分页查询
一共有三种方式
1. rownum 分页

select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum<=10) where rn>6;

1
2
3
4
5
6
7
2. ROWID 分页 
速度最快

3. 按分析函数分页
速度最慢

查询结果创建新表

create table mytable (id,name,sal,job,deptno) as select empno,ename,sal from emp;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
合并查询
为了合并多个select语句的结果,可以使用集合操作符号union,union all,intersect,minus.
union
去多个select查询结果的并集,取消重复行
union all
和union 类似,不取消重复行,or关系
intersect
取交集结果
minus
取差集(一个集合完全包含另一个集合,取没有的部分)


数据库管理员职责:
1. 安装和升级oracle数据库
2. 建库,表空间,表,视图,索引……
3. 指定并实施备份和恢复计划
4. 数据库权限管理,调优,故障排除,
5. 对于高级dba,要求能参与项目开发,会编写sql语句,存储过程,触发器,规则,约束,包

sys用户:
所有oracle的数据字典的基表和动态视图都存在sys用户中;
sys拥有dba(数据库管理员角色),sysdba(系统管理员),sysoper(系统操作员) 角色和权限;
是oracle最高权限用户,必须以as sysdba或as sysoper形式登录,不能以normal方式登录数据库

system 用户:
存放次级用户内部数据;
拥有dba,sysdba角色及权限;


显示oracle 初始化参数
show parameter;

数据库的逻辑备份和恢复

导出表

exp userid=username/passwd@sid tables=(table1,table2,table3) file=/path/x.dmp

1
导入表

imp userid=username/passwd@sid tables=(table1,table2,table3) file=/path/x.dmp

1
2
使用直接导出方式
这种方式比默认常规的方式速度要快,当数据量大时,可以考虑这种方法,这时需要数据库的字符集要与客户端的字符集完全一致,否则报错

exp userid=username/passwd@sid tables=(table1,table2,table3) file=/path/x.dmp direct=y

1
导出表结构

exp userid=username/passwd@sid tables=(table1,table2,table3) file=/path/x.dmp rows=n

1
导出方案

exp username/passwd@sid owner=username file=/path/x.dmp

1
导出数据库

exp userid=system/passwd@sid full=y inctype=complete file=/path/xx.dmp
imp userid=system/manager full=y file=/path/xx.dmp


数据字典记录了数据库的系统信息,它是只读表和视图的集合,数据字典的所有者为sys用户,用户只能在数据字典上执行查询操作,而其维护和修改是由系统自动完成的
数据字典包括字典基表和数据字典动态视图,其中基表存储数据库的基本信息,普通用户不能直接访问数据字典的基表,数据字典视图是基于数据字典基表所建立的视图,普通用户可以通过查询数据字典的视图取得系统信息
数据字典视图主要包括user_xxx,all_xxx,dba_xxx三种类型

user_tables;
用于显示当前用户拥有的所有表,只返回用户所对应方案的所有表;
select table_name from user_tables;
all_tables;
用于显示当前用户可以访问的所有表;
select table_name from all_tables;
dba_tables;
用户显示所有方案拥有的数据库表。但是查询这种数据库字典视图,要求用户必须是dba角色或是有select any table系统权限。
select table_name from dba_tables;

用户名,权限,角色
dba_users  可以显示所有数据库用户的详细信息;
dba_sys_privs 可以显示用户所具有的系统权限;
dba_tab_privs 可以显示用户具有的对象权限;
dba_col_privs 可以显示用户具有的列权限;
dba_role_privs 可以显示用户所具有的角色。

查询oracle中所有的系统权限,一般是dba
select * from system_privilege_map order by name;
查询oracle中所有的角色,一般是dba
select * from dba_roles;
查询oracle中所有的对象权限,一般是dba
select distinct privilege from dba_tab_privs;
查询数据表的表空间
select tablespace_name from dba_tablespaces;


oracle 有多少种角色?
select * from dba_roles;
查询一种角色有多少种权限?
a. 一个角色包含的系统权限
select * from dba_sys_privs where grantee='CONNECT';
select * from role_sys_privs where role='CONNECT';
b. 一个角色包含的对象权限
select * from dba_tab_privs where grantee='CONNECT';
如何查看某个用户具有什么样的角色?
select * from dba_role_privs where grantee='SCOTT';

显示当前用户可以访问的所有数据字典视图
select * from dict where comments like '%grant%'
显示当前数据库的全称
select * from global_name;