Oracle数据库---常用语句(附练习题)

表的结构---create /alter /drop /truncate

1 创建表

创建表结构的格式：

create  table   表名(

     列名1  数据类型 ,

     列名2  数据类型 ,

     列名3  数据类型 ,

     列名4  数据类型 ,

     列名5  数据类型 ,

     ...

     列名N 数据类型

);

create table 表名(

     列名1 数据类型 [primary key],

     列名2 数据类型 [unique],

     列名3 数据类型 [not null | null],

     列名4 数据类型 check(条件),

     列名5 数据类型 [references 表名(列名)]

     ...

     列名N 数据类型 [default 默认值] [各种约束]

);

列名命名规则：字母、数字、下划线(_)组成，不要以数字开头

mobile number(7,3)  

10000 12312313 1  123.45   1234.567  1234.567

shengri date

数据类型：

数字：number 可以表示整数，也可以表示小数 number(5,2) 一个数为5位数的有效数，2位小数 -999.99~999.99 numbe(5) -99999~99999

日期：date

字符串：char、varchar2，在Oracle中，字符串用单引号引起来，用双引号引起来是别名

       char是定长，如char(12)，存储内容的最大长度是12个字节，若实际存储内容只有6个字节，该列仍然占用12个字节 char(10) 小明

       varchar2是可变长，如varchar2(12)，存储内容的最大长度是12个字节，若实际存储内容只有6个字节，该列仍然占用6个字节

       

       字节：字节是由二进制代码组成，二进制代码就是0和1组成，8个二进制位组成1个字节，即1B = 8b

       字符：是由字节经过特定的编码方式组成，1个中文字符占3个字节，对于英文字符来说，一个字符占用1个字节。

创建一个简单的表

create table t_student(

       id number,

       name varchar2(50),

       sex char(12),

       birthday date

);

create table student_tmp(

       id number,

       name varchar2(50),

       sex char(12),

       birthday date

);

字段的约束条件(约束不是必写的)：

约束：就是限制

    唯一约束(unique)：值不能重复，但是可以为null。属于表对象(跟列一样)，必须要有名字，若没有指定名字，则Oracle随即分配一个唯一的名字

    非空约束(not null)：值不能为空，一个列默认是可以为空

    默认值(default)：当没有给列指定值的时候，则取默认值；在创建表时，若没有给列指定默认值，那么该列的默认值是空(即null)

    检查约束(check)：检查值是否满足某个条件，属于表对象，必须要有名字

    主键(primary key)：相当于身份证号码，包含非空约束和唯一约束，也是属于表对象，在设计一张表示，需要有主键列

    外键(references)：也属于表对象，必须要有名字

主键(primary key)：相当于身份证号码，是唯一的，通过身份证号码只能找到一个人，即通过主键列只能找到一行数据，在创建表时，通常要有主键列,主键属于表对象，所以主键有一个名字，若没给主键指定名字，oracle会自动分配一个唯一的名字，在一个表中只能有一个主键对象

以下两个表有约束关系：

create table t_class(

     classid number primary key,   ---- 主键

     classname varchar2(100)

);

--sysdate：获取系统当前时间，格式是：年月日时分秒

create table t_user(

     t_id number primary key,   ---- 主键

     t_sex varchar2(20) check(t_sex = 男 or t_sex = 女),   ----- 检查

     t_name varchar2(50) not null,   --- 非空

     t_cardid varchar2(20) unique,  --- 唯一

     t_birthday date default sysdate,   ---- 默认值

     classid number references t_class(classid)   ----- 外键

);

商品售货系统设计案例

现有一个商品的数据、记录客户及其购物情况，由下三张表组成

1）、商品goods（商品号goodsid,商品名goodsname,单价unitprice,商品类别ccategory，供应商provider）

2）、客户customer（客户号customerid，姓名cname,住址address，电邮email,性别sex，身份证cardid）

3）、购买purchase（客户号customerid,商品号goodsid，购买数量nums）

要求：每个表格要建立主键或外键，客户的姓名不能为空值；单价必须大于0，购买数量必须在1到30之间；

电邮不能重复；客户的性别必须是男或女，默认是男。

-------- 查看表结构 --------

在 command window(命令窗口)下输入：

desc 表名;

2 修改表

修改表：增加列

alter table 表名

add(字段名1 数据类型 [not null | null],字段名2 数据类型 [not null | null]);

如果要新增多个列，则要用逗号将各列定义分开。

例：向t_student表中增加一个性别和住址字段

alter table t_student

add(score number,addr varchar2(50));

alter table t_student

add classid number references t_class(classid);

--------修改列：修改数据类型、约束等等--------

alter table 表名

modify(列名  数据类型  [default 默认值] [not null | null]);

如果要修改多个列，则要用逗号将各列定义分开。

例：修改t_student的name字段类型为varchar2(100)。

alter table t_student

modify name varchar2(100) ;

删除列

alter table 表名

drop column 列名;

例：

alter table t_student

drop column score;

alter table t_student

drop column addr;

3 表的删除

删除表结构：drop table 表名

truncate table 表名

例：删除student_tmp2表

drop table student_tmp2;

dept --> 部门表：

deptno  部门号

dname   部门名称

loc     部门地址

emp --> 员工信息表：

empno    员工的工号

ename    员工的姓名

job      工种

mgr      员工上司的工号

hiredate 入职日期

sal      工资

comm     奖金

deptno   部门号

表内数据的操作---select /update /insert /delete

Oracle数据库数据操作(DML:data manipulation language)

/*dml：数据库操作语句，常见的增、删、改*/

1. 查询  

--- 注意：后面的查询案例，如无特殊说明，均以系统中存在的emp和dept两个表为例

/*查询的总语句

select 列1,列2,列3...列N 或 *

from 表名/(表达式) -- 数据池

[where 过滤条件]

[group by 分组内容]

[having 组内条件]

[order by 排序方式]

*/

********************1.1 无条件查询(全表查询)********************

语法：

select 列1,列2,列3...列N 或 *

from 表名;

--查询指定列

select empno,ename,job,mgr,hiredate,sal,comm,deptno

from emp;

--查询查询所有列,用*表示所有列

select * from emp;

********************1.2 条件查询********************

语法：

select 字段1,字段2,...,字段n  或者 *

from 表名

where 查询条件;

-- 查询条件的构成

1）、单值条件比较运算符： >  >=  <  <=   =   !=  <> ，比较运算符两边的数据类型要保持一致

2）、查询条件构成： 字段名  比较运算符  值

例：查询20号部门中全体员工的姓名、工资、和工种的有关信息。

select ename,job,sal from emp

where deptno = 20;

思考以下sql，两边的数据类型是否一致？

select * from emp where sal = 八百;

练习：查找出奖金超过其工资的雇员的姓名、工资、奖金和工种的信息。

----distinct：对于指定的字段所选择的重复行，只返回一行。

例：显示出20号部门中不同的工种。

select distinct job from emp

where deptno = 20

/*没有使用distinct关键字的结果

select job from emp

where deptno = 20

*/

---多条件过滤之逻辑运算符:and、or

例：查找出全部经理 和 第10号部门秘书的有关信息

select job,ename,sal,deptno from emp

where job=MANAGER or (job=CLERK and deptno=10);

练习：查找出不是30号部门中的所有经理的所有信息。

----多值比较运算符：(not)between 值1 and 值2, in(指定的值), not in(指定的值)

例:查找出工资在2000到3000之间的职工姓名、工种和工资

select ename,job,sal from emp

where sal between 2000 and 3000;

练习：查找出工资在2500到3500之外的职工姓名、工种和工资

例：查询出全部秘书、分析员或推销员的姓名、工种、工资和所在部门号

select ename,job,sal,deptno from emp

where job IN(CLERK,ANALYST,SALESMAN);

练习：查询出工资分别是1500，2500，3000的分析员或推销员的姓名、工种、工资和所在部门号

-----模糊查询: like

%表示匹配任意长度的字符串

%test%:表示匹配中间字符串为test的任意长度的字符串

_表示仅能匹配一个字符

o_a:表示匹配长度为3，以字母o开头，且以字母a结尾的字符串，中间一个字符为任意字符。

例：查询出名字以"MA"开头的全部职工的姓名、工种、工资和部门号

select ename,job,sal,deptno from emp

where ename like MA%;

例：查询出名字以"SCO"开头的，长度为5位的全部职工的姓名、工种、工资和部门号

select ename,job,sal,deptno from emp

where ename like SCO__;

练习：

1、查找出所有工种以MANAG开头的职工姓名、工种和工资

2、查找出所有姓名以ALLE开头，且长度为5的职工姓名、工种和工资

-----is null 或 is not null

例：查找出emp表中经理号(MGR)为空的职工的有关信息

select * from emp where MGR is null;

  

如果某字段为空，则要使用比较运算符"IS NULL"(非空使用"IS NOT NULL")，不能用"=NULL"或"!=NULL"表示。

练习：查找出没有奖金的员工的姓名、工种、工资和部门号

**************************1.3 排序显示查询结果********************

通常情况下，查询结果是以按数据在数据库表中存储的顺序显示的。若要使查询结果按某种特定顺序显示，就要使用“ORDER BY”子句。

格式：

ORDER BY 字段名 ASC/DESC;

字段名，指出查询出来的结果按该字段排序；选项ASC表示按升序排序，DESC表示按降序排序；默认是按升序排序。

ORDER BY子句可以对查询结果按一个字段或多个字段进行排序。按多个字段排序时，先按前面的字段排序，当字段相同时，再按下一个字段排序，以此类推。

例：计算每个销售人员的年度总报酬，并按总报酬由高到低顺序显示

select ename,job,sal,comm,12*(sal+comm) sumprice from emp

where job=SALESMAN

order by 12*(sal+comm) DESC;

select (sal + nvl(comm,0))*12 sumsal from emp

       where job = SALESMAN

       order by sumsal  desc;

---sumprice是12*(sal+comm)表达式的一个别名，该别名在标题中使用。别名不影响列的真名，它不能在查询语句的其他任何地方使用。

练习：

1、查找出工资高于1000元的职工的姓名、工种、工资和部门号，并按部门号由小到大排序显示

2、查找出奖金超过本人基本工资3%的职工的姓名，工资，奖金，奖金与工资的比例，并按其比例由高到低显示

3、按工种升序，而同工种按工资降序排列显示全部职工的姓名，工种，工资。

2 增加

向数据库中插入数据

/*新增数据

insert into 表名(列1,列2...列N) values(值1,值2...值N);

commit | rollback;

在Oracle字符串用单引号引起来

*/

insert into t_class values (1, 小班);

commit;

insert into t_class values (2, 大班);

rollback;

--多条dml语句组成一个事物

insert into t_class values (3, 小班);

insert into t_class values (4, 大班);

commit;

------ 当我们要向表中插入时间格式的数据时，需要使用to_date()函数把字符串转换为时间格式

语法：to_date(char,format)

例：向 emp 表中插入以下数据：(7250,abc,CLERK,2222,1997-07-01,250,50,20)

insert into emp values(7250,abc,CLERK,2222,to_date(1997-07-01,YYYY-MM-DD),250,50,20)；

练习：在emp表中，查找1981-05-01之前入职的员工信息

3 修改

3、修改数据库的数据

update 表名

set 列1 = 值1,列2 = 值2...列N = 值N

[where过滤条件];

例：将职工ALLEN提升为经理，工资增至$8000，奖金增加40%

update emp

set job = MANAGER,sal = 8000,comm=1.4*comm

where ename = ALLEN;

练习：对20号部门中的经理和销售人员提高15%的工资。

4 删除

4、删除数据库的数据

delete [from] 表名

[where过滤条件]

例：删除emp表中工资低于500元的所有员工的信息

delete from emp

where sal < 500;

**************************************

事务：通俗理解就是做一件事情的过程，这个过程有两种结果：成功、失败，在数据库中发出一条dml语句即开启一个事务，开启事务就一定要结束事务，

      结束事务的两种方式：

          a1)、commit，提交事务，所有的操作都生效，操作的结果直会永久保存在数据库中

          a2)、rollback，回滚事务，所有的操作都不会生效，操作的结果直接扔掉，不会永久保存到数据库中

**************************************

删除表的另一种方式：

truncate table 表名

截断表，即删除表中所有的数据，和delete的区别：

    delete：会发起事务，可以有where过滤条件(即删除指定的数据),属于dml语句，删除效率比truncate低

    truncate：不会发起事务，不能有where过滤条件(即只能删除表中所有的数据),属于ddl语句,删除效率比delete高，但有危险性

    若表中有列是其他表的外键，则不能在该表中使用truncate(即truncate会影响外键列)

    drop table 表名：即删除表的数据又删除表的结构。

delete from t_student;

commit;

truncate table t_student;

5 分组/聚合/统计函数：

count(*) -- 统计表中所有行的行数

select count(*) from emp;

count(字段) -- 统计表中指定字段的非空行数

select count(comm) from emp;

-- 练习：统计emp表中comm为空的人数

sum(字段) -- 统计指定字段的和

select sum(sal) 总工资 from emp;

avg(字段) -- 统计指定字段的平均值

select avg(sal) 平均工资  from emp;

max(字段) -- 统计指定字段的最大值

select max(sal) 最高工资 from emp;

min(字段) -- 统计指定字段的最小值

select min(sal) 最高工资 from emp;

select count(*) 总人数,

       count(comm) comm的非空行数,

       sum(sal) 总工资,

       avg(sal) 平均工资,

       max(sal) 最高工资,

       min(sal) 最低工资

from emp;

-- 例：计算emp表中公司职工的总人数及工种数

select count(*) 总人数,count(distinct job) 工种数

from emp;

-- 练习：计算全部销售员的年平均报酬。

---分组函数只能从一组值中计算出一个汇总信息，所以在以上给出的查询实例中，分组函数是将查询所获得满足条件的记录行为作为一组，并返回一个统计值。如果要想“对多个组分别进行统计汇总”，例如，分别计算emp表中每个部门职工的最低工资、最高工资、平均工资和总工资，则要在查询命令中使用"GROUP BY"子句，进行分组查询。Oracle会把查询到的行分成多个组，对于每一个组返回一个汇总信息。

分组查询命令的基本格式为：

select 字段名

from 表名

[where 过滤条件]

[group by 字段];

例：计算出公司支付给每个工种的总工资

select job,sum(sal) from emp

group by job;

如果要将各个分组再进行划分出不同的小组，则需要在"group by"子句中指定多个列名。

例：计算每个部门中每种工种各有多少职工数。

select deptno,job,count(*) from emp

group by deptno,job;

在本例中，先按部门号将职工分组，在同一部门中再按职工的工种进一部分组。

但要注意，在包括"group by"子句的查询中，select子句后的列名表中，

除统计函数外，均应包含在"group by"子句中(即列名表中不能出现与分组子句无关的列名)，否则出错！

练习：统计各部门的人数。

练习：找每个部门中每种职位的最高和最低的工资

4.2 带条件的分组查询

利用"group by"子句将选择到的进行分组时，可以使用 having 子句用于限制选择的组，having子句的作用同where子句相似，都是指定查询条件。不同的是where子句对行进行选择，检查每条记录是否满足条件，而having子句是检查分组之后的各组是否满足条件。having子句是配合group by子句使用，在没有"group by"子句时不能用，并且group by和having子句在命令中要出现在where子句之后。

select 字段/表达式

from 表名

where 单条记录的过滤条件

group by 字段

having 分组函数的比较表达式;

1、分组函数只能出现在select、having、order by 子句中

2、如果在select语句同时含有group by、having、order by，那么他们的顺序是group by、having、order by

3、group by 有一个原则,就是 select 后面的所有字段，必须出现在 group by 后面，否则，有语法错误（重要）

4、有having 一定有group by

例：查询各工种组的年平均工资，要求每个工种组至少在2人以上

select job,avg(sal+nvl(comm,0))*12 avgsal,count(*) from emp

group by job

having count(*)>2;

练习1：查询出至少有两名秘书的所有部门的部门号，并按人数降序排序。

练习2：查询出所有经理和销售人员的年平均工资，并按年平均工资降序排序。

----总结：什么时候需要用到 group by 进行分组：

-- 1）、题目直接告诉我们，需要对多组数据进行分组；

-- 2）、当select字句后面，同时出现字段和分组函数，就必须要用到group by进行分组，

--      且select语句后面的所有字段都要包含在group by后面。

------------******************华丽的分割线(第二天下午完)******************---------

6多表查询

1、等值连接

select 表别名1.列名1,表别名1.列名2....  或 表别名1.*,表别名2.*

from 表名1 表别名1,表名2 表别名2,...表名n,表别名n

where 表名1或表别名1.列名=表名2或表别名2.列名 and 表名(n-1)或表别名(n-1).列名=表名n或表别名n.列名

例：查找名字为"ALLEN"的职工所在的部门号、部门名和部门所在地

select e.ename,e.deptno,d.dname,d.loc from emp e,dept d

where e.deptno=d.deptno and e.ename=ALLEN;

e.deptno=d.deptno的作用：该命令对两个表进行连接查询，oracle会扫描dept表，分别检查每条记录在连接条件e.deptno=d.deptno中所用列的值从emp表取出的记录的列值是否相等。如果相等，则将这两个记录连接，产生一条新的记录作为查询到的一行，再继续从表emp中取下一条记录。重复这个步骤，一直到处理完emp表的全部符合条件的记录。

如果对多个表进行连接查询，但在where子句中没有给出连接条件(称无条件连接)，则Oracle将构成一个笛卡尔积，对所有表的全部行进行组合，产生大量的行，其结果很少使用，因此在查询中要避免产生笛卡尔积。

练习：

查询部门号是20,30,40的职工的员工编号，姓名，工资，部门所在位置。

显示部门号为10的部门名、员工号和工资

显示雇员名，雇员工资及所在的部门的名字，并按部门名排序

显示平均工资高于2000的部门号和它的平均工资

-- 分析题目的思路：

1、如果有多个表，先找到这些表的相同字段；

2、确定主从表。

--- 查询的总思路：

1、分析题目，确定查询的数据涉及哪些表；

2、如果查询的数据必须涉及多张表，就要用到多表查询：

   找到两张表中相同意思的字段，在where语句后面用等于号连接。

3、如果需要对单条数据做过滤，就把过滤条件追加到where语句后面，并用and连接；

4、根据题目要求，填写select语句后面的内容；

5、如果需要对多组数据进行统计，就要用到group by语句；

6、如果需要对分组后的数据进行过滤，就要用到having语句；

7、如果需要对数据进行排序，就要用到order by语句。

select 3

from  1

where 2

group by 4

having 5

order by 6

2、外连接查询(左右连接)  

select 列名

from 表名1 表别名1,表名2 表别名2

where 表别名1.列名=表别名2.列名(+) 或 表别名1.列名(+)=表别名2.列名;

select ... from table1 t1,table2 t2 where t1.id = t2.id(+); --左连接

select ... from table1 t1,table2 t2 where t1.id(+) = t2.id; --右连接

在where子句的条件中必须包含外部连接操作符"(+)"。当该操作符加在表的列名上时，每当该表没有行与另一表的一行连接时，Oracle将为该表列产生空值。外键不重复的值不可能比较主键的多，所以，把操作符"(+)"加到外键上即可。主键所在的表为主表，外键所在的表为从表。

---- 左右连接的使用思路：

1、确定主从表，从而确定主外键；

2、连接符(+)放在外键后面，能产生的结果是：

   当从表没有数据和主表连接的时候，就会产生空行与主表连接。

例：显示出所有部门的编号、名称和其职工的姓名与工种。

select d.deptno,d.dname,e.ename,e.job from dept d, emp e where d.deptno = e.deptno(+)

左右连接的另一种写法：

select * from A2 right JOIN B2 ON A2.id=B2.bid

select * from A2 left JOIN B2 ON A2.id=B2.bid

以上例子，可写成以下格式：

select d.deptno,d.dname,e.ename,e.job from emp e right join dept d on e.deptno = d.deptno;

select d.deptno,d.dname,e.ename,e.job from dept d left join emp e on d.deptno = e.deptno;

练习：列出无雇员的部门的情况。

---PS：有外连接，就有内连接，内连接用于返回满足连接条件的所有记录。默认情况下，在执行连接查询时，如果没有指定任何连接操作符，那么，这些连接查询都属于内连接。

3、子查询

格式：

SELECT <列名表>

FROM 表名

WHERE 列表或表达式 比较运算符(SELECT 列名

FROM 表名

WHERE 条件);

----子查询，必须要用圆括号括起来。

单行值子查询

单行值子查询，是指只返回一行(或者说是，一个记录)的子查询。

例：查找出与“SMITH”在同一个部门工作的所有职工姓名及工资

分析思路：

select e.ename,e.sal

from emp e

where deptno =  (与“SMITH”在同一个部门)

--- SMITH所在的部门

select deptno from emp where ename = SMITH

--- 答案：

select e.ename, e.sal

  from emp e

 where deptno = (select deptno from emp where ename = SMITH);

--对于该子查询命令，系统执行时先执行子查询，返回一个值(即SMITH的部门号)作为主要查询(或外层查询)的条件，然后再根据该条件执行主查询选择出预期的结果，即与“SMITH”同一部门的所有职工。通常，主查询与子查询之间用"="、">"等比较运算符进行连接。

练习：

(1)、查找出工资比"SCOTT"工资高的职工的名字，工种，工资和所在的部门号，并按工资升序排序。

(2)、查找出工资比"SCOTT"高，并且在"NEW YORK"工作的职工的有关情况。

(3)、查找出具有最高月工资的雇员的姓名、工种和工资。

(4)、查找出在"CHICAGO"工作的职工的姓名、工种和工资。

4、多行值子查询

    多行子查询，是指子查询会返回多个值，此时，需要用到多值比较运算符：[NOT]IN

   IN运算符表示属于的关系，即是否在所选数据的集合之中。"NOT IN"表示不属于集合或不是集合的成员。

例题：查找出工种在部门10号中没有的其它部门职工的姓名，工种和工资的信息 。

select e.ename,e.job,e.sal from emp e where e.job not in(select distinct e.job from emp e where e.deptno = 10)

-- 查找出工资在1000到3500元之间的职工所在部门的所有人员的有关信息。

-- 查找出工种不在10号部门的其他部门的职工的姓名、工种和工资信息。

-- 查找出部门20中的工种相同的职工的姓名与工种。

********************

注意：

1、数据库的增加，删除，修改，都不可以使用多表连接；

如果删除，修改的数据涉及多张表，必须使用子查询。

2、删除的数据存在于多张表，必须先删从表的数据，再删主表的数据。

例如：有a , c 两张表，要删除张三的信息（a为主表，c为从表）

delete from c where aid=(select aid from a where a.name=张三);

      Delete from a where a.name=张三;

********************

*************************  数据库扩展知识 ********************************************

7 在from子句中使用子查询

例：显示高于自己部门平均工资的员工的信息

select e.*

from emp e,(select deptno, round(avg(sal+nvl(comm,0)),2) avgsal from emp group by deptno) t

where e.deptno = t.deptno and e.sal > t.avgsal;

练习：

1、列出工资大于本部门平均工资的员工姓名和部门号及统计此部门的人数

结果显示如下：

EMPNAME DEPID 部门人数

ZHANGSAN 1     15

LISI   2     10

2、：

姓名          课程           成绩

张三           语文          94

张三           数学          95

李四           英语          88

李四           语文          99

李四           数学          96

找出语文，数学，英语三科成绩都大于或等于90分的学生姓名。

select 姓名

  from tclass

 where 姓名 not in

       (select 姓名 from tclass group by 姓名 having min(成绩) < 90);

8 /*伪列

 rowid：属于表中的列，是由数据库自动增加的列，是真实存在的一个列，唯一标识一条数据的物理位置（查询语句首先得到数据的rowid，然后根据rowid到数据文件中唯一定位数据）

       因此，根据rowid查询的效率是最高的，数据在入库时(即成功插入数据)，Oracle数据就给这条数据分配一个唯一的rowid

 rownum：是给结果集增加的一个序号，不属于任何的表，先有结果集，才能有rownum，始终是从1开始编号(不能跨过1而存在)  分页

*/

--根据rowid查询的效率是最高的，但通常情况下不知道rowid的值，rowid是整个数据库唯一(在不同的数据库中不一定唯一)

select rowid from emp where emp.rowid = AAAMgzAAEAAAAAgAAA;

select rowid,emp.* from emp;

--rownum不属于任何的表,以下sql是否有问题?

select * from emp st where st.rownum = 1;

select st.*, st.rownum from emp st;

select st.*, rownum from emp st;

--始终是从1开始编号(不能跨过1而存在)

select * from emp where rownum < 7;(可以查到数据)

select * from emp st where rownum > 10;(查不到数据)

select * from emp st where rownum between 1 and 10;(查不到数据)

select * from emp st where rownum between 2 and 10;(查不到数据)

select * from student st  where rownum <= 20 or rownum >= 10;(查不到数据)

--分页查询------使用子查询实现

练习1:查找5到10行的内容

select emp.*,rownum r from emp  where rownum <= 10;

select * from (select emp.*,rownum r from emp  where rownum <= 10) t where t.r > 5;

或者 select * from (select emp.*,rownum r from emp) t where t.r  between 5 and 10;

练习2:

select *from (select st.*, rownum rownum1 from emp st where rownum <= (select count(*) from emp)) t  where rownum1 >= 10;

使用rownum的注意事项

1.不能直接对rownum使用>（大于1的数值）、>=（大于1的数值）、=（大于1的数值），否则无结果。

2.在使用rownum时，只有当Order By的字段是主键时，查询结果才会先排序再计算rownum，但是，对非主键字段（如：name）进行排序时，结果可能就混乱了。出现混乱的原因是：oracle先按物理存储位置（rowid）顺序取出满足rownum条件的记录，即物理位置上的前5条数据，然后在对这些数据按照Order By的字段进行排序，而不是我们所期望的先排序、再取特定记录数。

用MySQL查询分页

Select *from 表名 limit 4,6;

意思是从第四行开始,然后选中后面5行的数据,就是查询5到10行的内容.

--视图就是一张临时表，不能往视图中插入、修改、删除数据，只能查询数据

---切换至sys账户---

create or replace view v_emp

as

select u.*, rownum rownum1 from USER$ u where rownum <= 10;

rename v_emp to v_temp;

drop view v_temp;

---- "索引"就是一本书的目录，提升查询数据的效率

create index i_student_name on t_student(name);

drop index i_student_name;

create unique index i_studnet_name on t_student(name);

9数据备份

--*********** 导出表数据 ***********

步骤：

1、选择PL/SQL菜单栏的Tools下面的Export Tables

2、切换到"SQL Inserts"窗口

3、选择"Create tables"

4、在Output file处，选择输出的地址，并自定义好以 .sql 结尾的文件名字，如 emp.sql

5、点击Output file最右边的Export按钮即可。

--*********** 导入表数据 ***********

步骤：

1、选择PL/SQL菜单栏的Tools下面的Import Tables

2、切换到"SQL Inserts"

3、选择“Use Command Window”

4、在Import file中选择要导入的sql文件

5、点击Import file最右边的Import按钮即可。

--*********** 导出表数据 ***********

步骤：

1、选择PL/SQL菜单栏的Tools下面的Export Tables

2、切换到"SQL Inserts"窗口

3、选择"Create tables",不要勾选包含 include的选项

4、在Output file处，选择输出的地址，并自定义好以 .sql 结尾的文件名字，如 emp.sql

5、点击Output file最右边的Export按钮即可。

  

--*********** 导入表数据 ***********

步骤：

1、选择PL/SQL菜单栏的Tools下面的Import Tables

2、切换到"SQL Inserts"

3、选择“Use Command Window”

4、在Import file中选择要导入的sql文件

5、点击Import file最右边的Import按钮即可。