Oracle数据笔记-【3】同义词序列视图索引

来源：互联网发布：知乎施工排水编辑：程序博客网时间：2024/05/17 06:34

--------------------数据库--------------------

--创建表空间create tablespace studatafile 'd:\stu.dbf'size 5mautoextend on--创建用户名create user stuidentified by stu grant connect to stugrant resource to stu create table stuInfo          --学生信息(  stuID number primary key,  stuName varchar2(10),  stuSex char(2));insert into stuInfo values(1,'Eric','男');insert into stuInfo values(2,'Mike','男');insert into stuInfo values(3,'Lily','女');insert into stuInfo values(4,'Lucy','女');create table subInfo         --课程信息(  subID number primary key,  subName varchar2(20));insert into subInfo values(1,'C语言');insert into subInfo values(2,'SQL基础');insert into subInfo values(3,'JAVA');create table markInfo        --成绩信息(  stuID int references stuInfo(stuID),  subID int references subInfo(subID),  score number(3),  primary key (stuID,subID));insert into markInfo values(1,1,98);insert into markInfo values(1,2,90);insert into markInfo values(1,3,80);insert into markInfo values(2,1,50);insert into markInfo values(2,2,40);insert into markInfo values(2,3,60);insert into markInfo values(3,1,70);insert into markInfo values(3,2,95);select * from stuinfoselect * from subInfoselect * from markInfo

--------------------同义词--------------------

--1、什么同义词 --别名--2、作用--简化对象的名字select * from myemp--3、创建语法create public synonym myemp for scott.emp--4、删除语法drop public synonym myempselect * from stuinfoselect stuid as 编号,stuname 姓名 from stuinfo

--------------------序列--------------------

--1、什么是序列？--标识列，自动增长--sql server:identity(1,1)--oracle:sequence--2、作用，等同于identity，为编号字段，提供自增长--3、语法select * from stuinfocreate sequence myseqincrement by 1 --步长start with 1001 --初始值maxvalue 100-- 无上限minvalue 1cyclecreate table stuInfo1          --学生信息(  stuID number primary key,  stuName varchar2(10),  stuSex char(2));insert into stuinfo1 values(myseq.nextval,'葬爱K','男')insert into stuinfo1 values(myseq.nextval,'葬爱Q','女')insert into stuinfo1 values(myseq.nextval,'葬爱A','男')insert into stuinfo1 values(myseq.nextval,'葬爱B','女')insert into stuinfo values(myseq.nextval,'葬爱Q','女')select * from stuinfo1--4.删除序列drop sequence myseq--5、查询序列值select myseq.currval from dual

--------------------视图--------------------

--1、什么是视图？--虚拟表--2、作用--1）简化，复杂的表查询--2）保密，视图名字不能推断表名--3) 缓冲，合作开发--创建视图之前要先授权grant dba to sturevoke dba from stu--创建视图create view empinfoasselect * from stuinfo666--查询视图select * from empinfo--drop view empinfo

--------------------索引--------------------

--1、什么是索引？--提高查询效率select * from stuinfo where stuname = 'eric'stuid --聚集索引（主键）stuname --非聚集（where条件中的字段）stusex --非聚集--2、索引分类--普通索引create index Instuid on stuinfo(stusex)--组合索引create index innamesex on stuinfo(stuname,stusex)--唯一索引create unique index inid on stuinfo(stuname)

0 0