南山草舍

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;