第16章 序列、索引、同义词

--------------------------序列-------------------------
create sequence emp_empno_seq;
语法:
create sequence EMP_EMPNO_SEQ   ---指定序列名称
minvalue 1                      ---指定最小值
maxvalue 50----指定最大值
start with 10                      ---指定从几开始
increment by 1               ---指定每次增长多少
cache 20;                      ---指定缓存个数


----访问序列的属性,只有两个属性
select emp_empno_seq.nextval from dual;   ----下一个值
select emp_empno_seq.currval from dual;   ----当前值


3、使用序列，分别向班级表插入3条数据，每个班级5名学生，3门课程数据，以及每个学生的选课数据。
----------------------------序列的用途---------------------------------
需求:
给班级表插入三条数据(班级编号1-10),
学生表5条数据(学生编号100-200),
课程表3条数据(课程编号1000-2000),
学生选课信息表15条数据(成绩编号10000-20000)
---创建生成班级流水号的序列
create sequence class_seq
minvalue 1
maxvalue 10
start with 1
increment by 1;
---学生流水号序列
create sequence student_seq
minvalue 100
maxvalue 200
start with 100
increment by 1;
----课程流水号序列
create sequence subject_seq
minvalue 1000
maxvalue 2000
start with 1000
increment by 1;
----成绩流水号序列
create sequence score_seq
minvalue 10000
maxvalue 20000
start with 10000
increment by 1;


------插入数据,注意顺序,先给课程表和班级表插入数据


insert into cla (clano,claid,claname) values(class_seq.nextval,'1','java1班');
insert into cla (clano,claid,claname) values(class_seq.nextval,'2','java2班');
insert into cla (clano,claid,claname) values(class_seq.nextval,'3','java3班');


insert into subject(subno,subid,subname) values(subject_seq.nextval,'1000','java基础');
insert into subject(subno,subid,subname) values(subject_seq.nextval,'1001','java高级');
insert into subject(subno,subid,subname) values(subject_seq.nextval,'1002','oraclesql');


----现在向学生表和选课信息表插入数据


insert into stu
       (sno,sid,sname,sex,indate,classid)
       values
       (student_seq.nextval,'100','zhangsan','男',to_date('2018-10-19','yyyy-MM-dd'),'1');
insert into stu
       (sno,sid,sname,sex,indate,classid)
       values
       (student_seq.nextval,'101','lisi','男',to_date('2010-10-19','yyyy-MM-dd'),'2');
insert into stu
       (sno,sid,sname,sex,indate,classid)
       values
       (student_seq.nextval,'102','wangwu','男',to_date('1998-10-19','yyyy-MM-dd'),'3');
insert into stu
       (sno,sid,sname,sex,indate,classid)
       values
       (student_seq.nextval,'103','liuhong','女',to_date('2008-10-19','yyyy-MM-dd'),'1');
insert into stu
       (sno,sid,sname,sex,indate,classid)
       values
       (student_seq.nextval,'104','xiaohua','女',to_date('2013-10-19','yyyy-MM-dd'),'2');
commit;


-----朝选课信息表中插入数据
---注意:stu_id,sub_id这两个字段是外键,所以值取在学生表的学生编号和课程表的课程编号
insert into select_subject(ss_id,stu_id,sub_id,grade) values(score_seq.nextval,'100','1000',100);
insert into select_subject(ss_id,stu_id,sub_id,grade) values(score_seq.nextval,'100','1001',78);
insert into select_subject(ss_id,stu_id,sub_id,grade) values(score_seq.nextval,'100','1002',80);
insert into select_subject(ss_id,stu_id,sub_id,grade) values(score_seq.nextval,'101','1000',36);
insert into select_subject(ss_id,stu_id,sub_id,grade) values(score_seq.nextval,'101','1001',78);
insert into select_subject(ss_id,stu_id,sub_id,grade) values(score_seq.nextval,'102','1002',90);
insert into select_subject(ss_id,stu_id,sub_id,grade) values(score_seq.nextval,'102','1000',19);
insert into select_subject(ss_id,stu_id,sub_id,grade) values(score_seq.nextval,'103','1000',56);
insert into select_subject(ss_id,stu_id,sub_id,grade) values(score_seq.nextval,'103','1001',66);
insert into select_subject(ss_id,stu_id,sub_id,grade) values(score_seq.nextval,'104','1002',89);


----序列的作用,可以用来自动生成id主键


----4、查询全部学生的，学号，学生姓名，课程名称， 成绩（要全部学生信息）


select s.sid,s.sname,sb.subname,ss.grade 
from stu s,subject sb,select_subject ss
where s.sid=ss.stu_id and sb.subid=ss.sub_id;


------5、请写出查询学生“李四”的选课情况的SQL语句，要求显示学生编号，学生姓名，所选课程编号，所选课程名称。


select s.sid,s.sname,sb.subid,sb.subname,ss.grade 
from stu s,subject sb,select_subject ss
where s.sid=ss.stu_id and sb.subid=ss.sub_id and s.sname='lisi';


-------6、请写出查询每门课程都有多少学生选择的SQL语句，要求显示课程名称，所选人数。


----方案一
select sb.subid,sb.subname,ss.人数
from subject sb,
     (select sub_id,count(1) 人数 from select_subject group by sub_id) ss
where sb.subid=ss.sub_id;
----方案二,用相关子查询做
select sb.subid,sb.subname,(select count(1) from select_subject ss where ss.sub_id=sb.subid) 人数
from subject sb




----------7、请写出还有哪些学生没有进行选课。要求用子查询方式。


select s.sid,s.sname
from stu s

where not exists(select (1) from select_subject sb where sb.stu_id=s.sid);

---------------------------------索引-----------------------------------
1.为什么要有索引?
  能够让查询更快


2.如何创建索引?
create table test42
(
       id number(7) primary key,
       name varchar2(20) unique,
       phone varchar2(20)
)


---索引的创建有两种方式:1.对于主键列和唯一列,系统自动创建索引;2.手动创建索引
select * from test42 where phone='13826189266';
---为了让根据手机号码查询更快,我们可以为phone这个字段创建索引
create index idx_test42_phone----注意这个名字的命名一般规则:idx_tableName_columnName
on test42(phone);
create index idx_test42_phone_ename----可以给多个字段创建索引
on test42(name,phone);
drop index idx_test42_phone;


3.索引的好处?
create table testemp as select * from emp where 1=0;---创建测试表
insert into testemp select * from emp;-----插入数据
insert into testemp select * from testemp;-----表复制
----update testemp set empno=rownum;修改员工编号等于当前行号
select * from testemp where empno=7788;-----1.138秒
----现在为empno创建索引,
create index idx_testemp_empno
on testemp(empno);
select * from testemp where empno=7788;-----0.78秒
----创建索引后明显查询变快了


4.如何删除索引?我们所学的所有对象的删除语法都是:drop ObjectType ObjectName;
drop index idx_test42_phone;


5.使用索引注意事项?
问题:索引是不是越多越好?
no,索引的维护需要开销.,创建索引的字段oracle会给它们排队,而且要维护队伍的顺序,有删除/更新/添加,那么
队伍的顺序都要变化.这些都是Oralce自动维护的.
所以:经常更新的字段不建议创建索引,这样的字段创建索引,系统开销太大.

大表数据比较多,查询比较费力,适合创建索引,小表适合全表扫描,不建议创建索引.

-----------------------同义词---------------------------
1.为什么要有同义词?
Oracle的对象管理机制:谁创建谁拥有


---需求:改变其他用户访问scott.emp表的囧状
create synonym quanjia_emp
for scott.emp;


---同义词的好处,让访问其他用户的对象变得更方便,访问该对象的同义词就好了
select * from quanjia_emp;




2.oracle数据库对于用户的管理采用权限来进行限制.
如果需要创建同义词,那么需要有create synonym 权限,
如果需要创建public synonym,那么需要有create public synonym权限
create public synonym quanjia2_emp for scott.emp;
说明问题:
   >>以上代码是由sys用户创建的,说明他拥有创建公共同义词的权限
   >>代码执行成功说明,已经创建了一个公共同义词给scott的emp表,那么所有用户都可以访问这个同义词,
       其实就是访问scott的emp表;
       create public synonym quanjia2_emp for scott.emp;---sys用户创建同义词
       create user qianliu identified by qianliu;----sys用户创建用户qianliu,密码qianliu
       grant connect to qianliu;----授予连接数据库的权限给qianliu
       grant select on quanjia2_emp to qianliu;----授予访问同义词的权限给qianliu
       select * from quanjia2_emp;---qianliu这个用户登录数据库访问同义词,ok
       

3.同义词最大的好处?方便不用用户之间访问彼此的对象,但是前提一定要有访问的权限

练习1
1.创建一个序列，该序列起始值从1开始，无最
大值，增量是1，不循环。
create sequence test_seq1
start with 1
increment by 1
minvalue 1
nocycle
cache 10;


2.查询序列的当前值及下一个值
select test_seq1.currval from dual;
select test_seq1.nextval from dual;


3.使用第1题所建的序列，向部门表中插入两条
记录，部门编号使用序列值，部门名称分别为：
Education、Market，城市分别为：DALLAS、
WASHTON
insert into dept (deptno,dname,loc)values(test_seq1.nextval,'Education','DALLAS')；
insert into dept (deptno,dname,loc)values(test_seq1.nextval,'Market','WASHTON')




练习2
1.使用子查询的方式，创建test表。
create table test as select * from emp where 1=1;
drop table test


2.快速复制test表中的数据，复制到100w条左右
insert into test select * from test


3.更新test表中的empno字段为rownum
update test set empno=rownum
--上面不能修改精度number的问题
alter table test modify empno number(10)


4.查询test中empno为800000的记录值，记录查询执行时间。
select * from test where empno=800000


5.在test表的empno字段上创建索引
create index test on emp(empno)


6.重新执行第4题，对比查询时间
select * from test where empno=800000
commit




练习3
1.有如下关系模式，
student(sno,sname,gender,birthday,email);--学生
course(cno,cname,type,credit);--课程
sc（sno,cno,grade);--选课
试分析哪些列上适合创建索引？
student 在sno
course表 在cno
course 在sno






课后作业
1.创建序列，起始位1，自增为1，最小值为1，最大值为9999
create sequence test_seq2
start with 1
increment by 1
minvalue 1
maxvalue 9999
cycle
cache 10;


2.创建序列，起始值为50，每次增加5；
create sequence test_seq3
start with 50
increment by 5
minvalue 50
nocycle
cache 10


• 3.在表copy_dept中插入记录，其中部门号码采用上一步中创建的序列
生成；
insert into copy_dept(deptno,dname,loc)values(test_seq2.nextval,'dd','dd')
select * from copy_dept


• 4.请为工资创建索引，比较<10000,>1000,与round（sal）>10000,哪
个索引有效，哪个索引无效；
create index indexsal on emp(sal) 
select sal from emp where sal<10000 慢
select sal from emp where sal>1000 快
select sal from emp where round(sal)>10000 没有显示无效


--5.创建表，采用“create table copy_emp_index as select * from
--” “”一 emp ，生成500万条数据，把其中的 员工号 字段修改为唯 ；
create table copy_emp_index as select * from emp where 1=0  --复制表结构
update copy_emp_index set empno=rownum;      --修改字段是伪劣
alter table copy_emp_index add  CONSTRAINT copy_emp_index_tb UNIQUE (empno)--键值是唯一的
alter table copy_emp_index disable   CONSTRAINT copy_emp_index_tb    --禁用函数
alter table copy_emp_index ENABLE   CONSTRAINT copy_emp_index_tb     --启用函数


insert into copy_emp_index select * from copy_emp_index   --疯狂复制是N的2次方
insert into copy_emp_index select * from emp         --第一次复制表数据
alter table copy_emp_index modify empno number(10) --修改了number的长度


select * from copy_emp_index
drop table copy_emp_index


--6.查询表copy_emp_index表中员工号为200001的员工姓名，工资，记录执行时间；
select * from copy_emp_index where empno=200001--使用索引查询快
select * from copy_emp_index where empno=2 --没有创建约束
select * from copy_emp_index where rownum=200001 --直接用伪劣查慢


• 7.在copy_emp_index表的empno字段上创建索引，再次执行第6题语句，
记录执行时间并做对比；
--序列
create sequence test_seq
start with 1
increment by 1
maxvalue 1000000000000
minvalue 1
cycle
cache 100


--索引
create index indexempno on copy_emp_index(empno)




insert into testemp select * from testemp;-----表复制
又叫做疯狂复制