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

数据库查询

一、SQL语言

• 数据定义语言（DDL）:CREATE、ALTER、DROP、MERGE；
• 数据查询语言（DQL）:SELECT；
• 数据操纵语言（DML）:INSERT、UPDATE、DELETE；
• 事务控制语言（TCL）:COMMIT、SAVEPOINT、ROLLBACK；
• 数据控制语言（DCL）:GRANT、REVOKE、DENY;

二、Oracle中SQL语言

• 关键字不区分大小写；字符串区分大小写；SQL语句以分号结束。

三、查询

1.查询全体学生的学号和姓名

select Sno,Sname from Student;

2.查询全体学生的学号和姓名，并将列名分别改为“学号”和“姓名”

select Sname as 姓名,Sno 学号 from Student;

3.查询全体学生的姓名及其出生年份

select Sname,TO_NUMBER(TO_CHAR(SYSDATE,'YYYY'))-Sage as 出生年份 from Student;

注：在SQL Server中的语句为

Select Sname;Year(GetDate())-Sage as 出生年份 from Student

四、伪列

（1）定义

• 伪列就像表中的列一样，但是在表中并不存储。可查询表中伪列的值，但不能插入、修改和删除伪列的值。

（2）伪列ROWID

• 表中的每一行在数据文件中都有一个物理地址，ROWID伪列返回该行的物理地址；
• 使用ROWID可以快速的定位表中的某一行；
• Oracle会自动为每一个表建立一个名称为ROWID的字段；
• ROWID的字段是隐含的，即查看表的信息时看不到ROWID字段；如：desc Student
• ROWID的扩展格式由18个字符组成；如：select rowid, Sno, Sname from Student;
• 前6个字符：数据库对象编号；
• 第7到9个字符：表空间中的数据文件的编号；
• 第10到15个字符：存储记录的数据块的编号；
• 第16到18个字符：标识同一个数据块中不同的记录的行编号。

1.使用伪列ROWID删除表中的重复的元组

• 创建表

  create table SC1 as select * from SC;

• 查看SC1表

  select * from SC1;

• 向SC1表中插入重复数据

  insert into SC1 values('2017001','1',92);
  insert into SC1 values('2017002','3',50);
  insert into SC1 values('2017001','1',80);
  

• 查看SC1表

  select rowid,Sno,Cno,Grade from SC1;
  

• 利用rowid删除指定行

  delete from SC1 where rowid = 'AAASSVAAEAAAAJbAAD';
  

• 使用rowid删除重复元组

  delete from SC1 where rowid not in (select min(rowid) from SC1 group by Sno,Cno,Grade);
  

（3）伪列ROWNUM

• 在查询的结果集中，ROWNUM为结果集中每一行标识一个行号，以此类推；
• 通过ROWNUM伪列可以限制查询结果集中返回的行数；
• ROWID是插入记录时生成的，ROWNUM是查询数据时生成的；
• ROWID标识的是行的物理地址，ROWNUM标识的是查询结果中的行的次序。

1.查询Student表中前3名学生的学号、姓名和专业。

select rownum Sno,Sname,Sdept 
from Student
where rownum <= 3;

2. 查询Student表中第1名学生的学号、姓名和专业。

select rownum Sno,Sname,Sdept 
from Student
where rownum = 1;

3.查询选课成绩最高的前3名学生的学号、课程号和成绩

• 错误代码

  select * from SC
  where rownum <= 3
  order by Grade desc;
  

• 正确代码

  select rownum,A.* from (select * from SC order by Grade desc) A
  where rownum <= 3;
  

• 可以看出错误代码虽然可以运行，但是结果错误，因为错误代码选出的结果是先按ROWNUM选出前3名学生的信息，再按这三名学生的成绩进行排序。

五、分页查询

（1）第一种语法

select *
from (select A.*,rownum rn from (select * from 表名）A where rownum <= 到第几行）
where rn >= 从第几行;

（2）第二种语法

select * 
from (select A.*,rownum rn from (select * from 表名) A)
where rn between 从第几行 and 到第几行;

1.查询Student表从第2行到第4行的记录

• 第一种

  select *
  from (select A.*,rownum rn from (select * from Student）A where rownum <= 4）
  where rn >= 2;
  

• 第二种

  select * 
  from (select A.*,rownum rn from (select * from Student) A)
  where rn between 2 and 4;