oracle分页和创建表和约束

oracle分页

回顾mysql分页,用limit关键字

查询users表中前二条记录

select * from users limit 0,2
或
select * from users limit 2;

0表示第一条记录的索引号，索引号从0开始
2表示最多选取二个记录

查询出users第2条到第4条记录

select * from users limit 1,3; 

回顾hibernate分页API

Query.setFirstResult(0);
Query.setMaxResult(3);  

什么是rownum，有何特点?
1）rownum是oracle专用的关健字
2）rownum与表在一起，表亡它亡,表在它在
3）rownum在默认情况下，从表中是查不出来的
4）只有在select子句中，明确写出rownum才能显示出来
5）rownum是number类型，且唯一连续
6）rownum最小值是1，最大值与你的记录条数相同
7）rownum也能参与关系运算
   * rownum = 1    有值
   * rownum < 5    有值    
   * rownum <=5    有值         
   * rownum > 2    无值        
   * rownum >=2    无值
   * rownum <>2    有值    与  rownum < 2 相同
   * rownum = 2    无值
8）基于rownum的特性，我们通常rownum只用于<或<=关系运算   

显示emp表中3-8条记录（方式一：使用集合减运算）
select rownum "伪列",emp.* from emp where rownum<=8
minus
select rownum,emp.* from emp where rownum<=2;

显示emp表中3-8条记录（方式二：使用子查询，在from子句中使用，重点）
select xx.*
from (select rownum ids,emp.* from emp where rownum<=8) xx
where ids>=2;
注意：在子查询中的别名，不可加""引号

显示emp表中5-9条记录
select yy.*
from (select rownum ids,emp.* from emp where rownum<=9) yy
where ids>=5;
注意:在项目中，from后台可能有真实表名，也可能用子查询看作的表名，
     同时真实表和子查询看作的表要做连接查询

创建表和约束

回顾MySQL创建表语句users(id整型/name字符串/birthday日期型，默认今天)
drop table if exists users;
create table if not exists users(
   id int(5) auto_increment primary key,
   name varchar(4) not null,
   birthday date default '2015-4-27'
);

使用oracleSQL，创建用户表users(id整型/name字符串/birthday日期/sal整型，默认今天)
create table users(
  id number(5) primary key,
  name varchar2(8) not null unique,
  sal number(6,2) not null,
  birthday date default sysdate
);

进入回收站
drop table users;

查询回收站中的对象
show recyclebin;

闪回，即将回收站还原
flashback table 表名 to before drop;
flashback table 表名 to before drop rename to  新表名;

彻底删除users表
drop table users purge;

清空回收站
purge recyclebin;
    
测试如下类型
（1）number(5)：

insert into users(id,name,sal) values(1,'A',6666.66);     
insert into users(id,name,sal) values(11,'AA',6666.66);     
insert into users(id,name,sal) values(111,'AAA',6666.66);     
insert into users(id,name,sal) values(1111,'AAAA',6666.66);     
insert into users(id,name,sal) values(99999,'AAAAA',6666.66);     
insert into users(id,name,sal) values(100000,'AAAAAA',6666.66); 错

5表示最多存99999    

（2）number(6,2)：
col sal for 9999.99    

insert into users(id,name,sal) values(1,'A',6.66);     
insert into users(id,name,sal) values(11,'AA',66.666);     
insert into users(id,name,sal) values(111,'AAA',666.6666);     
insert into users(id,name,sal) values(1111,'AAAA',6666.66666);     
insert into users(id,name,sal) values(11111,'AAAAA',66666.666666);错 

number(6,2)
其中2表示最多显示2位小数，采用四舍五入，不足位数补0，同时要设置col ... for ...
其中6表示小数+整数不多于6位
其中整数位数不得多于4位，可以等于4位

（3）varchar2(8)：

insert into users(id,name,sal) values(1,'A',7777.77);     
insert into users(id,name,sal) values(2,'AA',7777.77);     
insert into users(id,name,sal) values(3,'AAA',7777.77);     
insert into users(id,name,sal) values(4,'AAAA',7777.77);  
insert into users(id,name,sal) values(5,'AAAAA',7777.77);  
insert into users(id,name,sal) values(6,'AAAAAA',7777.77);  
insert into users(id,name,sal) values(7,'AAAAAAA',7777.77);  
insert into users(id,name,sal) values(8,'AAAAAAAA',7777.77);  
insert into users(id,name,sal) values(9,'AAAAAAAAA',7777.77);错
insert into users(id,name,sal) values(1,'哈',7777.77);     
insert into users(id,name,sal) values(2,'哈哈',7777.77);     
insert into users(id,name,sal) values(3,'哈哈哈',7777.77);     
insert into users(id,name,sal) values(4,'哈哈哈哈',7777.77);  
insert into users(id,name,sal) values(5,'哈哈哈哈哈',7777.77);错  

8表示字节  GBK 赵 2字节

（4）date：默认格式为：'27-4月-15'
（5）CLOB【Character Large OBject】：大文本对象，即超过65565字节的数据对象，最多存储4G
（6）BLOB【Binary Large OBject】：大二进制对象，即图片，音频，视频，最多存储4G

为emp表增加image列，alter table 表名 add 列名 类型(宽度)
alter table emp
add image blob;

修改ename列的长度为20个字节，alter table 表名 modify 列名 类型(宽度)
alter table emp
modify ename varchar2(20);

删除image列，alter table 表名 drop column 列名
alter table emp
drop column image;

重名列名ename为username，alter table 表名 rename column 原列名 to 新列名
alter table emp
rename column ename to username;

将emp表重命名emps，rename 原表名 to 新表名
rename emp to emps;

注意：修改表时，不会影响表中原有的数据

笔试题：有【1000亿】条会员记录，如何用最高效的方式将薪水字段清零，其它字段内容不变？

第一：从emp表中删除sal字段
      alter table emp
      drop column sal;      

第二：向emp表中添加sal字段，且内容默认0
      alter table emp
      add sal number(6) default 0;

修改表不可回滚    

创建表customers(单)和orders(多)，使用primary key/not null/unique/default/foreign key约束
要体现【on delete cascade/on delete set null】
需求：删除客户，级联删除他所有的订单
      delete from customers where id = 1;
需求：删除客户，不级联删除他所有的订单，只是将外健设置为NULL

      delete from customers where id = 1;   

#创建用户表
create table customers(
  id number(3) primary key,
  name varchar2(4) not null unique
);

#插入用户数据
insert into customers(id,name) values(1,'A');
insert into customers(id,name) values(2,'B');

#创建订单表
create table orders(
  id number(3) primary key,
  isbn varchar2(6) not null unique,
  price number(3) not null,
  cid number(3),
  --constraint cid_FK foreign key(cid) references customers(id) on delete cascade 
  constraint cid_FK foreign key(cid) references customers(id) on delete set null  
);

#插入订单数据
insert into orders(id,isbn,price,cid) values(1,'isbn10',10,1);
insert into orders(id,isbn,price,cid) values(2,'isbn20',20,1);
insert into orders(id,isbn,price,cid) values(3,'isbn30',30,2);
insert into orders(id,isbn,price,cid) values(4,'isbn40',40,2);

创建表students，包括id,name,gender,salary字段，使用check约束【性别只能是男或女，薪水介于6000到8000之间】

create table students(
  id number(3) primary key,
  name varchar2(4) not null unique,
  gender varchar2(2) not null check ( gender in ('男','女') ),
  salary number(6) not null check ( salary between 6000 and 8000 )
);
insert into students(id,name,gender,salary) values(1,'哈哈','中',6000);错
insert into students(id,name,gender,salary) values(2,'呵呵','男',5000);错
insert into students(id,name,gender,salary) values(3,'嘻嘻','女',7000);对