你的位置:首页 > 数据库

[数据库]oracle(sql)基础篇系列(三)数据维护语句、数据定义语句、伪列


 

DML语句

insert

向表中插入新的记录

 

--三种插入方式

--(1)不写字段的名字,直接按照字段的顺序把值逐个往里插

insert into dept2 values(50,'DANAME','BEIJING');

 

--(2)指定某些字段往里插,其他不插的字段默认都是空值

insert into dept2(deptno,dname) values(60,'DNAME2');

 

--(3)将子查询的结果插入到表中

insert into dept2 select * from dept;

 

update

修个记录。

--对部门号为10的员工的工资加2 倍

update emp2 set sal = sal*2 , ename='2'||ename where deptno =10;

 

--将编号为7369的员工的经理改为编号为7698 的经理

update emp2 set mgr = 7698 where empno =  7369;

 

delete

删除员工名为2MILLER的员工

delete from emp2 where ename = ‘2MILLER’;

 

 

DDL语句

create

创建新的表,创建表时可以指定字段的约束条件。约束条件通常有:默认约束,非空约束,唯一约束,主键约束,外键约束,check 。约束有可以分为列级约束、表级约束。

无约束创建表

create table stu

(

id number(10),

name varchar2(20),

sdate date,

class number(10),

grade number(2) ,

email varchar2(50)

);

非空约束&check约束&默认约束&唯一约束

可以对某些字段进行非空约束和唯一约束。

--name非空,emali唯一

create table stu

(

id number(10) check(id>1000),

name varchar2(20) not null,

sdate date,

class number(10),

grade number(2) default 1,

email varchar2(50) unique

);

 

--使用constraint给约束条件取名字,如果不给约束条件起名字,系统会默认的给约束条件起一个名字

create table stu

(

id number(10),

name varchar2(20) constraint stu_name_nn not null,

sdate date,

class number(10),

grade number(2) default 1,

email varchar2(50) constraint stu_email_un unique

);

 

主键约束&外键约束

 

主键的一些特性:

(1)可以唯一标识整条记录

(2)非空且唯一

(3)用类型的数值的字段做主键比字符类型的字段更合适

(4)可以用多个字段作为联合主键

 

外键的一些特性:

(1)建立于一张表的两个字段,或者两张表的两个字段,一个字段去参考另一个字段的值

(2)如果被参考字段没有这个值,不能把参考字段的值设置成为其他的值(如,被参考字段为空,而将参考字段设置为一个非空值是不能的)

(3)被参考的字段必须是主键

(4)被参考的字段不能作为删除条件

 

--name非空,emali唯一

create table stu

(

id number(10) primary key,

name varchar2(20) constraint stu_name_nn not null,

sdate date,

class number(10),

grade number(2) default 1,

email varchar2(50) constraint stu_email_un unique

);

 

--建立class表

create table class

(

id number(10) primary key,

name varchar2(20)

);

 

--stu表的class字段参考class表的id字段,即stu表的class字段是class表的外键

create table stu

(

id number(10) primary key,

name varchar2(20) constraint stu_name_nn not null,

sdate date,

class number(10) references class(id),

grade number(2) default 1,

email varchar2(50) constraint stu_email_un unique

);

列级约束&表级约束

上面sql的约束条件都是写在字段的后面,称为列级约束;还有另一种写法,就是在所有字段的最后用constraint声明,称为表级约束。需要注意的是,非空约束与默认约束只能用于列级约束。

create table stu

(

id number(10) ,

name varchar2(20),

sdate date,

class number(10),

grade number(2) default 1,

email varchar2(50),

check(id>1000),

constraint stu_id_pk primary key(id),

constraint stu_class_fk foreign key(class) references class(id),

constraint stu_email_un unique(name,email)

);

 

alter

修改现有表的结构。

增加列

alter table stu add(address varchar2(100));

 

删除列

alter table stu drop(address);

 

修改列

alter table stu modify(address varchar2(200));

 

删除约束条件

alter table stu drop constraint stu_class_fk;

 

添加约束条件

alter table stu add constraint stu_class_fk foreign key(class) references class(id);

修改约束条件

通常修改约束条件的方法时把原来的删掉,然后再添加个新的。

drop

删除表。

drop table stu;

 

伪列

 

Oracle表中的数据的默认显示顺序是先插入的先显示,Oracle表中rownum和rowid都是伪列,这两个伪列都可以对记录进行编号排序,很有用处。

rownum

rownum是根据sql查询出的结果给每行分配一个逻辑编号,对查询结果按照1,2,3...进行排列的,但这个字段不会显示出来。每条记录的rownum根据sql查询结果的不同而不同。

--新插入的数据

insert into emp2(empno,ename,deptno) values(8888,'test',80);

 

select * from emp2;

 

wps687B.tmp 

 

--选出最早插入的3条记录

select * from emp2 where rownum <=3;

 

wps687C.tmp 

 

可见,rownum 是对查询结果按照先插入的先显示的顺序进行编号。但是,rownum只能和< 或者 <= 一起用,不能与 > 和 = 一起使用。

 

(1)求10行以后的后4行的员工编号和姓名

--错误写法,查询结果为空

select empno, ename from emp where rownum > 10;

 

--正确写法,先对rownum 进行别名显示,别名显示后就可以采用>,>=

select empno, ename from  (

   select rownum r, empno,ename from emp

) where r > 10;

 

(2)求薪水最高的前5个人

--错误写法,先取出rownum <= 5的记录,再对记录排序

select ename,sal from emp where rownum <= 5 order by sal desc;

 

--正确写法,先排好序,再取出rownum <= 5的记录

select ename,sal from (

select ename,sal from emp order by sal desc ) where rownum <=5;

 

(3)求薪水最高的第6个人到第10个人

select ename,sal from (

select rownum r ,ename,sal from (

select ename,sal from emp order by sal desc )) where r >=6 and r <=10;

 

分析:先排序,再对rownum 进行别名化,别名显示后就可以采用>,>=。

rowid

rowid是物理结构上的记录,在每条记录插入到数据库中时,都会有一个唯一的物理记录 (不会变)。

 

select t.*,t.rowid from emp t;

 

wps688C.tmp 

rowid的一个应用

对emp表根据薪水降序分页查询。

--rowid分页,第一步获取数据物理地址rowid

select rowid rid,sal from emp order by sal desc;

 

--rowid分页,第二步根据rownum取得最大页数

select rownum rn,rid from(select rowid rid,sal from emp order by sal desc) where rownum<10;

 

--rowid分页,第三步根据rownum别名化rn取得最小页数

select rid from(select rownum rn,rid from(select rowid rid,sal from emp order by sal desc) where rownum<10) where rn>5;

 

--rowid分页,第四步根据物理地址,查询出具体数据

select * from emp where rowid in(select rid from(select rownum rn,rid from(select rowid rid,sal from emp order by sal desc) where rownum<10) where rn>5);

分析:

分页的关键是根据逻辑rownum取得最小最大页数,然后再根据物理rowid查询出物理记录。第一步先获取数据物理地址,第二步取得最大页数,第三步取得最小页数,第四步因为取得的页数都是物理地址,再根据物理地址,查询出具体数据。

 




几月份去荷兰旅游最好什么时候去荷兰旅游最便宜去荷兰旅游跟团要多少钱去荷兰旅游需要多少钱荷兰旅游大概多少钱从静安公园到青海路 上海闹市区的赏花之旅 佛山三水宝苞农场小孩免费吗?三水宝苞农场儿童票价格? 佛山宝苞农场几点开门?三水宝苞农场开门时间? 宝苞农场房间一晚多少钱?佛山三水宝苞农场住宿价格? 冬天里的风情 广西泡温泉的好去处Top5 梅州客天下景区门票价格?梅州客天下门票多少钱? 广州鳄鱼公园地址?长隆鳄鱼公园位置? 看电影中的四川 寻身边美景 北京有哪些便宜又好玩的旅游景点? 丽江度蜜月旅游攻略-最浪漫的八件事 2013西藏旅游攻略 十一月哪里旅游最好玩 阳江欢乐黄金海岸在哪里?阳江海陵岛欢乐黄金海岸怎么样?_阳江旅游攻略_阳江旅游景点 海陵岛黄金海岸自助游攻略?阳江海陵岛黄金海岸门票包含哪些项目?_阳江旅游攻略_阳江旅游景点 海陵岛敏捷黄金海岸广州开车怎么去?广州到阳江海陵岛敏捷黄金海岸路线?_阳江旅游攻略_阳江旅游景点 番禺南沙湿地公园钓鱼攻略?南沙湿地公园玩什么?_,旅游攻略_,旅游景点 MIE-144G1 Datasheet MIE-144G1 Datasheet MIE-144H4 Datasheet MIE-144H4 Datasheet MIE-184A4 Datasheet MIE-184A4 Datasheet 台湾旅游注意事项 台湾旅游注意事项 台湾旅游注意事项 欧洲国家有哪些 欧洲国家有哪些 欧洲国家有哪些 北京风景图片 北京风景图片 北京风景图片