Oracle数据库管理与SQL操作实战指南-优快云博客

本文链接：https://blog.youkuaiyun.com/Sandry2009/article/details/10822607

本文详细介绍了在Oracle环境下进行数据库管理和SQL操作的步骤，包括启动和关闭数据库实例、使用SQL*PLUS进行查询、编辑及排序数据。讲解了SQL的基础语法，如SELECT语句、子查询、连接查询、条件过滤以及数据插入、更新和删除操作。同时，还探讨了内外连接、子查询在WHERE和FROM子句中的应用，以及如何进行Top-N查询。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

课前准备

解压 Red Hat Enterprise Linux 5.6&Oracle10g&SQL.rar

VMware 安装解压后的linux （里面有配好的oracle）

SQL*PLUS执行环境

命令存放路径: $ORACLE_HOME/bin

oracle安装过程见：

Oracle10g Install for Redhat5.6.docx

打开新linux，登录oracle

#su - oracle

（oracle切换到root的话，直接用 'su -' 就可以了）

在oracle用户中打开终端，执行如下操作

1) [oracle@localhost ~]$lsnrctl start #启动监听

2) [oracle@localhost ~]$sqlplus / as sysdba #进入sql连接

3) 在sql连接中执行 SQL>startup #启动oracle数据库实例

4） SQL>select sysdate from dual；，若出现当前系统日期，则表示数据库启动成功。

编辑命令：[L]IST , [A]PPEND ,[C]HANGE , [I]NPUT , DEL , / （执行），EDITOR

SQL> DEFINE_EDITOR=vi

SQL> select first_name from employees;

SQL> L (查看缓存中的sql,可用edit命令进行编辑，ctrl+d退出来【注意先设置：DEFINE _EDITOR=vi】)

1* select first_name from employees

SQL> A wheredepartment_id = 90 (在原先的基础上加了个限定条件，显示如下，可用‘/’直接进行执行)

1* select first_name from employees where department_id = 90

SQL> C/90/80/

1* select first_name from employees wheredepartment_id = 80

SQL> I (接着上面的语句添加sql语句)

2 where department_id=90;

SQL> /

SQL> DEL (执行一个del会删除一条语句)

SQL> @/home/oracle/sqldemo/summit1.sql --执行sql文件

SQL> spool info (新建一个info文件保存后续操作)

SQL> spool off 退出

在Oracle主目录下回产生一个文件info.lst

----??

SQL> ^? ^H

stty erase ^? | ^H

-------

SQL> clear screen --清屏

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

iSQL*PLUS执行环境

[oracle@Redhat ~]$isqlplusctl start

http://ip:5560/isqlplus

登录名：system

密码：shjdora

注：在外部访问，要关闭Linux操作系统防火墙。（setup进行操作）

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

PLSQL Developer开发工具& sqldeveloper开发工具

大家对sql应该都比较熟了，以下是一些例子，ppt里没什么精华，去掉了。。。

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

PPT1:RetrievingData Using the SQLSELECTStatement

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

select first_name, *

from employees;

ERROR atline 1:

ORA-00936:missing expression

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

关键字名称一般为大写，其他一般为小写（增强可读性）。

SELECT first_name,last_name

FROM　employees;

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

select first_name,salary

from employees

where salary+100< 5000;

注：运算也可以用在WHERE条件上,除了From子句都可以。

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

select sysdate,sysdate+1, sysdate-1, systimestamp, systimestamp+1, systimestamp-1 from dual;

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

select salary "Sal($)"

from employees;

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

select department_name ||

', it's assigned Manager Id: '

|| manager_id

AS "Department and Manager"

from departments;

ERROR:

ORA-01756:quoted string not properly terminated

select department_name ||

q'[, it'sassigned Manager Id: ]'

||manager_id

AS"Department and Manager"

from departments;

注：这里的转义符可以是[ ], { }, ( ), or < >。

等价于

select department_name ||

q'\, it'sassigned Manager Id: \'

||manager_id

AS"Department and Manager"

from departments;

等价于

select department_name ||

', it''sassigned Manager Id: '

||manager_id

AS"Department and Manager"

from departments;

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

select department_id, job_id

from employees;

比较

select distinct department_id, job_id

from employees;

注：这里会把(department_id,job_id)一起重复的数据过滤。

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

PPT2:Restricting and Sorting Data

SELECT last_name,job_id, department_id

FROM employees

WHERE last_name = 'King' ;

比较

SELECT last_name,job_id, department_id

FROM employees

WHERE last_name = 'KING' ;

SELECT last_name,job_id, hire_date

FROM employees

WHERE hire_date='17-JUN-87';

比较

SELECT last_name, job_id, hire_date

FROM employees

WHERE hire_date='17-JUN-1987';

比较

select LAST_NAME,HIRE_DATE

from employees

where HIRE_DATE='1997-08-17';

ERROR at line 3:

ORA-01861: literal does not match format string

注：字符是大小写敏感的，日期是格式敏感的。

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

SELECT last_name,salary

FROM employees

WHERE salary BETWEEN 2500 AND 3500 ;

等价于

SELECT last_name,salary

FROM employees

WHERE salary>= 2500 AND salary <= 3500;

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

SELECT employee_id, last_name, job_id

FROM employees WHERE job_id LIKE '%SA_%'

比较

SELECTemployee_id, last_name, job_id

FROM employees WHERE job_id LIKE '%SA\_%' ESCAPE '\';

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

SELECT last_name, manager_id

FROM employees

WHERE manager_id IS NULL ;

比较

SELECT last_name, manager_id

FROM employees

WHERE manager_id = NULL ;

注：IS NULL条件用于空值测试,不能用"="等号测试空值，因为null不能等于或不等于任何值。

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

SELECT last_name, job_id

FROM employees

WHERE job_id NOT IN ('IT_PROG','ST_CLERK', 'SA_REP') ;

等价于

SELECT last_name, job_id

FROM employees

WHERE job_id !='IT_PROG' or job_id != 'ST_CLERK' or job_id != 'SA_REP';

The symbols != and ^= can also represent the not equal to condition.

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

SELECT last_name, job_id,department_id, hire_date

FROM employees

ORDER BY department_id;

等价于

SELECT last_name, job_id,department_id, hire_date

FROM employees

ORDER BY 3;

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

SELECT first_name,commission_pct

from employees

order by commission_pct asc;

注：null 这里会认为是无穷大。

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

SQL> insert into test values (&1,&2); ----要先建个test表

Enter value for 1: 1

Enter value for 2: 1

old 1: insert into test values (&1,&2)

new 1: insert into test values (1,1)

1 row created.

SQL> select *from test;

COL1 COL2

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

1 1

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

SQL> insert into test values (&&1,&&2);

SQL>/

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

DEFINE employee_num = 200;

SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE employee_id = &employee_num ;--因为前面有deffine employee_number,这里就不需要输入啦

UNDEFINE employee_num;--记得要把原先设定的值去掉

SET VERIFY ON/OFF （display of the substitution variable, both before and after iSQL*Plus replaces substitution variables with values）

显示/不显示

SHOW ALL --显示系统变量类型

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

Not only can you use the substitution variables in the WHERE clause of a SQL statement, but these variables can also be used to substitute for column names, expressions, or text.

Example

SELECT employee_id, last_name, job_id,&column_name
FROM employees
WHERE &condition
ORDER BY &order_column ;

The slide example displays the employee number, name, job title, and any other column that is specified by the user at run time, from the EMPLOYEES table. For each substitution variable in the SELECT statement, you are prompted to enter a value, and you then click the Continue button to proceed.
If you do not enter a value for the substitution variable, you get an error when you execute the preceding statement.

PPT3:Using Single-Row Functions to Customize Output

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

select last_name||job_id AS "Employees"

from employees;

等价于

select CONCAT(last_name,job_id) AS "Employees"

from employees;

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

SQL> select lpad('test',10) from dual;-左侧加10个空

LPAD('TEST

----------

test

SQL> select rpad('test',10) from dual;--右侧加10个空

RPAD('TEST

----------

test

SQL> select lpad('test',2) from dual;

SQL> select rpad('test',2) from dual;

lpad(string,n,[pad_string])：其中string1是需要粘贴字符的字符串
padded_length是返回的字符串的数量，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成padded_length;
pad_string是个可选参数，这个字符串是要粘贴到string1的左边，如果这个参数未写，lpad函数将会在string1的左边粘贴空格。

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

SQL> select substr('oracle10g', 4) from dual;

SUBSTR

------

cle10g

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

SQL> select trim(' 12 3 ') from dual;

TRIM

----

12 3

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

ROUND(for number)

ROUND函数返回处理后的数值，该函数对指定小数前或后的部分做相应舍入选择处理。

其具体的语法格式如下

ROUND（number[,decimals]）

其中：

number需四舍五入处理的数值

decimals四舍五入 , 小数取几位 ( 预设为 0 )

round(123.456, 0)=123

round(123.456, 1)=123.5

round(123.456, 2)=123.46

round(123.456, 3)=123.456

TRUNC(for number)

TRUNC函数返回处理后的数值，其工作机制与ROUND函数极为类似，只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去。

其具体的语法格式如下

TRUNC（number[,decimals]）

其中：

number 待做截取处理的数值

decimals 指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分

TRUNC（89.985，2）=89.98

TRUNC（89.985）=89

TRUNC（89.985，-1）=80

注意：第二个参数可以为负数，表示为小数点左边指定位数后面的部分截去，即均以0记。

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

SQL> select mod(2,3) from dual;

MOD(2,3)

----------

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

月的3字母缩写：
JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
周：
SUNDAY MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY

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

SELECT last_name, hire_date

FROM employees

WHERE hire_date < to_date('01-FEB-2088','DD-MON-YY');

比较

SELECT last_name, hire_date

FROM employees

WHERE hire_date < to_date('01-FEB-1988','DD-MON-YY');

比较

SELECT last_name, hire_date

FROM employees

WHERE hire_date < to_date('01-FEB-88','DD-MON-YY');

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

select first_name, salary

from employees

where salary='8300';

比较

select first_name, salary

from employees

where salary=8300;

select first_name, hire_date

from employees

where hire_date='07-JUN-94';

比较

select first_name, hire_date

from employees

where hire_date=to_date('07-JUN-1994',DD-MON-YY’);--结果是'07-JUN-94'

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

Single-row functions：Character，General，Number，Conversion，Date

Character functions: Accept character input and can return both character and number values
Number functions: Accept numeric input and return numeric values
Date functions: Operate on values of the DATE data type (All date functions return a value of DATE data type except the MONTHS_BETWEEN function, which returns a number.)
Conversion functions: Convert a value from one data type to another
General functions:
NVL
NVL2
NULLIF
COALESCE
CASE
DECODE

Character Functions
Single-row character functions accept character data as input and can return both character and numeric values. Character functions can be divided into the following:
Case-manipulation functions （LOWER，UPPER，INITCAP）
Character-manipulation functions （CONCAT，SUBSTR，LENGTH，INSTR，LPAD | RPAD，TRIM，REPLACE）

SELECT 'The job id for '||UPPER(last_name)||' is '
||LOWER(job_id) ,'and name is'||INITCAP(employee_name) AS "EMPLOYEE DETAILS"
FROM   employees;

Function                                                        Result
CONCAT('Hello', 'World')                        HelloWorld
SUBSTR('HelloWorld',1,5)                      Hello
LENGTH('HelloWorld')                            10
INSTR('HelloWorld', 'W')                          6
LPAD(salary,10,'*')                                  *****24000
RPAD(salary, 10, '*')                               24000*****
REPLACE ('JACK and JUE','J','BL')       BLACK and BLUE
TRIM('H' FROM 'HelloWorld')                 elloWorld

Date Functions
Date functions operate on Oracle dates. All date functions return a value of DATE data type except MONTHS_BETWEEN, which returns a numeric value.
MONTHS_BETWEEN(date1, date2): Finds the number of months between date1 and date2. The result can be positive or negative. If date1 is later than date2, the result is positive; if date1 is earlier than date2, the result is negative. The noninteger part of the result represents a portion of the month.
ADD_MONTHS(date, n): Adds n number of calendar months to date. The value of n must be an integer and can be negative.
NEXT_DAY(date, 'char'): Finds the date of the next specified day of the week ('char') following date. The value of char may be a number representing a day or a character string.
LAST_DAY(date): Finds the date of the last day of the month that contains date
ROUND(date[,'fmt']): Returns date rounded to the unit that is specified by the format model fmt. If the format model fmt is omitted, date is rounded to the nearest day.
TRUNC(date[, 'fmt']): Returns date with the time portion of the day truncated to the unit that is specified by the format model fmt. If the format model fmt is omitted, date is truncated to the nearest day.

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

SELECT employee_id, hire_date,
MONTHS_BETWEEN (SYSDATE, hire_date) TENURE,
ADD_MONTHS (hire_date, 6) REVIEW,
NEXT_DAY (hire_date, 'FRIDAY'), LAST_DAY(hire_date)
FROM employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date) < 36;

SELECT employee_id, hire_date,
ROUND(hire_date, 'MONTH'), TRUNC(hire_date, 'MONTH'),ROUND(hire_date, 'YEAR'), TRUNC(hire_date, 'YEAR')
FROM employees
WHERE hire_date LIKE '%97';
------------------------------------------------------------------------------

Data type conversion

In addition to Oracle data types, columns of tables in an Oracle database can be defined using ANSI, DB2, and SQL/DS data types. However, the Oracle server internally converts such data types to Oracle data types.
In some cases, the Oracle server uses data of one data type where it expects data of a different data type. When this happens, the Oracle server can automatically convert the data to the expected data type. This data type conversion can be done implicitly by the Oracle server or explicitly by the user.
Implicit data type conversions work according to the rules that are explained in the next two slides.
Explicit data type conversions are done by using the conversion functions. Conversion functions convert a value from one data type to another. Generally, the form of the function names follows the convention data type TO data type. The first data type is the input data type; the second data type is the output.
Note: Although implicit data type conversion is available, it is recommended that you do explicit data type conversion to ensure the reliability of your SQL statements.

Implicit data type conversions

From                                             To
VARCHAR2 or CHAR             NUMBER
VARCHAR2 or CHAR              DATE
NUMBER                                 VARCHAR2
DATE                                      VARCHAR2

Explicit data type conversions

NUMBER --TO_CHAR-->CHARACTER--TO_DATE-->DATE

DATE --TO_CHAR-->CHARACTER--TO_NUMBER-->NUMBER

Elements of the Date Format Model

Element                                     Result
YYYY                              Full year in numbers
YEAR                             Year spelled out (in English)
MM                                  Two-digit value for month
MONTH                           Full name of the month
MON                               Three-letter abbreviation of the month
DY                                   Three-letter abbreviation of the day of the week
DAY                                 Full name of the day of the week
DD                                   Numeric day of the month

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

Select to_char(salary,’$999,999.00’) from emp;
---------------------------------------------------------------------

Elements of the Date Format Model

Time elements format the time portion of the date:

HH24:MI:SS AM

15:45:32 PM

Add character strings by enclosing them in double quotation marks:

DD "of" MONTH

12 of OCTOBER

Number suffixes spell out numbers:

ddspth

fourteenth

--------------------------------------
SELECT last_name,
TO_CHAR(hire_date,
'fmDdspth "of" Month YYYY fmHH:MI:SS AM')
HIREDATE
FROM employees;
------------------------------------------

SELECT TO_CHAR(salary, '$99,999.00') SALARY
FROM employees
WHERE last_name = 'Ernst';

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

SELECT last_name, hire_date
FROM employees
WHERE hire_date = TO_DATE('May 24, 1999', 'fxMonth DD, YYYY');
-------------------------------------------------------------

RR Date Format Element

...这块我终于知道怎么上传图片了，那以后方便多了。。。。

SELECT   TO_CHAR(NEXT_DAY(ADD_MONTHS
            (hire_date, 6), 'FRIDAY'),
            'fmDay, Month DDth, YYYY')
            "Next 6 Month Review"
   FROM      employees
   ORDER BY hire_date;
-----------------

General Functions

NVL (expr1, expr2) expr1 is the source value or expression that may contain a null,expr2 is the target value for converting the null
NVL2 (expr1, expr2, expr3) expr1 is the source value or expression that may contain null,expr2 is the value that is returned if expr1 is not null,expr3 is the value that is returned if expr1 is null
NULLIF (expr1, expr2) 两值比较，相等返回null，不想等返回expr1
COALESCE (expr1, expr2, ..., exprn) expr1 returns this expression if it is not null,expr2 returns this expression if the first expression is null and this expression is not null,exprn returns this expression if the preceding expressions are null

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

SELECT last_name,
COALESCE(manager_id,commission_pct, -1) comm
FROM employees
ORDER BY commission_pct;

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

Using the CASE Expression

SELECT last_name,salary,
(CASE WHEN salary<5000 THEN 'Low'
      WHEN salary<10000 THEN 'Medium'
      WHEN salary<20000 THEN 'Good'
      ELSE 'Excellent'
END) qualified_salary
FROM employees;
----------------------------

Using the DECODE Function --相当于if then else

SELECT last_name, job_id, salary,
       DECODE(job_id, 'IT_PROG', 1.10*salary,
                      'ST_CLERK', 1.15*salary,
                      'SA_REP',   1.20*salary,
              salary)
       REVISED_SALARY
FROM   employees;

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

PPT4:ReportingAggregated Data Using the Group Functions

Types of Group Functions

AVG
COUNT
MAX （MAX and MIN cannot be used with LOB or LONG data types）
MIN
STDDEV
SUM
VARIANCE

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

SELECT department_id, AVG(salary)

FROM employees

WHERE salary > 8000

GROUP BY department_id;

SELECT department_id, AVG(salary)

FROM employees

GROUP BY department_id

Having salary > 8000;

ERROR at line 4:

ORA-00979: not a GROUP BY expression

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

SELECT job_id, SUM(salary) PAYROLL

FROM employees

WHERE job_id NOT LIKE '%REP%'

GROUP BY job_id

HAVING SUM(salary) > 13000

ORDER BY SUM(salary);

等价于

SELECT job_id, SUM(salary) PAYROLL

FROM employees

WHERE job_id NOT LIKE '%REP%'

GROUP BY job_id

HAVING SUM(salary) > 13000

ORDER BY PAYROLL;

SELECT job_id, SUM(salary) PAYROLL

FROM employees

WHERE job_id NOT LIKE '%REP%'

GROUP BY job_id

HAVING PAYROLL > 13000

ORDER BY PAYROLL;

ERROR at line 5:

ORA-00904: "PAYROLL": invalid identifier

SELECT job_id, SUM(salary) PAYROLL

FROM employees

WHERE job_id NOT LIKE '%REP%'

ORDER BY PAYROLL

GROUP BY job_id

HAVING SUM(salary) > 13000;

ERROR at line 5:

ORA-00933: SQL command not properly ended

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

--求部门号为90的员工的平均工资，显示部门号，平均工资

select department_id, avg(salary)

from employees

where department_id=90

group by department_id;

等价于

select department_id, avg(salary)

from employees

group by department_id

having department_id=90;

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

Oracle的GROUP BY语句除了最基本的语法外，还支持ROLLUP和CUBE语句：

如果是GROUP BY ROLLUP(A, B, C)的话，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。

SELECT department_id, job_id, SUM(salary)

FROM employees

GROUP BY (department_id, job_id) ;

SELECT department_id, SUM(salary)

FROM employees

GROUP BY (department_id) ;

SELECT SUM(salary)

FROM employees ;

SELECT department_id, job_id, SUM(salary)

FROM employees

GROUP BY rollup(department_id, job_id) ;

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

如果是GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。

SELECT department_id, job_id, SUM(salary)

FROM employees

GROUP BY (department_id, job_id) ;

SELECT department_id, SUM(salary)

FROM employees

GROUP BY (department_id) ;

SELECT job_id, SUM(salary)

FROM employees

GROUP BY (job_id) ;

SELECT SUM(salary)

FROM employees ;

SELECT department_id, job_id, SUM(salary)

FROM employees

GROUP BY cube(department_id, job_id) ;

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

GROUPING函数可以接受一列，返回0或者1。如果列值为空，那么GROUPING()返回1；如果列值非空，那么返回0。GROUPING只能在使用ROLLUP或CUBE的查询中使用。当需要在返回空值的地方显示某个值时，GROUPING()就非常有用。

SELECT grouping(department_id), department_id dept_id, SUM(salary)

FROM employees

GROUP BY rollup(department_id) ;

SELECT case grouping(department_id)

when 0 then to_char(department_id)

else 'total'

end dept_id, SUM(salary)

FROM employees

GROUP BY rollup(department_id) ;

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

Lesion 5 Displaying Data from Multiple Tables

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

Types of Joins

Joins that are compliant with the SQL:1999 standard include the following:
Cross joins
Natural joins
USING clause
Full (or two-sided) outer joins
Arbitrary join conditions for outer joins
-------------------------------------------------------------------

USING Clause

Natural joins use all columns with matching names and data types to join the tables. The USING clause can be used to specify only those columns that should be used for an equijoin. The columns that are referenced in the USING clause should not have a qualifier (table name or alias) anywhere in the SQL statement.

For example, the following statement is valid:
SELECT l.city, d.department_name
FROM locations l JOIN departments d USING (location_id)
WHERE location_id = 1400;

The following statement is invalid because the LOCATION_ID is qualified in the WHERE clause:
SELECT l.city, d.department_name
FROM locations l JOIN departments d USING (location_id)
WHERE d.location_id = 1400;
ORA-25154: column part of USING clause cannot have qualifier
The same restriction also applies to NATURAL joins. Therefore, columns that have the same name in both tables must be used without any qualifiers.

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

SELECT table1.column, table2.column
FROM table1

[NATURAL JOIN table2] | ---基于相同的列名(有相同名字的所有列)连接两个表

[CROSS JOIN table2] ---从两个表中返回笛卡尔乘积;

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

SELECT department_id, department_name,location_id, city

FROM departments

WHERE department_id IN (20, 50)

NATURAL JOIN locations;

ERROR at line 4:

ORA-00933: SQL command not properly ended

SELECT department_id, department_name, location_id, city

FROM departments d, locations l

WHERE d.location_id = l.location_id and department_id IN (20, 50);

ERROR at line 1:

ORA-00918: column ambiguously defined

SELECT department_id, department_name, d.location_id, city

FROM departments d, locations l

WHERE d.location_id = l.location_id and department_id IN (20, 50);

SELECT d.department_id, d.department_name, l.location_id, l.city

FROM departments d

NATURAL JOIN locations l

WHERE d.department_id IN (20, 50);

ERROR at line 1:

ORA-25155: column used in NATURAL join cannot have qualifier

SELECT d.department_id, d.department_name, location_id, l.city

FROM departments d

NATURAL JOIN locations l

WHERE d.department_id IN (20, 50);

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

SELECT l.city, d.department_name

FROM locations l JOIN departments d USING (location_id)

WHERE location_id = 1400;

SELECT l.city, d.department_name

FROM locations l JOIN departments d USING (location_id)

WHERE d.location_id = 1400;

ERROR at line 3:

ORA-25154: column part of USING clause cannot have qualifier

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

SELECT e.employee_id, e.last_name, e.department_id,

d.department_id, d.location_id

FROM employees e JOIN departments d

ON (e.department_id = d.department_id)

AND e.manager_id = 149 ;

等价于

SELECT e.employee_id, e.last_name, e.department_id,

d.department_id, d.location_id

FROM employees e JOIN departments d

ON (e.department_id = d.department_id)

WHERE d.manager_id = 149;

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