Oracle笔记系列这几篇是来自一位老师的笔记，分享给大家放在简书上，主要方便自己时常复习，还有学习Oracle的伙伴参考。（@来自邵永波老师整理分享）

多表查询

笛卡尔积

虚拟表

s_emp  s_dept

上述两个表所有的列在虚拟表都会出现，

引用那些列  原始表.列名

连接本质  去除笛卡尔积中 无效的行

分组查询

group by

having 组级别筛选;

分组函数可以出现的位置：

select、having、order by

where 后面绝对不可以出现分组函数

分组查询的时候，select、order by后面出现的列，如果没有分组函数修饰，则必须出现在group by后面;

分组并不会 筛选掉数据行，只是做了分类

如果在分组基础上select,则一定注意输出的内容 必须是【组级别】的。

子查询

两种形式

where或having 里面用到值，通过select获取到的

select时需要一张额外的表，select获取到的

第七章：运行时参数

sql语句中的值,我们可以使用一个参数来代替,然后每次运行的时候都可以重新输入这个值

运行时参数 格式：&参数名;

参数的名称任意；

参数可以是数字，也可以是字符串，可以是整个sql子句[where|order by];

一个sql语句中也可以写多个参数;

1.参数可以是数字

select last_name,salary,dept_id

from s_emp

where id=&id;

2.参数可以是字符串(加单引号，不加单引号)

select last_name,salary,dept_id

from s_emp

where last_name=&name;

select last_name,salary,dept_id

from s_emp

where last_name='&name';

3.参数可以出现多个

select last_name,salary,dept_id

from s_emp

where

salary>&a

and

salary<&b;

4.参数可以 设置为整个条件

select last_name,salary,dept_id

from s_emp

where &con;

//输入1=1  不限制

5.进一步放开条件

select last_name,salary,dept_id

from s_emp

&con;

注意:&变量名 表示使用这个oracle定义的变量,如果这个变量之前没有定义过,那么这个时候会让你重写输入这个变量的值.如果之前有定义过,那么就是要之前定义过的值

6.几个关键字：define undefine accept prompt hide命令

例如:

命令define(缩写def)可以定义一个变量

定义变量: def A=s_emp

查看定义的变量:  def

取消定义的变量:  undef A

然后变量A之前定义过,那么这里会之前替换为之前定义的值

select id,last_name,salary

from &A;

accept命令也可以【定义】一个【变量】,而且变量的值需要用户再次输入

例如:

accept A

回车之前需要用户再次输入变量A的值

之后可以使用def命令来查看刚刚定义的A变量

prompt可以在用户输入的时候显示一个【提示信息】:

例如:

accept name prompt '请输入name变量的值: '

hide可以【隐藏】用户的【输入内容】不被看见

例如:

accept name prompt '请输入name变量的值: ' hide

注意:这些定义的都是【临时变量】,sqlplus退出后重新登录进来就没有了

第八章：数据建模和数据库设计

1.软件开发流程和数据库设计

软件开发的步骤可大致分为:

1.需求分析

2.系统设计

3.编码实现

4.系统测试

5.运行维护

系统设计中一个重要的环节就是数据库设计. 

数据库设计的时候需要先进行数据建模(实体关系图 E-R图)

数据建模的依据就是前期所做的需求分析.

2.数据建模

参照179页的图形:

1.Model of system in client's mind

用户的想法

2.Entity model of client's model

实体模型图

3.Table model of entity model

具体的表

4.Tables on disk

存到数据库里面(磁盘)

3.实体-关系图

实体-关系图(Entity Relationship Diagram),也称为E-R图,提供了表示实体、属性和关系的方法，用来描述现实世界的概念模型。

构成E-R图的基本要素是【实体、属性和关系】

a.实体(Entity)：实体用来表示具有相同特征和性质的事物(类似于java的类)，实体由实体名和实体属性来表示。【实体侧重数据存储，类还包含行为】

b.属性(Attribute)：实体所具有的某一特性，一个实体可以有若干个属性

c.关系(Relationship)：实体彼此之间相互连接的方式称为关系。一般可分为以下 3 种类型:

一对一关系 (1 ∶ 1)

人  身份证

一对多关系 (1 ∶ N)

一个老师  多个学生

多对多关系 (M ∶ N)

一个学生 选 多门课程，

一门课程 由 多个学生选

4.实体间关系

员工  --  订单  --  明细

    may-be 和 must-be

在实体与实体之间的关系中,都会存在着may-be和must-be这俩种情况,

例如:

系统中有顾客和订单俩个实体(1:N关系),一个顾客对应多个订单,一个订单对应一个顾客,而且一个顾客可以(may be)没有订单和他对应,一个订单一定(must be)会有顾客和它对应.

5.ER图中符号的表示

1) # : 唯一, 以后可能表示为主键

2) * : 非空

3) o : 可有可无

4) 虚线: may be  顾客这边虚线，顾客可能没有订单

5) 实线: must be 订单这边实线，订单一定是属于某个客户。

6) 竖杠(|): 代表要强制在(|)一方建立一个联合主键，将对方ID拿过来做联合主键

7) 伞状图标代表多的一方,不是伞状图标则代表一的一方

6.数据库具体设计

数据建模完成之后,可以把【E-R图】转换成数据中的【表】

1).实体的名字转换为表的名字

2).实体的属性转换为表中的列

3).具有唯一特点的属性设置为表中的主键

4).根据实体之间的关系设置为表中某列为外键列(主外键关联)

注意:第四步主要是:【实体关系--->表关系】

一对一的关系: 主外键放在两个表中 都可以

一对多的关系: 主键放到一，外键放到多

多对多关系:

例如学生对课程, 利用桥表实现，桥接一下，(两个表中的【主键】都拿过来【做外键】，然后【两个外键】联合到一起)【作联合主键】。

学生表 课程表

id  name id  name

1 zs 1    数学

2 ls 2 语文

3    ww

学生选课表(桥表)

学生id 课程id

1 1 数学

1 2 语文

3 1 数学

2 2 语文

7.主键和外键

【主键】:  s_emp  id  s_dept id  s_region id

在创建表的时候，通过primary key来设置主键列。

1).能做主键的列必要满足【非空唯一】的特点

2).只要满足非空唯一的任何列都可以做主键(具备资格，但并非只要非空唯一，就是主键)

3).可以让表中一个【有意义的列做主键】,比如说学号,它既表示学生学号又作为表中的主键,因为这个列满足非空唯一的条件

4).也可以找一个【没有意义的列做主键】,就是用来唯一标识一行记录的

5).我们可以让【多个列联合】在一起做表中的主键,那么它就是【联合主键】,要求这几个列的值联合在一起是非空唯一的

课程成绩表

学号  课程  成绩

1 1 50

1 2 40

2 1 40

2 2 60

主键的作用:

1）保证实体的完整性

2）加快数据库的操作速度

3）在表中添加新记录时，数据库会自动检查新记录的主键值，不允许该值与其他记录的主键值重复。

4) 数据库自动按主键值的顺序显示表中的记录。如果没有定义主键，则按输入记录的顺序显示表中的记录。

主键不是非要不可，可以从：

1).是否满足业务要求

2).数据查询效率（主键可以提高查询效率，当然合理的索引替代也可以）

这两个角度权衡是否需要主键。

主键列 所在的表  属于主表

外键列 是依赖主键列的，外表

外键列  依赖  主表中主键列

【外键】:

1).表中的某一个列声明为【外键列】,一般这个外键列的值都会【引用于另外一张表的主键列的值】(有非空唯一约束的列就可以,不一定非要引用主键列)

2).另外一张表的主键列中出现过的值都可以在外键列中使用,【没有出现过的值,绝对不能使用】

3).【外键列值可以为空】,前提是这个外键列在表中不做主键。

我们也可以把表中的外键列当做主键来使用(只有满足非空唯一的要求就可以)

4).如果把B表中的【联合主键】的值引用到A表中做外键,那么A表引用过来的时候也要把俩个列的值都引用过来,那么它们在A表中就会作为一个【联合外键】出现

8.数据库范式

设计关系数据库时，遵从不同的规范要求，才能设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。

目前关系数据库有六种范式：

第一范式(1NF)

第二范式(2NF)

第三范式(3NF)

巴斯-科德范式(BCNF)

第四范式(4NF)

第五范式(5NF，又称完美范式)

注意:满足最低要求的范式是第一范式(1NF)。在第一范式的基础上进一步满足更多规范要求的称为第二范式(2NF)，其余范式以次类推。

一般说来，数据库只需满足第三范式(3NF)就行了

第一范式:

    一个表中,每个【列的值是不能再分割的】.

    例如:我们设计的表中有一个列是:爱好

    这个列的值可能会是这样:足球篮球乒乓球

    但是这值是可以再分割的:足球、篮球、乒乓球

    所以这种设计是不满足第一范式

第二范式:

第二范式是在满足第一范式的基础上

    表中的【非主键列都必须依赖于主键列】

    例如:

    订单表: 订单编号 是主键

订单编号  订单名称  订单日期  订单中产品的生产地

这几个非主键列中,产品生产地是不依赖于订单编号的,所以这种设计是不满足第二范式

第三范式:

第三范式是在满足第二范式的基础上

表中的非主键列都必须【直接依赖】于主键列,而不能间接的依赖.

(不能产生依赖传递)

    例如:

    订单表:  订单编号 是主键

订单编号  订单名称  顾客编号  顾客姓名

顾客编号依赖于订单编号,顾客姓名依赖于顾客编号,从而顾客姓名间接的依赖于订单编号,那么这里产生了依赖传递,所以这个设计是不满足第三范式的。

第九章：建表

create 实现，属于DDL

1.建表的格式(两种)

格式1：

create table 表名(

列名1 数据类型 [列级约束],

列名2 数据类型 [列级约束],

列名3 数据类型 [列级约束],

列名4 数据类型 [列级约束]

);

格式2：

create table 表名(

列名1 数据类型 [列级约束],

列名2 数据类型 [列级约束],

列名3 数据类型 [列级约束],

列名4 数据类型 [列级约束],

[表级约束1],

[表级约束2]

);

2.建表需求

根据以上格式,可以看出,建表过程中,需要以下几种东西:

a.关键字

b.表名

c.列名

d.数据类型

e.【约束】

f.固定格式

其中,约束分为【列级约束】和【表级约束】,列级约束和表级约束都是【对列中的值进行约束】的。

例如:列的值不能为空,列的值必须是唯一的等等，都属于列的约束。

列级约束 是跟在列的声明后面写的；

表级约束 是在全部列声明完之后写的；

注:列级约束也称为【行级约束】。

同时,【列级约束和表级约束都是可选的】,也就是都可以写也可以不写。例如:

create table 表名(

列名1 数据类型,

列名2 数据类型,

列名3 数据类型,

列名4 数据类型

);

3.表名要求

a.必须是字母开头

b.必须是1-30个字符之间的长度

c.表名中只能出现A–Z, a–z, 0–9, _, $, #

d.不能和数据库中已有对象的名字重复

e.不能是数据库中的关键字

命名法：项目名_模块名_表名;

4.列常用数据类型

a.char 默认长度是1，最长不超过2000字节

b.varchar

c.varchar2 默认长度是1，最长不超过4000字符

d.number(p)

  number(p,s) 既可以存浮点数，也可以存整数,p表示最大位数，s指小数位数，可存负数

e.date

  timestamp 不但存储日期的年月日，时分秒，以及秒后6位，同时包含时区

f.BLOB 存储二进制对象，如图形、视频、声音等

h.CLOB 存储大的文本，比如存储非结构化的XML文档

BLOB和CLOB都是【大字段】类型

5.char varchar varchar2的区别及特点(面试题)

name char(20) tom

last_name varchar(20) jack

first_name  varchar2(20) larry

a．CHAR的长度是固定的，而VARCHAR2的长度是可以变化的

b．CHAR的效率比VARCHAR2的效率稍高。

c．VARCHAR是数据库标准的字符类型

d. VARCHAR2是Oracle自己开发了一个数据类型VARCHAR2

e. VARCHAR2将数据库中varchar列可以【存储空字符】串的特性改为存储【NULL值】。

f. Oracle建议使用VARCHAR2而不是VARCHAR

6.列的约束

列的约束就是对这个列中的值的要求(可有可无)

1).主键约束  PRIMARY KEY  primary key

2).外键约束  FOREIGN KEY  foreign key

3).唯一约束  UNIQUE      unique

4).非空约束  NOT NULL    not null

5).check约束 CHECK       check(类似枚举)

列级约束/行级约束:在列的后面直接加上的约束

create table 表名(

列名1 数据类型 列级约束,

列名2 数据类型 列级约束,

列名3 数据类型 列级约束,

列名4 数据类型 列级约束

);

表级约束:在所有列声明以后加的约束

create table 表名(

列名1 数据类型 列级约束,

列名2 数据类型 列级约束,

列名3 数据类型 列级约束,

列名4 数据类型 列级约束,

表级约束1,

表级约束2

);

7.建表实例

例1:普通的建表例子

create table student(

id number primary key,

name varchar2(20) not null,

age number,

birthday date

);

select table_name from tabs;

drop table student;

例2:使用四种【列级约束】

主键约束 非空约束 唯一约束 check约束

create table student(

id number primary key,

name varchar2(20) not null,

email varchar2(30) unique,

gender char(1) check(gender in('f','m')),

age number,

birthday date

);

desc student; 查看表结构

drop table student;

例3:使用列级约束 声明 外键约束

外键约束格式:

外键列  类型  references 主表(主键);

create table t_customer(

id number primary key,

name varchar2(20) not null

);

create table t_order(

id number primary key,

content varchar2(20) not null,

customer_id number references t_customer(id)

);

注意：先建立主键表，再建立外键表，有【次序之分】。

drop table t_customer;

drop table t_order;

注意:订单表中的外键列customer_id的值,是引用自顾客表t_customer中的主键列id的值，【删除表主外键列值】的时候，有一定限制。

a.这时候【直接删除顾客表】是不行的,因为t_customer的主键列的值被别的表给引用了.

b.我们可以【先删除订单表t_order】,然后再删除t_customer就可以了

c.如果非要想直接删除到顾客表t_customer,就需要使用下面的语句:

drop table t_customer cascade constraints;

d.该语句表示,删除t_customer表的同时,也【级联删除与表相关的约束】,外键约束没有了,这个表自然可以被删除掉

e.【cascade是级联】的意思（选择省份，自动弹出市）

8.表级约束具体应用

例4:普通建表语句

create table student(

id number,

name varchar2(20) not null,

age number,

email varchar2(100) not null,

gender char,

primary key(id),

unique(email),

check(gender in('f','m'))

);

drop table student;

注:【非空约束】(not null)【不能】声明成【表级约束】

例5:

使用表级约束 声明 外键约束

create table t_customer(

id number primary key,

name varchar2(200) not null

);

create table t_order(

id number primary key,

content varchar2(200) not null,

customer_id number,

foreign key(customer_id) references t_customer(id)

);

表级外键约束格式:

foreign key(外键列) references 主表(主键列);

drop table t_order;

drop table t_customer;

例6:使用表级约束 声明 【联合唯一】约束

create table student(

id number primary key,

class varchar2(50) not null unique,

name varchar2(50) not null,

unique(class,name)

);

drop table student;

注意:学生的班级和学生的名字联合起来必须是唯一的(联合唯一)

注意:联合唯一约束【必须使用表级约束】来声明

例7:使用表级约束 声明 【联合主键】

create table t_customer(

id number,

name varchar2(50),

primary key(id,name)

);

drop table t_customer;

例8:使用表级约束 声明 【联合外键】

create table t_customer(

id number,

name varchar(50),

primary key(id,name)

);

create table t_order(

id number,

price number not null,

customer_id number,

customer_name varchar(50),

foreign key(customer_id,customer_name) references t_customer(id,name)

);

drop table t_order;

drop table t_customer;

注意：只要是【联合的】(联合主键、联合外键、联合唯一)，都【必须用表级约束】。

9.表级约束和列级约束对比(面试题)

1).表级约束和列级约束所写的【位置不一样】

2).【not null】约束不能用表级约束来声明

3).表级约束和列级约束声明【语法稍有所不同】

4).如果要声明的约束为联合主键、联合外键、联合唯一的时候,就一定要用表级约束.

10.constraint关键字

1).constraint是【约束】的意思

2).建表的时候可以给约束起一个名字,这个名字起的规律一般会是:【表名_列名_约束类型】

torder_customerid_for

3).如果没有给约束起名字,那么系统也会给这个约束起一个【默认的名字】,这不过这个默认的名字对我们来说并不友好(我们可能看不懂)

4).将来我们可以根据约束名而找到这个约束,然后进行修改

格式:

【constraint 约束名 具体约束;】

例1:列级约束 起约束名字

格式:

列名 数据类型 constraint 约束名 具体约束;

create table student(

id number constraint student_id_pk primary key,

name varchar2(100) constraint student_name_nn not null,

email varchar2(100) constraint student_email_un unique,

gender char(1) constraint student_gender_ck check(gender in('f','m')),

age number,

birthday date

);

drop table student;

例2:表级约束 起约束名字

格式: constraint 约束名 具体约束(列名);

create table t_customer(

id number,

name varchar2(20) not null,

age number,

email varchar2(100),

gender char,

constraint cus_id_pk primary key(id),

constraint cus_email_un unique(email),

constraint cus_gender_ck check(gender in('f','m'))

);

create table t_order(

id number,

price number not null,

customer_id number,

constraint order_id_pk primary key(id),

constraint order_cid_fk foreign key(customer_id) references t_customer(id)

);

drop table t_order;

drop table t_customer;

11.特殊的建表

建立一张表和s_dept一模一样的表

create table 表名

as

select 语句;

例1:

将s_dept的表结构和表中的数据全部复制过来

create table test1

as

select * from s_dept;

例2:

只拿来s_dept的表结构,没有数据

create table test22

as

select * from s_dept

where 1=2;

例3:

只复制表中某几个列以及数据

create table test3

as

select id,last_name,salary

from s_emp

where id<10;

12.ON DELETE CASCADE

on delete cascade

这个语句是在建表中,声明外键约束的时候一个可选项,我们后面的【DML章节】中再讨论

第十章：数据字典(了解)

作用:帮助用户了解当前数据库的一些信息或是对象的信息或是用户的信息.

注意:

a.数据字典随着数据库被创建 自动创建。

b.数据字典中的数据被数据库服务器自动更新和维护。

对象: 表 视图 索引 同义词 序列

常见的数据字典(它们都是视图)

USER开头的视图里面存放着用户自己拥有的对象

ALL开头的视图存放着用户有权限查看的对象

DBA开头的视图存放着数据库所有的对象

V$开头的视图存放数据库运行的一些性能属性数据

1、以user开头的数据字典： 包含当前用户所拥有的相关对象信息。

//查询用户拥有的所有表的名字

select table_name

from user_tables;

//查询用户对象表,找出对象类型是TABLE类型的对象名字

【table view sequence index synonym等都是oracle中的对象】

//注意字符串的值是区分大小写的

select object_name

from user_objects

where object_type = upper('synonym');

输出结果可以分为两类：实际存在的，删除后的残留数据

//查询用户对象表,找出对象类型的类型都有哪些

select distinct object_type

from user_objects;

//查询出s_emp表中的列及其对应的约束名字

select column_name,constraint_name

from user_cons_columns

where table_name = 'S_EMP';

//查询出s_emp表中的约束名字

select constraint_name

from user_constraints

where table_name = 'S_EMP';

注意: 表名必须大写。

2、以all开头的数据字典：  包含当前用户有权限访问的所有对象的信息

//查到当前用户有权限访问的对象

select table_name from all_tables; 

3、以dba开头的数据字典：  包含数据库所有相关对象的信息。

//只能是有dba权限的用户查询,能查到数据库中所有对象

select table_name from dba_tables;

(sys system,conn切换用户system,密码oracle)

其他视图: DICTIONARY

dictionary 视图中只有俩列:

TABLE_NAME 表示当前表的名字

COMMENTS 表示对这个表的描述

SQL> desc dictionary

名称

-----------------------------------------

TABLE_NAME

COMMENTS

select *

from dictionary

where table_name='USER_TABLES';

select *

from dictionary

where table_name='ALL_TABLES';

select table_name

from dictionary

where table_name like 'USER%';

select table_name

from dictionary

where table_name like 'V$%';