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语句

oracle常用变量类型

binary_integer,主要用来计数而不是用来表示数据类型

number,数字类型

char,定长字符串

varchar2,变长字符串

date,日期类型

long,长字符串,最长2GB

boolean,布尔型,可以取值true,false,null

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;

 

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

 

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

select * from emp2 where rownum <=3;

 

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

 

可见,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;

 

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

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查询出物理记录。第一步先获取数据物理地址,第二步取得最大页数,第三步取得最小页数,第四步因为取得的页数都是物理地址,再根据物理地址,查询出具体数据。

 

上一篇:配置 -- php运行报Call to undefined function curl_init()的解决办法


下一篇:PLSQL_数据泵Datapump导入导出数据IMPDP / EXPDP(概念)(Oracle数据导入导出工具)(转)