Oracle——基本内容（一）

心已赠人 2024-04-18 11:39 60阅读 0赞

# 一、用户管理 #

## 1、用户信息 ##

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:middle;">用户名/密码</td> 
   <td style="vertical-align:middle;">登录身份</td> 
   <td style="vertical-align:middle;">说明</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:middle;">sys/change_on_install</td> 
   <td style="vertical-align:middle;">SYSDBA或SYSOPER</td> 
   <td style="vertical-align:middle;">不能以NORMAL登录，可以作为默认的系统管理员</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:middle;">system/manager</td> 
   <td style="vertical-align:middle;">SYSDBA或NORMAL</td> 
   <td style="vertical-align:middle;">不能以SYSOPER登录，可以作为默认的系统管理员</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:middle;">sysman/oem_temp</td> 
   <td style="vertical-align:middle;">sysman</td> 
   <td style="vertical-align:middle;">为oms的用户名</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:middle;">scott/tiger</td> 
   <td style="vertical-align:middle;">NORMAL</td> 
   <td style="vertical-align:middle;">普通用户</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:middle;">aqadm/aqadm</td> 
   <td style="vertical-align:middle;">SYSDBA或NORMAL</td> 
   <td style="vertical-align:middle;">高级队列管理员</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:middle;">Dbsnmp/dbsnmp</td> 
   <td style="vertical-align:middle;">SYSDBA或NORMAL</td> 
   <td style="vertical-align:middle;">复制管理员</td> 
  </tr> 
 </tbody> 
</table>

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%';