Oracle入门笔记【5】表的创建与管理

前期准备：将emp表复制成myemp表。

create table myemp as select*from emp;(此类语法只有Oracle才支持)

数据增加：

1，使用完整格式编写：

Inser into
myemp(empno,sal,job,comm,ename,mgr,hiredate,deptno)values(8888,9000.0,'清洁工',10.0,'张三',7369,to_date('1979-10-10','yyyy-mm-dd'),40);

2, 简化格式的编写:

insert into myemp values (9999,'李四','清洁工'，7369,to_date('1979-10-10','yyyy-mm-dd'),9000.0,10.0,40);

（必须与表中类型一致）

注意：工作中不要用简化格式，否则只会扣工资；

3， （1）明确的设置一个字段为空：

Insert into
myemp(empno,sal,job,comm,ename,mgr,hiredate,deptno)values(7777,9000.0,'清洁工',10.0,'张三',7369,null,40);

（2）不编写不要的内容 则内容为默认值：

Insert into
myemp(empno,sal,job,comm,ename,mgr,deptno)values(6666,9000.0,'清洁工',10.0,'张三',7369,40);

数据的修改：

1，将smith 的工资修改为8000，佣金修改为9000；

update
myemp set sal=8000,comm=9000 where ename='smith';

2, 将Allen的工资修改为scott的工资；

update myemp set sal =(
select sal from
myemp where ename ='scott')
where ename =
'allen';

3,将低于公司平均工资的雇员工资上涨20%。

update
myemp set sal =sal*1.2
where
sal<(select avg(sal) from myemp);

4,将所有的雇员的雇佣信息修改为今天。

update myemp set hiredate=sysdate;

(当数据正在更新数据的时候，其他用户不能访问数据库)

5，如果说现在有一个大型的社交网站 ， 在线用户已有3亿人，每个人如果想要使用这个网站，都会实现一次自动更新最后登录的日期操作，突然有一天你的老板那说，要更新用户数据表的某一个字段的全部内容，那么你怎么处理这个问题？

首先更新全部数据那么是100%不可取的，但可以拆分操作，如果是活跃用户，那么每次使用的时候都要进行登录，登录的时候除了验证用户名和密码之外，再额外的执行一条更新操作
，这样就是以时间换空间。

总结 ：以后只要是写更新操作，不可能不写where。

数据的删除：

1，删除雇员编号为7566的雇员信息。

delete from myemp where empno=7566;

2,删除雇员编号为 7788，7902 的雇员信息。

delete from myemp where empno in(7788,7902);

3,删除高于公司平均工资的雇员信息。

delete from myemp where sal>(select
avg(sal)from myemp);

4,删除全部的记录。

delete from myemp;

注意：删除操作是极其危险的操作，实际上对于一个稳定的系统，对于删除操作都是具备逻辑删除和物理删除两种方式：

-物理删除：直接执行delete from ，彻底从表中删除；
逻辑删除：增加一个逻辑字段，例如：有一个flag字段 若flag=1，已经删除，flag=0，没有删除，如果真的执行删除操作 那么就相当于修改了flag的内容，同时在每次查询的时候只需要增加一个where限定条件（select * from....where flag=1）。

事务处理：

事物是针对数据更新使用的，也就是说只有DML的更新操作才存在有事物的支持。
Session（会话）：每一个session都表示不同的用户每一个session上都有自己的事物处理机制。每一 个session事物上都可以使用两个命令：

-commit：事物提交，如果执行了多条更新操作，那么只有执行了commit才会真正的发出，没有执行之前，所有的更新操作都将保存在缓冲区。

-rollback：事物回滚操作，即：如果发现更新的操作有问题，则回复所有的更新操作，以保证原始数据不被破坏。

create table myempe as select*from emp;

delete from myempe where empno=7369;

rollback；

死锁：就是一个session等待另一个造成的死锁。

行号ROWNUM：

在进行数据查询的时候，默认的情况下只是显示了数据表的结构内容，但是也可以利用rownum针对显示的数据进行一个自动的行号编号。

select ename，job，ROWNUM from emp where deptno=10；

取出第一行记录：

select* from emp where rownum=1;(只能取出第一行)

取出前N行记录（重点）：最有用处的一种

select empno,ename,job,rownum from emp where
rownum<=5;

取出11-15行记录。

select* from (select
empno ,ename,job,sal,hiredate,rownum rn from
emp
where rownum<=15
)temp
where temp.rn>10;

(只有14行）

currentPage =3 lineSize=5;

currentPage*linesize=15;

( curentPage-1) *linesize = 10;

行ID：（ROWID）

观察

select rowid,deptno,dname,loc from dept;

通过rowid查找：

select *from dept where rowid='AAAR3qAAEAAAACHAAA';

1，常见的数据类型；（查表可见）

2，创建数据表：

范例：创建一张成员信息表；

create
table member(

mid number,

name varchar2(50)default'无名氏',

age number(3),

birthday date
default sysdate,

note clob

);

向其中增加数据：

insert into
member(mid,name,age,birthday,note)values(10,'张三',30,to_date('1985-

11-11','yyyy-mm-dd'),'是个人');
使用默认的数据：

insert
into member(mid,age,note)values(20,20,'是个人');

3,数据表的重命名：

三个级别的数据字典

1：用户级别（use_*开头），指的是一个用户可以使用的字典；

select * from user
tables;

2：管理员级别（dba_*开头），指的是由数据库管理员使用的字典；

3：全部级别（all_*开头），表示不管是用户还是管理员都可以使用；

范例：将member表更名为person：

rename member to person;

4,截断表：

范例 截断person 表：

truncate table person;

一旦表被截断后所占的数据空间都将被释放掉；

5，复制表：

根据子查询返回的结构创建数据表，并将子查询的结果存入表中；

例：创建一张只包含10部门雇员信息的数据表

create table emp10 as select * from emp where deptno=10;

范例：赋值emp表的结构但是不复制里面的数据

create table empnull as

select* from emp where 1=2;

6,表的删除（对象的删除）：

范例：

drop table emp10;

7,闪回技术(类似与windows的回收站)：

查看回收站：

回收数据表：flashback table emp10 to before drop;

强制删除数据表：

drop table emp10 purge;(没法回收了)

删除回收站里的数据表：

purge table myemp;

清空回收站：

purge recyclebin;

8,修改表的结构（了解，不建议使用）；

约束的创建与管理

1，非空约束（指的是数据表中的某一个列的内容不允许为空）：

drop table member purge;

create table member (

mid
number ,

name
varchar2(20) not null

);

增加正确的信息：

insert into member (mid,name )values(10,'张三');

增加错误的信息：

insert into member (mid,name
)values(20,null);

insert into member (mid,name
)values(30);

2，唯一约束（数据的某一列不允许重复）：

此时表示在email字段上不允许保存重复的内容：

drop table member purge;

create table member(

mid
number,

name
varchar2(20)not null,

email
varchar2(30)unique

);

增加正确数据：

insert into member (mid,name)values(10,'张三');

insert into member (mid,name)values(60,'张三');

insert into member
(mid,name,email)values(20,'李四','mldn@163.com');

默认情况下null不会收到唯一约束的限制。

增加错误的信息:

insert into member
(mid,name,email)values(60,'王五','mldn@163.com');

查看约束对象：

col owner for a10;

col constraint_name for a20;

col table_name for a20;

select owner,constraint_name,table_name from
user_constraints;

为约束设置名称：

drop table member purge;

create table member(

mid
number,

name
varchar2(20)not null,

email
varchar2(30),

constraint uk_email unique(email)

);

增加正确信息：

insert into member (mid,name)values(10,'张三');

insert into member (mid,name)values(60,'张三');

insert into member
(mid,name,email)values(20,'李四','mldn@163.com');

增加错误的信息：

insert into member (mid,name,email)values(60,'王五','mldn@163.com');
这样可以显示错误的信息在何处。

3，主键约束(不能重复也不能为空)：

设置主键约束：

drop table member purge;

create table member(

mid
number,

name
varchar2(20)not null,

constraint pk_mid primary key(mid)

);

增加正确的数据：

insert into member (mid ,name)values(10,'张三');

增加错误的数据：

insert into member (mid ,name)values(10,'李四');

insert into member (mid,name
)values(20,null);

主键约束就是两种约束的集合体。

复合主键：

drop table member purge;

create table member(

mid number,

name
varchar2(20),

constraint pk_mid_name primary
key(mid,name)

);

增加正确数据：

insert into member (mid ,name)values(10,'张三');

insert into member (mid ,name)values(10,'李四');

insert into member (mid ,name)values(20,'张三');

增加错误：

insert into member (mid ,name)values(20,'张三');

复合主键基本不用。

4,检查约束：

设置检查约束：

drop table member purge;

create table member(

mid
number,

name
varchar2(20),

age
number(3),

constraint ck_age check(age between 0 and

);

增加正确的信息：

insert into member (mid ,name)values(10,'张三');

insert into member (mid
,name,age)values(10,'张三',20);

增加错误的信息：

insert into member (mid
,name,age)values(20,'张三',998);

5，外键约束（重难点）：

它是作用在两张数据表上的约束。

实现一个人有多本的书：
drop table member purge;

drop table book purge;
create table member(

mid
number,

name
varchar2(20) not null,

constraint pk_mid primary key(mid)

);

create table book(

bid
number,

title
varchar2(20) ,

mid
number,
constraint pk_bid primary key(bid)

);

增加有意义的数据：

insert into member (mid ,name)values(10,'张三');

insert into member (mid ,name)values(20,'李四');

insert into book
(bid,title,mid)values(10001,'Java',10);

insert into book (bid,title,mid)values(10002,'J5',10);

insert into book
(bid,title,mid)values(10003,'J6',10);

insert into book
(bid,title,mid)values(10004,'J7',20);

insert into book
(bid,title,mid)values(10005,'j8',20);

增加错误的信息：

insert into book
(bid,title,mid)values(8888,'幽灵',90);

子表中的数据与父表不一致。所以所谓的外键关联就是控制子表某一列的内容与父表中的数据范围相匹配而，

使用foreign key 来表示。

增加外键：

create table book(

bid
number,

title
varchar2(20) ,

mid
number,

constraint pk_bid primary key(bid),

constraint fk_mid foreign key(mid)
references member(mid)

);

再重新输入数据发现错误可以了。

限制一：

如果表中存在有外键关系，那么在删除父表前一定要先删除子表。

有一种强制删除：

drop table member cascade constraint ;

限制二：

父表中作为子表关联的的外键字段，必须设置为主键约束或者唯一约束。

限制三：

默认情况下，如果父表中有对应的子表记录，那么父表记录无法被删除。

delete from member where mid=10;这样无法删除。

数据的级联删除：

create table book(

bid
number,

title
varchar2(20) ,

mid
number,

constraint pk_bid primary key(bid),

constraint fk_mid foreign key(mid)
references member(mid) on delete cascade

);

数据的级联更新：

create table book(

bid
number,

title
varchar2(20) ,

mid
number,

constraint pk_bid primary key(bid),

constraint fk_mid foreign key(mid)
references member(mid) on delete set null
);

6,约束的修改（了解）：

要想控制约束，必须有约束的名字。

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 203,456评论 5赞 477
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,370评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 150,337评论 0赞 337
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,583评论 1赞 273
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,596评论 5赞 365
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,572评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,936评论 3赞 395
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,595评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,850评论 1赞 297
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,601评论 2赞 321
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,685评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,371评论 4赞 318
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,951评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,934评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,167评论 1赞 259
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 43,636评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,411评论 2赞 342

推荐阅读更多精彩内容