oracle数据库(三)
一、建表(DDL)
-
create table 表名(
字段名1 数据类型[默认值] [约束],
字段名2 数据类型[默认值] ,
字段名3 数据类型[默认值] [约束]
);
注意:最后一个字段不加逗号
-
标识符命名规则:
(1) 由 字母 、数字 、_(下划线) 、$ 、# 组成,不能以数字开头
(2) 有长度限制,最多30个字符
(3) 不区分大小写
(4) 不能是关键字,不能与其它对象重名
-
数据类型
(1) 数字类型:
-
number(m,n):小数类型,数据总长度是m,其中小数点后位数是n(位数超出n,四舍五入)
number(5,2) : 存储 123.45 可以 ; 存储 123.4567 可以,最终存储内容是123.46 ; 存储 1234.5 不可以,整数位最多为3位
-
number(m):整数类型,数据长度为m
- number(5):存储 123 可以 ; 存储 12345 可以 ; 存储 123456 不可以,超出最大长度
-
number:默认小数类型,数据库能存储的最大数据,相当于Java中的double。
-
integer : 相当于 number(38)
(2) 字符串类型:
-
char(n):固定长度字符串类型(n最大值为2000),分配n个字节空间长度
- char(10):‘abcdef’ ,不足10位用空白字符填充
- 特点:
- 优点:数据管理效率高
- 缺点:空白字符浪费大量空间
-
varchar2(n):可变长字符串类型(n的最大值为4000),最多分配n个字节空间长度。
- 注意: oracle独有的数据类型
- varchar2(10):‘abcdef’ ,存储后空间长度为5.
- 特点:
- 优点:节省数据存储空间
- 缺点:数据管理效率相对较低
-
实例: 名字 varchar2(100) 手机号 char(11) 邮箱 varchar2(200)
(3) 日期类型:
- date:年月日时分秒星期,精确到秒
- timestamp:精确到毫秒
(4) 布尔类型:
- oracle不支持布尔类型,可以使用number(1) 用 0 和 1表示 或是
使用 char(1)用 y/n表示或是 使用 char(3) 用 男/女表示
- 注意:oracle的编码方式默认为utf-8,汉字占3个字节
(5) 大数据类型:
- clob:字符大数据(最大4G),如文本文件等
- blob:二进制最大数据(最大4G),如视频等
-
-
约束
(1) 主键约束:Primary key,简称PK
- 作用:唯一标识表中的一行数据。如学号、工号等
- 特点:唯一、非空
- 语法:字段名 数据类型 primary key
- 注意:开发时,通常常每一张表都会有一个主键约束。
(2) 唯一约束:unique
- 作用:标识该字段的值不允许重复。如省份证号、手机号
- 特点:唯一、可以为空
- 语法:字段名 数据类型 unique
(3) 非空约束:not null
- 作用:标识该字段的必须有内容。如学生姓名
- 特点:非空、可以重复
- 语法:字段名 数据类型 not null
(4) 检查约束:自定义约束
-
作用:根据自定义规则限制该字段的内容。例如性别只能是男或女
-
语法:字段名 数据类型 check(检查约束的表达式)
- 性别只能是 男或女:check( sex in (‘男’,‘女’))
check( sex = ‘男’ or sex=‘女’ )
-
邮箱必须有@:check (email like ‘%@%’)
-
手机号码必须是11位:phone char(11) check(phone like ‘_____________’)
(5) 外键约束:foreign key 简称FK
-
作用:标识该字段的值不允许随便输入,必须是另一个表中的主键或唯一键
中存在的值。(标识当前表 [从表] 和另一个表 [主表] 之间的关系)
-
特点:可以重复、可以是null
-
语法:字段名 数据类型 references 主表名(主键/唯一键的字段名)
-
理解思路图:
-
默认值:
作用:标识该字段在不填写任意内容,系统默认分配的内容。
语法:字段名 数据类型 defualt 值
注意:给定的默认值的类型必须和该字段的数据类型一致
-
综合示例:
班级表信息:班级编号、班级名称
-
create table t_class( cls_id number(5) primary key, cls_name varchar2(20) not null );
学生表信息:学号、姓名、邮箱(必须有@)、出生日期(默认当前日期)、身份证号、
所在班级编号
-
create table t_student(
stu_id number(5) primary key,
stu_name varchar2(20) not null,
email varchar2(50) not null check(email like ‘%@%’),
birthday date default sysdate,
card_id char(18) unique,
cls_id number(5) references t_class(cls_id)
); -
注意:查询库中所有的表:select * from tabs;
以上主键约束、非空约束、唯一约束、检查约束、外键约束都是字段级的约束。 所谓的字段级约束是定义在某一个字段的后面。
-
-
联合约束
联合约束:使用表的任意一个字段无法标识主键或唯一约束时,使用多个键的组合来
进行约束
常见联合约束:联合主键、联合唯一约束
语法:
- 联合主键:primary key(字段名1 , 字段名2)
- 联合唯一约束:unique(字段名1 , 字段名2)
代码案例:实现以上选课的分配
- – 学生表
create table student(
s_id number(5) primary key,
s_name varchar2(20) not null
);
– 课程表
create table courses(
c_id number(5) primary key,
c_name varchar2(20) not null
);
– 学生和课程的关系
create table student_courses(
s_id number(5) references student(s_id),
c_id number(5) references courses(c_id),
primary key (s_id,c_id)
)
思路图分析:
二、增删改数据(DML)
-
添加数据
语法:insert into 表名(列1 , 列2 , … ) values (值1 , 值2 ,…)
作用:往表中插入一条数据。
案例:
-
insert into t_class(cls_id,cls_name) values(1,‘Java86’);
-
insert into t_student (stu_id,stu_name,email,birthday,card_id,cls_id)
values (100,‘zs’,‘10086@qq.com’, to_date(‘1989-09-10’,‘yyyy-mm-dd’),‘370818198904152922’,1);
注意:
-
表名后的列名,必须是该表中存在的字段,没有先后顺序
-
values后面的值列表,顺序、类型、个数必须和前面的字段列表完全一致
-
插入数据时,所有非空没有默认值的字段时,必须指定值
-
如果插入数据时,所有字段都依次指定值,则表名后的列名可以省略,但是值
-
列表的 顺序、类型、个数必须和表结构中的字段列表完全一致
- insert into 表名 values(值1 , 值2);
- insert into t_student values (101,‘lisi’,‘114@qq.com’, to_date(‘1996-09-10’,‘yyyy-mm-dd’),‘370818198904152952’,2);
-
如果插入外键列(FK)的数据时,引用的数据在主表中不存在,则运行报错,错误信息:
-
-
修改数据
语法:update 表名 set 列名1=新值 , 列名2=新值 where 过滤条件
作用:根据where过滤条件,修改符合条件数据中的指定列为 新值
案例:将编号为 100 的学生转入到2班
- update t_student set cls_id = 2 where stu_id = 100;
注意:修改数据时,也需要遵守约束和数据类型。
-
删除数据
语法:delete from 表名 where 过滤条件
作用:从表中删除符合过滤条件的数据
案例1:删除编号为100的学生。
- delete from t_student where stu_id=100
案例2:从班级表中删除班级编号为2的数据。
-
delete from t_class where cls_id = 2;
-
注意:如果被删除的数据已找到子记录(在从表中应用到该条数据),报错信息如下:
-
解决方法:
- 把该班中的学生转入到其他班级,然后再删除班级数据
- 先删除从表中对应的数据,再删除主表中的数据。(先将2班学生删除,再删除2班)
补充:删除表:
- delete from 表名;
- 特点:删除全表数据,效率较低。
- 表截断:truncate table 表名;
- 特点:直接将存储表数据部分的空间,直接清除,进而删除数据[非DML]
三、序列
-
作用:用于自动生成一组有顺序数字的数据库对象。
-
应用:通常用于insert语句,进行主键字段值的自动生成。
-
语法:
(1) create sequence 序列名;
- 创建默认的序列,从1开始,每次递增1
(2) create sequence 序列名 start with n increment by m
- 创建序列,从n开始,每次递增m
案例:创建一个序列,从1开始,每次递增2
- create sequence stu_seq start with 1 increment by 2;
-
使用:
(1) 获取序列中的下一个有效值: 序列名.nextval
(2) 获取序列中的当前值: 序列名.currval
- 获取序列当前值之前,必须至少使用过一次nextval
注意:
- 序列创建后,被所有的表共享
- 序列中的值一旦使用,将不能被重新生成
案例:使用序列,自动生成 t_student表中的主键。
- insert into t_student values (stu_seq.nextval,‘ww’,‘114@qq.com’, to_date(‘1989-09-10’,‘yyyy-mm-dd’),‘370818198904153366’,2);
补充:
(1) 查看当前用户相关的序列: select sequence_name from user_sequences ;
(2) 删除某一个序列:drop sequence 序列名;
四、视图
-
概念:一个起了名字的查询语句。
-
语法:
(1) 定义视图:create view 视图名 as select 语句
- create view emp as
select employee_id,last_name,salary,department_id from employees;
(2) 使用视图:select 列名,列名,列名 from 视图名;
- select * from emp; – 也可以写具体列名
(3) 注意:
- 视图是给sql查询语句定义名称,方便复用,简化SQL
- 视图相当于查询语句,不存储实际数据,在效率上没有得到提高
(4) 删除视图:drop view 视图名;
- drop view aa;
- create view emp as
五、索引(inder)
-
理解:
(1) 问题:数据量1000w(oracle)会出现查询效率急剧降低,如何解决?
(2) 解决:利用索引解决select 查询效率。
(3) 核心思路分析:
-
语法:
(1) 创建索引:create index 索引名 on 表(字段)
- create index last_name_index on employees(last_name);
(2) 使用索引:不需要手动使用,在查询中使用了创建索引的字段时,会自动使用索引,提高查询效率。
(3) 删除索引:drop index 索引名;
-
索引的特点:
(1) 索引不是创建的越多越好:索引会占硬盘空间;同时 增、删、改数据时,
需要同时维护索引中的数据,降低增删改操作的效率
(2) 通常会在经常会被查询的列上创建索引
(3) 主键和唯一约束的列,数据库会自动添加索引。
六、事务
-
引入:
(1) 操作:将 张三 建行账户的 10000 转账给 李四。
- account:[id , name , balance]
- 用户:
- 100 张三 20000.0
- 101 李四 1000.0
(2) 思路:
-
将张三的余额修改为 10000.0
update account set balance = 10000.0 where id = 100
-
同时将 李四的余额修改为 11000.0
update account set balance = 11000.0 where id = 101
(3) 问题:多个sql 语句,完成一个功能,出现执行一半sql语句,影响功能的实现。
(4) 总结:转账中的多个sql需要看成一个整体,要么全部成功,要么全部失败。
-
概念:
(1) 事物(transaction):是数据库的最小执行单元,通常由一个或是多个SQL语句
构成,所有的sql都执行成功(commit);有一个sql执行失败,则代表事务回 滚(rollback)
(2) 作用:保证业务的正确性。
(3) 大小:取决于实际业务的需求。
-
事务的边界
开始:client和DB建立连接后,上一个事务的结束。
结束:
- 显示结束:commit - 提交 / rollback-回滚
- 隐式结束:create / drop / alter
-
原理:
回滚段:
回滚段:数据库服务器DBServer会为每一个连接上的client,开辟一小块内存空间(称为回滚段), 用于暂时存储sql语句的执行结果,当事务commit时,会把自己回滚的数据真正的写 入DB中;当事务rollback时,则会清空自己回滚段里的数据。
原理:
锁:
(1) 在一个事务中insert/update/delete数据时,会获取该数据的锁标记,直到该事务结(commit/rollback) 才会释放锁标记;
(2) 在一个事务持有数据的锁标记时,多事务并发操作,其他事务不能对该数据进行 insert/update/delete 操作,直到获取到锁标记为止,但是可以进行select查询操作,查询操作默认不参与事务。
案例:在不同的客户端更新同一个表中的同一条数据,先执行的成功,但是还没有进行commit之前, 执行另一个客户端中的跟新语句,则出现以下现象:
-
事务的4大特性(ACID)【面试题目】
(1) Atomic(原子性):事务中的多个SQL为一个整体,都成功提交(commit),有一个失败 则回滚(rollback) -->A
(2) Consistency(一致性):事务结束后 和 数据库中的数据状态必须一致。—>C
(3) Isolation(隔离性):多用户并发操作时,用户和用户的数据是相互不影响。—>I
(4) Durability(持久性):事务对数据库的影响是永久的,不能是临时的。
百知_zmj: 是的,工作需要
猫头虎: 什么情况,娟姐开始讲C语言了吗,进入大学工作了嘛
周良致: 刚上线就发现新发布嘎嘎嘎嘎嘎嘎嘎
CSDN-Ada助手: 不知道 Java 技能树是否可以帮到你:https://edu.csdn.net/skill/java?utm_source=AI_act_java
夜们要战痘: 太感谢了,这居然是权限问题,折腾死我了