一、查询
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';