Oracle数据库的基本操作（三）

一、查询

1.查询CS系的所有学生的信息

• 第一种

  select * from Student where Sdept = 'CS';
  

• 第二种

  select * from Student where lower(Sdept) = 'cs';
  

• 第一种与第二种的区别在于“CS”的大小写，第一种是正常大写，第二种为小写，此时应将Sdept改为lower(Sdept)

2.查询姓名是张三或杨三的学生

select * from Student where regexp_like(Sname,'[张杨]三');

• 注：在SQL Server中的语句为

  select * from Student where Sname like '[张杨]三';
  

3.左外连接left outer join

3.1查询每个学生的信息及该生的选课信息，即使没有选课也要列出该生的信息

• SQL Server和Oracle中

  select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
  from Student left outer join SC
  on Student.Sno = Sc.Sno;
  

• Oracle中

  select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
  from Student,SC
  where Student.Sno = SC.Sno(+);
  

4.并集

• SQL Server 和 Oracle

  select * from Student where lower(Sdept) = 'cs'
  union 
  select * from Student where Ssex = '男';
  

5.交集

• SQL Server 和 Oracle

  select * from Student where lower(Sdept) = 'cs'
  intersect
  select * from Student where Ssex = '男';
  

6.差集

• SQL Server

  select * from Student where lower(Sdept) = 'cs'
  except
  select * from Student where Ssex = '女';
  

• Oracle

  select * from Student where lower(Sdept) = 'cs'
  minus
  select * from Student where Ssex = '女';
  

7.插入多条记录

• SQL Server

  insert into SC values
  ('2017003','1',100),
  ('2017002','2',80);
  

• Oracle_第一种

  insert into SC
  select '2017003','1',100 from dual union all
  select '2017003','2',80 from dual union all
  select '2017003','3',80 from dual;
  

• Oracle_第二种

  insert all
  into SC values('2017004','1',100)
  into SC values('2017004','2',60)
  select 1 from dual;
  

• Oracle_第三种（同时插入多个表）

  insert all
  into SC values('2017004','3',100)
  into SC values('2017004','4',60)
  into Student(Sno,Sname) values('2017111','007')
  into Student(Sno,Sname) values('2017112','110')
  select 1 from dual;
  

二、序列sequence

类似于SQL Server中的标识列；

• SQL Server

  create table Books
  (
   bkID int identity(1,1) primary key, --标识列
   bkName varchar(30)
  )
  

1.创建序列

（1）将在自己的方案中创建序列的权限赋予用户“yh”

（2）将在其他方案中创建序列的权限赋予用户“yh”

（3）语法
create sequence 序列名
[start with 整数]
[increment by 整数] --默认1
[maxvalue 整数 | nomaxvalue]
[minvalue 整数 | nominvalue]
[cycle | noclye]
[cache 整数 | nocache] --默认缓存20个序列值；

（4）序列有两个伪列：nextval和currval

• nextval:序列的下一个值；currval：序列当前的值；

• 写法：序列名.nextval/currval；

• 在第一次使用currval伪列之前，必须先引用nextval对序列初始化;
  即将currval的值设置为起始值；

• 创建Books表

  create table Books
  (
   bkID int primary key,
   bkName varchar(30)
  );
  

• 创建序列

  create sequence seqBkID
  start with 1
  increment by 1
  nomaxvalue
  nocycle
  cache 10;
  

• 向Books表插入数据并查看表

  insert into Books values(seqBkID.Nextval,'数据库');
  insert into Books values(seqBkID.Nextval,'操作系统');
  select * from Books;
  

1.1理解start with, increment by, maxvalue, minvalue这些参数的意义

• 创建序列

  create sequence seq1
  start with 5
  increment by 1
  maxvalue 15
  minvalue 1
  cycle
  nocache;
  

• 测试

  SQL> select seq1.nextval from dual;
  

• 从测试结果可以看出执行select seq1.nextval from dual;语句后的初始值为5（start with 5）并依次增加1（increment by 1）直到15（maxvalue 15）然后从1（minvalue 1）开始新的循环（cycle）直到15。

1.2理解cache和nocache

• 创建序列：不缓存序列值

  create sequence seq_nocache nocache;
  

• 测试

  declare
    x number;
  begin
    for i in 1..10000 loop
      select seq_nocache.nextval into x from dual;
    end loop;
  end;
  

• 创建序列

  create sequence seq_cache cache 50;
  

• 测试

  declare
    x number;
  begin
    for i in 1..10000 loop
      select seq_cache.nextval into x from dual;
    end loop;
  end;
  

• 显然不缓存序列值要比缓存序列值慢一点

1.3跳号

• 创建序列

  create sequence seq_test cache 20;
  

• 授予用户“yh”alter system 的权限
  

• 测试
  

• 显然产生跳号，从23跳到41

2.修改序列

• 如果要修改序列的起始值，必须先删除序列，然后再重新创建；

  alter sequence seq_test nocache;
  

3.查看序列

• 数据字典：user_sequences

  select * from user_sequences where SEQUENCE_NAME = 'SEQ_TEST';
  

select last_number from user_sequences where SEQUENCE_NAME = 'SEQ_TEST';

三、同义词synonym

• 是数据库对象的一个别名
• 分为：私有同义词，共有同义词；
• 将创建共有同义词的权限赋予用户“yh”
  
• 创建共有同义词synCourse
  
• 将删除同义词的权限赋予用户“yh”
  
• 删除同义词synCourse
  

四、视图

1.创建视图

• 将创建视图的权限赋予用户“yh”
  

• 创建视图

  create or replace view IS_Student
  as
  select * from Student
  where Sdept = 'CS'
  with read only;
  

• 不能通过视图对基本表进行插入、删除、修改操作；

2.并不是所有视图都可以更新

• 创建视图

  create or replace view S_G
  as
  select Sno,avg(Grade) avgGrade from SC
  group by Sno
  

• 更新视图出错
  

• 查看视图中哪些列是可以更新的，哪些是不可以更新的

  select * from user_updatable_columns where table_name = 'S_G';