Oracle数据库学习笔记

Oracle数据库学习笔记

作者：高达

第一天：

DBMS--数据库管理系统:

Date base Manage System

数据模型：

（1）层次模型类似于“倒树”型的结构

（2）网状模型

（3）关系模型RDBMS--Relation Date base System

记录和记录之间通过属性之间的关系来进行连接，保证数据独立性，并形成数据集之间的关系。

主键：关键词--PRIMARY KEY

用于行的区分，不会重复。主键可以由两列组成，叫做组合键。主键非空。如果为空则失去实体完整性。

外键：关键词--FOREIGN KEY

外键表示两个表之间的相关联系。外键的范围不能超过主键的范围，如果超过则失去引用完整性。

完整性：

是为保证数据库中数据的正确性和一致性。

(1).实体完整性:

数据行不能存在重复，也不能为空。即PK不重复不为空。

(2).引用完整性：

指建立两个关系建立联系的主外键的约束

1.要求子表中的相关项必须在主表中存在。

2.如果建立了主表和子表的关系,则：a.子表中的相关项目的数据，在主表中必须存在；b.主表中相关项的数据更改了，则子表对应的数据项也应当随之更改；c.在删除子表之前，不能够删除主表。

(3).域完整性:

保证表中数据的合理性

check 检查

default 默认

not null 不为空

unique 唯一约束

(4).自定义完整性:

根据用户需要自己定义。除了上述关键字，可以使用触发器来编写约束。

约束：关键词--CONSTRAINT

在创建表的时候添加约束。

目的：

确保表中数据的完整型

常用约束类型：

主键约束（Primary Key Constraint）：要求主键列数据唯一，并且不允许为空。

唯一约束（Unique Constraint）：要求该列唯一，允许为空，但只能出现一个空值。

检查约束（Check Constraint）：某列取值范围限制、格式限制等，如有关年龄的约束。

默认约束（Default Constraint）：某列的默认值，如我们的男性学员较多，性别默认为“男”。外键约束（Foreign Key Constraint）：用于两表间建立关系，需要指定引用主表的哪列。

不为空（not null）：不可以为空。

创建表：

建表语句：

创建：

CREATE TABLE 表名

(列名数据类型约束条件,主键,

列名数据类型);

添加：

ALTER TABLE 表名

ADD 新列名数据类型约束

删除列：

ALTER TABLE 表名

DROP COLUMN 列名;

修改列类型：

ALTER TABLE 表名

MODIFY 列名类型;

添加主键：

ALTER TABLE 表名

ADD PRIMARY KEY(列名);

添加删除修改约束：

在创建完表后添加约束：

ALTER TABLE emp add empno primary key(empno);--主键约束，可以把多个字段设为主键ALTER TABLE emp add sex check (sex in('女','男')),--check约束

ALTER TABLE emp modify name not null;--非空约束（特殊）

ALTER TABLE emp constraint name unique(name);--唯一约束（要求不可重复）

ALTER TABLE emp add constraint empno foreign key(deptno) references dept(deptno);--在创建完表后添加外键约束

禁用约束：

ALTER TABLE 表名DISABLE CONSTRAINT 约束名;

启用约束：

ALTER TABLE 表名ENABLE CONSTRAINT 约束名;

删除约束：

ALTER TABLE 表名DROP CONSTRAINT 约束名;

第二天：SQL 结构化查询语言

SQL --Structured Query Language

SQL 的分类：

数据定义语言（DDL）：Create,Drop,Alter

数据操作语言（DML）：Update,Insert,Delete、select

数据控制语言（DCL）：Grant Revoke

事务控制语言:commit rollback savepoint

数据操纵语言：DML

INSERT 插入数据：

INSERT INTO 表名属性1，属性2 V ALUSE 常量1，常量2；INSERT INTO 表名SELECT * FROM 表名；

UPDATE 修改数据：

UPDATE 表名SET 列名=新数据；

DELETE 删除数据：

DELECT FROM 表名WHERE 主键名；

SELECT 查询数据：

SELECT * FROM EMP;--查询全部信息

SELECT ENAME AS 名字,JOB AS 工作FROM EMP;--起别名

SELECT ENAME||'IS A'||JOB AS INFO FROM EMP;--增加相关联的列

SELECT * FROM EMP WHERE ENAME='SMITH';--条件查询

SELECT * FROM EMP WHERE JOB='MANAGER' OR JOB='CLERK';--组合查询AND,OR SELECT * FROM EMP WHERE DEPTNO IN (10,20);--等价于DEPTNO=10 OR DEPTNO=20,NOT IN查询不在（）里的内容

SELECT * FROM EMP WHERE COMM IS NULL;--查询COMM为空,IS NOT NULL不为空SELECT * FROM EMP WHERE JOB<>'MANAGER' AND SAL>3000;--<>,!=不等

SELECT DISTINCT(DEPTNO) FROM EMP;--DISTINCT用于去重

--比较=,>,<,>=,<=,<>, NOT+以上字符

--确定范围包括两边BETWEEN AND NOT BETWEEN AND

--确定集合IN,NOT IN

--字符匹配LIKE,NOT LIKE

--空值IS NULL,IS NOT NULL

--多重条件AND OR

聚合函数：

--COUNT(列名)统计个数,求和SUM(),求平均A VG(),求最大MAX(),求最小MIN()

--分组GROUP BY

SELECT COUNT(JOB) FROM EMP GROUP BY JOB; --后加HA VING加条件判断

在查询语句中，当聚合列和非聚合同时出现时，要求非聚合列必须写在GROUP BY 子句中用于分组的列，分组条件最好写在SELECT子句中

--排序ORDER BY

SELECT * FROM EMP ORDER BY SAL ASC,EMPNO DESC;--默认是升序，升序后加ASC,降序加DESC。先按照前面的排序，之后再按后面的排序

--模糊查询（LIKE只对字符类型有效）

SELECT * FROM EMP WHERE ENAME LIKE '%A%';--%表示无限制占位,LIKE 'S%'以S开头,NOT LIKE '%S'不以S结尾,-为占一位,'_A%'找到第二个字母为A

WHERE,HAVING区别：

WHERE在分组之前过滤，不能使用聚合函数。HA VING在分组之后过滤，可以使用聚合函数。

创建新用户：

语法：

CREATE USER MARTIN(用户名）

IDENTIFIED BY martinpwd（密码）

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP;

更改和删除用户：

ALTER USER 命令可用于更改口令：

ALTER USER MARTIN IDENTIFIED BY martinpass;

DROP USER 命令用于删除用户：

DROP USER MARTIN CASCADE;

授予权限：

GRANT CONNECT TO MARTIN;

Grant 权限名to 用户名；

CONNECT角色允许用户连接至数据库，

并创建数据库对象

GRANT RESOURCE TO MARTIN;

RESOURCE角色允许用户使用

数据库中的存储空间

GRANT CREATE SEQUENCE TO MARTIN;

此系统权限允许用户在当前模式中创建

序列，此权限包含在CONNECT角色中

对象授权：

GRANT SELECT ON TEST TO MARTIN;

Grant 权限名（查询，修改。）on 表名to 用户名。

允许用户查询TEST 表的记录

GRANT UPDATE ON TEST TO MARTIN;

允许用户更新TEST 表中的记录

GRANT ALL ON TEST TO MARTIN;

允许用户插入、删除、更新和查询

TEST 表中的记录

撤销权限：

Revoke select on表名from 用户名;

事务：

所谓事务是用户定义的一个数据库操作序列，这些操作要么全做要么全不做，是一个不可分割的工作单位。

Commit 提交事务

Rollback 回退事务

SavePoint 设置事务保存点，即回滚时，可滚至指定的位置。

savepoint p1;设置保存点。不提交既可以回滚。

第三天：

ORACLE数据的逻辑结构:

ORACLE的主要数据类型：

Character 数据类型：

Char

Varchar2

Long

Number 数据类型：

Number(p, s)

Date 数据类型：

Date

Timestamp

Interval day to second

Interval year to month

Timestamp with time zone

Timestamp with local time zone

Raw 和Long Raw 数据类型：

用于存储二进制数据

LOB 数据类型：

CLOB

BLOB

BFILE

SQL*Plus编辑等常用命令：

1、append

功能：往SQL缓冲区的当前行末增加SQL文本。

2、change

功能：替换缓冲区当前行中第一次找到的指定文本。

用法：change/旧文本/新文本

3、del

功能：从缓冲区中删除1行或多行。

用法：del 行号;

4、input

功能：在缓冲区的当前行之后增加1行或多行SQL文本。

5、list

功能：列出缓冲区中的1行或多行，如果后面不跟参数子句的话列出所有行。

用法：list

6、desc

功能：列出表、视图、同义词的列定义，及函数或存储过程的说明。

7、run

功能：列出并执行缓冲区中当前SQL语句或PL/SQL块。

用法：run

9、execute

功能：运行一个存储过程。

用法：execute statement

10、connect

功能：连接指定的Oracle用户。

Show lin （显示当前oracle的字符数）

Set lin 200 （设置当前oracle字符数）

还有两个不常用的命令1、clear；2、help。

Oracle的注释：

(1)/**/

(2)--

连接查询:

若一个查询同时涉及两个以上的表，则称为连接查询。连接查询是关系数据库中最主要的查询。

等值连接:

Select ename,emp.deptno,dname from emp e,dept d where e.deptno=d.deptno;

左外连接:

Select ename,emp.deptno,dname from emp e,dept d where e.deptno(+)=d.deptno;

右外连接:

Select ename,emp.deptno,dname from emp e,dept d where e.deptno=d.deptno(+);

自身连接:

Select e1.ename,’管理’,e2.ename from emp e1 join emp e2 on e1.mgr=e2.empno;

子查询（嵌套查询）:

在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询声。将一个查询块嵌套在另一个查询块的WHERE子句或HA VING 短语的条件中的查询为嵌套查询.。

select empno,ename,sal from emp where sal=(select max(sal) from emp);

子查询常用的谓词：

IN ，ANY，ALL

select empno,ename,sal from emp where sal>any(select sal from emp where job=‘MANAGER'); 函数:

字符函数:

lower：将大写或大小写混合的字符转换成小写；

upper：将小写或大小写混合的字符转换成大写；

initcap：将字符串中每一单词的首字母转换成大写，其余字符转换成小写；

concat：将字符串连接在一起；concat(‘str1’,’str2’)

substr：提取字符串的某一特定部分；substr(‘str’,index1,index2)

length：返回字符串的长度；length(‘str’)

instr：在字符串中查找某个子串的位置；instr(‘str’,’str1’)

lpad：在右边以指定字符填充字符型数据；lpad(‘str’,长度int,’str1’)

rpad：在左边以指定字符填充字符型数据；

ltrim：去除字符串左边空格；

rtrim：去除字符串右边空格；

trim：去除字符串头尾空格；

数值函数:

round：四舍五入到小数点后的n位小数。Round(double,int) trunc：截取（不进行四舍五入）到小数点后的n位小数。

mod(m,n)：取m除以n后的余数。

日期函数:

months_between(date1,date2)：返回date1减去date2的月份的数量。add_months(date,n)：返回date加上n月后的数量。

next_day(date, 星期名/数)：返回date后第一个“星期名”的日期。last_day(date)：返回date所在月的最后一天。

to_char：将日期或数值按指定格式转换成字符串。

to_number：将字符类型转换成数值型。

转换函数:

to_date：将字符型转换成日期型;

nvl：将空值赋以实际值。

其它常用函数:

select name,sex,decode(sex,'0','女','1','男',sex) from 表名;

第四天：

同义词：关键词--SYNONYM

同义词是现有对象的一个别名，分为私有和公有，私有只有当前用户才可访问，公有所有用户都可以访问。

优点：(1)简化SQL语句；

(2)隐藏对象名称和所有者；

(3)提供对对象的公共访问。

建立私有同义词：

CREATE SYNONYM EMP FOR SCOTT.EMP;

建立公有同义词：

CREATE PUBLIC SYNONYM EMP FOR SCOTT.EMP;

创建或替换现有同义词：

CREATE OR REPLACE SYNONYM EMP FOR SCOTT.EMP;

删除同义词：

DROP SYNONYM EMP;

序列：关键词--SEQUENCE

序列是用于生成唯一，连续序号的对象。可以是升序也可以是降序。

创建序列：

CREATE SEQUENCE sq_emp

minvalue 1

maxvalue 1000

start with 1

increment by 1;

nextval:返回序列下一个值

select sq_emp.nextval from dual;--查看序列中的当前值

currval：返回序列当前值

select sq_emo.currval from dual;--检索序列的当前值

向表中插入序列的值：

insert into scott.dept values(sq_emp.nextval,’something’,’something’);

更改和删除:

(1)更改：使用ALTER SEQUENCE语句修改序列，不可以更改start with参数。

alter sequence sq_emp maxvalue 6;

(2)删除：使用DROP SEQUENCE语句删除序列

drop sequence sq_emp;

视图：关键词--VIEW

以经过定制的方式显示来自一个或多个表的数据，是一个或多个表中的数据的简化描述。创建视图所依据的表成为基表。

视图的优点：

建立视图不用消耗其他空间，简化了SQL命令，表安全。

创建视图：

create or replace view 名称as

select *

from 基表和条件

删除视图：

drop view 视图名称;

索引：关键词--INDEX

类似书的目录结构，提高SQL查询语句的速度，索引直接指向包含查询值得行的位置，减少磁盘的I/O，Oracle自动维护更新索引。

创建索引：

create index 索引名on 表名(列名1,列名2,);

自动产生索引的列：

主键列和唯一值列。

修改索引：

Alter index 索引名rebuild;

rebuild 实际是删除原来的索引后再重新建立。dba经常用rebulid来重建索引，用来减少硬盘碎片和提高应用系统的性能。

删除索引：

drop index 索引名;

创建唯一索引：

create unique index 索引名

组合索引：

是在表的多个列上创建的索引，索引中列的顺序是任意的。

数据字典：

是Oracle存放有关数据库对象信息的地方，用途是描述数据，比如一个表的创建者信息，创建时间信息，所属表空间信息，用户访问权限信息等。当用户在对数据库中的数据进行操作时遇到困难就可以访问数据字典来查看详细的信息。数据字典中的表是不能直接被访问的，但是可以访问数据字典中的视图。静态数据字典中的视图分为三类，它们分别由三个前缀够成：user_*、all_*、dba_*。

查看Oralce表：

(1)查看当前用户所有的表：SELECT table_name from user_tables;

(2)查看所有用户的表：SELECT table_name from al_tables; (3) 查看包括系统表的表：SELECT table_name from dba_tables;