Oracle学习笔记(序列和同义词)
我就直接po代码了,我的代码就是笔记,笔记就是代码
/*
Oracle学习笔记(序列和同义词)
*/
--取别名
SELECT 666, '江西省赣州市于都县' as "家乡", 888.88, SYSDATE from dual;
SELECT 666, '赣州市于都县' as 家乡, 888.88, SYSDATE from dual;
SELECT 666, '于都县' 家乡, 888.88, SYSDATE from dual;
SELECT 666, '于都县' hometown, 888.88, SYSDATE from dual;
SELECT 666, '于都县' "homeTown", 888.88, SYSDATE from dual;
/*
序列
*/
--创建序列,最简单的创建
--创建语法create sequence 序列名; 这是最常用的创建方式
create sequence mySeq;
--删除序列
--drop sequence mySeq;
--nextval表示获取序列的下一个值,currval表示获取序列的当前值
select mySeq.nextval, mySeq.currval from dual;
select mySeq.currval, mySeq.nextval from dual;
--创建一张测试表,测试一下序列
create table testSeq(
number1 NUMBER,
number2 NUMBER
);
--插入数据(使用序列,nextval和currval)
insert into testSeq(number1, number2) values(mySeq.nextval, mySeq.currval);
insert into testSeq(number1, number2) values(mySeq.nextval, mySeq.currval);
insert into testSeq(number1, number2) values(mySeq.nextval, mySeq.currval);
insert into testSeq(number1, number2) values(mySeq.nextval, mySeq.currval);
insert into testSeq(number1, number2) values(mySeq.currval, mySeq.nextval);
--查询数据(序列默认的增长幅度是1,从查询testSeq表结果中就能看出来)
SELECT * from testSeq;
--创建序列(创建序列时带参数)
create sequence mySeq2 increment by 2; --每次增长幅度是2
insert into testSeq(number1, number2) values(mySeq2.nextval, mySeq2.currval);
insert into testSeq(number1, number2) values(mySeq2.nextval, mySeq2.currval);
insert into testSeq(number1, number2) values(mySeq2.nextval, mySeq2.currval);
--查询数据
SELECT * from testSeq;
--创建序列
create sequence mySeq3;
--执行下面的语句会报错,因为(使用/调用)currval前,要先(使用/调用)nextval
SELECT mySeq3.currval from dual;
--序列对象.nextval
SELECT mySeq3.nextval from dual;
--序列对象.currval
SELECT mySeq3.currval from dual;
--创建序列(创建序列时带参数)
create sequence mySeq4 increment by 2 START WITH 66; --从66开始,每次增长幅度是2
insert into testSeq(number1, number2) values(mySeq4.nextval, mySeq4.currval);
insert into testSeq(number1, number2) values(mySeq4.nextval, mySeq4.currval);
insert into testSeq(number1, number2) values(mySeq4.nextval, mySeq4.currval);
--查询数据
SELECT * from testSeq;
--创建序列
create sequence mySeq5
START WITH 1
increment by 2
maxvalue 10
cycle
CACHE 2;
--
delete from testSeq;
--
insert into testSeq(number1, number2) values(mySeq5.nextval, mySeq5.currval);
insert into testSeq(number1, number2) values(mySeq5.nextval, mySeq5.currval);
insert into testSeq(number1, number2) values(mySeq5.nextval, mySeq5.currval);
insert into testSeq(number1, number2) values(mySeq5.nextval, mySeq5.currval);
insert into testSeq(number1, number2) values(mySeq5.nextval, mySeq5.currval);
insert into testSeq(number1, number2) values(mySeq5.nextval, mySeq5.currval);
insert into testSeq(number1, number2) values(mySeq5.nextval, mySeq5.currval);
insert into testSeq(number1, number2) values(mySeq5.nextval, mySeq5.currval);
--
SELECT * FROM testSeq;
/*
同义词
*/
--dual是一张伪表/虚拟表
SELECT SYSDATE FROM dual;
--使用sys用户(超级管理员)连接数据库
--
select * from tab;
--DUAL表是在sys用户下的表
select * from tab where tname = 'DUAL';
/*
如果我们现在是使用scott用户连接到数据库,
按理来说应该写成SELECT SYSDATE FROM SYS.dual;才可以访问dual表才对,但
是现在直接写成SELECT SYSDATE FROM dual;这样也可以访问dual表,其实这
就是同义词的作用
同义词其实就是取别名,以后可以直接使用别名了
同义词的作用:可以让其他用户通过一个名称方便的访问"用户名.表名"
注意:同义词可以让其他用户通过一个名称方便的访问"用户名.表名"的这个作用,我
在oracle11g中测试发现,没效果;大家可以自己测试一下,看看是否有效果?
*/
--正常来讲,访问不同用户的表需要使用"用户名.表名"来访问
SELECT SYSDATE FROM SYS.dual;
--现在使用scott用户连接数据库,直接写dual,没有写成SYS.dual
SELECT SYSDATE FROM dual; --执行正确,没有报错
/*
创建同义词语法
CREATE SYNONYM 同义词名 FOR 用户名.表名;
*/
--创建同义词
CREATE SYNONYM myEmp FOR SCOTT.emp;
create table address2(
id NUMBER primary key not null,
name VARCHAR2(30) not null
);
insert into address2 values(1, '江西省赣州市于都县渡江大道XX号');
--
select * from address2;
--创建同义词
CREATE SYNONYM addr FOR SCOTT.address2;
--使用同义词(同义词其实就是别名),使用别名
select * from addr;
--删除同义词
DROP SYNONYM addr;