关于“oracle基础知识总结”系列博客的说明:
1 本系列博客是在本人学习过程中总结的笔记,学习资料为尚学堂马士兵老师的oracle视频,大部分示例来自于该资料;
2 做为数据库初学者,本系列博客主要讲解了oracle的基础知识,高手勿入勿喷;
3 由于是学习笔记,主要作用是便于本人以后复习和备忘。可能条理不是很清晰,上下文衔接的不是很好,请读本文的读者做好心理准备;
4 本系列博客中的语句主要使用了oracle中scott用户的表结构,该用户是数据库中自带的用户。
DML语句, 数据操作语句
insertupdate
delete
inser语句的三种形式:
insert into dept values (50, 'game', 'bj');
insert into dept (deptno, dname) values (60, 'develep');
insert into dept select * from dept2;
创建一张内容相同的表, 用于备份
create table dept2 as select * from dept;
update语句的使用方法
update emp2 set sal = sal*2, ename = ename || '-' where deptno = 10;
delete语句的使用方法:
delete from emp2;
delete from emp2 where ename = 'ClARK';
DDL语句, 数据定义语言 (建表, 建视图等)
create table 表名 (列名 数据类型, 列名 数据类型, 。。。); -- 建表 ctrate table stu(id number(6), name varchar2(20));
drop table 表名 ; -- 删除表
drop table stu;
alter table 表名 ; -- 修改表结构
在现有的表上添加一个字段
alter table stu add (addr varchar2(100));
删除表中的一个字段
alter table stu drop (addr);
修改字段
alter table stu modify (addr varchar2(150));
事务控制 transaction
事务是指 一系列操作要么同时完成, 要么什么都不做。
事务起始于第一条DML语句(insert , delete, update)。
rollback 回滚整个事务 , commit 提交整个事务。 这两个语句结束事务。一条DDL, 或DCL语句也会导致事务的提交。
正常断开连接(exit)时事务正常提交;非正常断开连接(断点,直接关闭命令行窗口)事务回滚。
oracle数据库中的常用对象
一 表 (table)
建表语句的格式:create table 表名 (列名 数据类型, 列名 数据类型, 。。。);
常见数据类型:
varchar 变长字符串, 灵活建表语句中使用数据类型的举例
char 定长字符串, 效率高, 但可能浪费空间
number 数字
date 日期时间
long 变长字符串, 可大于4096
varchar2 变长字符串, 不能大于4096
blob
clob
create table stu
(
id, number(6),
name varchar2(20),
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar(50)
);
二 约束 (constraint)
一共有5种约束条件:1 非空约束 not null
2 唯一约束 unique
3 主键约束 primary key
4 外键约束 references
5 检查约束 check
约束举例:
create table stu
(
id, number(6),
name varchar2(20) not null,
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar(50)
);
约束条件在数据库中也是一个对象, 可以给约束条件起名,如果不起名字, 系统就起一个默认的名字。
约束命名举例:
create table stu
(
id, number(6),
name varchar2(20) constraint stu_name_nn not null,
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar(50)
);
唯一约束举例:
create table stu
(
id, number(6),
name varchar2(20) constraint stu_name_nn not null,
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar(50) unique,
);
将约束直接写在字段的后面, 叫做字段级的约束,字段级的约束有一些限制,要约束多个字段,要使用表级的约束。
比如要求名字和email的组合不能重复(即组合在表中唯一)
表级约束举例:
create table stu
(
id, number(6),
name varchar2(20) constraint stu_name_nn not null,
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar(50),
constraint stu_name_email_uni unique(email, name)
);
主键约束, 主键标识表中的记录的唯一性, 数据库会自动为主键建立一个索引,增加查询速度
在语法上说, 主键要求字段不能为空, 并且是唯一的, 可以认为主键约束是非空约束和唯一约束两个的组合
但是在逻辑意义上还代表记录的唯一性
主键约束举例:
create table stu
(
id, number(6) primary key,
name varchar2(20) constraint stu_name_nn not null,
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar(50),
constraint stu_name_email_uni unique(email, name)
);
主键约束可以加在字段上,也可以加在表上:
create table stu
(
id, number(6),
name varchar2(20) constraint stu_name_nn not null,
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar(50),
constraint stu_id_pk primary key(id),
constraint stu_name_email_uni unique(email, name)
);
主键约束还可以加在多个字段上,称之为复合主键。下面的例子, 把id和name设为复合主键
create table stu
(
id, number(6),
name varchar2(20) constraint stu_name_nn not null,
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar(50),
constraint stu_id_pk primary key(id, name),
constraint stu_name_email_uni unique(email, name)
);
外键约束
外键约束比较复杂,可能会涉及到两张表
外键约束可以建立在一张表的两个字段上, 也可以建在两张表的两个字段上, 但必须涉及到两个字段。
例如在向雇员表emp中插入一条记录的时候, 雇员的部门编号必须出现在部门表dept中,所以可以建立这样的约束,向emp插数据时,deptno字段必须参考dept表中的deptno字段。
create table class
(
id number(4) primary key,
name varchar2(20) not null,
);
create table stu
(
id, number(6),
name varchar2(20) constraint stu_name_nn not null,
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4) references class(id),
email varchar(50),
constraint stu_id_pk primary key(id),
constraint stu_name_email_uni unique(email, name)
);
class number(4) reference class(id) 表示stu表中的class这个字段参考class表中的id字段,也就是说stu表中class字段的值必须在class表中的id字段中。
也可将class字段的外键约束加在表级:
create table stu
(
id, number(6),
name varchar2(20) constraint stu_name_nn not null,
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar(50),
constraint stu_class_fk foreign key (class) references class(id),
constraint stu_id_pk primary key(id),
constraint stu_name_email_uni unique(email, name)
);
在这个例子中,stu表中的class字段叫做参考字段, class表中的id叫做被参考字段。被参考字段必须为主键。
如果一个字段正在被参考,则不能删除它所在的记录,下面的delete语句会执行失败。
delete from class where id = 1000;
delete from class;
要想删除表或删除表中的记录, 必须先删除class表中id字段的外键约束条件。下面会介绍删除约束条件的方法。
alter删除增加修改约束条件
删除约束条件
alter table stu drop constraint stu_class_fk;
删除了外键约束, 比如删除了这个外键约束条件, 才能删除表或表中的字段,因为这个字段正在被其他字段引用。
增加约束条件
alter table stu add constraint stu_class_fk foreign key (class) references class(id);
三 索引 (index)
索引也是数据库中的一个数据对象为某个字段建立一个索引,增加查询的效率
如为enmai字段创建索引
create index idx_stu_email on stu (email);
为两个字段的组合建立索引, 也就是查询这两个字段的组合的时候效率会更高
create index idx_stu_enami_name on stu (enail, name);
删除索引
drop index idx_stu_email;
查询索引:
select index_name from user_indexs;
给某个字段加约束条件时,oracle会为这个字段加一个索引。
不要轻易的建立索引,只有必要的时候才建立索引,因为索引在读取时会提高效率,而在修改或写入时会降低效率。并且索引还会占用大量的空间。
四 视图 (view)
视图是一张虚表,严格来说,视图是一个子查询,只是为这个子查询起个名字视图会简化我们的查询,并且可以保护我们的数据。但是表结构改了之后, 视图也要跟着修改,增加维护的难度。
创建视图:
create view v$_stu as select id, name, age from stu;
视图可以更新数据, 只是很少使用, 一般用来查询。
五 序列(sequence)
产生连续的数据, 一般用来做主键序列是oracle中独有的。
为论坛上的帖子创建一张表
create table articale
(
id number,
title varchar2(l024),
cont long,
);
创建一个序列:
create sequence seq;
以下语句保证产生的数据不会重复
select seq.nextval from dual;
插入数据时, 自动生成id;
insert into articale values (seq.nextval, 'a', 'aaaaaaaaaaaaaaa');
删除序列:
drop sequence seq;
数据库中常用对象总结
数据库中的常用对象:1 表 table
2 约束 constraint
3 索引 index
4 视图 view
5 序列 sequence
oracle数据字典表
当前用户创建了哪些表,使用了哪些约束,有哪些视图等等信息,被oracle搜集到一张表中,称之为数据字典。这些表中存储了数据库中几种常用对象的信息。
1 user_tables 当前用户有哪些表
select table_name from user_tables;
2 user_views 当前用户创建了哪些视图
select view_name from user_views;
3 user_xonstraints 当前用户使用了哪些约束
select constraint_name from user_xonstraints;
4 dictionary表, 记录了所有用户字典表的信息,可以看成用户字典的字典
下面语句查询出所有的数据字典, 大概600多个
select table_name from dictionary;