Oracle基础操作下 有更新!

  |   0 评论   |   438 浏览

1.创建和管理表

1.1.数据库对象

对象 描述
基本的数据存储集合,由行和列组成
视图 从表中抽出逻辑上相关的数据集合
序列 提供有规律的数值
索引 提高查询的效率
同义词 给对象起别名

1.2.数据库中的表

  1. 用户自定义的表

    用户自己创建并维护的一组表
    包含了用户所需的信息

  2. 数据字典

    由 Oracle Server 自动创建的一组表
    包含数据库信息

1.3.查询用户字典

  1. 查看用户自定义的表
    SELECT table_name
    FROM user_tables ;
    
  2. 查看用户定义的各种数据库对象
    SELECT DISTINCT object_type
    FROM  user_objects ;
    
  3. 查看用户定义的表, 视图, 同义词和序列
    SELECT *
    FROM user_catalog ;
    

1.4.命名规则

表名和列名:
必须以字母开头
必须在 1–30 个字符之间
必须只能包含 A–Z, a–z, 0–9, _, $, 和 #
必须不能和用户定义的其他对象重名
必须不能是Oracle 的保留字

1.5.创建表

CREATE TABLE dept  (
	deptno NUMBER(2),
	dname VARCHAR2(14),
	loc VARCHAR2(13)
);

1.6.使用子查询建表

使用 AS subquery 选项,将创建表和插入数据结合起来
指定的列和子查询中的列要一一对应
通过列名和默认值定义列

CREATE TABLE  dept80
  AS
	SELECT employee_id, last_name,
	salary*12 ANNSAL,hire_date
	FROM employees  
 	WHERE dept_id = 80;

1.7.修改表

  1. 新增列
    ALTER TABLE dept80
    ADD  (job_id VARCHAR2(9));
    
  2. 修改列

    可以修改列的数据类型, 尺寸和默认值
    对默认值的修改只影响今后对表的修改

    ALTER TABLE dept80
    MODIFY (last_name VARCHAR2(30));
    
    ALTER TABLE dept80
    MODIFY (salary number(9,2) default 1000);
    
  3. 删除列
    ALTER TABLE dept80
    DROP COLUMN job_id;
    
  4. 重命名列
    ALTER TABLE dept80
    RENAME COLUMNjob_id TO id;
    

1.8.清空表

  1. TRUNCATE TABLE

    删除表中所有的数据
    释放表的存储空间
    TRUNCATE语句不能回滚
    可以使用 DELETE 语句删除数据,可以回滚

    TRUNCATE TABLE detail_dept;
    
    DELETE FROM  detail_dept;
    ROLLBACK;
    

1.9.修改表名

执行RENAME语句改变表, 视图, 序列, 或同义词的名称
必须是对象的拥有者

RENAME  dept  TO  detail_dept;

1.10.不可回滚语句

CREATE TABLE  创建表
ALTER TABLE  修改表结构
DROP TABLE  删除表
RENAME TO 重命名表
TRUNCATE TABLE  删除表中的所有数据,并释放存储空间

2.数据处理

2.1.INSERT语句

使用 INSERT 语句向表中插入数据。
使用这种语法一次只能向表中插入一条数据。

  1. 插入数据

    为每一列添加一个新值。
    按列的默认顺序列出各个列的值。
    在 INSERT 子句中随意列出列名和他们的值。
    字符和日期型数据应包含在单引号中。

    INSERT INTO
    dept(dept_id,dept_name,manager_id,location_id)
    VALUES(70, 'Public Relations', 100,1700);
    
    INSERT INTO
    emp(emp_id,last_name,email,hire_date,job_id)
    VALUES (300,’Tom’,’tom@126.com’,to_date(‘2012-3-21’,’yyyy-mm-dd’),’SA_RAP’);
    
  2. 向表中插入空值
    # 隐式方式: 在列名表中省略该列的值。
    INSERT INTO 
    dept (dept_id,dept_name )
    VALUES (30, 'Purchasing');
    
    # 显示方式: 在VALUES  子句中指定空值。
    INSERT INTO departments
    VALUES (100, 'Finance', NULL, NULL);
    
  3. 插入指定的值

    SYSDATE 记录当前系统的日期和时间。

    INSERT INTO 
    emp(emp_id,first_name,last_name,hire_date)
    VALUES  (113,'Louis', 'Popp','LPOPP',SYSDATE);
    
  4. 创建脚本

    在SQL 语句中使用 & 变量指定列值。
    & 变量放在VALUES子句中。

    INSERT INTO 
    dept(dept_id, dept_name, location_id)
    VALUES(&dept_id,'&dept_name',&location);
    
  5. 从其他表拷贝数据

    在 INSERT 语句中加入子查询。
    不必书写 VALUES 子句。
    子查询中的值列表应与 INSERT 子句中的列名对应。

    INSERT INTO emp2
    SELECT *
    FROM emp
    WHERE dept_id = 90;
    
    INSERT INTO sales_reps(id, name, salary, commission_pct)
    SELECT employee_id, last_name, salary, commission_pct
    FROM employees
    WHERE job_id LIKE '%REP%';
    

2.2.UPDATE语句

使用 UPDATE 语句更新数据。
可以一次更新多条数据。

  1. 更新数据

    使用 WHERE 子句指定需要更新的数据。
    如果省略 WHERE 子句,则表中的所有数据都将被更新。

    UPDATE emp
    SET dept_id = 70
    WHERE emp_id = 113;
    
    UPDATE copy_emp
    SET dept_id = 110;
    
  2. 使用子查询
    UPDATE employees
    SET job_id= (
    	SELECT job_id
    	FROM employees
    	WHERE employee_id = 205),
      salary = (
    	SELECT salary
    	FROM employees
    	WHERE employee_id = 205)
    WHERE employee_id = 114;
    

2.3.DELETE语句

使用 DELETE 语句从表中删除数据

  1. 删除数据

    使用 WHERE 子句删除指定的记录。
    如果省略 WHERE 子句,则表中的全部数据将被删除

    DELETE FROM departments
    WHERE department_name = 'Finance';
    
    DELETE FROM copy_emp;
    
  2. 使用子查询
DELETE FROM emp1
WHERE dept_id =(
	SELECT department_id
	FROM dept1
	WHERE dept_name LIKE '%Public%'
);

2.4.数据库事务

事务:一组逻辑操作单元,使数据从一种状态变换到另一种状态。
数据库事务由以下的部分组成:
–一个或多个DML 语句
–一个 DDL(Data Definition Language – 数据定义语言) 语句
–一个 DCL(Data Control Language – 数据控制语言) 语句

以第一个 DML 语句的执行作为开始
以下面的其中之一作为结束:
–COMMIT 或 ROLLBACK 语句
–DDL 语句(自动提交)
–用户会话正常结束
–系统异常终止

COMMIT和ROLLBACK语句的优点

使用COMMIT 和 ROLLBACK语句,我们可以:
–确保数据完整性。
–数据改变被提交之前预览。
–将逻辑上相关的操作分组。

控制事务.JPG

回滚到保留点

使用 SAVEPOINT  语句在当前事务中创建保存点。
使用 ROLLBACK TO SAVEPOINT  语句回滚到创建的保存点。

UPDATE...
SAVEPOINT update_done;
INSERT...
ROLLBACK TO update_done;

事务进程

自动提交在以下情况中执行:
–DDL 语句。
–DCL 语句。
–不使用 COMMIT 或 ROLLBACK 语句提交或回滚,正常结束会话。

会话异常结束或系统异常会导致自动回滚。

提交或回滚前的数据状态

改变前的数据状态是可以恢复的
执行 DML 操作的用户可以通过 SELECT 语句查询之前的修正
其他用户不能看到当前用户所做的改变,直到当前用户结束事务。
DML语句所涉及到的行被锁定,  其他用户不能操作。

提交后的数据状态

数据的改变已经被保存到数据库中。
改变前的数据已经丢失。
所有用户可以看到结果。
锁被释放,其他用户可以操作涉及到的数据。
所有保存点被释放。

数据回滚后的状态

使用 ROLLBACK 语句可使数据变化失效:
数据改变被取消。
修改前的数据状态被恢复。
锁被释放。

评论

发表评论