Oracle操作手册

于 2021-07-11 23:45:43 发布
阅读量2.1k 收藏 13
点赞数 2
分类专栏： 数据库 文章标签： 数据库 oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/xul999/article/details/118583708
版权

基础语句

基本select语句

SELECT	*|{[DISTINCT] column|expression [alias],...}
FROM	table;

SELECT 标识 选择哪些列。 FROM 标识从哪个表中选择。

选择全部列

SELECT *FROM   departments;

*可以表示全部的列

选择特定的列

SELECT department_id, location_id FROM   departments;

字段之间用“,”隔开

注意点

  1. SQL 语言大小写不敏感。
  2. SQL 可以写在一行或者多行 。
  3. 关键字不能被缩写也不能分行 。
  4. 各子句一般要分行写。
  5. 使用缩进提高语句的可读性。

算术运算符

操作符描述
+加
-减
*乘
/除

使用数学运算符

SELECT last_name, salary, salary + 300 FROM   employees;

操作符优先级

  • / + - 跟正常顺序一样，乘除优先于加减、从左向右、括号优先

定义空值

  1. 空值是无效的，未指定的，未知的或不可预知的值
  2. 空值不是空格或者0
  3. 包含空值的数学表达式的值都为空值

列的别名

  1. 重命名一个列。
  2. 便于查看、计算
  3. 紧跟列名，也可以在列名和别名之间加入关键字‘AS’，别名使用双引号，以便在别名中包含空格或特殊的字符并区分大小写
SELECT last_name AS name, commission_pct comm FROM   employees;

or

SELECT last_name "Name", salary*12 "Annual Salary" FROM   employees;

连接符

  1. 把列与列，列与字符连接在一起。
  2. 用 ‘||’表示。
  3. 可以用来‘合成’列
SELECT	last_name||job_id AS "Employees" FROM 	employees;

字符串

  1. 字符串可以是 SELECT 列表中的一个字符,数字,日期。
  2. 日期和字符只能在单引号中出现。
  3. 每当返回一行时，字符串被输出一次。
SELECT last_name	||' is a '||job_id  AS "Employee Details"
FROM   employees;

重复行

SELECT DISTINCT department_id
FROM   employees;

在 SELECT 子句中使用关键字 ‘DISTINCT’ 删除重复行

SQL 语句与 SQL*Plus 命令区别

SQL

1

. 一种语言
2. ANSI 标准
3. 关键字不能缩写
4. 使用语句控制数据库中的表的定义信息和表中的数据

SQL*Plus

  1. 一种环境
  2. Oracle 的特性之一
  3. 关键字可以缩写
  4. 命令不能改变数据库中的数据的值
  5. 集中运行

显示表结构

DESC[RIBE] tablename

使用 DESCRIBE 命令，表示表结构

过滤和排序

过滤

SELECT	*|{[DISTINCT] column|expression [alias],...}
FROM	table
[WHERE	condition(s)];

使用WHERE 子句，将不满足条件的行过滤掉

WHERE子句

SELECT employee_id, last_name, job_id, department_id
FROM   employees
WHERE  department_id = 90 ;

日期默认格式

默认的日期格式是 DD-MON月-RR
楼上说了字符和日期要包含在单引号中
字符大小写敏感，日期格式敏感。

比较运算符

操作符含义
=等于
>大于
>=大于等于
<小于
<=小于等于
<>不等于（也可以是**！=**）

比较运算

SELECT last_name, salary
FROM   employees
WHERE  salary <= 3000;

其他比较运算

操作符含义
BETWEEN…AND…在两个值之间（包含边界）
IN(set)等于值列表中的一个
LIKE模糊查询
IS NULL空值

BETWEEN…AND…案例

SELECT last_name, salary
FROM   employees
WHERE  salary BETWEEN 2500 AND 3500;

使用 BETWEEN 运算来显示在一个区间内的值

in案例

SELECT employee_id, last_name, salary, manager_id
FROM   employees
WHERE  manager_id IN (100, 101, 201);

使用 IN运算显示列表中的值

LIKE案例

SELECT	first_name
FROM 	employees
WHERE	first_name LIKE 'S%';
  1. 使用 LIKE 运算选择类似的值 。
  2. 选择条件可以包含字符或数字:
    • % 代表零个或多个字符(任意个字符)
    • _ 代表一个字符。
    • _ 和 % 可以同时使用

null案例

SELECT last_name, manager_id
FROM   employees
WHERE  manager_id IS NULL;

使用 IS (NOT) NULL 判断空值。

逻辑运算

操作符含义
and逻辑并
or逻辑或
not逻辑否

and案例

SELECT employee_id, last_name, job_id, salary
FROM   employees
WHERE  salary >=10000
AND    job_id LIKE '%MAN%';

AND 要求并的关系为真。

or案例

SELECT employee_id, last_name, job_id, salary
FROM   employees
WHERE  salary >= 10000
OR     job_id LIKE '%MAN%';

OR 要求或关系为真。

not案例

SELECT last_name, job_id
FROM   employees
WHERE  job_id 
       NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');

优先级

优先级
1算术运算符
2连接符
3比较符
4IS [NOT] NULL, LIKE, [NOT] IN
5[NOT] BETWEEN
6NOT
7AND
8OR

可以使用括号改变优先级顺序

ORDER BY排序

使用 ORDER BY 子句排序
- ASC（ascend）: 升序
- DESC（descend）: 降序
ORDER BY 子句在SELECT语句的结尾。

SELECT   last_name, job_id, department_id, hire_date
FROM     employees
ORDER BY hire_date ;

按别名排序

SELECT employee_id, last_name, salary*12 annsal
FROM   employees
ORDER BY annsal;

多个列排序

SELECT last_name, department_id, salary
FROM   employees
ORDER BY department_id, salary DESC;

单行函数

  1. 操作数据对象
  2. 接受参数返回一个结果
  3. 只对一行进行变换
  4. 每行返回一个结果
  5. 可以转换数据类型
  6. 可以嵌套
  7. 参数可以是一列或一个值

字符函数

大小写控制函数
函数结果
LOWER(‘SQL COURSE’)sql course
UPPER(‘SQL COURSE’)SQL COURSE
INITCAP(‘SQL Course’)Sql Course
字符串控制函数
操作符结果
CONCAT(‘Hello’, ‘World’)HelloWorld
SUBSTR(‘HelloWorld’,1,5)Hello
LENGTH(‘HelloWorld’)10
INSTR(‘HelloWorld’, ‘W’)6
LPAD(salary,10,’*’)*****24000
RPAD(salary, 10, ‘*’)24000*****
TRIM(‘H’ FROM ‘HelloWorld’)elloWorld
REPLACE(‘abcd’,’b’,’m’)amcd

数字函数

操作符结果
ROUND(45.926, 2)（四舍五入）45.93
TRUNC(45.926, 2)（截断）45.92
MOD(1600, 300)（求余）100

时间函数

Oracle 中的日期型数据实际含有两个值:

  • 日期和时间。

函数SYSDATE 返回:

  •   日期 

  •   时间

日期的数学运算

  1. 在日期上加上或减去一个数字结果仍为日期。
  2. 两个日期相减返回日期之间相差的天数。日期不允许做加法运算，无意义
  3. 可以用数字除24来向日期中加上或减去天数。
函数描述
MONTHS_BETWEEN两个日期相差的月数
ADD_MONTHS向指定日期中加上若干月数
NEXT_DAY指定日期的下一个星期*对应的日期
LAST_DAY本月的最后一天
ROUND日期的四舍五入
TRUNC日期截断

yyyy 年 mm 月 dd 日 day 星期 hh 小时 mi 分钟 ss 秒

转换函数

数据类型转换分为隐形和显性

TO_CHAR函数对字符的转换

注意点:

  1. 必须包含在单引号中而且大小写敏感。
  2. 可以包含任意的有效的日期格式。
  3. 日期之间用逗号隔开
TO_CHAR(date, 'format_model')  -- format_model：yyyy-mm-dd hh:mi:ss

TO_DATE 函数对时间的转换

TO_DATE(char[, 'format_model'])

TO_NUMBER 函数对数字的转换

主要是将字符串转换为数值型的格式

TO_NUMBER 函数中经常使用的几种格式:

TO_NUMBER(char[, 'format_model'])
参数含义
9数字
0零
$美元符
L本地货币符号
.小数点
,千位符

格式化案例
主要是将字符串转换为数值型的格式

SELECT TO_CHAR(salary, '$99,999.00') SALARY
FROM   employees
WHERE  last_name = 'Ernst';

NVL 函数

将空值转换成一个已知的值：
可以使用的数据类型有日期、字符、数字。
函数的一般形式:
NVL(commission_pct,0)
NVL(hire_date,‘01-JAN-97’)
NVL(job_id,‘No Job Yet’)

NVL2 函数

NVL2 (expr1, expr2, expr3) : expr1不为NULL，返回expr2；为NULL，返回expr3。

NULLIF 函数

NULLIF (expr1, expr2) : 相等返回NULL，不等返回expr1

COALESCE 函数

  1. COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。
  2. 如果第一个表达式为空,则返回下一个表达式，对其他的参数进行COALESCE
  3. COALESCE是一个函数， (expression_1, expression_2, …,expression_n)依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。使用COALESCE在于大部分包含空值的表达式最终将返回空值。
SELECT   last_name,
         COALESCE(commission_pct, salary, 10) comm
FROM     employees
ORDER BY commission_pct;

条件表达式

在 SQL 语句中使用IF-THEN-ELSE 逻辑
使用两种方法:
CASE 表达式
DECODE 函数

CASE 表达式
在需要使用 IF-THEN-ELSE 逻辑时:

CASE expr WHEN comparison_expr1 THEN return_expr1
         [WHEN comparison_expr2 THEN return_expr2
          WHEN comparison_exprn THEN return_exprn
          ELSE else_expr]
END

练习

查询部门号为 10, 20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数

答案

select department_id,first_name||last_name, salary,
case when department_id=10 then salary*1.1 
 when department_id=20 then salary*1.2
 when department_id=30 then salary*1.3
  else salary end
 from employees
where department_id in (10,20,30)

DECODE 函数
在需要使用 IF-THEN-ELSE 逻辑时:

DECODE(col|expression, search1, result1 ,
      			   [, search2, result2,...,]
      			   [, default])

案例

SELECT last_name, job_id, salary,
       DECODE(job_id, 'IT_PROG',  1.10*salary,
                      'ST_CLERK', 1.15*salary,
                      'SA_REP',   1.20*salary,
              			    salary)
       REVISED_SALARY
FROM   employees;

嵌套函数

  1. 单行函数可以嵌套。
  2. 嵌套函数的执行顺序是由内到外

多表查询

笛卡尔集

笛卡尔集会在下面条件下产生:

  1. 省略连接条件
  2. 连接条件无效
  3. 所有表中的所有行互相连接

为了避免笛卡尔集， 可以在 WHERE 加入有效的连接条件。

连接多个表查询

SELECT	table1.column, table2.column
FROM	table1, table2
WHERE	table1.column1 = table2.column2;

在 WHERE 子句中写入连接条件。
在表中有相同列时，在列名之前加上表名前缀
使用表名前缀在多个表中区分相同的列。
在不同表中具有相同列名的列可以用表的别名加以区分。
别名在查询表后面加上即可 departments depa
这样，就可以使用depa作为表名了

等值连接

SELECT employees.employee_id, employees.last_name, 
       employees.department_id, departments.department_id,
       departments.location_id
FROM   employees, departments
WHERE  employees.department_id = departments.department_id;

连接多个表

连接 n个表,至少需要 n-1个连接条件。 例如：连接三个表，至少需要两个连接条件。

  1. 内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
  2. 外连接:
    两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时,
    结果表中相应的列为空(NULL). 外连接的 WHERE 子句条件类似于内部连接, 但连接条件中没有匹配行的表的列后面要加外连接运算符,
    即用圆括号括起来的加号(+).
    不懂得此博客

外连接语法

SELECT	table1.column, table2.column
FROM	table1, table2
WHERE	table1.column(+) = table2.column; --右外连接

SELECT	table1.column, table2.column
FROM	table1, table2
WHERE	table1.column = table2.column(+); --左外连接

使用外连接可以查询不满足连接条件的数据。
外连接的符号是 (+)。

叉 集（了解）

使用CROSS JOIN 子句使连接的表产生叉集。
叉集和笛卡尔集是相同的。

SELECT last_name, department_name
FROM   employees
CROSS JOIN departments ;

自然连接

SELECT department_id, department_name,
       location_id, city
FROM   departments
NATURAL JOIN locations ;

使用 USING 子句创建连接

在NATURAL JOIN 子句创建等值连接时，可以使用 USING 子句指定等值连接中需要用到的列。 使用 USING
可以在有多个列满足条件时进行选择。 不要给选中的列中加上表名前缀或别名。 JOIN 和 USING 子句经常同时使用。
join … using…代替了 join … on…

例子：

select ename,dname from emp join dept on (emp.deptno = dept.deptno);

就相当于：

select ename,dname from emp join dept using (deptno);

使用ON 子句创建连接（常用）

  1. 自然连接中是以具有相同名字的列为连接条件的。
  2. 可以使用 ON 子句指定额外的连接条件。
  3. 这个连接条件是与其它条件分开的。
  4. ON子句使语句具有更高的易读性。

案例

SELECT e.employee_id, e.last_name, e.department_id, 
       d.department_id, d.location_id
FROM   employees e JOIN departments d
ON     (e.department_id = d.department_id);

数据库中的左连接和右连接的区别可以概括为一句话来表示即左连接where只影响右表，右连接where只影响到左表

  1. 左连接：返回包括左表中的所有记录和右表中连接字段相等的记录。
  2. 右连接：返回包括右表中的所有记录和左表中连接字段相等的记录。
  3. 全外连接：返回左右表中所有的记录和左右表中连接字段相等的记录。

左外连接

SELECT e.last_name, e.department_id, d.department_name
FROM   employees e
LEFT OUTER JOIN departments d
ON   (e.department_id = d.department_id) ;

右外连接

SELECT e.last_name, e.department_id, d.department_name
FROM   employees e
RIGHT OUTER JOIN departments d
ON    (e.department_id = d.department_id) ;

满外连接

SELECT e.last_name, e.department_id, d.department_name
FROM   employees e
FULL OUTER JOIN departments d
ON   (e.department_id = d.department_id) ;

分组函数

分组函数作用于一组数据，并对一组数据返回一个值。

函数意义
AVG平均值
COUNT计数
MAX最大值
MIN最小值
SUM求和

GROUP BY 子句语法

可以使用GROUP BY子句将表中的数据分成若干组

非法使用组函数

  1. 不能在 WHERE 子句中使用组函数。
  2. 可以在 HAVING 子句中使用组函数。

过滤分组： HAVING 子句

使用 HAVING 过滤分组:

  1. 行已经被分组。
  2. 使用了组函数。
  3. 满足HAVING 子句中条件的分组将被显示

子查询

SELECT	select_list
FROM	table
WHERE	expr operator
		 	(SELECT	select_list
		        FROM		table);
  1. 子查询 (内查询) 在主查询之前一次执行完成。
  2. 子查询的结果被主查询(外查询)使用 。
  3. 是不是跟嵌套函数一个意思
  4. 子查询要包含在括号内。
  5. 将子查询放在比较条件的右侧。

非法使用子查询

  1. 多行子查询使用单行比较符
  2. 子查询不返回任何行

多行子查询

  1. 返回多行。
  2. 使用多行比较操作符。
操作符含义
IN等于列表中的任意一个
ANY和子查询返回的某一值比较
ALL和子查询返回的所有值比较

使用案列更容易懂一些

题目：返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary

SELECT employee_id, last_name, job_id, salary
FROM   employees
WHERE  salary < ANY
                    (SELECT salary
                     FROM   employees
                     WHERE  job_id = 'IT_PROG')
AND    job_id <> 'IT_PROG';

结果
在这里插入图片描述

题目：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及salary

SELECT employee_id, last_name, job_id, salary
FROM   employees
WHERE  salary < ALL
                    (SELECT salary
                     FROM   employees
                     WHERE  job_id = 'IT_PROG')
AND    job_id <> 'IT_PROG';

结果
在这里插入图片描述

创建和管理表

常见的数据库对象

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

用户定义的表:

  1. 用户自己创建并维护的一组表
  2. 包含了用户所需的信息 如：SELECT * FROM user_tables;查看用户创建的表

数据字典:

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

命名规则

表名和列名:

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

CREATE TABLE 语句

必须具备:

  1. CREATE TABLE权限
  2. 存储空间

必须指定:

  1. 表名
  2. 列名, 数据类型, 尺寸
CREATE TABLE dept(
		deptno 	NUMBER(2),
		dname 	VARCHAR2(14),
		loc 	VARCHAR2(13));
Table created.

数据类型

数据类型描述
VARCHAR2(size)可变长字符数据
CHAR(size)定长字符数据
NUMBER(p,s)可变长数值数据
DATE日期型数据
LONG可变长字符数据，最大可达到2G
CLOB字符数据，最大可达到4G
RAW (LONG RAW)原始的二进制数据
BLOB二进制数据，最大可达到4G
BFILE存储外部文件的二进制数据，最大可达到4G
ROWID行地址

使用子查询创建表

  1. 使用 AS subquery 选项，将创建表和插入数据结合起来
  2. 指定的列和子查询中的列要一一对应
  3. 通过列名和默认值定义列
CREATE TABLE table
  	  [(column, column...)]
AS subquery;

使用子查询创建表案例

CREATE TABLE 	dept80
  AS 
      SELECT  employee_id, last_name, 
            salary*12 ANNSAL, 
            hire_date    FROM    employees    WHERE   department_id = 80;
Table created.

ALTER TABLE 语句

使用 ALTER TABLE 语句可以:

  1. 追加新的列
  2. 修改现有的列
  3. 为新追加的列定义默认值
  4. 删除一个列
  5. 重命名表的一个列名

追加一个新列

使用 ADD 子句追加一个新列

ALTER TABLE dept80
ADD		   (job_id VARCHAR2(9));
Table altered.

修改一个列

  1. 可以修改列的数据类型, 尺寸和默认值
  2. 对默认值的修改只影响今后对表的修改
ALTER TABLE	dept80
MODIFY		(salary number(9,2) default 1000);
Table altered.

删除一个列

使用 DROP COLUMN 子句删除不再需要的列

ALTER TABLE  dept80
DROP COLUMN  job_id; 
Table altered.

重命名一个列

使用 RENAME COLUMN [table_name] TO子句重命名列

ALTER TABLE  dept80
RENAME COLUMN  job_id TO id; 
Table altered.

删除表

  1. 数据和结构都被删除
  2. 所有正在运行的相关事务被提交
  3. 所有相关索引被删除
  4. DROP TABLE 语句不能回滚
DROP TABLE dept80;
Table dropped.

清空表

TRUNCATE TABLE 语句:

  1. 删除表中所有的数据
  2. 释放表的存储空间 TRUNCATE语句不能回滚

可以使用 DELETE 语句删除数据,可以回滚

TRUNCATE TABLE detail_dept;
Table truncated.

改变对象的名称

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

RENAME dept TO detail_dept;
Table renamed.

数据处理

数据操纵语言

DML(Data Manipulation Language – 数据操纵语言)

  1. 向表中插入数据
  2. 修改现存数据
  3. 删除现存数据

事务是由完成若干项工作的DML语句组成的

插入语句语法

使用 INSERT 语句向表中插入数据

INSERT INTO	table [(column [, column...])]
VALUES		(value [, value...]);

UPDATE 语句语法

使用 UPDATE 语句更新数据

UPDATE		table
SET		column = value [, column = value, ...]
[WHERE 		condition];

DELETE 语句

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

DELETE FROM	  table
[WHERE	  condition];

数据库事务

事务：一组逻辑操作单元,使数据从一种状态变换到另一种状态。 数据库事务由以下的部分组成:

  1. 一个或多个DML 语句
  2. 一个 DDL(Data Definition Language – 数据定义语言) 语句
  3. 一个 DCL(Data Control Language – 数据控制语言) 语句

以第一个 DML 语句的执行作为开始 以下面的其中之一作为结束:

  1. COMMIT 或 ROLLBACK 语句
  2. DDL 语句（自动提交）
  3. 用户会话正常结束 系统异常终止

COMMIT和ROLLBACK语句的优点

  1. 确保数据完整性。
  2. 数据改变被提交之前预览。
  3. 将逻辑上相关的操作分组。

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

UPDATE...
SAVEPOINT update_done;
Savepoint created.
INSERT...
ROLLBACK TO update_done;
Rollback complete.

事务进程

自动提交在以下情况中执行:

  1. DDL 语句。
  2. DCL 语句。
  3. 不使用 COMMIT 或 ROLLBACK 语句提交或回滚，正常结束会话。

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

提交或回滚前的数据状态

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

提交后的数据状态

  1. 数据的改变已经被保存到数据库中。
  2. 改变前的数据已经丢失。
  3. 所有用户可以看到结果。
  4. 锁被释放,其他用户可以操作涉及到的数据。
  5. 所有保存点被释放。
语句功能
INSERT插入
UPDATE修正
DELETE删除
COMMIT提交
SAVEPOINT保存点
ROLLBACK回滚

约束CONSTRAINT

什么是约束

约束是表级的强制规定

  1. NOT NULL
  2. UNIQUE
  3. PRIMARY KEY
  4. FOREIGN KEY
  5. CHECK

注意事项

如果不指定约束名 ，Oracle server 自动按照 SYS_Cn 的格式指定约束名 创建和修改约束:

  1. 建表的同时
  2. 建表之后

可以在表级或列级定义约束 可以通过数据字典视图查看约束

表级约束和列级约束

作用范围： ①列级约束只能作用在一个列上 ②表级约束可以作用在多个列上（当然表级约束也可以作用在一个列上）
定义方式：列约束必须跟在列的定义后面，表约束不与列一起，而是单独定义。
非空(not null) 约束只能定义在列上

  （1）定义列约束：Constraint <约束名> Primary Key

	（2）定义表约束：[Constraint <约束名>] Primary Key( <列名> [{<列名>}])

NOT NULL 约束

非空约束， 保证列值不能为空（上面注意点只能定义在列级）

UNIQUE 约束

唯一约束，允许出现多个空值：NULL。

PRIMARY KEY 约束

主键约束，设置的字段不能为空且不能重复，包含not null约束。起唯一标识作用，其值不能为NULL，也不能重复，以此来保证实体的完整性
在一个基本表中只能定义一个PRIMARY KEY约束

FOREIGN KEY 约束

外键约束
外部键约束用于强制参照完整性，提供单个字段或者多个字段的参照完整性。 FOREIGN KEY约束指定某一个列或一组列作为外部键，其中，包含外部键的表称为从表(参照表)，包含外部键所引用的主键或唯一键的表称主表(被参照表)。
现在一般不用外键约束

CHECK 约束

检查约束 来检查字段值所允许的范围，如，一个字段只能输入整数，而且限定在0-100的整数，以此来保证域的完整性

添加约束的语法

使用 ALTER TABLE 语句:

  1. 添加或删除约束,但是不能修改约束
  2. 有效化或无效化约束
  3. 添加 NOT NULL 约束要使用 MODIFY 语句

添加约束语法

ALTER TABLE	 table
  ADD [CONSTRAINT constraint] type (column);

Alter table emp modify(empname varchar2(50) not null);

添加约束举例

ALTER TABLE     employees
ADD CONSTRAINT  emp_manager_fk 
  FOREIGN KEY(manager_id) 
  REFERENCES employees(employee_id);
Table altered.

删除约束

ALTER TABLE      employees
DROP CONSTRAINT  emp_manager_fk;
Table altered.

无效化约束

ALTER TABLE 语句中使用 DISABLE 子句将约束无效化。

ALTER TABLE		employees
DISABLE CONSTRAINT	emp_emp_id_pk;
Table altered.

激活约束

ENABLE 子句可将当前无效的约束激活

ALTER TABLE		employees
ENABLE CONSTRAINT	emp_emp_id_pk;
Table altered.

当定义或激活UNIQUE 或 PRIMARY KEY 约束时系统会自动创建UNIQUE 或 PRIMARY KEY索引

查询约束

查询数据字典视图 USER_CONSTRAINTS

SELECT	constraint_name, constraint_type,
	search_condition
FROM	user_constraints
WHERE	table_name = 'EMPLOYEES';

查询定义约束的列

查询数据字典视图 USER_CONS_COLUMNS

SELECT	constraint_name, column_name
FROM	user_cons_columns
WHERE	table_name = 'EMPLOYEES';

视图

视图的描述：从表中抽出的逻辑上相关的数据集合。

  1. 视图是一种虚表。
  2. 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。
  3. 向视图提供数据内容的语句为 SELECT 语句,
  4. 可以将视图理解为存储起来的 SELECT 语句. 视图向用户提供基表数据的另一种表现形式

为什么使用视图

  1. 控制数据访问
  2. 简化查询
  3. 避免重复访问相同的数据

简单视图和复杂视图

特性简单视图复杂视图
表的数量一个一个或多个
函数没有有
分组没有有
DML 操作可以有时可以

创建视图

在 CREATE VIEW 语句中嵌入子查询

案例：

CREATE VIEW 	empViev
 AS SELECT  employee_id, last_name, salary
    FROM    employees
    WHERE   department_id = 80;
View created.

修改视图

CREATE OR REPLACE VIEW empvu80
  (id_number, name, sal, department_id)
AS SELECT  employee_id, first_name || ' ' || last_name, 
           salary, department_id
   FROM    employees
   WHERE   department_id = 80;
View created.

使用CREATE OR REPLACE VIEW 子句修改视图
CREATE VIEW 子句中各列的别名应和子查询中各列相对应

视图中使用DML的规定

可以在简单视图中执行 DML 操作 当视图定义中包含以下元素之一时不能使用delete:

  1. 组函数
  2. GROUP BY 子句
  3. DISTINCT 关键字
  4. ROWNUM 伪列

当视图定义中包含以下元素之一时不能使用update:

  1. 组函数
  2. GROUP BY子句
  3. DISTINCT 关键字
  4. ROWNUM 伪列
  5. 列的定义为表达式

当视图定义中包含以下元素之一时不能使insert:

  1. 组函数
  2. GROUP BY 子句
  3. DISTINCT 关键字
  4. ROWNUM 伪列
  5. 列的定义为表达式
  6. 表中非空的列在视图定义中未包括

屏蔽DML操作

可以使用 WITH READ ONLY 选项屏蔽对视图的DML 操作 任何 DML 操作都会返回一个Oracle server 错误

CREATE OR REPLACE VIEW empvu10
    (employee_number, employee_name, job_title)
AS SELECT	employee_id, last_name, job_id
   FROM     employees
   WHERE    department_id = 10
   WITH READ ONLY;
View created.

删除视图

删除视图只是删除视图的定义，并不会删除基表的数据

DROP VIEW view;

TOP-N分析

TOP-N查询（TOP-N分析）：就是获取某一数据集合中的前N条记录，实际应用中经常用到。

  1. Oracle中不支持SELECT TOP语句（MySQL中也没用此语句），需要借助ROWNUM伪列来实现TOP-N查询。
  2. ROWNUM伪列：是Oracle数据库对查询结果自动添加的一个伪列，编号从1开始。ROWNUM在物理上（查询目标表中）并不存在，是每一次查询过程中动态生成的，所以称为“伪列”。因此，不允许以任何查询基表的名称做为前缀，连接查询中涉及多个物理表，但也只动态生成一个伪列。

案例：查询工资最高的三名员工:

SELECT ROWNUM as RANK, last_name, salary 
FROM  (SELECT last_name,salary FROM employees
       ORDER BY salary DESC)
WHERE ROWNUM <= 3;

解析：此查询需要用到子查询，在已经排序好的基础上在top-n分析，而不是top-n和排序同时使用，这样会执行错误。

案例：

select *
from(
select rownum rn,employee_id,salary
from(
select employee_id,salary,last_name
from employees
order by salary desc
)
)where rn <=50 and rn >40
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值