一、用户管理
1、用户信息
用户名/密码 | 登录身份 | 说明 |
sys/change_on_install | SYSDBA或SYSOPER | 不能以NORMAL登录,可以作为默认的系统管理员 |
system/manager | SYSDBA或NORMAL | 不能以SYSOPER登录,可以作为默认的系统管理员 |
sysman/oem_temp | sysman | 为oms的用户名 |
scott/tiger | NORMAL | 普通用户 |
aqadm/aqadm | SYSDBA或NORMAL | 高级队列管理员 |
Dbsnmp/dbsnmp | SYSDBA或NORMAL | 复制管理员 |
ALTER USER user
IDENTIFIED BY password;
说明:
如果想查看当前用户所拥有的表和视图:Select * form tab;
如果想查看当前用户:select user from dual;
2、创建用户
创建用户的语法为:
CREATE USER user
IDENTIFIED BY password;
而给用户授权为:
GRANT privilege [ ,privelege...]
TO user [,user...];
其中privilege表示系统限权,例如,create session、create table等,举例如下:
CREATE USER us1
IDENTIFIED BY 1234;
GRANT create session
TO us1;
有时候创建用户过于麻烦,可以先创建角色,然后在给用户授权如下:
CREATE ROLE managers;
GRANT create session、create table TO managers;
GRANT managers TO us1;
附加,授予系统权限:
- create session:连接到数据库
- create table:创建表
- create sequence:创建序列
- create view:创建视图
- create proceduer:创建程序
而如果想撤销用户的权限,则:
revoke create table from us1;
二、数据库的启动与关闭
以下用SQL plus来实现启动和关闭数据库(以数据库orcl,用户为sys【连接身份为sysdba】)为例,首先以scott身份登录后,再转为以sys身份连接:
conn sys as sysdba;
startup open orcl;
而关闭数据库:
- shutown:也就是shutdown normal。在一般情况下关闭数据库,数据库会等待所有连接的用户离线,不再让新的用户连接,之后dismount(卸载)数据库,再关闭数据库。
- shutdown immediate: 立即关闭数据库,不会等到用户离线,先关闭数据库,卸载数据库后再关闭例程,数据库重启时,不必还原例程。
- shutdown abort:直接关闭数据库和例程,数据库重启时,需要还原例程。
- shutdown transaction:交易式关闭数据库,会等到有交易连接的用户完成后再关闭数据库,卸载数据库后,再关闭例程。
三、表空间、表和视图的基本操作
1、表空间
首先创建表空间的语法如下:
CREATE [UNDO | TEMPORARY] TABLESPACE tablespace
DATAFILE | TEMPFILE 'FILE_SPEC'
[MINIMUM EXTENT n [K|M] ]
[AUTOEXTEND
OFF
ON NEXT n KIM MAXSIZE UNLIMITED | n [K|M] ]
[LOGGING | NOLOGGING]
[ONLINE | OFFLING]
[DEFAULT (storage_clause) ] (N/A IfTEMPORARY above)
[EXTENT MANAGEMENT
DICTIONAR (Must be LOVAL for TEMPFILE)
LOCAL
AUTOALLOCATE
UNIFORM [SIZE n [ K|M] ]
[SEGMENT SPACE MANAGEMENT MANUAL|AUTO]
注释:
- FILE_SPEC:数据文件或临时文件的名称
- MINIMUM EXTENT:最小延伸数
- AUTOEXTEND:自动延伸数,ON表示打开,OFF表示关闭
- MAXSIZE:最大数
- UNLIMITED:无限制
- LOGGING:在表空间中,设置表、索引和切割区的纪录属性
- NOLOGGING:在表空间中,不设置纪录属性,也就是不会产生Unod和Redo纪录文件
- ONLINE:连线状态,可以使用
- OFFLINE:离线状态,不可以使用
- DEFAULT:系统自动产生的默认值
- EXTENT MANAGEMENT:指定库充的管理方式
- SEGMENT SPACE MANAGEMENT:区段空间的管理
举例,创建表空间TS1:
create tablespace TS1 datafile
'E:\testdb\TEST_DB02.dbf' SIZE 100M
autoextend on next 100M maxsize 1024M
default storage (initial 10M next 1M)
permanent
online
logging;
如果想修改表空间TS1:
alter tablespace ts1
nologging;
如果想在表空间TS1中添加文件则为:
alter tablespace TS1 add datafile
'E:\TEST_DB01.dbf' size 10M reuse
autoextend on next 10M maxsize unlimited;
如果想删除表空间TS1:
drop tablespace ts1;
如果在删除表空间的同时删除其数据:
drop tablespace ts1 including contents and datafiles;
2、表
1.表的创建
首先创建表的语法如下:
CREATE TABLE [ schema_name . ] table_name
{ column datatype [NOT NULL] }
[ TABLESPACE tablespace_name]
注释:
- schema_name:模式名称,最常使用的是拥有者
- table_name:表的名称
- column:字段的名称
- datatype:数据类型
- NOT NULL:不能为空
- TABLESPACE:表空间
- tablespace_name:表空间的名称
举例,在表空间(TS1)中创建customer表:
create table customer
(custID varchar2(10) default '00',
custname varchar2(10)
)
tablespace ts1;
此时可以使用describe命令查看表结构:
describe customer;
如果想将创建的表继承其它表的结构可以(emp2的表结构和emp相同):
create table emp2
as
select * from emp where 1=2;
如果想将创建的表继承其它表的结构以及数据(创建表,保存20号部门的员工):
create table emp20
as
select * from emp where deptno=20;
如果继承的不是单表结构可以:
create table empinfo
as
select e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname
from emp e, dept d
where e.deptno=d.deptno
2.表的修改
如果想修改表customer:
- 修改字段类型, 将表的custname字符类型由VARCHAR2(10)改为CHAR(15)其命令为:
ALTER TABLE customer
MODIFY (CustName CHAR(15));
- 向表中添加一个字段,在表customer后面添加custsex:
Alter table customer ADD(ENOTE CHAR(30));
- 如果想删除一个字段,先设置其无法还原,然后在将其删除:
alter tabe customer -- 先设置表无法还原
set unused(CustName);
alter tabe customer --再将其删除
drop unused columns;
- 如果想修改表的名称:
rename customer to cust;
3.表的删除
如果想删除表中的数据:
truncate table customer;
而要删除表则为:
drop table customer;
如果删除表时:
Drop table customer cascade constraints;
将会同时删除参照该表的外键约束
在Oracle中当删除一个表时,实际上该表并没有被真正删除,而是被添加到回收站(普通用户,如果是管理员就真正删除了),如果想查看回收站的内容可以:
show recyclebin;
如果想要清空回收站可以:
purge recyclebin;
如果想从回收站中恢复删除过的表可以:
flashback table customer to before drop;
4.表的约束条件
- 默认值
hiredate date default sysdate
- 唯一建
Constraint emp_uk UNIQUE (EMPID)
- 主建
语法为:
Constraint Constraint_name PRIMARY KEY (column);
举例,将EMPID设置为主键:
constraint emp_pk PRIMARY KEY(EMPID));
或者:
alter table EMP
add constraint EMP_emp_pk PRIMARY KEY(empid);
- 外键
用来将表中的一个字段设置成外键,以参照链接到相同或不同的表中的另一个字段,这两个有关系的字段可以相同也可以不同。不可以直接删除父表。语法为:
constraint constraint_name Foreign Key (column) references table_name (column);
- 非空值
直接在类型后面加上NOT NULL:
EMPID VARCHAR2(10) NOT NULL;
- 检查
语法为:
Constraint Constraint_name CHECK (condition);
举例:
部门代码(deptno)必须在1~99之间:
constraint deptno_ck check (deptno between 1 and 99));
创建一个学生信息表:
create table student
(
sid number constraint student_pk primary key,
sname varchar2(20) constraint student_name_notnull not null,
gender varchar2(2) constraint student_gender check (gender in ('男','女')),
email varchar2(40) constraint student_email_unique unique
constraint student_email_notnull not null,
deptno number constraint student_fk references dept(deptno) on delete set null
);
3、视图
首先创建视图的语法如下:
CREATE [OR REPLACE] [FORCE | NOFORCE] View view
[alias [,alias]...]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint ] ]
[WITH READ ONLY [CONSTRAINT] ];
注释:
- OR REPLACE:将已存在的视图再建一遍
- FORCE:无论基础表格是否存在,皆会建立视图
- NOFORCE:只有基础表格存在时,才会建立视图
- view:视图的名称
- alias:使用别名,已供视图在查询字段时使用
- subquery:为完整的select叙述
- WITH CHECK OPTION:只有未被指定的数据列才可以通过视图进行新增或者修改操作
- constraint:赋予WITH CHECK OPTION的名称
- WITH READ ONLY:保证不能在视图中执行DML,加入此句后就不会修改数据本身
举例,创建视图emp_dep1
create view emp_dep1
as select empid,empname
from emp
where deptno=21;
而如果想修改视图,则为:
create or replace view emp_dep1(id,name,no)
as select empid,empname,deptno
from emp
where deptno=21;
而如果想删除视图:
drop view emp_dep1;
四、数据的基本操作
1、添加
语法为:
Insert INTO table [(column [,column...])]
VALUES (Value [,Value..]);
举例,向EMP中添加一条数据:
insert into emp(empid,empname)
values('001','abel');
如果想进行批量插入,如一次性将emp中,所有10号部门的员工插入到emp10中:
insert into emp10 select * from emp where deptno=10;
附加,在Oracle中如果想进行海量数据的拷贝可以采用如下方式:
- 数据泵(datapump) ---> plsql
- SQL*Loader
- (数据仓库)外部表
- 可传输的表空间
2、查询
语法为:
select * from table_name where column=‘查询值’
如果想在查询时去掉重复(针对所有列):
select distinct * from table_name where column=‘查询值’
如果想在查询时加上排序功能,需要再在查询添加后面加上
order by + 列,表达式,别名,序号+asc/desc【默认为asc,从小到大】
如查询员工信息 按照月薪排序
select * from emp order by sal;
而如果想进行多个列排序,如即按照部门编号(先、主)又按照月薪(后、次)排序
select * from emp order by deptno,sal;
上面的多列排序是默认从小到大排的(每个列都是),但是如果写出这样
select * from emp order by deptno,sal desc;
将会deptno按升序排,然后sal按降序排,即desc只作用于离他最近的列,如果每列都是降序:
select * from emp order by deptno desc,sal desc;
如果在排序过程中有空值存在,如按奖金多少排序(可能没有),在Oracle中null为最大,即如果按照升序排列,null在最下面,而如果按降序排,null在最上面,但是一般我们都希望空值一直在最下面出现,这时可以:
select * from emp order by comm desc nulls last
3、修改
语法为:
UPDATE table
SET column =value[,column=value,...]
[where condition]
4、删除
语法为:
delete from EMP where EMPName ='Abel';
此外如果想要删除整张表中的所有数据除了可以使用delete,
delete from testdelete;
还可以使用truncate操作
truncate table testdelete;
delete和truncate的区别::
- delete逐条删除;truncate先摧毁表,再重建
- (根本)delete是DML,truncate是DDL
- delete可以回滚,truncate不可以回滚
- delete不会释放空间,truncate会
- delete可以闪回,truncate不可以
- delete会产生碎片,truncate不会
5、复制
语法为:
INSERT INTO table [column (,column) ] subquery;
举例,将EMP中EMPID开头为001的编号复制到manager中:
insert into manager (EMPID,EMPName,HireData,COMM,DeptNO)
select EMPID,EMPName,HireData,COMM,DeptNO
from EMP
where empid like '001%';