oracle 非常完整的总结

Ⅰ.oracle itcast

本文档对应程序在myeclipse的jdbc/src/下

 

 

 

Oracle安装自动生成sys用户和system用户

sys 超级用户  具有最高权限 具有sysDBA角色，有create database权限

该用户默认密码是change_on_install

 system 管理操作员 权限也比较大，具有sysoper角色，没有create database权限。

该用户默认密码是 manager

这是通过sqlplus客户端连接数据库时有多个实例采用下面DOS命令：sqlplus scott/tiger@zhulin

见2.13 oracle创建数据库实例

启动sqlplus，然后登陆数据库出现错误:TNS:协议适配器错误

原因有3个：

1.监听服务没有启动：services.msc或开始—>程序—>管理工具—>服务，打开服务面板：

       启动oraclehome92TNSlistener服务

2.database instance没有启动：services.msc或开始—>程序—>管理工具—>服务 启动oralceserviceXXX,XXX就是你databaseSID如zhulin

3.注册表问题：

       regedit.msc

       进入HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb11g_home1

下的ORACLE_SID值修改为zhulin

 

       你的全局数据库名字 你的数据库SID即可。

ORACLE用SYS和SYSTEM默认密码登录提示ORA-01017:invalidusername/password;logond denied该怎么解决？

解决办法：

有可能是你在建数据库的时候,

修改了默认的密码

而自己又忘记

你可再重新修改过来

sqlplus / assysdba

alter user system identified by manager;

alter user sys identified by manager;

或者改成其他的你自己容易记住的

默认scott用户密码是tiger

 

1.oracle解锁步骤

先使用system登录

然后输入alter user scott account unlock； //解锁scott账号

SQL语句必须带分号!!!!!!!!!!!!!最好都分号结束

2.oralce开发工具

sqlpulsw和sqlus工具

在开始→程序→oracle oradb_home10g→application development→sqlplus

或在运行栏输入sqlplus

pl/sqldeveloper 这款软件用的很多 第三方软件 需要单独安装

企业管理器(web)  首先保证相关服务启动即oracleDBconsole+实例名启动

在浏览器中输入http://ip:1158//em   ip是指你的具体ip地址或者你的机器名  1158是端口

一般情况下 这个服务是不启动 很不安全

3.oracle常用sql plus命令

 (1)请使用scott用户登录oracle数据库实例，然后切换为身份为system

       简单使用 conn 用户名/密码

       登录后，使用 conn[ect] 用户名/密码@数据库名 [assysdba/sysoper]

(2)showuser  显示当前用户名

(3) 断开连接 disc[onnect]

(4)exit断开连接和退出sqlplus窗口

(5) 修改密码(前提是system或sys用户) passw[ord]

       基本用法 password 用户名

       如果给自己修改密码 则可以不带用户名

       如果给别人修改密码 则需要带用户名

(6)& 交互命令可以替代变量的值

       select* from emp where job=”&job”;

(7)edit用于编辑脚本(文本)

  SQL>edit d:\

(8)spool  把屏幕上显示的记录，保存到文件中

spool on

spool d:/bak.sql

查询语句

spool off

(9)linesize用户控制每行显示多少个字符，默认80个字符 每次都要重新设置

       基本用法： set linesize 120

(10)pagesize  用于每页显示多少行

   基本用法： set pagesize 100

4.oracle用户管理

(1)创建用户  只有具有DBA权限才能创建比如system sys

       基本用法：create user 用户名 identified by 密码

       举例:create user xiaoqiang identified by hao200881037[oracle要求用户密码不能用数字开头]

后面我将密码修改为了200881037

？为什么创建的用户无法登陆

这是因为oracle 刚刚创建的用户是没有任何权限，需要管理员给用户分配适应的权限，才能够登陆

grantcreate session to xiaoqiang  //会话权限

 

(1)权限

系统权限：和数据库管理相关的权限：

       create session;create table;create index;createview;create sequence;create trigger

对象权限：和用户操作数据对象相关的权限：

       update;insert;delete;select

(2)角色

预定义角色：把常用的权限集中起来，形成角色(套餐)

              比如dba  connect resource  三种角色

自定义角色：自己定义套餐

(3)方案(schema)

在一个数据库实例下：

当一个用户，创建好后，如果该用户创建了任意一个数据对象(表或触发器等),这时我们的DBMS就会创建一个对应的方案与该用户对应，并且该方案名字和用户名一致。

小技巧：如果希望看到某个用户的方案的数据对象，可以使用PL/SQL developer工具

案例1：完成一个功能；让xiaoqiang用户去查询scott的emp表

步骤1：先用scott登录

       connscott/tiger

步骤2：在scott账号上给xiaoqiang赋权限

grant select[update|delete|insert|all] on emp to xiaoqiang

这里就可以看出来 方案A和方案B可以有相同名的数据库，但是方案A中不可以有相同名的数据库

步骤3：登录xiaoqiang用户去查询emp表

错误用法：select * from emp 原因是在xiaoqiang登录状态下需要制定emp表来自哪里？

正确用法：select * from scott.emp;

查询时如果查询其他方案 一定要用带上其他方案名。

如果不带，就默认是select* from xiaoqiang.emp

 

案例2：完成一个功能想办法将xiaoqiang拥有的对scott.emp的权限转给stu用户。

scott—>xiaoqiang—>stu[权限转移]

conn scott/tiger;

grant all on scott.emp to stu with grantoption;

 

//with grant option 对象权限   表示得到权限的用户可以把权限继续分配

//with admin option系统权限  如果是系统权限，则带with admin iption

 

 

 

 

创建了普通账户 xiaoqiang  密码hao200881037

修改密码(前提是system或sys用户) passw[ord]

       基本用法 password 用户名

       如果给自己修改密码 则可以不带用户名

       如果给别人修改密码 则需要带用户名

 

表空间：表存在的空间，一个表空间就是指向具体的数据文件

 

(4)用户管理的综合案例

创建的新用户是没有任何权限的，甚至连登录(会话)的数据库的权限都没有，需要为其指定响应的权限，给一个用户赋权限使用命令grant，回收权限revoke

grant权限/角色 to 用户

 

(1)   使用system创建xiaoqiang

后面我将密码修改为了200881037

(2)   使用system给小红分配2个常用角色

grantconnect to xiaoqiang

grantresource to xiaoqiang

disconn  //切断连接

(3)   让xiaoqiang登录

conn xiaoqiang/200881037

(4)   xiaoqiang修改密码 pasw[ord] xiaoqiang即可 然要求你输入旧密码 当然 超级管理员不需要输入旧密码

(5)   xiaohong创建一张最简单的表

(6)   使用system登录，然后回收角色。

revoke connect fromxiaoqiang

revoke resource fromxiaoqiang

(7)   删除xiaoqiang用户:

drop user 用户名[cascade]

       ☞当我们删除一个用户的时候，若这个用户自己已经创建了数据对象(表、触发器等)，需要加选项cascade表示把这个用户删除同时，把该用户创建的数据对象一并删除。

否则无法删除该用户，oracle用户认为删除了该用户就彻底抛弃了

 

(5)账号锁定

使用profile管理用户口令，账号锁定指用户登录时最多可以输入密码的次数，也可以指定用户锁定的时间(天)一般用DBA的身份去执行该命令。 profile文件[规则]

eg:

create profile lock_accountlimit failed_login_attempts 3 paswword_lock_time 2;

alter user tea profilelock_account;//其中lock_account是文件名

(6)账号解锁

alter user 用户名 account unlock;

(7)终止口令

eg:给tea创建一个profile文件，要求该用户每隔10天必须修改自家的登录密码，款限期为2天。

create profilemyprofile limit password_life_time 10 password_grace_time 2;

//可以继续加限制条件

alter user tea profile myprofile;

(8)删除profile文件

当不需要某个profile文件时，可以删除该文件。

drop profile profile文件名。

5.oracle数据库启动流程

oracle可以通过命令行的方式启动，我们看看具体如何操作：

windows下:

(1)lsnrctl start (启动监听)

(2)oradim –startup –sid 数据库实例名

linux下:

(1)  lsnrctl start (启动监听)

(2)  sqlplus sys/chang_on_install as sysdba(以sysdba身份登录)

sqlplus /nolog

conn sys/chang_on_install assysdba

(3)startup

6.oracle登录认证方式

oracle在windows和linux下是不完全相同的：

windows下:

如果当前用户属于本地操作系统的ora_dba组(对于windows操作系统而言)，即可通过操作系统认证。

普通用户:默认是以数据库方式认证，比如conn scott/tiger;             

特权用户：默认是以操作系统认证（即:只要当前用户是在ora_dba组中则可以通过认证），比如connsystem/manager as sysdba;DBMS一看到assysdba则认为要以特权用户登录，前面的用户名和密码不看，登录后自动切换成sys用户<=>connsys/manager。

如果当前用户（win7系统账号）不在ora_dba组中，conn sys/manager 输对了密码还是可以登录进去的(这时是采用了数据库方式验证)

sqlnet.ora文件在D:\xiaoqiang\oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN目录下：

同时如果你安装第三方工具PL/SQL Developer，同时也需要修改

D:\xiaoqiang\oracle\product\instantclient_11_2目录下的sqlnet.ora文件

通过配置sqlnet.ora文件，可以修改oracle登录认证方式:

SQLNET.AUTHENTICATION_SERVICES=(NTS)是基于操作系统验证

SQLNET.AUTHENTICATION_SERVICES=(NONE)是基于Oracle验证

SQLNET.AUTHENTICATION_SERVICES=(NONE,NTS)是二者共存

 

linux下:

默认情况下linux下的oracle数据库sqlnet.ora文件没有SQLNET.AUTHENTICATION_SERVICES参数，此时是基于操作系统认真和oracle密码验证共存，加上SQLNET.AUTHENTICATION_SERVICES参数后，不管SQLNET.AUTHENTICATION_SERVICES设置为NONE还是NTS都是基于oracle密码验证。

7.oracle丢失管理员密码怎么办

数据库实例名是根据实际情况命名的。

恢复办法：把原有密码文件删除，生成一个新的密码文件

恢复步骤如下：(1)搜索名为PWD数据库实例名.ora文件

                       (2)删除该文件，为以防万一，建议备份

                       (3)生成新的密码文件，在DOS控制台下输入命令

       orapwdfile=原来密码文件的全路径\密码文件名.ora password=新密码 entries=10;

这里密码文件名是原来的密码文件名=PWD数据库实例名

entries 表示登录sys的最多用户(特权用户)

如果希望新的密码生效，则需要重新启动数据库实例服务.dos下services.exe

 

还有出现以下情况：

ORACLE用SYS和SYSTEM默认密码登录提示ORA-01017:invalidusername/password;logond denied该怎么解决？

解决办法：

有可能是你在建数据库的时候,

修改了默认的密码

而自己又忘记

你可再重新修改过来

sqlplus / assysdba

alter user system identified by manager;

alter user sys identified by manager;

或者改成其他的你自己容易记住的

默认scott用户密码是tiger

8.oracle表管理

类(对象)和表(记录)之间的关系

◆创建表

基本语法

create table table_name( 列名 列类型, …… )

 

 

 

 

 

 

 

◆数据类型

       ① char(size)  存放字符串最大2000个字符，是定长

              eg：char(32)  最多只能放入32个字符 如果超过就报错，如果不够’abc’则用空格补全

② varchar2(size)变长最大可以存放4000个字符

       ③ nchar(size) 定长 编码方式unicode  最大字符数是2000个

             一个汉字占用nchar的一个字符空间，一个汉字，占用char的两个字符空间

④ nvarchar2(size)变长编码方式unicode最大字符数是4000个

⑤ clob  字符型大对象  变长 最大8TB

       ⑥ blob 变长

说明：我们在实际开发中很少把文件存放在数据库中(效率问题)，实际上我们一般记录文件的一个路径(URL或本地路径)，然后通过IO或网络来操作。

如果我们要求对文件安全性比较高，可以考虑放入数据库。

 

       ⑦ number(p,s)   p为整数位，s为小数位，范围是1<=p<=38,-84<=s<=-127 变长

              保存数据范围：-1.0e-130<=numbervalue<=1.0e+126  保存机器位数1-22byte

              e.gnumber(5,2) 表示一个小数有5位有效位，2位小数，范围-999,99-999,99

                     比如你输入 573.316则真正保存是573.32,无法保存数据1000

number(5)等价于number(5,0)，表示一个5位整数，范围-99999-99999，输入57523.316则保存57523

原则：如果在做实际开发中，我们没有指定数据小数位，则直接使用number

       ⑧date 日期类型

              包含年月日，时分秒

              插入数据时要使用默认格式是:'dd-mm-yyy';当然 如果用自己格式需要借用to_date函数

SQL> insert into test1values(to_date('2005-11-11','YYYY-MM-DD'));

1 row inserted

to_char

你可以使用select ename, hiredate, sal from emp where deptno =10;显示信息，可是，在某些情况下，这个并不能满足你的需求。

问题：日期是否可以显示 时/分/秒

SQL> select ename, to_char(hiredate, 'yyyy-mm-ddhh24:mi:ss') from emp;

 

9.oracle基本查询

oracle的crud操作(create retrieve/read update delete)

添加一个字段

SQL>ALTER TABLE student add (classIdNUMBER(2));

修改一个字段的长度

SQL>ALTER TABLE student modify (xm VARCHAR2(30));

修改字段的类型/或是名字（不能有数据） 不建议做

SQL>ALTER TABLE student modify (xm CHAR(30));

删除一个字段  不建议做(删了之后，顺序就变了。加就没问题，应为是加在后面)

SQL>ALTER TABLE student DROP COLUMN sal;

 修改表的名字   很少有这种需求

SQL>RENAME student TO stu;

 删除表

SQL>DROP TABLE student;

 

添加数据

所有字段都插入数据

INSERT INTOstudent VALUES('A001', '张三', '男', '01-5月-05',10);

oracle中默认的日期格式‘dd-mon-yy’ dd日子（天） mon 月份  yy  2位的年  ‘09-6月-99’ 1999年6月9日

修改日期的默认格式（临时修改，数据库重启后仍为默认；如要修改需要修改注册表）

ALTER SESSIONSETNLS_DATE_FORMAT ='yyyy-mm-dd';

修改后，可以用我们熟悉的格式添加日期类型：

INSERT INTOstudent VALUES('A002', 'MIKE', '男','1905-05-06', 10);

 插入部分字段

INSERT INTOstudent(xh, xm, sex) VALUES ('A003', 'JOHN', '女');

 插入空值

INSERT INTOstudent(xh, xm, sex, birthday) VALUES ('A004', 'MARTIN', '男', null);

问题来了，如果你要查询student表里birthday为null的记录，怎么写sql呢？

错误写法：select * from student wherebirthday = null;

正确写法：select * from student wherebirthday is null;

如果要查询birthday不为null,则应该这样写：

select * from student wherebirthday is not null;

 

修改数据

修改一个字段

UPDATE student SETsex = '女'WHERE xh = 'A001';

修改多个字段

UPDATE student SETsex = '男',birthday = '1984-04-01' WHERE xh = 'A001';

修改含有null值的数据

不要用 = null 而是用 is null；

SELECT * FROM student WHEREbirthday IS null;

 

删除数据

DELETE FROMstudent;

删除所有记录，表结构还在，写日志，可以恢复的，速度慢。

Delete 的数据可以恢复。

savepoint a; --创建保存点

DELETE FROMstudent;

rollback to a; --恢复到保存点

一个有经验的DBA，在确保完成无误的情况下要定期创建还原点。

DROP TABLE student; --删除表的结构和数据；

delete fromstudent WHERExh = 'A001'; --删除一条记录；

truncate TABLE student; --删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快。

oracle基本所有查询案例

在我们讲解的过程中我们利用scott用户存在的几张表（emp，dept）为大家演示如何使用select语句，select语句在软件编程中非常有用，希望大家好好的掌握。

 

 

 

 

 

 

 

查看表结构

DESC emp;

查询所有列

SELECT * FROM dept;

切忌动不动就用select*

SET TIMING ON; 打开显示操作时间的开关，在下面显示查询时间。

CREATE TABLE users(userId VARCHAR2(10),uName VARCHAR2 (20), uPassw VARCHAR2(30));

INSERT INTO users VALUES('a0001', '啊啊啊啊', 'aaaaaaaaaaaaaaaaaaaaaaa');

--从自己复制，加大数据量大概几万行就可以了  可以用来测试sql语句执行效率

INSERT INTO users (userId,UNAME,UPASSW)SELECT * FROM users;

SELECT COUNT (*) FROM users;统计行数

 

 查询指定列

SELECT ename, sal, job, deptno FROM emp;

 如何取消重复行DISTINCT

SELECT DISTINCT deptno, job FROM emp;

查询SMITH所在部门，工作，薪水

SELECT deptno,job,sal FROM emp WHERE ename= 'SMITH';

注意：oracle对内容的大小写是区分的，所以ename='SMITH'和ename='smith'是不同的

 

 使用算术表达式 nvl null

问题：如何显示每个雇员的年工资？

SELECT sal*13+nvl(comm, 0)*13 "年薪" , ename, comm FROM emp;

 使用列的别名

SELECT ename "姓名", sal*12 AS "年收入" FROM emp;

 如何处理null值使用nvl函数来处理

 如何连接字符串（||）

SELECT ename  || ' is a ' || job FROMemp;

 使用where子句

问题：如何显示工资高于3000的 员工？

SELECT * FROM emp WHERE sal > 3000;

问题：如何查找1982.1.1后入职的员工？

SELECT ename,hiredate FROM emp WHEREhiredate >'1-1月-1982';

问题：如何显示工资在2000到3000的员工？

SELECT ename,sal FROM emp WHERE sal>=2000 AND sal <= 3000;

 

 如何使用like操作符

%：表示0到多个字符                     _：表示任意单个字符

问题：如何显示首字符为S的员工姓名和工资？

SELECT ename,sal FROM emp WHERE ename like'S%';

如何显示第三个字符为大写O的所有员工的姓名和工资？

SELECT ename,sal FROM emp WHERE ename like'__O%';

 

 在where条件中使用in

问题：如何显示empno为7844, 7839,123,456 的雇员情况？

SELECT * FROM emp WHERE empno in (7844,7839,123,456);

 使用is null的操作符

问题：如何显示没有上级的雇员的情况？

错误写法：select *from emp where mgr = '';

正确写法：SELECT *FROM emp WHERE mgr is null;

 

使用逻辑操作符号

问题：查询工资高于500或者是岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J？

SELECT * FROM emp WHERE (sal >500 or job= 'MANAGER') and ename LIKE 'J%';

 使用order by字句   默认asc

问题：如何按照工资的从低到高的顺序显示雇员的信息？

SELECT * FROM emp ORDER by sal;

问题：按照部门号升序而雇员的工资降序排列

SELECT * FROM emp ORDER by deptno, sal DESC;

 

使用列的别名排序

问题：按年薪排序

select ename, (sal+nvl(comm,0))*12 "年薪" from emp order by "年薪" asc;

别名需要使用“”号圈中,英文不需要“”号

 

Clear清屏命令

 

数据分组 ——max，min， avg， sum， count

问题：如何显示所有员工中最高工资和最低工资？

SELECT MAX(sal),min(sal) FROM emp e;

      最高工资那个人是谁？

错误写法：selectename, sal from emp where sal=max(sal);

正确写法：selectename, sal from emp where sal=(select max(sal) from emp);

注意：selectename, max(sal) from emp;这语句执行的时候会报错，说ORA-00937：非单组分组函数。因为max是分组函数，而ename不是分组函数.......

但是selectmin(sal), max(sal) from emp;这句是可以执行的。因为min和max都是分组函数，就是说：如果列里面有一个分组函数，其它的都必须是分组函数，否则就出错。这是语法规定的问题：如何显示所有员工的平均工资和工资总和？

 

问题：如何计算总共有多少员工问题：如何

扩展要求：

查询最高工资员工的名字，工作岗位

SELECT ename, job, sal FROM emp e where sal= (SELECT MAX(sal) FROM emp);

显示工资高于平均工资的员工信息

SELECT * FROM emp e where sal > (SELECTAVG(sal) FROM emp);

 

 

groupby 和 having子句

groupby用于对查询的结果分组统计   having子句用于限制分组显示结果

 

问题：如何显示每个部门的平均工资和最高工资？

SELECT AVG(sal), MAX(sal), deptno FROM empGROUP by deptno;

（注意：这里暗藏了一点，如果你要分组查询的话，分组的字段deptno一定要出现在查询的列表里面，否则会报错。因为分组的字段都不出现的话，就没办法分组了）

 

问题：显示每个部门的每种岗位的平均工资和最低工资？

SELECT min(sal), AVG(sal), deptno, job FROMemp GROUP by deptno, job;

 

问题：显示平均工资低于2000的部门号和它的平均工资？

SELECT AVG(sal), MAX(sal), deptno FROM empGROUP by deptno having AVG(sal) < 2000;

 

 对数据分组的总结

1 分组函数只能出现在选择列表、having、order by子句中(不能出现在where中)

2 如果在select语句中同时包含有groupby, having, order by 那么它们的顺序是group by, having, order by

3 在选择列中如果有列、表达式和分组函数，那么这些列和表达式必须有一个出现在group by 子句中，否则就会出错。

如SELECTdeptno, AVG(sal), MAX(sal) FROM emp GROUP by deptno HAVING AVG(sal) < 2000;

这里deptno就一定要出现在group by 中

 

问题：显示雇员名，雇员工资及所在部门的名字【笛卡尔集】？

规定：多表查询的条件是 至少不能少于 表的个数-1 才能排除笛卡尔集

（如果有N张表联合查询，必须得有N-1个条件，才能避免笛卡尔集合）

SELECT e.ename, e.sal, d.dname FROM emp e,dept d WHERE e.deptno = d.deptno;

 

问题：显示部门号为10的部门名、员工名和工资？

SELECT d.dname, e.ename, e.sal FROM emp e,dept d WHERE e.deptno = d.deptno and e.deptno = 10;

 

问题：显示各个员工的姓名，工资及工资的级别？

先看salgrade的表结构和记录

SQL>select * from salgrade;

   GRADE        LOSAL          HISAL

-------------  -------------   ------------

       1         700           1200

       2         1201          1400

       3         1401          2000

       4         2001          3000

       5         3001          9999

SELECT e.ename, e.sal, s.grade FROM emp e,salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal;

 

扩展要求：

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

SELECT e.ename, e.sal, d.dname FROM emp e,dept d WHERE e.deptno = d.deptno ORDER by e.deptno;

（注意：如果用groupby，一定要把e.deptno放到查询列里面）

 

自连接

自连接是指在同一张表的连接查询

问题：显示某个员工的上级领导的姓名？

比如显示员工‘FORD’的上级

SELECT worker.ename, boss.ename FROM emp worker,emp boss WHERE worker.mgr = boss.empno AND worker.ename ='FORD';

 

 

请思考：显示与SMITH同部门的所有员工？

思路：

1 查询出SMITH的部门号

select deptno from emp WHERE ename = 'SMITH';

2 显示

SELECT * FROM emp WHERE deptno = (select deptno from emp WHERE ename= 'SMITH');

数据库在执行sql 是从左到右扫描的， 如果有括号的话，括号里面的先被优先执行。

 

 

请思考：如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号

SELECT DISTINCT job FROM emp WHERE deptno = 10;

SELECT * FROM emp WHERE job IN(SELECT DISTINCT job FROM emp WHERE deptno = 10);

（注意：不能用job=..，因为等号=是一对一的）

 

 在多行子查询中使用all操作符

问题：如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号？

SELECT ename, sal, deptno FROM emp WHERE sal > all (SELECT sal FROM emp WHERE deptno = 30);

扩展要求：

大家想想还有没有别的查询方法。

SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30);

执行效率上， 函数高得多

 

在多行子查询中使用any操作符

问题：如何显示工资比部门30的任意一个员工的工资高的员工姓名、工资和部门号？

SELECT ename, sal, deptno FROM emp WHERE sal > ANY (SELECT sal FROM emp WHERE deptno = 30);

扩展要求：

大家想想还有没有别的查询方法。

SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT min(sal)FROM emp WHERE deptno = 30);

 

多列子查询

单行子查询是指子查询只返回单列、单行数据，多行子查询是指返回单列多行数据，都是针对单列而言的，而多列子查询是指查询返回多个列数据的子查询语句。

请思考如何查询与SMITH的部门和岗位完全相同的所有雇员。

SELECT deptno, job FROM emp WHERE ename = 'SMITH';

SELECT * FROM emp WHERE (deptno, job) = (SELECT deptno, job FROM empWHERE ename = 'SMITH');

在from子句中使用子查询

请思考：如何显示高于自己部门平均工资的员工的信息

思路：

1. 查出各个部门的平均工资和部门号

SELECT deptno, AVG(sal) mysal FROM empGROUP by deptno;

2. 把上面的查询结果看做是一张子表

SELECT e.ename, e.deptno, e.sal, ds.mysalFROM emp e, (SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno) ds WHEREe.deptno = ds.deptno AND e.sal > ds.mysal;

如何衡量一个程序员的水平？

网络处理能力， 数据库， 程序代码的优化程序的效率要很高

小总结：

在这里需要说明的当在from子句中使用子查询时，该子查询会被作为一个视图来对待，因此叫做内嵌视图，当在from子句中使用子查询时，必须给子查询指定别名。

注意：别名不能用as，如：SELECT e.ename, e.deptno, e.sal,ds.mysal FROM emp e, (SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno)as ds WHERE e.deptno = ds.deptno AND e.sal > ds.mysal;

在ds前不能加as，否则会报错  （给表取别名的时候，不能加as；但是给列取别名，是可以加as的）

10.oracle分页查询

mysql:    select * from 表名 where 条件 limit 从第几条取，取几条 见mysql分页查询

sql server: select top 取几条 * from 表名 where idnot in(select top 4 id from 表名 where 条件)  也可以使用行集函数  见3.sql server分页查询

排除前4条，再取4条，这个案例实际上是取5-8条

oracle：

 

以scott/tiger账号登陆进行查询：[分页查询模板]

select t2.* from

(selectt1.*,rownum rn from

   (select * from emp) t1

 where rownum<=