Oracle笔记（三）

一、建表

建表语法：

        create table 表名（

                字段名 数据类型【默认值】【约束】，

                字段名 数据类型 【default值】【constraint约束名】【约束类型】，

                。。。。。

        ）

 

1. 合法标识符

1 ）由 字母 / 数字 /_ /$ / # 组成，其中数字不能开始

2 ）不能是关键字，不区分大小写，字数控制在 30 个字符以内

2. 数据类型

1 ）

数字类型（不分整数和小数）

① number( n1 ,n2 ) ：

有效位数为 n1 的数字，其中小数点后占 n2 位。 n1 最大取值为 38 ，省略 n2 则表示整数

② number ：一个很大的，数据库能表示的最大值，相当 java 里的 double

2 ）字符类型（不分字符和字符串）

① varchar2(n) ：不定长字符串，最大可存储 n 个字节 ，存储时会根据实际值的大小调整。 n 最大值为 4000

② char(n) ：定长字符串，不论实际值大小，一律分配 n 个字节 空间， n 最大取值 4000

③ nvarchar2(n) ：以 字符 为单位存放数据， n 最大取值为 1333

3 ）日期类型： date （ dd-mon-rr ） timestamp

4 ）大数据类型： CLOB （字符大对象 ,4G ） BLOB （二进制大对象 , 4G ）

5 ）注意： java 中的 Boolean 类型，在数据库里通常使用 “ 一位数字 0 or 1 ”

3. 约束类型

1 ）主键约束（ primary key , 简称 pk ） --- 表里的唯一标识符，非空 唯一，表里最多只有一个主键

2 ）非空约束（ not null , 简称 nn ）

3 ）唯一约束（ unique , 简称 uk ） --- 通常也称为 ” 唯一键 ” , 有值的时候 , 值唯一 , 空值不在统计范围 . 4 ）外键约束（简称 fk ） --- 通常使用关键字 references 定义 , 必须指向另一张表的 ” 主键 ” 或者 ” 唯一键 ”

5 ）检查约束（简称 ck , 也称为 ” 自定义约束 ” ） --- 使用关键字 check( 约束条件 )

4. 案例

-- 创建 team 表，有 id 和 name 两个字段

create table team(

        id number( 3 ) constraint team_id_pk primary key,

        name varchar2( 15 ) not null

);

-- 创建 student 表，有字段（ id name mobile ， email ， married 婚否， tid 外键）

create table student(

        id number( 7 ) primary key,

        name varchar2( 15 ) not null ,

        mobile char ( 11 ) constraint student_mobile_nn not null constraint student_mobile_uk         unique,

        email varchar2( 30 ) unique check( email like '%_@_%' ),

        birthday date default sysdate,

        married number( 1 ) default 0 check(married in ( 0 , 1 )), -- 应对 java 中的 boolean 类型数据

        tid number( 3 ) references team(id)

);

 二. DML命令 ---- insert update delete

1. insert命令 ----- 插入

1）全表插入

语法：insert into 表名 values（值1，值2，......）

要求：

1.values后值的个数，类型数量必须与表里的字段保持一致

2.如果不方便给定值的字段（可以为空字段）必须使用null值占位

insert into student values ( 1 , 'baizhi' , '13810020030' , 'baizhi@123.com' ,sysdate);

insert into student values ( 2 , 'luxw' , '12345678901' , 'luxw@123.com' , null );

 2）选择插入

语法：

        insert into表名（字段1，字段2，....）values（值1，值2，....）；

要求：

1.value 后值的个数，类型，数量必须与表名后面给定的字段保持一致

2.对于表里‘非空’并且‘没有默认值’的字段必须出现

insert into student(id,name,mobile,email) values ( 3 , ' 翠花 ' , '12345678909' , 'cuihua@123.com' );

 2. update 命令 ---- 更新

 语法：

        updayte表名 set 字段1=新值，字段2=新值，....where条件；

update student set name= 'baizhi3' ,mobile= '23456789012' where id= 3 ;

 3. delete命令 ---- 删除数据

 语法

        delete from 表名 ....where条件；

三、数据库里的其他对象--序列 视图 索引

1. 序列 ( sequence )

1 ）

作用：可以生成一系列唯一有序数值的对象，

是一个公开的公共的对象。

2 ）创建 :

语法：

                create sequence 序列名

                【start with num】 -------起始值

                【increment by num】------步长

                【maxvalue num】---------最大值

                【cycle| nocycle】----- 是否支持循环

                【cache num| nocache】------ 是否有缓存

3 ）使用

① 获取： 通过调用序列提供的一个变量 nextval 来获取序列里的值 ，一旦获取到值，

值与序列再无关系。

② 案例

select seq1.nextval from dual;

insert into team123 values(seq1.nextval,'aaa');

 注意：如果在一条 sql 命令里多次调用 nextval，只会得到一个值。

2. 视图（View）

1 ）概念：一条起了名字的查询语句，

视图没有数据 , 只有命令

2 ）创建： create view 视图名 as 查询语句 ;

3 ）作用：

-- 1. 简化查询 , 提高查询语句的复用性

-- 2. 帮助做表的权限管理

-- 1 ） create view emp as select employee_id,last_name,…. from employees;

-- 2 ） create view boss as select * from employees;

-- 3 ）禁掉 employees 表的访问权限 , 对外提供 emp 和 boss;

 4）删除： drop view v1;

3. 索引（ index ）

1 ）创建： create index 索引名 on 表名 ( 字段名 ) ;

2 ）概念：是一个两列多行的表（对建立索引的字段值排序 ---- 值所在的物理位置 rowid ）

3 ）作用：当建立索引的字段出现在查询条件里时， db sv 会自动应用索引提高查询效率

4 ）注意：索引不是越多越好，浪费空间，降低增删改效率

oracle server 会默认为表里的 ” 主键 ” 和 “ 唯一键 ” 自动添加索引

四. 事务（transaction） 【重点】

1. 概念

是操作数据库数据的最小单位，是由一组不可再分的 sql 命令组成的集合 ，事务的大小由实际的业务决定。

2. 原理

数据库服务器（ db sv ）会为每一个连接上来的 client ，开辟 一小块内存空间（回滚段），用来暂时缓存 sql 命令的

执行结果 ，当事务结束时根据 client 的指令决定数据的处理方式， client 发出 commit 指令（成功） ，则将回滚

段的数据写入数据文件； client 发出 rollback （失败） ，则清空回滚段里的数据。

3. 边界

1 ） begin ：从写第一条 sql 命令那一刻

2 ） end

① 如果输入一组 DML （ insert update delete ）命令，需要手动显示输入 commit 或者 rollback 结束事务

② 如果输入一条 DDL 或者 DCL 命令，则命令成功后自动 commit

③ 如果输入一组 DML 命令后退出 client ，正常退出做 commit ，非正常做 rollback

4. 数据安全 ---- 每一行记录都有一把锁(“行级锁”)

1 ）写锁

oracle 服务器会为事务操作（ insert update delete ）过的记录自动添加 ” 行级写锁 “ ，在锁没有释放之前 , 其他事 务不能够操作这条记录，在当前事务结束时，锁会被释放，然后随机分给一个等待锁的事务。

2 ）

查询命令 select 不受行级写锁的限制

3 ）读锁：在执行 select 命令时，手动获取行的锁标记

-- 查询时获取所有数据，并针对结果里的行进行加锁

select … from … for update ;

-- 对查询结果加锁时，以 nowait 方式加锁（如果无法加锁，直接返回）

select … from … for update nowait ;

-- 对查询结果加锁时，如无法直接加上，则等待 num 秒

select … from … for update wait num;

 

5. 事务的四大特性（ACID）

1 ） A （ atomic ）原子性：组成事务的 sql 命令不可再分

2 ） C （ consistancy ）一致性：在事务运行中，数据的状态要与事务所做的操作保持一致

3 ） I （ isolation ）

隔离性：事务并发运行时，事务之间不应该互相影响

4 ） D （ durable ）持久性：事务结束时，数据必须做 ” 持久化 “ 操作